I’ve hit an issue with Lemmy. Initial startup was fine… I created my admin user and logged in. Then I created this account.
Third account I went to test with failed to login… then my other accounts also failed, so I rebooted.
Now when I try to log into my admin account, it just spins forever. Checking logs, I see this:
lemmy_server::api_routes_websocket: email_not_verified: email_not_verified
Can anyone tell me how I can manually flag my admin account as having been email verified already?
Ok, figured it out myself. For anyone else that needs it…
Run a shell within the postgres docker container:
docker exec -it <sitename>_postgres_1 sh
Connect to the postgres service:
psql -U lemmy -h 127.0.0.1 -p 5432 -d lemmy
Connect to the lemmy database:
\c lemmy
Then the user table: \d local_user
Find the user ID of the account you want to manually toggle, probably #1:
SELECT * from local_user;
Then update the email_verified and _accepted_application flag on the first account:
UPDATE local_user SET email_verified = ‘t’, accepted_application = ‘t’ WHERE id = 1;
lemmy=# UPDATE local_user SET email_verified = ‘t’, accepted_application = ‘t’ WHERE id = 3; UPDATE 1 lemmy=# UPDATE local_user SET email_verified = ‘t’, email_verified= ‘t’ WHERE id = 3; UPDATE 1 lemmy=#
Thanks for this breakdown, I seem to be having issues with the update statements:
lemmy=# UPDATE local_user SET email_verified = ‘t’, accepted_application = ‘t’ WHERE id = 1; ERROR: column "‘t’" does not exist LINE 1: UPDATE local_user SET email_verified = ‘t’, accepted_applica... ^ HINT: Perhaps you meant to reference the column "local_user.id". lemmy=# UPDATE local_user SET email_verified =‘t’, accepted_application = ‘t’ WHERE id = 1; ERROR: column "‘t’" does not exist LINE 1: UPDATE local_user SET email_verified =‘t’, accepted_applicat... ^ HINT: Perhaps you meant to reference the column "local_user.id". lemmy=# UPDATE local_user SET email_verified = ‘t’, accepted_application = ‘t’ WHERE id = 3; UPDATE 1 ERROR: column "‘t’" does not exist LINE 1: UPDATE local_user SET email_verified = ‘t’, accepted_applica... ^ HINT: Perhaps you meant to reference the column "local_user.id". lemmy-#It seems that the query that Lodion shared has the wrong single quote character,
‘rather than'. Try using it with the latter, I had the same issue as you and this was what fixed it.Now it worked, thanks!
yes, I should have realized that. That worked! Thank you @prothy@lemmy.ml
Sorry Chris, I was figuring that out as I went. It looks like a postgres syntax error but the command matches what I used.
If you run “SELECT * from local_user;” do you get output showing your list of registered users? Perhaps you’re not attached to the lemmy database?
Yes, the Select did work, it was just the updates that didn’t work.
Not sure what to suggest, sorry I’m not a DBA :(
To make it clear, here is the command I ran enclosed with "
“UPDATE local_user SET email_verified = ‘t’, accepted_application = ‘t’ WHERE id = 1;”
“lemmy=# UPDATE local_user SET email_verified = ‘t’, accepted_application = ‘t’ WHERE id = 1; ERROR: column “‘t’” does not exist LINE 1: UPDATE local_user SET email_verified = ‘t’, accepted_applica… ^ HINT: Perhaps you meant to reference the column “local_user.id”.” This was its response.
I’ve just taken a look at your instance… you’re running a slightly older version. I’m guessing the DB schema has changed.
Does “SELECT * from local_user.id;” get you a list of users? You may need to update the other references to match whatever the old DB schema is.
no it doesn’t work. “ERROR: relation “local_user.id” does not exist LINE 1: SELECT * from local_user.id;”
My instance is only 4 days old, how do I update it?
Sorry mate, I’m out of ideas.
excellent info, this is way more straightforward than what I did.