Issue Details (XML | Word | Printable)

Key: DBAL-34
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Benjamin Eberlei
Reporter: Ting Wang
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Doctrine DBAL

MySql getListTableForeignKeysSQL doesn't work for 5.0.xx

Created: 19/Jul/10 04:36 AM   Updated: 15/Aug/11 10:00 AM
Component/s: Schema Managers
Affects Version/s: 2.0.0-BETA2
Fix Version/s: 2.0.0-BETA3

Environment:
MySQL Server version: 5.0.51a-24+lenny4 (Debian)


 Description  « Hide
The sql contains mysql specific code. And for the mysql 5.0.xxx the sql statement has syntax error.

in /Doctrine/DBAL/Platforms/MySqlPlatform.php:
public function getListTableForeignKeysSQL($table, $database = null)
{
$sql = "SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, ".
"k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ ".
"FROM information_schema.key_column_usage k /*!50116 ".
"INNER JOIN information_schema.referential_constraints c ON ".
" c.constraint_name = k.constraint_name AND ".
" c.table_name = '$table' */ WHERE k.table_name = '$table'";

if ($database) { $sql .= " AND k.table_schema = '$database' AND c.constraint_schema = '$database'"; }

$sql .= " AND `REFERENCED_COLUMN_NAME` is not NULL";

return $sql;
}

For the mysql lower as 5.1.16 the SQL could be as the following:

SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` FROM information_schema.key_column_usage k WHERE k.table_name = 'some_table' AND k.table_schema = 'some database' AND c.constraint_schema = 'some database' AND `REFERENCED_COLUMN_NAME` is not NULL

In this statement there is no reference of c



 All   Comments   Change History   FishEye      Sort Order: Ascending order - Click to sort in descending order
Benjamin Eberlei added a comment - 27/Jul/10 04:36 PM
Fixed

Aigars Gedroics added a comment - 15/Aug/11 08:55 AM
Pity that because of this the Doctrine schema-tool update action reports incorrect change list.
It tries to drop/add foreign keys because "ON DELETE CASCADE" option isn't read from the database at all.

Benjamin Eberlei added a comment - 15/Aug/11 10:00 AM
5.0.x has no way to export the CASCADE details. Its just not possible to get this data in 5.0