文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

mysql

復活したSchemaSpyでDB定義書を自動生成してnginxで確認するdocker環境を作ってみました

やってやった

docker for macでMySQLコンテナを生成してリモート接続する

ついにGAになったdocker for macとdocker for windows。これでVirtualboxやVMWareを別途インストールする事なくdockerが利用可能になりましたね!

ansibleを学ぶ:vol06:mysql-serverのインストールからcreate databaseまでを自動化する

yum install mysql-xxx時のコンフリクト、yum install MySQL-python時のコンフリクトにも対応してますよ〜

dockerでローカルに複数バージョンのMySQLサーバを一括で生成する!

前回手動でdocker runしてたので、今回はdocker-composeで一括で複数バージョンを用意しちゃいますよ〜

MySQLのIllegal mix of collations (latin1_swedish_ci,IMPLICIT)のエラーについて

以外と原因に気づかなかったりするんですよねこれ。

macにdocker-machineで使い捨てのmysqlサーバをたてる

dockerならローカルに複数バージョンの使い捨てのMySQLサーバをたてられるので便利なのです。

macでMySQL5.6をインストールするとportが3307になっている件

なんで3306じゃないんですかねえ・・

噂のMySQLクライアント「mycli」の挙動をGIFアニメと静止画で確認してみた

最近mycliというMySQLクライアントが流行りつつあるので、GIFアニメと静止画で挙動を確認してみようと思います。

Error: Table "mysql"."innodb_table_stats" not found.のバグに対応する

mysql5.6で、error.logに以下のログが出力される事があります。 2014-08-25 12:52:43 7f1c8e330700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 2014-08-25 12:52:43 7f1c8e330700 InnoDB: Recalculation of persistent statistics reque…

MySQL5.6のUsing a password on the command line interface can be insecureの対応

MySQL5.6を使っていると、以下をよく目にします。 Warning: Using a password on the command line interface can be insecure.これは言葉通り、コマンドラインからパスワード入力するのやめーや!ってことですね。 コマンドラインでパスワードまで入力する…

MySQL5.6のData truncation: Data too long for columnとJDBCドライバの罠

久々のMySQLネタです。今回はMySQL5.6で、datetime型のカラムへのinsertでエラーになる件についてです。 エラー内容 java側で生成した日付をdatetime型のカラムにinsertしようとすると、以下のエラーが発生しました。 Data truncation: Data too long for co…

flywayでDBマイグレーション!:調査編1:簡単な挙動確認

Gradle編が一段落したので、データベースのマイグレーションについて調査していこうと思います。 環境は、java・MySQLで開発している場合のマイグレーションする場合についてです。使用するツール使用するツールは flyway です。 Flyway: the agile database…

MySQLのload dataで日付型にnullをセットする

皆大好きLOAD DATAについてのお話です。 LOAD DATAコマンドはinsertと比較して10倍以上のパフォーマンスでデータを取り込む事ができます。 しかし、いろいろ癖があるため、扱いにくい点があります。本題は日付型にnullをセットする事ですが、この際いろいろ…

何故MySQLは中間・後方一致検索でインデックスが効かないのか

しょうもないトピックです。 MySQLは何故中間一致検索、又は後方一致検索するとフルスキャンしてしまうか解りますか? 簡単です。中間部分の索引を保持しても効率よくbtreeを辿れないからです。例えば現実世界の辞書を想像して下さい。 おまわりさんーーーー…

MySQLのストアドファンクションでisNotEmptyを実装する

MySQLで「空文字またはnullではない」という判定をしたいけど、 hoge != null and hoge != '' と書くと冗長になってしまって嫌なので、 ストアドファンクションで実装してみる事にしました。 javaのcommons-langのStringUtils.isNotEmptyと同じ使い方ができ…

インデックスを貼ればcount(*)は一瞬になるという誤解

