Questions tagged [sql]
The sql tag has no usage guidance.
44
questions
1
vote
1
answer
103
views
Issues with CiviCRM Tables During MySQL 8.0 Upgrade
During the upgrade to MySQL 8.0, I encountered three different warnings related to CiviCRM tables and I want to take the correct steps in this regard. These warnings are listed below:
Usage of db ...
0
votes
1
answer
19
views
Transfer a D7 view sql query to a D9 view
We are converting a number of D7 views (for reports) to D9 version. Is it possible to copy the sql query from a D7 view to use in a D9 view. The fields in Civi are the same.
2
votes
1
answer
58
views
How to get "Last contribution amount" and "Last contribution date" into report?
I'm needing a way to add 2 columns to contribution summary report, 1 for displaying "last contribution date" and 1 for "last contribution amount" in a CiviReport. Is there an easy ...
1
vote
0
answers
31
views
Search Builder: Using "in" operator on a custom multi value field giving unexpected results
we encountered a problem when building searches using the "in" operator.
While this should work as if making multiple where statements connected with or, we noticed that on one of our custom ...
1
vote
2
answers
92
views
How to split CiviCRM multiple database values with SQL?
The CiviCRM database stores multiple values as a varchar field (a string with separator characters). For example, the IDs 3, 9 and 15 are internally saved as #3#9#15# (where # is in fact the character ...
1
vote
0
answers
86
views
CiviCRM 5.31.0 Wordpress unable to change default language
I currently have CiviCRM 5.31.0 for Wordpress installed. I want to change the default language from EN_US to Dutch (NL). After a quick google i found a tutorial and did the following steps:
...
0
votes
1
answer
20
views
Why is this Activity Search failing?
Activity Search is failing every time for this install (Search/Find Activities) no matter what search parameters are used. The problem is the following SQL error:
SELECT count( DISTINCT ( ...
1
vote
2
answers
37
views
Constraint violation when viewing Mailing Detail Report (group_id the cause)
CiviCRM version: 5.19.4
CMS: Drupal 7.69
PHP version: 7.1
When trying to view the Mailing Details Report page, I get the following error:
DB Error: Constraint violation
Looking in the error logs,...
2
votes
0
answers
57
views
Updating Contribution payment instrument based on Financial Trxn payment instrument when there are multiple transactions
I have a task where I need to clean up payment instrument data. The basic problem was caused by a custom payment instrument of TBD, which is somehow preventing contributions from updating correctly, ...
3
votes
2
answers
134
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 ...
3
votes
2
answers
181
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
2
answers
99
views
Custom field default values at database level, not form level?
When you set a default value for a custom field, it only exists at the form level. Is there a practical way to enforce them at the database level? For instance, by altering the SQL table definition?
...
4
votes
1
answer
75
views
Ran direct SQL update to bulk update contact names, changes not appearing until editing and saving
I'm finishing up a migration to CiviCRM from a homegrown Python application I wrote, and without going into too much detail as to the why, I needed to do a bulk update of all the contact name fields ...
4
votes
2
answers
124
views
how to get an autoincrementing membership id with existing membership numbers
I've imported our membership list with existing membership numbers into CiviCRM.
The old membership numbers are currently under external ID. I'd like an autoincrementing membership number.
The best ...
2
votes
1
answer
43
views
Fatal DB query syntax error on rules search - Civi 5.0.0 and recent Civirules
I have 7 rules set up to help automate membership management. All are tagged with the same tag and all but one are active. WHen I search by tag or for all rules I get the correct result, but when I ...
2
votes
2
answers
69
views
What could trigger a transaction rollback on contact save?
I have a few contacts in CiviCRM 4.7.27 for which changes in the "edit contact" form are not saving. The update queries are being sent to MySQL correctly, but they're in a transaction which is being ...
1
vote
1
answer
188
views
SQL Syntax Error - When starting civicrm with joomla
Please help, i have no idea what is wrong, all is fine until i open civicrm with joomla, it displays sql syntax error, then when I try to look at my db functions with phpmyadmin there will be a whole ...
1
vote
2
answers
181
views
Bulk delete of events - is such a thing possible?
We've had a bit of a mix up and imported the same set of events twice. Most if not all of the duplicates have no participants (but I'm not 100% certain of that)
Is there a way to bulk delete these ...
0
votes
1
answer
49
views
Fixing null sort and display names
In one of the systems we support there are some contacts with NULL display and sort names. This means they don't show up in searches. How do we fix these?
1
vote
0
answers
25
views
Donation summary report (Repeat) gives error Incorrect integer value: 'text string' for column 'source' at row 1
Drupal 7, CiviCRM 4.7.27, using the standard report "Donation summary report (Repeat)", I'm getting an unexpected SQL error
Incorrect integer value: 'my custom source string' for column 'source' at ...
0
votes
1
answer
36
views
How to update to new SQL database username and password?
We recently received a site dump from the previous host, inclusive of the Civi CRM SQL database. When creating a new account, I had to create a new database Username and Password. Does this need to ...
1
vote
1
answer
95
views
Make phone type value "mobile" for every individual
We need to switch every individual contact's phone type to mobile. I have an SQL query that I could run manually, but for the civicrm_phone_before_update trigger using a function that doesn't get ...
0
votes
1
answer
89
views
Debugging SQL - possible database problem
This is possibly a question with much too deep technical aspects (SQL, PHP and Civi-innards...) for this forum, but I don't know where else to start, so here goes.
I am experiencing mysterious ...
3
votes
1
answer
79
views
Query to get a list of contacts not tagged with a tag, but the user has multiple other tags
I'm building a View that lists contacts. The View needs to list all contacts that are not tagged with a certain tag. The problem is if a contact is tagged with another tag, it will still show in the ...
0
votes
1
answer
155
views
Error occurs : Unknown column 'relationship_civireport.contact_id_a' however not for "Super User" users
UPDATE 29th July :
Yes it's taken me this long to work it out so now I need some advice please. I have tracked the place where "rogue" SQL down is being created and it's source is in this hook : ...
1
vote
1
answer
348
views
New install, system status says "cron not running" and "timestamp mismatch"
Running CiviCRM 4.7 on WordPress 4.7.3, hosted on a shared Linux server at 1and1.com. The WordPress site is using PHP 7.0 but apparently the latest version I can use from the command line is 5.5.
Two ...
1
vote
1
answer
86
views
How to find CiviCRM version in SQL from a database backup
I have a backup of a CiviCRM database from some time ago that I'd like to inspect inside a test site. When setting up the test site, I want to use the correct release version to match the version at ...
2
votes
2
answers
230
views
Error creating Custom Data - DB Error: already exists
When I try to create Custom Data ( Administer-->Customize Data and Screens-->Custom Fields ), I get the following message. The query info is different depending on the type of Custom Data I am trying ...
3
votes
1
answer
189
views
How to remove "Extended Demographics"?
Years ago we tried out CiviHR and it added something which looks like a set of custom fields, called "Extended Demographics". We uninstalled the extension but this did not go away. I can't find ...
5
votes
1
answer
52
views
Dropping thousands of records into a batch via SQL
I have imported (tens of) thousands of historic contributions to a site that uses Accounting Batches regularly. They were starting to bog down the batch assignment page so I thought I would simply ...
0
votes
1
answer
42
views
Can I change a contribution's values for Financial Type and Campaign directly in SQL without danger?
When using "Import Contributions > Update Existing Contributions" I can import a Financial Type using the full name of an existing (and active) Financial Type. However, if I try to use the full name ...
1
vote
0
answers
148
views
DB Error: syntax error in civicrm/contribute/transact
I'm getting the above error reported and I have no idea where to look to solve it.
The URL generating the error is http://adpca.org/civicrm/contribute/transact?reset=1&id=3
The log / ...
2
votes
2
answers
69
views
Is it possible to have many to many relationships between custom field sets?
i tried to look for an answer but im pretty confused with what i found..
I'm trying to create a many to many relationship like in SQL..
for example:
lets say i want to create a custom 'Event'(a ...
2
votes
2
answers
989
views
Is there a function that will execute multiple SQL statements?
I'm writing an extension that needs to make a bunch of temporary tables and copy data around. I think CRM_Core_DAO::executeQuery() only executes one semicolon-delimited statement at a time. Is there a ...
1
vote
2
answers
47
views
For an extension, to modify database data, is it better to use SQL or API
Simple question here,
From a computing and 'civicrm correctness' point of view, is using SQL (via CRM_Core_DAO::executeQuery or similar) or using the API ( civicrm_api3('Contact', 'create', $params)) ...
1
vote
1
answer
36
views
Selecting Participants report from Manage Events results in: "DB Error: syntax error Return to home page." after update
Worked fine with Drupal 7.37 core and CiviCRM 4.6.2.
Updated site to 7.38 core and Civi 4.6.5, then get an error for all of my events when trying to display Participants from "Manage Events" page.
...
1
vote
2
answers
195
views
Import from one db to another fails
I exported all the civi tables from one database and saved as a sql file, including a droptable statement. I then tried to import the data into a fresh site with brand new civi installation. The ...
8
votes
3
answers
368
views
Is there a way to encrypt a single field in Civi database?
To protect sensitive data - such as HIPAA info
11
votes
3
answers
5k
views
Why do I get "Access denied; you need (at least one of) the SUPER privilege(s) for this operation" when importing a SQL dump to CiviCRM?
When attempting to import a database dump from CiviCRM on a computer other than the one I dumped it from, I often get an error that looks like:
[ERROR in query 7] Access denied; you need (at least ...
1
vote
1
answer
51
views
How do I generate sql/civicrm_generated.mysql using bin/regen.sh file?
I am working on CRM-11369 bug. However, I was facing problems in generating sql/civicrm_generated.mysql using bin/regen.sh file. The fifth line of bin/regen.sh file is 'source dirname $0/setup.conf'. ...
6
votes
3
answers
725
views
How do I use custom fields in Drupal views?
[EDIT] Totally editing this question. Custom fields do not work in my Drupal views, at all:
The interface to add the custom field worked just fine. It's only the generated sql thats wrong. Its ...
3
votes
1
answer
113
views
How do I test a CiviCRM upgrade script?
I have a PR that adds a file CRM/Upgrade/Incremental/sql/4.7.alpha1.mysql.tpl. How should I go about testing this code?
7
votes
4
answers
451
views
Where is the CiviCRM ERD / data-dictionary?
In a variety of contexts it is useful to have a Entity Relationship Diagram (ERD) / data-dictionary:
Using the API
(Xavier avert your eyes) querying the database with SQL
Developing extensions; and ...
15
votes
6
answers
712
views
What's the best way to import memberships and their associated contributions?
I have a load of Memberships to import. Each has a linked Contribution.
While I can import Memberships and Contributions separately, I cannot find a way to import the two together - in other words ...