在AWS上-无法提取ResultSet;SQL [n/a]；嵌套的异常是org.hibernate.exception.SQLGrammarException:无法提取ResultSet AWS [英] on AWS - could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet aws

问题描述

我的数据库访问存在此(常见)问题.但就我而言，我找不到合适的答案...

I have this (common) problem with my DB access. But in my case, I dont find the right answere...

创建了一个非常基本的MySql DB和Spring Boot App访问它.

Created a very basic MySql DB and Spring Boot App accessing it.

在我的本地主机上，它运行顺利.

On my localhost it runs smooth.

然后我转储了本地数据库.将其插入到AWS-MySql数据库中.并将带有Docker的Spring-Boot应用程序部署到我的EC2.一切都很好.

Then I dumped the local DB. Inserted it into the AWS-MySql DB. And deployed the Spring-Boot app with Docker to my EC2. Everything is well.

但是在使用AWS MySQL时，所有REST端点都会返回:状态500无法提取ResultSet；SQL [n/a]；嵌套的异常是org.hibernate.exception.SQLGrammarException:无法提取ResultSet

But on using the AWS MySQL all REST Endpoints return: status 500 could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

对我来说，它看起来就像在本地主机上一样.

For me it looks like all the same as it was on localhost.

我想念什么?

推荐答案

我找到了原因.我知道一个解决方案.但是我不能接受，不知道它是怎么发生的:

I found the cause. I know a solution. BUT I cant accept to not to know how it happend:

我的POJO仅由类名定义(没有Table ="元数据装饰)

My POJOs are defined by Classname only (no Table="" Metadata decortion)

在我的本地计算机上，它运行良好.SQL查询为:"...'Dbname'.'tablename'..."

On my local machine it runs well. The SQL query is: "... 'Dbname'.'tablename' ... "

在RDS Mysql上的AWS上进行了Docker化.
SQL查询为:"...'Dbname'.'TableName'..."

Dockerized on AWS on the RDS Mysql.
The SQL query is: "... 'Dbname'.'TableName' ... "

然后查询当然会失败.

我的配置:

属性:

#Hibernate (Spring-JPA)
hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

POM:

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <spring-cloud-services.version>1.5.0.RELEASE</spring-cloud-services.version>
        <spring-cloud.version>Dalston.SR3</spring-cloud.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-eureka</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-eureka-server</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>io.pivotal.spring.cloud</groupId>
            <artifactId>spring-cloud-services-starter-service-registry</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
            <dependency>
                <groupId>io.pivotal.spring.cloud</groupId>
                <artifactId>spring-cloud-services-dependencies</artifactId>
                <version>${spring-cloud-services.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

因此，很明显，我的NamingStrategy设置将在本地或AWS中被忽略或覆盖.

So obviously my NamingStrategy settings will be ignored or overriden in local or aws.

但是在这一点上，我可能需要一些帮助.

But at this point I could need some help.

PS:为什么我找不到有关Hibernate命名策略设置的有用文档?！?例如:strat-setting-A => TableName-> TABLE_NAME等...为什么我必须阅读代码才能理解这一点?(T.T)

PS: And why I cant find any useful documentatins about the Hibernate naming strategy settings?!? e.g.: strat-setting-A => TableName -> TABLE_NAME etc... Why do I have to read the code to understand this? (T.T)

万岁！我有原因.我的本地数据库配置不同.好吧，默认情况下: lower_case_table_names = 1 因此，它只是忽略了CamelCase.但是在aws上，似乎将其设置为 0 ，这意味着它将使用骆驼的名称.

Hooray! I have the cause. My local db config is diffrent. Well its by default: lower_case_table_names=1 So it just ignores CamelCase. But on aws it seems to be set to 0 Which means it takes care of camel case names.

因此，灵魂很容易:将我的命名策略设置为仅小写！只是在哪里可以找到配置正确的文档?:-)

So soultion is easy: Setting my naming strategy to lowercase only! Just WHERE do I find the docs which config is right?? :-)

好吧...最好不要使用文档，然后再使用错误的文档！

Well anyway... better no docs then wrong docs!

解决方案:

2个选项:

1-使用@Table()装饰器手动配置POJO.2-从属性中删除休眠命名设置(或将其更改为所需的名称)

1 - Configuring the POJOs manually with @Table() decorator. 2 - Removing the hibernate naming setting from the properties (or change it to what ever needed)

PS:如果有人知道一个好的文档，Iam仍然会对所有可能的设置感兴趣.

PS: Iam still interessted into all setting possibilites if someone know a good doc.

这篇关于在AWS上-无法提取ResultSet;SQL [n/a]；嵌套的异常是org.hibernate.exception.SQLGrammarException:无法提取ResultSet AWS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！