search

ethercreative / simplemap

A beautifully simple map field type for Craft CMS.
Other
136 stars 47 forks source link

Migration Error #264

Open MattWilcox opened 4 years ago

MattWilcox commented 4 years ago

Description

Steps to reproduce

  1. Try to run the update
Database Exception: SQLSTATE[HY000]: General error: 1553 Cannot drop index 'c3_maps_ownerId_ownerSiteId_fieldId_unq_idx': needed in a foreign key constraint
The SQL being executed was: DROP INDEX c3_maps_ownerId_ownerSiteId_fieldId_unq_idx ON c3_maps

Migration: ether\simplemap\migrations\m190712_104805_new_data_format

Output:

1. Creating Maps content columns
- Create content column for Map Location in content table
> add column field_mapLocation text to table {{%content}} ... done (time: 0.001s)
- Create content column for Map in matrix map
> add column field_map_map text to table {{%matrixcontent_mixedcontent}} ... done (time: 0.001s)
2. Creating new Maps table
> drop index c3_maps_ownerId_ownerSiteId_fieldId_unq_idx on {{%maps}} ...Exception: SQLSTATE[HY000]: General error: 1553 Cannot drop index 'c3_maps_ownerId_ownerSiteId_fieldId_unq_idx': needed in a foreign key constraint
The SQL being executed was: DROP INDEX c3_maps_ownerId_ownerSiteId_fieldId_unq_idx ON c3_maps (/Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/db/Schema.php:674)
#0 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/db/Command.php(1298): yii\db\Schema->convertException(Object(PDOException), 'DROP INDEX c3_...')
#1 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/db/Command.php(1093): yii\db\Command->internalExecute('DROP INDEX c3_...')
#2 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/db/Migration.php(507): yii\db\Command->execute()
#3 /Users/USER/Sites/PROJECT/7_website/craft/vendor/ether/simplemap/src/migrations/m190712_104805_new_data_format.php(234): yii\db\Migration->dropIndex('c3_maps_ownerId...', '{{%maps}}')
#4 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/db/Migration.php(52): ether\simplemap\migrations\m190712_104805_new_data_format->safeUp()
#5 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#6 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/db/MigrationManager.php(153): craft\db\MigrationManager->migrateUp(Object(ether\simplemap\migrations\m190712_104805_new_data_format))
#7 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/services/Updates.php(233): craft\db\MigrationManager->up()
#8 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(510): craft\services\Updates->runMigrations(Array)
#9 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/controllers/UpdaterController.php(203): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#10 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#11 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#12 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#13 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/web/Controller.php(178): yii\base\Controller->runAction('migrate', Array)
#14 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('migrate', Array)
#15 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/web/Application.php(291): yii\base\Module->runAction('updater/migrate', Array)
#16 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/web/Application.php(646): craft\web\Application->runAction('updater/migrate')
#17 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/web/Application.php(223): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#18 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#19 /Users/USER/Sites/PROJECT/7_website/public/index.php(21): yii\base\Application->run()
#20 {main}

Knowing this might fail, I tried running the suggested ./craft migrate/all

