Default constraint symbol value is not same as MySQL, lead to replication breaks

[zing22845]

I build a MySQL to Dolt replication, and create a table which has a foreign key with default constraint symbol, then drop the constraint with the default constraint symbol created by MySQL, the replication broken.

MySQL operations:

 root@(slave:8203->1.1.1.1:3306) tdb1> CREATE TABLE Orders (
    ->     OrderID int NOT NULL AUTO_INCREMENT,
    ->     OrderNumber varchar(255) NOT NULL,
    ->     CustomerID int,
    ->     OrderDate date NOT NULL,
    ->     PRIMARY KEY (OrderID),
    ->     FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    -> );
Query OK, 0 rows affected (0.01 sec)

root@(slave:8203->1.1.1.1:3306) tdb1> show create table Orders;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                              |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Orders | CREATE TABLE `orders` (
  `OrderID` int NOT NULL AUTO_INCREMENT,
  `OrderNumber` varchar(255) NOT NULL,
  `CustomerID` int DEFAULT NULL,
  `OrderDate` date NOT NULL,
  PRIMARY KEY (`OrderID`),
  KEY `CustomerID` (`CustomerID`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customers` (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

root@(slave:8203->1.1.1.1:3306) tdb1> alter table orders drop CONSTRAINT `orders_ibfk_1`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Dolt

the default constraint name 80hln0fi is different from the name in MySQL: orders_ibfk_1

tdb1/main> show create table Orders;
+--------+------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                             |
+--------+------------------------------------------------------------------------------------------+
| Orders | CREATE TABLE `Orders` (                                                                  |
|        |   `OrderID` int NOT NULL AUTO_INCREMENT,                                                 |
|        |   `OrderNumber` varchar(255) NOT NULL,                                                   |
|        |   `CustomerID` int,                                                                      |
|        |   `OrderDate` date NOT NULL,                                                             |
|        |   PRIMARY KEY (`OrderID`),                                                               |
|        |   KEY `CustomerID` (`CustomerID`),                                                       |
|        |   CONSTRAINT `80hln0fi` FOREIGN KEY (`CustomerID`) REFERENCES `Customers` (`CustomerID`) |
|        | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin                         |
+--------+------------------------------------------------------------------------------------------+

and the replication is broken with error, when drop CONSTRAINT is executed:
Last_Error: Error executing query: Constraint "orders_ibfk_1" does not exist

[jycor]

Hey, @zing22845 thanks for reporting this issue!
I formatted it so the sql output is a little easier to read.

We will look into getting our default foreign key name generation to align with MySQL's.

In the meantime, you could add a name to foreign key constraints:

tmp/main*> create table parent (i int primary key);
tmp/main*> create table child (j int primary key, constraint `fk1` foreign key (j) references parent(i));
tmp/main*> show create table child;
+-------+------------------------------------------------------------------+
| Table | Create Table                                                     |
+-------+------------------------------------------------------------------+
| child | CREATE TABLE `child` (                                           |
|       |   `j` int NOT NULL,                                              |
|       |   PRIMARY KEY (`j`),                                             |
|       |   CONSTRAINT `fk1` FOREIGN KEY (`j`) REFERENCES `parent` (`i`)   |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-------+------------------------------------------------------------------+
1 row in set (0.00 sec)

tmp/main*> alter table child drop constraint fk1;
tmp/main*> show create table child;
+-------+------------------------------------------------------------------+
| Table | Create Table                                                     |
+-------+------------------------------------------------------------------+
| child | CREATE TABLE `child` (                                           |
|       |   `j` int NOT NULL,                                              |
|       |   PRIMARY KEY (`j`)                                              |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-------+------------------------------------------------------------------+
1 row in set (0.00 sec)

[zing22845]

Hey, @jycor Thank you so much for taking time to review my issue and providing such a detailed response. I really appreciate your prompt attention and format the output for clarity.
Please let me know if there's anything I can help.

[jycor]

Hey @zing22845, thanks again for the bug report!

We initially strayed away from MySQL's naming convention to avoid issues with merging different foreign keys with the same name. To address the merge issues, we have added new syntax (that isn't part of MySQL) to easily rename foreign key constraints.
Consequently, we changed our foreign key name generation to match MySQL's.

These changes are all in dolt main, and we'll have a release for you later this week.