ある日、RSpecのテストの実行中に、Ctrl+Cでキャンセルし、再びテストを走らせた。すると、いつまでたっても処理が止まったままだったのでそのまま放置していたら以下のようなエラーが出ました。

Lock wait timeout exceeded; try restarting transaction mysql

なぜこうなったのか

おそらく、MySQLのトランザクション（システムスペックは自動でテストデータのトランザクション処理をしてくれる）がコミットされる前にCtrl+Cでキャンセルしてしまったからだと思いますが、はっきりした原因は不明です（超ピンポイントでCtrl+Cを押してしまったため発生した？）

解決策

①mysqlにmysql -u root -p でログインする。
②SHOW ENGINE INNODB STATUSを実行し、TRANSACTIONSの部分を確認する。

TRANSACTIONS
------------
Trx id counter 60949
Purge done for trx's n:o < 60941 undo n:o < 0 state: running but idle
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421573119466232, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 60934, ACTIVE 151 sec
7 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 6
MySQL thread id 3, OS thread handle 140097739974400, query id 78 172.20.0.3 kiyo
Trx read view will not see trx with id >= 60934, sees < 60934

③すると、151秒コミットされないままのトランザクションがありました！
thread id 3　とあるのでこのプロセスを kill 3 で削除します。

④再度実行したら、RSpecが無事実行されるようになりました。

おそらくそう起こるエラーではないですが、同じ状況になった方の手助けになれば幸いです！

参考

トランザクションを強制的に終了させる
rakeタスクでDBのレコードが更新出来なかった時の解決法

最後まで読んでいただきありがとうございます！

何かご指摘などございましたら、コメントいただけると嬉しいです！

備忘録

内容は以下の通り

• バージョン確認
• データベース(以下DB)作成 ・削除
• テーブル作成・削除

バージョン確認

サーバサイド言語でMySQLを利用する場合、バージョンを指定することがよくある
そして、どのバージョンをインストールしたかをよく忘れるので、覚えておくと便利なコマンド

ログイン前と後でコマンドが変わる(どちらも結果はほとんど同じ)

ログイン前

$ mysql --version

ログイン後

mysql> select version();

MySQL(他のDBでもそうだが)ではDBを作成し、その中にテーブルを作成して、データを管理する。
アプリケーションは複数のテーブルを用い、データを管理し、その複数のテーブルをまとめて管理する単位をDBと呼ぶ。

DB作成

まずはDBにログインする

$ mysql -u root -p

上記はrootユーザでログインするコマンド
パスワードを求められるので入力(rootユーザはデフォルトで作成されている)

DBを作成する

mysql> CREATE DATABASE DB名 default character set utf8;

日本語のデータを扱う場合は
default character set utf8
を必ず記述する(ここは日本なので思考停止でDB作成時に入力する)
全角と半角はどちらでも構わないが末尾には;を必ず入力する(SQLの終端記号)

DB一覧を表示

mysql> show databases;

複数のアプリを作るとDBも複数作っていくので、このコマンドで一覧を表示して確認する。
また、DBを作成した後に、正しく作成されているかの確認の意味でも一覧表示してみる。(一覧表示なので複数形になっている点に注意)

DBの削除
間違ってDBを作成してしまった場合の対処方法

mysql> DROP DATABASE DB名;

※確認無しに即削除になるので、テーブルを作成したデータベースを削除する際は最新の注意を払うこと。

テーブルの作成

テーブルはDBの中に作成するので、以下の手順で作成する。

1. DBの選択
2. テーブルの作成
3. テーブル一覧の出力(確認のため)
4. テーブルの定義情報を出力

データベースの選択

mysql> USE DB名

このコマンドでDBを選択して、次のコマンドでこのDBの中にテーブルを作成する。

テーブル作成

mysql> CREATE TABLE DB名.テーブル名(
        カラム名1 データ型 制約,
        カラム名2 データ型 制約,
       );

DB名と制約は省略可です。
ファイルにSQLコードを記述して、まとめてSQLを実行する場合、DB名を選択できないので、DB名を省略するとエラーになってしまう。
故に、基本的にDB名は記述する癖をつけておいたほうが間違いがない。

例えば、次のようなテーブルを仮定して実行してみる

テーブル名:user

id	name	age	created_at
整数型	文字型	整数型	ﾀｲﾑｽﾀﾝﾌﾟ
主キー	null禁止	null禁止	null禁止
自動連番	最大100文字

mysql> CREATE TABLE DB名.user(
          id int auto_increment not null primary key,  
          name varchar(100) not null,
          age int not null,
          created_at timestamp not null 
          current_timestamp
       );

主キーとなるidはレコード追加時に自動で採番すると便利なので、オートインクリメントにしておく
タイムスタンプとはレコード追加日時を記録するためのデータ型

テーブル一覧の出力
作成したテーブルを一覧として出力するコマンド

mysql> show tables;

