topic Re: Communications link failure during rollback(). Transaction resolution unknown. in Data Quality

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

LHerrera1663782386 — Fri, 15 Nov 2024 22:32:14 GMT

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

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

Anonymous — Wed, 21 Sep 2022 21:53:33 GMT

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;

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

LHerrera1663782386 — Thu, 22 Sep 2022 00:12:02 GMT

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

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

LHerrera1663782386 — Thu, 22 Sep 2022 00:13:09 GMT

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.

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

Anonymous — Thu, 22 Sep 2022 00:45:02 GMT

Run that in MySQL.

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

Anonymous — Thu, 22 Sep 2022 00:48:50 GMT

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

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

LHerrera1663782386 — Thu, 22 Sep 2022 02:32:07 GMT

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

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

LHerrera1663782386 — Thu, 22 Sep 2022 02:37:13 GMT

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