質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

phpMyAdmin

phpMyAdminはオープンソースで、PHPで書かれたウェブベースのMySQL管理ツールのことです。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

WordPress

WordPressは、PHPで開発されているオープンソースのブログソフトウェアです。データベース管理システムにはMySQLを用いています。フリーのブログソフトウェアの中では最も人気が高く、PHPとHTMLを使って簡単にテンプレートをカスタマイズすることができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

Q&A

解決済

1回答

5125閲覧

MySQL で ROLLBACK が効かず、一部のテーブルだけが更新されてしまう ( WordPress )

yakan

総合スコア19

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

phpMyAdmin

phpMyAdminはオープンソースで、PHPで書かれたウェブベースのMySQL管理ツールのことです。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

WordPress

WordPressは、PHPで開発されているオープンソースのブログソフトウェアです。データベース管理システムにはMySQLを用いています。フリーのブログソフトウェアの中では最も人気が高く、PHPとHTMLを使って簡単にテンプレートをカスタマイズすることができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

0グッド

2クリップ

投稿2020/05/30 11:29

編集2020/05/31 06:00

###前提・実現したいこと
「親テーブルwp_mains」と「子テーブルwp_sub1s」があり、両方が同時に保存されなければならない仕様です。
そのため片方にエラーが出た際はROLLBACKを効かせたいです。

###発生している問題
両方保存するためにトランザクションを使っています。

ソースコードは以下で、wp_sub1smains_IDは外部キーであり、存在しない999の保存はエラーになります。

そしてエラーになったらROLLBACKさせたい(wp_mainswp_sub1sも保存したくない)のですが、wp_mainsにだけ普通に値が入ってしまいます。

つまりROLLBACKが効かないのです。

###該当のソースコード

php

1// wp_mains と wp_sub1s にレコードを保存する 2$result = test_insert_table(); 3var_dump($result); 4 5function test_insert_table(){ 6 global $wpdb; 7 8 // 戻り値 9 $result = ['insert_id'=>null,'errors'=>[]]; // $result['errors'] にエラーがあれば ROLLBACK する 10 11 // トランザクションとロックするテーブル 12 $wpdb->query("START TRANSACTION"); 13 $wpdb->query("LOCK TABLES wp_mains WRITE, wp_sub1s WRITE"); 14 15 try{ 16 // wp_mains テーブルを更新 17 $update_info = [ 18 'users_ID' => (int)get_current_user_id() 19 ,'content' => '本文' 20 ]; 21 $result_main = test_insert_sql_row( 'wp_mains', $update_info ); 22 // エラー 23 if( !empty($result_main['errors']) ){ 24 $result['errors']['$result_main'] = $result_main; 25 } 26 27 // wp_mains テーブル が無事入ったら 28 else{ 29 $result['insert_id'] = $result_main['insert_id']; 30 31 // wp_subs テーブルを更新 32 $update_info = [ 33 'mains_ID' => 999 // $result_main['insert_id'] を渡せばエラーにならないが、999 は外部キーとして存在しないのでエラーになる 34 ,'title' => 'タイトル' 35 ]; 36 $result_sub = test_insert_sql_row( 'wp_sub1s', $update_info ); 37 // エラー 38 if( !empty($result_sub['errors']) ){ 39 $result['errors']['$result_sub'] = $result_sub; 40 } 41 } 42 43 // エラーなら ROLLBACK する 44 if( !empty($result['errors']) ){ 45 $wpdb->query('ROLLBACK'); // ここで ROLLBACK が効かず、wp_mains にだけ値が入ってしまうのが問題です 46 error_log( 'test_insert_table() - $result = '. json_encode($result, JSON_UNESCAPED_UNICODE) ); 47 }else{ 48 $wpdb->query('COMMIT'); 49 } 50 }catch( Exception $e ){ 51 $wpdb->query('ROLLBACK'); 52 }finally{ 53 $wpdb->query('UNLOCK TABLES'); 54 } 55 56 return $result; 57} 58 59function test_insert_sql_row( $table_name, $update_info ){ 60 global $wpdb; 61 62 // 戻り値 63 $result = ['insert_id'=>null,'errors'=>[]]; 64 65 // 値の型 66 $format_arr = []; 67 foreach ( $update_info as $v ) { 68 $type = gettype( $v ); 69 if ( $type == 'string' ) { 70 $format = '%s'; 71 } elseif ( $type == 'integer' ) { 72 $format = '%d'; 73 } 74 $format_arr[] = $format; 75 } 76 77 // 保存 78 $rows = $wpdb->insert( $table_name, $update_info, $format_arr ); 79 // エラーがあればその内容を入れる 80 if( ! $rows ){ 81 $result['errors'] = [ 82 '$rows' => $rows 83 ,'$wpdb->last_query' => $wpdb->last_query 84 ,'$wpdb->last_error' => $wpdb->last_error 85 ,'$wpdb->last_result' => $wpdb->last_result 86 ,'$wpdb->col_info' => $wpdb->col_info 87 ,'$wpdb->insert_id' => $wpdb->insert_id 88 ]; 89 } 90 // エラーがなければIDを入れる 91 else{ 92 $insert_id = $wpdb->insert_id; 93 $result['insert_id'] = $insert_id; 94 } 95 return $result; 96} 97

テーブルのCREATEは以下です。

PHP

1test_create_table(); 2function test_create_table(){ 3 global $wpdb; 4 require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); // dbDelta() のため必要 5 6 // wp_mains テーブルを作成 7 $sql = "CREATE TABLE wp_mains ( 8 `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT 9 ,`users_ID` BIGINT(20) UNSIGNED NOT NULL 10 ,`content` VARCHAR(1000) 11 ,PRIMARY KEY (`ID`) 12 ,FOREIGN KEY (`users_ID`) REFERENCES wp_users(`ID`) 13 );"; 14 add_option($table_name."_version", '1.0'); 15 dbDelta($sql); 16 17 // wp_sub1s テーブルを作成 18 $sql = "CREATE TABLE wp_sub1s ( 19 `mains_ID` BIGINT(20) UNSIGNED NOT NULL 20 ,`title` VARCHAR(50) NOT NULL 21 ,PRIMARY KEY (`mains_ID`) 22 ,FOREIGN KEY (`mains_ID`) REFERENCES wp_mains(`ID`) 23 );"; 24 add_option($table_name."_version", '1.0'); 25 dbDelta($sql); 26}

###試したこと
まず上記ソースコードにありますが、999でなく$result_main['insert_id']を渡せばエラーにならず、wp_mainswp_sub1sも問題なく入りました。

次にROLLBACKすべき条件分岐に差し掛かっているかどうかを確認するため、error_log( '$result = ' . json_encode($result, JSON_UNESCAPED_UNICODE) );を書いており、これは出力されました。よって// エラーなら ROLLBACK するというのは分岐できているようです。

なのにこの一行前の$wpdb->query('ROLLBACK'); // ここで ROLLBACK が効かず、wp_mains にだけ値が入ってしまうのが問題ですのROLLBACKが効かないということです。

続いてこちらの記事(http://wxy.crap.jp/program/wordpress/transaction/)を見つけ、ストレージエンジンがInnoDBでないとROLLBACKが効かないのだという情報を得まして確認したのですが、すべてのテーブルはInnoDBで間違いございませんでした。

###バージョン情報等
WordPressは5.4です。
MySQLはselect version();10.0.33-MariaDBでした。

上記ソースコードで間違っている点、あやしい点、なおすべき点などお心あたりのある方がいらっしゃいましたらご回答いただけましたら幸いでございます。

初心者のコードにつき冗長であったり見にくかったり等あるかと思いますが、どうぞよろしくお願い致します。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

自己解決

###問題の原因
原因は以下のコード

php

1// トランザクションとロックするテーブル 2$wpdb->query("START TRANSACTION"); 3$wpdb->query("LOCK TABLES wp_mains WRITE, wp_sub1s WRITE");

###解決方法
LOCK TABLESする場合、ドキュメント(https://dev.mysql.com/doc/refman/5.6/ja/lock-tables-and-transactions.html)の最後の「正しい方法」に則って以下のようにしなければならない。

php

1// 「正しい方法」に則って以下のようにしなければならない 2$wpdb->query('SET autocommit=0'); // これで0を指定 3// $wpdb->query('START TRANSACTION'); // これは不要 4$wpdb->query('LOCK TABLES wp_mains WRITE, wp_sub1s WRITE');

なぜならデフォルトではautocommit=1となっており、するとSQLは実行されるたびにコミットされ、ROLLBACKが効かなくなるため。

尚、なぜSTART TRANSACTIONが不要かというと、このページ(https://open-groove.net/mysql/autocommit/)によれば、0にした場合

この場合のトランザクションは、SQL文の実行によって暗黙的に開始する

とのこと。

###補足

ちなみに、LOCK TABLESしない場合ならば、以下のようにSTART TRANSACTIONだけでOK。

php

1// START TRANSACTION だけでOK 2$wpdb->query("START TRANSACTION");

なぜなら、同ページ(https://open-groove.net/mysql/autocommit/)によれば次の通りで、

オートコミットを有効にした場合(デフォルトで有効)
1.START TRANSACTION文を実行した場合
単一のSQL文が実行されただけではコミットせず、COMMIT文を実行した時点で初めてコミットされる。ROLLBACK文を実行すればロールバックする。

つまりautocommit=1であってもSTART TRANSACTIONだけ実行してやればCOMMIT文の実行までコミットされずROLLBACKが効くため。

###残った疑問1
補足にある「>START TRANSACTIONだけ実行してやればCOMMIT文の実行までコミットされず」が正しいならば、問題の原因にあるコードでもコミットされないはずで、問題は発生しないはずで、解決方法のようにautocommit=0を指定する必要はないはず。

それがなぜLOCK TABLESする場合にはautocommit=0を指定しなければならなくなるのかが疑問。

###残った疑問2
さらに解決方法でautocommit=0とした直後に、autocommitを確認するとなぜか1になってるという点も疑問…

php

1// 解決方法 2$wpdb->query('SET autocommit=0'); 3$wpdb->query("LOCK TABLES wp_mains WRITE, wp_sub1s WRITE"); 4 5// autocommit を確認 6$result['check_autocommit'] = $wpdb->query("SELECT @@autocommit"); // なぜか1になってる

投稿2020/05/31 05:54

編集2020/05/31 06:26
yakan

総合スコア19

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問