PHP: maximum execution time when importing .SQL data file

Question

I am trying to import a large .sql data file using phpMyAdmin in XAMPP. However this is taking a lot of time and I keep getting:

Fatal error: Maximum execution time of 300 seconds exceeded in C:\xampp\phpMyAdmin\libraries\dbi\DBIMysqli.class.php on line 285

And the file is about 1.2 million lines long.

The file is about 30MB big, so it is not that big. I don't really understand why it is taking so long.

;;;;;;;;;;;;;;;;;;;
; Resource Limits ;
;;;;;;;;;;;;;;;;;;;

; Maximum execution time of each script, in seconds
; http://php.net/max-execution-time
; Note: This directive is hardcoded to 0 for the CLI SAPI
max_execution_time=30000

; Maximum amount of time each script may spend parsing request data. It's a good
; idea to limit this time on productions servers in order to eliminate unexpectedly
; long running scripts.
; Note: This directive is hardcoded to -1 for the CLI SAPI
; Default Value: -1 (Unlimited)
; Development Value: 60 (60 seconds)
; Production Value: 60 (60 seconds)
; http://php.net/max-input-time
max_input_time=60

; Maximum input variable nesting level
; http://php.net/max-input-nesting-level
;max_input_nesting_level = 64

; How many GET/POST/COOKIE input variables may be accepted
; max_input_vars = 1000

; Maximum amount of memory a script may consume (128MB)
; http://php.net/memory-limit
memory_limit=200M

The is the config file for php.ini in xampp, for some reason i still get

Fatal error: Maximum execution time of 300 seconds exceeded in C:\xampp\phpMyAdmin\libraries\dbi\DBIMysqli.class.php on line 285.

Judaize

Increase the time limit. Check your server configuration. 300 is already pretty high. Default is 30. – Timbered

Yeah but with 300 i can only get to line 285 out of the 1.2 million. Although the file is about 30MB, i don't really understand why it is taking so long. – Judaize

That is not the line number of your sql file, but your php. – Strainer

Scripts run from the commandline rather than via a web service don't have a maximum execution time limit by default. A script that's processing millions of datapoints shouldn't really be run in the context of a web server so I'd strongly advise you to refactor your script to run from the commandline instead – Barmen

Answer 1

There's a configuration variable within the phpMyAdmin directory that you can find in phpmyadmin\config.inc.php called $cfg['ExecTimeLimit'] that you can set to whatever maximum execution time you need.

Bellaude

There is a comment inside the config.default.php, warning to not edit that file. This is because the configurations there can be overwritten by the configurations set in another file: config.inc.php. So, change the timeout as stated by Randell, but do that in the appropriate file. – Mag

Good call, @Mag . I just used that method to successfully import a large sql file that was previously hanging at 300 seconds. I added $cfg['ExecTimeLimit'] = 600; to the end of my config.php.inc, which is located in the root of phpmyadmin/. – Lippi

@Lippi you're a genious. Everyone talk about file "config.inc.php" without precise where FOUND it ! Thx bro – Fontes

You should not edit this, instead edit the same in xampp\phpmyadmin\config.inc.php file, if the line is not available on the file add the line ($cfg['ExecTimeLimit'] = your_value_in_seconds;) – Prognosis

I've now just updated the answer with the correct config. – Bellaude

Answer 2

Well, to get rid of this you need to set phpMyadmin variable to either 0 that is unlimited or whichever value in seconds you find suitable for your needs. Or you could always use CLI(command line interface) to not even get such errors(For which you would like to take a look at this link.

Now about the error here, first on the safe side make sure you have set PHP parameters properly so that you can upload large files and can use maximum execution time from that end. If not, go ahead and set below three parameters from php.ini file,

1. max_execution_time=3000000 (Set this as per your req)
2. post_max_size=4096M
3. upload_max_filesize=4096M

Once that's done get back to finding phpMyadmin config file named something like "config.default.php". On XAMPP you will find it under "C:\xampp\phpMyAdmin\libraries" folder. Open the file called config.default.php and set :

$cfg['ExecTimeLimit'] = 0;

Once set, restart your MySQL and Apache and go import your database.

Enjoy... :)

Impassioned

This worked perfectly. I just needed to update my test server. I wouldn't do this on my production server. – Flagella

Answer 3

Set Only 3 Parameters from php.ini file of your server

A. max_execution_time = 3000000 (Set as per your requirment)
B. post_max_size = 4096M
C. upload_max_filesize = 4096M

Edit C:\xampp\phpMyAdmin\libraries\config.default.php Page

$cfg['ExecTimeLimit'] = 0;

After all set, restart your server and import again your database.

Done

Fuddle

Answer 4

You're trying to import a huge dataset via a web interface.

By default PHP scripts run in the context of a web server have a maximum execution time limit because you don't want a single errant PHP script tying up the entire server and causing a denial of service.

For that reason your import is failing. PHPMyAdmin is a web application and is hitting the limit imposed by PHP.

You could try raising the limit but that limit exists for a good reason so that's not advisable. Running a script that is going to take a very long time to execute in a web server is a very bad idea.

PHPMyAdmin isn't really intended for heavy duty jobs like this, it's meant for day to day housekeeping tasks and troubleshooting.

Your best option is to use the proper tools for the job, such as the mysql commandline tools. Assuming your file is an SQL dump then you can try running the following from the commandline:

mysql -u(your user name here) -p(your password here) -h(your sql server name here) (db name here) < /path/to/your/sql/dump.sql

Or if you aren't comfortable with commandline tools then something like SQLYog (for Windows), Sequel Pro (for Mac), etc may be more suitable for running an import job

Barmen

Answer 5

This worked for me. If you got Maximum execution time 300 exceeded in DBIMysqli.class.php file. Open the following file in text editor C:\xampp\phpMyAdmin\libraries\config.default.php then search the following line of code:

$cfg[‘ExecTimeLimit’] = 300;

and change value 300 to 900.

https://surya2in1.wordpress.com/2015/07/28/fatal-error-maximum-execution-time-of-300-seconds-exceeded/

Compossible

Answer 6

Simply set $cfg['ExecTimeLimit'] = 0; In xampp/phpMyAdmin/libraries/config.default.php.

Maximum execution time in seconds (0 for no limit).

And make this below changes in php.ini file as per file size.

post_max_size = 600M 
upload_max_filesize = 500M 
max_execution_time = 5000 
max_input_time = 5000 
memory_limit = 600M

But make sure 'post_max_size' and 'memory_limit' should be more than upload_max_filesize.

**Note - Don't forget to restart your server.

Relation

Answer 7

If you are using the laragon and your database is phpMyAdmin the process is the same for tackle this error.

Open laragon right-click on it and open php.ini file

set these value accordingly to your needs

• max_execution_time
• post_max_size
• upload_max_filesize

Open new file config.default.php

path is C:\laragon\etc\apps\phpMyAdmin\libraries\config.default.php

• and set the value of this $cfg['ExecTimeLimit'] = 0;

Restart the laragon.

I hope it would solve your problem for laragon environment 🙏 #Happy Coding :)

Relations

Answer 8

Is it a .sql file or is it compressed (.zip, .gz, etc)? Compressed formats sometimes require more PHP resources so you could try uncompressing it before uploading.

However, there are other methods you can try also. If you have command-line access, just upload the file and import with the command line client mysql (once at the mysql> prompt, use databasename; then source file.sql).

Otherwise you can use the phpMyAdmin "UploadDir" feature to put the file on the server and have it appear within phpMyAdmin without having to also upload it from your local machine.

This link has information on using UploadDir and this one has some more tips and methods.

Ragman

Answer 9

you must change php_admin_value max_execution_time in your Alias config (\XAMPP\alias\phpmyadmin.conf)

answer is here: WAMPServer phpMyadmin Maximum execution time of 360 seconds exceeded

Calise

Answer 10

After trying many things with no success, I've managed to get SSH access to the server, and import my 80Mb database with a command line, instead of phpMyAdmin. Here is the command:

mysql -u root -p -D mydatabase -o < mydatabase.sql

It's much easier to import big databases, if you are running xammp on windows, the path for mysql.exe is C:\xampp\mysql\bin\mysql.exe

Vergos

Answer 11

1-make a search in your local drive and type "php.ini" 2-you may see many files named php.ini you should choose the one that fits with your php version (see localhost) 3-open the php.ini file make a search on "max_execution_time" then make it equal to "-1" to make it unlimited

Cyclopropane

Answer 12

Set Only 3 Parameters from php.ini file of your server

A. max_execution_time = 3000000 (Set as per your requirment)

B. post_max_size = 4096M

C. upload_max_filesize = 4096M

Case 1 : If you are Using Xampp, Edit

C:\xampp\phpMyAdmin\libraries\config.default.php

Case 2 : If you are Using Wampp, Edit

c:\wamp64\apps\phpMyAdmin\libraries\config.default.php

Case 3 : If you are Using Laragon, Edit

C:\laragon\etc\apps\phpMyAdmin\libraries\config.default.php

search for $cfg['ExecTimeLimit'] in config.default.php file and make the following changes

$cfg['ExecTimeLimit'] = 0;

After all set, restart your server and import again your database.

Done

Pita

Answer 13

The working solution:

1. Update the configuration as mentioned in php.ini file (Located at xampp folder, \xampp\php\php.ini)

     post_max_size=1024M
     upload_max_filesize=1024M
     max_execution_time = 10000
     max_input_time=10000
     memory_limit=1024M
   