DB一覧の出力と同様に複数形になっている点に注意

テーブルの定義情報を出力
カラム名やデータ型、制約を確認することはよくあるため、覚えておくと便利

mysql> describe テーブル名

ディスクリブが説明という意味なので覚えやすい

テーブルの削除
テーブル定義を間違えた場合、作り直すがその際に実行するコマンド

mysql> DROP DB名.テーブル名

おしまい

開発環境にdockerを導入し、docker-compose upをした後にログインをしようとしたところエラーが発生。データベースに接続できなくなってしまったので、その時の対処法を記録として残します。

エラー内容

 ActiveRecord::StatementInvalid (Mysql2::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='):

latin1という文字コードとutf8という文字コードが混在していることが原因で起きているエラーのようです。

本当に混在しているか確認するため、こちらの記事を参考にDocker上でMySQLにログインしてみました。

確認してみるとlatin1とutf8が混在しているのが分かります。
そしてlatin1という文字コードは日本語を扱うことができないためこれをutf8に変更する必要があります。

解決方法

docker-compose.ymlを以下のように変更しました。（こちらの記事、およびdockerhub内mysqlの 公式リファレンス参照）

docker-compose.yml

version: '3'

services:
  db:
    image: mysql:5.7

    ↓↓↓# 文字コードをutf8に指定 この１行を追加する↓↓↓
    command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci

　　 environment:
      MYSQL_USER: root
      MYSQL_PASSWORD:
      # 仮想コンテナにアクセスするためのポート番号
    ports:
      - "3306:3306"
      # データを永続化する(ホスト側のdb/mysql/volumesをコンテナ側の/var/lib/mysqlに同期する)
    volumes:
      - mysql-data:/var/lib/mysql

dbにcommandを追加することで文字コードの設定を渡せます。

再度dockerを立ち上げたところエラーが起きることなく、無事問題は解決しました！

最後に

dockerを始めたばかりの初心者でわからないところがわからない状態ですが、１歩づつ進んでいきます！！

../

VPSサーバー（CentOS8.2）にMySQL8.0をインストールした。定期的にMySQLデータベースのバックアップを作成するようにしたい。その手順をメモしておく。バックアップは、毎日4:00に実行し、過去14日分を残すようにする。スケジュールはcron.dに登録して行う。

定期バックアップのシェル作成

以下のようにシェルファイルを作成する。例では、/var/share9/mysql-backup/mysql-backup.shとしている。データベースの接続情報は適宜、変更のこと。

$ vi /var/share9/mysql-backup/mysql-backup.sh 
#!/bin/sh

keepday=14      # 何日間をサイクルで回すか。
host=xxx        # ホスト名
dbname=kankeri  # データベース名
user=root       # ユーザー名
pwd=***         # パスワード

dst=/var/share9/mysql-backup/backup-${host}
bak=${dbname}.bak
d1=`date +%Y%m%d`
d2=`date "-d${keepday} days ago" +%Y%m%d`
new=${dbname}-${d1}.tar.gz
old=${dbname}-${d2}.tar.gz

cd ${dst}
/usr/bin/mysqldump -u${user} --password='${pwd}' --opt ${dbname} > ${bak}
tar zcvf ${new} ${bak} > /dev/null 2>&1
if [ $? != 0 -o ! -e ${new} ]; then
echo backup failed ${new}
exit 1
fi
if [ -e ${old} ]; then
rm -f ${old}
fi

echo ----------- ${dst}
ls -lag  ${dst}

cron.dへスケジュール登録

毎日4:00に実行されるようにcron.dに登録する。スケジューラはcrondというサービスであるが、登録方法は、cron.dやcrontabやcron.dailyなどがある。設定方法は同じである。私は、cron.dに登録することにした。/etc/cron.d/mysql-backup を以下のように作成した。毎日4:00に前述のmysql-backup.shを実行させる。

vi /etc/cron.d/mysql-backup
# Example of job definition:
# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed
  0 4 * * * root      /var/share9/mysql-backup/mysql-backup.sh

動作確認

まず、mysql-backup.sh がrootで正常に動作することを確認しておく。mysql-backup.shの所有者はrootで、権限は700であること。パスワード埋め込みのシェルなので、他者が触れないようにしておくこと。

そしてcrondサービスを再起動させておく。1日待てば確認できる。気が短い方は、cron.hourlyに変更して確認してみてもいい。

# rootで動作するかどうかの確認
$ su -
$ ls -lag /var/share9/mysql-backup/mysql-backup.sh
-rwx------ 1 root  680 11月 28 10:32 mysql-backup.sh
$ /var/share9/mysql-backup/mysql-backup.sh

# cron.dに登録されているか確認
$ ls -lag /etc/cron.d/
-rw-r--r--   1 root    52 11月 28 10:12 mysql-backup

# crondを再起動して、追加したcron.dを読み込ませる。
$ systemctl restart crond
$ systemctl status crond

以上

../