Steve Chambers Steve Chambers - 1 month ago 9
MySQL Question

How to insert compressed data using PDO?

I'm trying to insert a large serialized object into a MySQL database using PDO. Attempting to insert directly gives:

PDOStatement::execute() [pdostatement.execute]: SQLSTATE[08S01]: Communication link failure: 1153 Got a packet bigger than 'max_packet_allowed' bytes


There seem to be a few possible ways to tackle this but my first tack is
gzcompress
, bringing it down from 2383731 to 155955 bytes (using compression level 6). But am now struggling to insert the result for a different reason:

PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'lob) VALUES ('
[some binary data spued out here]
'


Here's the basic gist of the code:

$value = gzcompress(serialize($lob));
$stmt = $conn->prepare("INSERT INTO saved (lob) VALUES (:value)");
$stmt->bindParam(':value', $value, PDO::PARAM_LOB);
$stmt->execute();


The examples in the documentation all seem to be using file streams rather than binary data stored in a string so am not sure this is valid. Could anyone advise?

Answer

The error sounds like you need to add backticks around the field name:

INSERT INTO saved (`lob`) VALUES (:value)
Comments