MySQLのトランザクションで最後のSQLしか実行されない

Question

前提・実現したいこと

MySQLでトランザクション内で複数SQLを実行してコミットすると最後のSQLしか有効になりません。
原因等わかる方いらっしゃいますか？

発生している問題

使用しているMySQLのバージョンは以下のとおりです。

# mysql --version
mysql  Ver 15.1 Distrib 10.4.6-MariaDB, for Linux (x86_64) using readline 5.1

データベースには以下の2テーブルがあります。

test1
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)        | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+

test2
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| test1_id | bigint(20) unsigned | NO   |     | NULL    |                |
| name     | varchar(255)        | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+

このテーブルに以下のSQLを実行しました。
（テーブルには何もデータは入っていません）

begin;
insert into test1 (id, name) values (null, 'test');
insert into test2 (id, test1_id, name) values (null, 1, 'test');
commit;

SQL実行後のテーブルのデータは以下のとおりです。

select * from test1;
Empty set (0.001 sec)

select * from test2;
+----+----------+------+
| id | test1_id | name |
+----+----------+------+
|  1 |        1 | test |
+----+----------+------+
1 row in set (0.001 sec)

2つ目のSQLしか実行されていないようです。

トランザクションをかけずに個別にinsertを実行すると問題なく登録できています。

コミットではなく、ロールバックをするとtest1、test2どちらにもデータが入っていない状態となります。（正しい挙動）

MySQLの設定等原因わかる方いらっしゃいますでしょうか？
よろしくお願いします。

Answer 1

普通の設定ではtest1の投入が失敗したらtest2には処理がながれないと思いますが・・・

create table test1(
  id bigint unsigned not null primary key auto_increment,
  name varchar(255)
  ) ENGINE=InnoDB;
create table test2(
  id bigint unsigned not null primary key auto_increment,
  test1_id bigint unsigned not null default 0,
  name varchar(255)
  ) ENGINE=InnoDB;

• 失敗するSQL

start transaction;
insert into test1 (id, name) values (1, 'test1');
insert into test1 (id, name) values (1, 'test2');
insert into test2 (id, test1_id, name) values (null, 1, 'test3');
commit;

オートコミットで、エラーを無視するような設定にでもなっているのでしょうか？

参考

• 実践ハイパフォーマンスMySQL P23 トランザクション

にあるとおり、トランザクションの原子性から考えて、
「全体を適用するか、またはロールバックするかのどちらかである」と提示されています。
「一部が完了することはあり得ない。全か無かのどちらか一方しかない」
のがトランザクションです。

どうしても直らないならmysql自体の再インストールが必要かもしれません

Comments

[yoyoyo_yoyoyo]

ご回答ありがとうございます。
トランザクションをかけずに個別にSQLを実行するとtest1にもデータは登録されるのでエラーにはなっていないと思います。

[yambejp]

SQLの発行はなにをつかってやっています？
・mysqlクライアントにログインして手動で発行
・phpMyAdminなどなんらかのUI
・なんらかのプログラム

[yambejp]

それと仮に
insert into test1 (id, name) values (1, 'test');
というidを直接していしてもダメですか？

[yoyoyo_yoyoyo]

> SQLの発行はなにをつかってやっています？
SSHでサーバに接続し、mysqlクライアントにログインして手動で発行しています。

idを直接指定してもダメでした。

[yambejp]

私の環境でmysqlクライアントにログインしてやったところ
再現性はないですね

逆にcommitではなくrollbackした時に、test2への投入は
ちゃんとキャンセルされますか？

[yoyoyo_yoyoyo]

rollbackはtest1、test2どちらにもデータは入らず正常に動いています。

[yambejp]

あとtest1とtest2の投入順番を逆にするとどうですか？
今度はtest1だけが投入されます？
またtest1の投入を2度する（test2はしない）場合、投入されるのは
1つだけでしょうか？
begin;
insert into test1 values(1,'test1');
insert into test1 values(2,'test2');
commit;

またバルクインサートはどうでしょうか？
begin;
insert into test1 values(1,'test1'),(２,'test２');
commit;

[yoyoyo_yoyoyo]

> あとtest1とtest2の投入順番を逆にするとどうですか？
test2にはデータが入らずtest1にだけデータが入りました。

> またtest1の投入を2度する（test2はしない）場合、投入されるのは
1つだけでしょうか？
後に実行した1件のみデータが登録されます。（test2の方）

> またバルクインサートはどうでしょうか？
こちらでは2件データが登録されました。

[yambejp]

動作の切り分けはできてきたみたいですがそれにしてもおかしいですね。
（追記した参考のとおり）
tableのエンジンは間違いなくinnoDBですよね？
ダメならあきらめて再インストールするか
トランザクションに依存しない処理で我慢するかでしょう

[yoyoyo_yoyoyo]

テーブルのエンジンはinnoDBです。
トランザクションは必須なので再インストールしようと思います。

[yambejp]

test1とtest2の依存関係がわからないのでなんともいえませんが
test1の投入によりtest2にデータが投入されるなら
普通はtest1のトリガーでやる処理のような気がします。
もちろんtest1のトリガーが発火しなければtest2にデータが登録されない
ので、状況的にかわらないかもしれませんけどね

Answer 2

id は NOT NULL 制約が設定してある(NULL禁止)からだからでは？
auto_increment が設定してあるので、

insert into test1 (id, name) values (null, 'test');
                    ↓
insert into test1 (name) values ('test');

で試しては？

Comments

[yoyoyo_yoyoyo]

ご回答ありがとうございます。
上記のようにidを指定しない方法を試してみましたが結果は変わりませんでした。

[Orlofsky]

https://dev.mysql.com/doc/refman/5.6/ja/constraint-invalid-data.html
>STRICT_TRANS_TABLES は、トランザクションストレージエンジンに対して厳密モードを有効にし、非トランザクションエンジンに対してもある程度有効にします。
を試しては？
MySQLはあえて厳密にチェックしないことでパフォーマンスを稼いでいる部分があります。

[yoyoyo_yoyoyo]

デフォルトでSTRICT_TRANS_TABLESは有効になっていました。
無効にして試してみましたが結果は変わりありませんでした。

[Orlofsky]

あえて地雷原の中を進むのは避けたいですから、
insert into test2 (id, test1_id, name) values (null, 1, 'test');
も
insert into test2 (test1_id, name) values (1, 'test');
に。
動作確認の為に別名のテーブルを作って試しては？