Add Primary keys to each table in MySQL schema (required by hosted databases) #4485
Description
Before creating a ticket, please consider if this should fit the discussion forum better.
Is your feature request related to a problem? Please describe.
When you use hosted databases by example a primary key is required. This because of replication.
Sadly most of the tables do not have a primary key in the (new) MySQL scheme.
When I now try to import the table it gives the following error:
Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
Describe the solution you'd like
A solution would be to just add an extra field with a primary key in it like in this example:
CREATE TABLE `caps_features` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`node` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`subnode` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`feature` text COLLATE utf8mb4_unicode_ci,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `i_caps_features_node_subnode` (`node`(75),`subnode`(75))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
or make a combined primary key when possible.
I hope this can be added in a future release, since I expect more and more mysql databases will require this.