Author Topic: Error: Incorrect key file for table 'log_civicrm_log' (Read 2523 times)

awasson · April 03, 2013, 05:08:23 pm

First off, this is on Drupal 6 / CiviCRM 3.4.

We can't upgrade it to 4.x at the moment because we're working on a tax module for it and would rather not hack 4.x.

We have two databases, one for Drupal and one for CiviCRM and up until last Thursday, everything was working just fine. Now it seems that we cannot change any records or create new ones without it throwing an error. If I try to renew a membership, I get the following: Incorrect key file for table 'log_civicrm_membership_log'

If I try synching users to contacts., I get an interesting error indicating that the database user doesn't have permission to access the database:
Database Error Code: Access denied for user 'dbuser'@'localhost' to database 'civicrm'.

Anyone run into this issue?

My guess is that the database has a glitch or corrupt table somewhere.

Thanks,
Andrew

awasson · April 03, 2013, 06:53:19 pm

Oh, Jeeze

It looks like I've found the problem. The database seems to be quite corrupt due to the server puking during a database backup via PHPMyAdmin.

I did a database backup using PMA this evening and at the bottom of the SQL I found the following:

Code: [Select]

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <link rel="icon" href="./favicon.ico" type="image/x-icon" />
    <link rel="shortcut icon" href="./favicon.ico" type="image/x-icon" />
    <title>phpMyAdmin</title>
    <link rel="stylesheet" type="text/css" href="phpmyadmin.css.php?server=1&amp;token=571a183e32eb43a29576555a737659fe&amp;js_frame=right&amp;nocache=5435302230" />
    <link rel="stylesheet" type="text/css" href="print.css" media="print" />
    <link rel="stylesheet" type="text/css" href="./themes/pmahomme/jquery/jquery-ui-1.8.16.custom.css" />
    <meta name="robots" content="noindex,nofollow" />
<script src="./js/cross_framing_protection.js?ts=1356015781" type="text/javascript"></script>
<script src="./js/jquery/jquery-1.6.2.js?ts=1356015781" type="text/javascript"></script>
<script src="./js/jquery/jquery-ui-1.8.16.custom.js?ts=1356015781" type="text/javascript"></script>
<script src="./js/update-location.js?ts=1356015781" type="text/javascript"></script>
<script src="./js/config.js?ts=1356015781" type="text/javascript"></script>
<script src="./js/functions.js?ts=1356015781" type="text/javascript"></script>
<script src="./js/jquery/jquery.qtip-1.0.0-rc3.js?ts=1356015781" type="text/javascript"></script>
<script src="./js/messages.php?lang=en&amp;db=&amp;token=571a183e32eb43a29576555a737659fe" type="text/javascript"></script>
<script src="./js/get_image.js.php?theme=pmahomme" type="text/javascript"></script>
<script type="text/javascript">
// <![CDATA[
if (typeof(parent.document) != 'undefined' && typeof(parent.document) != 'unknown'
    && typeof(parent.document.title) == 'string') {
    parent.document.title = ' / localhost /  / log_civicrm_activity | phpMyAdmin 3.5.5';
}
// ]]>
</script>
        <meta name="OBGZip" content="true" />
                <!--[if IE 6]>
        <style type="text/css">
        /* <![CDATA[ */
        html {
            overflow-y: scroll;
        }
        /* ]]> */
        </style>
        <![endif]-->
    </head>

    <body>
            <div id="prefs_autoload" class="notice" style="display:none">
        <form action="prefs_manage.php" method="post">
            <input type="hidden" name="token" value="571a183e32eb43a29576555a737659fe" />
            <input type="hidden" name="json" value="" />
            <input type="hidden" name="submit_import" value="1" />
            <input type="hidden" name="return_url" value="export.php?" />
            Your browser has phpMyAdmin configuration for this domain. Would you like to import it for current session?            <br />
            <a href="#yes">Yes</a> / <a href="#no">No</a>
        </form>
    </div>
    <div id='floating_menubar'></div>
