Monica: Error while php artisan migrate --force

[anmol26s]

I get error while updating the app:

php artisan migrate --force

In Connection.php line 664:

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: ALTER TABLE `cache` CONVERT
  TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;)


In PDOStatement.php line 143:

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


In PDOStatement.php line 141:

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

[djaiss]

@asbiin seems to be linked to the recent migration we've made for the emojis?

[asbiin]

@anmol26s what is your version of mysql ?

[anmol26s]

@asbiin

mysql --version
mysql  Ver 15.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[erdmenchen]

I get the same error when running php artisan monica:update --force

This happend during updating to the latest version of Monica.
Now the instance is broken, because the migrations are not working...

Can we fix this asap? This breaks self hosted versions.

[djaiss]

Really sorry about that.

This is linked to this file https://github.com/monicahq/monica/blob/master/database/migrations/2018_05_24_203000_convert_mysql_encoding.php

It works on all my environments but obviously breaks yours...

I'd like to simply delete this migration and submit a new release but I need to see if it'd solve your problems:

• Delete this file
• Perhaps you could also delete in the migrations table the line 2018_05_24_203000_convert_mysql_encoding (which should be the last line) - but I think it shouldn't be there if the migrations trigger an error
• Run php artisan monica:update again

I hope you've also made a backup of the DB before running migrations - perhaps we should find a way to do that automatically somehow to prevent this kind of things from happening in the future.

[anmol26s]

As far as YunoHost Monica package we will stick to version v2.1.1 till the fix comes.

[erdmenchen]

@djaiss it still gives me the same error after removing the particular migration:

**************************************
*     Application In Production!     *
**************************************

 Do you really wish to run this command? (yes/no) [no]:
 > y

✓ Maintenance mode: on
php artisan down --message=Upgrading Monica v2.2.1 --retry=10

✓ Maintenance mode: off
php artisan up


In Connection.php line 664:

  SQLSTATE[42000]: Syntax error or access violation: 1115 Unknown character set: 'utf8mb4' (SQL: select * from information_schema.tables where table_schem
  a = [...] and table_name = cache)


In PDOConnection.php line 82:

  SQLSTATE[42000]: Syntax error or access violation: 1115 Unknown character set: 'utf8mb4'


In PDOConnection.php line 80:

  SQLSTATE[42000]: Syntax error or access violation: 1115 Unknown character set: 'utf8mb4'

Also I am running this version of mysql:

mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

[erdmenchen]

After reverting back all the changes from
https://github.com/monicahq/monica/commit/643efa1bd60222f48d119f36d94ae90d0c5eea13#diff-8a025ad5d4f4fade612727b1bb0e974f
I was able to run the migration again and update to the latest version.

So deleting the particular migration from this commit and reverting the changes to config/database, fixes this... but this is only a hotfix, this does not fix the root cause I think...

Also this is not a permanent fix, since with the next update this will be reverted back...

[gramakri]

Would be great if there is a link from https://github.com/monicahq/monica/releases/tag/v2.2.1 to this issue (assuming this is the issue that you are warning users about).

[asbiin]

Thank you for the report!

To be clear: we do not test Monica on all databases versions. We just can't. Sorry for that.
This migration should works fine on mysql > 5.7.7. For other database system, I cannot garantee it will works (obviously, it's not!).

It would be very usefull if some of you could test the fix from #1466 on fix/migration-utf8mb4-1442 branch, setting DB_USE_UTF8MB4 to false in .env file.

[anmol26s]

Sorry for the delay.
Fresh installs work. But upgrade from previous version fails.

sudo /usr/bin/php7.1 artisan migrate --force

In Connection.php line 664:

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: ALTER TABLE `cache` CONVERT
  TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;)


In PDOStatement.php line 143:

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


In PDOStatement.php line 141:

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

[asbiin]

@anmol26s ok now it should works.
I've added a new command called migrate:collation, which is called from monica:update.
It handles the utf8<->utf8mb4 migration regarding the value of DB_USE_UTF8MB4 variable.

Hope it will make it!

[anmol26s]

This has fixed the issue. Thanks a lot for quick fixed and giving a wonderful app like Monica.

[asbiin]

Thanks @anmol26s for the test.
(I will close this issue when the PR is merged.)