cancelled ALTER TABLE operation hangs indefinitely

[Guillaume Lefranc]

Here's exactly what I tried:

MariaDB> alter table fb_likes modify column user bigint unsigned not null;
Stage: 1 of 2 'copy to tmp table' 0.057% of stage done
^CCtrl-C -- query killed. Continuing normally.
^Z
[1]+ Stopped mysql

It seems that operation has switched to enabling keys in the meantime...

From show processlist;

| 2477097 | dragonscale | localhost | customer_com | Killed | 792 | Enabling keys | alter table fb_likes modify column user bigint unsigned not null | 0.000 |

Initial table structure:

Create Table: CREATE TABLE `fb_likes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user` char(255) NOT NULL DEFAULT '',
  `name` char(255) NOT NULL DEFAULT '',
  `category` char(255) NOT NULL DEFAULT '',
  `object_id` char(255) NOT NULL DEFAULT '',
  `created_time` datetime NOT NULL,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `pushBrand` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user` (`user`,`object_id`),
  KEY `updatedAt` (`updatedAt`),
  KEY `pushBrand` (`pushBrand`)
) ENGINE=InnoDB AUTO_INCREMENT=131182304 DEFAULT CHARSET=utf8

[Elena Stepanova (elenst)]

Hi,

How many rows do you have in the table? I went up to 4M, but still haven't got the described effect. I did see the status briefly changing to "Enablling keys" when I was interrupting the query, but the query disappeared from the processlist without a delay.

Is the problem reproducible, or did you see it only once?
If it's reproducible, could you maybe upload the contents of the table, so I could try on the real data?

Thanks