0

My web application is using Hibernate as ORM to connect to MySQL version Community Server (GPL) 5.6.24. Recently, I usually get the exception from the server like this:

2016-04-06 19:44:25 WARN  SqlExceptionHelper:144 - SQL Error: 0, SQLState: S1000
2016-04-06 19:44:25 ERROR SqlExceptionHelper:146 - Packet for query is too large (2278 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.
2016-04-06 19:46:01 WARN  SqlExceptionHelper:144 - SQL Error: 0, SQLState: S1000
2016-04-06 19:46:01 ERROR SqlExceptionHelper:146 - Packet for query is too large (2257 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.

After the exception rise, instead of returning the query result, the server return this message:

org.hibernate.exception.GenericJDBCException: could not extract ResultSet

I make a lot of research about this problem. I try to increase max_allowed_packet parameter of mysql configuration to 1GB like this guide (link) but it still didn't work anymore.

So how can I detect exactly which is query that lead to exception ?

Updated: More information about my web application:

  • Recently, it's not a large application. The database just saves normal text data, not image information, not blob text data !
  • It's a crawler system. It receives request from user, get data from another side and return result back to user.
  • It saves user information, order from users, v.v... Each table takes less than 10,000 rows !
Community
  • 1
  • 1
Leo Le
  • 815
  • 3
  • 13
  • 33
  • Just how huge are these queries you're sending in? 1GB is usually more than large enough. Are you sure that the configuration change was applied? – tadman Apr 06 '16 at 16:48
  • I confirmed the configuration change by running "SHOW VARIABLES WHERE variable_name = 'max_allowed_packet';". How can I measure "the huge" of my queries" ? Maybe many user sent a lot of requests make my db die ? 1Gb actually is a lot – Leo Le Apr 06 '16 at 16:51
  • @RiggsFolly I make the change in /etc/my.cnf file – Leo Le Apr 06 '16 at 16:56
  • Based on this statement in the [MYSQL Manual](https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html) _A communication packet is a single SQL statement sent to the MySQL server, **a single row that is sent to the client**, or a binary log event sent from a master replication server to a slave._ I would look for a query that is returning a **HUGH** image or something like that from a BLOB column. Or possibly an INSERT/UPDATE of a hugh BLOB – RiggsFolly Apr 06 '16 at 16:57
  • @RiggsFolly I'd forgotten about the implications of `LONGBLOB` which could be the culprit here. Anyone who stores >1GB records in their database is bound to earn the scorn of their DBA though. – tadman Apr 06 '16 at 17:02
  • @tadman I think the word _scorn_ is a little generous. I would have used a few different word myself. hahhhahha – RiggsFolly Apr 06 '16 at 17:04
  • The database doesn't save image data, doesn't save any BLOB data type in any table. Have NO table has over 1Gb data records – Leo Le Apr 06 '16 at 17:05
  • I would add that logging to your database logic, so if it encounters an error, it logs the sql statement causing it. Mysql does not have an sql error log. The alternative is to set up a proxy which will log every statement and mysql's response to it. – Shadow Apr 06 '16 at 17:05
  • you might need to turn on the slow query log, or the general log to see which query is causing you issues then – RiggsFolly Apr 06 '16 at 17:07
  • Anybody know exactly what these numbers mean `(2257 > 1024)` in the error message, because if `1024` means 1GIG then `2257` means MySQL is seeing a 2.3Gig packet. Or does it mean its lost the new `max_allowed_packet` value? – RiggsFolly Apr 06 '16 at 17:08
  • @RiggsFolly could you help me some guide about track query log in mysql, please ? I really need some hint, actually I'm not an expert MySQL guy ! – Leo Le Apr 06 '16 at 17:10
  • 1
    Neither am I, but I can read a manual and run a Google query: Turning on the [General Log](http://dev.mysql.com/doc/refman/5.7/en/query-log.html) Its all in the manual – RiggsFolly Apr 06 '16 at 17:12
  • 1
    Have you checked out the mysql documentation? It offers quite detailed guidance on enabling the general query log: http://dev.mysql.com/doc/refman/5.7/en/query-log.html – Shadow Apr 06 '16 at 17:13
  • Thanks all! I will check it and inform result later – Leo Le Apr 06 '16 at 17:19

0 Answers0