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

[Anonymous]

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

[fdenis]

hello,
can you insert without bulk?

[Anonymous]

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

[Anonymous]

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.

[Anonymous · Accepted solution]

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

[kanuparthy]

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

[Anonymous]

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

[kanuparthy]

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]