search

barrelstrength / sprout

Sprout Marketing Suite modules for Craft CMS applications
https://sprout.barrelstrengthdesign.com
Other
2 stars 8 forks source link

Migration error from 3.x from Sprout Reports, missing table aliases #252

Closed Romanavr closed 1 year ago

Romanavr commented 1 year ago

Description

PR with fix of this problem https://github.com/barrelstrength/sprout/pull/253 Checked locally, after the fix the plugin is installed and the migration is applied

The problem:

Using version ^4.45 for barrelstrength/sprout-data-studio
*** installing sprout-data-studio
    > insert into {{%sprout_source_groups}} ... done (time: 0.006s)
    > update in {{%sproutreports_reports}} ... done (time: 0.004s)
Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sproutreports_reports.id' in 'field list'
The SQL being executed was: SELECT `sproutreports_reports`.`id` AS `id`, `sproutreports_reports`.`groupId` AS `groupId`, `sproutreports_reports`.`name` AS `name`, `sproutreports_reports`.`nameFormat` AS `nameFormat`, `sproutreports_reports`.`handle` AS `handle`, `sproutreports_reports`.`description` AS `description`, `sproutreports_reports`.`allowHtml` AS `allowHtml`, `sproutreports_reports`.`sortOrder` AS `sortOrder`, `sproutreports_reports`.`sortColumn` AS `sortColumn`, `sproutreports_reports`.`delimiter` AS `delimiter`, `sproutreports_reports`.`dataSourceId` AS `dataSourceId`, `sproutreports_reports`.`settings` AS `settings`, `sproutreports_reports`.`enabled` AS `enabled`, `sproutreports_reports`.`dateCreated` AS `dateCreated`, `sproutreports_reports`.`dateUpdated` AS `dateUpdated`, `sproutreports_reports`.`uid` AS `uid`, `elements_sites`.`siteId` AS `siteId`
FROM `craft_sproutreports_reports`
INNER JOIN `craft_elements_sites` ON `sproutreports_reports`.`id` = `elements_sites`.`elementId` (/app/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /app/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'SELECT `sproutr...')
#1 /app/vendor/yiisoft/yii2/db/Command.php(1168): yii\db\Command->internalExecute('SELECT `sproutr...')
#2 /app/vendor/yiisoft/yii2/db/Command.php(410): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /app/vendor/yiisoft/yii2/db/Query.php(249): yii\db\Command->queryAll()
#4 /app/vendor/craftcms/cms/src/db/Query.php(248): yii\db\Query->all(NULL)
#5 /app/vendor/barrelstrength/sprout/src/datastudio/migrations/m211101_000008_migrate_reports_tables.php(117): craft\db\Query->all()
#6 /app/vendor/craftcms/cms/src/db/Migration.php(49): BarrelStrength\Sprout\datastudio\migrations\m211101_000008_migrate_reports_tables->safeUp()
#7 /app/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#8 /app/vendor/craftcms/cms/src/db/MigrationManager.php(149): craft\db\MigrationManager->migrateUp(Object(BarrelStrength\Sprout\datastudio\migrations\m211101_000008_migrate_reports_tables))
#9 /app/vendor/barrelstrength/sprout/src/core/db/MigrationHelper.php(47): craft\db\MigrationManager->up()
#10 /app/vendor/barrelstrength/sprout-data-studio/src/SproutDataStudio.php(71): BarrelStrength\Sprout\core\db\MigrationHelper::runMigrations(Object(BarrelStrength\SproutDataStudio\SproutDataStudio))
#11 /app/vendor/craftcms/cms/src/base/Plugin.php(154): BarrelStrength\SproutDataStudio\SproutDataStudio->afterInstall()
#12 /app/vendor/craftcms/cms/src/services/Plugins.php(533): craft\base\Plugin->install()
#13 /app/vendor/craftcms/cms/src/console/controllers/PluginController.php(281): craft\services\Plugins->installPlugin('sprout-data-stu...')
#14 /app/vendor/craftcms/cms/src/console/controllers/PluginController.php(143): craft\console\controllers\PluginController->_installPluginByHandle('sprout-data-stu...')
#15 [internal function]: craft\console\controllers\PluginController->actionInstall('sprout-data-stu...')
#16 /app/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#17 /app/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#18 /app/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction('install', Array)
#19 /app/vendor/craftcms/cms/src/console/ControllerTrait.php(87): yii\console\Controller->runAction('install', Array)
#20 /app/vendor/craftcms/cms/src/console/Controller.php(217): craft\console\Controller->traitRunAction('install', Array)
#21 /app/vendor/yiisoft/yii2/base/Module.php(552): craft\console\Controller->runAction('install', Array)
#22 /app/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('plugin/install', Array)
#23 /app/vendor/craftcms/cms/src/console/Application.php(90): yii\console\Application->runAction('plugin/install', Array)
#24 /app/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('plugin/install', Array)
#25 /app/vendor/craftcms/cms/src/console/Application.php(121): yii\console\Application->handleRequest(Object(craft\console\Request))
#26 /app/vendor/yiisoft/yii2/base/Application.php(384): craft\console\Application->handleRequest(Object(craft\console\Request))
#27 /app/craft(27): yii\base\Application->run()
#28 {main}
*** failed to install sprout-data-studio: An error occurred while executing the "BarrelStrength\Sprout\datastudio\migrations\m211101_000008_migrate_reports_tables migration: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sproutreports_reports.id' in 'field list'
The SQL being executed was: SELECT `sproutreports_reports`.`id` AS `id`, `sproutreports_reports`.`groupId` AS `groupId`, `sproutreports_reports`.`name` AS `name`, `sproutreports_reports`.`nameFormat` AS `nameFormat`, `sproutreports_reports`.`handle` AS `handle`, `sproutreports_reports`.`description` AS `description`, `sproutreports_reports`.`allowHtml` AS `allowHtml`, `sproutreports_reports`.`sortOrder` AS `sortOrder`, `sproutreports_reports`.`sortColumn` AS `sortColumn`, `sproutreports_reports`.`delimiter` AS `delimiter`, `sproutreports_reports`.`dataSourceId` AS `dataSourceId`, `sproutreports_reports`.`settings` AS `settings`, `sproutreports_reports`.`enabled` AS `enabled`, `sproutreports_reports`.`dateCreated` AS `dateCreated`, `sproutreports_reports`.`dateUpdated` AS `dateUpdated`, `sproutreports_reports`.`uid` AS `uid`, `elements_sites`.`siteId` AS `siteId`
FROM `craft_sproutreports_reports`
INNER JOIN `craft_elements_sites` ON `sproutreports_reports`.`id` = `elements_sites`.`elementId`

