How to Fix Database Index?

[QuotesUK]

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.

 

[Al Brookbanks]

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. 

[QuotesUK]

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.

[Dirty Butter]

Maybe someone could make a short movie showing the creating of a Primary key and also of changing a key to Primary?

[QuotesUK]

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

 

[Al Brookbanks]

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

[QuotesUK]

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.

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?

 

[havenswift-hosting]

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

[QuotesUK]

Thank you. The explanation helps. But where do I click? Are there any options I will be asked about?

[havenswift-hosting]

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

[QuotesUK]

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".

[havenswift-hosting]

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

[QuotesUK]

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!

[harrisorganic]

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

I go to phpmyadmin

Go to database

Click on order_summary

Click on structure

Now what to do please?

[havenswift-hosting]

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

Ian

[harrisorganic]

Thanks Ian,

Now no more errors in the admin maintenance database.

 

[havenswift-hosting]

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

[James S]

Thanks Guys - good guide to sorting my DB out as well, would have been lost without it.

[traylor23]

I accidentally dropped the index CubeCart_category.cat_parent_id

Of course I didn't back the db up before. Please help :-<

[bsmither]

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`);

 

 

[traylor23]

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

[bsmither]

Do you have a website we can look at?

 

 

[traylor23]

Just now, bsmither said:

Do you have a website we can look at?

 

 

Ballcardz.com

[bsmither]

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?

[traylor23]

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