MySQL Bugs: #46951: Rotate to logbin.1

Bug #46951	Rotate to logbin.1
Submitted:	27 Aug 2009 9:24	Modified:	27 Sep 2009 16:12
Reporter:	berthier laurent	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	4.1.21-standard-log	OS:	Linux (Red Hat 3.4.6-3)
Assigned to:		CPU Architecture:	Any
Tags:	log-bin-index, rotate

Description:
Hi,

I'am very surprise by the output of my log-bin-index :

[logbin]$ cat logbin.index
/data/nobs1/logbin/logbin.036726
/data/nobs1/logbin/logbin.036727
/data/nobs1/logbin/logbin.036728
/data/nobs1/logbin/logbin.036729
/data/nobs1/logbin/logbin.036730
/data/nobs1/logbin/logbin.036731
/data/nobs1/logbin/logbin.036732
/data/nobs1/logbin/logbin.036733
/data/nobs1/logbin/logbin.036734
/data/nobs1/logbin/logbin.036735
/data/nobs1/logbin/logbin.036736
/data/nobs1/logbin/logbin.036737
/data/nobs1/logbin/logbin.036738
/data/nobs1/logbin/logbin.036739
/data/nobs1/logbin/logbin.1
/data/nobs1/logbin/logbin.036740
/data/nobs1/logbin/logbin.036741
/data/nobs1/logbin/logbin.036742
/data/nobs1/logbin/logbin.036743
/data/nobs1/logbin/logbin.1

the mysqlbinlog result of logbin.036739 :

[logbin]$ mysqlbinlog logbin.036739| tail -10f
SET ONE_SHOT CHARACTER_SET_CLIENT=33,COLLATION_CONNECTION=33,COLLATION_DATABASE=8,COLLATION_SERVER=8;
# at 9042883
#090816  3:02:57 server id 1000  log_pos 9042883        Intvar
SET INSERT_ID=1369410;
# at 9042911
#090816  3:02:57 server id 1000  log_pos 9042911        Query   thread_id=23379426      exec_time=0     error_code=0
SET TIMESTAMP=1250384577;
INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (0, 'php', '<em>File &amp;#039;./bibliobs/vocabulary.MYD&amp;#039; not found (Errcode: 24)\nquery: SELECT t.* FROM term_node r INNER JOIN term_data t ON r.tid = t.tid INNER JOIN vocabulary v ON t.vid = v.vid WHERE r.nid = 1379 ORDER BY v.weight, t.weight, t.name</em> in <em>/data/www.bibliobs.com/htdocs/includes/database.mysql.inc</em> on line <em>172</em>.', 2, '', 'http://bibliobs.nouvelobs.com/la-dentelliere';, '', '83.231.156.2', 1250384577);
# at 9043503
#090816  3:03:03 server id 1000  log_pos 9043503        Rotate to logbin.1  pos: 4

the mysqlbinlog result of logbin.1 :

[logbin]$ mysqlbinlog logbin.1| tail -10f
ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 1000, event_type: -17
Could not read entry at offset 8507996:Error in log format or read error
#090816  3:30:02 server id 1000  log_pos 8507463        Query   thread_id=23365043      exec_time=0     error_code=0
use bibliobs;
SET TIMESTAMP=1250386202;
SET ONE_SHOT CHARACTER_SET_CLIENT=33,COLLATION_CONNECTION=33,COLLATION_DATABASE=8,COLLATION_SERVER=8;
# at 8507602
#090816  3:30:02 server id 1000  log_pos 8507602        Query   thread_id=23365043      exec_time=0     error_code=0
SET TIMESTAMP=1250386202;
INSERT INTO sessions (sid, uid, cache, hostname, session, timestamp) VALUES ('8fccc2391f1670dc292053a5cdb32bb5', 0, 0, '81.19.110.2', 'image_captcha|a:1:{i:1464879912;s:5:\"hrjiN\";}captcha|a:1:{s:13:\"user_register\";a:2:{s:32:\"867d4e517549f8aaa79530eefc4ab6ed\";s:5:\"hrjiN\";s:7:\"success\";b:0;}}', 1250386202);
# at 8507956
#090816  3:30:03 server id 1000  log_pos 8507956        Rotate to logbin.036740  pos: 4

the mysqlbinlog result of logbin.036743 :

[logbin]$ mysqlbinlog logbin.036743| tail -10f
SET ONE_SHOT CHARACTER_SET_CLIENT=33,COLLATION_CONNECTION=33,COLLATION_DATABASE=8,COLLATION_SERVER=8;
# at 6415235
#090816  4:02:22 server id 1000  log_pos 6415235        Intvar
SET INSERT_ID=29230415;
# at 6415263
#090816  4:02:22 server id 1000  log_pos 6415263        Query   thread_id=23462404      exec_time=0     error_code=0
SET TIMESTAMP=1250388142;
INSERT INTO accesslog (title, path, url, hostname, uid, sid, timer, timestamp) values('Access Denied / User Login', 'user', '', '81.20.74.2', 0, '5f58af1d6c3411b91d04b33299820d86', 3466, 1250388142);
# at 6415500
#090816  4:03:05 server id 1000  log_pos 6415500        Rotate to logbin.1  pos: 4

So, the replication failed with "duplicate entry error".

Thanks for your help !!!

How to repeat:
I don't succeed in repeat this problem

Thank you for the report.

But version 4.1.21 is a old. Also version of 4.1 is in extended support state: see http://www.mysql.com/company/legal/lifecycle/ for details. Please upgrade to version 4.1.22, or, better, current GA 5.1.37, try with it and if problem still exists provide error log file. Would be better if you turn general query log or some external logging to ON as well, so we can catch FLUSH LOGS and other administrative commands: probably usage of such commands lead to the problem.

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".