Solved

MySQL on Windows - Unable to insert a blob file

Posted on 2011-03-03
12
1,064 Views
Last Modified: 2012-05-11
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
Comment
Question by:sah18
  • 6
  • 5
12 Comments
 
LVL 14

Expert Comment

by:R-Byter
ID: 35026466
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
 
LVL 9

Author Comment

by:sah18
ID: 35026774
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
 
LVL 14

Accepted Solution

by:
R-Byter earned 250 total points
ID: 35026847
I understand now. Look at workaround here:

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

Regards
0
 
LVL 9

Author Comment

by:sah18
ID: 35026979
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
 
LVL 14

Expert Comment

by:R-Byter
ID: 35027091
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
 
LVL 9

Author Comment

by:sah18
ID: 35027316
I receive an error when trying to do that:

 error
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 14

Expert Comment

by:R-Byter
ID: 35027504
I'm out of ideas for now, but I'm continuing to look for a possible solution.

Regards
0
 
LVL 9

Author Comment

by:sah18
ID: 35027589
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
 
LVL 14

Expert Comment

by:R-Byter
ID: 35027704
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
 
LVL 3

Assisted Solution

by:mwiercin
mwiercin earned 250 total points
ID: 35030326
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
 
LVL 9

Author Comment

by:sah18
ID: 35033211
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
 
LVL 9

Author Closing Comment

by:sah18
ID: 35033227
None of these were the solution (see my solution), but I appreciate the help!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
A quick step-by-step overview of installing and configuring Carbonite Server Backup.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now