Bug #92029 Diagnostic area not populated on prepare statement error 1615
Submitted: 15 Aug 2018 20:03 Modified: 10 Oct 2018 22:01
Reporter: Marcelo Altmann (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.7.23, 8.0.12, 5.6.41 OS:Any
Assigned to: CPU Architecture:Any

[15 Aug 2018 20:03] Marcelo Altmann
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.
[16 Aug 2018 6:33] Umesh Shastry
Hello Marcelo,

Thank you for the report.
Observed this with 8.0.12.

Thanks,
Umesh
[10 Oct 2018 22:01] Paul Dubois
Posted by developer:
 
Fixed in 8.0.14.

No ER_NEED_REPREPARE diagnostic was pushed to the diagnostics area
when a reprepare failed for prepared statements.