Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-1669

LP #1515719: Unclear table rebuild while altering TokuDB table with non-existing option

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Shahriyar Rzayev last update 30-03-2016 15:50:42

      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)

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Unassigned
                Reporter:
                lpjirasync lpjirasync (Inactive)
                Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved: