Webdesign, Tutorials und mehr - Webmasterpro.de

Import von .sql-Dateien: Problem mit FK-Constraints

Max B, am Montag den 01.03.2010 um 17:48:31

Hey Leute,

ich baue gerade einen Installer für ein System von mir. Um die Datenbank zu erstellen soll der Installer mitgelieferte SQL-Dateien importieren. Ich führe den Inhalt der Dateien mit mysqli_multi_query() aus.

Klappt soweit auch ganz gut. Nur machen die Foreign-Key-Constraints (InnoDB) Stress. Hatte damit bisher nie Probleme - hab bisher die .sql-Datei immer per phpMyAdmin importiert.

Doch kaum importiere ich die mit mysqli_multi_query(), kann PHP die Tabellen wegen FK-Fehler nicht erstellen.

Hier ein simples Bsp:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

SET storage_engine=INNODB;
SET collation_database=utf8_unicode_ci;
SET character_set_database = utf8;

CREATE TABLE pages (
       pageID INT NOT NULL AUTO_INCREMENT
     , pageName VARCHAR(255) NOT NULL
     , pageTitle VARCHAR(255)
     , permaLink VARCHAR(255) NOT NULL
     , oldPermaLink VARCHAR(255)
     , description VARCHAR(255)
     , isCategory TINYINT(1) DEFAULT 0
     , status INT(1) NOT NULL
     , PRIMARY KEY (pageID)
);
ALTER TABLE pages COMMENT='Contains all content pages';
ALTER TABLE pages MODIFY COLUMN description VARCHAR(255)
      COMMENT 'Is used for the meta tag "description" (therefore only 255 characters)';

CREATE TABLE page_features (
       pageFeatureID INT NOT NULL AUTO_INCREMENT
     , parentPageFeatureID INT
     , pageID INT NOT NULL
     , featureID INT NOT NULL
     , areaID INT NOT NULL
     , rank INT NOT NULL DEFAULT 0
     , parameters TEXT
     , status INT(1) NOT NULL
     , PRIMARY KEY (pageFeatureID)
);
ALTER TABLE page_features COMMENT='Stores the features that are currently installed on a page';
ALTER TABLE page_features MODIFY COLUMN parentPageFeatureID INT
      COMMENT 'ID of the PageFeature this PageFeature inherits from';

ALTER TABLE page_features
  ADD CONSTRAINT FK_page_features_pageID
      FOREIGN KEY (pageID)
      REFERENCES pages (pageID);

Die Fehlermeldung sagt "Can't create table 'mec-cms.page_features' (errno: 150)"

Wenn ich in phpMyAdmin SHOW ENGINE INNODB STATUS ausführe um mir den letzten FK-Fehler anzeigen zu lassen, kommt:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

LATEST FOREIGN KEY ERROR
------------------------
100301 11:41:41 
Error in foreign key constraint of table mec@002dcms/page_features:
There
 is no index in table "mec-cms"."page_features" where the columns appear
as
 the first columns. Constraint:

      FOREIGN KEY (pageID)
    
  REFERENCES pages (pageID)
See 
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for
 correct foreign key definition.

Also ist irgendwas mit den Indizes falsch. Aber warum funktioniert der Import in phpMyAdmin problemlos? phpMyAdmin korrigiert die Indizes wohl kaum, oder? Ehrlich gesagt wüsste ich aber auch nicht wie ich die SQL-Statements korrigieren müsste, damit's klappt.

Problem ist auch, dass der SQL-Code aus meinem Datenbank-Designer kommt. Den würde ich ungern manuelle korrigieren müssen...

Hat jemand ne Ahnung wie ich das hinkriegen kann?

Vielen Dank und viele Grüße!

Max B, am Montag den 01.03.2010 um 18:24:43

Ok, selbst das einfache Standardbeispiel aus der MySQL-Doku funktioniert nicht...

1
2
3
4
5
6
7
8

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

Fehler sagt wieder:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

LATEST FOREIGN KEY ERROR
------------------------
100301 12:24:53 
Error in foreign key constraint of table mec@002dcms/parent:
FOREIGN 
KEY (parent_id) REFERENCES parent(id)
                      ON DELETE
 CASCADE
) ENGINE=INNODB:
Cannot resolve column name close to:
)
 REFERENCES parent(id)
                      ON DELETE CASCADE
) 
ENGINE=INNODB

liegts vielleicht an mysqli_multi_query()? Geht das damit vllt nicht? Wär aber doch bescheuert...

Max B, am Montag den 01.03.2010 um 20:04:16

Sorry dass ich hier soviel nachträglich poste.

Aber ich habe gerade das Beispiel für mysqli_multi_query() mit dem einfachen Beispiel aus der MySQL-Doku aufgesetzt.

Klappt immer noch nicht!

Also sieht's definitiv nach nem konzeptionellen Fehler aus. Warum klappt das nicht?

Hier kann's jeder mal testen:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

<?php
$link = mysqli_connect("localhost", "benutzer", "passwort", "datenbankname");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;";

/* execute multi query */
echo "sending query";
if (mysqli_multi_query($link, $query)) {
    echo "start processing multi-query";
    do {
        /* store first result set */
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        else
            echo "no result returned";

        /* print divider */
        if (mysqli_more_results($link)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($link));
}

/* close connection */
mysqli_close($link);
?>

Die Ausgabe ist eine weiße Seite mit "sending query" - also kommt er noch nichtmal in die while-Schleife.

Patrick Freitag, am Dienstag den 02.03.2010 um 00:44:01

Ähm ich hab das Schnippsel jetzt getestet. Bei mir funktioniert es tadellos. Sobald ich die Tabelle einmal angelegt habe kommt "sending query". Jetzt gehe ich davon aus, dass die Tabelle einfach schon existiert, die du eigentlich anlegen willst. ;)

Ansonsten, falls das nicht der Grund sein sollte, ändere mal den Code auf diesen hier:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

<?php
$link = mysqli_connect("localhost", "benutzer", "passwort", "table");

/* check connection */
if (!$link) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;";

/* execute multi query */
echo "sending query";
if (mysqli_multi_query($link, $query)) {
    echo "start processing multi-query";
    do {
        /* store first result set */
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        else
            echo "no result returned";

        /* print divider */
        if (mysqli_more_results($link)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($link));
// der else-zweig hier is wichtig, hier bekommst du die genaueren
// infos über den/die Fehler
} else {
  echo mysqli_error($link);
}

/* close connection */
mysqli_close($link);
?>

Max B, am Dienstag den 02.03.2010 um 00:49:10

Also bei mir kommt dann definitiv n Fehler.

Die Ausgabe ist: "sending queryCan't create table 'mec-cms.parent' (errno: 150)".

Kommt bei dir "start processing multi-query" und "no results returned"? Das muss kommen, weil CREATE keine results liefert.

Werden bei dir beide Tabellen erstellt? Inklusive FK-Constraints? Kannst du in phpmyadmin nachschauen.

Wenn alles ja, welche PHP/MySQL-Version setzt du ein? Momentan ist meine letzte Hoffnung dass es ein Bug in einem der beiden ist.

PS: Ich führe das Script auf einer komplett neuen DB aus - die Tabellen gibts definitiv nicht.

Jannik Zschiesche, am Dienstag den 02.03.2010 um 01:07:07

Nur eine Vermutung:

Es sieht ganz so aus, als ob die Tabellen während dem Parsing noch nicht erstellt sind und deswegen der FK-Constraint schiefgeht. Das Problem scheint behoben, wenn man die Queries hintereinander ausführt.

Hier wäre nun interessant zu wissen, wie mysqli_multi_query intern aufgebaut ist.

Max B, am Dienstag den 02.03.2010 um 03:01:27

Haha :D Es wird immer abstruser ey! :D

Die Idee mit dem Parsing ist gut! Ich habs deshalb mal auf 2 Aufrufe aufgeteilt:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

<?php
$link = mysqli_connect("localhost", "benutzer", "passwort", "datenbank");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$queries = array();

$queries[] = "CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child1 (id INT, parent_id INT
) ENGINE=INNODB;

CREATE TABLE child2 (id INT, parent_id INT
) ENGINE=INNODB;";

$queries[] = "ALTER TABLE child1
  ADD CONSTRAINT FK_child1
      FOREIGN KEY (parent_id)
      REFERENCES parent (id);

ALTER TABLE child2
  ADD CONSTRAINT FK_child2
      FOREIGN KEY (parent_id)
      REFERENCES parent (id);";

$i = 0;
foreach($queries as $query) {
    /* execute multi query */
    echo "<br>sending query " . $i . "<br>";
    if(mysqli_multi_query($link, $query)) {
        echo "start processing multi-query<br>";
        do {
            /* store first result set */
            if ($result = mysqli_store_result($link)) {
                while ($row = mysqli_fetch_row($result)) {
                    printf("%s\n<br>", $row[0]);
                }
                mysqli_free_result($result);
            }
            else
                echo "no result returned<br>";

            /* print divider */
            if (mysqli_more_results($link)) {
                printf("-------next result-------\n<br>");
            }
        } while (mysqli_next_result($link));
    }
    else {
      echo mysqli_error($link);
    }
    $i++;
}

/* close connection */
mysqli_close($link);
?>

Das Ergebnis ist:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

sending query 0
start processing multi-query
no result returned
-------next result-------

no result returned
-------next result-------

no result returned

sending query 1
start processing multi-query
no result returned
-------next result-------

Es werden also ALLE Statements ausgeführt! Nur beim letzten bricht er ab (es kommt kein "no result returned"). Und wenn ich in phpMyAdmin unter "Beziehungsübersicht" bei einer Tabelle gucke, sehe ich, dass nur child1 sein FK-Constraint bekommen hat. Child2 nicht.

SHOW ENGINE INNODB STATUS sagt wie oben:

Error in foreign key constraint creation for table `mec-cms`.`#sql-198_253`.
A foreign key constraint of name `mec@002dcms/FK_child2`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

Bei euch auch?

Dabei wird das Constraint doch nur EINMAL geadded! Warum soll das schon existieren?

Jannik Zschiesche, am Dienstag den 02.03.2010 um 23:01:50

Billig-Lösung: Trenne alles in wirklich einzelne Queries auf, und benutze das "normale" mysqli_query. Dann geht es auf jeden Fall.

Ich weiß leider überhaupt nicht, wie mysqli_multi_query intern arbeitet, sodass ich hier keine große Hilfe sein kann.

Max B, am Mittwoch den 03.03.2010 um 04:10:06

Ja das ist genau das Problem. Das will ich nämlich eigentlich nicht. Denn wenn ichs nach ";" aufsplitte würde ich evtl auch mitten in nem VARCHAR o.Ä. splitten.

Ich habe halt mehrere SQL-Dateien die sowohl Struktur als auch (Basis-)Daten in das System importieren sollen. Ich denke ich werd's so machen dass ich die Struktur anhand von ";" aufsplitte (weil es da mit ziemlicher Sicherheit kein ungewolltes ";" gibt) und die Daten dann mit multi_query reinhaue.

Trotzdem ziemlich ätzender Workaround

Edit: Ich denke ich werde es so wie hier machen, um wenigstens die dümmsten ";"-Fehler zu umschiffen. Das Regex sieht recht gut aus ;)

Jannik Zschiesche, am Mittwoch den 03.03.2010 um 10:46:21

Ich hab mal irgendwo im Netz ein (noch) längeres RegEx gefunden, das - wenn man den Kommentatoren Glauben schenken darf - wohl einwandfrei funktioniert hat. Wenn ich den Link wieder finde, werde ich ihn dir hier natürlich gleich schicken.