0

I'm trying to store binary data as a blob in a MySQL table. I'm aware that my server is configured with a max_allowed_packet size of 1M so I'm chunking the data into blobs of 1M. However I still see errors like this:

Exception: Packet for query is too large (1851203 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

What appears to be happening is the PreparedStatement is escaping the data in my chunked blob.

e.g. blob 0000 would get sent as \0\0\0\0.

So in the worst case data may be doubling in size which causes the error.

My only thought is to chunk the data into half the max_allowed_packet size to leave space for potential escape characters.

Is there a better way? Should I pre-escape my data before I chunk it?

Leif Gruenwoldt
  • 13,561
  • 5
  • 60
  • 64
  • You are using a `setBlob` with an `InputStream`? – Joop Eggen Jul 03 '15 at 15:39
  • @JoopEggen I am currently using `setBlob(int parameterIndex, Blob x)` – Leif Gruenwoldt Jul 03 '15 at 15:52
  • The data example (0000) would suggest first compressing, but I doubt you can work with that. Blobs are a PITA; think for instance about database backups (and restores). – Joop Eggen Jul 03 '15 at 16:06
  • I just tried using `setBlob(int parameterIndex, InputStream x)` and got the same error. – Leif Gruenwoldt Jul 03 '15 at 16:13
  • Almost to be expected when the java driver sends full SQL to the database. Nowadays also database engines may keep a _real_ native prepared statement. Most solid seems to store the blobs on the file system as blob-record-id = filename. A pity. – Joop Eggen Jul 03 '15 at 16:23

1 Answers1

0

I needed to do two things:

  1. Use PreparedStatement#setBlob(int parameterIndex, InputStream x) instead of setBlob(int parameterIndex, Blob x), as @JoopEggen suggested trying.
  2. And set the property useServerPrepStmts=true on the Connection, as seen over in answer https://stackoverflow.com/a/4846674/52176 (other properties were not required in my case).
Community
  • 1
  • 1
Leif Gruenwoldt
  • 13,561
  • 5
  • 60
  • 64