Bug #22504 load data infile sql statement in replication architechure get error
Submitted: 20 Sep 2006 3:28 Modified: 15 Apr 2009 9:58
Reporter: steven chang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.26, 5.0.24a OS:Linux (RedHat ASE 4)
Assigned to: CPU Architecture:Any

[20 Sep 2006 3:28] steven chang
Description:
Here is the error from mysql error file:
Slave: Error 'You have an error in your SQL syntax; check the manual that corresponds
o your MySQL server version for the right syntax to use near 'INTOtable test1' at line 1' on query. Default dat
abase: 'test'. Query: 'load data  INFILE '/tmp/SQL_LOAD-2-1-1.data' INTOtable test1', Error_code: 1064   
and show slave status\G;
             Slave_IO_State: Waiting for master to send event
                Master_Host: mytest.chiahsin.com.tw
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000077
        Read_Master_Log_Pos: 96446251
and the following is the data from mysqbinlog command ;
SET TIMESTAMP=1158623364;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=501277198;
/*!\C big5 */;
SET @@session.character_set_client=1,@@session.collation_connection=1,@@session.collation_server=1;
load data  LOCAL INFILE '/tmp/SQL_LOAD_MB-1-2' INTOtable test1; 
             Relay_Log_File: mytest2-relay-bin.000024
              Relay_Log_Pos: 290
      Relay_Master_Log_File: mysql-bin.000077
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1064
                 Last_Error: Error '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 'INTOtable test1' at line 1' on query. Default datab
ase: 'test'. Query: 'load data  INFILE '/tmp/SQL_LOAD-2-1-1.data' INTOtable test1'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 96444029
            Relay_Log_Space: 3721
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
    

How to repeat:
We just test the mysql and want to use mysql for our ASP service system.
If this is really a bug , and now could not be fiexed in a few moments,
I think we woud skip the load data infile in the replication , and writing 
C program to import text file.
[20 Sep 2006 12:10] Valeriy Kravchuk
Thank you for a problem report. Sorry, but what exact statement was executed on master server? "load data  LOCAL INFILE '/tmp/SQL_LOAD_MB-1-2' INTOtable test1;"?

Please, try to run 

load data LOCAL INFILE '/tmp/SQL_LOAD_MB-1-2' INTO table test1; 

(note space after INTO) and check if the same error message will appear.
[21 Sep 2006 1:14] steven chang
Hi :

    The following is the statement run in master server :

    load data infile 'test.txt' into table test1;

    test.txt just contain one row data, "test"

Regards,
Steven
[13 Oct 2006 14:16] Valeriy Kravchuk
Please, try to repeat with a newr version, 5.0.26, on both master and slave, and inform about the results.
[17 Oct 2006 4:02] steven chang
Hi :

    After I test 5.0.26, the bug still exists. Any suggestion!!
    Would you mind telling me how to solve this problem through mysqlbin log
    or other method. How do I try to modify the sql statement when
    slave I/O format the sql from relay bin log? Thank you!!
  
    Is there any site report the same bug ???

Regards,
Steven
[26 Oct 2006 6:24] Ramesh Donnipadu
Can you check if IGNORE_SPACE is enabled for SQL_MODE? Disabling this should fix the problem.
[27 Oct 2006 5:46] steven chang
Hi :

  The following is the ignore_space definition in 5.0 reference guide.
  I think it's for build-in funtion usage in the sql statement and not 
  related with the formating sql statement for the slave serer. And by 
  the way , here is the ignore_space in my test system.

mysql> show variables like 'ignore_space';
Empty set (0.00 sec)

Anyway thank you for your help!!
 

