Primary Key error on Digital Ocean database cluster
Hello -- We are hosting our Collective Access database on Digital Ocean's Managed Databases. Everything works great, good speed, etc. The only issue really is the persistent error from their admin., "Our systems have indicated that your MySQL cluster, db-mysql-*-*, has tables without primary keys". I am aware that it is a requirement of their service that all tables in a hosted database have primary keys. Apparently some Collective Access databases do not? Is there a way to remedy this so we don't get these errors, perhap to add primary keys to those tables that do have them? Thanks!
Comments
Which tables is it complaining about?
Hi Seth,
Thank you! See below:
+----------------------------+------------+
| table_name | table_rows |
+--------------------+--------------------+
| __integrity_check_dummy__ | 0 |
| ca_application_vars | 0 |
| ca_change_log_snapshots | 16105 |
| ca_change_log_subjects | 21889 |
| ca_eventlog | 312 |
| ca_media_content_locations | 0 |
| ca_schema_updates | 12 |
| ca_sql_search_ngrams | 0 |
+--------------------+--------------------+
We don't not have primary keys on those tables as they don't absolutely need them. ca_application_vars, for example, is a table with a single record.
We can certainly add keys to these. It adds a bit of (negligible) overhead but won't hurt anything. Back in 2007 when a lot of these tales were first designed there was more concern about managing row width, especially for tables ca_event_log or ca_media_content_locations where potentially there'd be lots of rows.
Hi Seth -- Thank you. Digital Ocean has a tutorial for adding primary keys to existing tables, e.g. "To add a primary key to an existing table, use the ALTER TABLE statement, replacing the placeholder values with your own:
ALTER TABLE your_table_name ADD PRIMARY KEY (column1, column2, ...);"
If I used this technique on the tables listed above in the Collective Access database, would that be OK? Thanks!
https://docs.digitalocean.com/products/databases/mysql/how-to/create-primary-keys/#how-to-manually-add-a-primary-key
It would be ok for now, but you'd risk your system not being compatible with future updates.
Ah, OK. So maybe I should just leave as is and ignore the warnings. Thanks!