Cms: Setup command for pgsql database sends mysql queries to database

Created on 5 Mar 2020  路  7Comments  路  Source: craftcms/cms

Description

When running ./craft setup and nominating pgsql as the driver, craft\console\controllers\InstallController::actionCraft creates an Install migration that seems to send mysql commands to the database instance. In my particular case, I attempted first without the database existing, then without quitting the setup, I created the database and went through the flow again.

The error is:

Exception 'craft\errors\MigrationException' with message 'An error occurred while executing the "craft\migrations\Install migration: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `assetindexdata` (
                     ^
The SQL being executed was: CREATE TABLE `assetindexdata` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `sessionId` varchar(36) NOT NULL DEFAULT '',
    `volumeId` int(11) NOT NULL,
    `uri` text,
    `size` bigint(20) UNSIGNED,
    `timestamp` datetime(0),
    `recordId` int(11),
    `inProgress` tinyint(1) DEFAULT FALSE,
    `completed` tinyint(1) DEFAULT FALSE,
    `dateCreated` datetime(0) NOT NULL,
    `dateUpdated` datetime(0) NOT NULL,
    `uid` char(36) NOT NULL DEFAULT '0'
) ENGINE=InnoDb DEFAULT CHARSET=utf8'

in /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/db/MigrationManager.php:250

Steps to reproduce

  1. composer create-project craftcms/craft craft
  2. cd craft
  3. ./craft setup
  4. Observe error Testing database credentials ... failed: SQLSTATE[08006] [7] FATAL: database "craft" does not exist
  5. Create database (psql postgres, create database craft)
  6. Continue with the same database details
  7. Select to install craft
  8. Observe error

Full output

./craft setup
Which database driver are you using? [mysql,pgsql,?]: pgsql
Database server name or IP address: [127.0.0.1] 
Database port: [5432] 
Database username: [root] tom
Database password: 
Database name: craft
Database schema: [public] 
Database table prefix: 
Testing database credentials ... failed: SQLSTATE[08006] [7] FATAL:  database "craft" does not exist
Database server name or IP address: [127.0.0.1]         
Database port: [5432] 
Database username: [tom] 
Database password: 
Database name: [craft] 
Database schema: [public] 
Database table prefix: 
Testing database credentials ... success!
Saving database credentials to your .env file ... done

Install Craft now? (yes|no) [yes]:

Username: [admin] 
Email: 
Invalid input.
Email: [email protected]
Password: 
Confirm: 
Site name: 
Invalid input.
Site name: site
Site URL: 
Invalid input.
Site URL: site
Site language: [en-US] 
*** installing Craft
    > create table {{%assetindexdata}} ...Exception: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `assetindexdata` (
                     ^
The SQL being executed was: CREATE TABLE `assetindexdata` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `sessionId` varchar(36) NOT NULL DEFAULT '',
    `volumeId` int(11) NOT NULL,
    `uri` text,
    `size` bigint(20) UNSIGNED,
    `timestamp` datetime(0),
    `recordId` int(11),
    `inProgress` tinyint(1) DEFAULT FALSE,
    `completed` tinyint(1) DEFAULT FALSE,
    `dateCreated` datetime(0) NOT NULL,
    `dateUpdated` datetime(0) NOT NULL,
    `uid` char(36) NOT NULL DEFAULT '0'
) ENGINE=InnoDb DEFAULT CHARSET=utf8 (/Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/db/Schema.php:674)
#0 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'CREATE TABLE `a...')
#1 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('CREATE TABLE `a...')
#2 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/db/Migration.php(323): yii\db\Command->execute()
#3 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/migrations/Install.php(96): yii\db\Migration->createTable('{{%assetindexda...', Array)
#4 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/migrations/Install.php(65): craft\migrations\Install->createTables()
#5 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/db/Migration.php(52): craft\migrations\Install->safeUp()
#6 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#7 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/controllers/InstallController.php(155): craft\db\MigrationManager->migrateUp(Object(craft\migrations\Install))
#8 [internal function]: craft\console\controllers\InstallController->actionCraft()
#9 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#10 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#11 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/console/Controller.php(164): yii\base\Controller->runAction('', Array)
#12 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/Controller.php(187): yii\console\Controller->runAction('', Array)
#13 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/Module.php(528): craft\console\Controller->runAction('', Array)
#14 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('install', Array)
#15 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/Application.php(87): yii\console\Application->runAction('install', Array)
#16 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/controllers/SetupController.php(121): craft\console\Application->runAction('install')
#17 [internal function]: craft\console\controllers\SetupController->actionIndex()
#18 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#19 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#20 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/console/Controller.php(164): yii\base\Controller->runAction('', Array)
#21 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/Controller.php(187): yii\console\Controller->runAction('', Array)
#22 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/Module.php(528): craft\console\Controller->runAction('', Array)
#23 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('setup', Array)
#24 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/Application.php(87): yii\console\Application->runAction('setup', Array)
#25 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('setup', Array)
#26 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#27 /Volumes/Suffragette/Development/msbit/craft/craft(22): yii\base\Application->run()
#28 {main}
Exception 'craft\errors\MigrationException' with message 'An error occurred while executing the "craft\migrations\Install migration: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `assetindexdata` (
                     ^
