Add Primary keys to each table in MySQL schema (required by hosted databases)

[blieb]

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.

[licaon-kter]

Back track a bit, so you've installed ejabberd how? Which SQL schema did you use, old or multihost? Which OS? Which DB?

[blieb]

Back track a bit, so you've installed ejabberd how? Which SQL schema did you use, old or multihost? Which OS? Which DB?

Hi,

I'm Running ejabberd from the docker container. Latest (docker) release (25.08) Running mysql 8.4.7 with sql_require_primary_key which is required if you want to do replication. That's why most hosted mysql instances use that: https://dev.mysql.com/blog-archive/enforce-primary-key-constraints-on-replication/

I tried to import the new multihost sql scheme from GitHub: https://github.com/processone/ejabberd/blob/master/sql/mysql.new.sql