Import SQL data on an existing server

When to use this: you setup UXWizz on a new server, but forgot to import your old data. The new server already has new data, so you can't directly import old data, because the incremental IDs will clash.

This page explains how you can import SQL data that contains incremental IDs in an existing, already-populated database.

Some tips in the migration tutorial could help for this too.

Steps to import old SQL data in a UXWizz server that already has data:

1. Old server: Purge old data (to make the DB smaller)

   • Run the queries from limit record/heatmaps 60 days cron scripts.

2. Old server: Export old.sql DB from phpMyAdmin

   • Only export data, no table structure (otherwise manually replace CREATE TABLE with CREATE TABLE IF NOT EXISTS into the exported SQL file)

   • Don't export indexes (or manually remove Index creations section in the SQL file)

3. New server: Add a large value to all IDs in the new database (see SQL script below)

4. New server: Backup of current SQL file on the new server

5. New server: Import old.sql data.

SQL script to increase all IDs (in order to make room for old data)

This script was tested on UXWizz version 8.5.0. If a new version introduced any database structure changes, you might have to update the script to include all missing tables/IDs.

-- Change 10million to a larger value (up to 99 million) if you had more data stored

-- Disable FK checks
SET FOREIGN_KEY_CHECKS=0;
SET @offset = 10000000;

START TRANSACTION;

-- Primary keys
UPDATE ust_users SET id = id + @offset;
UPDATE ust_clients SET id = id + @offset;
UPDATE ust_clientpage SET id = id + @offset;
UPDATE ust_client_tag SET id = id + @offset;
UPDATE ust_client_event SET id = id + @offset;
UPDATE ust_clicks SET id = id + @offset;
UPDATE ust_movements SET id = id + @offset;
UPDATE ust_records SET id = id + @offset;
UPDATE ust_partials SET id = id + @offset;
UPDATE ust_access SET id = id + @offset;
UPDATE ust_user_client_ip_label SET id = id + @offset;
UPDATE ust_limits SET id = id + @offset;
UPDATE ust_ab SET id = id + @offset;

-- Foreign keys
UPDATE ust_access SET userid = userid + @offset;
UPDATE ust_user_client_ip_label SET userid = userid + @offset;
UPDATE ust_user_client_watched 
    SET userid = userid + @offset,
        clientid = clientid + @offset;
UPDATE ust_clientpage SET clientid = clientid + @offset;
UPDATE ust_client_tag SET clientid = clientid + @offset;
UPDATE ust_client_event 
    SET clientid = clientid + @offset,
        clientpageid = clientpageid + @offset;
UPDATE ust_clicks SET client = client + @offset;
UPDATE ust_movements SET client = client + @offset;
UPDATE ust_records SET client = client + @offset;
UPDATE ust_partials SET client = client + @offset;

-- === Reset AUTO_INCREMENT for each table ===

-- ust_users
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_users;
SET @sql = CONCAT('ALTER TABLE ust_users AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_clients
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_clients;
SET @sql = CONCAT('ALTER TABLE ust_clients AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_clientpage
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_clientpage;
SET @sql = CONCAT('ALTER TABLE ust_clientpage AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_client_tag
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_client_tag;
SET @sql = CONCAT('ALTER TABLE ust_client_tag AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_client_event
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_client_event;
SET @sql = CONCAT('ALTER TABLE ust_client_event AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_clicks
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_clicks;
SET @sql = CONCAT('ALTER TABLE ust_clicks AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_movements
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_movements;
SET @sql = CONCAT('ALTER TABLE ust_movements AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_records
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_records;
SET @sql = CONCAT('ALTER TABLE ust_records AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_partials
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_partials;
SET @sql = CONCAT('ALTER TABLE ust_partials AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_access
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_access;
SET @sql = CONCAT('ALTER TABLE ust_access AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_user_client_ip_label
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_user_client_ip_label;
SET @sql = CONCAT('ALTER TABLE ust_user_client_ip_label AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_limits
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_limits;
SET @sql = CONCAT('ALTER TABLE ust_limits AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ust_ab
SELECT GREATEST(IFNULL(MAX(id), 0) + 1, @offset) INTO @next_id FROM ust_ab;
SET @sql = CONCAT('ALTER TABLE ust_ab AUTO_INCREMENT = ', @next_id);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

COMMIT;

-- Re-enable FK checks
SET FOREIGN_KEY_CHECKS=1;