Migration failing when updating from 5.4.1 to 6.1.1 - Syntax error or access violation

[mckenzieclark]

Description

When updating from 5.4.1 to 6.1.1 I get the following error during the m250808_100206_update_shopify_product_indexes migration:

SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'shopifyId' used in key specification without a key length

The full migration output:

*** applying m250808_100206_update_shopify_product_indexes
    > dropping index on {{%shopify_products}} if it exists ... done (time: 0.001s)
    > dropping index on {{%shopify_products}} if it exists ... done (time: 0.000s)
    > create index idx_shhigwfgufovubcxhtxczitztmgpcsyirptm on {{%shopify_products}} (shopifyId) ...Exception: SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'shopifyId' used in key specification without a key length
The SQL being executed was: ALTER TABLE `shopify_products` ADD INDEX `idx_shhigwfgufovubcxhtxczitztmgpcsyirptm` (`shopifyId`) (/var/www/html/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1325): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE `sh...')
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute('ALTER TABLE `sh...')
#2 /var/www/html/vendor/yiisoft/yii2/db/Migration.php(506): yii\db\Command->execute()
#3 /var/www/html/vendor/craftcms/cms/src/db/Migration.php(450): yii\db\Migration->createIndex('idx_shhigwfgufo...', '{{%shopify_prod...', Array, false)
#4 /var/www/html/vendor/craftcms/cms/src/db/Migration.php(465): craft\db\Migration->createIndex(NULL, '{{%shopify_prod...', Array, false)
#5 /var/www/html/vendor/craftcms/shopify/src/migrations/m250808_100206_update_shopify_product_indexes.php(21): craft\db\Migration->createIndexIfMissing('{{%shopify_prod...', Array)
#6 /var/www/html/vendor/craftcms/cms/src/db/Migration.php(50): craft\shopify\migrations\m250808_100206_update_shopify_product_indexes->safeUp()
#7 /var/www/html/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(758): craft\db\Migration->up()
#8 /var/www/html/vendor/craftcms/cms/src/console/controllers/MigrateController.php(361): yii\console\controllers\BaseMigrateController->migrateUp('m250808_100206_...')
#9 [internal function]: craft\console\controllers\MigrateController->actionAll()
#10 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#11 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#12 /var/www/html/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction('all', Array)
#13 /var/www/html/vendor/craftcms/cms/src/console/ControllerTrait.php(90): yii\console\Controller->runAction('all', Array)
#14 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): craft\console\controllers\MigrateController->runAction('all', Array)
#15 /var/www/html/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#16 /var/www/html/vendor/craftcms/cms/src/console/Application.php(91): yii\console\Application->runAction('migrate/all', Array)
#17 /var/www/html/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('migrate/all', Array)
#18 /var/www/html/vendor/craftcms/cms/src/console/Application.php(122): yii\console\Application->handleRequest(Object(craft\console\Request))
#19 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest(Object(craft\console\Request))
#20 /var/www/html/craft(13): yii\base\Application->run()
#21 {main}
*** failed to apply m250808_100206_update_shopify_product_indexes (time: 0.002s)

Steps to reproduce

1. Update version number from 5.4.1 to 6.1.1 in composer.json
2. Run (ddev) composer update

Additional info

• Craft version: 4.16.17
• PHP version: 8.2
• Database driver & version: mariadb 10.3
• Plugins & versions: Shopify 6.1.1 (from 5.4.1)

The particular project has had craftcms/shopify installed since version 3.1.0

[linear]

PT-2993

[nfourtythree]

Hi @mckenzieclark

Thank you for your message.

Could you check the structure of the shopify_products table in your database? Is the data type for the shopifyId column varchar(255)?

Thanks!

[mckenzieclark]

Hey @nfourtythree thanks for the reply.

The shopify_products.shopifyId column is currently longtext

[nfourtythree]

Hi @mckenzieclark

Thank you for your reply, it is strange that the column type is different.

We went back to previous versions, installed them and tried migrations and every time it was a varchar. Wondering if there is a chance a backup/restore has been done at some point in time that might have somehow changed this.

Would it be possible to send a backup of your database to support@craftcms.com referencing this issue.

That way we can take a look to see if there is anything else that might look out of place.

Thanks!

[mckenzieclark]

Ah how odd @nfourtythree, thanks for checking. The oldest db backup I have handy is from Sept 2023 and the column type is longtext there too. Will try and get to the bottom of why that changed, as there are no Craft migrations that have made this happen.

Will get the latest db sent over to the support email. Thanks for the help!