PDA

View Full Version : error on incremental restore



mpopetz
April 30th, 2008, 01:06 PM
Hello all,

I'm continuing to read on this, but am wondering if anyone has run into this before while I research?

My current gut feel is it has something to do with setting NO_BACKSLASH_ESCAPE but haven't determined if that is true yet or not.

I'm getting the following error on incremental restore:

- Executing the extracted SQL statements against the MySQL server.
- WARNING: Binary logging is off.
- ERROR: Output of command: '"/usr/bin"/mysql --user="appManager" --password="*****" --host="xx.xx.xx.xx" -e "source /tmp/mCdKHdJHYS;"' is {
- ERROR 1064 (42000) at line 47 in file: '/tmp/mCdKHdJHYS': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\C utf8 */' at line 1
- }
- ERROR: Incremental restore failed

It looks like it's barfing on a comment line setting the charset to utf8 in it in the binlog.
Here's the part of the binlog up until the line it fails on as displayed by mysqlbinlog:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_ TYPE=0*/;
DELIMITER /*!*/;
# at 4
#080430 8:00:02 server id 5 end_log_pos 98 Start: binlog v 4, server v 5.0.50sp1a-enterprise-gp
l-log created 080430 8:00:02
# at 98
#080430 8:41:35 server id 1 end_log_pos 157 Query thread_id=33318 exec_time=0 error_code=0
SET TIMESTAMP=1209570095/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=3145728/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collat ion_connection=33,@@session.collation_server=3
3/*!*/;
BEGIN/*!*/;
# at 157
#080430 8:41:35 server id 1 end_log_pos 361 Query thread_id=33318 exec_time=0 error_code=0
use aac/*!*/;
SET TIMESTAMP=1209570095/*!*/;


When I show binlog events in mysql, it doens't even show the commented line.

When I run the zrm parselogs function, I get this:
------------------------------------------------------------
Log filename | Log Position | Timestamp | Event Type | Event
------------------------------------------------------------
DELIMITER /*!*/;
20080430090002/master-bin.000144 | 4 | 08-04-30 08:00:02 | Start: binlog v 4, server v 5.0.50sp1a-en
terprise-gpl-log created 080430 8:00:02 |
20080430090002/master-bin.000144 | 98 | 08-04-30 08:41:35 | Query | /*!\C utf8 *//*!*/; BEGIN/*!*/;
20080430090002/master-bin.000144 | 157 | 08-04-30 08:41:35 | Query | use aac/*!*/; update Vendor set
name='Vendor 23', url='', imageAttachmentId=null, description='', notes='', indexField=2, type='MAT
', disabled=0 where id=18/*!*/;

kkg
April 30th, 2008, 06:44 PM
Hello all,

I'm continuing to read on this, but am wondering if anyone has run into this before while I research?

My current gut feel is it has something to do with setting NO_BACKSLASH_ESCAPE but haven't determined if that is true yet or not.

I'm getting the following error on incremental restore:

- Executing the extracted SQL statements against the MySQL server.
- WARNING: Binary logging is off.
- ERROR: Output of command: '"/usr/bin"/mysql --user="appManager" --password="*****" --host="xx.xx.xx.xx" -e "source /tmp/mCdKHdJHYS;"' is {
- ERROR 1064 (42000) at line 47 in file: '/tmp/mCdKHdJHYS': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\C utf8 */' at line 1
- }
- ERROR: Incremental restore failed

It looks like it's barfing on a comment line setting the charset to utf8 in it in the binlog.
Here's the part of the binlog up until the line it fails on as displayed by mysqlbinlog:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_ TYPE=0*/;
DELIMITER /*!*/;
# at 4
#080430 8:00:02 server id 5 end_log_pos 98 Start: binlog v 4, server v 5.0.50sp1a-enterprise-gp
l-log created 080430 8:00:02
# at 98
#080430 8:41:35 server id 1 end_log_pos 157 Query thread_id=33318 exec_time=0 error_code=0
SET TIMESTAMP=1209570095/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=3145728/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collat ion_connection=33,@@session.collation_server=3
3/*!*/;
BEGIN/*!*/;
# at 157
#080430 8:41:35 server id 1 end_log_pos 361 Query thread_id=33318 exec_time=0 error_code=0
use aac/*!*/;
SET TIMESTAMP=1209570095/*!*/;


When I show binlog events in mysql, it doens't even show the commented line.

