28fb3108

Forum Replies Created

Viewing 2 replies - 1 through 2 (of 2 total)
  • Forum: Plugins
    In reply to: [WP Content Security Plugin] WordPress database error Specified key was too long
    Thread Starter 28fb3108

    (@28fb3108)

    9 years, 2 months ago

    … 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

    Forum: Plugins
    In reply to: [WP Content Security Plugin] WordPress database error Specified key was too long
    Thread Starter 28fb3108

    (@28fb3108)

    9 years, 2 months ago

    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.

Viewing 2 replies - 1 through 2 (of 2 total)