Allow spaces between a function name and the ‘(’ character. This forces all function names to be treated as reserved words. As a result, if you want to access any database, table, or column name that is a reserved word, you must quote it. For example, because there is a USER() function, the name of the user table in the mysql database and the User column in that table become reserved, so you must quote them: 

SELECT "User" FROM mysql."user";

The IGNORE_SPACE SQL mode applies to built-in functions, not to stored routines. it is always allowable to have spaces after a routine name, regardless of whether IGNORE_SPACE is enabled. 

Regards,
Steven
[28 Oct 2006 14:28] Ashok Padda
Steven,

Pls lookup variable 'sql_mode' and if it has ignore_space enabled, unset it and try. 

+Ashok.
[29 Oct 2006 9:30] steven chang
Hi :

     Solved !! Thank you for your help!!
     My orginal sql_mode = oracle, traditional
     after modification :
     sql_mode =  PIPES_AS_CONCAT,ANSI_QUOTES,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,STRICT_TRANS_TABLES
,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER

Regards,
Steven
[30 Oct 2006 7:48] Valeriy Kravchuk
So, I think, this (if it is intended behaviour), should be properly documented at http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html.
[30 Oct 2006 15:21] Sergei Golubchik
I don't think it's intended that "IGNORE_SPACES" should have this effect :)
[31 Oct 2006 1:59] steven chang
Hi :

     I do really thank you for your technical help!! If every vendor
     gets such service attitude like you , I think we would not suffer
     in IT. 

    I retry the Replication setting-up and find this problem is caused 
    by Master server with "sql_mode=oracle,traditional", and the following
    is the testing result.

    1. part of my.cnf in master server 
#SQL-MODE
sql-mode=oracle,traditional
#sql-mode=pipes_as_concat,ansi_quotes,no_key_options,no_table_options,no_field_options,no_auto_create_user,trad
itional

# Replication Master Server (default)
# binary logging is required for replication
log-bin=data/mysql-bin
expire_logs_days = 1
sync_binlog=1
log_bin_trust_function_creators = 1     
2. the error from "show slave status\G":
           Error '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 'INTOtable test.test' at line 1' on query. Default
atabase: ''. Query: 'load data  INFILE '/tmp/SQL_LOAD-2-1-1.data' IGNORE INTOtable test.test'

Then I rebuild the slave ,and modify my.cnf to tune sql_mode variables in master server
1. part of my.cnf of slave server:
#SQL-MODE
sql-mode=oracle,traditional
#sql-mode=pipes_as_concat,ansi_quotes,no_key_options,no_table_options,no_field_options,no_auto_create_user,trad
itional

# Replication Master Server (default)
# binary logging is required for replication
log-bin=data/mysql-bin
expire_logs_days = 1
sync_binlog=1
log_bin_trust_function_creators = 1

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       =  2
max_relay_log_size=100M
slave-load-tmpdir = /tmp
read-only                                            

2. relay bin log :
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#061031 19:55:36 server id 2  end_log_pos 98    Start: binlog v 4, server v 5.0.26-standard-log created 061031 
19:55:36
# at 98
#700101  8:00:00 server id 1  end_log_pos 0     Rotate to mysql-bin.000026  pos: 98
# at 141
#061031  7:27:01 server id 1  end_log_pos 0     Start: binlog v 4, server v 5.0.26-standard-log created 061031 
 7:27:01
# at 235
#061031  7:32:27 server id 1  end_log_pos 93    Query   thread_id=4     exec_time=13    error_code=0
use test;
SET TIMESTAMP=1162251147;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=501276678;
/*!\C big5 */;
SET @@session.character_set_client=1,@@session.collation_connection=1,@@session.collation_server=1;
insert into test values('ccc');
# at 328
#061031  7:32:27 server id 1  end_log_pos 218   Xid = 18
COMMIT;
# at 355
#061031  7:33:23 server id 1  end_log_pos 31
#Begin_load_query: file_id: 1  block_len: 8
# at 386
#061031  7:33:23 server id 1  end_log_pos 163   Execute_load_query      thread_id=5     exec_time=0     error_c
ode=0
SET TIMESTAMP=1162251203;
load data LOCAL INFILE '/tmp/SQL_LOAD_MB-1-3' INTO  table test.test;
# file_id: 1
# at 518
#061031  7:33:23 server id 1  end_log_pos 408   Xid = 21
COMMIT;
# at 545
#061031  7:36:55 server id 1  end_log_pos 93    Query   thread_id=6     exec_time=0     error_code=0
use test;
SET TIMESTAMP=1162251415;
insert into test values('ddd');     
# at 638
#061031  7:36:55 server id 1  end_log_pos 528   Xid = 28
COMMIT;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Hope this would give you some hint.

