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
1answer
21 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
26 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
23 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
33 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
40 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
37 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
42 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
22 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
35 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
55 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
28 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
38 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
45 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
32 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
164 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
32 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
51 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
49 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
41 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
52 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
50 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
49 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
135 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
48 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
35 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
232 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
49 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
59 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
660 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
36 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
56 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
27 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
83 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
33 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
123 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
113 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
328 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
102 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
229 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 '...
2
votes
1answer
111 views

Is there a process to tidy the tables created by logging?

I've successfully improved the performance of the 'Change Log' by taking Eileen's advice here, converting 5 tables to InnoDB and adding indexes. In so doing, I almost blew the space limit on the host ...
3
votes
2answers
79 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 ...
2
votes
0answers
127 views

ETA on Support for MySQL 8?

I'm running CiviCRM 5.10.3 on the Bitnami WordPress stack (WP has been upgraded to 5.1), and the version of the Bitnami stakc I'm currently on uses PHP 7.0.30, so I'm getting warnings that as of the ...
3
votes
1answer
34 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 "...
2
votes
2answers
57 views

WordPress installation with MySql connection problem

I've been trying to install the lastest version, 5.9.1, on a new WordPress 5.0.3 site. Requirement checks all passed but when I clicked "Check Requirements and Install CiviCRM" button I got this error:...
1
vote
2answers
110 views

Drush sql-sync and civicrm issue

I wanted to quickly pull all the data (contacts, custom types, etc) from one Drupal CiviCRM install to another. I got a little too clever for myself and used Drush sql-sync to do it. It failed. As I ...