[DBAL-833] [GH-542] [DBAL-825] Drop default constraints before altering column type on SQL Server Created: 07/Mar/14  Updated: 07/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: sqlserver, sqlsrv

Issue Links:
Duplicate
duplicates DBAL-825 ALTER COLUMN on mssql is failing if d... In Progress
duplicates DBAL-826 [GH-536] [WIP] unit test for DBAL-825 Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/542

Message:

SQL Server implements column default values as constraints and therefore requires them to be dropped before a column type change.
This PR implements the recreation of default constraints on column type alterations.
Additionally some code in `SQLServerPlatform::getAlterTableSQL()` has been refactored to avoid code duplication and unnecessary SQL generation.
Please note that due to the issue's tests the PostgreSQL platform had to be altered to fulfill the same behaviour. PostgreSQL returned some strange default value like `666:smallint` when reverse engineering a column type change with a default value of `666` from type `smallint` to `integer`. So I think this PR fixes a bug in this platform, too. Additionally I had to change the deprecated default value retrieval SQL in PostgreSQL in order to work flawlessly. See the [documentation](http://www.postgresql.org/docs/9.3/static/catalog-pg-attrdef.html) at the very end.

I would also like to note that this PR is definitely not the end of the line concerning default values and column alterations. Some weird errors were revealed on other platforms while fixing this issue. MySQL for example denies default values on BLOB/TEXT type columns, DB2 just throws non-understandable syntax errors around and Oracle seems to map decimal/numeric types without a defined scale to integer when reverse engineering (not really related to this issue but it came up while testing).

Otherwise the tests pass on all platforms.



 Comments   
Comment by Doctrine Bot [ 07/Mar/14 ]

A related Github Pull-Request [GH-536] was closed:
https://github.com/doctrine/dbal/pull/536





[DBAL-835] Old column name not quoted during column rename in MySQL Created: 12/Mar/14  Updated: 19/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Arttu Manninen Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None
Environment:

CentOS with Doctrine 2.3 MySQL 5.5.31



 Description   

This is a broken feature, because escaping only sometimes (behavior at least in 2.3, if it has not been fixed after that) leads to breaking things. It is possible to CREATE a column named `usage` (without any backticks in the code), but things break from that point on. If I try to change the column name to a non-reserved word later, ALTER TABLE syntax will break

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'ALTER TABLE products CHANGE usage purpose VARCHAR(256) NOT NULL':

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'usage purpose VARCHAR(256) NOT NULL' at line 1

Migrating down then again escapes the column name even if it wasn't escaped in the definition.

I found a ticket from 2012 describing that column names using reserved keywords should be escaped manually, but since migrations will lead into a dead-end with at least using `usage` as a column name, this feature is somewhat broken.



 Comments   
Comment by Steve Müller [ 12/Mar/14 ]

Arttu Manninen There have been some fixes around quoting identifiers in DDL lately. It seems there is one missing around $oldColumnName in MySqlPlatform::getAlterTableSQL().
I have moved this issue to DBAL as it is an issue there.

