constraint failed: NOT NULL constraint failed: workouts.checksum (1299)

[pixelspark]

@jovandeginste I am seeing this error now when adding a workout for GPX files that used to work. Could be related to your recent change that separated the GPX data to a separate table?

[jovandeginste]

The GPX file is not yet added before?

[pixelspark]

It was but I deleted the workout. Also note it is not the UNIQUE constraint failing but the NOT NULL.

Maybe this is the culprit? https://github.com/jovandeginste/workout-tracker/blob/75d8a131593c5c13b87aaea60b052edbec479699/pkg/database/workouts.go#L78

[pixelspark]

Also, note it errors on workouts.checksum, not the GPX table. So apparently gorm still has a not null constraint on workouts.checksum after migration!

[jovandeginste]

No, I think I need to do a cascade delete when a workout is deleted. I will look into this tomorrow; you can check the database manually via a sqlite cli tool.

[jovandeginste]

Also, note it errors on workouts.checksum, not the GPX table. So apparently gorm still has a not null constraint on workouts.checksum after migration!

O, right! That's good to know... I did have that issue too, and tried again and again until I got the issue resolved for me. Apparently, it's not always resolved...

[jovandeginste]

FYI, the nil is not the issue; that's the way you calculate a checksum :)

[pixelspark]

Yep, it has the NOT NULL constraint in the wrong place:

sqlite> select * from sqlite_master where name='workouts';
+-------+----------+----------+----------+--------------------------------------------------------------+
| type  |   name   | tbl_name | rootpage |                             sql                              |
+-------+----------+----------+----------+--------------------------------------------------------------+
| table | workouts | workouts | 5        | CREATE TABLE "workouts"  (`id` integer PRIMARY KEY AUTOINCRE |
|       |          |          |          | MENT,`created_at` datetime,`updated_at` datetime,`deleted_at |
|       |          |          |          | ` datetime,`name` text NOT NULL,`date` datetime NOT NULL,`us |
|       |          |          |          | er_id` integer NOT NULL,`dirty` numeric,`notes` text,`type`  |
|       |          |          |          | text,`data` text,`checksum` blob NOT NULL,`gpx_data` mediumt |
|       |          |          |          | ext,`filename` text,CONSTRAINT `fk_users_workouts` FOREIGN K |
|       |          |          |          | EY (`user_id`) REFERENCES `users`(`id`))                     |
+-------+----------+----------+----------+--------------------------------------------------------------+

Is there such a thing as a gorm:"null" annotation to force it to allow NULL?

[jovandeginste]

You could look at this page: https://gorm.io/docs/migration.html#Constraints

[pixelspark]

FYI, the nil is not the issue; that's the way you calculate a checksum :)

I see the h.Write above it now. If you don't know the Go API for hashing it looks funny I guess.

[jovandeginste]

I was thinking about using the DropConstraint, but then it just worked for me, so I left it as it was.

[pixelspark]

The easy way out would be to set Checksum to an empty array:

Checksum: []byte{},

[pixelspark]

Ah nevermind, that causes issues with the (apparently also still existing) unique constraint...

[jovandeginste]

What would that solve?

[jovandeginste]

The best fix is to use the drop constraint; if you want to continue with other tests, you can just write a random string as checksum (there is code for that already)

[pixelspark]

What would that solve?

It solves the NOT NULL error as an empty array is probably not the same as null (depends on how gorm translates empty arrays to SQL and how SQLite works, but generally with e.g. strings in SQL there is a difference between '' and NULL).

It does not solve the issue however because there is still a UNIQUE constraint on the checksum. And having '' more than once is not allowed still.

[pixelspark]

For now I can just drop the database, it creates it anew without the constraints:

sqlite> select * from sqlite_master where name = 'workouts';
+-------+----------+----------+----------+--------------------------------------------------------------+
| type  |   name   | tbl_name | rootpage |                             sql                              |
+-------+----------+----------+----------+--------------------------------------------------------------+
| table | workouts | workouts | 8        | CREATE TABLE `workouts` (`id` integer PRIMARY KEY AUTOINCREM |
|       |          |          |          | ENT,`created_at` datetime,`updated_at` datetime,`deleted_at` |
|       |          |          |          |  datetime,`name` text NOT NULL,`date` datetime NOT NULL,`use |
|       |          |          |          | r_id` integer NOT NULL,`dirty` numeric,`notes` text,`type` t |
|       |          |          |          | ext,`data` text,`gpx_data` mediumtext,`filename` text,`check |
|       |          |          |          | sum` blob,CONSTRAINT `fk_users_workouts` FOREIGN KEY (`user_ |
|       |          |          |          | id`) REFERENCES `users`(`id`))                               |
+-------+----------+----------+----------+--------------------------------------------------------------+

[jovandeginste]

Yes our comments crossed, sorry

[jovandeginste]

Luckily we are still at <V1 😅

[jovandeginste]

Should be fixed now, but feel free to test!