MySQL Bugs: #112659: Mysqldump produces an unexpected result

Bug #112659	Mysqldump produces an unexpected result
Submitted:	9 Oct 2023 1:49	Modified:	10 Oct 2023 14:04
Reporter:	shi ban	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	8.1	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
When I try to dump a database using:

mysqldump -u root test1 > test1.sql

The following error occured.

mysqldump: Got error: 1066: Not unique table/alias: 'x' when using LOCK TABLES

But when I tried to verify the presence of duplicate tables in the database using the following method, the following output showed that there were no duplicate tables：

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> select table_schema,table_name
    -> from information_schema.tables 
    -> where lcase(table_name)='x';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| test1        | x          |
+--------------+------------+
1 row in set (0.01 sec)

mysql> show tables like '%x%';
+-----------------------+
| Tables_in_test1 (%x%) |
+-----------------------+
| x                     |
+-----------------------+
1 row in set (0.02 sec)

How to repeat:
Since mysqldump doesn't work, I will upload the entire mysql installation folder to sftp.oracle.com.

Step 1: Start mysql using the following command in the My Upload folder:

./bin/mysqld --basedir=./ --datadir=./data

Step 2: After starting mysql, dump the test1 database using the following command:

./bin/mysqldump -u root test1 > test1.sql

Hi Mr. ban,

Thank you for your bug report.

Can you provide us only with CREATE TABLE statements for each table from that schema.

We do not need the contents.

Waiting on your feedback.

Since using mysqldump to dump the database doesn't work, is there any way to generate a CREATE TABLE statement? Sorry, I don't know much about it.

Using mysqldump to dump a single table at a time is fine. But with thousands of tables in this schema, exporting one by one seems impractical.

The same error was found to occur when the following operations were performed on some of these tables:

mysql> show create table v915;
ERROR 1066 (42000): Not unique table/alias: 'x'

mysql> select * from test1.v915;
ERROR 1066 (42000): Not unique table/alias: 'x'

Hi,

Using mysqldump is fine, but use it with --no-data option.

Waiting on your feedback.

Using the mysqldump dump can cause errors for some tables：

root@3b41f1e7483e:/home/mysql/bugs# ./bin/mysqldump -u root --no-data --skip-comments test1 v915 >> test1.sql
mysqldump: Got error: 1066: Not unique table/alias: 'x' when doing LOCK TABLES

Hi Mr. ban,

Thank you for your bug report.

However, this is not a bug.

We had no problems recreating tables from your dump.

This is because you have not used proper options with our command line utility.

You have to dump the tables and views in such an order that views and tables that are required for other views and tables are declared and defined in the proper order.

This is especially true for your views.

You should just try to create all the tables and views from the last dump that you have seen and you will understand why do you have all these problems.

This is all explained in our Reference Manual.

Not a bug.

I'm sorry, I'm a little confused.

I think you were able to recreate the tables with my dump because I did not provide a complete dump of the database.

The dump I provide is only the tables that I can dump using mysqldump. There are still some tables that I can't dump using mysqldump.

When I use mysqldump to dump these specific tables, the following happens:

root@3b41f1e7483e:/home/mysql/bugs# ./bin/mysqldump -u root --no-data --skip-comments test1 v915 >> test1.sql
mysqldump: Got error: 1066: Not unique table/alias: 'x' when doing LOCK TABLES

So I think the problem might be in these tables where I didn't provide a CREATE TABLE statement.

Hi,

Actually, no .....

Every mysqldump output comes with the necessary DDL's.

You have a problem of not using the mysqldump options, which would enable you to have tables and especially views dumped in the correct dependency order, as described in our Reference Manual.

Not a bug.

Emm...

So what if I dump these particular tables? In fact, these tables cannot even execute the SHOW CREATE TABLE statement.

Thank you very much for your reply.

I am very sorry that I did not find the relevant solution in the Reference Manual.