Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
to reproduce
OS: Unix
lower_case_table_names = 0 # default value for unix
CREATE DATABASE to_be_lowered;
|
USE to_be_lowered;
|
CREATE TABLE T1(c1 INT);
|
Stop service
Change lower_case_table_names in my.cnf to
lower_case_table_names = 1
start service
DROP DATABASE to_be_lowered;
|
SQL Error (1010): Error drop database (can't rmdir './to_be_lowered', errno: 39 "Directory not empty) |
The error message comes from unix rmdir himself.
All *.ibd and db.opt already deleted, but files T1.frm still exists in database directory,
which provoke above error message.
DROP DATABASE should check, if some .frm names are in the datadir with capital_letters,
if lower_case_table_names = 1
2 Solutions are possible.
1. Throw an error before ! deleting *.ibd files
2. Delete also the .frm files with Captial letter.
Attachments
Activity
Hi,
the new table must include uppercase letters.
CREATE TABLE T1(c1 INT);
|
I corrected in the testcase above.
Richard
Thanks, this way it's reproducible.
However, there is a whole section in MySQL manual dedicated to switching from lower_case_table_names=0 to lower_case_table_names=1, and that's not how it's done.
https://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html
Starts from "If you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting".
Unfortunately, I couldn't find a similar section in MariaDB KB, it needs to be added.
I'm inclined to move it to documentation-only, unless you want it to be converted to a feature request. In this case, we'll need a detailed specification of what the new behavior might be, identifier case-sensitivity is quite tricky.
Current behaviour is inconsistent.
Currently tablename.ibd db.opt and will be deleted,
but not TABLENAME.frm.
Drop database is a unequivocal expression of will.
The most complicated part already work, the effort of deletion of the table in the datadictionary,
so I assume the last part of --> delete the tablename.frm
The routine find_db_tables_and_rm_known_files() (in sql_db.cc) is a isolated routine for this single usecase.
The specification iq quite clear.
1.
Delete all Files in the datadir of the database and all table entries in the data dictionary regardsless of case sensitivtiy.
2.
Alternatively throw an error message like in MySQL 8.0
"[Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0')"
but I'm sure, the effort is here higher.
It is clearly a bug for me, but the label is not important, if this usecase will be handled more intuitive and user friendly with litttle effort,
so we can change to feature request , if you like 
I tend to think that DROP DATABASE should delete rogue .frm files that don't correspond to any table.
I can't reproduce it on the current 10.0.
MariaDB [(none)]> select @@lower_case_table_names;+--------------------------+| @@lower_case_table_names |+--------------------------+| 0 |+--------------------------+1 row in set (0.00 sec)MariaDB [(none)]> CREATE DATABASE to_be_lowered;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> USE to_be_lowered;Database changedMariaDB [to_be_lowered]> CREATE TABLE t1(c1 INT);Query OK, 0 rows affected (0.28 sec)MariaDB [to_be_lowered]> shutdown;After restart:
MariaDB [(none)]> select @@lower_case_table_names;+--------------------------+| @@lower_case_table_names |+--------------------------+| 1 |+--------------------------+1 row in set (0.00 sec)MariaDB [(none)]> DROP DATABASE to_be_lowered;Query OK, 1 row affected (0.21 sec)Was there anything else in the scenario that you haven't mentioned?