search

craftcms / cms

Build bespoke content experiences with Craft.
https://craftcms.com
Other
3.28k stars 635 forks source link

[4.4]: DB error during m221027_160703_add_image_transform_fill #12855

Closed jmauzyk closed 1 year ago

jmauzyk commented 1 year ago

What happened?

Description

Encountering errors when attempting to apply the m221027_160703_add_image_transform_fill migration. The install was at 4.3.11 prior to upgrading to 4.4.0, and was migrated to Craft 4 from Craft 3. The errors are as follows:

2023-03-09 16:30:37 [web.ERROR] [craft\db\MigrationManager::migrateUp] Failed to apply m221027_160703_add_image_transform_fill (time: 1.227s). Output:
    > add column fill string(11) NULL DEFAULT NULL to table {{%imagetransforms}} ... done (time: 0.043s)
    > add column upscale boolean NOT NULL DEFAULT TRUE to table {{%imagetransforms}} ... done (time: 0.001s)
    > execute SQL: alter table {{%imagetransforms}} drop constraint {{%imagetransforms_mode_check}}, add check ([[mode]] in ('stretch','fit','crop','letterbox')) ...    > execute SQL: alter table {{%imagetransforms}} drop constraint {{%assettransforms_mode_check}}, add check ([[mode]] in ('stretch','fit','crop','letterbox')) ...Exception: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block
The SQL being executed was: SAVEPOINT LEVEL2 (/var/www/html/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'SAVEPOINT LEVEL...')
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('SAVEPOINT LEVEL...')
#2 /var/www/html/vendor/yiisoft/yii2/db/Schema.php(387): yii\db\Command->execute()
#3 /var/www/html/vendor/yiisoft/yii2/db/Transaction.php(144): yii\db\Schema->createSavepoint('LEVEL2')
#4 /var/www/html/vendor/yiisoft/yii2/db/Connection.php(798): yii\db\Transaction->begin(NULL)
#5 /var/www/html/vendor/craftcms/cms/src/services/ImageTransforms.php(249): yii\db\Connection->beginTransaction()
#6 /var/www/html/vendor/craftcms/cms/src/base/ApplicationTrait.php(1714): craft\services\ImageTransforms->handleChangedTransform(Object(craft\events\ConfigEvent))
#7 /var/www/html/vendor/craftcms/cms/src/services/ProjectConfig.php(1125): craft\web\Application->craft\base\{closure}(Object(craft\events\ConfigEvent))
#8 [internal function]: craft\services\ProjectConfig->handleChangeEvent(Object(craft\events\ConfigEvent))
#9 /var/www/html/vendor/yiisoft/yii2/base/Component.php(633): call_user_func(Array, Object(craft\events\ConfigEvent))
#10 /var/www/html/vendor/craftcms/cms/src/models/ProjectConfigData.php(82): yii\base\Component->trigger('updateItem', Object(craft\events\ConfigEvent))
#11 /var/www/html/vendor/craftcms/cms/src/services/ProjectConfig.php(515): craft\models\ProjectConfigData->commitChanges(Array, Array, 'imageTransforms...', true, NULL, true)
#12 /var/www/html/vendor/craftcms/cms/src/services/ProjectConfig.php(475): craft\services\ProjectConfig->_setInternal('imageTransforms...', Array, NULL, true, false)
#13 /var/www/html/vendor/craftcms/cms/src/migrations/m221027_160703_add_image_transform_fill.php(63): craft\services\ProjectConfig->set('imageTransforms...', Array)
#14 /var/www/html/vendor/craftcms/cms/src/db/Migration.php(49): craft\migrations\m221027_160703_add_image_transform_fill->safeUp()
#15 /var/www/html/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#16 /var/www/html/vendor/craftcms/cms/src/db/MigrationManager.php(149): craft\db\MigrationManager->migrateUp(Object(craft\migrations\m221027_160703_add_image_transform_fill))
#17 /var/www/html/vendor/craftcms/cms/src/services/Updates.php(245): craft\db\MigrationManager->up()
#18 /var/www/html/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(493): craft\services\Updates->runMigrations(Array)
#19 /var/www/html/vendor/craftcms/cms/src/controllers/UpdaterController.php(203): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#20 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#21 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#22 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#23 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('migrate', Array)
#24 /var/www/html/vendor/craftcms/cms/src/web/Application.php(302): yii\base\Module->runAction('updater/migrate', Array)
#25 /var/www/html/vendor/craftcms/cms/src/web/Application.php(714): craft\web\Application->runAction('updater/migrate')
#26 /var/www/html/vendor/craftcms/cms/src/web/Application.php(239): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#27 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#28 /var/www/html/web/index.php(15): yii\base\Application->run()
#29 {main}
 {"memory":6065096} 
