WHMCS tables lack primary keys

[unity100]

While trying to import a WHMCS installation's (8.5.1) mysql dump into a replicated high available Mysql 8 database at Upcloud, the server gave me the warning that the tables lacked primary keys and this could cause issues with mysql replication.

What is the recommended practice in this case? Should I just go ahead and make the "id" columns in the tables into primary keys?

[unity100]

There is a tutorial at the below link that shows how to migrate a WHMCS db to a Galera cluster. Which involves adding primary keys, and they just go ahead and do it.

https://severalnines.com/blog/how-migrate-whmcs-database-mariadb-galera-cluster/

Would that work?

[unity100]

There are two things causing this problem:

1 - Mysqldump and phpmyadmin both export a table in two stages - first they export the statement for creating the table, then they export an alter table statement to set the keys. This is easily fixable with mysqldump client command by using  --single-transaction flag so that mysqldump will export the table definition in one single statement. This makes the import go around that problem.

2 - However, there is a very oddly designed table in WHMCS db:

DROP TABLE IF EXISTS `tblticketpendingimports`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tblticketpendingimports` (
  `ticketmaillog_id` int NOT NULL,
  `ticket_id` int NOT NULL,
  UNIQUE KEY `ticketmaillog_id_ticket_id` (`ticketmaillog_id`,`ticket_id`),
  KEY `idx_ticket_id` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

This table does not have a primary key, instead, it uses a composite unique key. It doesn't even use a composite, non-named primary key made up from those two fields even. This breaks any import to high available Mysql databases because it hampers row-based replication.

I added a totally dummy primary key to this table and that made the import go through.

DROP TABLE IF EXISTS `tblticketpendingimports`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tblticketpendingimports` (
  `ticketmaillog_id` int NOT NULL,
  `ticket_id` int NOT NULL,
  `dummy_primary_key` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`dummy_primary_key`),
  UNIQUE KEY `ticketmaillog_id_ticket_id` (`ticketmaillog_id`,`ticket_id`),
  KEY `idx_ticket_id` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

Moreover, it seems like its working at WHMCS admin and user interfaces as well - they load without problems, and the email ticket interfaces also work normally. 

I think that this should be ok since the code would be constructing the queries by explicitly specifying column names, is this correct?

 

 

 

 

Edited December 19, 2022 by unity100

[unity100]

Support says that WHMCS is not made for HA situations, however they confirmed that this change should not have noticeable impact since WHMCS already constructs the sql queries using field names. So the dummy primary key will mostly be ignored and it will only be used by the underlying replication of Mysql. This sounds workable.