Error (near "ON" at position 25) while importing Table for WORDPRESS ( A foreign key Error)

I try to import a large file to my Database (WordPress), it shows some error like this:

Error

Static analysis:

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 had the same error when I tried to migrate Drupal database to a new local apache server(I am using XAMPP on Windows machine). Actually I don't know the meaning of this error, but after trying steps below, I imported the database without errors. Hope this could help:

Changing php.ini at C:\xampp\php\php.ini

max_execution_time = 600
max_input_time = 600
memory_limit = 1024M
post_max_size = 1024M

Changing my.ini at C:\xampp\mysql\bin\my.ini

max_allowed_packet = 1024M

  1. Goto XAMPP control panel>
  2. Stop Apache and MySql services>
  3. Click on Config of Apache>select php.ini and change the followings:
    max_execution_time = 600
    max_input_time = 600
    memory_limit = 1024M
    post_max_size = 1024M

  4. Click on Config button of MySql and select my.ini and change:
    max_allowed_packet = 1024M

  5. Again Start the services and try.. i hope it will work.

This happens because of the importing connectivity issue with phpmyadmin when you import large DB, here is how you can import large data base to the phpmyadmin. First change the following files according to your DB size.


Changing php.ini at C:\xampp\php\php.ini

max_execution_time = 600
max_input_time = 600
memory_limit = 1024M
post_max_size = 1024M

Changing my.ini at C:\xampp\mysql\bin\my.ini

max_allowed_packet = 1024M

then run the shell from xampp control panel and enter the following command.

#mysql -p -u root DBname < c:\xampp\DBfolder\db.sql

EnterPassword: (usually it is blank).

then the process will start. :):):)


Just specifying the above answer from Candle.

You will need to adjust only one line in my.ini:

  1. Changing my.ini at C:\xampp\mysql\bin\my.ini OR click on config of XAMP>select php.ini and change the following:

    max_allowed_packet = 50M // the value depends on the size of the .sql files you are trying upload.