Database error when upgrading 5.4.6 to 5.5.1

[svenahrensnl]

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

When updating my self hosted system (with the help of the official manual) the php artisan migrate --seed command results in the follwoing error:

[user@host firefly-iii-updated]# php artisan migrate --seed
Migrating: 2020_11_12_070604_changes_for_v550

In Connection.php line 678:

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t
  oo long; max key length is 767 bytes (SQL: alter table `webhooks` add uniqu
  e `webhooks_user_id_title_unique`(`user_id`, `title`))


In Connection.php line 471:

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t
  oo long; max key length is 767 bytes

When running the code again, the follwing error occurs:

[user@host firefly-iii-updated]# php artisan migrate --seed
Migrating: 2020_11_12_070604_changes_for_v550

In Connection.php line 678:

  SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'budget_
  limit_id' (SQL: alter table `budget_transaction_journal` add `budget_limit_
  id` int unsigned null after `budget_id`)


In Connection.php line 471:

  SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'budget_
  limit_id'

Steps to reproduce

• SSH into the server
• composer create-project grumpydictator/firefly-iii --no-dev --prefer-dist firefly-iii-updated 5.5.1
• cp firefly-iii/.env firefly-iii-updated/.env
• cp firefly-iii/storage/upload/* firefly-iii-updated/storage/upload/
• cp firefly-iii/storage/export/* firefly-iii-updated/storage/export/
• cd firefly-iii-updated
• rm -rf bootstrap/cache/*
• php artisan cache:clear
• php artisan migrate --seed

Extra info
Debug information generated at 2021-03-28 17:06:44 Europe/Amsterdam for Firefly III version 5.4.6.

Scope	Version
Firefly III	5.4.6
Firefly III API	1.4.0
PHP	7.4.16
Host	Linux

System info	Value
Installation ID	ef4dc684-e1c8-4dd1-97d0-bdeb8bc42ee6
Using docker?	false
Telemetry	false
Layout
App environment	local
App debug mode	false
App cache driver	file
App logging	notice, daily
Display errors	Off
Error reporting	ALL errors
Interface	fpm-fcgi
Default language	en_US
Default locale	equal
BCscale	12
DB drivers	mysql, pgsql, sqlite
Current driver	mysql
DB version	15 (exp. 15)
Login provider	eloquent
Trusted proxies (.env)

User info	Value
Session start
Session end
Session first
User ID	1
User language	en_US
User locale	nl_NL
Attempt at "nl_NL.utf8"	'nl_NL.utf8'
Attempt at "nl_NL.UTF-8"	'nl_NL.UTF-8'
User agent	Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:86.0) Gecko/20100101 Firefox/86.0

Bonus points

• I searched and nobody reported this bug before
• I have added a stack trace from my log files
• I have added a screenshot.
• I was able to replicate it on the demo site https://demo.firefly-iii.org/

[JC5]

This happens when the field length in combination with the key is too much for your MySQL installation to handle.

https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes

I can't do much code-wise, I'm afraid.

[M-Paladin]

I had the same problem with MariaDB version 10.1 and Firefly 5.4.6
I'm not a database expert but at least the upgrade went well after doing this :

I'm on debian so I began with
apt update
apt ugprade
It seems important to have a mariadb version up-to-date

My database is named "firefly"
~# mysql -u root -p

Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 30
Server version: 10.1.48-MariaDB-0+deb9u1 Debian 9.13

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use firefly
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [firefly]> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)

MariaDB [firefly]> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

MariaDB [firefly]> SET GLOBAL innodb_default_row_format=DYNAMIC;
Query OK, 0 rows affected (0.00 sec)

MariaDB [firefly]> quit
Bye

Hope this help