MySQL Bugs: #81629: ERROR 1137 occurs WHEN Execution TRIGGER

Bug #81629	ERROR 1137 occurs WHEN Execution TRIGGER
Submitted:	27 May 2016 20:22	Modified:	2 Aug 2016 15:28
Reporter:	Sergei Kaufman	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	Ver 14.14 Distrib 5.7.12,for Linux (i686	OS:	Any (14.04 LTS)
Assigned to:		CPU Architecture:	Any

Description:
The problem occured in processing a trigger that is starting work after INSTRUCTION UPDATE  with several conditions and variables that is set before.

Then i got that error message 
ERROR 1137 (HY000): Can't reopen table: 'SysStructureTemp' 

While i checked and tried to solve what is the problemm i found also , that that trigger have no any INSTRUCTIONS for making any possible actions in the "SysStructureTemp"  except that in the end of the trigger execution i have an INSTRUCTION that is actually set to "DROP TEMPORARY TABLE SysStructureTemp".

so i tried to found where was the bug in the relevant code blocks of current execution and i found that the error occurs exactly in this block of execution and AFTER UPDATE TRIGGER   - so maybe on this time actually the bug was not in my execution?

How to repeat:
I have read Mysql documentation that ERROR 1137 occurs when you trying to reopen temporary table on the same query execution.  

In this time the error message was wrong because in execution block i not found any instructions to make possible actions with the table - 'SysStructureTemp' except of "DROP" instruction at the end of the execution block.

Suggested fix:
I suggest but i did not found any solution except that it was wrong error message.

For my needs, I have a complete execution log file that is weight around 500kb that i can attach to this topic to solve the bug.

ERROR 1137 (HY000): Can't reopen table: 'SysStructureTemp' 

While i checked and tried to solve what is the problemm i found also , that that trigger have no any INSTRUCTIONS for making any possible actions in the "SysStructureTemp"  except that in the end of the trigger execution i have an INSTRUCTION that is actually set to "DROP TEMPORARY TABLE SysStructureTemp".

so i tried to found where was the bug in the relevant code blocks of current execution and i found that the error occurs exactly in this block of execution and AFTER UPDATE TRIGGER   - so maybe on this time actually the bug was not in my execution?

Please provide a repeatable test case. Thanks.

The test case was in execution the instruction of  "DROP TEMPORARY TABLE" - "SysStructureTemp" at the end of the execution block that is starts working in execution of "AFTER UPDATE TRIGGER". 

Each execution of AFTER UPDATER TRIGGER ends with error message - Can't reopen table: 'SysStructureTemp'.

I had repeated the same execution few times but without TRUE case.

The temporary solution that i had found, was to comment lines with DROP TEMPORARY TABLE "SysStructureTemp" instruction. On this execution the test case ends with TRUE case but the previous case not solved yet.

Test Case Log

Attachment: TestCaseLog.txt (text/plain), 27.76 KiB.

Test Case 1

Attachment: TestCase1.sql (application/sql, text), 10.29 KiB.

Test Case 2

Attachment: TestCase2.sql (application/sql, text), 10.15 KiB.

Setup environment and variables between execution of each tests

Attachment: SetupTestEnv.sql (application/sql, text), 126 bytes.

Test Log file, and Test cases files attached now

Just couple of questions ....

Which of the two test cases can be executed by simply doing:

mysql database-name < TestCase.sql

First or second or none of those ???

Second question. Will the real test case, that are the three last lines in the second test case, always lead to the error.

Third and last, can you also send us your configuration file ???

yes., yes., no

Hi,

I have  fully tested your TestCase2.sql several times with 5.7.13 release. I did not change your test in any way, except for adding two lines.

Not a single error. Not a single warning. I have  inserted "SHOW WARNINGS", just in case,
but I did not get a single warning.

It could be your configuration or problems with number of files ...

so do you call your self tester now>?

No, I do not call myself anything.

Testing bugs was something I started doing ever since joining MySQL, back in 1998.

Now I understand who I am.