Skip to main content

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
1 vote
0 answers
3 views

"MySQL Timezone Problem" Status alert: what test is run to display this alert?

I look after a number of CiviCRM implementations. Some of them display the System Status alert about "MySQL Timezone Problem", and advise that timezone data needs to be loaded. However when ...
Graham's user avatar
  • 4,075
1 vote
1 answer
45 views

Looking for Experienced & Knowledgeable CIVICRM Freelancer

Looking for referrals for a knowledgeable, experienced CIVICRM expert (not a consulting company) that freelances and that I could keep on retainer to assist with CIVICRM technical and application ...
Margaret's user avatar
  • 121
0 votes
0 answers
25 views

Bad performance on CiviEvent Dashboard

We have a site that is fairly performant, around a second for most CiviCRM actions. But the CiviEvent Dashboard is extremly slow, around 12 seconds. Over the years we have hosted a number of events ...
thoni56's user avatar
  • 1,241
1 vote
1 answer
18 views

SQL error when installing Joomla add-on

I am trying to install the add-on for Joomla, but I get the following error message: Cannot execute CREATE TABLE `civicrm_extension` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Local ...
TMRMT's user avatar
  • 11
0 votes
0 answers
20 views

Retrying after Database deadlock encountered hit on attempt 1 at query : INSERT INTO civicrm_mailing_recipients?

Drupal 10.2.3 , CiviCRM 5.70.0, PHP 8.2.13, MySQL: 10.6.16-MariaDB-0ubuntu0.22.04.1, nginx/1.18.0 Got the below in logs after almost each contribution (contributions status="Completed" ) : ...
yurg's user avatar
  • 2,053
2 votes
1 answer
110 views

Storage engine MEMORY is disabled with digitalocean managed database

