MySQL Bugs: #60574: Temporary tables "like" fail after 5.1.56 update

Bug #60574	Temporary tables "like" fail after 5.1.56 update
Submitted:	21 Mar 2011 17:41	Modified:	22 May 2015 7:16
Reporter:	Jerry Stratton	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.50, 5.1.73	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I upgraded from MySQL 5.1.41 to 5.1.56 last week. Creating temporary tables using "CREATE TEMPORARY TABLE tablename LIKE sourcetable" now fails; "CREATE TABLE tablename LIKE sourcetable" still works, as does "CREATE TEMPORARY TABLE tablename (SELECT * FROM sourcetable WHERE 1=0)".

The error that "CREATE TEMPORARY TABLE tablename LIKE sourcetable" fails with is error 9.

How to repeat:
	CREATE TABLE fred (ID int);

	CREATE TEMPORARY TABLE barney LIKE fred;

	Can't create file '/var/tmp/#sqlac83_45a50f5_a.frm' (errno: 9)

perror reports error 9 as "OS error code   9:  Bad file descriptor"

However, MySQL definitely can create files in /var/tmp/, as this works:

	CREATE TEMPORARY TABLE barney (SELECT * FROM fred WHERE 1=0);

	DESCRIBE barney;
	+-------+---------+------+-----+---------+-------+
	| Field | Type    | Null | Key | Default | Extra |
	+-------+---------+------+-----+---------+-------+
	| ID    | int(11) | YES  |     | NULL    |       | 
	+-------+---------+------+-----+---------+-------+

This creates temporary files in /var/tmp, and the table appears to work fine.

	ls -l /var/tmp/
	-rw-rw----  1 _mysql  wheel     0 Mar 17 09:56 #sqlac83_dbabad_15.MYD
	-rw-rw----  1 _mysql  wheel  1024 Mar 17 09:56 #sqlac83_dbabad_15.MYI
	-rw-rw----  1 _mysql  wheel  8672 Mar 17 09:56 #sqlac83_dbabad_15.frm

Those files weren't there before creating the temporary table, and they disappear if I either "DROP TABLE barney" or just close the session.

However, creating temporary tables in this way does not fully copy the source table--the main issue is that auto increment doesn't get copied over.

The way I'm getting around it right now is to just create the tables as permanent using:

	CREATE TABLE barney LIKE fred;

But that introduces further issues when some other error causes the session to end prematurely--this ends up leaving those tables lying around to mess with later sessions.

I upgraded using mysql-5.1.56-osx10.6-x86_64.dmg on Mac OS X Server 10.6.6. The client I used for testing is the mysql command line.

I can not repeat this with current mysql-5.1 on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot testReading 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 9
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE fred (ID int);
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TEMPORARY TABLE barney LIKE fred;
Query OK, 0 rows affected (0.02 sec)

Please, send the output of SHOW CREATE TABLE fred, like this:

mysql> show create table fred\G
*************************** 1. row ***************************
       Table: fred
