Bug #19306524 FAILING ASSERTION WITH TEMP TABLE FOR A PROCEDURE CALLED

FROM A FUNCTION

Scenario:

In a stored procedure, CREATE TABLE statement is not allowed.  But an
exception is provided for CREATE TEMPORARY TABLE.  We can create a temporary
table in a stored procedure.

Let there be two stored functions f1 and f2 and two stored procedures p1 and
p2.  Their properties are as follows:

. stored function f1() calls stored procedure p1().
. stored function f2() calls stored procedure p2().
. stored procedure p1() creates temporary table t1.
. stored procedure p2() does DML on t1.

Consider the following situation:

1.  Autocommit mode is on. 
2.  select f1()
3.  select f2()

Step 2:  In this step, t1 would be created via p1().  A table level transaction
lock would have been taken.  The ::external_lock() would not have been called
on this table.  At the end of step 2, because of autocommit mode on, this table
level lock will be released.

Step 3:  When we execute DML on table t1 via p2() we have two problems:

Problem 1:

The function ha_innobase::external_lock() would have been called but since
it is a select query no table level locks would have been taken.  Hence the
following assert will fail:

ut_ad(lock_table_has(thr_get_trx(thr), index->table, LOCK_IX));

Solution:

The solution would be to identify this situation and take a table level lock
and use the proper lock type prebuilt->select_lock_type = LOCK_X for DML
operations.

Problem 2:

Another problem is that in step 3, ha_innobase::open() is never called on
the table t1.  

Solution:

The solution would be to identify this situation and call re-init the handler
of table t1.

rb#6429 approved by Krunal.

