topic Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job in Talend Studio

"GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

SND — Mon, 11 Apr 2022 20:40:06 GMT

Hello everyone,

I am trying to transfer data from a MySql 5.6.48 database table (tDBInput_1) to a Mysql 8.0.22 database table (tMysqlOutput_1) using talend open studio 7.3.1 but I get the error below :

Exception in component tDBInput_1 (ExtractAndLoadFromWSRecipient___Release)

java.sql.SQLException: GC overhead limit exceeded

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)

....

( While I allocated enough memory to the job (-Xms3072M; -Xmx4096M) and activated the "enable stream" option...)

It is as if the tDBInput_1 having to use the driver for mysql 5 (com.mysql.jdbc.Driver) uses that of mysql 8 (com.mysql.cj.jdbc.Driver) as indicated in the error output (I don't know if that means anything...)

Ps :

-> In

the job execution beginning , i have this warning:

loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

-> mysql-connector-java-5.1.30-bin and mysql-connector-java-8.0.18 are the main connectors in lib directory after building the job

-> I was using the same job options with a mariadb database version, to transfer data to a Mariadb database table, and everything was working without errors

Thank you in advance for help.

Regards;

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

Anonymous — Tue, 12 Apr 2022 07:22:39 GMT

Hello,

Do you need to keep both mysql-connector-java-5.1.30-bin and mysql-connector-java-8.0.18 in your PC?

I find this warning on stackoverflow: https://stackoverflow.com/questions/52032739/loading-class-com-mysql-jdbc-driver-this-is-deprecated-the-new-driver-class

Hope it will be helpful for your use case.

Best regards

Sabrina

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

SND — Tue, 12 Apr 2022 11:24:43 GMT

Hello,

Thank you for your response ...

For the warning on stackoverflow it doesn't help me as i am using talend open studio for data integration 😞 , they are talking about hibernate, spring and so on for java apps ...

For the connectors i did not install those by myself, in fact when i use a schema as input from the mysql 5 database connection and another as output from the mysql 8 one and then build the job for deploying purposes, i notice there are these two connectors in the lib directory, and after executing the job i notice that the "GC over limit exceeded" concerns the tmysqlInput (which has "enable stream" option activated ) and the exception mentions "... at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) "; the "..cj.." shows that the mysql 8 driver is used in this case ...

But my main trouble is why i am getting this "GC overhead limit exceeded" error , since i have enough memory allocated to the job and the "enable stream " option activated, i was always proceeding in this way without any error and this time it is not working ... 😞

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

SND — Tue, 12 Apr 2022 19:12:36 GMT

Hi Sabrina

i think i may have a conflict between the two drivers...

When i analyze the source code i see the test below :

java.sql.Statement stmt_tDBInput_1 = conn_tDBInput_1.createStatement();

if(stmt_tDBInput_1 instanceof com.mysql.jdbc.Statement){

((com.mysql.jdbc.Statement)stmt_tDBInput_1).enableStreamingResults();

}else if(stmt_tDBInput_1 instanceof com.mysql.jdbc.jdbc2.optional.JDBC4StatementWrapper){

((com.mysql.jdbc.jdbc2.optional.JDBC4StatementWrapper)stmt_tDBInput_1).enableStreamingResults();

}

And i guess that the "enable stream" option might be never activated for tDBInput_1 ...

how can i safely force talend ( open studio 7.3.1) to use only the mysql-connector-java-8.0.18.jar (for both mysql 5 and mysql 8 components/connexion) as far as the two connectors are already installed on ?

Thanks in advance.

Regards

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

Anonymous — Wed, 13 Apr 2022 06:26:51 GMT

Hello,

I am using the jdbc driver 'mysql-connector-java-8.0.19.jar' and it doesn't show this error.

Is it possible for you to remove the old driver from folder and retry to see if your job is still giving the warning message?

Best regards

Sabrina

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

SND — Wed, 13 Apr 2022 10:58:34 GMT

Hello,

After having built a job in which i use both MySql 5 and MySql 8 , i deleted mysql-connector-java-5.1.30-bin from the lib folder and let the mysql-connector-java-8.0.18 there, but i got an error telling that it can't find the class com.mysql.jdbc.Driver (from mysql-connector-java-5.1.30-bin).

Talend is using in some cases mysql-connector-java-5.1.30-bin and in other ones mysql-connector-java-8.0.18, how can i force it to use only one connector without breaking anything in my previous jobs ?

Regards.

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

Anonymous — Thu, 14 Apr 2022 07:47:53 GMT

Hello,

Maybe we could force a Talend component to use other jar than downloaded by default by Talend Studio.

In module view click on tMysqlXXX components which point to the mysql-connector-java-5.1.30 jar file and click on the pot button and locate the mysql-connector-java-8.0.18 which you downloaded. This will install this jar into studio's .m2 repository.

Feel free to let me know if it is OK with you.

Best regards

Sabrina

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

SND — Thu, 14 Apr 2022 19:10:18 GMT

Hello ,

I did what you said but there are no changes, nothing happens and i still have the mysql-connector-java-5.1.30 jar listed for the tmysqlXXX components ...

below is a screenshot of tMysqlInput case

Regards.

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

Anonymous — Tue, 19 Apr 2022 08:39:49 GMT

Hello,

I will make an investigation on your case with our team and then come back to you as soon as I can.

Best regards

Sabrina

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

Anonymous — Tue, 19 Apr 2022 09:47:56 GMT

Hello,

Because I haven’t been able to recreate the issue and want to try to do that

So we need to see how your job is written. Could you please share a screenshot of your job design? Please mask your sensitive data as well.

Best regards

Sabrina

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

SND — Thu, 21 Apr 2022 01:35:45 GMT

Hello,

Thank you for assistance.

So i redesigned my job after making a downgrade to MySql 5 just for a while as far as i had deadlines, but below is the case :

i have two databases A (MySQL 5) and B (MySQL 😎 and i want to migrate data from table A.ta (with over 10 millions of rows ) to table B.tb ... so i used tMysqlInput and tMysqlOuput components, with enough memory and "enable stream" option activated.

After executing the job , i got the warning first and then the error .

Thank you in advance.

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

Anonymous — Wed, 27 Apr 2022 07:05:10 GMT

Hello,

Sorry for our delay. I was trying to find out if this "GC overhead limit exceeded" error is caused by two versions of Mysql in the same job.

I made a testing job with MySql5 and MySql8 in a same job and get the same warning that "Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary."

Fortunately, it doesn't block job execution and I'm able to migrate my data from Mysql5 to Mysql 8 successfully without deleting old driver(mysql5).

From above scenario, the error warning is not the root cause and "GC overhead limit exceeded" error is probably caused by your migrated data volume, memory and so on.

It would be better that you could post your job design screenshots here which will helpful for us to get more details from your current situation.

Best regards

Sabrina

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

SND — Fri, 20 May 2022 21:34:59 GMT

Hello Sabrina, sorry for the late reply

Thank you for your assistance.

Indeed it might be caused by my data volume (over 11 millions rows) , for the memory i allocated to the job a max of 4G (-Xms3072M; -Xmx4096M) and activated the "enable stream" option to avoid the GC overhead limit exceeded.

It's like the "enable stream" option does not work if two different versions of MySql are used in the same job.

I have tested the same job using two close MySql versions (5.6 for the source and 5.7 for the destination) and it works like a charm ... 😞

Below are some screenshots :

the selected rows from billing table are over 11 millions in number, and for the recipient table it is about the same number of rows (and at the end of the job i am inserting inner join rejects from the tmap in the output "recipient" table.

Hope it will be helpful.

Best regards.

Re: "GC overhead limit exceeded" error when dealing with two versions of Mysql in the same job

Anonymous — Mon, 23 May 2022 04:32:05 GMT

Hello,

That might be caused by DB driver when using the streaming mode.

You are able to get around it and still use the stream, is to set the DB Version to mysql5 not mysql 8. Or without using the streaming mode. Without streaming the driver caches, you can only try to reduce the data.

Feel free to raise a jira issue on talend bug tracker.

Best regards

Sabrina