Comment by Steve Müller [ 19/Mar/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/545





[DBAL-825] ALTER COLUMN on mssql is failing if default constraint is attached Created: 03/Mar/14  Updated: 07/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Thomas Müller Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None
Environment:

MSSQL


Issue Links:
Duplicate
is duplicated by DBAL-833 [GH-542] [DBAL-825] Drop default cons... In Progress
is duplicated by DBAL-826 [GH-536] [WIP] unit test for DBAL-825 Resolved

 Description   

Here is the unit test - implemented in class SchemaManagerFunctionalTestCase

 
    public function testChangeColumnsTypeWithDefault()
    {
        $table = new \Doctrine\DBAL\Schema\Table('column_change_type_test');
        $table->addColumn('id', 'integer', array('default' => 5));

        $this->_sm->createTable($table);

        $columns = $this->_sm->listTableColumns("column_change_type_test");
        $this->assertEquals(1, count($columns));
        $this->assertInstanceOf('Doctrine\DBAL\Types\IntegerType', $columns['id']->getType());

        $tableDiff = new \Doctrine\DBAL\Schema\TableDiff('column_change_type_test');
        $tableDiff->changedColumns['id'] = new \Doctrine\DBAL\Schema\ColumnDiff(
            'id', new \Doctrine\DBAL\Schema\Column(
                'id', \Doctrine\DBAL\Types\Type::getType('smallint'), array('default' => 5)
            ),
            array('type'),
            new \Doctrine\DBAL\Schema\Column(
                'id', \Doctrine\DBAL\Types\Type::getType('integer'), array('default' => '5')
            )
        );

        $this->_sm->alterTable($tableDiff);

        $columns = $this->_sm->listTableColumns("column_change_type_test");
        $this->assertEquals(1, count($columns));
        $this->assertInstanceOf('Doctrine\DBAL\Types\SmallIntType', $columns['id']->getType());
        $this->assertSame('', $columns['id']->getDefault());
    }

Causes following result

 
Exception : [Doctrine\DBAL\DBALException] An exception occurred while executing 'ALTER TABLE column_change_type_test ALTER COLUMN id SMALLINT NOT NULL':

SQLSTATE [42000, 5074]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The object 'DF_A74995E2_BF396750' is dependent on column 'id'.
SQLSTATE [42000, 4922]: [Microsoft][SQL Server Native Client 11.0][SQL Server]ALTER TABLE ALTER COLUMN id failed because one or more objects access this column.

With queries:
5. SQL: 'ALTER TABLE column_change_type_test ALTER COLUMN id SMALLINT NOT NULL' Params: 
4. SQL: 'SELECT    col.name,
                          type.name AS type,
                          col.max_length AS length,
                          ~col.is_nullable AS notnull,
                          def.definition AS [default],
                          col.scale,
                          col.precision,
                          col.is_identity AS autoincrement,
                          col.collation_name AS collation,
                          CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type
                FROM      sys.columns AS col
                JOIN      sys.types AS type
                ON        col.user_type_id = type.user_type_id
                JOIN      sys.objects AS obj
                ON        col.object_id = obj.object_id
                JOIN      sys.schemas AS scm
                ON        obj.schema_id = scm.schema_id
                LEFT JOIN sys.default_constraints def
                ON        col.default_object_id = def.object_id
                AND       col.object_id = def.parent_object_id
                LEFT JOIN sys.extended_properties AS prop
                ON        obj.object_id = prop.major_id
                AND       col.column_id = prop.minor_id
                AND       prop.name = 'MS_Description'
                WHERE     obj.type = 'U'
                AND       (obj.name = 'column_change_type_test' AND scm.name = SCHEMA_NAME())' Params: 
3. SQL: 'ALTER TABLE column_change_type_test ADD CONSTRAINT DF_A74995E2_BF396750 DEFAULT 5 FOR id' Params: 
2. SQL: 'CREATE TABLE column_change_type_test (id INT NOT NULL)' Params: 

Trace:
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Connection.php:988
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:971
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:612
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\SQLServerSchemaManager.php:232
C:\projects\doctrine\dbal\tests\Doctrine\Tests\DBAL\Functional\Schema\SchemaManagerFunctionalTestCase.php:621
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:976
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:831
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php:648
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:776
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:775
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:745
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php:349
C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php:176
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:268
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:506

#0 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(946): Doctrine\Tests\DbalFunctionalTestCase->onNotSuccessfulTest(Object(Doctrine\DBAL\DBALException))
#1 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php(648): PHPUnit_Framework_TestCase->runBare()
#2 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(776): PHPUnit_Framework_TestResult->run(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest))
#3 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(775): PHPUnit_Framework_TestCase->run(Object(PHPUnit_Framework_TestResult))
#4 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(745): PHPUnit_Framework_TestSuite->runTest(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest), Object(PHPUnit_Framework_TestResult))
#5 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php(349): PHPUnit_Framework_TestSuite->run(Object(PHPUnit_Framework_TestResult), false, Array, Array, false)
#6 C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php(176): PHPUnit_TextUI_TestRunner->doRun(Object(PHPUnit_Framework_TestSuite), Array)
#7 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(268): PHPUnit_TextUI_Command->run(Array, true)
#8 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(506): IDE_Base_PHPUnit_TextUI_Command::main()
#9 {main}



 Comments   
Comment by Thomas Müller [ 03/Mar/14 ]

Possible solution: http://www.select-sql.com/mssql/how-to-alter-column-with-default-constraint-in-mssql.html

Comment by Thomas Müller [ 03/Mar/14 ]

Here is the unit test: https://github.com/DeepDiver1975/dbal/commit/53238301f7e124d31232e9b3eab774c32c9e04c4

Comment by Steve Müller [ 03/Mar/14 ]

Thomas Müller Thanks for reporting. Which version of SQL Server is affected by this?

Comment by Thomas Müller [ 03/Mar/14 ]

SQL Server 2012 Express Edition





[DBAL-807] Index renaming in postgresql does not work when index relates to table inside namespace Created: 08/Feb/14  Updated: 22/Feb/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.5
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Artur Eshenbrener Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Reference
is referenced by DBAL-821 [GH-532] DBAL-807 [DBAL-807] - Added ... Open
is referenced by DBAL-822 [GH-533] [DBAL-807] Respect schema wh... Open

 Description   
CREATE SCHEMA test;
CREATE TABLE test.table_name (id INT);
CREATE INDEX idx_1 ON test.table_name (id);

If index would be renamed, generated sql is:

ALTER INDEX idx_1 RENAME TO new_index_name;

But valid sql code should be:

ALTER INDEX test.idx_1 RENAME TO new_index_name;


 Comments   
Comment by Steve Müller [ 21/Feb/14 ]

Artur Eshenbrener Can you please provide more details about your use case so that we can reproduce it better. How do you get the wrong SQL?

Comment by Artur Eshenbrener [ 22/Feb/14 ]

I've pushed failing test, reproduces this problem.
https://github.com/doctrine/dbal/pull/532

Comment by Doctrine Bot [ 22/Feb/14 ]

A related Github Pull-Request [GH-532] was closed:
https://github.com/doctrine/dbal/pull/532

Comment by Steve Müller [ 22/Feb/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/533





[DBAL-474] SchemaManager / Connection on PostgreSQL platform does not respect filterExpression for sequences Created: 27/Mar/13  Updated: 19/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.2.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: jos de witte Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: postgresql, schematool
Environment:

Windows & Linux



 Description   

Dear Symfony team,

the filterExpression on AbstractSchemaManager seems not to work for sequences.

This only happens under postgres.

It seems the way the sequences are handled are the culprit: It tries to get min_value etc of sequences without matching sequence names to the filter expression in advance.

If for example access to the sequences is denied, (Different schema without permissions for the current entity manager), any higher-level ORM operations like generating migration versions fail.

--------------------- UPDATE

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.



 Comments   
Comment by Benjamin Eberlei [ 14/Apr/13 ]

Can you paste an exception trace? I see that filtering is applied to sequences, but your description seems to indicate this happens due to an SQL query much earlier?

Comment by jos de witte [ 24/Apr/13 ]

Dear Benjamin,

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.

Comment by Steve Müller [ 06/Jan/14 ]

jos de witte I think your issue got fixed in commit: https://github.com/doctrine/dbal/commit/8beb732fe9d5cd40a0d677f250d2be325482744f
This patch was first introduced in 2.3. Can you please confirm that this is fixed? Otherwise can you please provide a concrete example so that we can reproduce you issue?

Comment by Arnout Standaert [ 29/Jan/14 ]

I believe we are bumping into the same issue. Our webapp uses Migrations, but for our webapp we are limited to a certain schema within a bigger PostgreSQL database. We only have permissions on our own schema and public.
Now, listSequences in AbstractSchemaManager does filter the asset names correctly with the mentioned fix.

But the problem is with the step before, _getPortableSequencesList (see line 135 of AbstractSchemaManager):

        return $this->filterAssetNames($this->_getPortableSequencesList($sequences));

This function goes out and does a _getPortableSequenceDefinition on every sequence in the unfiltered list. For every sequence, the _getPortableSequenceDefinition in PostgreSqlSchemaManager performs a SELECT:

        $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

Now, our user role in the database doesn't have SELECT permissions on these sequences in other schemas, so the migration fails with a privilege error.

There should be some kind of filtering on the sequence list BEFORE the SELECT statement in the _getPortableSequenceDefinition function are performed, no?

Comment by Arnout Standaert [ 30/Jan/14 ]

I currently have a workaround running locally, which filters the sequences list before creating the PortableSequence's. This is probably hackish and a poor workaround, just posting here as a temporary solution and further illustration of the problem.

Altered line 135 in AbstractSchemaManager:

        return $this->_getPortableSequencesList($this->filterSequenceNames($sequences));

I added function filterSequenceNames() in AbstractSchemaManager for appropriate sequence filtering:

    /**
     * Filters sequence names if they are configured to return only a subset of all
     * the found elements.
     *5
     * @param array $sequenceNames
     *
     * @return array
     */
    protected function filterSequenceNames($sequenceNames)
    {
        $filterExpr = $this->getFilterSchemaAssetsExpression();
        if ( ! $filterExpr) {
            return $sequenceNames;
        }
        
        return array_values ( array_filter($sequences, function ($sequenceName) use ($filterExpr) {
                $sequenceName = $sequenceName["schemaname"].".".$sequenceName["relname"];
                return preg_match($filterExpr, $sequenceName);
            })
        );

    }

After these modifications, doctrine:migrations:migrate operations complete succesfully, even with our limited-permission database account.

Comment by Steve Müller [ 21/Feb/14 ]

Arnout Standaert Your fix looks promising and reasonable. Can you create a PR on the DBAL repo for that?

Comment by Viktor Sidochenko [ 15/Mar/14 ]

Why this fix is not in master?

Comment by Steve Müller [ 15/Mar/14 ]

Viktor Sidochenko because nobody has fixed it yet Feel free to provide a patch on GitHub.

Comment by Arnout Standaert [ 17/Mar/14 ]

I haven't gotten around to doing the PR on GitHub yet, I'm not yet too familiar with that.
I'll try to find some time for this the coming days.

Comment by Viktor Sidochenko [ 17/Mar/14 ]

Will be good. I`m not professional developer to make patches to upstream. So just voted for this issue.

Comment by Steve Müller [ 19/Mar/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/546
jos de witte, Arnout Standaert, Viktor Sidochenko can you please test if the supplied PR fixes the problem?





[DBAL-444] OraclePlatform getSequenceNextValSQL not handling case/quoting properly on 11g Created: 10/Feb/13  Updated: 03/Jan/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.2
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Max Milaney Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: oci8, oracle, sequence
Environment:

PHP version 5.4.11
Oracle 11g Instant Client version 11.2.0.3.0
Oracle Database 11g Enterprise Edition version 11.2.0.3.0 (x64)
OCI8 DBAL driver


Attachments: File example.php    

 Description   

I have an installer script that uses ORM SchemaTool to create the entities in the DB and then populates with basic data using basic EM->persist calls via ORM.

Sequence objects are created, and when using the 10g Instant Client everything worked correctly, however, upon upgrade to latest version of the Instant Client Oracle seems to be expecting consistent case for these schema objects. It appears as if they are being created with a quoted name as they are created in lowercase. OraclePlatform::getSequenceNextValSQL, however, generates "SELECT entity_id_seq.nextval FROM DUAL" and this fails with error "General error: 2289 OCIStmtExecute: ORA-02289: sequence does not exist".

Executing "SELECT "entity_id_seq".nextval FROM DUAL" directly on the DB returns the correct value.

I believe this may also impact the code in http://www.doctrine-project.org/jira/browse/DBAL-278



 Comments   
Comment by Max Milaney [ 10/Mar/13 ]

Hi there,
Wondering if there is any update on this? I'm having to use a workaround in my applications.
Cheers,
Max

Comment by Benjamin Eberlei [ 14/Mar/13 ]

Can you maybe show an entity definition with its sequence mapping?

Comment by Max Milaney [ 17/Mar/13 ]

Here you are mate. Please see attachment.

Comment by Benjamin Eberlei [ 04/Apr/13 ]

I cant seem to find the problem, in DBAL "lib/Doctrine/DBAL/Platforms/OraclePlatform.php" on line 171, the sequence statement is created with $sequence->getQuotedName($platform), but this only works if quoting is requrested for the sequence.

How do you actually create the sequence? Your entity doesnt have @GeneratedValue.

What does the create schema command say with "--dump-sql" flag? Is the SQL quoted?

Comment by Steve Müller [ 03/Jan/14 ]

Max Milaney Can you please test if this still exists in the current master branch? If so, can you please provide the information requested by Benjamin Eberlei ? Otherwise hunting this down is rather hard... Thank you!





[DBAL-843] Doctrine DBAL getSchema() detect wrong text type (LONGTEXT instead of TEXT) Created: 22/Mar/14  Updated: 26/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Stefano Kowalke Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I created a database from Doctrine Schema (refer as "expected schema" in the text below) which worked fine. Later I need to compare the "expected schema" with the "current schema" from the database. While I am doing that I figured out the the SQL statements of both schemas differs. While the "expected schema" defines a TEXT type for a field, the "current schema" defines a LONGTEXT for the field.

Here is what I am doing:
$tableName = $schema->createTable('tableName');
$tableName->addColumn('fieldName', 'text', array('length' => 65535, 'notnull' => FALSE));

$currentSchema = $connection->getSchemaManager()->createSchema();
$currentSQL = $currentSchema->toSql($connection->getPlatform());

// CREATE TABLE tableName (fieldName LONGTEXT DEFAULT NULL) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB

$expectedSchema = $this->getExpectedSchema();
$expectedSQL = $expectedSchema->toSql($connection->getPlatform());
// CREATE TABLE tableName (fieldName TEXT DEFAULT NULL) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB

This happens because in Doctrine/DBAL/Schema/MySqlSchemaManager.php the type of text is detect correctly but the length is set to FALSE instead of 65535 and when it comes to create the SQL string from the schema it just returns LONGTEXT because the variable $length is FALSE (See Doctrine/DBAL/Platforms/MySqlPlatform::getClobTypeDeclarationSQL().

I added a new case in MySqlSchemaManager::_getPortableTableColumnDefinition which set the length of text types to 65535 and it works. I don't know if this is the correct place in code to do that.

Maybe you can point at the correct place and I will create a PR at Github.



 Comments   
Comment by Steve Müller [ 22/Mar/14 ]

Stefano Kowalke which DBAL version are you using? As far as I can see the length is preserved for text type columns. See here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/MySqlSchemaManager.php#L131-L160

Comment by Stefano Kowalke [ 22/Mar/14 ]

But there is no length information inside $tableColumn['type']. While the value of INT type in $tableColumn['type'] is 'int(11)', the value for TEXT type is just 'text'.

The number inside the parentheses is extracted in https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/MySqlSchemaManager.php#L111 for INT type but not for the TEXT type - line 111 returns and save FALSE in $length.

Comment by Steve Müller [ 25/Mar/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/553

Comment by Stefano Kowalke [ 26/Mar/14 ]

Thanks for taking care of this.

Comment by Steve Müller [ 26/Mar/14 ]

Thanks for reporting





[DBAL-423] Type GUID = VARCHAR(255) on platforms that don't have a native GUID support Created: 25/Jan/13  Updated: 08/Mar/14

Status: In Progress
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: amr Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: None


 Description   

I'm using MySQL with entities that have GUID ids. Therefore I'm using @ORM\Column(type="guid") for the ORM mapping. As MySQL does not have a native GUID data type, it gets mapped to type="string" with a default length of 255 -> VARCHAR(255). I don't really understand why we don't limit the length to 36, which is the fixed length for GUIDs. You could even think about using CHAR(36) for MySQL.

-> see Doctrine\DBAL\Platforms\AbstractPlatform -> getGuidTypeDeclarationSQL()



 Comments   
Comment by Steve Müller [ 23/Dec/13 ]

Patch PR: https://github.com/doctrine/dbal/pull/465

Comment by Michael Kühn [ 28/Feb/14 ]

With the latest support for the MyISAM-Engine merging this pull request would save some trouble.

Background/Steps to reproduce:
If you have 2 entities with guid/uuid as primary key, @ManyToMany/@JoinTable fails on MyISAM, because it would create a jointable with 2 VARCHAR(255) columns and would apply a combined primary key on these two columns. But MyISAM doesn't support keys longer than 1000 bytes so you can't create the jointable.

See: https://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

In my opinion this isn't just a "minor" issue but a major because some people can't run on MySQL with InnoDB for some reason.

Comment by Marco Pivetta [ 28/Feb/14 ]

Michael Kühn MyISAM is niche support for the ORM - using a custom type is perfectly fine in such a case.

Comment by Michael Kühn [ 04/Mar/14 ]

Marco Pivetta I agree, but i don't see why we would assume a GUID/UUID - which is per definition 36 chars long - as a 255 char long string. It would save storage space (for platforms don't supporting a native uuid type) and circle around at least 1 barrier if using MyISAM.

By the way, the PR is missing something. While it works perfectly fine the first time, every orm:schema-tool:update would output the guid-columns every time if you don't specifiy "length=36" and "fixed=true" on every GUID-@Column. IMHO the GUID-Type should implicit this both attributes in this pull request so you don't get column updates that don't change anything.

Comment by Steve Müller [ 08/Mar/14 ]

Michael Kühn I get your point and thanks for pointing out the remaining issue. The problem is caused by the comparator which detects differences in the column definition because the length and fixed attribute are hardcoded in the platform which is beyond comparator's knowledge. Still I think this can be fixed easily but as long as Benjamin Eberlei is of the opinion that this change is a minor BC break, this PR won't make it into the master branch.





Generated at Wed Apr 23 07:53:08 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.