Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-943

dbal db2 platform uses incorrect column modification strategy for clob

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Critical Critical
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Platforms
    • Security Level: All
    • Labels:
      None
    • Environment:
      db2 v10.5 centos 6.5

      Description

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

      Example entity

      Widget2.php
      <?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 ...

      Widget2.php
          /** @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.

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            chris rehfeld
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: