Migration Issue

[pratkal]

Hello Blesta Team & Community,

 

We are having some issue migrating from WHMCS to Blesta. 

 

We are migrating from 7.5 to Blesta and using inbuild plugin import Manager 

 

We have successfully Imported Clients / Packages / Invoices / Modules but some part of migration is not completed

 

 

1- No Domains/hosting is migrated 

2- Module Solusvm Server is not migrated 

 

Please advice we are stuck on this process for nearly a day now

[Himax]

On 11/7/2021 at 3:06 PM, pratkal said:

Hello Blesta Team & Community,

 

We are having some issue migrating from WHMCS to Blesta. 

 

We are migrating from 7.5 to Blesta and using inbuild plugin import Manager 

 

We have successfully Imported Clients / Packages / Invoices / Modules but some part of migration is not completed

 

 

1- No Domains/hosting is migrated 

2- Module Solusvm Server is not migrated 

 

Please advice we are stuck on this process for nearly a day now

No answers since november 2021? It's April 2022 today.

I'm also stuck in a pre-flight test of WHMCS to Blesta. May issue is a duplicate entry for a primary key importing transactions. Anything below that fails because of the failed transaction above:

```

importInvoices
-----------------
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-519' for key 'PRIMARY'
importTransactions
-----------------
There is already an active transaction
importPackages
-----------------
There is already an active transaction
importPackageOptions
-----------------
importPackageOptions took: 0.1337 seconds
-----------------

....

Array
(
    [error] => Array
        (
            [0] => The import completed but the following errors ocurred:
            [1] => importInvoices: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-519' for key 'PRIMARY' on line 196
            [2] => importTransactions: There is already an active transaction on line 231
            [3] => importPackages: There is already an active transaction on line 231
            [4] => importServices: There is already an active transaction on line 231
            [5] => importSupportDepartments: There is already an active transaction on line 231
            [6] => importSupportTickets: There is already an active transaction on line 231
            [7] => importAffiliates: There is already an active transaction on line 231
            [8] => importMisc: There is already an active transaction on line 231
        )

)

```

Now I can't find this 1-519 key in my whmcs dump. Is there a better log/debug or shall I enable the mysql detailed log here?

Thanks.

[ChristopherB]

May be worth submitting a ticket or joining the discord! Not many users come here :)

[Paul]

It sounds like there might be an invoice with duplicate number or ID. Since it probably failed at that point, it would be useful to take a look in the invoices table to see what the most recent invoice imported was. Then compare to WHMCS and see which invoice is next. That one is likely the culprit.

[Himax]

  

Hi Paul, I'll try to reply, in hope of an anwer.

All invoices seem to have been imported, including their detail lines.

I traced the mysql statements issued by the migration tool. It tries to insert twice the same row in the "invoices_recur_created" table (here is invoice id 521 while in the previous example it was 519).

637226 Query    INSERT INTO `invoices_recur_created` (`invoice_recur_id`, `invoice_id`) VALUES ('1', '521')
637226 Query    INSERT INTO `invoices_recur_created` (`invoice_recur_id`, `invoice_id`) VALUES ('1', '521')

Here the invoices_recurcreated table has an unique primary index over "invoice_recur_id+invoice_id".

Investigating further, it looks like the issue happens on a customer with "billable items" that were invoiced on multiple invoices, please see the attached image That is, WHMCS allows you to create a generic billable item and to specify that it has to be billed multiple times. So WHMCS will bill the item on multiple invoices at the predefined schedule. Then you can see on which invoices it was billed. So you have a back-reference or link from the invoices to the billable item. My guess is that the import module is trying to create this recurring billable item multiple times.

Perhaps it's enough to "ignore" the error or to check if the recurring item is already present and act accordingly.

Let me know, as this is blocking for me. I'd like to proceed with a paid license.

Thanks.

[Paul]

On 5/11/2022 at 6:18 AM, Himax said:

  

Hi Paul, I'll try to reply, in hope of an anwer.

All invoices seem to have been imported, including their detail lines.

I traced the mysql statements issued by the migration tool. It tries to insert twice the same row in the "invoices_recur_created" table (here is invoice id 521 while in the previous example it was 519).

637226 Query    INSERT INTO `invoices_recur_created` (`invoice_recur_id`, `invoice_id`) VALUES ('1', '521')
637226 Query    INSERT INTO `invoices_recur_created` (`invoice_recur_id`, `invoice_id`) VALUES ('1', '521')

