Menu

#469 (ok 3.2) Incorrect parsing of constraints in ALTER TABLE

closed-accepted
None
1
2009-06-15
2009-04-10
No

The SQL parser seems to analyse incorrectly the alter queries designed to add a new foreign key to a table.
After using API to analyse a request as follows :
$parsed_sql = PMA_SQP_parse($myQuery);
$sql_info = PMA_SQP_analyze($parsed_sql);

The sample query is :
ALTER TABLE `local_table2`
ADD CONSTRAINT `fk_local_table_foreign_table` FOREIGN KEY (`foreign_table_id_foreign_table`) REFERENCES `foreign_table2` (`id_foreign_table`) ON DELETE NO ACTION ON UPDATE NO ACTION;

The $sql_info contains the description of the ALTER, but a dump of the "foreign_key" part outputs :
[foreign_keys] => Array
(
[0] => Array
(
[constraint] => fk_local_table_foreign_table
[index_list] => Array
(
[0] => foreign_table_id_foreign_table
)

[ref_table_name] => id_foreign_table
[on_delete] => NO_ACTION
[on_update] => NO_ACTION
)

)

Note that the [ref_table_name] is the table key, it should contain in fact "foreign_table2"
After analysing the sqlparse.inc.php, I supposed this happens because ALTER queries have one less bracket level compared to CREATE queries.
I tried to correct the script,
On line 1758 I added :
if ($upper_data == 'ALTER') {
$seen_alter = TRUE;
}

On line I 1927 changed
if ( $brackets_level > 0) {
$foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
with :
if ($seen_alter && $brackets_level > 0) {
$foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
} elseif ($brackets_level > 1) {
$foreign[$foreign_key_number]['ref_index_list'][] = $identifier;

Now with the same query I have the following output :
[foreign_keys] => Array
(
[0] => Array
(
[constraint] => fk_local_table_foreign_table
[index_list] => Array
(
[0] => foreign_table_id_foreign_table
)

[ref_table_name] => foreign_table2
[ref_index_list] => Array
(
[0] => id_foreign_table
)

[on_delete] => NO_ACTION
[on_update] => NO_ACTION
)

)

Which is correct.
Is it the right way to correct this bug ? Is the bug due to some mis-use of the API ?

Discussion

  • Marc Delisle

    Marc Delisle - 2009-04-11
    • assigned_to: nobody --> lem9
    • status: open --> pending
     
  • Marc Delisle

    Marc Delisle - 2009-04-11

    I am curious: where do you find a sqlparse.inc.php in phpMyAdmin?

    Also, which action are you doing in phpMyAdmin that would trigger this bug?

     
  • Yannick Betemps

    Yannick Betemps - 2009-04-14

    oups my apologies, the file is :
    libraries/sqlparser.lib.php

    Actually I used the sqlparser api for another personal purpose, to analyse queries. However I assume this bug would appear if I tried to paste or import a sql query containing foreign keys in an "ALTER" sequence.

     
  • Marc Delisle

    Marc Delisle - 2009-04-15
    • status: pending --> open
     
  • Marc Delisle

    Marc Delisle - 2009-04-19

    Ok, we also needed to initialize your $seen_alter to false. Patch accepted.

     
  • Marc Delisle

    Marc Delisle - 2009-04-19
    • milestone: 896875 -->
    • labels: 438488 -->
     
  • Marc Delisle

    Marc Delisle - 2009-04-19

    Merged in subversion, thanks.

     
  • Marc Delisle

    Marc Delisle - 2009-04-19
    • priority: 5 --> 1
    • summary: sql parser bug on constraint analyses in alter queries --> (ok 3.2) Incorrect parsing of constraints in ALTER TABLE
    • status: open --> open-accepted
     
  • Marc Delisle

    Marc Delisle - 2009-06-15
    • status: open-accepted --> closed-accepted
     
MongoDB Logo MongoDB