MySQL

SQL で範囲が記録されたテーブルから任意の範囲が隙間なくすべて埋まっているか得る

次のようなテーブルがあったとします。1 レコードが a~b の範囲を示しています。終端の端点は含みません。例えば 3~6 なら 3,4,5 を含む範囲です。 create table t ( id int not null primary key auto_increment, a int not null, b int not null ); inse…

INSERT 1文だけでもデッドロックするという話

下記の記事のように複数行に対する UPDATE は BEGIN の無い 1 文でもデッドロックすることがあります。 UPDATE1文だけでもデッドロックするという話 - tmtms のメモ これを見て思ったのですが INSERT であればバルクではない 1 行だけでもデッドロックするこ…

「入力した項目だけで検索」に IS NOT FALSE を使うなら被検索列が NULL にならないか注意

わりとよくある、複数の項目に検索条件が入力できて、入力した項目だけを検索条件として使用し、未入力の項目は検索条件には使わない、というフォーム、クエリビルダのようなものを使わないなら IS NOT FALSE を使うと便利です。 SELECT * FROM t WHERE ( a …

MySQL の行ベースレプリケーションで multi delete するとレプリケーションが止まる件

MySQL で行ベースレプリケーションしていて、特定の操作を行ったときにレプリケーションがエラーで止まる事象が発生しました。 そのときの調査のログや対応の方法のメモ。 原因調査 まず、レプリケーションのスレーブでレプリケーションの状態を見ました。 m…

docker-compose で MySQL レプリケーション環境をサクッと用意する

本番環境は MySQL のマスターとリードレプリカのスレーブで構成されているものの、ローカル環境はシングル構成なこと、よくあります。 そういうとき、レプリケーション遅延が原因による不自然な表示・・不具合が生じることがあったりするので、docker-compos…

PHP 製のデータベースのスキーマ定義を差分で適用するツール「dbdatool」

データベースのスキーマ定義の変更、いわゆるデータベースのマイグレーションついて、スキーマ定義の差分を SQL とかそれ用の DSL とかで作成し、リポジトリ管理してマイグレーションツールで実環境に適応するのが多いと思います。 この方法はわかりやすくて…

MySQL で年月(yyyy/mm)のデータ型と PERIOD_ADD/DIFF

いわゆる請求データみたいなやつで請求年月のような yyyy/mm の値を表すために DATE 側を使うか整数型で yyyymm みたいにするか。 DATE 型で日付を 1 固定で持っていたとして、例えば請求年月が 2018/01 という条件で検索するとき 請求年月 = 2018/01/01 だ…

MySQL Group Replication 素振り

公式のドキュメントを読みながら素振りしました。 MySQL :: MySQL 8.0 Reference Manual :: 18 Group Replication グループレプリケーションでは、グループのメンバシップ管理、ノードの障害検出、追加ノードの同期、などが自動で行われます。一方でアプリケ…

mysqldump で default-character-set とか hex-blob とか

とある MySQL のダンプファイルをインポートしようとしたところ、次のような警告が表示されました。 Warning (Code 1300): Invalid utf8 character string: 'FFFFFF' インポートに失敗しているわけではないですがやや気持ち悪いです。 原因 blob とかのバイ…

MySQL の GTID レプリを replicate-do-db でフィルタすると欠番になる?

スレーブ側で--replicate-do-や--replicate-ignore-などのルールを使ってフィルタリングをすると、GTIDに欠番ができて、連番が連続しなくなるため、SHOW SLAVE STATUSの出力が大変なことになってしまう。GTIDを用いるときは、フィルタリングしないのほうが無…

MySQL GTID レプリケーション素振り

従来のレプリケーションとの違いをざっくりと。 ノードに UUID が付与されて「UUID+連番」ですべてのトランザクションに一意なID(GTID)がつく 各ノードが「適用済の GTID」を持っているので循環レプリケーションやマスター切り替えが容易にできる 「適用済の…

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…