MySQL

MySQL で date_add/sub で年が 0000 になると 0000-00-00 が返る

MySQL の sql_mode で NO_ZERO_IN_DATE,NO_ZERO_DATE とかを指定すると 0000-00-00 や、月や日が 00 などの日時の挿入を禁止できます。 MySQL の NO_ZERO_DATE と NO_ZERO_IN_DATE で無効な日時を禁止 がしかし、年が 0000 の場合の動きが謎いです。 MySQL …

MySQL の where 狙いと order by 狙い

わたしは参加していないのですが YAPC::Asia Tokyo 2014 で次のような発表があったようです。 Where狙いのキー、order by狙いのキー このセッションを聞いている人を WHERE gender = 'male' なら ORDER BY を狙った方がいいだろうし、WHERE gender = 'female…

MySQL で ORDER BY 狙いのインデックスがある列で NULL を下に持ってくる

MySQL で ORDER BY で NULL を下に持ってくるには下記の記事のような SQL で出来ますが、 MySQL の ORDER BY で NULL を先にもってきて NULL 以外は降順にする « をぶろぐ MySQL で NULL を一番最後にして昇順にソートする | Sun Limited Mt. MySQLでNULLを…

mysql_use_result と Server Sent Events でデータをストリーム的にブラウザに返す

まず下記のような手順で 1000 万件のレコードを持つテーブルを作ります。 MySQL に大量のデータを手っ取り早く挿入する - Qiita $ mysql test -e " create table t ( id int not null primary key, str text not null ) " $ seq 10000000 | mysql test -e " …

MySQL でクライアントが突然死したらロックが残ったままになった

珍しい現象に遭遇しました。冷静に考えると当たり前のことだし、MySQL に限ったことでは無いと思いますが。 MySQL のサーバとクライアントが別のホストになっているものとします(仮に DB サーバ と AP サーバ)。 192.0.2.1 => DB サーバ 192.0.2.2 => AP …

MySQL で sql_mode が Strict なときの ENUM 型

下記の2つの記事を読んで「でも sql_mode が Strict なときに ENUM に Invalid な値? 整数値が 0 である値? を合法的に入れる方法あったっけ?」と思ってやってみたら妙なことに・・・ MySQLにおいて真とされる値を調べた - tagomorisのメモ置き場 メモ:…

MySQL で実行計画によってデッドロックしたりしなかったりするクエリ

わりと珍しそうなデッドロックに遭遇しました。 BEGIN/COMMIT/ROLLBACK などのトランザクション関連のコマンドは使っていない 2接続で SELECT ~ FOR UPDATE だけでデッドロック 外部キー制約やトリガは使っていない 実行計画によってはデッドロックしない…

MySQL の `NO_ZERO_DATE` と `NO_ZERO_IN_DATE` で無効な日時を禁止

MySQL は、次のような変な日時を DATETIME に挿入出来てしまいます。 create table tt (dd datetime); insert into tt value (0); /* 0000-00-00 00:00:00 */ insert into tt value (''); /* 0000-00-00 00:00:00 */ insert into tt value ('9999-99-99 99:9…

LOCK IN SHARE MODE の使いドコロを間違えてデッドロック

先日、作っていたシステムでデッドロックがわりかし頻繁に発生することが判りました。 RDBMS のデッドロックは必ずしもバグでは無く、無理に対応するよりアプリケーション側でハンドリングしてトランザクションのリトライなどを行った方がいい場合もあると思…

外部キー制約の定義順で DELETE が成功したり失敗したり

2013/04/16 追記 次のようなテーブル定義がありました。 CREATE TABLE t1 ( a INT NOT NULL, PRIMARY KEY (a) ); CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a, b) ); CREATE TABLE t3 ( a INT NOT NULL, b INT NOT NULL, c INT NOT N…

remi リポジトリから入れた MySQL を CentOS リポジトリのものに戻す

諸事情で remi リポジトリから入れていた MySQL 5.5 を CentOS リポジトリの 5.1 に戻しました。 remi から MySQL をインストールする手順は下記の 方法2 標準リポジトリの優先度を remi より高く で行なっている前提です。 CentOS6 で remi から php や my…

MySQLのデフォルトのトランザクション分離レベルは SELECT がスナップショットを参照する

MySQL の InnoDB のデフォルトのトランザクション分離レベルは REPEATABLE READ で、ファジーリードしないようにトランザクション中の SELECT はトランザクションを開始してから最初の SELECT の時点のスナップショットから行われます。 そのため、次のやり…

CentOS6 で remi から php や mysql をインストールするための yum の設定

注:これは Qiita に投稿した内容と同じです epel リポジトリを追加します(remi-release-6 が依存している)。 rpm --import http://ftp-srv2.kddilabs.jp/Linux/distributions/fedora/epel/RPM-GPG-KEY-EPEL-6 rpm -ivh http://ftp-srv2.kddilabs.jp/Linux/d…

CentOS6 に MySQL 5.6 を RPM でインストール

MySQL 5.6 を試したくなったので CentOS6 に RPM でインストールしようとしたことろ、少し面倒なことになっていました。 RPM をダウンロードしてインストール 「MySQL :: Download MySQL Community Server」から RPM をダウンロードします。 wget http://dev…

グループ内の上位 N 件を抽出する SQL はストアドでいいんじゃない?

以前書いた「グループ内の上位 N 件を抽出する SQL がうまく書けない(或いは SQL を show status で解析) - ngの日記」ですが、アプリケーション側でループで回す・・・というかストアドプロシージャでやればいいのでは? と思ったのでやってみました。Ora…

グループ内の上位 N 件を抽出する SQL がうまく書けない

この記事には続きがあります グループ内の上位 N 件を抽出する SQL はストアドでいいんじゃない? - ngの日記 MySQL で簡単に出来そうで出来ない SQL に「グループ内の上位 N 件を抽出する SQL」があります。年に 1 回ぐらいこの問題について考え、毎回同じ…

mysql コマンドのタブでの入力補完を無効にする

普段 SQL を書くときは基本的にタブ文字は使っていないのですが、たまに SQL にタブ文字が含まれていると mysql コマンドのコンソールに貼りつけたときに、タブ文字が入力されなくてうまく貼り付けられないことがあります(「AB」→「AB」のように文字が繋が…

MySQL の RAND 関数の引数

MySQL の RAND 関数の引数の意味がよくわからなかったので色々試してみました。 MySQL :: MySQL 5.1 リファレンスマニュアル :: 11.4.2 数学関数 引数に定数を与えて複数回呼び出すと毎回同じ値が返されます。同じシードで初期化していることになるので当然…

MySQL 5.5.22 と 5.5.24 のステートメントベースレプリケーションの変更点

MySQL 5.5.22 と 5.5.24 でステートメントベースのレプリケーションで気になる変更点があったので調べてみました。どちらも binlog_format = statement でテストしています。 MySQL 5.5.22 Statements that wrote to tables with AUTO_INCREMENT columns bas…

innodbでサブクエリを使ったときの FOR UPDATE のロックの範囲

前提として、次の通りテーブルを使用する。 CREATE TABLE A ( id INT NOT NULL, no INT NOT NULL, PRIMARY KEY (id, no) ); CREATE TABLE B ( id INT NOT NULL, PRIMARY KEY (id) ); 普通に結合すると A と B の両方の行がロックされる。 SELECT * FROM A IN…

mysqld_multiで複数のインスタンスを立ち上げ

1つのサーバで複数のMySQLのインスタンスを立ち上げるために mysqld_multi を使ったときのメモです。 使用した版は 5.1.61-1.el6_2.1 (CentOS6) です。 設定ファイルを作成します。 vi /etc/my.cnfとりあえず以下の様に作成します、基本的な設定のみです。 …

ストレージエンジン混在でレプリケーション

ストレージエンジンで MyISAM と InnoDB が混在している環境でレプリケーションしたらどうなるか気になったのでやってみました。 前提条件 5.5.16 で、ステートメントベースでレプリケーションしてます。 テーブル定義 CREATE TABLE ii ( id INT NOT NULL PR…

VARCHAR と 〜TEXT の違い

長年知らなかったが、「VARCHAR」と「〜TEXT」では最大文字数の意味が違っていた!!! 試してみる CREATE TABLE t1 (s VARCHAR(255) NOT NULL); CREATE TABLE t2 (s TINYTEXT NOT NULL); INSERT INTO t1 VALUES ( REPEAT('a', 1024) ), ( REPEAT('あ', 1024…

MySQLのinnodbでテーブルロックを使う

テーブルロックとトランザクションは排他的にしか使えないと思っていたが*1自動コミットをOFFにするとテーブルロックとトランザクションを同時に使えるらしい。 http://dev.mysql.com/doc/refman/5.1/ja/lock-tables.html トランザクションとテーブルロック…