Cms: Transferring content when deleting sites is error-prone

[timkelty]

Looks like it chokes on duplicate URIs…

2018-08-27 15:48:15 [::1][2][-][info][yii\db\Command::execute] UPDATE `elements_sites` SET `siteId`=1, `dateUpdated`='2018-08-27 19:48:15' WHERE `elementId` IN ('253', '254', '255', '258', '327', '419', '257')
2018-08-27 15:48:15 [::1][2][-][profile begin][yii\db\Command::execute] UPDATE `elements_sites` SET `siteId`=1, `dateUpdated`='2018-08-27 19:48:15' WHERE `elementId` IN ('253', '254', '255', '258', '327', '419', '257')
2018-08-27 15:48:15 [::1][2][-][profile end][yii\db\Command::execute] UPDATE `elements_sites` SET `siteId`=1, `dateUpdated`='2018-08-27 19:48:15' WHERE `elementId` IN ('253', '254', '255', '258', '327', '419', '257')
2018-08-27 15:48:15 [::1][2][-][error][yii\db\IntegrityException] PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'about-us-1' for key 'elements_sites_uri_siteId_unq_idx' in /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/db/Command.php:994
Stack trace:
#0 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/db/Command.php(994): PDOStatement->execute()
#1 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/services/Sites.php(840): yii\db\Command->execute()
#2 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/services/Sites.php(753): craft\services\Sites->deleteSite(Object(craft\models\Site), 1)
#3 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/controllers/SitesController.php(321): craft\services\Sites->deleteSiteById(2, 1)
#4 [internal function]: craft\controllers\SitesController->actionDeleteSite()
#5 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#6 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#7 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Controller.php(80): yii\base\Controller->runAction('delete-site', Array)
#8 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('delete-site', Array)
#9 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(240): yii\base\Module->runAction('sites/delete-si...', Array)
#10 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(450): craft\web\Application->runAction('sites/delete-si...', Array)
#11 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(211): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#12 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#13 /Users/timkelty/Sites/rdac/lib/bootstrap.php(36): yii\base\Application->run()
#14 /Users/timkelty/Sites/rdac/sites/rdac/public/index.php(2): require_once('/Users/timkelty...')
#15 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'about-us-1' for key 'elements_sites_uri_siteId_unq_idx'
The SQL being executed was: UPDATE `elements_sites` SET `siteId`=1, `dateUpdated`='2018-08-27 19:48:15' WHERE `elementId` IN ('253', '254', '255', '258', '327', '419', '257') in /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/db/Schema.php:595
Stack trace:
#0 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/db/Command.php(1004): yii\db\Schema->convertException(Object(PDOException), 'UPDATE `element...')
#1 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/services/Sites.php(840): yii\db\Command->execute()
#2 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/services/Sites.php(753): craft\services\Sites->deleteSite(Object(craft\models\Site), 1)
#3 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/controllers/SitesController.php(321): craft\services\Sites->deleteSiteById(2, 1)
#4 [internal function]: craft\controllers\SitesController->actionDeleteSite()
#5 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#6 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#7 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Controller.php(80): yii\base\Controller->runAction('delete-site', Array)
#8 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('delete-site', Array)
#9 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(240): yii\base\Module->runAction('sites/delete-si...', Array)
#10 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(450): craft\web\Application->runAction('sites/delete-si...', Array)
#11 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(211): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#12 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#13 /Users/timkelty/Sites/rdac/lib/bootstrap.php(36): yii\base\Application->run()
#14 /Users/timkelty/Sites/rdac/sites/rdac/public/index.php(2): require_once('/Users/timkelty...')
#15 {main}
Additional Information:
Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry 'about-us-1' for key 'elements_sites_uri_siteId_unq_idx'
)