Regards,
Steven
[31 Oct 2008 16:43] Valeriy Kravchuk
The bug is still repeatable with 5.0.70:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.70-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set sql_mode='oracle,traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile 'c:/tmp/work/t1.txt' into table t1;
Query OK, 3 rows affected (0.08 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlbinlog "..\..\MySQL Server 5.0\
data\toshiba-user-bin.000011"
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#081031 18:34:01 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.
70-enterprise-gpl-nt-log created 081031 18:34:01 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed wri
ting it.
ROLLBACK/*!*/;
BINLOG '
eTMLSQ8BAAAAXgAAAGIAAAABAAQANS4wLjcwLWVudGVycHJpc2UtZ3BsLW50LWxvZwAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB5MwtJEzgNAAgAEgAEBAQEEgAASwAEGg==
'/*!*/;
# at 98
#081031 18:35:43 server id 1  end_log_pos 174   Query   thread_id=3     exec_tim
e=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1225470943/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/
;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
drop table t1
/*!*/;
# at 174
#081031 18:35:54 server id 1  end_log_pos 260   Query   thread_id=3     exec_tim
e=1     error_code=0
SET TIMESTAMP=1225470954/*!*/;
create table t1(c1 int)
/*!*/;
# at 260
#081031 18:36:36 server id 1  end_log_pos 328   Query   thread_id=3     exec_tim
e=0     error_code=0
SET TIMESTAMP=1225470996/*!*/;
BEGIN
/*!*/;
# at 328
#081031 18:36:36 server id 1  end_log_pos 358
#Begin_load_query: file_id: 1  block_len: 7
# at 358
#081031 18:36:36 server id 1  end_log_pos 491   Execute_load_query      thread_i
d=3     exec_time=0     error_code=0
SET TIMESTAMP=1225470996/*!*/;
load data LOCAL INFILE 'C:/DOCUME~1/SATELL~1/LOCALS~1/Temp/SQL_LOAD_MB-1-3' INTO
 table t1
/*!*/;
# file_id: 1
# at 491
#081031 18:36:36 server id 1  end_log_pos 518   Xid = 4
COMMIT/*!*/;
# at 518
#081031 18:38:52 server id 1  end_log_pos 586   Query   thread_id=4     exec_tim
e=0     error_code=0
SET TIMESTAMP=1225471132/*!*/;
SET @@session.sql_mode=501277198/*!*/;
BEGIN
/*!*/;
# at 586
#081031 18:38:52 server id 1  end_log_pos 616
#Begin_load_query: file_id: 2  block_len: 7
# at 616
#081031 18:38:52 server id 1  end_log_pos 749   Execute_load_query      thread_i
d=4     exec_time=0     error_code=0
SET TIMESTAMP=1225471132/*!*/;
load data  LOCAL INFILE 'C:/DOCUME~1/SATELL~1/LOCALS~1/Temp/SQL_LOAD_MB-2-1' INT
Otable t1
/*!*/;
# file_id: 2
# at 749
#081031 18:38:52 server id 1  end_log_pos 776   Xid = 11
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[31 Oct 2008 17:17] Valeriy Kravchuk
MySQL 5.1 is also affected:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -P3310 -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.29-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.11 sec)

mysql> create table t1(c int);
Query OK, 0 rows affected (0.08 sec)

mysql> set sql_mode='ignore_space';
Query OK, 0 rows affected (0.05 sec)

mysql> select @@sql_mode;
+--------------+
| @@sql_mode   |
+--------------+
| IGNORE_SPACE |
+--------------+
1 row in set (0.00 sec)