$ ./craft migrate/allYii Migration Tool (based on Yii v2.0.35)
    > dropping {{%projectconfig}} if it exists ... done (time: 0.043s)
    > create table {{%projectconfig}} ... done (time: 0.021s)
    > batch insert into {{%projectconfig}} ... done (time: 0.005s)
    > batch insert into {{%projectconfig}} ... done (time: 0.002s)
    > batch insert into {{%projectconfig}} ... done (time: 0.002s)
    > batch insert into {{%projectconfig}} ... done (time: 0.009s)
    > batch insert into {{%projectconfig}} ... done (time: 0.005s)
    > batch insert into {{%projectconfig}} ... done (time: 0.002s)
    > batch insert into {{%projectconfig}} ... done (time: 0.004s)
    > batch insert into {{%projectconfig}} ... done (time: 0.003s)
    > batch insert into {{%projectconfig}} ... done (time: 0.004s)
    > batch insert into {{%projectconfig}} ... done (time: 0.003s)
    > batch insert into {{%projectconfig}} ... done (time: 0.003s)
    > batch insert into {{%projectconfig}} ... done (time: 0.006s)
    > batch insert into {{%projectconfig}} ... done (time: 0.002s)
    > batch insert into {{%projectconfig}} ... done (time: 0.004s)
    > batch insert into {{%projectconfig}} ... done (time: 0.003s)
    > batch insert into {{%projectconfig}} ... done (time: 0.004s)
    > batch insert into {{%projectconfig}} ... done (time: 0.003s)
    > batch insert into {{%projectconfig}} ... done (time: 0.003s)
    > batch insert into {{%projectconfig}} ... done (time: 0.008s)
    > drop column config from table {{%info}} ... done (time: 0.019s)
    > dropping {{%drafts}} if it exists ... done (time: 0.002s)
    > dropping {{%revisions}} if it exists ... done (time: 0.002s)
    > create table {{%drafts}} ... done (time: 0.041s)
    > create table {{%revisions}} ... done (time: 0.117s)
    > add foreign key c3_drafts_creatorId_fk: {{%drafts}} (creatorId) references {{%users}} (id) ... done (time: 0.129s)
    > add foreign key c3_drafts_sourceId_fk: {{%drafts}} (sourceId) references {{%elements}} (id) ... done (time: 0.066s)
    > add foreign key c3_revisions_creatorId_fk: {{%revisions}} (creatorId) references {{%users}} (id) ... done (time: 0.055s)
    > add foreign key c3_revisions_sourceId_fk: {{%revisions}} (sourceId) references {{%elements}} (id) ... done (time: 0.053s)
    > create unique index c3_revisions_sourceId_num_unq_idx on {{%revisions}} (sourceId,num) ... done (time: 0.006s)
    > add column draftId integer AFTER `id` to table {{%elements}} ... done (time: 0.150s)
    > add column revisionId integer AFTER `draftId` to table {{%elements}} ... done (time: 0.044s)
    > add foreign key c3_elements_draftId_fk: {{%elements}} (draftId) references {{%drafts}} (id) ... done (time: 0.099s)
    > add foreign key c3_elements_revisionId_fk: {{%elements}} (revisionId) references {{%revisions}} (id) ... done (time: 0.094s)
    > dropping {{%entryversionerrors}} if it exists ... done (time: 0.007s)
    > dropping {{%entrydrafterrors}} if it exists ... done (time: 0.003s)
    > create table {{%entrydrafterrors}} ... done (time: 0.050s)
    > create table {{%entryversionerrors}} ... done (time: 0.037s)
    > add foreign key c3_entrydrafterrors_draftId_fk: {{%entrydrafterrors}} (draftId) references {{%entrydrafts}} (id) ... done (time: 0.043s)
    > add foreign key c3_entryversionerrors_versionId_fk: {{%entryversionerrors}} (versionId) references {{%entryversions}} (id) ... done (time: 0.054s)
    > add column propagationMethod string DEFAULT 'all' AFTER `enableVersioning` to table {{%sections}} ... done (time: 0.015s)
    > update in {{%sections}} ... done (time: 0.003s)
    > update in {{%sections}} ... done (time: 0.000s)
    > update in {{%sections}} ... done (time: 0.000s)
    > update in {{%sections}} ... done (time: 0.000s)
    > update in {{%sections}} ... done (time: 0.000s)
    > update in {{%sections}} ... done (time: 0.000s)
    > update in {{%sections}} ... done (time: 0.000s)
    > update in {{%sections}} ... done (time: 0.000s)
    > update in {{%sections}} ... done (time: 0.000s)
    > update in {{%sections}} ... done (time: 0.000s)
    > alter column propagationMethod in table {{%sections}} to string NOT NULL DEFAULT 'all' ... done (time: 0.023s)
    > drop column propagateEntries from table {{%sections}} ... done (time: 0.024s)
    > insert into {{%userpermissions}} ... done (time: 0.004s)
    > insert into {{%userpermissions}} ... done (time: 0.001s)
    > insert into {{%userpermissions}} ... done (time: 0.001s)
    > insert into {{%userpermissions_usergroups}} ... done (time: 0.004s)
    > insert into {{%userpermissions_usergroups}} ... done (time: 0.001s)
    > add column previewTargets text AFTER `propagationMethod` to table {{%sections}} ... done (time: 0.019s)
    > add column progressLabel string AFTER `progress` to table {{%queue}} ... done (time: 0.009s)
    > alter column creatorId in table {{%revisions}} to integer ... done (time: 0.011s)
    > alter column sourceId in table {{%drafts}} to integer ... done (time: 0.010s)
    > dropping {{%gqlschemas}} if it exists ...Exception: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails
The SQL being executed was: DROP TABLE IF EXISTS `c3_gqlschemas` (/Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/db/Schema.php:674)
#0 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/db/Command.php(1298): yii\db\Schema->convertException(Object(PDOException), 'DROP TABLE IF E...')
#1 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/db/Command.php(1093): yii\db\Command->internalExecute('DROP TABLE IF E...')
#2 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/db/Migration.php(316): yii\db\Command->execute()
#3 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/migrations/m190617_164400_add_gqlschemas_table.php(18): craft\db\Migration->dropTableIfExists('{{%gqlschemas}}')
#4 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/db/Migration.php(52): craft\migrations\m190617_164400_add_gqlschemas_table->safeUp()
#5 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#6 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/db/MigrationManager.php(153): craft\db\MigrationManager->migrateUp(Object(craft\migrations\m190617_164400_add_gqlschemas_table))
#7 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/services/Updates.php(225): craft\db\MigrationManager->up()
#8 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/console/controllers/MigrateController.php(260): craft\services\Updates->runMigrations(Array)
#9 [internal function]: craft\console\controllers\MigrateController->actionAll()
#10 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#11 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#12 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/console/Controller.php(164): yii\base\Controller->runAction('all', Array)
#13 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('all', Array)
#14 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#15 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/console/Application.php(87): yii\console\Application->runAction('migrate/all', Array)
#16 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('migrate/all', Array)
#17 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#18 /Users/USER/Sites/PROJECT/7_website/craft/craft(22): yii\base\Application->run()
#19 {main}
Exception 'craft\errors\MigrateException' with message 'An error occurred while migrating Craft CMS.'

in /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/services/Updates.php:241

Stack trace:
#0 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/console/controllers/MigrateController.php(260): craft\services\Updates->runMigrations(Array)
#1 [internal function]: craft\console\controllers\MigrateController->actionAll()
#2 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#3 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#4 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/console/Controller.php(164): yii\base\Controller->runAction('all', Array)
#5 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('all', Array)
#6 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate/all', Array)
#7 /Users/USER/Sites/PROJECT/7_website/craft/vendor/craftcms/cms/src/console/Application.php(87): yii\console\Application->runAction('migrate/all', Array)
#8 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('migrate/all', Array)
#9 /Users/USER/Sites/PROJECT/7_website/craft/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#10 /Users/USER/Sites/PROJECT/7_website/craft/craft(22): yii\base\Application->run()
#11 {main}

Additional info

Craft Pro 3.1.34.3 Maps 3.5.2

alloyking commented 4 years ago

Same here Craft 3.4 Upgrading to simplemaps 3.8.4.1

NicolasSchmutz commented 4 years ago

same here. plz gogogo 🙏

alloyking commented 4 years ago

The issue appears here: migrations/m190712_104805_new_data_format.php

$this->getDb()->createCommand('SET foreign_key_checks=0;')->execute();

                foreach ($indexNames as $name)
                    $this->dropIndex($name, MapRecord::TableName);

                $this->getDb()->createCommand('SET foreign_key_checks=1;')->execute();
alloyking commented 4 years ago

I can confirm that trying this in SQL fails as well

SET foreign_key_checks=0;
ALTER TABLE `craft_maps` DROP INDEX craft_maps_ownerId_ownerSiteId_fieldId_unq_idx;
abryrath commented 3 years ago

Any updates on this? It is very frustrating that there is no feedback whatsoever 1 year later.