com.mysql.jdbc.PacketTooBigException

Question

I have a big problem! when I do this:

 String query = "SELECT * FROM utente WHERE confermato=1 and Username='" + username
            + "' AND Password='" + password + "'";

        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection con = DriverManager.getConnection("ind_server/nome_db","user","password");

Eclipse gives me this error:

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

So, first: this query is really so big? -.- after: which commands do I have to wrtite in the ssh? It' the first time I due with this things! thanks in advance!

I don't have root privileges on the server!

I tried using this:

 mysql>set global max_allowed_packet=32*1024*1024;

but it gives me this error: ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

EDIT

Now I have modified the max_allowed_packet variable to 32MB. Stopped and restarted the server But eclipse gives me the same error!

What can I do?

Answer 1

I just had to deal with the same error message, thrown by DriverManager.getConnection(URL,username,password). I even got the same "magic numbers" (4739923 > 1048576); googling for those "magic numbers" will show results from people also getting the error thrown by some DriverManager.getConnection statement. In my case, the error message about packet size was COMPLETELY MISLEADING; I simply used a wrong URL, and the whole thing had to do NOTHING with packet size.

Maybe it will help to explain what was wrong with the URL I used - although this is kind of embarrassing (it's kind of a very stupid mistake.) Here it goes.

I had successfully connected to the remote server/database with some "database manager" software called SQLYog (kind of like phpMyAdmin). In order to connect, I had to give SQLYog 2 sets of things. First, URL(or IP adress), username, password, and port, so that my computer can connect with the server. Second, URL, port, MySQL username, MySQL password, so that the server machine can connect to the database (located on that very machine.) So there were 2 URLs and also 2 ports (and also 2 usernames and 2 passwords) one for client -> server and one for server -> database.

Now, deciding on which URL to of those to pick for the JAVA code in DriverManager.getConnection, I picked the wrong one, stupid me! Obviously, that has to be the one for the second step (server -> database), not the first step (client -> server); after all the servlet runs on the server, so the fact that the servlet is even running shows that step 1 is already taken care of, and step 2 remains to be done. Since the database was on the same machine as the machine the servlet was running on, that url was just "localhost"; or, more exactly, "jdbc:mysql://localhost:3306/myDatabaseName". And not "jdbc:mysql://domainNameOfServer.com:somePortNumber/myDatabaseName", which threw above error.

Stupid mistake and very stupid error message. I guess that fits.

Answer 2

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

SOLUTIONS:-
You can see it's current value in mysql like this:

SHOW VARIABLES LIKE 'max_allowed_packet'

You can try to change it like this, but it's unlikely this will work on shared hosting:

SET GLOBAL max_allowed_packet=16777216;
and restart mysql service..

Answer 3

Following worked for me

edit my.cnf file ( mine was in /etc/mysql )

Then modify the max_allowed_packet value I set it to max_allowed_packet=200M

Make sure you restart MySQL for change to take effect

Answer 4

Edit your my.cnf(ubunut) or my.ini(windows) file and set max_allowed_packet size.

max_allowed_packet=512M

Then restart your mysql server.It will work