Connect to a remote server error

Highlighted
Four Stars

Connect to a remote server error

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 


Accepted Solutions
Six Stars JVL
Six Stars

Re: Connect to a remote server error

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


All Replies
Six Stars JVL
Six Stars

Re: Connect to a remote server error

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

Four Stars

Re: Connect to a remote server error

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

Re: Connect to a remote server error

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 8) 
  • 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 

 

Six Stars JVL
Six Stars

Re: Connect to a remote server error

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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog