Database Table(s) Without Primary Key #1348

Closed
bhsmither opened this Issue Nov 10, 2016 · 18 comments

Projects

None yet

4 participants

@bhsmither
Contributor

Please review the strategy for having some tables without a Primary Key.

For example, CubeCart_downloads, CubeCart_modules, and others.

(Not that I know the critical benefit of having a Primary Key vs a regular Key.)

@havenswift-hosting

One difference is that a table with a PRIMARY key is editable via phpMyAdmin

@bhsmither
Contributor

Which is why I brought it up - but as such, that is a convenience. The table can still be updated with an appropriate query.

Having any kind of an index is crucial to optimize efficiency - as discussed by Noodleyman, but a Primary vs Regular, I still don't know.

@havenswift-hosting

All tables should have a PRIMARY key and it is even more important when using Innodb tables.

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values. With the InnoDB storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns.

@bhsmither
Contributor

My initial research between a Primary Key, Unique Key, and a regular Key shows that each have their respective operational characteristics. Any kind of a key drastically improves searching and row maintenance.

In the specific case of CubeCart_downloads, having a Key is good, but the question that remains is, what are the consequences of this table having an index which can hold non-unique, possibly including null values?

This loose capability may help in constructing a "multiple download files per digital product" solution. (A project I am currently working on.) However, adding another column works just as well.

@abrookbanks abrookbanks added the bug label Nov 21, 2016
@abrookbanks abrookbanks added this to the 6.1.2 milestone Nov 21, 2016
@abrookbanks abrookbanks self-assigned this Nov 21, 2016
@abrookbanks abrookbanks added a commit that closed this issue Nov 21, 2016
@abrookbanks abrookbanks Fixed #1348 8fe6c3d
@abrookbanks
Member
abrookbanks commented Nov 21, 2016 edited

From my testing all the upgrade SQL commands work so long as the keynames are consistent on all CubeCart stores. For example the CubeCart_category_language table has a keyname id for column translation_id whereas CubeCart_modules has keyname module_id for column module_id.

This was referenced Nov 21, 2016
@Dirty-Butter

I tried running this as a Query within Admin and then in cPanel. Both times I get the error message shown below and nothing changes.

ALTER TABLE `CubeCart_alt_shipping` DROP INDEX `id`, ADD PRIMARY KEY (`id`) USING BTREE; #EOQ
ALTER TABLE `CubeCart_alt_shipping_prices` DROP INDEX `id`, ADD PRIMARY KEY (`id`) USING BTREE; #EOQ
ALTER TABLE `CubeCart_category_language` DROP INDEX `id`, ADD PRIMARY KEY (`translation_id`) USING BTREE; #EOQ
ALTER TABLE `CubeCart_downloads` DROP INDEX `id`, ADD PRIMARY KEY (`digital_id`) USING BTREE;
ALTER TABLE `CubeCart_inventory_language` DROP INDEX `id`, ADD PRIMARY KEY (`translation_id`) USING BTREE; #EOQ
ALTER TABLE `CubeCart_modules` DROP INDEX `module_id`, ADD PRIMARY KEY (`module_id`) USING BTREE; #EOQ

## Error Message

SQL query:

ALTER TABLE CubeCart_alt_shipping DROP INDEX id ,
ADD PRIMARY KEY ( id ) USING BTREE;

MySQL said: Documentation
#1091 - Can't DROP 'id'; check that column/key exists

@abrookbanks
Member

Can't DROP 'id'; check that column/key exists
This is exactly what I was expecting as per comment #1346 (comment)

I don't think every CubeCart store, depending on upgrade history may have the same key with the same key name necessarily.

@abrookbanks abrookbanks reopened this Nov 22, 2016
@abrookbanks
Member

From what I can see the install schema now has good indexes.

I'd suggest a 6.1.2 upgrade script to list all database table keys and match them up against a mapped array. This can then remove any duplicate keys and add any missing ones ignoring any unrecognised ones from modified code.

@abrookbanks abrookbanks modified the milestone: 6.1.2, 6.1.3, 6.1.4 Jan 3, 2017
@abrookbanks abrookbanks modified the milestone: 6.1.4, 5.1.5 Jan 23, 2017
@abrookbanks abrookbanks added a commit that referenced this issue Feb 6, 2017
@abrookbanks abrookbanks #1348 Progress c8b5ea8
@abrookbanks abrookbanks added a commit that referenced this issue Feb 6, 2017
@abrookbanks abrookbanks #1348 progress 46edccf
@abrookbanks
Member

screen shot 2017-02-06 at 16 52 27

@abrookbanks abrookbanks added a commit that referenced this issue Feb 6, 2017
@abrookbanks abrookbanks #1348 implemented a26338f
@abrookbanks abrookbanks added a commit that referenced this issue Feb 6, 2017
@abrookbanks abrookbanks Added prefix support #1348 5f300e1
@abrookbanks abrookbanks closed this Feb 6, 2017
@abrookbanks
Member

screen shot 2017-02-06 at 17 17 58

@abrookbanks abrookbanks added a commit that referenced this issue Feb 7, 2017
@abrookbanks abrookbanks #1348 added missing indexes 69246bc
@abrookbanks abrookbanks added a commit that referenced this issue Feb 7, 2017
@abrookbanks abrookbanks #1348 CSS tweak df1b0fb
@abrookbanks abrookbanks added a commit that referenced this issue Feb 7, 2017
@abrookbanks abrookbanks #1348 more indexes added bef93cf
@abrookbanks abrookbanks added a commit that referenced this issue Feb 7, 2017
@abrookbanks abrookbanks #1348 more indexes added 0fb3f9e
@abrookbanks abrookbanks added a commit that referenced this issue Feb 7, 2017
@abrookbanks abrookbanks #1348 more keys d16dd2c
@Dirty-Butter

I'm still not able to run the db queries to change keys manually - I DO now see the Maintenance screen with the triangles and orange background of the error messages, but no messages are there. All this part of my install is stock.
maintenance_no_error_messages

@Dirty-Butter

I tried running upgrade setup but still get these blank orange bars.

@abrookbanks
Member

The blanks bars are most likely caused by missing phrases from the language/definitions.xml file.

@abrookbanks
Member
abrookbanks commented Feb 10, 2017 edited

This block;

    <string name="missing_index" introduced="6.1.5"><![CDATA[%s has no key set. Expecting %s.]]></string>
    <string name="wrong_index" introduced="6.1.5"><![CDATA[%s has a key type %s but expecting %s.]]></string>
    <string name="table_indexes" introduced="6.1.5"><![CDATA[Indexes]]></string>```
    
@Dirty-Butter

Ugh - as many times as I checked, I missed those definition additions. So, now the last questions on this topic (I hope!) Now that I have the information, I don't have a clue how to use it without taking the chance of ruining my database tables. Will there be any guidance on how to change these tables? What happens if the admin does nothing? I doubt I would be the only one who is reluctant to make manual changes of this type to their database.

@havenswift-hosting

What happens if you do nothing is that your store will respond more slowly both because missing indexes slow down your store but it also puts more load on the MySQL server in general (an unindexed search puts a LOT more load on the server than an indexed one does).

As your sites are hosted with us, if you log a support ticket, I am happy to do this work for you

@abrookbanks
Member

I've only upgraded a few sites today but already found a lot of wrong,missing and even doubled indexes. This tool is pretty handy.

@havenswift-hosting

I completely agree - upgraded and checked quite a few sites already today and all but one had one or more issues

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment