TransWikia.com

Deleted rows from elements_sites hunting stray slugs now I can't login

Craft CMS Asked on July 11, 2021

Working on a migration up/down testing I went through and deleted TOO much from the element_sites table to remove unwanted slugs, not understanding really what’s in there. Now I understand many elements are in there and I’ve deleted some vital stuff without a backup.

Is there a way to perhaps use these config/*.yaml files to rebuild the tables or something to that affect?

At the very least, when logging in my username and password no longer works, and forgot your password gives no joy. What a mess. Can the project be salvaged or am I looking at starting fresh?

I have time machine backups on my mac but as a docker newbie I didn’t have the VM included in the backup. I believe the mysql data is all stored in the VM that nitro creates.

One Answer

I'll post how I've begun to fix this. Since I couldn't login, I assume there was entries I removed from elements_sites relating to my user account. I did a query to see what elements I had that were not in elements_sites:

SELECT elements.id, elements.type
FROM elements
WHERE NOT EXISTS
    (SELECT *
     FROM elements_sites
     WHERE elements.id = elements_sites.elementId)
ORDER BY elements.id ASC

Which returned something like

1,craftelementsUser
8,craftelementsAsset
10,craftelementsMatrixBlock
12,craftelementsMatrixBlock
13,craftelementsGlobalSet
18,craftelementsAsset
19,craftelementsMatrixBlock
21,craftelementsMatrixBlock
25,craftelementsAsset
28,craftelementsMatrixBlock

Seeing that my User element was missing, I created a new entry in elements_sites for it:

INSERT INTO nitro.elements_sites 
    (id, elementId, siteId, slug, uri, enabled, dateCreated, dateUpdated, uid) 
VALUES 
    (1, 1, 1, null, null, 1, null, null, DEFAULT)

I was now able to login again.

I know that the rest of the entries I deleted also had null slugs so I created entries in elements_sites for all the others I deleted. There were thousands. It would have been nice if I was an sql maestro but the best I could do was export an CSV of the results of my initial missing elements query, and then looped through the csv while creating an entry for each:

// Loop through CSV
$file = fopen("elements.csv","r");

while (($data = fgetcsv($file)) !== FALSE)
{
    // create query 
    $id = $data[0];

    // INSERT (table name, column values)
    $insert = Craft::$app->db->createCommand()->insert('elements_sites', [
        'id' => $id,
        'elementId' => $id,
        'siteId' => 1,
        'slug' => null,
        'uri' => null,
        'enabled' => 1,
        'dateCreated' => '2021-04-03 04:19:48',
        'dateUpdated' => '2021-04-03 04:19:48',
    ])->execute();


}

Happy to say the site is now back at 100% health and I've made a backup of the db.

As a side note, it was pretty disappointing to learn that docker by default isn't backing up to Time Machine. I could have just restored the docker image but didn't realise there's these images aren't being backed up. A valuable lesson in making regular backups and checks along the way.

Correct answer by Robert on July 11, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP