Support » Plugin: WP Statistics » Major: Table creations fail due to large index key size

  • Thanks for your work on 13.0.3, but I’m seeing a problem in error_log creating the tables wp_statistics_visitor, wp_statistics_pages and wp_statistics_historical.

    We are on CentOS, PHP 7.3, Mariadb 10.3.

    The tables are not created.

    Here is the last error:

    WordPress database error Specified key was too long; max key length is 1000 bytes for query 
    CREATE TABLE wp_statistics_historical (
    	category varchar(25) NOT NULL,
    	page_id bigint(20) NOT NULL,
    	uri varchar(255) NOT NULL,
    	value bigint(20) NOT NULL,
    	KEY category (category),
    	UNIQUE KEY page_id (page_id),
    	UNIQUE KEY uri (uri)
    ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci made by activate_plugin, do_action('activate_wp-statistics/wp-statistics.php'), WP_Hook->do_action, WP_Hook->apply_filters, WP_Statistics::install, WP_STATISTICS\Install->install, WP_STATISTICS\Install::create_table, WP_STATISTICS\Install::table_sql, dbDelta
Viewing 4 replies - 1 through 4 (of 4 total)
  • Perhaps this explains why (in v 13.0.3) my Pages Statistics table, when set to ‘week’, shows the all-time total visits instead of the week’s total visits.

    Thread Starter kitchin


    This is the proper way to do it, according to WP.
    The $max_index_length calculated is used for creating default tables wp_termmeta, wp_commentmeta, wp_postmeta, wp_posts, wp_usermeta, and, in WPMU, wp_blogs and wp-site_meta.

    	 * Indexes have a maximum size of 767 bytes. Historically, we haven't need to be concerned about that.
    	 * As of 4.2, however, we moved to utf8mb4, which uses 4 bytes per character. This means that an index which
    	 * used to have room for floor(767/3) = 255 characters, now only has room for floor(767/4) = 191 characters.
    	$max_index_length = 191;
    	// Blog-specific tables.
    	$blog_tables = "CREATE TABLE $wpdb->termmeta (
    	meta_id bigint(20) unsigned NOT NULL auto_increment,
    	term_id bigint(20) unsigned NOT NULL default '0',
    	meta_key varchar(255) default NULL,
    	meta_value longtext,
    	PRIMARY KEY  (meta_id),
    	KEY term_id (term_id),
    	KEY meta_key (meta_key($max_index_length))
    ) $charset_collate;
    CREATE TABLE $wpdb->terms (
     term_id bigint(20) unsigned NOT NULL auto_increment,
     name varchar(200) NOT NULL default '',
     slug varchar(200) NOT NULL default '',
     term_group bigint(10) NOT NULL default 0,
     PRIMARY KEY  (term_id),
     KEY slug (slug($max_index_length)),
     KEY name (name($max_index_length))
    ) $charset_collate;
    • This reply was modified 2 years, 3 months ago by kitchin.
    • This reply was modified 2 years, 3 months ago by kitchin.
    Thread Starter kitchin


    The developer has committed a likely fix to this in Github. There should be a new version to replace 13.0.3.

    Plugin Contributor veronalabs



    Thank you for using our plugin.

    We tried our best to fix the issues, and we released the new version yesterday.

    Please update your plugin to v13.0.4.

    Have a nice weekend.


Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Major: Table creations fail due to large index key size’ is closed to new replies.