CiviCRM Community Forums (archive)

It's a bug. The list of permissions listed in the upgrade documentation are incorrect - see civiwiki: /confluence/display/CRMDOC43/Upgrade+Drupal+Sites+to+4.3+-+Drupal+7
Specifically: CREATE VIEW, TRIGGER, CREATE ROUTINE, ALTER ROUTINE are new requirements for update to CiviCRM 4.3, the others were needed before on 4.2.x.

You actually need SUPER access. Having TRIGGER is not enough.

Sloppy.

Can replicate readily on Mysql 5.5.31 on a box that I control.

I think I've worked it out in the process of accidentally breaking something else.

There's at least one FUNCTION used in CiviCRM:

  CREATE FUNCTION civicrm_strip_non_numeric  ...

and... consequently:

http colon slash slash dev.mysql.com/doc/refman/5.0/en/create-procedure.html
"As of MySQL 5.0.3, CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. They might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE FUNCTION might require the SUPER privilege, as described in Section 18.6... "

That's my guess anyway. I haven't run through a manual check to test specifically. I can however reliably make the standard installation fail for a user who has ALL PRIVILEGES but not SUPER.

I stumbled across this because I managed to hose our database by creating too many custom field groups and hitting the 64 table Left join limit, so dropped it, restored from backup and found that it's now missing the function because mysqldump's default behaviour is to ignore functions / stored procs. So in nosing through the CREATE FUNCTION doco to try and fix it I found this. But I'll raise another post to ask for help with this as I'm now digressing from the original thread!

Cheers

@civichris can you think of a better way to achieve what we're trying to do with the phone_numeric field? Perhaps using a sql function is not the best option if it requires extra permissions.

I think I've found the reason for the "Column 'trxn_id' cannot be null" error - if you have logging enabled. I had this error and found that while civicrm_financial_trxn had:

  `trxn_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
and no other constraints preventing it from being NULL, by contrast in the logging db, log_civicrm_financial_trxn had:

  `trxn_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'unique processor transaction id, bank id + trans id,... depending on payment_method',
I changed the latter with:

ALTER TABLE log_civicrm_financial_trxn MODIFY `trxn_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL;
- taking the definition from the former. This fixed the problem for me.

Cheers,

Dave J

Thanks Dave for finding the solution to this.  Yes, I have been using logging tables. I didn't think to try turning them off. Presumably your fix will sort out the civicrm_contribution table too.

Stuart