Solved

How do I import a very large .SQL file using PHPMyAdmin?

Posted on 2009-05-14
14
1,497 Views
Last Modified: 2012-08-14
I am trying to import a .SQL file using PHPMyAdmin. The file I am trying to import is 71MB.
I create the database first using PHPMyAdmin, then use "Import". The process proceeds for a few minutes, then the status bar does not progress much, and then I get the following message:

Fatal error: Maximum execution time of 300 seconds exceeded in C:\AppServ\www\phpMyAdmin\libraries\import\sql.php on line 87

The end result is that it imports one table, and I can browse the table using PHPMyAdmin, but there are numerous more tables that are not created.  It is timing out in the process and the full database is not created.

When I attempt the same without the database already created, I get an error that no database is found. and this happens quickly.

I will admit, I am definitely a newbie when it comes to PHP / MySQL and I did try the same using the MySql command prompt suggestions posted in this forum, but nothing happens after executing the command.

Any Suggestions would be greatly appreciated. Thank You!
0
Comment
Question by:Kevin212
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 10

Expert Comment

by:mahome
ID: 24384842
You can use the mysql console

mysql dbname < sql.php
0
 
LVL 10

Assisted Solution

by:mahome
mahome earned 80 total points
ID: 24384875
Sorry you are on windows, then you call

mysql.exe dbname < sql.php

mysql.exe is located in your bin folder of Mysql, for example: C:\Programs\MySQL\MySQL Server 5.0\bin

if you have user and password, command is:

mysql.exe -u user -p password dbname < sql.php
0
 

Author Comment

by:Kevin212
ID: 24384930
Ok, thank you. one thing I am not clear onL in your syntax, I don't see where I indicate the backed-up .sql file that needs to be imported. if the file is named "test.sql", were does that factor into thet syntax you mentioned?, let's assume the database is named Testdb in PHPMyAdmin

0
 
LVL 10

Assisted Solution

by:mahome
mahome earned 80 total points
ID: 24385053
You use
mysql.exe -u user -p password Testdb < test.sql

where user and password are variables, you have to replace it with your username and password for Mysql
0
 

Author Comment

by:Kevin212
ID: 24385147
I used that exact syntax, with the propper User name and PW, with the tes.sql file in the "C:\AppServ\MySQL\bin" directory. Nothing happens, just a flashing cursor on the next line.

do I need to put that .sql file in a different directory? 0r to I need to do specify a hard path such as:

mysql.exe -u user -p password Testdb < C:\Temp\test.sql

??
0
 
LVL 39

Assisted Solution

by:Roger Baklund
Roger Baklund earned 40 total points
ID: 24385219
There should not be a space between "-p" and "password". You don't need to type the ".exe" part:

mysql -u user -pPassword Testdb < C:\Temp\test.sql
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24385228
If the test.sql file is in the current folder, you don't need to specify the path.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Kevin212
ID: 24385319
Thank you very very much for your help, Unfortunately...

..not sure what I am missing here. I'm doing exactly as stated, but again, just a flashing cursor. When I look in the .."MySQL\Data\Testdb" folder, there is only the file "db.opt"
0
 
LVL 10

Assisted Solution

by:mahome
mahome earned 80 total points
ID: 24385419
Try a login with console to get sure that everything works so far:

mysql -u user -pPassword

in the console you can check databases:
show databases;
0
 

Author Comment

by:Kevin212
ID: 24385527
I did that, no errors or anything.

I think I will satrt from scratch. Is there a particular version of WAMP that you suggest I install, don't wanna spin my wheels if there is a version issue going on here..
0
 
LVL 10

Assisted Solution

by:mahome
mahome earned 80 total points
ID: 24385737
0
 
LVL 39

Assisted Solution

by:Roger Baklund
Roger Baklund earned 40 total points
ID: 24386374
>> I did that, no errors or anything.

You did not even get the MySQL welcome message? This is how it should look like:
C:\Users\cxr>mysql -uUsername -pPassword DatabaseName

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 441

Server version: 5.0.67-community-nt MySQL Community Edition (GPL)
 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 

mysql>

Open in new window

0
 

Accepted Solution

by:
Kevin212 earned 0 total points
ID: 24402519
I was able to accomplish this by changing a setting in "config.inc.php"

The setting I changed was:

$cfg['ExecTimeLimit']  = 0;

I forget what the original value was, but by setting it to "0", it allowed the import to run to completion. I'm up and running now.

Thanks for the answers I did get, much appreciated.
0
 

Expert Comment

by:stmayes
ID: 24402547
I recommend that you just edit your .sql file. Split it into smaller files. If it is a PHPMyAdmin dump it is very verbose. So cut it at the comment sections and just upload it in multiple files.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now