Introduction

This guide covers the complete process of migrating WHMCS data between installations and resolving common issues. Whether you’re upgrading your WHMCS installation or moving to a new server, this guide will help you navigate the process successfully.

Table of Contents

  1. Understanding WHMCS Database Structure
  2. Data Migration Process
  3. Handling Payment Gateways
  4. Common Issues and Solutions
  5. Post-Migration Verification
  6. Frequently Asked Questions (FAQ)

1. Understanding WHMCS Database Structure

Key Database Tables

  • tblclients: Contains all client information
  • tblorders: Stores order information
  • tblinvoices: Holds invoice data
  • tblinvoiceitems: Contains individual invoice line items
  • tblaccounts: Stores transaction records
  • tblannouncements: System announcements and notifications

Relationships

  • Orders are linked to invoices through invoiceid
  • Invoices are connected to clients through userid
  • Transactions are linked to invoices and clients

2. Data Migration Process

Step 1: Client Data Migration

sql
SELECT * FROM tblclients
WHERE status = 'Active'
ORDER BY id ASC;

Export and import this data first as it’s the foundation for all other records.

Step 2: Invoice Migration

sql
SELECT * FROM tblinvoices
WHERE date >= '2024-01-01'
ORDER BY date ASC;

Important: Maintain referential integrity with client IDs.

Step 3: Order Migration

sql
SELECT * FROM tblorders
WHERE date >= '2024-01-01'
ORDER BY date ASC;

Step 4: Transaction Records

sql
SELECT * FROM tblaccounts
WHERE date >= '2024-01-01'
ORDER BY date ASC;

3. Handling Payment Gateways

Standardizing Payment Methods

sql
UPDATE tblorders
SET paymentmethod = 'Paypal - Payment request'
WHERE date >= '2024-01-01';UPDATE tblinvoices
SET paymentmethod = ‘Paypal – Payment request’
WHERE date >= ‘2024-01-01’;

Payment Gateway Migration Checklist

  1. Export existing payment configurations
  2. Update payment gateway settings in new installation
  3. Verify payment gateway functionality
  4. Test transaction processing

4. Common Issues and Solutions

Dashboard Not Displaying

Check admin permissions:

sql
SELECT a.*, r.name as role_name, r.widgets
FROM tbladmins a
JOIN tbladminroles r ON a.roleid = r.id
WHERE a.username = '[admin_username]';

Reset widget settings:

sql
UPDATE tbladmins
SET homewidgets = 'activity_log:true,getting_started:true,income_forecast:true,income_overview:true,my_notes:true,network_status:true,open_invoices:true,orders_overview:true,supporttickets_overview:true,todo_list:true,whmcs_news:true',
widget_order = ''
WHERE username = '[admin_username]';

Removing Unwanted Announcements

sql
DELETE FROM tblannouncements
WHERE title LIKE '%Thank you for downloading%'
OR announcement LIKE '%nullcave%';

Data Integrity Verification

sql
-- Check invoice-order relationships
SELECT COUNT(*) FROM tblorders o
LEFT JOIN tblinvoices i ON o.invoiceid = i.id
WHERE o.date >= '2024-01-01' AND i.id IS NULL;— Verify transaction records
SELECT COUNT(*) FROM tblaccounts
WHERE date >= ‘2024-01-01’;

5. Post-Migration Verification

Essential Checks

  1. Client Data
    • Verify client count matches
    • Check client login functionality
    • Confirm client permissions
  2. Financial Records
    • Compare invoice totals
    • Verify transaction history
    • Check payment gateway settings
  3. System Settings
    • Confirm admin access levels
    • Verify email templates
    • Check automated functions

6. Frequently Asked Questions (FAQ)

Q: How do I migrate only recent data?

A: Use date filters in your SQL queries:

sql
WHERE date >= '2024-01-01'

Q: What should I do if payment methods don’t match?

A: Standardize payment methods using UPDATE queries:

sql
UPDATE tblorders
SET paymentmethod = 'Paypal - Payment request'
WHERE date >= '2024-01-01';

Q: How can I verify successful migration?

A: Use counting queries to compare records:

sql
-- In old database
SELECT COUNT(*) FROM tblorders WHERE date >= '2024-01-01';— In new database
SELECT COUNT(*) FROM tblorders WHERE date >= ‘2024-01-01’;

Q: What should I do about missing dashboard widgets?

A: Reset widget settings and clear cache:

sql
UPDATE tbladmins
SET homewidgets = 'default_widgets_string',
widget_order = ''
WHERE username = '[admin_username]';

Q: How do I handle unwanted system messages?

A: Clear announcements and check configuration:

sql
DELETE FROM tblannouncements WHERE published = 1;
UPDATE tblconfiguration SET value = '' WHERE setting LIKE '%notice%';

Conclusion

Successful WHMCS migration requires careful planning and attention to detail. Always backup your data before starting the migration process, verify data integrity at each step, and thoroughly test all functionality after migration. Keep this guide handy for troubleshooting common issues and ensuring a smooth transition.

Remember to:

  • Backup all data before starting
  • Maintain table relationships
  • Verify data after each step
  • Test thoroughly after migration
  • Document any custom changes made

This guide will help you navigate the complexities of WHMCS migration while maintaining data integrity and system functionality.