Here the invoices_recurcreated table has an unique primary index over "invoice_recur_id+invoice_id".

Investigating further, it looks like the issue happens on a customer with "billable items" that were invoiced on multiple invoices, please see the attached image That is, WHMCS allows you to create a generic billable item and to specify that it has to be billed multiple times. So WHMCS will bill the item on multiple invoices at the predefined schedule. Then you can see on which invoices it was billed. So you have a back-reference or link from the invoices to the billable item. My guess is that the import module is trying to create this recurring billable item multiple times.

Perhaps it's enough to "ignore" the error or to check if the recurring item is already present and act accordingly.

Let me know, as this is blocking for me. I'd like to proceed with a paid license.

Thanks.

Thanks for the update. Tagging @Jono so he can take a look.

If you are willing to provide basic steps on how to create a billable item to generate multiple invoices in WHMCS as you indicated, we can try to reproduce for a test import.

[Himax]

On 5/14/2022 at 1:05 AM, Paul said:

Thanks for the update. Tagging @Jono so he can take a look.

If you are willing to provide basic steps on how to create a billable item to generate multiple invoices in WHMCS as you indicated, we can try to reproduce for a test import.

To generate a generic recurring billing item for a customer (client) in WHMCS, that will be billed multiple times on multiple invoices you follow this procedure:

1. In the client profile page, you go to the "Billable Items" tab.
2. Click on the "+ Add billable Item" link (on the right) and put a generic description, amount etc.
3. In the "invoice action "select "Recur every <N> <days/weeks/months etc> for <Y> times". Leave "invoice count" to 0 (this is the counter for WHMCS to keep track of the number of invoices generated already.
4. Save changes.

WHMCS will generate multiple invoices for this billing item, up to the selected number of times. This happens on the cron run, once per day at the predefined schedule. It might be possible to select the item and  click to "invoice now". It works for single-shot billable items. I have not tested if it works on items invoiced multiple times.

As invoices are generated, this billing item will move to the "Invoiced Items" list on the same page. This is a table that contains links to the invoice numbers. A multiple invoiced item will have multiple invoice links to each invoice.

Background: I use this whmcs feature to invoice customer-specific jobs without the need to create a package, create an order etc. Mostly useful for ad-hoc consultancy work where I bill the customer a fixed amount per month on a regular basis, for work that does not apply to other customers. Or, when I need the customer to pay a bigger amount in multiple installments (i.e. say 1000 USD, 250 USD/month in 4 months). This way they get a single invoice, reminders, overdue notices on each installment.

Le me know if you need more info.

Thank you.

[Himax]

Any news?

[Jono]

Hi there, just to throw in an update.  I'm testing this yesterday and today.  Unable to reproduce by simply doing an import with a billable item with multiple invoices.  I'll look more into the code and your report to see what specific set of circumstances causes the error.

[Jono]

On 5/20/2022 at 3:05 AM, Himax said:

Any news?

On plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php line 751 change

                    $this->local->insert('invoices_recur_created', $vars);

To

                    $this->local->duplicate('invoice_recur_id', '=', $recurring_id)->insert('invoices_recur_created', $vars);

tblinvoiceitems must have records with a duplicate invoiceid-relid pair.  Not sure if there's a valid reason for that.

[Himax]

13 hours ago, Jono said:

On plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php line 751 change

                    $this->local->insert('invoices_recur_created', $vars);

To

                    $this->local->duplicate('invoice_recur_id', '=', $recurring_id)->insert('invoices_recur_created', $vars);

tblinvoiceitems must have records with a duplicate invoiceid-relid pair.  Not sure if there's a valid reason for that.

So, this fixed the immediate problem and the import was able to be completed. I'll check now the imported data for other issues.

On the issue of multiple invoiceid-relid pairs, I can confirm that I have a lot of these invoices. The duplicated invoiceid-relid pairs are on multiple levels, and here's the list:

• Setup charges of the same type of service when multiple services of the same type are grouped in the same invoice.
• Multiple (monthly) periods of the same recurring service chargned on the same invoice
• Multiple occurrences of a recurring billing item billed on the same invoice

In general, when multiple periods of the same service are grouped in the same invoice you'll get a duplicate invoiceid-relid pair in table tblinvoiceitems.

Are you confident that the fix above and the import in general will deal with these occurrences appropriately?

Thank you.