WordPress.org

Ready to get started?Download WordPress

Forums

WP SlimStat
[resolved] WordPress database error Incorrect key file for table (16 posts)

  1. Texiwill
    Member
    Posted 1 year ago #

    Hello,

    I keep getting this error since upgrading to 2.9.

    WordPress database error Incorrect key file for table '/tmp/#sql_bf8_1.MYI'; try to repair it for query \r\n\t\t\tSELECT t1.*, tb.*,tci.*,tss.*\r\n\t\t\t\tFROM (\r\n\t\t\t\t\tSELECT t1.id, MAX(t1.id) maxid\r\n\t\t\t\t\tFROM slim_stats t1 \r\n\t\t\t\t\tWHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799\r\n\t\t\t\t\tGROUP BY t1.id \r\n\t\t\t\t) AS ts1 INNER JOIN slim_stats t1 ON ts1.maxid = t1.id INNER JOIN slim_browsers tb ON t1.browser_id = tb.browser_id INNER JOIN slim_screenres tss ON t1.screenres_id = tss.screenres_id INNER JOIN slim_content_info tci ON t1.content_info_id = tci.content_info_id\r\n\t\t\t\tORDER BY t1.dt desc\r\n\t\t\t\tLIMIT 0, 50 made by do_action('toplevel_page_wp-slimstat'), call_user_func_array, wp_slimstat_admin::wp_slimstat_include_view, include('/plugins/wp-slimstat/admin/view/index.php'), require_once('/plugins/wp-slimstat/admin/view/panel1.php'), wp_slimstat_db::get_recent, dbrc_wpdb->query, dbrc_wpdb->dbcr_query,

    Thoughts?

    Best regards,
    Edward

    http://wordpress.org/extend/plugins/wp-slimstat/

  2. camu
    Member
    Plugin Author

    Posted 1 year ago #

    For some reason your database seems to be corrupted. This is a problem with the database that holds all your content. If you have access to phpMyAdmin, you may be able to repair the database from that. Otherwise, you should contact your host and ask them to fix it.

    Cheers,
    Camu

  3. Texiwill
    Member
    Posted 1 year ago #

    #sql_bf8_1 is a temporary table or some such that cannot be repaired via normal means. Actually, mysqlcheck will not pick it up nor will mysql command 'check table #sql_bf8_1' nor does it show up in phpMyAdmin... So it must be a temporary table formed by the join.

    Keeping that in mind, I did however run a mysqlcheck against all wordpress tables and they show as OK. So no corruptions reported.

    Given that, there sees to be something else going on here, and this is also apparently related to the whole site stopping until Admin screens refresh properly. This only occurs when viewing admin screens. Recording is happening, BUT updates are not happening on the admin reports and when I do go to an admin screen I get exactly one line that reports what I gave you and the site hangs until the admin screen refreshes. I suspect a db timeout is the root cause related to this failed query.

    If I run the query SELECT t1.*, tb.*,tci.*,tss.* FROM (SELECT t1.id, MAX(t1.id) maxid FROM slim_stats t1 WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799 GROUP BY t1.id) AS ts1 INNER JOIN slim_stats t1 ON ts1.maxid = t1.id INNER JOIN slim_browsers tb ON t1.browser_id = tb.browser_id INNER JOIN tslim_screenres tss ON t1.screenres_id = tss.screenres_id INNER JOIN slim_content_info tci ON t1.content_info_id = tci.content_info_id ORDER BY t1.dt desc LIMIT 0, 50;

    I receive the output:
    ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_bf8_1.MYI'; try to repair it

    Which clearly shows this is a temporary table formed by the JOIN that is of issue. There is plenty of disk space and the tmp space exists in mysql... So this I think is something entirely different.

    Best regards,
    Edward Haletky

  4. camu
    Member
    Plugin Author

    Posted 1 year ago #

    Not sure, but according to this support forum, it could be a bug in MyISAM:

    http://forums.mysql.com/read.php?21,50214,50303#msg-50303

    Can you try to switch to InnoDB? It seems to fix the issue according to

    http://forums.mysql.com/read.php?21,50214,50220#msg-50220

    You can do that directly from within WP SlimStat (Maintenance tab)

    Thanks,
    Camu

  5. camu
    Member
    Plugin Author

    Posted 1 year ago #

    PS: a vote for my plugin would be a nice way to say thank you ;)

  6. Texiwill
    Member
    Posted 1 year ago #

    Hello,

    I am using INNODB. Next suggestion?

    Best regards,
    Edward Haletky

  7. camu
    Member
    Plugin Author

    Posted 1 year ago #

    Next suggestion: google.com :)

  8. Texiwill
    Member
    Posted 1 year ago #

    Hello,

    More research, the JOIN is causing a temporary table that is most likely greater than available file space.... which in my case would be > 10G off a 380M database.....

    So I am removing some larger tables in hopes that frees up enough space. But that seems to be the issue....

    Check out: http://www.coderchris.com/mysql/mysql-51-multiple-tmpdirs-and-incorrect-key-file-for-table-errors/2009/01/22

    I think the query in question needs to be done differently.

    Best regards,
    Edward

  9. Texiwill
    Member
    Posted 1 year ago #

    Update:

    After freeing YET another 4G of space from the system, the JOIN is still too big for the temporary filesystem. Which is 14G.

    My comment is that this query is too complex and needs to change. I noticed that when this fails, the site halts until the db timeout is satisfied on generating this table. I am testing the query directly from the mysql client as to NOT impact the site.

    This did not happen on previous versions of Slimstat, so it appears to be related to a change in the 2.9 version of this query.... and only for the 'get_recent' query....

    Tables sizes: slim_stats 380M, outbound 16K, countries 3.52M, browsers 80K, screenres 160K, content info 64K

    Odd it is still collecting data but showing nothing under 'recent' unless you show by some of the fields (i.e. by username). Nor does it update the visitors and other tabs until you show by some other field. Which means perhaps the group by is not correct?

    Best regards,
    Edward

  10. camu
    Member
    Plugin Author

    Posted 1 year ago #

    Hi Edward,

    thank you for your detailed analysis. get_recent is now joining screenres, to show that information. Weird enough, my test database is about 60MB and the join doesn't take that much space. But I might remove that table from the join and make a special method to display that information in one of the modules.

    Can you try removing screenres from the JOIN to see if things change?

    Thanks,
    Camu

  11. Texiwill
    Member
    Posted 1 year ago #

    More information, the inner query

    SELECT t1.id, MAX(t1.id) maxid FROM slim_stats t1 WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799 GROUP BY t1.id;

    produces 1132453 rows and works just fine.

    The following works just fine as well:

    SELECT * FROM (SELECT t1.id, MAX(t1.id) maxid FROM slim_stats t1 WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799 GROUP BY t1.id) AS ts1;

    However, once you start the inner join, it fails and is incredibly slow, as follows:

    SELECT t1.* FROM (SELECT t1.id, MAX(t1.id) maxid FROM slim_stats t1 WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799 GROUP BY t1.id) AS ts1 INNER JOIN slim_stats t1 ON ts1.maxid = t1.id ORDER BY t1.dt desc LIMIT 0, 50;

    Removing just screenres also just fails... as it is the first INNER JOIN that is causing the issue....

    BTW, I also optimized the tables with no affect.

    Yes the following command works just fine and produces 50 row that look proper.... This method is MUCH faster and uses implied INNER JOINS ...instead of explicit ones...

    SELECT t1.*,tb.*,tci.*,tss.* FROM slim_stats t1,slim_browsers tb,slim_screenres tss,slim_content_info tci WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt > 1356998400 AND t1.dt < 1359676799 and t1.browser_id = tb.browser_id and t1.screenres_id=tss.screenres_id and t1.content_info_id = tci.content_info_id order by t1.dt desc limit 50;

    I tested this against the following command which just lists the latest entries:

    select id,resource,from_unixtime(dt) from slim_stats order by id desc limit 50;

    And the info was the same...

    Best regards,
    Edward

  12. camu
    Member
    Plugin Author

    Posted 1 year ago #

    Edward,

    thank you for your feedback. I must admit that my knowledge of MySQL internals is not so advanced, I could definitely use a hand from a SQL guru ;)

    So you're saying that using the syntax with comma and where would work much better? I'm open to implement that in my code :) I'll test it and see how it goes on my dev server.

    Please feel free to take a look at the rest of the code ( wp-slimstat-db.php under wp-slimstat/admin/view ) and let me know if further optimizations could be implemented.

    Cheers,
    Camu

  13. camu
    Member
    Plugin Author

    Posted 1 year ago #

    Okay, I got the new library using your SQL which replaces the 'explicit' join with the implicit one (it's just a beta, and not all the reports are working at the moment). Would you like to give it a try? If so contact me at

    http://www.duechiacchiere.it/contatto

    mentioning this thread, so that I know who you are :)

    Thanks,
    Camu

  14. camu
    Member
    Plugin Author

    Posted 1 year ago #

    By the way, if I run the explain of both queries on my dev env, MySQL gives me the same exact execution plan, which means that they are equivalent:

    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY <derived2> ALL NULL NULL NULL NULL 24225 Using temporary; Using filesort
    1 PRIMARY t1 eq_ref PRIMARY,browser_id PRIMARY 4 ts1.maxid 1
    1 PRIMARY tci eq_ref PRIMARY PRIMARY 3 duechiac38047.t1.content_info_id 1
    1 PRIMARY tss eq_ref PRIMARY PRIMARY 2 duechiac38047.t1.screenres_id 1
    1 PRIMARY tb eq_ref PRIMARY PRIMARY 2 duechiac38047.t1.browser_id 1
    2 DERIVED t1 ALL PRIMARY NULL NULL NULL 319406 Using where; Using temporary; Using filesort

    1 PRIMARY <derived2> ALL NULL NULL NULL NULL 24225 Using where; Using temporary; Using filesort
    1 PRIMARY t1 eq_ref PRIMARY,browser_id PRIMARY 4 ts1.maxid 1
    1 PRIMARY tci eq_ref PRIMARY PRIMARY 3 duechiac38047.t1.content_info_id 1
    1 PRIMARY tss eq_ref PRIMARY PRIMARY 2 duechiac38047.t1.screenres_id 1
    1 PRIMARY tb eq_ref PRIMARY PRIMARY 2 duechiac38047.t1.browser_id 1
    2 DERIVED t1 ALL PRIMARY NULL NULL NULL 319406 Using where; Using temporary; Using filesort

  15. camu
    Member
    Plugin Author

    Posted 1 year ago #

    First query:

    explain SELECT t1.*, tb.*,tci.*,tss.* FROM ( SELECT t1.id, MAX(t1.id) maxid FROM wp_slim_stats t1 WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799 GROUP BY t1.id ) AS ts1, wp_slim_stats t1, wp_slim_browsers tb, wp_slim_screenres tss, wp_slim_content_info tci WHERE t1.browser_id = tb.browser_id AND t1.screenres_id = tss.screenres_id AND t1.content_info_id = tci.content_info_id AND ts1.maxid = t1.id ORDER BY t1.dt desc LIMIT 0, 50

    second query:

    explain SELECT t1.*, tb.*,tci.*,tss.* FROM ( SELECT t1.id, MAX(t1.id) maxid FROM wp_slim_stats t1 WHERE t1.id <> '' AND t1.id <> '__l_s__' AND t1.dt BETWEEN 1356998400 AND 1359676799 GROUP BY t1.id ) AS ts1 INNER JOIN wp_slim_stats t1 on ts1.id = t1.id INNER JOIN wp_slim_browsers tb on t1.browser_id = tb.browser_id INNER JOIN wp_slim_screenres tss on tss.screenres_id = t1.screenres_id INNER JOIN wp_slim_content_info tci ON t1.content_info_id = tci.content_info_id AND ts1.maxid = t1.id ORDER BY t1.dt desc LIMIT 0, 50

    What about changing the order of the tables in the INNER JOIN (leaving the biggest table for last?) Do you think that would help?

  16. camu
    Member
    Plugin Author

    Posted 1 year ago #

    Marking as resolved, since we are talking about this off-line.

    Thanks

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic

Tags

No tags yet.