1

Again I need your help. I'm trying to put my java web site online.

What I use :

  • MySQL server : command line mysql -V, result : mysql Ver 15.1 Distrib 10.1.23-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
  • Cayenne
  • Debian server
  • Java (Vaadin)

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

What I tried :

1. Like the error said, I tried to change the value on the server by doing :

  • Log on my server
  • Connect to MySQL with : mysql -u root
  • Enter : SET GLOBAL max_allowed_packet=1073741824;
  • then, restart the server with : /etc/init.d/mysql restart

But I still have the error.

2. I took a look to : How to change max_allowed_packet size

But, When I did the nano /etc/mysql/my.cnf, the file looks like (I don't have any [mysql]) :

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Fri Nov 10 23:57:02 2017 from 82.236.220.195
root@XXXX:~# nano /etc/mysql/my.cnf
  GNU nano 2.7.4               File: /etc/mysql/my.cnf                Modified

# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

In mysql, the folders/files in the 'mysql' folder is :

enter image description here

Any hint will be very appreciate! Thanks

EDIT: In /etc/mysql/mariadb.conf.d/50-server.cnf, I changed :

  1. max_allowed_packet = 1073741824
  2. max_connections = 100000

and I added : net_buffer_length = 1048576

For info : In my workbench, I can see the server variables : enter image description here

EDIT2 : Now, when I select the variable in command line on the server, I have : 

MariaDB [(none)]> SELECT @@global.max_allowed_packet;
+-----------------------------+
| @@global.max_allowed_packet |
+-----------------------------+
|                  1073741824 |
+-----------------------------+
1 row in set (0.00 sec)

SOLUTION Because the error was not explicit.

Thanks to com.mysql.jdbc.PacketTooBigException

My cayenne configuration was : 

<url value="jdbc:mysql://IPADDRESS:22/DBBASENAME" />
<login userName="ServerUserName" password="ServerPassword" />

But it should be : 

<url value="jdbc:mysql://IPADDRESS/DBBASENAME" />
<login userName="MYSQLUserName" password="MYSQLPassword" />
Bob
  • 529
  • 1
  • 7
  • 28

1 Answers1

5

Change it in my.cnf, then restart mysqld.

Better yet, put it in a file under /etc/mysql/mariadb.conf.d/, and specify the section:

[mysqld]
max_allowed_packet = 1073741824

What you did (SET) went away when you restarted. Even so, it only applied to connections that logged in after doing the SET.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Hi @Rick James, Thanks for your help. But, I think I did what you said. In your first point : 'change it in `my.cnf`, I tried but like I said, i don't have any [mysqld], I put the same lines you wrote in the `my.cnf` and restart it : without success. Then, you said, put a file under `/ect/mysql/mariadb.conf.d`, like I said in my post, I edit `50-server.cnf`. Should I create a new file?? if yes, what should be the file name? Thanks – Bob Nov 14 '17 at 09:16
  • Assuming `...d` is a directory, create any filename you like (I use my initials + `.cnf`) and put those 2 lines in it. `50-server.cnf` should be fine. – Rick James Nov 14 '17 at 14:23
  • not working :(. when I put it in a new bob.cnf, I didn't have any change (I still have the error). This is very strange, isn't it?? – Bob Nov 14 '17 at 14:50
  • I updated the post with screen shot (see EDIT1 and EDIT2), as you can see, I have your max_allowed_packet, it's persistent, when I restart mysql, max_allowed_packet is the same... But I still have the issue... – Bob Nov 14 '17 at 14:58
  • I checked : https://mariadb.com/kb/en/library/server-system-variables/#max_allowed_packet, My version is : 10.1.23-MariaDB. When I read the documentation, '1048576 (1MB) <= MariaDB 10.1.6'. the number seems to be the limit in the error... – Bob Nov 14 '17 at 15:40
  • You did restart after adding `bob.cnf`? 1M is the _default_ value. The "Range" is much larger. Let's try something: also add `group_concat_max_len = 1111` to the file; restart; login; do `SHOW VARIABLES LIKE 'group%'. See if it is still the default of 1024, or if it changed to 1111. – Rick James Nov 14 '17 at 15:53
  • I'll try ! I take a look to : https://stackoverflow.com/questions/13759418/com-mysql-jdbc-packettoobigexception. It's can be my configuration?? – Bob Nov 14 '17 at 16:05
  • I still have 1024 – Bob Nov 14 '17 at 16:08
  • Okay....... the error was not explicit at all!!!! It was not an error with the max_allowed_packet, the post https://stackoverflow.com/questions/13759418/com-mysql-jdbc-packettoobigexception was right. My error was in the cayenne configuration exactly like @mathheadinclouds told... – Bob Nov 14 '17 at 16:19
  • I accepted your answer because 99% of time it's the solution. – Bob Nov 14 '17 at 16:43