12

I backed the database up, opened it up in Sequel Pro and went to the civicrm_system_log table. Under "Table Info" I could select "InnoDB" from the dropdown. Then I cleared the caches on the CiviCRM installation and the system status came back green ("System Status: OK"). Per a comment from George Lozier on another thread, the MySQL query to do the same is: ...


11

If logging is on then results will be retrieved from the log_xxx tables not civicm_log. Normally these tables use the archive engine - which is not indexable. We have in some cases selectively converted these to INNODB & added indexes Archive tables are supposed to be quick to write to and slow to read from. They are also non-transactional - which ...


7

Sounds good. To be sure, please execute ///civicrm/menu/rebuild?reset=1&triggerRebuild=1 which fixes the trigger and function definitions in the database.


4

If you don't need the logs, you could delete them. Which engine are your log tables using? There is a useful article by Eileen about ARCHIVE vs INNODB: https://civicrm.org/blog/eileen/who-did-what-when


3

Most people who have this issue are on CiviCRM 5.3-5.6, and is caused by the "missing logging tables" check assuming that temp tables should have a logging table. There is a fix available on Github, and it's part of CiviCRM 5.7+.


3

The error occurs when it tries to create the table _drupal7`.log_civicrm_value__4. It is trying to create it having storage engine of ARCHIVE. You can't see the whole command from the log. It would be something like create table_name ( column1, column2) Engine=ARCHIVE... So do you have that engine? From mysql do a show engines; You should get ...


3

For people googling, I significantly increased the speed of the Change Log by following Eileen's advice in her answer: convert some tables (activity, activity_contact, contact, group, group_contact) from ARCHIVE to INNODB, and adding indexes. The Change Log still takes ~20 seconds to load, but that's much better than the minutes it used to. We've taken a ...


3

A few orgs who use logging on fairly high volume sites have modified the logging tables to either use MyISAM or InnoDB and have had fairly good success with it. Can you please report back if you adopt this approach and your success with it


2

Take a look at the INNODB logging extension. It converts the log table format from ARCHIVE to INNODB, adding indexes and speeding up as a result.


2

The log tables as originally implemented (I have checked for a few years if this is the case) also contain a large number of superfluous records due to writes that don't actually change the data. Improving the triggers to check for a difference between old and new values before the write to log would improve things for everyone.


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 ...


2

Single command to update all tables is: SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';


2

Writing "code" could be as simple as a cron script that runs daily and does mysql -u user -ppassword -e "DELETE FROM log_civicrm_group WHERE log_date < DATE_SUB(NOW(), INTERVAL 2 MONTH)" databasename, which deletes records older than 2 months. EDIT: To address your comment, if you want to completely disable logging but just for this table, you could drop ...


2

Here is summary of our situation after ours reappeared. This was actually a wrong message shown on the status page. The logging is disabled on /civicrm/admin/setting/misc?reset=1. The flow which can lead to this error is - Logging is enabled on the site which generates a list of log tables in the database. User now disables the logging functionality on ...


1

There is an extension which will record exports using civicrm export from contact search. Unless you have this extension installed there really isn't a way to see if there were specific exports of information. More information on the extension can be found here


1

As Demerit mentioned, you have the same issues I reported. I don't think it's about the number of fields that's the issue, but the combination of data types. If you are receiving that error, be careful if your site is live because some of your cases will hit an error and you won't be able to view the case page. Here's what I did: If possible, you will want ...


1

Cleared Litespeed Cache (a Wordpress cache) and error persisted. Cleared CiviCRM Cache - error disappeared - but returned the next day. Same result as clicking rebuild tables. So the issue persists.


1

UPDATE in case anyone stumbles on this question. There is a working answer at Civi 5.9 - DB Error: no such table / cannot disable logging


1

Short answer: Change the storage engine of the table with the following command in mysql: ALTER TABLE log_civicrm_install_canary ENGINE=InnoDB The civicrm_install_canary is a table used for installation purposes. The purpose is explained in the answer on What is the installation canary?


1

I had the issue which kept recurring - traced it to an extension that I use for exporting to excel - was creating the temp file in MyISAM - removed the extension and the issue went away.


1

I discussed this with Bjorn Endres, and his idea is to turn logging off, drop the log tables, and then turn logging back on. This is, I think, a good answer. The reason our log tables are so big is because we have transferred data from a legacy system. We don't really need 99% of the data in these logs so this is what we intend to do. Thanks, Bjorn!


1

Pretty sure an index would help indeed, the question is which one? Can you log slow queries on your database and see which queries are taking a lot of time, see where it can be added? finetune how many seconds before a query is slow (any query that takes more than 1 or 2 sec is slow is what I use) http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html


Only top voted, non community-wiki answers of a minimum length are eligible