Dec 14, 2012 at 3:10 PM
Edited Dec 17, 2012 at 4:19 AM
I have just ran a test by inserting 35 16MB(16224798 bytes) of files into a MEDIUMBLOB table and 215 of various length of files into the same table.
Then I try to backup the table with MySqlBackup.NET and it created a 1.3GB Dump File successfully. I try again by restoring the file, it was a success too.
(I did try using MySqlDump through MySQL WorkBench, it created a 1.4GB Dump File).
About 2 months ago, I ran a similar test to find out the limit of the MySqlBackup.NET. I started to catch System.OutOfMemory exception when the length of single SQL query approached some where around 18MB to 23MB.
My conclusion is, MySqlBackup.NET is able to handle a table with one MEDIUMBLOB or MEDIUMTEXT column, but not more than that. Of course the table could have other several non-BLOB columns. Non-BLOB columns will not create a length more than 1MB.
My computer has 4GB of RAM, Windows 7 32 bit,, but the program still raise the exception of System.OutOfMemory. I did read some articles some where around the internet which describe the problem, but I haven't done a research/study in deep on this matter.
I feel that, it seems to have some other steps to be taken to enable System.String holds more bytes.
However, one thing for sure is, if the length of SQL can be inserted into MySQL database by any .NET application, MySqlBackup.NET is able to do so. Conversely, if the SQL cannot be inserted (because of the length) by any .NET apps, MySqlBackup.NET
too can't. This is because MySqlBackup.NET is using System.String to hold the queries, just as other .NET apps.
Before the solution is found, if we really need to backup a large BLOB table, we might need to seek for new techniques other than writing it into SQL Dump File.