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?

score 17 · Answer 1 · answered Dec 04 '14 at 20:44

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.

This was also my case. Was using a SSH tunnel and thought I had to write external address again... Thank you! — Typewar, Oct 27 '18 at 23:21
i have this error in sql developer for mysql connection via ssh tunnel. the mysql driver doesnt have an option to select connection type as for oracle, so unable to connect — Janaka R Rajapaksha, Jun 14 '21 at 06:14

score 2 · Answer 2 · answered Mar 23 '15 at 10:56

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

score 1 · Answer 3 · answered Apr 24 '15 at 03:24

1

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

answered Apr 24 '15 at 03:24

Farm

3,356
2
31
32

score 1 · Answer 4 · answered Jul 30 '15 at 12:54

1

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

answered Jul 30 '15 at 12:54

Raja

29
5

score 0 · Answer 5 · answered Dec 07 '12 at 09:04

0

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

Try sudo set global max_allowed_packet=32*1024*1024

answered Dec 07 '12 at 09:04

Daniel Rapaport

375
2
18

mysql> sudo set global max_allowed_packet=32*1024*1024; ERROR 1064 (42000): 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 'sudo set global max_allowed_packet=32*1024*1024' at line 1 – Martina Dec 07 '12 at 09:08
my mistake, your user problem is in mysql - root -p make sure you login as admin – Daniel Rapaport Dec 07 '12 at 09:30
I'm not the admin and someone gives me user and password that probably has not the privileges that I need. – Martina Dec 07 '12 at 09:32
so you need to ask him to grant you privileges or ask him to do it himself ;-) – Daniel Rapaport Dec 07 '12 at 09:36

score 0 · Answer 6 · answered Nov 03 '16 at 10:22

I meet this problem and solve it by change my mysql-connector version .May be my mysql-connector version in pom is too high.

Stack Print:

Thu Nov 03 18:24:03 CST 2016 WARN: Invalid value 'null' for server variable named 'auto_increment_increment', falling back to sane default of '1'.
Exception in thread "main" com.mysql.jdbc.PacketTooBigException: Packet for query is too large (44 > -1). You can change this value on the server by setting the max_allowed_packet' variable.
    at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:577)
    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:417)
    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3105)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2336)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2729)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962)
    at com.sparkproject.jdbc.JDBCHelper.main(JDBCHelper.java:382)

score 0 · Answer 7 · answered Jan 28 '18 at 15:12

If you are unable to stop your database, you can alternatively set the value of the max allowed packet parameter dynamically. To do so:

1. Log in as a root user.
2. You'll need to set the value as an integer, rather than '256M'. 256M is equivalent to 256*1024*1024, or 268435456.
3. mysql> SET GLOBAL max_allowed_packet=268435456;
4. To check that this has been applied:

    a. Relogin to your mysql client.

    b. mysql> SELECT @@max_allowed_packet;

Restart your Confluence instance.

You will still need to update your /etc/my.cnf file as described in the method above to make the change persistent.

score 0 · Answer 8 · answered Sep 23 '20 at 20:05

0

I had the same misleading error message.

I fixed it by changing my port number to 3306. Previously I had the port number set as 2083 because that's what I use on my web server.

answered Sep 23 '20 at 20:05

NewForOlly

35
5

score -1 · Answer 9 · edited May 23 '17 at 12:17

-1

well we don't know how big your database is, but your query is pretty specific.

as for max_allowed_packet, check this post:

how to check and set max_allowed_packet mysql variable

edited May 23 '17 at 12:17

Community

1
1

answered Dec 07 '12 at 08:32

Justin McDonald

2,156
16
19

com.mysql.jdbc.PacketTooBigException

9 Answers9

Linked

Related