Key length

[nuxeh]

When doing: mysql -u root -p new_database < database.sql I see the error: ERROR 1071 (42000) at line 4: Specified key was too long; max key length is 767 bytes using MariaDB.

This seems to be a limitation of the database itself, according to:

https://answers.launchpad.net/maria/+question/241612

Would there be any way to work around this?

[krayon]

@nuxeh You can safely specify a shorter key length using the column(length_in_characters) format.

So if you are using, for example, a utf8 character set (default for MySQL), then you need (potentially) 3 bytes per character as per the output of SHOW CHARACTER SET;. Thus we can calculate the maximum key length (in bytes) possible:

SELECT 767/(SELECT Maxlen FROM INFORMATION_SCHEMA.CHARACTER_SETS WHERE CHARACTER_SET_NAME='utf8');
+--------------------------------------------------------------------------------------------+
| 767/(SELECT Maxlen FROM INFORMATION_SCHEMA.CHARACTER_SETS WHERE CHARACTER_SET_NAME='utf8') |
+--------------------------------------------------------------------------------------------+
|                                                                                   255.6667 |
+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So, 255. Therefore, this will work:

#
# Table schema for MySQL
#
CREATE TABLE urls (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    url VARCHAR(1000) NOT NULL,
    created DATETIME NOT NULL,
    accessed DATETIME,
    hits INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE (url(255))
);

(Note the 255 length specified for url in the UNIQUE parameter).

[krayon]

Worth noting I have not created a PR for this change as this limitation will differ from DB to DB and even from table engine to table engine (MyISAM vs InnoDB etc). Further still, I believe different versions of MySQL/MariaDB may differ.

I'm sure there's a way to use different CREATE commands based on MySQL versions and engines but I've not bothered to work that out ... yet. In the event I do, I'll create a PR (unless someone else gets there first).

Note too there will almost definitely be a performance impact when inserting new entries.