2

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.

1

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,craft\elements\User
8,craft\elements\Asset
10,craft\elements\MatrixBlock
12,craft\elements\MatrixBlock
13,craft\elements\GlobalSet
18,craft\elements\Asset
19,craft\elements\MatrixBlock
21,craft\elements\MatrixBlock
25,craft\elements\Asset
28,craft\elements\MatrixBlock

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.