During boot up a migration fails with "Duplicatie column name".

[kutagh]

Bug description
I am running Firefly III version 5.5.1, and my problem is:

When starting the firefly container in docker-compose, it tries to run a migration which keeps failing. See extra info for the stdout output. I use the jc5x/firefly-iii:latest tag.

Steps to reproduce
Restart the docker container with a specific database schema that seems to contain the specific columns already.

Extra info

[2021-03-28 20:27:48] local.ERROR: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'transaction_type_id' (SQL: alter table `recurrences_transactions` add `transaction_type_id` int unsigned null after `transaction_currency_id`) {"exception":"[object] (Illuminate\\Database\\QueryException(code: 42S21): SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'transaction_type_id' (SQL: alter table `recurrences_transactions` add `transaction_type_id` int unsigned null after `transaction_currency_id`) at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:678)
[stacktrace]
#0 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(638): Illuminate\\Database\\Connection->runQueryCallback()
#1 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(472): Illuminate\\Database\\Connection->run()
#2 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(109): Illuminate\\Database\\Connection->statement()
#3 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(359): Illuminate\\Database\\Schema\\Blueprint->build()
#4 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(206): Illuminate\\Database\\Schema\\Builder->build()
#5 /var/www/html/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(261): Illuminate\\Database\\Schema\\Builder->table()
#6 /var/www/html/database/migrations/2021_03_12_061213_changes_for_v550b2.php(63): Illuminate\\Support\\Facades\\Facade::__callStatic()
#7 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php(392): ChangesForV550b2->up()```

[JC5]

I suggest you restore your DB to the 5.4.6 version and restart 5.5.1. If the migration failed for any reproducible reason it'll show.

[kutagh]

Haven't got an easily restorable migration and table was empty, so I simply dropped the FK & column then restarted the container, that did the trick for me.

[steakhutzeee]

Haven't got an easily restorable migration and table was empty, so I simply dropped the FK & column then restarted the container, that did the trick for me.

I have the same issue #4549 (comment)

What you mean by FK?

Can you share how you did this?

[steakhutzeee]

@JC5 I can say that this is not impacting daily operations with Firefly III,after all it is a warning and not an error.

I'm a noob at database and actually I'm using Mariadb from LinuxServer. Maybe you have some commands I can fire to remove the duplicate?

[JC5]

Not from the command line I'm afraid. I'm just not sure what the root cause is.

[steakhutzeee]

Oh i see, well i think the only thing i can do is wait.

@kutagh said "dropped the FK & column" , wondering what he means.

[JC5]

Did any of you run a beta version by any chance? I’m working on a root cause analysis of this problem.

[steakhutzeee]

Never used a beta version

[JC5]

I pushed a solution to the develop branch. It should skip the offending migration if it exists already. Use the develop tag instead of the latest tag and it should work.

[JC5]

Will do, thanks for reminding me!

[jaylenw]

Hello @JC5 , I finally got around to do some testing and would like to share with you some of my findings. Hopefully they are useful lol and not too long winded :).

After some time researching and testing I thought it would be useful to first see if we can fix the sequences after a database seeding was done. Out of the links I shared with you to try and fix the sequences only this one seemed to work as intended https://wiki.postgresql.org/wiki/Fixing_Sequences to fix all of the sequences. Below I will be sharing fixing one of the sequences that are affected by the seeds, specifically, account_types_id_seq.

1.) Ran SELECT * FROM information_schema.sequences; to see all of the sequences that exists and chose account_types_id_seq to test out with as it is related to what we see in database seeding logs.

2.) Ran SELECT * FROM account_types_id_seq; to see what the last value was set for the acount_types sequence as saw that the last_value was 96.

3.) I ran SELECT * FROM public.account_types ORDER BY id ASC; and we see that the max id value is 12.

4.) I then ran the following to obtain the list of sql commands needed to fix all of the sequences (code listed in https://wiki.postgresql.org/wiki/Fixing_Sequences).

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

From the output of the above command, I ran SELECT SETVAL('public.account_types_id_seq', COALESCE(MAX(id), 1) ) FROM public.account_types to set the current value of the sequence.

5.) I ran SELECT * FROM account_types_id_seq; again and we see that the last value is now 12 which is good.

6.) I then proceeded to restart Firefly-iii (run the database seeds again) and ran SELECT * FROM account_types_id_seq; again and saw that the last_value is now 36 which should be 12. I don't know exactly why this is happening.

---

So this issue laravel/framework#22683 has information that would explain better than I can of what is happening. I will say though, I don't think the issue is caused by the code for Firefly-iii itself but more likely dealing with Laravel's ORM layer as it appears in this issue laravel/framework#4897. In laravel/framework#4897, a user ended up adding some code into his seeds to set the sequences laravel/framework#4897 (comment). Is this something we could more or less implement a similar solution to fix the issue? Thanks!

[JC5]

I am seeing the exact same thing in a fresh pgsql/5.5.9 installation. I'll tinker with the solution in a dev image and report back!

[JC5]

I added some code to Firefly III and the boot sequence. It should start reporting something like this in the next version:

fireflyiii_1    | Going to verify PostgreSQL table sequences.
fireflyiii_1    | Checking the next id sequence for table "2fa_tokens".
fireflyiii_1    | nextval is NULL for table "2fa_tokens", go to next table.
fireflyiii_1    | Checking the next id sequence for table "account_meta".
fireflyiii_1    | nextval is NULL for table "account_meta", go to next table.
fireflyiii_1    | Checking the next id sequence for table "account_types".
fireflyiii_1    | Table "account_types" autoincrement is correct.

[jaylenw]

Thanks @JC5 !