December 01, 2020, 09:47:44 pm
News: If someone gives you assistance give them a Good Star
Pages: [1]   Go Down
Author Topic: Insert large sql query  (Read 8525 times)
Slowhand
Supporters
*

Stars: +0/-0
Offline Offline

Gender: Male
Awards:
Topic Necromancer
Posts: 4668


I code C#!


WWW
« on: December 17, 2006, 10:09:40 am »

Hi,

I have a very large sql dump (800 mb) that I would like to execute on my mysql server.
Unfortunately the file is so big I don't know what to do with it.
I tried opening it with the mysql query browser, but it crashed being out of memory.

Is there a way to run it directly on the database, or won't that work as well?
« Last Edit: February 04, 2007, 02:32:31 pm by Slowhand » Logged


wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #1 on: December 17, 2006, 10:04:49 pm »

Nope not going to happen.

Only product I know which can open that big TEXT file is V-Edit. So the best thing you can do is open that dump in V-Edit and then take chunks off the main file to create smaller files.

And TSQL query limit is 4000 or 8000 lines anyways (Can't remember which is the correct number but I am sure it's one of them)

Hope this helps.
Logged

CornedBee
Ancient Poster
*

Stars: +0/-0
Offline Offline

Gender: Male
Posts: 5258


WWW
« Reply #2 on: December 17, 2006, 10:36:17 pm »

Is it one query or many small queries?

You could try the command line mysql client, piping the file into it. I don't think there's a limit on the size of a pipable file beyond FS limits, and MySQL would never see more than one query.
Logged

All the buzzt
CornedBee

"Writing specifications is like writing a novel. Writing code is like writing poetry."
- Anonymous, published by Raymond

"This is how Liberty dies - with thunderous applause."
Senator Padmé Amidala - Revenge of the Sith

Slowhand
Supporters
*

Stars: +0/-0
Offline Offline

Gender: Male
Awards:
Topic Necromancer
Posts: 4668


I code C#!


WWW
« Reply #3 on: December 18, 2006, 08:23:36 am »

Quote from: CornedBee
Is it one query or many small queries?

You could try the command line mysql client, piping the file into it. I don't think there's a limit on the size of a pipable file beyond FS limits, and MySQL would never see more than one query.

I believe every line is a new INSERT query.
Logged


Slowhand
Supporters
*

Stars: +0/-0
Offline Offline

Gender: Male
Awards:
Topic Necromancer
Posts: 4668


I code C#!


WWW
« Reply #4 on: December 18, 2006, 12:09:51 pm »

Hmm, just to clarify: It's a database dump from Wikipedia, I want to run it locally.
These dumps come as compressed XML files, and with some tool I turned it into an sql file.
But now I read there might be a way to pump this XML file into the database directly.

Anyone has any experience with that?
Logged


amitabh
Old Timer
*

Stars: +0/-0
Offline Offline

Posts: 2764


WWW
« Reply #5 on: December 18, 2006, 07:34:56 pm »

You  might have to code something that reads some part of the dump file, executes the query and keeps on repeating the process until the entire dump is completely restored. Not sure that you would getting something off the shelf.
Logged

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #6 on: December 18, 2006, 09:35:12 pm »

Yups I don't think there is an easier way to do this slowy
Logged

CornedBee
Ancient Poster
*

Stars: +0/-0
Offline Offline

Gender: Male
Posts: 5258


WWW
« Reply #7 on: December 20, 2006, 01:19:51 am »

Have you tried
Code:
slowhand@dbhost:~ $ mysql -u [username] -p [database] < bigfatfile.sql
Enter Password: [passwod]
yet?
Logged

All the buzzt
CornedBee

"Writing specifications is like writing a novel. Writing code is like writing poetry."
- Anonymous, published by Raymond

"This is how Liberty dies - with thunderous applause."
Senator Padmé Amidala - Revenge of the Sith

HaxSoft
Hero Member
*

Stars: +0/-0
Offline Offline

Gender: Male
Posts: 671



WWW
« Reply #8 on: February 04, 2007, 04:47:43 pm »

If every line is something like:

Code:
INSERT INTO table_name(field1, field2, field3) VALUES ("bla", "blah", "blaa")

Then I'd simply cut the file in half and try. If that doesn't work, cut the halves in half and so on, until I found a size that MySQL can handle. It shouldn't amount to more than 2, 4, 8, or maybe even 16 chunks.
Logged

May all your problems be solved digitally.

Slowhand
Supporters
*

Stars: +0/-0
Offline Offline

Gender: Male
Awards:
Topic Necromancer
Posts: 4668


I code C#!


WWW
« Reply #9 on: February 04, 2007, 09:51:09 pm »

I had abandonned this project for a while. Might get back to it and give it another shot. smile
Logged


wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #10 on: January 08, 2008, 08:51:14 am »

Better late than never smile
Logged

Jason Reed
Administrator
*

Stars: +4/-0
Offline Offline

Gender: Male
Posts: 4545

Pure Evil Administrator


WWW
« Reply #11 on: January 08, 2008, 09:44:42 am »

I thought phpMyAdmin actually had a functionality to handle <XML> dumps. However, I think the size will overload the phpMyAdmin. I know I had some problems with database dumps when I was converting GT from IPB to SMF. The IPB dump that Martin gave me was huge (would have hated to see how big it would have been if it were complete). I ended up doing what HaxSoft suggested. I broke it up into many smaller files.

I'm sure you can come up with a way to read the file and then break it up using C#, and then you could load each files separately.
Logged

-- signature --

simmo
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8560



« Reply #12 on: January 08, 2008, 03:05:30 pm »

I had abandonned this project for a while. Might get back to it and give it another shot. smile
Better late than never smile
Are you referring to Slowie or that your reply is 11months later  laugh1
Logged

                 ..... .....

wrack
Master of TMEOotAP
*

Stars: +3/-0
Offline Offline

Posts: 11116


Don't look back. Look in the mirror to look back.


WWW
« Reply #13 on: January 08, 2008, 10:16:50 pm »

11 Month rofl_yello
Logged

Slowhand
Supporters
*

Stars: +0/-0
Offline Offline

Gender: Male
Awards:
Topic Necromancer
Posts: 4668


I code C#!


WWW
« Reply #14 on: January 09, 2008, 08:25:45 am »

Pff, gravediggers! Another Tounge
Posted on: 09-01-2008, 09:24:49
For the record, I tried Cornedbee's solution, and it worked.
Still never got wikipedia to run locally, though..
Logged


Pages: [1]   Go Up
Print
 
Jump to: