Problems importing database!

Forum announcements are posted here. New cool features, scheduled backups and maintenance, you name it.
Post Reply
Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Problems importing database!

Post by Jorg »

Hi all,

this is frustrating the heck out of me. I am trying to import the database from my old to new provider. Somehow the MySQL phpMyAdmin 2.6.0 console will not import the tables. I am getting timeout errors:

Fatal error: Maximum execution time of 60 seconds exceeded in /var/www/html/phpMyAdmin-2.6.0-pl3/libraries/read_dump.lib.php on line 173

Trying a few tables at the time works, but it chokes on the largest of them all, the posts table.

This is very serious as I cannot get the darn database in the new site. Does anybody have any ideas or experience with backing up and restoring databases?

Is there a mysql query I can do on the command line perhaps to get the database in?

Thanks,

- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Avi
Super wx Problem Solver
Super wx Problem Solver
Posts: 398
Joined: Mon Aug 30, 2004 9:27 pm
Location: Tel-Aviv, Israel

Post by Avi »

Hey, I've encountered the same problems (timeouts... max size... etc..) on my sites... I remember that using mysql_tool helped me! http://www.smartbb.net/mysql_tool.zip

EDIT: almost forgot! To change the timeout in mysql tool:
If you are still getting time out problems try forcing a lower execution time between refreshes by editing "var $maximum_time = 0;" on line 26 changing 0 to the number of seconds you wish the script to execute (work up from 15)
Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg »

Hi Avi,

Thanks a lot for your help! However, my &%&%$^ current hosting service reports this;

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 1167 bytes) in /home/virtual/site155/fst/var/www/html/###/mysql_tool.php on line 641

I only selected the wxwidgets board tables. Ohh well. I will wait for SSH access tomorrow and try it with the command line prompt.

- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Avi
Super wx Problem Solver
Super wx Problem Solver
Posts: 398
Joined: Mon Aug 30, 2004 9:27 pm
Location: Tel-Aviv, Israel

Post by Avi »

Well, I guess your server has a strict php/mysql access? Can you connect the mysql server externally? Try using MySQL Administrator...
Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg »

Nope unfortunately not. But what I will do is simply import it with the mysql command line (prays the hosting has that) ... they already helped me great unfortunately I am not helped completely yet.

We will see, I will manage the hard way or the easy way ;-)

- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
alexcoppo
Knows some wx things
Knows some wx things
Posts: 37
Joined: Mon Sep 06, 2004 8:56 am
Location: Italy
Contact:

Post by alexcoppo »

Try splitting the original table into pieces (invent some where clause which cuts the whole table into smaller, mutually exclusive parts) and import the various pieces.
Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg »

Uhm ... the text file is 55 Mb, this is about 400.000 lines of text. I am not even considering splitting this file up by hand :-(

I considered breaking up the tables or simply exporting a few sql tables but the wxwidgetsbb_posts is the biggest one and that table alone cannot be imported by uploading, the problem is that my upload takes longer then 60 seconds, and I cannot change that because it is set by the hosting.

I will figure it out though ;-) thanks fr your help!
- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
krysa
Experienced Solver
Experienced Solver
Posts: 71
Joined: Wed Feb 16, 2005 9:23 pm
Location: Lithuania

Post by krysa »

If nothing works - try to use some simple php script to import the data. You can write a php script, that takes .sql file as input, splits the file into single queries, and executes those queries on the mysql server. this way, you can upload any .sql file of any size (ftp or something) and then use that script to import the data into database.
Platform: MSW (Windows XP Pro)
Compiler: msvc 13.10.3077 (Free Toolkit)
wxWidgets: v2.6.0
Jorg
Moderator
Moderator
Posts: 3971
Joined: Fri Aug 27, 2004 9:38 pm
Location: Delft, Netherlands
Contact:

Post by Jorg »

Hi guys! I managed to do it! SSH is your friend!
My hosting service turned SSH on, and I performed the following steps;

mysql -D solidsteel_nl_-_ourdata -u solidsteel -p

> fill in password
mysql> connect
Connection id: 32852
Current database: solidsteel_nl_-_ourdata

> source localhost.sql

And all the queries are executed beautifully!

Now I will wait until the domain is transferred, and set up forum in the new site in the mean time.

Regards,
- Jorgen
Forensic Software Engineer
Netherlands Forensic Insitute
http://english.forensischinstituut.nl/
-------------------------------------
Jorg's WasteBucket
http://www.xs4all.nl/~jorgb/wb
Post Reply