Yii2: bug in Migration->renameColumn

[mj4444ru]

Add 2 migration

• First
  $this->alterColumn('{{%carer_case}}', 'description', $this->text()->notNull());
• Second
  $this->renameColumn('{{%customer}', '[[client_no]]', '[[system_id]]');

First executed correctly
Second with an error

ufa2@ufa2:/var/www/cw$ ./yii migrate
Yii Migration Tool (based on Yii v2.0.6)

Total 2 new migrations to be applied:
    m150908_093351_carer_case_description
    m150909_141019_rename_customer_client_no

Apply the above migrations? (yes|no) [no]:y
*** applying m150908_093351_carer_case_description
    > alter column description in table {{%carer_case}} to text NOT NULL ... done (time: 0.099s)
*** applied m150908_093351_carer_case_description (time: 0.122s)

*** applying m150909_141019_rename_customer_client_no
    > rename column [[client_no]] in table {{%customer} to [[system_id]] ...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 '{{%customer}' at line 1
The SQL being executed was: SHOW CREATE TABLE {{%customer}'

in /var/www/cw/vendor/yiisoft/yii2/db/Schema.php:628

Error Info:
Array
(
    [0] => 42000
    [1] => 1064
    [2] => 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 '{{%customer}' at line 1
)

Stack trace:
#0 /var/www/cw/vendor/yiisoft/yii2/db/Command.php(852): yii\db\Schema->convertException(Object(PDOException), 'SHOW CREATE TAB...')
#1 /var/www/cw/vendor/yiisoft/yii2/db/Command.php(373): yii\db\Command->queryInternal('fetch', NULL)
#2 /var/www/cw/vendor/yiisoft/yii2/db/mysql/QueryBuilder.php(57): yii\db\Command->queryOne()
#3 /var/www/cw/vendor/yiisoft/yii2/db/Command.php(613): yii\db\mysql\QueryBuilder->renameColumn('{{%customer}', '[[client_no]]', '[[system_id]]')
#4 /var/www/cw/vendor/yiisoft/yii2/db/Migration.php(340): yii\db\Command->renameColumn('{{%customer}', '[[client_no]]', '[[system_id]]')
#5 /var/www/cw/console/migrations/m150909_141019_rename_customer_client_no.php(10): yii\db\Migration->renameColumn('{{%customer}', '[[client_no]]', '[[system_id]]')
#6 /var/www/cw/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(492): m150909_141019_rename_customer_client_no->up()
#7 /var/www/cw/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(129): yii\console\controllers\BaseMigrateController->migrateUp('m150909_141019_...')
#8 [internal function]: yii\console\controllers\BaseMigrateController->actionUp(0)
#9 /var/www/cw/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)
#10 /var/www/cw/vendor/yiisoft/yii2/base/Controller.php(151): yii\base\InlineAction->runWithParams(Array)
#11 /var/www/cw/vendor/yiisoft/yii2/console/Controller.php(91): yii\base\Controller->runAction('', Array)
#12 /var/www/cw/vendor/yiisoft/yii2/base/Module.php(455): yii\console\Controller->runAction('', Array)
#13 /var/www/cw/vendor/yiisoft/yii2/console/Application.php(167): yii\base\Module->runAction('migrate', Array)
#14 /var/www/cw/vendor/yiisoft/yii2/console/Application.php(143): yii\console\Application->runAction('migrate', Array)
#15 /var/www/cw/vendor/yiisoft/yii2/base/Application.php(375): yii\console\Application->handleRequest(Object(yii\console\Request))
#16 /var/www/cw/yii(27): yii\base\Application->run()
#17 {main}

[mj4444ru]

Migration-> renameColumn uses incorrect syntax mysql

Yii Migration Tool (based on Yii v2.0.6)

Total 1 new migration to be applied:
    m150909_141019_rename_customer_client_no

Apply the above migration? (yes|no) [no]:y
*** applying m150909_141019_rename_customer_client_no
    > rename column [[client_no]] in table customer to [[system_id]] ...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 '' at line 1
The SQL being executed was: ALTER TABLE `customer` CHANGE `client_no` `system_id`'

[mj4444ru]

Migration->renameColumn expects parameters without quotes [[ ]]. You may want to do with support for [[ ]] ?
https://github.com/yiisoft/yii2/blob/master/framework/db/mysql/QueryBuilder.php#L67

[mj4444ru]

An alternative embodiment produces an error
https://github.com/yiisoft/yii2/blob/master/framework/db/mysql/QueryBuilder.php#L78

[mdmunir]

?

$this->renameColumn('{{%customer}', 'client_no', 'system_id');

[rtomsmith]

I don't understand why this was closed. Using Yii 2.0.9 I cannot rename a column in a MySQL database table. I get the syntax error.

[dynasource]

I can not reproduce this in mariadb 10.1.9 (Yii 2.0.10)

[samdark]

@rtomsmith any idea how to reproduce it?

[rtomsmith]

It looks like the problem has to do with the SQL mode being used. The failure happens when ANSI Quotes are enabled. The regex in db/mysql/QueryBuilder.php is using back-ticks which will not work for ANSI Quotes mode. I substituted double quotes in the regex as a test, and it worked fine.

Note that the developer can set ANSI_QUOTES explicitly, or by setting one or more of the "combination" modes. See "Server SQL Modes" in the MySQL docs.

[samdark]

The problem in original issue seems to be with the code itself:

$this->renameColumn('{{%customer}', // <--- note that there's no 2nd closing }

In this case it's not escape sequence Yii processes. Thus it's passed to SQL as is as you can see from the error:

The SQL being executed was: SHOW CREATE TABLE {{%customer}'

[samdark]

ANSI_QUOTES support is totally different so should be a separate issue. @rtomsmith would you like to create a pull request fixing it?