Jump to content
QuotesUK

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.

 

Share this post


Link to post
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. 

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Just found something. Just above the Information infopanel at the bottom is the word Indexes, which when you click on opens up some details...

 

Screen Shot 2017-03-08 at 14.15.12.png

Share this post


Link to post
Share on other sites

That's right.. make appropriate changes there. In this case delete the second and third rows. Even the notices above suggest what to do.

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

In your last screenshot see the bit that says "Create an index on 1 column" !

You will be asked a few things but pretty self explanatory - all missing indexes I have seen in all the sites I have done have always been single column ones and call them the same as the column name

Ian

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

The first set are all missing indexes so as I said before, simply enter the name of the column (it can be anything but column name is standard) and change index type to INDEX.

The others simply need to be edited and change the index type from INDEX to PRIMARY which will also auto change the name

Share this post


Link to post
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!

Share this post


Link to post
Share on other sites

Hi there , I have been working through the above with some success. But the last line below has me stumped.

5954d13b2719c_uniquekey.thumb.PNG.0da6fa3a8b5c42d834828c00075a6621.PNG

I go to phpmyadmin

Go to database

Click on order_summary

Click on structure

cart-orderid.thumb.PNG.75067c80b461a7327977a30bf1305e9c.PNG

Now what to do please?

Share this post


Link to post
Share on other sites

Scroll down until you see + Indexes, click on that which shows you all current indexes and allows you to Create, Amend or Delete

Ian

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
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...