Unclear table rebuild while altering TokuDB table with non-existing option

[Shahriyar Rzayev]

With MySQL 5.6 there is an option to specify data directory for table while creating. It is only supported with create statement:

https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html

Of course if you try to use it in ALTER statement with InnoDB table:

mysql> alter table titles data directory='/home/datadir';
Query OK, 0 rows affected, 1 warning (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------+
| Warning | 1618 | <DATA DIRECTORY> option ignored |
+---------+------+---------------------------------+
1 row in set (0.00 sec)

So option is ignored.

But with TokuDB table:

mysql> show create table titles;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| titles | CREATE TABLE `titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  KEY `emp_no` (`emp_no`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

There is a table rebuild with ignored option:

mysql> alter table titles data directory='/home/datadir';
Query OK, 443308 rows affected, 1 warning (15.40 sec)
Records: 443308 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------+
| Warning | 1618 | <DATA DIRECTORY> option ignored |
+---------+------+---------------------------------+
1 row in set (0.00 sec)

From processlist:

mysql> show processlist;
+----+------+-----------+----------+---------+------+-------------------------------------------------------+---------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+----------+---------+------+-------------------------------------------------------+---------------------------------------------------+-----------+---------------+
| 38 | root | localhost | emp_toku | Query | 13 | Queried about 397001 rows, Inserted about 397000 rows | alter table titles data directory='/home/datadir' | 0 | 0 |
| 39 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |
+----+------+-----------+----------+---------+------+-------------------------------------------------------+---------------------------------------------------+-----------+---------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+----+------+-----------+----------+---------+------+---------------------+---------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+----------+---------+------+---------------------+---------------------------------------------------+-----------+---------------+
| 38 | root | localhost | emp_toku | Query | 15 | rename result table | alter table titles data directory='/home/datadir' | 0 | 0 |
| 39 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |
+----+------+-----------+----------+---------+------+---------------------+---------------------------------------------------+-----------+---------------+
2 rows in set (0.00 sec)

[George Ormond Lorch III (gl-az)]

https://tokutek.atlassian.net/browse/DB-959

[George Ormond Lorch III (gl-az)]

Out of curiosity, what does MyISAM do in this situation?

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1669