Cannot view old TOTP entries on latest Docker Image

[shucking]

Describe the bug
After updating to v3.0, I am unable to access my old TOTP entries.

To Reproduce
Steps to reproduce the behavior:

1. Update to latest docker image after having backed up sqlite db.
2. Before starting container, copy database into 2fauth folder and ensure proper permissions as directed by Github guide.
3. Start container and log in to web app.
4. Click on any TOTP entry
5. Receive error message saying "An error occurred: This OTP format is not currently supported".

Expected behavior
Access TOTP entries from before the upgrade.

Screenshots
If I restore the database and the browser remembers my previous session, I am able to see the entries, but when I click on any one of them, I get the following error:

Desktop (please complete the following information):

• OS: [e.g. iOS] Linux
• Browser [e.g. chrome, safari] Firefox
• Version [e.g. 22]

[Bubka]

Hi.
Can you run some shell command within your container? If so please ensure all migrations have run with php artisan migrate from the /srv container folder.

[marcogiorgio]

Hi,
I don't know if it's the same issue, but after I upgraded to the latest version all my TOTP codes are vanished. When I log in I only get: "No 2FA here! Add your first account" I ran the command php artisan migrate inside my docker container but I get: "Nothing to migrate". Please at least add the previous image as a different tag in Docker hub so we may rollback.

This is the log:

In Connection.php line 712:

SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: twofaccounts.otp_type (SQL: INSERT INTO twofaccounts (id, service, leg acy_uri, account, icon, created_at, updated_at, order_column, group_id, otp _type, secret, algorithm, digits, period, counter) SELECT id, service, legacy_uri, account, icon, created_at, updated_at, order_column, group_id, otp_type, secret, algorithm, digits, period, counter FROM __temp__twofaccounts)

In Connection.php line 501:

SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: twofaccounts.otp_type

Thanks

[Bubka]

Please make sure you have a backup of your db in a safe place, then try running the following artisan command:

php artisan 2fauth:fix-unsplitted-accounts

[marcogiorgio]

This is the result: "0 inconsistent accounts found Nothing to fix"

[Bubka]

Ok thanks. The error in the log probably occurred during the migration of the database, but in this case no record is supposed to be deleted, just ignored.

Do you have the ability to get the /2fauth/database.sqlite file from the container and check if there is some records in the twofaccounts table? You can use https://sqlitebrowser.org/ to browse the db content.

[marcogiorgio]

Yes, it's empty. I checked the old sqlite (I have a backup) and all the entries are there, so it's something that happens during the migration which corrupts the file

[Bubka]

Can you try this:

• Restore /2fauth/sqlite.database with your backup (again, be sure to keep a copy of the backup in a safe place)
• Run php artisan migrate in a container shell.

If this is a design bug the error should occurred again.

[marcogiorgio]

This happened again:

• If I stop the container before restoring the database, when I log in the database is empty.
• If I restore the database while the container is running and refresh the web page, the same behaviour as the OP occurs

[Bubka]

If I stop the container before restoring the database, when I log in the database is empty.

With or without php artisan migrate before login?

Another question: Do you use db encryption in the 2FAuth settings?

[marcogiorgio]

Without logging in/php artisan migrate. The db is emptied after the start of the container

I don't think I activated the encryption. The values in "twofaccount" are in clear text

[Bubka]

Can you copy/paste the shell output when you start your container please. thx

[marcogiorgio]

Running version latest commit 8944fae built on 2022-05-10T13:38:58Z
supervisord version: v0.6.8
PHP 7.4.26 (fpm-fcgi) (built: Nov 18 2021 21:39:13)
nginx version: nginx/1.20.2
Installed commit 45219c3 is different from program commit 8944fae, we are migrating...
Migrating: 2020_12_04_073605_alter_encrypted_columns_to_text_for_sqlite
Migrated: 2020_12_04_073605_alter_encrypted_columns_to_text_for_sqlite (13.46ms)
Migrating: 2020_12_04_073606_alter_encrypted_columns_to_text_for_sqlite_bis
Migrated: 2020_12_04_073606_alter_encrypted_columns_to_text_for_sqlite_bis (2.36ms)
Migrating: 2021_09_08_191139_split_twofaccounts_uri_in_multiple_columns
Migrated: 2021_09_08_191139_split_twofaccounts_uri_in_multiple_columns (4.01ms)
Migrating: 2021_09_08_191140_rename_uri_to_legacy_uri
Migrated: 2021_09_08_191140_rename_uri_to_legacy_uri (3.77ms)
Migrating: 2021_09_14_195451_change_nullable_in_twofaccounts_table

In Connection.php line 712:

SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint fai
led: twofaccounts.otp_type (SQL: INSERT INTO twofaccounts (id, service, leg
acy_uri, account, icon, created_at, updated_at, order_column, group_id, otp
type, secret, algorithm, digits, period, counter) SELECT id, service, lega
cy_uri, account, icon, created_at, updated_at, order_column, group_id, otp
type, secret, algorithm, digits, period, counter FROM __temp__twofaccounts)

In Connection.php line 501:

SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint fai
led: twofaccounts.otp_type

