PDO与SQLLite错误（代码：HY000 errorInfo中：HY000，5，数据库被锁定）
问题描述：
我使用此代码时，你得到错误Code: HY000 errorInfo: HY000, 5, database is locked：PDO与SQLLite错误（代码：HY000 errorInfo中：HY000，5，数据库被锁定）
function saveCity($dbname, $city) {
$SQLLogger = '';
$city_id = '';
try {
// connect to SQLite database
$dbh = new PDO("sqlite:".$dbname);
// put city in the database
$sql = "INSERT INTO Orte (ORT) VALUES (:city);";
$sth = $dbh->prepare($sql);
$sth->bindValue(":city", $city, PDO::PARAM_STR);
if(!$sth->execute()){
// error handling
echo "saveCity";
echo "Could not execute statement.\n";
echo "errorCode: " . $sth->errorCode() . "\n";
echo "errorInfo: " . implode(", ", $sth->errorInfo()) . "\n";
} else {
// log SQL statements
$sql = str_replace(":city", $city, $sql);
$SQLLogger .= $sql;
}
// get id for new created city
$sql = "SELECT _id FROM Orte WHERE ORT='$city';";
$sth = $dbh->prepare($sql);
$sth->execute();
$res = $sth->fetch();
$city_id = $res['_id'];
// close the database connection
$dbh = null;
echo "closed";
} catch(PDOException $e) {
echo $e->getMessage();
}
// write SQL statements into log file
if(!empty($SQLLogger)) {
$logfile = "logs/SQLLogger.txt";
$new_line = chr(10) . chr(13); //ASCI-character for \r\n
file_put_contents($logfile, $new_line . $SQLLogger, FILE_APPEND);
}
return $city_id;
}
function saveZIP($dbname, $zip, $city_id) {
$SQLLogger = '';
$zip_id = '';
try {
// connect to SQLite database
$dbh = new PDO("sqlite:".$dbname);
// save zip code in database
$sql = "INSERT INTO Plzn (Postleitzahl, Ort) VALUES (:zip, :city_id);";
$sth = $dbh->prepare($sql);
$sth->bindValue(":zip", $zip, PDO::PARAM_STR);
$sth->bindValue(":city_id", $city_id, PDO::PARAM_INT);
if(!$sth->execute()){
// error handling
echo "saveZIP";
echo "Could not execute statement.\n";
echo "errorCode: " . $sth->errorCode() . "\n";
echo "errorInfo: " . implode(", ", $sth->errorInfo()) . "\n";
} else {
// log SQL statements
$sql = str_replace(":zip", $zip, $sql);
$sql = str_replace(":city_id", $city_id, $sql);
$SQLLogger .= $sql;
}
// get id for new created zip code
$sql = "SELECT _id FROM Plzn WHERE Postleitzahl='$zip';";
$sth = $dbh->prepare($sql);
$sth->execute();
$res = $sth->fetch();
$zip_id = $res['_id'];
// close the database connection
$dbh = null;
} catch(PDOException $e) {
echo $e->getMessage();
}
// write SQL statements into log file
if(!empty($SQLLogger)) {
$logfile = "logs/SQLLogger.txt";
$new_line = chr(10) . chr(13); //ASCI-character for \r\n
file_put_contents($logfile, $new_line . $SQLLogger, FILE_APPEND);
}
return $zip_id;
}
function saveZIPCity($dbname, $zip, $city) {
$SQLLogger = '';
$zip_id = '';
try {
// connect to SQLite database
$dbh = new PDO("sqlite:".$dbname);
// check if zip code is already in database
$zip = $_SESSION['Plzn']['Postleitzahl'];
$sql = "SELECT * FROM Plzn WHERE Postleitzahl='$zip';";
$sth = $dbh->prepare($sql);
$sth->execute();
$result = $sth->fetch();
if (!empty($result)){
// zip code is already there so lets take it
$zip_id = $result['_id'];
// zip code isn't in the database, perhaps the city name is?
} else {
// check if city is already in database
$city = $_SESSION['Orte']['ORT'];
$sql = "SELECT _id FROM Orte WHERE ORT='$city';";
$sth = $dbh->prepare($sql);
$sth->execute();
$result = $sth->fetch();
if (!empty($result)){
// city is in database, lets take it
$city_id = $result['_id'];
$zip_id = saveZIP($dbname, $zip, $city_id);
echo "city exists. save new zip";
// city is not in database
} else {
$city_id = saveCity($dbname, $city);
$zip_id = saveZIP($dbname, $zip, $city_id);
}
}
// close the database connection
$dbh = null;
} catch(PDOException $e) {
echo $e->getMessage();
}
// write SQL statements into log file
if(!empty($SQLLogger)) {
$logfile = "logs/SQLLogger.txt";
$new_line = chr(10) . chr(13); //ASCI-character for \r\n
file_put_contents($logfile, $new_line . $SQLLogger, FILE_APPEND);
}
return $zip_id;
}
在这段代码中我使用的是独立的功能，似乎有一个并发问题。如果我把所有功能都放在这里：
function saveZIPCity($dbname, $zip, $city) {
$SQLLogger = '';
$zip_id = '';
try {
// connect to SQLite database
$dbh = new PDO("sqlite:".$dbname);
// check if zip code is already in database
$zip = $_SESSION['Plzn']['Postleitzahl'];
$sql = "SELECT * FROM Plzn WHERE Postleitzahl='$zip';";
$sth = $dbh->prepare($sql);
$sth->execute();
$result = $sth->fetch();
if (!empty($result)){
// zip code is already there so lets take it
$zip_id = $result['_id'];
// zip code isn't in the database, perhaps the city name is?
} else {
// check if city is already in database
$city = $_SESSION['Orte']['ORT'];
$sql = "SELECT _id FROM Orte WHERE ORT='$city';";
$sth = $dbh->prepare($sql);
$sth->execute();
$result = $sth->fetch();
if (!empty($result)){
// city is in database, lets take it
$city_id = $result['_id'];
// save zip code in database
$sql = "INSERT INTO Plzn (Postleitzahl, Ort) VALUES (:zip, :city_id);";
$sth = $dbh->prepare($sql);
$sth->bindValue(":zip", $zip, PDO::PARAM_STR);
$sth->bindValue(":city_id", $city_id, PDO::PARAM_INT);
if(!$sth->execute()){
// error handling
echo "Could not execute statement.\n";
echo "errorCode: " . $sth->errorCode() . "\n";
echo "errorInfo: " . implode(", ", $sth->errorInfo()) . "\n";
} else {
// log SQL statements
$sql = str_replace(":zip", $zip, $sql);
$sql = str_replace(":city_id", $city_id, $sql);
$SQLLogger .= $sql;
}
// get id for new created zip code
$sql = "SELECT _id FROM Plzn WHERE Postleitzahl='$zip';";
$sth = $dbh->prepare($sql);
$sth->execute();
$res = $sth->fetch();
$zip_id = $res['_id'];
// city is not in database
} else {
// put city in the database
$sql = "INSERT INTO Orte (ORT) VALUES (:city);";
$sth = $dbh->prepare($sql);
$sth->bindValue(":city", $city, PDO::PARAM_STR);
if(!$sth->execute()){
// error handling
echo "Could not execute statement.\n";
echo "errorCode: " . $sth->errorCode() . "\n";
echo "errorInfo: " . implode(", ", $sth->errorInfo()) . "\n";
} else {
// log SQL statements
$sql = str_replace(":city", $city, $sql);
$SQLLogger .= $sql;
}
// get id for new created city
$sql = "SELECT _id FROM Orte WHERE ORT='$city';";
$sth = $dbh->prepare($sql);
$sth->execute();
$res = $sth->fetch();
$city_id = $res['_id'];
// save zip code in database
$sql = "INSERT INTO Plzn (Postleitzahl, Ort) VALUES (:zip, :city_id);";
$sth = $dbh->prepare($sql);
$sth->bindValue(":zip", $zip, PDO::PARAM_STR);
$sth->bindValue(":city_id", $city_id, PDO::PARAM_INT);
if(!$sth->execute()){
// error handling
echo "Could not execute statement.\n";
echo "errorCode: " . $sth->errorCode() . "\n";
echo "errorInfo: " . implode(", ", $sth->errorInfo()) . "\n";
} else {
// log SQL statements
$sql = str_replace(":zip", $zip, $sql);
$sql = str_replace(":city_id", $city_id, $sql);
$SQLLogger .= $sql;
}
// get id for new created zip code
$sql = "SELECT _id FROM Plzn WHERE Postleitzahl='$zip';";
$sth = $dbh->prepare($sql);
$sth->execute();
$res = $sth->fetch();
$zip_id = $res['_id'];
}
}
// close the database connection
$dbh = null;
} catch(PDOException $e) {
echo $e->getMessage();
}
// write SQL statements into log file
if(!empty($SQLLogger)) {
$logfile = "logs/SQLLogger.txt";
$new_line = chr(10) . chr(13); //ASCI-character for \r\n
file_put_contents($logfile, $new_line . $SQLLogger, FILE_APPEND);
}
return $zip_id;
}
它可以正常工作。哪里有问题？
Solution： 使用一个类进行查询并避免同时打开数据库连接。
答
我相信，因为这条线
$city_id = saveCity($dbname, $city);
$zip_id = saveZIP($dbname, $zip, $city_id);
在每个函数调用的。你初始化一个新的数据库连接。在这种情况下，您使用的sqlite只有在您只调用一次的情况下才有效。因为它需要一个写锁（这就是为什么你有这个错误 - 你称它为两次）
但不应该是一个接一个处理的调用吗？我也在这行''zip_id = saveZIP（$ dbname，$ zip，$ city_id）; '。我读到，也许pdo驱动程序不支持多个准备好的语句（在'saveZIP'，'INSERT'和'SELECT'语句的情况下）。 – testing 2012-01-31 11:11:20
不，我不这么认为。对于快速测试，您可以在每个功能的末尾（返回语句之前）放置'$ dbh = null'来关闭“PDO”连接，以查看它是否适用于您。 – Rezigned 2012-01-31 16:42:32
我认为你是对的。否则就不会有问题。与此同时，我切换到[查询类]（http://*.com/q/8094623/426227），我不再有这个问题了。感谢您的帮助。我赞成你。 – testing 2012-01-31 17:26:39