Bug #19996 mysqlimport needs "ignore foreign keys" option
Submitted: 22 May 2006 10:59 Modified: 16 Nov 2009 10:49
Reporter: Johan Idrén Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:4.0 and up OS:Any (All)
Assigned to: CPU Architecture:Any
Tags: Contribution
Triage: Triaged: D5 (Feature request)

[22 May 2006 10:59] Johan Idrén
Description:
When using mysqlimport there is no way to tell the server to ignore foreign keys, resulting in a lot of preparation when importing to respect the data hierarchy.
This makes mysqlimport quite a lot less useful than it could be (and since it should be a lot faster than a regular dump from mysqldump, that's no fun).

How to repeat:
create table master_table
(
   funky_id                       int                            not null,
   some_data                      varchar(255),
   primary key (funky_id)
)
type = InnoDB;

create table other_data
(
   funky_id                       int                            not null,
   other_data                     varchar(255),
   primary key (funky_id),
   CONSTRAINT 
)
type = InnoDB;

alter table other_data add constraint funky_key foreign key (funky_id)
      references master_table (funky_id);

Then use mysqlimport to import data and be annoyed that you have to insert data into master_table first, even though you know your data is perfect.

Suggested fix:
Allow mysqlimport to ignore foreign keys while importing data with some spectacular option, perhaps --ignore-foreign-key-checks.
[23 May 2006 9:50] Valeriy Kravchuk
Thank you for a reasonable feature request.
[31 May 2007 12:25] Hasan Ceylan
Hi,

I was an immediate need for this feature (also for the mysql command line client).

I am sending a patch that I hope makes it way into the source repo. 
Patch is againist 5.0.27 version.

Regards,

Hasan Ceylan
[31 May 2007 12:26] Hasan Ceylan
patch to allow -k --ignore-foreign-keys switch to mysql command line client

Attachment: mysql.cc.patch (application/octet-stream, text), 1.76 KiB.

[31 May 2007 12:27] Hasan Ceylan
patch to allow -k --ignore-foreign-keys switch to mysqlimport command line tool

Attachment: mysqlimport.c.patch (application/octet-stream, text), 1.39 KiB.

[10 Aug 2007 13:20] Sveta Smirnova
Bug #12242 was marked as duplicate of this one.
[3 Oct 2007 17:17] Hasan Ceylan
Any chance this fix will go main stream?
[3 Jul 2008 14:45] Hasan Ceylan
Is this bug in progress, in need of more information or testing or in need of someone to take care?

Can someone please comment...

regards
Hasan Ceylan
[19 Feb 2009 0:30] Michael Tibben
This problem has just caught me out...

This bug reported was 3 years ago, and a simple patch supplied 2 years ago.

Is there any reason this has not been implemented ? 
If not, I would appreciate if this could be applied to the next point release of mysql 5.1
[19 Feb 2009 3:12] Hasan Ceylan
Funny, isn't it.

You've hit an interesting one.

I contributed the patch. But I hate to see it still standing on a hard drive. I made a complaint to MySQL about that. We had several conversations on the phone. But such a simple and valuable feature never went stream line.

This reflects what mysql have become. Before and after the acquisition....

Hasan Ceylan
[13 Jul 2009 15:08] liz drachnik
Hello Hasan - 

 In order for us to continue the process of reviewing your contribution
 to MySQL - We need you to review and sign the Sun|MySQL contributor
 agreement (the "SCA")

 The process is explained here:
 http://forge.mysql.com/wiki/Sun_Contributor_Agreement

 Getting a signed/approved SCA on file will help us facilitate your
 contribution-- this one, and others in the future.

 Thank you !

 Liz Drachnik  - Program Manager - MySQL
[2 Oct 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[4 Nov 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[25 Apr 2010 14:10] Mark Callaghan
Another patch for this is at http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/3459

The change is trivial
[16 Nov 2011 20:14] Raghavendra Prabhu
Is there a use-case for this option ? Since mysqldump automatically adds 
"
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
"
to the dump file.
[15 May 2012 21:19] Jeff Van Boxtel
A use case for this is when using mysqlimport to import a tab or comma separated values file. (For example a file created using mysqldump with the --tab option)

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0

Accepting one of the patches would be much appreciated.
[7 Jun 2012 15:24] Dan V
Happy 6th anniversary, bug. Does Oracle suppose this might be a reality anytime soon?
[10 Jun 2013 18:51] Travis Johnson
Bump. This is a very easy addition that would save a LOT of headaches. What needs to be done to make this happen? Since the original author of the patch hasn't responded to the request to sign the agreement, would a similar patch by a different author who WOULD do so help?
[15 Jan 2014 17:53] Tuomas Laatikainen
You can always temporarily set the global system variable 'foreign_key_checks" off by command line before running database creating script.

See example:
http://www.hackyours.com/2014/01/import-mysql-dump-folder-with-script.html
[19 May 2016 21:39] De Veloper
Happy 10th anniversary, bug! :)

When loading large data sets into AWS RDS, the mysqlimport tool is one of the best options, however RDS does not grant SUPER privileges, so no "SET GLOBAL variable" statements are allowed. 

Thus, this minor feature request would be a valuable addition.