jpayam Posted February 19, 2017 Share Posted February 19, 2017 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. ... .... Quote Link to comment Share on other sites More sharing options...
havenswift-hosting Posted February 19, 2017 Share Posted February 19, 2017 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 Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted February 19, 2017 Share Posted February 19, 2017 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. Quote Link to comment Share on other sites More sharing options...
jpayam Posted February 20, 2017 Author Share Posted February 20, 2017 any help would be appreciated. is there any mysql comment to correct this? Quote Link to comment Share on other sites More sharing options...
bsmither Posted February 20, 2017 Share Posted February 20, 2017 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. Quote Link to comment Share on other sites More sharing options...
bsmither Posted February 20, 2017 Share Posted February 20, 2017 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. Quote Link to comment Share on other sites More sharing options...
havenswift-hosting Posted February 20, 2017 Share Posted February 20, 2017 @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 Quote Link to comment Share on other sites More sharing options...
bsmither Posted February 20, 2017 Share Posted February 20, 2017 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. Quote Link to comment Share on other sites More sharing options...
jpayam Posted February 21, 2017 Author Share Posted February 21, 2017 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. Quote Link to comment Share on other sites More sharing options...
bsmither Posted February 21, 2017 Share Posted February 21, 2017 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. Quote Link to comment Share on other sites More sharing options...
havenswift-hosting Posted February 21, 2017 Share Posted February 21, 2017 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 Quote Link to comment Share on other sites More sharing options...
Dirty Butter Posted February 21, 2017 Share Posted February 21, 2017 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.