database Notice after upstanding to 6.1.5

[jpayam]

Hi,

 

Today after manual  upgrade from   6.1.1 to 6.1.5 my store seems works fine only in admin panel  ADVANCED >  maintenance > database i see  below  notice:

CubeCart_admin_users.admin_id has a key type KEY but expecting PRIMARY.

CubeCart_category.cat_parent_id has no key set. Expecting KEY.

CubeCart_admin_users.admin_id has a key type KEY but expecting PRIMARY.

CubeCart_category_index.id has a key type KEY but expecting PRIMARY.
CubeCart_category_index.cat_id has no key set. Expecting KEY.

. CubeCart_category_language.translation_id has a key type KEY but expecting PRIMARY.

CubeCart_order_inventory.id has a key type KEY but expecting PRIMARY. CubeCart_order_inventory.product_id has no key set. Expecting KEY. CubeCart_order_inventory.cart_order_id has no key set. Expecting KEY.

...

....

[havenswift-hosting]

This is a new fantastic tool added in 6.1.5 that indicates problems with your database indexes.  You can use phpMyAdmin to alter these and correct them as adding indexes and correcting ones that are wrong will speed up your store

Ian

[Dirty Butter]

If you are like I am and don't have experience changing Index Keys on the tables, it's a good idea to backup or make a COPY of your database before trying to make each of those changes. If something goes wrong you will be able to recover.

[jpayam]

any help would be appreciated.

is there any mysql comment to correct this?

[bsmither]

Truthfully, these banners indicate a less than ideal state of your database schema. That said, there is nothing about this situation that will break your store. Consider it the equivalent of removing a few barnacles off the hull of your sailboat. Doing so is only important if you need the utmost speed in the regatta.

[bsmither]

There are a couple of issues posted in the Github that hopes to resolve a few anomalies about the indicators in this list.

Some of the messages may be false positives.

[havenswift-hosting]

@bsmither while it is true in that a store will generally continue to work if people do nothing about these database warnings (searching will fail in certain circumstances if the wrong type of index or no index is applied) but that isn't to say they don't have a detrimental effect in other areas as well so it is always better to deal with them than simply ignore them.

All the stores that we see that fail (as in don't make enough money to justify the costs / work) are mostly down to store owners that have stores that run "fine" but they don't pay attention to four or five areas. Speed of the website is one of those areas as this affects both user satisfaction and also Google rankings

[bsmither]

If this banner is in your list:

CubeCart_inventory.popularity has no key set. Expecting KEY.

then execute this query:

ALTER TABLE `CubeCart_inventory` ADD INDEX `popularity`(`popularity`);

A fresh install of CC6 will have this index (as of CC600), but upgrading from a version prior to that will have this index dropped at CC500b1 or otherwise not put in place, and the mistake is not eventually adding it back at the CC600 upgrade step.

You can use phpMyAdmin (a tool in your hosting control panel) to do this, as well as examining all the other banners shown and adding any missing keys.

[jpayam]

Thanks a Lot.  

Now there is not any notice for "Expecting KEY" , any suggestion for  expecting PRIMARY.  and   expecting UNIQUE KEY  notices will be highly appreciated.

[bsmither]

I think those are false positives. The code to fix those are in CC616.

Again using phpMyAdmin, examine the indexes that are in the referenced tables. If you see more than one index for the same column, and there is in fact a PRIMARY or UNIQUE key as well as any other kind of key for that column, then the false positive is from not knowing what to do about that other key. CC616 will know what to do with indexes that are duplicates or are superfluous.

[havenswift-hosting]

There are some duplicate indexes, and ideally the incorrect one of these should be deleted as writing two duplicate indexes each time a record is written nearly doubles the amount of time needed for each database write.  However, there are also some tables that dont have any indexes at all and these also need sorting out - so each notice needs to be looked at carefully and the appropriate action taken

Ian

[Dirty Butter]

In my MyPhpAdmin when I look at the Structure of a table, there is a little +Indexes link underneath the list of entries.

Click on that +Indexes for each table you received an orange notice of duplicates. The Duplicated lines with 2, 3, etc. can be deleted one at a time. Try deleting all duplicates on one table and then refresh your Admin Database tab. The orange warning on that table should disappear.