20200225のMySQLに関する記事は6件です。

tblsでデータベースのスキーマ情報をマークダウンで出力する

概要

DBのスキーマ情報をドキュメント化したいと思ったので使用したツールをメモします。

使用方法

tblsのgithubを参考。

dockerイメージの入手

$ docker pull k1low/tbls:latest

1コマンドでデータベースをドキュメント化できる手軽さ。

$ tbls doc postgres://dbuser:dbpass@hostname:5432/dbname

dockerでやる場合は以下です。

$ docker run --rm -v $PWD:/work k1low/tbls doc postgres://dbuser:dbpass@hostname:5432/dbname

筆者の場合は、mysqlなので、以下です。

$ docker run --rm -v $PWD:/work k1low/tbls doc mysql://dbuser:dbpass@hostname:3306/dbname
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

データベースにおける『ロック』ってなんだ?

データベースにおけるロック ってなんだ?

READ LOCKWRITE LOCK という2つのロックがある。

ロック種別 ロックを取ったスレッド その他のスレッド
READ READのみ可、WRITEするとエラー READのみ可能。WRITEするとブロック
WRITE READ/WRITE可能 READ/WRITE不可。ブロックされる。

READ LOCKを試した(MySQL)

  • スレッドAでREAD LOCK
LOCK TABLES user READ;
  • スレッドBでREAD/WRITEしてみる
mysql> select * from user;
+------+----------+-------+----------+
| id   | username | email | password |
+------+----------+-------+----------+
|    1 | hoge     | hoge  | hoge     |
mysql> INSERT INTO user VALUES ('1','hoge','hoge','hoge');
# 待機
  • スレッドAでREAD LOCKを解除
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
  • スレッドBのWRITEが実行される
mysql> INSERT INTO user VALUES ('1','hoge','hoge','hoge');
Query OK, 1 row affected (39.20 sec)

WRITE LOCKを試した(MySQL)

  • スレッドAでWRITE LOCK
mysql> LOCK TABLES user WRITE;
Query OK, 0 rows affected (0.00 sec)
  • スレッドBでREAD
mysql> select * from user;
# 待機
  • スレッドAでWRITE LOCKを解除
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
  • スレッドBのREADが実行される
