我将相同的2个查询应用于3个表,因此只需循环并替换表名。第一个INSERT查询始终在测试服务器和生产环境中运行。第二个DELETE查询可在两个不同的测试服务器上运行,但在生产上失败并抛出mysql错误1046未选择数据库。
受影响的代码提取:
$queries = array(
"INSERT INTO knd_bkg.@table@
SELECT DISTINCT SQL_NO_CACHE
B.ASIN, Author, Title, EditorialReview, DetailPageURL, current_price, salesrank, number_reviews, erotica, G.BrowseNodeId, price_salesrank_checked_on
FROM bkd_books.club_books B
USE INDEX (free_quality)
RIGHT JOIN bkd_books.book_genre G on G.ASIN=B.ASIN
WHERE EXISTS
(
SELECT 1 FROM knd_bkg.genres K WHERE K.browsenode=G.BrowseNodeId
)
AND @priceWhere@
AND price_salesrank_checked_on > '@newerThan@'
AND B.number_reviews >= 4
AND B.rating >= 4.0
AND B.language = 'english'
AND B.hide_knd=0
AND B.public_domain=0;",
"DELETE T FROM knd_bkg.@table@ T
INNER JOIN
(
SELECT N.* FROM knd_bkg.@table@ N
INNER JOIN
(
SELECT DISTINCT ASIN, BrowseNodeId FROM knd_bkg.@table@
INNER JOIN knd_bkg.genres ON knd_bkg.genres.browsenode=knd_bkg.@table@.BrowseNodeId
WHERE isFiction=1
) F
ON F.ASIN=N.ASIN
INNER JOIN knd_bkg.genres
ON knd_bkg.genres.browsenode=N.BrowseNodeId
WHERE knd_bkg.genres.isNonFiction=1
) D
USING (ASIN, BrowseNodeId)
WHERE D.ASIN=T.ASIN AND D.BrowseNodeId=T.BrowseNodeId"
);
$tables = array(
"free_books" => array('priceWhere' => "B.current_price = 0", 'timePeriod' => $freeTimePeriod),
"99_books" => array('priceWhere' => "B.current_price > 0 AND B.current_price < 100", 'timePeriod' => $paidTimePeriod),
"399_books" => array('priceWhere' => "B.current_price >= 100 AND B.current_price < 400", 'timePeriod' => $paidTimePeriod),
);
connectSlaveDB();
foreach ($tables as $table => $data) {
$newerThan = date("Y-m-d H:i:s", strtotime("-" . $data['timePeriod'] . " hours"));
foreach ($queries as $query) {
$query = str_replace('@table@', $table, $query);
$query = str_replace('@newerThan@', $newerThan, $query);
$query = str_replace('@priceWhere@', $data['priceWhere'], $query);
Logger::_write(LOG_VERBOSE, "API - api/bkg/rebuild query: $query");
$result = mysql_query($query);
if (!$result) {
Logger::_write(LOG_ERR, "API - api/bkg/rebuild FAILED: " . mysql_error());
$success = false;
} else {
Logger::_write(LOG_VERBOSE, "API - api/bkg/rebuild SUCCESS");
}
}
}
和connectSlaveDB()
function connectSlaveDB()
{
global $dbSlave, $dbUser, $dbPass, $dbName;
$connSlave = mysql_connect($dbSlave, $dbUser, $dbPass, TRUE);
if (!$connSlave) {
Logger::_write(LOG_CRIT, "Unable to connect to slave DB");
include 'screens/error.inc.php';
exit();
}
mysql_set_charset('utf8', $connSlave);
mysql_select_db($dbName, $connSlave);
return TRUE;
}
这些表是MEMORY表,但是我在MYISAM上遇到相同的错误,并且仅在生产时出现。该查询在复制从属服务器上运行,并且不涉及主服务器,但是以前的代码会截断表,因此我认为它不属于权限。当查询从日志文件中删除时,它在phpMyAdmin中可以正常工作。而且在测试服务器上也可以。
我没有1046 No database selected error的原因。 3小时后,我可能会丢失一些明显的东西。尤其是对于一个数据库连接而言,故障模式是这样的:
INSERT on free_books - success
DELETE on free_books - fail
INSERT on 99_books - success
DELETE on 99_books - fail
INSERT on 399_books - success
DELETE on 399_books - fail
请原谅丑陋的全局变量和不推荐使用的mysql *函数。这是一个过时的应用程序。我知道...
最佳答案
您的DELETE查询的语法可能会导致问题
DELETE T FROM
我知道这有时由于mysql错误而导致UPDATE查询问题。
重新编写查询以避免
T
引用应解决此问题。关于php - 在一个循环中,某些查询成功,其他查询失败“未选择数据库”,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/17718864/