simplemap Upgrading from Craft 2 -> 3 and installing this gives migration error for creating maps table

Description

I have upgraded from Craft 2 which used the Craft 2 version of this plugin to Craft 3. I'm now trying to install the plugin but it keeps failing.

Steps to reproduce

Upgrade existing Craft2 site using this plugin to Craft 3.
Try to install this plugin

Additional info

Craft version: 3.3.9
Maps version: 3.6.4.3
PHP version: 7.2
Database driver & version: MySql 5.7
Other Plugins: Supertable & Freeform

The SQL being executed was: ROLLBACK TO SAVEPOINT LEVEL1
2019-10-15 21:27:08 [-][60226][-][error][craft\db\MigrationManager::migrateUp] Failed to apply Install (time: 0.010s). Output:
    > create table {{%maps}} ...Exception: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'craft_maps' already exists
The SQL being executed was: CREATE TABLE `craft_maps` (
	`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

Oct 15 '19 20:10 johnwbaxter

Hey @johnwbaxter

Could you try updating to the latest version 3.7 and see if the issue persists? We updated a few things in that release that aim to fix some migration issues.

Oct 16 '19 09:10 alexjcollins

Hey @alexjcollins i have tried that but now getting this:

Exception: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '9591-1-67' for key 'craft_maps_ownerId_ownerSiteId_fieldId_unq_idx'
The SQL being executed was: INSERT INTO `craft_maps` (`ownerId`, `ownerSiteId`, `fieldId`, `lat`, `lng`, `dateCreated`, `dateUpdated`, `uid`) VALUES (9591, 1, 67, '37.56014090', '-1.27325760', '2019-10-16 10:45:15', '2019-10-16 10:45:15', '90dbd3a0-31da-4237-a845-63360f2def2d') (/var/www/vendor/yiisoft/yii2/db/Schema.php:664)
#0 /var/www/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `cr...')
#1 /var/www/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('INSERT INTO `cr...')
#2 /var/www/vendor/yiisoft/yii2/db/Schema.php(433): yii\db\Command->execute()
#3 /var/www/vendor/yiisoft/yii2/db/ActiveRecord.php(600): yii\db\Schema->insert('{{%maps}}', Array)
#4 /var/www/vendor/yiisoft/yii2/db/ActiveRecord.php(566): yii\db\ActiveRecord->insertInternal(NULL)
#5 /var/www/vendor/yiisoft/yii2/db/BaseActiveRecord.php(678): yii\db\ActiveRecord->insert(false, NULL)
#6 /var/www/vendor/ether/simplemap/src/services/MapService.php(117): yii\db\BaseActiveRecord->save(false)
#7 /var/www/vendor/ether/simplemap/src/migrations/m190226_143809_craft3_upgrade.php(98): ether\simplemap\services\MapService->saveRecord(Object(ether\simplemap\models\Map), true)
#8 /var/www/vendor/ether/simplemap/src/migrations/m190226_143809_craft3_upgrade.php(48): ether\simplemap\migrations\m190226_143809_craft3_upgrade->_upgrade2()
#9 /var/www/vendor/ether/simplemap/src/migrations/Install.php(98): ether\simplemap\migrations\m190226_143809_craft3_upgrade->safeUp()
#10 /var/www/vendor/craftcms/cms/src/db/Migration.php(56): ether\simplemap\migrations\Install->safeUp()
#11 /var/www/vendor/craftcms/cms/src/db/MigrationManager.php(239): craft\db\Migration->up(true)
#12 /var/www/vendor/craftcms/cms/src/base/Plugin.php(152): craft\db\MigrationManager->migrateUp(Object(ether\simplemap\migrations\Install))
#13 /var/www/vendor/craftcms/cms/src/services/Plugins.php(520): craft\base\Plugin->install()
#14 /var/www/vendor/craftcms/cms/src/controllers/PluginsController.php(53): craft\services\Plugins->installPlugin('simplemap', 'lite')
#15 [internal function]: craft\controllers\PluginsController->actionInstallPlugin()
#16 /var/www/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#17 /var/www/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#18 /var/www/vendor/craftcms/cms/src/web/Controller.php(187): yii\base\Controller->runAction('install-plugin', Array)
#19 /var/www/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('install-plugin', Array)
#20 /var/www/vendor/craftcms/cms/src/web/Application.php(299): yii\base\Module->runAction('plugins/install...', Array)
#21 /var/www/vendor/craftcms/cms/src/web/Application.php(566): craft\web\Application->runAction('plugins/install...', Array)
#22 /var/www/vendor/craftcms/cms/src/web/Application.php(278): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#23 /var/www/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#24 /var/www/public/index.php(21): yii\base\Application->run()
#25 {main}

Oct 16 '19 10:10 johnwbaxter

@johnwbaxter Have you tried running the update more than once on the same database? It looks like parts of the upgrade were initially successful but are now failing since they already exist in the database.

Oct 24 '19 10:10 Tam

@Tam No, it fails first time.

Oct 28 '19 10:10 johnwbaxter

@Tam Any news on this one?

Oct 30 '19 09:10 johnwbaxter

@Tam Hello?

Nov 01 '19 09:11 johnwbaxter

@johnwbaxter Are you able to send a copy of your database to [email protected]?

Nov 01 '19 09:11 alexjcollins

@alexjcollins Sorry for late reply. No, cannot send copy of DB for GDPR reasons. I can't even uninstall the plugin now, it's going to cause me a lot of grief :(

Dec 11 '19 22:12 johnwbaxter

@johnwbaxter sorry to hear that :( What error do you get when you try to uninstall it?

Dec 11 '19 22:12 alexjcollins

@alexjcollins Sorry, I take that back, I can uninstall, but then I can't re-install. I think it hasn't properly uninstalled.

I get this error:

2019-12-11 22:33:43 [-][60226][-][warning][craft\db\mysql\Schema::rollBackSavepoint] Tried to roll back a savepoint, but it does not exist: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT LEVEL1 does not exist
The SQL being executed was: ROLLBACK TO SAVEPOINT LEVEL1
2019-12-11 22:33:43 [-][60226][-][error][craft\db\MigrationManager::migrateUp] Failed to apply Install (time: 0.158s). Output:
    > create table {{%maps}} ... done (time: 0.036s)
    > create unique index craft_maps_ownerId_ownerSiteId_fieldId_unq_idx on {{%maps}} (ownerId,ownerSiteId,fieldId) ... done (time: 0.026s)
    > create index craft_maps_lat_idx on {{%maps}} (lat) ... done (time: 0.026s)
    > create index craft_maps_lng_idx on {{%maps}} (lng) ... done (time: 0.026s)
    > add foreign key craft_maps_ownerId_fk: {{%maps}} (ownerId) references {{%elements}} (id) ...Exception: SQLSTATE[23000]: Integrity constraint violation: 1022 Can't write; duplicate key in table '#sql-1_2488'
The SQL being executed was: ALTER TABLE `craft_maps` ADD CONSTRAINT `craft_maps_ownerId_fk` FOREIGN KEY (`ownerId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE (/var/www/vendor/yiisoft/yii2/db/Schema.php:674)
#0 /var/www/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE `cr...')
#1 /var/www/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('ALTER TABLE `cr...')
#2 /var/www/vendor/yiisoft/yii2/db/Migration.php(467): yii\db\Command->execute()
#3 /var/www/vendor/craftcms/cms/src/db/Migration.php(373): yii\db\Migration->addForeignKey('craft_maps_owne...', '{{%maps}}', Array, '{{%elements}}', Array, 'CASCADE', NULL)
#4 /var/www/vendor/ether/simplemap/src/migrations/Install.php(74): craft\db\Migration->addForeignKey('craft_maps_owne...', '{{%maps}}', Array, '{{%elements}}', Array, 'CASCADE')
#5 /var/www/vendor/craftcms/cms/src/db/Migration.php(58): ether\simplemap\migrations\Install->safeUp()
#6 /var/www/vendor/craftcms/cms/src/db/MigrationManager.php(239): craft\db\Migration->up(true)
#7 /var/www/vendor/craftcms/cms/src/base/Plugin.php(155): craft\db\MigrationManager->migrateUp(Object(ether\simplemap\migrations\Install))
#8 /var/www/vendor/craftcms/cms/src/services/Plugins.php(520): craft\base\Plugin->install()
#9 /var/www/vendor/craftcms/cms/src/controllers/PluginsController.php(53): craft\services\Plugins->installPlugin('simplemap', 'lite')
#10 [internal function]: craft\controllers\PluginsController->actionInstallPlugin()
#11 /var/www/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#12 /var/www/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#13 /var/www/vendor/craftcms/cms/src/web/Controller.php(187): yii\base\Controller->runAction('install-plugin', Array)
#14 /var/www/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('install-plugin', Array)
#15 /var/www/vendor/craftcms/cms/src/web/Application.php(299): yii\base\Module->runAction('plugins/install...', Array)
#16 /var/www/vendor/craftcms/cms/src/web/Application.php(565): craft\web\Application->runAction('plugins/install...', Array)
#17 /var/www/vendor/craftcms/cms/src/web/Application.php(278): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#18 /var/www/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#19 /var/www/public/index.php(21): yii\base\Application->run()

Dec 11 '19 22:12 johnwbaxter

@johnwbaxter Thanks for sharing. We'll see what we can do with this and get back to you.

Dec 11 '19 22:12 alexjcollins

Thanks @alexjcollins

Dec 11 '19 22:12 johnwbaxter

Any word on this since? I am performing essentially the same update:

Craft 2.6.3105 > 3.4.0 Simplemap 1.8.1 > 3.7

Using the Craft Upgrade Guide you’re starting from scratch and just copying in content, settings and DB. I hit the same error on first install and the same error if retried.

*** installing simplemap
    > create table {{%maps}} ...Exception: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'craft_maps' already exists
The SQL being executed was: CREATE TABLE `craft_maps` (
	`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`ownerId` int(11) NOT NULL,
	`ownerSiteId` int(11),
	`fieldId` int(11) NOT NULL,
	`lat` decimal(11,9),
	`lng` decimal(12,9),
	`dateCreated` datetime(0) NOT NULL,
	`dateUpdated` datetime(0) NOT NULL,
	`uid` char(36) NOT NULL DEFAULT '0'
) ENGINE=InnoDb DEFAULT CHARSET=utf8 (/home/vagrant/app/vendor/yiisoft/yii2/db/Schema.php:674)
#0 /home/vagrant/app/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'CREATE TABLE `c...')
#1 /home/vagrant/app/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('CREATE TABLE `c...')
#2 /home/vagrant/app/vendor/yiisoft/yii2/db/Migration.php(323): yii\db\Command->execute()
#3 /home/vagrant/app/vendor/ether/simplemap/src/migrations/Install.php(41): yii\db\Migration->createTable('{{%maps}}', Array)
#4 /home/vagrant/app/vendor/craftcms/cms/src/db/Migration.php(52): ether\simplemap\migrations\Install->safeUp()
#5 /home/vagrant/app/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#6 /home/vagrant/app/vendor/craftcms/cms/src/base/Plugin.php(140): craft\db\MigrationManager->migrateUp(Object(ether\simplemap\migrations\Install))
#7 /home/vagrant/app/vendor/craftcms/cms/src/services/Plugins.php(517): craft\base\Plugin->install()
#8 /home/vagrant/app/vendor/craftcms/cms/src/console/controllers/InstallController.php(189): craft\services\Plugins->installPlugin('simplemap')
#9 [internal function]: craft\console\controllers\InstallController->actionPlugin('simplemap')
#10 /home/vagrant/app/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#11 /home/vagrant/app/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#12 /home/vagrant/app/vendor/yiisoft/yii2/console/Controller.php(164): yii\base\Controller->runAction('plugin', Array)
#13 /home/vagrant/app/vendor/craftcms/cms/src/console/Controller.php(187): yii\console\Controller->runAction('plugin', Array)
#14 /home/vagrant/app/vendor/yiisoft/yii2/base/Module.php(528): craft\console\Controller->runAction('plugin', Array)
#15 /home/vagrant/app/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('install/plugin', Array)
#16 /home/vagrant/app/vendor/craftcms/cms/src/console/Application.php(87): yii\console\Application->runAction('install/plugin', Array)
#17 /home/vagrant/app/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('install/plugin', Array)
#18 /home/vagrant/app/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#19 /home/vagrant/app/craft(22): yii\base\Application->run()
#20 {main}

I installed simplemap first in this process, but Redactor is the only other plugin, and I got the same if it went first (and it did succeed).

Thanks!

Feb 10 '20 21:02 mckelvey

My post is non-rush. Thankfully I only had less than fifteen uses. My solution to get this working was to dump all data related to SimpleMaps, install SimpleMaps (having to delete the table craft_maps manually) and then re-run the install. Once installed, I was then able to re-create the fields and re-enter the data.

Feb 10 '20 22:02 mckelvey

@mckelvey I had to carry out the same steps. Removing the craft_maps table, installing the plugin and then adding the data back in manually did the trick for me. This could really be a pain if a user had hundreds or more instances.

Feb 10 '20 22:02 jamiehunt841

Also running into this issue.

It seems the issue is only occurring for translated map values, eg. multiple records in the old database table for the same record (but with a different language).

It seems that it's always trying to insert records with my default site (ID 1) instead of using the new site that was created by craft earlier. I'd guess it's failing somewhere in the getSiteByLocale function in the craft3_upgrade migration.

Since I only have one record which is multilingual, I ended up "fixing" the issue by simply removing one of the translated values in the old table and re-added it manually in the admin CP after installing the plugin again.

Feb 11 '20 17:02 FreekVR

I'm getting the same problems, 3.3.5 -> 3.7.7

~~Even if I uninstall the plugin, then manually remove all the tables, I then can't re-activate the plugin. I can install using composer, but it won't activate: "Couldn’t install plugin." when I hit the Install button in the dashboard.~~ I need to remove the 'map's table too.

Feb 12 '20 16:02 iparr

simplemap simplemap copied to clipboard

Upgrading from Craft 2 -> 3 and installing this gives migration error for creating maps table

Description

Steps to reproduce

Additional info

simplemap
simplemap copied to clipboard