How to pass session variables to JDBC URL correctly?

Question

I need to increase group_concat_max_len. I cannot do this by preparing, and also cannot do this in the mysql file my.conf.

In mysql docs, I found that there is an option to pass session variables to url. But there is no example, I tried to do it like this:

jdbc.url=jdbc:mysql://xxxx.xx.xx.xx/dbName?sessionVariables=group_concat_max_len:204800 

and I have this exception:

 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':204800' at line 1 

And also I tried it as follows:

 jdbc.url=jdbc:mysql://xxxx.xx.xx.xx/dbName?sessionVariables=group_concat_max_len,204800 

since the official docs say:

 sessionVariables A comma-separated list of name/value pairs to be sent as SET SESSION ... to the server when the driver connects. Since version: 3.1.8 

Any ideas ???

Answer 1

Try the following:

 jdbc.url=jdbc:mysql://xxxx.xx.xx.xx/dbName?sessionVariables=group_concat_max_len=204800 

Answer 2

If you need to add several session parameters, you can do it as follows:

 jdbc:mysql://localhost/database?sessionVariables=FOREIGN_KEY_CHECKS=0&sessionVariables=SQL_SAFE_UPDATES=0