ERROR: (42S22) SQLSTATE[42S22] but column exists!

[jwit]

We have upgrade our installation to the latest version and have issues with a custom entity based on EVENT. When opening the list view, everything is fine. When opening the detail page of an entity, we see a server error 500 banner and also some sub lists do not resolve.

In the logfile we can see the following error:

[2023-04-28 12:31:15] ERROR: (42S22) SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cairful_server_access.date_start' in 'field list'; GET /CairfulServerAccess/5addc43331bfbb5c5; line: 76, file: /var/www/html/espo/application/Espo/ORM/Executor/DefaultSqlExecutor.php
[2023-04-28 12:31:15] ERROR: (42S22) SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cairful_server_access.date_start' in 'field list'; GET /Activities/CairfulServerAccess/5addc43331bfbb5c5/activities; line: 76, file: /var/www/html/espo/application/Espo/ORM/Executor/DefaultSqlExecutor.php
[2023-04-28 12:31:15] ERROR: (42S22) SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cairful_server_access.date_start' in 'field list'; GET /CairfulServerAccess/5addc43331bfbb5c5/tasks; line: 76, file: /var/www/html/espo/application/Espo/ORM/Executor/DefaultSqlExecutor.php
[2023-04-28 12:31:15] ERROR: (42S22) SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cairful_server_access.date_start' in 'field list'; GET /CairfulServerAccess/5addc43331bfbb5c5/stream; line: 76, file: /var/www/html/espo/application/Espo/ORM/Executor/DefaultSqlExecutor.php
[2023-04-28 12:31:15] ERROR: (42S22) SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cairful_server_access.date_start' in 'field list'; GET /Activities/CairfulServerAccess/5addc43331bfbb5c5/history; line: 76, file: /var/www/html/espo/application/Espo/ORM/Executor/DefaultSqlExecutor.php


It states that the date_start column is missing in the database, but in fact it exists. I have seen similar cases here in the forum, but these all had the issue after manually deleting a field, which we did not. In the opend detail page, the edit form is rendered and also, the start date is displayed properly. You can even add new entities and the value put in start date field is persisted. Nevertheless it is no longer possible to delete the entry via UI. Are tickmarks correct in the SQL? 'cairful_server_access.date_start' -> `cairful_server_access`.`date_start`?

We have the same issue with another entity also based on type EVENT.

I have tried rebuild, deleting cache, checked entity/client defs (seem to be ok, did not change).
I did not try rebuild --hard, but I assume this wont change much since database fields seem to be ok.

During the migration path from our former version to the latest version, I had one issue with a hangig upgrade script which I cancelled after some minutes.
After repeating the upgrade, everything seems to upgrade normal and fast. May this be related? Did something change in the event based entities' structures?

Espo Version is latest, PHP is 8.2.5
We have paid AdvancedPack + SalesPack + Outlook expansions installed in latest version.

[esforim]

Welcome to me! And another person have this same problem (unfortunate to hear but it show that I'm not alone). Now it a +2. How I fix mine was... basically I just remove the relationship altogether. and haven't add it back since I have feeling it will break. But for you, you don't have that freedom.

You may want to do a backup and use the -hard rebuild though, since that may fix it. I couldn't use it cause my server CLI suck!

https://forum.espocrm.com/forum/general/90853-v7-4-3-bug-error-and-discussion#post90900

[jwit]

Thank you for the reply. I think it does not have anything todo with relations. But since you also experienced this with an event based entity I did some more investigations and I come to a funny result: the update procedure to latest version messed up with updating event-based entities, since it seems that additional fields have been added. I conducted a test with 4 entites.

- pre-update entity, no longer working (500 + above ERROR), does not have new dateStartDate/dateEndDate fields (db + def)
- pre-update entity, no longer working (500 + above ERROR), HAS new dateStartDate/dateEndDate fields (db + def)
- pre-update entity, WORKING, does not have new dateStartDate/dateEndDate fields (db + def)
- new created test entity, WORKING, HAS new dateStartDate/dateEndDate fields (db + def)

(see image attached)
So seems update started to introduce new dateStartDate/dateEndDate fields, but did not complete successfully.
Next step is to analyze the json file definitions and I think, editing them so that they are equal to the new created test entity + adding the db fields will fix it.
If not I need yuri

[esforim]

Thank you! Love your work. Hmm, so if we can somehow do the manual fix of these two date then we can fix this error?

[jwit]

Digging into the JSON defs finally pointed me to the bug:

It seems like the construction of reminders field in the entityDefs is the reason. The structure of the select and order instruction seems to have changed, but was not updated during the version upgrades. Especailly, the select contains a TIMESTAMPDIFF expression, which contains the errornous part of the query, which results in the ERROR: (42S22) SQLSTATE[42S22] from the logs. The new version no longer contains the table name. I adopted the structure from my newly created Event based entity to my existing entity and did a clear cache + rebuild. Thats it. See the attached image to see the difference (left: new event based entity, right: old event based entity).

[esforim]

I guess this is the end and the 'solution' to reproducing the bug?

Do you reckon you can compile all the major information into single post and post it in either Bug section or https://github.com/espocrm/espocrm/ issue?

Yuri might totally miss this.

[jwit]

Now I did:

https://github.com/espocrm/espocrm/issues/2735

[jwit]

esforim Yuri fixed it immediately for the 7.4 upgrade