https://linen.dev logo
Join Slack
Powered by
Hello! I came across an error which I wasn’t able ...
# announcements
x
Hello! I came across an error which I wasn’t able to solve when changing the meltano system database from the default sqlite db to a postgresql db on aws. The error I saw is:
Copy code
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateObject) type "job_state" already exists

[SQL: CREATE TYPE job_state AS ENUM ('IDLE', 'RUNNING', 'SUCCESS', 'FAIL', 'DEAD')]
(Background on this error at: <http://sqlalche.me/e/14/f405>)
Cannot upgrade the system database. It might be corrupted or was created before database migrations where introduced (v0.34.0)
(psycopg2.errors.UndefinedTable) relation "role" does not exist
LINE 2: FROM role 
             ^

[SQL: SELECT role.id AS role_id, role.name AS role_name, role.description AS role_description 
FROM role 
WHERE role.name = %(name_1)s 
 LIMIT %(param_1)s]
[parameters: {'name_1': 'admin', 'param_1': 1}]
(Background on this error at: <http://sqlalche.me/e/14/f405>)
Any advice will be appreciated, thank you!
d
@xinge_li That's odd; we use alembic to handle the migration (creation of relevant tables etc) and I'm not sure why it would try to create a type again that already exists
Can you check if a 
job
table was created already as well?
x
No, actually my new meltano meta database which is a postgresql one now, it doesn’t contain anything
Emmm, strange, now I cannot run any command start with meltano, e,g meltano ui, meltano invoke xx
d
They all fail with the same error about these migrations?
Can you try to explicitly delete the 
job_state
enum using 
DROP TYPE job_state;
and try again?
x
Sure
Yep, 
meltano ui
command works when I drop the type explicitly 👍
d
OK! Does the 
job
table exist now?
x
Yes
d
Perfect. I have no idea why this didn't work on the first try, why that 
job_state
type apparently already existed, or why the migration created it twice and failed on the second try, but at least we resolved it
If someone else runs into that issue again we can continue debugging!
x
Thank you very much for your quick help!
@douwe_maan What does it mean by this error message when I try to run 
meltano invoke airflow
command?
Copy code
alembic.util.exc.CommandError: Online migration expected to match one row when updating 'd2ae31099d61' to '0e2a74e0fc9f' in 'alembic_version'; 0 found
d
Very weird. Do you see additional details when you run again with 
meltano --log-level=debug invoke ...
We're doing #C01QS0RV78D in 5 minutes, maybe you can join that and we can get to the bottom of this synchronously?
x
Seems like it is related to initiate airflow db, which makes sense because I am changing from local db to a postgresql db.
I can join your office hour later
d
Ah, so you configured Airflow's DB as well? Did you point it at the same DB as Meltano's system DB? That may cause conflicts actually
x
it is the same db, just different user
So am I NOT supposed to point it at the same DB?
d
OK, then I think that's the issue. Can you use a different schema inside the same DB for Airflow using https://airflow.apache.org/docs/apache-airflow/stable/configurations-ref.html#sql-alchemy-schema? It can be the same DB, but the tables can't overlap
x
Aha, I see, I will try it out, thank you!
Sorry @douwe_maan We have two different DBs for meltano and airflow, but in the same instance
d
Ah, that should be OK. That 
alembic.util.exc.CommandError: Online migration expected to match one row when updating
error you just saw looks like it's coming from Airflow itself. I'm not sure why it's also having trouble migrating and creating the tables
In the DB you're using for Airflow, do you see anything in the 
alembic_version
table?
x
Yes, I can see it
d
What's in there?
x
Do I need to specify in my 
.env
file 
AIRFLOW__CORE__SQL_ALCHEMY_SCHEMA
version_num: ‘82b7c48c147f’
d
If you're already using a different database for Meltano and Airflow (even if it's inside the same postgres cluster) setting the schema isn't necessary
if you run 
meltano invoke airflow
again, are you seeing the same 
alembic.util.exc.CommandError: Online migration expected to match one row when updating 'd2ae31099d61' to '0e2a74e0fc9f' in 'alembic_version'; 0 found
error with the same version IDs?
x
Copy code
alembic.util.exc.CommandError: Online migration expected to match one row when updating '849da589634d' to '2c6edca13270' in 'alembic_version'; 0 found
same error but version number seems to change
in the airflow db, the version_num is the same 82b7c48c147f
a
Hey @douwe_maan and @xinge_li, I realise this thread is from a while ago now but did you ever figure out why the 'job_state already exists' error happened in the first place? I just got the same error, and 
DROP TYPE job_state;
seemed to fix the issue, but it'd be good to know what caused it 🙂
d
@al_whatmough I don’t think we ever nailed it down, but it may have been related to using the same database for both Meltano’s system database and Airflow’s metadata database. Could that be related in your case?
a
@douwe_maan yeah that could have been it - at one point I was using the same database for both Meltano and AIrflow. I switched to using different ones but maybe the database was still in a bad state as a result of starting out with them both in the same db
x
I honestly do not know the cause. I am using the same database but different instance for Meltano and Airflow, it seems to work.
14 Views
Open in Slack
PreviousNext
Powered by