Inserting Images Into MySQL

kevinkcw
kevinkcw used Ask the Experts™
on
How would one adapt the code for the example at:
http://www.stardeveloper.com/articles/display.html?article=2001033101&page=1

...for use with MySQL. My attempts thus far have been unsuccessful. The primary problem I have had is that using the .AppendChunk method results in the error: "Lost connection to MySQL server during query"

I have made the appropriate changes to the cursor location.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
http://www.phpbuilder.com/columns/florian19991014.php3?print_mode=1

You don't mention what language you are trying to use, but that provides examples and troubleshooting for PHP.

<snip>

How to handle files larger than 1 MB:

If you want to upload and store files bigger than 1 MB, you have make several changes to the scripts and your php/sql setup, as it is caused by default limitations of the programs. Do the following to be able to store files as large as 24 Megabyte:


Edit the store.php3 script. Change the MAX_FILE_SIZE value (in the form) to 24000000.
Remove the filesize limitation from your php installation. This is set either in your php.ini or in your apache config files. By default, php3 only allowes files smaller than 2 MB. You have to change the max_filesize variable to: max_filesize = 24000000
Remove the mysql packet size limitation. By default, mysql only accepts packets that are smaller than 1 MB.
You have to restart your database with some parameters, which will remove the limitations.

If you still get errors:

This could be a timeout problem. If you upload large files via a slow connection, php's default timeout of 30 seconds might kill your process. Change the max_execution:time variable in your php.ini to: max_execution_time=-1

-Keisha

Author

Commented:
Keisha,

Thank you very much. Here's some additional information: The application is in ASP, and as far as I know ASP  doesn't have problems with file sizes. The problem isn't caused by timeout. I know this because the error returns within 15 seconds. I have set the max_allowed_packet on the server with "set max_allowed_packet=16000000;" and I still encounter the problem, though once I did receive an error about the packet size, but I haven't been able to duplicate it. The article addressed starting the database with some particular options, but didn't elaborate very much at all. I'm not familiar with starting MySQL. I just put it on the server and it works. I think it starts with the winmysqladmin-nt.exe that is called at startup. Do you know more about what I should do here?

Commented:
Ok, couple more questions/comments:

Have you been able to insert any pictures at all? Try with a very small iamge and let me know what happens.

mySQL has a limit on table size. Not normally a problem, but with an image database, it could become one. How large are the files that you are trying to put in, and how many are already in(if any)?

http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Table_size

In the database that you are trying to insert the image into, what is the type of the field? At the command line, "describe" the table and it will tell you.

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Column_types

Different types of fields allow different amounts of data. You may be able to set max packets high, but if the field isn't large enough, or is the wrong type, it will throw error messages.

With it being ASP, I'm assuming that you are running Microsoft IIS server? I'm not familar with that at all, but you might check to see if the server itself doesn't have a size limitation as well.

-Keisha
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Author

Commented:
Keisha,

Thank you again, for considering this question. Yes, I have been able to upload images using the .AppendChunk method, up to a file size of about 1 MB, and have uploaded around 8 images of various sizes below 1 MB. Using MS Access as the database on the same machine I have successfully uploaded files of up to 15MB in size. The field in the MySQL is of type MEDIUMBLOB, which should be good up to 16 MB. The table size is certainly not an issue yet, but it is interesting to note that there are limits. I'll investigate the possibility that IIS is interfering with the upload, which may in fact be the problem when I get "Internal Server Error" for all I know, but I assume that it's not a hard problem since I have been able to upload 15 MB files before (into Access,) from a fast connection.

I can see it might be useful for a more complete description of the problem and issues:

I was able to follow the example on stardeveloper.com (http://www.stardeveloper.com/articles/display.html?article=2001033101&page=1) for uploading files and inserting them into a database as described without trouble.

The troubles start when I try to adapt the example for use with MySQL instead of MS Access.

I can't for the life of me get it to work consistently. I have changed the cursor location to client, I have changed the max_allowed_packed to 16000000, I have swapped out the table names that you can use with Access with the complete select statements that MySQL requires. Still, when I try to upload a file larger than 1 MB I get the following error: "Microsoft OLE DB Provider for ODBC Drivers error '80040e21' [MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]Lost connection to MySQL server during query." Sometimes the error is: "Microsoft OLE DB Provider for ODBC Drivers error '80040e21' [MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]Got a packet bigger than 'max_allowed_packet'" I'm sure the file is less than 2MB.

With this code:

' Checking to make sure if file was uploaded
If fileSize > 0 Then

set rsImage = Server.CreateObject("ADODB.Recordset")
rsImage.ActiveConnection = MM_connShooters_STRING
rsImage.Source = "select * from web_lab.Images limit 0"
rsImage.CursorType = adOpenKeyset
rsImage.CursorLocation = adUseClient
rsImage.LockType = adLockPessimistic
rsImage.Open()
rsImage_numRows = 0

' Adding data
rsImage.AddNew
rsImage("userID") = userID
rsImage("imageType") = contentType
rsImage("imageSize") = fileSize
rsImage("imageName") = fileName
rsImage("imageCaption") = caption
ERROR-->rsImage.Update

rsImage.Close
Set rsImage = Nothing

Response.Write "<font color=""green"">File was successfully uploaded..."
Response.Write "</font>"
Else
Response.Write "<font color=""brown"">No file was selected for uploading"
Response.Write "...</font>"
End If

When I adapt the code to use the Stream object instead of .AppendChunk, I get this error: "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." The code for the insert is as follows:

' Checking to make sure if file was uploaded
If fileSize > 0 Then

set adoImageStream = Server.CreateObject("ADODB.Stream")
adoImageStream.Type = adTypeBinary

set rsImage = Server.CreateObject("ADODB.Recordset")
rsImage.ActiveConnection = MM_connShooters_STRING
rsImage.Source = "select * from web_lab.Images limit 0"
rsImage.CursorType = adOpenKeyset
rsImage.CursorLocation = adUseClient
rsImage.LockType = adLockPessimistic
rsImage.Open()
rsImage_numRows = 0

' Adding data
rsImage.AddNew
adoImageStream.Open
ERROR--> adoImageStream.Write fileData
rsImage("userID") = userID
rsImage("imageType") = contentType
rsImage("imageSize") = fileSize
rsImage("imageName") = fileName
rsImage("imageCaption") = caption
rsImage("imageData") = adoImageStream.read
rsImage.Update

adoImageStream.Close
rsImage.Close
Set rsImage = Nothing

Response.Write "<font color=""green"">File was successfully uploaded..."
Response.Write "</font>"
Else
Response.Write "<font color=""brown"">No file was selected for uploading"
Response.Write "...</font>"
End If

I'm almost sure this error arises because the class object used for the page (found on the site with the tutorial, called 'loader.asp') changes the binary form data to a string, so the stream of type adTypeBinary doesn't accept the data, but when I change the stream type to text then I get an error because the field is expecting binary data. I haven't found an efficient enough way to change the data back to binary or to alter the class so that it doesn't turn the file data into a string in the first place. The relevant code from the class is this subroutine, which gets called when the object is initialized, to parse the form data:

Private Sub getData(rawData)
Dim separator
separator = MidB(rawData, 1, InstrB(1, rawData, ChrB(13)) - 1)

Dim lenSeparator
lenSeparator = LenB(separator)

Dim currentPos
currentPos = 1
Dim inStrByte
inStrByte = 1
Dim value, mValue
Dim tempValue
tempValue = ""

While inStrByte > 0
inStrByte = InStrB(currentPos, rawData, separator)
mValue = inStrByte - currentPos

If mValue > 1 Then
value = MidB(rawData, currentPos, mValue)

Dim begPos, endPos, midValue, nValue
Dim intDict
Set intDict = Server.CreateObject("Scripting.Dictionary")

begPos = 1 + InStrB(1, value, ChrB(34))
endPos = InStrB(begPos + 1, value, ChrB(34))
nValue = endPos

Dim nameN
nameN = MidB(value, begPos, endPos - begPos)

Dim nameValue, isValid
isValid = True

If InStrB(1, value, stringToByte("Content-Type")) > 1 Then

begPos = 1 + InStrB(endPos + 1, value, ChrB(34))
endPos = InStrB(begPos + 1, value, ChrB(34))

If endPos = 0 Then
endPos = begPos + 1
isValid = False
End If

midValue = MidB(value, begPos, endPos - begPos)
intDict.Add "FileName", trim(byteToString(midValue))

begPos = 14 + InStrB(endPos + 1, value, stringToByte("Content-Type:"))
endPos = InStrB(begPos, value, ChrB(13))

midValue = MidB(value, begPos, endPos - begPos)
intDict.Add "ContentType", trim(byteToString(midValue))

begPos = endPos + 4
endPos = LenB(value)

nameValue = MidB(value, begPos, ((endPos - begPos) - 1))
Else
nameValue = trim(byteToString(MidB(value, nValue + 5)))
End If

If isValid = True Then

intDict.Add "Value", nameValue
intDict.Add "Name", nameN

dict.Add byteToString(nameN), intDict
End If
End If

currentPos = lenSeparator + inStrByte
Wend
End Sub

An effective solution to my problem lies either in what I'm doing wrong with MySQL (obviously I'm doing something wrong, else I wouldn't be getting errors using the AppendChunk method) or with converting the data into a binary type so it can be written to the adTypeBinary type Stream object.
Commented:
I think we're both looking too hard for the problem.

If 1 MB seems to be your cutoff point, that tells me part of the problem. 1MB is the default cutoff point in mySQL as determined by the max_allowed_packets variable.

You do say you changed the max_allowed_packets to 16MB, but did you restart the process afterwards? In other words, you changed it, but did you tell the server that you did?

The max_allowed_packets variable also exists on both client and server, you have to change it in both places. (If you are using a client, that is. You don't mention one, but just in case.)

<snip>
When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. With some clients, you may also get Lost connection to MySQL server during query error if the communication packet is too big.
 
You can use the option file to set max_allowed_packet to a larger size in mysqld. For example, if you are expecting to store the full length of a MEDIUMBLOB into a table, you'll need to start the server with the set-variable=max_allowed_packet=16M option.
</snip>
 
http://www.mysql.com/doc/en/Packet_too_large.html

Hope this helps, if not, get back to me and we'll try finding other things.

-Keisha

Author

Commented:
Keisha,

Thanks! Spot on with the answer and thanks for sticking with it. The server did in fact need to be restarted and changes to the .ini file needed to be made. It turned out to be an education in MySQL server administration that I probably should have had prior to dinking with it in the first place.

To be specific, since I'm running MySQL server on a Windows 2000 Server machine the service is started automaticly at startup, through the winmysqladmin-nt.exe program. That opens the GUI client, but throws it in the task bar, minimized. Click on that icon and select Show Me, and the GUI opens. Click on the "my.ini setup" tab. In the [mysqld] section add the line "set-variable=max_allowed_packet=16M". Then all that's left is to restart the service by right clicking in the masthead section of the GUI and selecting Win NT > Stop This Service. After the stop light changes to red, right click on the masthead of the GUI again and select Win NT > Start This Service.

To confirm that the changes are in effect, open a command prompt and navigate to the install folder (usually C:\mysql\bin\) and open the mysql command line shell. From there type SHOW VARIABLES; and look for the "max_allowed_packet" entry. If this is 16776192 then Bob's Your Uncle and you can get on with inserting large binary data into your database.

Thanks again,
-Kevin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial