Error on update database migration: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

[infeeeee]

Describe the bug
I'm on rolling release on my prod server, so I just git pull without checkout. I followed the upgrading guide and on the database migration I got the following error:

Migration 20190510205245 failed during Execution. Error An exception occurred while executing 'CREATE TABLE kimai2_tags (id INT AUTO_IN
CREMENT NOT NULL, name VARCHAR(255) NOT NULL, UNIQUE INDEX UNIQ_27CAF54C5E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4
 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':

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

Now I get error 500. Is it fixable, or should I reinstall kimai? I have a backup before the migration.

Here is the full terminal output: https://pastebin.com/GSUbFrgS

I tried running migration again as root and again as www-data. This is a sudo-less debian install so before this I always run migration as www-data.

Here is the output of the other two trials: https://pastebin.com/J4q6qGri

To Reproduce
Try to update from my current setup.

Logfile
Here is the prod.log around this time: https://pastebin.com/nAgCdR9P

Screenshots

Desktop/Smartphone

Additional context
Add any other context about the problem here.

• Mysql: collation on all tables is utf8mb4_unicode_ci except for the migration table, that's utf8_unicode_ci
  Here is my DB summary:
  

• Kimai version: from 0.9 master to current master

• PHP:

PHP 7.2.16-1+0~20190307202415.17+stretch~1.gbpa7be82 (cli) (built: Mar  7 2019 20:24:15) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.2.16-1+0~20190307202415.17+stretch~1.gbpa7be82, Copyright (c) 1999-2018, by Zend Technologies

[infeeeee]

I guess the problem is that my collation is utf8mb4 by default but the migration script assumes it's utf8 from VARCHAR(255). on utf8mb4 this should be VARCHAR(191): https://stackoverflow.com/a/31474509

[kevinpapst]

Yep, sorry! That was reported yesterday, but I didn't have the time to fix it by now: #782 (comment)

AFAIK this is not happening on newer database engines (both MySQL and MariaDB), I tried it on at least three servers and none failed, thats why I didn't find it.

But I will push an update later, reducing the column length to 100 character, nobody needs tags with 255 chars ;-)

[infeeeee]

I fixed it quick and dirty: I changed 255 to 191 on line 35 in the last migration script. Here:
https://github.com/kevinpapst/kimai2/blob/bbe1f9beda565a61893a017cd56e11f07f6fdf37/src/Migrations/Version20190510205245.php#L35

Now it migrates and runs flawlessly

At least now I have a working setup, will I have a problem later with this change? I think my next git pull will overwrite this, but will it be a problem on the next migration?

[kevinpapst]

No, you won't get into troubles with that solution.
But better reduce the column length to 100 to be fully compatible with the codebase.

[kevinpapst]

Oh btw: are you using the calendar?

[infeeeee]

No

[kevinpapst]

Too bad, then I need to find someone else for testing :D

[infeeeee]

Are you speaking about this PR? #784 I can test it.

[kevinpapst]

Cool, thanks!
Ok, I'll let you know later, I am still changing some stuff.

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs. If you use Kimai on a daily basis, please consider donating to support further development of Kimai.