Skip to content
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

utf8mb4 charset in MySQL causes error: Specified key was too long; max key length is 767 bytes #2307

Open
teyeheimans opened this issue Oct 12, 2017 · 6 comments

Comments

@teyeheimans
Copy link

@teyeheimans teyeheimans commented Oct 12, 2017

Technical details regarding my environment

  • YOURLS version: 1.7.3
    • Plugins enabled: none
  • PHP version: PHP Version 7.0.22-0ubuntu0.16.04.1
  • Using Aurora (Mysql Variant from Amazon) version 1.14 with default charset utf8mb4_general_ci

Reproducible bug summary

The installation fails and I receive a status code 500. So there is no "friendly" error page. This is because there is no try/catch statement around the query.

I found out that the query for the URL table fails because the primary key is too long: Error Code: 1071. Specified key was too long; max key length is 767 bytes. This is related because I use the default charset utf8mb4_general_ci

The create statement which is executed by yourls:

CREATE TABLE IF NOT EXISTS `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`)
);

For example, this also fails:

CREATE TABLE IF NOT EXISTS `url` (
    `keyword` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  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=INNODB;

However, this works:

CREATE TABLE IF NOT EXISTS `url` (
    `keyword` VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci  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=INNODB;

After dropping the schema and creating a new one it worked fine:

CREATE SCHEMA `yourls` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

Conclusion: the schema does not work correctly with utf8mb4_general_ci

@teyeheimans
Copy link
Author

@teyeheimans teyeheimans commented Oct 12, 2017

Attached a patch file so that there will be no more http 500 errors, but a nice "friendly" error message when the create table statements fail.

create-tables-error.patch.zip

@ozh
Copy link
Member

@ozh ozh commented Oct 12, 2017

This is a known matter, see #2226 and related issue.

This said, your point is valid regarding error messages during SQL install function calls.

@teyeheimans
Copy link
Author

@teyeheimans teyeheimans commented Oct 12, 2017

Ah sorry, I did search for the error message but did not find any similar issues.

@diegofcv
Copy link

@diegofcv diegofcv commented Nov 5, 2019

Same problem here, changing collation worked fine.

@Seegras
Copy link

@Seegras Seegras commented Jan 9, 2020

please fix. we're in 2020, we're not supposed to use only 3-byte UTF-8. The correct solution is not to change the charset, but the CREATE statements

@LeoColomb
Copy link
Member

@LeoColomb LeoColomb commented Jan 9, 2020

please fix. we're in 2020

@Seegras I'm sure now we're in 2020 you will have time to submit a pull request with your own contribution! 😉

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
5 participants
You can’t perform that action at this time.