|
Seems to be working:
create table t1 (a int);
|
alter online table t1 modify a int comment 'test';
|
|
create table t2 (a int) engine=innodb;
|
alter online table t2 modify a int comment 'test';
|
|
create table t3 (a int) partition by hash(a) partitions 2;
|
alter online table t3 modify a int comment 'test';
|
|
|
I tried to verify this again on a MyISAM partitioned table of size 100 GB. As you can see below, it does the table copy. Please check. I guess it should happen instantly (irrespective of the table size).
mysql> show full processlist;
|
+----------+---------+-----------+-----+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----------+---------+-----------+-----+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------+----------+
|
| 23871669 | xyz | XYZ:52222 | xyz | Query | 38 | copy to tmp table | alter online table ABC modify `col1` int(10) unsigned NOT NULL DEFAULT '0' comment 'Test Comment.', LOCK=SHARED | 0.165 |
|
| 23875810 | xyz | XYZ:52710 | xyz | Query | 0 | init | show full processlist | 0.000 |
|
+----------+---------+-----------+-----+---------+------+-------------------+-----------------------------------------------------------------------------------------------------------------+----------+
|
2 rows in set (0.00 sec)
|
|
mysql> show variables like '%version%';
|
+-------------------------+---------------------------------+
|
| Variable_name | Value |
|
+-------------------------+---------------------------------+
|
| innodb_version | 5.6.28-76.1 |
|
| protocol_version | 10 |
|
| slave_type_conversions | |
|
| version | 10.0.24-MariaDB-1~trusty |
|
| version_comment | mariadb.org binary distribution |
|
| version_compile_machine | x86_64 |
|
| version_compile_os | debian-linux-gnu |
|
| version_malloc_library | bundled jemalloc |
|
+-------------------------+---------------------------------+
|
Thanks.
-Bala
|
|
Very interesting. Note that you have “copy to tmp table” for “alter online” that should not be possible.
Could you share the table definition (not the data) so that I could try to repeat this?
|
|
CREATE TABLE `test` (
|
`day` date NOT NULL DEFAULT '0000-00-00',
|
`col1` int(10) unsigned NOT NULL DEFAULT '0',
|
`col2` mediumint(9) NOT NULL DEFAULT '0',
|
`col3` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'ff',
|
`col4` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
|
`col5` tinyint(1) NOT NULL DEFAULT '0',
|
`col6` bigint(12) unsigned DEFAULT NULL,
|
`col7` bigint(12) unsigned DEFAULT NULL,
|
`col8` bigint(12) unsigned DEFAULT NULL,
|
`col9` bigint(12) unsigned DEFAULT NULL,
|
PRIMARY KEY (`day`,`col1`,`col2`,`col3`,`col4`,`col5`),
|
KEY `col1` (`col1`),
|
KEY `col2` (`col2`),
|
KEY `col4` (`col4`(16))
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
/*!50100 PARTITION BY HASH ( year(day) %2 * 12 + month(day)) PARTITIONS 24 */
|
|
alter online table test modify `col1` int(10) unsigned NOT NULL DEFAULT '0' comment 'Test Comment.', LOCK=SHARED;
|
Please try and let me know if you can repeat this.
Thanks.
-Bala
|
|
No, unfortunately, I cannot.
I've created a table using your CREATE TABLE statement, populated it with 262144 random rows, and run your ALTER TABLE. I've repeated it many times — ALTER was always instant and I've never was able to catch it in the copy to tmp table state.
|
|
Wait, a thought. I was trying it (and everything above) on my development tree, that includes the fix for MDEV-9868.
While you're using 10.0.24 — without that fix.
I've repeated your test case with an earlier version of the tree, without MDEV-9868 fix. And I've got copy to tmp table.
So, I believe, your use case was fixed in MDEV-9868. The fix will be in 10.0.25.
|
|
Awesome. I will look forward for 10.0.25.
Thanks.
-Bala
|