Import SQL Database to local Local PHPMyAdmin

Submitted by D2D on Sun, 05/03/2020 - 17:19

I was unable to import a SQL file into my local XAMPP setup for testing and possible use as a staging site.  I was able to get past the first few errors with some tweaks to the php.ini file, but I was still getting the following error:

1 errors were found during analysis.

 

Unrecognized keyword. (near "ON" at position 25)

 

SQL query: Edit Edit

 

SET FOREIGN_KEY_CHECKS = ON;

 

MySQL said: Documentation

 

#2006 - MySQL server has gone away

 

I tried several tweaks until I finally found something that worked:

Make the following changes in the XAMPP configuration files

In XAMPP\php\php.ini

Imax_execution_time = 600
max_input_time = 600
memory_limit = 1024M
post_max_size = 1024M

 

In XAMPP\mysql\my.ini (click configure button on MYSQL line in XAMPP Control Panel)

max_allowed_packet = 1024M

innodb_log_file_size=512M
innodb_log_buffer_size=512M
innodb_lock_wait_timeout=28800

 

Don’t forget to Stop and Start the Apache Server and the MYSQL server in the XAMPP Control Panel.

After saying a lot of bad words and repeatedly Googling the error, I found the answer on these 2 sites.

https://stackoverflow.com/questions/33974492/error-near-on-at-position-25-while-importing-table-for-wordpress-a-foreign

https://matomo.org/faq/troubleshooting/faq_183/

 

 

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.