CODEWITHSUNDEEP
javamysqljdbc
m.aibin
m.aibin

Reputation: 3593

How to pass session variables in JDBC url properly?

I have to increase group_concat_max_len. I cannot do it by preparestatement, and also I cannot do it in mysql my.conf file.

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

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 like like this:

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

because the official docs says:

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

Upvotes: 23

Views: 17679

Answers (3)

naian
naian

Reputation: 136

The MySQL Connector/J Configuration Properties documentation has been updated since with a better description, especially when it comes to setting multiple system variables in the connection URL :

sessionVariables

A comma or semicolon separated list of name=value pairs to be sent as SET [SESSION] ... to the server when the driver connects.

Since version: 3.1.8

Which results in URLs like this one :

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

Semicolons can be used instead of commas. A mix of the two can be used within the same URL.

This will only work for system variables that have a SESSION scope (you can find out which ones can be set at SESSION-level by looking at the list of Server System Variables). The connection will fail if the variable doesn't exist, if it is not a server system variable or if its scope is GLOBAL-only.

Upvotes: 7

Ned
Ned

Reputation: 181

If you need to add more than one session parameter you can do it like this:

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

Upvotes: 14

Cristian Greco
Cristian Greco

Reputation: 2596

Try this:

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

Upvotes: 31

Related Questions