DB triggers

[Chris Burgess]

CiviCRM keeps some logic at the DB layer, which means that some of the CiviCRM logic occurs at the data storage layer - beneath even the DAO - and is written in a separate language to the main codebase.

I feel like this is one of the hardest parts for CiviCRM community with regard to upgrading (eg when upgrade logic written in SQL doesn't gracefully handle unexpected DB structures), and that in some places it introduces some performance/behaviour issues (eg CRM-13796). Dynamic generation of triggers, routines add a requirement for DB permissions above and beyond what most web apps would require, which can be a security consideration also (opinion).

What do others think about the amount CiviCRM uses DB-layer functionality like triggers, views, routines, and dynamic creation of these? Is it the way we want to do things in future?

[totten]

Random thoughts:

A. Generally, I think the benefit of using triggers is that (1) they're relatively fast, (2) they work in cases where a developer/integrator bypasses Civi's main logic and hits the DB, and (3) they don't require much logic in the PHP layer.

B. If the issue here is really the performance of a system-flush (like in CRM-13796), then I think we can tackle with some modest performance optimizations (as discussed in the ticket).

C. It can make things a bit clearer to enumerate the use-cases for triggers. Here are a few that I'm aware of:

1. (Currently) Fine-grained logging (ie storing each revision of each major entity)
2. (Currently) Managing default translations in multi-lingual configurations (eg if there's no Spanish translation for a string, copy in the English version)
3. (Currently) Tracking the creation/modification date for contacts and closely related tables (phone/email/etc). This is useful in oplocking.
4. (Currently) Storing a normalized phone-number (civicrm_strip_non_numeric)
5. (In NYSS) Managing indexes that facilitate performant deduping
6. (Proposed/wishlist) Providing performant, incremental updates to the smart-group-cache

D. Doctrine doesn't seem to have any built-in support for triggers. It may be able to coexist with triggers as long as they don't contradict Doctrine's in-memory caching mechanism, but there is a risk of cache-coherency issues. For example,

 - If the PHP business logic seeks to modify record X and the SQL trigger modifies a field in record X, then there would be high risk that the in-memory PHP object becomes stale. (And ORMs like Doctrine are generally more aggressive about re-using the in-memory PHP objects.) Cases #2 and #4 are examples of "triggers which modify the same record".
 - But if the PHP business logic modifies record X and the SQL trigger writes to a separate record Y, and if Y isn't generally cached, then there's less risk. Cases #1, #5, and #6 are examples.

E. Doctrine does provide its own trigger-like event mechanism -- eg http://docs.doctrine-project.org/en/latest/reference/events.html

[Eileen]

Another current use is caching looked up data into a custom field - ie. the PTP extension stores aggregate information about contributions into that field. We have also done the same thing to store information based on postcode lookups into a custom field on the contact. The alternative to this sort of thing can be extending advanced search. In the case of the former it is less performant. In the case of the latter it works pretty well & you can save smart groups on advanced search extensions (nice) but you don't get views & reports 'for free'

[adixon]

Here's where triggers got me messed up recently - I moved a multilingual install and was using the --tab feature in mysqldump and it mangled all the translations when I reloaded.

As a specific issue, it's manageable, though it was hard to debug until I remembered about the triggers and took a look.

But it's an example of the kind of thing that Chris is probably referring too, about managing complexity and unexpected interactions.

Maybe there are some criteria we could use in evaluating when triggers are appropriate and when they're too dangerous, e.g. when they overlap with functions provided by php. Orthogonal is a good word ...

[jdunkley]

Here's my "trigger tale"!

{Trigger warnings: MySQLism, bad error messages, CRM-13796, developer experience.}

Copied database from the live server back to my laptop. Local DB user confgured with permissions as per wiki. Database loaded up and ran fine. Until ...

Changed a permisson in Drupal. Didn't work - and submitting the Drupal permissions page, a CiviCRM error page? Hello!

Sorry but we are not able to provide this at the moment.
DB Error: unknown error
Return to home page.

Luckily recognised the CiviCRM error display for what it was, so checked the error message in the CiviCRM.12345.log file.

http://pastebin.ca/2824678

So the real (hidden) error is "The user specified as a definer does not exist". Created the user, moved onto the next CiviCRM error when trying to save Drupal permissons again.

Sorry but we are not able to provide this at the moment.
DB Error: insufficient permissions
Return to home page.

Back to the CiviCRM.12345.log ...

http://pastebin.ca/2824680

Dirty secret: to migrate a CiviCRM DB, it's required to create a matching DB user and permissions to the previous server setup. (So you can't export a CiviCRM.sql from hosting company A to hosting company B unless you obtain matching usernames?)

If this requirement isn't fulfilled, CiviCRM gives an uninformative error message - even masks "user does not exist" with "unknown error".

This sort of thing drives users away ... maybe familiar enough to regular CiviCRM developers, but it's a turn-off for newbies.

Thansk for listening!

[Hershel]

Dirty secret: to migrate a CiviCRM DB, it's required to create a matching DB user and permissions to the previous server setup. (So you can't export a CiviCRM.sql from hosting company A to hosting company B unless you obtain matching usernames?)

No, all you must do actually is remove all of the DEFINER statements in the dump and then import. As also posted here http://forum.civicrm.org/index.php/topic,30485.msg130739.html#msg130739

[Chris Burgess]

Stripping out the DEFINERs is a bit of a pain - especially if you're dealing with many-GB DB dumps. (In which case it's much easier to do if you can strip it out via sed or perl than to open the file in an editor, but that takes some confidence.)

Hershel, do you know of a way to use mysqldump (etc) to avoid getting DEFINER statements included in the first place? That would be easiest of all - but still a bit of a trap for anyone who doesn't know about it. There are quite a few of these "if only you knew ..." things in CiviCRM.

[Chris Burgess]

Also: until now I didn't know we rebuild triggers etc on permissions save. Should have realised - of course that triggers cache clear, which in turn ... I need to make those "Oh yeah CRM-13796 yay" moments part of a meditative practice. Or dedicate the time to tackling it.

[Hershel]

I do not know of a way to avoid those DEFINERs. For the sed code, click on the link I posted above and scroll down two posts. It's not my code, but the odds are it works.

[Chris Burgess]

Thanks. Also perl code here. For people who don't mind editing databases using stream editors, the CRM of choice is clear.