my.cnf

Posted by muchag | MySQL | 2017-12-11 (月) 0:38:39

【環境】
MySQL: 5.7.18
追記
/etc/my.cnf

[mysqld]
character_set_server=utf8
default-storage-engine=InnoDB
innodb_file_per_table
group_concat_max_len=10000000

[ mysql] # ショートカットタグの都合で半角スペースを入れてある
default-character-set=utf8

[mysqldump]
default-character-set=utf8
innodb_file_per_table

公式:14.5.2 InnoDB File-Per-Table モード
よくわからないので、先輩の仰せの通りに。。。

group_concat_max_len

デフォルトが 1024 バイトって、UTF-8 だと、全角340文字くらいね。
大分少ないのね~

参考サイト

Qiita:apache php mysqlをインストール(yum)(2015-09-07)

MySQL | 2017-12-11 (月) 0:38:39 |

タイムゾーン

Posted by muchag | MySQL | 2017-12-10 (日) 23:47:19

【環境】
MySQL: 5.7.18
経緯

CakePHP3 を利用しているときに、下記エラーが出た。

SQLSTATE[HY000]: General error: 1298 Unknown or incorrect time zone: ‘Asia/Tokyo’

調べてみると、MySQL のタイムゾーン設定がなされていないとのこと。

mysql データベースを見ると、確かに空っぽだった。


mysql> select * from mysql.time_zone;
Empty set (0.00 sec)
手順
Linux 環境
コマンド

mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root mysql -p
Enter password:
Warning: Unable to load '/usr/share/zoneinfo//iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//zone1970.tab' as time zone. Skipping it.
結果

3つ位失敗したみたいだけど、無事にタイムゾーンが mysql データベース time zone name テーブルへ登録された♪


select * from mysql.time_zone;
+--------------+------------------+
| Time_zone_id | Use_leap_seconds |
+--------------+------------------+
|            1 | N                |
|            2 | N                |
|            3 | N                |

            __ snip __

|         1778 | Y                |
|         1779 | Y                |
|         1780 | Y                |
+--------------+------------------+
1780 rows in set (0.00 sec)
参考サイト

有限会社 エス技研:MySQLのタイムゾーン(mysql_tzinfo_to_sql)の設定方法・XAMPP環境の解説も(2017-06-16)

MySQL | 2017-12-10 (日) 23:47:19 |

インストール

Posted by muchag | phpMyAdmin | 2017-12-10 (日) 11:39:12

いつも大変お世話になっている phpMyAdmin。
この度、初めてインストール作業を行うことになりました。

公式:Bringing MySQL to the web

【環境】
phpMyAdmin: 4.7.6
DL

上記公式サイト右上の [Douwload 4.7.6] をクリックすると DL できる。

2017-12-10 現在
phpMyAdmin-4.7.6-all-languages.zip

ダウンロードページ

または、ダウンロードページ から、詳細な選択をすることも可能。

配置

DL した .zip ファイルを解凍して、任意のディレクトリへ配置。

今回は、アプリケーションディレクトリ直下へ配置した。

また、ディレクトリ名が長いので
phpMyAdmin-4.7.6-all-languages → pma とリネームした。

ログイン

Cookie を有効にしておかないと、ログインできない。

設定

必須ではない。

設定ファイルの配置
/pma/config.sample.inc.php

をコピーリネームして

/pma/config.inc.php

を配置。

デフォルト設定
/pma/libraries/config.default.php

これがデフォルトの設定らしい。

パスフレーズ

今回はローカル環境なので、適当に。


$cfg['blowfish_secret'] = 'hoge'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
大問題!

上記設定をしたら、pma 側で下記エラーが出だ。

phpMyAdmin – エラー
設定ファイルのパーミッションが正しくありません。誰でも書き込み可能になっています!

現在の VirtualBox を利用した仮想環境においては
config.inc.php に、パーミッションを設定できないので
こうなるらしい。

config.inc.php を削除しました。。。

ここまで

その他の config.inc.php については
ko-iya528:phpMyAdminのインストール(2017-10-26)

テーマ

phpMyAdmin は、テーマの概念があり
テーマを変更することで外観を変更することができる。

意義

テスト環境と本番環境において
取り違えによる大事故を起こさないためにも
テーマは設定しておこうね!