2. Update config.inc.php (Located at Xampp folder, \xampp\phpMyAdmin\config.inc.php) if exists update value otherwise add the bellow line

   $cfg['ExecTimeLimit'] = 10000;
   

3. Update my.ini (Located at Xamp folder, \xampp\mysql\bin\my.ini )

    max_allowed_packet=1024M
   

4. Stop & Restart Apache & MySQL

Connelley

Answer 14

1. Never change original config.default.php file.
2. Changing general executing time in php.ini has no effect on phpmyadmin scripts.
3. Use a new config.inc.php or the config.sample.inc.php provided in the /phpMyAdmin folder instead.
4. You can set $cfg[‘ExecTimeLimit’] = 0; means endless execution in the config.inc.php as recommended above. Be aware this is not a "normal" ini file. Its a php script, so you need a open <?php at the beginning of that file.
5. But most important: Do not use this procedure at all! phpmyadmin is okay for small database but not for huge databases with several MB or GB.

You have other tools on a server to handle the import.

a) If you have a server admin system like Plesk, use there database import tool.

b) use ssh commands to make database dump or to write databases directly in mysql via ssh. Commands below.

Create a database dump:

mysqldump DBname --add-drop-table -h DBhostname -u DBusername -pPASSWORD > databasefile.sql

Write a database to mysql:

mysql -h rdbms -u DBusername -pPASSWORD DBname < databasefile.sql

Aaberg

Answer 15

Best solution for this error when i tried some points. Follow this steps to solve this issue:

1. locate the file [XAMPP Installation Directory]\php\php.ini (e.g. C:\xampp\php\php.ini)
2. open php.ini in Notepad or any Text editor
3. locate the line containing max_execution_time and
4. increase the value from 30 to some larger number (e.g. set: max_execution_time = 90)
5. then restart Apache web server from the XAMPP control panel

Yancy

Answer 16

Changing the max_execution_timeout in php.ini. may help with maximum execution error. but sometimes the database is imported correctly but still it shows maximum execution time error, it can be due to some error of xamp.

I was experiencing the same error even after making all changes in php.ini. which is mentioned above and realized that all the things, tables of database were imported and it was working fine but it was still showing the max_execution_timeout error.

Pickings

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Amphitropous

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Admiration

Answer 17

100% Working Solution

I'm using Xampp:

Step1: open my.ini file from Xampp control panel config option and update below env Variable

myisam_sort_buffer_size=8M

To

myisam_sort_buffer_size=1024M

Navigate to your Xampp folder

C:\xampp\mysql\bin>

Step2: Copy and Paste SQL file you want to import and execute below command in Terminal,

mysql -u user -p test < database.sql

Here user is the user of your Database (i.e root) and test is the database name you created in PHPMyAdmin and after < you have to place your sql file path (in my case there is same directory so i'm placing only name of the file which going to import in our Database).

Hope this Solution Fixed your Problem

Loser

If above solution would not work in any case you can also use alternate solution like ------------------------------------> SET GLOBAL max_allowed_packet=1073741824; SET GLOBAL wait_timeout = 600; SET GLOBAL net_read_timeout = 600; SET GLOBAL connect_timeout = 600; <------------------------------------ Above commands you can directly hit on your MYSQL Query. – Loser

Answer 18

use mysql workbench , it will import quickly , and will not ask any time error .

Champignon

Answer 19

You can increase the limit:

ini_set('max_execution_time', 3000);

(Note that this script can cause high memory usage as well, so you probably have to increase that as well)

Other possible solution: Chunk your sql file, and process it as parts. I assume, it is not one big SQL query, is it?

Update: As @Isaac pointed out, this is about PHPMyAdmin. In this case set max_execution_timeout in php.ini. (The location depends on your environment)

Strainer

Since the poster is using phpMyAdmin, I don't think opening the source code and finding the best place to put that line is an ideal solution. – Ragman

Answer 20

The following might help you:

ini_set('max_execution_time', 100000);

And in your mysql - max_allowed_packet=100M in some cases where queries are too long sql also produce and error "MySQL server has gone away";

Change the values to whatever you need.

Armpit

for some reason he gives me the same error. Fatal error: Maximum execution time of 300 seconds exceeded in C:\xampp\phpMyAdmin\libraries\dbi\DBIMysqli.class.php on line 285. Even though i have changed it to 100000 – Judaize

there there are 2 other possibility, your script doesnt have permission to change that, 2. the script DBIMysqli.class.php, overrides your max_execution_time. – Armpit

in phpadmin, there is an option "Allow the interruption of an import in case the script detects it is close to the PHP timeout limit. (This might be a good way to import large files, however it can break transactions.)" if turned of might it help ? – Judaize

Try turning it off and see – Armpit

Since the poster is using phpMyAdmin, I don't think opening the source code and finding the best place to put that line is an ideal solution. – Ragman