CODEWITHSUNDEEP
mysqlsql-updatesql-insertcivicrm
bpmccain
bpmccain

Reputation: 600

MySQL Column giving Insert / Update erros

Having a strange problem with a mysql table. I can use the column 'entity_table' without any issues in a SELECT statement, but it is giving me issues with any UPDATE or INSERT statement.

Specifically, I am getting 

"Error Code: 1054. Unknown column 'entity_table' in 'field list'"

The column definitely exists, as I can use it in a SELECT statement as mentioned above.

I get the exact same error on both of the following statements:

INSERT INTO `civicrm_note` (`entity_table`) VALUES ('civicrm_relationship')

and 

INSERT INTO `civicrm_note` (`entity_id`) VALUES (513576)

which is interesting since I am not even referencing entity_table in the field list in the second statement.

No problems on any other table or field in the database as far as I can tell.

This SELECT statement works fine:

SELECT * FROM civinbla.civicrm_note WHERE entity_table = 'civicrm_contact';

Upvotes: 1

Views: 130

Answers (2)

bpmccain
bpmccain

Reputation: 600

The answer was related to triggers. The triggers on our DB were all programmatically created using a MySQL user that exists solely for the application. For some reason, the AFTER INSERT and AFTER UPDATE triggers had been modified so that the DEFINER was root instead of the application user.

Changed the DEFINER to the application user, and everything worked fine.

Upvotes: 2

Stacky
Stacky

Reputation: 915

I'm guessing it's a trigger problem as Barmar mentioned. Take a look here: MySQL Error 1054: Unknown column in 'field list' on INSERT

Additionally, if entity_id is the primary key, you might want to insert both values (entity_table and entity_id) in one statement. Otherwise, the table will not take your insert.

I don't know though, if this would throw such an error.

Upvotes: 1

Related Questions