策定

色々とあるのかもしれないけど
公式:Themes
で、配布しているので、こちらから頂戴する。

今回は、4.7.6 なので、4.7 の中から選択。

4種類ある中から、Metro 2.6 にした。

DL

metro-2.6.zip というリンクから DL。

配置

DL してきた .zip ファイルを解凍して
出てきた metro ディレクトリをそのまま

/pma/themes

へ配置。

設定
pma

ホームページ
「外観の設定」
Theme:[metro] を選択

Metro のテーマ
/metro/layout.inc.php

にて、Metro テーマのテーマを選択できる。(ややこしいw)


/* Theme color scheme
 * Values: "teal", "redmond", "blueeyes", "mono", "win"
 * Set this value for the desired color scheme
 */

$scheme                                     = "win";
カスタマイズ
/metro/layout.inc.php

に、色設定があるので、そちらをカスタマイズ。


case "win":

    $GLOBALS['cfg']['NaviColor']                = '#EEEEEE';
    $GLOBALS['cfg']['NaviBackground']           = '#377796';	// ページ上部ナビゲーションバーの背景色
    $GLOBALS['cfg']['NaviBackgroundLight']      = '#428EB4';
    $GLOBALS['cfg']['NaviPointerColor']         = '#333333';
    $GLOBALS['cfg']['NaviPointerBackground']    = '#377796';
    $GLOBALS['cfg']['NaviDatabaseNameColor']    = '#333333';
    $GLOBALS['cfg']['NaviHoverBackground']      = '#428EB4';
    $GLOBALS['cfg']['MainColor']                = '#444444';
    $GLOBALS['cfg']['MainBackground']           = '#FFFFFF';
    $GLOBALS['cfg']['BrowsePointerColor']       = '#377796';
    $GLOBALS['cfg']['BrowseMarkerColor']        = '#000000';
    $GLOBALS['cfg']['BrowseWarningColor']       = '#D44A26';
    $GLOBALS['cfg']['BrowseSuccessColor']       = '#01A31C';
    $GLOBALS['cfg']['BrowseGrayColor']          = '#CCCCCC';
    $GLOBALS['cfg']['BrowseMarkerBackground']   = '#EEEEEE';
    $GLOBALS['cfg']['BorderColor']              = '#DDDDDD';
    $GLOBALS['cfg']['ButtonColor']              = '#FFFFFF';
    $GLOBALS['cfg']['ButtonBackground']         = '#377796';
    $GLOBALS['cfg']['ButtonHover']              = '#428EB4';
    $GLOBALS['cfg']['ThBackground']             = '#F7F7F7';
    $GLOBALS['cfg']['ThDisabledBackground']     = '#F3F3F3';
    $GLOBALS['cfg']['ThColor']                  = '#666666';
    $GLOBALS['cfg']['ThPointerColor']           = '#000000';
    $GLOBALS['cfg']['BgOne']                    = '#F7F7F7';
    $GLOBALS['cfg']['BgTwo']                    = '#FFFFFF';
    $GLOBALS['cfg']['BlueHeader']               = '#3A7EAD';	// 注意書きなどの背景色
    break;
phpMyAdmin | 2017-12-10 (日) 11:39:12 |

ERROR 1452 (23000): Cannot add or update a child row: …

Posted by muchag | MySQL | 2016-01-19 (火) 13:58:54

【環境】
MySQL:5.5.27
状況

とあるテーブルに CSV データを入れ込もうとして下記エラーが出た。

エラー
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`database`.`table_b`, CONSTRAINT `[constraint_name]` FOREIGN KEY (`table_a_id`) REFERENCES `table_a` (`id`) ON DELETE CASCADE)

このエラーは外部キー制約に違反したデータを保持した状態で
当該テーブルを更新しようとすると出るとのこと。

原因

親テーブル(table_a)の取り込み時に、2行欠落していた。

解決

親テーブル(table_a)を取り込み直して解決。
かなすぃ。 XO

調査過程

あ~、またやってしまったと思い、
あれこれデータを調べてみたけど、不整合を見つけることができなかった。

検索

親テーブル(table_a)の ID は、1~365。
子テーブル(table_b)の table_a_id カラムに入っている値を
phpMyAdmin で検索して、1 より小さいもの、365 より大きいものを見てみたけど
検索結果は 0。

