You get a bonus - 1 coin for daily activity. Now you have 1 coin

Other cases of SQL errors 8. Large data volumes. Got a packet bigger than 'max_allowed_packet' bytes

Lecture



It often happens that a request is only an indirect cause of incorrect behavior, and the true reason is settings.

One option is a small max_allowed_packet for transmitted data. The MySQL server variable max_allowed_packet determines the maximum amount of data that the MySQL server can receive or send. The max_allowed_packet size is in bytes.

The error is usually reproduced as follows:

$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: the error message is unique.

But sometimes it is reproduced like this:

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

While you are convinced that the request does not contain a syntax error. If you see a syntax error message while the query is valid, check the value of max_allowed_packet

max_allowed_packet must be set for both server and client - these are different values. Also note that max_allowed_packet is the value for the entire query, including SQL, not just for the input data. Hence the errors for queries like SELECT REPEAT ('a', 10,000,000);

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

Reception number 15: check the value of max_allowed_packet and the size of the transmitted data if the server generates an error for a syntactically correct request.


Comments


To leave a comment
If you have any suggestion, idea, thanks or comment, feel free to write. We really value feedback and are glad to hear your opinion.
To reply

Error detection methods in SQL application

Terms: Error detection methods in SQL application