ubuntu 16.04 - Error creating table 'yourls_url'. Error creating YOURLS tables. #2125

Open
infotek opened this Issue Jul 23, 2016 · 5 comments

Projects

None yet

6 participants

@infotek
infotek commented Jul 23, 2016

Fresh Ubuntu 16.04 install.

install.php failed to create the first two tables.

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

I enables sql query logging and attempted to replicate statements in the sql query log. The create tables command produced an error.

MariaDB [yourlsdb]> CREATE TABLE IF NOT EXISTS yourls_url (keyword varchar(200) BINARY NOT NULL,url text BINARY NOT NULL,title text CHARACTER SET utf8,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))
-> ;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

version:

MariaDB [yourlsdb]> select version();
+----------------------------------+
| version() |
+----------------------------------+
| 10.0.25-MariaDB-0ubuntu0.16.04.1 |
+----------------------------------+
1 row in set (0.01 sec)

@infotek
infotek commented Jul 23, 2016

I added "ENGINE=MyISAM ;" to successfully create the tables.

MariaDB [yourlsdb]> CREATE TABLE IF NOT EXISTS yourls_url (keyword varchar(200) BINARY NOT NULL,url text BINARY NOT NULL,title text CHARACTER SET utf8,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=MyISAM
-> ;
Query OK, 0 rows affected (0.00 sec)

@stevenmcastano

The only problem with MyISAM is it's a little slower and has some row locking issues.... it shouldn't be a big problem if your site isn't going to get a lot of hits, but the big reason to go with the default engine of InnoDB is that if you ever want to setup a MariaDB Galera cluster (a.k.a. two MariaDB servers), the MyISAM tables won't get replicated.

The fact that you had to specify and engine probably means there's something missing in your my.cnf file, I'd have to look at MariaDB 10.1 running on Ubuntu 16, but the default engine if none is specified should set to InnoDB.

@Jackobli
Jackobli commented Aug 12, 2016 edited

I can confirm this error.
Found a hint pointing to "set global innodb_large_prefix=on;"
but while using this, YOURLS does only create the table "options" and not "log". But on both "on" or "off" it never creates the table "url".
So this is an error on Ubuntu/MariaDB or on YOURLS 1.7?
yourls_create_tables_error

@tcmeggs
tcmeggs commented Aug 22, 2016

I also have this error and worked around it by defining the table engine as MyISAM. It looks like some people apply a subset of the key rather then the entire key as a solution. http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes

@folz
folz commented Aug 29, 2016

I just got bit by this. I ended up rerunning the SQL CREATE TABLE command for yourls_url manually, and replaced varchar(200) in keyword with varchar(191) since 191 chars * 4 bytes/char = 764 < 767.

It looks like the issue is twofold:

  1. YOURLS uses natural keys as the PRIMARY KEY, instead of autoincrementing integer IDs
  2. Switching to utf8mb4 increased bytes-stored-per-character from 3 (with utf8) to 4 (utf8mb4).

There's a hard max key length of 767 bytes for InnoDB, and a 200-char string with utf8 takes up to 600 bytes max while a 200-char string with utf8mb4 takes up to 800 bytes max.

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