FRH Dave Posted October 23, 2013 Report Share Posted October 23, 2013 Similar results, but without MySQL crashing: The import completed but the following errors ocurred: importServices: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module_row_id' cannot be null on line 124 importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 To make absolutely certain I was starting with a clean environment, I re-imaged the VPS, performed an update, installed WHM + cPanel, set up my cPanel account, installed Blesta (re-downloaded just in case), installed the import module from the first page, installed the WHMCS plugin from the first page (may be redundant?), imported my data from WHMCS. It took only a minute. I'm on the console right now and see no immediate signs of trouble on the server. The results are the same as before except for the error messages above: Looks like all clients were imported, looks like all packages were created, but only the first client got any services (and not all services for that client carried over). Quote Link to comment Share on other sites More sharing options...
Cody Posted October 23, 2013 Author Report Share Posted October 23, 2013 Similar results, but without MySQL crashing: The import completed but the following errors ocurred: importServices: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module_row_id' cannot be null on line 124 This error hints at the importer not being able to determine the module row used for the service. Check to see that all of the modules were imported and all contain at least some data. If you can figure out the exact service that's attempted and triggers this error we might be able to investigate a little. Aside from that the only thing to do is to skip the service: Add the following to line 818 of /plugins/import_manager/components/migrators/whmcs/whmcs_migrator.php if (!$module_row_id) continue; importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 These are just reflections of the original error above. Michael 1 Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 23, 2013 Report Share Posted October 23, 2013 Similar results, but without MySQL crashing: The import completed but the following errors ocurred: importServices: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module_row_id' cannot be null on line 124 importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 To make absolutely certain I was starting with a clean environment, I re-imaged the VPS, performed an update, installed WHM + cPanel, set up my cPanel account, installed Blesta (re-downloaded just in case), installed the import module from the first page, installed the WHMCS plugin from the first page (may be redundant?), imported my data from WHMCS. It took only a minute. I'm on the console right now and see no immediate signs of trouble on the server. The results are the same as before except for the error messages above: Looks like all clients were imported, looks like all packages were created, but only the first client got any services (and not all services for that client carried over). Ugh, I kind of wish someone else was having the troubles I am. Any chance you can shoot me a copy of your my.cnf and php.ini? Are you importing a copy of your full whmcs database, or are you using a smaller test database? email: my username @lithiumhosting.com Biscuit1001 1 Quote Link to comment Share on other sites More sharing options...
Cody Posted October 23, 2013 Author Report Share Posted October 23, 2013 Ugh, I kind of wish someone else was having the troubles I am. Any chance you can shoot me a copy of your my.cnf and php.ini? Are you importing a copy of your full whmcs database, or are you using a smaller test database? email: my username @lithiumhosting.com My guess is you've got a lot of data. Seems like there's some correlation between a lot of data and this particular error given that you generally get about the same number of clients imported before the connection is reset. Try this. Update line 157 of whmcs_migrator.php: from: foreach ($this->WhmcsClients->get() as $client) { to: $clients = $this->WhmcsClients->get()->fetchAll(); foreach ($clients as $client) { This will, undoubtedly, require you to set your memory_limit in PHP to something very high (all dependent on how much data you have). But it's worth a shot. Michael 1 Quote Link to comment Share on other sites More sharing options...
FRH Dave Posted October 23, 2013 Report Share Posted October 23, 2013 This error hints at the importer not being able to determine the module row used for the service. Check to see that all of the modules were imported and all contain at least some data. If you can figure out the exact service that's attempted and triggers this error we might be able to investigate a little. WHMCS package #1: cPanel user account, user #1 - made it to Blesta, assigned to correct user WHMCS package #9: cPanel user account, user #4 - package created in Blesta, but not assigned to any user WHMCS package #11: cPanel user account, user #6 - package created in Blesta, but not assigned to any user ... the rest of the packages have similar results. If I sort my WHMCS packages by username, starting with user #1: WHMCS package #1: cPanel user account, user #1 - made it to Blesta, assigned to correct user WHMCS package #121: TheSSLStore module, RapidSSL, user #1 - package created in Blesta, but not assigned to any user, no module assigned WHMCS package #118: TheSSLStore module, Comodo Essential, user #1 - package created in Blesta, but not assigned to any user, no module assigned ... the rest of the packages have similar results. Does the importer process entries by username, or by the actual sequence in the database? If by username, then I'm guessing it's failing on the RapidSSL product / TheSSLStore module. I can try again using the modification you posted above, unless you want me to try troubleshooting this. All the cPanel packages were correctly assigned to the cPanel module. I haven't verified, but it looks like the hash came over correctly. All of my domains were correctly assigned to the Logicboxes module. My NetEarthOne information appears correct in this module. All of my SolusVM packages and colocation packages were assigned to the Universal module. Blesta created one product label in the Universal Module for each VPS node, which appears to also store the access keys. All of my dedicated servers were not assigned to any module. Quote Link to comment Share on other sites More sharing options...
FRH Dave Posted October 23, 2013 Report Share Posted October 23, 2013 Ugh, I kind of wish someone else was having the troubles I am. Any chance you can shoot me a copy of your my.cnf and php.ini? Are you importing a copy of your full whmcs database, or are you using a smaller test database? email: my username @lithiumhosting.com Sent! I'm importing the full database. Or at least, trying to. Quote Link to comment Share on other sites More sharing options...
Cody Posted October 23, 2013 Author Report Share Posted October 23, 2013 WHMCS package #1: cPanel user account, user #1 - made it to Blesta, assigned to correct user WHMCS package #9: cPanel user account, user #4 - package created in Blesta, but not assigned to any user WHMCS package #11: cPanel user account, user #6 - package created in Blesta, but not assigned to any user ... the rest of the packages have similar results. If I sort my WHMCS packages by username, starting with user #1: WHMCS package #1: cPanel user account, user #1 - made it to Blesta, assigned to correct user WHMCS package #121: TheSSLStore module, RapidSSL, user #1 - package created in Blesta, but not assigned to any user, no module assigned WHMCS package #118: TheSSLStore module, Comodo Essential, user #1 - package created in Blesta, but not assigned to any user, no module assigned ... the rest of the packages have similar results. Does the importer process entries by username, or by the actual sequence in the database? If by username, then I'm guessing it's failing on the RapidSSL product / TheSSLStore module. I can try again using the modification you posted above, unless you want me to try troubleshooting this. The importer processes items in batches. That is, all clients are imported, then it moves on to importing all contacts, etc. So it's something like Clients > Modules > Packages > Services. It can't import a service until it has imported both the client and the package; and it can't import a package until it has the module. So I guess to clarify what I wrote earlier, what is the highest number service ID that is imported? By the time services are imported you should already have all of your clients, contacts, modules, and packages. All the cPanel packages were correctly assigned to the cPanel module. I haven't verified, but it looks like the hash came over correctly. All of my domains were correctly assigned to the Logicboxes module. My NetEarthOne information appears correct in this module. All of my SolusVM packages and colocation packages were assigned to the Universal module. Blesta created one product label in the Universal Module for each VPS node, which appears to also store the access keys. Yup, that will happen because the SolusVM module created by SolusVM is a bit odd and would take quite a bit of time to reverse engineer (it's 100% ioncube encoded... why, I don't know). All of my dedicated servers were not assigned to any module. How are dedicated services stored in WHMCS? That is, what module do you use? My guess is this is where you're running into trouble. Quote Link to comment Share on other sites More sharing options...
FRH Dave Posted October 23, 2013 Report Share Posted October 23, 2013 So I guess to clarify what I wrote earlier, what is the highest number service ID that is imported? By the time services are imported you should already have all of your clients, contacts, modules, and packages. The highest service number is #1 in Blesta, which corresponds to #1 in WHMCS. There is only one service showing in Blesta. It appears all the clients are there, as well as all of the packages. How are dedicated services stored in WHMCS? That is, what module do you use? My guess is this is where you're running into trouble. I use Autorelease, which is their version of your Universal module. It's possible it's failing on the dedicated servers. That would explain it. But user #1 in WHMCS doesn't have any dedicated servers; only shared, IPs, SSLs, and some other things. If it's crashing on dedicated servers, wouldn't the rest of those services come over? Quote Link to comment Share on other sites More sharing options...
Cody Posted October 23, 2013 Author Report Share Posted October 23, 2013 The highest service number is #1 in Blesta, which corresponds to #1 in WHMCS. There is only one service showing in Blesta. It appears all the clients are there, as well as all of the packages. I use Autorelease, which is their version of your Universal module. Interesting. We'll take a look. It's possible it's failing on the dedicated servers. That would explain it. But user #1 in WHMCS doesn't have any dedicated servers; only shared, IPs, SSLs, and some other things. If it's crashing on dedicated servers, wouldn't the rest of those services come over? Normally, yes, but this particular error throws an exception, which halts all further process for that particular batch. If you do as I suggested (edit line 818), it should continue processing services and skip those it can't find module row records for. Michael 1 Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 23, 2013 Report Share Posted October 23, 2013 I got a new error. I made some more mysql and php changes and restarted the import. I get this error now: It comes up after the import has supposedly completed but doesn't say the usual import successful message. I only did a partial import, the services and everything are commented out as I am still troubleshooting. If this is because one person didn't have a last name specified, maybe the error can be less of an error and just an info notice with a link to the user so we can modify them. I'm trying the import with a larger data set now, including invoices and transactions. Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 23, 2013 Report Share Posted October 23, 2013 Sent! I'm importing the full database. Or at least, trying to. It didn't come through. its just my forum username @lithiumhosting.com Thanks for your help. Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 23, 2013 Report Share Posted October 23, 2013 It's working!!! It did all clients, contacts, invoices and invoice line items. It takes for freaking ever though. I'm going to start over and do a run through with everything and see how it goes. I did a search and replace in the WHMCS database for empty first and last names which fixed the previous error. Here's my my.cnf file. I have to admit, I don't know what all of the config options do, but I found this example on a site claiming to be a very optimized config file. Some of the vars were different than what I had and it's working now so I'm ok with it. Anyone with more mysql server experience might be able to slim this down or tweak it a bit more. [mysqld] back_log = 75 max_connections = 500 key_buffer = 384M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 1800 thread_cache_size = 384 wait_timeout = 7200 connect_timeout = 10 tmp_table_size = 64M max_heap_table_size = 64M max_allowed_packet = 64M max_connect_errors = 1000 read_rnd_buffer_size = 524288 bulk_insert_buffer_size = 8M query_cache_limit = 4M query_cache_size =128M query_cache_type = 1 query_prealloc_size = 65536 query_alloc_block_size = 131072 default-storage-engine = MyISAM innodb_buffer_pool_size=100M innodb_file_per_table=1 innodb_stats_on_metadata=0 log_error=/var/log/mysql/mysqld.log log_warnings=2 [safe_mysqld] pid-file="/var/lib/mysql/mysql.pid" nice = -5 open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M [mysql.server] user=mysql Quote Link to comment Share on other sites More sharing options...
FRH Dave Posted October 23, 2013 Report Share Posted October 23, 2013 After the code modification described above, the import appears mostly successful. I'm going to go through and audit the clients / services, but at first glance, it appears that anything using the WHMCS Autorelease module is not being imported. The package is created in Blesta, but it is not assigned to the customer. EDIT: Many if not all of the prices are set to $0.00. EDIT 2: The prices are correct when viewed from Packages > Browse, but not when viewed from Clients > (select client) > Manage Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 23, 2013 Report Share Posted October 23, 2013 My guess is you've got a lot of data. Seems like there's some correlation between a lot of data and this particular error given that you generally get about the same number of clients imported before the connection is reset. Try this. Update line 157 of whmcs_migrator.php: from: foreach ($this->WhmcsClients->get() as $client) { to: $clients = $this->WhmcsClients->get()->fetchAll(); foreach ($clients as $client) { This will, undoubtedly, require you to set your memory_limit in PHP to something very high (all dependent on how much data you have). But it's worth a shot. This most likely made things start working. That combined with my MySQL changes above. After the code modification described above, the import appears mostly successful. I'm going to go through and audit the clients / services, but at first glance, it appears that anything using the WHMCS Autorelease module is not being imported. The package is created in Blesta, but it is not assigned to the customer. EDIT: Many if not all of the prices are set to $0.00. This will be a problem for me too. Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 24, 2013 Report Share Posted October 24, 2013 Ugh, The import completed but the following errors ocurred: importServices: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'module_row_id' cannot be null on line 124 importSupportDepartments: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 importSupportTickets: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 importMisc: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 124 execution time: 7931.23912191 seconds (132 minutes) Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 24, 2013 Report Share Posted October 24, 2013 I'll do more digging to see what I can find wrong with the import but I did notice that all domain tlds were imported multiple times with different modules. I don't see any difference between the different tld entries except the module they are linked to. I also have issues changing views. On the Invoices page, when I click on Past Due, it changes the url to /admin/billing/invoices/past_due/ but the content doesn't change. If I refresh the page, it loads the past_due invoices. This is true for the products page too where the domains were listed. Edit: It imported 4 whole client services. Clicking tabs on the manage service page also require a refresh to load the content. This is frustrating in itself. Only a few hosting products where imported fully including pricing terms. The prices are all set to 0.00 even for monthly and annual payment terms. Quote Link to comment Share on other sites More sharing options...
FRH Dave Posted October 24, 2013 Report Share Posted October 24, 2013 Any thoughts on the price issue? I was about to say I can live with manually importing the autorelease module, but I can't. Those products need to renew on their current dates, and from what I can tell, Blesta has no way to start a product in the future on a specific date. This would be necessary so that no charges are generated before the renewal date except as specified by the invoice settings. Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 24, 2013 Report Share Posted October 24, 2013 I just realized that no support tickets, departments or anything came over. Also, support is missing from the header and the support manager does not appear to be installed / activated. Edit: I found the cause of the issue with tabs not working as expected. By having System.benchmark set to true, the tabs were broken. This is an easy thing to replicate. Kind of an odd thing to happen. Quote Link to comment Share on other sites More sharing options...
Cody Posted October 24, 2013 Author Report Share Posted October 24, 2013 I'll do more digging to see what I can find wrong with the import but I did notice that all domain tlds were imported multiple times with different modules. I don't see any difference between the different tld entries except the module they are linked to. This is expected. WHMCS stores pricing based on TLD, whereas Blesta only stores pricing based on package. With Blesta you can select multiple TLDs for a single package and define a specific pricing. Packages for domain registrars are non-existent in WHMCS so Blesta has to create them as one TLD per registrar to ensure that your pricing per TLD per registrar remains the same. Quote Link to comment Share on other sites More sharing options...
Cody Posted October 24, 2013 Author Report Share Posted October 24, 2013 Only a few hosting products where imported fully including pricing terms. The prices are all set to 0.00 even for monthly and annual payment terms. Can you send me a dump of your tbldomainpricing, tblpricing, and tblcurrencies tables? Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 24, 2013 Report Share Posted October 24, 2013 This is expected. WHMCS stores pricing based on TLD, whereas Blesta only stores pricing based on package. With Blesta you can select multiple TLDs for a single package and define a specific pricing. Packages for domain registrars are non-existent in WHMCS so Blesta has to create them as one TLD per registrar to ensure that your pricing per TLD per registrar remains the same. I guess I don't follow. Each TLD product shows pricing for 1-10 years. They are all identical... Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 24, 2013 Report Share Posted October 24, 2013 Can you send me a dump of your tbldomainpricing, tblpricing, and tblcurrencies tables? PM sent Quote Link to comment Share on other sites More sharing options...
Cody Posted October 24, 2013 Author Report Share Posted October 24, 2013 I guess I don't follow. Each TLD product shows pricing for 1-10 years. They are all identical... You have multiple registrars. Each Blesta package can only be assigned to 1 registrar, therefore you need at minimum 1 package per registrar. However, because WHMCS allows setting different prices per TLD you need a different package for each TLD to ensure that the package assigned to the service for that TLD has the correct pricing. If you had the same price for each TLD then it would be theoretically possible to assign them all to the same package, but the importer doesn't go through all the trouble trying to determine that. It's certainly possible to tweak the importer to produce one package per registrar with all TLDs, though I suspect the majority of people won't be able to do that. That said, we're looking to introduce a plugin in Blesta that will simplify pricing for TLDs, since they are so different than most other packages. This will essentially be an option to allow the package to defer to the module for pricing as opposed to the pricing being defined at the package level. Quote Link to comment Share on other sites More sharing options...
tsiedsma Posted October 24, 2013 Report Share Posted October 24, 2013 You have multiple registrars. Each Blesta package can only be assigned to 1 registrar, therefore you need at minimum 1 package per registrar. However, because WHMCS allows setting different prices per TLD you need a different package for each TLD to ensure that the package assigned to the service for that TLD has the correct pricing. If you had the same price for each TLD then it would be theoretically possible to assign them all to the same package, but the importer doesn't go through all the trouble trying to determine that. It's certainly possible to tweak the importer to produce one package per registrar with all TLDs, though I suspect the majority of people won't be able to do that. That said, we're looking to introduce a plugin in Blesta that will simplify pricing for TLDs, since they are so different than most other packages. This will essentially be an option to allow the package to defer to the module for pricing as opposed to the pricing being defined at the package level. I disagree... Quote Link to comment Share on other sites More sharing options...
Cody Posted October 24, 2013 Author Report Share Posted October 24, 2013 I disagree... So you think it should only import packages for TLDs that are assigned to a specific registrar? I wasn't sure if it was a requirement to assign a registrar to a specific TLD in order for the TLD to be orderable, but I guess it is. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.