Debugging Error 500: Integrity constraint violation: Column 'id' cannot be null

[iYo2einu]

Hi,

I'm trying to update existing Accounts via the api. It works for 2/3s of all Accounts but fails for the other 1/3. For those where it does not work, it fails regardless of which attribute we try to change. For example, an update to such a problematic Account produces the following log

2021-08-31 08:48:43] DEBUG: API (23000) SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'id' cannot be null;
PUT /Account/60c1defe09e2486bb; Input data: {"vatNumber": "XXXX"}; Route pattern: /{controller}/{id};
Route params: Array ( [controller] => Account [action] => update [id] => 60c1defe09e2486bb ) [] []

[2021-08-31 08:48:43] ERROR: (23000) SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'id' cannot be null;
PUT /Account/60c1defe09e2486bb; line: 72, file: /espocrm/application/Espo/ORM/SqlExecutor.php [] []

The same issue occurs when the Account is updated via webbrowser. These Accounts were created via API without any issue.
Any ideas on how to narrow this issue down?

[shalmaxb]

Hi, this is a database error. In one table you have an id, which cannot be empty (NULL), but seems to be empty from the records, your API created. Sometimes this could happen, if the format of id is not the same in all record (if you mix e.g. number and varchar format).
It is only a bit guessing, because you have to look into your database and find the culprit table/column.

From your json file you have one field notNull: true (isPartner), maybe it is this one. You could try to set this on false, to see if it works then.

[iYo2einu]

Hi,
thank you for the feedback. Disabling the notNull requirement did not help anything. However, I was finally able to track this down and fix it:

We use Accounts and Opportunities. Opportunities can be linked to Accounts (as is the default functionality).

• However, for whatever reason, we added an additional One-to-Many link to Accounts to link Opportunities to it.
• I'm not quite sure what happened next, but I think this was removed as the mistake was discovered and the "main link accounts and opportunities" was already in place
• However, one field remained in Accounts which created this issue (In Accounts -> Fields there was an one-to-many entry "Opportunities")
• After removing this, everything is back to normal

I am not quite sure how this issue was introduced, but I wanted to post this as a reference for the future. Maybe somewhere a validator for creating a link which already exists is missing?

Anyway, thanks for your help and the great tool!

[esforim]

Thanks for coming back with a solution. I assume the rebuilt-clear cache didn't not fix this and require a manual update? I guess you can (1) report it as a bug, or (2) request for a feature request (validator)

[shalmaxb]

Hi, unfortunately, this is something, that occurs in espoCRM always. If you had a relationship and you delete it in the Entity settings, the automatically by the system created database tables stay in place. So you will have to make alwasy correct relationships (what is nearly impossible, when constructing an app) or you must be aware to delete the corresponding database tables from formely created and deleted relationships.

Anyway, thank you for the solution feedback. That always helps for whom hits the same kind of problem.

[telecastg]

The same is true for fields created through the Admin panel, when you "delete" a field or a link, Espo deletes the metadata reference (entityDefs) BUT NOT THE ACTUAL database field or tables and also does not delete any possible references that you might have like layouts.

This issue has been raised in GitHub but there is no resolution offered or planned by the development team https://github.com/espocrm/espocrm/issues/1537

[esforim]

Sometime it a good thing that the database itself isn't delete. The bad is that you can error that you have no idea how to fix.