DataBase query after upgrade

[shags38]

v1.5.7b

I've just upgraded from 1.55f to 1.5.7b and all went very well with the exception of 1 issue with the database. It is more my lack of understanding of MySQL rather than an issue.

Between staring to build the upgraded site and making it live there were some orders received on the live 1.5.5f site. I did an export from the live site database of the tables I required, i.e. orders, order status etc - customers, address book , customer info etc. So I did a custom export ticking the tables I wanted I then imported those tables into the new database but that created a problem, parts of admin went blank and an error warning showed up.

I then reinstalled the saved copy of the new database and all was fine - but orders and customers are not up to date. I then just exported the lines within the orders table only and imported them, again an sql error showed in phpMyAdmin (see below) - didn't affect my admin but still the orders are not updated.

So what is the correct way to obtain the data I want from the old database and then put it into the new database?

I'd really appreciate some assistance.

cheers, Mike

Error

SQL query:

--
-- Database: `dazzler3_155fjuly2020`
--

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `orders_id` int(11) NOT NULL,
  `customers_id` int(11) NOT NULL DEFAULT 0,
  `customers_name` varchar(64) NOT NULL DEFAULT '',
  `customers_company` varchar(64) DEFAULT NULL,
  `customers_street_address` varchar(64) NOT NULL DEFAULT '',
  `customers_suburb` varchar(32) DEFAULT NULL,
  `customers_city` varchar(32) NOT NULL DEFAULT '',
  `customers_postcode` varchar(10) NOT NULL DEFAULT '',
  `customers_state` varchar(32) DEFAULT NULL,
  `customers_country` varchar(32) NOT NULL DEFAULT '',
  `customers_telephone` varchar(32) NOT NULL DEFAULT '',
  `customers_email_address` varchar(96) NOT NULL DEFAULT '',
  `customers_address_format_id` int(5) NOT NULL DEFAULT 0,
  `delivery_name` varchar(64) NOT NULL DEFAULT '',
  `delivery_company` varchar(64) DEFAULT NULL,
  `delivery_street_address` varchar(64) NOT NULL DEFAULT[...]

MySQL said: Documentation
#1050 - Table 'orders' already exists

[marton_1]

I am by no means an SQL expert but I believe you should somehow use the INSERT command not CREATE, maybe read up on it?

[mc12345678]

The live database should continue to operate and process orders until the new store is being put into place and operational.... What that means is while you are testing your rebuild/upgrade the database that you are accessing should be independent of your live site and *WILL* become out-of-date... Once the upgrade looks like it is good *AND* you have tested doing the upgrade of the database (import of copy of live store to this rebuild area and database upgrade), *THEN* it is time to temporarily place the live site in maintenance mode, either move the files or redirect visitors to your new location so that access to your normal site location is to the new fileset. Before again taking the site out of maintenance mode, update the database, verify that store is operational and then take it out of maintenance mode.

The time during which the site is in maintenance mode based on the above could be as short as a few minutes but plan for about an hour just to be sure that all is working as it should...

As far as the issue about doing this potentially dangerous database data move, which even now your new site is going to have out-of-date information, the table should only be created if it is missing or doesn't exist:

