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.
188
questions
1
vote
1
answer
26
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, ...
- 1,661
0
votes
1
answer
25
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 ...
- 123
1
vote
1
answer
163
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 ...
- 13
0
votes
1
answer
39
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 ...
- 6,614
1
vote
0
answers
34
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 ...
0
votes
2
answers
145
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....
- 742
1
vote
2
answers
43
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 ....
- 11
1
vote
2
answers
50
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/...
- 37
3
votes
2
answers
45
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 "...
- 1,784
0
votes
2
answers
114
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 ...
- 317
2
votes
2
answers
137
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 `...
- 23
0
votes
1
answer
42
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 ...
- 3,312
0
votes
1
answer
110
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 &...
- 21
0
votes
1
answer
35
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. ...
- 2,513
1
vote
1
answer
20
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?
- 1,661
2
votes
2
answers
54
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
0
answers
92
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. ...
- 101
0
votes
1
answer
37
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 ...
- 373
2
votes
2
answers
53
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,784
1
vote
2
answers
64
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 ...
- 171
1
vote
1
answer
157
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 - ...
- 2,037
1
vote
1
answer
38
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
'...
- 799
1
vote
1
answer
44
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 ...
- 403
1
vote
1
answer
46
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 ...
- 223
1
vote
0
answers
56
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 ...
- 223
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 ...
- 799
0
votes
2
answers
187
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 ...
- 70
2
votes
0
answers
32
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 ...
- 21
1
vote
1
answer
72
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
1
answer
137
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\...
- 357
2
votes
1
answer
55
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,...
- 362
4
votes
2
answers
235
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 ...
- 403
0
votes
1
answer
63
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,...
- 867
2
votes
1
answer
65
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 '...
- 867
1
vote
1
answer
111
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 ...
- 11
2
votes
1
answer
55
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 ...
- 171
1
vote
1
answer
84
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....
- 21
2
votes
1
answer
62
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 ...
- 23
1
vote
1
answer
22
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 ...
- 263
1
vote
2
answers
62
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 ...
- 227
0
votes
1
answer
246
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
2
answers
114
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 ...
- 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 ...
- 95
2
votes
1
answer
42
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 ...
- 3,312
2
votes
1
answer
332
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 ...
- 121
3
votes
0
answers
59
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
1
answer
88
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 ...
- 655
2
votes
1
answer
2k
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()
...
- 6,614
1
vote
2
answers
45
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 ...
- 227
3
votes
2
answers
89
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 = ...
- 311