PHP MySQL查询的视图在应该有结果时返回0个结果

I have a site that is running PHP 5.6. The database for the site was recently upgraded from MySQL 5.1 to MariaDB 10.0 (MySQL 5.5 Compatible) and now a query on my site won't work (and it was working without error when connecting to the MySQL 5.1 database.

我有一个运行PHP 5.6的网站。该站点的数据库最近从MySQL 5.1升级到MariaDB 10.0(与MySQL 5.5兼容)，现在我站点上的查询无法工作(连接到MySQL 5.1数据库时，查询运行无误)。

For this demo, I have a database table called "content" with fields "id" (integer), and "page_title" (var_char). In that table are two rows - one with an ID of 1 and page_title of "Test 1", and the second row has ID of 2 and page_title set to "Test 2".

对于这个演示，我有一个名为“content”的数据库表，其中包含字段“id”(整数)和“page_title”(var_char)。在该表中有两行，一行ID为1,page_title为“Test 1”，第二行ID为2,page_title设置为“Test 2”。

I then created a view of this table using the following query inside of phpMyAdmin:

然后，我使用phpMyAdmin内部的以下查询创建了这个表的视图:

select `content`.`id` AS `id`,`content`.`page_title` AS `page_title` from `content`

I can see the view and both rows from the content table in the view in phpMyAdmin.

我可以在phpMyAdmin的视图中看到视图和内容表中的两行。

On my PHP page, I have a object oriented style query. That code is:

在PHP页面上，我有一个面向对象的样式查询。这段代码是:

<?php
try {
require_once 'Connections/dbconn.php';
$sql = "SELECT id, page_title FROM v_content WHERE id = 1 LIMIT 1";
$stmt = $db->stmt_init();
if (!$stmt->prepare($sql)) {
    $error = $stmt->error;
} else {
    $stmt->bind_result($id, $page_title);
    $stmt->execute();
    $stmt->store_result();
    $stmt->fetch();
}
} catch (Exception $e) {
$error = $e->getMessage();
}?>

This query is returning the following: Attempt to read a row while there is no result set associated with the statement

此查询返回以下内容:尝试读取一行，而没有与语句关联的结果集

If I change the table from the VIEW "v_content" to just the table "content", I get a result returned. Additionally, if I use the same query (querying the VIEW v_content), and connect to a MySQL 5.1 database, I get results returned. I know there is nothing wrong with the query itself, as I have copied and pasted the query into phpMyAdmin and have had results returned, and the exact same code was used on a MySQL 5.1 database and that returned results. I have checked the error logs and there are no errors being shown.

如果我将表从“v_content”视图更改为仅表“content”，则会得到一个返回的结果。此外，如果我使用相同的查询(查询VIEW v_content)并连接到MySQL 5.1数据库，则会返回结果。我知道查询本身没有问题，因为我已经将查询复制并粘贴到phpMyAdmin并返回了结果，并且在MySQL 5.1数据库上使用了相同的代码，并返回了结果。我检查了错误日志，没有显示错误。

Anyone have any idea what could be causing me to get 0 results returned from the view?

有人知道是什么原因导致我从视图返回0个结果吗?

2 个解决方案

#1

Looks like after all, there was no issue on my end with the actual coding of the site. The hosting company gave me the following explanation:

看起来毕竟，网站的实际编码并没有问题。托管公司给了我以下的解释:

It appears that the issue with prepared statements working incorrectly with views was related to the table_definition_cache size on the new maria database servers. This is a global setting, and essentially, if the number of tables put into cache exceeds this value, it starts flushing older entries out of the cache. It appears that if a statement is prepared, but the entries leave the table_definition_cache before the prepared statement is executed, it's considered "invalid" and needs to be re-prepared. Since this is a global value, the accessing of others tables can reset its counter. As a result, we increased the value of table_definition_cache to a much higher value on all of the database servers, which has resolved the issue.

在新的maria数据库服务器上，有准备的语句错误地处理视图的问题与table_definition_cache大小有关。这是一个全局设置，本质上，如果放入缓存的表数量超过这个值，它将开始从缓存中刷新旧的条目。如果一个语句准备好了，但是在准备好的语句执行之前，条目离开了table_definition_cache，它被认为是“无效的”，需要重新准备。由于这是一个全局值，所以访问其他表可以重置其计数器。因此，我们在所有数据库服务器上将table_definition_cache的值增加到一个更高的值，从而解决了这个问题。

#2

I think you just need to switch the places of execute and bind.

我认为您只需要切换执行和绑定的位置。

...
} else {
    $stmt->execute();
    $stmt->bind_result($id, $page_title);
    $stmt->store_result();
    $stmt->fetch();
}
...

#1