Questions tagged [sql]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
2 votes
1 answer
38 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 ...
user avatar
  • 355
1 vote
0 answers
28 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 ...
user avatar
1 vote
2 answers
53 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 ...
user avatar
  • 431
1 vote
0 answers
45 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: ...
user avatar
0 votes
1 answer
19 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 ( ...
user avatar
  • 4,263
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,...
user avatar
  • 684
2 votes
0 answers
44 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, ...
user avatar
3 votes
2 answers
115 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 ...
user avatar
3 votes
2 answers
110 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 ...
user avatar
  • 3,122
2 votes
2 answers
90 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? ...
user avatar
4 votes
1 answer
74 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 ...
user avatar
4 votes
2 answers
113 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 ...
user avatar
  • 367
2 votes
1 answer
39 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 ...
user avatar
  • 45
2 votes
2 answers
64 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 ...
user avatar
  • 1,640
1 vote
1 answer
177 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 ...
user avatar
1 vote
2 answers
157 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 ...
user avatar
  • 403
0 votes
1 answer
42 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?
user avatar
  • 1,685
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 ...
user avatar
  • 5,994
0 votes
1 answer
34 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 ...
user avatar
1 vote
1 answer
76 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 ...
user avatar
  • 574
0 votes
1 answer
87 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 ...
user avatar
  • 958
3 votes
1 answer
72 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 ...
user avatar
0 votes
1 answer
142 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 : ...
user avatar
1 vote
1 answer
344 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 ...
user avatar
1 vote
1 answer
58 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 ...
user avatar
  • 3,148
2 votes
2 answers
195 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 ...
user avatar
  • 189
3 votes
1 answer
183 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 ...
user avatar
  • 1,247
5 votes
1 answer
51 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 ...
user avatar
0 votes
1 answer
41 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 ...
user avatar
1 vote
0 answers
125 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 / ...
user avatar
  • 11
2 votes
2 answers
63 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 ...
user avatar
2 votes
2 answers
895 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 ...
user avatar
  • 1,960
1 vote
2 answers
46 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)) ...
user avatar
1 vote
1 answer
34 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. ...
user avatar
1 vote
2 answers
192 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 ...
user avatar
  • 473
8 votes
3 answers
323 views

Is there a way to encrypt a single field in Civi database?

To protect sensitive data - such as HIPAA info
user avatar
  • 1,139
10 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 ...
user avatar
1 vote
1 answer
48 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'. ...
user avatar
6 votes
3 answers
693 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 ...
user avatar
  • 1,157
3 votes
1 answer
110 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?
user avatar
7 votes
4 answers
421 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 ...
user avatar
  • 1,561
15 votes
6 answers
679 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 ...
user avatar
  • 171