do restore on mysql from .sql file

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

LVL 6
r3nderAsked:
Who is Participating?
 
theGhost_k8Database ConsultantCommented:
Well then trim down your sql scripts is the only option, like small batch or one query at a time.
0
 
sno0401Commented:
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
0
 
r3nderAuthor Commented:
this is on a remote server that is not owned by us - so I cant change that
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
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
0
 
r3nderAuthor Commented:
ok - how?
0
 
theGhost_k8Database ConsultantCommented:
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.
0
 
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
0
 
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
0
 
theGhost_k8Database ConsultantCommented:
my bad r3nder,

The variable is dynamic you can do:
set global max_allowed_packet=<VALUE>; and try
0
 
r3nderAuthor Commented:
I need super user  rights to do that - and I don't have access to the server - its a go daddy server
0
 
Tomas Helgi JohannssonCommented:
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
0
 
Salah Eddine ELMRABETTechnical Lead Manager (Owner)Commented:
Hi,

Did yoy have a look on what I posted before?

Regards.

Salah
0
 
r3nderAuthor Commented:
I linked the servers and did a bulk transfer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.