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
1 answer
25 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 ...
user avatar
  • 3,122
0 votes
1 answer
31 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 &...
user avatar
0 votes
1 answer
15 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. ...
user avatar
1 vote
1 answer
18 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?
user avatar
  • 1,350
0 votes
0 answers
15 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 ...
user avatar
  • 1,350
2 votes
2 answers
42 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 ...
user avatar
0 votes
0 answers
56 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. ...
user avatar
0 votes
1 answer
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 ...
user avatar
  • 363
0 votes
0 answers
15 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 ...
user avatar
  • 355
2 votes
1 answer
37 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)...
user avatar
  • 1,640
1 vote
1 answer
33 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 ...
user avatar
1 vote
1 answer
43 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 - ...
user avatar
  • 1,887
1 vote
1 answer
30 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 '...
user avatar
1 vote
1 answer
42 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 ...
user avatar
  • 403
1 vote
1 answer
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 ...
user avatar
1 vote
0 answers
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 ...
user avatar
1 vote
1 answer
46 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 ...
user avatar
0 votes
2 answers
99 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 ...
user avatar
2 votes
0 answers
31 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 ...
user avatar
  • 21
1 vote
1 answer
52 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 ...
user avatar
0 votes
1 answer
97 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\...
user avatar
2 votes
1 answer
44 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,...
user avatar
4 votes
2 answers
203 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 ...
user avatar
  • 403
0 votes
1 answer
52 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,...
user avatar
  • 867
2 votes
1 answer
58 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 '...
user avatar
  • 867
1 vote
1 answer
63 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 ...
user avatar
  • 11
2 votes
1 answer
49 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 ...
user avatar
1 vote
0 answers
56 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....
user avatar
2 votes
1 answer
56 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 ...
user avatar
  • 23
1 vote
1 answer
17 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 ...
user avatar
1 vote
2 answers
53 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 ...
user avatar
0 votes
1 answer
204 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 ...
user avatar
2 votes
2 answers
74 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 ...
user avatar
  • 407
0 votes
1 answer
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 ...
user avatar
  • 95
2 votes
1 answer
39 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 ...
user avatar
  • 3,122
2 votes
1 answer
257 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 ...
user avatar
  • 121
3 votes
0 answers
57 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 ...
user avatar
1 vote
1 answer
71 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 ...
user avatar
2 votes
1 answer
1k 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() ...
user avatar
  • 5,994
1 vote
2 answers
40 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 ...
user avatar
3 votes
2 answers
67 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 = ...
user avatar
  • 311
2 votes
1 answer
63 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/...
user avatar
  • 3,688
3 votes
1 answer
30 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-...
user avatar
3 votes
2 answers
108 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 - ...
user avatar
2 votes
1 answer
41 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 ...
user avatar
  • 783
5 votes
1 answer
44 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?
user avatar
  • 71
1 vote
2 answers
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 '...
user avatar
3 votes
1 answer
140 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 ...
user avatar
  • 33
2 votes
2 answers
121 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 ?
user avatar
6 votes
1 answer
343 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 ...
user avatar
  • 1,012