SuiteCRM Integration | Lookup By number and call journaling not working suddenly

[rsp#24]

Hello,

Under the CRM Integration, we have Database MySQL configured to lookup by number and also to do call journaling. It was working smooth for 2-3 weeks and again suddenly it stopped working.

When I do TEST, I am getting the below error. We did not change any settings on 3CX as well as on CRM.

Started scenario with empty id

MySqlConnector.MySqlException (0x80004005): Connect Timeout expired.
---> MySqlConnector.MySqlException (0x80004005): Connect Timeout expired.
at MySqlConnector.Core.ServerSession.OpenTcpSocketAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 1131
at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int32 startTickCount, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 438
at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, String logMessage, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 403
at MySqlConnector.Core.ConnectionPool.ConnectSessionAsync(MySqlConnection connection, String logMessage, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 408
at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 98
at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int32 startTickCount, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 128
at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int32 startTickCount, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 929
at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int32 startTickCount, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 959
at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 423
at Integration.Crm.Engine.SqlDatabaseClient.Execute(String statement, CancellationToken token)
at Integration.Crm.Engine.ScenarioProcessorBase.ProcessSQLDatabaseQuery(ISQLClientFactory sqlClientFactory, ScenarioQuery query, IValueManager customManager, CancellationToken token)
at Integration.Crm.Engine.ScenarioProcessorBase.ProcessScenario(ProtocolScenario scenario, IValueManager customManager, CancellationToken token)
at Integration.Crm.Engine.ScenarioProcessorBase.Execute(ProtocolScenario scenario, CancellationToken token)
at Integration.Crm.Engine.CrmProcessor.GetContactInfoFromCrm(List`1 result, IEnumerable`1 scenariosToProcess, IValueManager customManager, Func`2 filterResultsFunctor, CancellationToken token)
at Integration.Crm.Engine.CrmProcessor.GetContactInfo(String contactNumber, InteractionTypes interactionType, CancellationToken token)

Please help to resolve this issue.

 

[rsp#24]

The server's firewall is inactive too.

 

[TheodorosG_3CX]

There is a disconnection from your SQL. You need to check from the SQL side first.

 

[rsp#24]

I don't see any error log files for MySQL in the server. Are there any other places I should check it.

Do I have to reconfigure CRM integration on the 3CX?

 

[rsp#24]

Anything wrong in the below statements?

# Lookup By Number SQL Statement:

SELECT c.id AS contactid,last_name AS lastname,first_name AS firstname,phone_work AS phonebusiness,phone_mobile AS phonemobile,phone_other AS phoneother,phone_home AS phonehome,acc.name AS companyname FROM contacts as c JOIN accounts_contacts as ac JOIN accounts as acc WHERE (replace(replace(replace(replace(replace(phone_work, ' ', ''), '-', ''), '+', ''),'(',''),')','') = '[Number]' OR replace(replace(replace(replace(replace(phone_mobile, ' ', ''), '-', ''), '+', ''),'(',''),')','') = '[Number]' OR replace(replace(replace(replace(replace(phone_other, ' ', ''), '-', ''), '+', ''),'(',''),')','') ='[Number]' OR replace(replace(replace(replace(replace(phone_home, ' ', ''), '-', ''), '+', ''),'(',''),')','') = '[Number]') AND c.id = ac.contact_id AND ac.account_id = acc.id AND acc.deleted = 0;

# Call Journaling SQL Statement:

insert into suitecrm.log_3cx_call (CallType,Number,CallDirection,Name,EntityId,EntityType,Agent,AgentEmail,Duration,CallStartTimeLocal,CallEndTimeLocal,CallStartTimeUTC, CallEndTimeUTC) values ('[CallType]','[Number]','[CallDirection]','[Name]','[EntityId]','[EntityType]','[Agent]','[AgentEmail]','[Duration]','[CallStartTimeLocal]','[CallEndTimeLocal]','[CallStartTimeUTC]','[CallEndTimeUTC]');

 

[TheodorosG_3CX]

This is not something that i can confirm only by checking the statement however your error previously is related with network disconnection.

 

[rsp#24]

Okay! I am trying to find out root cause.

 

[rsp#24]

How to find out 3CX's public IP address?

 

[TheodorosG_3CX]

This is under the Information section in your Dashboard;

 

[rsp#24]

Thank you!

Now, when I am using my SELECT query under 'Lookup By Number SQL Statement:', I am getting an SQL connection error.

But when I am using it under 'Search Contacts SQL Statement:' then I am getting the below message. What's wrong here?

CRM Integration is working OK but contact number does not exist. Check log for more info or try a different number.

Started scenario with empty id

Statement:

Response text: []
Response finished
Finished scenario with empty id

I have that number into the CRM. What is an issue here and how to fix it?

 

[rsp#24]

It is working now.

But, I am still struggling with INSERT query which we're using for the call journaling.

 

[rsp#24]

I think our INSERT statement stopped working after the suggested update.

https://www.3cx.com/blog/news/sql-database-integration-update/

FYI, I have updated INSERT query as per new required way.

Could anyone please help to solve this INSERT query issue?

Thank you!

 

[rsp#24]

Downgraded the 3CX version and it started working!!!