| Bug #33873 | Fast ALTER TABLE doesn't work with multibyte character sets | ||
|---|---|---|---|
| Submitted: | 14 Jan 2008 22:42 | Modified: | 18 Jun 2010 1:21 |
| Reporter: | Kolbe Kegel | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.1.25 | OS: | Any |
| Assigned to: | Davi Arnaut | CPU Architecture: | Any |
[14 Jan 2008 22: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 0: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 0: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 21: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 21: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 21: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 21:24]
Kolbe Kegel
output of bug33873_charsets.sql
Attachment: bug33873_charsets.out (application/octet-stream, text), 24.73 KiB.
[10 Jun 2008 17:48]
Kolbe Kegel
Appears to affect both MyISAM and InnoDB.
[18 Jun 2008 13: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 13:13]
Davi Arnaut
Queued to 5.1-bugteam
[23 Jul 2008 10:55]
Georgi Kodinov
Pushed in 5.1.28 and 6.0.7-alpha
[24 Jul 2008 0: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 16:49]
Bugs System
Pushed into 5.1.27 (revid:joerg@mysql.com-20080626161101-h5hsaluwtf05xdr2) (version source revid:joerg@mysql.com-20080626161101-h5hsaluwtf05xdr2) (pib:3)
[5 May 2010 15:10]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 13:38]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:02]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:31]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:58]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 2:29]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[17 Jun 2010 12:06]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:50]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:33]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
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.