MySQL Bugs: #69151: DDL ONLINE for ALTER TABLE and temp tables created in tempdir

Bug #69151	DDL ONLINE for ALTER TABLE and temp tables created in tempdir
Submitted:	6 May 2013 8:56	Modified:	21 Nov 2015 9:41
Reporter:	Didier Dieudonne	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.6.10	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	DDL ONLINE ALTER TABLE TEMPORARY TABLE

Description:
Hello all,

I have several quite big tables (several Gb) with several hundreds of INSERTs per second on them and some reads in parallel.
Problem is coming from the modification of table structure online.
I can't prevent transactions from reading or updating the data stored in theses tables when I am obliged to change the table structure (i.e. add 1 or more columns in).
I wanted to test the Online DDL feature of MySQL 5.6.10 (with InnoDB engine 1.2.10), that seems to be improved from the availability point of view, allowing the updates/reads during the ALTER TABLE ADD col, thanks to the LOCK option.
(http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-syntax.html)
Some quick tests (add 1 simple varchar(2) column at the end of table) on a small table effectively confirm that an insert can be performed while the ALTER command is still processing.
Some other tests on a bigger table of 5 Gb also work, but not really as expected.
1)ALTER TABLE creates a temporary table in the same directory as the original table (http://dev.mysql.com/doc/refman/5.6/en/temporary-files.html).
While the documentation says that tempo tables created by ALTER TABLE are in the same directory that original table, if tmpdir variable is not set in the my.cnf (so the server uses /tmp as default under Unix), the ALTER TABLE command aborts after several seconds with an error saying that my table is full. In fact, my file system /tmp is too small (512Mb only) and can't contain what I think to be the copy of the changed table or part of it. If I use a bigger file system by specifying it in the tmpdir variable, The ALTER TABLE finishes OK.
I would conclude that the documentation does not reflect the reality, else it does not mention that something else (that can be big as well) is created in the temporary area tmpdir ?
2)I even added the option ALGORITHM=INPLACE to be sure , but result is the same.
Shouldn't this option avoid the creation of temporary tables?
Can you please check this and fix because it saturates the temp area, supposed to be used for sorts or other operations like this.

Thanks in advance.
DD

How to repeat:
- Create a big tables (several Gb)
- Set tmpdir variable to a small area (smaller than the table itself) in my.cnf
1) ALTER TABLE ADD column with LOCK=none option. (if it creates temporary tables, they should be created in the same directory than the modified table itself)
-> the ALTER TABLE command should aborts after several seconds with an error saying that the table is full.
2) ALTER TABLE ADD column with LOCK=none option and ALGORITHM=INPLACE option. -> the ALTER TABLE command aborts again with same error while the file system containing the table is large enough to store 10 of this table
3) Set tmpdir variable to a larger area (i.e. the file system used to store the database that should provide enough room)
-> the same ALTER TABLE command should finish OK this time.

Suggested fix:
1) avoid the creation of temporary table, especially if ALGORITHM=INPLACE option is requested
2) if the creation of a temporary table is really necessary, really create the temporary table in the directory of the table (as mentioned in the documentation)
3) if not possible, provide a ddltmpdir to specify a directory to be used for the creation of temporary table for [online] DDL. The goal is to separate these big temporary tables created during DDL execution from the 'usual' temporary data created by sorts for example (this can be very useful when we want to target a file system on RAMDisk for tmpdir that should not be perturbed by DDL big temporary tables)

Thank you for the report.

Please send us output of SHOW CREATE TABLE table_you_alter and exact ALTER command you use.

Hello,

