Hot answers tagged

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


9

Yes, running OPTIMIZE TABLES on a regular basis is beneficial. I dont think there is significant risk with it. I'd run it on a copy of the DB first. If you have the memory on your database server increasing the cache and buffer sizes of mysql is a good thing, IMO In the past, i've found: https://www.percona.com/blog/ quite a useful resource. Also some ...


8

If you want to see everything, you can enable MySQL logging as follows (don't do this on production!): From the mysql console (or within an application like MySQL Workbench), run: SET global general_log = 1; SET global log_output = 'table'; Look at all queries run within the last 15 seconds select * from mysql.general_log where command_type = '...


8

Try setting: define('CIVICRM_DAO_DEBUG', 1); in your civicrm.settings.php file, this might work based on a quick glance at the code. The output will appear onscreen and in the CiviCRM log file (eg sites/default/files/civicrm/ConfigAndLog/CiviCRM*log or media/civicrm/ConfigAndLog/CiviCRM*log or wp-content/plugins/files/civicrm/ConfigAndLog/CiviCRM*log)


8

Recommend setting MAMP to use PHP5.6 if you have that option. The screenshots show MAMP is set to use PHP7, but CiviCRM isn't ready for PHP7 yet. (PHP7 removes ext/mysql in favour of ext/mysqli and ext/pdo_mysql. CiviCRM looks for ext/mysql, and the installer test is bailing out when it detects that this isn't supported.) CiviCRM's PHP requirements are ...


7

The suggestion to increase the join_buffer_size is a good one if your server can afford the 128K. The number of queries with joins on unindexed fields, which need more cache, is not insignificant. (Background: There are write performance downsides to putting an index on every field that might have a join put on it. I believe in a few places in CiviCRM there ...


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.


7

I will answer your questions #1 and #2. (I'm not sure what you mean by #3. If #3 is sufficiently unrelated to #1 and #2, I would suggest creating a separate question for it) Why an ER (Entity Relationship) diagram might not be helpful As a visual learner, I too looked for an ER diagram when first trying to understand the CiviCRM database schema. I was ...


6

It seems like it's still under discussion as of last week. See e.g. https://lab.civicrm.org/dev/core/issues/979


5

Depending on your end goal, one solution is to set the External ID of a record in instance B, to the Internal ID from instance A. The Internal ID (civicrm_contact.id) is a primary key; the database engine assigns that field, and you risk data corruption by trying to keep it in sync yourself. The reliable way to keep them in sync is to have instance B act ...


5

With WordPress, 90% of the time it's just the WordPress timezone that needs to be reset. Make sure that's set right as a first step.


5

Two issues. 1. Database is getting reset when you upgrade 2. you cannot restore from backup. Answer to #1 is this: Make sure you are following the upgrade procedure on this page: http://wiki.civicrm.org/confluence/display/CRMDOC/Installation+and+Upgrades Answer to #2 is this, before restoring from backup, follow this procedure. a. ensure that the backup ...


4

Before pronouncing the old database deceased, I would make sure the structure is correct and if not what is broken. You can try this: https://github.com/progressivetech/civi-schema-harmonizer or http://wiki.civicrm.org/confluence/plugins/servlet/mobile#content/view/86213632. The current dB may be ok.


4

Here's a patch that seems to address the issue, although there could still be compatibility issues in the PEAR mysql and mysqli library versions used by CiviCRM. This patch is for CiviCRM 4.7.4: diff --git a/civicrm/CRM/Core/Lock.php b/civicrm/CRM/Core/Lock.php index b594e0b..4643fc4 100644 --- a/civicrm/CRM/Core/Lock.php +++ b/civicrm/CRM/Core/Lock.php @@ ...


4

CiviCRM historically uses the CRM_Core_DAO::VALUE_SEPARATOR character as a serialization technique for storing multiple values in a field. Best practice is use the API for accessing values, or if you really want to do direct database access then you need to treat that value as a multiple value separator.


4

This line reads the data from the table civicrm_setting and specifically the column value. You could use a SQL client to run the query: SELECT id, name, value FROM civicrm_setting; Typical values would look these: NULL i:0; (an integer) b:1; (a boolean?) s:20:"CRM/common/fatal.tpl"; (a string) a:1:{s:3:"key";s:5:"value";} (an array or dictionary) Skim ...


4

2) You can use a tool like SchemaSpy to extract the structure of the database, have the foreign key displayed in a table or on a schema and navigate easily between related tables. You can see an example (and use) of SchemaSpy doc generated for CiviCRM 4.7 https://doc.symbiotic.coop/dev/civicrm/v47/schema/index.html EDIT: For those interested, there is a ...


4

There are only two scenarios in which CiviCRM requires the SUPER privilege: You're using a very old version of MySQL. Versions of MySQL 5.1.6 and higher support the TRIGGER permission. Before that, SUPER was necessary. MySQL 5.1 reached its "end of life" at the end of 2013. No modern server should have it installed. You're attempting to use the advanced ...


4

It turns out that if you are using Wordpress you need to set the timezone to specific location, e.g. London, rather than using the default UTC offset. When I used the London timezone the problem went away. Configure this in WordPress at Settings > General /wp-admin/options-general.php


4

it seems php 7.2 is not compatible with 5.x.x or i do not know how to do the configuration i think downgrade the php would be the best solution but i wonder if there is other answer, and how to do it.... updates: -downgrade php does not help, same error appears -downgrade mysql to 5.5 works!


3

Two options: a. Use the bitnami package: https://bitnami.com/stack/civicrm b. Use a CiviCRM hosting provider: https://civicrm.org/providers/hosting


3

I was able to 'fix' the issue by simply deleting all the groups (I didn't have any previous mailings in here) from the Recipients field and then re-adding them. Make no other edits before the estimated number of recipients finishes, and the draft is saved. I was able to send a test email successfully after that. The mailing list this happened with to me is ~...


3

The event_queue_id points to a row in the event queue table which has a job id associated with it. The mailing job table is a join table which matches a job to a specific mailing. Check the civimail reports to give a better idea of the SQL used to construct the queries and extract useful info from the tables


3

Try the civicrm_mailing_recipients table - mailing_id tells you which Mailing they relate to. Is that what you are after?


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


3

If your page happens to be a report, you can install the Extended Reports extension which will display the full sql query being run.


3

Another solution is to upgrade at least to CiviCRM 4.6.16 or to CiviCRM 4.7.7 or you can also patch your installation. If you are familair with patches you can also download a patch from the civi site. https://github.com/civicrm/civicrm-core/commit/3f7df5902facad68489e225b504a887c2b45eb91.diff and then apply that patch to your civicrm installation.


3

Don't know if this will help, but at least you can try it. :) The custom() hook is called after the db write on a custom table, so when the field is writen, you can't know which was the change, so you can't have the id (or at least I think so). Instead of custom() you can try the pre() hook. Check if the Enjoyment Level has changed (use api to read the ...


3

I ran into a similar problem when manually updating. Depending on how your database is configured the update will fail when trying to change the image_URL from VARCHAR(256) to VARCHAR(512). I wasn't able to figure out how to get it to let me make the change (it would even fail when I tried to directly manipulate the table). I ended up commenting out this ...


3

The user does not have the permissions to alter and create routines (functions). This should fix it: GRANT CREATE ROUTINE, ALTER ROUTINE ON civicrm.* TO civicrm;


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