- 投稿日：2020-03-19T23:45:35+09:00
Heroku MySQLのデータベースから持ってきた日本語が文字化けする
3行で
- ClearDBのアドオンでMySQLを使う
- Heroku公式のやり方でMySQLからデータを持ってくる
- 文字コードを指定する
環境
PHP, Heroku, ClearDB, Mac
文字化け
HerokuとMySQLを同期するやり方は割愛。
Heroku公式のやり方でを参考にMySQLからデータを持ってくる。<?php $url = parse_url(getenv("CLEARDB_DATABASE_URL")); $server = $url["host"]; $username = $url["user"]; $password = $url["pass"]; $db = substr($url["path"], 1); $conn = new mysqli($server, $username, $password, $db); ?>原則ここに書いてある内容はいじらなくてよい。
例えばカッコの中身をデータベース名やユーザー名に変える必要はない。
しかしこの形でデータベースから日本語のデータを持ってくると『?????』 といった形で出力されてしまう。これでイケるかと思ったけどダメだった。
解決
$conn = new mysqli($server, $username, $password, $db); $conn->set_charset('utf8');文字コードを指定してあげる。これだけ。
教えていただいて解決したんですが、これを参考にするべきだったのかも。mysqli::set_charset - PHP.net
https://www.php.net/manual/ja/mysqlinfo.concepts.charset.php
- 投稿日：2020-03-19T17:42:40+09:00
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
概要
特定のログを埋め込んで抽出してSQL化して適当なDBとテーブル作ってSQL流してクエリで解析できるように対応を進めていたところ、大量のログ＆sqlファイル１つ数百MBという結果になり、ローカルでは対応厳しいためRDSに対応していたところ色々とハマったまとめです。
環境
- RDS - MySQL Community 5.6.40
問題
調べつつ次の通り作業を進めていたところ、タイトルのエラーでハマりさらに調べるとAWSコンソール上で作業すれば良いと分かった。
$ mysql dev_analysis -h staging.xxxxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -P 33066 -u gremito -p < ~/sqls/xxxxxxxxxx.sql Enter password: ERROR 2006 (HY000) at line 24: MySQL server has gone away $ mysql -h staging.xxxxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -P 33066 -u gremito -p mysql> show variables; （略） | max_allowed_packet | 4194304 （略） mysql> set global max_allowed_packet = 512000; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation mysql> show grants; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for gremito@% | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'bananaore'@'%' IDENTIFIED BY PASSWORD <secret> WITH GRANT OPTION | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)やったこと
パラメータグループから
max_allowed_packetの値を変更しているとエラーになり、原因はデフォルトを変更することができず、さらにデフォルトからはコピーを作ることもできないため新しくパラメータグループを作り、max_allowed_packetの値を変更＆保存。
パラメータグループの変更は、データベースを起動していないと変更できないようで、変更後は再起動が必要。
再起動後、
max_allowed_packetの値が変わっていることを確認。mysql> show variables; （略） | max_allowed_packet | 512000 （略）参考記事
- 投稿日：2020-03-19T13:07:53+09:00
2つの日付のdiffを求める方法がPostgreSQLとMySQLで異なる
TR; DL
PostgreSQL
SELECT 'date(''2020-03-01'') - date(''2020-02-01'')' AS expr, date('2020-03-01') - date('2020-02-01') AS days;
expr days date('2020-03-01') - date('2020-02-01') 29 MySQL
SELECT 'datediff(''2020-03-01'', ''2020-02-01'')' AS expr, datediff('2020-03-01', '2020-02-01') AS days
expr days datediff('2020-03-01', '2020-02-01') 29 ハマったこと
これまで2年超、業務でPostgreSQL (9.6 〜 11)を使ってきたのですが、最近これに加えてMySQL (5.7) も触るようになりました。
そこで、PostgreSQLのノリで日付計算を書いたところ、
-- MySQLで実行 SELECT 'date(''2020-02-29'') - date(''2020-02-01'')' AS expr, date('2020-02-29') - date('2020-02-01') AS days UNION SELECT 'date(''2020-03-01'') - date(''2020-02-01'')' AS expr, date('2020-03-01') - date('2020-02-01') AS days UNION SELECT 'date(''2021-03-01'') - date(''2020-02-01'')' AS expr, date('2021-03-01') - date('2020-02-01') AS days;
expr days date('2020-02-29') - date('2020-02-01') 28 date('2020-03-01') - date('2020-02-01') 100 date('2021-03-01') - date('2020-02-01') 10100
時空を超えたようです同一月であれば問題ないようにも見えますが、月や年を跨ぐと、正しい結果になりません。
- 投稿日：2020-03-19T12:39:58+09:00
【Laravel】php artisan migrateでハマったので、XAMPP環境でPostgreSQLを使ってみた。
migrateでエラーが出まくった。。。
いつもお世話になっているTechpitの教材で学習していた際に、migrateで思いっきりハマり、解決できなかったのでXAMPPでPostgreSQLを使えるようにしてみました。その際のメモです。
学習に使用している教材のリンク：
https://www.techpit.jp/p/laravel-vue-snsこちら作りながら学べる学習教材で楽しみながらミドルウェア、認可、フロントエンドとの連携などかなり深いところまで学べるのでめちゃくちゃおススメです！！
自身の学習環境に関して
教材ではMac、Docker、PostgreSQLを用いて学習します。僕の場合はWindowsユーザーでDocker(Docker for Windows)の環境構築を以前挫折した経緯があったので、Windows、XAMPP、MariaDB(MySQL)で学習していました。
つまり、教材をじぶんの環境に合わせて読み替える必要がありました。
(僕にこれができるスキルはまだなかったってことです。。。泣)
データベースの構成
この教材のデータベースの構成は以下になります。(無料で見れる範囲の内容ですので引用させていただいております。)
順番としては、articlesテーブルを作った後に、likesテーブルを作るのですが、likesテーブルを作ろうとしたところエラーが出るようになってしまいました。(超絶序盤。)
どうハマったのか
※これに関しては解決できなかったので、原因分かり次第追記します。※どうやら外部キー制約関連で違いがある(負の値の無い数値型にするのがポイント)とのことです。
嘆いていたところやんばるさん(著者)からご教授いただきました。ありがとうございます。泣
参考(教えていただきました。)：
https://qiita.com/0w0/items/4a9cb7d27794bfb93d46#%E5%8E%9F%E5%9B%A0さっそくご教示いただいた通り、unsigned()を付けて、php artisan migrateを実行！！
ところが。。。
SQLSTATE[HY000]: General error: 1005 Can't create table laravel_vue_sns.articles (errno: 150 "Foreign key constraint is key (user_id) references users (id))
⇒laravel_vue_sns.articlesを作成できません（errno：150"外部キー制約はキー（user_id）はusers（id）を参照します）
SQLSTATE[HY000]: General error: 1005 Can't create table laravel_vue_sns.articles (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter tarticles(errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter tablearticlesadd constraintarticles_user_id_foreignforeign key (user_id) referencesusers(id`))⇒
laravel_vue_sns.articlesを作成できません（errno：150"外部キー制約の形式が正しくありません "）（SQL：alter tarticles（errno：150"外部キー制約が正しくありません （SQL：変更テーブルarticles制約の追加articles_user_id_foreign外部キー（user_id）参照users（id`））こうなる。
続いて教えていただいたQiitaの記事を参考に自分のスクリプト見ながら睨めっこ。
increments()で作ったカラムには、実は裏でunsined（符号無し）属性が付与される。要は採番項目なので正の値しか登録できないわけだが、役割テーブル側のuser_idとauthority_idには同様の制約を付けていない。https://qiita.com/0w0/items/4a9cb7d27794bfb93d46#%E5%8E%9F%E5%9B%A0
自分のマイグレーションファイルでは、bigIncrementsで記載していることに気付く。違いを調べてみました。
参考：
https://qiita.com/fuubit/items/17f3eb306c64ede163d2
どうやらbigIncrements()は負の値も登録できてしまうっぽい。ってことで、全てincrements()に変えてみました。
が、先ほどと同様にエラーが出てしまいました。。。
解決できず。。。泣
環境の違いだけでここまで躓くものかと痛感。。。
(何回目だ。。。)
XAMPPでPostgreSQLを使ってみた。
ってことで、キッパリ諦めてXAMPPでPostgreSQLを使う形に挑戦してみました。(Dockerにも再挑戦しましたがcompose upができず再挫折。。。)参考：
https://tasulife-23.com/xampp-postgresql/この記事では、PHPファイルからPostgreSQLまでのつなぎ方が記載されています。
ざっくりこんな内容です。
その後、Laravelに紐づけるためには.envファイルを編集する必要があります。
DB_CONNECTION=pgsql DB_HOST=localhost DB_PORT=5432 DB_DATABASE=test DB_USERNAME=postgres DB_PASSWORD=secret(ここはご自身で設定したパスワードを記載してください。)
僕の場合はこんな感じです。別途、データベースを用意する必要があるのでデータベースを作成。
psql -U postgres -c "create database test;
その後、マイグレーション。
無事、マイグレーションすることができました。
- 投稿日：2020-03-19T10:04:19+09:00
適切なインデックスを張るために
はじめに
DBパフォーマンス向上を目的としてインデックスを張ることは一般的な手法ですが、その効果を高めるためには対象となるカラムを適切に選択したいところです。
今回はインデックス対象とすべきカラムを選択する際に検討するいくつかのことをまとめてみました。そもそもインデックスとは
indexの訳には「索引」というものがありますが、これが一番わかりやすいと思います。
本を読む時に、あるテーマについて書かれたページを探す時、二つの方法があります。
- 最初のページから最後のページまで順番に探していく。
- 本の末尾にある索引から探して、該当ページ数を調べる。
それぞれのメリット／デメリットを整理すると、次のようになります。
メリット デメリット 1 1. 索引作成の手間が不要。
2. 索引のためのページも不要（紙の節約になる）。1. 探すのに時間がかかる。 2 1. 短時間で探せる。 1. 索引作成の手間がかかる。
2. 索引のためのページも必要。
3. 索引があっても探したいテーマと無関係だったら意味がない。以上のことをDBに置き換えると次のようになります。
テーブルから特定のレコードを抽出したい時、次の二つの方法があります。
- 対象テーブルを先頭から順にシーケンシャルに探していく（＝インデックスのない状態）
- インデックスを参照して該当レコードのIDを調べる。（＝インデックスのある状態）
（※インデックスは一般的に木構造で表現されますが、ここではわかりやすくするため表形式で表現しています）
それぞれのメリット／デメリットを整理すると、次のようになります。
メリット デメリット 1 1. インデックス作成のためのオーバーヘッド無し。
2. インデックスのためのデータも不要（容量の節約になる）。1. 探すのに時間がかかる。 2 1. 短時間で探せる。 1. インデックス作成のオーバーヘッドが必要。
2. インデックスを保存するための容量も必要。
3. インデックスがあっても探したいカラムのインデックスがなければ意味がない。以上がインデックスに関する説明です。
インデックス対象となるカラムを選択する時に検討すること
WHEREやJOINで使用される → インデックス候補
インデックスは特定のカラムから該当するレコードを探すために使用されるので、WHEREやJOINで指定されるカラムはインデックス候補となります。レコード件数が1万件を超えるテーブル → インデックス候補
レコード件数が少ない場合、インデックスによる検索よりシーケンシャルな全件検索の方が早い場合があります。
明確な閾値ではありませんので、1万件は目安として捉えてください。外部キー → インデックス候補
MySQLの場合、外部キーを作成すると自動的にインデックスが作成されます。カーディナリティが20以上 → インデックス候補
カーディナリティとは、カラムに設定される値の種類の数です。下記テーブルのageに設定される値の種類は20と25の二種類なので、カーディナリティは2です。
カーディナリティが小さいと、一つの値に多くのレコードが紐づくことになるため、結局探すのに時間がかかることになります。
インデックス内の一つの値が指し示すレコード件数はレコード全体の5%程度が良いとされています。
（上記の例ではtrueとfalse、それぞれが全体の50%のレコードを指し示しているので、望ましくありません。）
カーディナリティの20以上という値は、インデックス内の一つの値が指し示すレコード数が、各値で均等な場合に、それぞれ5%になるという意味で設定したものです。
そのため、レコード数に偏りがある場合はカーディナリティーが大きくてもインデックスが有効でない場合があるので注意が必要です。カラムに対してSQL関数が使われている → インデックス候補となるかはクエリ次第
例えば、ageカラムにインデックスが張られていて、クエリが下記のようになっている場合、インデックスは使用されません。
WHERE AVG(age)>= 20
この場合、関数インデックスを使用するか、関数を使用しないようにすることでインデックスが使用されるようにする必要があります。インデックスが使用されているか確認する
カラムにインデックスを張っても、それが必ずしも使用されるとは限りません。
MySQLではクエリの先頭にEXPLAINをつけることで、そのクエリで使用されるインデックスを確認することができます。EXPLAINEXPLAIN SELECT * FROM users WHERE name = 'Bob';実行結果の
possible_keysが使用されるインデックスの候補で、keyが実際に使用されたインデックスです。インデックスが意図通りに使用されない場合
WHEREの条件で複数のカラムが指定される場合などは、単一のインデックスは使用されないことがあります。
その場合は、複数のカラムに対して一つのインデックスを張る複合インデックスを張ります。複合インデックスCREATE INDEX ON users(name, age);複合インデックスが使用されない場合は、指定するカラムの順序をクエリに合わせて調整します。
まとめ
本記事では、インデックスを張る時に確認することを記述しました。
- WHEREやJOINで使用されるかどうか
- レコード件数が1万件を超えるかどうか
- 外部キーかどうか
- カーディナリティが20以上かどうか
- カラムに対してSQL関数が使われているかどうか
ボトルネックになりがちなDBパフォーマンス向上のために、本記事がお役に立てると幸いです。
