オートインクリメントの確認と再設定

2017.9.12 (火)

オートインクリメントの設定をしているカラム(id)に新しい番号が付番されていないっぽい。通常nullを入れるとオートインクリメントが設定されているカラムは自動的に次の値を付番します。急にインサートできなくなったという次第です。

1048:Column 'id' cannot be null

auto_incrementの設定を確認する(1)

以下のコマンドで確認

show variables like '%auto_inc%';

案の定おかしなことになっている

mysql> show variables like '%auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

auto_incrementの設定を確認する(2)

こっちのコマンドでも見れる

use DBNAME;
show table status like 'TABLENAME%'\G

いろいろいっぱい出てきますが、Auto_incrementというところに、現在の最大値がある筈です。nullの場合は設定されていません。

*************************** 1. row ***************************
           Name: pf_report
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 9688
 Avg_row_length: 294
    Data_length: 2853268
Max_data_length: 281474976710655
   Index_length: 136192
      Data_free: 0
 Auto_increment: NULL ←ココ
    Create_time: 2017-09-11 14:02:07
    Update_time: 2017-09-12 00:24:49
....

auto_incrementの設定を確認する(3)

上記のコマンドと同じですが、オートインクリメントの設定を個別に見れます。

SELECT auto_increment FROM information_schema.tables WHERE table_name = 'TABLENAME';
+----------------+
| auto_increment |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

オートインクリメントを設定する(1)

今回はおかしなことになっていて、すでにidカラムが5000ぐらいになっていたので、その次が付番できるように設定します。

ALTER TABLE TABLENAME AUTO_INCREMENT=5001;

今の最大値の次に付番される番号を設定します。
これで上記確認方法で確認すると、オートインクリメントの値が設定値になっている筈です。
とはいえ、これで再設定できない場合がありました。

オートインクリメントを設定する(2)

上記方法でauto_incrementが再設定できない場合は、一回リセットしてから、再度設定します。

alter table TABLENAME change COLUMNNAME COLUMNNAME int not null;
alter table TABLENAME auto_increment = 1;
alter table TABLENAME change COLUMNNAME COLUMNNAME int not null auto_increment;

元々changeはカラム名とか属性を変更できるコマンドなので、カラム名そのままのときは、同じカラム名を連続して書いてあげます。
オートインクリメント自体の設定をいっかい外して、値を再設定して、それからまたオートインクリメントを設定するという段取りです。


後でわかったことですが、phpmyadminを使ってテーブルのコピーをした際に「AUTO_INCREMENT 値を追加する」にチェック入れないとオートインクリメントが初期値に巻き戻って1になっているということでした。