I’m doing a ruby on rails project with MySQL that needs to store some images, and I thought to use the database. However MySQL’s default blob size is only 64K, so I wanted to use their LONGBLOB column instead. No big deal, and in fact they will automatically detect which of their myriad BLOB types to use based on the length of a BLOB column. Nice!
Now for a quick pop quiz. One of the following statements is a syntax error from MySQL. Can you guess which one?
A) CREATE TABLE test (id int, data blob(2147483647));
B) CREATE TABLE test (id int, data blob(2147483648));
C) Neither, they are almost exactly the same, how could one be a syntax error?
If you guessed ‘C’ congratulations, you are smarter than (at least one) MySQL developer. But you lose the quiz, because he correct answer is ‘B’, which generates the following error:
ERROR 1064 (42000): 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 ‘2147483648))’ at line 1
I deciphered from their cryptic documentation that LONGBLOB columns can have up 2^32 -1 bytes (almost 2 gigabytes), and apparently if you enter a number larger than that it’s invalid syntax. Thanks for wasting that hour of my life, MySQL.
And by the way, if you need to create a LONGBLOB column in a Ruby on Rails ActiveRecord migration, use this line:
add_column :table_name, :column_name, :binary, :limit => (2.gigabytes - 1) #mysql is terrible software