我认为这可能是一个死锁问题,但我只是在读取表数据。或者一些我看不见的非常简单的东西。
我的$msqli
对象适用于数据库访问
但是我对这个错误有点困惑。。我被告知clients
表未被LOCK TABLES
锁定
欢迎提出任何建议。。谢谢这是我的PHP代码
$mysqli = $GLOBALS['mysqli'];
$mysqli->query("LOCK TABLES clients READ, invoices READ, estimates READ");
$mysqli->query("SET @inv='Invoice'");
$mysqli->query("SET @est='Estimate'");
$sql = " (SELECT @inv, name AS client, invoices.id, invoices.ref AS ref, invoices.addTs AS ts 'n"
. "FROM `clients` , `invoices` 'n"
. "WHERE invoices.user_id = " . $_SESSION['user_id'] . " 'n"
. "AND clients.id = invoices.client_id)'n"
. "UNION ALL'n"
. "(SELECT @est, name as client, estimates.id, estimates.ref AS ref, estimates.addTs AS ts 'n"
. "FROM `clients` ,`estimates` 'n"
. "WHERE estimates.user_id = " . $_SESSION['user_id'] . " 'n"
. "AND clients.id = estimates.client_id) 'n"
. "ORDER BY ts DESC LIMIT 5";
if ($result = $mysqli->query($sql) or die(mysqli_error($mysqli))) {
//Do stuff here
}
当我在PhpMyAdmin中运行这个$sql
查询时,它是成功的。。。谢谢你的帮助。。
*******************/em>已解决(?)em>
已修复(或似乎已修复):
报价来源http://bugs.mysql.com/bug.php?id=6588
"不能在一个查询中多次使用锁定的表,请使用别名。"
因此查询更新为
$mysqli->query("LOCK TABLES clients_t READ,clients READ ,
invoices READ, estimates READ");
$mysqli->query("SET @inv='Invoice'");
$mysqli->query("SET @est='Estimate'");
$sql = " " .
"(SELECT @inv, name AS client, invoices.id,
invoices.ref AS ref, invoices.addTs AS ts "
. "FROM `clients` AS clients_t , `invoices`'n"
. "WHERE invoices.user_id = " . $_SESSION['user_id'] . " 'n"
. "AND clients_t.id = invoices.client_id)'n"
. "UNION ALL 'n"
. "(SELECT @est , name AS client, estimates.id,
estimates.ref AS ref, estimates.addTs AS ts'n"
. "FROM `clients` ,`estimates` 'n"
. "WHERE estimates.user_id = " . $_SESSION['user_id'] . " 'n"
. "AND clients.id = estimates.client_id)'n"
. "ORDER BY ts DESC LIMIT 5";
我还需要锁定别名表clients_t
。不管怎样,我希望这对未来的用户有所帮助。
有点挑剔,但明智的做法是对$_SESSION['user_id']输入执行addslashes或mysql_real_sescape_string之类的操作,以避免注入问题。