Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-05T02:16:00.717Z
Answers.LastActivityDate: 2021-04-05T02:16:00.717Z
Answers.UserDisplayName: Robert
Body: <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>
  
Tags: <mysql><back-up>
Title: Deleted rows from elements_sites hunting stray slugs now I can't login
ThreadText: # 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.
  
  
  ---
  
  