Russian Version

Part 3. Other cases.

Chapter 8. Large amount of data.

There are cases when query is just symphtom of wrong behavior, but true reason is wrong settings.

One of the cases is too small max_allowed_packet for data sent. MySQL server variable max_allowed_packet defines maximum possible amount of data which MySQL server can receive or send. Amount of max_allowed_packet is specified in bytes.

Error usually looks like:

$mysql51 test <phpconf2009_1.sql
ERROR 1153 (08S01) at line 33: Got a packet bigger than 'max_allowed_packet' bytes

In this case everything is clear: error message has only meaning.

But sometimes you can see same error printed as:

$./my sql test <phpconf2009_1.sql
ERROR 1064 (42000) at line 33: 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 '00000000000000000000000000000000000000000000000000000000000000000000000000000000' at line 2

At the same time you are sure the query does not contain syntax error. If you see message about syntax error while you are sure query is correct check value of max_allowed_packet.

max_allowed_packet must be set for server and for client separately: these are different variables. Also pay attention max_allowed_packet used for the query, not only for data in the query. This is why you see this error for queries like SELECT REPEAT('a', 10000000);

mysql> \W
Show warnings enabled.
mysql> SELECT REPEAT('A', 10000000);
| REPEAT('A', 10000000) |
| NULL                  |
1 row in set, 1 warning (0.00 sec)

Warning (Code 1301): Result of repeat() was larger than max_allowed_packet (1048576) - truncated

Method #15: check value of max_allowed_packet and size of data and SQL queries if MySQL server returns syntax error for correct query.

Back Content Forward

Author 2010 Sveta Smirnova
COPYRIGHT © 2010 S.Smirnova and S. Lasunov