Duplicate tables being created in Azure MySQL Database

Jazeem Ilyas 31

Hi,

We are facing a strange issue using Azure MySQL databases. recently we noticed that tables with same name are being shown under databases in one of our Azure MySQL database instance. We have no idea how this is possible as we cannot create table with same name in MySQL.

As an example we have a table named as mst_transport_bus. when we execute show tables command 3 such table names are displayed in the list. any change made to one reflects in all the other mst_transport_bus table.

What could be the issue here?

Any assistance will be appreciated.

Thank you

Anurag Sharma 17,571 Reputation points

2021-03-22T04:51:30.137+00:00

Hi @Jazeem Ilyas , welcome to Microsoft Q&A forum.

Could you please try connecting the Azure Database for MySQL with MySQL Workbench and verify the same if tables are duplicated?

Also, is this an existing database or newly created one?
Jazeem Ilyas 31 Reputation points

2021-03-22T05:16:39.82+00:00

Hi @AnuragSharma-MSFT ,

This was an existing database which had no duplicate tables initially. I also tried connecting using MySQL Workbench and it does show duplicate tables. Attached screenshot for your reference.

Thank you
Anurag Sharma 17,571 Reputation points

2021-03-22T06:51:56.65+00:00
Hi @Jazeem Ilyas , thanks for replying back.

This looks a strange behavior as we cannot have multiple objects with same name. That means if we want to create a view with same name as table thats not possible either.

Can you please try running below query and check if there is any difference in any columns values:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'yourdatabasename' AND table_name in ('yourtablename')

Also, please check when the duplicate tables got created by referring to create_time column here.

If these things don't help, I can reach out to internal product team and seek their inputs on the same.
Jose Cotto 6 Reputation points

2021-03-23T16:49:35.333+00:00

We are experiencing the same issue. When we run a simple create table or alter table script in MySQL Workbench, multiple tables are "created". I tried running the suggested script by @AnuragSharma-MSFT , but I am getting only one table.

Also I noticed that although multiple tables are created with the same name, only one of these tables have the actual column definitions. The other repeated tables have no columns. Also, if I delete one of these tables (even if is one with no columns, all tables are dropped as well).

We are also seeing the same duplicated tables when using a Linked Server from SQL Server to MySQL. (see screenshot)
Jazeem Ilyas 31 Reputation points

2021-03-24T02:38:23.147+00:00

Hi Guys,

I almost gave up on this and tried restarting the Azure MySQL instance and the duplicate table names disappeared. There are no duplicates now. clearly there is some issue with Azure MySQL.

@Jose Cotto @AnuragSharma-MSFT

Thank you
Bas Weitjens 11 Reputation points

2021-03-24T13:18:20.953+00:00
We had the same problem and were able to reproduce it:

CREATE TABLE test(id INT); ALTER TABLE Test MODIFY COLUMN id INT;

We use mysql 5.7 with lower_case_table_names = 1
Anurag Sharma 17,571 Reputation points

2021-03-24T16:36:50.83+00:00
Hi @Jose Cotto , thanks for the detailed reply.

I tried using the same scripts (create and alter) but could not repro the issue. Below is the seq of steps followed:

Created azure MySQL Db(version 5.7, lower_case_table_names = 1)

Ran the create and alter script of table

Tried 'Show tables' but it display only 1 table.

Created a linked server in SSMS, was able to see only one table . Please check the screenshot below:

Can you please tell me which region you created Azure MYSQL Database in?
Bas Weitjens 11 Reputation points

2021-03-24T16:43:24.037+00:00

Region = West Europe

After restarting the database server as @Jose Cotto mentioned the duplicate tables have disappeared, and i can no longer reproduce with my previous script :(

Note that when the duplicate tables did exist, MysqlWorkbench did show duplicates, but the database UI in IntelliJ didn't. So to rule out any extra layer (such as SSMS) I checked by querying the database directly with select * from information_schema.TABLES where TABLE_SCHEMA = '<dbName>';
This did show duplicate records.
Anurag Sharma 17,571 Reputation points

2021-03-24T17:10:37.037+00:00

Hi @Bas Weitjens , I tried on West Europe and could not repro it as you already mentioned.

This looked like a temporary glitch which seems resolved already. We really appreciate you going extra miles to write down the steps to repro it.

Please let us know if you see anything like it again.
ray 6 Reputation points

2021-03-30T01:36:35.91+00:00

We also have the same issue. in MySQL Db(version 5.7 in Southeast Asia region
and pricing tier General Purpose

when ALTER Table the duplicate tables being created

5 answers

Bashar Hussein 6 Reputation points

2021-04-06T00:41:15.54+00:00

Hello @Nikita Makarov @Wenjian Feng @Jazeem Ilyas @ray @Bas Weitjens @Jose Cotto My name is Bashar Hussein and I am a program manager in the Azure Database for MySQL, this issue was caused by a bug which was mitigated with a hotfix, users needs to restart Azure Database for MySQL server for the hotfix to take affect.

Users may restart the server using instructions here: https://learn.microsoft.com/en-us/azure/mysql/howto-restart-server-portal

We apologize for any inconvenience occurred due to this bug. Please feel free to reach out to me if you have further concerns or questions about this bug.

Regards
Bashar Hussein
Senior Program Manager | Azure Database for MySQL
bashar.hussein@microsoft.com
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment
Wenjian Feng 41 Reputation points

2021-03-25T04:27:18.54+00:00

We also have the same issue.
But with the different mysql client tools present different situations. Sequel pro can show duplicate table name, but Navicat Premium can't show duplicate table name.
With mysql command line also show duplicate table name.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Nikita Makarov 1 Reputation point

2021-03-31T17:58:39.403+00:00
I have the same issue, with Azure MySQL

SHOW VARIABLES LIKE "%version%";

innodb_version 5.7.32 protocol_version 10 slave_type_conversions tls_version TLSv1,TLSv1.1,TLSv1.2 version 5.7.32-log version_comment MySQL Community Server (GPL) version_compile_machine x86_64 version_compile_os Win64 SELECT TABLE_NAME, TABLE_SCHEMA, count(TABLE_NAME) from information_schema.`TABLES` GROUP BY TABLE_NAME, TABLE_SCHEMA HAVING count(TABLE_NAME) >=2;

Shows more than one tables with the same name within one schema

That makes it unavailable to use the Doctrine library to migrate DB state because it uses SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'
See https://github.com/doctrine/dbal/blob/4.0.x/src/Platforms/MySQLPlatform.php#L274

Please let me know how to fix duplicates in information_schema :)

PS: The issue is not reproduced on MySQL 5.7.32 docker container
Please sign in to rate this answer.
Wenjian Feng 41 Reputation points

2021-04-01T13:00:09.257+00:00

this is a bug, and Azure already fixed it, but for instances which has already occurred, a reboot is required to fix the problem.

Haifeng Liu 1 Reputation point

2021-04-02T02:31:53.53+00:00

We have the same issue after executing Alter table. Is there any official claim regarding this bug? @Wenjian Feng

Anurag Sharma 17,571 Reputation points

2021-04-02T08:59:45.383+00:00

Hi @Nikita Makarov @Haifeng Liu we observed this issue for some days but later on when we tried we were not able to reproduce it. Could be come patch fixed this issue.

As mentioned by @Wenjian Feng , could you please do a reboot and check if you are still seeing duplicate tables or not? If yes, I can go ahead and involve Product team into this matter.

Anurag Sharma 17,571 Reputation points

2021-04-05T08:26:48.113+00:00

Hi @Nikita Makarov @Haifeng Liu , please let us know if you were able to reboot an check?

Nikita Makarov 1 Reputation point

2021-04-05T10:04:01.683+00:00

unfortunately, I've moved all databases to dedicated MySQL VM instance without this lack and unable to check if reboot will fix my issues
Sign in to comment
Kiet Nhi Tran 1 Reputation point Microsoft Employee

2021-07-13T06:17:51.923+00:00

hi @Bashar Hussein ,

One of partner is still facing this issue. Even their server restarted everyday. The hotfix you mentioned above seems to not taking effect. Duplicate tables still happens.
When they query tables info in information_schema, there are 2 rows for same tables.
Is there any hint for this issue? any other thing need to do to resolve the issue?

Feel free reach out to me for further discussion.

Thanks,
Nhi
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Bashar Hussein 6 Reputation points

2021-07-13T18:12:36.117+00:00

Hello @Kiet Nhi Tran please reach out to the team at AskAzureDBforMySQL@abdou .microsoft.com, Please also file a support ticket to log the information about the issue.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment