0

On Drupal 7.52 and CiviCRM 4.7.13

Recently I discovered Civicrm couldn't connect to MySQL. This wonderful error popped up when registering someone for an event:

DB Error: no database selected

Checking the SQL log I see the following over and over

Couldn't repair table: civicrm.log_civicrm_status_pref

How do I get rid of the problem? MySQL seems unhappy at start up when it says

1:55:43 UTC - mysqld got signal 11 ;

Followed by a bunch of mundane stuff, none appearing error related then 

Some pointers may be invalid and cause the dump to abort.
Query (7fc4005eff10): is an invalid pointer
Connection ID (thread ID): 9
Status: NOT_KILLED

I've heard that if I drop the table civicrm.log_civicrm_status_pref this will fix the problem. Really? Is there anything else I should know? Is it really as simple as 

SELECT civicrm
DROP TABLE civicrm.log_civicrm_status_pref

Then the world returns to normal?

What if I think MySQL is pooched and I want to re-install it. Will I then find a database server with no databases? Thanks for any help the community can provide. Until now CiviCRM has been running great. This is the only serious problem I've had with it but it seems to be quite a problem. I've tried recovering from backup. No go. Same error.

Improve this question
5
  • Still getting the following error, using debug and backtrace DB Error: unknown error Return to home page. Sorry but we are not able to provide this at the moment. DB Error: no database selected Database Error Code: MySQL server has gone away, 2006
    – carclubadmin
    Commented Jan 27, 2017 at 2:20
  • Did you run out of disk space? That causes a load of problems in MySQL. Have you tried the repair table commands in MySQL?
    – Aidan ♦
    Commented Jan 27, 2017 at 8:20
  • Just checked and I'm using 3 gigs on a 40 gig volume. Space looks good. When I run the command myslqcheck it runs fine all the way to the table log_civicrm_action_schedule, then the error 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE'. Attempting to DROP or REPAIR instantly creates the error ERROR 2006 (HY000): MySQL server has gone away. use civicrm; show tables; fails as well. Backup of the data has the same problem.
    – carclubadmin
    Commented Jan 27, 2017 at 16:04
  • I'm curious about something. The file log_civicrm_status_pref.ARN is 38,780,277 in size. A 38 meg file when most of the log_ files are < 15k. Is that weird? Is mysql having a memory problem?
    – carclubadmin
    Commented Jan 27, 2017 at 16:13
  • Don't know ... but it's definitely a MySQL issue rather than Civi directly. If you don't get any answers here you could try forums.mysql.com
    – Aidan ♦
    Commented Jan 27, 2017 at 19:07

1 Answer 1

Reset to default
2

Found the problem. Corrupt log tables. Causes MEGA-chaos for poor mysql. It's crash-a-roo anytime a CiviCRM table is used.

To resolve,

Find where the civicrm databases are stored. If on a typical *nix distribution and using mysql, likely /var/lib/mysql/civicrm

Shutdown mysql (service mysql stop)

Create a new directory (mkdir logtables), now move all the log tables into that directory (mv civicrm.log_* logtables)

Clear out the error.log files so you can see what is happening each time you start mysql. To do that cat /dev/null >/var/log/mysql/error.log (this assumes the mysql server log file is located at /var/log/mysql, which you'd check before issuing that command)

Start mysql (service mysql start)

If you do not have root privileges the OS will freak out when you attempt to move the log tables. Suggest elevating your privileges to root either using sudo (e.g. sudo mv civicrm.log_*) or su root, then go crazy but not too crazy. Remember, there's no undo in Unix/Linux. After starting mysql, check the mysql server's log file to see if it's happy again.

Improve this answer
3
  • MySQL & derivatives have issues with the ARCHIVE format being prone to corruption. If you can trade some diskspace for more reliable storage, convert all log_* tables to InnoDB. If you stick with ARCHIVE, you may find that next time this happens you can just DROP the table in question. CiviCRM can regenerate log tables (maybe someone can clarify process?), or you can grab the CREATE TABLE statement (& data) from a recent backup. There's also a hook to exclude specific tables from logging, see CRM-18104. Or, turn off DB logging! 😁
    – Chris Burgess
    Commented Feb 2, 2017 at 8:35
  • Should I have posted that comment as several different answers to let people vote for their preferred way to deal with ARCHIVE tables? 🗳 🗳 🗳
    – Chris Burgess
    Commented Feb 2, 2017 at 8:38
  • Hey Chris, thanks for the update. Just an FYI, once the table gets corrupted, attempts to drop it or even truncate it all fail. Even a simple command like SHOW TABLES; MySQL freaks out and dies. Very nasty
    – carclubadmin
    Commented Feb 3, 2017 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.