Hot answers tagged

13 votes
Accepted

MyISAM database but all civicrm_ tables are INNODB... is this a problem?

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 ...
user avatar
10 votes
Accepted

What is the easiest way to see the MySQL queries run by a CiviCRM page?

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 ...
user avatar
  • 3,148
9 votes

What is the easiest way to see the MySQL queries run by a CiviCRM page?

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 ...
user avatar
  • 3,803
9 votes

MySQL Tuning: Is Defragmenting Tables safe?

Yes, running OPTIMIZE TABLES on a regular basis is beneficial. I don't 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 ...
user avatar
  • 3,803
8 votes
Accepted

Error during Drupal installation: MySQL support not included in PHP

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 ...
user avatar
7 votes
Accepted

MySQL Tuning: Is Defragmenting Tables safe?

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. (...
user avatar
  • 6,671
7 votes

MyISAM database but all civicrm_ tables are INNODB... is this a problem?

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

ER diagram for the latest Civicrm , Foreign key in Structure, test cases/plans

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 ...
user avatar
  • 3,148
7 votes

When will MySQL utf8mb4 support be mandatory?

It seems like it's still under discussion as of last week. See e.g. https://lab.civicrm.org/dev/core/issues/979
user avatar
  • 18.4k
6 votes

timestamp mismatch

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 ...
user avatar
5 votes

How can i match CiviCRM ID on two installations?

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 ...
user avatar
  • 2,808
5 votes
Accepted

How do I correct mismatched Timestamps for MySQL and PHP without access to server?

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.
user avatar
  • 7,357
5 votes

Civi Database erased when upgrading from 4.6.3 to 4.6.4

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....
user avatar
5 votes

Need help solving unserialize():Error at offset

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 ...
user avatar
  • 5,589
5 votes

Update DB Query fails sporadically when saving contact; nativecode=1205 ** Lock wait timeout exceeded

We have seen this on large instances. Things to do: upgrade to 5.13.5 (this also puts you close to the next ESR - a paid for service - which will be released on Nov20). There have been performance ...
user avatar
5 votes

Possible Bug? Foreign key constraint error on upgrade to 5.31.0 (populating civicrm_relationship_cache)

Further to Pradeep Nayak's reply, the ultimate cause of the problems I was having were missing foreign key constraints on the civicrm_relationship table leaving orphaned data when contacts were ...
user avatar
  • 403
4 votes

I need to migrate data from one CiviCRM on Drupal instance to a clean one. HELP?

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:...
user avatar
4 votes

INSTALLER fails. Database can't "SELECT GET_LOCK" at civicrm/install/index.php(271)

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: ...
user avatar
  • 51
4 votes
Accepted

non-asci characters ending up in database

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 ...
user avatar
  • 18.4k
4 votes
Accepted

ER diagram for the latest Civicrm , Foreign key in Structure, test cases/plans

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 ...
user avatar
  • 2,311
4 votes
Accepted

What character is VALUE_SEPARATOR?

In fact, if you use HEX() function, you can guess it's the ASCII code 01 which is not really readable : MySQL> SELECT substring(besoins_sp__114, 1,1) FROM ...
user avatar
  • 2,311
4 votes

My hosting company does not support SUPER privilege - can I use CiviCRM with them?

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, ...
user avatar
4 votes
Accepted

SQL Syntax Error - When starting civicrm with joomla

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....
user avatar
4 votes

Update DB Query fails sporadically when saving contact; nativecode=1205 ** Lock wait timeout exceeded

Karin's answer is a good one. However, not all smart groups are created equal! Culling the right smart groups is what will fix your deadlocks. Identify your slow smart groups. Here's a script (...
user avatar
4 votes
Accepted

Possible Bug? Foreign key constraint error on upgrade to 5.31.0 (populating civicrm_relationship_cache)

Your system have orphan data in civicrm_relationship table. There are few rows in civicrm_relationship table that have contact id in column civicrm_relationship.contact_id_a or ...
user avatar
4 votes
Accepted

One database table very large

Thats from Civimobile extension and You can find quick solution at https://lab.civicrm.org/extensions/civimobileapi/-/issues/57#note_54817 One thing I have found is editing the scheduled job ...
user avatar
3 votes

I need to migrate data from one CiviCRM on Drupal instance to a clean one. HELP?

If you wish to transfer the content of the database a) export the existing data. You can do this as an action from the search/find screens. Export contacts, activities, memberships, contributions as ...
user avatar
3 votes

is there an easy local install method for civics and all dependent software

Two options: a. Use the bitnami package: https://bitnami.com/stack/civicrm b. Use a CiviCRM hosting provider: https://civicrm.org/providers/hosting
user avatar
  • 3,803
3 votes

CiviMail recipient count stuck on Estimating

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 ...
user avatar
  • 388
3 votes

How do I find the mails belonging to a particular mailing by looking at the SQL tables?

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 ...
user avatar
  • 3,803

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