- 投稿日：2019-12-05T23:21:53+09:00
Python3 + mysql-connector-pythonで実行したSQLを知りたい時
- 投稿日：2019-12-05T23:14:34+09:00
OracleからMySQLへデータ移行したお話
はじめに
この記事は第二のドワンゴ Advent Calendar 2019の記事になります。
OracleDB → MySQL移行作業にて、テーブルデータを移行した時のお話です。
異なるデータベース間の移行作業は移行リスク、作業工数の観点から発議し辛い提案ですが、
実際に「異なるデータベース間にて、実データの移行作業はどうやるか」を主軸に進めたいと思います。目次
- 移行方法の選定
- 環境と準備
- テーブル構造移行
- データ移行
- まとめ
1. 移行方法の選定
同じデータベース間であれば、
1. dump
2. import
上記2手順で済みますが、SQL構造の異なる場合は単純には上手くいきません。DDL/DMLを別にimportする上で、SQLのフォーマットをデータベースに合わせた形にconvertする必要があります。
今回はOracle to MySQL Migrationを用いたconvert方法を紹介させて頂こうと思います。
次にデータinsertのDML文は上記ツールでのconvertではなく、
- 明示的なinsert文では、設定によりエラーになる可能性がある（mysql/STRICTモード等）1
- csvの方がinsert文より柔軟性があるのも強み
上記理由からcsvファイルとしてデータexportし、csvとしてデータimportする事にしました。
2. 環境と準備
作業
移行元：Oracle 11g
移行先：MySQL 5.7必要なアプリケーション
SQL Plus*
Oracle to MySQL Migration2
MySQL3
SQL Plus* install 時のメモ（クリックで展開）
sqlplus コマンドが動作せず、libc.so.6 が読み込めないエラーは以下で対処した。# 必要なパッケージを探す yum whatprovides libc.so.6 # パッケージをインストールする yum install libc.so.6 # その他、インストールしたコマンド ## ld-linux.so.2 yum install ld-linux.so.2 ## libstdc++.so.6 適正バージョンとインストール yum whatprovides libstdc++.so.6 yum install libstdc++.so.6 yum install libstdc++-4.4.7-23.el6.i686 yum upgrade libstdc++
GNU lib install 時のメモ（クリックで展開）
GNU lib のバージョン2.14が求められた
# version確認 # ldd --version ldd (GNU libc) 2.12 # yum upgrade（2.12以上はupdateされない） yum update glibc # server からDL wget https://ftp.gnu.org/gnu/glibc/glibc-2.14.tar.gz tar zxf glibc-2.14.tar.gz cd glibc-2.14 mkdir build && cd build ../configure --prefix=/opt/local make -j8 sudo make install # env追加 echo 'export LD_LIBRARY_PATH=/opt/local/lib:$LD_LIBRARY_PATH' >> ~/.bash_profile # 再起動 shutdown -r now
3. データ構造移行
Oracle Table Struct Dump
コマンドラインで実行
expdpを用いる4
EXPDP/IMPDP {ユーザー}/{パスワード}@{接続文字列} DIRECTORY={ディレクトリ} DUMPFILE={ファイル名} LOG={ログファイル名} TABLES={テーブル} CONTENT=METADATA_ONLY -- example EXPDP test/pass@orcl DIRECTORY=dp_dir DUMPFILE=dmp_table.dmp LOGFILE=exp.log TABLES=test.emp CONTENT=METADATA_ONLYSQL DeveloperのUI上から実行
「ツール」→「データベース・エクスポート」
「DDLのエクスポート」にチェック
「データのエクスポート」はチェックを外す
Query Convert
Oracle to MySQL Migrationの
sqllinesを用います# DB移行ツール(sqllines)を用いる ファイル名 - oracle_export.sql ./sqlines -s=oracle -t=mysql -in=oracle_export.sql > oracle_export_output.sql が出力されるMySQL Query Struct Import
# mysqlコマンド MYSQL_PWD='****' mysql -u(ユーザー名) -A -h (サーバー) < oracle_export_output.sql4. データ移行
(1)Oracle Record Export
# oracleにログイン sqlplus / nolog # 接続 conn (ユーザー名)/(パスワード)@(ホスト):(ポート番号)/(スキーマ) # データdump exp (ユーザー名)/(パスワード)@(ホスト):(ポート番号)/(スキーマ) tables=(テーブル名) directory=export dumpfile=(dumpファイル名) logfile=(logファイル名)(2)MySQL Record Import
MYSQL_PWD='****' mysql -u(ユーザー名) -A -h (ホスト) use (スキーマ);LOAD DATA LOCAL INFILE '(logファイル名)' INTO TABLE （テーブル） FIELDS TERMINATED BY ',' ENCLOSED BY '"';移行時に使ったScript
テーブル単位でデータ移行するScript
https://github.com/yuichi-sato/query-script5. まとめ
Table、Materialized View も単純なデータレベルでの移行なら可能で、
型を精査する作業時間が短縮される恩恵は大きく、移行作業の助けになりました。作業に一手間掛かりますが、sqlines / shellで完結するのは大きいです。
参考
sqlines
http://www.sqlines.com/oracle-to-mysql
oracle exp
https://oracle-chokotto.com/ora_export.html
oracle expdp
https://oracle-chokotto.com/ora_export_expdp.html
- 投稿日：2019-12-05T20:49:15+09:00
PostGISとMySQL8のGIS機能の違い
本記事はPostgreSQL Advent Calendar 2019の7日目となります。
はじめに
私は普段PostGISを使っていますが、最近MySQL 8.0.xのGIS機能について調査する機会がありました。
本記事は筆者が調査の中で気づいた両者の違いをまとめたものです。PostGISは2.x〜3.x、MySQLは8.0.17を対象としています。
両者は共にOpenGIS1やSQL/MMなどの標準に基づく実装を進めているため共通している部分も多いものの、独自拡張や実装の違いによりいくつかの違いもあります。
本記事の目的は両者の優劣をつけることではありません。
両者の違いを理解して、場面に応じた適切な使い方を選択するための一助となることが目的です。所感
本文に入る前に、私の所感を述べておきたいと思います。
まず、普段PostGISを使っている人が、MySQLのGIS機能に乗り換えるメリットは現時点では少ないかもしれません。それは現時点では機能面、安定性、速度等の面で、PostGISに一日の長があると言わざるを得ないからです。
しかし、MySQLのGIS機能を使う上でPostGISで培った知識は役に立ちます。それは先に述べたとおり両者が同じ標準に基づく実装を進めていることもその理由の一つです。PostGISユーザにとってもMySQLのGIS機能が進化することによってGISデータを扱う手法の選択肢が増えるのは喜ばしいことだと思います。(少なくとも私は嬉しい)
そして、PostGISユーザはMySQLのGIS機能を試してみて機能追加リクエストやバグ報告をすることで、MySQLのGIS機能の発展に寄与することができます。次に、MySQLのユーザにとっては、より自在に地理空間情報が扱えるようになったことは喜ばしいことでしょう。
もし、更に強力なGIS機能が必要であればPostGISを使ってみて(あるいはこの記事を読んでみて)、MySQLの開発チームに機能追加をリクエストすることもできます。両者の違い
ざっと並べてみました。以降はこの項目毎に詳しく解説します。
項目 PostGISのGIS機能 MySQL8のGIS機能 利用方法 PostgreSQL本体とは別に、拡張機能としてインストール MySQL本体に含まれているのでそのまま利用可能 集約関数 対応 非対応 データのインポート 同梱ツール(shp2pgsql)を利用、あるいはogr2ogr、osm2pgsql等の外部ツールを利用 ogr2ogr等の外部ツールを利用 ドキュメント PostGISのドキュメント MySQL本体のドキュメント内 WKTの座標指定順(axis order) 経度 - 緯度 緯度 - 経度 関数の数 200強 100弱 周辺ツール QGIS、pgRouting MySQL Workbench その他 Materialized Viewとの組み合わせが強力 一部の関数でインデックスが使われないというバグが存在する 利用方法
- PostGIS
- PostgreSQL本体とは別に、拡張機能としてインストール
- MySQL
- MySQL本体に含まれているのでそのまま利用可能
MySQL8利用者であれば誰でもGIS機能が使えるということです。
利用者にとって、非常にメリットが大きい特徴です。PostGISはPostgreSQL本体には取り込まれておらず、拡張機能として実装されています。これは本体のリリースサイクルに左右されないなどの開発者にとってはメリットでもありますが、利用者にとってハードルが高いことは否めません。
また、今後、MySQL8が使えるレンタルサーバではGIS機能が使えるという状況が増えることは非常にワクワクします。
これは、PostgreSQLが使えるレンタルサーバで必ずしもPostGIS拡張が有効になっているわけではない、という状況とは違います。ただ、そもそもPostgreSQLが使えるレンタルサーバそのものが少ないのですが。ちなみに、PostgreSQLの主要なマネージドサービスであるAmazon RDS、Azure Database、Google Cloud SQLでは、いずれもPostGIS拡張が有効になっています。
集約関数
- PostGIS
- 対応
- MySQL
- 非対応
これは非常に重要な機能です。MySQLにもぜひ対応して欲しい。
例えばST_Union関数をみてみましょう。
ST_Union関数はPostGISとMySQLそれぞれに存在し、その基本的な機能はジオメトリ(図形)の結合です。PostGISとMySQLのST_Unionは共通して2つのジオメトリを結合する機能を提供します。
MySQLのST_Union関数の例mysql> SET @g1 = ST_GeomFromText('LineString(1 1, 3 1)'); mysql> SET @g2 = ST_GeomFromText('LineString(1 3, 3 3)'); mysql> SELECT ST_AsText(ST_Union(@g1, @g2)); +--------------------------------------+ | ST_AsText(ST_Union(@g1, @g2)) | +--------------------------------------+ | MULTILINESTRING((1 1,3 1),(1 3,3 3)) | +--------------------------------------+ 1 row in set (0.00 sec)PostGISのST_Union関数の例(非集約関数版)opendata=# \set g1 'ST_GeomFromText(''LineString(1 1, 3 1)'')' opendata=# \set g2 'ST_GeomFromText(''LineString(1 3, 3 3)'')' opendata=# SELECT ST_AsText(ST_Union(:g1, :g2)); st_astext -------------------------------------- MULTILINESTRING((1 1,3 1),(1 3,3 3)) (1 row)PostGISのみで提供されるST_Unionの機能として、いわゆる集約関数としても動作する点が挙げられます。
つまり、2つだけではなく複数のジオメトリを結合することができるのです。PostGISのST_Union関数の例(集約関数版)opendata=# CREATE TABLE test(geom GEOMETRY); CREATE TABLE opendata=# INSERT INTO test(geom) VALUES(ST_GeomFromText('LineString(1 1, 3 1)')); INSERT 0 1 opendata=# INSERT INTO test(geom) VALUES(ST_GeomFromText('LineString(1 3, 3 3)')); INSERT 0 1 opendata=# INSERT INTO test(geom) VALUES(ST_GeomFromText('LineString(1 5, 3 5)')); INSERT 0 1 opendata=# SELECT ST_AsText(geom) FROM test; st_astext --------------------- LINESTRING(1 1,3 1) LINESTRING(1 3,3 3) LINESTRING(1 5,3 5) (3 rows) opendata=# SELECT ST_AsText(ST_UNION(geom)) FROM test; st_astext ------------------------------------------------ MULTILINESTRING((1 1,3 1),(1 3,3 3),(1 5,3 5)) (1 row)これは非常に強力な機能です。
例えば、市区町村のポリゴンが格納されたテーブルから、県のポリゴンを生成することができたりします。
データのインポート
- PostGIS
- 同梱ツール(shp2pgsql)を利用、あるいはogr2ogr、osm2pgsql等の外部ツールを利用
- MySQL
- ogr2ogr等の外部ツールを利用
PostGISに同梱されるshp2pgsqlは非常に強力です。
取り込むシェープファイルの文字コード指定や、空間参照系(SRS)を変換しながらのインポートにも対応しています。
また、何よりPostGISに同梱されているという点で、利用のハードルが低いという点も優れています。それに対して、MySQLは現状、GISデータを取り込むためのツールを同梱していないため、別途用意する必要があります。
せっかくGIS機能が標準機能として取り込まれているのに勿体ない。MySQLにシェープファイルをインポートする方法については下記の記事で解説しています。
https://qiita.com/miyauchi/items/c8349e1e6339bdf26a20
また、OpenStreetMapのデータを取り込むosm2pgsqlを使えば、道路データを取り込むことが可能です。
さらにその道路データを地図タイルにレンダリングするなどの活用例も事例が豊富です。ドキュメント
- PostGIS
- MySQL
PostGISは拡張機能であるため専用のドキュメントが整備され、MySQLのGIS機能は本体のドキュメント内の一部として整備されています。
WKTの座標指定順(axis-order)
- PostGIS
- 経度 - 緯度
- MySQL
- 緯度 - 経度
単純に言うと、座標(緯度経度)の指定順が異なる場合があります。地理空間参照系(SRS)によります。
少なくとも、よく使われるWGS84(SRID=4326)では逆になっています。PostGISのWKTでは経度緯度の指定順(axis order)は経度(lat)、緯度(lon)です。
opendata=# SELECT ST_AsText(ST_GeomFromText('POINT(139.708056 35.628611)', 4326));対するMySQLのWKTでは緯度経度の指定順は緯度(lat)、経度(lon)です。
mysql> SELECT ST_AsText(ST_GeomFromText('POINT(35.628611 139.708056)', 4326));MySQLではaxis orderを明示的に指定することができます。この機能を使えばPostGISと同じaxis orderにすることが可能です。
mysql> SELECT ST_AsText(ST_GeomFromText('POINT(139.708056 35.628611)', 4326, 'axis-order=long-lat'));axis orderの違いは「一律緯度、経度の順とする」か、「EPSGの定義順に準拠する」かのポリシーの違いです。
利用者としてはどちらかに統一してほしいのですが、現状では異なっていることを認識した上で使用する必要があります。参考: MySQL8.0のaxis orderについて - PC関係のメモ
https://blog.mylab.jp/posts/2018102801/関数の数
- PostGIS
- 200強
- MySQL
- 100弱
2019/12/1時点のドキュメントの下記項で数えました。
14.11. PostGIS Function Support Matrix - PostGIS 3.0.0 マニュアル
https://postgis.net/docs/manual-3.0/postgis-ja.html#PostGIS_TypeFunctionMatrix
12.16.1 Spatial Function Reference - MySQL 8.0 Reference Manual
https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html多ければ良いというものではありませんが、重要なファクタです。
個人的にはやはり集約関数は早く実装してほしいところです。集約関数以外だと、個人的にPostGISの下記の関数はMySQLでも早期に実装して欲しい。
- ST_SimplifyPreserveTopology
- ST_PointOnSurface
周辺ツール
- PostGIS
- QGIS、pgRouting
- MySQL
- MySQL Workbench
QGISはPostGISのデータを可視化したり、PostGISのデータと他の情報を重ねることもできて非常に強力です。
また、pgRoutingを使うことで経路探索もできます。MySQLはまだ周辺ツールが少ないものの、公式ツールであるMySQL Workbenchはいち早くSpatialデータの可視化に対応しています。
その他
- PostGIS
- Materialized Viewとの組み合わせが強力
- MySQL
- 一部の関数でインデックスが使われないというバグが存在する
処理が重たくなりがちなGISデータとPostgreSQLのMaterialized Viewの組み合わせは非常に強力です。
MySQLのGIS機能はまだバグも多く、特に致命的なのは一部の関数でインデックスが効いていないことです。
Bug #96311 ST_Intersects() is very slow on MySQL 8.0
Submitted: 24 Jul 9:02
https://bugs.mysql.com/bug.php?id=96311Bug #96269 st_intersects mysql 8 does not use index
Submitted: 22 Jul 13:10
https://bugs.mysql.com/bug.php?id=96269Bug #94655 Some GIS function do not use spatial index anymore
Submitted: 14 Mar 11:04
https://bugs.mysql.com/bug.php?id=94655まとめ
PostGISは成熟期で安定感がありますし、MySQLのGIS機能は導入期から成長期といった感じで日進月歩で進化するのを見ているのは非常に楽しいです。
ぜひ両者を使ってみてそれぞれの良さを体感してみて下さい！
Open Geospatial Consortium, Inc.（OGC）が策定した技術仕様 ↩
- 投稿日：2019-12-05T20:01:35+09:00
Herokuで自分のサイトを公開する方法 〜DBとの接続、GitHubからdeployまで〜
自分で作ったサイトを簡単に公開できる、Herokuの導入方法について紹介します。
Herokuとは？？
Heroku(ヘロク)は大まかに説明すると、PHPやPythonなどのプログラミング言語を用いて開発したWebアプリ、WebサービスなどをWeb上で簡単に公開できるというサービスです。今回はPHPで作ったサイトを公開することをメインに説明していきます。
事前準備
まずは、進めていくに当たってこの準備が必要になります。
まだの方のために隣にリンク貼っときます！
- Herokuのアカウント作成 https://jp.heroku.com/
- GitHubのアカウント作成 https://github.com/
- Sourcetreeのインストール https://www.sourcetreeapp.com/
- homebrewのインストール https://brew.sh/index_ja
heroku CLI(旧Heroku toolbelt)のインストール
こちらからheroku CLIをインストールできます。
Heroku CLIとは、Herokuをコマンドで操作するためのツールになります！https://devcenter.heroku.com/articles/heroku-cli
今回はターミナルからインストールしていきます。
以下のコードを入力してインストールします。$brew tap heroku/brew && brew install herokuインストールが無事終了したら、
＄heroku loginを入力するとherokuのサイトにとんでログインできます。
HerokuからCreate a new appを作成
ログインしたら、SourcetreeでローカルのフォルダからGitHubへpushした自分のWebサイトを用意しておきましょう！
SourcetreeはローカルのファイルをGitHubへpushするために必要になるのですがここではその説明は省きます。https://dashboard.heroku.com/apps
このURLをクリックすると、このような画面になると思います。
ここのCreate new appをクリック。
するとこのような画面になると思います。App nameに入力した内容はアクセスする際のURLの一部分になります。
例えば、App nameにhogeと入力すると、URLはhttps://hoge.herokuapp.comとなります。regionはそのまま「United States」でオッケーです。
GitHubからdeploy
Herokuでアプリを作成したら、「Deploy」をクリックして、Deployment methodからGitHubを選択。
ここで、選択する際に「Connect to GitHub」というボタンが表示されるので、それをクリックして
Herokuと自分のGitHubを紐付けましょう。
次に、Connect to GitHubのところに検索欄があるので、そこに自分のGitHubの公開させたいリポジトリを検索して「connect」ボタンをクリックします。うまくいったら、画面下に行くとこのような画面があるはずです。
「Automatic deploys」は自動でdeployする場合、
「Manual deploy」は手動でdeployする場合にそれぞれ用います。どちらの方法でdeployするか選択したら、「Enable Automatic Deploys」または「Deploy Branch」をクリック。
これでGitHubからのdeployは完了です！！
ちゃんと公開されてるか確認したい場合は先ほど作成したURLをブラウザで入力すると確認できます。
ここまでが公開までの手順です！Heroku Add-onsからClearDB MySQLをインストール
続いて、HerokuにDBを接続していきます。Resourcesをクリック。
Find more add-onsボタンをクリックすると別のサイトにとぶので、そこから「ClearDB MySQL」を見つけてクリック。
クリックして「ClearDB MySQL」のページに入ったら「Install ClearDB MySQL」ボタンをクリックします。
注意 : ここでクレカ登録をする必要があります。
クリックするとこんな画面にいくかと思います。
「Add-on plan」はDBの容量になります。Free(無料)だと5MBになります。もっとDBの容量を増やしたいよ、という方は月間でそれぞれの容量に応じた金額を支払う必要があります。
「App to provision to」にはHerokuの「create a new app」であらかじめ作成しておいたアプリの名前を入力します。名前を入力するとこんな感じで「Provision add-on」のボタンが押せるようになるかと思います。
「Provision add-on」をクリック。無事インストールが終わったら、Resourcesのところに「ClearDB MySQL」が追加されてることを確認します。
Sequel Proのインストール
DBの操作するために、Sequel Proをダウンロードします。
こちらからダウンロードできます。
https://www.sequelpro.com/インストールしてアプリを開くと、こんな画面が表示されると思います。
ここに入力する内容は先ほど追加した「ClearDB MySQL」で取得されるデータを入力します。
一度Herokuの自分のアプリ編集画面に戻って、
Settingsの「Config Vars」から確認できると思います。CLEARDB_DATABASE_URLの値から取得できます。読み方はこんな感じです。
mysql://(--username--):(--password--)@(--hostname--)/(--dbname--)?reconnect=trueここに該当する内容をさっきの画面に入力します。
Sequel Proのデータベースと、ポートには何も入力しなくて大丈夫です。
一度「接続をテスト」ボタンを押して接続を確認してみてください。入力後、接続できたらオッケーです。サイトで使用するテーブルを作成しておいてください。
これでSequel Proとの接続は完了です！
WebサイトをDBとつなげるためのファイルを作成する
WebサイトとDBをつなげるためのファイルを作成しましょう。
作成したらこのように入力しましょう<?php $host = getenv('ホストネームの値'); //MySQLがインストールされてるコンピュータ $dbname = getenv('DBネームの値'); //使用するDB $charset = "utf8"; //文字コード $user = getenv('ユーザーネームの値'); //MySQLにログインするユーザー名 $password = getenv('パスワード'); //ユーザーのパスワード $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //SQLでエラーが表示された場合、画面にエラーが出力される PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //DBから取得したデータを連想配列の形式で取得する PDO::ATTR_EMULATE_PREPARES => false, //SQLインジェクション対策 ]; //DBへの接続設定 $dsn = "mysql:host=$host;dbname=$dbname;charset=$charset"; try { //DBへ接続 $dbh = new PDO($dsn, $user, $password, $options); } catch (\PDOException $e) { throw new \PDOException($e->getMessage(), (int) $e->getCode()); }上記5行が先ほどSequel Proに入力した内容です。入力した内容をダイレクトに打ち込んでもうまく行くのですが、GitHubなどで公開されるため、そのまま入力するのはまずいです。
そのため、PHPの定義済み変数の環境変数を使用します。
getenv('')の''の部分に値を代入しています。では、この環境変数はどこで定義するのかについて説明します。
Herokuのページに戻ってSettingsの「Config Vars」のところを開きます。
するとこのような欄があるかと思います。
ここの「KEY」に定義する環境変数、
「VALUE」に実際の値を入力します。「KEY」で入力した値を先ほどのファイルに入力することで「KEY」に対応した「VALUE」が読み込まれるという仕組みになってます！
入力が終わったらDBと接続したいPHPファイルに
require_once('DBを接続するファイル名.php');
を入力してください。入力例はこんな感じです。ここでは、WebサイトをDBとつなげるためのファイル名をdbconnect.php
としています。<?php require_once('dbconnect.php'); //DBと紐付ける値 $A = $_POST['a']; $B = $_POST['b']; $C = $_POST['c']; //DBのテーブルに上記の変数を代入する設定 $stmt = $dbh->prepare('INSERT INTO テーブル名 (カラムA, カラムB, カラムC) VALUES (?, ?, ?)'); //(?, ?, ?)に入る値の定義 $stmt->execute([$A, $B, $C]); ?>これでWebサイトとDBをつなげることができるかと思います。
これでDBとの接続も完了です！！以上がHerokuで自分のサイトを公開する方法です。
わからないこと、不明な点があればコメントをお願いします。
- 投稿日：2019-12-05T17:34:03+09:00
使う:Django-MySQL
この記事はDjango Advent Calendar 2019 5日目の記事です。
はじめに
DjangoのORMではselect_related, prefetch_related, Preftch, Qオブジェクト、などを駆使することによって開発するにあたって十二分なクエリを発行することができる。
しかし、できないこともいくつかあり、生SQLを書かなければならない、というケースもあったりする。例えば、複数のインデックスを貼っている場合、使いたいインデックスを指定する、
USE_IDNEXや、使いたくないインデックスを指定するIGNORE_INDEXなど。その場合、生SQLを書く必要がある。Django-MySQL
しかし、こちらのDjango-MySQLを使うことによって、生SQLを書かずにDjangoのORMを拡張し、
USE_IDNEXやIGNORE_INDEXといったクエリを発行できる。セットアップ
インストール
pip install django-mysqlsettings.pyの編集
INSTALLED_APPSにdjango-mysqlを追加する
INSTALLED_APPS = ( "django.contrib.admin", "django.contrib.auth", ... ... "django_mysql", )DJANGO_MYSQL_REWRITE_QUERIESを有効にする
DJANGO_MYSQL_REWRITE_QUERIES = Truesql_modeとinnodb_strict_modeの設定
DATABASES = { "default": { "ENGINE": "django.db.backends.mysql", "HOST": "127.0.0.1", "NAME": "hogehoge", "USER": "fugafuga", "OPTIONS": { "charset": "utf8mb4", "init_command": "SET sql_mode='STRICT_TRANS_TABLES', innodb_strict_mode=1", }, } }この変更による注意点として、STRICTモードを有効にすると、max_lengthを超えたデータをinsert/updateしようとするとエラーが発生する。
STRICTモードを有効にしていない場合、max_lengthを超えたデータをinsert/updateすると、超えた分を切り捨てて、エラーが発生せずに処理されるので、そこらへんのバリデーションをちゃんとやっていなかったりする場合は注意が必要。なお、この変更はDjangoからMySQLに接続するときの設定なので、新たにmigrateをしなおすとかそういうことは必要ない。
使ってみよう
modelの設定
usersアプリケーションにhogeというモデルを作ってみる。
フィールドのfuga,piyo両方にindexを貼る。users/hoge.pyfrom django.db import models from django_mysql.models import QuerySet class Hoge(models.Model): fuga = models.IntegerField(db_index=True) piyo = models.IntegerField(db_index=True) objects = QuerySet.as_manager()通常と違う点は
from django_mysql.models import QuerySetをして、ORMのマネージャに使用するという点。
これをやることによって、Django-MySQLで拡張したクエリを発行することができる。作られたテーブルの情報
mysql> show index from users_hoge; +------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | users_hoge | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | users_hoge | 1 | users_hoge_fuga_8a4fba19 | 1 | fuga | A | 0 | NULL | NULL | | BTREE | | | | users_hoge | 1 | users_hoge_piyo_4539c423 | 1 | piyo | A | 0 | NULL | NULL | | BTREE | | | +------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+こんな感じです。
IGNORE INDEXしてみる
fugaのindexを使わないでクエリ発行する、というケースの場合は、以下のようにやっていく。
Hoge.objects.all().ignore_index("users_hoge_fuga_8a4fba19")これによって発行されるSQLはこの通り
SELECT `users_hoge`.`id`, `users_hoge`.`fuga`, `users_hoge`.`piyo` FROM `users_hoge` IGNORE INDEX(`users_hoge_fuga_8a4fba19`);ちゃんとignore indexが発行されている。
なお、これをDjango-MySQLを使わずにやろうとすると
AttributeError: 'QuerySet' object has no attribute 'ignore_index'このようにエラーが出る。
おわり
今の所、IGNORE INDEXしか使ったことがないけれど他にも force indexが使えたり、その他便利機能があるので、詳細はドキュメントを読んでみてください。
通常のORMでは実現できずに、苦肉の策でSQLを書かなければ、というケースが発生したときに一度、Djnago-MySQLの導入を検討してみるといいかもしれません。余談
いつ見ても、ロゴ？というかキャラクターがやばい。
- 投稿日：2019-12-05T14:43:18+09:00
【 Laravel 6 】投稿の表示・修正・更新・削除
【 Laravel 6 】投稿アプリの基本的機能（一覧表示・詳細表示・修正・更新・削除）を実装する
2019年11月から、Laravel歴数ヶ月の初心者が投稿型ナレッジベースのコミュニティサイトを作るというチャレンジ中。作りたいアプリケーション→機能を因数分解→ググる→先人の轍をたどる（写経する）→ぬかるみにはまる→エラー解消の神を探す→解決を繰り返す日々( ·ㅂ·)و 。備忘録として、Qiitaに投稿しています。
一覧表示・詳細表示・修正・更新・削除
投稿型ナレッジベースということで、「投稿アプリ」を軸に色々と機能を付加していきます。
「投稿アプリ」については、こちら↓のサイトを参考に（ほぼ写経）させてもらいました ｱｻﾞﾏｽ！！m(__)m【入門】Laravelチュートリアル – 掲示板を作成してみよう
https://blog.hiroyuki90.com/articles/laravel-bbs/#i-2備忘録
私自身が素人すぎて、途中でつまずいたところを追記しておきます。
投稿一覧が表示されるViewファイル（resources/views/posts/index.blade.php）について、Laravelのバージョンアップにより非推奨となっている記述があったので、Laravel 5.8以降に推奨となった書き方に修正。
index.blade.php{!! nl2br(e(str_limit($post->body, 200))) !!} ← Laravel5.8から非推奨 {{ \Illuminate\Support\Str::limit($post->body, ２００) }} ← こちらの書き方に変更以上です(ง ´͈౪`͈)ว
- 投稿日：2019-12-05T14:31:45+09:00
Laravel 6.x 投稿型ナレッジベースのコミュニティサイトを作る
Laravel歴数ヶ月の初心者が投稿型ナレッジベースのコミュニティサイトを作る
ある趣味を盛り上げるためのコミュニティサイトを作ることを発想。Qiitaや、CrieitといったWeb開発系のものに着想を得て、投稿型のナレッジベースを主軸としたWebアプリケーションを作ります。
だいぶ初心者なので、他の方のブログやQiita投稿をガシガシ写経が基本です。詰まった部分について、備忘録としてQiitaに挙げていく感じでいきます。
機能一覧
入れたい機能は、こちら。順不同で、触れそうな機能から作っていくので、「作る⇄崩し」の繰り返しがありそう。機能実装の都度、Qiita投稿を下記の機能一覧にリンク貼っつけていく。
- Laravelプロジェクト新規作成（←機能では無いですが(·ε·)）
- ログイン機能
- ソーシャルログイン機能
- マイページ
- 投稿一覧
- ストック機能
- ユーザープロフィール
- 表示
- 修正・更新・削除
- 投稿の表示・修正・更新・削除
- マークダウンエディタ
- 画像のアップ
- ハッシュタグ機能
- Like機能
- ランディングページ
- 投稿一覧の表示
- カテゴリー別の投稿一覧（Like順or新着順）
- 広告
- 広告（アフェリエイト）
- データベース周り
環境
- Laravel 6.２
- php 7.2
- Bootstrap（とりあえず、動作のみを作成するので”ガワ”は基本ノータッチ・・・の予定）
背景
機能やサービス設計の諸々について考えあぐねていたら、気づけばどんどん時間ばかりが経過してしまっていた・・・(´ε｀；)ﾊｧ。アイディア思考ばかりで、コードからもどんどん離れていることに気づき、その焦りとで、結局どっちつかずの日々。
技術力が全く無いのに「考えても始まらんだろう」と思い、「手を動かす」。そして、開発の試行錯誤しながら、サービスをまとめていくということだけを決意。2019年11月中旬に制作開始しました。年越しのお籠り時期を経て、2020年1月中頃までに一通りの機能を回せるようになるといいなぁ・・・と画策中(´-_ゝ-`)
- 投稿日：2019-12-05T13:58:27+09:00
業務未経験者がWEB開発企業の面接に向けて、Java(lsp/servlet)のポートフォリオを作る。
★以下「前提条件」をクリア済みだとスムーズに作業が進みます。
前提条件
・Javaの実行環境がそろっている（環境変数設定ができている）
→わからない人は以下の様な資料が参考になります。
https://eng-entrance.com/java-install-env
それでもわからない場合は自力で調べるか他の人に聞きましょう。・Mavenが導入されている。
→以下の資料を参考にする場合「3.2. Mavenプラグインの設定」までを実行します。
「2.2. プロキシの設定」は現時点ではやらないでください。
https://qiita.com/tarosa0001/items/e5667cfa857529900216・MySQLが導入されている。
→以下の様な資料が参考になります。
https://qiita.com/FORTE/items/d31e7054aa89ba467f90
この資料の最後の「文字コードの設定」はファイルの実行権限がないため、
編集できない場合があります。
その場合は以下の様な資料を参考にして、権限を変えましょう。
https://qiita.com/murashi/items/708acd6b37aaf46b4fec・Githubのアカウントがある。
→作成方法は以下の様な資料が参考になります。
https://qiita.com/okumurakengo/items/848f7177765cf25fcde0・Herokuのアカウントがある。
→作成方法は以下の様な資料が参考になります。
https://programmingnavi.com/1438/・HerokuCLIの導入。
→作成方法は以下の様な資料が参考になります。
https://devcenter.heroku.com/articles/heroku-cli・Herokuにクレカ登録ができている。
→登録方法は以下の様な資料が参考になります。
https://wp.developapp.net/?p=5250・HerokuにてJavaビルドパックを導入。
→以下の資料では「Heroku側の下準備」が参考になります。
それ以外は今はやらないほうがいいです。
https://qiita.com/norihiko_tokudaiji/items/eb7c067b77757b20d113動的WEBプロジェクトの作成
いよいよ開発です(*‘ω‘ *)
・Eclipseにて「ファイル」→「新規」→「動的WEBプロジェクト」をクリック。すると以下の様な画面になります。・以下を入力または選択し「完了」を押下します。
→プロジェクト名:TestApp
→ターゲット・ランタイム：Tomcat8（Java8）
※Tomcat（Java）のバージョンは特に指定がない場合は”８”を指定しましょう。
この後使うHerokuのサポートが８推奨だからです。
・以下の様な画面になればプロジェクト作成は成功です
必要なファイルを作成していく
今回は以下の様なログイン画面を作成していきます。
必要なファイルとその役割は以下の通り。
●JSP(作成方法は→https://sukkiri.jp/technologies/ides/eclipse/jsp_create.html)
・index.jsp
→Webcontent直下に配備。
プロジェクト実行時に自動的にこのファイルが起動する。
・Webcontent/WEB-INF/jsp/login.jsp
・Webcontent/WEB-INF/jsp/loginFail.jsp
・Webcontent/WEB-INF/jsp/mainPage.jsp
→３つまとめましたがまず、WEB-INF直下に”jspフォルダ”を作成します。
そのあと３つのjspファイルを作成します。
それぞれのファイルの説明ですが、login.jspはログインページ、mainPage.jspはログインが成功されたときに表示されるメインページ、loginFail.jspはログインが失敗したときに表示されるページです。●Servlet(作成方法は→https://sukkiri.jp/technologies/ides/eclipse/servlet_create.html)
・LoginServlet.java
→ログインページの呼び出しと、DBへの接続メソッド呼び出し。●javaクラスファイル(作成方法は→https://sukkiri.jp/technologies/ides/eclipse/class_create.html)
・model/User.java
→パッケージ：model
ユーザ情報のゲッターとセッター。JavaBeanzに当たる。・model/CheckUserLogic.java
→パッケージ：model
DAOを呼び出すためのクラス。
Servletから直でDAOを呼び出してもいいのだけど、ワンクッション挟む方が無難らしい。
Ruby（Rails）だとあまりやないけど、ServletがControllerでDAOがmodelファイルみたいな感じだと思う。この場合だとModelに直接処理を書いてるみたいなイメージで思っている。・dao/UserDAO.java
→パッケージ：dao
DAOはDBとのやり取りをするクラス。
事実上MVCのMに当たりそう。●その他ファイル
・Procfile
→プロジェクトフォルダー直下に配備。今回ならTestApp直下。
ちなみにこのファイルはWEB公開するときに必要で、
ここに記載されているwarファイルを読み込むことで、本番環境でアプリが起動される。とりあえず今のところはこんな感じです。
文章だけだとわかりづらいので以下の画面も参照しながら、確実に作成しましょう。
作成したファイルにコードを書いていく。
index.jsp<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>TestApp</title> </head> <body> <div> <h1>ようこそ ゲスト さん</h1> <a href="./LoginServlet">ログインページへ＞＞</a> </div> </body> </html>login.jsp<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>TestApp</title> </head> <body> <div> <h3>Login</h3> <form action="./LoginServlet" method="post"> <p>・ニックネーム:</p> <input type="text" name="nickName"> <p>・パスワード:</p> <input type="password" name="password"> <button type="submit">Login</button> </form> </div> </body> </html>loginFail.jsp<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>TestApp</title> </head> <body> <div> <h3>Login</h3> <p style="color:red;">入力に誤りがあります。</p> <form action="./LoginServlet" method="post"> <p>・ニックネーム:</p> <input type="text" name="nickName"> <p>・パスワード:</p> <input type="password" name="password"> <button type="submit">Login</button> </form> </div> </body> </html>mainPage.jsp<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %> <%@ page import="model.User" %> <% User findedUser = (User) request.getAttribute("findedUser"); %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>TestApp</title> </head> <body> <div> <h3>メインページ</h3> <p>ようこそ<strong><%= findedUser.getNickName() %></strong>さん</p> </div> </body> </html>LoginServlet.javapackage servlet; import java.io.IOException; import javax.servlet.RequestDispatcher; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import model.CheckUserLogic; import model.User; @WebServlet("/LoginServlet") public class LoginServlet extends HttpServlet { private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { RequestDispatcher dispatcher = request.getRequestDispatcher("/WEB-INF/jsp/login.jsp"); dispatcher.forward(request, response); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // jspから送られた値を取得 String nickName = request.getParameter("nickName"); String password = request.getParameter("password"); // userインスタンスを生成しつつ、コントラスタを動かす。 User user = new User(nickName, password); // CheckUserLogicインスタンスを生成。 CheckUserLogic checkUser = new CheckUserLogic(); // executeメソッドの処理結果を新たに変数に入れる。入る値はnullかDAOで生成した新たなUserインスタンス。 User findedUser = checkUser.execute(user); // findedUserの処理結果に応じて表示するViewを選定する。 if (findedUser != null) { // Userインスタンスが入っていれば、結果をリクエストパラメータにセットしmainPageを表示する。 request.setAttribute("findedUser", findedUser); RequestDispatcher dispatcher = request.getRequestDispatcher("/WEB-INF/jsp/mainPage.jsp"); dispatcher.forward(request, response); } else { RequestDispatcher dispatcher = request.getRequestDispatcher("/WEB-INF/jsp/loginFail.jsp"); dispatcher.forward(request, response); } } }User.javapackage model; import java.io.Serializable; public class User implements Serializable { private String nickName; private String password; public User() { } public User(String nickName, String password) { this.nickName = nickName; this.password = password; } public String getNickName() { return nickName; } public String getPassword() { return password; } }CheckUserLogic.javapackage model; import dao.UserDAO; public class CheckUserLogic { public User execute(User user) { UserDAO dao = new UserDAO(); return dao.findUser(user); } }UserDAO.javapackage dao; import java.net.URI; import java.net.URISyntaxException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import model.User; public class UserDAO { private static Connection getConnection() throws URISyntaxException, SQLException { // heroku configに設定されている値を取得。 URI dbUri = new URI(System.getenv("CLEARDB_DATABASE_URL")); // :をデリミタとして必要な情報を抜き取る。 String username = dbUri.getUserInfo().split(":")[0]; String password = dbUri.getUserInfo().split(":")[1]; // JDBC用のURLを生成。 String dbUrl = "jdbc:mysql://" + dbUri.getHost() + dbUri.getPath(); return DriverManager.getConnection(dbUrl, username, password); } public User findUser(User user) { User findedUser = null; try { // ドライバの読み込み。 Class.forName ("com.mysql.cj.jdbc.Driver"); // データベースへ接続のためprivateメソッドを呼び出す。 try(Connection conn = getConnection()) { // SELECT文を準備。 String sql = "SELECT * FROM account WHERE nickname = ? AND password = ?"; PreparedStatement pStmt = conn.prepareStatement(sql); pStmt.setString(1, user.getNickName()); pStmt.setString(2, user.getPassword()); // SELECTを実行し、結果表を取得。 ResultSet rs = pStmt.executeQuery(); // 一致したユーザーが存在した場合、 // そのユーザーを表すfindedUserインスタンスを生成。 if (rs.next()) { // 結果表からデータを取得 String nickName = rs.getString("nickname"); String password = rs.getString("password"); findedUser = new User(nickName, password); } } catch (URISyntaxException e) { e.printStackTrace(); return null; } catch (SQLException e) { e.printStackTrace(); return null; } } catch (ClassNotFoundException e1) { e1.printStackTrace(); return null; } return findedUser; } }Procfileweb: java $JAVA_OPTS -jar target/dependency/webapp-runner.jar --port $PORT target/*.war・書き込んだら、サーバを追加（Tomcat8）しプロジェクトを実行しましょう。
サーバの追加方法→https://sukkiri.jp/technologies/ides/eclipse/server_add.html
プロジェクトの実行
→「プロジェクトを右クリック」→「実行」→「サーバで実行」
・以下の様な画面が表示されるはずです。
Mavenプロジェクトに変換。
デプロイするためにはMavanは必須です。
Mavenの説明→https://qiita.com/ASHITSUBO/items/6c2aa8dd55043781c6b4・プロジェクトを「右クリック」→「構成」→「Mavenプロジェクトに変換」をクリック。
すると以下の様な画面になります。
・基本的にはこのままで「完了」を押下します。
もし変更したい場合は以下の様な記事が参考になります。
https://itsakura.com/java-eclipse-maven
ちなみに、グループIdは何とかなりますが、アーティファクトIdを変更すると後々面倒です。
アプリ自体のURLが書き換わるので、Servletなどのリンクを書き直す必要があります。
当方はこれでかなり苦しみましたpom.xmlの編集。
Mavenに変更するとpom.xmlというファイルがプロジェクト直下に出来上がっていると思います。
これを以下の様に変更しましょう。pom.xml<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>test</groupId> <artifactId>test</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>war</packaging> <!--javaxを読込めるようにする--> <dependencies> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> </dependencies> <build> <sourceDirectory>src</sourceDirectory> <plugins> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>3.7.0</version> <configuration> <source>1.8</source> <target>1.8</target> </configuration> <!--ASMの導入。（http://atmarksharp.v01.jp/posts/asm-java-bytecode-generator.html）--> <dependencies> <dependency> <groupId>org.ow2.asm</groupId> <artifactId>asm</artifactId> <version>6.1</version> </dependency> </dependencies> </plugin> <plugin> <artifactId>maven-war-plugin</artifactId> <version>3.0.0</version> <configuration> <warSourceDirectory>WebContent</warSourceDirectory> </configuration> </plugin> <!-- アプリ依存のjarファイルを作成するためのプラグイン --> <!-- https://devcenter.heroku.com/articles/deploying-java --> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-dependency-plugin</artifactId> <executions> <execution> <phase>package</phase> <goals><goal>copy</goal></goals> <configuration> <artifactItems> <artifactItem> <groupId>com.github.jsimone</groupId> <artifactId>webapp-runner</artifactId> <version>9.0.27.0</version> <destFileName>webapp-runner.jar</destFileName> </artifactItem> </artifactItems> </configuration> </execution> </executions> </plugin> </plugins> </build> </project>・pom.xmlを「右クリック」→「実行」→「Maven clean」を実行。
「BUILD SUCCESS」が出ていればOKです。(表示例は以下画像参照)
・続いて同じく「Maven install」を実行します。
こちらも「BUILD SUCCESS」が出ていればOKです。・この「Maven clean」「Maven install」の作業をプロジェクトに対しても実行しましょう。
Githubにコードをアップする。
Eclipseで作成したプロジェクトをGithub上にアップします。
・以下「Github 上で新規リポジトリを作成する」を参考にしましょう。
またリポジトリ名はアプリと同じ名前（TestApp）にするとわかりやすいでしょう。
https://qiita.com/cotrpepe/items/7cafaacb538425a78f1f・プロジェクトをコミットします。
「プロジェクト右クリック」→「チーム」→「コミット」すると以下の様な画面になるので「＋＋」ボタンを押下します。
・コミットメッセージに「first commit」と入力し「コミットおよびプッシュ」を押下すると以下の様な画面になります。
・「URI」にGithubで作成したリポジトリーのURLを貼り付けます。
・次に「認証」の欄から、「ユーザ」を「パスワード」を入力し、「次へ」を押下します。
・そのまま「次へ」を押下します。
・ここもこのまま「完了」でOKです。
・おそらくこの画面が出ればOKです。
・Githubを確認し、プッシュしたものが上がっていれば成功です！
Herokuにアップする。
次にHerokuにアップします。
これができたら、とりあえずWEB公開自体は成功です。
一応ポートフォリオとして見せれるかと思います！・コマンドプロンプトまたはPowerShellを起動。
ちなみにPowershellの方が流行りなのと操作性に優れています。
起動は以下画像参照。
・PowerShellにてプロジェクト(TestApp)のworkspaseまで下ります。
例）「cd C:\pleiades-4.7.3-java-win-64bit-jre_20180411\pleiades\workspace\TestApp」
また以下の様にドラック＆ドロップでも行けます。
わからなければ、「PowerShell 使い方」などで検索してみましょう。
ググれカス！ってやつですね！・heroku CLIを起動しHeroku上にアプリをアップする。
PowerShell// プロジェクトに移動 > cd C:\pleiades-4.7.3-java-win-64bit-jre_20180411\pleiades\workspace\TestApp //現在の位置を確認します。アップしたいプロジェクト配下にいればOK。 > pwd Path ---- C:\pleiades-4.7.3-java-win-64bit-jre_20180411\pleiades\workspace\TestApp // HerokuCLIを使ってログインします。プロンプトが帰ってきたらEnterを押します。 > heroku login heroku: Press any key to open up the browser to login or q to exit: Opening browser to https://cli-auth.heroku.com/auth/browser/799a4794-66aa-46dd-ab9c-2aad6a4b3a6f・すると、ログイン画面が出てくるのでログインボタンを押下します。
・無事ログインできれば以下の様な画面が出てくるので画面を閉じ、PowerShellに戻りましょう。
PowerShell// Heroku上にアプリの箱を作成します。その際URLが自動的に発行されるのでメモしておきましょう。 > heroku create Creating app... done, ⬢ secure-hollows-65644 https://secure-hollows-65644.herokuapp.com/ | https://git.heroku.com/secure-hollows-65644.git //URLが自動生成されたら、ローカルのデータをリモート（Heroku）へアップします。 > git push heroku master Enumerating objects: 59, done. Counting objects: 100% (59/59), done. Delta compression using up to 4 threads Compressing objects: 100% (39/39), done. Writing objects: 100% (59/59), 20.40 MiB | 2.61 MiB/s, done. Total 59 (delta 1), reused 0 (delta 0) remote: Compressing source files... done. remote: Building source: remote: remote: -----> Java app detected remote: -----> Installing JDK 1.8... done remote: -----> Installing Maven 3.6.2... done remote: -----> Executing: mvn -DskipTests clean dependency:list install ＜中略＞ remote: [INFO] Installing /tmp/build_7748a56485859c3572741c4b0b374be0/pom.xml to /app/tmp/cache/.m2/repository/test/test/0.0.1-SNAPSHOT/test-0.0.1-SNAPSHOT.pom remote: [INFO] ------------------------------------------------------------------------ remote: [INFO] BUILD SUCCESS remote: [INFO] ------------------------------------------------------------------------ remote: [INFO] Total time: 13.463 s remote: [INFO] Finished at: 2019-12-05T02:46:08Z remote: [INFO] ------------------------------------------------------------------------ remote: -----> Discovering process types remote: Procfile declares types -> web remote: remote: -----> Compressing... remote: Done: 71M remote: -----> Launching... remote: Released v3 remote: https://secure-hollows-65644.herokuapp.com/ deployed to Heroku remote: remote: Verifying deploy... done. To https://git.heroku.com/secure-hollows-65644.git * [new branch] master -> master PS C:\pleiades-4.7.3-java-win-64bit-jre_20180411\pleiades\workspace\TestApp>・「deploy... done.」が出て入ればおそらく成功です。
・うまくいけば以下コマンドでWEBアプリが起動します。
PowerShell> heroku open・以下画像を参考にしてアプリの動作確認をします。
・ここまで確認できれば、index.jsp⇔LoginServlet.java⇔login.jsp,loginFail.jsp間のリンクは出来ているということになります。
MySQLを使うためにローカルを編集します。
ここで初めて「ローカル」といったワードが出てきましたが、手元にあるコードのことを指します。
またHerokuにアップしたアプリを「本番環境」や「Production」「リモート」など言ったりします。
基本的にはローカルで編集を行い、本番環境へアップし画面の確認を行うといった流れになります。・MySQLのドライバーをダウンロード。
以下の記事が分かりやすいと思います。
https://qiita.com/syoki/items/3d82ef00300868353572・ダウンロードしたファイルを２か所に格納します。
今回起動しているバージョンのTomcatファイルのlib配下
→例）C:\pleiades-4.7.3-java-win-64bit-jre_20180411\pleiades\tomcat\8\lib
プロジェクトの「Webcontent/WEB-INF/lib」配下にもjarを入れる。
→以下画像を参照。
コミットしてGithubを最新にする。
ローカルを編集したら、必ずGithubも更新します。
まずは以下を確認して、Eclipse上で「Gitステージング」を表示させてください。
https://tech.pjin.jp/blog/2015/07/01/git-on-eclipse-4-how-to-push/・コミットします。以下画像参照。
※コミットメッセージは基本英語です。今回は「add MySQL jar file」にしました。
構文は気にせず、伝わればいいと思います。Herokuも最新にします。
Powershellを閉じてしまってる場合は、プロジェクト(TestApp)のworkspaseまで下り「heroku login」をお願いします。
さっきまでの状態のままなら、「git push heroku master」だけ実行します。PowerShell//現在の位置を確認します。アップしたいプロジェクト配下にいればOK。 >pwd // 一度閉じてしまっている場合は、以下のコマンドも実行しましょう。 >heroku login //URLが自動生成されたら、ローカルのデータをリモート（Heroku）へアップします。 >git push heroku master // アプリを開きます。 >heroku open・アプリが開いたら、先ほどのように動作確認を行いましょう。
HerokuにMySQLを導入。
動作確認が出来たら、最後にMySQLを導入します。
導入後は、あらかじめMySQLにレコードを入れておきます。
そしてアプリの「Login」ボタン実行時に処理結果に応じた処理がなされます。
無事ログインできれば成功です。・アドオンを追加
→PowerShellにてTestApp配下にいること。CLIにてlogin状態であることを確認して下さい。PowerShell// 何も表示されないことを確認。 > heroku config === secure-hollows-65644 Config Vars // AddOnを追加します。 > heroku addons:create cleardb:ignite Creating cleardb:ignite on ⬢ secure-hollows-65644... free Created cleardb-flexible-21500 as CLEARDB_DATABASE_URL Use heroku addons:docs cleardb to view documentation // 今度は「CLEARDB_DATABASE_URL」が表示されることを確認。 > heroku config === secure-hollows-65644 Config Vars CLEARDB_DATABASE_URL: mysql://bc56879409bf95:0cebfc1d@us-cdbr-iron-east-05.cleardb.net/heroku_d80576c5913a375?reconnect=true・MySQL内部にログイン。
→「CLEARDB_DATABASE_URL」の構成は以下になります。
mysql://(--username--):(--password--)@(--hostname--)/(--dbname--)?reconnect=trueこれを元に以下のコマンドを組み立て、実行します。
mysql --user=(--username--) --password=(--password--) --host=(--hostname--) (--dbname--)
例）CLEARDB_DATABASE_URL: mysql://aaaaaaaaaaaaaa:bbbbbbbb@cc-cccc-cccc-cccc-05.cleardb.net/heroku_ddddddddddddddd?reconnect=true
↓
表示結果を元にコマンドを組み立てると・・・
↓
mysql --user=aaaaaaaaaaaaaa --password=bbbbbbbb --host=cc-cccc-cccc-cccc-05.cleardb.net heroku_dddddddddddddddPowerShell// 組み立てたコマンドを実行。 > mysql --user=bc56879409bf95 --password=0cebfc1d --host=us-cdbr-iron-east-05.cleardb.net heroku_d80576c5913a375 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 243376916 Server version: 5.6.42-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> // ↑↑↑mysql>が出ればOK。・コマンドを実行するとMySQLにログインできます。
次にテーブルを作成し、レコード追加、最後に確認を行います。PowerShellmysql> CREATE TABLE account -> ( -> id INT PRIMARY KEY AUTO_INCREMENT, -> nickname VARCHAR(16) NOT NULL, -> password VARCHAR(16) NOT NULL -> ); Query OK, 0 rows affected (0.21 sec) // Query OKが出ればOK。エラーならERROR～とか出る。 mysql> INSERT INTO account (NICKNAME, PASSWORD) VALUES('test1', 'test1+'); Query OK, 1 row affected (0.20 sec) mysql> SELECT * FROM account; +----+----------+----------+ | id | nickname | password | +----+----------+----------+ | 1 | test1 | test1+ | +----+----------+----------+ 1 row in set (0.19 sec)・上記の様にレコードがちゃんと表示されたらOK。
・「exit」でMySQLから抜けましょう。
アプリの最終確認。
ログイン成功確認まで実行します。
・「heroku open」または既に開いているアプリの更新を行いトップページを表示させます。
以下画像を参考にして動作確認を行います。
お疲れ様です、これでおしまいです。
- 投稿日：2019-12-05T11:23:34+09:00
SQL 基礎の備忘録
はじめに
SQLって意外とちゃんと習ったことが無いような気がして、現場では使っているけどちゃんと復習した際に、あ〜なるほど、そうですよね！ってなったことをメモします（タイトルにありますが、念のためもう一度。基礎です。）
* mysqlほぼ使ったことないですが一応mysqlを前提とします。Primary Keys
いやいやそんなこと忘れんな！って話ですがおさらい。
- 列に対してユニークな値を保持するためのKey
NULLは保持できないSQLの書き方
- 大文字小文字の区別は無い
- 大抵は大文字で揃える
- 空白と改行は無視される
- 文字列はシングルクオーテーション('')で囲む
SQL Operator
- = (Equal)
- != (Not Equal)
- > (Greater than)
- < (Smaller than)
- >= (Greater than or Equal)
- <= (Smaller than or Equal)
- BETWEEN (Between the range)
データ型
- INT
- FLOAT
- DOUBLE
- DATE (YYYY-MM-DD)
- DATETIME (YYYY-MM-DD HH:MM:SS)
- TIMESTAMP (January 1, 2000)
- TIME (HH:MM:SS)
- CHAR
- VARCHAR
- BLOB (Large Binary data such as an image)
- TEXT (Large text data)
Basic Commands
データベースの表示
SHOW DATABASES;テーブルの表示
SHOW TABLES;テーブルの情報表示
Field, Type, key, Default, Extraが表示される
SHOW COLUMNS FROM TABLE;レコードの選択
asterisk(*) の意味はALLとなる
SELECT * FROM TABLE;ユニークな値だけを取得
SELECT DISTINCT COLUMN_NAME FROM TABLE結果行数の指定
最初の例は10行だけ表示。
二番目の例は結果の3番目のポジションから10行だけを表示。
(mysqlは行数0から始まっているので行数が表示される場合+1になります）SELECT COLUMN_NAME FROM TABLE LIMIT 10; SELECT COLUMN_NAME FROM TABLE LIMIT 3, 10;レコードの並び替え
Defaultの並び順は
ASC（昇順）です。降順にしたい場合はDESCをカラム名の後につけます。
ORDER BYに複数カラムを指定した場合は、最初のカラムにて並び順がまず適用されます。そのあと2番目以降のカラム順で並びます。SELECT FROM COLUMN_NAME FROM TABLE ORDER BY COLUMN_NAME;取得する値の範囲指定
例では1~3の範囲でSELECT
SELECT * FROM TABLE WHERE ID BETWEEN 1 AND 3;WHERE句のオプション
- AND (指定した条件が全てTRUEの場合値が返る)
- OR (指定した条件どれかがTRUEの場合値が返る)
- IN (複数指定した条件が全てTRUEの場合値が返る)
- NOT （指定した条件がFALSEの場合値が返る）
ANDとORを併せた場合SELECT * FROM TABLE WHERE COLUMN = 'A' AND (NO = 10 OR NO = 15);
INの場合。
INの前にNOTをつければ条件に満たさない結果が返ってきます。SELECT * FROM TABLE WHERE COLUMN IN ('A', 'B', 'C');複数列の値を結合したい場合
CONCATを使います。間に挟んだ値で結合できます。
例だと,で値が結合されます。SELECT CONCAT(COLUMN_1, ', ' , COLUMN_2) FROM TABLE;
ASを使うことで結合した列の名前を指定できます。SELECT CONCAT(COLUMN_1, ', ' , COLUMN_2) AS NEW_COLUMN FROM TABLE;列の値を計算して結果を出力
また、列名に
+,-,*,/といった計算をさせることも可能で、この結果に対してASを使うこともできます。SELECT COLUMN_1, NO+10 AS NEW_NO FROM TABLE;結果を大/小文字にしたい時
UPPER/LOWERを使います。SELECT UPPER(COLUMN) AS UPPER_COLUMN FROM TABLE;平均値や合算値を求める
SELECT AVG(COLUMN) FROM TABLE; SELECT SUM(COLUMN) FROM TABLE; SELECT MAX(COLUMN) FROM TABLE; SELECT MIN(COLUMN) FROM TABLE;複数のSQLを一度にやっちゃいたい
Aの条件をして、次にBの条件をして、結果をだすという時に2つのSQLを書かずに1つのSQLで一気にやっちゃいたい時に。
()で囲むのが大切です。SELECT NO, FROM TABLE WHERE NO > (SELECT AVG(NO) FROM TABLE) ORDER BY NO DESC;曖昧な検索がしたいとき
LIKEを使います。例だとAから始まる名前のレコードが結果として返ってきます。
ワイルドカードは%です。SELECT NAME FROM TABLE WHERE NAME LIKE 'A%';Table Operation
JOIN (結合）
JOINは複数のテーブルに同じ列名（もしくは同じ意味合いの値を持つ列）がある場合、
それらを軸として複数のテーブルから同時に値を取得する感じです。
二個目のSQLはテーブルに簡易的な名前をつけている例で、取得できる値はもちろん丁寧に書いた一つ目と変わりません。SELECT TABLE_A.COLUMN_1, TABLE_B.COLUMN_2 FROM TABLE_A, TABLE_B WHERE TABLE_A.NO = TABLE_B.NO; SELECT A.COLUMN_1, B.COKUMN_2 FROM TABLE_A AS A, TABLE_B AS B WHERE A.NO = B.NO;さて恒例の、JOINのタイプについてです。
INNER JOIN
INNER JOINに関しては上記のただの
JOINに対して変わりはありません。（INNER JOIN、ONと明示的に書く必要はあります）
複数テーブルから、マッチした値を取得します。
SELECT NO FROM TABLE_1 INNER JOIN TABLE_2 ON TABLE_1.NO = TABLE_2.NO;LEFT JOIN
複数テーブル間でマッチした値と、左側のテーブルの検索条件にマッチした値を返します
OUTERに関してはoptionalです。
SELECT COLUMN_A, COLUMN_B FROM TABLE_1 LEFT (OUTER) JOIN TABLE_2 ON TABLE_1.NO = TABLE_2.NO;RIGHT JOIN
複数テーブル間でマッチした値と、右側のテーブルの検索条件にマッチした値を返します
OUTERに関してはoptionalです。
SELECT COLUMN_A, COLUMN_B FROM TABLE_1 RIGHT (OUTER) JOIN TABLE_2 ON TABLE_1.NO = TABLE_2.NO;UNION
複数テーブルを結合し、重複データを削除した値が返ってきます。しかし行うには下記条件があります。
- 結合する列は同じデータ型であること。
- 結合する列は同じ並びで指定すること。
もし列が片方のテーブルで足りない場合は、NULLを指定することで実行できます。SELECT COLUMN_1, COLUMN_2 FROM TABLE_A UNION SELECT COLUMN_1, COLUMN_2 FROM TABLE_B; SELECT COLUMN_1, COLUMN_2 FROM TABLE_A UNION SELECT COLUMN_1, NULL FROM TABLE_B;UNION ALL
UNION ALLは重複した値を削除しない。そのためUNIONより実行速度が早い。INSERT
INSERTはたまに構文忘れる。
INSERT INTO TABLE VALUES (VALUE_1, VALUE_2, VALUE_3); INSERT INTO TABLE (COLUMN_1, COLUMN_2, COLUMN_3) VALUES (VALUE_1, VALUE_2, VALUE_3);UPDATE
一度
WHERE句をつけ忘れてしまい、全てのテーブルデータをUPDATEしてしまったことがありました泣
何があってもWHEREをつけましょう！！UPDATE TABLE SET COLUMN_1 = VALUE_1, COLUMN_2 = VALUE_2 WHERE condition;DELETE
DELETE FROM TABLE WHERE NO = 1;CREATE TABLE
カラム名、データ型と指定します。
カラムに下記オプションを持たせることができます。データ型宣言の後に指定します。
- NOT NULL
- UNIQUE
- PRIMARY KEY (これだけ最下部に指定する)
- CHECK
- DEFAULT
- AUTO_INCREMENT (値がインサートされるたびに自動でインクリメント)
CREATE TABLE TABLE_1 ( COLUMN_1 int, COLUMN_2 varchar(100), COLUMN_3 varchar(100), COLUMN_4 varchar(100), PRIMARY KEY(COLUMN_1) );ALTER TABLE
ADDはカラムの追加（DEFAULT値はNULLになる）
DROPはカラムの削除ALTER TABLE TABLE ADD COLUMN_5 DATE; ALTER TABLE TABLE DROP COLUMN_5 DATE; ALTER TABLE TABLE CHANGE COLUMN_5 COLUMN_6 TIMESTAMP; RENAME TABLE COLUMN_6 TO COLUMN_7CREATE VIEW
CREATE VIEW TMP AS SELECT COLUMN_1, COLUMN_2 FROM TABLE; SELECT * FROM TMP; REPLACE VIEW TMP AS SELECT COLUMN_1, COLUMN_2 FROM TABLE; DROP VIEW TMP;
- 投稿日：2019-12-05T10:42:47+09:00
【MySQL】ERROR 1206 (HY000): The total number of locks exceeds the lock table size
SELECTしてINSERTしようとした時に，下記のエラーが出た．
どうやらデータが多くバッファプールが足りなくなったようです．ERROR 1206 (HY000): The total number of locks exceeds the lock table sizeそのため，
~/etc/my.confに
innodb_buffer_pool_size=404750336
を追記．sudo権限必要です．~/etc/my.confinnodb_buffer_pool_size=404750336更新したら再起動．
$ mysql.server restart確認すると
> show variables like 'innodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 536870912 | +-------------------------+-----------+ 1 row in set (0.00 sec)足される形になるんですね．
いろいろ調べてみると
https://qiita.com/mita2/items/8fd915164f0851c96e54
こちらの記事が参考になりました．
- 投稿日：2019-12-05T10:08:16+09:00
【Laravel】routes.phpでDBから値を取得する
routes.phpからレコードの値を取ってきてそのままviewへ表示させたい、という部分があったので、メモ程度に書きます。
環境
PHP : 5.6.30
MySQL : 10.1.21
Laravel : 5.1.46テーブル
以下サンプルテーブルです。
wordsテーブル
id message 1 Hello 2 こんにちは コード
route.phpRoute::get('/', function () { // cookieからidを設定する if ($_COOKIE['lang'] == 'en') { $id = 1; } else { $id = 2; } // DB::table()を使用して値を取得する $query = DB::table('words') ->select('message') ->where('id', $id) ->first(); $message = $query->message return view('main',['message' => $message]); }main.blade.php<!DOCTYPE html> <html> <head> <meta charset="utf-8"> </head> <body> {{$message}} </body> </html>bladeの方はおまけ的な感じですが、一応念のため。
- 投稿日：2019-12-05T09:48:33+09:00
MySQL8.0 の CLONE プラグインで色々試してみる
この記事は、MySQL Advent Calendar 2019の5日目の記事です。
MySQL 8.0.17 で実装された「CLONEプラグイン」ですが、登場して以来多くの反響が見受けられます。
その証拠に、既に以下のようにCLONEを取り上げる記事が多く書かれています。
- Clone: Create MySQL instance replica
- MySQL 8.0.17 Clone Plugin: How to Create a Slave from Scratch
- MySQL 8.0.17でついにCloneプラグインが入った
- MySQL8.0.17で導入されたCLONEプラグインについて
基本的なCLONE機能の使い方については上記で取り上げられているので、この記事ではCLONE機能の細かな部分に着目してみたいと思います。
0. 検証環境
今回試す環境は、CentOS7サーバ上にdbdeployerを用いてお手軽に構築しています。
$ dbdeployer deploy replication 8.0.18 $ ./rsandbox_8_0_18/use_all -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so'"ちなみにdbdeployerでもCLONE機能を正式サポートしていて、レプリケーションスレーブの作成などに利用できます。
→ 詳しくは公式ページの『Cloning databases』の章などを読んでくださいデプロイ後、簡単にテストデータを作成します。
$ ./rsandbox_8_0_18/master/use -e "CREATE DATABASE d1" $ ./rsandbox_8_0_18/master/use d1mysql> CREATE TABLE t1 (id int, col1 char(10)); mysql> CREATE TABLE t2 (id int, col2 char(10), col3 text); mysql> INSERT INTO t1 VALUES (1,"aaa"),(2,"bbb"); mysql> INSERT INTO t2 VALUES (10,"A","This is test."),(20,"X","これはテストです。");1. CLONEデータを部分リストアしてみる
CLONE機能では以下のバックアップ（スナップショット）を取得します。対象はInnoDBテーブルに限定されます。
・スキーマ
・テーブル
・テーブルスペース
・データディクショナリのメタデータまた、この時の取得範囲は必ず「全テーブル」となりますが、取得したCLONEデータの一部のテーブルのみを復元させることができないか試してみました。
$ mkdir ./rsandbox_8_0_18/clone_data $ ./rsandbox_8_0_18/master/use -u root -e "CLONE LOCAL DATA DIRECTORY '<path to sandbox>/rsandbox_8_0_18/clone_data'" $ ls ./rsandbox_8_0_18/clone_data/ #clone d1 ib_buffer_pool ib_logfile0 ib_logfile1 ibdata1 mysql mysql.ibd sys undo_001 undo_002このうち、d1.t2テーブルのみを新しくデプロイしたインスタンス（以下、新DB）にリストアします。
$ dbdeployer deploy single 8.0.18(1) 単純に
t2.ibdをコピーしてみるまずは単純に新DBのデータディレクトリ上に.ibdファイルをコピーしてみます。
ただ単にコピーしただけではそもそもコピーしてきたd1ディレクトリがデータベースとして認識されません$ ./msb_8_0_18/stop $ cp -r ./rsandbox_8_0_18/clone_data/d1/ ./msb_8_0_18/data/ $ rm ./msb_8_0_18/data/d1/t1.ibd $ ./msb_8_0_18/start ...... sandbox server started $ ./msb_8_0_18/use -e "SELECT * FROM d1.t2" ERROR 1049 (42000) at line 1: Unknown database 'd1'(2) システムテーブル情報をコピーする
各テーブルのメタ情報などはMySQLのシステムテーブルが保持しています。これは mysql データベースにまとまっているので、クローンデータからまとめてコピーしてみると、t2テーブルが動作しました。
$ ./msb_8_0_18/stop $ cp -r ./rsandbox_8_0_18/clone_data/mysql ./msb_8_0_18/data/ $ cp ./rsandbox_8_0_18/clone_data/mysql.ibd ./msb_8_0_18/data/ $ ./msb_8_0_18/start .... sandbox server started $ ./msb_8_0_18/use -e "INSERT INTO d1.t2 VALUES (100,'XXX','test test')" $ ./msb_8_0_18/use -e "SELECT * FROM d1.t2" +------+------+-----------------------------+ | id | col2 | col3 | +------+------+-----------------------------+ | 100 | XXX | test test | | 10 | A | This is test. | | 20 | X | これはテストです。 | +------+------+-----------------------------+もちろん、既存のシステムテーブルが上書きされる点に注意してください。また、システムテーブル上は t1 テーブルも存在していることになっているので、気になる場合は DROP TABLE でメタデータを消してしまいましょう。
$ ./msb_8_0_18/use -e "SHOW TABLES FROM d1" +--------------+ | Tables_in_d1 | +--------------+ | t1 | | t2 | +--------------+ $ ./msb_8_0_18/use -e "SELECT * FROM d1.t1" ERROR 1812 (HY000) at line 1: Tablespace is missing for table `d1`.`t1`. $ ./msb_8_0_18/use -e "DROP TABLE d1.t1" $ ./msb_8_0_18/use -e "SHOW TABLES FROM d1" +--------------+ | Tables_in_d1 | +--------------+ | t2 | +--------------+ちなみに、空のテーブルだけ用意してそこに.ibdファイルを上書きする方法ではダメでした。
$ ./msb_8_0_18/use -e "DROP DATABASE d1" $ ./msb_8_0_18/use -e "CREATE DATABASE d1" $ ./msb_8_0_18/use -e "CREATE TABLE d1.t2 (id int, col2 char(10), col3 text)" $ ./msb_8_0_18/stop $ cp ./rsandbox_8_0_18/clone_data/d1/t2.ibd ./msb_8_0_18/data/d1/ $ ./msb_8_0_18/start ................................................................................................................................................................................... sandbox server not started yet(3) トランザクションログ（Redoログ）をコピーする
今回はDBにアクセスが無い状態でCLONEデータを取得したので、トランザクションログ（Redoログ）に更新がないので無視して構いません。ただ、実際の環境ではオンライン状態でCLONEを取得すると思うので、これらも合わせてコピーする必要がありそうです。
$ ls ./rsandbox_8_0_18/clone_data/ #clone d1 ib_buffer_pool ib_logfile0 ib_logfile1 ibdata1 mysql mysql.ibd sys undo_001 undo_0022. 他バージョンのCLONEデータをリストアしてみる
次にバージョンが異なるMySQLから取得したCLONEデータをリストアした場合にどのような挙動になるのかを、8.0.17 / 8.0.18 で確認してみました。
■ 8.0.18 → 8.0.17
8.0.18で取得したCLONEデータを、8.0.17でリストアします。
※ CLONEデータは上記で作ったものを流用します
$ dbdeployer deploy single 8.0.17 $ ./msb_8_0_17/stop $ cp -r ./rsandbox_8_0_18/clone_data/* ./msb_8_0_17/data/ $ ./msb_8_0_17/start ................................................................................................................................................................................... sandbox server not started yetエラーで起動できませんでした。エラーログを見ると、データディクショナリのバージョンの不一致が原因と出ており、ダウングレードはできない仕様のようです。
$ tail ./msb_8_0_17/data/msandbox.err ... 2019-12-04T08:35:30.225093Z 1 [ERROR] [MY-013171] [InnoDB] Cannot boot server version 80017 on data directory built by version 80018. Downgrade is not supported 2019-12-04T08:35:35.318429Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine 2019-12-04T08:35:35.318880Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2019-12-04T08:35:35.319736Z 0 [ERROR] [MY-010119] [Server] Aborting■ 8.0.17 → 8.0.18
次に 8.0.17 のCLONEデータを8.0.18でリストアしてみます。
$ dbdeployer delete ./msb_8_0_17/ $ dbdeployer deploy single 8.0.17 $ ./msb_8_0_17/use -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so'" $ ./msb_8_0_17/use -e "CREATE DATABASE d1" $ ./msb_8_0_17/use -e "CREATE TABLE d1.t1 (id int)" $ ./msb_8_0_17/use -e "INSERt INTO d1.t1 VALUES (100)" $ ./msb_8_0_17/use -u root -e "CLONE LOCAL DATA DIRECTORY '<path to sandbox>/msb_8_0_17/clone_data'" $ dbdeployer delete ./msb_8_0_18/ $ dbdeployer deploy single 8.0.18 $ ./msb_8_0_18/stop $ cp -r ./msb_8_0_17/clone_data/* ./msb_8_0_18/data/ $ ./msb_8_0_18/start .............. sandbox server startedこのパターンでは起動できました。どうやら、8.0.17 → 8.0.18 のようなアップグレードのケースではCLONEが利用できそうです。エラーログにも問題なくアップグレードできたメッセージが記録されていました。
$ ./msb_8_0_18/use -e "SELECT * FROM d1.t1" +------+ | id | +------+ | 100 | +------+ $ tail ./msb_8_0_18/data/msandbox.err … 2019-12-04T09:17:03.597265Z 4 [System] [MY-013381] [Server] Server upgrade from '80017' to '80018' started. 2019-12-04T09:17:10.635010Z 4 [System] [MY-013381] [Server] Server upgrade from '80017' to '80018' completed.なお、以下の公式マニュアルの通り、リモートでCLONEを実行する場合はDONORとRECIPIENTのバージョンは統一する必要があるようです。
5.6.7.13 Clone Plugin Limitations
An instance cannot be cloned from a different MySQL server version. The donor and recipient must have the same MySQL server version.
3. CLONE実行中に設定を変更してみる
最後はCLONE実行中にCLONE関連のパラメータ変数を変更するとどのような挙動になるか確認したいと思います。
今回は、clone_max_concurrency変数をCLONE実行中に変更して、データ転送の速度を制御できないか試してみます。
まずは大きめのテストデータを生成しましょう。
$ ./msb_8_0_18/use d1 -e "CREATE TABLE large_t \ (id int auto_increment primary key, col1 text, col2 text, col3 text)" $ ./msb_8_0_18/use d1 -e "INSERT INTO large_t(col1,col2,col3) SELECT \ SUBSTRING(MD5(RAND()),1,100),SUBSTRING(MD5(RAND()),1,100),SUBSTRING(MD5(RAND()),1,100)" ### 以下を何度か繰り返す $ ./msb_8_0_18/use d1 -e "INSERT INTO large_t(col1,col2,col3) SELECT \ SUBSTRING(MD5(RAND()),1,100),SUBSTRING(MD5(RAND()),1,100),SUBSTRING(MD5(RAND()),1,100) \ FROM large_t" ... $ ./msb_8_0_18/use d1 -e "SELECT count(*) FROM large_t" +----------+ | count(*) | +----------+ | 33554432 | +----------+clone_max_concurrencyを"1"にした後にCLONEをバックグラウンド実行し、同変数を"16"（デフォルト）に戻した時の挙動を確認します。
また、この時CLONE操作の自動拡張（clone_autotune_concurrency)もOFFにしておきます。※ これらの変数はリモートCLONE実行時のRECIPIENTノードで機能します
### 別のMySQL8.0.18のDONOR関連の設定を変更する $ ./rsandbox_8_0_18/node2/use -e "SET GLOBAL clone_max_concurrency = 2; SET GLOBAL clone_autotune_concurrency=OFF" $ ./rsandbox_8_0_18/node2/use -e "SHOW GLOBAL VARIABLES LIKE 'clone_max_concurrency'" +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | clone_max_concurrency | 2 | +-----------------------+-------+ ### リモートCLONEをバックグラウンド実行する $ ./rsandbox_8_0_18/node2/use -e "SET GLOBAL clone_valid_donor_list = '127.0.0.1:8018'" $ ./rsandbox_8_0_18/node2/use -e "CLONE INSTANCE FROM root@127.0.0.1:8018 IDENTIFIED BY 'msandbox'" & ### cloneを実行する（Sending to client）スレッド数が"2"であることを確認する $ ./msb_8_0_18/use -e "SHOW PROCESSLIST" +-----+-----------------+-----------------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-----------------+------+---------+------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 7449 | Waiting on empty queue | NULL | | 122 | root | localhost:33624 | NULL | clone | 18 | Sending to client | NULL | | 123 | root | localhost:33626 | NULL | clone | 17 | Receiving from client | NULL | | 124 | root | localhost:33644 | NULL | clone | 16 | Sending to client | NULL | | 127 | msandbox | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | +-----+-----------------+-----------------+------+---------+------+------------------------+------------------+ ### clone_max_concurrencyを16に戻す $ ./rsandbox_8_0_18/node2/use -e "SET GLOBAL clone_max_concurrency = 16" $ ./msb_8_0_18/use -e "SHOW PROCESSLIST" +-----+-----------------+-----------------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------------+-----------------+------+---------+------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 7469 | Waiting on empty queue | NULL | | 122 | root | localhost:33624 | NULL | clone | 38 | Sending to client | NULL | | 123 | root | localhost:33626 | NULL | clone | 37 | Receiving from client | NULL | | 124 | root | localhost:33644 | NULL | clone | 36 | Sending to client | NULL | | 132 | msandbox | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | +-----+-----------------+-----------------+------+---------+------+------------------------+------------------+流石にCLONE実行中に設定を変更しても即時反映されることは無さそうです。事前に必要なネットワーク帯域などを考慮する必要がありますね。
おわりに
今回はパッと思いつくアイデアを試してみましたが、CLONE機能にはまだまだ判明していない挙動や仕様がありそうでワクワクします。
是非、積極的に試してみて各種ブログなどでシェアしてみてください！（特に本番環境での利用実績！）
- 投稿日：2019-12-05T08:31:27+09:00
[CakePHP]助けて！hasManyで結合したテーブルが取得できないの！[MySQL]
テーブル
ゲームから見てメーカーは多対1の関係(belongsTo)レビューは1対多(hasMany)の関係。
GamesTable.phpclass GamesTable extends Table { public function initialize(array $config) { $this->hasMany('Reviews'); $this->belongsTo('Makers'); } }selectしようとしたらなんかエラー出たんだけど！
SQLを発行するときに、データをselectするのはいい考えですね。
クエリビルダーはselectメソッドを使わない場合、全てのカラムがselectされますが、
パフォーマンスを考えると必要なカラムだけ取得したいものです。ReviewController.php<?php namespace App\Controller; use App\Controller\AppController; use Cake\ORM\TableRegistry; class GameController extends AppController { public function index() { $reviews_table = TableRegistry::get('Games'); $review = $reviews_table->find() ->contain('Makers', 'Reviews') ->select([ 'title' => 'Games.title', 'price' => 'Games.price', 'name' => 'Makers.name', 'comment' => 'Reviews.comment' ]); } }しかし、Reviews.commentなんてカラムはないよって怒られます。
でも同じアソシエーションしてるMakersテーブルからは持ってこれてるじゃん！なんで！？これは、belongsToとhasManyとで発行しているSQLの違いにあります。
クエリオーオブジェクトをdebug()すると、データベースで発行してるSQLを見ることができます。SELECT Games.title AS title, Games.price AS price, Makers.name AS name FROM Games LEFT JOIN Makers ON Games.id = Makers.Game_id; SELECT Reviews.comment FROM Revies WHERE Game_id IN (1, 2, 3); /* このIN句の数字は上記のSQLで発行されたGamesテーブル主キー*/だいたいこんな感じのSQLが発行されます。
belongsToで関連付けられたテーブルは、LEFT JOINが使用されていることがわかりますね。hasManyで関連付けれたテーブルは、複数のSQLに分割して発行されます。
ちなみに4つのアソシエーションがどのようなSQLを発行するかはこんな感じ。
アソシエーション種別 発行されるSQL hasOne LEFT JOIN belongsTo LEFT JOIN hasMany 分割 belongsToMany 分割 つまりどういうことだってばよ!?
クエリビルダーで使ったselectメソッドは、１つ目のSQL文に対して適応されます。
一目瞭然ですが、１つ目に発行されたSQL分にはReviews.commentなんてカラムはかけらも見当たらないですね。だからCakePHPちゃんは「お前の指定したカラム見つかれねえんだけど!!」とお怒りだったわけです。
それじゃあhasManyやbelongsToManyでSELECTしたいときは？
containにクロージャーを渡してあげます。
$review = $reviews_table->find() ->contain(['Makers', 'Reviews' => function ($q) { return $q->select([ 'Game_id', 'comment' ]); }]) ->select([ 'title' => 'Games.title', 'price' => 'Games.price', 'name' => 'Makers.name', ]); ]);ここで注意したいのは、関連テーブルをselectする際には、外部キーを確実にselectする必要がある。ということです。
外部キーは規約にしたがってると、ここではGame_idになります。公式にもそう書いてありますね。
関連によってフェッチされるフィールドを限定する場合、外部キーの列が確実に select されなければなりません 。外部キーのカラムが select されない場合、関連データが 最終的な結果の中に無いということがおこります。
って書いてあるけど外部キーがselectされてなかったエラー吐いてくれます。
主キー側も(binding key)もselectされてなければならない！！
それよりもやっかいなのはこっち。
主キーつまり、Gamesテーブルのidをselectしていないと、エラーを吐かないでただただhasMany関連が空で取得されます。
結構気づきくい。まあこれは、hasManyで発行されるSQLを見ればなんでそうなるかなんとなく理解できると思います。
最終的にクエリビルダーはこうなる。
$review = $reviews_table->find() ->contain(['Makers', 'Reviews' => function ($q) { return $q->select([ 'Game_id' 'comment' => 'Reviews.comment' ]); }]) ->select([ 'id', 'title' => 'Games.title', 'price' => 'Games.price', 'name' => 'Makers.name', ]); ]);もう一個ハマリポイント
主キーをselectしなきゃいけないけど、Makersテーブルとは
LEFT JOINしてるから、idはアンビシャスになるんだろうなーって思いつつこんな書き方したら、->select([ 'id' => 'Games.id' 'title' => 'Games.title', 'price' => 'Games.price', 'name' => 'Makers.name', ]);だめだった。これもhasManyを取得できないです。
selectしなかった時にSQLログを見ると、Games.id AS Games__idみたいな書き方されてたから、->select([ 'Games__id' => 'Games.id' 'title' => 'Games.title', 'price' => 'Games.price', 'name' => 'Makers.name', ]);って感じにしたらうまくいきました。
というか、そもそもさっきみたいにidをselectしてもアンビシャスにならないっぽいですね。
- 投稿日：2019-12-05T00:20:56+09:00
MySQL のバイナリログを整理してディスクを空けた話
株式会社オズビジョンの @terra_yucco です。
Qiita 投稿自体久々なのですが、また地道に自分のログを書き溜めていこうと思います。
※まだログしかありませんが、以前にオススメいただいた Qrunch も始めました。アカウントに-や_が使えなかったので @terrayucco でやっております。本日は自分の開発環境をあふれさせ、ディスク拡張ではなく不要ファイル整理で解消したのでそちらのメモを置いておきます。
TL; DR
本番環境のデータ dump を import した MySQL を local vagrant 上で動かしていたら、ディスク領域が 100% になったので、拡張ではなく不要ファイル削除で対応した話。
より詳しく
事象説明
本番環境のデータ (100 万件くらい) を dump して local vagrant の MySQL にリストアし、バッチ処理を流すテストを何度も実施。(リランのテストなど兼ねていたので)
そうしたら、何回目かで
"Error writing file '\/tmp\/MYuQz1fV' (Errcode: 28)"的なメッセージを吐き出してバッチが全く動かないようになった。状況
ほんの少しだけ空いていますが、ルートディスクを 100% 使い切っていました。
$ df ファイルシス 1K-ブロック 使用 使用可 使用% マウント位置 /dev/mapper/centos-root 8775680 8775000 680 100% / devtmpfs 498600 0 498600 0% /dev tmpfs 508684 0 508684 0% /dev/shm tmpfs 508684 6668 502016 2% /run tmpfs 508684 0 508684 0% /sys/fs/cgroup /dev/sda1 508588 149056 359532 30% /boot 192.168.33.1:/C/work/batch-code 248611840 212789792 35822048 86% /vagrant tmpfs 101740 0 101740 0% /run/user/0
du -sch ./*を繰り返して階層を下って行ったところ、大きかったのは以下の場所。$ du -sch /usr/local/mysql/* 20K /usr/local/mysql/COPYING 132K /usr/local/mysql/INSTALL-BINARY 4.0K /usr/local/mysql/README 155M /usr/local/mysql/bin 5.1G /usr/local/mysql/data 16K /usr/local/mysql/docs 704K /usr/local/mysql/include 15M /usr/local/mysql/lib 760K /usr/local/mysql/man 122M /usr/local/mysql/mysql-test 16K /usr/local/mysql/scripts 2.5M /usr/local/mysql/share 2.8M /usr/local/mysql/sql-bench 100K /usr/local/mysql/support-files 5.4G 合計
/usr/local/mysql/dataということで、消えても local のデータなので良いだろうということでここを空けました。空け方
一覧
mysql-bin.000xxxは MySQL のバイナリログファイルということで、レプリケーションが終わっていれば (特に個人の local では) 消して良いもの。1
なんと一年以上消していなかったらしく、そりゃ溜まるはずだという感じでした。$ ls -ltr 合計 5274624 drwxr-xr-x 2 mysql mysql 19 3月 18 2016 test drwx------ 2 mysql mysql 4096 3月 18 2016 performance_schema drwx------ 2 mysql root 4096 3月 18 2016 mysql -rw-rw---- 1 mysql mysql 264 3月 18 2016 mysql-bin.000001 : // mysql-bin.000xxx が連番で続く drwx------ 2 mysql mysql 8192 11月 22 12:05 hoge -rw-rw---- 1 mysql mysql 5242880 11月 26 09:57 ib_logfile1 -rw-rw---- 1 mysql mysql 2717 12月 4 14:06 mysql-bin.index -rw-rw---- 1 mysql mysql 5 12月 4 14:06 localhost.localdomain.pid -rw-rw---- 1 mysql mysql 1193279488 12月 4 15:05 ibdata1 -rw-rw---- 1 mysql mysql 5242880 12月 4 15:05 ib_logfile0 -rw-rw---- 1 mysql mysql 153083532 12月 4 15:33 mysql-bin.000143 -rw-r----- 1 mysql root 457642 12月 4 15:33 localhost.localdomain.errコマンド
ここで
rmしてはダメで、
mysql 上で (権限のあるユーザで) 以下を実行します。上記の例では、最新のバイナリログファイルは
mysql-bin.000143なので、このファイルだけを残しました。mysql> PURGE BINARY LOGS TO 'mysql-bin.000142'; Query OK, 0 rows affected (0.02 sec)結果はスッキリ。
ひそかにmysql-bin.indexも更新されていました。[root@localhost data]# ls -ltr 合計 1424124 drwxr-xr-x 2 mysql mysql 19 3月 18 2016 test drwx------ 2 mysql mysql 4096 3月 18 2016 performance_schema drwx------ 2 mysql root 4096 3月 18 2016 mysql drwx------ 2 mysql mysql 8192 11月 22 12:05 hoge -rw-rw---- 1 mysql mysql 5242880 11月 26 09:57 ib_logfile1 -rw-rw---- 1 mysql mysql 5 12月 4 14:06 localhost.localdomain.pid -rw-rw---- 1 mysql mysql 1193279488 12月 4 15:05 ibdata1 -rw-rw---- 1 mysql mysql 5242880 12月 4 15:05 ib_logfile0 -rw-rw---- 1 mysql mysql 153083532 12月 4 15:33 mysql-bin.000143 -rw-r----- 1 mysql root 457642 12月 4 15:33 localhost.localdomain.err -rw-rw---- 1 mysql mysql 38 12月 4 15:39 mysql-bin.indexまとめ
MySQL 利用環境で Disk 利用率が高くなったら、バイナリログの整理でスッキリできることも。
Vagrantディスクフルなどで検索すると、ディスク容量を増やす方法の方がよく出てきますが、不要なものであれば削除してスッキリ使いたい派なので、このエントリを書くに至りました。
local 環境であれば普段本番では触れられない MySQL の管理系コマンドも試せるので、いろいろと学びになりました。2
ローカル環境であれば my.cnf に
expire_logs_days=7とか書いておけば回避できそうです。(書いてなかった) ↩