DBAL-943: dbal db2 platform uses incorrect column modification strategy for clob #2187

Open
doctrinebot opened this Issue Jul 20, 2014 · 1 comment

3 participants

@doctrinebot

Jira issue originally created by user rehfeldchris:

db2 only allows certain column types to be used in an ALTER TABLE ALTER COLUMN myCol ... type statement.

Example entity

<?php
/****
 * @ORM\Entity
 ****/
class Widget
{
    /*** @ORM\Id @ORM\Column(type="integer") @ORM\GeneratedValue ***/
    protected $id;

    /*** @ORM\Column(type="string") ***/
    private $str;
}

orm:schema-tool:create produces:
CREATE TABLE Widget2 (id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, str VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id));

If you then change the column type from string to text via ...

    /*** @ORM\Column(type="text") ***/
    private $str;

... and you then run orm:schema-tool:update, it will try to run:

ALTER TABLE WIDGET2 ALTER STR str CLOB(1M) NOT NULL;

The sql syntax is wrong, but that's a different issue I'll address elsewhere.Lets assume it's fixed to be proper syntax:
ALTER TABLE WIDGET2 ALTER COLUMN str SET DATA TYPE CLOB(1M) ALTER COLUMN str SET NOT NULL;

This triggers sql error code -190, sqlstate 42837
http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.codes/src/tpc/n190.dita

Because the from type => to type isn't valid. This link:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.1.0/com.ibm.db2.udb.admin.doc/doc/r0000888.htm?lang=en
seems to list the valid alterations.

I'm guessing that a different strategy needs to be used; where we drop the old column, and create the new one in such scenarios. This could cause unexpected data loss though.

@doctrinebot doctrinebot added the Bug label Dec 7, 2015
@beberlei beberlei was assigned by doctrinebot Dec 7, 2015
@beberlei beberlei was unassigned by deeky666 Jan 6, 2016
@deeky666
Doctrine member

Data loss is unacceptable here. What we could do is adopt SQLite's table alteration logic where we move the data to a temp table, then alter the schema and finally move data back into the modified table.

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