データ件数

ふと気になって、table_a のデータ件数を見てみたら
なんと! 364件。
あり? と思って、データを追ってみたら
ID:358 がない。。。

ログ

改めて、LAOD DATA LOCAL INFILE のログを見てみたら、あらら。。。

  1. Query OK, 363 rows affected (0.05 sec)
  2. Records: 365  Deleted: 0  Skipped: 2  Warnings: 0

じぇんじぇん見てなかったじぇ。。。
これを確認していれば、無駄な時間を取られずにすんだのに~。

Skipped: 2 なのに、364件あったのは。。。
い、今は気が付かなかったことにしましょ。。。

MySQL | 2016-01-19 (火) 13:58:54 |

HAVING

Posted by muchag | MySQL | 2015-12-28 (月) 9:49:07

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.2.9.2 JOIN Syntax

用途

グループ化されたデータ(GROUP BY)に対して
検索条件を指定する。

検索対象

よって、GROUP BY 句内のカラムか、または集約関数で使用されるカラムしか参照できない。

記述位置

HAVING 句は、すべての GROUP BY 句のあとで、かつすべての ORDER BY 句の前にある必要があります。

MySQL:13.2.9 SELECT 構文

この記事は、日本語版 5.5用はなかった。

適用順

HAVING 句は、ほぼ最後 (項目がクライアントに送信される直前) に最適化なしで適用されます。(LIMIT は HAVING のあとに適用されます。)

MySQL:13.2.9 SELECT 構文

エイリアス

カラムエイリアスを使用できる。

  1. SELECT COUNT(user_id) AS cnt
  2. FORM hoge
  3. HAVING cnt > 10;
MySQL | 2015-12-28 (月) 9:49:07 |

調査

Posted by muchag | MySQL | 2015-12-27 (日) 23:18:52

ちょ、ちょっと私にはハードルが高い。。。と思われる。
でも、少しずつでも触っていれば、理解していけるかもしれないので
いつもの通り特攻してみるメモ。

EXPLAIN

クエリの実行内容解析?
SELECT ステートメントの頭に EXPLAIN を付けて実行する。

  1. EXPLAIN
  2. SELECT *
  3. FROM tableA
  4. WHERE column1 = a;

上のクエリ例と下の EXPLAIN 例の内容は全く合致してないけど。。。

参考サイト

漢のコンピュータ道:MySQLのEXPLAINを徹底解説!!(2009-03-31)
→ちょっと古い記事だけど、詳しくてわかりやすい

Qiita:MySQLのEXPLAIN(2014-10-23)
→上記記事を参考にまとめられた記事

FROFILE

MySQL 5.6 では、非推奨になってしまったらしい。
なくなっちゃうんだって。
GMOメディア エンジニアブログ:MySQL 5.6ではprofilingが非推奨になってしまった件

参考サイト

漢のコンピュータ道:プロファイリングで快適MySQLチューニング生活(2009-02-03)
→ちょっと古い記事だけど、詳しくてわかりやすい

MySQL Casual:show profileでフェーズごとの実行時間を見る – MySQL Casual Advent Calendar 2010(2010-12-05)
→PROFILE を貯める方法? と、それを参照する方法が載っている

障害解析
参考サイト

Qiita:誰も教えてくれなかったMySQLの障害解析方法(2014-07-12)
→まだまだサッパリわからない。というか試す暇がないけど、いつかやりたい!

MySQL | 2015-12-27 (日) 23:18:52 |

String

Posted by muchag | MySQL | 2015-11-20 (金) 15:10:04

面白い関数もあったよ~♪

【環境】
MySQL:5.5.27, 5.5.38

MySQL:12.5 String Functions
 
文字列処理関数。

REPLACE

REPLACE

書式
  1. REPLACE(str,from_str,to_str)

第1引数:対象文字列
第2引数:検索文字列
第3引数:置換文字列

第1引数の対象文字列には、もちろん直接文字列を指定してもいいんだけど
要はデータを扱いたいので、そこにフィールド名を指定する。
フィールド名の場合は、”” は無し。

  1. UPDATE sample SET name=REPLACE(name, 'Tanaka', 'Yamada');
MySQL | 2015-11-20 (金) 15:10:04 |

CASE

