BUG#11747548: DETECT ORPHAN TEMP-POOL FILES, AND HANDLE GRACEFULLY

Analysis:
--------
Certain queries using intrinsic temporary tables may fail due to
name clashes in the file name for the temporary table when the
'temp-pool' enabled.

'temp-pool' tries to reduce the number of different filenames used for
temp tables by allocating them from small pool in order to avoid
problems in the Linux kernel by using a three part filename:
<tmp_file_prefix>_<pid>_<temp_pool_slot_num>.
The bit corresponding to the temp_pool_slot_num is set in the bit
map maintained for the temp-pool when it used for the file name.
It is cleared after the temp table is deleted for re-use.

The 'create_tmp_table()' function call under error condition
tries to clear the same bit twice by calling 'free_tmp_table()'
and 'bitmap_lock_clear_bit()'. 'free_tmp_table()' does a delete
of the table/file and clears the bit by calling the same function
'bitmap_lock_clear_bit()'.

The issue reported can be triggered under the timing window mentioned
below for an error condition while creating the temp table:
a) THD1: Due to an error clears the temp pool slot number used by it
   by calling 'free_tmp_table'.
b) THD2: In the process of creating the temp table by using an unused
   slot number in the bit map.
c) THD1: Clears the slot number used THD2 by calling
  'bitmap_lock_clear_bit()' after completing the call 'free_tmp_table'.
d) THD3: Uses the slot number used the THD2 since it is freed by THD1.
   When it tries to create the temp file using that slot number,
   an error is reported since it is currently in use by THD2.
   [The error: Error 'Can't create/write to file
   '/tmp/#sql_277e_0.MYD' (Errcode: 17)']

Another issue which may occur in 5.6 and trunk is that:
When the open temporary table fails after its creation(due to ulimit
or OOM error), the file is not deleted. Thus further attempts to use
the same slot number in the 'temp-pool' results in failure.

Fix:
---
a) Under the error condition calling the 'bitmap_lock_clear_bit()'
   function to clear the bit is unnecessary since 'free_tmp_table()'
   deletes the table/file and clears the bit. Hence removed the
   redundant call 'bitmap_lock_clear_bit()' in 'create_tmp_table()'
   This prevents the timing window under which the issue reported
   can be seen.

b) If open of the temporary table fails, then the file is deleted
   thus allowing the temp-pool slot number to be utilized for the
   subsequent temporary table creation.

c) Also if the attempt to create temp table fails since it already
   exists, the temp-pool slot for it is marked as used, to avoid
   the problem from re-appearing.

4 files changed, 64 insertions, 3 deletions

diff --git a/mysql-test/r/error_simulation.result b/mysql-test/r/error_simulation.result
index f1835186787..4ea8ead7879 100644
--- a/mysql-test/r/error_simulation.result
+++ b/mysql-test/r/error_simulation.result
@@ -98,3 +98,22 @@ DROP TABLE t1,t2;
 #
 # End of 5.1 tests
 #
+#
+# BUG#11747548:DETECT ORPHAN TEMP-POOL FILES, AND HANDLE GRACEFULLY.
+#
+#Set up.
+CREATE TABLE pid_table(pid_no INT);
+CREATE TABLE t1 (a BLOB);
+INSERT INTO t1 VALUES (1), (2);
+#Create MYD and MYI files for intrinsic temp table.
+LOAD DATA LOCAL INFILE 'pid_file' INTO TABLE pid_table;
+#Reports an error since the temp file already exists.
+SELECT a FROM t1 ORDER BY rand(1);
+ERROR HY000: Can't create or write to file
+#With patch, the query executes successfully.
+SELECT a FROM t1 ORDER BY rand(1);
+a
+1
+2
+#cleanup
+DROP TABLE t1, pid_table;
diff --git a/mysql-test/t/error_simulation-master.opt b/mysql-test/t/error_simulation-master.opt
new file mode 100644
index 00000000000..c9f8d7bd0c0
--- /dev/null
+++ b/mysql-test/t/error_simulation-master.opt
@@ -0,0 +1 @@
+--tmpdir=$MYSQLTEST_VARDIR/tmp
diff --git a/mysql-test/t/error_simulation.test b/mysql-test/t/error_simulation.test
index 95ec2c5b21d..59e3aba9fe1 100644
--- a/mysql-test/t/error_simulation.test
+++ b/mysql-test/t/error_simulation.test
@@ -1,4 +1,5 @@
 -- source include/have_debug.inc
+--source include/not_embedded.inc
 
 #
 # Bug #28499: crash for grouping query when tmp_table_size is too small
@@ -106,3 +107,35 @@ DROP TABLE t1,t2;
 --echo #
 --echo # End of 5.1 tests
 --echo #
+
+
+--echo #
+--echo # BUG#11747548:DETECT ORPHAN TEMP-POOL FILES, AND HANDLE GRACEFULLY.
+--echo #
+
+--echo #Set up.
+CREATE TABLE pid_table(pid_no INT);
+CREATE TABLE t1 (a BLOB);
+INSERT INTO t1 VALUES (1), (2);
+
+--echo #Create MYD and MYI files for intrinsic temp table.
+--let $pid_file=`SELECT @@pid_file`
+--replace_result $pid_file pid_file
+--eval LOAD DATA LOCAL INFILE '$pid_file' INTO TABLE pid_table
+--let $temp_file_MYD= `SELECT CONCAT('#sql_', LCASE(HEX(pid_no)), '_0', '.MYD') FROM pid_table`
+--let $temp_file_MYI= `SELECT CONCAT('#sql_', LCASE(HEX(pid_no)), '_0', '.MYI') FROM pid_table`
+--write_file $MYSQLTEST_VARDIR/tmp/$temp_file_MYD
+EOF
+--write_file $MYSQLTEST_VARDIR/tmp/$temp_file_MYI
+EOF
+
+--echo #Reports an error since the temp file already exists.
+--replace_regex /.*Can't create\/write *.*/Can't create or write to file/
+--error 1
+SELECT a FROM t1 ORDER BY rand(1);
+
+--echo #With patch, the query executes successfully.
+SELECT a FROM t1 ORDER BY rand(1);
+
+--echo #cleanup
+DROP TABLE t1, pid_table;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 860a4b7f5dc..6403fd71f39 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -10963,8 +10963,6 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 err:
   thd->mem_root= mem_root_save;
   free_tmp_table(thd,table);                    /* purecov: inspected */
-  if (temp_pool_slot != MY_BIT_NONE)
-    bitmap_lock_clear_bit(&temp_pool, temp_pool_slot);
   DBUG_RETURN(NULL);				/* purecov: inspected */
 }
 
@@ -11225,6 +11223,16 @@ static bool create_myisam_tmp_table(TABLE *table,TMP_TABLE_PARAM *param,
 		       HA_CREATE_TMP_TABLE)))
   {
     table->file->print_error(error,MYF(0));	/* purecov: inspected */
+    /*
+      Table name which was allocated from temp-pool is already occupied
+      in SE. Probably we hit a bug in server or some problem with system
+      configuration. Prevent problem from re-occurring by marking temp-pool
+      slot for this name as permanently busy, to do this we only need to set
+      TABLE::temp_pool_slot to MY_BIT_NONE in order to avoid freeing it
+      in free_tmp_table().
+    */
+    if (error == EEXIST)
+      table->temp_pool_slot= MY_BIT_NONE;
     table->db_stat=0;
     goto err;
   }