Error on upgrade to 4.8.0.3

[Nik-vr]

I just upgrade Firefly III from 4.7.17 to 4.8.0.3. I use composer for this. After update, I try to login, Firefly has update database, and I have error-message:

An unknown error prevented Firefly III from executing the upgrade commands. Sorry. SQLSTATE[HY000]: General error: 1 too many SQL variables (SQL: select "transaction_journal_id" from "transactions" where "transactions"."id" in (55, 56, 85, 86, 135, 136, 155, 156, 165, 166, 167, 168, 169, 170, 385, 386, 433, 434, 443, 444, 459, 460, 471, 472, 617, 618, 675, 676, 685, 686, 695, 696, 699, 700  ..............here is many transactions id..........
7301, 7302, 7303, 7304, 7305, 7306, 7307, 7308, 7315, 7316, 7317, 7318))

Please read the official documentation about this, and upgrade by hand.

[JC5]

Is there a stack trace in your log files? I'd like to know where the problem occurred exactly.

[Nik-vr]

My log-files:

ff3-apache2handler-2019-08-24.log
ff3-cli-2019-08-24.log

[JC5]

I've pushed a fix that you could apply to your own code, if you want to. It involves replacing the code of two functions with the code of the fix.

Find this line in your code, and replace everything between { and } with the code between the brackets of this code.

Do the same with this function and this replacement.

[Nik-vr]

I replace code, login in my Firefly III installation, and got the same error message :(

ff3-apache2handler-2019-08-25.log
ff3-cli-2019-08-24.log

A new log file (ff3-cli-2019-08-25) has not been created. In folder only ff3-cli-2019-08-24.

[JC5]

No worries, we can fix that one as well!

1. Replace old function with new function
2. Replace another old function with another new function

Let me know if it works 👍

[Nik-vr]

Now the error message has changed a bit:

An unknown error prevented Firefly III from executing the upgrade commands. Sorry. SQLSTATE[HY000]: General error: 1 too many SQL variables (SQL: select * from "transaction_journals" where "id" in (27, 27, 34, [... there are many more id's... ] 3445, 3445) and "transaction_journals"."deleted_at" is null)

[JC5]

Did you replace both?

This is weird because that's all the code there is that's doing large replacements. I'll do some googling but I'm kind of lost, sorry about that.

[JC5]

Are you using SQLite?

[Nik-vr]

I replace both (getIdsForBudgets and getIdsForCategories) functions.

Yes, I use SQLite DB.

[JC5]

The fix limits the number of ID's to 50. There may be other commands where this is happening. So keep posting stack traces.

I can strongly recommend you to migrate to MySQL or Postgresql though.

[Nik-vr]

I can strongly recommend you to migrate to MySQL

I would gladly switch to MySQL, but there is one problem. I use Firefly III on a shared hosting. And I can’t correctly update my Firefly installation on my server (on the server there is no way to use GIT, Composer, etc.). Now I am doing it simply: copy the database to the local server (where the Composer is), update the Firefly, and copy the updated Firefly along with the database back to the hosting. As soon as the opportunity arises to painlessly upgrade the Firefly with the MySQL database on a shared hosting, I will be happy to switch to MySQL. In the meantime, I have to use SQLite.

[JC5]

Could you also use the same routine with MySQL? You could use something like Vagrant or Virtualbox to maintain a Linux image with a MySQL database in it.

[JC5]

But it's tricky, I admit it.

[Nik-vr]

This is too complicated a procedure. I would not want to make so much effort to update one software.

Perhaps there is a way to somehow simplify the process of updating Firefly? At least so: download the full distribution kit of the new version, replace all the files on the server, run the update script.

[Nik-vr]

Now I try to update from my 4.7.17 to 4.8.1.1. And I get in console new error message:

In Connection.php line 664:

  SQLSTATE[HY000]: General error: 1 too many SQL variables (SQL: select count(*) as aggrega
  te from "transaction_groups" where "deleted_at" is null and "id" not in (139, 140, 141, 1
  ..............here is many transactions id's..............
   125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138) and "transaction_g
  roups"."deleted_at" is null)

In PDOConnection.php line 63:

  SQLSTATE[HY000]: General error: 1 too many SQL variables

[JC5]

Could you extract the stack trace from the log files?

[Nik-vr]

Log attached:

ff3-cli-2019-10-02.log

[JC5]

I pushed a fix to the develop branch. If you run across other instances, please open a new ticket. It's easier for me to track :)