トランザクション処理が失敗しても通ってしまいます

Question

トランザクション処理が失敗しても通ってしまいます。
1つ目は成功、2つ目は失敗にしていますが、2が失敗しても1はそのまま実行されてしまいます。
参考サイト

環境：MYSQL

2つ目のインサート文は引数があっていません

<?php
// DB情報
$dbh       = null;
$db_admin = "sbiccicms_admin_local_auto";
$db_common = "sbiccicms_common_local_auto";
$db_front = "sbiccicms_front_local_auto";
$user      = 'root';
$pass      = '';

try {
	// DB接続
	$dbh = new PDO('mysql:host=127.0.0.1; dbname='. $db_admin.'; charset=utf8', $user, $pass);

	//例外処理を投げる（スロー）ようにする
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	// トランザクション開始
	$dbh->beginTransaction();

	$table_cp = 'create table test_import like site_configs';
	$table_create = $dbh->query($table_cp);

	$table_cp = 'create table error like site_configs';
	$table_create = $dbh->query($table_cp);

	$insert_site_configs = "INSERT INTO test_import (site_url, site_name, wrt_user_id, created, modified) VALUES ('CCI', 'CCIホーム', 99, cast(now() as datetime),cast(now() as datetime))";
	$insert_users_check = $dbh->query($insert_site_configs);

	$insert_site_configs = "INSERT INTO error (site_url, site_name, wrt_user_id, created, modified) VALUES ('CCIホーム', 99, cast(now() as datetime),cast(now() as datetime))";
	$insert_users_check = $dbh->query($insert_site_configs);

	// コミット
	$dbh->commit();

} catch (PDOException $e) {
	// ロールバック
	$dbh->rollBack();
	echo 'DB接続エラー：' . $e->getMessage();
	die();
} catch (Throwable $e) {
	echo "ロールバック";
	// ロールバック
	$dbh->rollBack();
	echo '失敗：' . $e->getMessage();
	die();
}


// DB接続終了
$dbh = null;

?>

Comments

[javahack]

プログラムで動的にテーブルを作成するのは、ほとんどの場合設計が間違っています。
設計を見直した方が良いです。

[kaito2414]

テーブル作成は、php側でやらないほうがいいということでしょうか

[javahack]

・設計段階でテーブル構成を決定
・運用開始前に設計に従いテーブルを作成
・運用中はレコードの操作のみでテーブル構成は基本的には触らない
非常に雑なフローですが、多くのシステムは上記のような流れで作られていると思います。

[m.ts10806]

回答依頼いただきましたが、既についた回答で進めることは可能かと思います。

Answer 1

create tableのrollbackをしようとしていますか？
もしそうなら無理です

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.3 暗黙的なコミットを発生させるステートメント

Comments

[kaito2414]

今実現したいことは主にインサートが失敗したらロールバックといった感じです。
なるほど、create tableはロールバックできないのですね

[yambejp]

インサートが失敗したらロールバックをしなくても
データは登録されませんけどね

[kaito2414]

インサートが４つあり、そのうちの1つが失敗し、ほかの3つのインサートが成功した場合は、どうなりますか？
１つでも失敗したらほかの完了した3つのインサートをキャンセルしたいのですが

[yambejp]

同じテーブルへのインサートならバルクで処理すればいいのでは？
１データでも失敗すれば全部キャンセルされます

複数テーブルへのインサートだとしてもrollbackすれば
成功したインサートはキャンセルされます

[kaito2414]

複数テーブルのインサートです。

以下のコードで試してみたのですが、成功したインサートはキャンセルされませんでした。

errorのほうは引数の数があってないためエラー

<?php
// DB情報
$dbh = null;
$db_admin = "sbiccicms_admin_local_auto";
$db_common = "sbiccicms_common_local_auto";
$db_front = "sbiccicms_front_local_auto";
$user = 'root';
$pass = '';
$table_name = "categories";

try {
// DB接続
$dbh = new PDO('mysql:host=127.0.0.1; dbname='. $db_admin.'; charset=utf8', $user, $pass);
// トランザクション開始
$dbh->beginTransaction();

// サブテーブル作成
$table_cp = 'create table test_import like site_configs';
$table_create = $dbh->query($table_cp);

$table_cp = 'create table error like site_configs';
$table_create = $dbh->query($table_cp);

$insert_site_configs = "INSERT INTO test_import (site_url, site_name, wrt_user_id, created, modified) VALUES ('CCI', 'CCIホーム', 99, cast(now() as datetime),cast(now() as datetime))";
$insert_users_check = $dbh->query($insert_site_configs);

$insert_site_configs = "INSERT INTO error (site_url, site_name, wrt_user_id, created, modified) VALUES ('CCIホーム', 99, cast(now() as datetime),cast(now() as datetime))";
$insert_users_check = $dbh->query($insert_site_configs);

// コミット
$dbh->commit();

} catch (PDOException $e) {
echo 'DB接続エラー：' . $e->getMessage();

} catch (Throwable $e) {
echo "ロールバック";
// ロールバック
$dbh->rollBack();
echo '失敗：' . $e->getMessage();
exit();
}

// DB接続終了
$dbh = null;

?>

[yambejp]

以下テストしてみてください

create table tbl_a(id int primary key);
create table tbl_b(id int primary key);

try{
$pdo = new PDO($dsn, $user,$password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->beginTransaction();
$sql="insert into tbl_a values(1)";
$stmt = $pdo->query($sql);
$sql="insert into tbl_b values(1)";
$stmt = $pdo->query($sql);
$pdo->commit();
}catch(PDOException $e){
$pdo->rollback();
die($e->getMessage());
}

１回目はこれで登録される
次にtbl_aに2、tbl_bに1を投入すると、rollbackされて
tbl_aのinsertもキャンセルされる

[kaito2414]

Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'sbiccicms_admin_local_auto.tbl_a' doesn't exist
このエラーが起こりました

[yambejp]

create tableは予めつくっておいてください。
そもそもphpからcreate tableするような運用方法は
よほどのセキュリティに関する知識がない限りやってはいけませんｎ

[kaito2414]

やはり、php側でのテーブル作成はふさわしくないのですね。

<?php
// DB情報
$dbh = null;
$db_admin = "sbiccicms_admin_local_auto";
$db_common = "sbiccicms_common_local_auto";
$db_front = "sbiccicms_front_local_auto";
$user = 'root';
$pass = '';

try {
// DB接続
$dbh = new PDO('mysql:host=127.0.0.1; dbname='. $db_admin.'; charset=utf8', $user, $pass);

//例外処理を投げる（スロー）ようにする
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// トランザクション開始
$dbh->beginTransaction();

$insert_site_configs = "INSERT INTO test_import (site_url, site_name, wrt_user_id, created, modified) VALUES ('CCI', 'CCIホーム', 99, cast(now() as datetime),cast(now() as datetime))";
$insert_users_check = $dbh->query($insert_site_configs);

$insert_site_configs = "INSERT INTO error (site_url, site_name, wrt_user_id, created, modified) VALUES ('CCIホーム', 99, cast(now() as datetime),cast(now() as datetime))";
$insert_users_check = $dbh->query($insert_site_configs);

// コミット
$dbh->commit();

} catch (PDOException $e) {
// ロールバック
$dbh->rollBack();
echo 'DB接続エラー：' . $e->getMessage();
die();
} catch (Throwable $e) {
echo "ロールバック";
// ロールバック
$dbh->rollBack();
echo '失敗：' . $e->getMessage();
die();
}

// DB接続終了
$dbh = null;

?>
このコードを実行してみたのですが、やはり2つ目が間違っていても、一つ目が実行されてしまいます。
どこがおかしいですか？

[yambejp]

そうなると多分、オートコミットの設定になっているのだと思います
サーバーの設定を変更するか
まずは「SET autocommit=0;」を実行してからトランザクションをしてください。

またテーブルのエンジンはinnoDBで間違いないですよね？

[kaito2414]

分かりました。
間違いないです。

[kaito2414]

何度も申し訳けないのですが、トランザクション処理内にテーブル作成の処理を入れると、トランザクションはうまく働かなくなるということで間違いないですか？
省いたのですが、実は上記のコードにcreate tableが入っています。
先にテーブル作成し、php側でインサートに変更したら、トランザクションは行われました。

[yambejp]

> トランザクション処理内にテーブル作成の処理を入れると、トランザクションはうまく働かなくなるということで間違いないですか？

ちがいます
セッションがオートコミット状態の場合はトランザクションが無効だということです。
気になるならbeginTransactionする前にqueryでcreate tableしてください
またcreate tableは原則transactionの対象外ということです。

[kaito2414]

いま本当に行いたいことはテーブルデータの上書きです。
今までは、サブテーブル作成＞サブにインサート＞サブが成功したなら本来インサートしたいテーブルを削除＞サブを本来インサートしたいテーブル名にリネーム
という風に行っていたのですが、
トランザクション＞すべてのレコード削除＞auto インクリメントを1に設定>サブを作らずインサート
成功なら上書き完成、失敗ならレコード削除は無効化され以前のままのコードのほうがふさわしいでしょうか？

[yambejp]

サブテーブルの有効性が微妙ですが
temporaryテーブルを利用すればいいのでは？

逆にサブテーブルをつくってインサートするときの失敗って
どういう場合を想定していますか？

[kaito2414]

csvファイルのインサートが途中でうまくいかなかったときですかね
あと、レコード削除、auto インクリメントを1に設定を導入したらトランザクションが働かなくなりました

[yambejp]

> auto インクリメントを1に設定を導入したら

オートインクリメントとオートコミットは別物ですが大丈夫ですか？
オートインクリメントでトランザクションが影響することはないと思います

[kaito2414]

＞オートインクリメントとオートコミットは別物ですが大丈夫ですか？
インサートした際に、IDが1からではなく、前回の番号の続きからになるので1に設定しました。

Answer 2

提示されたコードでは PDOException をキャッチしているので、未定義変数の Notice はキャッチしません。

- } catch (PDOException $e) {
+ } catch (Throwable $e) {

Comments

[kaito2414]

途中でエラーがあってすべての処理を無効化するには、キャッチはどのように記載すればよろしいでしょうか

[退会済みユーザー]

Throwable をキャッチします。

[退会済みユーザー]

ただし、MySQLのCreate table はロールバックできません。
ほかに trancate もロールバックできません。

[kaito2414]

なるほど、ありがとうございます