Create Table: CREATE TABLE `fred` (
  `ID` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

I want to know what storage was used by default.

simmons:~ henry$ mysql -h simmons -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 73355228
Server version: 5.1.56-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed

mysql> CREATE TABLE fred (ID int);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE fred;
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------+
| fred  | CREATE TABLE `fred` (
  `ID` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | 
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> CREATE TEMPORARY TABLE barney LIKE fred;
ERROR 1004 (HY000): Can't create file '/var/tmp/#sqlac83_45f4fdc_1.frm' (errno: 9)

Installing the Mac OS X 10.6 .dmg versions between 5.1.41 and 5.1.56 on a test server, the error starts occurring in 5.1.50.

Works in 5.1.41
Does not work in 5.1.56
Works in 5.1.48
Does not work in 5.1.52.
Works in 5.1.49
Does not work in 5.1.50.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.50-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE bedrock;
Query OK, 1 row affected (0.01 sec)

mysql> USE bedrock
Database changed
mysql> CREATE TABLE fred (id int);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TEMPORARY TABLE barney LIKE fred;
ERROR 1004 (HY000): Can't create file '/var/tmp/#sql199b_1_2.frm' (errno: 9)
mysql>

Should be something specific to your OS or my.cnf then. I have Mac OS X 10.5.x and 64-bit 5.1.52 from .dmg (as well as all other versions I have there) just works:

macbook-pro:mysql-5.1.52-osx10.5-x86_64 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.52 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE fred (id int);
Query OK, 0 rows affected (0.50 sec)

mysql> CREATE TEMPORARY TABLE barney LIKE fred;
Query OK, 0 rows affected (0.03 sec)

Just an FYI, I was able to "get around" the problem by making a new temporary folder (/usr/local/mysql/var/tmp) and making it owned by the _mysql account.

Not sure why this would help, because /var/tmp was rwxrwxrw:

drwxrwxrwt  3 root  wheel  102 May 24 09:01 /var/tmp

[root@dev ~]# stat /var/lib/mysql/test/fred.frm | grep Uid
Access: (0660/-rw-rw----)  Uid: (  102/   mysql)   Gid: (  104/   mysql)

[root@dev ~]# mysql -D test
...
Server version: 5.1.50-rel11.4 Percona Server (GPL), 11.4, Revision 111
...
mysql> CREATE TEMPORARY TABLE barney LIKE fred;
Query OK, 0 rows affected (0.03 sec)

[root@dev ~]# chgrp user1 /var/lib/mysql/test/fred.frm
[root@dev ~]# mysql -D test
...
Server version: 5.1.50-rel11.4 Percona Server (GPL), 11.4, Revision 111
...
mysql> CREATE TEMPORARY TABLE barney LIKE fred;
ERROR 1004 (HY000): Can't create file '/tmp/#sql1997_6_0.frm' (errno: 9)

Thank you for taking the time to write to us, but this is not a bug.

Note: Reproduced on Linux platform and it should affect at least all Unix-based systems to a similar extent.

This is a result of the .frm files being modified at the system level e.g. ownership/group changes etc without mysqld being restarted to detect changes. This will cause the same error for the database directories and for temporary tables since both try to read the .frm file when 'LIKE' is used.

For the CREATE TEMPORARY TABLE ... LIKE ... statements having issues and the normal data ones not, it would suggest that instead of the .frm files having the change, the /tmp directory was altered in some way. Creating the new directory fixed this and I suspect a restart of mysqld would do the same if the permissions were correct.

Version 5.1.62

# stat test |fgrep -i uid
Access: (0755/drwxr-xr-x)  Uid: (  102/   mysql)   Gid: (  104/   mysql)

# mysql -D test
mysql> create temporary table barney like fred;
Query OK, 0 rows affected (0.00 sec)

# chgrp -R user1 test
# stat test |fgrep -i uid
Access: (0755/drwxr-xr-x)  Uid: (  102/   mysql)   Gid: (  105/ user1)

mysql> create temporary table barney like fred;
ERROR 1004 (HY000): Can't create file '/tmp/#sql426d_2_0.frm' (errno: 9)

Restart of mysqld and change tmpdir does not help. Maybe it's not a bug, but I would like to find a workaround.

No problem in version 5.5.23

# stat test |fgrep -i uid
Access: (0755/drwxr-xr-x)  Uid: (  102/   mysql)   Gid: (  104/   mysql)

# mysql -D test
mysql> create temporary table barney like fred;
Query OK, 0 rows affected (0.00 sec)

# chgrp -R user1 test
# stat test |fgrep -i uid
Access: (0755/drwxr-xr-x)  Uid: (  102/   mysql)   Gid: (  105/ user1)

mysql> create temporary table barney like fred;
Query OK, 0 rows affected (0.00 sec)

I have the same problem.
Platform is Linux Debian, mysql-server - 5.1.63

I try to change /tmp dir, but this not help.

Server version: 5.1.63-0+squeeze1-log (Debian)
 CREATE TEMPORARY TABLE IF NOT EXISTS grp_ LIKE grp;
ERROR 1004 (HY000): Can't create file '/sata/mysql/tmp/#sqla7f_21b_0.frm' (errno: 9)

On other server :
Server version: 5.0.51a-24+lenny4 (Debian)
 CREATE TEMPORARY TABLE IF NOT EXISTS art_ LIKE art;
Query OK, 0 rows affected (0.00 sec)

For those who has the problem - please confirm:
- what filesystem is in use on datadir and mount options
- whether re-setting correct owner and permissions on problem files and directories works around the problem
- whether following plan works around the problem: manually create copy of table files, then deleting original files and renaming copy back to original 
(make sure that resent tested backup exists before trying steps above).

It may be caused by bug #47126

mysql_create_like_table() calls my_copy() with flag MY_DONT_OVERWRITE_FILE

but my_copy() confuses it with MY_SYNC_DIR flag, because both are identically defined:

#define MY_SYNC_DIR     1024    /* my_create/delete/rename: sync directory */
#define MY_DONT_OVERWRITE_FILE 1024	/* my_copy: Don't overwrite file */

when MY_SYNC_DIR is set, my_copy() tries to sync directory - and upon failure may corrupt file descriptor :

  if ((MyFlags & MY_SYNC_DIR) && (fd >=0) &&
      my_sync_dir_by_file(FileName, MyFlags))
  {
    my_close(fd, MyFlags);
    fd= -1;
  }

This seem to be Linux-specific, but if NEED_EXPLICIT_SYNC_DIR is somehow defined in other OS, that for sure may trigger such behavior.

it seems the bug happens in those environments where OS user of mysqld process is not allowed to execute chown command on own files .

If you meet the same problem for temporary tables - make sure that mysql user has permission to execute chown command in folder configured by tmpdir parameter 

If you meet the same problem for regular tables - make sure that mysql user has permission to execute chown command in data directory

For now I will mark this as duplicate of bug #77127 , because latter should be easier for devs to deal with.

To work around the problem make sure that rtschown policy is not set on filesystem where problem occurs.

Some other reasons may lead to similar behavior, so if chown works properly in your environment - then your problem should be covered by bug #63305 . (In any case it will be hard to troubleshoot until both #77127 and #63305 are fixed)