Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

15
votes
2answers
626 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
1k 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 ...
10
votes
1answer
240 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 ...
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 ...
6
votes
2answers
169 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 "...
6
votes
1answer
43 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
5answers
571 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
283 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
284 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
413 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
146 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
410 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
3answers
216 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 ...
4
votes
2answers
287 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
1answer
57 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 ...
4
votes
2answers
101 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 ...
4
votes
2answers
51 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
207 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
136 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
206 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
5answers
481 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
2answers
145 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
2answers
34 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
1answer
183 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
1answer
45 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
135 views

Drupal - MySQL support not included in PHP

I am getting the following error on install of CiviCRM: PHP Configuration MySQL support MySQL support not included in PHP. I am running: CENTOS 6.8 Apache 2.4.18 Drupal 7.54 CiviCRM 4.7.16 ...
3
votes
1answer
313 views

Should I change mysql.connect_timeout variable in php.ini?

MySQL has been losing connection on me while trying to complete the upgrade. I am wondering if I should change the php.ini variable mysql.connect_timeout = 60 to a value of -1 (unlimited) and if this ...
3
votes
1answer
392 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
2answers
390 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
1answer
231 views

Transfer Database to new location raised MySQL Error function already exists

I follow the instructions here https://wiki.civicrm.org/confluence/display/CRMDOC/Moving+the+database+using+phpMyAdmin I want to import the so called Old_without_five_tables.sql with phpmyadmin in ...
3
votes
3answers
148 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
89 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
15 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
601 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
60 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
101 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
112 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
33 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
2answers
47 views

What could trigger a transaction rollback on contact save?

I have a few contacts in CiviCRM 4.7.27 for which changes in the "edit contact" form are not saving. The update queries are being sent to MySQL correctly, but they're in a transaction which is being ...
3
votes
0answers
162 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
54 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
92 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
60 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
553 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 ...
2
votes
4answers
366 views

How to check if DB table exists?

Is there a built-in way to check to see if a DB table exists via PHP? I'd like to add that to my API call and create the table if necessary. Thanks!
2
votes
4answers
76 views

Best way to integrate two sites with different security levels

We have a civi install that contains some sensitive data. We also have a public facing website. We would like to integrate them so that you can sign up for events, give donations etc on the website. ...