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
Highlighted
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

View solution in original post


All Replies
Highlighted
Thirteen 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'"

 

Highlighted
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.

Highlighted
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

View solution in original post

Highlighted
Five Stars

R

 
Highlighted
Four Stars

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

I getting error that mysql.com cannot resolved when i am using marinaDB

Highlighted
Moderator

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

Hello @kanuparthy 

On which talend build version you got this issue? What's marinaDB version are you using? More information will be helpful for us to address your issue.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Highlighted
Four Stars

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

I am getting the below error when i am trying to insert the data.

 

I am using Talend 6.1 Version and mysql version 5.7.27

 

 

Starting job H_DIM_FAC_PROVIDER_EXP_VISIT at 01:07 04/12/2019.

[statistics] connecting to socket on port 3896
[statistics] connected
failed to close the connection in tMysqlOutput_2 :Communications link failure during rollback(). Transaction resolution unknown.
Exception in component tMysqlOutput_2
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.PreparedStatement.executeBatch(PreparedStatement.java:1356)
at aco_final_last.h_dim_fac_provider_exp_visit_0_1.H_DIM_FAC_PROVIDER_EXP_VISIT.tMysqlInput_2Process(H_DIM_FAC_PROVIDER_EXP_VISIT.java:4298)
at aco_final_last.h_dim_fac_provider_exp_visit_0_1.H_DIM_FAC_PROVIDER_EXP_VISIT.runJobInTOS(H_DIM_FAC_PROVIDER_EXP_VISIT.java:17886)
at aco_final_last.h_dim_fac_provider_exp_visit_0_1.H_DIM_FAC_PROVIDER_EXP_VISIT.main(H_DIM_FAC_PROVIDER_EXP_VISIT.java:17743)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 190,789 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.
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.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4155)
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)
... 5 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3161)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3615)
... 13 more
Picked up _JAVA_OPTIONS: -Xmx1024M
[statistics] disconnected
Job H_DIM_FAC_PROVIDER_EXP_VISIT ended at 01:10 04/12/2019. [exit code=1]

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog