Top Exits not working, table serendipity_exits not created

Found a bug? Tell us!!
Post Reply
User avatar
erAck
Regular
Posts: 111
Joined: Mon Feb 16, 2015 2:20 am

Top Exits not working, table serendipity_exits not created

Post by erAck » Sun Feb 22, 2015 6:46 pm

Since long the Top Exits plugin didn't display anything for me but I wasn't bothered enough to investigate why. Actually I hoped that it would magically work after an upgrade to 2.0 but it didn't. So today I sat down and dug into, and the cause is that the serendipity_exits table was never created in the MySQL database. Taking the structure from sql/db.sql I tried to create the table but got only the famous

Code: Select all

Specified key was too long; max key length is 1000 bytes
error. Yeah.. it was mentioned here already several times, and the underlying cause is that in an UTF-8 key 3 bytes are reserved per character, which for host(128)+path(255)+day(8)+entry_id(11) results in 3*(128+255)+8+11 = 1168.

That's 168/3=56 too many characters. Splitting that up into weighed host and path I used host=128-1*18=110 and path=255-2*18-2=217 which gives a key length of 3*(110+217)+8+11 = 1000.

I created the table with these new values and the corresponding index and voila, Top Exits works.

This should be fixed in the next release.

mattsches
Regular
Posts: 440
Joined: Sat Nov 05, 2005 10:35 pm
Location: Wiesbaden, Germany
Contact:

Re: Top Exits not working, table serendipity_exits not creat

Post by mattsches » Sun Feb 22, 2015 9:01 pm

erAck wrote:I created the table with these new values and the corresponding index and voila, Top Exits works.
Could you please post the SQL you used for creating the table? Did you just make the host and path fields shorter e.g. varchar(110)?

User avatar
erAck
Regular
Posts: 111
Joined: Mon Feb 16, 2015 2:20 am

Re: Top Exits not working, table serendipity_exits not creat

Post by erAck » Mon Feb 23, 2015 1:17 am

Actually to ensure things are created exactly as s9y would, I used a hacked copy of s9y where sq/db.sql contained only the exits related entries, with changed host varchar(110) and path varchar(217), and a minimal php script to install the database:

Code: Select all

<?php
# Install database from sql/db.sql
# Call this file in a copy of s9y.
# Only the desired database and index(es) must be present in sql/db.sql

    define('IN_serendipity', true);
    include('serendipity_config.inc.php');
    serendipity_installDatabase();
?>
You could also create the table and index manually in phpMyAdmin, make sure you use the MYISAM storage engine and utf8 encoding and collation utf8_unicode_ci on all varchar fields.
Or with SQL statements as expanded by s9y:

Code: Select all

SET storage_engine=MYISAM
CREATE TABLE serendipity_exits (entry_id int(11) NOT NULL default '0',day date NOT NULL,count int(11) NOT NULL default '0',scheme varchar(5),host varchar(110) NOT NULL,port varchar(5),path varchar(217),query varchar(255),PRIMARY KEY  (host,path,day,entry_id)) /*!40100 CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
CREATE INDEX exits_idx ON serendipity_exits (entry_id,day,host);
I hope that does it for you.

Post Reply