When I run the zrm parselogs function, I get this:
------------------------------------------------------------
Log filename | Log Position | Timestamp | Event Type | Event
------------------------------------------------------------
DELIMITER /*!*/;
20080430090002/master-bin.000144 | 4 | 08-04-30 08:00:02 | Start: binlog v 4, server v 5.0.50sp1a-en
terprise-gpl-log created 080430 8:00:02 |
20080430090002/master-bin.000144 | 98 | 08-04-30 08:41:35 | Query | /*!\C utf8 *//*!*/; BEGIN/*!*/;
20080430090002/master-bin.000144 | 157 | 08-04-30 08:41:35 | Query | use aac/*!*/; update Vendor set
name='Vendor 23', url='', imageAttachmentId=null, description='', notes='', indexField=2, type='MAT
', disabled=0 where id=18/*!*/;


Could you just do the following and see what the output is.
a) Go to the directory containing the backup
b) convert all of the binary logs into sql statements using
mysqlbinlog master-bin.* > /tmp/a.sql
c) Then use mysql to crunch the /tmp/a.sql into the server and let us know what error messages if any yoiu get.

BTW which version of mysql was backedup and which version are you restoring to?

--kkg

mpopetz
May 1st, 2008, 06:14 AM
Hmm..I don't really understand this, but when I go to the backup dir I listed in the zrm parselogs (that shows sql statements):

There are no master-bin logs:
-rw-r--r-- 1 mysql mysql 2271 Apr 30 13:00 backup-data
-rw-r--r-- 1 mysql mysql 657 Apr 30 13:00 index
-rw-r--r-- 1 mysql mysql 140 Apr 30 13:00 zrm_checksum

Is it referencing the main master-bin files since they're on the same server?

I'm running server v 5.0.50sp1a-enterprise-gpl-log

Here are a diff of the my.cnf files..I'm backing up 05 and restoring to 04. You can see I tried turning off backslash escapes on 04 and it still didn't work. I have binary logging turned on on mysql05 assuming I have to in order to get incrementals to work?

[aauadmin@aaumysql04 ~]$ diff -w my-05.cnf /etc/my.cnf
4,5c4,5
< server-id=5
< report-host=aaumysql05-c
---
> server-id=4
> report-host=aaumysql04-c
7a8
54,57d55
<
<
< innodb_flush_log_at_trx_commit = 1
<
87c85
< sql_mode="NO_BACKSLASH_ESCAPES"
---
> #sql_mode="NO_BACKSLASH_ESCAPES"
92,94d89
< log-bin=master-bin
< log-slave-updates
< sync_binlog=1

kkg
May 1st, 2008, 09:23 PM
Hmm..I don't really understand this, but when I go to the backup dir I listed in the zrm parselogs (that shows sql statements):

There are no master-bin logs:
-rw-r--r-- 1 mysql mysql 2271 Apr 30 13:00 backup-data
-rw-r--r-- 1 mysql mysql 657 Apr 30 13:00 index
-rw-r--r-- 1 mysql mysql 140 Apr 30 13:00 zrm_checksum

Is it referencing the main master-bin files since they're on the same server?

I'm running server v 5.0.50sp1a-enterprise-gpl-log

Here are a diff of the my.cnf files..I'm backing up 05 and restoring to 04. You can see I tried turning off backslash escapes on 04 and it still didn't work. I have binary logging turned on on mysql05 assuming I have to in order to get incrementals to work?

[aauadmin@aaumysql04 ~]$ diff -w my-05.cnf /etc/my.cnf
4,5c4,5
< server-id=5
< report-host=aaumysql05-c
---
> server-id=4
> report-host=aaumysql04-c
7a8
54,57d55
<
<
< innodb_flush_log_at_trx_commit = 1
<
87c85
< sql_mode="NO_BACKSLASH_ESCAPES"
---
> #sql_mode="NO_BACKSLASH_ESCAPES"
92,94d89
< log-bin=master-bin
< log-slave-updates
< sync_binlog=1


backup-data contains the backed up data since the data is compressed and/or encrypted. If you want to see the contents, use mysql-zrm-extract-backup and then go to the backup directory to see the actual contents of the backup.

mpopetz
May 2nd, 2008, 05:17 AM
Huh, I had assumed it was a zrm issue..but I took zrm out of the picture completely and it turns out mysql is generating binlogs that it can't read back in. I opened an issue with mysql and will post results back here in case anyone else runs into the problem.

Thanks for the pointers in the right direction.

-mp

mpopetz
May 2nd, 2008, 08:43 AM
bug in mysql
http://bugs.mysql.com/bug.php?id=36391

not fixed yet in the 5.0.xxxx releases.