CREATE TABLE IF NOT EXISTS `orders`(

This should be a selection option when exporting the existing table from phpMyAdmin, though there still is the concern about other records that *should* be moved, but have not been selected.

Please re-review the guidance on updating: [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://docs.zen-cart.com/user/upgrading/detailed_upgrading/[/FONT]

[shags38]

The live database should continue to operate and process orders until the new store is being put into place and operational.... What that means is while you are testing your rebuild/upgrade the database that you are accessing should be independent of your live site and *WILL* become out-of-date... Once the upgrade looks like it is good *AND* you have tested doing the upgrade of the database (import of copy of live store to this rebuild area and database upgrade), *THEN* it is time to temporarily place the live site in maintenance mode, either move the files or redirect visitors to your new location so that access to your normal site location is to the new fileset. Before again taking the site out of maintenance mode, update the database, verify that store is operational and then take it out of maintenance mode.

The time during which the site is in maintenance mode based on the above could be as short as a few minutes but plan for about an hour just to be sure that all is working as it should...

As far as the issue about doing this potentially dangerous database data move, which even now your new site is going to have out-of-date information, the table should only be created if it is missing or doesn't exist:

CREATE TABLE IF NOT EXISTS `orders`(

This should be a selection option when exporting the existing table from phpMyAdmin, though there still is the concern about other records that *should* be moved, but have not been selected.

Please re-review the guidance on updating: [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://docs.zen-cart.com/user/upgrading/detailed_upgrading/[/FONT]

Thanks MC,

I thought I'd followed the upgrade correctly in respect to the database, but obviously not. I built the site in a folder in the root using the rebuild process, not a normal upgrade - after installing the new version I copied the existing database from the live site to the new rebuild and did the update.

Seems I should have copied across the existing database into the new rebuild after testing it and just before going live?

So to try to recover the DB situation I could delete the tables in the new 1.5.7b site, then import the tables from the 1.5.5f DB which is more current - obviously it would need to be done one after the other in quick time with site in maintenance mode - as long as I have full backup copies of both databases I at least will have a fall back position.

I think now that I have made a right royal mess of the upgrade - may need to restore the old site and start again.

[DrByte]

Seems like when you created the database backup/export you didn't include the "Drop Tables" option which deletes any pre-existing table when importing.
That option is often "unchecked" by default so that people don't accidentally wipe out production databases by importing to the wrong destination, and implies that the person using the exported data to import into another database will first make sure those tables don't already exist (eg: delete/drop them manually, or use a new clean database) in the database they're restoring into.

[shags38]

Seems like when you created the database backup/export you didn't include the "Drop Tables" option which deletes any pre-existing table when importing.
That option is often "unchecked" by default so that people don't accidentally wipe out production databases by importing to the wrong destination, and implies that the person using the exported data to import into another database will first make sure those tables don't already exist (eg: delete/drop them manually, or use a new clean database) in the database they're restoring into.

Thanks DrByte - that is what I've done - I normally religiously check the add drop tables when exporting, someone told me that many moons ago - at about 1:00am or so this morning, tired and frustrated, and only exporting a couple of tables I ommited to check that box - my fault. So from what you are saying, if I repeat the process making sure I check the add drop tables in the export then the import process will drop the existing tables and import those in the exported sql ... have I got that right?

again many thanks,
cheers
Mike

[DrByte]

if I repeat the process making sure I check the add drop tables in the export then the import process will drop the existing tables and import those in the exported sql ... have I got that right?

Yes, when you tick the drop-tables checkbox and select some tables to export, importing that exported file will first drop each table before recreating and loading that table with whatever is in the export file, table by table.

[shags38]

Yes, when you tick the drop-tables checkbox and select some tables to export, importing that exported file will first drop each table before recreating and loading that table with whatever is in the export file, table by table.

Thanks for the reply DrByte - so I followed that process and it went OK at phpMyAdmin, no errors, no warnings and I could see the tables had been adjusted .... however in ZC admin half the home page was blank, the RH side section that shows customers, orders, stats etc, as was admin/customers and admin/orders ... so I reinstalled my saved master copy of the database and everything was reinstated, minus the extra data I was trying to retrieve.

I can live without that data being added but just wondered if you had thoughts as to why this would occur?

cheers,
Mike

[mc12345678]

Thanks for the reply DrByte - so I followed that process and it went OK at phpMyAdmin, no errors, no warnings and I could see the tables had been adjusted .... however in ZC admin half the home page was blank, the RH side section that shows customers, orders, stats etc, as was admin/customers and admin/orders ... so I reinstalled my saved master copy of the database and everything was reinstated, minus the extra data I was trying to retrieve.

I can live without that data being added but just wondered if you had thoughts as to why this would occur?

cheers,
Mike

If the data pulled was not of the current ZC version, then it would need to be updated through the zc_install process... This can be done through jumping through other hoops, but ultimately the data needs to only be the most up-to-date when it is time to make the data part of the new live site under the new software... That is why there is the process described previously (though I didn't provide the link to the "simple" upgrade by rebuild process which I expect to be linked from what I previously provided... FYI, I'm still working on my level of familiarity with the new instruction considering I have limited ways by which to access and search it because of my own issues.)

As for the issues that occurred and has been suggested time and again, check your logs folder... When something stops being presented it is because of an error of some sort. More than likely the cause will be logged in one way or another... Obviously it is possible that there is incorrect logic that will give "unexpected" results though the results that were programmed.