5 years ago I had a Visual Basic program that could save images into a MySQL database and retrieve it later. It all worked. When COVID struck this project was canned. Now they want it back...
I've got an SQL backup file of the entire database that I restored to the new server. All information restored fine, except the image data, which was stored as Long Blob.
The funny thing is, if I try to open the original database image it does not work, but if I update the same record, with the original image I used 5 years back, it works!!!
None of the code have changed. After several days searching online, I think I've narrowed it down to what seems to be database character set and collation.
I used a hex editor to see the differences between the restored file data in the database and the newly saved data: Hex edit of two files
The top file is the newly uploaded image and the bottom is from the backup. You can clearly see both files are jpeg images from the "JFIF" in the first line. The newly saved file data starts with "FF D8 FF E0", recognized by Wikipedia as a Jpeg file signature. However, the restored data starts with "C3 BF C3 98 C3 BF C3 A0" which is not any recognized file signature Google can find.
I even managed to open the Backup SQL file to see what is supposed to be restored, and the image data starts with "FF D8 FF E0"!!! Why would SQL restore 4 byte "FF D8 FF E0" binary data as 8 bytes ("C3 BF C3 98 C3 BF C3 A0")?
I tried restoring the database with different character sets but without success.
Would it matter if the new Mysql installation is on a 64bit computer and the backup comes from a 32 bit PC?
Also my def PC have changed from win 7 32bit to a win 10 64bit PC.
The only viable solution currently is to re-upload all the pictures to the database, if I can still find them all, but there are thousands of product pictures that needs to be redone. A database solution would be quickest.
It is probably just an restore setting in MySQL but the answer has so far eluded me.
Any suggestions?
Edit: Here's the header on the backup file:
CREATE DATABASE IF NOT EXISTS `xxxx_planner` /*!40100
DEFAULT CHARACTER SET latin1 */;
USE `xxxx_planner`;
-- MySQL dump 10.13 Distrib 5.7.17, for Win64 (x86_64)
--
-- Host: xxx.xxx.xx.xx Database: xxxx_planner
-- ------------------------------------------------------
-- Server version 5.7.19
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
Edit 2: Heres the functions to store and retrieve that data:
Public Function updateMapData(sName As String, iID As Int16, iHeight As Int16, iWidth As Int16, iImg As Image)
Dim arrImage() As Byte
Dim Sql As String
Try
Dim mstream As New System.IO.MemoryStream()
iImg.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
arrImage = mstream.GetBuffer()
mstream.Close()
Sql = "UPDATE maps SET Map_Data = @studimg, Map_Name = @MNAME, Height = @HEIGHT, Width = @WIDTH WHERE id = @iID"
' Sql = "INSERT INTO maps(Map_Name, Store, Height, Width, Map_Data) VALUES (@MNAME, @STORE, @HEIGHT, @WIDTH, @studimg)"
Using cmd As New MySqlCommand
cmd.Connection = MySqlConn
cmd.CommandText = Sql
cmd.Parameters.AddWithValue("@MNAME", sName)
cmd.Parameters.AddWithValue("@iID", iID)
cmd.Parameters.AddWithValue("@HEIGHT", iHeight)
cmd.Parameters.AddWithValue("@WIDTH", iWidth)
cmd.Parameters.AddWithValue("@studimg", arrImage)
Dim r As Integer
r = cmd.ExecuteNonQuery()
If r > 0 Then
MsgBox("Record hass been Saved!")
Else
MsgBox("No record has been saved!")
End If
cmd.Parameters.Clear()
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Return 0
End Function
Public Function getMapData(iID As Integer) As Image
Dim sql As String
Dim dbPic As Image
sql = "SELECT Map_Data FROM maps WHERE id=" & iID
RefreshMySQLConnectionState()
Using cmd As New MySqlCommand(sql, MySqlConn)
Using sqlReader As MySqlDataReader = cmd.ExecuteReader()
sqlReader.Read()
Dim arrImage As Byte()
Try
arrImage = sqlReader("Map_Data")
'This just writes the data to file for troubleshooting
Dim filename As String
filename = iID & "-" & DateTime.Now.ToString("dd HHmmss") & ".blob"
File.WriteAllBytes(filename, arrImage)
If Not arrImage.Length = 0 Then
'' Dim mstream As New System.IO.MemoryStream(arrImage)
'' dbPic = Image.FromStream(mstream)
'Dim imageConverter As ImageConverter = New System.Drawing.ImageConverter()
' dbPic = TryCast(ImageConverter.ConvertFrom(arrImage), Image)
Dim mstream As New IO.MemoryStream(CType(arrImage, Byte()))
mstream.Position = 0 'added recently as part of posible solutions...
dbPic = System.Drawing.Image.FromStream(mstream) ' Error happens here...
Else
dbPic = Nothing
End If
Catch ex As Exception
dbPic = Nothing 'ERROR: Parameter is not valid.
End Try
End Using
End Using
Return dbPic
End Function
