WordPress database error Specified key was too long

  • Hi

    I’m using this plugin on several websites and have managed to successfully implement a CSP on each. Thanks for a great plugin that really fills a gap in the WP security model.

    However, I get the this error (see below), in the wp-admin error_log. All my sites are (currently) running WP v4.7.2. All are running the latest version of the plugin. All run on the same cloud host.

    The error is (I think) created when a web page is served. Sometimes I get 12 errors a minute. Sometimes a there are gaps of many minutes in the log. The log has built up to ~2MB in 6 weeks.

    My websites are only family / small business sites, but I’d like to get them set up properly.

    I have googled the error and my guess is that the plugin is trying to create a table with a key that is too big for my database. The limit on key length seems to be 100bytes for utf8mb4 DBs. People seem to think that this is a reasonable limit for a key.

    Do you know if this is what is happening? And if there’s any way to fix things or apply a work-around, so that I can stop these errors being created?

    Many thanks,

    Dom.

    Error:
    [06-Jan-2017 12:49:32 UTC] WordPress database error Specified key was too long; max key length is 1000 bytes for query CREATE TABLE myuniquetableprefix_wpcsplog (
    id mediumint(9) NOT NULL AUTO_INCREMENT,
    violated_directive varchar(50) NOT NULL default ”,
    blocked_uri varchar(1024) NOT NULL default ”,
    document_uri varchar(1024) NOT NULL default ”,
    useragent varchar(1024) NOT NULL default ”,
    remoteaddress varchar(1024) NOT NULL default ”,
    information text NOT NULL default ”,
    createdon timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY id (id),
    KEY violated_directive (violated_directive, blocked_uri),
    KEY createdon (createdon)
    ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci made by require_once(‘wp-load.php’), require_once(wp-config.php’), require_once(‘wp-settings.php’), do_action(‘plugins_loaded’), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, wpCSPAdmin::update_database, dbDelta

    • This topic was modified 8 years, 1 month ago by 28fb3108.
    • This topic was modified 8 years, 1 month ago by 28fb3108. Reason: Corrected another typo
Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author Dylan

    (@dyland)

    I was trying to set these fields big enough for most uses, so I picked a large number but they really don’t need to be that large.

    It looks like its the key violated_directive (violated_directive, blocked_uri) which would be 1074 in total

    Manually create the table by running the following command through your phpMyAdmin;
    CREATE TABLE myuniquetableprefix_wpcsplog (
    id mediumint(9) NOT NULL AUTO_INCREMENT,
    violated_directive varchar(50) NOT NULL default ”,
    blocked_uri varchar(900) NOT NULL default ”,
    document_uri varchar(1024) NOT NULL default ”,
    useragent varchar(1024) NOT NULL default ”,
    remoteaddress varchar(1024) NOT NULL default ”,
    information text NOT NULL default ”,
    createdon timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY id (id),
    KEY violated_directive (violated_directive, blocked_uri),
    KEY createdon (createdon)
    ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci

    Let me know if that works and I will change the next release to limit this field to 900 characters, still more than enough.

    Thread Starter 28fb3108

    (@28fb3108)

    Dylan

    Thanks for looking into this for me. I used your code but ran into a copy/paste issue that turned the two single quotes into a double quote. I fixed this and tried again, but was still hitting the 1000 byte index limit.

    A bit of trial and error found that reducing the length of blocked_uri to varchar(200) allowed the table to be created. Varchar(201) still gave the 1000 byte error. This gives a total key length of 250 varchars for violated_directive (violated_directive, blocked_uri)

    Some googling found this. It looks like using the utf8mb4 character set means that MySQL 5.5.3 (and up) use 4 bytes per character for indexes. So a index of a varchar (250) is the biggest you can go, as it actually uses 1000 bytes.

    Is this going to be long enough to your plugin to log correctly? Some people on Stack Exchange recommend having long field lengths, but only indexing the first 20-50 chars.

    Thanks again,

    Dom.

    Thread Starter 28fb3108

    (@28fb3108)

    … specifically this makes the phpMyAdmin command to create the table:

    CREATE TABLE yourtableprefix_wpcsplog(
    id MEDIUMINT( 9 ) NOT NULL AUTO_INCREMENT ,
    violated_directive VARCHAR( 50 ) NOT NULL DEFAULT ”,
    blocked_uri VARCHAR( 200 ) NOT NULL DEFAULT ”,
    document_uri VARCHAR( 1024 ) NOT NULL DEFAULT ”,
    useragent VARCHAR( 1024 ) NOT NULL DEFAULT ”,
    remoteaddress VARCHAR( 1024 ) NOT NULL DEFAULT ”,
    information TEXT NOT NULL DEFAULT ”,
    createdon TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY id( id ) ,
    KEY violated_directive( violated_directive, blocked_uri ) ,
    KEY createdon( createdon )
    ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘WordPress database error Specified key was too long’ is closed to new replies.

Tags