InnoDBのAuto Incrementsの問題
今回、InnoDBとMyISAMの
Auto Increments(以下AI）の挙動に対する
対処方法についての質問になります。
以前は5.1のMySQLを使用していましたが、
最近利用しているホスティング会社の方で変更があり、
MySQL5.6へのバージョンアップが行われ、
そこから発生している問題です。
例えば
testtb
-------------
id item
-------------
1 aaa
2 bbb
3 ccc
-------------
というテーブルがあります。（InnoDB）
idはプライマリーキーでAIが設定されています。
基本的にこのテーブルでは
itemのアップデートくらいで、
レコードを削除するような動作は起こりません。
ところが管理者サイドのテストの際に
下記のように、でMAX(id)を抽出してそのレコードを削除し、
AIの値を削除したMAX(id)に変更するという動作が稀に発生します。
$sql = "SELECT max(id) FROM testtb ";
$result = mysql_query($sql, $con);
$maxid = mysql_result($result, 0);
$sql = "DELETE FROM testtb WHERE id = '{$maxid}' ";
mysql_query($sql, $con);
$sql = "ALTER TABLE testtb AUTO_INCREMENT = {$maxid} ";
mysql_query($sql, $con);
要するにテストで登録した最新idのレコードを削除し、
AI値も元に戻してテスト前の状態に復旧するという内容です。
以前のMySQLのバージョンでは
これが問題なく動作していましたが、
MySQL5.6に変わってから
InnoDBの場合には、
ALTER TABLE testtb AUTO_INCREMENT = {$maxid}
このSQL文を実行しても結果が全く反映されません。
phpmyadminからも実行してみましたが
実行結果にエラーはでないのですが、
同様に結果は反映されず、AI値の変更が行われません。
少しググってみて、innodb_autoinc_lock_mode
の設定が関係しているのではと思い見てみると
innodb_autoinc_lock_mode = 1になっていました。
ちなみにこの値は共有DBのため、
ホスティング会社の方での固有の設定で
私の方で変更はできません。
上記の問題はMyISAMでは発生しませんので、
InnoDB固有の特徴によるものかと思います。
innodb_autoinc_lock_modeの設定変更なしで
上記を解消する方法はありますでしょうか？
もうMyISAMで運用するくらいしか方法が見つかりません。
それからこれに関連する内容で
もう一つ質問があります。
これはphpmyadminに関する質問なのですが、
以前ですと、テーブルを選んで
上メニューの「操作」という箇所から
AIの値が確認できて変更もできたかと思うのですが
現在はAIの値を操作する箇所が全く見当たりません。
確認のためにSHOW TABLE STATUSを実行する以外に
確認・変更のできる場所はありませんでしょうか？
ちなみにphpmyadminのバージョンは4.0.0です。
よろしくお願いいたします。
お礼
ibdataファイルを一度削除してから、mysqlを再起動することで解決しました。 ありがとうございました。