Envers + MYSQL + List< String> = SQLSyntaxErrorException:指定的密钥太长； [英] Envers + MYSQL + List<String> = SQLSyntaxErrorException: Specified key was too long;

问题描述

我正在使用Envers扩展具有审计支持的现有应用程序.我注释了所有@Entity类，并且得到了一堆Exception跟踪.在查看它们时，它们似乎都与具有以下形式的属性定义有关

protected List<String> testActivities;  

@ElementCollection
protected List<String> getTestActivities() {
    return testActivities;
}

public void setTestActivities(List<String> testActivities) {
    this.testActivities = FXCollections.observableList(testActivities);
}

所有异常都是List<String>属性，并且getter方法具有@ElementCollection批注.

我总是收到异常(这是上面的testActivities属性的异常)

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table TestCase_testActivities_AUD (REV integer not null, TestCase_id bigint not null, testActivities varchar(255) not null, REVTYPE tinyint, primary key (REV, TestCase_id, testActivities)) engine=MyISAM" via JDBC Statement
..
Caused by: java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
..

我猜这是包含testActivities的主键吗?

testActivities属性引用用户必须执行的指令列表，因此，如某些与键长问题相关的StackOverflow页面上所建议的那样，减少代码端的String长度可能不是一个选择吗? /p>

当前所有表都是使用DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci创建的，我可能可以通过使用utf8而不是utf8mb4来节省内存，但这是一个很好且可靠的解决方案吗?

如何正确解决此问题?对于以上两点，我持不同意见.

我运行MySQL Server 8.0.15，MyISAM和
我正在使用Spring Boot，它为我提供了Hibernate Envers 5.3.10

解决方案

我忘了提到我在课程级别使用@Access(AccessType.PROPERTY).无论如何，我扩展了相关的getter方法

@ElementCollection  
@Column(length=175)     // keep in sync with maxDBStringLength
public List<String> getEnvironmentalInterfaces() {
    return environmentalInterfaces;
}

因此实际上可以解决问题.但是，为了不丢失信息，我还扩展了所有方法，将元素添加到列表中，就像这样

// Must be in sync with @Column(length=175)  definitions
protected static int maxDBStringLength = Constants.maxDBStringLength;

public void addEnvironmentalInterfaces(String environmentalInterface) throws StringTooLongException {
    if(environmentalInterface.length() > maxDBStringLength) {
        throw new StringTooLongException(maxDBStringLength, environmentalInterface.length());
    }
    environmentalInterfaces.add(environmentalInterface);
}

现在创建所有表.不幸的是，我现在遇到了一个N​​ullPointer问题，您可以在这里创建测试数据时引发NullPointerException -以防万一，您正在经历相同的学习过程.

I am extending an existing application with Audit support using Envers. I annotated all @Entity classes and I got a bunch of Exception traces. When taking a look at them, it seems that they all relate to attribute definitions that have the following form

protected List<String> testActivities;  

@ElementCollection
protected List<String> getTestActivities() {
    return testActivities;
}

public void setTestActivities(List<String> testActivities) {
    this.testActivities = FXCollections.observableList(testActivities);
}

All exceptions are List<String> attributes, and the getter method has a @ElementCollection annotation.

The Exception I am getting is always (here is the exception for the above testActivities attribute)

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table TestCase_testActivities_AUD (REV integer not null, TestCase_id bigint not null, testActivities varchar(255) not null, REVTYPE tinyint, primary key (REV, TestCase_id, testActivities)) engine=MyISAM" via JDBC Statement
..
Caused by: java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
..

I guess the issue is the primary key containing the testActivities?!

The testActivities attribute refers to a list of instructions that a user has to perform, so reducing the String length on the code side, as suggested on some StackOverflow pages related to the key length issue, is probably not an option?!

Currently all tables are created with DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci and I could probably save memory by using utf8 instead of utf8mb4, but is that a good and reliable solution?

How to fix this the right way? I am open to different views regarding the above two points.

I run MySQL Server 8.0.15, MyISAM, and
I am using Spring Boot which gives me Hibernate Envers 5.3.10

解决方案

I forgot to mention that I am using @Access(AccessType.PROPERTY)on the class level. Anyway, I extended the related getter methods

@ElementCollection  
@Column(length=175)     // keep in sync with maxDBStringLength
public List<String> getEnvironmentalInterfaces() {
    return environmentalInterfaces;
}

Thus actually does the trick. However, in order to not loose information, I also extended all methods to add an element to the list, like so

// Must be in sync with @Column(length=175)  definitions
protected static int maxDBStringLength = Constants.maxDBStringLength;

public void addEnvironmentalInterfaces(String environmentalInterface) throws StringTooLongException {
    if(environmentalInterface.length() > maxDBStringLength) {
        throw new StringTooLongException(maxDBStringLength, environmentalInterface.length());
    }
    environmentalInterfaces.add(environmentalInterface);
}

Now all tables are created. Unfortunately I have now a NullPointer issue, which you find here Envers NullPointerException when creating test data - just in case you are going through the same learning curve.

这篇关于Envers + MYSQL + List< String> = SQLSyntaxErrorException:指定的密钥太长；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！