Jump to content

How to Fix Database Index?


Recommended Posts

Okay, so I have manually upgraded to v6.1.5 and I can see the database now has some issues - this was to be expected.

However, I wasn’t expecting the link to a "database checker tool" to be just a link to a discussion elsewhere and the comments by Al Brookbanks aren’t much help... “Please use a tool such as phpMyAdmin to make any required changes” ... erm, yes, so how exactly?

There appear to be two types of error message.

This is what I am looking at:

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_language.translation_id has a key type KEY but expecting PRIMARY.
CubeCart_category.cat_parent_id has no key set. Expecting KEY.
CubeCart_currency.currency_id has a key type KEY but expecting PRIMARY.
CubeCart_image_index.id has a key type KEY but expecting PRIMARY.
CubeCart_inventory_language.translation_id has a key type KEY but expecting PRIMARY.
CubeCart_inventory.popularity has no key set. Expecting KEY.
CubeCart_inventory.status has no key set. Expecting KEY.
CubeCart_modules.module_id has a key type KEY but expecting PRIMARY.
CubeCart_order_summary.cart_order_id has a key type KEY but expecting UNIQUE KEY.
CubeCart_reviews.approved has no key set. Expecting KEY.
CubeCart_seo_urls.item_id has no key set. Expecting KEY.
CubeCart_seo_urls.type has no key set. Expecting KEY.
CubeCart_sessions.customer_id has no key set. Expecting KEY.
CubeCart_sessions.session_last has no key set. Expecting KEY.
CubeCart_transactions.id has a key type KEY but expecting PRIMARY.

 

Link to comment
Share on other sites

1 minute ago, QuotesUK said:

... erm, yes, so how exactly?

It's not something that can be explained very easily. Please login to phpMyAdmin and view the table structure in question. At the bottom is a section listing all column indexes.

These need to be edited. You may find incorrect ones and duplicates. Don't worry if you make a mistake as it can be corrected with no data loss. 

Take a full database backup first. 

Link to comment
Share on other sites

I am using phpMyAdmin and looking at the table - CubeCart_admin_user

There are 23 database fields, starting with admin_id

At the bottom are two infopanels - Space Usage and Row Statistics - nothing about column indexes

What am I looking for? I can see the data, I can see the structure.

Link to comment
Share on other sites

Okay, and for the benefit of anyone else following this, here is a new screengrab.

I have used a red arrow to show you where to find this panel. The other two rows are now removed because I clicked on the word ‘Drop’ (just in case anyone is looking for a delete button). In my CubeCart admin panel this error is now cleared.

Screen Shot 2017-03-08 at 14.21.39.png

Here is the other type of error:

CubeCart_category.cat_parent_id has no key set. Expecting KEY.

And my screengrab... how do we fix this sort, there are no onscreen prompts?

 

Screen Shot 2017-03-08 at 14.31.59.png

Link to comment
Share on other sites

That is simply indicating a missing index on the specified column.  The fact that it says that it is expecting KEY means you mean to add a non-unique index which is called INDEX in phpMyAdmin.  While deleting duplicate indexes is worth doing (each new record added had to write three indexes instead of just the one needed meaning each INSERT and UPDATE was taking three times as long), missing indexes are more important as without them any use of them means the database does a full scan of the whole table (which could be 1 record or 1 million records) rather than only those with a matching value in that column

Ian

Link to comment
Share on other sites

I now have the database warnings down to the following:

CubeCart_category.cat_parent_id has no key set. Expecting KEY.
CubeCart_inventory.popularity has no key set. Expecting KEY.
CubeCart_inventory.status has no key set. Expecting KEY.
CubeCart_reviews.approved has no key set. Expecting KEY.
CubeCart_seo_urls.item_id has no key set. Expecting KEY.
CubeCart_seo_urls.type has no key set. Expecting KEY.
CubeCart_sessions.customer_id has no key set. Expecting KEY.
CubeCart_sessions.session_last has no key set. Expecting KEY.

and

CubeCart_currency.currency_id has a key type KEY but expecting PRIMARY.
CubeCart_modules.module_id has a key type KEY but expecting PRIMARY.

I have taken some screengrabs and wonder whether you can give me a few pointers on what to enter. On the first image I have clicked on the Go button you suggested. The other two look a bit trickier because they are about changing something rather than add/delete. Someone at CubeCart might also want to make a note about the warning for using the table field name of "default".

Screen Shot 2017-03-08 at 17.16.07.png

Screen Shot 2017-03-08 at 17.17.13.png

Screen Shot 2017-03-08 at 17.17.42.png

Link to comment
Share on other sites

Thank you - I have now cleared all the warnings from the maintenance section.

It was time consuming, but I suppose it will now mean the server resources are used more wisely.

I will just keep my fingers crossed that this upgrade issue is a one off, and auto upgrades will start working again.

...now onto my next CC install for more of the same!

Link to comment
Share on other sites

  • 3 months later...
1 minute ago, harrisorganic said:

Thanks Ian,

Now no more errors in the admin maintenance database.

You are welcome - remember to check it every now and again after each upgrade, firstly because (hopefully) this tool will continue to get better and highlight things that it currently doesnt and secondly because there is always a possibility of new errors creeping in

Link to comment
Share on other sites

  • 2 weeks later...
  • 3 years later...

In the hosting control panel, there should be phpMyAdmin. You should be able to figure out how to add an index.

Or, in CubeCart's admin, Maintenance, Query Database (Advanced) tab, enter the following in the box - observing any table prefix.

ALTER TABLE `CubeCart_category` ADD INDEX `cat_parent_id` (`cat_parent_id`);

 

 

Link to comment
Share on other sites

I have no clue what I'm doing. When I drop in the code into the SQL section of Cubecart, it tells me that 1 line was affected, but it makes no changes that I can see to the database table. This thing has literally unassociated every category from it's parent category. This happened under the structure tab in phpMyAdmin

Link to comment
Share on other sites

Wow.

Let me get some thoughts together. I'll be back shortly.

 

Dropping the index `cat_parent_id` would not have deleted the column `cat_parent_id`.

In phpMyAdmin, make sure, in CubeCart_category, there is still the column `cat_parent_id`.

Based on what I see, either the column is gone, or the values of all the rows of that column got set to zero or is blank.

I see "Jackson, Reggie" in the Shop by Category. Is this an actual category? Are all of these players names actually categories?

Link to comment
Share on other sites

That is correct. What happened is, that I was trying to fix an error that was listed here, went to the Structured column under Cubecart_category, and hit DROP. It was a mistake. I didn't catch it before it was gone. And, now, it appears I'm going to have to manually reassign all of these categories. If there's another way, that'd be awesome!

Reggie Jackson should be under the following Category Tree:

All Baseball Cards --> Last Names - J --> Jackson, Reggie

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...