Restoring 25gb File into MySQL

[bmacbmac]

Hello. I am very new to MySQL, but this is what I have so far.

I have a 25gb File.sql file I have been asked to import into MySQL. The purpose is to eventually migrate the data to MS SQL 2008.

The first 3000 rows are 'create table' statements. The remaining rows are 'insert into' rows.

I created a database in MySQL Workbench 5.2.33 CE

I click file > Execute SQL File > select File.SQL and receive an error 'bad allocation'.

I copied the 'create table' rows into their own .sql file and launched it with file > Execute SQL File. It created tables just fine.

Now my question is, how do I run the 'insert into' rows? I found a text file splitter which will split my original.sql file based on row counts. For example, I can create smaller text files by splitting out every Nth row.

I have to get the text file down to about 300,000 rows for the error to not appear.

I still haven't been able to complete one .sql file however. MySQL locks up about 30,000 rows into it.

So my questions are....
1) Am I going about this the best way? Should I be running the .sql file differently than I am now?

2) If I am forced to use these smaller files (I'm sure there will be 100's of them). Can I force Workbench to use my specific database? So far I have only made it work by opening my File1.sql and adding:

use DatabaseName;

Hoping I don't have to add this to 100's of Text files.

Thanks!

Brian

 

[DonQuichote]

There are a few tips:
[ul]
[li]An allocation is a part of the character encoding settings. I assume you are trying to set an allocation that is unknown to MySQL in a CREATE DATBASE statement or something like that.[/li]
[li]Use the command-line client. It executes the file without trying to load the whole thing into memory first. Also, the command-line client stops at the first error and shows the line number where it went wrong. I don't know the workbench enough to know if it does the same.[/li]
[li]Disable autocommit. You can put

SET AUTOCOMMIT=0;

into the file before the insert statements. After the whole script, add

SET AUTOCOMMIT=1;

. This speeds up things considerably[/li]
[li]It might help to show the part of the script that causes the error, so we can see better what goes wrong.[/li]
[/ul]
Good luck!


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[bmacbmac]

Thanks for the tips! The command line works much better, but I am getting a different error. I'll start a new thread for that.