| Bug #50117 | With bin logging enabled, the tick character is omitted. | ||
|---|---|---|---|
| Submitted: | 6 Jan 2010 20:36 | Modified: | 7 Jan 2010 17:08 |
| Reporter: | john henderson | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.1.41-community, 5.1.43-bzr | OS: | Windows (Server 2003) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | bin-log, binlog, error, logging, regression, replication | ||
[7 Jan 2010 3:40]
Caio James
Confirmed that this also occurs in 5.1.41-community on self-compiled MacOS X Server 10.6 build.
[7 Jan 2010 10:25]
Valeriy Kravchuk
This problem is NOT repeatable with 5.0.89:
77-52-1-11:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.89-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> load data infile '/tmp/mytable.txt'
-> into table mytable
-> fields terminated by ','
-> (c1, `key`);
Query OK, 1 row affected, 2 warnings (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 2
mysql> show binlog events;
+---------------+-----+--------------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+--------------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
| openxs.000001 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.89-debug-log, Binlog ver: 4 |
| openxs.000001 | 98 | Query | 1 | 200 | use `test`; create table mytable(c1 int, `key` int) |
| openxs.000001 | 200 | Query | 1 | 300 | use `test`; insert into mytable (`key`) values(1) |
| openxs.000001 | 300 | Begin_load_query | 1 | 327 | ;file_id=1;block_len=4 |
| openxs.000001 | 327 | Execute_load_query | 1 | 494 | use `test`; load data infile '/tmp/mytable.txt'
into table mytable
fields terminated by ','
(c1, `key`) ;file_id=1 |
+---------------+-----+--------------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
But even with recent 5.1.43 it is repeatable:
77-52-1-11:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.43-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table mytable(c1 int, `key` int);Query OK, 0 rows affected (0.06 sec)
mysql> insert into mytable (`key`) values(1);Query OK, 1 row affected (0.00 sec)
mysql> load data infile '/tmp/mytable.txt' into table mytable fields terminated by ',' (c1, `key`);
Query OK, 1 row affected, 2 warnings (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 2
mysql> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| openxs.000003 | 558 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'openxs.000003';
+---------------+-----+--------------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+--------------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| openxs.000003 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.43-debug-log, Binlog ver: 4 |
| openxs.000003 | 106 | Query | 1 | 208 | use `test`; create table mytable(c1 int, `key` int) |
| openxs.000003 | 208 | Query | 1 | 308 | use `test`; insert into mytable (`key`) values(1) |
| openxs.000003 | 308 | Begin_load_query | 1 | 335 | ;file_id=1;block_len=4 |
| openxs.000003 | 335 | Execute_load_query | 1 | 558 | use `test`; LOAD DATA INFILE '/tmp/mytable.txt' INTO TABLE `mytable` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (c1, key) ;file_id=1 |
+---------------+-----+--------------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
So, we have a bad regression bug here!
[7 Jan 2010 13:11]
Caio James
Also note that this issue does not occur in our build of 5.1.39, therefore must have been introduced in 5.1.40 or 5.1.41.
[7 Jan 2010 17:08]
Valeriy Kravchuk
Actually, this looks like a duplicate of bug #49479.
Description: I have a table which contains a field named "key". Whenever I reference the field in queries, I enclose it with tick marks ( `key` ). When replication is turned on and an insert query is issued under normal operation, the query is written to the bin log without error. For example, this query: INSERT INTO mytable (field1, `key`) VALUES ('HELLO', '123ABC'); will be written to the bin-log verbatim. However, when a "LOAD DATA INFILE" query is issued, the tick marks are dropped when it is written to the bin-log. For example, this query: LOAD DATA INFILE 'myfile.txt' INTO TABLE mytable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (field1, `key`) looks like this in the bin-log: LOAD DATA INFILE 'myfile.txt' INTO TABLE mytable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (field1, key) Notice the lack of tick marks surrounding the field "key". This is causing an error in the replication. I understand that keywords should not be used for field names, and that changing the field name would circumvent the issue. I would like to point out this bug anyway. How to repeat: Set up simple replication with 1 master and 1 slave. Enable bin-logging on master. Create a table with one field named "key" on the master (should replicate to slave). Create a file to import on the master. Issue this query: LOAD DATA INFILE 'filename.ext' INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (field1, `key`) The bin-log will write the query sans tick marks and replication will break.