[MDEV-11217] Regression: LOAD DATA INFILE started to fail with an error - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.0.28
Fix Version/s: N/A
Component/s: Character Sets
Labels:
- need_feedback

Description

I create a file with a UTF8MB4 string:

SELECT CONCAT('aaa',0xF09F988E,'bbb') INTO OUTFILE '/tmp/test.txt';

where 0xF09F988E is UTF8MB4 encoding for the character "U+1F60E SMILING FACE WITH SUNGLASSES".

Now I load this file:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;

SHOW WARNINGS;

SELECT * FROM t1;

Notice, the CHARACTER SET utf8 clause is wrong. It should be CHARACTER SET utf8mb4 instead. But the problem is that in 10.0.24 it loaded the data with a warning, and in 10.0.28 it fails with an error.

Results in 10.0.24:

MariaDB [test]> DROP TABLE IF EXISTS t1;

Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);

Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;

Query OK, 1 row affected, 1 warning (0.00 sec)

Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

MariaDB [test]> SHOW WARNINGS;

+---------+------+-------------------------------------------------------------------------+

| Level   | Code | Message                                                                 |

+---------+------+-------------------------------------------------------------------------+

| Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x8Ebb...' for column 'a' at row 1 |

+---------+------+-------------------------------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;

+------+

| a    |

+------+

| aaa  |

+------+

Results in 10.0.28:

MariaDB [test]> DROP TABLE IF EXISTS t1;

Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);

Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;

ERROR 1300 (HY000): Invalid utf8 character string: 'aaa'

MariaDB [test]> SHOW WARNINGS;

+-------+------+--------------------------------------+

| Level | Code | Message                              |

+-------+------+--------------------------------------+

| Error | 1300 | Invalid utf8 character string: 'aaa' |

+-------+------+--------------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;

Empty set (0.00 sec)

As a result, replication from a 10.0.24 master to a 10.0.28 slave stops with an error.

The bug was likely introduced after merging this commit from MySQL:

commit 9f7288e2e0179db478d20c74f57b5c7d6c95f793

Author: Thayumanavar S <thayumanavar.x.sachithanantha@oracle.com>

Date:   Mon Jun 20 11:35:43 2016 +0530

    BUG#23080148 - BACKPORT BUG 14653594 AND BUG 20683959 TO

                   MYSQL-5.5

    The bug asks for a backport of bug#1463594 and bug#20682959. This

    is required because of the fact that if replication is enabled, master

    transaction can commit whereas slave can't commit due to not exact

    'enviroment'. This manifestation is seen in bug#22024200.

Attachments

Issue Links

relates to

MDEV-11343 LOAD DATA INFILE fails to load data with an escape character followed by a multi-byte character

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2016-11-02 14:18

Alexander Barkov made changes - 2016-11-02 14:19

Field	Original Value	New Value
Description	I create a file with a UTF8MB4 string: {code:sql} SELECT CONCAT('aaa',0xF09F988E,'bbb') INTO OUTFILE '/tmp/test.txt'; {code} where {{0xF09F988E}} is UTF8MB4 encoding for the character "U+1F60E SMILING FACE WITH SUNGLASSES". Now I load this file: {code:sql} DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8; SHOW WARNINGS; SELECT * FROM t1; {code} Notice, the {{CHARACTER SET utf8}} clause is wrong. It should be {{CHARACTER SET utf8mb4}} instead. But the problem is that in 10.0.24 it loaded the data with a warning, and in 10.0.26 it fails with an error. {noformat\|title=Results in 10.0.24} MariaDB [test]> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.01 sec) MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); Query OK, 0 rows affected (0.01 sec) MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 MariaDB [test]> SHOW WARNINGS; +---------+------+-------------------------------------------------------------------------+ \| Level \| Code \| Message \| +---------+------+-------------------------------------------------------------------------+ \| Warning \| 1366 \| Incorrect string value: '\xF0\x9F\x98\x8Ebb...' for column 'a' at row 1 \| +---------+------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [test]> SELECT * FROM t1; +------+ \| a \| +------+ \| aaa \| +------+ {noformat} {noformat:Title=Results in 10.0.28} MariaDB [test]> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.02 sec) MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); Query OK, 0 rows affected (0.05 sec) MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8; ERROR 1300 (HY000): Invalid utf8 character string: 'aaa' MariaDB [test]> SHOW WARNINGS; +-------+------+--------------------------------------+ \| Level \| Code \| Message \| +-------+------+--------------------------------------+ \| Error \| 1300 \| Invalid utf8 character string: 'aaa' \| +-------+------+--------------------------------------+ 1 row in set (0.00 sec) MariaDB [test]> SELECT * FROM t1; Empty set (0.00 sec) {noformat} As a result, replicating from a 10.0.24 master to a 10.0.28 slave stops replication.	I create a file with a UTF8MB4 string: {code:sql} SELECT CONCAT('aaa',0xF09F988E,'bbb') INTO OUTFILE '/tmp/test.txt'; {code} where {{0xF09F988E}} is UTF8MB4 encoding for the character "U+1F60E SMILING FACE WITH SUNGLASSES". Now I load this file: {code:sql} DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8; SHOW WARNINGS; SELECT * FROM t1; {code} Notice, the {{CHARACTER SET utf8}} clause is wrong. It should be {{CHARACTER SET utf8mb4}} instead. But the problem is that in 10.0.24 it loaded the data with a warning, and in 10.0.26 it fails with an error. h3. Results in 10.0.24: {noformat} MariaDB [test]> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.01 sec) MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); Query OK, 0 rows affected (0.01 sec) MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 MariaDB [test]> SHOW WARNINGS; +---------+------+-------------------------------------------------------------------------+ \| Level \| Code \| Message \| +---------+------+-------------------------------------------------------------------------+ \| Warning \| 1366 \| Incorrect string value: '\xF0\x9F\x98\x8Ebb...' for column 'a' at row 1 \| +---------+------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [test]> SELECT * FROM t1; +------+ \| a \| +------+ \| aaa \| +------+ {noformat} h3. Results in 10.0.28: {noformat} MariaDB [test]> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.02 sec) MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); Query OK, 0 rows affected (0.05 sec) MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8; ERROR 1300 (HY000): Invalid utf8 character string: 'aaa' MariaDB [test]> SHOW WARNINGS; +-------+------+--------------------------------------+ \| Level \| Code \| Message \| +-------+------+--------------------------------------+ \| Error \| 1300 \| Invalid utf8 character string: 'aaa' \| +-------+------+--------------------------------------+ 1 row in set (0.00 sec) MariaDB [test]> SELECT * FROM t1; Empty set (0.00 sec) {noformat} As a result, replicating from a 10.0.24 master to a 10.0.28 slave stops replication.

Alexander Barkov made changes - 2016-11-02 14:20

Description

I create a file with a UTF8MB4 string:
{code:sql}
SELECT CONCAT('aaa',0xF09F988E,'bbb') INTO OUTFILE '/tmp/test.txt';
{code}
where {{0xF09F988E}} is UTF8MB4 encoding for the character "U+1F60E SMILING FACE WITH SUNGLASSES".

Now I load this file:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
SHOW WARNINGS;
SELECT * FROM t1;
{code}

Notice, the {{CHARACTER SET utf8}} clause is wrong. It should be {{CHARACTER SET utf8mb4}} instead. But the problem is that in 10.0.24 it loaded the data with a warning, and in 10.0.26 it fails with an error.

h3. Results in 10.0.24:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

MariaDB [test]> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x8Ebb...' for column 'a' at row 1 |
+---------+------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
+------+
| a |
+------+
| aaa |
+------+
{noformat}

