| 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: | |
| 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 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)
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.