[MDEV-19285] INSTANT ALTER from ascii_general_ci to latin1_general_ci produces corrupt data - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.4(EOL)
Fix Version/s: 10.4.5
Component/s: Character Sets, Storage Engine - InnoDB
Labels:
None

Description

Copying ALTER from ascii_general_ci to latin1_general_ci may fail in case of 8-bit characters:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ascii COLLATE ascii_general_ci, PRIMARY KEY(a)) ENGINE=InnoDB;

INSERT INTO t1 VALUES ('a'),(0xC0),('b');

ALTER TABLE t1 ALGORITHM=COPY, MODIFY a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci;

Instant ALTER performs such conversion silently, but then CHECK TABLE rerpots corrupted data:

ALTER TABLE t1 ALGORITHM=INSTANT, MODIFY a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci;

CHECK TABLE t1;

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

| Table   | Op    | Msg_type | Msg_text                                          |

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

| test.t1 | check | Warning  | InnoDB: The B-tree of index PRIMARY is corrupted. |

| test.t1 | check | error    | Corrupt                                           |

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

Instant ALTER should be disallowed from ascii_general_ci to xxx_general_ci.

Attachments

Issue Links

is caused by

MDEV-15564 Avoid table rebuild in ALTER TABLE on collation or charset changes

Closed

relates to

MDEV-19284 INSTANT ALTER with ucs2-to-utf16 conversion produces bad data

Closed

MDEV-28323 Redundant Item_func_conv_charset on WHERE utf8mb4_field=utf8mb3_field

Open

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2019-04-19 07:25

A similar problem is repeatable on alter from ascii_general_ci to utf8_general_ci.

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ascii COLLATE ascii_general_ci, PRIMARY KEY(a)) ENGINE=InnoDB;

INSERT INTO t1 VALUES ('a'),(0xC0),('b');

ALTER TABLE t1 ALGORITHM=COPY, MODIFY a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci;

ERROR 1366 (22007): Incorrect string value: '\xC0' for column `test`.`t1`.`a` at row 3

ALTER TABLE t1 ALGORITHM=INSTANT, MODIFY a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci;

SELECT HEX(a) FROM t1;

+--------+

| HEX(a) |

+--------+

| 61     |

| 62     |

| C0     |

+--------+

Notice, 0xC0 is a not well-formed utf8 string.

Alexander Barkov added a comment - 2019-04-19 07:25 A similar problem is repeatable on alter from ascii_general_ci to utf8_general_ci . DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR (10) CHARACTER SET ascii COLLATE ascii_general_ci, PRIMARY KEY (a)) ENGINE=InnoDB; INSERT INTO t1 VALUES ( 'a' ),(0xC0),( 'b' ); ALTER TABLE t1 ALGORITHM=COPY, MODIFY a VARCHAR (10) CHARACTER SET utf8 COLLATE utf8_general_ci; ERROR 1366 (22007): Incorrect string value: '\xC0' for column `test`.`t1`.`a` at row 3 ALTER TABLE t1 ALGORITHM=INSTANT, MODIFY a VARCHAR (10) CHARACTER SET utf8 COLLATE utf8_general_ci; SELECT HEX(a) FROM t1; +--------+ | HEX(a) | +--------+ | 61 | | 62 | | C0 | +--------+ Notice, 0xC0 is a not well-formed utf8 string.

Alexander Barkov added a comment - 2019-04-19 07:29

A similar problem is repeatable on alter from ascii_bin to utf8_bin.

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ascii COLLATE ascii_bin, PRIMARY KEY(a)) ENGINE=InnoDB;

INSERT INTO t1 VALUES ('a'),(0xC0),('b');

ALTER TABLE t1 ALGORITHM=COPY, MODIFY a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin;

ERROR 1366 (22007): Incorrect string value: '\xC0' for column `test`.`t1`.`a` at row 3

ALTER TABLE t1 ALGORITHM=INSTANT, MODIFY a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci;

SELECT HEX(a) FROM t1;

+--------+

| HEX(a) |

+--------+

| 61     |

| 62     |

| C0     |

+--------+

Notice, 0xC0 is a not well-formed utf8 string.

Alexander Barkov added a comment - 2019-04-19 07:29 A similar problem is repeatable on alter from ascii_bin to utf8_bin . DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR (10) CHARACTER SET ascii COLLATE ascii_bin, PRIMARY KEY (a)) ENGINE=InnoDB; INSERT INTO t1 VALUES ( 'a' ),(0xC0),( 'b' ); ALTER TABLE t1 ALGORITHM=COPY, MODIFY a VARCHAR (10) CHARACTER SET utf8 COLLATE utf8_bin; ERROR 1366 (22007): Incorrect string value: '\xC0' for column `test`.`t1`.`a` at row 3 ALTER TABLE t1 ALGORITHM=INSTANT, MODIFY a VARCHAR (10) CHARACTER SET utf8 COLLATE utf8_general_ci; SELECT HEX(a) FROM t1; +--------+ | HEX(a) | +--------+ | 61 | | 62 | | C0 | +--------+ Notice, 0xC0 is a not well-formed utf8 string.

Alexander Barkov added a comment - 2019-05-16 12:32

Closed by a join patch with ~~MDEV-19284~~

Alexander Barkov added a comment - 2019-05-16 12:32 Closed by a join patch with MDEV-19284

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2019-04-19 07:20

Updated:: 2022-04-15 12:42

Resolved:: 2019-05-16 12:32

Git Integration

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

MariaDB Server