Newest version fail at detecting if migrations table exists on pgsql #99

Closed
kimhemsoe opened this Issue Oct 24, 2012 · 5 comments

Comments

Projects
None yet
2 participants
@kimhemsoe
Member

kimhemsoe commented Oct 24, 2012

@kimhemsoe

This comment has been minimized.

Show comment Hide comment
@kimhemsoe

kimhemsoe Oct 24, 2012

Member

@rhunwicks I cant reproduce the problem you describe. schema::listTableNames() returns all tables from alle schemas with tables under another schema prefixed with schema name.

I tested with DBAL 2.3.x and master

Member

kimhemsoe commented Oct 24, 2012

@rhunwicks I cant reproduce the problem you describe. schema::listTableNames() returns all tables from alle schemas with tables under another schema prefixed with schema name.

I tested with DBAL 2.3.x and master

@kimhemsoe

This comment has been minimized.

Show comment Hide comment
@kimhemsoe

kimhemsoe Oct 29, 2012

Member

@rhunwicks ping

Member

kimhemsoe commented Oct 29, 2012

@rhunwicks ping

@kimhemsoe

This comment has been minimized.

Show comment Hide comment
@kimhemsoe

kimhemsoe Nov 29, 2012

Member

@rhunwicks Is this stil a issue ?

Member

kimhemsoe commented Nov 29, 2012

@rhunwicks Is this stil a issue ?

@rhunwicks

This comment has been minimized.

Show comment Hide comment
@rhunwicks

rhunwicks Dec 3, 2012

The error still occurs with Migrations c1d875c and DBAL 22a30c9cece379eab619727c83e1859288f32f89 loaded via Composer today.

As noted in #94: the problem is specific to Postgresql and it occurs if the migrations table is in the default schema for the connection and you provide a schema-qualified name for it in the configuration. In that case, what happens is:

  1. Doctrine\DBAL\Migrations\Configuration\Configuration::createMigrationTable() is called to check if the table exists and create it if it doesn't
  2. That calls $this->connection->getSchemaManager()->tablesExist(array($this->migrationsTableName)), e.g. $this->connection->getSchemaManager()->tablesExist(array('my_schema.doctrine_migrations_table'))
  3. Doctrine\DBAL\Schema\AbstractSchemaManager::tablesExist() calls $this->listTableNames() to get the list of tables available
  4. Doctrine\DBAL\Schema\AbstractSchemaManager::listTableNames() gets the list of tables from the information schema as an array where each entry is an associative array of table_name and schema_name
  5. It then calls $this->getPortableTablesList() to collapse the arrays to strings
  6. getPortableTablesList() calls $this->_getPortableTableDefinition() which is overriden in PostgresqlSchemaManager
  7. Doctrine\DBAL\Schema\PostgresqlSchemaManager::_getPortableTableDefinition() compares the schema for the table with the search path for the connection. If the table schema is the first one in the search path, then it returns the bare table name, if it isn't then it returns the schema-qualified table name (i.e. schema.table). In this case the Migrations table is in the default schema, so _getPortableTableDefinition() returns doctrine_migrations_table
  8. Back in AbstractSchemaManager::tablesExist() it does an array_intersect but is looking for my_schema.doctrine_migrations_table and the table array contains doctrine_migrations_table so the array intersect misses it.
  9. Configuration::createMigrationTable() therefore thinks the table doesn't exist yet and tries to create it, but it does exist, so the table creation fails: ERROR: relation "doctrine_migration_versions" already exists

Given that the problem is caused by the definition of _getPortableTableDefinition() in PostgreSqlSchemaManager the least intrusive way to fix it seems to be to make sure that PostgreSqlSchemaManager::tablesExist also uses _getPortableTableDefinition to clean up the list of tables it is searching for:

    /**
     * Return true if all the given tables exist.
     *
     * @param array $tableNames
     * @return bool
     */
    public function tablesExist($tableNames)
    {
        foreach ($tableNames as $key => $tableName) {
            if (strpos($tableName, '.') !== false) {
                $tableName = explode('.', $tableName, 2);
                $tableNames[$key] = $this->_getPortableTableDefinition(array('schema_name'=>$tableName[0], 'table_name'=>$tableName[1]));
            }
        }
        return parent::tablesExist($tableNames);
    }

The error still occurs with Migrations c1d875c and DBAL 22a30c9cece379eab619727c83e1859288f32f89 loaded via Composer today.

As noted in #94: the problem is specific to Postgresql and it occurs if the migrations table is in the default schema for the connection and you provide a schema-qualified name for it in the configuration. In that case, what happens is:

  1. Doctrine\DBAL\Migrations\Configuration\Configuration::createMigrationTable() is called to check if the table exists and create it if it doesn't
  2. That calls $this->connection->getSchemaManager()->tablesExist(array($this->migrationsTableName)), e.g. $this->connection->getSchemaManager()->tablesExist(array('my_schema.doctrine_migrations_table'))
  3. Doctrine\DBAL\Schema\AbstractSchemaManager::tablesExist() calls $this->listTableNames() to get the list of tables available
  4. Doctrine\DBAL\Schema\AbstractSchemaManager::listTableNames() gets the list of tables from the information schema as an array where each entry is an associative array of table_name and schema_name
  5. It then calls $this->getPortableTablesList() to collapse the arrays to strings
  6. getPortableTablesList() calls $this->_getPortableTableDefinition() which is overriden in PostgresqlSchemaManager
  7. Doctrine\DBAL\Schema\PostgresqlSchemaManager::_getPortableTableDefinition() compares the schema for the table with the search path for the connection. If the table schema is the first one in the search path, then it returns the bare table name, if it isn't then it returns the schema-qualified table name (i.e. schema.table). In this case the Migrations table is in the default schema, so _getPortableTableDefinition() returns doctrine_migrations_table
  8. Back in AbstractSchemaManager::tablesExist() it does an array_intersect but is looking for my_schema.doctrine_migrations_table and the table array contains doctrine_migrations_table so the array intersect misses it.
  9. Configuration::createMigrationTable() therefore thinks the table doesn't exist yet and tries to create it, but it does exist, so the table creation fails: ERROR: relation "doctrine_migration_versions" already exists

Given that the problem is caused by the definition of _getPortableTableDefinition() in PostgreSqlSchemaManager the least intrusive way to fix it seems to be to make sure that PostgreSqlSchemaManager::tablesExist also uses _getPortableTableDefinition to clean up the list of tables it is searching for:

    /**
     * Return true if all the given tables exist.
     *
     * @param array $tableNames
     * @return bool
     */
    public function tablesExist($tableNames)
    {
        foreach ($tableNames as $key => $tableName) {
            if (strpos($tableName, '.') !== false) {
                $tableName = explode('.', $tableName, 2);
                $tableNames[$key] = $this->_getPortableTableDefinition(array('schema_name'=>$tableName[0], 'table_name'=>$tableName[1]));
            }
        }
        return parent::tablesExist($tableNames);
    }
@rhunwicks

This comment has been minimized.

Show comment Hide comment
@rhunwicks

rhunwicks Jan 7, 2013

This is actually a bug in Doctrine DBAL and I've now come across another situation where I need to check if a table exists without knowing what the search path will be in advance so I've logged http://www.doctrine-project.org/jira/browse/DBAL-406

This is actually a bug in Doctrine DBAL and I've now come across another situation where I need to check if a table exists without knowing what the search path will be in advance so I've logged http://www.doctrine-project.org/jira/browse/DBAL-406

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment