Questions tagged [mysql]

The relational data management system that is used by CiviCRM. It is open source just as CiviCRM and the only database that is supported (Although the drop in replacement MariaDB works also fine). Minimal 5.1.x with INNODB support.

Filter by
Sorted by
Tagged with
15
votes
2answers
651 views

MySQL Tuning: Is Defragmenting Tables safe?

I like using MySQLTuner to check on things from time to time. It's as easy as: $: sudo apt-get install mysqltuner (CentOS: sudo yum install mysqltuner) $: sudo mysqltuner Also, besides 'top' ...
11
votes
4answers
2k views

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

When debugging CiviCRM pages, is there a native way to emit the SQL statements being run, eg on a Search page? I'm looking for something easier than finding the code for the page and putting in a ...
10
votes
4answers
1k views

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

I just upgraded a large site to 4.7 (without any issues in the upgrade process by the way). The new "System Status" admin screen gives a warning as follows: "Your database is configured to use the ...
9
votes
4answers
1k views

Error during Drupal installation: MySQL support not included in PHP

I am getting an error as show by the image above when attempting to install CiviCRM on drupal. What does this error message mean and how could I go about fixing it? The entire configuration report ...
9
votes
1answer
248 views

CiviCRM still creates MyISAM tables which results in Warning

CiviCRM 4.7.10 --- WordPress 4.6 The following warning message appeared in CiviCRM System Status: Your database is configured to use the MyISAM database engine. CiviCRM requires InnoDB. You will ...
6
votes
2answers
171 views

CiviMail recipient count stuck on Estimating

I'm unable to send and/or produce test emails for large mailings. This seems to be a result of CiviMail not completing the recipient estimation process. Attempting a test email while it's stuck on "...
5
votes
1answer
177 views

When will MySQL utf8mb4 support be mandatory?

With recent upgrade, CiviCRM showed me this message Future versions of CiviCRM may require MySQL utf8mb4 support. It is recommended, though not yet required, to configure your MySQL server for ...
5
votes
5answers
613 views

Timestamp mismatch between MySQL and PHP

Every time I log in to CiviCRM I get the following error message: Timestamps reported by MySQL (eg "2015-11-06 13:44") and PHP (eg "2015-11-06 11:44" ) are mismatched. I am using CiviCRM 4.6.10 ...
5
votes
1answer
288 views

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

I'm using civiCRM 4.6.2 under Wordpress. The PHP and MySQL timezone settings are reported as incompatible, MySQL as 12:38 (which is the current clock time here in Zurich) and PHP as 10:38. However, ...
5
votes
3answers
319 views

Performance Warning: Missing Indices. Mismatched Index Key

Just upgraded to 4.7.23 and am receiving a Performance Warning: Missing Indices. The warning states: The following tables have an index key with a mismatch in value. Please delete the key ...
5
votes
1answer
476 views

Tweaking my.cnf for performance

Over the last year, with the help of mysqltuner, I've adjusted my.cnf to improve the performance of the 7 CiviCRM installations on my VPS. But in the last few days, as I upgraded my sites to 4.7.10, ...
5
votes
2answers
148 views

Why can't I save search preferences? Specifically 'Autocomplete Results'

On my 4.6.14 system I'd like to change the 'Autocomplete results' to (say) 15, but it won't save. This is in 'Administer/Customise Data and Screens/Search Preferences'. I've can change the 'Smart ...
5
votes
3answers
415 views

MyISAM vs InnoDB - WordPress Installation

We're running CiviCRM 4.6.8 on WordPress 4.3.1. I've been getting the following error message since the last CiviCRM upgrade: Warning Your database is configured to use the MyISAM database engine. ...
5
votes
1answer
45 views

Dropping thousands of records into a batch via SQL

I have imported (tens of) thousands of historic contributions to a site that uses Accounting Batches regularly. They were starting to bog down the batch assignment page so I thought I would simply ...
5
votes
3answers
237 views

Add custom field and populated with array/data from a non-civicrm table

newby here with a quick question about custom fields. Is it possible to set up a custom field that queries a non-civicrm table? Hoping to create an autocomplete dropdown menu that does a SELECT on a ...
5
votes
1answer
29 views

Has anyone mapped the CiviCRM Schema to Classes in SQLAlchemy?

I would like to use Python and SQLalchemy to construct queries and create reports for Civicrm. Has anyone mapped the Classes and Relationships in order to use the ORM capabilities of SQLAlchemy?
4
votes
2answers
371 views

Issues with Migration - Cannot add foreign key constraint

I'm having multiple issues with a CiviCRM 4.7.17 on WordPress 4.9.5 which has encouraged me to make different attempts to migrate the site following the official SysAdmin Guide. The closest I've ...
4
votes
2answers
63 views

how to get an autoincrementing membership id with existing membership numbers

I've imported our membership list with existing membership numbers into CiviCRM. The old membership numbers are currently under external ID. I'd like an autoincrementing membership number. The best ...
4
votes
1answer
226 views

MySQL Master-Master Replication on 2 servers

For our installation in a "Third World Country" we need 2 replicated Master-Master Mysql-Servers: one in a European data center to serve the public website and allow access from mobile devices to ...
4
votes
2answers
122 views

How can I prevent deduping from killing my server?

On a reasonably big data set (e.g. one with 70,000 individual contacts), it's not hard to kill your server with a naive manual de-duplication attempt. For example, if you don't specify a group, that ...
4
votes
1answer
159 views

Suscpicious Database Errors

In my installation I firstly recognized database errors like described in CiviCRM issue CRM-19109: Draft mailings are not saved. In a fresh local installation (Ubuntu 16.04LTS, php 5.6, mysql 5.7.13, ...
3
votes
3answers
243 views

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

Where can I get the ER diagram for the latest CiviCRM? I need it for internal reporting purposes in our organization. I am able to download the Table structure from phpMyAdmin. However, it does not ...
3
votes
2answers
35 views

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

The exact query and issue is below. UPDATE civicrm_contact SET contact_type = 'Individual' , contact_sub_type = NULL , sort_name = 'XXXXXX XXXXXX' , display_name = 'XXXXXX XXXXXX' , first_name = ...
3
votes
5answers
505 views

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

The person who was supposed to be maintaining my CiviCRM on Drupal instance flaked on my a while ago and my site has been sitting vulnerable. Due to hosting changes, I decided to just set up a clean ...
3
votes
1answer
260 views

timestamp mismatch

I have a host running in a different time-zone from where I am operation and unless I set the website time zone to the hosting time zone I get the following error in CiviCRM: Timestamps reported by ...
3
votes
2answers
53 views

Trouble saving changes to a report

EDIT: This question turned out to be, in practical terms, something different than I originally thought I was asking. I am changing the title so that others will find the answer more easily in the ...
3
votes
2answers
178 views

DB Error: unknown error - [debug_info] => INSERT INTO civicrm_contact

it's fresh install of CiviCrm into my Joomla, run with UniserverZ (assume all Apps are in latest version) When I clicked CiviCRM, in Components, there was a DB Error: unknown error In the joomla/...
3
votes
1answer
462 views

Need help solving unserialize():Error at offset

Thank you all for your continued patience with me. Since the past two updates, I keep getting the following error: Notice: unserialize(): Error at offset 0 of 8 bytes in Civi\Core\SettingsBag->...
3
votes
1answer
24 views

Is it 'better' to use mysql_dsn rather than mysql_ram_disk with buildkit and amp config?

I've successfully set up a drupal-clean test build on my Ubuntu 19.04 machine using buildkit, amp config and amp test and chose mysql_ram_disk rather than mysql_dsn. My intent was to have a semi-...
3
votes
1answer
63 views

What character is VALUE_SEPARATOR?

I need to run a MySQL query to split the values in the fee_level field of the civicrm_participant table. I just can't determine what character to use as the delimiter. When I look at VALUE_SEPARATOR ...
3
votes
1answer
65 views

What is required to run CiviCRM on Amazon RDS?

Amazon have scalable MySQL/MariaDB offerings in Amazon RDS for MySQL (and Amazon RDS for MariaDB). CiviCRM uses MySQL functionality extensively, so it would be interesting to see if the two work well ...
3
votes
2answers
481 views

CiviCRM Can't Connect to Databases on Server, but WordPress Can

I've been trying to install CiviCRM on fresh WordPress 4.7.3 installations on my managed server, but I keep having database connection problems. When I try to install CiviCRM 4.7.17, the CiviCRM ...
3
votes
2answers
55 views

How do you separate wordpress and civicrm tables?

When we first installed CiviCRM - the installer was super eager to install using the existing WordPress database. We were just testing out CiviCRM at the time, so it was not a big deal - however we ...
3
votes
2answers
115 views

What are these big MySQL mailing tables for?

Tomorrow, I will again begin the process of updating CiviCRM. I know that as the upgrade process is going through 4.7.8, it upgrades mailing foreign key constraints in civicrm_mailing_event_queue and ...
3
votes
3answers
156 views

save contact with non numeric phone number gives access denied civicm_strip_non_numeric

I recently update civicrm till 4.6.2 till 4.6.4 on drupal 7.38, mariadb 5.5.41. When saving a contact with a non numeric phone number (+32 123456798) I got the error: Database foutcode: execute ...
3
votes
2answers
93 views

Does this view error mean CiviCRM has lost some MySQL DB perms?

The last CiviCRM upgrade I was running, I received a couple of the same DB errors. Sanitized, minimally, the error was as follows: #7 /srv/www/sites/d1.orgname.org/www/sites/all/modules/civicrm/...
3
votes
1answer
21 views

How to set super-user privileges in CiviCRM for MySQL? CiviCRM requesting for multilanguage support

I have properly installed CiviCRM 10.0, PHP version 7.2, in Joomla 3.6.5 on Linux shared hosting. Works perfectly! Now that we have configure the Spanish language in Joomla and in CiviCRM set to "...
3
votes
3answers
880 views

Is 1Gb memory enough for small server?

We have AmazonAWS t2.micro server (free tier). T2. micro has 1 Gb of ram and 1 vCPU. Only CiviCRM (4.7.14) is hosted there along with its Drupal. Currently the database is very small (less than 5000 ...
3
votes
1answer
61 views

Error on CiviCRM Installer Page

Warning: Invalid argument supplied for foreach() in /public_html/wp-content/plugins/civicrm/civicrm/install/index.php on line 769 I'm also receiving the following under PHP Configuration: MySQL ...
3
votes
2answers
59 views

1 hour discrepancy between created_date field in MySQL and CiviCRM report output

I hope I have a strange, but straight forward issue -- there's a 1 hour offset SOMETIMES in the created_date field in civicrm_contact table, and what gets outputted in a CiviCRM report (see the ...
3
votes
1answer
109 views

How do I modify a core CiviCRM file (to change a search order)?

Looks like one of core CiviCRM files (namely part of mySQL query at CRM/Contact/BAO/Query.php) has to be changed in order to achieve "Search results sort by Last Name". What would be the best (I mean, ...
3
votes
1answer
127 views

What is the best way to avoid cartesian product on specific SELECT attributes?

I faced similar issue in some CiviReports where some of the Select attributes applied with Group function like SUM(), got affected due to LEFT JOINs. Let me provide further clarification on basis of ...
3
votes
1answer
23 views

What is the significance of event_queue_id in civicrm_mailing_event_delivered table?

I generally find that the event_queue_id and id values are same for all the tuples in the civicrm_mailing_event_delivered table. Why do we need a separate event_queue_id then? Can two tuples in this ...
3
votes
0answers
38 views

Civivolunteer - Can't upgrade or install

I'm running CiviCRM 5.5.3 on Drupal 7.59. I installed CiviVolunteer a long time ago, but had disabled it, because no one wanted to use it at the time. Now, there is interest in using it. So, I ...
3
votes
0answers
164 views

Wordpress V4.7.31 install - Cannot add or update a child row: a foreign key constraint fails

installation fails with a bunch of the same errors. Summary below: DB_Error object ( [error_message_prefix] => [MODE] => 1 [level] => 1024 [code] => -3 [MESSAGE] => db ERROR: ...
3
votes
0answers
58 views

Activity Tab takes much time to load on civi 4.7.29

The activity tab on the contact summary page takes much time to load and list the activities. Below is the time-consuming query which takes around 20-25 sec each for the contacts having ~50 activity ...
3
votes
0answers
50 views

Too many tables - MySQL limitation - CiviCRM API errors

On CiviCRM 4.6.31 We have seen CiviCRM errors with SQL LEFT JOINs relating to custom data sets. It appears there is a MySQL limitation on how many tables can be joined. This impacts on a number of ...
3
votes
0answers
108 views

MyISAM Database Engine Error [duplicate]

I'm getting the MyISAM Database Engine error: Your database is configured to use the MyISAM database engine. CiviCRM requires InnoDB. You will need to convert any MyISAM tables in your database to ...
3
votes
0answers
65 views

Deleting job log files on database

Is it safe to delete job log files? I currently have 504,742 lines in my civicrm_job_log table, which comes to 223MB.
2
votes
5answers
576 views

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

After complete wipe and install new MYSQL 5.7.9 can't get fresh civi drupal up and running but existing drupal sites work fine. So I'd like to know if others can install on this combination or if a db ...