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
0
votes
0answers
4 views

DB Error: unknown error, INSERT INTO civicrm_prevnext_cache

CiviCRM 5.39, MySQL 5.7 Got "DB Error: unknown error, INSERT INTO civicrm_prevnext_cache" with mysql query below. Apparently Incorrect datetime value: '' for column 'receive_date' is the key ...
2
votes
2answers
40 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 ...
0
votes
0answers
26 views

Failed clean installation

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. ...
0
votes
1answer
26 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 ...
0
votes
0answers
11 views

How should I configure default time zones for Wordpress, CiviCRM, PHP, and MySQL?

Wordpress's time zone is set to "New York", PHP is set to "UTF", and MySQL is set to a UK time zone. Is the proper configuration to set each of these to "New York", or ...
2
votes
1answer
26 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)...
1
vote
1answer
25 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 ...
1
vote
1answer
27 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 - ...
1
vote
1answer
26 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 '...
0
votes
0answers
39 views

Slow Query Fixes - MSP, Custom Searches

I ran into the following problematic queries while troubleshooting CiviCRM performance issues. insert into civicrm_prevnext_cache (cachekey, entity_id1, data) SELECT DISTINCT 'civicrm search ...
1
vote
1answer
41 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 ...
1
vote
1answer
38 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 ...
1
vote
0answers
48 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 ...
0
votes
0answers
30 views

Re-Instalation Error

I'm having trouble re-installing CiviCRM 5.31. on Wordpress, hosted on Ubuntu 20. I needed to uninstall an earlier version, and I now want to install the latest version. When I activate the plugin I ...
1
vote
1answer
39 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 ...
0
votes
2answers
76 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 ...
2
votes
0answers
29 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 ...
1
vote
1answer
45 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 ...
0
votes
1answer
72 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\...
2
votes
1answer
36 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,...
4
votes
2answers
179 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 ...
0
votes
1answer
41 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,...
2
votes
1answer
55 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 '...
1
vote
1answer
50 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 ...
2
votes
1answer
45 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 ...
1
vote
0answers
54 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....
2
votes
1answer
51 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 ...
1
vote
1answer
14 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 ...
1
vote
2answers
50 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 ...
0
votes
1answer
170 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 ...
2
votes
2answers
63 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 ...
0
votes
1answer
21 views

Mailings advanced search error - Invalid mysql syntax

I'm on Drupal 7.69 and CiviCRM 5.20.0. As long as I remember I've been experiencing an error when I've tried to look up the bounced recipients of mailings through Advanced Search. I can't look up ...
2
votes
1answer
37 views

What information does log_civicrm_group keep? How is it useful?

Do you use the log_civicrm_group logs? Our log file is now about 300MB - quite crazy. Are records of removal and adding contacts to groups stored here? Thats information we would like to keep. How do ...
2
votes
1answer
248 views

Can't find MySQL server on WordPress install with sockets

I am using Local by Flywheel which connects to MySQL with sockets. My socket is: /Users/efc/Library/Application Support/Local/run/Qv8NIbiue/mysqld.sock The MySQL server is localhost, database is ...
3
votes
0answers
53 views

CiviCRM with Master - Slave Replication

I'm interested to get some latest war stories on CiviCRM with Master - Slave replication. Does it work well or are there any known issues? I've seen that Master - Master has some work needed but most ...
1
vote
1answer
66 views

CIVICRM with MYSQL encrypted on ecryptfs

I managed to have CIVICRM (5.15.2 on WP 5.0.7 on Ubuntu 18) running on MySQL (5.7.28) from an encrypted mount via ecryptfs. Currently only the mysql files are in the encrypted mount and it's running ...
2
votes
1answer
873 views

nativecode=1260 ** Row 60 was cut by GROUP_CONCAT()

After an upgrade from 5.7.x to 5.13.x, a standard contribution/detail report started failing. In the CiviCRM admin log, we see this sql error: nativecode=1260 ** Row 60 was cut by GROUP_CONCAT() ...
1
vote
2answers
38 views

Unsure if I just broke my database with a custom field

I added a custom field for authenticated users to add a bio on their profile page (and if there's an existing field I should use for this, please let me know). Not knowing how long I should make this ...
3
votes
2answers
62 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 = ...
2
votes
1answer
59 views

CiviCRM on Debian 10 gives warning about PHP MySQL Driver (libmysqlclient)

Recently moved my CiviCRM install to a server running Debain 10 which brings: php7.3 MariaDB 10.3.17 Everything seems to be working fine, except that in the system checks I'm now seeing this error/...
3
votes
1answer
29 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
2answers
94 views

mysql_dsn error on amp config - why am I getting "access denied"?

I'm trying to install buildkit on my Ubuntu 19.04 laptop, per the instructions at https://docs.civicrm.org/dev/en/latest/tools/buildkit/#config I've used curl to run the script, successfully - ...
2
votes
1answer
38 views

Everything In CRM is Slow - Errors With CRM_Contact_BAO_GroupContactCache

After a database was corrupted and reloaded from a backup. Looking at the error log this appears to be where CiviCRM is hanging. CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO)) Here ...
5
votes
1answer
43 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?
1
vote
2answers
37 views

Unable to enable CiviCase [duplicate]

When I try to enable CiviCase I get a white screen of death and the following msg: "A fatal error was triggered: Could not create the MySQL views for CiviCase. Your mysql user needs to have the '...
3
votes
1answer
127 views

SSL-encypted Database connection for CiviCRM?

I am in the middle of deploying a CiviCRM instance and have decided for scalability and security reasons to separate my web server and database server. I have a set of self-signed certificates ...
2
votes
2answers
116 views

Does civicrm support mysql sockets?

My wordpress is installed and uses localhost:/run/mysqld/mysqld/socket but civicrm only works if I point to localhost Is there a way to install civicrm through file socket ?
6
votes
1answer
334 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 ...
3
votes
2answers
111 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 ...
2
votes
1answer
259 views

Error: more than 'max_user_connections' active connections

Several times a day on our Civi site (CiviCRM 5.11.0 with Drupal 7.64) we are experiencing the following error: PDOException: SQLSTATE[HY000] [1203] User xxx already has more than '...