Intermittent issues after rehosting

[err1max2@gmail.com]

We use coreBos (crm) as back-end for our market place (front-end on WP).
Due to change of provider we had to rehost corebos server.
We copied DB and full application folder.
DNS wise everything is the same ... FQDN hasn't changed.
The only change is that, instead of using standard https port (443), we had to use a different one (xxxx).
I reflected the same in the config.inc.php

Everything seems to be working fine.
We can loging, search contacts, workflows are working fine and communication with front-end (WP) is also fine.
Not the less when we edit some contacts (not all) the application hangs and takes ages (minutes) to perform that action.
To be noted that while the portal hangs for me saving the contact, this do not impact the front-end (WP). I mean that while my browser hangs saving the record I can still navigate on my market place which is continously talking and fetching info from the crm.

In apache logs I have some Warning

PHP Warning:  Invalid argument supplied for foreach() in /var/www/html/wordpress/wp-content/plugins/corebos/lib/cbSurveys.php on line 123

And under logs/vtigercrm.log I have several entries

FATAL webservice Lock wait timeout exceeded; try restarting transaction

Any advise or suggestion?
Let me know
Thanks

[joebordes]

This is the blocking warning:

FATAL webservice Lock wait timeout exceeded; try restarting transaction

we have to understand which query is blocking and why. This will appear in MySQL, it is a MySQL issue. there is an overlap of two or more queries accessing the same table for modifications. There are some MySQL commands to see those blocking queries but I don't remember exactly which ones they are right now and on cell phone.

It may very well be the session, but we have to ask MySQL to be sure.

I'll give more information when I have better access but that is the first thing I would look into

[joebordes]

This is what we are looking for:

SHOW FULL PROCESSLIST;

https://serverfault.com/questions/36260/how-can-i-show-mysql-locks
https://www.dbrnd.com/2016/02/mysql-script-to-identify-the-locks-and-blocking-transactions/
https://dba.stackexchange.com/questions/260543/identifying-blocking-queries-on-mysql-5-5
https://sql-developers.co.uk/mysql-tutorial/finding-mysql-blocking-queries-and-deadlocks.html

see if you can share some of that information here, see if I can help

[joebordes]

You have to do that when it is blocked, obviously :-)

[err1max2@gmail.com]

(02-02-2024, 08:23 PM)joebordes Wrote: You have to do that when it is blocked, obviously :-)

Thanks Joe for the hints ... I'll dig a bit more and provide you an update soon.

[err1max2@gmail.com]

Joe,
I did seevral tests and while the locks are certainly a valid concern it's definitely NOT the culprit of the slow performance we face.
I was lucky to see once a lock on the transactions table (information_schema.innodb_trx) but in the following 10+ additional tests there was nothing.
I have no traffic or users on the crm. No processes returned by processlist ... in other words Db comoletely idle.
As sson as I try to save a specific record I see up to 3 (typically 2) processes in sleep status ... they stay in that status for 1,5 minute and then finally the sql to update the contact info is executed (see below)

MariaDB [(none)]> source list_db_process+transactions.sql;
+-------+--------------+-----------+-------------+---------+------+----------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+--------------+-----------+-------------+---------+------+----------+------------------+----------+
| 20284 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
| 20291 | crm_usr | localhost | crm_db | Sleep | 199 | | NULL | 0.000 |
| 20292 | crm_usr | localhost | crm_db | Sleep | 173 | | NULL | 0.000 |
+-------+--------------+-----------+-------------+---------+------+----------+------------------+----------+
3 rows in set (0.000 sec)

Empty set (0.000 sec)

MariaDB [(none)]> source list_db_process+transactions.sql;
+-------+------+-----------+------+---------+------+----------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+------+-----------+------+---------+------+----------+------------------+----------+
| 20284 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
+-------+------+-----------+------+---------+------+----------+------------------+----------+
1 row in set (0.000 sec)

Empty set (0.000 sec)

Tried to incrase Error logging but still I don't get anything.
Is there any specific setting to be applied at DB level?
I'm currently running

Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12

+-----------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------+----------------------------+
| in_predicate_conversion_threshold | 1000 |
| protocol_version | 10 |
| slave_type_conversions | |
| system_versioning_alter_history | ERROR |
| system_versioning_asof | DEFAULT |
| system_versioning_insert_history | OFF |
| tls_version | TLSv1.1,TLSv1.2,TLSv1.3 |
| version | 10.11.4-MariaDB-1~deb12u1 |
| version_comment | Debian 12 |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| version_malloc_library | system |
| version_source_revision | |
| version_ssl_library | OpenSSL 3.0.11 19 Sep 2023 |
| wsrep_patch_version | wsrep_26.22 |
+-----------------------------------+----------------------------+

Please note that I have this slow behavior only while editing a "complex" Contact with Sales Orders, Tickets, Invoices, Surveys ...
Editing a new "clean" contact is fine .. the update takes only few seconds.

What else can I check?
Any other hint?

Let me know
thanks

[joebordes]

could it be some workflow trying to update related records?

or some workflow trying to connect to Wordpress that is getting a timeout?

check the workflows, try deactivating the ones related to contacts and see then activating them one by one to see which one blocks the save.

[err1max2@gmail.com]

(02-04-2024, 11:52 PM)joebordes Wrote: could it be some workflow trying to update related records?

Joe,
I initentionally disabled the workflows (stopped the job running them) to avoid any "additional" transactions/"noise" happening on the DB.
In a certain way I "isolated" the application/DB so that the only transaction/action running was my update from the web page (editing contact page).
Needless to say that running directly the following sql on the db (logically equivalento to what I'm trying to do via the web) do not take any second (imediate).

update `vtiger_contactdetails` set online=1 WHERE contactid=497481;

Is there any debugging that I can enable to understand what is happening?
Let me know
Thanks 

[err1max2@gmail.com]

Joe,
I enabled full DB logging and made sure the only action taken on corebos was my Contact update and this is the transaction which took 3 minutes

240213 20:31:33 63 Connect crm_usr@localhost on crm_db using Socket
63 Init DB crm_db
63 Query SET NAMES utf8
63 Query select distinct funcfile from com_vtiger_workflows_expfunctions

AFTER 3 MINUTES this is the follwoing select listed in the logs

240213 20:34:18 62 Query SELECT * FROM vtiger_modtracker_tabs WHERE vtiger_modtracker_tabs.visible=1 AND vtiger_modtracker_tabs.tabid=4

Any idea why this statement:

select distinct funcfile from com_vtiger_workflows_expfunctions

could take 3 minutes?
Am I missing some important index?
Can you please advise?

Thanks

---

BTW I just run the statement directly in the DB and it took no time to be executed.

MariaDB [crm_db]> select distinct funcfile from com_vtiger_workflows_expfunctions;
+-----------------------------------------+
| funcfile |
+-----------------------------------------+
| modules/AutoNumberPrefix/wf_autonum.php |
+-----------------------------------------+
1 row in set (0.000 sec)


Based on that I believe is issue is on the code and not in the DB.
What I mean is that the DB answers to the select in no time and then something happens on the corebos application that take 3 minutes before the code/application runs the following select.

Hope this makes sense and helps
Please advise what to check next

Thanks

[joebordes]

how did this turn out? did you find what the issue was?