mysql> select * from user;
^[[A+------+----------+-------+----------+
| id   | username | email | password |
+------+----------+-------+----------+
|    1 | hoge     | hoge  | hoge     |
・・・
5 rows in set (48.68 sec)

スレッド

  • show processlist;でスレッドの情報を確認すると、当該スレッドがロックされていることが確認できる。
    • ロックされているスレッドが多いということは、テーブルロックを長時間に渡って持ったままになる処理がある可能性がある。
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+--------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info               |
+----+------+-----------+------+---------+------+---------------------------------+--------------------+
|  2 | root | localhost | foo  | Sleep   |   22 |                                 | NULL               |
|  3 | root | localhost | foo  | Query   |   20 | Waiting for table metadata lock | select * from user |
|  6 | root | localhost | NULL | Query   |    0 | starting                        | show processlist   |
+----+------+-----------+------+---------+------+---------------------------------+--------------------+
3 rows in set (0.00 sec)

「グローバルな読み取りロック」とは?

FLUSH TABLES WITH READ LOCKにより データベース全体 のREAD LOCKを取得する。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

Django:MigrationでモデルがDBに反映されない

反映されない

表題の通り、マイグレーションしてもDBに反映されず苦しんだので記録しておきます。

環境

・Windows10 Enterprise
・Docker Desktop for Windows:2.1.0.1
・MySQL:5.7.28(Docker上)
・Django:3.0(Docker上)

経緯

models.pyに新モデル(モデルAとします)を追加してマイグレーションを行った所、DBに反映されず。
他モデルの変更は反映されるので、モデルAのみ問題と推定。

色々やってみた

下記記事などを参考に色々やってみました。

・DjangoでmigrateしてるのにDBに反映されない時の対処法【Djangoアウトプット】
・DjangoでMigrationsのリセット方法(既存のデータベースを残したまま)
・djangoでmigrateができない
・Djangoのmodels.pyの反映(python manage.py makemigrations/migrate)
・modelにカラムを追加したのにmigrationファイルに追加されない現象
・python manage.py migrationが効かない時の対処法 [Django]

大体「python manage.py migrate --fake hoge zero」でマイグレーション履歴削除して、フォルダのファイルも削除後、makemigrationしてmigrateすれば反映されるでしょ、という事でしたが全滅。

おまえだったのか

で、一旦DBドロップして再度マイグレーションしてみると…モデルA以外にもうひとつモデル(モデルBとします)が反映されていない。
「?」と思いよくよく「models.py」を読んでいるとこれら2つのモデルのみ「class Meta:」の中に「managed = False」の文字が。

models.py
    class Meta:
        managed = False
        db_table = 'temp'

試しにこれを削除して再度マイグレーションしたら、出来ました。あっさりと。
モデルBはどこかからかコピーしてきた書式をペーストしていて、これをコピペしてモデルAを作成したので今回のトラップにかかった様です。
「managed = False」と書く事でマイグレーションの管理対象外とするそうな。そりゃ反映されない筈だ。
(「経緯」で他モデルの変更は反映されたと書いていますが、モデルBでは試していませんでした)

という訳で

コピー元がなぜこれを含んでいたかは謎ですが、一先ず動いたので…
灯台下暗し。もう少し手前から調べてみようと思いました。

また時間できたら纏めたいと思います。
時間できたら…(やるのか…?)

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

rails db:migrateをやり直す

はじめに

テーブル設計ミスでcreate_table :hoge2_tablesにキーを書き忘れ、idというカラムができてしまいました
これでは困ってしまうのでテーブルを作り直そうと思います

現在のテーブルはこんな

+-----------+--------+------+-----+---------+----------------+
| Field     | Type   | Null | Key | Default | Extra          |
+-----------+--------+------+-----+---------+----------------+
| id        | bigint | NO   | PRI | NULL    | auto_increment | <-これを消したい
| user_id   | int    | YES  |     | NULL    |                | <-ここにキーを移したい
| user_name | text   | YES  |     | NULL    |                |
+-----------+--------+------+-----+---------+----------------+

migrationファイルを書き換えてrails db:migrate:resetを行う

>rails db:migrate:reset
Mysql2::Error: Access to system schema 'mysql' is rejected.: DROP DATABASE IF EXISTS `mysql`
Couldn't drop database 'mysql'
rails aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: Access to system schema 'mysql' is rejected.: DROP DATABASE IF EXISTS `mysql`
bin/rails:4:in `require'
bin/rails:4:in `<main>'

Caused by:
Mysql2::Error: Access to system schema 'mysql' is rejected.
bin/rails:4:in `require'
bin/rails:4:in `<main>'
Tasks: TOP => db:drop:_unsafe
(See full trace by running task with --trace)

リジェクトされてテーブルの更新ができねえ...
いろいろ調べてこちらの記事にたどり着いた

バージョンを指定してやり直す

まずはmigrateのステータスを確認する

rails db:migrate:status
database: mysql

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20200218022902  Create huga tables
   up     20200218074718  Create huga2 tables
   up     20200219072619  ********** NO FILE **********
   up     20200220020515  Create hoge tables
   up     20200220095931  ********** NO FILE **********
   up     20200225014707  Create hoge2 tables

一覧からやり直したいテーブルのmigration IDを控えて次のコマンドを実行

rails db:migrate:redo VERSION=20200225014707
== 20200225014707 CreateHoge2Tables: reverting ==============================
-- drop_table(:hoge2_tables, {:primary_key=>:user_id})
   -> 0.0397s
== 20200225014707 CreateHoge2Tables: reverted (0.0514s) =====================

== 20200225014707 CreateHoge2Tables: migrating ==============================
-- create_table(:hoge2_tables, {:primary_key=>:user_id})
   -> 0.0275s
== 20200225014707 CreateHoge2Tables: migrated (0.0281s) =====================

いけたっぽいのでテーブルを確認する

mysql> desc hoge2_tables;
+-----------+--------+------+-----+---------+----------------+
| Field     | Type   | Null | Key | Default | Extra          |
+-----------+--------+------+-----+---------+----------------+
| user_id   | bigint | NO   | PRI | NULL    | auto_increment |
| user_name | text   | YES  |     | NULL    |                |
+-----------+--------+------+-----+---------+----------------+

idカラムが消えてuser_idが主キーになってますね、めでたしめでたし

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

Rails5でINSERT IGNOREする

色々あるみたいです。

インサートしたidは不要!パターン

インサート後、id(オートインクリメントされるプライマリーキー)は不要の場合。

1. create! rescue

対象が数件のときには簡単便利。

param = {
  id: 6,                # プライマリーキー
  name: 'ポール・ポグバ',
  position: 'MF'
}
Pogba.create!(param) rescue ActiveRecord::RecordNotUnique

idやユニークインデックスをparamに指定しておけば、重複時に発生する例外をrescueですっ飛ばせる。
参考:https://stackoverflow.com/questions/31396310/insert-ignore-into-table-statement-in-rails-active-records

2.gemactiverecord-importを利用

対象が大量にある場合に便利。

ksk.push(Honda.new(id: 10, name: '本田圭佑', position: 'いい意味でプレッシャー'))
ksk.push(Honda.new(id: 33, name: '本田圭佑', position: 'ケイスケ・ホンダ'))
...

Honda.import ksk on_duplicate_key_ignore: true

ちなみにPostgreSQLだけにはなるが、インサートしたあとにidが取得できるらしい。
参考:https://github.com/zdennis/activerecord-import#duplicate-key-ignore

インサートしたidが欲しい!パターン

1. find_or_initialize_by

param = {id: 45, name: 'マリオ・バロテッリ', position: 'FW'}
milano = Intel.find_or_initialize_by(param)

milano.update(param) if milano.new_record?

update(param)で「INSERT INTO〜」が実行されるのはキモチワルイ。
参考:https://qiita.com/taimuzu/items/0a21738d018f475d63ae

find_or_create_byもあるけどブロック内でわざわざINSERTする値を作り直さなきゃいけないので面倒。(なんか他にいい方法あるのかな)
参考:https://qiita.com/yusabana/items/1b566f61ca556a482f52

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

C#でMySQLからSELECTした結果を取り出したい

C#は素人なのと、あとコードは再現なので不適切なコードになっている可能性が高いです。
おそらくもっといい解法があるはずですが、調べた限りではよくわかりませんでした。

課題

テーブルAからSELECTする、テーブルBからSELECTする、その後ふたつのデータを色々やって最後にテーブルCにインサートする、みたいなことがやりたかったわけですよ。
コード側で色々と処理を行う必要があるため、一度コード側にデータを引き取るのが前提です。

問題

ベストプラクティスがわからない。
適切なサンプルコードが見付からない。

信頼できるドキュメントはMicrosoft公式くらいしかないわけですが、そこに載ってるサンプルコードはどうにも役に立ちません。

https://docs.microsoft.com/ja-jp/azure/mysql/connect-csharp
https://docs.microsoft.com/ja-jp/dotnet/api/overview/azure/mysql?view=azure-dotnet

公式サンプルの例
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
    conn.Open();

    // SQL発行
    MySqlCommand selectCommand = new MySqlCommand("SELECT * FROM MyTable", conn);
    MySqlDataReader results = selectCommand.ExecuteReader();

    // 行ごとにループ
    while(results.Read())
    {
        Console.WriteLine("Column 0: {0} Column 1: {1}", results[0], results[1]);
    }
}

ループ中でログ出力する例しか載ってない。
そんな役に立たないコードじゃなくてもっとこう、取得結果をreturnして他所で使い回す方法はないんですかね。

しかもresults[0]とか、今どき列番号で取ってくるなんて有り得ないじゃろ。
列名で取ってきてくれよ。

やってみた

// メイン
public static void Main{
    MySqlConnection mySqlConnection = new MySqlConnection("ConnectionString");
    mySqlConnection.Open();

    var tableA = getTable("tableA");
    var tableB = getTable("tableB");
}

// 取得
public async Task<DataReader> getTable(string tableName){
    using (var command = mySqlConnection.CreateCommand()) {
        command.CommandText = $"SELECT * FROM {tableName}";
        using (var reader = command.ExecuteReaderAsync()) {
            return reader;
        }
    }
}

できた。

はい、これThere is already an open DataReader associated with this Connection which must be closed firstとか言われて死にます。
どうもDataReaderは同時にひとつしか開けないらしい。
二つ目のSQLを投げる前に、一つ目のSQLはCloseしなくてはなりません。
Closeすると当然データが取れなくなるので、Closeする前に値を取り出しておかねばならないということです。

少し変更した

DataReaderから値を取得して、Dictionaryに突っ込んで返すようにしました。

static MySqlConnection mySqlConnection;

public static void Main{
    mySqlConnection = new MySqlConnection("ConnectionString");
    mySqlConnection.Open();

    var tableA = getTable("tableA");
    var tableB = getTable("tableB");
}

// 取得
public Dictionary<string, Dictionary<string, string>> getTable(string tableName){
    using (var command = mySqlConnection.CreateCommand()) {
        command.CommandText = $"SELECT * FROM {tableName}";
        using (var reader = command.ExecuteReader()) {
            if (reader.HasRows) {
                while (reader.Read()) {
                    JObject line = new JObject();
                    for (int i = 0; i < reader.FieldCount; i++) {
                        line.Add(reader.GetName(i), reader.GetString(i));
                    }
                    ret.Add(reader.GetString("id"), line);
                }
            }
        }
    }
    return ret;
}

これでできた!
と思いきや、何故かこれでもalready an open DataReaderが発生することがあります。
なんで?returnする前にusingの外に出てるやろ?
原因がさっぱりわからなかったので諦めました。

しかも、どうやらその行の列を全て取得するみたいなメソッドがどうも存在しないっぽい。
いちいちFieldCountで列数を調べて全列をループで回して取り出しています。
SQL発行する度に毎回行×列のループを回さねばならないとか、本当かこれ???

そもそも最初からfetchAllがあれば解決なのですよ。
どうしてこの程度のメソッドが用意されてないのですかね?

とりあえず完成

mySqlConnectionを毎回開けばええんや。

public static void Main{
    var tableA = getTable("tableA");
    var tableB = getTable("tableB");
}

// 取得
public Dictionary<string, Dictionary<string, string>> getTable(string tableName){
    using (MySqlConnection mySqlConnection = new MySqlConnection("ConnectionString")) {
        mySqlConnection.Open();
        using (var command = mySqlConnection.CreateCommand()) {
            command.CommandText = $"SELECT * FROM {tableName}";
            using (var reader = command.ExecuteReader()) {
                if (reader.HasRows) {
                    while (reader.Read()) {
                        var line = new Dictionary<string, string>();
                        for (int i = 0; i < reader.FieldCount; i++) {
                            line.Add(reader.GetName(i), reader.GetString(i));
                        }
                        ret.Add(reader.GetString("id"), line);
                    }
                }
            }
        }
    }
    return ret;
}

いや……一応これで動いたんだけどさあ、どう考えてもおかしいだろ、これ。
SQLを発行するたび毎回MySqlConnectionをnewしてるってことは、PHPで言うと毎回new PDOってしてるってことですよね。
そんな書き方ありえなーい。
そもそも高々SQL発行するだけなのに何重に括らせる気なんだよ。

なんかもっとこう、ちゃんとした正しい書き方ってのがあるよね?
あると思うのですよ。
間違いなく存在するでしょう。

実はありまぁす

DataTableってのがあった。

public static void Main{
    var tableA = getTable("tableA");
    var tableB = getTable("tableB");
}

public DataTable getTable(string tableName) {
    DataTable tbl = new DataTable();
    using(MySqlConnection mySqlConnection = new MySqlConnection(builder.ConnectionString)) {
        mySqlConnection.Open();
        using(var command = mySqlConnection.CreateCommand()) {
            command.CommandText = $"SELECT * FROM {tableName}";
            using(var reader = command.ExecuteReader()) {
                tbl.Load(reader);
            }
        }
    }
    return tbl;
}

これでDataTableに全データが入ってくるようで、MySqlConnectionを閉じた後でも問題なく参照することができました。

どう考えても真っ先にこれを紹介すべきなのに、普通に調べたら全然出てこないとかいう。
ここまで調べるのにめっちゃ時間かかった。

なおMySqlConnectionを閉じないとエラーになるのは相変わらずです。
どうやったら使い回せるんですかね?
それ以前に、そもそも本当にコレが最終解なんですかね?

その他

C#では特に目立つ気がするのですが、Stackoverflowを機械訳したようなゴミURLばっかり引っかかってくるのはどうにかならないんですかね?

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む