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
Error detection methods in SQL application
Terms: Error detection methods in SQL application