h3. Results in 10.0.28:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
ERROR 1300 (HY000): Invalid utf8 character string: 'aaa'
MariaDB [test]> SHOW WARNINGS;
+-------+------+--------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------+
| Error | 1300 | Invalid utf8 character string: 'aaa' |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
Empty set (0.00 sec)
{noformat}

As a result, replicating from a 10.0.24 master to a 10.0.28 slave stops replication.

I create a file with a UTF8MB4 string:
{code:sql}
SELECT CONCAT('aaa',0xF09F988E,'bbb') INTO OUTFILE '/tmp/test.txt';
{code}
where {{0xF09F988E}} is UTF8MB4 encoding for the character "U+1F60E SMILING FACE WITH SUNGLASSES".

Now I load this file:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
SHOW WARNINGS;
SELECT * FROM t1;
{code}

Notice, the {{CHARACTER SET utf8}} clause is wrong. It should be {{CHARACTER SET utf8mb4}} instead. But the problem is that in 10.0.24 it loaded the data with a warning, and in 10.0.26 it fails with an error.

h3. Results in 10.0.24:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

MariaDB [test]> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x8Ebb...' for column 'a' at row 1 |
+---------+------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
+------+
| a |
+------+
| aaa |
+------+
{noformat}

h3. Results in 10.0.28:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
ERROR 1300 (HY000): Invalid utf8 character string: 'aaa'
MariaDB [test]> SHOW WARNINGS;
+-------+------+--------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------+
| Error | 1300 | Invalid utf8 character string: 'aaa' |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
Empty set (0.00 sec)
{noformat}

As a result, replication from a 10.0.24 master to a 10.0.28 slave stops with an error.

Alexander Barkov made changes - 2016-11-02 14:22

Description

I create a file with a UTF8MB4 string:
{code:sql}
SELECT CONCAT('aaa',0xF09F988E,'bbb') INTO OUTFILE '/tmp/test.txt';
{code}
where {{0xF09F988E}} is UTF8MB4 encoding for the character "U+1F60E SMILING FACE WITH SUNGLASSES".

Now I load this file:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
SHOW WARNINGS;
SELECT * FROM t1;
{code}

Notice, the {{CHARACTER SET utf8}} clause is wrong. It should be {{CHARACTER SET utf8mb4}} instead. But the problem is that in 10.0.24 it loaded the data with a warning, and in 10.0.26 it fails with an error.

h3. Results in 10.0.24:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

MariaDB [test]> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x8Ebb...' for column 'a' at row 1 |
+---------+------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
+------+
| a |
+------+
| aaa |
+------+
{noformat}

h3. Results in 10.0.28:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
ERROR 1300 (HY000): Invalid utf8 character string: 'aaa'
MariaDB [test]> SHOW WARNINGS;
+-------+------+--------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------+
| Error | 1300 | Invalid utf8 character string: 'aaa' |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
Empty set (0.00 sec)
{noformat}

As a result, replication from a 10.0.24 master to a 10.0.28 slave stops with an error.

I create a file with a UTF8MB4 string:
{code:sql}
SELECT CONCAT('aaa',0xF09F988E,'bbb') INTO OUTFILE '/tmp/test.txt';
{code}
where {{0xF09F988E}} is UTF8MB4 encoding for the character "U+1F60E SMILING FACE WITH SUNGLASSES".

Now I load this file:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
SHOW WARNINGS;
SELECT * FROM t1;
{code}

Notice, the {{CHARACTER SET utf8}} clause is wrong. It should be {{CHARACTER SET utf8mb4}} instead. But the problem is that in 10.0.24 it loaded the data with a warning, and in 10.0.26 it fails with an error.

h3. Results in 10.0.24:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

MariaDB [test]> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x8Ebb...' for column 'a' at row 1 |
+---------+------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
+------+
| a |
+------+
| aaa |
+------+
{noformat}

h3. Results in 10.0.28:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
ERROR 1300 (HY000): Invalid utf8 character string: 'aaa'
MariaDB [test]> SHOW WARNINGS;
+-------+------+--------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------+
| Error | 1300 | Invalid utf8 character string: 'aaa' |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
Empty set (0.00 sec)
{noformat}

