[#MDEV-32071] Bad case letter in *.ibd files with --lower-case-table-names=2

[MDEV-32071] Bad case letter in *.ibd files with --lower-case-table-names=2 Created: 2023-09-01 Updated: 2023-09-04
Status:	Open
Project:	MariaDB Server
Component/s:	Character Sets, Storage Engine - InnoDB
Affects Version/s:	11.3
Fix Version/s:	11.3

Type:

Bug

Priority:

Major

Reporter:

Alexander Barkov

Assignee:

Alexander Barkov

Resolution:

Unresolved

Votes:

Labels:

None

Issue Links:

Relates
relates to	MDEV-31531	Remove my_casedn_str() and my_caseup_...	In Testing

Description

I start a MariaDB Linux server on a case insensitive file system with --lower-case-table-names=2.

Now run these scripts:

CREATE DATABASE db_myisam;

CREATE TABLE db_myisam.`O` (a INT) ENGINE=MyISAM;

CREATE TABLE db_myisam.`Ó` (a INT) ENGINE=MyISAM;

CREATE TABLE db_myisam.`#mysql50#Ó` (a INT) ENGINE=MyISAM;

CREATE DATABASE db_aria;

CREATE TABLE db_aria.`O` (a INT) ENGINE=Aria;

CREATE TABLE db_aria.`Ó` (a INT) ENGINE=Aria;

CREATE TABLE db_aria.`#mysql50#Ó` (a INT) ENGINE=Aria;

CREATE DATABASE db_innodb;

CREATE TABLE db_innodb.`O` (a INT) ENGINE=InnoDB;

CREATE TABLE db_innodb.`Ó` (a INT) ENGINE=InnoDB;

CREATE TABLE db_innodb.`#mysql50#Ó` (a INT) ENGINE=InnoDB;

Then I execute these `ls` commands in the data directory:

ls -l db_myisam/

ls -l db_aria/

ls -l db_innodb/

The output is:

ls -l db_myisam/

-rwxr-xr-x. 1 bar bar  432 Sep  1 15:37 @0Z.frm

-rwxr-xr-x. 1 bar bar    0 Sep  1 15:37 @0Z.MYD

-rwxr-xr-x. 1 bar bar 1024 Sep  1 15:37 @0Z.MYI

-rwxr-xr-x. 1 bar bar   67 Sep  1 15:37 db.opt

-rwxr-xr-x. 1 bar bar  432 Sep  1 15:37 O.frm

-rwxr-xr-x. 1 bar bar  432 Sep  1 15:37 Ó.frm

-rwxr-xr-x. 1 bar bar    0 Sep  1 15:37 O.MYD

-rwxr-xr-x. 1 bar bar    0 Sep  1 15:37 Ó.MYD

-rwxr-xr-x. 1 bar bar 1024 Sep  1 15:37 O.MYI

-rwxr-xr-x. 1 bar bar 1024 Sep  1 15:37 Ó.MYI

ls -l db_aria/

-rwxr-xr-x. 1 bar bar  430 Sep  1 15:37 @0Z.frm

-rwxr-xr-x. 1 bar bar 8192 Sep  1 15:37 @0Z.MAD

-rwxr-xr-x. 1 bar bar 8192 Sep  1 15:37 @0Z.MAI

-rwxr-xr-x. 1 bar bar   67 Sep  1 15:37 db.opt

-rwxr-xr-x. 1 bar bar  430 Sep  1 15:37 O.frm

-rwxr-xr-x. 1 bar bar  430 Sep  1 15:37 Ó.frm

-rwxr-xr-x. 1 bar bar 8192 Sep  1 15:37 O.MAD

-rwxr-xr-x. 1 bar bar 8192 Sep  1 15:37 Ó.MAD

-rwxr-xr-x. 1 bar bar 8192 Sep  1 15:37 O.MAI

-rwxr-xr-x. 1 bar bar 8192 Sep  1 15:37 Ó.MAI

ls -l db_innodb/

-rwxr-xr-x. 1 bar bar   432 Sep  1 15:38 @0Z.frm

-rwxr-xr-x. 1 bar bar 65536 Sep  1 15:38 @0z.ibd

-rwxr-xr-x. 1 bar bar    67 Sep  1 15:38 db.opt

-rwxr-xr-x. 1 bar bar   432 Sep  1 15:38 O.frm

-rwxr-xr-x. 1 bar bar   432 Sep  1 15:38 Ó.frm

-rwxr-xr-x. 1 bar bar 65536 Sep  1 15:38 o.ibd

-rwxr-xr-x. 1 bar bar 65536 Sep  1 15:38 ó.ibd

Notice, MyISAM and Aria tables have equal letter case in *.frm and data files (MAD/MAI,MYD/MYI). For example, O.frm and O.MYD.

InnoDB tables have different letter case in *.frm vs *.ibd files, for example: O.frm vs o.ibd.

It should be possible to move a --lower-case-table-names=2 data directory to a case sensitive file system and run the server with --lower-case-table-names=0 on it.

MyISAM and Aria work correctly. InnoDB does not.

Comments

Comment by Alexander Barkov [ 2023-09-01 ]

The output on Windows (thanks to oleg.smirnov for testing) looks similar:

PS C:\11.1\data11.1_3> ls -l db_myisam

    Directory: C:\11.1\data11.1_3\db_myisam

Mode                 LastWriteTime         Length Name

----                 -------------         ------ ----

-a----        01.09.2023     21:00            432 @0Z.frm

-a----        01.09.2023     21:00              0 @0Z.MYD

-a----        01.09.2023     21:00           1024 @0Z.MYI

-a----        01.09.2023     21:00             65 db.opt

-a----        01.09.2023     21:00            432 O.frm

-a----        01.09.2023     21:00              0 O.MYD

-a----        01.09.2023     21:00           1024 O.MYI

-a----        01.09.2023     21:00            432 Ó.frm

-a----        01.09.2023     21:00              0 Ó.MYD

-a----        01.09.2023     21:00           1024 Ó.MYI

PS C:\11.1\data11.1_3> ls -l db_aria

    Directory: C:\11.1\data11.1_3\db_aria

Mode                 LastWriteTime         Length Name

----                 -------------         ------ ----

-a----        01.09.2023     21:00            430 @0Z.frm

-a----        01.09.2023     21:00           8192 @0Z.MAD

-a----        01.09.2023     21:00           8192 @0Z.MAI

-a----        01.09.2023     21:00             65 db.opt

-a----        01.09.2023     21:00            430 O.frm

-a----        01.09.2023     21:00           8192 O.MAD

-a----        01.09.2023     21:00           8192 O.MAI

-a----        01.09.2023     21:00            430 Ó.frm

-a----        01.09.2023     21:00           8192 Ó.MAD

-a----        01.09.2023     21:00           8192 Ó.MAI

PS C:\11.1\data11.1_3> ls -l db_innodb

    Directory: C:\11.1\data11.1_3\db_innodb

Mode                 LastWriteTime         Length Name

----                 -------------         ------ ----

-a----        01.09.2023     21:00            432 @0Z.frm

-a----        01.09.2023     21:00          65536 @0z.ibd

-a----        01.09.2023     21:00             65 db.opt

-a----        01.09.2023     21:00            432 O.frm

-a----        01.09.2023     21:00          65536 o.ibd

-a----        01.09.2023     21:00            432 Ó.frm

-a----        01.09.2023     21:00          65536 ó.ibd

Oleg tested with:

Windows 10
MariaDB 11.3

Generated at Thu Feb 08 10:28:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-32071] Bad case letter in *.ibd files with --lower-case-table-names=2 Created: 2023-09-01 Updated: 2023-09-04