We help IT Professionals succeed at work.

do restore on mysql from .sql file

r3nder
r3nder asked
on
I am trying to write an app that allows me to drop,create insert.....etc into a mysql database
I have the .sql scripts and I tried this but it gives me an error message "Packets larger than max_allowed_packet are not allowed." how can I fix this?
            string myTestSql = System.IO.File.ReadAllText(filename);

            using (MySqlConnection conn = new MySqlConnection(sqlCon))
            {
                MySqlCommand newCmd = new MySqlCommand(myTestSql, conn);
                conn.Open();
                newCmd.ExecuteNonQuery();
                conn.Close();
            }     

Open in new window

Comment
Watch Question

Commented:
You will need to change the max_allowed_packet value in your MySQL configuration file, my.cnf. Change this value to the  max size that you think you will need. Since you are programming in .NET, I'm guessing that you have MySQL installed on Windows, so your configuration file could be in one of these locations:
C:\Program Files\MySQL\MySQL Server 5.5\my.ini
C:\Program Files\MySQL\MySQL Server 5.5\my.cnf

Author

Commented:
this is on a remote server that is not owned by us - so I cant change that
Salah Eddine ELMRABETTechnical Lead Manager (Owner)

Commented:
Hi,

If you cannot manage the MySQL, you can try to a use a script that will upload the file, copy the content and then insert the result into MySQL.

Best regards.

Salah

Author

Commented:
ok - how?
theGhost_k8Database Consultant

Commented:
Hi R3nder...
Read: http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
Change configuration parameter and restart MySQL to fix this.
Salah Eddine ELMRABETTechnical Lead Manager (Owner)

Commented:
Hi,

@theGhost_k8: He cannot access the configuration of my.ini since the MySQL server is not managed locally!!

So He re you can find and example of how to load and read the .sql file, then execute the .sql file using  the variable used to read the content:

https://social.msdn.microsoft.com/Forums/en-US/a8fa22d0-1f0f-4133-8238-4115b6f3fb18/read-sql-file-in-vbnet

I hope this will help you, you need to adapt the script to your situation or just inspire from this script to modify your code.

Best Regards.

Salah
Salah Eddine ELMRABETTechnical Lead Manager (Owner)

Commented:
Hi,

You can also refer to this question, it can help you:

http://www.experts-exchange.com/Database/MySQL/Q_25126958.html

Regards.

Salah
theGhost_k8Database Consultant

Commented:
my bad r3nder,

The variable is dynamic you can do:
set global max_allowed_packet=<VALUE>; and try

Author

Commented:
I need super user  rights to do that - and I don't have access to the server - its a go daddy server
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi!

What is the size of max_allowed_packet ?
You will need to divide your script into data chunks (batch sizes) of a size that is little smaller than max_allowed_packet.
Do all the table creations first and then issue the inserts in those batch sizes with commit after each batch.

Regards,
    Tomas Helgi
Database Consultant
Commented:
Well then trim down your sql scripts is the only option, like small batch or one query at a time.
Salah Eddine ELMRABETTechnical Lead Manager (Owner)

Commented:
Hi,

Did yoy have a look on what I posted before?

Regards.

Salah

Author

Commented:
I linked the servers and did a bulk transfer