<p>The <code>id</code> primary key column in the <code>sessions</code> table (and nearly all Craft tables) is set to be auto-incrementing (if you're on MySQL).  It should look something like:</p>

<pre><code>CREATE TABLE `sessions`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `token` char(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `dateCreated` datetime(0) NOT NULL,
  `dateUpdated` datetime(0) NOT NULL,
  `uid` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `sessions_uid_idx`(`uid`) USING BTREE,
  INDEX `sessions_token_idx`(`token`) USING BTREE,
  INDEX `sessions_dateUpdated_idx`(`dateUpdated`) USING BTREE,
  INDEX `sessions_userId_idx`(`userId`) USING BTREE,
  CONSTRAINT `sessions_userId_fk` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
</code></pre>

<p>It sounds to me like that table is missing it's <code>AUTO_INCREMENT</code> on the <code>id</code> column.  Craft assumes it's there and that the database will automatically insert it.</p>

CC BY-SA 4.0
2019-03-29T10:38:48.467Z
Hi Brad, thanks for getting back to me —i've amended my question to clarify that I'm using postgres as the DB. 

The problem, (as I found out here: https://stackoverflow.com/questions/49356043/postgresql-error-relation-sequence-does-not-exist-while-restoring-from-dump) was that I was exporting from postgres 10.4 and hadn't updated the postgres version in my local environment. As a result the sequences were not being recreated correctly.

Changed the version and all is good.
Piaras Hoban
CC BY-SA 4.0
2019-03-30T04:24:39.197Z
Awesome and thanks for the follow-up! I thought about adding postgres in the answer, but figured since most people are using MySQL that'd probably cover it.
Brad Bell
CC BY-SA 4.0
2019-03-29T03:09:36.853Z
2019-03-29T03:09:36.853Z
Brad Bell
<p>I'm attempting to restore a postgres DB backup on to my local dev environment but I encounter an error when trying to login as the admin user (the salient parts being):</p>

<pre><code>SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "id" violates not-null constraint
</code></pre>

<p>and </p>

<pre><code>The SQL being executed was: INSERT INTO "sessions"
</code></pre>

<p>So it seems as though it correctly authenticates my username and password and then tries to create a new entry in the sessions table but fails to do so because the session service is returning a null value.</p>

<p>Is this interpretation correct?</p>

<p>I'm using redis for my session storage, so I tried clearing the keys from there but it doesn't fix the issue.</p>

<p>I'm really blocked on this one and would appreciate any thoughts!</p>

<p>kind regards,</p>

<p>piaras hoban</p>

<craft3><database><back-up>
Can't login after restoring production DB locally
# Can't login after restoring production DB locally

(Asked by: username_0 on 2019-03-28)

I'm attempting to restore a postgres DB backup on to my local dev environment but I encounter an error when trying to login as the admin user (the salient parts being):
SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "id" violates not-null constraint

and 
The SQL being executed was: INSERT INTO "sessions"

So it seems as though it correctly authenticates my username and password and then tries to create a new entry in the sessions table but fails to do so because the session service is returning a null value.
Is this interpretation correct?
I'm using redis for my session storage, so I tried clearing the keys from there but it doesn't fix the issue.
I'm really blocked on this one and would appreciate any thoughts!
kind regards,
piaras hoban

**Tags:** craft3, database, back-up


---

## 1 answers

### Answer by username_1 on 2019-03-29. Score: 0 | Accepted answer

The id primary key column in the sessions table (and nearly all Craft tables) is set to be auto-incrementing (if you're on MySQL).  It should look something like:
CREATE TABLE `sessions`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `token` char(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `dateCreated` datetime(0) NOT NULL,
  `dateUpdated` datetime(0) NOT NULL,
  `uid` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `sessions_uid_idx`(`uid`) USING BTREE,
  INDEX `sessions_token_idx`(`token`) USING BTREE,
  INDEX `sessions_dateUpdated_idx`(`dateUpdated`) USING BTREE,
  INDEX `sessions_userId_idx`(`userId`) USING BTREE,
  CONSTRAINT `sessions_userId_fk` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

It sounds to me like that table is missing it's AUTO_INCREMENT on the id column.  Craft assumes it's there and that the database will automatically insert it.

> **Comments:**
> **username_0**: Hi Brad, thanks for getting back to me —i've amended my question to clarify that I'm using postgres as the DB.   The problem, (as I found out here: https://stackoverflow.com/questions/49356043/postgresql-error-relation-sequence-does-not-exist-while-restoring-from-dump) was that I was exporting from postgres 10.4 and hadn't updated the postgres version in my local environment. As a result the sequences were not being recreated correctly.  Changed the version and all is good.  
> **username_1**: Awesome and thanks for the follow-up! I thought about adding postgres in the answer, but figured since most people are using MySQL that'd probably cover it.  

---

