MySQL Bugs: #103463: ALTER TABLE produces a duplicate table

Bug #103463	ALTER TABLE produces a duplicate table
Submitted:	24 Apr 2021 19:30	Modified:	29 Apr 2021 1:30
Reporter:	Michael Grauman	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.7.32-log	OS:	Windows (Azure cloud instance)
Assigned to:		CPU Architecture:	Any (Unknown)
Tags:	ALTER TABLE DUPLICATE

Description:
This began happening on any/all schemas on my server. It is repeating every single time. Any ALTER TABLE statement produces a duplicate record in the information_schema (causing a duplicate table name to appear in Workbench)

In fact, every subsequent ALTER TABLE statement produces another duplicate table.

Dropping the table and recreating it does not work. multiple tables appear again immediately when a table of the same name is created. The only workaround I have found is to recreate the Schema from scratch.

Now that I know this is happening, the only thing I can do when a table needs to be altered is to back up the table data, DROP it, then CREATE it in the new format, then restore the data.

How to repeat:

CREATE TABLE `test` (
  `ID` int(11) NOT NULL,
  `testcol1` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `test` ADD COLUMN `testcol2` VARCHAR(45) NULL AFTER `testcol1`;
  this produces a duplicate table

ALTER TABLE `test` ADD COLUMN `testcol3` VARCHAR(45) NULL AFTER `testcol2`;
  this would produce it again ... and so on

Suggested fix:
I wish I had one!

Thank you for the bug report. I couldn't repeat with current release of 5.7:
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE r;
Query OK, 1 row affected (0.00 sec)

mysql> USE r
Database changed
mysql> CREATE TABLE `test` (
    ->   `ID` int(11) NOT NULL,
    ->   `testcol1` varchar(45) DEFAULT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER TABLE `test` ADD COLUMN `testcol2` VARCHAR(45) NULL AFTER `testcol1`;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW TABLES;
+-------------+
| Tables_in_r |
+-------------+
| test        |
+-------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `ID` int(11) NOT NULL,
  `testcol1` varchar(45) DEFAULT NULL,
  `testcol2` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE `test` ADD COLUMN `testcol3` VARCHAR(45) NULL AFTER `testcol2`;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `ID` int(11) NOT NULL,
  `testcol1` varchar(45) DEFAULT NULL,
  `testcol2` varchar(45) DEFAULT NULL,
  `testcol3` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+-------------+
| Tables_in_r |
+-------------+
| test        |
+-------------+
1 row in set (0.00 sec)

mysql>

If I misunderstand what you meant with duplicate table please print here your results. Thanks.

I don't believe you misunderstood, but through more online searches, I have found other people who are experiencing this. It seems to be isolated to Azure MySQL cloud servers. A reboot of the server clears the problem.

I am not sure what causes it to start happening, but if I determine that I will post further comments.

We don't support Azure Cloud server you need to contact their support.