MySQL recovery data data appears variable Sql_mode cant solution

01./*!40101 SET sql_mode= @OLD_SQL_MODE * *;
02./*!40014 SET foreign_key_checks= @OLD_FOREIGN_KEY_CHECKS * *;
03./*!40014 SET unique_checks= @OLD_UNIQUE_CHECKS * *;
04./*!40101 SET character_set_client= @OLD_CHARACTER_SET_CLIENT * *;
05./*!40101 SET character_set_results= @OLD_CHARACTER_SET_RESULTS * *;
06./*!40101 SET collation_connection= @OLD_COLLATION_CONNECTION * *;
07./*!40111 SET sql_notes= @OLD_SQL_NOTES * *;

Solutions

Workaround: Delete the comment statement and then execute the batch SQL statement operation

Use binary log to recover an error? Variable ' Sql_mode ' can ' t be set to the value of ' 1344274432 '

Server version 5.0.18-log
Protocol version 10
Connection localhost via TCP/IP

Operating system: Windows XP

MySQL for InnoDB storage engine

Database is UTF

Configuration in Win.ini

[MySQL]
Default-character-set=utf8

[Mysqld]
Basedir= "c:/mysql5/"
Datadir= "c:/mysql5/data/"

Log-bin= "D:/log/log"
Binlog-do-db=test
Innodb_data_home_dir =
Innodb_data_file_path=e:/mysql Datafiles/ibdata1:10m;e:/mysql Datafiles/ibdata2:1m:autoextend

Innodb_log_group_home_dir= "d:/log/binlog/"
Default-character-set=utf8
Default-storage-engine=innodb

Sql-mode= "Strict_trans_tables,no_auto_create_user,no_engine_substitution"

...............

Log recovery error after resuming cold backup
C:\mysqlbinlog D:/log/log.000002|mysql-uroot-pa

ERROR 1231 (42000) at line 10:variable ' Sql_mode ' can ' t being set to the value of
' 1344274432 '

------------------------

The log appears as follows:
C:\mysqlbinlog d:/log/log.000002

/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE =@ @COMPLETION_TYPE, completion_type=0*/;
# at 4
#070423 1:10:40 Server ID 1 end_log_pos start:binlog v 4, Server v 5.0.
18-log created 070423 1:10:40
# at 98
#070423 1:10:46 Server ID 1 end_log_pos Query thread_id=9 exec_tim
E=0 error_code=0
Use IService;
SET timestamp=1177261846;
SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=1, @ @session. uniq
Ue_checks=1;
SET @ @session. sql_mode=1344274432;
SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @sessio
n.collation_server=33;
INSERT into test values (3);
# at 191
#070423 1:10:46 Server ID 1 end_log_pos 218 Xid = 27
COMMIT;
# at 218
#070423 1:11:02 Server ID 1 end_log_pos 237 Stop
# End of log file
ROLLBACK/* Added by Mysqlbinlog * *;
/*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;

Solving method

Mysqlbinlog: Utility for processing binary log files
The server-generated binary log file is written in binary format. To check files in these text formats, you should use the Mysqlbinlog utility.

You should call Mysqlbinlog this way:

Shell> mysqlbinlog [options] log-files ...
For example, to display the contents of the binary log binlog.000003, use the following command:

Shell> Mysqlbinlog binlog.0000003
The output includes all the statements contained in the BINLOG.000003, as well as other information such as the time spent by each statement, the thread ID issued by the customer, the timestamp of the outgoing thread, and so on.

Typically, you can use Mysqlbinlog to read binary log files directly and apply them to a local MySQL server. You can also use the--read-from-remote-server option to read binary logs from a remote server.

When reading remote binary logs, you can use the connection parameter options to indicate how to connect to the server, but they are often ignored unless you also specify the--read-from-remote-server option. These options are--host 、--password 、--Port 、--protocol 、--sockets and--user.

You can also use Mysqlbinlog to read the relay log files that were written from the server during the replication process. The relay log format is the same as the binary log file.

Mysqlbinlog supports the following options:

· ---help,-?

Displays a help message and exits.

· ---database=db_name,-d db_name

Only entries for this database are listed (local log only).

· --force-read,-f

With this option, if Mysqlbinlog reads a binary log event that it does not recognize, it prints a warning, ignores the event, and continues. Without this option, if Mysqlbinlog reads this type of event, it stops.

· --hexdump,-h

Displays the hexadecimal dump of the log in the note. This output can help with debugging during replication. This option has been added to the MySQL 5.1.2.

· --host=host_name,-h host_name

Gets the binary log of the MySQL server on a given host.

· --local-load=path,-l Pat

Preprocess the local temporary file for the load DATA infile in the specified directory.

· --offset=n,-o N

Skip the first n entries.

· --password[=password],-p[password]

The password to use when connecting to the server. If you use the Short option form (-p), there is no space between the option and the password. If you do not have a password value after the--password or P option on the command line, you are prompted to enter a password.

· --port=port_num,-p Port_num

The TCP/IP port number used to connect to the remote server.

· --position=n,-j N

Not in favor of use, should use--start-position.

· --protocol={tcp | SOCKET | PIPE | -position

The connection agreement used.

· --read-from-remote-server,-r

Read the binary log from the MySQL server. If this option is not given, any connection parameter options are ignored. These options are--host 、--password 、--Port 、--protocol 、--sockets and--user.

· --result-file=name,-R name

Points the output to a given file.

· --short-form,-s

Displays only the statements contained in the log and does not display additional information.

· --socket=path,-s Path

The socket file to use for the connection.

· --start-datetime=datetime

Starts the reading of an event in the binary log that has the 1th date time equal to or later than the DateTime parameter. The datetime value is relative to the local time zone on the machine running Mysqlbinlog. The value format should conform to datetime or timestamp data types. For example:

shell> mysqlbinlog--start-datetime= "2004-12-25 11:25:56" binlog.000003
This option can help you to point to point recovery.

· --stop-datetime=datetime

Stops reading from the event that the 1th date time in the binary log is equal to or later than the DateTime parameter. A description of the datetime value see the--start-datetime option. This option can help you recover in a timely manner.

· --start-position=n

Starts reading from the event that the 1th position in the binary log equals the n parameter.

· --stop-position=n

Stops reading from the event that the 1th position equals and is greater than the n parameter in the binary log.

· --to-last-logs,-t

Does not stop at the end of the requested binary log in the MySQL server, but continues to print until the end of the last binary log. If you send the loss to the same MySQL server, it can cause an infinite loop. This option requires--read-from-remote-server.

· --disable-logs-bin,-d

Disables binary logging. If you use the--to-last-logs option to send the loss to the same MySQL server, you can avoid an infinite loop. This option is also useful for crash recovery and avoids copying statements that have already been recorded. Note: This option requires a super permission.

· --user=user_name,-u user_name

The MySQL user name to use when connecting to the remote server.

· --version,-v

Displays version information and exits.

You can also use the--var_name=value option to set the following variable:

· Open_files_limit

Specifies the number of open file descriptors to keep.

The Mysqlbinlog output can be uploaded to the MySQL client to execute the statement contained in the binary log. If you have an old backup, this option is also useful in the case of crash recovery

shell> Mysqlbinlog hostname-bin.000001 | Mysql
Or:

Shell> Mysqlbinlog Hostname-bin. [0-9]* | Mysql
If you need to modify the log with the statement first, you can also point the output of the Mysqlbinlog back to a text file. (for example, you want to delete statements that you do not want to execute for some reason). After you edit the file, enter it into the MySQL program and execute the statements it contains.

Mysqlbinlog has a--position option to print only those statements in the binary log that are greater than or equal to a given position (the given position must match the start of an event). It also has options to stop or start after seeing events for a given date and time. This allows for point-to-point recovery using the--stop-datetime option (for example, the ability to say "roll the database to today's 10:30 AM").

If there are more than one binary log to execute on the MySQL server, the secure approach is to handle them in a connection. Here is an example of what is unsafe:

The code is as follows	Copy Code
shell> Mysqlbinlog hostname-bin.000001 | MySQL # danger!! shell> Mysqlbinlog hostname-bin.000002 | MySQL # danger!!

When using a different connection to the server to process the binary log, if the 1th log file contains a Create temporary table statement, the 2nd log contains a statement that uses the temporary table, which can cause problems. When the 1th MySQL process ends, the server revokes the temporary table. When the 2nd MySQL process wanted to use the table, the server reported "unaware of the table."

To avoid this type of problem, use a connection to perform the contents of all binary logs that you want to work with. The following provides a method:

The code is as follows	Copy Code
shell> Mysqlbinlog hostname-bin.000001 hostname-bin.000002 | Mysql

Another method is:

The code is as follows	Copy Code
shell> Mysqlbinlog hostname-bin.000001 >/tmp/statements.sql shell> mysqlbinlog hostname-bin.000002 >>/tmp/statements.sql shell> mysql-e "Source/tmp/statements.sql"

The output generated by Mysqlbinlog can regenerate a load data infile operation without the need for the original data file. Mysqlbinlog copies the data to a temporary file and writes a load data local infile statement that references the file. The default location of the directories to which these files are written by the system. To explicitly specify a directory, use the--local-load option.

Because Mysqlbinlog can convert the load data infile statement to the load data local infile statement (that is, it adds local), the client and server used to process the statement must be configured to allow local operations. See section 5.6.4, "LOAD DATA Local security issues."

Warning: Temporary files created for the load DATA Local statement are not automatically deleted because they are required before actual execution of those statements. You should delete the temporary files yourself after you no longer need the statement log. The file is in the temporary file directory, and the filename is similar to original_file_name-#-#.