2018-08-27 15:48:14 [::1][2][-][info][application] $_GET = [
    'p' => 'cp/actions/sites/delete-site'
]
2018-08-27 15:48:15 [::1][2][-][info][yii\db\Command::execute] UPDATE `elements_sites` SET `siteId`=1, `dateUpdated`='2018-08-27 19:48:15' WHERE `elementId` IN ('253', '254', '255', '258', '327', '419', '257')
2018-08-27 15:48:15 [::1][2][-][profile begin][yii\db\Command::execute] UPDATE `elements_sites` SET `siteId`=1, `dateUpdated`='2018-08-27 19:48:15' WHERE `elementId` IN ('253', '254', '255', '258', '327', '419', '257')
2018-08-27 15:48:15 [::1][2][-][profile end][yii\db\Command::execute] UPDATE `elements_sites` SET `siteId`=1, `dateUpdated`='2018-08-27 19:48:15' WHERE `elementId` IN ('253', '254', '255', '258', '327', '419', '257')
2018-08-27 15:48:15 [::1][2][-][error][yii\db\IntegrityException] PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'about-us-1' for key 'elements_sites_uri_siteId_unq_idx' in /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/db/Command.php:994
Stack trace:
#0 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/db/Command.php(994): PDOStatement->execute()
#1 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/services/Sites.php(840): yii\db\Command->execute()
#2 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/services/Sites.php(753): craft\services\Sites->deleteSite(Object(craft\models\Site), 1)
#3 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/controllers/SitesController.php(321): craft\services\Sites->deleteSiteById(2, 1)
#4 [internal function]: craft\controllers\SitesController->actionDeleteSite()
#5 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#6 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#7 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Controller.php(80): yii\base\Controller->runAction('delete-site', Array)
#8 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('delete-site', Array)
#9 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(240): yii\base\Module->runAction('sites/delete-si...', Array)
#10 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(450): craft\web\Application->runAction('sites/delete-si...', Array)
#11 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(211): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#12 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#13 /Users/timkelty/Sites/rdac/lib/bootstrap.php(36): yii\base\Application->run()
#14 /Users/timkelty/Sites/rdac/sites/rdac/public/index.php(2): require_once('/Users/timkelty...')
#15 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'about-us-1' for key 'elements_sites_uri_siteId_unq_idx'
The SQL being executed was: UPDATE `elements_sites` SET `siteId`=1, `dateUpdated`='2018-08-27 19:48:15' WHERE `elementId` IN ('253', '254', '255', '258', '327', '419', '257') in /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/db/Schema.php:595
Stack trace:
#0 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/db/Command.php(1004): yii\db\Schema->convertException(Object(PDOException), 'UPDATE `element...')
#1 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/services/Sites.php(840): yii\db\Command->execute()
#2 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/services/Sites.php(753): craft\services\Sites->deleteSite(Object(craft\models\Site), 1)
#3 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/controllers/SitesController.php(321): craft\services\Sites->deleteSiteById(2, 1)
#4 [internal function]: craft\controllers\SitesController->actionDeleteSite()
#5 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#6 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#7 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Controller.php(80): yii\base\Controller->runAction('delete-site', Array)
#8 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('delete-site', Array)
#9 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(240): yii\base\Module->runAction('sites/delete-si...', Array)
#10 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(450): craft\web\Application->runAction('sites/delete-si...', Array)
#11 /Users/timkelty/Sites/rdac/vendor/composer/craftcms/cms/src/web/Application.php(211): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#12 /Users/timkelty/Sites/rdac/vendor/composer/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))
#13 /Users/timkelty/Sites/rdac/lib/bootstrap.php(36): yii\base\Application->run()
#14 /Users/timkelty/Sites/rdac/sites/rdac/public/index.php(2): require_once('/Users/timkelty...')
#15 {main}
Additional Information:
Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry 'about-us-1' for key 'elements_sites_uri_siteId_unq_idx'
)

2018-08-27 15:48:14 [::1][2][-][info][application] $_GET = [
    'p' => 'cp/actions/sites/delete-site'
]

[brandonkelly]

Can you post steps to reproduce? It’s hard to tell from your title what exactly you’re doing when this occurs.

