Description:
If a prepared statement is open and it's table gets evicted from table cache/table definition cache it needs to be re-preprepared.
When this happens, this particular error doesn't populate the diagnostic area:
mysql [localhost] {msandbox} (db1) > PREPARE SQLCOMMAND FROM 'INSERT INTO tb1 VALUES (1)';
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql [localhost] {msandbox} (db1) > EXECUTE SQLCOMMAND;
ERROR 1615 (HY000): Prepared statement needs to be re-prepared
mysql [localhost] {msandbox} (db1) > get diagnostics condition 1 @varErrorMessage = message_text, @varErrorNo = mysql_errno;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql [localhost] {msandbox} (db1) > SELECT @varErrorMessage, @varErrorNo;
+------------------+-------------+
| @varErrorMessage | @varErrorNo |
+------------------+-------------+
| NULL | NULL |
+------------------+-------------+
1 row in set (0.00 sec)
How to repeat:
-- Session1:
On MySQL:
#lower table definition / open cache so the problem
SET GLOBAL table_definition_cache=400;
SET GLOBAL table_open_cache=5;
DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
USE db1;
CREATE TABLE tb1 (ID INT);
PREPARE SQLCOMMAND FROM 'INSERT INTO tb1 VALUES (1)';
EXECUTE SQLCOMMAND;
# Don't close this session
-- Session2:
Bash:
# Create some tables
MYSQL="mysql -u root -psekret"
(
echo "DROP DATABASE IF EXISTS test_tables; CREATE DATABASE test_tables;"
for i in {1..4000}; do echo "CREATE TABLE test_tables.t$i (ID INT);"; done
) | $MYSQL > /dev/null
# Run mysqldump so it requires more table definition / open cache than it's available
mysqldump --defaults-file=my.sandbox.cnf --all-databases > /dev/null
Session 1:
MySQL:
# While mysqldump is running execute PS twice:
EXECUTE SQLCOMMAND;
EXECUTE SQLCOMMAND;
# Second command should fail and attempting to query diagnostic area will return null values
get diagnostics condition 1 @varErrorMessage = message_text, @varErrorNo = mysql_errno;
SELECT @varErrorMessage, @varErrorNo;
Suggested fix:
Populate the diagnostic area.
Description: If a prepared statement is open and it's table gets evicted from table cache/table definition cache it needs to be re-preprepared. When this happens, this particular error doesn't populate the diagnostic area: mysql [localhost] {msandbox} (db1) > PREPARE SQLCOMMAND FROM 'INSERT INTO tb1 VALUES (1)'; Query OK, 0 rows affected (0.01 sec) Statement prepared mysql [localhost] {msandbox} (db1) > EXECUTE SQLCOMMAND; ERROR 1615 (HY000): Prepared statement needs to be re-prepared mysql [localhost] {msandbox} (db1) > get diagnostics condition 1 @varErrorMessage = message_text, @varErrorNo = mysql_errno; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT @varErrorMessage, @varErrorNo; +------------------+-------------+ | @varErrorMessage | @varErrorNo | +------------------+-------------+ | NULL | NULL | +------------------+-------------+ 1 row in set (0.00 sec) How to repeat: -- Session1: On MySQL: #lower table definition / open cache so the problem SET GLOBAL table_definition_cache=400; SET GLOBAL table_open_cache=5; DROP DATABASE IF EXISTS db1; CREATE DATABASE db1; USE db1; CREATE TABLE tb1 (ID INT); PREPARE SQLCOMMAND FROM 'INSERT INTO tb1 VALUES (1)'; EXECUTE SQLCOMMAND; # Don't close this session -- Session2: Bash: # Create some tables MYSQL="mysql -u root -psekret" ( echo "DROP DATABASE IF EXISTS test_tables; CREATE DATABASE test_tables;" for i in {1..4000}; do echo "CREATE TABLE test_tables.t$i (ID INT);"; done ) | $MYSQL > /dev/null # Run mysqldump so it requires more table definition / open cache than it's available mysqldump --defaults-file=my.sandbox.cnf --all-databases > /dev/null Session 1: MySQL: # While mysqldump is running execute PS twice: EXECUTE SQLCOMMAND; EXECUTE SQLCOMMAND; # Second command should fail and attempting to query diagnostic area will return null values get diagnostics condition 1 @varErrorMessage = message_text, @varErrorNo = mysql_errno; SELECT @varErrorMessage, @varErrorNo; Suggested fix: Populate the diagnostic area.