MySQL


2016年8月29日

mariadbが起動しない

SELinuxが有効だったというオチ。SELinuxは正しく設定できればよいのだが、難解で毛嫌いしてしまうところが問題。

$sudo tail /var/log/mariadb/mariadb.log
160828 20:10:36 mysqld_safe Starting mysqld daemon with databases from /(mariadb-path)/m
ysql
160828 20:10:36 [Note] /usr/libexec/mysqld (mysqld (mysqlversion)-MariaDB-log) starting
as process (processID) ...
160828 20:10:36 [Warning] Can't create test file /(mariadb-path)/mysql/(hostname).lower-test
160828 20:10:37 InnoDB: The InnoDB memory heap is disabled
160828 20:10:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160828 20:10:37 InnoDB: Compressed tables use zlib (zlibversion)
160828 20:10:37 InnoDB: Using Linux native AIO
160828 20:10:37 InnoDB: Initializing buffer pool, size = 128.0M
160828 20:10:37 InnoDB: Completed initialization of buffer pool
160828 20:10:37 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
160828 20:10:37 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid en
ded

2011年4月 7日

ORDER BY句におけるNULLの順番について

Mysqlでは、昇順(ASC)でORDER BYするとNULLは先頭に来る。降順の場合には、最後に来る。順番を逆にしたい場合には、COALESCE(列名, 型の最大値)を指定するなどの対応が必要である

一方で、Oracle Database 11gでは、昇順(ASC)でORDER BYするとNULLは最後に来る。降順の場合には、最初に来る。順番を逆にしたい場合には、NULLS FIRSTもしくはNULLS LASTを指定すればよい。

2009年7月17日

mysql-bin.000001が肥大化する

/varディレクトリへのディスク割り当てが100%になってしまい、MySQLサーバーの接続上限に達して、接続できないという現象が発生した。

調べてみたところmysql-bin.000001が1.8GBにもなっていたことがわかった。詳しく調べてみると、mysql-bin.NNNNNNはバイナリログになっており、レプリケーションを利用する際に使用される。

レプリケーションを使用しない場合(MySQLをサーバー1台で運用する場合)には、my.cnfの
log-bin=mysql-bin
をコメントアウトして再起動すればよい。

ログを削除する場合には、上記の設定変更を行う前に、rootユーザーでMySQLサーバーに接続後、
PURGE MASTER LOGS before now();
として安全にバイナリログを削除するとよい。

2009年7月 8日

MySQLが高負荷になる

クエリを発行するとmysqldのCPU使用率が100%になったまま張り付いてしまう現象が発生した。
MySQL/PostgreSQLで実行中のSQLを確認する方法を参考にして、show pricesslistクエリを発行して処理が完了していないSQLを確認してみたところ、やはり長時間処理が完了しないクエリが原因であった。

対象のクエリはexplain select ~で確認してみても、正しくインデックスが使われていることがわかったが、複数のテーブルを結合するSQLだったので、対象テーブルにおいてshow index from tablenameコマンドを発行すると、CardinalityがNULLになっていることがわかった。

おそらくこれが原因で発行したSQLにおいてインデックスが利用されないパターンであったと判断し、analyze table tablenameを発行して再度クエリを発行したところ、すぐに処理が完了した。

レコードが頻繁に追加されるテーブルは、手動でanalyze tableコマンドを定期的に発行したほうがよさそうだ。

2009年5月 4日

テーブルの定義を確認する

show columns from テーブル名;
でも確認できるが、外部キーなどの確認はできない。

MySQLのマニュアルにあるが、
show create table テーブル名\G;
とすることで定義を確認することができる。

なお、MyISAMでは外部キーは使用できず、alter table テーブル名 add foreign key 外部キー名(カラム名) references 参照テーブル名(参照カラム名)としても無視されてしまう。

2008年2月 4日

検索結果が正しくない

なぜか「中」を含む文字列を調べようとしているのに、「団」とか「台」が検索されてしまう。
どうやらMySQLをrpmでインストールするとlatin1がデフォルト文字コードセットになってしまっていてそれでマルチバイトの検索がうまくいかないことがあるようだ。

現在の文字コード設定をチェックしてみる。
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

binaryというのは無変換を示している。現在は、入力された文字がlatin1と見なされ、latin1のデータベースに登録されるため、例えばEUCの文字で登録した場合でもデータが破壊されることはない。

データベースのデフォルト文字コードをEUCにするためには、/etc/my.iniに以下のように設定して再起動すればよい。

[mysqld]
default-character-set=ujis
skip-character-set-client-handshake

何もデータがない状態であれば、これでよいのだが、もうすでに運用済みの場合にはこれではデータが破壊されてしまう。(登録済みデータがlatin1なのに変更後はEUCと見なされて処理されるため)

不幸なことに、自分はUTF-8で運用しているデータベースもあれば、EUC-JPで運用しているデータベースもあるため、一括で変換することも難しい。

今回はいろいろ調べた結果、特定のデータベースの文字コードセットだけを変更する方法があったので紹介したい。hogehogeデータベースに対してlatin1からEUC-JPへ変換する方法をご紹介する。

#ダンプ(hogehoge.dmp)を取得する。
mysqldump --default-character-set=binary -u root news --password=XXXX > hogehoge.dmp
#MySQLへログインし、データベースのcharacter_set_databaseを変換する。
alter database hogehoge character set ujis;
#hogehoge.dmp の中のSQL文にcharsetが指定されている箇所があるので、viなどで開きlatin1からujisへ置換しておく。
mysql -u root -p hogehoge --default-character-set=ujis < hogehoge.dmp で取り込む

これで正しく取り込めるのだが、問題はクライアントの問い合わせ方法になる。
alter database news character set ujis; のSQLで変更されているのはcharacter_set_databaseだけであることが分かる。

mysql -u root -p hogehoge
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

このままだと、発行されるクエリはlatin1と見なされるが、データベースの内部文字コードはEUCなので、SELECT文はマッチしないだろうし、INSERT文などを発行すればデータが破壊されてしまう。
そこで、以下のSQLを事前に発行すると・・・

mysql> set names ujis;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | ujis |
| character_set_connection | ujis |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | ujis |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

となる。つまり、SET NAMES ujis文によってcharacter_set_client、character_set_connection、character_set_databaseがEUCとなる。ちなみにcharacter_set_serverはデフォルト文字コードセットなので、latin1となっているが特に問題は発生しない。またcharater_set_systemはUTF-8固定でありこちらも処理上は問題はない。

※SET NAMES ujis; とおなじクエリを接続時にパラメータとして渡すこともできる。
mysql -u root -p hogehoge --default-character-set=ujis
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | ujis |
| character_set_connection | ujis |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | ujis |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)


もしPHPなどのプログラムによってクエリを発行する場合には、データベースへ接続後
set names ujis;
を実行する必要がある。

今回の問題について非常に有益となったサイトを最後にご紹介する。
http://www.mysql.gr.jp/frame/modules/bwiki/index.php?FAQ#fb74bab6
MySQLの文字コードに関するメモ

2006年11月29日

Mysqlに関する管理コマンド

#rootユーザーのパスワードを設定
mysqladmin -u root password '(password)'

#DBの追加
mysql -u root -p
mysql> CREATE DATABASE hogehoge
なお、5.0では大文字小文字を区別するらしく、create databaseはエラーになる。

#ユーザーの追加(すべてのDBに権限を付与の場合)
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO (username)@localhost IDENTIFIED BY '(password)';

#ユーザーの追加(hogehogeデータベースの任意テーブルに権限を付与する場合)
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON hogehoge.* TO (username)@localhost IDENTIFIED BY '(password)';