<p>I'll post how I've begun to fix this. Since I couldn't login, I assume there was entries I removed from <code>elements_sites</code> relating to my user account. I did a query to see what <code>elements</code> I had that were not in <code>elements_sites</code>:</p>
<pre><code>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
</code></pre>
<p>Which returned something like</p>
<pre><code>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
</code></pre>
<p>Seeing that my User element was missing, I created a new entry in <code>elements_sites</code> for it:</p>
<pre><code>INSERT INTO nitro.elements_sites 
    (id, elementId, siteId, slug, uri, enabled, dateCreated, dateUpdated, uid) 
VALUES 
    (1, 1, 1, null, null, 1, null, null, DEFAULT)
</code></pre>
<p>I was now able to login again.</p>
<p>I know that the rest of the entries I deleted also had null slugs so I created entries in <code>elements_sites</code> 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:</p>
<pre><code>// Loop through CSV
$file = fopen(&quot;elements.csv&quot;,&quot;r&quot;);

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

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


}
</code></pre>
<p>Happy to say the site is now back at 100% health and I've made a backup of the db.</p>
<p>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.</p>

CC BY-SA 4.0
2021-04-05T02:16:00.717Z
2021-04-05T02:16:00.717Z
Robert
<p>Working on a migration up/down testing I went through and deleted TOO much from the <code>element_sites</code> 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.</p>
<p>Is there a way to perhaps use these config/*.yaml files to rebuild the tables or something to that affect?</p>
<p>At the very least, when logging in my username and password no longer works, and <code>forgot your password</code> gives no joy. What a mess. Can the project be salvaged or am I looking at starting fresh?</p>
<p>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.</p>

<mysql><back-up>
Deleted rows from elements_sites hunting stray slugs now I can't login
# Deleted rows from elements_sites hunting stray slugs now I can't login

(Asked by: username_0 on 2021-04-03)

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.

**Tags:** mysql, back-up


---

## 1 answers

### Answer by username_0 on 2021-04-05. Score: 1 | Accepted 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,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.


---

