Connect to a remote server error

[Anonymous]

Hello, 'Talended' folks!

I've been trying to connect to a remote server that has already been connected to my MySql Workbench through SSH. Whenever I try to set up the connection through tDBConnection component it throws an error :

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4739923 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

It would be great if anyone can provide guidance. 

Thanks and Regards

Sofia 

[Anonymous · Accepted solution]

Sofia,

You could solve your problem on two ways: in Talend Studio or in MySQL.

I would recommend to take the second one because it is the easiest: Change a configuration in your MySQL database, since I don't know the environment you are working on, it is difficult to handle your problem.

There will be a configuration called  "max_allowed_packet", change the value.

Hope this helps!

Kr,

Jvl7

[Anonymous]

Thank you for your quick response JVL!
The environment we are working on is MySql 8. I am trying to increase the variable size and see how it goes.
Also, I am interested to know the first approach you mentioned i.e through Talend

Best Regards,
Sofia

[Anonymous]

Hey JVL,

So increasing the packet size to 50MB worked for me. Along with that, I observed some parameters that were incorrect during the component configuration : 

• Port should be set to "3306"
• Chose the correct DB type ( my remote server was Maria DB not MySql 😎 
• Use a tSSH component for creating a tunnel 
• Do not remove the additional parameter "noDatetimeStringSync=true"

Hope it helps people who are facing the same error!

Best Regards,

Sofia 

[Anonymous]

Sofia,

Glad your problem is resolved!

If you would like to do it in Talend, I would do it as follows (did not test it):

1) Create a database session;

2) Execute a command to change the packet size in a tRow component;

3) Do your database transaction;

4) Close your database connection.

But a beter approach is to set the variable in the database, otherwise you need to use unnecessary component in Talend (over and over again)...

Kr,

Jvl7