com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DESTSPACEKEY' at row 1

Atlassian JIRA | Husein Alatas [Atlassian] | 6 years ago
  1. 0

    h5. Steps to reproduce: # Create a page # Insert the following text in Rich-Text Editor: {noformat} [this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. thissssss is 256 character:test123] {noformat} # Save the page # User will get a System Error page with the following error: {noformat} org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: [com.atlassian.confluence.links.OutgoingLink#4784133]; SQL []; Data truncation: Data too long for column 'DESTSPACEKEY' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DESTSPACEKEY' at row 1 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:110) caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DESTSPACEKEY' at row 1 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3561) {noformat} h5. Workaround # Add a space after the opening square bracket '[' or before the closing square bracket '[' or, # Replace the colon ':' with: {noformat} : {noformat} or, # Insert the text inside [Noformat Macro|http://confluence.atlassian.com/display/DOC/Noformat+Macro] or [Code Block Macro|http://confluence.atlassian.com/display/DOC/Code+Block+Macro]. h5. Findings I think this happens because Rich-Text Editor will take square brackets '[ ]' as a link. So any characters before the colon ':' within the square brackets '[ ]' will be considered as a Space Key. If there are more than 255 characters for that part - ie. before colon ':' within the square brackets '[ ]', this will cause an error because {{DESTSPACEKEY}} column in {{LINKS}} table can only hold 255 characters value as shown below: {noformat} mysql> desc links; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | LINKID | bigint(20) | NO | PRI | NULL | | | DESTPAGETITLE | varchar(255) | YES | | NULL | | | DESTSPACEKEY | varchar(255) | NO | MUL | NULL | | | CONTENTID | bigint(20) | NO | MUL | NULL | | | CREATOR | varchar(255) | YES | | NULL | | | CREATIONDATE | datetime | YES | | NULL | | | LASTMODIFIER | varchar(255) | YES | | NULL | | | LASTMODDATE | datetime | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ {noformat} This bug has been tested in MySQL, Postgre and SQL Server. But it seems to be not an issue in HSQL.

    Atlassian JIRA | 6 years ago | Husein Alatas [Atlassian]
    com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DESTSPACEKEY' at row 1
  2. 0

    h5. Steps to reproduce: # Create a page # Insert the following text in Rich-Text Editor: {noformat} [this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. this is 256 character. thissssss is 256 character:test123] {noformat} # Save the page # User will get a System Error page with the following error: {noformat} org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: [com.atlassian.confluence.links.OutgoingLink#4784133]; SQL []; Data truncation: Data too long for column 'DESTSPACEKEY' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DESTSPACEKEY' at row 1 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:110) caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DESTSPACEKEY' at row 1 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3561) {noformat} h5. Workaround # Add a space after the opening square bracket '[' or before the closing square bracket '[' or, # Replace the colon ':' with: {noformat} : {noformat} or, # Insert the text inside [Noformat Macro|http://confluence.atlassian.com/display/DOC/Noformat+Macro] or [Code Block Macro|http://confluence.atlassian.com/display/DOC/Code+Block+Macro]. h5. Findings I think this happens because Rich-Text Editor will take square brackets '[ ]' as a link. So any characters before the colon ':' within the square brackets '[ ]' will be considered as a Space Key. If there are more than 255 characters for that part - ie. before colon ':' within the square brackets '[ ]', this will cause an error because {{DESTSPACEKEY}} column in {{LINKS}} table can only hold 255 characters value as shown below: {noformat} mysql> desc links; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | LINKID | bigint(20) | NO | PRI | NULL | | | DESTPAGETITLE | varchar(255) | YES | | NULL | | | DESTSPACEKEY | varchar(255) | NO | MUL | NULL | | | CONTENTID | bigint(20) | NO | MUL | NULL | | | CREATOR | varchar(255) | YES | | NULL | | | CREATIONDATE | datetime | YES | | NULL | | | LASTMODIFIER | varchar(255) | YES | | NULL | | | LASTMODDATE | datetime | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ {noformat} This bug has been tested in MySQL, Postgre and SQL Server. But it seems to be not an issue in HSQL.

    Atlassian JIRA | 6 years ago | Husein Alatas [Atlassian]
    com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DESTSPACEKEY' at row 1
  3. 0

    Criteria.list() ending with BatchUpdateException: Data truncation

    Stack Overflow | 2 years ago
    java.sql.BatchUpdateException: Data truncation: Data too long for column 'COMMUNE' at row 1
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    SphinxQL Data too long for column

    Stack Overflow | 4 years ago | Colin Pickard
    com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'title' at row 1
  6. 0

    MYSQL/JAVA.. Data too long for column 'HOSTNAME' at row 1

    experts-exchange.com | 1 year ago
    com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '20090220210660.000000' for column 'bidding_time' at row 1

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. com.mysql.jdbc.MysqlDataTruncation

      Data truncation: Data too long for column 'DESTSPACEKEY' at row 1

      at com.mysql.jdbc.MysqlIO.checkErrorPacket()
    2. MySQL jdbc
      MysqlIO.checkErrorPacket
      1. com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3561)
      1 frame