Running version latest commit 8944fae built on 2022-05-10T13:38:58Z
supervisord version: v0.6.8
PHP 7.4.26 (fpm-fcgi) (built: Nov 18 2021 21:39:13)
nginx version: nginx/1.20.2
Installed commit 45219c3 is different from program commit 8944fae, we are migrating...
Migrating: 2021_09_14_195451_change_nullable_in_twofaccounts_table
Migrated: 2021_09_14_195451_change_nullable_in_twofaccounts_table (14.27ms)
Migrating: 2021_12_03_220140_create_web_authn_tables
Migrated: 2021_12_03_220140_create_web_authn_tables (2.30ms)
Configuration cache cleared!
The [/srv/public/storage] link has been connected to [/srv/storage/app/public].
The links have been created.
Configuration cache cleared!
Configuration cached successfully!
time="2022-05-11T14:49:55Z" level=info msg="load configuration from file" file=/etc/supervisor/supervisord.conf

[Lysuo]

Hi
I have the exact same issue as @marcogiorgio here
If my understanding is correct this the first image with v3 ?
Could you republish on docker hub the container image for the last tag of v2 ?
The image sha I have in local is 52d7e4fd36fd
Thanks

[aronmal]

Ok thanks. The error in the log probably occurred during the migration of the database, but in this case no record is supposed to be deleted, just ignored.

Do you have the ability to get the /2fauth/database.sqlite file from the container and check if there is some records in the twofaccounts table? You can use https://sqlitebrowser.org/ to browse the db content.

It is not empty, but filled with NULL?

I don't have made an backup before updating (had to fix the same issue #68 (comment) ), but I have my TOTPs in Google Authenticator, so I should be fine "restoring" them if I can scan/upload them this time and not have to add them manually. 😉

But this should be fixed fast so others don't lose their TOTPs.

[marcogiorgio]

Ok thanks. The error in the log probably occurred during the migration of the database, but in this case no record is supposed to be deleted, just ignored.
Do you have the ability to get the /2fauth/database.sqlite file from the container and check if there is some records in the twofaccounts table? You can use https://sqlitebrowser.org/ to browse the db content.

It is not empty, but filled with NULL?

I don't have made an backup before updating (had to fix the same issue #68 (comment) ), but I have my TOTPs in Google Authenticator, so I should be fine "restoring" them if I can scan/upload them this time and not have to add them manually. 😉

But this should be fixed fast so others don't lose their TOTPs.

I second this. I don't think everyone has a backup of some sort. Maybe it would be better to restore the previous version until this is fixed...

[Bubka]

I'm working on it

[Bubka]

The image sha I have in local is 52d7e4fd36fd Thanks

@Lysuo Do you have the full digest?

[Lysuo]

The image sha I have in local is 52d7e4fd36fd Thanks

@Lysuo Do you have the full digest?

Yes it's 52d7e4fd36fd42b19facee1b53eb711b99ffd590785640fb3aa19a325559a5f0
It is based on the following commit, based on the logs:

Running version latest commit 45219c3 built on 2022-02-10T11:36:46Z

[Bubka]

Thanks.
Unfortunately I failed at doing something useful with this hash. I thought I could pull the image at this specific digest but docker cli denied the request.

Does anyone know if it's possible with a free docker hub account to rollback an image?

[aronmal]

Found this, which sais it is not possible.

And I've already wondered about only one "latest" tag. As I quote from the source above:

Avoid using the latest tag and give each build a unique tag.

[Bubka]

And I've already wondered about only one "latest" tag. As I quote from the source above:

The ci actions written by @qdm12 is supposed to handle the creation of tags based on the release number but something went wrong apparently. Or I missed something. Don't know.

[Lysuo]

@Bubka

I pushed the working image I have to my account on docker hub: jeromeasg/2fauth:2.0.x
It's based on this commit: 45219c31006886cf324d887821a2e7e85114557d

[aronmal]

And why is this issue being closed? You now uploaded an "old" version on docker hub, so people hopefully won't lose their database, but this not fixed lognterm in the latest v3 version yet, or am I wrong?

[Bubka]

And why is this issue being closed?

Automatically closed by GH as the issue is referenced in the commit message.

I was able to reproduce the migration error, this was a design issue (only for sqlite setup) so I pushed a fix for the v3. The updated image on dockerhub is not a rollback but an update to v3.0.1 (the github release is coming)

Unfortunately, for people who encountered the error, the only way to get data back is to restore a db backup, this cannot be restored with the previous image. Sorry for that.

[aronmal]

Ok, my misunderstanding. @Lysuo uploaded an old image

I pushed the working image I have to my account on docker hub: jeromeasg/2fauth:2.0.x

And I read issue closed, not closed this in 42c3bd8 🙄

By this, I was confused and thought you also uploaded the old image.

But because I was wrong, great job of you. 👍

[Bubka]

You are welcome 😃

I will also try to push Lysuo's image to docker hub to have a v2 tag (for the record) but my priority for yesterday was to fix the :latest to prevent users db loss as there is no way to recover the data other than restoring a backup. Although everyone is informed that a db backup is essential I know that some people won't have backed their db.