Questions tagged [sql]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
15
votes
6answers
589 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 ...
9
votes
3answers
3k 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 ...
8
votes
3answers
236 views

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

To protect sensitive data - such as HIPAA info
7
votes
4answers
316 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 ...
6
votes
3answers
557 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 ...
5
votes
1answer
45 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 ...
4
votes
1answer
59 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
2answers
63 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 ...
3
votes
1answer
107 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?
3
votes
2answers
54 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
1answer
156 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 ...
3
votes
2answers
55 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 ...
3
votes
1answer
49 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 ...
2
votes
2answers
573 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 ...
2
votes
1answer
31 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
2answers
121 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 ...
2
votes
0answers
30 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, ...
2
votes
2answers
52 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 ...
2
votes
2answers
57 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 ...
1
vote
2answers
44 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
1answer
129 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
1answer
41 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 ...
1
vote
2answers
182 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 ...
1
vote
2answers
100 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 ...
1
vote
1answer
63 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 ...
1
vote
1answer
264 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
1answer
32 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
1answer
44 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'. ...
1
vote
2answers
44 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? ...
1
vote
0answers
21 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 ...
1
vote
0answers
97 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 / ...
0
votes
1answer
124 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 : ...
0
votes
1answer
65 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 ...
0
votes
1answer
26 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?
0
votes
1answer
23 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 ...
0
votes
1answer
32 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 ...