Help us understand the problem. What is going on with this article?

LOAD DATAした行数と、AUTO_INCREMENT値が一致しない。【バグフィックス済】

More than 5 years have passed since last update.

新規に生成したテーブルに load data でデータを投入すると、auto_increment の値がおかしくなる事象が発生。

hoge.sql
CREATE TABLE `hoge` (
  `auto_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOAD DATA LOCAL INFILE 'hoge.txt' INTO TABLE hoge;

ここで、hoge.txt は9807行とする。

Query OK, 9807 rows affected (1.10 sec)
Records: 9807 Deleted: 0 Skipped: 0 Warnings: 0

すると

hoge.sql
[dev]> show create table hoge\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `hoge` (
  `auto_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

というふうに、auto_increment値が実際の行数と合わない現象が起きました。

実際に新規のデータをinsertしてみると…

hoge.sql
[dev]> select * from hoge\G
*************************** 9807. row ***************************
    auto_id: 9807
create_date: 2013-10-30 09:36:05
*************************** 9808. row ***************************
    auto_id: 16384
create_date: 2013-10-30 11:56:10

ただしく動いて、16384が振られました。

あー、16384って、$16384 = 2^{14}$だったなーと。(TeXなつかしい)

仕様は詳しく読んでませんが、load data を高速に実行するために
$2^{13} = 8192 < 9807 < 16384 = 2^{14}$
で、メモリを確保しているのではないだろうか。
そのあと、再計算し忘れてるんではなかろうか(笑

解決策は下記のとおりですが

hoge.sql
ALTER TABLE hoge ALGORITHM=COPY, AUTO_INCREMENT=1

で、めでたく

hoge.sql
[dev]> show create table hoge\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `hoge` (
  `auto_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`auto_id`),
) ENGINE=InnoDB AUTO_INCREMENT=9808 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

load data 時点で auto_increment 値を正しく設定する方法を調べなきゃ。。。

参考
http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_old_alter_table

pb_tmz08
Perl 生まれ。Linux育ち。今は、MySQLとPHPとJavaScript、FFmpegとかで遊んでいます。デザイン以外なんでもやる係。2016年からangular使い。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away