<div id='serverinfo'>
<img src="themes/dot.gif" title="" alt="" class="icon ic_s_host item" />
<a href="main.php?token=571a183e32eb43a29576555a737659fe" class="item">localhost</a>
<div class="clearfloat"></div></div>
<!-- PMA-SQL-ERROR -->
    <div class="error"><h1>Error</h1>
    <p><strong>SQL query:</strong>
<a href="tbl_sql.php?sql_query=SHOW+TABLE+STATUS+FROM+%60psycholo_civicrm%60+LIKE+%27log_civicrm_activity%27&amp;show_query=1&amp;db=&amp;table=log_civicrm_activity&amp;token=571a183e32eb43a29576555a737659fe"><span class="nowrap"><img src="themes/dot.gif" title="Edit" alt="Edit" class="icon ic_b_edit" /> Edit</span></a>    </p>
    <p>
        <span class="syntax"><span class="inner_sql"><a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.1%2Fen%2Fshow.html&amp;token=571a183e32eb43a29576555a737659fe" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">SHOW</span></a>  <span class="syntax_alpha syntax_alpha_reservedWord">TABLE</span>  <span class="syntax_alpha syntax_alpha_reservedWord">STATUS</span>  <span class="syntax_alpha syntax_alpha_reservedWord">FROM</span>  <span class="syntax_quote syntax_quote_backtick">`psycholo_civicrm`</span>  <a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.1%2Fen%2Fstring-comparison-functions.html%23operator_like&amp;token=571a183e32eb43a29576555a737659fe" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span></a>  <span class="syntax_quote syntax_quote_single">'log_civicrm_activity'</span></span></span>
    </p>
<p>
    <strong>MySQL said: </strong><a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.1%2Fen%2Ferror-messages-server.html&amp;token=571a183e32eb43a29576555a737659fe" target="mysql_doc"><img src="themes/dot.gif" title="Documentation" alt="Documentation" class="icon ic_b_help" /></a>
</p>
<code>
#2002 - Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)<br />The server is not responding (or the local server's socket is not correctly configured).
</code><br />
</div><script type="text/javascript">
//<![CDATA[
$(document).ready(function() {
// updates current settings
if (window.parent.setAll) {
    window.parent.setAll('en', 'utf8_general_ci', '1', '', 'log_civicrm_activity', '571a183e32eb43a29576555a737659fe');
}
    // set current db, table and sql query in the querywindow
if (window.parent.reload_querywindow) {
    window.parent.reload_querywindow(
        '',
        'log_civicrm_activity',
        '');
}
    
if (window.parent.frame_content) {
    // reset content frame name, as querywindow needs to set a unique name
    // before submitting form data, and navigation frame needs the original name
    if (typeof(window.parent.frame_content.name) != 'undefined'
     && window.parent.frame_content.name != 'frame_content') {
        window.parent.frame_content.name = 'frame_content';
    }
    if (typeof(window.parent.frame_content.id) != 'undefined'
     && window.parent.frame_content.id != 'frame_content') {
        window.parent.frame_content.id = 'frame_content';
    }
    //window.parent.frame_content.setAttribute('name', 'frame_content');
    //window.parent.frame_content.setAttribute('id', 'frame_content');
}
});

//]]>
</script>
</body>
</html>

Chris Burgess · April 04, 2013, 01:45:49 pm

Code: [Select]

