DB table that exists says it doesn't

Question

After upgrading to 4.7.10 from 4.7.9, I'm no longer able to edit/add price sets. I think that the DB changes related to https://issues.civicrm.org/jira/browse/CRM-12252 are biting me. (Multi-lingual is enabled.) I don't recall any problems with the upgrade. However, now the DB error says: [debug_info] => UPDATE civicrm_price_field_value_en_US SET is_default = 0 WHERE price_field_id = 27 [nativecode=1054 ** Unknown column 'help_pre_en_US' in 'field list']

I can reproduce this same error by editing any field for any record in the civicrm_price_field_value table and the civicrm_price_field_value_en_US view via phpMyAdmin. The field does exist, I've recreated the View, and dropped the column and recreated it. Other _en_US tables are unaffected. (Yes, I've cleared CiviCRM caches, and run commands in Private windows to try to get away from cache poisoning.)

This is happening after the upgrade, with new data since the pre-upgrade backup on a live server. Rolling back isn't really an option. There must be something that needs to be refreshed, but I can't find it. Any ideas?

Wordpress 4.6 CiviCRM 4.7.10

Another bit of weirdness that points toward a failed DB upgrade: The COMMENT portion of the two new fields is not there, but both fields are decidedly there.

Answer 1

It's not really an answer, but I did fiddle around with things enough to break it into working again. I ended up dumping the DB to a backup, dropping both new columns, civicrm_price_field_value.help_pre_en_US and civicrm_price_field_value.help_post_en_US, then changing the DB version back to 4.7.9. I then re-ran the upgrade and got a "Table already exists" error on another table. Dumped the backup back into the DB, recreated the two columns from above, changed the version back, and now everything seems kosher for the most part. I can add/delete/change price sets and therefore work with Events. Can't edit the broken events, but that's a leftover issue I can work around.

Answer 2

I believe it could be a trigger problem. The way the multilingual works in CiviCRM is that CiviCRM will only work on a language at a time using a sql view (e.g civicrm_price_field_value_en_US). When you create a new entity, a trigger ensure that every language is properly initialized by copying the multilingual column data to others languages. If the trigger is not up-to-date, it will fail to do this and can lead to the error you got.

A way to solve that is to rebuild triggers like so : https://yoursite.org/civicrm/menu/rebuild?reset=1&triggerRebuild=1