starred/lldap-lldap

Fork 0

mirror of https://github.com/lldap/lldap.git synced 2026-04-25 08:15:52 +03:00

[GH-ISSUE #919] Migrate DB to postgres: column "password_hash_v2" of relation "users" does not exist #334

New issue

Closed

opened 2026-02-27 08:16:42 +03:00 by kerem · 6 comments

kerem commented

2026-02-27 08:16:42 +03:00

Owner

Originally created by @Hobbabobba on GitHub (Jun 22, 2024).
Original GitHub issue: https://github.com/lldap/lldap/issues/919

I want to migrate my sqlite-DB to postgres.

The migration in my test-stage went smooth, i just followed https://github.com/lldap/lldap/blob/main/docs/database_migration.md

The migration in production show an error message. After i execute psql -d <database> -U <username> -W < /path/to/dump.sql I get the error message

ERROR:  column "password_hash_v2" of relation "users" does not exist
LINE 1: ...tion_date,password_hash,totp_secret,mfa_type,uuid,password_h...
                                                             ^
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR

I use the image 2024-06-16-alpine

Any idea what i did wrong? I am very sure that i did everything in the same way like on my test-stage.

Originally created by @Hobbabobba on GitHub (Jun 22, 2024). Original GitHub issue: https://github.com/lldap/lldap/issues/919 I want to migrate my sqlite-DB to postgres. The migration in my test-stage went smooth, i just followed https://github.com/lldap/lldap/blob/main/docs/database_migration.md The migration in production show an error message. After i execute `psql -d <database> -U <username> -W < /path/to/dump.sql` I get the error message ``` ERROR: column "password_hash_v2" of relation "users" does not exist LINE 1: ...tion_date,password_hash,totp_secret,mfa_type,uuid,password_h... ^ ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR: current transaction is aborted, commands ignored until end of transaction block ERROR ``` I use the image `2024-06-16-alpine` Any idea what i did wrong? I am very sure that i did everything in the same way like on my test-stage.

kerem

2026-02-27 08:16:42 +03:00

closed this issue
added the
bug
label

kerem commented

2026-02-27 08:16:43 +03:00

Author

Owner

@nitnelave commented on GitHub (Jun 22, 2024):

That's strange, since there's no mention of a password_hash_v2 anywhere in the code.

~~Could you share your SQL dump, the part where it sets up the schema? Everything except the inserts.~~

EDIT: Oh, wait, the dump should only contain inserts, the schema is created independently. Can you list the columns that the insert is trying to populate?

@nitnelave commented on GitHub (Jun 22, 2024): That's strange, since there's no mention of a password_hash_v2 anywhere in the code. ~~Could you share your SQL dump, the part where it sets up the schema? Everything except the inserts.~~ EDIT: Oh, wait, the dump should only contain inserts, the schema is created independently. Can you list the columns that the insert is trying to populate?

kerem commented

2026-02-27 08:16:43 +03:00

Author

Owner

@Hobbabobba commented on GitHub (Jun 22, 2024):

how do i do that in postgres?

@Hobbabobba commented on GitHub (Jun 22, 2024): how do i do that in postgres?

kerem commented

2026-02-27 08:16:43 +03:00

Author

Owner

@nitnelave commented on GitHub (Jun 22, 2024):

Just one line of the dump.sql file should be enough, truncated after the columns

@nitnelave commented on GitHub (Jun 22, 2024): Just one line of the dump.sql file should be enough, truncated after the columns

kerem commented

2026-02-27 08:16:43 +03:00

Author

Owner

@Hobbabobba commented on GitHub (Jun 22, 2024):

should be this part

BEGIN;
INSERT INTO users(user_id,email,display_name,creation_date,password_hash,totp_secret,mfa_type,uuid,password_hash_v2,lowercase_email)

I tried to migrate it with pgloader and got this result

2024-06-22T21:38:16.020001Z LOG pgloader version "3.6.7~devel"
2024-06-22T21:38:16.112002Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///home/stefan/docker/ldap/users.db {1005DA3D53}>
2024-06-22T21:38:16.112002Z LOG Migrating into #<PGSQL-CONNECTION pgsql://ldapdb@aptcache.fritz.box:5433/lldap {1005F78293}>
2024-06-22T21:38:16.268004Z WARNING Failed to transform default value "FALSE": junk in string "FALSE"
2024-06-22T21:38:16.272004Z ERROR Database error 42601: type modifier is not allowed for type "text"
QUERY: CREATE TABLE users
(
  user_id          text(255),
  email            text(255),
  display_name     text(255),
  creation_date    text,
  password_hash    bytea,
  totp_secret      text(64),
  mfa_type         text(64),
  uuid             text(36),
  password_hash_v2 bytea,
  lowercase_email  text(255) default 'UNSET'
);
2024-06-22T21:38:16.272004Z FATAL Failed to create the schema, see above.
2024-06-22T21:38:16.276004Z LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
            fetch          0          0                     0.000s
  fetch meta data          0         40                     0.052s
   Create Schemas          0          0                     0.004s
 Create SQL Types          0          0                     0.004s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------

@Hobbabobba commented on GitHub (Jun 22, 2024): should be this part ``` BEGIN; INSERT INTO users(user_id,email,display_name,creation_date,password_hash,totp_secret,mfa_type,uuid,password_hash_v2,lowercase_email) ``` I tried to migrate it with pgloader and got this result ``` 2024-06-22T21:38:16.020001Z LOG pgloader version "3.6.7~devel" 2024-06-22T21:38:16.112002Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///home/stefan/docker/ldap/users.db {1005DA3D53}> 2024-06-22T21:38:16.112002Z LOG Migrating into #<PGSQL-CONNECTION pgsql://ldapdb@aptcache.fritz.box:5433/lldap {1005F78293}> 2024-06-22T21:38:16.268004Z WARNING Failed to transform default value "FALSE": junk in string "FALSE" 2024-06-22T21:38:16.272004Z ERROR Database error 42601: type modifier is not allowed for type "text" QUERY: CREATE TABLE users ( user_id text(255), email text(255), display_name text(255), creation_date text, password_hash bytea, totp_secret text(64), mfa_type text(64), uuid text(36), password_hash_v2 bytea, lowercase_email text(255) default 'UNSET' ); 2024-06-22T21:38:16.272004Z FATAL Failed to create the schema, see above. 2024-06-22T21:38:16.276004Z LOG report summary reset table name errors rows bytes total time ----------------- --------- --------- --------- -------------- fetch 0 0 0.000s fetch meta data 0 40 0.052s Create Schemas 0 0 0.004s Create SQL Types 0 0 0.004s Create tables 0 0 0.000s ----------------- --------- --------- --------- -------------- ----------------- --------- --------- --------- -------------- ```

kerem commented

2026-02-27 08:16:43 +03:00

Author

Owner

@nitnelave commented on GitHub (Jun 22, 2024):

Assuming you have a backup of your SQLite DB (if you don't, make one now), can you open the DB in sqlite3 and run

ALTER TABLE users DROP COLUMN password_hash_v2;

And then try to migrate again?

@nitnelave commented on GitHub (Jun 22, 2024): Assuming you have a backup of your SQLite DB (if you don't, make one now), can you open the DB in sqlite3 and run `ALTER TABLE users DROP COLUMN password_hash_v2;` And then try to migrate again?

kerem commented

2026-02-27 08:16:43 +03:00

Author

Owner

@Hobbabobba commented on GitHub (Jun 22, 2024):

perfect, now i can migrate the data. Thank you!

@Hobbabobba commented on GitHub (Jun 22, 2024): perfect, now i can migrate the data. Thank you!

kerem referenced this issue

2026-02-27 09:09:22 +03:00

[PR #334] [MERGED] Added dell idrac sample #686