在RStudio中调用tbl()访问MySQL视图时出现预准备语句错误

问题详情 #

连接数据库后，Connections窗口和dbListTables()都能正常获取视图名称，但调用tbl(con, "database_view")时触发如下错误：

Error: nanodbc/nanodbc.cpp:1655: HY000: [MySQL][ODBC 8.0(a) Driver][mysqld-5.5.5-10.3.34- 
MariaDB-log]Prepared statement needs to be re-prepared 
<SQL> 'SELECT *
FROM `database_view` AS `q01`
WHERE (0 = 1)'

已加载的R包：

library(tidyverse)
library(dbplyr)
library(DBI)
library(odbc)

数据库连接代码：

con <- DBI::dbConnect(odbc::odbc(),
                  Driver = "MySQL ODBC 8.0 ANSI Driver",
                  Server = "[Server]",
                  UID = "[UID]",
                  PWD = "[PWD]",
                  Port = 3306,
                  Database = "[Database]")

解决方案 #

1. 调整数据库缓存参数 #

该错误常因视图依赖的表结构变更或缓存不足导致，登录数据库执行以下命令临时调整缓存：

SET GLOBAL table_definition_cache = 1024;

若需永久生效，修改数据库配置文件（Linux为my.cnf，Windows为my.ini），添加或修改：

table_definition_cache = 1024

修改后重启数据库服务。

2. 关闭ODBC语句缓存 #

修改连接代码，添加StmtCache=0参数禁用ODBC的语句缓存：

con <- DBI::dbConnect(odbc::odbc(),
                  Driver = "MySQL ODBC 8.0 ANSI Driver",
                  Server = "[Server]",
                  UID = "[UID]",
                  PWD = "[PWD]",
                  Port = 3306,
                  Database = "[Database]",
                  StmtCache = 0)

3. 直接执行SQL查询替代tbl函数 #

如果上述方法无效，用dbGetQuery直接获取视图数据：

view_data <- DBI::dbGetQuery(con, "SELECT * FROM database_view")

或用dbplyr的sql()函数包装查询：

view_data <- tbl(con, sql("SELECT * FROM database_view"))

4. 刷新视图定义 #

登录数据库执行命令刷新视图缓存，或重新创建视图：

FLUSH TABLES database_view;
-- 若有权限，重新创建视图
CREATE OR REPLACE VIEW database_view AS [原视图的SELECT语句];

内容的提问来源于stack exchange，提问作者Trent