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.
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP