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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
theGhost_k8Database ConsultantCommented:
Well then trim down your sql scripts is the only option, like small batch or one query at a time.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.