Bug #70424 Unable to add index to the table - tablespace and undo logs are used
Submitted: 25 Sep 2013 21:03 Modified: 5 Apr 2016 14:08
Reporter: Pavel Baranov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:7.3.2 OS:Linux (centos 6.4 64bit)
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: ALTER TABLE, cluster

[25 Sep 2013 21:03] Pavel Baranov
Description:
A simple query:

alter table TABLE_NAME add index profile_id(profile_id);
returns:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1297 | Got temporary error 266 'Time-out in NDB, probably caused by deadlock' from NDB |
| Error   | 1205 | Lock wait timeout exceeded; try restarting transaction                          |
| Warning | 1296 | Got error 4350 'Transaction already aborted' from NDB                           |
| Error   | 1296 | Got error 4350 'Transaction already aborted' from NDBCLUSTER                    |
| Error   | 1180 | Got error 4350 during COMMIT                                                    |
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back                   |
+---------+------+---------------------------------------------------------------------------------+

How to repeat:
1. Create table:

 CREATE TABLE `TABLE_NAME` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `link_hash` bigint(20) unsigned NOT NULL,
  `profile_id` bigint(20) unsigned NOT NULL,
  `bindata` blob NOT NULL COMMENT '64kb should be enough for gziped page',
  `timestamp` bigint(20) DEFAULT NULL COMMENT 'how many bytes unix timestamp take?',
  `version` tinyint(4) DEFAULT NULL COMMENT 'we need to revise versioning of mspiderv3',
  PRIMARY KEY (`id`),
  KEY `link_hash` (`link_hash`),
  KEY `timestamp` (`timestamp`),
  KEY `version` (`version`)
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster

2. Load 228273 (that's we have) rows of data

3. run alter table TABLE_NAME add index profile_id(profile_id);