Database After upgrade

[JulesInFrance]

Hi, I ran a difference checker between the database post 443 => 500 upgrade and a cc500 fresh install and have well over 2,000 structural differences.

I'm sure the differences are all minor but as a non-techy/non-developer I was surprised to see

Missing Tables in Fresh Install (so could have been in the Upgrade as a result of cc4xx Addons)

Missing Columns in Upgrade

Missing or mis-stated KEY and PRIMARY KEY values

Multiple instances of numerous variations between column attributes in Upgrade; lots of missing 'unsigned', 'NOT NULL', 'tinyint', 'smallint', etc, etc (etc) => there's certainly 1.000+ of these

Multiple variations between auto_increment values (After Upgrade I have one where ENGINE MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2363 COLLATE=utf8_unicode_ci ; whereas the Fresh Install has AUTO_INCREMENT=1 !!!! => There's certainly 500+ of these

The order of many, many columns within tables is materially different.

My guess is that If I was to attempt to make my Upgrade DB reflect the Fresh Install DB at approximately 1 minute per change would take me 40 hours work or around £2,000 of labour to resolve.

I tried to lay them out here but the post was too big for the forum, I tried to attach the db structure files but the forum does not accept that file format.

So you'll have to take my word for it.

Jules

PS I used CHANGES Version 1.5.6 (524) on Mac OSX for analysis

[Guest hennaboy]

There is a lot of variables to take into account when upgrading rather than clean install it would be impossible to take into account all of them.

Modifications will make upgrade indeed more difficult especially if you have modifications which has altered the base structure of the database. This could lead to various issues with the upgrading process for the database.

The auto increment will be correct on a upgrade. It will always start at 1 for a clean install as thats how many entries have occured 1. The high number is your current total which has been preserved.

Where did you find missing KEY and other column settings? which database tables?

[JulesInFrance]

There is a lot of variables to take into account when upgrading rather than clean install it would be impossible to take into account all of them.

Modifications will make upgrade indeed more difficult especially if you have modifications which has altered the base structure of the database. This could lead to various issues with the upgrading process for the database.

The auto increment will be correct on a upgrade. It will always start at 1 for a clean install as thats how many entries have occured 1. The high number is your current total which has been preserved.

Where did you find missing KEY and other column settings? which database tables?

HIya,

#1 Thank you very much for clarifying that the structural value for AUTO_INCREMENT is the last value used rather than the frequency. Though I am shattered to think that the next value for sts_CubeCart_category_index => AUTO_INCREMENT=4502 => is 4,503!

I'm also surprised that the base install of cc500 has in that case some quite high values when it's seen no data. But hey, what do I know?

#2 I do take your point that when, say, the whole way catalogue images are handled and indexed is changed between versions then that will of necessity leave orphaned tables/columns/data in the database but other issues, when a field type in cc500 changes materially from that in cc443 and the changes are not carried over fails to inspire confidence in me.

I already lost all of my settings yet found my metadata still appearing in the browser though erased from setting; whaddya-know that seo data is one of the tables/columns that has material variances between the cc500 pure format and the 443=500 legacy format.

Like I saw, what do I know?

#3 Key/Primary Key variations (excluding cc500 omissions)

cc5_CubeCart_category_language has KEY `id` (`translation_id`), which has no equivalent after Upgrade

cc5_CubeCart_currency has PRIMARY KEY (`currency_id`), which has not equivalent after Upgrade

cc5_CubeCart_inventory has KEY `status` (`status`), which has no equivalent after Upgrade

cc5_CubeCart_inventory Fresh Install has FULLTEXT KEY `fulltext` (`product_code`,`description`,`name`) whereas Upgrade has FULLTEXT KEY `fulltext` (`product_code`,`name`,`description`)

cc5_CubeCart_inventory_language has KEY `id` (`translation_id`), which has not equivalent after Upgrade

cc5_CubeCart_modules has KEY `module_id` (`module_id`) which has no equivalent after Upgrade

cc5_CubeCart_option_assign has KEY `member_id` (`set_member_id`) which has no equivalent after Upgrade

cc5_CubeCart_order_inventory has KEY `product_id` (`product_id`), which has no equivalent after Upgrade

cc5_CubeCart_order_notes has KEY `admin_id` (`admin_id`,`cart_order_id`,`time`), for which the closest equivalent is KEY `admin_id` (`admin_id`),

cc5_CubeCart_sessions has KEY `customer_id` (`customer_id`), AND KEY `session_last` (`session_last`) for which there are no equivalents after upgrade

cc5_CubeCart_transactions has KEY `customer_id` (`customer_id`), which has no equivalent after Upgrade

ERRORS AND OMISSIONS EXCLUDED

#4 Other observations

cc5_CubeCart_category_language column `seo_custom_url` text, has not equivalent after Upgrade

cc5_CubeCart_customer column `verify` varchar(32), has not equivalent after Upgrade

cc5_CubeCart_documents columns `seo_meta_description` text NOT NULL, AND `seo_meta_keywords` text NOT NULL, have no equivalent after Upgrade (note that cc443 columns `doc_metadesc` text NOT NULL, AND `doc_metakeywords` text NOT NULL, were orphaned by the changes)

cc5_CubeCart_inventory column `image` varbinary(250), AND `stock_by_option` tinyint(1) unsigned NOT NULL, AND `updated` timestamp DEFAULT CURRENT_TIMESTAMP, have no equivalent after Upgrade

cc5_CubeCart_inventory_language columns `language` varchar(5) NOT NULL, AND `name` varchar(255) NOT NULL, have no equivalent after Upgrade

cc5_CubeCart_order_summary column `offline_capture` blob, has not equivalent after Upgrade

ERRORS AND OMISSIONS EXCLUDED

Jules

[Guest hennaboy]

You have to bear in mind that every time you insert into the database the auto increment will go up. So if you create a test category it will go up by 1. If you delete that category it will not go back down. This is how mysql works nothing to do with the CC programming.

If on a clean install some tables have a high auto increment value this is due to the database being exported after all the tests have been done. Not really looked to see if they have done this. But its something they could quite easily rectify for clean installs.

As for the keys and data. Your dealing with a complete restructure. Not a minor one but even the database class which controls the connection to the database and its its queries and results has been changed. As such changes in what is now the primary key could have been done however it will not affect how your store runs.

If anything changing the key can have a better reaction than you may think. It makes for a more optimized database.

I was more than expecting tables and fields from an upgrade to become redundant. However from where I am sitting at the moment it all looks like it has been upgraded just fine.

[JulesInFrance]

Thanks for your thoughts and I'm glad it's working for you.

For me sadly not (too many crashes and data losses.

Jules

[Al Brookbanks]

LOL most of the DB inconsistencies really make no difference at all. Others might though. There is a bug report about this and we will check over all of them.

Our local telephone exchange got hit by lightening the other day and we have had no phone or internet which has massively affected us and delayed progress. The phone company say it won't be back until Monday.

We'll get any v5 niggles cleared up over the next week. Thanks to all for reporting any problems and for your patience. v5 had a huge amount of beta testing but things inevitably have slipped though.