Yii2: Schema builder (MySQL): append in the wrong place

[daniele-athome]

What steps will reproduce the problem?

Create a migration like this:

        $this->createTable('table', [
            'name' => $this->string(50)->notNull()->append('CHARACTER SET ascii COLLATE ascii_general_ci')->comment('Property name'),
            'value' => $this->text()->comment('Property value')
        ], 'ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci');

What is the expected result?

The SQL command is execute successfully.

What do you get instead?

An error is generated by MySQL because of the wrong order in the column definition:

Exception 'yii\db\Exception' with message '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 'CHARACTER SET ascii COLLATE ascii_general_ci,
        `value` text COMMENT 'Property va' at line 2
The SQL being executed was: CREATE TABLE `table` (
        `name` varchar(50) NOT NULL COMMENT 'Property name' CHARACTER SET ascii COLLATE ascii_general_ci,
        `value` text COMMENT 'Property value'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci'

This is happening because the append() function literally appends the given string to the column definition (maybe I'm doing something wrong?). I don't know if it's legal in other RDBMS, but in MySQL it's not.

Additional info

| Q | A |
| --- | --- |
| Yii version | 2.0.9 |
| PHP version | 7.0.7 |
| Operating system | Latest Debian testing |
| MySQL version | 5.6.30 |

Great software and thanks for your help.

[klimov-paul]

This is expected behavior append() method always adds SQL to the end of the column statement.
It does not aware of the method order in the chain, just as well as any other method at ColumnSchemaBuilder.

[daniele-athome]

Fair enough, but you should state that in your docs:
http://www.yiiframework.com/news/102/yii-2-0-9-is-released/

In the release notes there are examples for using append() for column encoding.

[rob006]

@klimov-paul

This is expected behavior append() method always adds SQL to the end of the column statement.

This syntax was designed to set column collate/charset: https://github.com/yiisoft/yii2/issues/11195
I you can't rely on it for setting charset, what is the real use case for append()?

[klimov-paul]

Why asking me? Ask @df2 for that - it was his improvement.

[rob006]

I am going to the conclusion, that current implementation is buggy and this issue is valid bugreport. If there is no use case for append text after comment declaration, append() behavior should be changed.

[daniele-athome]

Or at least it should be noted somewhere, and probably a new feature request for another function should be opened (since this is a special use-case right?)

[samdark]

Yes, it's a bug. Append should go before comment.

[daniele-athome]

Please be aware that this could vary on the DBMS being used - but I guess you already know that, just a reminder :-)

[Nex-Otaku]

Please fix this bug.

For now, it's impossible to use "CHARACTER SET" for specific column in ALTER TABLE statement via ColumnSchemaBuilder.

This code:

        $this->alterColumn(
            $tableName,
            $columnName,
            $this->string(500)->null()->defaultValue(null)->append('CHARACTER SET utf8')
        );

appends "CHARACTER SET" to end of colun definition, so MySQL throws error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

[simialbi]

Yes, it's a bug. Append should go before comment.

The official documentation says something else:

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY}]
      [reference_definition]
      [check_constraint_definition]

But according this documentation the DEFAULT and CHECK keywords are misplaced too...
If we'd like to follow the documentation we would have to change

    case self::CATEGORY_NUMERIC:
        $format = '{type}{length}{unsigned}{notnull}{unique}{default}{comment}{check}{append}{pos}';
        break;
    default:
        $format = '{type}{length}{notnull}{unique}{default}{comment}{check}{append}{pos}';

to

    case self::CATEGORY_NUMERIC:
        $format = '{type}{length}{unsigned}{notnull}{default}{unique}{comment}{append}{pos}{check}';
        break;
    default:
        $format = '{type}{length}{notnull}{default}{unique}{comment}{append}{pos}{check}';

[samdark]

@simialbi have time to implement it?

[simialbi]

I think it should be possible