2023-03-09 16:30:37 [web.ERROR] [craft\db\MigrationManager::up] Migration failed. The rest of the migrations are cancelled. {"memory":6073440} 
2023-03-09 16:30:37 [web.ERROR] [craft\controllers\BaseUpdaterController::runMigrations] craft\migrations\m221027_160703_add_image_transform_fill migration failed: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block
The SQL being executed was: SAVEPOINT LEVEL2 {"memory":6065664} 
2023-03-09 16:30:37 [web.ERROR] [yii\db\Exception] PDOException: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block in /var/www/html/vendor/yiisoft/yii2/db/Command.php:1302
Stack trace:
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('SAVEPOINT LEVEL...')
#2 /var/www/html/vendor/yiisoft/yii2/db/Schema.php(387): yii\db\Command->execute()
#3 /var/www/html/vendor/yiisoft/yii2/db/Transaction.php(144): yii\db\Schema->createSavepoint('LEVEL2')
#4 /var/www/html/vendor/yiisoft/yii2/db/Connection.php(798): yii\db\Transaction->begin(NULL)
#5 /var/www/html/vendor/craftcms/cms/src/services/ImageTransforms.php(249): yii\db\Connection->beginTransaction()
#6 /var/www/html/vendor/craftcms/cms/src/base/ApplicationTrait.php(1714): craft\services\ImageTransforms->handleChangedTransform(Object(craft\events\ConfigEvent))
#7 /var/www/html/vendor/craftcms/cms/src/services/ProjectConfig.php(1125): craft\web\Application->craft\base\{closure}(Object(craft\events\ConfigEvent))
#8 [internal function]: craft\services\ProjectConfig->handleChangeEvent(Object(craft\events\ConfigEvent))
#9 /var/www/html/vendor/yiisoft/yii2/base/Component.php(633): call_user_func(Array, Object(craft\events\ConfigEvent))
#10 /var/www/html/vendor/craftcms/cms/src/models/ProjectConfigData.php(82): yii\base\Component->trigger('updateItem', Object(craft\events\ConfigEvent))
#11 /var/www/html/vendor/craftcms/cms/src/services/ProjectConfig.php(515): craft\models\ProjectConfigData->commitChanges(Array, Array, 'imageTransforms...', true, NULL, true)
#12 /var/www/html/vendor/craftcms/cms/src/services/ProjectConfig.php(475): craft\services\ProjectConfig->_setInternal('imageTransforms...', Array, NULL, true, false)
#13 /var/www/html/vendor/craftcms/cms/src/migrations/m221027_160703_add_image_transform_fill.php(63): craft\services\ProjectConfig->set('imageTransforms...', Array)
#14 /var/www/html/vendor/craftcms/cms/src/db/Migration.php(49): craft\migrations\m221027_160703_add_image_transform_fill->safeUp()
#15 /var/www/html/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#16 /var/www/html/vendor/craftcms/cms/src/db/MigrationManager.php(149): craft\db\MigrationManager->migrateUp(Object(craft\migrations\m221027_160703_add_image_transform_fill))
#17 /var/www/html/vendor/craftcms/cms/src/services/Updates.php(245): craft\db\MigrationManager->up()
#18 /var/www/html/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(493): craft\services\Updates->runMigrations(Array)
#19 /var/www/html/vendor/craftcms/cms/src/controllers/UpdaterController.php(203): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#20 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#21 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#22 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#23 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('migrate', Array)
#24 /var/www/html/vendor/craftcms/cms/src/web/Application.php(302): yii\base\Module->runAction('updater/migrate', Array)
#25 /var/www/html/vendor/craftcms/cms/src/web/Application.php(714): craft\web\Application->runAction('updater/migrate')
#26 /var/www/html/vendor/craftcms/cms/src/web/Application.php(239): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#27 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#28 /var/www/html/web/index.php(15): yii\base\Application->run()
#29 {main}

