MySql Amazon RDS:来自应用程序的“Innodb 功能已禁用"错误 [英] MySql Amazon RDS: 'Innodb feature disabled' error from application

问题描述

第一个问题的答案是应用程序调用集群的读取器实例.如果我在读取器实例上执行该过程，我可以使用工作台重现该问题.

我有一个带有临时表的存储过程.我正在使用 Amazon AWS RDS (Aurora) MySql.我创建临时表，如:

如果不存在则创建临时表 tmpResources(pkKey varchar(50) NOT NULL, PRIMARY KEY(resource), UNIQUE KEY(resource),...) 引擎=InnoDB 默认字符集=utf8;

当我从 MySql 工作台调用该过程时，它执行得很好.当我从应用程序调用它时，收到以下错误:

<块引用>

'InnoDB' 功能被禁用；你需要用'InnoDB'构建的MySQL让它工作

我有一个 ASP.NET Web 应用程序，使用 Oracle c# 驱动程序 8.0.20 版.AWS RDS 目前是 MySql 版本 5.7.12.

有两个非常令人困惑的问题:1. 为什么从工作台调用它的工作方式不同?错误似乎来自服务器端.2.为什么我会收到这个关于 InnoDB 禁用的错误，而它显然没有被禁用?

感谢您的任何见解...

解决方案

我通过 AWS 技术支持验证了这一点 - 读取器集群上的临时表是 MyISAM.我最初的问题涉及 MyISAM 数据库上的索引，但我会将其作为自己的问题发布.AWS RDS/Aurora 团队的回应如下:

<块引用><块引用>

这是在 Aurora 中的设计，这是因为当使用 InnoDB 在 writer 上创建临时表或系统生成的内部临时表时，它需要转到集群中的 reader 的底层存储，但是当您创建临时表时读取器上的表，它仅用于该特定读取器实例，不会填充到其他节点，因此默认情况下读取器实例将选择 MyISAM 引擎.这种行为归因于变量innodb_read_only"的值对读者设置为ON，对写者设置为OFF，从而限制了在reader实例上创建InnoDB表

初始响应:看来问题出在读者身上.读取器 RDS 实例中存储过程的临时表不是 InnoDB，即使在任何地方都没有记录，并且读取器和写入器的实例类型/价格相同.我不知道它们到底是什么，我对 AWS 有一个问题.如果他们回复，我会在这里发布.

Edit: the answer to the first question is that the application calls the reader instance of the cluster. I can reproduce the problem with workbench if I execute the procedure on the reader instance.

I have a stored procedure with a temporary table. I am using Amazon AWS RDS (Aurora) MySql. I create the temporary table like:

create temporary table if not exists tmpResources(
 pkKey varchar(50) NOT NULL, PRIMARY KEY(resource), UNIQUE KEY(resource), 
 ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I call the procedure from MySql workbench it executes fine. When I call it from my application, I receive the following error:

The 'InnoDB' feature is disabled; you need MySQL built with 'InnoDB' to have it working

I have an asp.net web application, using the Oracle c# drivers version 8.0.20. AWS RDS is currently MySql version 5.7.12.

There are 2 very perplexing questions: 1. Why does it work differently when called from workbench? The error seems to be coming from server side. 2. Why do I get this error about InnoDB disabled, when it is clearly not disabled?

Thanks for any insight...

解决方案

Edit: I verified this with AWS technical support - temp tables on reader clusters are MyISAM. My initial problem involves indices on MyISAM databases, but I will post that as its own question. The response from AWS RDS/Aurora team follows:

this is by design in Aurora, and it is because when a temporary table or a system generated internal temp table is created on writer with InnoDB, it needs to go to underlying storage for readers in the cluster, but when you create temporary table on the reader, it is just for that particular reader instance and does not populate to other nodes, hence by default reader instances will pick up MyISAM engine. This behavior is attributed to the fact that the value of the variable "innodb_read_only" is set as ON for readers and OFF for writers, thus restricting the creation of InnoDB tables on the reader instances

Initial response: It appears the issue is with readers. Temporary tables on stored procedures in the reader RDS instances are not InnoDB, even though this is not documented anywhere and the instance type/price is the same for readers as for the writer. What they are exactly I don't know, I have a question to AWS about it. If they respond I'll post here.

这篇关于MySql Amazon RDS:来自应用程序的“Innodb 功能已禁用"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！