• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1172
  • Last Modified:

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

phpMyAdmin SQL Error when inserting a blob file (png in this example)
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.
0
sah18
Asked:
sah18
  • 6
  • 5
2 Solutions
 
R-ByterCommented:
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
0
 
sah18Author Commented:
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.
0
 
R-ByterCommented:
I understand now. Look at workaround here:

http://forums.overclockers.co.uk/showthread.php?t=17830439

Regards
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
sah18Author Commented:
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.

0
 
R-ByterCommented:
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
0
 
sah18Author Commented:
I receive an error when trying to do that:

 error
0
 
R-ByterCommented:
I'm out of ideas for now, but I'm continuing to look for a possible solution.

Regards
0
 
sah18Author Commented:
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!
0
 
R-ByterCommented:
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
0
 
mwiercinCommented:
It's not really strict mode related. Insert query fails as there is a warning thrown, what strict mode simply escalates to error.  Simply change the column definition to purely "mediumblob", with no  DEFAULTs (blob columns in MySQL can not have default values - this is the reason why CREATE TABLE fails in the second exmple, again warning + strict mode on ).

If you take strict mode off it will allow you to create that table, but will completely ignore the defaults you have set, issuing a warning and populating it with NULL or empty string.

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

BLOB and TEXT columns cannot have DEFAULT values.

 Example:
mysql> create table test_blob (id int not null default 0, content blob) engine = InnoDB; 
Query OK, 0 rows affected (0.15 sec)

mysql> insert into test_blob set id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> insert into test_blob set id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from test_blob;
+----+---------+
| id | content |
+----+---------+
|  1 | NULL    | 
|  2 | NULL    | 
+----+---------+
2 rows in set (0.00 sec)

mysql> 

Open in new window


Now if I've put NOT NULL there it would trigger a warning (and query would fail in strict mode):
mysql> alter table test_blob modify column content blob not null; 
Query OK, 2 rows affected, 2 warnings (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'content' at row 1
*************************** 2. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'content' at row 2
2 rows in set (0.00 sec)

mysql> insert into test_blob set id = 3; 
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'content' doesn't have a default value
1 row in set (0.00 sec)

mysql> select * from test_blob;
+----+---------+
| id | content |
+----+---------+
|  1 |         | 
|  2 |         | 
|  3 |         | 
+----+---------+
3 rows in set (0.00 sec)

Open in new window

0
 
sah18Author Commented:
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.
0
 
sah18Author Commented:
None of these were the solution (see my solution), but I appreciate the help!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now