Error 1062 (23000): Duplicate entry '' for key 'access_token.sha256'

[axiomen]

Gogs version

0.13.0

Git version

• Server: 2.39.3
• Client: N/A

Operating system

RedHat 9

Database

MySQL 8.0.36

Describe the bug

I'm in the process of migrating Gogs 0.12.3 (on CentOS7) to another machine to Gogs 0.13.0 (on RedHat 9). I backed up Gogs 0.12.3 with adjusted 0.13.0 app.ini, moved it to the new machine, but when trying to restore the backup, I'm met with this database error Duplicate entry '' for key 'access_token.sha256'. I checked the database on both sides, and I cannot find the duplicate anywhere. I tried to manually remove it from the table, but I'm met with the same error upon restoring. Both installations are on the same MySQL version.

Might be worth mentioning that 0.12.3 on the first machine was installed from Packager.io, while the new 0.13.0 version on the other machine was installed from binary.

To reproduce

1. Adjust app.ini to match the new 0.13.0 config.
2. Create a backup on machine one running 0.12.3 with ./gogs backup --exclude-repos
3. Move the backup to machine two running 0.13.0 and attempt a restore with ./gogs restore --from="backup.zip"
4. Shortly after the process starts, receive error Duplicate entry '' for key 'access_token.sha256'.

Expected behavior

Successful restore from one machine to the other.

Additional context

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[unknwon]

Hey,

I think it because the backed-up data did not get chance to run the require DB migration that makes sure every row has a non-empty value for the access_tokens.sha256 column:

gogs/internal/database/migrations/migrations.go

Lines 55 to 56 in dd49412

	// v19 -> v20:v0.13.0
	NewMigration("migrate access tokens to store SHA56", migrateAccessTokenToSHA256),

There are two possible approaches:

1. Run the 0.13.0 version against the database on your old machine once, so it can complete the DB migration. Then backup again and restore.
2. Connect to the database on the new machine, and drop the unique constraint on the access_tokens.sha256 column, restore your backup, run 0.13.0 (which will add back the unique constraint as part of DB migration).

Be noted that approach 2 only solves this specific error you're getting blocked (which, maybe the only one after all, or you may also hit #7687).

[axiomen]

I'll see if I can go with the first option. It's a production machine, so I wanted to avoid dealing with the update on it, as we need to move to the new machine anyway.

About #7687, since we use LDAP for logging in, with a few native Gogs accounts, does that mean I would have to go through every non-LDAP account, login without a password and set it up again?

[axiomen]

It's been a while, but thought I'd write an update.

I ran upgrade to 0.13.0 on the prod machine, which has been working more or less fine. However after migrating to the new machine, I noticed there have been several database record not found related errors ever since I updated to 0.13.0 (i.e. before migrating).

[0.287ms] [rows:0] SELECT * FROM `access` WHERE user_id = 70 AND repo_id = 8 ORDER BY `access`.`id` LIMIT 1
2024/06/11 09:34:00 gogs.io/gogs/internal/db/repos.go:294 record not found
[0.426ms] [rows:0] SELECT * FROM `repository` WHERE owner_id = 87 AND lower_name = 'REDACTED' ORDER BY `repository`.`id` LIMIT 1
2024/06/11 09:34:10 gogs.io/gogs/internal/db/perms.go:114 record not found

Also happens for several users, even though they haven't reported having any issues with Gogs.

[0.762ms] [rows:0] SELECT `user`.`id`,`user`.`lower_name`,`user`.`name`,`user`.`full_name`,`user`.`email`,`user`.`passwd`,`user`.`login_source`,`user`.`login_name`,`user`.`type`,`user`.`location`,`user`.`website`,`user`.`rands`,`user`.`salt`,`user`.`created_unix`,`user`.`updated_unix`,`user`.`last_repo_visibility`,`user`.`max_repo_creation`,`user`.`is_active`,`user`.`is_admin`,`user`.`allow_git_hook`,`user`.`allow_import_local`,`user`.`prohibit_login`,`user`.`avatar`,`user`.`avatar_email`,`user`.`use_custom_avatar`,`user`.`num_followers`,`user`.`num_following`,`user`.`num_stars`,`user`.`num_repos`,`user`.`description`,`user`.`num_teams`,`user`.`num_members` FROM `user` LEFT JOIN email_address ON email_address.uid = user.id WHERE user.type = '0' AND ((user.email = 'REDACTED' AND user.is_active = true) OR (email_address.email = 'REDACTED' AND email_address.is_activated = true)) ORDER BY `user`.`id` LIMIT 1
2024/06/11 09:51:02 gogs.io/gogs/internal/db/users.go:790 record not found

Moreover, I had to create dir /etc/gogs/conf/ to place the custom app.ini in, because Gogs doesn't want to recognize the one in custom/conf in its own installation directory.

A side note, unrelated to the errors: I had to manually dump the database from the old machine and import into SQL on the new machine, as Gogs' native database restore command would always hang without any error.