mysql> load data local infile 'c:/tmp/work/t1.txt' into table t1;
Query OK, 3 rows affected (0.13 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> show master status;
+-------------------------+----------+--------------+------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| toshiba-user-bin.000005 |      525 |              |                  |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqlbinlog "c:\Documents and Settin
gs\All Users\Application Data\MySQL\MySQL Server 5.1\data\toshiba-user-bin.00000
5"
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#081031 19:10:02 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.
29-rc-community-log created 081031 19:10:02 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed wri
ting it.
ROLLBACK/*!*/;
BINLOG '
6jsLSQ8BAAAAZgAAAGoAAAABAAQANS4xLjI5LXJjLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADqOwtJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#081031 19:11:32 server id 1  end_log_pos 182   Query   thread_id=1     exec_tim
e=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1225473092/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq
ue_checks=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/
;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
drop table t1
/*!*/;
# at 182
#081031 19:11:43 server id 1  end_log_pos 267   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1225473103/*!*/;
create table t1(c int)
/*!*/;
# at 267
#081031 19:12:20 server id 1  end_log_pos 335   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1225473140/*!*/;
SET @@session.sql_mode=8/*!*/;
BEGIN
/*!*/;
# at 335
#081031 19:12:20 server id 1  end_log_pos 365
#Begin_load_query: file_id: 1  block_len: 7
# at 365
#081031 19:12:20 server id 1  end_log_pos 498   Execute_load_query      thread_i
d=1     exec_time=0     error_code=0
SET TIMESTAMP=1225473140/*!*/;
load data  LOCAL INFILE 'C:/DOCUME~1/SATELL~1/LOCALS~1/Temp/SQL_LOAD_MB-1-4' INT
Otable t1
/*!*/;
# file_id: 1
# at 498
#081031 19:12:20 server id 1  end_log_pos 525   Xid = 6
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[28 Feb 2009 3:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67923

2810 Leonard Zhou	2009-02-26
      BUG#22504 load data infile sql statement in replication architecture get error
      
      The problem is issued because we set wrong start position and stop position of query string into binlog.
      That two values is stored as part of head info of query string.
      So when we parse binlog, we will get position values then get the query string according position values.
      But seems that two values is not calculated correctly after the parse of Yacc.
      
      We don't wan to touch so much of yacc because it maybe influence other code.
      So just add one space after 'INTO' key word when parsing.
      This can easily resolve the problem.
[19 Mar 2009 6:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69681

2810 Leonard Zhou	2009-03-16
      BUG#22504 load data infile sql statement in replication architecture get error
      
      The problem is issued because we set wrong start position and stop position of query string into binlog.
      That two values are stored as part of head info of query string.
      When we parse binlog, we first get position values then get the query string according position values.
      But seems that two values are not calculated correctly after the parse of Yacc.
      
      We don't want to touch so much of yacc because it may influence other codes.
      So just add one space after 'INTO' key word when parsing.
      This can easily resolve the problem.
[19 Mar 2009 7:58] li zhou
Pushed into 5.1-bugteam and 6.0-bugteam
[19 Mar 2009 9:59] li zhou
Seperate this problem into 2 parts: yacc and replication.

Fix replication problem in this bug and file a new bug#43746 to report yacc problem.
[20 Mar 2009 14:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69926

2823 Georgi Kodinov	2009-03-20
      Fixed the failing tests. Addendum to the fix for bug #22504.
[27 Mar 2009 14:55] Bugs System
Pushed into 5.1.34 (revid:joro@sun.com-20090327143448-wuuuycetc562ty6o) (version source revid:azundris@mysql.com-20090320180900-52heo1ox8xqjknt2) (merge vers: 5.1.34) (pib:6)
[28 Mar 2009 7:41] Jon Stephens
Documented bugfix in the 5.0.34 changelog as follows:

        When the server SQL mode included IGNORE_SPACES, statement-based
        replication of LOAD DATA INFILE failed because the statement was
        read incorrectly from the binary log.

        See also Bug #43746.
[13 Apr 2009 9:20] Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090413084402-snnrocwzktcl88ny) (version source revid:azundris@mysql.com-20090320180810-jcyb5cq4jcx58zsz) (merge vers: 6.0.11-alpha) (pib:6)
[15 Apr 2009 9:58] Jon Stephens
Bugfix also documented in 6.0.11 changelog.

NOTE: An entry for this bug was added previously to the *5.1.34* changelog, the 5.0.34 (this was merely typo in my comment above).

It appears this bug won't be fixed in 5.0, so closing bug report.
[9 May 2009 16:39] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (merge vers: 5.1.34-ndb-6.2.18) (pib:6)
[9 May 2009 17:36] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (merge vers: 5.1.34-ndb-6.3.25) (pib:6)
[9 May 2009 18:33] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)