[MDEV-14432] mysqldump does not preserve case of table names in generated sql Created: 2017-11-17  Updated: 2023-07-19

Status: Confirmed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.0.26, 10.0, 10.1, 10.2.10, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: David Crimmins Assignee: Unassigned
Resolution: Unresolved Votes: 3
Labels: Papercut, beginner-friendly
Environment:

Windows 10 64bit MyISAM engine.

Issue Links:
Relates
relates to MDEV-31472 Wrong results in queries to I_S for t... Confirmed

 Description   

The table name in the sql file generated by mysqldump is in lowercase. This only happens if the command arguments include a table name. If run without specifying a table the case of the table names is preserved.

From looking at logs, it appears as if earlier versions used "show tables like ..." and the later versions use "select from information_schema.tables where table_name = ....".

Interestingly there is a different result between like and =

MariaDB [daves]> select table_name from information_schema.tables where table_schema = database() and table_name like 'BouncyCaps';
 
+------------+
 
| table_name |
 
+------------+
 
| BouncyCaps |
 
+------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [daves]>
 
MariaDB [daves]> select table_name from information_schema.tables where table_schema = database() and table_name = 'BouncyCaps';
 
+------------+
 
| table_name |
 
+------------+
 
| bouncycaps |
 
+------------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Marko Mäkelä [ 2017-11-20 ]

I think that this might be broken by InnoDB design. On Windows, InnoDB always converts table names to lower case.
For .frm files and for mysqldump, the lower_case_table_names option should matter more.
wlad is our main Windows developer. Maybe we should review and revise the logic?

Comment by Elena Stepanova [ 2017-11-20 ]

David Crimmins,

It looks like you are using lower_case_table_names=2, is it so? (The default value on Windows is 1, so you must have changed it).

Indeed, with lower_case_table_names=2, the difference between select ... = and select ... like is reproducible on Windows. Possibly it has something to do with different execution plans:

MariaDB [test]> explain select table_name from information_schema.tables where table_schema = database() and table_name = 'BouncyCaps';
 
+------+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
 
| id   | select_type | table  | type | possible_keys | key                     | key_len | ref  | rows | Extra                                             |
 
+------+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
 
|    1 | SIMPLE      | tables | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL | Using where; Skip_open_table; Scanned 0 databases |
 
+------+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select table_name from information_schema.tables where table_schema = database() and table_name = 'BouncyCaps';
 
+------------+
 
| table_name |
 
+------------+
 
| bouncycaps |
 
+------------+
 
1 row in set (0.01 sec)


MariaDB [test]> explain select table_name from information_schema.tables where table_schema = database() and table_name like 'BouncyCaps';
 
+------+-------------+--------+------+---------------+--------------+---------+------+------+--------------------------------------------------+
 
| id   | select_type | table  | type | possible_keys | key          | key_len | ref  | rows | Extra                                            |
 
+------+-------------+--------+------+---------------+--------------+---------+------+------+--------------------------------------------------+
 
|    1 | SIMPLE      | tables | ALL  | NULL          | TABLE_SCHEMA | NULL    | NULL | NULL | Using where; Skip_open_table; Scanned 1 database |
 
+------+-------------+--------+------+---------------+--------------+---------+------+------+--------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select table_name from information_schema.tables where table_schema = database() and table_name like 'BouncyCaps';
 
+------------+
 
| table_name |
 
+------------+
 
| BouncyCaps |
 
+------------+
 
1 row in set (0.03 sec)

alice,
Could you please check how it works on Mac, where lower_case_table_names=2 is the default value?

Comment by David Crimmins [ 2017-11-20 ]

We are using lower_case_table_names=2

Comment by Alice Sherepa [ 2017-11-21 ]

on OS X 10.13 also different result of "like" and "="

MariaDB [test]> select table_name from information_schema.tables where table_schema = database() and table_name like 'BouncyCaps';
 
+------------+
 
| table_name |
 
+------------+
 
| BouncyCaps |
 
+------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select table_name from information_schema.tables where table_schema = database() and table_name = 'BouncyCaps';
 
+------------+
 
| table_name |
 
+------------+
 
| bouncycaps |
 
+------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> show variables like '%lower_case_table_names%' ;
 
+------------------------+-------+
 
| Variable_name          | Value |
 
+------------------------+-------+
 
| lower_case_table_names | 2     |
 
+------------------------+-------+
 
1 row in set (0.00 sec)

Comment by Sergei Kulakov [ 2020-05-05 ]

I confirm the bug for Windows 8.1. This wasn't the case with MySql's mysqldump.
And, by the way, this has nothing to do with InnoDB - I don't use it.

Comment by Sergei Kulakov [ 2020-05-08 ]

I got
mysqldump.exe Ver 10.17 Distrib 10.4.12-MariaDB, for Win64 (AMD64)
And what do you mean by "Fix Version" / "Affects Version" ?

Comment by Alice Sherepa [ 2020-05-08 ]

Versions of MariaDB where the bug is repeatable and where it should be fixed

Comment by Sergei Kulakov [ 2020-05-08 ]

Ok, thank you, I thought they're all affected. Can't see those comments any more for a reason.

Generated at Thu Feb 08 08:13:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.