sah18
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)
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
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.
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 ;
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
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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):
Regards
`image` mediumblob NOT NULL default '',
Regards
I'm out of ideas for now, but I'm continuing to look for a possible solution.
Regards
Regards
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!
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
None of these were the solution (see my solution), but I appreciate the help!
Regards