issue Database keys issue

[Nelly111s]

I've noticed a few errors in the Maintenance > Database tab. 

The errors are

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

This is listed in phpMyAdmin as both Primary and Index (Key). Should this just be Primary?

CubeCart_order_summary.cart_order_id has a key type KEY but expecting UNIQUE KEY.

When I try and change the index from key to unique, I get these warnings

 

 The indexes cart_order_id_2 and cart_order_id seem to be equal and one of them could possibly be removed.

 The indexes cart_order_id_2 and cart_order_id_4 seem to be equal and one of them could possibly be removed.

 The indexes cart_order_id_2 and cart_order_id_3 seem to be equal and one of them could possibly be removed.

CubeCart_order_summary.custom_oid has a key type KEY but expecting UNIQUE KEY.

When I try and change this, I get an error "#1062 - Duplicate entry "NULL" for key custom_old". I googled the error, but I'm none the wiser!

 

The three below I fixed in phpMyAdmin

CubeCart_reviews.approved has no key set. Expecting KEY.

CubeCart_seo_urls.type has no key set. Expecting KEY.
CubeCart_seo_urls.item_id has no key set. Expecting KEY.

 

[bsmither]

For CubeCart_admin_users table, you can delete all indexes, except the PRIMARY which is for admin_id column..

For CubeCart_order_summary, you can delete the cart_order_id_2, _3, and _4 indexes. The cart_order_id index needs to be UNIQUE.

For CubeCart_order_summary, the custom_oid index needs to be UNIQUE. However, you may need to first change the details of the custom_oid column from NOT NULL to NULL (possibly clear a checkmark named Not Null for this column). Being able to hold a NULL in this column will then allow you to set a UNIQUE index against this column.

[Nelly111s]

Thanks. I’ll try in the morning. 

Any thoughts on why there are duplicate indices? I’m a little concerned my database isn’t in tip top condition. 

[bsmither]

MySQL will automatically append a numerical suffix onto the name of an index if, when being told to ADD that index, an index by that name already exists.

That said, perhaps you ran CubeCart's upgrade setup three additional times.

[Nelly111s]

11 hours ago, bsmither said:

For CubeCart_admin_users table, you can delete all indexes, except the PRIMARY which is for admin_id column..

For CubeCart_order_summary, you can delete the cart_order_id_2, _3, and _4 indexes. The cart_order_id index needs to be UNIQUE.

For CubeCart_order_summary, the custom_oid index needs to be UNIQUE. However, you may need to first change the details of the custom_oid column from NOT NULL to NULL (possibly clear a checkmark named Not Null for this column). Being able to hold a NULL in this column will then allow you to set a UNIQUE index against this column.

The admin_users table is now fine. Thank you.

I deleted the cart_order_id_x indices and that worked fine. When I try and change the custom_oid index, I get this error

 #1062 - Duplicate entry 'NULL' for key 'custom_oid_2'

The picture below shows that the index is already set at NULL (I think)

 

[bsmither]

The error message references 'custom_oid_2' which is an extra index. The only index by that name would be 'custom_oid' - so delete 'custom_oid_2', _3, _4, etc.

The image above shows the index 'custom_oid' has 'Yes' in the Null column (that's good), but still needs to be 'Yes' in the Unique column.

[Nelly111s]

5 hours ago, bsmither said:

The error message references 'custom_oid_2' which is an extra index. The only index by that name would be 'custom_oid' - so delete 'custom_oid_2', _3, _4, etc.

The image above shows the index 'custom_oid' has 'Yes' in the Null column (that's good), but still needs to be 'Yes' in the Unique column.

I deleted the custom_oid_2 originally (and 3 and 4), but it's not listed in the table at all now, so I don't know how to delete it!

[bsmither]

You deleted cart_order_id_2, _3, and _4 indexes. And this new error message refers to custom_oid_2 index. (It might be at the bottom of the list. Can you scroll the page?)

[Nelly111s]

As far as I can see, there are 51 entires in the CubeCart_order_summary table. There is only the custom_oid index and no entries with a numeric suffix. All names are listed on one page, with no option to scroll to another page.

 

[bsmither]

True, there are 51 data columns. We are interested in the list of indexes (the table with columns named Keyname, Type, Unique, etc.).

[Nelly111s]

These are the only indices listed for that table.

I also tried to delete the custom_oid index (which worked) and then recreate it as Unique (which didn't work)

[bsmither]

Please examine the actual contents of the CubeCart_order_summary table, custom_oid column. If you have never actually used CubeCart's new "Custom Cart Order ID" feature, then this column should have no content in it.

However, phpMyAdmin will show a null content with the representation of null (or something similar).

If the content shown is absolutely blank - nothing at all - then the value being stored in this column is a zero-length-string (-zls-). A -zls- is not the same as a null, and will thwart a UNIQUE key.

Have phpMyAdmin change all instances of -zls- in the 'custom_oid' column to be null. This can be done with:
UPDATE pre_CubeCart_order_summary SET `custom_oid` = DEFAULT WHERE `custom_oid` = '';

Note the pre_ needs to be your table prefix if there is one.

[Nelly111s]

13 hours ago, bsmither said:

Please examine the actual contents of the CubeCart_order_summary table, custom_oid column. If you have never actually used CubeCart's new "Custom Cart Order ID" feature, then this column should have no content in it.

However, phpMyAdmin will show a null content with the representation of null (or something similar).

If the content shown is absolutely blank - nothing at all - then the value being stored in this column is a zero-length-string (-zls-). A -zls- is not the same as a null, and will thwart a UNIQUE key.

Have phpMyAdmin change all instances of -zls- in the 'custom_oid' column to be null. This can be done with:
UPDATE pre_CubeCart_order_summary SET `custom_oid` = DEFAULT WHERE `custom_oid` = '';

Note the pre_ needs to be your table prefix if there is one.

All the early entries (pre-March of this year, probably pre v6.1.14) have nothing (zls) in the column, Post that, they're all "NULL". I ran the query (with my prefix), the whole column had "NULL" in it. I then deleted the custom_oid index and recreated it with a UNIQUE key. Which worked. Hurrah - the database shows no errors/warnings now!

 

Thank you so much for your help in resolving this. You've been very patient and helpful.