MySQL のテーブルコピーでやらかしてしまった件

はい、タイトル通り、今回はやらかしてしまった件です。
幸いにも被害は自分だけに留まったので大目玉をくらう前にコッソリ修正できましたが…

で、何をやったかというと、データごとテーブルのコピーを取るのに「CREATE TABLE test_t SELECT * FROM current_t」と叩いて test_t テーブルを作成したわけですよ。
それで、test_t テーブルをいろいろいじってから「RENAME TABLE current_t TO old_t, test_t TO current_t」として現行のテーブルと入れ替えました。
念の為にちょこっと動かしてみて、問題が無いようなのでそのまま利用することにしました。

悲劇はその後に起こったのです!

機能の追加を行った後で動作確認をしようと、テーブルに新規レコードを追加してからそのレコードを検索したところ… 見付からない…?
慌てて同じ条件で SQL コマンドを叩くと、ちゃんと居る…?
まさか幽霊かと思っても、この相手には見て確かめるための足はありません。
首を傾げながらレコードを消しては作り、消しては作り… 何度目かにふと思い立って SELECT * で全カラムを表示させてみました。
するとそこには… ああ、今思い出しても恐しい… プライマリキー値 0 (ゼロ)が居たのです!!

この時の動作は、レコードを探してそのプライマリキーを得るというものでした。その関数はエラー時に FALSE を返します。
ここで親切な PHP さんは、boolean の文脈では空文字列や数値の 0 (ゼロ)も FALSE だと判断してくださいます。
賢明な読者様にはもうお分かりでしょう。上の新規レコードが返すプライマリキー値 0 はエラー処理ルーチンの方へ入ってしまうのです。

まあ、ここまでは比較的よくある話とも言えます。
しかし、今回の真の敵はコイツであるはずが無いのです。何故ならそのカラムには AUTO_INCREMENT 属性が付けてあったのです。
つまり、新規レコードには自動的に新しい ID値が付与されるため、値 0 が設定される事実とは矛盾します。
この矛盾が今回の密室殺人の謎を解くカギになるはずだっ!(違)

と、ここまでが前置きです。
本題は「SHOW CREATE TABLE current_t」を実行した時に発見した驚愕の事実にあります。
コピー元(リネーム後の old_t)では「NOT NULL PRIMARY KEY AUTO_INCREMENT」となっていた部分が「NOT NULL DEFAULT ‘0’」に変わっているではありませんか。
確かにこれならプライマリキー値を指定しない新規レコードで、その値がデフォルトの 0 になるのは納得です。

実はテーブルのコピーを行う SQL 命令はもう一つあります。
こちらはテーブルの形だけコピーし、レコードは拾って来ない「CREATE TABLE new_t LIKE current_t」というものです。
この命令では、上で問題になった部分も元のまま「PRIMARY KEY AUTO_INCREMENT」で引っ張ってくれます。

以上から導き出される結論が「テーブルの全コピーを取るには CREATE TABLE LIKE で定義をコピーしてから INSERT INTO SELECT でレコードをコピーするという二段階で行うべし」というものです。
まあ、それ以前に作業した後にはちゃんと確認しろ(この場合は SHOW CREATE TABLE)という事なんですが…

ということで、今回の(イタい)レポートはこれで終わります。

phpMyAdminの環境保管領域が完全に設定されていない… エラー

おひさしぶりです。
不定期開催の今回のネタは phpMyAdmin on Ubuntu 14.04 LTS です。

とりあえずパッケージ入れてみたんですよ。
そしたら、ログインすると「phpMyAdmin 環境保管領域が完全に設定されていないため、いくつかの拡張機能が無効になっています。理由についてはこちらをご覧ください。」と表示されたわけです。
コマンドラインから見てみると、環境保管領域に使われる phpmyadmin データベースが無かったので CREATE TABLE して…ダメ。
中身を入れればいいのかと /usr/share/doc/phpmyadmin/examples/create_tables.sql.gz を流し込んでみても…ダメ。
権限の問題かと CREATE USER と GRANT を駆使しても…ダメ。

…結局、/etc/phpmyadmin/config.inc.php の中身で環境保管領域用のテーブル名を定義している部分で「_」(下線、アンダースコア)が一つ不足していたのが原因でした。
直接そのファイルを直すかわりに /etc/phpmyadmin/conf.d/env-storage.php を以下の内容で作りました。
ほとんどは /usr/share/doc/phpmyadmin/example/config.example.inc.php からコピーしたものです。


<?php // ここから

$i = 1; // インデックスを 1 に戻して(1-ベースなので最初が 1)
/* Storage database and tables */
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
/* Contrib / Swekey authentication */
// $cfg['Servers'][$i]['auth_swekey_config'] = '/etc/swekey-pma.conf';
?> // ここまで

それから、試行錯誤している時に /etc/phpmyadmin/config.inc.php を見ていたところ、設定ファイルは次のような順序で読み込まれるようでした。(後ろにある内容が優先)

  1. /var/lib/phpmyadmin/config.inc.php (phpMyAdmin/setup で変更されるファイル)
  2. /etc/phpmyadmin/config-db.php
  3. /etc/phpmyadmin/config.inc.php (今回の悪の元凶)
  4. /etc/phpmyadmin/conf.d/*.php (設定を追加した場所)

…以上で今回のレポートを終わります。

MariaDB を Debian6(squeeze) に入れるのが大変だった

知り合いから頼まれて MariaDB を Debian6 に入れたんですが、公式のインストール方法(apt リポジトリ追加 → apt-get update → apt-get install mariadb-server)では最後の方で止まってしまい、悩みました。
というわけで、うろ覚えながら方法をメモしておきたいと思います。

まずは公式の方法でインストールに挑戦します。
そうすると「subprocess installed post-installation script returned error」とかが出てエラー終了するはずです。(出ないで成功した貴方はラッキーですね)
続きを読む

MySQL の varchar 最大文字数について

掲題の件でちょっと引っかかってしまったので、忘れないようにメモしておきます。

MySQL のリファレンスを見ると、「10.5 データタイプが必要とする記憶容量」ページに 『varchar 型カラムの有効最大長は 65532 まで』と書いてあったので、素直に「varchar(65532)」で使おうとしたら怒られました。
というか、勝手に mediumtext に変更されてしまいました。

仕方無いので色々試していたところ、なぜか「varchar(20000)」だと通ったので、その後に alter table でカラム長を varchar(25000) に増やそうとしたところで 「最大 21845 までしか使えへんで(意訳)」というお叱りを受け、そういえば UTF-8 が最大3バイト食うんだっけと思い出して 3 × 21845 を計算してみたら、ぴったり 65535 だったというお話でした。

つまり varchar のサイズ指定は、最大でも「65535 ÷ 文字のバイト数」までしか使えないという教訓を得たのでした。
あと、text系のカラムでも「長さ=文字数×文字のバイト数」で計算されるようです。上の例で text よりも長い型である mediumtext (2^16(=65536) < L < 2^32) に変換されていた事から気付きました。