php - mysql INSERT fails but no error thrown: max_allowed_packet: settings show conflicting results
I successfully receive data into server client account via _POST but I can't insert data into row table that holds longblob types when row data is above ~25MB. I cannot see any errors thrown by the the php prepared statements/file, php file closes gracefully. I'm checking whether my max_allowed_packet is correctly configured.
I don't have server root privileges and access, I can't see my.cnf, and I'm talking to my host to ensure both client and server max_allowed_packet are set at 256M.
php file insert section:
$itemAttributes=$_POST['itemAttributes'];
$itemName=$_POST['itemName'];
if(!($stmt = $conn->prepare("INSERT INTO $itemTable (`itemName`, `itemAttributes`) VALUES (?, ?)")))
echo "<br/>Failed to Prepare";
else
echo "<br/>Prepare success.";
$stmt->execute([$itemName,$itemAttributes]);
echo " Success executing";
$conn->connection=null;
if($conn->connection==null)
echo "<br />Connection Closed.......";
These are the checks I am doing, am I missing any to make sure the max_allowed_packet won't be overriden elsewhere and is setup in all places correctly?
Client setting check: All these three check give me 256MB max_allowed_packet:
mysql SHOW VARIABLES LIKE 'max_allowed_packet'
mysql SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'
mysql SHOW SESSION VARIABLES LIKE 'max_allowed_packet'
However, I successfully login to mysql command prompt (mysql --user userName --password databaseName) to check max_allowed_packet but it shows NULL, what does that mean?
mysql> select @max_allowed_packet;
+
Server setting check: how can I check server 'max_allowed_packet' for GLOBAL and SESSION? I try the above replacing "mysql" for "mysqld" but I get no result and warnings I can't relate to the size of 'max_allowed_packet':
{-code-4}
mysqldump check: Finally, I try to read max_allowed_packet for mysqldump with the three commands above (i.e. mysqldump SHOW VARIABLES LIKE 'max_allowed_packet' ) substituting "mysql" for "mysqldump" but I get access denied. As an alternative I then successfully login to mysqldump to read max_allowed_packet (mysqldump --user userName --password databaseName) and I get a lot of garbage scrolling through the screen so I can't get this value.
Answer
Answer
Answer
Answer
Solution:
As it turns out, this has nothing to with MySQL directly.
This is a PHP error message, which tells you that your script has excseeded the 128M memory limit.
You can increase the limit either in your config file or in your script with
You can use
-1as value to disable the limit completely.However - you should though avoid copying large amounts of data.
The following case is clear:
You copy 32M of data into a new variable. And your script is at least at 64M now.
The
execute()method with parameters is more tricky, and I'm not sure if the following is exactly true: You pass a new array as parameterThis array first needs to be created in memory, before it is passed to
execute(). That again consumes at least 32M more. Then due to internal implementation (which I don't know) every array element is passed to something likebindValue(), which will again copy all the data. At this point your script is already at 128M limit (32*4).So you should do something like the following:
Remove these lines:
Prepare the statement:
Bind the parameters with bindParam:
bindParam()is using&in&$variableindicates, that no copy will be done, but a reference to the value is passed instead.Note, that I am usually not a friend of call by reference, and avoid it when not dealing with resource critical code.
execute([..])is fine in most cases.If you want to see, how much memory has been allocated, you can use memory_get_peak_usage() somewhere at the end of your script.