My sql: PacketTooBigException: Packet for query is too large (4,739,923 > 65,535)

Question

I am trying to connect to a database on my raspbery pi. I have mysql downloaded on the pi and have created a database on there already. I am trying to use driver manager to connect.

Here is my code for it:

public static void main(String[] args) {
    try {
        String host = "jdbc:mysql://scheduleit.duckdns.org:99/scheduleit";
        String uName = username;
        String uPass = password;
        Connection con = DriverManager.getConnection(host, uName, uPass);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

I have gone into the configuration files and changed the max_allowed_packet to 32M for [mysqldump]. I looked through the other configuration files and was unable to find another max_allowed_packet field to edit. Additionally, I have used the command: SET GLOBAL max_allowed_packet=32M; in mysql.

One thing I was curious about was whether the hostname was wrong. I ssh into the pi using port 99, but perhaps I use a different port when connecting with a DriverManager? The default port for mysql is 3306 and I tried that, but wasn't getting a connection.

Finally, I am using mariadb, which to my understanding is essentially mysql, since I log into using the mysql -u root -p command. Any help would be much appreciated.

Answer 1

Both the MySQL Server and the client (JDBC in this case) must increase their setting for max_allowed_packet. The max is effectively the lesser of the configuration of the client and the server.

For the server, you change this with SET GLOBAL max_allowed_packet. You should also change it in the server's my.cnf file because SET GLOBAL changes are lost when the server restarts.

For the client, as Michael commented above, you can specify this as a property in the JDBC URL. Except JDBC properties are spelled in camelCase.

String host = "jdbc:mysql://scheduleit.duckdns.org:99/scheduleit?maxAllowedPacket=33554432";

See https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html for more documentation on JDBC URL properties.