Deadlock in "metadata_cache" table cause the system having no response

Symptoms:
When the deadlock happened, user could not do any operation as they got no response from system.
This issue also happened last year and it seems that it could be happened repeatedly. As the "metadata_cache" table being deadlock 3 times within 2 years and happened different time of day, it seems like random occurrence.

Investigation:
After we looking at the activity monitor in SQL Server Management Studio, we found that there were quite a few processes with "DELETE FROM metadata_cache WHERE id = 'xxxx-xxx-xx-xxx' " having deadlock. We also found that there are 3 records in metadata_cache table with the same type "meta_hash_base".

Action after encountering the issue:
The server being rebooted, the metadata_cache table was truncated and then asked user to login to the system. The records in metadata_cache table were then rebuilt and the system resumed.

Issue simulation:
I tried to create multiple "meta_hash_base" records in metadata_cache table. After I login to the system using a new browser instance, the system is then no response and the metadata_cache table being deadlock.

I would like to ask few questions regarding the metadata_cache table:

1. What is the role of metadata cache table? (There is an article on SugarCRM developer guide but it seems focus on the file and architecture on application server)
2. The operation that involved lookup on metadata cache table. (e.g. View the record in some modules)
3. How often will the entries in metadata cache table be updated / deleted / inserted?
4. Could we set the field "Type" as the key so that meta_hash_base entry will not be duplicated?

Thank you.

Environment: SugarCRM Pro 7.6.2.1, MS SQL Server 2014, PHP 5.4.45

Parents

0 Justin Kuehlthau over 6 years ago

I ran into this issue on an on-premise 7.7.0.0 instance in a Windows environment. The fix from Sugar Support was always the same:
1. Delete the contents of the 'cache' directory in the Sugar file system.
2. Truncate the 'metadata_cache' table.
3. Refresh a page in Sugar, so that both the table and the cache directory are rebuilt.
Sugar Support also noted that: "We have made numerous bug fixes directly related to metadata cache problems available in 7.7.2.1." You should try upgrading. We eventually re-built the instance in ondemand and migrated the data to the new ondemand instance.
0 Björn Canales Pfisterer over 6 years ago in reply to Justin Kuehlthau

not very helpful but additional input:
same experience but SugarCRM 7.9.4 on Windows environment and MS SQL.
Truncate or delete from metadata_cache solves the problem. It occures randomly but only by using Studio or Repair and rebuild.
Bests
Björn
0 Justin Kuehlthau over 6 years ago in reply to Björn Canales Pfisterer

I guess that means they did not fix all of the metadata_cache issues in 7.7.2.1. Have you upgraded past 7.9.4? Did the issue continue?
I've only ever seen this issue on the one Windows/MSSQL instance I inherited. I've never had this issue on LAMP.
0 Björn Canales Pfisterer over 6 years ago in reply to Justin Kuehlthau

I can confirm this occurs on Windows / MSSQL system with SugarCRM 7.9.4
All admin privileged people are aware of it and don't do any changes during office time to avoid productive disurbances so you can say it has a direct impact on how to use SugarCRM
Bests
Björn
0 Kent Tsui over 6 years ago in reply to Björn Canales Pfisterer

Well, I used to ask for the help from sugar support and the technical support reply that he could not simulate the issue in Linux and MySQL. So it maybe the way that MS SQL server handle under certain condition is different from MySQL.
Thank you for your reply.
Kent

Reply

0 Kent Tsui over 6 years ago in reply to Björn Canales Pfisterer

Well, I used to ask for the help from sugar support and the technical support reply that he could not simulate the issue in Linux and MySQL. So it maybe the way that MS SQL server handle under certain condition is different from MySQL.
Thank you for your reply.
Kent

Children

No Data