I have this error: Possibly unhandled rejection: {"error_code":-1,"sql":"CREATE TEMPORARY TABLE civicrm_tmp_e_exrecipient_dc8f2500b15714f5703ad66bc5a8bb37 (contact_id int ...
Robert Garrigos's user avatar
2 votes
1 answer
98 views

Database Upgrade from 5.65.0 to 5.67.0 throws WSOD and kills MySQL

On my local lando dev environment, I proceeded to composer update to get onto the latest stable/recommended Drupal 10.1.6 and Civi 5.67.0. Post-upgrade state: CiviCRM UI seems to work fine CiviCRM ...
Justin657's user avatar
  • 365
0 votes
0 answers
39 views

MySQL ERROR 3161 at Change Log on Contact pages after db migration

After db migration, Change Log tab on Contact pages doesn't load due to error about creating temporary tables using memory engine. I could recreate the error with the following query: mysql> CREATE ...
kenorb's user avatar
  • 131
1 vote
1 answer
35 views

The mysql commands you need to run are stored in

Have got this after the most recent CiviCRM 5.58.1. update on Drupal 7 ( same udate went fine on WP sites ). ConfigAndLog/CiviCRM.trigger63f7a62151e54WPFX4db1365ff9d7f93fb47a7eb13184cce6.sql contains, ...
yurg's user avatar
  • 2,053
0 votes
1 answer
26 views

MySQL OptionGroup values, retrieve in SELECT statement

Using Drupal 8.9.20 and CiviCRM 5.39.1 (on Ubuntu 18.04 LTS). I need to write a query in MySQL to retrieve the values of an optiongroup. The OptionGroup is used to assign keywords to a contact record ...
ArisK's user avatar
  • 123
1 vote
1 answer
261 views

What issues are there when running with a database that "does not fully support timezones"

I recently installed CiviCRM 5.58.0 on wordpress, but under system status I am getting a warning that says: The MySQL database does not fully support timezones. Please ask the database administrator ...
RobotToaster's user avatar
0 votes
1 answer
62 views

DB Error: unknown error when trying to send mosaico civimail

For a recently updated CiviCRM 5.56.2 + Drupal 7 (using mariadb 10.3), I got the little black box with the message "DB Error: unknown error" popping up when trying to send a mosaico civimail ...
Alan Dixon's user avatar
  • 6,861
1 vote
0 answers
35 views

Cannot Add Contributions for Some Contacts

I have had and solved an error before where MySQL reports "Can't update table 'civicrm_contact' in stored function/trigger because it is already used by statement which invoked this stored ...
Drupal Learner's user avatar
0 votes
2 answers
453 views

Help with mariadb crash, out of memory error

I'm trying to track down the cause of an occasional mariadb crash on my server. The system is a Linode 4GB Ubuntu 22.04 instance running Wordpress 6.1.1, apache2 2.4.52, mariadb 10.6.11, and civicrm 5....
twowheeler's user avatar
1 vote
2 answers
57 views

Civicrm on Wordpress installer table doesnt exist

Using Ampp version 4.3 with Apache v2.4.54, MySQL v8.0.31, php version 7.4.32, Windows Every time I run the CiviCrm installer I get an error "Table 'civicrm.civicrm_worldregion' doesn't exist in ....
SaKa's user avatar
  • 11
1 vote
2 answers
60 views

Php Standalone Skript doesnt Query Database, APIv4 Simple Example

I tried to write a Standalone PHP-Skript for CiviCRM and looked after the Simple Example, but unfortunately it doesnt return something. <?php require_once '/var/www/html/wp-content/plugins/civicrm/...
gzwg's user avatar
  • 37
3 votes
2 answers
56 views

Dangerous to force a binary collation on civicrm_contact.external_identifier?

We're trying to get External Identifier working for an external system that uses case-sensitive IDs (such that external contact "abc123" John Smith is distinct from external contact "...
TwoMice's user avatar
  • 1,824
0 votes
2 answers
324 views

Issue moving site from mysql 5.7 to mysql 8

I was just trying to move three old CiviCRM drupal 7 sites from Ubuntu 16, PHP7.2 and mysql 5.7 Ubnuntu 22.04 lts, php 7.4 and mysql 8 and have run into some issues. two of the three sites work but ...
tanbog's user avatar
  • 317
2 votes
2 answers
269 views

MariaDB "underlying table doesn't have a default value" on event creation

I'm using CiviCRM 5.50.4 on Drupal 7 with MariaDB. The NGO I volunteer for uses French language. When a user creates an event, we have the following error (debugging mode): UserInfo INSERT INTO `...
Romain Dub's user avatar
0 votes
1 answer
54 views

Possible Deleted Contact - Trying to Restore Records - Possible SQL Solution?

So here is the issue - that I think was caused by a merger of contacts without transferring the related fields to the new contact. I have a contact, that seems to be missing a large number of ...
themak's user avatar
  • 3,441
0 votes
1 answer
124 views

CiviMail issue - "DB Error: no such table" when sending mailing

Basic Problem When attempting to send a basic traditional mailing (though it also occurs in Mosaico), attempting to send a test email to a single address or a group fails, showing an error that says &...
SunriseGV's user avatar
0 votes
1 answer
75 views

Error with charset on MySQL

Trying to modify a label for a relationship I got this error #1253 - COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8' But the structure seems to be correct and not using utf8mb4_bin. ...
Guillaume Sorel's user avatar
1 vote
1 answer
22 views

Logs in a separate database: would CiviCRM hangs if Logs database got corrupted?

Well, subject has pretty much all of the question. Anyone with firsthand experience please?
yurg's user avatar
  • 2,053
2 votes
2 answers
57 views

Move data from field to another field as a batch

I recently have taken over managing CiviCRM for an orchestra in Holland. Some fields have been used inconsistently, for example Nickname and First Name. I now need to copy all data in Nickname to ...
Dirkjan from AJO Amersfoort's user avatar
0 votes
0 answers
104 views

Failed clean installation (WP and Cloudways)

I have read related topics but could not find any real help there, except that the same error message was issued on other occasions. I'm trying to install 5.43.2 on WP on a cloudways brand new site. ...
Claes Rickeby's user avatar
0 votes
1 answer
51 views

How can I update all my external-IDs?

My client's outside database has changed. I have spent a few hours (fun!) matching the old and new external IDs and the internal CiviCRM contact ID as well. Not every record in the CiviCRM database ...
TimETown's user avatar
  • 373
2 votes
2 answers
62 views

How is Civi::lockManager supposed to be used?

I'm confused about how Civi::lockManager is supposed to be used in handling sql-level locks on certain processes. I'm looking at a few existing code usages (as in Omnipay here and in civicrm core here)...
TwoMice's user avatar
  • 1,824
1 vote
2 answers
84 views

Civicrm separate database login syntax

I am installing Civi on localhhost. I want to set up a database separate to Wordpress. I am using these credentials: mysql://root:HIDDEN@localhost/cividata. The database username is root, without a ...
Phillip Money's user avatar
1 vote
1 answer
264 views

SQL too many joins limitations- what will break and how

From experience I know that once a database reaches a certain size the sql limitation of 61 joins can cause problems I've seen it raised in stack exchang here:Too many tables - MySQL limitation - ...
Craig Almond's user avatar
  • 2,037
1 vote
1 answer
44 views

sql queries using too many resources

My host has said one of my sites is using too many sql minutes. I have a list of sql queries (20 in total) which are using the most system resources. The information starts '...
Caroline B's user avatar
1 vote
1 answer
45 views

Illegal text in MySQL queries

I have a CiviCRM install on Windows shared server operated by a client. The installation was originally set up on a dedicated Ubuntu Linux virtual server, but was transferred to the Windows server at ...
fred2's user avatar
  • 403
1 vote
1 answer
51 views

Change Log Tab Excrutiatingly Slow - Poorly Performing Query and Fix (from 8 minutes down to 4 seconds)

The page takes about 8 minutes to return which is absurdly slow for anything expected to be remotely interactive. It is so slow that my client is referring to it as the "Triangle of Doom". I ...
Gordan Bobić's user avatar
1 vote
0 answers
59 views

Poorly performing unindexed query on log tables

While investigating a CiviCRM performance issue, I came across this query: SELECT * FROM `civicrm`.`log_civicrm_activity_contact` WHERE log_conn_id = ? AND id = ? ORDER BY log_date DESC limit ...
Gordan Bobić's user avatar
1 vote
1 answer
58 views

One database table very large

Database table Table: civicrm_contact_push_notification_messages Is huge. 51GB to be precise. The main field is %display_name has created activity. Is this the table that shows up what emails have ...
Caroline B's user avatar
0 votes
2 answers
276 views

Trapped by MySQL 5.6

HostGator has frozen their shared platforms at MySQL 5.6 with no plans to offer MySQL 5.7 to the shared packages, even though 5.6 went EOL on February 5th, 2021. They're pushing people to use their ...
Geof Newton's user avatar
2 votes
0 answers
36 views

Suddenly can't backup mysql

After years of no issues with MySQL backups we are suddenly getting these errors. I can't sort out any way to delete the tables. There shouldn't be any permissions issues. MySQL 5.7. Error: Couldn't ...
Andrew's user avatar
  • 21
1 vote
1 answer
85 views

Troubleshooting SELECT Privileges Issue with Drupal 9 Views Integration

I'm having an almost identical problem to this person (https://www.drupal.org/project/civicrm_entity/issues/3198146) so I'm hoping to get an answer for both of us. It looks like this may also be the ...
Austin Zumbro's user avatar
0 votes
1 answer
163 views

Installer fails with Cannot execute CREATE TABLE civicrm_acl_cache

First, thanks to anyone who takes a look at an issue I can't get to the bottom of regarding a Civi 5.33.2 install. The issue: When running the installer I get the following error: Civi\Setup\...
carclubadmin's user avatar
2 votes
1 answer
62 views

CiviCRM Smart Group Cache SQL Collation Error

Running CiviCRM 5.32.2 on Drupal 7, PHP 7.3, MySQL 8, Apache2, Ubuntu Server 20.04. This instance of CiviCRM has just been migrated from another server and the migration has largely gone well. However,...
Rich Smith's user avatar
4 votes
2 answers
265 views

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

I've actually already diagnosed and worked around this problem for myself in a fairly large CiviCRM database within a Wordpress install, but thought I'd refer to those more expert here as to whether ...
fred2's user avatar
  • 403
0 votes
1 answer
66 views

utfmb4 conversion gone wrong - Some columns in the databse have utf8mb4_general_ci others utf8mb4_unicode_ci causing and Illegal mix of collations

I've recently run utf8mb conversion (through API explorer) on civi 5.28. Everything seemed OK, but now whenever I try to access events dashboard I get Illegal mix of collations (utf8mb4_general_ci,...
Marko's user avatar
  • 877
2 votes
1 answer
66 views

DB error for sort_name when adding contacts with long names in 2 bytes UTF8 characters

The problem occurs when I try to add a contact (in the example it's an organization) with long name in Cyrillic (multiple words in it's name). I get the nativecode=1406 ** Data too long for column '...
Marko's user avatar
  • 877
1 vote
1 answer
148 views

DB Error: unknown error when sending CiviMail

I recently upgraded my WordPress CiviCRM from 5.25 to 5.28 and in doing so needed to migrate the SQL old 5.5 database to 5.7. The upgrades went fine, but I now have an error when trying to send group ...
Alex's user avatar
  • 11
2 votes
1 answer
63 views

Upgrade MySQL to v5.7+

I have a message in my installation as follows: "To prepare for CiviCRM v5.34+, please upgrade MySQL. The recommended version will be MySQL v5.7+ or MariaDB v10.1+." My site is hosted on ...
Phillip Money's user avatar
1 vote
1 answer
96 views

I am suddenly receiving the message, "Your database is configured to use the MyISAM database engine

I am suddenly receiving the message, "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 InnoDB....
Doug Thomson's user avatar
2 votes
1 answer
67 views

New Install "MYSql Server Has Gone Away"

Good evening, I'm new here and trying to get CiviCRM installed for the first time (volunteer sysadmin for a non-profit). I think I have gone through all of the steps to troubleshoot but I suspect I am ...
DEVIANT's user avatar
  • 23
1 vote
1 answer
28 views

How to update preferred language depending on a custom field in imported contacts?

I have imported hundreds of contacts having a custom field called "Langue" in set "ocsr" and having two possible values, 'F' and 'N'. I want to transfer this information into the field preferred ...
Pierre François's user avatar
1 vote
2 answers
70 views

Baffled by Views database error on new install

Hours-old install of CiviCRM 5.25 under Backdrop 1.9. I've gotten as far as putting in the Board of Directors with dummy bio information, which I was trying to use to generate a View with a page ...
Jeff Porten's user avatar
0 votes
1 answer
268 views

I get an error "One of parameters (value: sort_name) is not of the type MysqlColumnNameOrAlias" when trying to access most of my data

Okay, I'm new at this - honestly, probably not skilled enough to be doing this, but my organization has no money, so, well, I'm it. I installed on WordPress last year, and everything generally went ...
Ashley Morton's user avatar
2 votes
2 answers
145 views

how to drop all civicrm tables using drush?

Civi is using it's own (not shared with Drupal) database. During a script to rebuild a dev/test site from production data I want to drop all the tables in the civi dev db. How do I do that? After ...
alec's user avatar
  • 407