Link to home
Start Free TrialLog in
Avatar of sah18
sah18Flag for United States of America

asked on

MySQL on Windows - Unable to insert a blob file

I have MySQL version 5.1.41 installed on Windows Server 2003.
I am unable to insert a blob file (any file) into MySQL.

For example, I created a database (named test) with one table (named testtable) and two fields:
no - an id field
image - a mediumblob field
(see creation sql code attached)

CREATE TABLE `testtable` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `image` mediumblob NOT NULL,
  PRIMARY KEY (`no`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

Open in new window


In phpMyAdmin, on the Insert tab, I try to insert a single record.  I leave "no" blank because it is an auto-increment.  I click on the "Choose File" button and select any file at all.  I've been sticking with small files, in case size is an issue, but the problem seems to happen across all files & all file types that I've tested.  So, for example, I've selected a very small png file.  It shows the filename on the screen.  Then I click the "Go" button to actually do the insert.

I get this error (also see screenshot)

Error
SQL query:

INSERT INTO  `test`.`testtable` (

`no`
)
VALUES (
NULL
)
MySQL said:

#1364 - Field 'image' doesn't have a default value

User generated image
As you can see, it isn't uploading the file at all, but has placed a NULL value instead of where the binary data should appear.  

I'm not sure what might cause this issue.  Any suggestions would be appreciated.
Avatar of R-Byter
R-Byter
Flag of Serbia image

You declared 'image' to be NOT NULL, yet your query tries to do that. Remove NOT NULL form definition of 'image' and try again.

Regards
Avatar of sah18

ASKER

I may not have been clear in my question -- yes, the query shows NULL, but it shouldn't!  It should contain my uploaded file (the blob insert I am trying to accomplish).  That is the crux of the issue -- for some reason, it shows NULL instead of showing the binary data.

I tried the same exact thing on a XAMPP local install, and had no issues with a blob file insert.

btw, if I do change the field to allow nulls, all I get is a NULL value for the image field, but I still am failing to get the file itself inserted.

Let me know if I'm not explaining this well.
ASKER CERTIFIED SOLUTION
Avatar of R-Byter
R-Byter
Flag of Serbia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sah18

ASKER

I'm getting the same results.  I set the field to allow nulls, inserted a new record with null for image, then tried to upload a file after the fact, and still nothing uploads.

Also, I see this person's initial question, but I don't see any responses to their thread on this -- no solutions given.

SInce I never had that kind of problem, I woudl suggest you to try with this (as suggested on some other website):

`image` mediumblob NOT NULL default '',

Open in new window


Regards
Avatar of sah18

ASKER

I receive an error when trying to do that:

 User generated image
I'm out of ideas for now, but I'm continuing to look for a possible solution.

Regards
Avatar of sah18

ASKER

I appreciate your help very much.  Yes, if you come across anything else, definitely let me know.

Or, if anyone else has any thoughts, please chime in!
Could be MySQL strict mode related problem:

http://www.contao.org/board/topic/222.html

Specifically this post on that page:

http://www.contao.org/board/message/3058.html

If its turned on, try to turn it off.

http://lynnepope.net/turn-off-mysql-strict-mode

Regards
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sah18

ASKER

I finally found the issue with this.  It was actually related to php -- the temp folder that php was using for file uploads did not have the proper permissions set on it, therefore when using phpMyAdmin to insert a blob file, the file was always interpreted as NULL, since it never made it into the temp folder to begin with.  I do have MySQL running in strict mode, but as mentioned above, this was really not the issue.

Even though I solved this issue myself, I'm awarding points for all the efforts in helping to think through the possible issues.
Much appreciated.
Avatar of sah18

ASKER

None of these were the solution (see my solution), but I appreciate the help!