MySQL Bugs: #87384: MysqlDataTruncation

Bug #87384	MysqlDataTruncation
Submitted:	11 Aug 2017 2:56	Modified:	10 Oct 2017 15:35
Reporter:	Philippe Riand	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.2dmr	OS:	Any (x64)
Assigned to:		CPU Architecture:	Any
Tags:	cte 8 recursive jdbc java connector

Description:
We have a code generator that generates tables and queries for multiple databases (Oracle, Postgres...). Recently, we updated our MySQL generator to take advantage of the new CTE capability available in 8.0. When we execute the request bellow from the JDBC driver, we get the following exception:

Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'P' at row 9
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:98)
	at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1867)
	at com.mysql.cj.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1923)

When we execute the same request from the workbench (6.3.9), it executes like a charm: we get the proper result back. So it seems to be related to the JDBC driver.

Here is the request:
WITH RECURSIVE REC AS ((SELECT 0 AS RID,D.DOCID,D.STOREID,D.UNID,D.PSTOREID,D.PARENT,0 AS LEVEL,D.JSON,0 AS READF,D.UNID AS BP,'' AS P FROM tests_DOC D WHERE (D.STOREID='Crud') AND (D.INSTID='') AND (D.PSTOREID IS NULL )) UNION ALL (SELECT 0 AS RID,R.DOCID,R.STOREID,R.UNID,R.PSTOREID,R.PARENT,REC.LEVEL+1 AS LEVEL,R.JSON,0 AS READF,REC.BP AS BP,CONCAT(REC.P,' ',R.UNID) AS P FROM tests_DOC R INNER JOIN REC ON (REC.STOREID=R.PSTOREID) AND (REC.UNID=R.PARENT) WHERE (LEVEL<4) AND (R.STOREID='Crud') AND (R.INSTID='')) )SELECT * FROM REC ORDER BY BP, P

And the driver being used:
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.7-dmr</version>
		</dependency>

How to repeat:
I'll be happy to send you the database if you're interested, which I presume is the data directory. Just let me know what to send you and where (can use a dropbox folder if necessary)

Hello  Philippe,

Thank you for the bug report.
Could you please provide repeatable test case (exact steps, database/create table statements, full stack trace, sample code etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

Sure - I just created a sample Java snippet that reproduces the problem with my database. I tried to export the tabe as sql statements and re-import it in another schema but then it works. I suspect that there is an issue with character sets. I'm not a MySQL expert: how can I give you my tables as they exist? Should I just zip the data directory and send it to you?
Finally, what is the best way for sending you a large file?

Hello  Philippe,

Thank you for your feedback.
If the data you need to attach is more than 3MB, you should create a compressed archive of the data and a README file that describes the data with a filename that includes the bug number (recommended filename: mysql-bug-data-87384.zip) and upload one to sftp.oracle.com.

Thanks,
Chiranjeevi.

I cannot connect to your FTP site so here is a dropbox link: https://www.dropbox.com/s/yri7rh9664z4twe/mysql-bug-data-87384.zip?dl=0
Let me know upon reception so I'll remove the file.

Hello - Do you have any news on this one? Were you able to reproduce the problem with the database I sent you?

Hello  Philippe,

Thank you for your feedback.
I could not repeat the issue using with MySQL 8.0.2 dmr and 5.7.19 with Connector/Net 5.1.44/ 8.0.7 versions.
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Chiranjeevi.

Hello - I bet the issue is with the JDBC driver (Connector/J), not Connect/NET. I bet this is with the JDBC client implementation. Have you tried it with the JDBC driver?

Is there any update on this issue?