Here is an example of what could be the test table:
CREATE TABLE `MY_TABLE` (
  `FIELD1` varchar(5) NOT NULL,
  `FIELD2` int(11) NOT NULL,
  `FIELD3` int(11) NOT NULL,
  `FIELD4` varchar(10) DEFAULT NULL,
  `FIELD5` varchar(20) DEFAULT NULL,
  `FIELD6` varchar(2) DEFAULT NULL,
  `FIELD7` int(11) DEFAULT NULL,
  `FIELD8` varchar(3) DEFAULT NULL,
  `FIELD9` varchar(3) DEFAULT NULL,
  `FIELD10` varchar(1) DEFAULT NULL,
  `FIELD11` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`FIELD1`,`FIELD2`,`FIELD3`),
  UNIQUE KEY `XPKMY_TABLE` (`FIELD1`,`FIELD2`,`FIELD3`),
  KEY `XIE1MY_TABLE` (`FIELD1`,`FIELD2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

That ALTER command is :
ALTER TABLE MY_TABLE ADD NEWFIELD VARCHAR(5) AFTER FIELD11;

Brgds
DD

Just an add ... 
MY_TABLE contains a little more than 45 millions of rows, that represents more than 4 Gb.

Thank you for the feedback.

In your case this is not a bug.

Please read at http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html:

Operation                 Add a column
In-Place?                 Yes
Copies Table?             Yes
Allows Concurrent DML?    Yes
Allows Concurrent Query?  Yes
Notes                     Concurrent DML is not allowed when adding an auto-increment column. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.

Since you add a column copy of the table is needed.

Hello Sveta,

Thanks for your answer but, I am not challenging the fact that a temporary table has to be created because it is an Alter table with an column addition, OK I can understand that in this case. 
I am challenging what is written at the end of page http://dev.mysql.com/doc/refman/5.6/en/temporary-files.html : <<ALTER TABLE creates a temporary table in the same directory as the original table.>>
For me it is not true, the created temporary table is NOT created in the same directory as the original table, it is created in the usual directory specified by tmpdir variable, like it is for all other temporary tables created.
As I mentioned it in the suggested fix: 
-if the creation of a temporary table is really necessary, really
create the temporary table in the directory of the table (as mentioned
in the documentation)
-if not possible, provide a ddltmpdir to specify a directory to be
used for the creation of temporary table for [online] DDL. 

The goal is to separate these big temporary tables created during DDL execution
from the 'usual' temporary data created by sorts for example (this can
be very useful when we want to target a file system on RAMDisk for
tmpdir that should not be perturbed by DDL big temporary tables).

I hope my bug report is more clear.

Thanks in advance.
Brgds
DD

Thank you for the explanation.

But I can not repeat described behavior, because temporary table exists in database directory:

[sveta@delly mysql-5.6]$ ls -la mysql-test/var/mysqld.1/data/test/
total 160
drwxr-xr-x 2 sveta sveta   4096 Jun 18 22:12 .
drwxr-xr-x 6 sveta sveta   4096 Jun 18 21:54 ..
-rw-rw---- 1 sveta sveta   8908 Jun 18 21:54 MY_TABLE.frm
-rw-rw---- 1 sveta sveta 131072 Jun 18 22:12 MY_TABLE.ibd
-rw-rw---- 1 sveta sveta   8946 Jun 18 22:12 #sql-9cf_1.frm

Please send us output of SHOW VARIABLES LIKE '%tmp%' and SHOW VARIABLES LIKE '%temp%'

Hello,

SHOW VARIABLES LIKE '%tmp%'
  default_tmp_storage_engine     InnoDB
  max_tmp_tables                 32
  slave_load_tmpdir              /tmp
  tmp_table_size                 16106127360
  tmpdir                         /tmp
SHOW VARIABLES LIKE '%temp%'
  none is displayed

There are several way to reproduce this case:
1) if you have created a big table, set tmpdir variable to a file system that is too small to contain the table (i.e. /tmp is often adequate to reproduce) and do the alter table command. The table copy should be produced and an error issued because the lack of place in /tmp
2) if you can read the code of MySQL ALTER TABLE part, can you check that, in case a temporary copy of the table is needed, this temporary table is effectively created in the same directory than the original table and not in the usual directory given by the tmpdir variable?

Thanks in advance.

Brgds
DD

I filed this bug internally long ago.

Bug 12859101 - FAST INDEX CREATION SHOULD PUT TMPFILES IN DATADIR INSTEAD OF TMPDIR

It was closed as not a bug, because it was apparently intended behaviour.

See also:
http://dev.mysql.com/doc/innodb/1.1/en/innodb-create-index-limitations.html
http://jcole.us/blog/archives/2011/01/05/innodb-online-index-add-and-the-table-t-is-full-e...

could try "set old_alter_table=1" before doing the ALTER to force table copy to be made in datadir.

Hello,

Thanks for answer but, I have some remarks:
1) our 2 cases are not really similar even if it concerns an Alter Table, you add an index, I don't, I just add a column
2) you were using an older version (5.1 with InnoDb 1.0.6.9), I was testing version 5.6 with InnoDb 1.2.10 and, in particular, an new improvement concerning Alter command. In the documentation of this version (http://dev.mysql.com/doc/refman/5.6/en/temporary-files.html), it is written the following : <<ALTER TABLE creates a temporary table in the same directory as the original table.>> . But, as far as I can see with my test, this is not true, the tempo table is created in tmpdir directory.
3) helas, I can't use your proposition ("set old_alter_table=1") or I will lost the new feature proposed by the new Alter Table, that is the DDL online. This is this particular feature that is interesting me (the possibility to specify a lock option on Alter Table request that allows the structure modification of a table - add a column - while other sessions can still update it).

For my specific case, MySQL does not work as expected. Temp table should be created in original table directory. I also can't set tmpdir to this directory, because, for performance reason, we want to set tmpdir to a RAMDisk directory, and we can't use RAMDisk for tempo tables created by Alter table request, or it will be saturated immediately.
So for me, either MySQl does what is written or we need a second tmpdir variable to specify the directory only used for Alter Table.

Brgds
DD

Thank you for the feedback.

> 2) if you can read the code of MySQL ALTER TABLE part, can you check
that, in case a temporary copy of the table is needed, this temporary
table is effectively created in the same directory than the original
table and not in the usual directory given by the tmpdir variable?

This is exactly how I tried. I set breakpoint in close_temporary_table, then watched where temporary table is created. In my case it was in the same place where table is located. Probably this happened, because I used small amount of rows.

Anyway, since our engineer could repeat it and his bug was closed as "Not a Bug" I verify this one and re-qualify as Documentation.

Hello,

If I understand well, you mean that the behavior I described has been verified and that the documentation will be adapted in consequence, right?
Damned ... I would have expected the contrary, that the code would stick to the current documentation...
Do you think that the temporary table creation in origin table directory or in a directory specified by another variable than tmpdir could be a possible change in a future release?

Thanks in advance.
Brgds
DD

Thank you for the feedback.

> If I understand well, you mean that the behavior I described has been
verified and that the documentation will be adapted in consequence,
right?

Unfortunately yes.

> Do you think that the temporary table creation in origin table
directory or in a directory specified by another variable than tmpdir
could be a possible change in a future release?

According to Oracle polices we are not allowed to make any predictions about future product features. So don't rely on this possibility.

I would have to agree with Didier. The table copy should be made in the database directory and not in the tmp directory. The tmp directory is usually not big enough in most case.

Hi,

Ran into the "table is full" problem as well on MySQL 5.6, doing a complex ALTER TABLE (not expecting an ALTER ONLINE at all):

CREATE TABLE `alter_table_tmp` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `col1` bigint(20) unsigned DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  `value` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_id` (`col1`,`col2`,`col3`)
) ENGINE=InnoDB ;