As a result, replication from a 10.0.24 master to a 10.0.28 slave stops with an error.

The bug was likely introduced after merging this commit from MySQL:
{noformat}
commit 9f7288e2e0179db478d20c74f57b5c7d6c95f793
Author: Thayumanavar S <thayumanavar.x.sachithanantha@oracle.com>
Date: Mon Jun 20 11:35:43 2016 +0530

    BUG#23080148 - BACKPORT BUG 14653594 AND BUG 20683959 TO
                   MYSQL-5.5

    The bug asks for a backport of bug#1463594 and bug#20682959. This
    is required because of the fact that if replication is enabled, master
    transaction can commit whereas slave can't commit due to not exact
    'enviroment'. This manifestation is seen in bug#22024200.
{noformat}

Jean-François Gagné added a comment - 2016-11-02 14:38 - edited

I think this is not a bug. The failure in 10.0.28 is the right behavior, and the 10.0.24 behavior is wrong. As for [1], I think MariaDB is doing the right thing in making this fail in 10.0.28. Documenting this incompatible change is however important (and I do not think it well documented).

[1]: https://bugs.mysql.com/bug.php?id=78758

Jean-François Gagné added a comment - 2016-11-02 14:38 - edited I think this is not a bug. The failure in 10.0.28 is the right behavior, and the 10.0.24 behavior is wrong. As for [1] , I think MariaDB is doing the right thing in making this fail in 10.0.28. Documenting this incompatible change is however important (and I do not think it well documented). [1] : https://bugs.mysql.com/bug.php?id=78758

Valerii Kravchuk made changes - 2016-11-02 14:44

Description

I create a file with a UTF8MB4 string:
{code:sql}
SELECT CONCAT('aaa',0xF09F988E,'bbb') INTO OUTFILE '/tmp/test.txt';
{code}
where {{0xF09F988E}} is UTF8MB4 encoding for the character "U+1F60E SMILING FACE WITH SUNGLASSES".

Now I load this file:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
SHOW WARNINGS;
SELECT * FROM t1;
{code}

Notice, the {{CHARACTER SET utf8}} clause is wrong. It should be {{CHARACTER SET utf8mb4}} instead. But the problem is that in 10.0.24 it loaded the data with a warning, and in 10.0.26 it fails with an error.

h3. Results in 10.0.24:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

MariaDB [test]> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x8Ebb...' for column 'a' at row 1 |
+---------+------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
+------+
| a |
+------+
| aaa |
+------+
{noformat}

h3. Results in 10.0.28:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
ERROR 1300 (HY000): Invalid utf8 character string: 'aaa'
MariaDB [test]> SHOW WARNINGS;
+-------+------+--------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------+
| Error | 1300 | Invalid utf8 character string: 'aaa' |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
Empty set (0.00 sec)
{noformat}

As a result, replication from a 10.0.24 master to a 10.0.28 slave stops with an error.

The bug was likely introduced after merging this commit from MySQL:
{noformat}
commit 9f7288e2e0179db478d20c74f57b5c7d6c95f793
Author: Thayumanavar S <thayumanavar.x.sachithanantha@oracle.com>
Date: Mon Jun 20 11:35:43 2016 +0530

    BUG#23080148 - BACKPORT BUG 14653594 AND BUG 20683959 TO
                   MYSQL-5.5

    The bug asks for a backport of bug#1463594 and bug#20682959. This
    is required because of the fact that if replication is enabled, master
    transaction can commit whereas slave can't commit due to not exact
    'enviroment'. This manifestation is seen in bug#22024200.
{noformat}

I create a file with a UTF8MB4 string:
{code:sql}
SELECT CONCAT('aaa',0xF09F988E,'bbb') INTO OUTFILE '/tmp/test.txt';
{code}
where {{0xF09F988E}} is UTF8MB4 encoding for the character "U+1F60E SMILING FACE WITH SUNGLASSES".

