whmcs import - errors ocurred?

[cluster]

Debian Buster / PHP 7.4 (fpm) / MariaDB 10.3

WHMCS 7.10.3

Blesta 5.2.1

 

I have tested an import from whmcs with a few errors ...

not imported:
Packages, Services, Affiliates, SupportTickets, Servers, Domain price list

Cients are imported but many inactive are shown as activ, only clients from whmcs with status closed are set to inactive, but both (closed & inactive) needs to be set as inactive in Blesta.

The import completed but the following errors ocurred:

importTransactions: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away on line 196

importPackages: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196

importPackageOptions: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196

importServices: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196

importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196

importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196

importAffiliates: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196

importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196

 

another try w/ debug mode enabled:

importCurrencies took: 0.0036 seconds
-----------------

importInvoices
-----------------
importInvoices took: 185.6431 seconds
-----------------

importTransactions
-----------------
importPackages
-----------------
importPackageOptions
-----------------
importServices
-----------------
importSupportDepartments
-----------------
importSupportTickets
-----------------
importAffiliates
-----------------
importMisc
-----------------
decrypted 0 values using WHMCS' custom algorithm
decrypt took: 0 seconds
total time took: 1026.206 seconds
Array
(
    [error] => Array
        (
            [0] => The import completed but the following errors ocurred:
            [1] => importTransactions: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away on line 196
            [2] => importPackages: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196
            [3] => importPackageOptions: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196
            [4] => importServices: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196
            [5] => importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196
            [6] => importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196
            [7] => importAffiliates: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196
            [8] => importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 196

 

[cluster]

I searched all the whmcs tables and emptied the following which did not have a valid userid
tbloauthserver_access_tokens
tbloauthserver_clients
the same error message is still displayed, only customers and invoices are imported.

I am a little disappointed that there is no feedback here ...
I was able to import everything from whmcs in a wisecp installation (except for cancellations & domains price list).
When will these problems be fixed in the blesta import script?

[Paul]

On 11/30/2021 at 5:56 AM, cluster said:

 [1] => importTransactions: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away on line 196

This error seems to indicate that MySQL crashed, are you still getting this error? If so, see that you meet the "Recommended Requirements" and options for MySQL/MariaDB https://docs.blesta.com/display/user/Requirements

If you get past that error and still have trouble and want to get us a copy of the database securely so we can run a test import and debug in a local environment we will be able to resolve any issues and get an updated importer to you (and delete all copies of the data).

[cluster]

13 hours ago, Paul said:

This error seems to indicate that MySQL crashed, are you still getting this error? If so, see that you meet the "Recommended Requirements" and options for MySQL/MariaDB https://docs.blesta.com/display/user/Requirements

If you get past that error and still have trouble and want to get us a copy of the database securely so we can run a test import and debug in a local environment we will be able to resolve any issues and get an updated importer to you (and delete all copies of the data).

 

Thanks that helped, the timeouts were too low ...
I have adjusted:
wait_timeout = 3600
connect_timeout = 1200
interactive_timeout = 1200

I don't know why but the whmcs date format for all affiliate tables is different from the whmcs date format - so the affiliate page in blesta now shows an error message about wrong date format ...
whmcs date format is set to d/m/Y but the affiliate tables use e.g. 2015-11-12

Is there any sql query which can convert the date format in the affliate tables from Y-m-d to d/m/Y?