MySQL ODBC Connector - question about recovery with binlog

Author

question about recovery with binlog

wangxu

2006-01-19, 7:23 am

------ =_NextPart_000_03C3_
01C61D1E.2B1AD140
Content-Type: text/plain;
charset="gb2312"
Content-Transfer-Encoding: base64

ICBNeXNxbGJpbmxvZyB0
aHJvdyBvdXQgYSBlcnJv
ciAiRVJST1IgMTIzMSAo
NDIwMDApIGF0IGxp
bmUgMTA6IFZhcmlhYmxl
ICdzcWxfbW9kZScgY2Fu
J3QgYmUgc2V0IHRvIHRo
ZSB2YWx1ZSBvZiAn
NTAxNDgxNDg3JyIgd2hl
biBpIHJlY292ZXJ5IGEg
YmlubG9nLiANCg0KICBX
aGF0IGNhbiBpIGRv
Pw==

------ =_NextPart_000_03C3_
01C61D1E.2B1AD140--

Gleb Paharenko

2006-01-19, 7:23 am

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
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ 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/mysql? unsub...sie.nctu.edu.tw

Gleb Paharenko

2006-01-20, 9:23 am

Hello.

Just a possible workaround - you can parse the output of mysqlbinlog and
replace the buggy statement with the correct one.

wangxu wrote:[color=darkred
]
> 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_t
hreads=0*/;
> /*!50003 SET @OLD_COMPLETION_TYPE
=@@COMPLETION_TYPE,C
OMPLETION_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','7
9NK0006',1),('79NK00
07','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 COMPLETION_TYPE=@OLD
_COMPLETION_TYPE*/;
>
> ----------------------------------------------------------------------------------
>
> ----- Original Message -----
> From: "Gleb Paharenko" <Gleb.Paharenko@ensita.net>
> To: <mysql@lists.mysql.com>
> Sent: Thursday, January 19, 2006 6:55 PM
> Re: question about recovery with binlog
>
>
>

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ 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/mysql? unsub...sie.nctu.edu.tw

Gleb Paharenko

2006-01-23, 7:23 am

Hello.

There a lot of different ways to perform this operation. See:
http://dev.mysql.com/doc/refman/5.0...ce-utility.html
man sed
man awk

wangxu wrote:
> How to replace it?
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Gleb Paharenko

2006-01-27, 9:23 am

Hello.

At least it replaces the contents of my test file. If you're able
to provide the test case where replace utility doesn't work please
provide it to the list with the contents of the file.

wangxu wrote:[color=darkred
]
> I execute follow statement.
> -----------------------------------------
> shell>replace @@session.sql_mode=0 @@session.sql_mode=1 -- 1.000001
> -----------------------------------------
> But string "@@session.sql_mode=0" doesn't replace to "@@session.sql_mode=1".
>
>
>
> ----- Original Message -----
> From: "Gleb Paharenko" <Gleb.Paharenko@ensita.net>
> To: <mysql@lists.mysql.com>
> Sent: Monday, January 23, 2006 7:56 PM
> Subject:Re: question about recovery with binlog
>
>
>

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ 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/mysql? unsub...sie.nctu.edu.tw

Sponsored Links