Reproduce issue with SQL Query Builder in DI 6.4.0

[mobmsc2]

Can anyone reproduce the following error when using MySQL/MariaDB as input?

1. Use a tMysqlInput component and connect to a database using a Metadata Db Connection
2. Select any table schema from the repository
3. Click Guess Query to generate some query before opening the Query Build
4. Click running man to execute the query and get the error "String index out of range: 4"
5. Copy and paste the "Guess Query" into another Sql tool and note it runs successfully

Open the .metadata/.log file to see errors

!STACK 0
java.lang.StringIndexOutOfBoundsException: String index out of range: 4
	at java.lang.String.charAt(Unknown Source)
	at org.mariadb.jdbc.internal.common.AbstractValueObject.getTimestamp(AbstractValueObject.java:245)
	at org.mariadb.jdbc.MySQLResultSet.getTimestamp(MySQLResultSet.java:3506)
	at org.talend.sqlbuilder.dataset.dataset.DataSet.initialize(DataSet.java:260)
	at org.talend.sqlbuilder.dataset.dataset.DataSet.<init>(DataSet.java:71)
	at org.talend.sqlbuilder.sqlcontrol.SQLExecution.doExecution(SQLExecution.java:165)
	at org.talend.sqlbuilder.sqlcontrol.AbstractSQLExecution$LocalThread.run(AbstractSQLExecution.java:66)

[cterenzi]

That's a mismatch between the length of a String column in your Talend schema and the same column in your database. Check your schema to see which String is defined with length 4 and compare against the same column definition in Mysql.

[mobmsc2]

When I view the schema in DI I don't have any columns set with a row length of 4 but when I view the schema in MySQL I have 1 column of length 4 using the "Year" database datatype which in DI is listed as date with Date Pattern as "dd-MMM-yyyy" and length set as zero. Should that be just "yyyy"

Two confusing parts

1. the database schema is based off a talend job which creates the tables fresh each time based on a generic schema
2. The Table schemas are then imported back directly from the database 

so why would there be an error when talend DI is responsible for identifying the schema used by the database?

[cterenzi · Accepted solution]

Ok, that makes sense given the error stack you posted. (This indicates the error happens when getting a timestamp value: at org.mariadb.jdbc.internal.common.AbstractValueObject.getTimestamp(AbstractValueObject.java:245) )

You may want to change the type of that column in your Talend schema to String. A MariaDB Year does not appear to be a true date type.

[mobmsc2]

Editing the table schema under Metadata ->MyMariaDB->tables from year to string allowed the MySQLinput to work. 

Would you class this as a bug in Talend DI? in that it doesn't know to use the string data type for MariaDB Year?

Also keeping it as date with a date pattern of "yyyy" did not resolve the issue

[cterenzi]

It could qualify as a bug. The default mapping should work.