topic Re: Reproduce issue with SQL Query Builder in DI 6.4.0 in Talend Studio

Reproduce issue with SQL Query Builder in DI 6.4.0

mobmsc2 — Mon, 12 Jun 2017 15:43:27 GMT

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

Use a tMysqlInput component and connect to a database using a Metadata Db Connection
Select any table schema from the repository
Click Guess Query to generate some query before opening the Query Build
Click running man to execute the query and get the error "String index out of range: 4"
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)

Re: Reproduce issue with SQL Query Builder in DI 6.4.0

cterenzi — Mon, 12 Jun 2017 19:58:25 GMT

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.

Re: Reproduce issue with SQL Query Builder in DI 6.4.0

mobmsc2 — Tue, 13 Jun 2017 12:56:22 GMT

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

the database schema is based off a talend job which creates the tables fresh each time based on a generic schema
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?

Re: Reproduce issue with SQL Query Builder in DI 6.4.0

cterenzi — Tue, 13 Jun 2017 13:54:13 GMT

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.

Re: Reproduce issue with SQL Query Builder in DI 6.4.0

mobmsc2 — Tue, 13 Jun 2017 14:01:52 GMT

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

Re: Reproduce issue with SQL Query Builder in DI 6.4.0

cterenzi — Tue, 13 Jun 2017 14:13:32 GMT

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