Next yii\db\Exception: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block
The SQL being executed was: SAVEPOINT LEVEL2 in /var/www/html/vendor/yiisoft/yii2/db/Schema.php:676
Stack trace:
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'SAVEPOINT LEVEL...')
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('SAVEPOINT LEVEL...')
#2 /var/www/html/vendor/yiisoft/yii2/db/Schema.php(387): yii\db\Command->execute()
#3 /var/www/html/vendor/yiisoft/yii2/db/Transaction.php(144): yii\db\Schema->createSavepoint('LEVEL2')
#4 /var/www/html/vendor/yiisoft/yii2/db/Connection.php(798): yii\db\Transaction->begin(NULL)
#5 /var/www/html/vendor/craftcms/cms/src/services/ImageTransforms.php(249): yii\db\Connection->beginTransaction()
#6 /var/www/html/vendor/craftcms/cms/src/base/ApplicationTrait.php(1714): craft\services\ImageTransforms->handleChangedTransform(Object(craft\events\ConfigEvent))
#7 /var/www/html/vendor/craftcms/cms/src/services/ProjectConfig.php(1125): craft\web\Application->craft\base\{closure}(Object(craft\events\ConfigEvent))
#8 [internal function]: craft\services\ProjectConfig->handleChangeEvent(Object(craft\events\ConfigEvent))
#9 /var/www/html/vendor/yiisoft/yii2/base/Component.php(633): call_user_func(Array, Object(craft\events\ConfigEvent))
#10 /var/www/html/vendor/craftcms/cms/src/models/ProjectConfigData.php(82): yii\base\Component->trigger('updateItem', Object(craft\events\ConfigEvent))
#11 /var/www/html/vendor/craftcms/cms/src/services/ProjectConfig.php(515): craft\models\ProjectConfigData->commitChanges(Array, Array, 'imageTransforms...', true, NULL, true)
#12 /var/www/html/vendor/craftcms/cms/src/services/ProjectConfig.php(475): craft\services\ProjectConfig->_setInternal('imageTransforms...', Array, NULL, true, false)
#13 /var/www/html/vendor/craftcms/cms/src/migrations/m221027_160703_add_image_transform_fill.php(63): craft\services\ProjectConfig->set('imageTransforms...', Array)
#14 /var/www/html/vendor/craftcms/cms/src/db/Migration.php(49): craft\migrations\m221027_160703_add_image_transform_fill->safeUp()
#15 /var/www/html/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#16 /var/www/html/vendor/craftcms/cms/src/db/MigrationManager.php(149): craft\db\MigrationManager->migrateUp(Object(craft\migrations\m221027_160703_add_image_transform_fill))
#17 /var/www/html/vendor/craftcms/cms/src/services/Updates.php(245): craft\db\MigrationManager->up()
#18 /var/www/html/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(493): craft\services\Updates->runMigrations(Array)
#19 /var/www/html/vendor/craftcms/cms/src/controllers/UpdaterController.php(203): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#20 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#21 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#22 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#23 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('migrate', Array)
#24 /var/www/html/vendor/craftcms/cms/src/web/Application.php(302): yii\base\Module->runAction('updater/migrate', Array)
#25 /var/www/html/vendor/craftcms/cms/src/web/Application.php(714): craft\web\Application->runAction('updater/migrate')
#26 /var/www/html/vendor/craftcms/cms/src/web/Application.php(239): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#27 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#28 /var/www/html/web/index.php(15): yii\base\Application->run()
#29 {main}
Additional Information:
Array
(
    [0] => 25P02
    [1] => 7
    [2] => ERROR:  current transaction is aborted, commands ignored until end of transaction block
)
 {"memory":6065880,"exception":"[object] (yii\\db\\Exception(code: 25P02): SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block
The SQL being executed was: SAVEPOINT LEVEL2 at /var/www/html/vendor/yiisoft/yii2/db/Schema.php:676)
[previous exception] [object] (PDOException(code: 25P02): SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block at /var/www/html/vendor/yiisoft/yii2/db/Command.php:1302)"}