MySQLの話ですが、 インデックス貼ってるからcount(*)は一瞬だぜ! という事をドヤ顔でいう人がいますが、完全に誤解です。ストレージエンジンがMyISAMの場合はその通りで、100億レコードあろうと一瞬で件数が返ります。 それもその筈、MyISAMの場合はカウン…

「solrが解らないので実装できません」という言い訳が多い件

solrに限った話ではありませんが、 最近「solrが解らないので実装できない・見積もりできない」という言い訳を沢山耳にします。このセリフを吐く人は大抵solr以外の事もほとんど解ってないです。 solrが解らないから他も解らない事にしたいのか、と思ってし…

MySQLのストアドプロシージャで名寄せする

最近「電話番号」または「メールアドレス」が同一の場合同一人物とみなす、 という処理が必要になったので、MySQLを使って名寄せする処理を書きました。頭がいい人たちは非常に高度で少ない手順で名寄せする方式を組み上げているかと思います。 が、私のよう…

my.cnfの変更点のせいでMySQL5.6がなかなか起動しない件

つい先日MySQL 5.6 GAが公開されたので、早速macにインストールしてみました。 DMG形式インストールしたので、インストールで躓くことはありませんでした。 .bash_profileにmysql/binのパスも通しました。 さて、起動です。 ( #^^) 「今日も平常運転ですねMy…

MySQLでauto_incrementを使わず自力で採番する

MySQLにはauto-increment-incrementとauto-increment-offsetという設定があって、 auto_incrementの値を偶数・奇数に固定する事ができます。[server01] auto-increment-increment = 2 auto-increment-offset = 1 [server02] auto-increment-increment = 2 au…

DBの定義を更新した際のデプロイ順序

これは基本的ではありますが、非常に重要です。 サイトをグローバル公開し、アプリを止めずにデプロイする際のDB更新についてです。3パターン考えてみましょう。(O/Rマッパー、s2jdbcを使っている前提の話となります)※ 追加・・・テーブルの追加・カラムの…

tree-tips更新しました:【シェルスクリプト版】MySQLのキャッシュヒット率計算ツール

前回の記事に書いた通り、シェルクリプト版のMySQLキャッシュヒット率計算スクリプトを用意しました。コマンド1発で実行できるので、けっこう便利ですよ。 tree-tips: 【シェルスクリプト版】MySQLキャッシュヒット率計算ツール | MySQL tree-tips: 【WEB版…

tree-tips更新しました:【WEB版】MySQLのキャッシュヒット率計算ツール

仕事でMySQLのキャッシュヒット率を算出する事がたまにあったりするのですが、 計算式を忘れたり、一々excelに入力して計算していました。 面倒くさいのでツール作りました。tree-tipsにMySQLのキャッシュヒット率計算ツールを作ってみました。これはWEB版で…

solrで緯度経度をボックス検索する

solrに限った話ではありませんが、 最近緯度・経度を使って、ボックス(四角形の範囲内)検索をする要件が増えています。 方法はいくつかありますが、やりやすい方式を上げてみます。 緯度をbetween検索、かつ、軽度をbeween検索。 この方式が一番汎用性があ…

MySQLのgroup_concatで複数レコードを1行にまとめる

全然知らなかったのですが、mysqlでは複数レコードを1行にまとめる事ができます。 複数行をカンマ区切りにしたり、結構有効に使えそうです。早速サンプルコードを。 mysql> create table gc1(id int auto_increment, uid int, name varchar(30), primary key…

group_concatで複数レコードを1行にまとめる:その2

group_concatで複数レコードを1行にまとめる:その2 - 文系プログラマによるTIPSブログ前回に引き続き、複数カラムをgroup_concatしたらどうなるか気になったので試してみます。 mysql> create table gc2 (id int auto_increment, uid int, v1 varchar(50), …

offsetの罠:その3:もっと対策

懲りずにmysqlのoffsetの遅さに立ち向かいます。 今回は少し実践っぽくするため、以下の住所データを使用して、offsetを使ったクエリの高速化を目指します。 住所データTSVまず、以下のようにcreate tableをします。 drop table if exists address; create t…

MySQLで複数レコードからカンマ区切りの結合文字列を生成する

複数のカラムをカンマ区切りで結合するケース、よくありますよね。 今回はmysqlで実装してみます。非常に簡単です。concat_wsを使うだけです。 mysql> select concat_ws (',', 'aaa', '', 'bbb', null, 'ccc'); +------------------------------------------…

updateでjoinを利用する

update文でも普通にjoinできます。 謎の更新クエリですが、こんな書き方も可能なのです。 update test t left outer join test1 t1 using (hoge) left outer join test2 t2 using (hoge) left outer join test3 t3 using (hoge) set t.fuga_1 = t1.hige_1 ,t…

MySQLで複数項目を一気にnull・空文字チェックする

相当邪道で、いつできなくなるか解らないクエリの書き方になりますが、 null・空文字チェックをする方法を考えてみました。正直かなり邪道だと思います。。hogeとhigeカラムがnull・空文字でない事をチェックするクエリを普通に記述すると、 こんな感じの冗…

全テーブルをカウントする

全テーブル・ビューのカウント結果を取得する - treeのメモ帳 前回はビューを含む、全テーブル・全ビューのカウントをしました。今回はテーブルのみの全カウントですが、SQL一発で確認できます。 mysql> select table_name, table_rows from information_sch…

DBViewerのmetadata lockの罠:その2

DBViewerのmetadata lockの罠 - treeのメモ帳 MySQLのmetadata lockですが、前回はロック解除ためにサービスを再起動していました。 今回はサービスの再起動ではなく、プロセスをシェルスクリプトで自動的にkillする事で対応してみます。metadata lockされて…

MySQLのshow processlistを数秒おきに実行してSQLを監視する

watchコマンドを利用して、mysqlのshow processlistを数秒おきに実行することができます。 こんな感じです↓引数にインターバルを指定できるようにしています。 #!/bin/sh interval=$1 # インターバルの単位は秒で初期値は1秒 test -z $interval && interval=…

load dataとトランザクション:まとめ

前回のトピック、自分で見なおしてちょっと解りづらかったので、もう一度まとめてみようと思います。 お題は、LOAD DATAとトランザクション、です。まず、使う材料は以下の通り。engineとload data localは適宜書き換えます。 1 11 111 2 22 222 1 33 333CRE…

MySQLのエディションを見て気づいたこと

MySQLを商用利用するためには、MySQL Community Editionは使用できません。 必然的に商用版を選ぶことになり、以下の3つから選ぶことになります。 MySQL Standard Edition MySQL Enterprise Edition MySQL Cluster Carrier Grade Edition7 http://www-jp.mys…

ストアドプロシージャのログを頑張って出力する

mysqlのストアドプロシージャは、SQLをログ出力できません。 出力されるのは、call proc_name(1, 2, 3); という呼び出し部分のみです。 本当はプロシージャ内のSQLの内容を出力したい場合が多い。そこでちょっと考えました。ログ出力するためのプロシージャ…

AmazonRDS:ログ関連とログローテート

クラウドDBとして有名なAmazonRDSのログについてのお話。RDSはサーバのファイルシステムに直接アクセスする事ができません。 つまり、通常のクエリログ・slow-query-logをファイルとして出力できず、テーブル出力のみとなります。 ログテーブルは、mysqlスキ…

DBViewerのmetadata lockの罠

罠シリーズ。eclipseのpluginであるDBViewer、結構有名なプラグインです。 今回はDBViewerとMySQL5.5の罠を紹介。DBViewerには、自動コミットモードと手動コミットモードが選択できる。 手動コミットモードには大きな罠が潜んでいる・・■罠の内容 対象テーブ…

MySQLのPK・UNIQUEのフィールド長制限に立ち向かう

MySQLで、InnoDBでPK・Uniqueをはるカラムには、フィールド長の制限があります。 俗に言う767byte問題です。 プリフィックスは最高で1000バイトの長さまで可能です。(InnoDB テーブルは767バイト)非バイナリ データ タイプ(CHAR、VARCHAR、TEXT)では CREATE …

offsetの罠:その2:どれくらい遅いか検証

http://d.hatena.ne.jp/treeapps/20110330/1301500024MySQLのoffsetは遅いです。主に、offset値を見つけるまでの処理が遅いのです。 せっかくだから、今回はどのくらい遅いか試してみる。 PK1個で1000万件ほど入れてみよう。 mysql> desc lotest2; +-------+…

LOAD DATAを小刻みに行う

http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/ これに対抗してシェルスクリプトだけでLOAD DATAを小刻みに行うコードを書いてみる。 中途半端な引数だったり、MySQL関連の変数がベタ…

全テーブル・ビューのカウント結果を取得する

DBを管理していると、全テーブル・ビューのカウント数を取りたい時がある(よね?)。 毎回カウントするのがめんどいので、シェルスクリプトを書いてみた。 rootはパス無し前提なので、rootのパスが有る人は適当にいじってね。 #!/bin/sh schemaName=$1 if […

InnoDBでコミット前のデータのカウント結果を取得する

MyISAMの場合はカウントの数値はファイル保存されている為一瞬でカウント結果が返る。 しかしInnoDBはそうもいかない。 InnoDBテーブルで、大量データ投入時にどの位insertされたか知りたい時がある(かもしれない)。 その場合、トランザクション分離レベル…

LOAD DATAの罠:その3

続、罠シリーズ。環境:MySQL5.1.45、MacPRO、MacOSX10.6、CPU:Xeon2.8G X 4、MEM:4G今回はまた LOAD DATA について。 LOAD DATAには、LOCAL というオプションがある。この LOCAL オプション使用時のエラーについて、知っていないと解らない動作が有った。ht…

ストアドファンクションとレプリケーション

最近やってしまったミス。 レプリケーション環境下でlog_bin_trust_function_creatorsが0のままストアドファンクションを追加すると酷い目に合う。サーバ構成はこんな感じで、レプリケーションはマルチマスター構成。 【サーバA】web01 / db01 【サーバB】we…

SQLからTSVを生成

お手軽にTSVを作る方法。 eオプションの前に -N を指定してヘッダーを非表示にして、標準出力をテキストにリダイレクションするだけ。 ただし、文字コード・改行コードは環境依存なので注意。 mysql> select * from t_test2; +------+------+ | id | name | …

シェルスクリプトからSQLを実行する

一応基本を押さえておく。シェルスクリプトからSQLを実行する場合、いくつか方法がある。 eオプションで実行 SQLファイルをリダイレクション ヒアドキュメント ■-eオプション $ mysql -uroot test -e "select * from t_test" + id + 1 + ◯メリット ・手軽に…

offsetの罠:その1:少しだけ対策

続・罠シリーズ。今回はoffsetについて紹介。 selectした結果の1000件目から(offset)100件取得(limit)という感じで、1000〜1100件を取得する 一見すると非常に効率が良さそうに見える。しかしここに罠が潜む・・試しに10件のレコードから、以下のように2…

LOAD DATAの罠:その2

続、LOAD DATAの罠を紹介。 今回は LOAD DATA のトランザクションと強制コミットについて。初期状態はこんな感じ。 mysql> select * from t_del; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)mysql> select * from t_test; Empty set (0.00 s…

LOAD DATAの罠

LOAD DATAは高速で非常に使い勝手がよいが、いくつか注意する点がある。 今回は LOAD DATA とエラーコードの関係について紹介。 試しにシェルスクリプトからLOAD DATAを実行する。読み込むファイルはわざとDuplicate Key Errorが出るようにしておく。<t_tes…