The problem is that the FROM query is missing aliases, Same goes for the INNER JOIN So instead of:

            $rows = (new Query())
                ->select($oldTableCols)
                ->from([self::OLD_REPORTS_TABLE])
                ->innerJoin(
                    Table::ELEMENTS_SITES,
                    '[[sproutreports_reports.id]] = [[elements_sites.elementId]]'
                )
                ->all();

It should be:

            $rows = (new Query())
                ->select($oldTableCols)
                ->from(['sproutreports_reports' => self::OLD_REPORTS_TABLE])
                ->innerJoin(
                   ['elements_sites' => Table::ELEMENTS_SITES],
                    '[[sproutreports_reports.id]] = [[elements_sites.elementId]]'
                )
                ->all();

How to reproduce

Try to migrate from the latest Craft 3.x & Sprout-Reports version, in my case it was:

        "barrelstrength/sprout-reports": "1.5.13",
        "barrelstrength/sprout-reports-commerce": "^1.0",

Sprout Version

4.45.1

Craft Version

4.4.13

Database Type Version

MySQL 8.0.31

anita-chouhan commented 1 year ago

Any updates on this issue? I am also getting the same error while migrating from Craft version 3.8.10.2 to Craft version 4.4.13.

Screenshot for reference:

Screenshot 2023-06-19 at 6 39 31 PM

Sprout Data Studio Version 4.45.1

Craft Version 4.4.13

Database Type Version MySQL 8.0.15

BenParizek commented 1 year ago

This issue has been addressed in Data Studio 4.45.2

Thanks to @Romanavr for the PR.