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

Question

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

score 127 · Answer 1 · answered Dec 24 '15 at 00:05

127

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

answered Dec 24 '15 at 00:05

candle

1,963
3
15
24

6

For Mac it is "/Applications/XAMPP/etc/php.ini" & "/Applications/XAMPP/etc/my.cnf" and restart the server after above changes. – Raj Pawan Gumdal Feb 24 '17 at 18:05
3

see also [PhpMyAdmin Script timeout](https://stackoverflow.com/a/46175484/8740349) (because once this error is gone you may get that) – Top-Master Jun 12 '19 at 10:54
3

Super advise candle, I would just add to restart services once changes have been made. – Dupls Jan 24 '20 at 02:57
For me, it's always the max_allowed_packet that makes all the difference. By default, it is usually only set at 1 MB. – John Jul 26 '22 at 16:51
Just thought I would add that sometimes I need to add innodb_log_file_size = 256M to my MySQL my.ini file – John Jul 26 '22 at 17:38

score 48 · Answer 2 · edited Jul 08 '19 at 21:09

48

Goto XAMPP control panel>
Stop Apache and MySql services>
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
Click on Config button of MySql and select my.ini and change:
max_allowed_packet = 1024M
Again Start the services and try.. i hope it will work.

edited Jul 08 '19 at 21:09

Prasad Gayan

1,424
17
26

answered Jun 02 '16 at 11:24

Santosh Kumar Sahu

481
4
3

3

I spent hours trying to fault find this and finally realised that the max_allowed_packet in my.ini (or my.cnf in my case with MAMP) needs to appear under [mysqld]. For example: [mysqld] max_allowed_packet=2048M – Must Impress Mar 24 '20 at 16:27
1

For WAMP, you can set these values by clicking on the WAMP tray icon -> PHP -> PHP Settings, then scrolling up to the relevant setting. Click to change its value. You'll need to do this 1 at a time, and the service will auto-restart after each setting is changed. Do the same for WAMP tray icon -> MySQL -> MySQL Settings. For good measure, I then clicked Restart All Services, even though the services were reset each time a setting was changed. – SherylHohman Aug 25 '20 at 20:33
Thanks, it helped me today with the same problem, kudos :) – P.Davide Sep 30 '22 at 16:42

score 6 · Answer 3 · answered Dec 20 '20 at 11:19

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. :):):)

score 5 · Answer 4 · edited Aug 29 '20 at 06:38

5

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

edited Aug 29 '20 at 06:38

David Buck

3,752
35
31
35

answered Aug 29 '20 at 03:50

Ashraful Islam

51
1
4

This worked for me! Just that on my wamp the my.ini file was in C:\wamp64\bin\mariadb, under the right mariadb version, hope it helps out. – Shmuel Jun 29 '21 at 10:40

score 4 · Answer 5 · answered Jul 23 '19 at 07:38

Just specifying the above answer from Candle.

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

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.

score 3 · Answer 6 · answered Feb 01 '22 at 12:24

3

The solutions provided here (those referring to the my.conf settings) did not resolve the problem in my case (at least not alone). It seems that there are multiple causes for the error. One is due to wrong phpMyAdmin export settings. What helped me was to check the option "IF NOT EXITS" during the export in phpMyAdmin (use "custom" settings).

Found the solution here: https://stackoverflow.com/a/70562262/7647311

answered Feb 01 '22 at 12:24

Kloker

499
4
14

This worked for me...All other solution (like editing max values in php.ini or my.ini didn't work for me) – Mehbub Rashid Jul 02 '22 at 16:39

score 2 · Answer 7 · answered Jan 14 '16 at 16:30

2

I had the same problem when trying to import a Wordpess MySQL dump to a new server. In the end after an hour debugging I had to manually remove the following tables from the .sql file:

wp_cf7dbplugin_st wp_cf7dbplugin_submits

There were some really large values (possibly PDF files) stored in some of the fields which were causing a problem.

Imported fine after I removed the tables from the script.

answered Jan 14 '16 at 16:30

pixelkicks

866
2
11
23

In my case, three rows of two of the tables in the .sql had this problem. After I deleted these rows I imported fine. Thanks! – jperezmartin Jun 02 '20 at 01:11
@jperezmartin and pixelkicks: How did you find out which tables were the problem? – SherylHohman Aug 25 '20 at 20:43

Luis Gouveia · Answer 8 · 2020-07-13T11:28:02.300

If you are running Wamp, just do the following:

Edit C:\wamp64\bin\mysql\mysql5.7.26\my.ini
Increase the max packet size to 1024: max_allowed_packet = 1024M

If after this you run into the error

Script timeout passed, if you want to finish import, please resubmit the same file and import will resume

Then do the following:

Go to C:\wamp64\alias\phpmyadmin.conf and increase the following values:

php_admin_value max_execution_time 360

php_admin_value max_input_time 360

Credits to RiggsFolly (https://stackoverflow.com/a/20364163/3231884) on the 3rd (optional) step

score 1 · Answer 9 · answered Apr 27 '21 at 17:35

1

for me this issue resolved with

SQL compatibility mode: ANSI

I changed above to ANSI and this worked smoothly.
I've already set these..

max_execution_time = 120
max_input_time = 120
memory_limit = 512M
post_max_size = 64M

File was huge... so reimporting same file a few times imported everything smoothly.

answered Apr 27 '21 at 17:35

MFarooqi

1,004
5
12
26

1

Please, be more specific as to where you changed the `SQL compatibility mode: ANSI` setting. – Casper May 29 '22 at 09:35

score 0 · Answer 10 · answered Apr 10 '20 at 05:05

For me my main goal was to make a stage site for my wordpress site, which had wordfence on it.

I was using plesk obsidian and had to update mariadb to get past the error in the main question here Error (near “ON” at position 25) while importing Table for WORDPRESS ( A foreign key Error) which I was able to do by reading this blog article - https://support.plesk.com/hc/en-us/articles/213403429-How-to-upgrade-MySQL-5-5-to-5-6-5-7-or-MariaDB-5-5-to-10-0-10-1-10-2-on-Linux-

That took forever but got me passed the above error, but then I was getting this error on db import:

#1062 - Duplicate entry '\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xEF\xBF\xBD\xEF\xBF\xBD' for key 'PRIMARY' when find and replace domain on wordfence

I was doing the domain find and replace in vscode "mydomain.com" with "stage.mydomain.com". The problem turned out to be that wordfence saves binary data or something to the database, and just opening it in vscode and saving it was altering that somehow, and thus throwing obscure duplicate entry error for primary key

The fix for the above was to not open, or alter, the .sql file in vscode but to do the find in replace with linux. This was the commands I ran to do this on Mac -

find and replace http: with https: -

perl -pi -w -e 's/http:\/\/mydomain.com/https:\/\/mydomain.com/g;' my_db_file.sql

find and replace mydomain.com with stage.mydomain.com -

perl -pi -w -e 's/mydomain.com/stage.mydomain.com/g;' my_db_file.sql

Demetrius Lewis · Answer 11 · 2021-01-04T14:25:39.630

0

I would double check and make sure that you are import a compressed file and not the plain .sql file. use (gzip, bzip2, zip) and in your ini file adjust your max_allowed_packet from 1M to 5M or 10M.

Hopefully this helps someone.

edited Jan 04 '21 at 14:25

answered Jan 04 '21 at 13:38

Demetrius Lewis

23
5

score 0 · Answer 12 · answered May 07 '21 at 22:20

0

After long time, i finally uninstall MAMP and try XAMPP. It worked. I don't know where was the problem i edit all the limits (created my.cnf with another values) but no success. With XAMPP those answers works...

answered May 07 '21 at 22:20

Daniel Malachov

1,604
1
10
13

score 0 · Answer 13 · answered Sep 21 '21 at 15:19

0

I had the same problem and the solution was to increase the ram memory of the database. I was using a docker container.

answered Sep 21 '21 at 15:19

Márlon Assis

1

score 0 · Answer 14 · answered Jan 24 '22 at 00:41

0

Disable the foreign key checks on import page of phpmyadmin and then, try to import again

answered Jan 24 '22 at 00:41

Angelus Roman

191
2
11

score 0 · Answer 15 · answered Jan 25 '22 at 15:02

I have the same issue and none of the above answers helped me out sadly. I post this because it may help someone one day. The database dump i deal with is made via phpmyadmin, and it does not work maybe for some corrupted data in large database files. The the solution for me is to dump the database via mysqldump and import it via source cmd under mysql. it worked without any warnings or errors. (i have not the time to dig why the phpmyadmin solution does not work as expected).

Hopfully this may help anybody out there.

cmd examples :

EXPORT : mysqldump -u db_user -p db_name > dump_file.sql

IMPORT : mysql> source dump_file.sql

Klaassiek · Answer 16 · 2022-12-02T14:14:15.113

0

Apart from the changes to php.ini and my.ini, I also had to add the following line in the phpadmin configuration file. Using XAMPP on Windows, the file is here:

C:/xampp/phpMyAdmin/config.inc.php

Add the following line to set the time limit to infinite:

$cfg['ExecTimeLimit'] = 0;

edited Dec 02 '22 at 14:14

answered Nov 28 '22 at 15:35

Klaassiek

2,795
1
23
41

score -1 · Answer 17 · answered Mar 26 '22 at 14:37

-1

I'm using Xampp. I tried all the solutions given here without success. In the end I rebooted my Mac and the import worked straight away.

answered Mar 26 '22 at 14:37

user1179501

100
6

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

17 Answers17

Linked