Now I load this file:
{code:sql}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
SHOW WARNINGS;
SELECT * FROM t1;
{code}

Notice, the {{CHARACTER SET utf8}} clause is wrong. It should be {{CHARACTER SET utf8mb4}} instead. But the problem is that in 10.0.24 it loaded the data with a warning, and in 10.0.28 it fails with an error.

h3. Results in 10.0.24:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1

MariaDB [test]> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x98\x8Ebb...' for column 'a' at row 1 |
+---------+------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
+------+
| a |
+------+
| aaa |
+------+
{noformat}

h3. Results in 10.0.28:
{noformat}
MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE t1 CHARACTER SET utf8;
ERROR 1300 (HY000): Invalid utf8 character string: 'aaa'
MariaDB [test]> SHOW WARNINGS;
+-------+------+--------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------+
| Error | 1300 | Invalid utf8 character string: 'aaa' |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t1;
Empty set (0.00 sec)
{noformat}

As a result, replication from a 10.0.24 master to a 10.0.28 slave stops with an error.

The bug was likely introduced after merging this commit from MySQL:
{noformat}
commit 9f7288e2e0179db478d20c74f57b5c7d6c95f793
Author: Thayumanavar S <thayumanavar.x.sachithanantha@oracle.com>
Date: Mon Jun 20 11:35:43 2016 +0530

    BUG#23080148 - BACKPORT BUG 14653594 AND BUG 20683959 TO
                   MYSQL-5.5

    The bug asks for a backport of bug#1463594 and bug#20682959. This
    is required because of the fact that if replication is enabled, master
    transaction can commit whereas slave can't commit due to not exact
    'enviroment'. This manifestation is seen in bug#22024200.
{noformat}

Alexander Barkov added a comment - 2016-11-02 15:01

The best behavior is implemented in 10.2.
Instead of giving up on a broken byte, it keeps loading more data.
Please see here for details:
https://lists.launchpad.net/maria-developers/msg10047.html

As for 10.0.x there are now multiple options:
a. just document it
b. fix it to be closer to what 10.2 does
c. fix it to 10.0.24 behavior

Perhaps "just document it" is the best solution.
This code used to be quite fragile. In 10.2 we rewrote it in a much clearer way.
Unfortunately, backporting the 10.2 changes to 10.0 is not possible (too much dependency code).
And fixing it on top of the old fragile code is not a good idea (there is a risk to add more bugs).

Alexander Barkov added a comment - 2016-11-02 15:01 The best behavior is implemented in 10.2. Instead of giving up on a broken byte, it keeps loading more data. Please see here for details: https://lists.launchpad.net/maria-developers/msg10047.html As for 10.0.x there are now multiple options: a. just document it b. fix it to be closer to what 10.2 does c. fix it to 10.0.24 behavior Perhaps "just document it" is the best solution. This code used to be quite fragile. In 10.2 we rewrote it in a much clearer way. Unfortunately, backporting the 10.2 changes to 10.0 is not possible (too much dependency code). And fixing it on top of the old fragile code is not a good idea (there is a risk to add more bugs).

Alexander Barkov made changes - 2016-11-24 03:38

Link

This issue relates to ~~MDEV-11343~~ [ ~~MDEV-11343~~ ]

Julien Fritsch made changes - 2020-08-17 10:12

Labels

need_feedback

Sergei Golubchik made changes - 2020-09-21 08:59

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.0 [ 16000 ]
Resolution		Won't Fix [ 2 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:43

Workflow

MariaDB v3 [ 78188 ]

MariaDB v4 [ 151173 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 2 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2016-11-02 14:18

Updated:: 2020-09-21 08:59

Resolved:: 2020-09-21 08:59

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Results in 10.0.24:

Results in 10.0.28:

Attachments

Issue Links

Activity

People

Dates

Git Integration