Talend 7.1 connection failure with MySQL Server 8.0 (localhost)

Question

I am trying to enstablish a connection between Talend Open Studio 7.1 and MySQL Server 8.0. I am at my first day in using Talend, but when I execute the connection with my database with tDBConnection (the database is 'world', the sample installed with MySQL) I obtain these errors:

[statistics] connecting to socket on port 3738
[statistics] connected
Mon Nov 05 14:37:45 CET 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Exception in component tDBConnection_1 (PrimoTestAcademy)
java.sql.SQLException: The server time zone value 'ora solare Europa occidentale' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:832)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at thinkopen.primotestacademy_0_1.PrimoTestAcademy.tDBConnection_1Process(PrimoTestAcademy.java:379)
    at thinkopen.primotestacademy_0_1.PrimoTestAcademy.runJobInTOS(PrimoTestAcademy.java:849)
    at thinkopen.primotestacademy_0_1.PrimoTestAcademy.main(PrimoTestAcademy.java:698)
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'ora solare Europa occidentale' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
    at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:128)
    at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2236)
    at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2260)
    at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1314)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:963)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:822)
    ... 8 more
Mon Nov 05 14:37:45 CET 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Exception in component tDBInput_1 (PrimoTestAcademy)
java.sql.SQLException: The server time zone value 'ora solare Europa occidentale' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:832)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at thinkopen.primotestacademy_0_1.PrimoTestAcademy.tDBInput_1Process(PrimoTestAcademy.java:530)
    at thinkopen.primotestacademy_0_1.PrimoTestAcademy.runJobInTOS(PrimoTestAcademy.java:861)
    at thinkopen.primotestacademy_0_1.PrimoTestAcademy.main(PrimoTestAcademy.java:698)
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value 'ora solare Europa occidentale' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
    at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:128)
    at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2236)
    at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2260)
    at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1314)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:963)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:822)
    ... 8 more
[statistics] disconnected

I tried to reinstall MySQL Server and the jConnector, but nothing has changed. It seems like it is unable to connect to the host "localhost". I have tried to google the error message but I obtained nothing. Thank you in advance for the help.

Could you try appending serverTimezone as an additional parameter to your connection ? (https://stackoverflow.com/questions/26515700/mysql-jdbc-driver-5-1-33-time-zone-issue) Also, why are you using tDBxxx components ? I recommend you use tMysqlxxx or tJDBCxxx components. — Ibrahim Mezouar, Nov 05 '18 at 14:08
Talend is known to have issues with MySQL 8. If you want to use MySQL 8, try using the MariaDB connector in the tMysqlConnection component. Else try installing a lower version of MySQL like 5.5 or so. — shaahiin, Nov 05 '18 at 19:59
I solved this issue editing the my.ini file and adding a new line under [mysqld] that was `'default_time_zone='+01:00'` . I restarted the server and it worked fine! Thank you a lot! — fx7sts, Nov 06 '18 at 11:34

score 1 · Answer 1 · answered Jun 18 '19 at 11:36

You need to mention the server timezone as well in the Additional JDBC Parameters. And to get rid of SSL Warning, there is one more argument to be passed. In my case, I had server time different from my local computer so in order to comply with the server. My Additional JDBC Parameters look as such:

useSSL=false&serverTimezone=America/Chicago

Also make sure DB Version is set to Mysql 8

score 1 · Answer 2 · edited Aug 26 '19 at 20:07

1

Use the JDBC connection type and add the jar file for JDBC

Example

jdbc:mysql://localhost:3306/sakila?noDatetimeStringSync=true&serverTimezone=UTC

Driver

mysql-connector-java-8.0.11.jar

edited Aug 26 '19 at 20:07

Mehdi Yeganeh

2,019
2
24
42

answered Aug 26 '19 at 18:42

Tarkeshwar Prasad

19
3

Talend 7.1 connection failure with MySQL Server 8.0 (localhost)

2 Answers2