Steps to reproduce

  1. From console, run craft migrate/all

Expected behavior

Successfully complete migration

Actual behavior

Migration fails with exit status 1

Craft CMS version

4.4.0

PHP version

8.2

Operating system and version

Linux 5.15.49-linuxkit

Database type and version

PostgreSQL 14.6

Image driver and version

Imagick 3.7.0 (ImageMagick 6.9.11-60)

Installed plugins and versions

No response

brandonkelly commented 1 year ago

Thanks for reporting that! I wasn’t able to reproduce on Postgres, but we just released Craft 4.4.1 which includes a change to that migration which should fix this for you. Can you please try updating to 4.4.1 and see if it solves the issue?

jmauzyk commented 1 year ago

@brandonkelly that did the trick, thanks!

brandonkelly commented 1 year ago

Excellent, thanks for letting me know!

shoored commented 1 year ago

Hello, I'm also stuck at this particular migration with the following error:

*** applying m221027_160703_add_image_transform_fill
> add column fill string(11) NULL DEFAULT NULL to table {{%imagetransforms}} ... done (time: 0.004s)
> add column upscale boolean NOT NULL DEFAULT TRUE to table {{%imagetransforms}} ... done (time: 0.001s)
> execute SQL: alter table {{%imagetransforms}} drop constraint if exists {{%imagetransforms_mode_check}} ... done (time: 0.001s)
> execute SQL: alter table {{%imagetransforms}} drop constraint if exists {{%assettransforms_mode_check}} ... done (time: 0.001s)
> execute SQL: alter table {{%imagetransforms}} add check ([[mode]] in ('stretch','fit','crop','letterbox')) ...Exception: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input value for enum assettransforms_mode: "letterbox"
The SQL being executed was: alter table "imagetransforms" add check ("mode" in ('stretch','fit','crop','letterbox')) (/var/www/html/vendor/yiisoft/yii2/db/Schema.php:676)

I'm also on Postgres. Upgrading from Craft 3.8.11 to Craft 4.4.13.

Please do let me know if I can be of further assistance. Cheers!

brandonkelly commented 1 year ago

@shoored Any chance you can send your database into support@craftcms.com?

shoored commented 1 year ago

@brandonkelly Sure thing, will sent one your way tomorrow morning.

brandonkelly commented 1 year ago

For posterity, @shoored’s issue occurred because the database originated as MySQL and was converted to Postgres. Enum columns like imagetransforms.mode were given a dynamic column type, rather than a check constraint like Craft/Yii uses for enum values in Postgres.

We worked around it by ensuring the column is a varchar type before adding the new check constraint.

shoored commented 1 year ago

Thank you @brandonkelly for looking into this and for the workaround.

brandonkelly commented 1 year ago

Craft 4.4.14 is out with that migration improvement.

shoored commented 1 year ago

In addition to my specific issue, here's something that might help other people. We wrote a script that outputs all Postgres DB columns with a custom data_type along with the table it belongs to. Then we created a custom DB migration that would alter these data_types to a varchar type. Then the Craft 4 upgrade with 40 migrations went flawlessly.