MySQL3 times Import Error Resolution notes!

Error 1, error at line 16779:unknown command ' \ '.

[Email protected] ~]# mysql-u hyxd-p xxxx <xxxx.sql

Enter Password:

ERROR at line 16779:unknown command ' \ '.

cause of error : Character Set problem

Workaround: Modify the character set to Utf-8

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Error 2,error 2006 (HY000) at line 11903:mysql server have gone away

[Email protected] ~]# mysql-uhyxd-p XEDK <xxxx.sql

Enter Password:

ERROR 2006 (HY000) at line 11903:mysql server have gone away

cause of error :

Response Timeout, etc.

How to resolve:

Max_allowed_packet = x M

wait_timeout=10

In addition, there may be some other buffer_size variables will affect, also worth noting!

For example

Read_buffer_size

Read_rnd_buffer_size

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Error 3, error 1418 (HY000) at line 24166:this function have none of deterministic, NO SQL, or READS SQL DATA in its declaratio N and binary logging is enabled (*might* want to use the less safe log_bin_trust_function_creators variable)

[Email protected] ~]# mysql-uroot-p xxxx <xxxx.sql

Enter Password:

ERROR 1418 (HY000) at line 24166:this function have none of deterministic, NO SQL, or READS SQL DATA in it declaration an D binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Cause of Error:

After MySQL turns on Bin-log, the error number 1418 error occurs when you call a stored procedure or function and trigger:

How to resolve:

1. Execute set GLOBAL log_bin_trust_function_creators = 1 on the client;

2.MySQL start, plus--log-bin-trust-function-creators, parameter set to 1

3. Add Log-bin-trust-function-creators=1 to the [mysqld] segment in MySQL configuration file My.ini or my.cnf

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Detailed analysis of the 3rd type of error: (refer to from the network)

Because create PROCEDURE, create FUNCTION, ALTER procedure,alter function,call, Drop PROCEDURE, drop FUNCTION and other statements are written into the binary log, It is then executed from the server. However, an indeterminate subroutine (stored procedure, function, trigger) that performs an update is not repeatable, and executing from the server (as opposed to a primary server) can cause the recovered data to be different from the original data, from the server to the primary server.

To solve this problem, MySQL enforces the requirement that:

On the primary server, creating or replacing a subroutine is rejected unless the subroutine is declared deterministic or does not change the data.

This means that when you create a subroutine, you must either declare it to be deterministic, or it will not change the data.

There are two ways of declaring it,

The first type: whether the declaration is deterministic

Deterministic and not deterministic indicate whether a subroutine always produces the same result for a given input.

If no feature is given, the default is not deterministic, so deterministic must be explicitly specified to declare a subroutine to be deterministic.

This is to illustrate that using the Now () function (or its synonym) or the rand () function does not make a subroutine non-deterministic. For now (), the binary log includes a timestamp and is executed correctly. RAND () can be correctly copied as long as it is called within a subroutine. Therefore, www.linuxidc.com can assume that timestamps and random number seeds are deterministic inputs for subroutines, which are the same on both the primary and slave servers.

The second type: whether the declaration changes the data

CONTAINS sql, NO sql, READS SQL data, modifies SQL is used to indicate whether a subroutine reads or writes data.

No SQL or reads SQL data indicates that the subroutine does not alter the data, but one must be explicitly specified, because if any of the designations, the default designation is contains SQL.

By default, if you allow create PROCEDURE or create FUNCTION statements to be accepted, you must explicitly specify either deterministic or NO SQL with reads SQL DATA, or you will incur a 1418 error.

or understand:

This is when we open the bin-log and we have to specify whether our function is

1 Deterministic indeterminate

2 No SQL does not have SQL statements and of course does not modify the data

3 READS SQL Data just reads and of course does not modify the data

4 modifies SQL data to modify

5 CONTAINS SQL contains SQL statements

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Workaround:

1, when creating subroutines (stored procedures, functions, triggers), declared as deterministic or no SQL and reads SQL data one,

For example:

CREATE definer = current_user PROCEDURE ' Newproc ' ()

Deterministic

BEGIN

#Routine body goes here ...

END;;

2, the second is to trust the creator of the subroutine, prohibit the creation, modify the sub-program on the Super permission requirements, set the Log_bin_trust_routine_creators Global system variable is 1. There are three ways to set up:

1. Execute set GLOBAL log_bin_trust_function_creators = 1 on the client;

2.MySQL start, plus--log-bin-trust-function-creators, parameter set to 1

3. Add Log-bin-trust-function-creators=1 to the [mysqld] segment in MySQL configuration file My.ini or my.cnf

This article from the "[email protected]" blog, declined to reprint!

MySQL3 times Import Error Resolution notes!