MySQL Bugs: #46333: Can't update table 'tstamp' in stored function/trigger because it is already use

Bug #46333	Can't update table 'tstamp' in stored function/trigger because it is already use
Submitted:	22 Jul 2009 5:30	Modified:	27 Jul 2009 5:43
Reporter:	Vishnu Purushothaman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.83, 5.0 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello 
I get this error Can't update table 'tstamp' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
	I know that this error usually happens when we have a trigger on a table that updates itself. But 'tstamp' doesn't have any trigger. 
	My execution plan is pretty complex.
	1: Vb6 App->calls stored procedure - "edit_booking(val1,valn)"
	2: SP edit_booking(), updates the table "Booking"
	3: "Booking" table has a trigger that calls stored procedure "AccountsBookingUpdate()" (Sp that handles financial Accounting logic on a booking )
	4: SP AccountsBookingUpdate, in turn calls Stored procedure "TranscationSave" (SP that handles all financial accounting inserts)
	5: SP "TranscationSave" in turn calls Stored procedure "tstamp"(as per the software's requirements, we save very DMLs in a table)
	6: SP "tstamp" inserts a row in the table tstamp (SP and table have the same name).
	   Its on inserting the data that the error is generated.
	   
	This is the sqls i execute
	SET AUTOCOMMIT=0;
	LOCK TABLES booking WRITE, consignment WRITE,receive_booking WRITE, receive_consignment WRITE, outward_booking WRITE,outward_consignment WRITE, inward_booking WRITE, inward_consignment WRITE, chargeequation WRITE, branch WRITE, state WRITE, district WRITE, company WRITE, branch_reference WRITE, Customer WRITE, global_config WRITE, contenttype WRITE, packingtype WRITE, rebook WRITE, tstamp WRITE, tripdetails write, receive write;
	call edit_booking(179,'T1AA11100167','2009-06-26','10:05:22',1590000006,2540006137,0,19,19,160,'TO PAY','2/3','DOOR DELIVERY',120,10,2.575,'','2009-06-27',6909,6640,5,30,12,332,1,'',0,0,0,1,6640,0,0,0);
	call new_consignment(0,1110000022,1110000002,'2','3','2009-06-26',3,332,'',3,2,6640,19,1,20);
	COMMIT;
	UNLOCK Tables;
	
	There are no errors if
	
	1: I execute the sql the first time(after a server restart)
	2: If i add   FLUSH TABLE tstamp;   as the first line. so it will be 
	
	   	FLUSH TABLE tstamp;
		SET AUTOCOMMIT=0;
		LOCK TABLES booking WRITE, consignment WRITE,receive_booking WRITE, receive_consignment WRITE, outward_booking WRITE,outward_consignment WRITE, inward_booking WRITE, inward_consignment WRITE, chargeequation WRITE, branch WRITE, state WRITE, district WRITE, company WRITE, branch_reference WRITE, Customer WRITE, global_config WRITE, contenttype WRITE, packingtype WRITE, rebook WRITE, tstamp WRITE, tripdetails write, receive write;
		call edit_booking(179,'T1AA11100167','2009-06-26','10:05:22',1590000006,2540006137,0,19,19,160,'TO PAY','2/3','DOOR DELIVERY',120,10,2.575,'','2009-06-27',6909,6640,5,30,12,332,1,'',0,0,0,1,6640,0,0,0);
		call new_consignment(0,1110000022,1110000002,'2','3','2009-06-26',3,332,'',3,2,6640,19,1,20);
		COMMIT;
		UNLOCK Tables;
		
		I am wondering if this is any bug in mysql. If this is a limitation, then it shouldn't execute with or without FLUSH Table statement and on first execution(after restart). I tried these SQL statements from SQLyog(a sql client), but get the SAME error. 

How to repeat:
I don't find any other situations that produces the same error.

I think we need SHOW CREATE TABLE results for all the tables used and code of the trigger and all SPs mentioned to check if this is really a bug or not.

Hi  Valeriy Kravchuk,
   Do you want the db dump?.If so i can upload it
Regards,
Vishnu

Thank you for the feedback.

Please provide output of SHOW CREATE TABLE global_config also.

test case for the testsuite

Attachment: bug46333.test.zip (application/zip, text), 124.60 KiB.

Unfortunately, I am not able to repeat this. Neither on Linux nor on Windows.

Hi Susanne Ebrecht,
     Thanks for the feed back. My customer's machine is "Intel Core2Duo " + 3GB RAM and mysql is configured to use 512 MB RAM. He doesn't get this error as often as I do. Probably, because i run the tests on a VMWare(virtual machine software) with P4 128 MB RAM configuration. One of the possible reason why this error happens may be due to low memory. If you could use the data(that i uploaded, Sveta Smirnova has removed those in the test case), you will surely hit it(i hope so :)). I Initially thought it might due to the mysql version(i was using 5.0.45) so switched to 5.0.83 this week, but was of no use. 
