Regarding larger blob files

Dec 14, 2012 at 8:27 AM

I've edited the code of MySqlBackup.NET so that even blob files can be exported. Regardless of the size of them.

I did this by first checking in the export function itself if the rdr data values are byte arrays.

And if they are I would count up the length of each byte array for each record.

If the total exceeds 5 mb.

If it does I get the standard valuestring but replace every byte array in that value string with an unique identifier.

I then split the valuestring at the next available identifier. Writing to file the left part.

Then I process the byte array from the identifier. Grabbing a piece of 5mb, processing it and directly writing it to file as well. Before doing it again until the file is finished.

I then repeat the process for each file.

 

This has enabled me to easily make a backup of a database with large blob files.

 

I'm still working on importing a large backup. 

If you are interested in the code just reply

Coordinator
Dec 14, 2012 at 11:06 AM
Edited Dec 14, 2012 at 11:08 AM

Is this what you are trying to say? (I'm trying to understand)

1st, write this line into the file:

INSERT INTO `tablename`(`blob`) VALUES

We assume that the table has 1 column.

then, retrieve the 1st 5MB of byte[], convert it into hexadecimal string and flush(write) it into file:

(0x00....<write 1st 5MB>..

repeat the action until the file is finished loading...

(0x00....<write 1st 5MB>..<write 2nd 5MB>..<write 3rd 5MB>..);
Dec 14, 2012 at 12:03 PM
Yeah that's close.
For example.
Step1: <-- only happens if insertstatementheader is not null
INSERT INTO `versies` (`Id`,`Applicatie_id`,`Major`,`Minor`,`Revisie`,`Datum`,`RevisieText`,`SQLBestand`,`UpdateZip`) VALUES
Step2: <--- valuestring till the first seperator
('9655cb8a-58f6-497f-836b-a12700933ae8','59dd1322-1cc9-4afd-827a-a0d100bbaef0',1,2,3,'2012-12-14 00:00:00','test',NULL,
Step3: <--- convert and write the file in 5mb chunks
0x504B0304140000000800886283411549C9EF08000000060000001100>5mb worth
0000436F6E66696742657374616E642E747874F3C94F2F8ED10200504B>5mb worth
01021F00140000000800886283411549C9EF08000000060000 >rest mb worth
Step6: <--- leftover valuestring.
)
Step7 <--- this happens at the end if either sb.length is bigger then 0 or there were files with byte arrays in them
;
This proces only happens for records that have more then 5mb worth of byte arrays.
The problem I have with loading the sql file is that I can't really figure out if it is even possible to insert a blob file in smaller chunks.
Coordinator
Dec 14, 2012 at 3:10 PM
Edited Dec 17, 2012 at 4:19 AM

I see.

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.

Coordinator
Dec 15, 2012 at 8:16 AM

The length of a MEDIUMBLOB is : 16777215. The largest file size that used in the above test is : 16224798.

 

 

Dec 17, 2012 at 12:59 PM

I've now managed to make backups with records that have files as large as I want them to be.

 

The problem is the restore. I can only restore records that are no bigger then 50mb a piece.

Coordinator
Dec 17, 2012 at 1:13 PM

Hi, how do you do that to restore up to 50MB?

Dec 17, 2012 at 1:34 PM
adriancs wrote:

Hi, how do you do that to restore up to 50MB?

Long story short:

Stringbuiler uses way more memory then just string.

I also each time I write a record with a blob value in it I prepend it's own Insert into before it.

And when I restore the database I go trough the sql file line by line until I hit a ;

I then write the sql lines to the commandtext.

Coordinator
Dec 17, 2012 at 1:51 PM
Edited Dec 17, 2012 at 1:53 PM

So... a sample result of backing up a large blob would be something this?

INSERT INTO `tableblob`(`id`,`filename`,`file`) VALUES
('9655cb8a-58f6-497f-836b-a12700933ae8','myfile.zip',
0x504B03041400000008008.........000000110
0000436F6E6669674265737.........4616E642E
01021F00140000000800886.........283411549
E747874F3C94F2F8E747874.........F3C94F2F8
00080088628340008008862834);
INSERT INTO `tableblob`(`id`,`filename`,`file`) VALUES
('9655cb8a-58f6-497f-836b-a12700933ae8','myfile2.zip',
0x504B03041400000008008.........000000110
0000436F6E6669674265737.........4616E642E
01021F00140000000800886.........283411549
E747874F3C94F2F8E747874.........F3C94F2F8
00080088628340008008862834);

the hexadecimal string of the blob data is split into multiple lines?

In the above reply, you mentioned that stringbuilder will consume more memory than using string?

So, you do not use stringbuilder? you use string?

Is this what you do?

string sql = part1 + part2 + part3 + ");";

Coordinator
Dec 17, 2012 at 2:04 PM

Anyway, I'm interested in the code that you've wrote. It would be glad to have a look at it and if possible, implement it. Even though we are not able to restore it, but at least large blob data can be exported at the moment. We'll figure it out later on how to restore the large data. Thanks for the sharing :)

Dec 17, 2012 at 2:12 PM

This is the code I use to restore the database wtih my edited backup file.

I've managed to restore 50 mb large records but will probably limit it to 40 mb just to be safe.

string sqlLine = string.Empty;
                string tempSqlLine = string.Empty;
                StreamReader textReader = null;
                MySqlConnection conDatabase = null;
                MySqlCommand cmdDatabase = null;
                using (conDatabase = new MySqlConnection(@"server=localhost;port=3306;user=test;pwd=test;"))
                {
                    
                    using (cmdDatabase = new MySqlCommand())
                    {

                        cmdDatabase.Connection = conDatabase;
                        conDatabase.Open();

                        
                        textReader = new StreamReader(@"C:\test\test1.sql", true);

                        while (sqlLine != null)
                        {
                            sqlLine = textReader.ReadLine();
                            if (sqlLine == null)
                                continue;
                            else if (sqlLine.StartsWith("--") || (sqlLine.Equals("\r\n")) || sqlLine.Equals("\n"))
                                continue;
                            else if (sqlLine.EndsWith(";"))
                            {
                                cmdDatabase.CommandText += sqlLine;
                                cmdDatabase.ExecuteNonQuery();
                                sqlLine = string.Empty;
                                cmdDatabase.CommandText = "";
                            }
                            else
                            {
                                cmdDatabase.CommandText += sqlLine;

                            }

                        }
                    }
                }


I'll post the backup code in another post.
Dec 17, 2012 at 2:30 PM

This is the changed code for the backup.

I changed the code in the function: 
         private void ExportStart()

                    while (rdr.Read())
                    {
                        // Cancel the export process
                        if (cancelProcess)
                        {
                            exportCompleteArg.CompletedType = ExportCompleteArg.CompleteType.Cancelled;
                            return;
                        }

                        // Raise the ProgressChanged Event
                        if (ExportProgressChanged != null)
                        {
                            exportProgressArg.CurrentRowInCurrentTable += 1;
                            exportProgressArg.CurrentRowInAllTable += 1;
                            ExportProgressChanged(this, exportProgressArg);
                        }

                        if (InsertStatementHeader == null)
                        {
                            int fc = rdr.FieldCount;
                            string[] ColumnNames = new string[fc];
                            for (int ci = 0; ci < rdr.FieldCount; ci++)
                            {
                                ColumnNames[ci] = rdr.GetName(ci);
                            }
                            InsertStatementHeader = GetInsertStatementHeader(exportProgressArg.CurrentTableName, ColumnNames);
                        }

                        int byteSize = 0;
                        byte[] temp;
                        List<string> intList = new List<string>();
                        for (int i = 0; i < rdr.FieldCount; i++)
                        {
                            if (rdr[i] is byte[])
                            {
                                intList.Add(i.ToString());
                                temp = (byte[])rdr[i];
                                byteSize += temp.Length;
                            }
                        }

                        

                        if (byteSize > 0)
                        {
                            string ValueString = GetSqlValueString(rdr, true);
                            if (sb.Length != 0)
                            {
                                sb.Append(";");
                                textWriter.WriteLine("\r\n" + Encrypt(sb.ToString()));
                                textWriter.Flush();
                                sb = new StringBuilder();
                            }

                            textWriter.WriteLine("\r\n" + Encrypt(InsertStatementHeader));

                            foreach (string intId in intList)
                            {

                                string leftPart = ValueString.Split(new string[] { "<[{" + intId + "}]>" }, StringSplitOptions.None)[0];
                                ValueString = ValueString.Split(new string[] { "<[{" + intId + "}]>" }, StringSplitOptions.None)[1];


                                textWriter.Write(Encrypt(leftPart));
                                int checkInt = Int32.Parse(intId);
                                temp = (byte[])rdr[checkInt];
                                byte[] tempArray = new byte[5000000];
                                int arrayPosition = 0;
                                int amount = 5000000;
                                bool first = true;
                                while (arrayPosition < temp.Length)
                                {

                                    if (temp.Length - arrayPosition < 5000000)
                                    {
                                        if (first)
                                        {
                                            amount = temp.Length;
                                        }
                                        else
                                        {
                                            amount = temp.Length - arrayPosition;
                                        }
                                    }
                                    Array.Copy(temp, arrayPosition, tempArray, 0, amount);

                                    char[] c;
                                    byte b;
                                    int x;
                                    if (first)
                                    {
                                        x = 2;
                                        c = new char[amount * 2 + 2];
                                        c[0] = '0'; c[1] = 'x';
                                        first = false;
                                    }
                                    else
                                    {
                                        c = new char[amount * 2];
                                        x = 0;
                                    }

                                    for (int y = 0; y < amount; ++y, ++x)
                                    {
                                        b = ((byte)(tempArray[y] >> 4));
                                        c[x] = (char)(b > 9 ? b + 0x37 : b + 0x30);
                                        b = ((byte)(tempArray[y] & 0xF));
                                        c[++x] = (char)(b > 9 ? b + 0x37 : b + 0x30);
                                    }

                                    textWriter.Write(Encrypt(new string(c)));
                                    textWriter.Flush();
                                    if (first)
                                    {
                                        arrayPosition += amount + 2;
                                    }
                                    else
                                    {
                                        arrayPosition += amount;
                                    }
                                }



                            }
                            textWriter.Write(Encrypt(ValueString) + ";");
                            textWriter.Flush();

                        }
                        else
                        {
                            string ValueString = GetSqlValueString(rdr, false);
                            if (sb.Length == 0)
                            {
                                sb.Append(InsertStatementHeader);
                                sb.Append("\r\n");
                                sb.Append(ValueString);
                            }
                            else if (((long)sb.Length + (long)ValueString.Length) < _exportInfo.MaxSqlLength)
                            {
                                sb.Append(",\r\n");
                                sb.Append(ValueString);
                            }
                            else
                            {
                                sb.Append(";");

                                textWriter.WriteLine(Encrypt(sb.ToString()));
                                textWriter.Flush();

                                sb = new StringBuilder();
                                sb.Append(InsertStatementHeader);
                                sb.Append("\r\n");
                                sb.Append(ValueString);
                            }
                        }
                    }

 

And for the second part:

In the function:
private string GetSqlValueString(MySqlDataReader rdr, bool bytes)


                else if (ob is System.Byte[])
                {
                    if (bytes)
                    {
                        sbData.Append("<[{" + i + "}]>,");
                    }
                    else
                    {
                        sbData.Append(methods.GetBLOBSqlDataStringFromBytes((byte[])rdr[i]) + ",");
                    }
                }

Dec 19, 2012 at 1:15 PM

After fiddling around a bit more with some code I think I've found a way to perhaps insert bigger files.

My idea was to read a sql backup file in line by line. And when a line is bigger then 50000000 I would turn the blob files back into byte arrays and attach them trough cmdDatabase.Parameters.AddWithValue while replacing the blob file in the string with the parameter name.

 

The problem is that the string you read fully is too big already. It balks on the second big record you come across.

Coordinator
Dec 19, 2012 at 2:16 PM
Edited Dec 19, 2012 at 2:19 PM

Hi, wanna join as one of the developer?
Actually you have just been added.
but, if you would suggest to join at a later time...
however, you are welcome to join :)

Coordinator
Dec 26, 2012 at 1:23 AM

I'm currently busy on something else. This patch will be carried out some where in between of January and February.

Thanks for the code, pal. 

merry christmas & happy new year.

Coordinator
Feb 3, 2013 at 11:15 PM
Edited Feb 3, 2013 at 11:18 PM
Hi, EmptyNull,

May I know how do you insert large BLOB binary files/data into MySQL database in your applications?
Can you show me the codes that you used?

Thanks.

This feature is listed into To-Do list at here: http://mysqlbackupnet.codeplex.com/discussions/430799