Posted by muchag | MySQL | 2015-11-20 (金) 14:39:18


いわゆる CASE 文。
こんなこともできるなんてね~。

【環境】
MySQL:5.5.27, 5.5.38

MySQL:13.6.5.1 CASE Syntax

書式

THEN の後ろの値が戻ってくる。

  1. CASE case_value
  2.     WHEN when_value THEN statement_list
  3.     [WHEN when_value THEN statement_list] ...
  4.     [ELSE statement_list]
  5. END CASE
  6.  
  7. //
  8. CASE sex
  9.     WHEN 1 THEN '男'
  10.     WHEN 2 THEN '女'
  11.     ELSE 'その他'
  12. END
条件式
  1. CASE
  2.     WHEN search_condition THEN statement_list
  3.     [WHEN search_condition THEN statement_list] ...
  4.     [ELSE statement_list]
  5. END CASE
  6.  
  7. //
  8. CASE
  9.     WHEN sex = 1 THEN '男'
  10.     WHEN sex = 2 THEN '女'
  11.     ELSE 'その他'
  12. END
  13.  
  14. // 不等号はもちろん、IN 句も使える
  15. CASE
  16.     WHEN age IN (13, 14, 15) THEN '中学生'
  17.     WHEN age IN (16, 17, 18) THEN '高校生'
  18.     ELSE 'その他'
  19. END
利用
SELECT

散らばった値を集約して表示できる。

点数に応じて振り分け

80点を超えた人は合格。
60点を超えた人は再テスト。
それ以外の人は不合格。

  1. SELECT
  2.     name, result,
  3.     CASE
  4.         WHEN result > 80 THEN 'Pass'
  5.         WHEN result > 60 THEN 'ReTest'
  6.         ELSE 'Fail'
  7.     END as judgment
  8. FROM test;
集約関数併用
  1. SELECT  dept_nbr,
  2.     COUNT(CASE WHEN sex = 1 THEN 1 ELSE 0 END), // 男の人数
  3.     COUNT(CASE WHEN sex = 2 THEN 1 ELSE 0 END)  // 女の人数
  4. FROM Table_A
  5. GROUP BY dept_nbr;
GROUP BY

散らばった値を集約できる。
ただし、SELECT にも同内容の指定をすること。

豪快に GROUP BY句にも SELECT句の CASE式をコピーしてやるのがポイントです。
単純に「GROUP BY pre_name」と変換前の列を指定すると、
正しい結果が得られませんので注意してください。

ミックのページ:CASE式のススメ

ORDER BY

任意のソート順を設定できる。

CHECK 制約との併用

CASE式は CHECK制約と非常に相性が良いのです。
実際に CASE式を使う局面の半分は CHECK制約の中ではないかと思うほどです。

ミックのページ:CASE式のススメ

と、あるけど、まだ理解できていない。

複数カラムの最大値を返す

ミックのページ:CASE式のススメ 参照。

参考サイト

DBOnline:カラムの値に応じて異なる結果を返す条件式の記述

サンプルコードによるPerl入門:SQLのhaving句はcase式で書くと直感的に記述できる(2011-04-13)

DN-Web64:MySQL : CASE文で複雑な条件を指定
→簡潔で読みやすい。SELECT, GROUP BY, ORDER BY 別にサンプルあり

ミックのページ:CASE式のススメ
→いつもお世話になっております。相変わらずの詳細な解説をありがとうございます。

MySQL | 2015-11-20 (金) 14:39:18 |

TRUNCATE

Posted by muchag | MySQL | 2015-09-28 (月) 18:37:18

データの全削除。

【環境】
MySQL:5.5.27
XAMPP:1.8.3
書式
  1. TRUNCATE TABLE tbl_name;
リレーション付きの際のエラー

当該テーブルにリレーションを貼ってあると下記のようなエラーが出る。

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

こういうときは、リレーションに対するチェック機能を一旦 OFF に。

  1. SET foreign_key_checks = 0;
  2. TRUNCATE TABLE table_name;
  3. SET foreign_key_checks = 1;
参考サイト

ミラボ:mysqlでrelation貼ってあるテーブルに対してtruncate tableを強制的に行うときの処理

MySQL | 2015-09-28 (月) 18:37:18 |

設定 -> 文字コード

