topic Re: Connecting talnd to MySQL [transation read-only status server] in Talend Studio

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

Anonymous — Mon, 20 Aug 2018 18:11:15 GMT

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

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

fdenis — Tue, 21 Aug 2018 07:53:03 GMT

hello,
can you insert without bulk?

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

Anonymous — Tue, 21 Aug 2018 10:01:30 GMT

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'"

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

Anonymous — Tue, 21 Aug 2018 10:32:28 GMT

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.

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

Anonymous — Tue, 21 Aug 2018 13:01:19 GMT

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

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

kanuparthy — Mon, 04 Nov 2019 07:21:44 GMT

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

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

Anonymous — Mon, 04 Nov 2019 08:45:11 GMT

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

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

kanuparthy — Wed, 04 Dec 2019 09:15:18 GMT

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]