Bug #37114 sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE
Submitted: 31 May 2008 3:59 Modified: 15 Oct 2008 19:29
Reporter: Chris Calender
Status: Closed
Category:Server: General Severity:S3 (Non-critical)
Version:5.0, 5.1, 6.0 OS:Any
Assigned to: Tatjana A. Nuernberg Target Version:
Tags: NO_BACKSLASH_ESCAPES, LOAD DATA INFILE, LOAD DATA, sql mode, SQL_MODE
Triage: Triaged: D5 (Feature request) / R2 (Low) / E3 (Medium)

[31 May 2008 3:59] Chris Calender 
Description:
Setting the sql_mode to NO_BACKSLASH_ESCAPES does not affect data inserted by LOAD DATA
INFILE.

A similar bug was fixed in the client in 5.0.25, but that fix does not affect LOAD DATA
INFILE.

http://bugs.mysql.com/bug.php?id=20103

How to repeat:
<create file.txt with '1 \aa' as the contents (sans the 's)>

CREATE DATABASE test2;

USE test2;

CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM;

SET sql_mode = 'NO_BACKSLASH_ESCAPES';

LOAD DATA INFILE 'C:/users/chris/desktop/file.txt' 
REPLACE INTO TABLE t1
FIELDS TERMINATED BY ' ';

SELECT * FROM t1;

INSERT INTO t1 (id, val1) VALUES (1, '\aa');

SELECT * FROM t1;

SET sql_mode='';

INSERT INTO t1 (id, val1) VALUES (1, '\aa');

SELECT * FROM t1;

--- Here it is with the output (and comments from me) ---

C:\Program Files\MySQL\mysql-5.0.60\bin>mysql --version
mysql  Ver 14.12 Distrib 5.0.60, for Win32 (ia32)

<You can see it is the current client>

C:\Program Files\MySQL\mysql-5.0.60\bin>mysql -u root -P 3374
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> SELECT VERSION();
+--------------------------+
| VERSION()                |
+--------------------------+
| 5.0.60-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.00 sec)

<You can see it is the current server>

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.00 sec)

mysql> USE test2;
Database changed

mysql> CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> SET sql_mode = 'NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE 'C:/users/chris/desktop/file.txt'
    -> REPLACE INTO TABLE t1
    -> FIELDS TERMINATED BY ' ';
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+------+
| id   | val1 |
+------+------+
|    1 | aa   |
+------+------+
1 row in set (0.00 sec)

<the above should show the '\'>

mysql> INSERT INTO t1 (id, val1) VALUES (1, '\aa');
Query OK, 1 row affected (0.00 sec)

<Now, let's do an INSERT to make sure I'm not crazy>

mysql> SELECT * FROM t1;
+------+------+
| id   | val1 |
+------+------+
|    1 | aa   |
|    1 | \aa  |
+------+------+
2 rows in set (0.00 sec)

<Ok, not crazy>

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

<But, let's just change the sql_mode back to be 100% sure>

mysql> INSERT INTO t1 (id, val1) VALUES (1, '\aa');
Query OK, 1 row affected (0.01 sec)

<One last insert, now the actual '\' should be gone in the 3rd record, as we set the
sql_mode to ''.>

mysql> SELECT * FROM t1;
+------+------+
| id   | val1 |
+------+------+
|    1 | aa   |
|    1 | \aa  |
|    1 | aa   |
+------+------+
3 rows in set (0.00 sec)

<And that is what we see>

Suggested fix:
Please ensure that LOAD DATA INFILE can use the sql_mode of NO_BACKSLASH_ESCAPES with the
expected results.
[31 May 2008 4:01] Chris Calender 
Verified on 5.0.60 (and 5.0.50).
[31 May 2008 5:04] Chris Calender 
Also verified on 5.1.23-rc.
[31 May 2008 5:36] Chris Calender 
Verified on 5.0.62 and 6.0.4.
[3 Jun 2008 23:47] Chris Calender 
This bug also affects mysqlimport, as the NO_BACKSLASH_ESCAPES mode is ignored when using
it also.
[4 Jun 2008 22:19] Chris Calender 
** Important Consideration / Question ** 

With this bug fixed, will it be possible to continue to use existing line terminators,
such as \n, or will this require one to use a different line terminator, such as '|', or
some other character that does not begin with '\'?
[10 Jun 2008 9:05] Tatjana A. Nuernberg 
sql_yacc.yy:
load_data:
          load_data_lock opt_local INFILE TEXT_STRING_filesystem
          {
            LEX *lex=Lex;
            lex->sql_command= SQLCOM_LOAD;
            lex->lock_option= $1;
            lex->local_file=  $2;
            lex->duplicates= DUP_ERROR;
            lex->ignore= 0;
            if (!(lex->exchange= new sql_exchange($4.str, 0)))
              MYSQL_YYABORT;
          }

sql_class.cc:
static String default_escaped("\\",default_charset_info);

