| Bug #45638 | Create temporary table with engine innodb fails | ||
|---|---|---|---|
| Submitted: | 21 Jun 2009 12:12 | Modified: | 18 Dec 2009 13:13 |
| Reporter: | Nicklas Westerlund (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.35, 5.1.36-bzr | OS: | MacOS (10.5.7) |
| Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
| Tags: | community, regression | ||
[21 Jun 2009 12:42]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described with recent 5.1.36 from bzr on Mac OS X 10.5.6:
valeriy-kravchuks-macbook-pro:5.1 openxs$ uname -a
Darwin valeriy-kravchuks-macbook-pro.local 9.6.0 Darwin Kernel Version 9.6.0: Mon Nov 24 17:37:00 PST 2008; root:xnu-1228.9.59~1/RELEASE_I386 i386
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.36-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TEMPORARY TABLE
-> temptable_fails (a int) ENGINE=InnoDB;
ERROR 1146 (42S02): Table 'test.temptable_fails' doesn't exist
mysql> CREATE TEMPORARY TABLE temptable_myisam (a int) ENGINE=MyISAM;
Query OK, 0 rows affected (0.39 sec)
mysql> exit
Bye
I've got the following in the error log:
valeriy-kravchuks-macbook-pro:5.1 openxs$ tail -50 var/valeriy-kravchuks-macbook-pro.local.err
...
090618 07:16:36 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.1/var
090618 7:16:36 [Warning] Setting lower_case_table_names=2 because file system for /Users/openxs/dbs/5.1/var/ is case insensitive
090618 7:16:36 [Warning] One can only use the --user switch if running as root
090618 7:16:36 [Warning] You have enabled the binary log, but you haven't set server-id to a non-zero value: we force server id to 1; updates will be logged to the binary log, but connections from slaves will not be accepted.
090618 7:16:36 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=valeriy-kravchuks-macbook-pro-bin' to avoid this problem.
090618 7:16:36 [Note] Plugin 'FEDERATED' is disabled.
090618 7:16:36 [Note] Plugin 'ndbcluster' is disabled.
090618 7:16:36 InnoDB: Started; log sequence number 0 9138582
090618 7:16:36 [Note] Event Scheduler: Loaded 0 events
090618 7:16:36 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.36-debug-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
090621 15:35:54 [ERROR] Cannot find or open table -Tmp-/#sql3321_3_2 from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.
090621 15:35:54 InnoDB: Error: table 090621 15:35:54 [ERROR] Invalid (old?) table or database name '-Tmp-'
`#mysql50#-Tmp-`.`#sql3321_3_2` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
090621 15:35:54 [Warning] Could not remove temporary table: '/var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/#sql3321_3_2', error: 2
[22 Jun 2009 16:46]
Mikhail Izioumtchenko
could you try to reproduce it with a system which is not MacOS, maybe by setting the tmpdir value to something similar to what I see in the log: /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp- are all the +++ and -Tmp- feature of MacOS or did you have to set it specifically in my.cnf?
[22 Jun 2009 17:28]
Nicklas Westerlund
I tried setting tmpdir on linux to something I took from my OS X machine right now (created the directory first), and got this: mysql> select @@tmpdir; +----------------------------------------------------+ | @@tmpdir | +----------------------------------------------------+ | /var/folders/6v/6v92QatzEaOaB2LKKiKGA++++TI/-Tmp-/ | +----------------------------------------------------+ 1 row in set (0.01 sec) mysql> use test Database changed mysql> create temporary table ttbl1 (a int) engine=InnoDB; Query OK, 0 rows affected (0.03 sec) So that didn't work. Also verified (on OS X) that the server had access to write to the directory specified (as does my user) So far I've not been able to reproduce this on Linux, but if I manage to do so, I'll let you know.
[25 Jun 2009 16:55]
Heikki Tuuri
The problem might be table name case. -Tmp- does have the upper case letter 'T'. Maybe InnoDB internally stores the table as -tmp-/#sql...? MySQL is using lower_case_table_names=2. You could try to set it to 1. The problem is that InnoDB's table names are internally case sensitive, while the file system of OS X is, by default, case insensitive. Since MyISAM refers to a table with a directory path, MyISAM is immune to these letter case problems.
[25 Jun 2009 17:08]
Nicklas Westerlund
Thanks Heikki, Indeed you are correct that it is related to case, setting lower_case_table_names to 1 does help. However, I doubt that mysql internally stores the table as -tmp-/#sql... as I tried to move the directory to lowercase, but still ran into the same issue. So I guess that somewhere between the OS and InnoDB (via MySQL) the casing of the table is messed up. However, the workaround with lower_case_table_names=1 does work.
[16 Jul 2009 11:24]
Sergey Vojtovich
Verdict 1: not a bug. According to http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_lower_case_tabl... <quot> If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase. </quot> Verdict 2: there is still a bug in get_canonical_filename() - it shouldn't case down tmpdir path, the same way as it doesn't case down datadir part of file name. Other non file based engines are likely affected. E.g. MEMORY leaks one HP_SHARE descriptor per each create. E/R are low - should be one line fix.
[21 Jul 2009 12:08]
Satya B
Oops dont have a mac :(
[2 Sep 2009 7:05]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82180 3093 Sergey Vojtovich 2009-09-02 BUG#45638 - Create temporary table with engine innodb fails Create temporary InnoDB table fails on case insensitive filesystems, when lower_case_table_names is 2 (e.g. OS X) and temporary directory path contains upper case letters. The problem was that tmpdir prefix was converted to lower case when table was created, but was passed as is when table was opened. Fixed by leaving tmpdir prefix part intact. @ sql/handler.cc Fixed get_canonical_filename() to not lowercase filesystem path prefix for temporary tables.
[8 Sep 2009 7:11]
Alexander Barkov
The patch http://lists.mysql.com/commits/82180 looks fine. Please add a test before push.
[9 Sep 2009 10:36]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82796 3093 Sergey Vojtovich 2009-09-09 BUG#45638 - Create temporary table with engine innodb fails Create temporary InnoDB table fails on case insensitive filesystems, when lower_case_table_names is 2 (e.g. OS X) and temporary directory path contains upper case letters. The problem was that tmpdir prefix was converted to lower case when table was created, but was passed as is when table was opened. Fixed by leaving tmpdir prefix part intact. @ mysql-test/r/lowercase_mixed_tmpdir_innodb.result A test case for BUG#45638. @ mysql-test/t/lowercase_mixed_tmpdir_innodb-master.opt A test case for BUG#45638. @ mysql-test/t/lowercase_mixed_tmpdir_innodb-master.sh A test case for BUG#45638. @ mysql-test/t/lowercase_mixed_tmpdir_innodb.test A test case for BUG#45638. @ sql/handler.cc Fixed get_canonical_filename() to not lowercase filesystem path prefix for temporary tables.
[14 Sep 2009 16:06]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[23 Sep 2009 20:32]
Paul DuBois
Noted in 5.4.4 changelog. CREATE TEMPORARY TABLE failed for InnoDB tables on systems with case-insensitive file systems when lower_case_table_names=2 and the pathname of the temporary file directory contained uppercase characters. Setting report to NDI pending push into 5.1.x.
[6 Oct 2009 9:01]
Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20090916070128-6053el2ucp5z7pyn) (merge vers: 5.1.39) (pib:11)
[6 Oct 2009 23:22]
Paul DuBois
Noted in 5.1.40 changelog.
[6 Oct 2009 23:22]
Paul DuBois
The 5.4 fix has also been pushed into 5.4.3.
[18 Dec 2009 10:38]
Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:54]
Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:09]
Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:23]
Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[18 Dec 2009 13:13]
MC Brown
Already documented in 5.1.41
Description: When trying to create a temporary table using InnoDB as engine, it fails with "table doesn't exist". I've verified this on 5.0.75 and 5.1.35, both on OS X. However, it works on Linux (5.1.34) and it also works on Solaris (5.1.30 and 5.1.35), where it lets you create a temporary table as InnoDB. How to repeat: mysql [localhost] {msandbox} (test) > CREATE TABLE verify_that_innodb_works (a int) ENGINE=InnoDB; Query OK, 0 rows affected (0.06 sec) mysql [localhost] {msandbox} (test) > SHOW CREATE TABLE verify_that_innodb_works; +--------------------------+-------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------------------+-------------------------------------------------------------------------------------------------------------+ | verify_that_innodb_works | CREATE TABLE `verify_that_innodb_works` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------------------------+-------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > CREATE TEMPORARY TABLE temptable_fails (a int) ENGINE=InnoDB; ERROR 1146 (42S02): Table 'test.temptable_fails' doesn't exist mysql [localhost] {msandbox} (test) > CREATE TEMPORARY TABLE temptable_csv_works (a int not null) ENGINE=CSV; Query OK, 0 rows affected (0.18 sec) mysql [localhost] {msandbox} (test) > SHOW CREATE TABLE temptable_csv_works; +---------------------+-----------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------------+-----------------------------------------------------------------------------------------------------------+ | temptable_csv_works | CREATE TEMPORARY TABLE `temptable_csv_works` ( `a` int(11) NOT NULL ) ENGINE=CSV DEFAULT CHARSET=latin1 | +---------------------+-----------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)