MySqlDump HexBlob bug with empty strings?
Posted: Mon 11 May 2015 18:39
Consider the following table structure:
Notice the Data column is marked as NOT NULL. However, it is possible to store an empty string in this column.
If you backup this table using MySqlDump with the HexBlob property set to True it will produce the following in the dump file:
As you can see, an empty string will be dumped as ,,
If you then attempt to restore this backup you will get an exception:
Setting the HexBlob property to False will produce the following dump:
This can be restored successfully, as the empty string was dumped as ''
Code: Select all
DROP TABLE IF EXISTS table_name;
CREATE TABLE `table_name` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Data` longblob NOT NULL,
`MoreStuff` varchar(100) NOT NULL,
PRIMARY KEY (`ID`),
KEY `Name_idx` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;If you backup this table using MySqlDump with the HexBlob property set to True it will produce the following in the dump file:
Code: Select all
INSERT INTO table_name VALUES(1,'some_info', ,'more_text');If you then attempt to restore this backup you will get an exception:
Code: Select all
Devart.Data.MySql.MySqlException - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,'some_info', ,'more_text'' at line 1Code: Select all
INSERT INTO table_name VALUES(1,'some_info', '','more_text');