Installing yourls on Ubuntu 18.04 with MariaDB 10.1.x Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
Issue:
When installing yourls and clicking the INSTALL button under /admin/install.php you get a server Error 500 and the following error shows up in the log:
2018/12/08 21:22:10 [error] 21768#21768: *2 FastCGI sent in stderr: “PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes in /home/webuser/public_html/includes/vendor/aura/sql/src/ExtendedPdo.php:748
Stack trace:
#0 /home/webuser/public_html/includes/vendor/aura/sql/src/ExtendedPdo.php(748): PDOStatement->execute()
#1 /home/webuser/public_html/includes/functions-install.php(252): Aura\Sql\ExtendedPdo->perform(‘CREATE TABLE IF…’)
#2 /home/webuser/public_html/admin/install.php(46): yourls_create_sql_tables()
#3 {main}
thrown in /home/webuser/public_html/includes/vendor/aura/sql/src/ExtendedPdo.php on line 748″ while reading response header from upstream, client: 172.33.44.54, server: domain.com, request: “POST /admin/install.php? HTTP/1.1”, upstream: “fastcgi://unix:/var/run/php/php7.2-fpm.sock:”, host: “domain.com”, referrer: “https://domain.com/admin/install.php”
drop database yourls
Solution:
Check that your database it set to utf8, NOT utf8mb4. Change it to utf8 and the issue will resolve. Your install of MariaDB may be set up to default new databases to utf8mb4 which has a quarter of the key length of utf8.
MariaDB [(none)]> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "yourls";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8mb4 |
+----------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> ALTER DATABASE yourls CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "yourls";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8 |
+----------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
Sources:
https://github.com/YOURLS/YOURLS/issues/2350
https://www.a2hosting.com/kb/developer-corner/mysql/convert-mysql-database-utf-8#a-nameprocCurrentCharSetaDetermine-the-current-character-encoding-set
https://github.com/YOURLS/YOURLS/issues/2226
THX! 😉