FWIW the error you’re getting won’t be possible in the next release though, as element URIs’ uniqueness is no longer going to be enforced by the database. (https://github.com/craftcms/cms/commit/b4b7c6bb92273832f5d052d12cee203093ded15c#diff-0f41c7962fd021be270e235dd1391a2c)

[thisisjamessmith]

I'm getting a similar error when clicking on the 'save as new entry' button in an entry:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'the-uri-path/the-original-entry-slug-1' for key 'elements_sites_uri_siteId_unq_idx'
The SQL being executed was: INSERT INTO `craft_elements_sites` (`elementId`, `siteId`, `slug`, `uri`, `enabled`, `dateCreated`, `uid`, `dateUpdated`) VALUES (7623, 1, 'the-original-entry-slug', 'the-uri-path/the-original-entry-slug', 1, '2019-01-17 16:45:51', '59bb9841-c0f0-4861-9a01-506c38a72632', '2019-01-17 16:45:51')

Running Craft 3.0.36, multi-site with 2 sites. It only happens on entries that have URLs.

The error is thrown from line 521 of /src/controllers/EntriesController.php

throw new ServerErrorHttpException(Craft::t('app', 'An error occurred when duplicating the entry.'), 0, $e);

[thisisjamessmith]

Ahh, I think I've found my problem... At some point in the distant past, the site was created in 3.0 without any table prefixes. Shortly after installation it was decided that we wanted to keep the craft_ prefix. Instead of starting over, a query was run against all the tables to add the prefix... but this doesn't update the indexes, gahh. So now all our indexes are a mix of craft_ prefixed names and non-prefixed names. Any advice on how I can fix this mess?

[brandonkelly]

@thisisjamessmith yikes… you can try this:

1. make a DB backup
2. update to Craft 3.1 and enable the useProjectConfigFile config setting
3. ensure you have a config/project.yaml file
4. make a new DB backup, but leave out any DROP and CREATE statements, or FK/index definitions, etc. So just the table data, nothing else.
5. delete your database and recreate it with the same name
6. run the Craft installer now that the DB is fresh
7. you should have all your old sections, fields, etc., still in-tact thanks to project.yaml
8. import the 2nd SQL backup

That should import all the data into a freshly-created new DB schema, with all the right indexes in place.

No idea if it will work but it could in theory!

[narration-sd]

...second one that made me smile today :)

[brandonkelly]

Actually that idea won’t work if you have any plugins installed that make their own schema changes, and don’t support project config “yet”.

You may just need to manually check each individual index, comparing them to a fresh install with the same plugins, sections, fields, etc.. :-/

[thisisjamessmith]

@brandonkelly Thanks for the advice! Sadly it doesn't quite work - when importing the data I'm getting a handful of errors mostly along the lines of [ERROR in query 30] Duplicate entry '1' for key 'PRIMARY'. I think it might be to do with Singles, which seem to be automatically recreated from Project Config

[thisisjamessmith]

...so, just before your step-8, I tried truncating all tables and _then_ importing the content, and at least my initial checks show that everything _seems_ to be working ok. (Just for my own reference, or any other poor souls arriving here from Google... to do that in Sequel Pro, you first need to run SET FOREIGN_KEY_CHECKS=0;, then select all tables, right-click and choose 'Truncate Tables'... then put the fk checks back again with SET FOREIGN_KEY_CHECKS=1;).

So for completeness we now need to double-check all indexes on all tables (key names, column names, and existence) against a fresh install with the same plugins in case a plugin has added/removed/altered any of them... There goes the weekend!

[brandonkelly]

@thisisjamessmith Another option is you could just programmatically add them back in.

1. Creating a full DB backup
2. Copy the backup and store it someplace safe in case something goes wrong.
3. Find all CREATE INDEX lines in the main backup and delete them
4. Delete the database and create a new one with the same name
5. Import the backup (sans CREATE INDEX lines)
6. Make a copy of your web/index.php to web/restore-indexes.php, and delete the last $app->run(); line in it

7. At the end of restore-indexes.php, add this:

   ```php
   $db = \Craft::$app->db;

   // Add standard Craft indexes
   (new \craft\migrations\Install())->createIndexes();

   // Add Matrix field indexes
   $fields = \Craft::$app->getFields()->getAllFields();
   foreach ($fields as $field) {
   if ($field instanceof \craft\fields\Matrix) {
   $name = $db->getIndexName($field->contentTable, ['elementId', 'siteId'], true);
   $db->createCommand()->createIndex($name, field->contentTable, ['elementId', 'siteId'], true)->execute();
   }
   }

Check your plugins’ install migrations to see if they are adding any indexes on install as well, and if you have Neo or Super Table installed you will likely need to recreate that Matrix field logic for them as well.

[thisisjamessmith]

@brandonkelly wow, thanks so much for this - sounds like a great idea. I've actually found a handful of historical sites that will also be suffering from the same problem (they were installed from our boilerplate DB), so this will come in very handy.

[narration-sd]

...and this approach may well help with a database I would like to save as well, thanks @brandonkelly

It's more than a bit clotted up with 'things' that happened at a stage of Neo's Craft 3 conversion, but do not ask ;)