Connecting talnd to MySQL [transation read-only status server]

Five Stars

Connecting talnd to MySQL [transation read-only status server]

Hey guys, 

 

I am trying to connect Talend (version 7.0.1) to MySQL Server (version 8.0.12), I can successfully connect and perform simple select queries to the database. However as soon as I try to perform a bulk Insert I get the following exception:

 

 

Starting job A_2_ProcessRestData_NEW at 17:59 20/08/2018.

[statistics] connecting to socket on port 3407
[statistics] connected
Exception in component tDBOutputBulkExec_1_tMBE (A_2_ProcessRestData_NEW)
java.sql.SQLException: Could not retrieve transation read-only status server
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:949)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
	at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3976)
	at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3947)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:813)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:788)
	at living_dashboard.a_2_processrestdata_new_0_1.A_2_ProcessRestData_NEW.tFileInputDelimited_1Process(A_2_ProcessRestData_NEW.java:4671)
	at living_dashboard.a_2_processrestdata_new_0_1.A_2_ProcessRestData_NEW.runJobInTOS(A_2_ProcessRestData_NEW.java:6955)
	at living_dashboard.a_2_processrestdata_new_0_1.A_2_ProcessRestData_NEW.main(A_2_ProcessRestData_NEW.java:6733)
Caused by: java.sql.SQLException: Unknown system variable 'tx_read_only'
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2781)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)
	at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3970)
	... 6 more
[statistics] disconnected

Job A_2_ProcessRestData_NEW ended at 17:59 20/08/2018. [exit code=1]

Do you have any clue on what might be? 

 

Thanks in advance

 

 


Accepted Solutions
Five Stars

Re: Connecting talnd to MySQL [transation read-only status server]

I eventually solved the question. It turns out that the DB Version to be used cannot be MySQL 5 as I was specifying. I need to use MariaDB instead. Problem solved Smiley Wink


All Replies
Twelve Stars

Re: Connecting talnd to MySQL [transation read-only status server]

hello,
can you insert without bulk?

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Highlighted
Five Stars

Re: Connecting talnd to MySQL [transation read-only status server]

Hy @fdenis. No, normal insert just ouputs pretty much the same: Hi. No, normal insert also outputs "[statistics] connecting to socket on port 3612 [statistics] connected Could not retrieve transation read-only status server [statistics] disconnected". For what I understand, tx_read_only is a variable that defines that the database is read-only. In my understanding, during the connection, Talend asks for this variable back, although the serve cannot provide it because it does not exist. If I type the command "SELECT @@session.tx_read_only " the ouput is "select @@tx_read_only LIMIT 0, 1000 Error Code: 1193. Unknown system variable 'tx_read_only'"

 

Five Stars

Re: Connecting talnd to MySQL [transation read-only status server]

So, According to https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html, the variable tx_read_only was removed in 8.0.3 version. I have the 8.0.12. So, either updating the driver or downgrading mysql version should work. Fingers crossed. Lets hope.

Five Stars

Re: Connecting talnd to MySQL [transation read-only status server]

I eventually solved the question. It turns out that the DB Version to be used cannot be MySQL 5 as I was specifying. I need to use MariaDB instead. Problem solved Smiley Wink

Five Stars

R

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now