New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Workaround for install problem, with MySQL and "1071 - Specified key was too long; max key length is 767 bytes" #2115

Closed
lightweight opened this Issue Jun 28, 2016 · 1 comment

Comments

Projects
None yet
2 participants
@lightweight
Copy link

lightweight commented Jun 28, 2016

Technical details

  • YOURLS version: 1.7.2
  • PHP version: 5.5.9-1ubuntu4.17
  • MySQL version: 5.5.49-0ubuntu0.14.04.1
  • nginx 1.4.6

Bug Workaround Summary

This is a report to document how I solved a problem I encountered when installing YOURLS. The installer - browser pointed at https://myurl/admin/install.php - took me to the "install" screen, and the install with the message:

Error creating table 'yourls_url'.
Error creating table 'yourls_options'.
Error creating table 'yourls_log'.
Error creating YOURLS tables.
File .htaccess successfully created/updated.

as also described in #1793

In my case, despite what the error reported, the tables yourls_options and yourls_log were in fact created, but yourls_url was not. I tried creating it manually in my YOURLS database, using

CREATE TABLE IF NOT EXISTS yourls_url ( keyword varchar(200) BINARY NOT NULL, url text BINARY NOT NULL, title text CHARACTER SET utf8mb4, timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, ip VARCHAR(41) NOT NULL, clicks INT(10) UNSIGNED NOT NULL, PRIMARY KEY (keyword), KEY timestamp (timestamp), KEY ip (ip))

however this failed with an error: 1071 - Specified key was too long; max key length is 767 bytes

I was able to get the installer to work by changing the global MySQL configuration in /etc/mysql/my.cnf by a) ensuring that innodb_file_format = barracuda was set in the [mysqld] section, and then adding the line (directly beneath it, by itself):
innodb_large_prefix
to set that variable to "ON" after a restart of MySQL via sudo service mysql restart (verify in values via show variables like 'innodb_file%'; in MySQL)

I was then able to manually create the yourls_url table using this create statement:

CREATE TABLE IF NOT EXISTS yourls_url ( keyword varchar(200) BINARY NOT NULL, url text BINARY NOT NULL, title text CHARACTER SET utf8mb4, timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, ip VARCHAR(41) NOT NULL, clicks INT(10) UNSIGNED NOT NULL, PRIMARY KEY (keyword), KEY timestamp (timestamp), KEY ip (ip)) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

In my case, I used the utf8mb4 charset and utf8mb4_unicode_ci collation, others might choose different values.

I hope this helps someone - and perhaps a more informative error message could be provided by the YOURLS installer...

@ozh

This comment has been minimized.

Copy link
Member

ozh commented Apr 17, 2017

Many thanks for covering this issue and presenting your solution. Much appreciated.

TL;DR : if using InnoDB instead of MYISAM, enable innodb_large_prefix in your DB server config. An alternative would be to decrease keyword varchar(200) to something lower, as described in #2125

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment