MySQL Bugs: #15612: Replication stops with LOCK TABLES error

Bug #15612	Replication stops with LOCK TABLES error
Submitted:	9 Dec 2005 0:20	Modified:	25 Jan 2006 7:29
Reporter:	Ananth Reddy	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.13-standard-log	OS:	Linux (RedHat Linux)
Assigned to:		CPU Architecture:	Any

Description:
We have multi-level replication as follows
master-->slave1-->slave2--->slave3
We are seeing an error 1100 in slave3 SQL thread
Here is exact error
Last_Errno: 1100
                 Last_Error: Error 'Table 'LocalMemberAggregateVal' was not locked with LOCK TABLES' on query. Default database: 'silo_s3'. Query: 'update LocalMemberAggregateVal set version=214, aggval=21265822171, sequence=458791431, aggdate='2005-12-08 00:56:52', createDate='2005-12-08 00:00:07' where aggval_type=1 and aggval_key=0 and bucketType=2 and bucketVal=315008 and version=213'

We are seeing these errors on many table just today.
This replication setup is running w/o any problems for two months and all of sudden this error started today.

We are also seeing these errors on INSERTs too.

The work-around I am going to try is to setup slave3 from slave3 again from scratch by copying data.

How to repeat:
don't know how to repeat

Thank you for a problem report. Do you have anything unusual in the error logs since (and before) these errors appeared?

Please, inform about the results of your workaround applied. Take into account also, that without a repeatable sequence of actions to reproduce the problem we will not be able to fix it.

We shutdown this slave3 server everynight and start again at 6am
Here is the errorlog just before these errors
===========
051208  0:05:01 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown

051208  0:05:01 [ERROR] Slave I/O thread killed while reading event
051208  0:05:01 [ERROR] Slave I/O thread exiting, read up to log 's3r2-log-bin.001855', position 197030941
051208  0:05:01 [ERROR] Error reading relay log event: slave SQL thread was killed
051208  0:05:02 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

051208 00:05:02  mysqld ended

051208 06:00:01  mysqld started
051208  6:00:01 [Warning] Changed limits: max_open_files: 1024  max_connections: 50  table_cache: 482
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.1.13-standard-log'  socket: '/tmp/mysql.socks3'  port: 3309  MySQL Community Edition - Standard (GPL)
051208  6:00:01 [Note] Slave SQL thread initialized, starting replication in log 's3r2-log-bin.001855' at position 197030941, relay log '/mysql/binlogs/s3logs/s3r3-relay.001102' position: 33410002
051208  6:00:01 [Note] Slave I/O thread: connected to master 'repl@s3r2.mypoints.com:3306',  replication started in log 's3r2-log-bin.001855' at position 197030941
==========

One more thing about what was different on this day when we started getting these errors than previous 2 months :

We have a timer process which runs on slave1 in our multi-level config
master-->slave1-->slave2--slave3
and the process creates TEMPORARY tables. The timer task had it DB connection to slave1 closed while it is processing data in TEMPORARY table. We got lot of errors in slave3 that the TEMPORARY table does not exist.

We have testing environment and we may be able to recreate the problem.
I'll let you know in couple of days. Meanwhile, if you want to discuss in person, you canemail me and I'll send my phone number.

Thanks

Thank you for the additional information. I hope, you'll be able to repeat the problem in the testing environment. 

In the meantime, please, send the my.cnf files from master and all your slaves.

I uploded cnf files for all servers in the hierarchy.
As you can see we are running multiple servers on slave3 machine
The work-around we tried worked fine.
The work-around is to setup slave3 from scratch using data from slave2

I am still working on recreating it in our testing. I'll let you know when I am successful.

Coming back to your last comment:

> I am still working on recreating it in our testing. I'll let you know when I am
> successful.

Are there any results with recreation of the problem?

I didn't get a chance to recreate problem yet. I'll be out until new year and will let you know

> I'll be out until new year and will let you know

I put this bug report to "Need Feedback" status. Please, reopen it next year when you'll be able to do some testing.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".