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:
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 ...
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
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+.
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
You should get ...
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 ...
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
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.
Found the problem. Corrupt log tables. Causes MEGA-chaos for poor mysql. It's crash-a-roo anytime a CiviCRM table is used.
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 ...
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 ...
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 ...
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 ...
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?
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!
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)