Hello.
Just a possible workaround - you can parse the output of mysqlbinlog and
replace the buggy statement with the correct one.
wangxu wrote:
> My mysql version is 5.0.16.
>
> My problem is similar to the bug.
>
> My sql mode is ANSI and TRADITIONAL
>
> If mysql can't do recovery with mysqlbinlog at my sql mode until the bug
> will be fixed?
>
> Follow is the information of my binlog.
>
> ----------------------------------------------------------------------------------
> /*!40019 SET @@session.max_insert_delayed_threads=0*/;
> /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
> # at 4
> #060119 13:55:30 server id 1 end_log_pos 98 Start: binlog v 4, server v
> 5.0.16-standard-log created 060119 13:55:30
> # Warning: this binlog was not closed properly. Most probably mysqld crashed
> writing it.
> # at 98
> #060119 13:57:41 server id 1 end_log_pos 165 Query thread_id=4
> exec_time=0 error_code=0
> use wangxu;
> SET TIMESTAMP=1137650261;
> SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
> @@session.unique_checks=1;
> SET @@session.sql_mode=501481487;
> SET
> @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
> insert ht_detail
> values('79NK0006','79NK0006',1),('79NK0007','79NK0007',1),('79NK0008','79NK0008',1);
> # at 263
> #060119 13:57:41 server id 1 end_log_pos 290 Xid = 215
> COMMIT;
> # at 290
> #060119 14:08:30 server id 1 end_log_pos 445 Query thread_id=7
> exec_time=0 error_code=0
> SET TIMESTAMP=1137650910;
> SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
> SET @@session.sql_mode=524288;
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wangxu` /*!40100 DEFAULT CHARACTER
> SET latin1 */;
> # at 445
> #060119 14:08:30 server id 1 end_log_pos 542 Query thread_id=7
> exec_time=0 error_code=0
> SET TIMESTAMP=1137650910;
> DROP TABLE IF EXISTS `ht_detail`;
> # at 542
> #060119 14:08:40 server id 1 end_log_pos 639 Query thread_id=8
> exec_time=0 error_code=0
> SET TIMESTAMP=1137650920;
> DROP TABLE IF EXISTS `ht_detail`;
> # at 639
> #060119 14:23:13 server id 1 end_log_pos 733 Query thread_id=11
> exec_time=0 error_code=0
> SET TIMESTAMP=1137651793;
> SET @@session.foreign_key_checks=1, @@session.unique_checks=1;
> SET @@session.sql_mode=501481487;
> create table ht_header(a int);
> # at 733
> #060119 14:23:53 server id 1 end_log_pos 818 Query thread_id=12
> exec_time=0 error_code=0
> SET TIMESTAMP=1137651833;
> drop table ht_header;
> # at 818
> #060119 14:28:10 server id 1 end_log_pos 915 Query thread_id=15
> exec_time=0 error_code=0
> SET TIMESTAMP=1137652090;
> SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
> SET @@session.sql_mode=524288;
> DROP TABLE IF EXISTS `ht_detail`;
> # at 915
> #060119 14:29:44 server id 1 end_log_pos 998 Query thread_id=16
> exec_time=0 error_code=0
> use test;
> SET TIMESTAMP=1137652184;
> SET @@session.foreign_key_checks=1, @@session.unique_checks=1;
> SET @@session.sql_mode=501481487;
> drop table ht_detail;
> # at 998
> #060119 14:30:29 server id 1 end_log_pos 1160 Query thread_id=16
> exec_time=0 error_code=0
> SET TIMESTAMP=1137652229;
> create table ht_detail(hth varchar(30),sbh varchar(15),sbsl int,primary
> key(hth,sbh)) type = innodb;
> # at 1160
> #060119 14:54:50 server id 1 end_log_pos 1243 Query thread_id=20
> exec_time=0 error_code=0
> SET TIMESTAMP=1137653690;
> drop schema wangxu;
> # at 1243
> #060119 14:54:57 server id 1 end_log_pos 1328 Query thread_id=20
> exec_time=0 error_code=0
> SET TIMESTAMP=1137653697;
> create schema wangxu;
> # at 1328
> #060119 14:55:17 server id 1 end_log_pos 1422 Query thread_id=20
> exec_time=0 error_code=0
> use wangxu;
> SET TIMESTAMP=1137653717;
> create table ht_detail(a int);
> # at 1422
> #060119 17:10:13 server id 1 end_log_pos 1505 Query thread_id=24
> exec_time=0 error_code=0
> SET TIMESTAMP=1137661813;
> drop schema wangxu;
> # at 1505
> #060119 17:10:17 server id 1 end_log_pos 1590 Query thread_id=24
> exec_time=0 error_code=0
> SET TIMESTAMP=1137661817;
> create schema wangxu;
> # at 1590
> #060119 17:10:47 server id 1 end_log_pos 1687 Query thread_id=25
> exec_time=0 error_code=0
> SET TIMESTAMP=1137661847;
> SET @@session.foreign_key_checks=0, @@session.unique_checks=0;
> SET @@session.sql_mode=524288;
> DROP TABLE IF EXISTS `ht_detail`;
> # at 1687
> #060119 17:10:47 server id 1 end_log_pos 1959 Query thread_id=25
> exec_time=0 error_code=0
> SET TIMESTAMP=1137661847;
> CREATE TABLE `ht_detail` (
> `hth` varchar(30) NOT NULL default '',
> `sbh` varchar(15) NOT NULL default '',
> `sbsl` int(11) default NULL,
> PRIMARY KEY (`hth`,`sbh`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> # at 1959
> #060119 17:10:47 server id 1 end_log_pos 2072 Query thread_id=25
> exec_time=0 error_code=0
> SET TIMESTAMP=1137661847;
> /*!40000 ALTER TABLE `ht_detail` DISABLE KEYS */;
> # at 2072
> #060119 17:10:47 server id 1 end_log_pos 188 Query thread_id=25
> exec_time=0 error_code=0
> SET TIMESTAMP=1137661847;
> INSERT INTO `ht_detail` VALUES
> ('79NK0001/0003','79NK0001',1),('79NK0001/0003','79NK0002',1),('79NK0001/0003','79NK0003',1);
> # at 2260
> #060119 17:10:47 server id 1 end_log_pos 2287 Xid = 432
> COMMIT;
> # at 2287
> #060119 17:10:47 server id 1 end_log_pos 2399 Query thread_id=25
> exec_time=0 error_code=0
> SET TIMESTAMP=1137661847;
> /*!40000 ALTER TABLE `ht_detail` ENABLE KEYS */;
> # End of log file
> ROLLBACK /* added by mysqlbinlog */;
> /*!50003 SET [EMAIL PROTECTED]/;
>
> ----------------------------------------------------------------------------------
>
> ----- Original Message -----
> From: "Gleb Paharenko" <[EMAIL PROTECTED]>
> To: <mysql@lists.mysql.com>
> Sent: Thursday, January 19, 2006 6:55 PM
> Re: question about recovery with binlog
>
>
>
>>Hello.
>>
>>Are you sure that the bug is thrown by mysqlbinlog? May be you're
>>getting this while importing the output produced by mysqlbinlog?
>>Have a look here:
>> http://bugs.mysql.com/bug.php?id=13897
>>
>>Check that you're using the same versions of mysql client and mysqlbinlog.
>>
>>
>>wangxu wrote:
>>
>>> Mysqlbinlog throw out a error "ERROR 1231 (42000) at line 10: Variable
>>> 'sql_mode' can't be set to the value of '501481487'" when i recovery a
>>> binlog.
>>>
>>> What can i do?
>>
>>
>>--
>>For technical support contracts, goto https://order.mysql.com/?ref=ensita
>>This email is sponsored by Ensita.NET http://www.ensita.net/
>> __ ___ ___ ____ __
>> / |/ /_ __/ __/ __ \/ / Gleb Paharenko
>> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
>>/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
>> <___/ www.mysql.com
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
