| Bug #33873 | Fast ALTER TABLE doesn't work with multibyte character sets | ||
|---|---|---|---|
| Submitted: | 14 Jan 2008 23:42 | Modified: | 24 Jul 2008 2:32 |
| Reporter: | Kolbe Kegel | ||
| Status: | Closed | ||
| Category: | Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.1.25 | OS: | Any |
| Assigned to: | Davi Arnaut | Target Version: | 5.1+ |
| Triage: | D3 (Medium) / R3 (Medium) / E3 (Medium) | ||
[14 Jan 2008 23:51]
Kolbe Kegel
Perhaps unrelated, but the fast alter table also doesn't work for adding values to enum
columns, as reported in the documentation:
mysql 5.1.23-rc-pb1598 (root) [test]> ALTER TABLE t1 modify d enum('a');
Query OK, 2097152 rows affected (10.20 sec)
Records: 2097152 Duplicates: 0 Warnings: 0
mysql 5.1.23-rc-pb1598 (root) [test]> ALTER TABLE t1 modify d enum('a', 'b');
Query OK, 2097152 rows affected (9.91 sec)
Records: 2097152 Duplicates: 0 Warnings: 0
[2 Feb 2008 1:02]
Kolbe Kegel
Re-verified with ChangeSet@1.2512, 2008-01-27 14:31:49-07:00, tsmith@ramayana.hindu.god +2 -0
[14 Feb 2008 1:12]
Kolbe Kegel
This behavior seems to be somehow related to the character-set-server setting. I'll try to get more details about what is happening here.
[15 Feb 2008 22:20]
Kolbe Kegel
So, fast alter doesn't work in some situations if multibyte character sets are used.
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id int, c int) character set latin1;
INSERT INTO t1 VALUES ();
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
SHOW CREATE TABLE t1\G
ALTER TABLE t1 CHANGE c d int;
ALTER TABLE t1 CHANGE d c int;
ALTER TABLE t1 MODIFY c VARCHAR(10);
ALTER TABLE t1 CHANGE c d varchar(10);
ALTER TABLE t1 CHANGE d c varchar(10);
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id int, c int) character set utf8;
INSERT INTO t1 VALUES ();
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
INSERT INTO t1 (id) SELECT NULL FROM t1;
SHOW CREATE TABLE t1\G
ALTER TABLE t1 CHANGE c d int;
ALTER TABLE t1 CHANGE d c int;
ALTER TABLE t1 MODIFY c VARCHAR(10);
ALTER TABLE t1 CHANGE c d varchar(10);
ALTER TABLE t1 CHANGE d c varchar(10);
mysql> CREATE TABLE t1 (id int, c int) character set latin1;
Query OK, 0 rows affected (0.01 sec)
[...]
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> ALTER TABLE t1 CHANGE c d int;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 CHANGE d c int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 MODIFY c VARCHAR(10);
Query OK, 131072 rows affected (0.10 sec)
Records: 131072 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 CHANGE c d varchar(10);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 CHANGE d c varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE t1 (id int, c int) character set utf8;
Query OK, 0 rows affected (0.01 sec)
[...]
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> ALTER TABLE t1 CHANGE c d int;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 CHANGE d c int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 MODIFY c VARCHAR(10);
Query OK, 131072 rows affected (0.08 sec)
Records: 131072 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 CHANGE c d varchar(10);
Query OK, 131072 rows affected (0.15 sec)
Records: 131072 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 CHANGE d c varchar(10);
Query OK, 131072 rows affected (0.18 sec)
Records: 131072 Duplicates: 0 Warnings: 0
[15 Feb 2008 22:21]
Kolbe Kegel
I tested with all character sets and fast alter universally failed for this test case with all multibyte character sets. I'll attach the input and output files.
[15 Feb 2008 22:23]
Kolbe Kegel
input to test fast alter for all character sets
Attachment: bug33873_charsets.sql (text/x-sql), 5.30 KiB.
[15 Feb 2008 22:24]
Kolbe Kegel
output of bug33873_charsets.sql
Attachment: bug33873_charsets.out (application/octet-stream, text), 24.73 KiB.
[10 Jun 2008 19:48]
Kolbe Kegel
Appears to affect both MyISAM and InnoDB.
[18 Jun 2008 15:11]
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/48089 2667 Davi Arnaut 2008-06-17 Bug#33873: Fast ALTER TABLE doesn't work with multibyte character sets The problem was that when comparing tables for a possible fast alter table, the comparison was being performed using the parsed information and not the final definition. The solution is to use the possible final table layout to compare if a fast alter is possible or not.
[18 Jun 2008 15:13]
Davi Arnaut
Queued to 5.1-bugteam
[23 Jul 2008 12:55]
Georgi Kodinov
Pushed in 5.1.28 and 6.0.7-alpha
[24 Jul 2008 2:32]
Paul DuBois
Noted in 5.1.28, 6.0.7 changelogs. Fast ALTER TABLE operations were not fast for columns that used multibyte character sets.
[28 Jul 2008 18:49]
Bugs System
Pushed into 5.1.27 (revid:joerg@mysql.com-20080626161101-h5hsaluwtf05xdr2) (version source revid:joerg@mysql.com-20080626161101-h5hsaluwtf05xdr2) (pib:3)
Description: Fast ALTER TABLE apparently doesn't work for all column types. How to repeat: CREATE TABLE t1 (id int, c int); INSERT INTO t1 VALUES (); INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; INSERT INTO t1 (id) SELECT NULL FROM t1; SHOW CREATE TABLE t1\G ALTER TABLE t1 CHANGE c d int; ALTER TABLE t1 CHANGE d c int; ALTER TABLE t1 MODIFY c VARCHAR(10); ALTER TABLE t1 CHANGE c d varchar(10); ALTER TABLE t1 CHANGE d c varchar(10); mysql 5.1.23-rc-pb1598 (root) [test]> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql 5.1.23-rc-pb1598 (root) [test]> ALTER TABLE t1 CHANGE c d int; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.1.23-rc-pb1598 (root) [test]> ALTER TABLE t1 CHANGE d c int; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.1.23-rc-pb1598 (root) [test]> ALTER TABLE t1 MODIFY c VARCHAR(10); Query OK, 131072 rows affected (0.07 sec) Records: 131072 Duplicates: 0 Warnings: 0 mysql 5.1.23-rc-pb1598 (root) [test]> ALTER TABLE t1 CHANGE c d varchar(10); Query OK, 131072 rows affected (0.10 sec) Records: 131072 Duplicates: 0 Warnings: 0 mysql 5.1.23-rc-pb1598 (root) [test]> ALTER TABLE t1 CHANGE d c varchar(10); Query OK, 131072 rows affected (0.10 sec) Records: 131072 Duplicates: 0 Warnings: 0 Note that the ALTER TABLE statements are no longer "fast" (instant) for the varchar modifications as they were for the int changes. (If you put more rows into the table, the situation becomes more and more clear.) Suggested fix: Fast ALTER TABLE should quite surely work for all data types, but if, for some reason, this will not be the case in 5.1 GA, the documentation at http://dev.mysql.com/doc/refman/5.1/en/alter-table.html needs to be updated to make that very clear.