Bug #15475 Replication of GET_LOCK()
Submitted: 5 Dec 2005 4:43 Modified: 7 Nov 2007 9:59
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.1.17-BK, 4.1.15 OS:Linux (Linux)
Assigned to: Mats Kindahl CPU Architecture:Any

[5 Dec 2005 4:43] Peter Zaitsev
If   SELECT GET_LOCK()   is used   and  connection is aborted without  SELECT RELEASE_LOCK() called server seems to write  DO RELEASE_LOCK() in the binary log.   However it generates wrongly formed statement in some circumstances:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 22136806
#051203 17:50:04 server id 1  log_pos 22136806  Query   thread_id=254470        exec_time=0     error_code=0
SET TIMESTAMP=1133661004;
DO RELEASE_LOCK("http://www.southeastroads.com/louisiana001/i-010_eb_exit_261_03.jpg"");
# at 22136924
#051203 17:50:04 server id 1  log_pos 22136924  Query   thread_id=1429  exec_time=0     error_code=0

Note double "" in the end of release_lock 

This statement is marked as it is executed successfully in binary log, while it obviously fails once slave executes it.

How to repeat:
I can't simply repeat it with runing arbitraty get lock,  I however see many events in the binary log file which has same syntax error which makes it unlikely to be corruption:

DO RELEASE_LOCK("http://www.southeastroads.com/louisiana001/i-010_eb_exit_261_03.jpg"");
DO RELEASE_LOCK("http://www.everythingtarantino.com/grind_house/header.jpg"");
DO RELEASE_LOCK("http://www.vrag.ru/images/news_pic/2910027fgv.jpg"");
DO RELEASE_LOCK("http://www.southeastroads.com/louisiana001/i-010_eb_exit_261_03.jpg"");
DO RELEASE_LOCK("http://img.photobucket.com/albums/v103/reinventing_axl/septonicmadeusdoit.jpg"");
DO RELEASE_LOCK("http://img.photobucket.com/albums/v218/Ononoke/Reactor/Fansview1.jpg"");
DO RELEASE_LOCK("http://www.graywolfden.net/gallery/picKLE-cache/Midwest%20Furfest%202005/DSC00106_640.JPG"");
DO RELEASE_LOCK(""http://www.everythingelse.org/livejournal/hair01.jpg"");
DO RELEASE_LOCK("http://hometown.aol.com/justintgurl16/e27.jpeg"");
DO RELEASE_LOCK("http://hometown.aol.com/justintgurl16/e44.gif"");

Interesting enough - there are no appropriate GET_LOCKs in the binary log so it is hard to check which syntax was actually used.
[5 Dec 2005 4:52] Peter Zaitsev
OK.  Found way to repeat it.  The problem is  you can use both  '  and "     in GET_LOCK while  
logged DO RELEASE_LOCK  always used  ""  so  if    lock name contains  "  it results in broken query generated.

Here is simple php code to repeat it: 




$r=mysql_query("select get_lock('$a',10)");

[5 Dec 2005 9:33] Valeriy Kravchuk
Thank you for a bug report. Verified just as described on 4.1.17-BK (ChangeSet@1.2478, 2005-12-01 14:26:19+02:00):

[openxs@Fedora 4.1]$ cat 15475.php



$r=mysqli_query($x, "select get_lock('$a',10)");


[openxs@Fedora 4.1]$ bin/mysqld_safe --log-bin=binlog &
[1] 11694
[openxs@Fedora 4.1]$ Starting mysqld daemon with databases from /home/openxs/dbs/4.1/var

[openxs@Fedora 4.1]$ php 15475.php
Content-type: text/html
X-Powered-By: PHP/5.0.5

[openxs@Fedora 4.1]$ bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/4.1/var/Fedora.pid
051205 12:25:02  mysqld ended

[1]+  Done                    bin/mysqld_safe --log-bin=binlog
[openxs@Fedora 4.1]$ ls var/binlog.*
var/binlog.000001  var/binlog.index
[openxs@Fedora 4.1]$ bin/mysqlbinlog var/binlog.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#051205 12:24:36 server id 1  log_pos 4         Start: binlog v 3, server v 4.1.
17-log created 051205 12:24:36 at startup
# at 79
#051205 12:24:45 server id 1  log_pos 79        Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1133778285;
DO RELEASE_LOCK("abcdtest"");
[5 Nov 2007 13:06] Mats Kindahl
The bug will be fixed in 5.0 and upward since 4.1 is under conditional support. If a fix in 4.1 is critical, please file a bug explicitly for fixing in 4.1.
[7 Nov 2007 9:59] Mats Kindahl
This bug is not repeatable in 5.0 and is only present in 4.1. Since it is a minor bug, and 4.1 is under conditional support, this bug will not be fixed (in 4.1), and not in any later version since it just don't exist there.