#173 (in 2.5.5) Fix export of foreign keys

[Kris Ven]

When importing a database with InnoDB tables, most of
the times the tables aren't sorted correctly, so that
some foreign key constrains fail.

Starting from version 3.23.52 and 4.0.3, MySQL supports
the "FOREIGN_KEY_CHECKS" variable. If this is set to 0
(zero), no checks are performed when importing a
database. This means the tables can be sorted in random
order.

This patch adds an option box in the "Export SQL"
window that indicates whether you want to disable the
foreign key check. If the option was selected, "SET
FOREIGN_KEY_CHECKS=0;" will be added to the output.

In attachement, you can find a patch against the CVS
repository.

[Kris Ven]

Patch file

[Michal Čihař]

Logged In: YES
user_id=192186

Foreign keys are since 2.5.4 created after creating tables,
so this is IMHO not needed.

[Kris Ven]

Logged In: YES
user_id=590508

This doesn't seem to work with a database dump I have here
(that's why I wrote the patch), but I will look into that
and file a bug report if required.

I thought it would be usefull to be able to include this
setting to a dump file, if you need to import this file on a
server where you don't have PMA installed. At work for
example, I construct a database with PMA, but once it's
done, a dump file gets scp'ed to to server to be imported at
the command line.

[Michal Čihař]

Logged In: YES
user_id=192186

Could you post how does your dump that doesn't work look like?

[Kris Ven]

Logged In: YES
user_id=590508

I further looked into the problem. When I export a database
from server A (PMA 2.5.4, Mysql 4.0.15, PHP 4.3.3), the
foreign keys are placed at the end of the dump.

When I do the same for server B (PMA 2.5.4, Mysql 4.0.12,
PHP 4.3.2), the foreign keys are *not* placed at the end of
the file. Presumably because they don't contain CONSTRAINT
before the FOREIGN KEY clause.

A simplified test case (taken from the MySQL manual) that
fails on server B follows. I will try this test case on
server A tomorrow, and I will post the results. Maybe this
is due to the difference in version number of MySQL.

SQL commands:

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id))
TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind
(parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE SET NULL
) TYPE=INNODB;

Output of PMA export:

CREATE TABLE `child` (
`id` int(11) default NULL,
`parent_id` int(11) default NULL,
KEY `par_ind` (`parent_id`),
FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON
DELETE SET NULL
) TYPE=InnoDB;

CREATE TABLE `parent` (
`id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=InnoDB;

The import obviously fails since child comes before parent.

[Kris Ven]

Logged In: YES
user_id=590508

Ok, on server A, this example works correctly. It seems that
on server A the SHOW CREATE TABLE command prefixes the
FOREIGN KEY clause with CONSTRAINT `some_identifier`, while
on server B this is not the case. Presumably this is a known
bug in MySQL?

The PMA export output as illustration follows:

CREATE TABLE `child` (
`id` int(11) default NULL,
`parent_id` int(11) default NULL,
KEY `par_ind` (`parent_id`)
) TYPE=InnoDB;

CREATE TABLE `parent` (
`id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=InnoDB;

ALTER TABLE `child`
ADD CONSTRAINT `0_156` FOREIGN KEY (`parent_id`)
REFERENCES `parent` (`id`) ON DELETE SET NULL;

[Michal Čihař]

Logged In: YES
user_id=192186

Could you plese try attached patch, whether it fixes it?

[Michal Čihař]

Fix for FIREIGN KEY detection

[Kris Ven]

Logged In: YES
user_id=590508

The attached patch didn't work. I attached testcase2.sql
which is a more extended example. When multiple FOREIGN
KEY's exist for a table, they are not displayed correctly
(see patch1.sql for the output).

When I looked at the output of SHOW CREATE TABLE for the
table 'child2' in this testcase, it appeared that the
CONSTRAINT clause was deleted from the output, although it
was present in the testcase2.sql file (on server B):

CONSTRAINT `0_90` FOREIGN KEY (parent3_id) REFERENCES
parent3(id) ON DELETE SET NULL

became:
FOREIGN KEY (parent3_id) REFERENCES parent3(id) ON DELETE
SET NULL

I wrote a new patch (sql-export-2.patch) that works in my
situation (see output in patch2.sql). I couldn't test it yet
on server A.

[Kris Ven]

Extended test case

[Kris Ven]

Output when using sql-export.patch

[Kris Ven]

New fix for sql.php

[Kris Ven]

Output when using sql-export-2.patch

[Michal Čihař]

Fix for FIREIGN KEY detection, third attempt

[Michal Čihař]

Logged In: YES
user_id=192186

I tried to fix my patch, because your one complicates code
too much. Please try it.

[Kris Ven]

Fix for FOREIGN KEY detection, fourth attempt

[Kris Ven]

Logged In: YES
user_id=590508

I'm not that familiar with regular expressions, hence my
approach.

patch3 works for the most part, except that the first 'ADD
...' in the ALTER TABLE statement is missing:

#
# Constraints for table `child`
#
ALTER TABLE `child`
FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON
DELETE SET NULL;

#
# Constraints for table `child2`
#
ALTER TABLE `child2`
FOREIGN KEY (`parent3_id`) REFERENCES `parent3` (`id`) ON
DELETE SET NULL,
ADD FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
ON DELETE SET NULL,
ADD FOREIGN KEY (`parent2_id`) REFERENCES `parent2` (`id`)
ON DELETE SET NULL,
ADD FOREIGN KEY (`parent4_id`) REFERENCES `parent4` (`id`)
ON DELETE SET NULL;

Patch4 should resolve this. I added an extra line that adds
the first 'ADD' clause. It also prefixes the string with the
required number of spaces (those spaces are deleted from the
original output with ltrim in the line afterwards). This
preserves the indentation of the command.

I still have to check if this works on server A (with
CONSTRAINT clauses), but it appears to be working very well
on server B (with FOREIGN KEY clauses).

[Kris Ven]

Logged In: YES
user_id=590508

Verified on server A+B: sql-export-4.patch works correctly.
sql-export-3.patch still has the problem that the first ADD
is missing from the output.

[Michal Čihař]

Logged In: YES
user_id=192186

Your fix for adding spaces didn't remove spaces after ADD,
so it didn't look well.

So I fixed the regular expressions and here is one more
patch revision :-). I don't know how could I overlook
missing ADD with previous patch :-)

[Michal Čihař]

Fix for FOREIGN KEY detection, fivth (and I hope last) attempt

[Michal Čihař]

Logged In: YES
user_id=192186

I put last patch into CVS as I beleive it is okay :-)