MySQL Packet Error on BLOB fields

[ADobkin]

I followed the instructions in the "How to handle file uploads" doc, and I created LONGBLOB fields for my files, mainly PDF and TIFF in this case. As soon as I tried to upload a "large" file (only 1 MB), there was a bunch of binary data dumped to the client browser, and the following error appeared in the web server logs:

PHP Fatal error: Failed to update due to sql error: Got a packet bigger than 'max_allowed_packet' bytesOn line 577 of file /var/www/html/dataface-0.7.1/Dataface/IO.php in function print

According to the MySQL manual:

The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns).

http://dev.mysql.com/doc/refman/5.0/en/ ... large.html

Could this value be set somewhere in Dataface, such as conf.ini or index.php, rather than changing it for the whole MySQL server? Either way, this information should probably be added to the "How to handle file uploads" doc for future reference....

Thanks,
Alan

[shannah]

http://forums.mysql.com/read.php?52,867 ... #msg-86786
This gives some tips on this.

In particular you could add:

Code: Select all

mysql_query("SET SESSION max_allowed_packet=1234567", df_db());


in your getPreferences() method.

-Steve

[shannah]

Another note. When I first created dataface I decided it was important to incorporate the ability to work with BLOB fields nicely. However most people advocate using the Container method for storing files. (i.e. you store the path of the file in the DB, but the actual file is stored on the file system).

Using blob fields currently has some security benefits, but the container method has performance benefits.

Just a note.

-Steve

[ADobkin]

Perfect, thanks.

[ADobkin]

Thanks for the note. The fact that things work so nicely with BLOB fields (and probably containers too) in Dataface is one of the things that really amazes me about it. I uploaded a TIFF, and it automatically displayed the preview image in both the edit and the details views. (BTW, this didn't work some other browsers however, probably due to lack of a TIFF viewer or MIME type problem, I'm not sure.) Anyway, these automatic functions are really a pleasure to work with.

I did give some thought to BLOB vs. container, especially because the statement you made in your how to document was exactly how I felt:

One thing that may scare you about storing files in the database is that they may seem less accessible than if they were on the file system.

However, I thought it would be easier to have everything in one place, rather than have to maintain a separate file system with accurate pointers in the database, and hope that nothing gets moved or deleted. (I assume deleting a record requires you to manually delete the file to avoid leaving an "orphan" and vice-versa.) Performance may be an issue though, so I may change my mind down the road, and hopefully it won't be too difficult to migrate if necessary.

Thanks,
Alan

[ADobkin]

FYI, this didn't work for me. I have the following in my conf/ApplicationDelegate.php file:

Code: Select all

function getPreferences(){
     mysql_query("SET SESSION max_allowed_packet=16777216", df_db());
}

It generates the following error on top of my pages and leaves out most of the page navigation widgets:

Code: Select all

Warning: array_merge() [function.array-merge]: Argument #2 is not an array in /var/www/html/dataface-0.7.1/Dataface/Application.php on line 712

Alan

[shannah]

Sorry, didn't realize that you weren't already using a getPreferences() method.

The getPreferences() method must return an array.

so just add

Code: Select all

return array();


to the end of it.

[ADobkin]

My bad, I should have realized that. Thanks, it works now.

[ADobkin]

I understand the performance benefits with the container method, but I initially didn't realize what you meant about the security benefits with blob fields in this statement:

[quote="shannah"]Using blob fields currently has some security benefits, but the container method has performance benefits.

[ADobkin]

The "How to handle file uploads" document specifies that BLOB fields be defined with the LONGBLOB data type. This type allows for files to be stored in the database with a maximum size of 4GB! I have smaller requirements and want to increase performance as much as possible. Will Dataface recognize and function the same way with regular BLOB (up to 64K) or MEDIUMBLOB (up to 16M) data types also?

[shannah]

Yes. It will recognize any blob field type.

-Steve