Fullfill with data ...

INSERT INTO alter_table_tmp SELECT NULL,ROUND(RAND()*18446744073709551615),ROUND(RAND()*294967295),ROUND(RAND()*294967295),MD5('alter');

INSERT INTO alter_table_tmp SELECT NULL,ROUND(RAND()*18446744073709551615),ROUND(RAND()*294967295),ROUND(RAND()*294967295),MD5('alter') FROM alter_table_tmp;

Then run an ALTER statement that you expect to copy the entire table ... so not online.

ALTER TABLE alter_table_tmp DROP COLUMN id, DROP PRIMARY KEY, DROP KEY unique_id, ADD PRIMARY KEY  (`col1`,`col2`,`col3`) ;

Transaction stays in:
    trx_operation_state: reading clustered index
Until tmpdir is full.

Command succeeded nicely with Shane's recommendation:
set old_alter_table=1; 

This is a real difference from MySQL < 5.6 behavior ....

Joffrey

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Modifying http://dev.mysql.com/doc/refman/5.6/en/temporary-files.html to say:

ALTER TABLE creates a temporary table in the same directory as the
original table, with the exception that for online DDL operations
InnoDB creates any temporary files in the temporary file directory.
If this directory is not large enough to hold such files, you may
need to set the tmpdir system variable to a different directory.

Since the present bug has been classified as a docs bug, I'm closing it. However,
you may want to open a new bug and classify it as a feature request,
referencing the present bug for the required background on the
issue in question.

This is important to fix for the cases when tmpdir is defaulted to a slower block device than what mysql is on. There are systems that don't make heavy use of temp tables so there was no need to change tmpdir to some places faster until now. 

The other opposite case is systems that create/destroy temp tables rapidly enough that tmpdir is configured to be on a small ramdisk. In this case the alter table can quickly fill up the ramdisk and fail. 

What would be extra awesome is if tmpdir were a dynamic session variable

Reopening, as a feature request.

This looks like a duplicate of Bug #50291

This is a duplicate of Bug #73250.