#2002 - Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
The server is not responding (or the local server's socket is not correctly configured).

Looks like your DB server choked while trying to make the backup - hopefully you'll have better luck if you try again at a lower load time, or if you make the backup selectively (eg exclude data from some of the larger tables).

Note that excluding some tables may produce a backup which doesn't restore due to foreign keys - you'll probably have to either understand the key references OR do a bit of trial and error there.

awasson · April 04, 2013, 02:00:11 pm

Hi and thanks for the reply.

Yes, the database has been up and down for this site even today as I was tinkering with it, it went down.

I have a full (as full as I can get at the moment) backup with foreign keys disabled. I thought that perhaps I could create a new database with the database dump I have after removing the extraneous html from it and ten change my setting file to point to it but that didn't work so my plan is the following:

Take the site offline. Do a fresh install of CiviCRM by putting in a fresh settings file and then once it's in, I'll import the cleaned data I have minus the cached tables and the domain table.

That should do the trick. I've used a sanitized version of the DB dump and imported it into a test site and it does in fact respond correctly. What a pain

Chris Burgess · April 04, 2013, 02:10:05 pm

Be mindful that DB workarounds on one version can cause you issues later in the upgrade process - CiviCRM's upgrader can fail if you are upgrading a DB with keys / indexes that aren't in the expected state.

If possible, import a pristine DB structure for your DB version, then overwrite the data in it using a --no-create-info dump. That should leave you with the correct indexes and keys.

If your hosting provider isn't handling CiviCRM DBs well, there are CiviCRM-recommended providers listed on civicrm.org. It's worth your time spending a bit more for a provider who handles CiviCRM well.

---

Do balance the cost of maintaining changes to an older version against what you'd have to do to get to 4.x - you're missing out on lots of bugfixes and (importantly) security fixes. I know where you're coming from, as someone who works with clients to modify CiviCRM to suit their needs - it can be challenging to make the leap to each new version.

The best advice I can give is to develop mindfully of upgrades, using "clean" methods like CiviCRM's hooks and API to ensure you're on the most supported path at all times.

Good luck!

awasson · April 04, 2013, 03:51:36 pm

Hey, thanks for the advice with the --no-create-info dump. I'm not familiar with that particular switch but it sounds like it would take some of the trouble out of moving DBs.

Yes, the hosting of this site has been a concern of mine since it was launched. At some point I intend to move it to my VPS but I'm waiting for the organization to make that decision. They have been extremely faithful to their hosting provider.

When I detected a problem with the database last week I downloaded a dump of it (keys dissabled) and imported it to one of the test sites I have. Fortunately after pulling the garbage out, it looks like it's ok so I have several avenues to go down although some are a bit tedious.

I'll post back my success/failures as I move forward.

Andrew

Chris Burgess · April 04, 2013, 06:16:53 pm

Food for thought: here's a script I wrote recently for a client DB, which had various issues in the upgrader. (Not shown here are a couple of patches I was applying to the files in CRM/Upgrade/Incremental/*.sql when building the site via Drush Make.)

https://gist.github.com/xurizaemon/c75697509627671d3786

The interesting part for you (perhaps) is the way it pushes the default CiviCRM structure to a temp DB, then copies the live data over the top. This went into a shell script because I'm rebuilding the test system repeatedly to track the live system's DB while upgrading the other customisations. Also inline rewriting of table engines and a bunch of other hackish stuff which I wouldn't advise near a production system unless you're very confident about what you're doing

mysqldump options used here are -

* -c -- complete inserts (don't fail on changes to table column order)
* -e -- extended inserts (faster)
* -t -- no create info (data only)
* -n -- no create db (probably not req'd in retrospect)

This is a .erb template of a .sh script, don't expect it to run, just stare in horror at what's being done.

CiviCRM Community Forums (archive)

News:

Author Topic: Error: Incorrect key file for table 'log_civicrm_log' (Read 2523 times)

awasson

Error: Incorrect key file for table 'log_civicrm_log'

awasson

Re: Error: Incorrect key file for table 'log_civicrm_log'

Chris Burgess

Re: Error: Incorrect key file for table 'log_civicrm_log'

awasson

Re: Error: Incorrect key file for table 'log_civicrm_log'

Chris Burgess

Re: Error: Incorrect key file for table 'log_civicrm_log'

awasson

Re: Error: Incorrect key file for table 'log_civicrm_log'

Chris Burgess

Re: Error: Incorrect key file for table 'log_civicrm_log'