Unable to change the version column length into the table migration_version

[macintoshplus]

trafficstars

BC Break Report

I have upgraded the doctrine migration from the version 1.8.1 to 2.0.0 but when I want check the status of the database migration an exception is throwed.

Q	A
BC Break	yes
Version	2.0.0

Summary

This query is tried to run: ALTER TABLE migration_versions ALTER COLUMN version NVARCHAR(14) NOT NULL

Previous behavior

No error displayed !

Current behavior

The result is this error:

In PDOConnection.php line 90:

  [Doctrine\DBAL\Driver\PDOException (42000)]
  SQLSTATE[42000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The objet 'PK__migratio__79B5C94C0C85DE4D' depends to column 'version'.

Unable to use the doctrine migration.

How to reproduce

My version column has 510 in length

[jwage]

I tried to reproduce this without any luck. Is it possible for you to try and reproduce in a test case?

[bretrzaun]

I am having a similar problem using an Oracle database, where I get the following message:

An exception occurred while executing 'ALTER TABLE migration_versions ADD (version VARC
  HAR2(14) NOT NULL, executed_at TIMESTAMP(0) DEFAULT NULL NULL)':

  ORA-01430: column being added already exists in table

Problem seems to be that Oracle reports the existing column name in upper case.

[jwage]

@bretrzaun can I see your configuration used?

[bretrzaun]

@jwage Which configuration do you mean?

[jwage]

@bretrzaun https://www.doctrine-project.org/projects/doctrine-migrations/en/2.0/reference/configuration.html#configuration

[macintoshplus]

I tried to reproduce this without any luck. Is it possible for you to try and reproduce in a test case?

The units tests can use Microsoft SQL Server 2016 ? The SQL standard allows change the column length if the column is the primary key ? The error is provided because the PK is not deleted before the column change in length.

@jwage Thanks for the documentation, I can set the actual value in the configuration. But this error will not be resolved.

[bretrzaun]

@jwage This is our migrations.yml:

name: MyProject Migrations
migrations_namespace: MyProject\Migrations
table_name: myproject_migration_versions
migrations_directory: src/Migrations

[jwage]

Can you both share the CREATE TABLE definition you have in your database for the migration_versions table?

[macintoshplus]

I cannot generate the CREATION TABLE sql query, but the structure is:

[bretrzaun]

simplified version (skipped all the Oracle overhead):

CREATE TABLE "MYPROJECT_MIGRATION_VERSIONS" 
   (	"VERSION" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	 PRIMARY KEY ("VERSION")
   );

[jwage]

Can you try configuring the column length to match what you have defined in your database? https://www.doctrine-project.org/projects/doctrine-migrations/en/2.0/reference/configuration.html#line-number-7f19d1d8416762550acdebe4f5bf050f7c623d19-8

[tobias-93]

@bretrzaun I think you're having the same issue as in #788, and it's unrelated to this issue.

[pvsiv]

Same issue here, after upgrading to doctrine/doctrine-migrations-bundle v2.0.0 and doctrine/migrations v2.0.0

An exception occurred while executing
'ALTER TABLE migration_version ALTER COLUMN   
  version NVARCHAR(14) NOT NULL':

SQLSTATE [42000, 5074]: [Microsoft][ODBC  
   Driver 17 for SQL Server][SQL Server]The object 'PK__migratio__79B5C94CB7E  
  C3F78' is dependent on column 'version'.

SQLSTATE [42000, 4922]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ALTER TABLE ALTER COLUMN version failed because one or more objects access this column.

We're running SQL Server 2016, which requires you to drop all indexes on a column before changing its length.

migrations bundle config:

doctrine_migrations:
    dir_name: '%kernel.project_dir%/src/Migrations'
    namespace: DoctrineMigrations
    table_name: migration_version

CREATE TABLE definition (pre-2.0.0):

CREATE TABLE migration_version (
  version NVARCHAR(255) NOT NULL
    PRIMARY KEY
)

Explicitly configuring column_length: 255 solved the issue for us, for now.

[jwage]

@morozov What are your thoughts on this issue? I am using the DBAL to do this change of the schema and it is producing DDL statements that don't work for some databases. Have you seen similar issues before?

[morozov]

@jwage what exactly is the schema change which causes this? Or how can I reproduce it not being familiar with the Migrations?

DBAL may definitely be missing some edge cases. E.g. on SQL Server, an ALTER TABLE ... ALTER COLUMN cannot be done on a column which has indices. The indices need to be dropped and created later, but DBAL seems to be handling only the DEFAULT and foreign key constraints this way.

[JEDIBC]

I have the same problem with MSSQL.

I think that MSSQL automatically create an index PK__migratio__79B5C94CB7E on the primary key of each table. Deleting manually this index makes the doctrine:migrations:* commands works.

[akrabat]

FWiW, I've just come across this problem in SQL Server 2017 when migrating to 2 from 1.8

$ php bin/migrations.php --db-configuration=migrations-windev-online-dev.php -n status

In DBALException.php line 169:

  An exception occurred while executing 'ALTER TABLE migration_versions ALTER COLUMN version NVARCHAR(14) NOT NULL':

  SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The object 'PK__migratio__79B5C94C0E391C95' is dependent on column 'version'.


In PDOConnection.php line 80:

  SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The object 'PK__migratio__79B5C94C0E391C95' is dependent on column 'version'.


In PDOConnection.php line 75:

  SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The object 'PK__migratio__79B5C94C0E391C95' is dependent on column 'version'.


status [--show-versions] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]]```

[akrabat]

Adding:

"column_length" : 255

To my migrations.json config seems to have worked around the problem.