mysql - cles::blog (2ページ目)

blogs:
cles::blog
NP_cles()

» ArchiveList (Tag for "mysql"

)

«Prev || 1 · 2 · 3 · 4 · 5 · | Next»

2018/02/10

ActiveRecord で ROW_FORMAT=DYNAMIC を自動でつけるようにするには

ruby

mysql

ActiveRecord でテーブルが CREATE TABLE されるときに、ROW_FORMAT=DYNAMIC を自動的に追加する方法を見つけたのでメモ。
とりあえず RAILS_ROOT で以下のシェルスクリプトを流せば OK です。

cat << 'EOS' > config/initializers/ar_innodb_row_format.rb
# https://qiita.com/kamipo/items/101aaf8159cf1470d823#comment-6a9c50b52e94b8d2cc08
module InnodbRowFormat
  def create_table(table_name, options = {})
    table_options = options.merge(options: 'ENGINE=InnoDB ROW_FORMAT=DYNAMIC')
    super(table_name, table_options) do |td|
      yield td if block_given?
    end
  end
end

ActiveSupport.on_load :active_record do
  module ActiveRecord::ConnectionAdapters
    class AbstractMysqlAdapter
      prepend InnodbRowFormat
    end
  end
end
EOS

† 参考

ActiveRecordをutf8mb4で動かす - Qiita

at 19:33 |

2018/02/10

MySQL/MariaDB に Error 1709: Index column size too large. The maximum column size is 767 bytes. と言われた時は

mysql

charset

MySQL や MariaDB で文字コードを utf8mb4 にしようとすると以下のようなエラーに遭遇することがあります。

Error 1709: Index column size too large. The maximum column size is 767 bytes.

例えばこんな感じでデータベースを作ったときですね。

CREATE DATABASE hogedb CHARACTER SET utf8mb4;

これは InnoDB の最大キー長が 767 バイトに制限されているため。
この制限はある条件をクリアすると 3072 バイトまでに緩和^*1することができます。

その条件は row_format が DYNAMIC もしくは COMPRESSED （これを設定するためには innodb_file_format が Barracuda であることが必要になります）であり、innodb_file_per_table、innodb_large_prefix が有効になっていること。具体的には my.cnf に以下のような設定を記述するだけです。ちなみに以下の設定は既に作成済みのテーブルには効果がないので、設定変更後にテーブルを作り直す必要があることに注意が必要です。

/etc/my.cnf.d/server.cnf

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix = on
innodb_default_row_format = DYNAMIC # この設定が効かない場合には以下を参照

最後の行の設定ができない場合には、テーブルの CREATE 文に以下のような感じで ROW_FORMAT=DYNAMIC を追加してやる必要があります。

CREATE TABLE `hogetable` (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

^*1: 第32回　InnoDBインデックスの最大キー長について：MySQL道普請便り｜gihyo.jp … 技術評論社

at 16:10 |

2017/01/24

MySQL の定期バックアップを取る sh スクリプト

mysql

systemmanagemant

オペミス等が怖いので MySQL のバックアップは日時で取るようにしています。
バックアップも単に取り続けるとディスク容量を圧迫してしまうので、最大10個に制御されるようなスクリプトを書いてみました。

mysqlbackup.sh

#!/bin/bash
SCRIPT_DIR=`dirname $0`
cd $SCRIPT_DIR

yyyymmdd=`date +%Y%m%d`
SNAPCOUNT=10

mysqldump -u root --opt --add-drop-database -A | xz -T 0 > mysqldump_$yyyymmdd.sql.xz
find . -name 'mysqldump_*' -type f | sort -r | tail -n +$SNAPCOUNT | xargs --no-run-if-empty rm -f

このスクリプトをサーバに仕込んで cron などで定期的に呼出すようにしてやれば気軽にバックアップが取れます。
root のパスワードはスクリプト中に書かずに ~/.my.cnf に書いておくと便利です。

~/.my.cnf

[client]
password="ここにパスワードを書く"

at 19:12 |

2016/11/12

MySQL で implode() のようなことがしたい

mysql

php

MySQL （MariaDB）で PHP で言うところの implode() をやりたいと思っていろいろ調べてみたら、GROUP_CONCAT()^*1 を使えば一撃で実現できることが分かったのでメモ。

例えば以下のようなテーブルがあったときに GROUP_CONCAT() を使うとこんな感じになります。
GROUP_CONCAT() には DISTINCT や ORDER BY が効くところもポイントです。

SELECT * FROM foo;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+

SELECT GROUP_CONCAT(a ORDER BY a DESC SEPARATOR ',') AS aaaaa FROM foo;
+-------------+
| aaaaa       |
+-------------+
| 6,5,4,3,2,1 |
+-------------+

† カンマ区切りのデータの検索は FIND_IN_SET() で

カンマ区切りになった1つのフィールドから、一致するデータの個数を返してくれるのが FIND_IN_SET()^*2。

真っ当な正規化を行っていればこんな関数のお世話になる機会はないはずですが、テーブル設計の時に「どうせ検索条件としてこのフィールドは使わないはずだから、非正規形のままカンマ区切りでフィールドにいれちゃえ」なんていう無精をしたりするとお世話になることがあります。真っ当な解決方法としてはテーブル構造を正規化することですが、それがイヤな場合はFULLTEXT インデックスを使ったりしていましたが、一番お手軽に解決できるのが FIND_IN_SET() です。パフォーマンス的にはアレかもしれないので注意は必要ですけどね。

実際の例としては以下のような感じになります。FIND_IN_SET() の第１引数は検索する文字列、第２引数はカンマ区切りの文字列で、返り値は見つかった個数（見つからないときは 0 ）になります。GROUP_CONCAT() と違ってセパレーターが指定できないので、元になる文字列の構造には注意が必要です。

SELECT FIND_IN_SET('2','1,2,3,2,4,5,6') AS result;
+--------+
| result |
+--------+
|      2 |
+--------+

^*1: GROUP_CONCAT(expr) - 12.19.1 GROUP BY (集約) 関数

^*2: FIND_IN_SET(str,strlist) - 12.5 文字列関数

at 13:46 |

2016/09/13

MySQL にゼロデイ脆弱性（CVE-2016-6662）

mysql

cve

MySQL にゼロデイ脆弱性が見つかったようなのでメモ。

MySQLにゼロデイ脆弱性2件、デフォルトで影響、修正プログラムは未提供 -INTERNET Watch

脆弱性「CVE-2016-6662」は、ウェブアプリケーションへのSQLインジェクション攻撃や、管理ツール「phpMyAdmin」へのアクセスにより、MySQLの設定ファイル「my.cnf」を改変することで、ローカルまたはリモートから任意のコードを実行可能にするもの。

† MariaDB は対策版へのアップデートを

ちなみに MySQL と互換性がある MariaDB ではすでに対策版が公開されているようです。5.5.51 、10.0.27、10.1.17 のリリースノートに CVE と共に「[MDEV-10465] general_log_file can be abused」へのリンクがあり、ここに対応の詳細が記載されていました。

at 21:12 |

2015/12/26

MySQL 5.1 から 5.5 にアップグレードしてハマったこと

mysql

charset

MySQL 5.1 から MariaDB 5.5 にアップグレードしたら、デーモンが起動できなくなってしまいました。
落ち着いてエラーログを確認してみると以下のような行があり、my.cnf の default-character-set=utf8 がダメな様子。

151226  2:43:35 [ERROR] /usr/sbin/mysqld: unknown variable 'default-character-set=utf8'
151226  2:43:35 [ERROR] Aborting

同じ問題を抱えていた人のエントリを参考^*1に my.ini を以下のように書き換えたところ、起動させることができました。

[mysqld]
character-set-server=utf8

念のためもう一度エラーログを確認してみると、さらに以下のようなエラーを発見。

151226  2:48:21 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it

管理用のテーブルのスキーマも変わっているようですね。言われたとおりにコマンドラインから以下のコマンドを実行したところこちらも解消することができました。

mysql_upgrade -u root -p

^*1: MySQL 5.1 系と MySQL 5.5 系の文字コード - make world

at 03:12 |

2015/10/20

MySQL 5.7 が GA に

mysql

oracle

Oracle が MySQL 5.7 GA をリリースしました。

Oracle Announces General Availability of MySQL 5.7

Oracle today announced the general availability of MySQL 5.7, the latest version of the world’s most popular open source database. The new version delivers greater performance, scalability and manageability, plus enhanced NoSQL capabilities with JSON support and MySQL Router, which makes it easy to connect applications to multiple MySQL databases.

いろいろと機能強化されていますが、ハマりポイントもかなりありそうなので導入時には設定に十分注意したいところです。ただ、CentOS 7 などを使っているとコマンドは MySQL だけど実際に入っているのは MariaDB だった・・・というパターンも多いと思われるので、このへん違いも意外とスルーされる可能性もありますね。

at 22:01 |

2015/09/18

MySQL でユーザーに SSL を強制する

mysql

MySQL で接続時に SSL を強制させる方法を調べてたのでメモ。
すでにある username@% というユーザーを SSL 接続必須にするには以下のように指定します。

GRANT USAGE ON *.* TO 'username'@'%' REQUIRE SSL; -- SSLを強制する
GRANT USAGE ON *.* TO 'username'@'%' REQUIRE NONE; -- SSLでなくても接続を許可する

† GRANT USAGE ？

この GRANT 文のポイントになるのは、冒頭の GRANT USAGE の部分でしょうか。この USAGE の部分は本来、 ALL や SELECT などのユーザーの権限を表すものを列挙しなければならない部分ですが、この USAGE を使うと登録済みのユーザーの権限はそのままに、他のユーザーの属性を上書きすることができます。SSL の On / Off だけでなく、パスワードの変更などもこれでいけます。これまではバカ正直にユーザーを削除してから、再度登録しなおしたり、ユーザーの権限を調べて同じ権限を再指定したりしていたのですが、そんな必要はなかったのでした。

GRANT USAGE については MySQL のリファレンスに以下のような記載があるので、一度目を通しておくとよさそうです。

MySQL :: MySQL 5.1 Reference Manual :: 6.2.1 Privileges Provided by MySQL

The USAGE privilege specifier stands for “no privileges.” It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.

at 19:21 |

2015/08/03

replace() を使って MySQL のフィールド内容を一括置換する

mysql

MySQL のフィールドのある文字を他の文字に一括置換するときには、replace() を使うと一撃で済むのですがやり方をいつも忘れるのでメモ。
例えばテーブル table_name のフィールド field に含まれる http:// を https:// に書き換えるには下記のような感じになります。

UPDATE table_name SET field=REPLACE(field, 'http://', 'https://' );

† 参考

12.5. 文字列関数 - REPLACE(str,from_str,to_str)

at 18:11 |

2015/03/03

CentOS7 標準の MariaDB で base64 エンコード/デコードしたい

centos7

mysql

以前、CentOS 6.x で実現したPostfix + MySQL + postfixadmin + Dovecotを CentOS 7.x + Postfix + MariaDB + postfixadmin + Dovecot でやろうとしたら、思わぬところで躓いてしまいました。

かなり CentOS 7 の systemd や FirewallD などには慣れてきたので、プロダクトのインストールはスムーズに済んだのですが、動かしてみると MariaDB にうまくユーザー情報が書き込みされません。あれこれ調べてみると、CentOS 7 の標準の MariaDB では MySQL 5.6 では使えていた TO_BASE64() / FROM_BASE64() が使えないようです。

実際にコンソールで試してみるとこんな感じなのでしょんぼり。

MariaDB [(none)]> SELECT TO_BASE64('Maria');
ERROR 1305 (42000): FUNCTION TO_BASE64 does not exist

マニュアルをよく見ると、MariaDB 10.0.5 以降^*1って書いてありますね。仕方がないので、以前 URL エンコードをストアドで実現したときのように、今回もストアドで・・・・・・と思っていろいろ調べてみたところ、 UDF で実現できるというエントリを見つけたので、今回はこれを使うことにしました。

やり方は下記のサイトそのままでＯＫ。CentOS 7 の MariaDB で問題なく導入できました。