backup_migrate - memory error on log_civicrm_preferences

[ehowland]

A CiviCRM site that has been running a couple of years was recently moved to a new host.  Things were going fine until cron stopped working and the Civi database was not getting backed up - actually neither database was getting backed up.

It was getting this error message from drush and something similar from manually running backup_migrate.

Code: [Select]

mysqli_query(): (HY000/2008): MySQL client ran out of memory database.mysqli.inc:114 [warning]
MySQL client ran out of memory 
query: SELECT * FROM `log_civicrm_preferences` database.mysqli.inc:134
Lost connection to MySQL server during query                                         [warning]
query: SHOW CREATE TABLE `log_civicrm_preferences_date` database.mysqli.inc:134
MySQL server has gone away   
query: SELECT * FROM `log_civicrm_preferences_date` database.mysqli.inc:134
MySQL server has gone away                                                           [warning]
query: SHOW CREATE TABLE `log_civicrm_premiums` database.mysqli.inc:134
MySQL server has gone away                                                           [warning]
query: SELECT * FROM `log_civicrm_premiums` database.mysqli.inc:134
MySQL server has gone away                                                           [warning]
. . .

It turned out that log_civicrm_preferences had grown to 320,000+ rows and backup_migrate was doing a Select * from that table and running out of memory.   

I do not understand why backup_migrate was that big.  Viewing the table in phpmyadmin revealed that there were 4000 log_conn_id values  -- if that sheds any light on the problem.

I thought to truncate the table - but since it uses the ARCHIVE engine I could not do that so I ran:
         show create table log_civicrm_preferences

to get the structure and then dropped the entire table, and recreated it with the results of show create table.

That seems to have solved the backup_migrate problem.  I hope this has not broken something deeper.

[noah]

Hi, I just recently encountered the same problem. How has this solution worked out for you, long-term?

[ehowland]

This problem has not re-occurred. I am thinking when cron got working again that served to prune the table in some way.