Database migration failed with "Index column size too large. The maximum column size is 767 bytes" while upgrading to 3.0.0

[brownsugar]

How Shlink is set-up

• Shlink Version: 2.10.3
• PHP Version: 8.0.16
• How do you serve Shlink: Self-hosted Apache
• Database engine used: MariaDB 10.5.15

Summary

Error occurred while upgrading Shlink instance from 2.10 to 3.0.x, looks like related to DB charset.
Below is the upgrading logs.

$ php vendor/bin/shlink-installer update -vvv

 Welcome to Shlink!!
 This tool will guide you through the installation process.

 Do you want to import configuration from previous installation? (You will still be asked for any new config option that did not exist in previous shlink versions) (yes/no) [yes]:
 >

 Previous shlink installation path from which to import config:
 > ../shlink-2.10

INTEGRATIONS
============

 Do you want to use a redis instance, redis cluster or redis sentinels as a shared cache for Shlink? (recommended if you run a cluster of Shlink instances) (yes/no) [no]:
 >

 Custom configuration properly generated!

Updating database... [Running "/opt/plesk/php/8.0/bin/php bin/cli db:migrate"]   RUN  '/opt/plesk/php/8.0/bin/php' 'bin/cli' 'db:migrate'
  OUT  Migrating database...
  OUT
  ERR    RUN  '/opt/plesk/php/8.0/bin/php' 'vendor/doctrine/migrations/bin/doctrine-migrations.php' 'migrations:migrate' '--no-interaction'
  ERR    ERR
  ERR    ERR  In ExceptionConverter.php line 119:
  ERR    ERR
  ERR    ERR    [Doctrine\DBAL\Exception\DriverException (1709)]
  ERR    ERR    An exception occurred while executing a query: SQLSTATE[HY000]: General err
  ERR    ERR    or: 1709 Index column size too large. The maximum column size is 767 bytes

...

  ERR    ERR  migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration CONFIGURATION] [--em EM] [--conn CONN] [--] [<version>]
  ERR    ERR
  ERR    ERR
  ERR  In Process.php line 267:
  ERR
  ERR    [Symfony\Component\Process\Exception\ProcessFailedException]
  ERR    The command "'/opt/plesk/php/8.0/bin/php' 'vendor/doctrine/migrations/bin/d
  ERR    octrine-migrations.php' 'migrations:migrate' '--no-interaction'" failed.
  ERR
  ERR    Exit Code: 255(Unknown error)
  ERR
  ERR    Working directory: /var/www/vhosts/the__path
  ERR
  ERR    Output:
  ERR    ================
  ERR
  ERR
  ERR    Error Output:
  ERR    ================
  ERR
  ERR    In ExceptionConverter.php line 119:
  ERR
  ERR
  ERR      [Doctrine\DBAL\Exception\DriverException (1709)]
  ERR
  ERR      An exception occurred while executing a query: SQLSTATE[HY000]: General e
  ERR    rr
  ERR      or: 1709 Index column size too large. The maximum column size is 767 byte
  ERR    s

...

  ERR  db:migrate
  ERR
  ERR
  RES  1 Command did not run successfully

[acelaya]

Probably related with this #1358

You will have to update to a newest Mariadb version

[brownsugar]

I think my MariaDB version is new enough as it's still under supported, and it's quite dangerous to upgrade the version as it's also used in other services.

Finally I found the solution to fix this, the Row_format of original tables was set to Compact.

SHOW TABLE STATUS FROM {DB_NAME}

Just alter them all to Dynamic.

ALTER TABLE api_key_roles row_format = dynamic;
ALTER TABLE api_keys row_format = dynamic;
ALTER TABLE domains row_format = dynamic;
ALTER TABLE migrations row_format = dynamic;
ALTER TABLE short_urls row_format = dynamic;
ALTER TABLE short_urls_in_tags row_format = dynamic;
ALTER TABLE tags row_format = dynamic;
ALTER TABLE visit_locations row_format = dynamic;
ALTER TABLE visits row_format = dynamic;

Then perform the update action, solved the issue.

But I'm not sure if this will bring any side effects, though all features still works good now.

[acelaya]

Thanks for the tip. I'll check if it's possible to incorporate this to Shlink.