Whmcs Migrator (Beta) - Updated 2013-11-12

[FRH Dave]

That makes it look like you didn't start off with a fresh database. As of b5, the migrator decodes department names, so that department predates your latest import. I actually ran into a similar issue with my test environment because I made a back up of the database after I ran only select migration routines.

 

That's very strange, because I took a SQL backup immediately after installing Blesta.  After my last trial run I restored that database.  I'll delete the databases, reinstall, and try again.  Hopefully that's all it is!

 

EDIT:  Sheesh.  That was it.  So much for my mad cPanel skills.  Everything is fine, nothing to see here.

[Paul]

That's very strange, because I took a SQL backup immediately after installing Blesta.  After my last trial run I restored that database.  I'll delete the databases, reinstall, and try again.  Hopefully that's all it is!

 

EDIT:  Sheesh.  That was it.  So much for my mad cPanel skills.  Everything is fine, nothing to see here.

 

Curious if you're still seeing any issues with ticket assignments.

[tsiedsma]

So... I've got some initial stats on the migrator.

 

On my machine each DB write takes .03 seconds on average, so 1000 records = 30 seconds. Hopefully your machine is faster, but likely your dataset is much larger. Just something to keep in mind.

The biggest slowdown is WHMCS' custom cipher algorithm. In case you weren't aware, WHMCS encrypts credit card numbers using AES (good for them), but they encrypt everything else using their stupid custom encryption algorithm which is nothing more than a glorified caesar cipher. Well, we cracked the algorithm a while ago and implemented it as part of the importer and it turns out it's slow as molasses.

 

 

 

 

That's 0.383 seconds per string! In contrast, our AES encryption takes 0.0007 seconds per string.

 

WHMCS' insecure custom algorithm takes 550 times longer to run than 256-bit AES.

 

 

EDIT: Factoid, the full import took 39.2339 seconds. Over 25% of the time spent was attempting to decrypt WHMCS encrypted data.

 

Have you benchmarked WHMCS decryption using the WHMCS decrypt function?  Just curious if you found that their function was faster or slower than what you came up with.

If you take the whmcs/includes/functions.php file, you can run the decrypt function.  You'll need the $cc_encryption_hash variable defined before running the function as they appear to globalize that variable and use it in the function.  I know this because it doesn't work without it in the script.

 

 

<?php
include('whmcs/includes/functions.php');
$cc_encryption_hash = '1234';
$password = decrypt($encryptedpassword);
?>

Edit:

You probably already know this and have even seen the source since a quick search on your favorite pastebin type site for anything WHMCS related returns lots of results for decoded files and functions...

[FRH Dave]

Curious if you're still seeing any issues with ticket assignments.

 

Nope, looks like that was the problem.

 

It looks like the only problem I'm facing now is that SolusVM packages are coming over using the Universal module instead of the SolusVM module.

[Cody]

Have you benchmarked WHMCS decryption using the WHMCS decrypt function?  Just curious if you found that their function was faster or slower than what you came up with.

If you take the whmcs/includes/functions.php file, you can run the decrypt function.  You'll need the $cc_encryption_hash variable defined before running the function as they appear to globalize that variable and use it in the function.  I know this because it doesn't work without it in the script.

 

 

<?php
include('whmcs/includes/functions.php');
$cc_encryption_hash = '1234';
$password = decrypt($encryptedpassword);
?>

Edit:

You probably already know this and have even seen the source since a quick search on your favorite pastebin type site for anything WHMCS related returns lots of results for decoded files and functions...

 

 

Total Time (ours): 1.4387562274933 seconds

Total Time (theirs): 1.7466928958893 seconds

Total iterations: 1000

 

Looks like ours is 20% faster.

 

I was wrong about the original benchmarks, however. Their algorithm is not terribly inefficient (despite it being terribly insecure). I've updated the debugging in the next rev to accurately reflect the proper calculations.

 

<?php
include_once "./functions.php";

// Have to define variable with this name since WHMCS imports it via global
$cc_encryption_hash = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";

$x = "this is my secret string";
$y = encrypt($x);

$total1 = 0;
$total2 = 0;
$iterations = 1000;
for ($i=0; $i<$iterations; $i++) {
    $start1 = microtime(true);
    decryptData($y, $cc_encryption_hash);
    $total1 += (microtime(true) - $start1);
    
    $start2 = microtime(true);
    decrypt($y);
    $total2 += (microtime(true) - $start2);
}

echo "Total Time (ours): " . $total1 . " seconds<br />";
echo "Total Time (theirs): " . $total2 . " seconds<br />";
echo "Total iterations: " . $iterations . "<br />";


function decryptData($str, $key) {
    #
    # Get the source from the migrator
    #
}
?>

[Paul]

Nope, looks like that was the problem.

 

It looks like the only problem I'm facing now is that SolusVM packages are coming over using the Universal module instead of the SolusVM module.

 

I remember talking to Cody about SolusVM mapping and it sounded challenging due to the differences in how they do things so we skipped over it initially. We'll be taking another look at this

[tsiedsma]

Looks like ours is 20% faster.

 

I was wrong about the original benchmarks, however. Their algorithm is not terribly inefficient (despite it being terribly insecure). I've updated the debugging in the next rev to accurately reflect the proper calculations.

 

LOL, I love this.  Your function that you hacked together to decrypt their data is faster than the one they use.  That just proves how much better at this you are than them.

[FRH Dave]

I remember talking to Cody about SolusVM mapping and it sounded challenging due to the differences in how they do things so we skipped over it initially. We'll be taking another look at this

 

Great.  I should be able to assign them manually, but I'd rather have everything jump in automatically.

[FRH Dave]

Getting ready to make my final switch.  The instructions recommend starting with a blank database.  Does this apply to configurations as well?  I'm going to leave the plans alone until after the switch, but what about setting the company name, setting email templates, etc?

[Paul]

Some configurations may be overwritten by the importer. You should be ok changing the company name, and modifying email templates though.

 

However, I would suggest doing the import on a fresh install, then going through all settings prior to going live. If you want to have 2 installs, configure things how you want, and then compare/copy information over post-import to your imported install, we can issue a dev license.

[tsiedsma]

Is there any update on the outstanding issues?  

[Cody]

Is there any update on the outstanding issues?  

 

What issues are you experiencing?

[tsiedsma]

I had a migration running for most of the day, it completed with an error "Please enter a last name".

execution time: 26777.006063 seconds (7.43806 hours) LOL

 

That was a full database import, it did get the packages but not the client packages.

It also didn't get the support tickets this time.

 

 

I just ran another import, well it just finished anyways. Even with your default values and the fact that I set all empty last names to Smith, I still got the error:

 

 

 

Almost everything imported for my test group.

A couple of issues.

 

• Servers are listed but show 0 accounts.
  • Editing and saving the server details causes the number of accounts to populate
• Server default name servers were not imported
• Server Label and Server Hostname are the same, WHMCS has Server Name and Server Hostname which I would expect to be imported respectively.
• Support stuff came over but there is no Support option in the header even though the support manager is installed.
  • (Default Admin group doesn't get assigned permissions to the support manager)
• Some packages have odd pricing.  I'm seeing some with duplicate terms like Monthly but with two prices and only one is accurate.
• Some packages have quantity available set to zero even though WHMCS had stock control unchecked.
• All tickets came over as Open and the Ticket Number doesn't show up in the Ticket list.
  • 

[Cody]

I just ran another import, well it just finished anyways. Even with your default values and the fact that I set all empty last names to Smith, I still got the error:

 

 

The "please enter a last name" error is the result of having no last name set for a particular payment account. This is fixed in b5, I believe, where "unknown" will be set for the name if none given.

 

If you're still having an issue with this update whmcs_migrator.php (line 54 - 67) like so and enable debugging:

 

        $actions = array(
            "importStaff", // works
            "importClients", // works
            #"importContacts", // works
            #"importTaxes", // works
            #"importCurrencies", // works
            #"importInvoices", // works
            #"importTransactions", // works
            #"importPackages", // works
            #"importServices", // works
            #"importSupportDepartments", // works
            #"importSupportTickets", // works
            #"importMisc" // works
        );

[tsiedsma]

I am using the latest version of the importer, after I received that error I checked my whmcs database and couldn't find any missing first or last names.

Not sure where the error came from but it happened twice.

 

The other issue is the length of time the importer takes.  Perhaps it's because of how WHMCS does things, maybe because of how you do things.

I watched the queries running on the server and it appears that when you apply transactions there are temporary tables created and this takes a boatload of time.

 

I have yet to see a complete import even with a seriously reduced WHMCS database.

[Cody]

Almost everything imported for my test group.

A couple of issues.

 

• Servers are listed but show 0 accounts.
  • Editing and saving the server details causes the number of accounts to populate

 

That's expected.

 

• Server default name servers were not imported

 

That's correct.

 

• Server Label and Server Hostname are the same, WHMCS has Server Name and Server Hostname which I would expect to be imported respectively.

 

For what module(s)?

 

• Support stuff came over but there is no Support option in the header even though the support manager is installed.
  • (Default Admin group doesn't get assigned permissions to the support manager)

 

Install the Support Manager plugin before you run the importer, or set permissions after the importer finishes.

 

• Some packages have odd pricing.  I'm seeing some with duplicate terms like Monthly but with two prices and only one is accurate.

 

Examples of what you're seeing? For domains or products/services (in WHMCS terminology)?

 

Package pricings come straight from the tblpricing table. You shouldn't have any prices defined in Blesta that aren't defined in your tblpricing table.

 

 

• Some packages have quantity available set to zero even though WHMCS had stock control unchecked.

 

Fixed in the next release. To patch change line 845 of whmcs_migrator.php to:

 

                'qty' => $product->stockcontrol == "on" ? $product->qty : null,

 

 

• All tickets came over as Open and the Ticket Number doesn't show up in the Ticket list.
  • 

 

Tickets numbers in Blesta must be numeric. To extract only numeric values from ticket number change line 1167 of whmcs_migrator.php to:

 

                'code' => is_numeric($ticket->tid) ? (int)$ticket->tid : preg_replace("/[^0-9]+/", "", $ticket->tid),

 

Ticket status mappings were fixed in b6.

 

b7 with the above changes coming soon.

[Cody]

I am using the latest version of the importer, after I received that error I checked my whmcs database and couldn't find any missing first or last names.

Not sure where the error came from but it happened twice.

 

The other issue is the length of time the importer takes.  Perhaps it's because of how WHMCS does things, maybe because of how you do things.

I watched the queries running on the server and it appears that when you apply transactions there are temporary tables created and this takes a boatload of time.

 

I have yet to see a complete import even with a seriously reduced WHMCS database.

 

Judging by the time it takes to run, I'd say your WHMCS database contains about 900,000 records, or your server is slower than my 3 GHz Pentium D (circa 2005). You might consider importing into a database on a separate server than you are importing from. That is, if WHMCS database is on server1, have Blesta database on server2. This will help split the CPU/disk load. Though this really only makes sense if both server1 and server2 are on the same LAN, or are connected through a 100mbps+ connection. You might also consider performing the import on faster hardware (CPU/disk).

 

Blesta does not create temporary tables at all, so sounds like you might also have something else running on the same machine slowing things down.

[Paul]

temp tables may be created automatically by mysql

[Cody]

B7 is now ready for download.

 

Also, another tip for speed improvement is to comment out or remove line 66 from whmcs_migrator.php:

 

            #"importMisc" // works

 

The "importMisc" method imports email log data, configurations, calendar events, and todo events. So there's a lot of inserts there that may not be necessary. We have a small data set, obviously, but 26% of the import time is spent importing that data. Your results may vary, obviously.

[tsiedsma]

temp tables may be created automatically by mysql

 

You're right, I did some digging and there is nothing in the blesta code that does any of the advanced stuff I saw in the SQL logs.  Either way, the queries were definitely related to processing transactions which took a majority of the time.

Since my WHMCS license is owned, I may keep it around as an archive.  I can always modify the client area pages in Blesta to pull in data via the WHMCS API such as invoice history and stuff so I don't have to worry so much about importing historic data.

[tsiedsma]

Judging by the time it takes to run, I'd say your WHMCS database contains about 900,000 records, or your server is slower than my 3 GHz Pentium D (circa 2005). You might consider importing into a database on a separate server than you are importing from. That is, if WHMCS database is on server1, have Blesta database on server2. This will help split the CPU/disk load. Though this really only makes sense if both server1 and server2 are on the same LAN, or are connected through a 100mbps+ connection. You might also consider performing the import on faster hardware (CPU/disk).

 

Blesta does not create temporary tables at all, so sounds like you might also have something else running on the same machine slowing things down.

 

I'm gonna try it on my computer instead of my dev VPS.  Any chance I can get a Dev license for Blesta without having an active license?

[Paul]

I'm gonna try it on my computer instead of my dev VPS.  Any chance I can get a Dev license for Blesta without having an active license?

 

You'll need an active license, but, nothing is stopping you from signing up for another trial license. As long as it's on a different domain/subdomain/host, during installation, selecting 30 day free trial should get you going.

[tsiedsma]

Ugh, I can't even get that to work on XAMPP on Windows.

 

Is there a way to find out what the problem is?

 

 

I figured I would let my core i7 do the heavy lifting, then copy the database to the server to see how things look.

[Michael]

Ugh, I can't even get that to work on XAMPP on Windows.

 

Is there a way to find out what the problem is?

 

 

You need to edit your c://windows/system32/drivers/etc/hosts file and put your hostname to your IP I believe.

[tsiedsma]

You need to edit your c://windows/system32/drivers/etc/hosts file and put your hostname to your IP I believe.

 

Why can't I just connect to http://localhost/blesta ???

I'm trying to start a new trial to just do the import with my core i7 and SSD drive so I have no performance related issues.