The SQL being executed was: CREATE TABLE `assetindexdata` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `sessionId` varchar(36) NOT NULL DEFAULT '',
    `volumeId` int(11) NOT NULL,
    `uri` text,
    `size` bigint(20) UNSIGNED,
    `timestamp` datetime(0),
    `recordId` int(11),
    `inProgress` tinyint(1) DEFAULT FALSE,
    `completed` tinyint(1) DEFAULT FALSE,
    `dateCreated` datetime(0) NOT NULL,
    `dateUpdated` datetime(0) NOT NULL,
    `uid` char(36) NOT NULL DEFAULT '0'
) ENGINE=InnoDb DEFAULT CHARSET=utf8'

in /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/db/MigrationManager.php:250

Stack trace:
#0 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/controllers/InstallController.php(155): craft\db\MigrationManager->migrateUp(Object(craft\migrations\Install))
#1 [internal function]: craft\console\controllers\InstallController->actionCraft()
#2 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#3 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#4 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/console/Controller.php(164): yii\base\Controller->runAction('', Array)
#5 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/Controller.php(187): yii\console\Controller->runAction('', Array)
#6 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/Module.php(528): craft\console\Controller->runAction('', Array)
#7 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('install', Array)
#8 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/Application.php(87): yii\console\Application->runAction('install', Array)
#9 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/controllers/SetupController.php(121): craft\console\Application->runAction('install')
#10 [internal function]: craft\console\controllers\SetupController->actionIndex()
#11 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#12 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#13 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/console/Controller.php(164): yii\base\Controller->runAction('', Array)
#14 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/Controller.php(187): yii\console\Controller->runAction('', Array)
#15 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/Module.php(528): craft\console\Controller->runAction('', Array)
#16 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('setup', Array)
#17 /Volumes/Suffragette/Development/msbit/craft/vendor/craftcms/cms/src/console/Application.php(87): yii\console\Application->runAction('setup', Array)
#18 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('setup', Array)
#19 /Volumes/Suffragette/Development/msbit/craft/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#20 /Volumes/Suffragette/Development/msbit/craft/craft(22): yii\base\Application->run()
#21 {main}

Additional info

  • Craft version:
3.4.9
  • PHP version:
PHP 7.1.33 (cli) (built: Jan  6 2020 22:19:21) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2018 Zend Technologies
  • Database driver & version:
psql (PostgreSQL) 11.7
  • Plugins & versions:
Not applicable, initial installation
picture msbit
👍1

Most helpful comment

So it is, thanks for the heads up! Just pulled that in for the next release, and verified this bug is fixed now.

picture brandonkelly on 26 Mar 2020
👍2

All 7 comments

Running again from scratch works properly, so seems like the driver configuration might get partially out of sync due to the second run through the flow.

msbit picture msbit on 5 Mar 2020

Actually, I'm not too sure whether running it from scratch actually does work; it seems like the DB_DSN environment variable is set correctly, but the DB_DRIVER environment variable isn't, but I'm not sure when exactly this behaviour occurs.

msbit picture msbit on 5 Mar 2020

Thanks! Narrowed this down to a Yii bug, and submitted a fix (yiisoft/yii2#17910). Will close this issue once Yii 2.0.33 is released with a fix.

brandonkelly picture brandonkelly on 5 Mar 2020
👍1

That PR has been accepted, so now just need to wait for Yii 2.0.33 to drop.

brandonkelly picture brandonkelly on 13 Mar 2020
👍1

@brandonkelly Yii 2.0.33 is out now. (I just hit this)

sayrer picture sayrer on 24 Mar 2020

So it is, thanks for the heads up! Just pulled that in for the next release, and verified this bug is fixed now.

brandonkelly picture brandonkelly on 26 Mar 2020
👍2

Craft 3.4.11 is out now with the fix for this.

brandonkelly picture brandonkelly on 27 Mar 2020
Was this page helpful?
0 / 5 - 0 ratings

Related issues

lukebailey picture lukebailey  路  3Comments
mccombs picture mccombs  路  3Comments
RitterKnightCreative picture RitterKnightCreative  路  3Comments
michel-o picture michel-o  路  3Comments
angrybrad picture angrybrad  路  3Comments