Jump to content
Sign in to follow this  
Nelly111s

Database keys issue

Recommended Posts

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

 

dot.gif The indexes cart_order_id_2 and cart_order_id seem to be equal and one of them could possibly be removed.
dot.gif The indexes cart_order_id_2 and cart_order_id_4 seem to be equal and one of them could possibly be removed.
dot.gif 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.

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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.

Share this post


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

dot.gif #1062 - Duplicate entry 'NULL' for key 'custom_oid_2'

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

 

Screenshot 2019-09-12 at 10.05.18.jpg

Share this post


Link to post
Share on other sites

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.

Edited by bsmither

Share this post


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

Share this post


Link to post
Share on other sites

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?)

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

These are the only indices listed for that table.indices.thumb.jpg.25f47c3d7b88dea299a47718aae08f21.jpg

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

Share this post


Link to post
Share on other sites

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.

Share this post


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

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.

Sign in to follow this  

×
×
  • Create New...