Open wowdyaln opened 6 years ago
先看一下你的 Database engine 是用哪一個好了,MyISAM 還是 InnoDB?
是 InnoDB
已經重複的 username 會被擋下,但新的 username 會成功(最後一行)
Error: Duplicate entry 'Scott and Zelda Fitzgerald' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Scott and Zelda Fitzgerald', 'Black Widow', '$2y$10$ifRiRmkf7byLB8vU..agYu2IEFRo5UNEpVf7qWF9W7d425TSCzBTW') Error: Duplicate entry 'Jean Cocteau' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Jean Cocteau', 'Shadow', '$2y$10$7LqXQ1vQhNR6ZAWTXuP6kOQsaYth8WaRxvwHPmSmFnVsBazlhVNvO') Error: Duplicate entry 'Brick top' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Brick top', 'Robin Hood', '$2y$10$x7qZXt..R/SaLjtYX2l.auRy96ZNFyaoy4GfdZwb48gxMLODrVbRG') Error: Duplicate entry 'Hemingway' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Hemingway', 'C-Brown', '$2y$10$kCv/C4BaP53a0kgqY59KkevqFhPV.Zdo5r7Mbh392NZUGKICq/Mlq') Error: Duplicate entry 'Mark Twain' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Mark Twain', 'Jungle Man', '$2y$10$StkQLNf9LXhwqMCNBiB/p.xm2d7NCxC9wVx4lpaQw/458I0ndLDRK') Error: Duplicate entry 'Sertride Stein' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Sertride Stein', 'Ella', '$2y$10$D15jLbhFY/Vrn1gHAX5ra.EBK52KhjPzbmKEIfJjYE7.iwWnUIn2a') Error: Duplicate entry 'Belmonte' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Belmonte', 'Doctor', '$2y$10$4KxbEQcE4SEBJdD.hprF8uYOr6.wtCaeHrQPGOe8Bfygn0Gpq3zYS') Error: Duplicate entry 'Pablo' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Pablo', 'Thunderbird', '$2y$10$FVuD.EBj351CwT1l/jVYPeyIYWM.6LtkTkrKo4lPWYGqoti3X8ZEu') Error: Duplicate entry 'Adriana' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Adriana', 'Zodiac', '$2y$10$AGLnhua1pQZSdE/hC3pNBedx6XdA7FdnpOV4IYRvCXYcDQ1F8oCL2') Error: Duplicate entry 'Amedeo' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Amedeo', 'Terminator', '$2y$10$sH7cEsiqkR9Anr1E1unCseyyzmpbSvBp1JrP6/AQaNUTFsn5HV0ra') Error: Duplicate entry 'Modigliani' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Modigliani', 'Wasp', '$2y$10$5lbC8EkFtfql7f0lil6NMOY6cTZoDqsUKm5kLV2gSxCth/B3O8Bwy') Error: Duplicate entry 'Cole Porter' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Cole Porter', 'Trinity', '$2y$10$S1LUZsL8KB7M/pIWOJVZJukeMaACC45lWj11rVm2ua3Lg1s2T9HEq') Error: Duplicate entry 'Define' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Define', 'Godzilla', '$2y$10$H5ri7ThU4WBl5xlLfMd3WOCS.MKOej0O4zS1Njwd4et92SO5hni4W') Error: Duplicate entry 'Archibald MacLeish' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Archibald MacLeish', 'Zelda', '$2y$10$xoA4jyPPKzt1zyJBWTlWvuBdTYqZt33Qmo9Q2EY1H3Bd7dXsbRhJK') Error: Duplicate entry 'Dejuan Barnes' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Dejuan Barnes', 'Tin Man', '$2y$10$l3vBsCfydKjytLAmk.hm6efe7bpWu3yl4IKmPeYMHBC0.KbJMuZWi') Error: Duplicate entry 'Dal' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Dal', 'Twister', '$2y$10$KA5J5QD4/vIxzvNllSWd4.AEsHbVlhgBu/mj6kZdCybLj60CvUn3K') Error: Duplicate entry 'Señor Bunuel' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Señor Bunuel', 'Popeye', '$2y$10$x4aiuBshMSjrxSSJY5GByuKGUHkgJGJNL7YALEJJXR2hjLP5KQG5y') Error: Duplicate entry 'Man Ray' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Man Ray', 'Captian RedBeard', '$2y$10$W9XjEFYcoRBju7RqwZDJNuWQCv7VsJkHx.uA3zri8rb6rGVyXppDi') Error: Duplicate entry 'Tom Stearns Eliot' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Tom Stearns Eliot', 'Dahlia', '$2y$10$BP3/Bvw8lT2i33Q6ysKUD.YxfHdKOP0QeO04jxlpMvwZqyMLWGsB2') Error: Duplicate entry 'Matisse' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Matisse', 'Reno', '$2y$10$1r8KQOT8oe.Q4LlmwjRvTefpN2xY4YmGhxCUB1bPOZtVdYTMO1.JS') Error: Duplicate entry 'Paul Gaugin' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Paul Gaugin', 'Aphrodite', '$2y$10$.E1Uyj2ufJyt5lHZOOUBxuYbU8cJ78Dgc.XMxb../auDpsD2jvczS') Error: Duplicate entry 'Degas' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Degas', 'Lady Luck', '$2y$10$kkE0oaZ9g547OWlzR8NAQ.6Va9OD.LHv0WJt0gfTPR6L122itV1wC') Error: Duplicate entry 'Lautrec' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Lautrec', 'V-Mort', '$2y$10$e0FVDWJ0a7JTHxEJsTA/A.OUJtn3pCvFGTsr.grB8SJg0F8bTVAsq') Error: Duplicate entry 'Richard' for key 'username' :
sql: INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, 'Richard', 'Goldfinger', '$2y$10$wMiC.148CPtfAWCFrAdXg..SYX8SM3vSBy/eK.F2EJdhWqmLmDzSu') good! create a user: 'new man'.
話說你 commit 之後,在資料庫裡面一樣找得到那筆成功的資料嗎?
有耶,寫進去了
乍看之下沒看到什麼明顯的問題,我晚點再試試看
thank you ~ 這是全部的 code ,只有多了要隨機寫進去的 data https://github.com/wowdyaln/mentor-program-hw7_3/blob/master/db/0_insert_user.php
@wowdyaln 你這問題問得真好,我查了一下資料,看起來我自己對 transaction 的理解也有錯 可參考這篇:https://www.cnblogs.com/jaejaking/p/5342285.html 看起來失敗的話要自己 rollback 之類的,我晚點回家再測一下 你可以試試看用這種方式看是不是對的:https://stackoverflow.com/questions/2708237/php-mysql-transactions-examples,有錯誤的話就 rollback
看完上面的參考文章
there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.
參考 stackoverflow 的做法,把 for 迴圈擺在 transaction 區域裡面, 有丟出錯誤就 rollback,如下:
try {
$conn->autocommit(FALSE);
$conn->begin_transaction();
for ($i=0; $i < count($users); $i++){
$user = $users[$i];
$nickname = $nicknames[ mt_rand(0, count($nicknames)-1 )];
$password = password_hash('123', PASSWORD_DEFAULT);
$createUser = "INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, '{$user}', '{$nickname}', '{$password}') ";
$conn->query($createUser);
}
$conn->commit();
} catch(PDOException $Exception) {
$conn->rollback();
}
但是問題跟之前一樣,有的會寫入。
在 catch 這邊不知道是不是放錯參數了? 衍生另個問題:php catch 好像有很多不同變數可以放 不像 javascript 很單純的 try {...} catch (e) {... ... } 就好?
你把 PDOException
改成 Exception
試試看
如果還是不行的話應該就是要自己做判斷,要改成:
if (!$conn->query($createUser)) {
throw new Exception($conn->error);
}
然後 js 就只有 catch(e),php 或一些程式語言你可以決定你要抓什麼型態的 Excpetion
問題修復了:
只改 exception 類型不行;改成自行判斷就可以了!
try {
$conn->autocommit(FALSE);
$conn->begin_transaction();
for ($i = 0; $i < count($users); $i++) {
$user = $users[$i];
$nickname = $nicknames[mt_rand(0, count($nicknames) - 1)];
$password = password_hash('123', PASSWORD_DEFAULT);
$createUser = "INSERT INTO `users` (`id`, `username`, `nickname`, `password`) VALUES (NULL, '{$user}', '{$nickname}', '{$password}') ";
if (!$conn->query($createUser)) {
throw new Exception($conn->error);
}
echo "... ... create a user: '$user' <br> ";
}
$conn->commit();
echo "all transaction succeeded.";
} catch (Exception $e) {
$conn->rollback();
var_dump("Error: $e : <br><br>
sql: {$createUser} <br><br>
all transaction failed." );
}
程式執行順序:
使用 catch (Exception $e) { ... }
或是 catch (PDOException $e){ ... }
都是可以的,出來的 error msg 會有些微差別。
看起來原因是 $conn->query
失敗的話並不會 throw exception,只會回傳錯誤而已,所以用 try catch 的話也沒辦法接收到錯誤,要自己拋出去才行
issue 解決:
重點整理:
js 就只有 catch(e),php 或一些程式語言你可以決定你要抓什麼型態的 Exception。
$conn->query 失敗的話並不會 throw exception,只會回傳錯誤而已,所以用 try catch 的話也沒辦法接收到錯誤,要自己拋出去才行
https://stackoverflow.com/questions/2708237/php-mysql-transactions-examples
其實可以開著,以後比較好找XD
slack 帳號
roro
作業名稱
資料庫的 transaction 機制實作
問題描述
寫一個 php 檔來隨機寫入一些 user 資料 users table 的 username 欄位是 unique 不能重複的。 當資料庫發現有重複 username 要寫入的時候會擋下來,並顯示錯誤原因
例如資料庫裡面已經有 A, B, C 三個 username 當 for 迴圈要寫入 A, C 的時候資料庫會擋下來。
預期的行為
for 迴圈寫入 A, B, E 的時候(3筆 query) 因爲 A, B, 是無法寫入的,預期 E 這筆寫入也不會執行 (全部成功或失敗)
但結果 E 卻被寫入了。
?不知道那邊觀念錯了還是程式碼錯了
嘗試過的解決方法
試着加上 try catch,還是會寫入
相關程式碼
( gist 似乎不能 embed 在這邊?) mySQL transaction.php
原本的 code 是 :6 ~ :19 加上 :3 :4 :20 這3行