Posted by muchag | MySQL | 2015-05-22 (金) 22:39:16

最近の XAMPP に入っている MySQL は、my.ini をいじらなくても
phpMyAdwmin からデータベースを作成する際に
文字コードを指定することで、文字化けをしなかったので
そのままで利用していたけど、久しぶりに文字化けに遭った。

【環境】
MySQL:5.5.27
XAMPP:1.8.3
デフォルト?

何もしてない気がするので、多分下記がデフォルト。

  1. mysql> show variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | cp932                          |
  7. | character_set_connection | cp932                          |
  8. | character_set_database   | latin1                         |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | cp932                          |
  11. | character_set_server     | latin1                         |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+
my.ini

インストール 1.7.3
以前、こちらの記事で書いたのが基本。

  1. [mysqld]
  2. # 2015-05-22 追記
  3. character-set-server=utf8
  4. skip-character-set-client-handshake
  5.  
  6. [mysqldump]
  7. # 2015-05-22 追記
  8. default-character-set=utf8
  9.  
  10. [mysql]
  11. # 2015-05-22 追記
  12. default-character-set=utf8
以前との変更点

以前 の通りに my.ini をいじったら、MySQL が起動しなくなった。

[ERROR] (xampp)\mysql\bin\mysqld.exe: unknown variable ‘default-character-set=utf8’

MySQL 5.5 から仕様が変更になったらしい。

[mysqld] については
default-character-set=utf8 ではなく
character-set-server=utf8 と記述することで無事に起動。

  1. [mysqld]
  2. - default-character-set=utf8
  3. + character-set-server=utf8
参考サイト

cyberarchitect:[ERROR] mysql\bin\mysqld.exe: unknown variable ‘default-character-set=utf8′

my.ini 編集後
  1. mysql> show variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | utf8                           |
  7. | character_set_connection | utf8                           |
  8. | character_set_database   | latin1                         |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | utf8                           |
  11. | character_set_server     | utf8                           |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+

むw 1つだけ latin1。

global 値

参考サイトにしたがって、global も覗いてみる。

  1. mysql> show global variables like 'character_set%';
  2.  
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | utf8                           |
  7. | character_set_connection | utf8                           |
  8. | character_set_database   | utf8                           |
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | utf8                           |
  11. | character_set_server     | utf8                           |
  12. | character_set_system     | utf8                           |
  13. | character_sets_dir       | D:\xampp\mysql\share\charsets\ |
  14. +--------------------------+--------------------------------+

ほー。こちらはちゃんとなっている。

my,ini 設定前の global 値も見ておけばよかった~。

character_set_database
原因

character_set_database だけが値が異なっている原因は・・・

この値はどこを参照しているかというと、create database した時の値です。

よかろうもん!:MySQLでcharacter_set_databaseがlatin1になってしまう問題の対応方法

だそうです。

あれれ。
データベースを作成した時に、文字コードを設定しそこねたのかしら。

確認方法
  1. mysql> show create database `databese1`;
  2.  
  3. +-----------+----------------------------------------------------------------------+
  4. | Database  | Create Database                                                      |
  5. +-----------+----------------------------------------------------------------------+
  6. | databese1 | CREATE DATABASE `databese1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
  7. +-----------+----------------------------------------------------------------------+

うほ!確かに。。。

解決方法
  1. mysql> ALTER DATABASE `databese1` default character set utf8;

直った~♪

経緯

問題解決にはあまり関係ないので
記事末に覚え書き用経緯を。

データ情報

駅データ.jp
こちらのサイトから、全国の駅データをいただいてきた。

編集

配布されているデータは、ちゃんと UTF8 なのだけど
データを追加する都合上、Excel で編集してから DB へ流し込んだ。

データが多く、phpMyAdmin からではタイムアウトになってしまったので
コマンドラインから実行。

手順
  1. 配布元よりデータを DL
  2. Terapad で、SHIFT-JIS、CR+LF へ変換
  3. Excel で、データ編集
  4. Excel から CSV 出力
  5. Terapad で、UTF-8N、LF へ変換
  6. コマンドラインより DB へ流し込み

すると、途中から文字化けという事態が起こった。
これはこれで不思議。。。

MySQL | 2015-05-22 (金) 22:39:16 |
次ページへ »