sql_exchange::sql_exchange(char *name,bool flag)
  :file_name(name), opt_enclosed(0), dumpfile(flag), skip_lines(0)
{
  field_term= &default_field_term;
  enclosed=   line_start= &my_empty_string;
  line_term=  &default_line_term;
  escaped=    &default_escaped;
  cs= NULL;
}

sql_load.cc:
int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
	        List<Item> &fields_vars, List<Item> &set_fields,
                List<Item> &set_values,
                enum enum_duplicates handle_duplicates, bool ignore,
                bool read_file_from_client)
{
  ...
  String ...,*escaped=ex->escaped;

So yeah, totally ignoring SQL_MODE.

If we heed SQL_MODE, we should do it as a (changed) default while parsing: mysql_load()
has no way of telling whether an escape of \ in the parameters is a default that we'll
have to fix if SQL_MODE is NO_BACKSLASH_ESCAPE, or whether the user explicitly used LOAD
DATA ... ESCAPED BY '\\', which I believe should still override whatever default SQL_MODE
suggests.
[11 Jun 2008 9:53] Tatjana A. Nuernberg 
Anyway, changing for both LOAD DATA and SELECT/OUTFILE passes all the existing
INFILE/OUTFILE/NO_BACKSLASH_ESCAPE tests ( bootstrap ctype_big5 distinct loaddata outfile
outfile_loaddata ps query_cache sp sp-error subselect union view 
character_set_database_func ctype_big5 distinct federated fulltext3 gis loaddata
local_infile_basic local_infile_func mysqlbinlog mysqlbinlog_base64 outfile_loaddata
ps_1general ps_ddl sp-error trigger variables view warnings mysql select sql_mode
sql_mode_basic sql_mode_func system_mysql_db_fix50030 system_mysql_db_fix50117 ), but
let's make sure we have definitely a correct OUTFILE/INFILE cycle with and without
NO_BACKSLASH_ESCAPES before we attach the patch, shall we?
[13 Jun 2008 19:14] 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/47852

2661 Tatiana A. Nurnberg	2008-06-13
      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA
INFILE
      
      NO_BACKSLASH_ESCAPES was not heeded in LOAD DATA INFILE
      and SELECT INTO OUTFILE.  It is now.
[8 Sep 2008 15:05] 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/53527

2715 Tatiana A. Nurnberg	2008-09-08
      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA
INFILE
      
      NO_BACKSLASH_ESCAPES was not heeded in LOAD DATA INFILE
      and SELECT INTO OUTFILE.  It is now.
[17 Sep 2008 8:38] 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/54235

2680 Tatiana A. Nurnberg	2008-09-17
      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA
INFILE
      
      NO_BACKSLASH_ESCAPES was not heeded in LOAD DATA INFILE
      and SELECT INTO OUTFILE.  It is now.
[18 Sep 2008 11:29] 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/54278

2682 Tatiana A. Nurnberg	2008-09-18
      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA     
Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE
      
      tweaked test to make embedded server happy
[18 Sep 2008 13:25] 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/54285

2682 Tatiana A. Nurnberg	2008-09-18
      Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA     
Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with LOAD DATA INFILE
      
      tweaked test to make embedded server happy
[18 Sep 2008 15:32] 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/54301
[19 Sep 2008 18:31] Tatjana A. Nuernberg 
5.0.70, 5.1.30, 6.0.8
[9 Oct 2008 20:12] Bugs System 
Pushed into 5.1.30  (revid:azundris@mysql.com-20080918092450-v3hqnpgo8wb7v5oy) (version
source revid:matthias.leich@sun.com-20080918130519-n5rkfl809hokgnp3) (pib:4)
[15 Oct 2008 17:04] Paul DuBois 
This is actually pushed to 5.1.29, not 5.1.30.
[15 Oct 2008 19:29] Paul DuBois 
Noted in 5.0.70, 5.1.29, 6.0.8 changelogs.

The NO_BACKSLASH_ESCAPES SQL mode was ignored for LOAD DATA INFILE
and SELECT INTO ... OUTFILE. The setting is taken into account now.
[17 Oct 2008 18:43] Bugs System 
Pushed into 6.0.8-alpha  (revid:azundris@mysql.com-20080918092450-v3hqnpgo8wb7v5oy)
(version source revid:matthias.leich@sun.com-20080918121317-gcwf7sntty3hw8lj) (pib:5)
[28 Oct 2008 22:03] Bugs System 
Pushed into 5.1.29-ndb-6.2.17  (revid:azundris@mysql.com-20080918092450-v3hqnpgo8wb7v5oy)
(version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 23:21] Bugs System 
Pushed into 5.1.29-ndb-6.3.19  (revid:azundris@mysql.com-20080918092450-v3hqnpgo8wb7v5oy)
(version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 10:47] Bugs System 
Pushed into 5.1.29-ndb-6.4.0  (revid:azundris@mysql.com-20080918092450-v3hqnpgo8wb7v5oy)
(version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)