Sping Boot + JPA[Hibernate backed] + mysql =数据截断:第1行的列“ATTRIBUTE_NAME”的数据太长

guykilcj  于 2023-08-02  发布在  Mysql
关注(0)|答案(2)|浏览(124)

尝试对mysql数据库运行insert语句时发生错误。我已经为hibernate打开了sql args跟踪,以确切地查看操作转换为查询以及绑定的内容。
在mysql数据库中,列ATTRIBUTE_NAME是VARCHAR(200)...查看跟踪的日志,我看到字符串是79个字符(“org.springframework.security.web.csrf.HttpSessionCsrfTokenRepository.CSRF_TOKEN”)。
下面是日志-请给我指出正确的方向,因为这确实让我很困惑。查找“SQL错误:1406,SQLState:22001”并在此之前读取条目。

The attr: org.springframework.security.web.csrf.HttpSessionCsrfTokenRepository.CSRF_TOKEN
 and the length: 79
The attr: SPRING_SECURITY_CONTEXT
 and the length: 23
2019-02-07 09:07:50.761 DEBUG 18764 --- [nio-8998-exec-8] org.hibernate.SQL                        : select CODE_NAMEse0_.id as id1_16_, CODE_NAMEse0_.EXPIRY_DATE as EXPIRY_D2_16_, CODE_NAMEse0_.INACTIVE_INTERVAL_MIN as INACTIVE3_16_, CODE_NAMEse0_.SESSION_TYPE as SESSION_4_16_, CODE_NAMEse0_.SESSION_ID as SESSION_5_16_, CODE_NAMEse0_.START_DATE as START_DA6_16_, CODE_NAMEse0_.username as username7_16_ from CODE_NAME_SESSION CODE_NAMEse0_ where CODE_NAMEse0_.username=?
2019-02-07 09:07:50.761 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [0835623872]
2019-02-07 09:07:50.770 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_16_] : [BIGINT]) - [474]
2019-02-07 09:07:50.770 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([EXPIRY_D2_16_] : [TIMESTAMP]) - [2019-02-07 09:11:40.0]
2019-02-07 09:07:50.770 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([INACTIVE3_16_] : [BIGINT]) - [5]
2019-02-07 09:07:50.771 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([SESSION_5_16_] : [VARCHAR]) - [67898350-7858-4de1-8e45-e6545768b875]
2019-02-07 09:07:50.771 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([START_DA6_16_] : [TIMESTAMP]) - [2019-02-07 09:06:40.0]
2019-02-07 09:07:50.771 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([username7_16_] : [VARCHAR]) - [0835623872]
2019-02-07 09:07:50.771 DEBUG 18764 --- [nio-8998-exec-8] org.hibernate.SQL                        : select CODE_NAMEse0_.CODE_NAME_SESSION_ID as CODE_NAME4_17_0_, CODE_NAMEse0_.id as id1_17_0_, CODE_NAMEse0_.id as id1_17_1_, CODE_NAMEse0_.ATTRIBUTE_NAME as ATTRIBUT2_17_1_, CODE_NAMEse0_.ATTRIBUTE_VALUE as ATTRIBUT3_17_1_, CODE_NAMEse0_.CODE_NAME_SESSION_ID as CODE_NAME4_17_1_ from CODE_NAME_SESSION_ATTRIBUTE CODE_NAMEse0_ where CODE_NAMEse0_.CODE_NAME_SESSION_ID=?
2019-02-07 09:07:50.771 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [474]
2019-02-07 09:07:50.773 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_17_1_] : [BIGINT]) - [1410]
2019-02-07 09:07:50.774 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([ATTRIBUT2_17_1_] : [VARCHAR]) - [SPRING_SECURITY_CONTEXT]
2019-02-07 09:07:50.776 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([ATTRIBUT3_17_1_] : [VARBINARY]) - [org.springframework.security.core.context.SecurityContextImpl@2ec961af: Authentication: za.co.intellimali.CODE_NAME.security.AuthenticationToken@2ec961af: Principal: za.co.intellimali.CODE_NAME.domain.CODE_NAMESession@7552b3f3; Credentials: [PROTECTED]; Authenticated: true; Details: null; Granted Authorities: ROLE_MEAL, ROLE_ACCOM, ROLE_STU, MEMBER]
2019-02-07 09:07:50.776 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([CODE_NAME4_17_1_] : [BIGINT]) - [474]
2019-02-07 09:07:50.776 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([CODE_NAME4_17_0_] : [BIGINT]) - [474]
2019-02-07 09:07:50.777 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_17_0_] : [BIGINT]) - [1410]
2019-02-07 09:07:50.780 DEBUG 18764 --- [nio-8998-exec-8] org.hibernate.SQL                        : insert into CODE_NAME_SESSION_ATTRIBUTE (ATTRIBUTE_NAME, ATTRIBUTE_VALUE, CODE_NAME_SESSION_ID) values (?, ?, ?)
2019-02-07 09:07:50.780 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [org.springframework.security.web.csrf.HttpSessionCsrfTokenRepository.CSRF_TOKEN]
2019-02-07 09:07:50.780 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARBINARY] - [org.springframework.security.web.csrf.DefaultCsrfToken@6f0adee5]
2019-02-07 09:07:50.780 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [BIGINT] - [474]
2019-02-07 09:07:50.791  WARN 18764 --- [nio-8998-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1406, SQLState: 22001
2019-02-07 09:07:50.792 ERROR 18764 --- [nio-8998-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper   : Data truncation: Data too long for column 'ATTRIBUTE_NAME' at row 1
2019-02-07 09:07:50.805 DEBUG 18764 --- [nio-8998-exec-8] org.hibernate.SQL                        : select CODE_NAMEse0_.id as id1_16_, CODE_NAMEse0_.EXPIRY_DATE as EXPIRY_D2_16_, CODE_NAMEse0_.INACTIVE_INTERVAL_MIN as INACTIVE3_16_, CODE_NAMEse0_.SESSION_TYPE as SESSION_4_16_, CODE_NAMEse0_.SESSION_ID as SESSION_5_16_, CODE_NAMEse0_.START_DATE as START_DA6_16_, CODE_NAMEse0_.username as username7_16_ from CODE_NAME_SESSION CODE_NAMEse0_ where CODE_NAMEse0_.SESSION_ID=?
2019-02-07 09:07:50.806 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [67898350-7858-4de1-8e45-e6545768b875]
2019-02-07 09:07:50.807 TRACE 18764 --- [nio-8998-exec-8] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id1_16_] : [BIGINT]) - [474]