Regards,
Vishnu.

Thank you for the feedback.

Strange. I got this error once, but can not repeat anymore.

Good News finally!!!!
Sveta Smirnova, are you executing the two  test case together?, 

Case 1 : Error Will Occur
----------------------------------------------------------------------
SET AUTOCOMMIT=0;
	LOCK TABLES booking WRITE, consignment WRITE,receive_booking WRITE, receive_consignment
WRITE, outward_booking WRITE,outward_consignment WRITE, inward_booking WRITE,
inward_consignment WRITE, chargeequation WRITE, branch WRITE, state WRITE, district WRITE,
company WRITE, branch_reference WRITE, Customer WRITE, global_config WRITE, contenttype
WRITE, packingtype WRITE, rebook WRITE, tstamp WRITE, tripdetails write, receive write;
	call
edit_booking(179,'T1AA11100167','2009-06-26','10:05:22',1590000006,2540006137,0,19,19,160,
'TO PAY','2/3','DOOR
DELIVERY',120,10,2.575,'','2009-06-27',6909,6640,5,30,12,332,1,'',0,0,0,1,6640,0,0,0);
	call
new_consignment(0,1110000022,1110000002,'2','3','2009-06-26',3,332,'',3,2,6640,19,1,20);
	COMMIT;
	UNLOCK Tables;
------------------------------------------------------------------------

Case 2 : Error won't occur
------------------------------------------------------------------------
FLUSH TABLE tstamp;
		SET AUTOCOMMIT=0;
		LOCK TABLES booking WRITE, consignment WRITE,receive_booking WRITE, receive_consignment
WRITE, outward_booking WRITE,outward_consignment WRITE, inward_booking WRITE,
inward_consignment WRITE, chargeequation WRITE, branch WRITE, state WRITE, district WRITE,
company WRITE, branch_reference WRITE, Customer WRITE, global_config WRITE, contenttype
WRITE, packingtype WRITE, rebook WRITE, tstamp WRITE, tripdetails write, receive write;
		call
edit_booking(179,'T1AA11100167','2009-06-26','10:05:22',1590000006,2540006137,0,19,19,160,
'TO PAY','2/3','DOOR
DELIVERY',120,10,2.575,'','2009-06-27',6909,6640,5,30,12,332,1,'',0,0,0,1,6640,0,0,0);
		call
new_consignment(0,1110000022,1110000002,'2','3','2009-06-26',3,332,'',3,2,6640,19,1,20);
		COMMIT;
		UNLOCK Tables;
--------------------------------------------------------------------------

In the second case i have added "FLUSH TABLE tstamp;" command, so it won't give any error. Keep executing the first case, 9 out 10 it will be error(for the first time after a restart or when the memory is "cleared" there won't be any error).
Regards,
Vishnu

Thank you for the feedback.

Verified as described using attached test case. Problem is not repeatable every run. To repeat one should run test several times.