Getting Error “Data truncation: Data too long for column 'USER'” from MySQL trigger

Question

I have a table Project:

column name | Type
------------|-------------
id          | int
name        | varchar(255)
priority    | int

...

And I create a trigger for updating Project:

CREATE TRIGGER `Project_After_Update`
AFTER UPDATE ON `Project`
FOR EACH ROW
BEGIN
    DECLARE `db_user`   varchar(255)    DEFAULT (select `USER` from `information_schema`.`processlist` WHERE `ID`=connection_id());
    DECLARE `program_name`  varchar(255)    DEFAULT (select `ATTR_VALUE` from `performance_schema`.`session_connect_attrs` WHERE `ATTR_NAME` = 'program_name' AND `processlist_id` = connection_id());


    INSERT INTO `Project_Revision`(
        `project_id` ,
        `name`,
        `priority`,
        `editor`,
        `edit_comment`
    )VALUES(
        new.`id`,
        new.`name`,
        new.`priority`,
        `db_user`,
        concat('[Auto Comment]\nEdit by ', `program_name`)
    );
END

I use Hibernate to update it:

public void updateProjectPriority(int id, int priority){
    // code for get session

    Query query = session.createQuery("UPDATE Project SET priority=:PPriority WHERE id=:PID");
    query.setParameter("PID", id);
    query.setParameter("PPriority", priority);
    query.executeUpdate();

    // code for close connection
}

Usually it can works. However, it sometimes gives me the error message:

org.hibernate.exception.DataException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:69)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211)
    at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:109)
    at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:78)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:445)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:379)
    at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1322)
    at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:118)
    at com.example.DAO.updatePriority(ProjectDAO.java:110)
    at sun.reflect.GeneratedMethodAccessor56.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:668)
    at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:223)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1517)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1474)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'USER' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2983)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
    ... 49 more

I have no idea why I got it.

Also, it makes me confuce since neither Project nor Project_Revision has column 'USER'. In fact, Neither table in my database has column called 'User'.

Possible duplicate of [Data truncation: Data too long for column 'logo' at row 1](http://stackoverflow.com/questions/21522875/data-truncation-data-too-long-for-column-logo-at-row-1) — Aniket Kulkarni, Oct 08 '15 at 05:55
What is the size of `User` column in `information_schema.processlist`? — Aniket Kulkarni, Oct 08 '15 at 05:56
@AniketKulkarni I have tried to set the type of `Project_Revision.editor` to `varchar(255)`, `text` or `longtext` but the issue still happen. — Shiyou, Oct 08 '15 at 06:21
Also, [Data truncation: Data too long for column 'logo' at row 1](http://stackoverflow.com/q/21522875/1618596) doesn't give me the answer why the column whose Data is too long is **USER**. — Shiyou, Oct 08 '15 at 06:43

score 0 · Answer 1 · answered Oct 15 '15 at 05:27

I find the way to solve the issue but I don’t know why:

DECLARE `db_user`      varchar(255) DEFAULT '';
DECLARE `program_name` varchar(255) DEFAULT '';

select `USER`       into `db_user`      from `information_schema`.`processlist`           WHERE `ID` = connection_id();
select `ATTR_VALUE` into `program_name` from `performance_schema`.`session_connect_attrs` WHERE `ATTR_NAME` = 'program_name' AND `processlist_id` = connection_id();

I just change the way for setting variables (use select into to set its value).

After changing the trigger, the issue never happen. However, I have no idea why it can solve.

Getting Error “Data truncation: Data too long for column 'USER'” from MySQL trigger

1 Answers1