MySQL Bugs: #18775: Temporary table from alter table visible to other threads

Bug #18775	Temporary table from alter table visible to other threads
Submitted:	4 Apr 2006 15:47	Modified:	18 Jun 2010 1:06
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.7, 5.1.9-bk	OS:	Windows (Windows, Linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
I think such temporary tables should not be visible (and usable) to other threads.

show processlist:
|  17 | root | localhost:1974 | xcc  | Query   | 414358 | copy to tmp table | ALTER TABLE `xwi_serials_valid` ADD PRIMARY KEY (`serial`) |

show tables:
+------------------------------+
| Tables_in_xcc                |
+------------------------------+
| #sql-a94_11                  |

dir:
@0023sql@002da94_11.frm
@0023sql@002da94_11.MYD
@0023sql@002da94_11.MYI

How to repeat:
ALTER TABLE `xwi_serials_valid` ADD PRIMARY KEY (`serial`)

verified with todays 5.1.9 bk pull on linux.
5.0.21-bk didn't show the tables.

[session1]
create table t1(id int) engine=myisam;
insert into t1 values (1),(2),(3);
insert into t1 select * from t1; <------repeat a few times to get many rows
alter table t1 engine=myisam;

[session2]
show tables;

--------------------
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| #sql-7a29_1    |
| t1             |
+----------------+

[sbester@fc4 test]$ ls -l                      
total 146616                                   
     8556 Apr  4 19:24 @0023sql@002d7a29_1.frm 
 32505856 Apr  4 19:25 @0023sql@002d7a29_1.MYD 
     1024 Apr  4 19:24 @0023sql@002d7a29_1.MYI 
     8556 Apr  4 19:22 t1.frm                  
117440512 Apr  4 19:24 t1.MYD                  
     1024 Apr  4 19:24 t1.MYI                  

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.9-beta-debug |
+------------------+
1 row in set (0.00 sec)

My test script

Attachment: bug18775-1.sh (application/x-sh, text), 5.55 KiB.

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/7559

Ingo, I think the patch is ok to push.

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/7744

Pushed into tree currently marked as 5.1.12.

The intermediate (not temporary) files of the new table
  during ALTER TABLE was visible for SHOW TABLES. These
  intermediate files are copies of the original table with
  the changes done by ALTER TABLE. After all the data is
  copied over from the original table, these files are renamed 
  to the original tables file names. So they are not temporary 
  files. They persist after ALTER TABLE, but just with another 
  name.
  
  Normal GRANT checking takes place for the intermediate table.
  Everyone who can see the original table (and hence the final
  table) can also see the intermediate table. But noone else.
  
  In 5.0 the intermediate files are invisible for SHOW TABLES
  because all file names beginning with "#sql" were suppressed.
  In 5.1 temporary files are created in TMPDIR, so that they
  don't appear in the database directories. Also in 5.1 a
  translation between table names and file names is done. The
  tmp_file_prefix on file level is now "@0023sql".
  
  The suppression of files starting with tmp_file_prefix is
  still in place, but still only files beginning with "#sql"
  were suppressed.
  
  I do now translate tmp_file_prefix from table name to file
  name before comparing it with the files in a directory.
  This suppresses the intermediate files again.

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/9370

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/9943

ChangeSet@1.2247, 2006-08-02 15:23:08+02:00, ingo@chilla.local +14 -0
  Bug#18775 - Temporary table from alter table visible to other threads
  Continued implementation of WL#1324 (table name to filename encoding)
  
  The intermediate (not temporary) files of the new table
  during ALTER TABLE was visible for SHOW TABLES. These
  intermediate files are copies of the original table with
  the changes done by ALTER TABLE. After all the data is
  copied over from the original table, these files are renamed 
  to the original tables file names. So they are not temporary 
  files. They persist after ALTER TABLE, but just with another 
  name.
  
  In 5.0 the intermediate files are invisible for SHOW TABLES
  because all file names beginning with "#sql" were suppressed.
  
  This failed since 5.1.6 because even temporary table names were
  converted when making file names from them. The prefix became
  converted to "@0023sql". Converting the prefix during SHOW TABLES
  would suppress the listing of user tables that start with "#sql".
  
  The solution of the problem is to continue the implementation of
  the table name to file name conversion feature. One requirement
  is to suppress the conversion for temporary table names.
  
  This change is straightforward for real temporary tables as there
  is a function that creates temporary file names.
  
  But the generated path names are located in TMPDIR and have no
  relation to the internal table name. This cannot be used for
  ALTER TABLE. Its intermediate files need to be in the same
  directory as the old table files. And it is necessary to be
  able to deduce the same path from the same table name repeatedly.
  
  Consequently the intermediate table files must be handled like normal
  tables. Their internal names shall start with tmp_file_prefix
  (#sql) and they shall not be converted like normal table names.
  
  I added a flags parameter to all relevant functions that are
  called from ALTER TABLE. It is used to suppress the conversion
  for the intermediate table files.
  
  The outcome is that the suppression of #sql in SHOW TABLES
  works again. It does not suppress user tables as these are
  converted to @0023sql on file level.
  
  This patch does also fix ALTER TABLE ... RENAME, which could not 
  rename a table with non-ASCII characters in its name.
  
  It does also fix the problem that a user could create a table like
  `#sql-xxxx-yyyy`, where xxxx is mysqld's pid and yyyy is the thread
  ID of some other thread, which prevented this thread from running 
  ALTER TABLE.
  
  Some of the above problems are mentioned in Bug 1405, which can
  be closed with this patch.
  
  This patch does also contain some minor fixes for other forgotten
  conversions. Still known problems are reported as bugs 21370,
  21373, and 21387.

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/9952

ChangeSet@1.2247, 2006-08-02 17:57:06+02:00, ingo@chilla.local +25 -0
  Bug#18775 - Temporary table from alter table visible to other threads
  Continued implementation of WL#1324 (table name to filename encoding)
  
  The intermediate (not temporary) files of the new table
  during ALTER TABLE was visible for SHOW TABLES. These
  intermediate files are copies of the original table with
  the changes done by ALTER TABLE. After all the data is
  copied over from the original table, these files are renamed 
  to the original tables file names. So they are not temporary 
  files. They persist after ALTER TABLE, but just with another 
  name.
  
  In 5.0 the intermediate files are invisible for SHOW TABLES
  because all file names beginning with "#sql" were suppressed.
  
  This failed since 5.1.6 because even temporary table names were
  converted when making file names from them. The prefix became
  converted to "@0023sql". Converting the prefix during SHOW TABLES
  would suppress the listing of user tables that start with "#sql".
  
  The solution of the problem is to continue the implementation of
  the table name to file name conversion feature. One requirement
  is to suppress the conversion for temporary table names.
  
  This change is straightforward for real temporary tables as there
  is a function that creates temporary file names.
  
  But the generated path names are located in TMPDIR and have no
  relation to the internal table name. This cannot be used for
  ALTER TABLE. Its intermediate files need to be in the same
  directory as the old table files. And it is necessary to be
  able to deduce the same path from the same table name repeatedly.
  
  Consequently the intermediate table files must be handled like normal
  tables. Their internal names shall start with tmp_file_prefix
  (#sql) and they shall not be converted like normal table names.
  
  I added a flags parameter to all relevant functions that are
  called from ALTER TABLE. It is used to suppress the conversion
  for the intermediate table files.
  
  The outcome is that the suppression of #sql in SHOW TABLES
  works again. It does not suppress user tables as these are
  converted to @0023sql on file level.
  
  This patch does also fix ALTER TABLE ... RENAME, which could not 
  rename a table with non-ASCII characters in its name.
  
  It does also fix the problem that a user could create a table like
  `#sql-xxxx-yyyy`, where xxxx is mysqld's pid and yyyy is the thread
  ID of some other thread, which prevented this thread from running 
  ALTER TABLE.
  
  Some of the above problems are mentioned in Bug 1405, which can
  be closed with this patch.
  
  This patch does also contain some minor fixes for other forgotten
  conversions. Still known problems are reported as bugs 21370,
  21373, and 21387.

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/9958

ChangeSet@1.2277, 2006-08-02 18:39:21+02:00, ingo@chilla.local +1 -0
  Bug#18775 - Temporary table from alter table visible to other threads
  Reverting part of the patch. NDB has unencoded names in their
  data dictionary.

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/9986

ChangeSet@1.2278, 2006-08-03 08:12:56+02:00, ingo@chilla.local +4 -0
  Bug#18775 - Temporary table from alter table visible to other threads
  New test cases. Names with umlauts don't compare well on Windows.

Pushed to 5.1.12.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.12 changelog.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)