字符串
表:x1c 0d1x
java端的变量:

@Column(name = "ATTRIBUTE_NAME")
private String attributeName;


我肯定漏掉了什么明显的东西。
编辑:我从wireshark中获得的信息

insert into CODE_NAME_SESSION_ATTRIBUTE (ATTRIBUTE_NAME, ATTRIBUTE_VALUE, CODE_NAME_SESSION_ID) 
values 
(
    'org.springframework.security.web.csrf.HttpSessionCsrfTokenRepository.CSRF_TOKEN'
    , _binary'..\0.sr\06org.springframework.security.web.csrf.DefaultCsrfTokenZ.../....\0.L\0\nheaderNamet\0.Ljava/lang/String;L\0\rparameterNameq\0~\0.L\0.tokenq\0~\0.xpt\0.X-CSRF-TOKENt\0._csrft\0$04735f03-57cf-4984-860c-e8e03400d432'
    , 474);
    ....~.#22001Data too long for column 'ATTRIBUTE_NAME' at row 1  ....rollback................SET autocommit=1................SET autocommit=0................commit................SET autocommit=1................SET autocommit=0................select CODE_NAMEse0_.id as id1_16_, CODE_NAMEse0_.EXPIRY_DATE as EXPIRY_D2_16_, CODE_NAMEse0_.INACTIVE_INTERVAL_MIN as INACTIVE3_16_, CODE_NAMEse0_.SESSION_TYPE as SESSION_4_16_, CODE_NAMEse0_.SESSION_ID as SESSION_5_16_, CODE_NAMEse0_.START_DATE as START_DA6_16_, CODE_NAMEse0_.username as username7_16_ from CODE_NAME_SESSION CODE_NAMEse0_ where CODE_NAMEse0_.SESSION_ID='36c6cb34-aa46-4821-b3a4-15e80c7c57fa'


上面的代码在mysql workbench中运行时会显示一个警告,并将ATTRIBUTE_NAME截断为'org.springframework.security.web.csrf.HttpSessionC'
如果我跑步:

insert into CODE_NAME_SESSION_ATTRIBUTE (ATTRIBUTE_NAME, ATTRIBUTE_VALUE, CODE_NAME_SESSION_ID) 
values 
(
    'CSRF_TOKEN'
    , _binary'..\0.sr\06org.springframework.security.web.csrf.DefaultCsrfTokenZ.../....\0.L\0\nheaderNamet\0.Ljava/lang/String;L\0\rparameterNameq\0~\0.L\0.tokenq\0~\0.xpt\0.X-CSRF-TOKENt\0._csrft\0$04735f03-57cf-4984-860c-e8e03400d432'
    , 474);


在没有警告的情况下工作,显示表没有为ATTRIBUTE_NAME占用更长的长度。我觉得现在这是一个MySQL的问题,而不是Java + Spring + JPA

7tofc5zh

7tofc5zh1#

Antoniosss的问题:SHOW CREATE TABLE youtTableName
这就引出了问题。原因如下:
最初使用spring session使用他们的脚本创建了一个名为SPRING_SESSION_ATTRIBUTE的表...现在我们更改了这个表以添加更多功能和名称,但在dev上我们仍然将其作为副本(应该删除它)。出于习惯,我改变了那里的值,并在我应该改变的表根本不是那个表而是我们的自定义表时提交

cclgggtu

cclgggtu2#

原因是hibernate * 默认String大小为255 *;可以使用 *@Column注解指定String长度。
x1c 0d1x的数据
让我们假设我们的插入字符串长度应该是
1000**字符长度。我们可以像下面这样解决它。

@Column(length = 1000)
private String data;

字符串

相关问题