2 files changed, 356 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/sp_temp_table.result b/mysql-test/suite/innodb/r/sp_temp_table.result
new file mode 100644
index 00000000000..49a2a4aa831
--- /dev/null
+++ b/mysql-test/suite/innodb/r/sp_temp_table.result
@@ -0,0 +1,253 @@
+#
+# Bug #19306524 FAILING ASSERTION WITH TEMP TABLE FOR A PROCEDURE
+# CALLED FROM A FUNCTION
+#
+call mtr.add_suppression("MySQL is trying to drop table");
+CREATE PROCEDURE cachedata(
+IN obj_id BIGINT UNSIGNED,
+IN start  DATETIME,
+IN end    DATETIME
+)
+cachedata:BEGIN
+DECLARE cache_count BIGINT;
+SET @timestamp := NOW();
+CREATE TEMPORARY TABLE IF NOT EXISTS cachedata (
+timestamp    DATETIME,
+object_id    BIGINT UNSIGNED NOT NULL,
+start        DATETIME,
+end          DATETIME,
+seqno        BIGINT AUTO_INCREMENT,
+value        FLOAT,
+PRIMARY KEY (seqno),
+INDEX (timestamp),
+INDEX (object_id, start, end)
+) ENGINE=INNODB;
+DELETE FROM cachedata WHERE
+timestamp < DATE_SUB(@timestamp, INTERVAL 15 SECOND);
+SELECT count(*) INTO cache_count FROM cachedata WHERE
+object_id = obj_id
+AND start = start
+AND end = end;
+IF cache_count > 0 THEN LEAVE cachedata;
+END IF;
+INSERT INTO cachedata (timestamp, object_id, start, end, value) VALUES
+(@timestamp, obj_id, start, end, 1234),
+(@timestamp, obj_id, start, end, 4567),
+(@timestamp, obj_id, start, end, 8901),
+(@timestamp, obj_id, start, end, 1234),
+(@timestamp, obj_id, start, end, 4567),
+(@timestamp, obj_id, start, end, 8901),
+(@timestamp, obj_id, start, end, 1234),
+(@timestamp, obj_id, start, end, 4567),
+(@timestamp, obj_id, start, end, 8901),
+(@timestamp, obj_id, start, end, 1234),
+(@timestamp, obj_id, start, end, 4567),
+(@timestamp, obj_id, start, end, 8901),
+(@timestamp, obj_id, start, end, 2345),
+(@timestamp, obj_id, start, end, 1234),
+(@timestamp, obj_id, start, end, 4567),
+(@timestamp, obj_id, start, end, 8901),
+(@timestamp, obj_id, start, end, 2345),
+(@timestamp, obj_id, start, end, 1234),
+(@timestamp, obj_id, start, end, 4567),
+(@timestamp, obj_id, start, end, 8901),
+(@timestamp, obj_id, start, end, 2345);
+END$$
+CREATE FUNCTION get_cache(
+obj_id   BIGINT UNSIGNED,
+start    DATETIME,
+end      DATETIME
+)
+RETURNS FLOAT
+READS SQL DATA
+BEGIN
+DECLARE result FLOAT;
+CALL cachedata(obj_id, start, end);
+SELECT SUM(value) INTO result FROM cachedata WHERE
+object_id = obj_id
+AND start = start
+AND end = end;
+RETURN result;
+END$$
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+SELECT get_cache(1, '2014-01-01', '2014-02-01');
+get_cache(1, '2014-01-01', '2014-02-01')
+95247
+select sleep(1);
+sleep(1)
+0
+DROP FUNCTION get_cache;
+DROP PROCEDURE cachedata;
diff --git a/mysql-test/suite/innodb/t/sp_temp_table.test b/mysql-test/suite/innodb/t/sp_temp_table.test
new file mode 100644
index 00000000000..b2883f551b9
--- /dev/null
+++ b/mysql-test/suite/innodb/t/sp_temp_table.test
@@ -0,0 +1,103 @@
+--source include/have_innodb.inc
+--source include/big_test.inc
+
+--echo #
+--echo # Bug #19306524 FAILING ASSERTION WITH TEMP TABLE FOR A PROCEDURE
+--echo # CALLED FROM A FUNCTION
+--echo #
+
+call mtr.add_suppression("MySQL is trying to drop table");
+
+DELIMITER $$;
+CREATE PROCEDURE cachedata(
+  IN obj_id BIGINT UNSIGNED,
+  IN start  DATETIME,
+  IN end    DATETIME
+)
+
+cachedata:BEGIN
+  DECLARE cache_count BIGINT;
+
+  SET @timestamp := NOW();
+
+  CREATE TEMPORARY TABLE IF NOT EXISTS cachedata (
+    timestamp    DATETIME,
+    object_id    BIGINT UNSIGNED NOT NULL,
+    start        DATETIME,
+    end          DATETIME,
+    seqno        BIGINT AUTO_INCREMENT,
+    value        FLOAT,
+    PRIMARY KEY (seqno),
+    INDEX (timestamp),
+    INDEX (object_id, start, end)
+  ) ENGINE=INNODB;
+
+  DELETE FROM cachedata WHERE
+    timestamp < DATE_SUB(@timestamp, INTERVAL 15 SECOND);
+
+  SELECT count(*) INTO cache_count FROM cachedata WHERE
+    object_id = obj_id
+    AND start = start
+    AND end = end;
+
+  IF cache_count > 0 THEN LEAVE cachedata;
+  END IF;
+
+  INSERT INTO cachedata (timestamp, object_id, start, end, value) VALUES
+    (@timestamp, obj_id, start, end, 1234),
+    (@timestamp, obj_id, start, end, 4567),
+    (@timestamp, obj_id, start, end, 8901),
+    (@timestamp, obj_id, start, end, 1234),
+    (@timestamp, obj_id, start, end, 4567),
+    (@timestamp, obj_id, start, end, 8901),
+    (@timestamp, obj_id, start, end, 1234),
+    (@timestamp, obj_id, start, end, 4567),
+    (@timestamp, obj_id, start, end, 8901),
+    (@timestamp, obj_id, start, end, 1234),
+    (@timestamp, obj_id, start, end, 4567),
+    (@timestamp, obj_id, start, end, 8901),
+    (@timestamp, obj_id, start, end, 2345),
+    (@timestamp, obj_id, start, end, 1234),
+    (@timestamp, obj_id, start, end, 4567),
+    (@timestamp, obj_id, start, end, 8901),
+    (@timestamp, obj_id, start, end, 2345),
+    (@timestamp, obj_id, start, end, 1234),
+    (@timestamp, obj_id, start, end, 4567),
+    (@timestamp, obj_id, start, end, 8901),
+    (@timestamp, obj_id, start, end, 2345);
+
+END$$
+
+
+CREATE FUNCTION get_cache(
+  obj_id   BIGINT UNSIGNED,
+  start    DATETIME,
+  end      DATETIME
+)
+  RETURNS FLOAT
+  READS SQL DATA
+BEGIN
+  DECLARE result FLOAT;
+
+  CALL cachedata(obj_id, start, end);
+
+  SELECT SUM(value) INTO result FROM cachedata WHERE
+    object_id = obj_id
+    AND start = start
+    AND end = end;
+
+  RETURN result;
+END$$
+
+DELIMITER ;$$
+
+let $i = 30;
+while ($i)
+{
+ SELECT get_cache(1, '2014-01-01', '2014-02-01');
+ select sleep(1);
+ dec $i;
+}
+
+DROP FUNCTION get_cache;
+DROP PROCEDURE cachedata;