1

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

0

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.

2
  • 1
    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: stackoverflow.com/questions/49356043/…) 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. Mar 29 '19 at 10:38
  • 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
    Mar 30 '19 at 4:24

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.