Communications link failure during rollback(). Transaction resolution unknown.

[LHerrera1663782386]

Hi guys, I have a problem with the connection between talend and mysql... mainly the execution of the talend job stops me the mysql service in windows... I don't know what could be happening, but I attach the log of the error that the console gives me...

please i need your help, i have tried many methods but nothing works....

failed to close the connection in tDBOutput_3 :Communications link failure during rollback(). Transaction resolution unknown.

Exception in component tDBInput_2 (ResultadoS7_Fotos_Final_v2)

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 10.561 milliseconds ago. The last packet sent successfully to the server was 10.561 milliseconds ago.

at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)

at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)

at project_luis.resultados7_fotos_final_v2_0_1.ResultadoS7_Fotos_Final_v2.tDBInput_2Process(ResultadoS7_Fotos_Final_v2.java:801)

at project_luis.resultados7_fotos_final_v2_0_1.ResultadoS7_Fotos_Final_v2.runJobInTOS(ResultadoS7_Fotos_Final_v2.java:5365)

at project_luis.resultados7_fotos_final_v2_0_1.ResultadoS7_Fotos_Final_v2.main(ResultadoS7_Fotos_Final_v2.java:5214)

Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet successfully received from the server was 10.561 milliseconds ago. The last packet sent successfully to the server was 10.561 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.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)

at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)

at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)

at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)

at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:539)

at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:703)

at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:642)

at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:941)

at com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:887)

at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1073)

at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1168)

... 3 more

Caused by: java.net.SocketException: Connection reset

at java.net.SocketInputStream.read(Unknown Source)

at java.net.SocketInputStream.read(Unknown Source)

at sun.security.ssl.SSLSocketInputRecord.read(Unknown Source)

at sun.security.ssl.SSLSocketInputRecord.readHeader(Unknown Source)

at sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(Unknown Source)

at sun.security.ssl.SSLSocketImpl.readApplicationRecord(Unknown Source)

at sun.security.ssl.SSLSocketImpl.access$300(Unknown Source)

at sun.security.ssl.SSLSocketImpl$AppInputStream.read(Unknown Source)

at java.io.FilterInputStream.read(Unknown Source)

at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64)

at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)

at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)

at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52)

at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41)

at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54)

at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44)

at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:533)

... 9 more

[Anonymous]

Can you show the whole job design? This sort of issue can occur with table/row locking. Can you run this query in your query analyser to see whether this is the case?

show processlist;

[LHerrera1663782386]

Hello @Richard Hall​ thank you for replying!

Here I leave a screenshot of the complete process, which is autonomous, I don't need to go to execute queries to MYSQL after loading the file, since in the other jobs the queries are inserted.

the file as such, is a text document, about 700mb on average... it loads a mysql table of about 2.2 million rows in 7 columns (although this is not the problem, it always worked fine)...

if you look at the picture of the post, the process threw error in the job n° 12... now I made an execution and the error was in the n° 6, although if you look well, there is a common factor for now, and it is that the error is in the same type of job...

 

[statistics] connecting to socket on port 3605

[statistics] connected

failed to close the connection in tDBOutput_2 :Communications link failure during rollback(). Transaction resolution unknown.

Exception in component tDBInput_1 (ResultadoS7_Fotos_Final_v2)

java.sql.SQLException: Connection reset

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.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)

at project_luis.resultados7_fotos_final_v2_0_1.ResultadoS7_Fotos_Final_v2.tDBInput_1Process(ResultadoS7_Fotos_Final_v2.java:2884)

at project_luis.resultados7_fotos_final_v2_0_1.ResultadoS7_Fotos_Final_v2.tFileInputDelimited_1Process(ResultadoS7_Fotos_Final_v2.java:2457)

at project_luis.resultados7_fotos_final_v2_0_1.ResultadoS7_Fotos_Final_v2.tDBRow_2Process(ResultadoS7_Fotos_Final_v2.java:1160)

at project_luis.resultados7_fotos_final_v2_0_1.ResultadoS7_Fotos_Final_v2.tDBRow_1Process(ResultadoS7_Fotos_Final_v2.java:936)

at project_luis.resultados7_fotos_final_v2_0_1.ResultadoS7_Fotos_Final_v2.runJobInTOS(ResultadoS7_Fotos_Final_v2.java:12307)

at project_luis.resultados7_fotos_final_v2_0_1.ResultadoS7_Fotos_Final_v2.main(ResultadoS7_Fotos_Final_v2.java:12156)

[statistics] disconnected

[LHerrera1663782386]

By the way, it is not clear to me where I should run the query "show processlist;" could you tell me if it is in mysql or talend? in which section? thank you very much.

[Anonymous]

Run that in MySQL.

[Anonymous]

How is the tDBOutput_1 configured? Can you share a screenshot?

[LHerrera1663782386]

I attach screenshots of the configurations, it is worth mentioning that when the error message appears, automatically the windows MYSQL service is stopped (I attach screenshots of this too)...

SERVICE WINDOWSBASIC SETTINGSADVANCED SETTINGS

[LHerrera1663782386]

here is the result of the query, on the "photos" server where talend runs the jobs

note: I had to "START" the MYSQL service in windows to execute this query, because after the error that talend throws, the mysql service stops and it is not possible to execute queries...