shopdb/sql/archive/migration_phase2

Phase 2: PC Data Migration

Overview

Phase 2 migrates all PC data from the legacy pc table and related tables into the new unified schema created in Phase 1. This phase handles the actual data migration of 277 active PCs and their associated records.

Status: Ready for testing on DEV environment Estimated Time: 10-15 minutes Dependencies: Phase 1 must be completed first

What This Phase Does

Phase 2 migrates data from these legacy tables:

• pc (277 records) → machines table
• pc_network_interfaces (705 records) → communications table
• pc_comm_config → communications table
• pc warranty fields → warranties table
• pc_dualpath_assignments → machinerelationships table

Migration Scripts

Script Execution Order

Run scripts in this exact order:

Script	Purpose	Records	Time
01_migrate_pcs_to_machines.sql	Migrate PC records to machines table	~277 PCs	5-10 min
02_migrate_network_interfaces_to_communications.sql	Migrate network interfaces	~705 interfaces	3-5 min
03_migrate_comm_config_to_communications.sql	Migrate serial/IP comm configs	Variable	2-3 min
04_migrate_warranties.sql	Migrate PC warranty data	Variable	1-2 min
05_migrate_dualpath_assignments.sql	Migrate dualpath machine relationships	Variable	1-2 min
VERIFY_PHASE2_MIGRATION.sql	Comprehensive verification	N/A	1-2 min

Script Details

01_migrate_pcs_to_machines.sql

Critical Script - All other scripts depend on this

Migrates all active PC records from pc table to machines table:

• Creates pc_to_machine_id_mapping table for tracking old→new IDs
• Maps PC types to new machine types:
  • Standard → PC - Standard
  • Shopfloor → PC - Shopfloor
  • Engineer → PC - Engineer
  • Server/VM → PC - Server
  • Laptop → PC - Laptop
• Sets machinenumber = hostname for PCs
• Creates backup: pc_backup_phase2

Key Fields Migrated:

• hostname, loggedinuser, serialnumber
• modelnumberid, osid, machinestatusid, pctypeid
• requires_manual_machine_config
• businessunitid, printerid (if exists)
• mapleft, maptop (if exists)

02_migrate_network_interfaces_to_communications.sql

Migrates PC network interfaces to the unified communications table:

• Uses comstypeid for 'Network_Interface'
• Maps ipaddress → address field
• Preserves: macaddress, subnetmask, defaultgateway
• Preserves: interfacename, isdhcp, ismachinenetwork
• Creates backup: pc_network_interfaces_backup_phase2

03_migrate_comm_config_to_communications.sql

Migrates serial and IP communication configs:

• Maps Serial configs: portid → address, preserves baud/databits/stopbits/parity
• Maps IP configs: ipaddress → address, socketnumber → port
• Combines crlf + additionalsettings into settings field
• Creates backup: pc_comm_config_backup_phase2

04_migrate_warranties.sql

Migrates warranty data from PC table to normalized warranties table:

• Only migrates PCs with warrantyenddate NOT NULL
• Maps fields:
  • warrantyenddate → enddate
  • warrantyservicelevel → servicelevel
  • warrantylastchecked → lastcheckeddate
• warrantyname set to NULL (not in old schema)

05_migrate_dualpath_assignments.sql

Migrates dualpath machine relationships:

• Creates bidirectional relationships:
  • primary_machine → secondary_machine
  • secondary_machine → primary_machine
• Uses relationshiptype = 'Dualpath'
• Joins on machinenumber (hostname) to find machine IDs
• Creates backup: pc_dualpath_assignments_backup_phase2

VERIFY_PHASE2_MIGRATION.sql

Comprehensive verification script with 9 sections:

1. PC to machines migration verification
2. Network interfaces migration verification
3. Comm config migration verification
4. Warranties migration verification
5. Dualpath assignments verification
6. Overall communications summary
7. Relationship summary
8. Backup table verification
9. Final summary and critical issues check

All checks should show status with 0 critical issues.

How to Run

Option A: Run Individual Scripts (Recommended for Testing)

# Connect to MySQL
mysql -u root -p shopdb

# Run scripts in order
source 01_migrate_pcs_to_machines.sql
source 02_migrate_network_interfaces_to_communications.sql
source 03_migrate_comm_config_to_communications.sql
source 04_migrate_warranties.sql
source 05_migrate_dualpath_assignments.sql

# Verify migration
source VERIFY_PHASE2_MIGRATION.sql

Option B: Run All Scripts at Once

# Run all scripts and capture output
mysql -u root -p shopdb < 01_migrate_pcs_to_machines.sql > /tmp/phase2_01.log 2>&1
mysql -u root -p shopdb < 02_migrate_network_interfaces_to_communications.sql > /tmp/phase2_02.log 2>&1
mysql -u root -p shopdb < 03_migrate_comm_config_to_communications.sql > /tmp/phase2_03.log 2>&1
mysql -u root -p shopdb < 04_migrate_warranties.sql > /tmp/phase2_04.log 2>&1
mysql -u root -p shopdb < 05_migrate_dualpath_assignments.sql > /tmp/phase2_05.log 2>&1
mysql -u root -p shopdb < VERIFY_PHASE2_MIGRATION.sql > /tmp/phase2_verify.log 2>&1

# Review logs
cat /tmp/phase2_*.log

Prerequisites

Phase 1 Must Be Complete

Before running Phase 2, verify Phase 1 is complete:

-- Should return 8 tables
SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema = 'shopdb'
AND table_name IN (
    'comstypes', 'communications', 'warranties', 'compliance',
    'relationshiptypes', 'machinerelationships', 'machinestatus'
);

-- Should return rows
SELECT COUNT(*) FROM comstypes;
SELECT COUNT(*) FROM relationshiptypes;

-- machines table should have new columns
DESCRIBE machines;  -- Check for hostname, osid, pctypeid, etc.

Database Backup

CRITICAL: Create a full database backup before running Phase 2:

# Create backup
mysqldump -u root -p shopdb > /tmp/shopdb-before-phase2-$(date +%Y%m%d-%H%M%S).sql

# Verify backup
ls -lh /tmp/shopdb-before-phase2-*.sql

What Gets Created

New Records

• 277 new machine records (PCs migrated to machines table)
• ~705 communication records (network interfaces)
• Variable communication records (serial/IP configs)
• Variable warranty records (PCs with warranty dates)
• Variable relationship records (dualpath assignments × 2)

Mapping Tables

• pc_to_machine_id_mapping: Maps old pcid to new machineid
  • Used by scripts 02-05
  • Used by Phase 3 view updates
  • Used by Phase 4 application updates
  • DO NOT DELETE until migration fully complete

Backup Tables

Phase 2 creates these backup tables:

• pc_backup_phase2 (277 records)
• pc_network_interfaces_backup_phase2 (~705 records)
• pc_comm_config_backup_phase2
• pc_dualpath_assignments_backup_phase2

Verification Checklist

After running all scripts, verify:

1. Record Counts Match

-- PCs migrated
SELECT COUNT(*) FROM pc WHERE isactive = 1;  -- Original count
SELECT COUNT(*) FROM machines WHERE pctypeid IS NOT NULL;  -- Should match

-- Network interfaces migrated
SELECT COUNT(*) FROM pc_network_interfaces WHERE isactive = 1;  -- Original
SELECT COUNT(*) FROM communications c
JOIN comstypes ct ON c.comstypeid = ct.comstypeid
WHERE ct.typename = 'Network_Interface';  -- Should match

-- Mapping table populated
SELECT COUNT(*) FROM pc_to_machine_id_mapping;  -- Should equal PC count

2. No NULL Values in Critical Fields

-- Check for data quality issues
SELECT COUNT(*) FROM machines
WHERE pctypeid IS NOT NULL
  AND (hostname IS NULL OR machinetypeid IS NULL);  -- Should be 0

SELECT COUNT(*) FROM communications
WHERE machineid IS NULL OR comstypeid IS NULL;  -- Should be 0

3. Dualpath Relationships Bidirectional

-- Check for one-way relationships (should be 0)
SELECT COUNT(*) FROM machinerelationships mr1
JOIN relationshiptypes rt ON mr1.relationshiptypeid = rt.relationshiptypeid
WHERE rt.relationshiptype = 'Dualpath'
  AND NOT EXISTS (
    SELECT 1 FROM machinerelationships mr2
    WHERE mr2.machineid = mr1.related_machineid
      AND mr2.related_machineid = mr1.machineid
      AND mr2.relationshiptypeid = mr1.relationshiptypeid
  );

4. All Backup Tables Created

-- Should return 4 rows
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'shopdb'
  AND table_name LIKE '%_backup_phase2';

5. Run Full Verification

mysql -u root -p shopdb < VERIFY_PHASE2_MIGRATION.sql

Review output for:

• All checks showing status
• Critical issues count = 0
• Record counts matching expected values

Rollback Procedures

Full Rollback

If Phase 2 needs to be completely rolled back:

-- 1. Delete migrated PC records from machines
DELETE FROM machines WHERE pctypeid IS NOT NULL;

-- 2. Delete communications from PCs
DELETE FROM communications
WHERE machineid IN (SELECT new_machineid FROM pc_to_machine_id_mapping);

-- 3. Delete warranties for PCs
DELETE FROM warranties
WHERE machineid IN (SELECT new_machineid FROM pc_to_machine_id_mapping);

-- 4. Delete dualpath relationships
DELETE FROM machinerelationships
WHERE relationshiptypeid = (
    SELECT relationshiptypeid FROM relationshiptypes
    WHERE relationshiptype = 'Dualpath'
);

-- 5. Drop mapping table
DROP TABLE IF EXISTS pc_to_machine_id_mapping;

-- 6. Verify original data intact
SELECT COUNT(*) FROM pc WHERE isactive = 1;
SELECT COUNT(*) FROM pc_network_interfaces WHERE isactive = 1;
SELECT COUNT(*) FROM pc_comm_config;

Restore from Database Backup

If full rollback needed:

# Stop applications using database
# ...

# Restore from backup
mysql -u root -p shopdb < /tmp/shopdb-before-phase2-YYYYMMDD-HHMMSS.sql

# Verify restoration
mysql -u root -p shopdb -e "SELECT COUNT(*) FROM pc; SELECT COUNT(*) FROM machines;"

Known Issues and Considerations

1. Hostname as Machine Number

PCs use hostname as machinenumber in the machines table. This differs from other equipment that uses numeric machine numbers (e.g., "M-1234").

Rationale: PCs are identified by hostname in the organization. Using hostname preserves this convention.

2. NULL Warranty Names

The old pc table didn't have a warrantyname field, so migrated PC warranties will have warrantyname = NULL. This is expected and correct.

3. Business Unit Assignment

Script 01 attempts to preserve existing businessunitid if the PC already exists in machines table. Otherwise defaults to businessunitid = 1.

Post-migration: Review and update business unit assignments as needed.

4. Communication Type Mapping

The old schema used string-based configtype ('Serial', 'IP'). The new schema uses foreign keys to comstypes table. Mapping is:

• configtype = 'Serial' → comstypeid for 'Serial'
• configtype = 'IP' → comstypeid for 'IP'
• Network interfaces → comstypeid for 'Network_Interface'

5. MySQL 5.6 Compatibility

All scripts are tested and compatible with MySQL 5.6. Phase 1 script 03 was updated to use FROM DUAL syntax for MySQL 5.6 compatibility.

Testing Recommendations

DEV Environment Testing

1. Create backup before testing
2. Run Phase 2 scripts individually
3. Review each script output for errors
4. Run verification script
5. Test application (check PC pages still work)
6. Test rollback procedure
7. Restore from backup to test restoration
8. Document any issues found

Application Testing

After Phase 2, test these application areas:

• PC list pages
• PC detail pages
• Network interface displays
• Warranty reports
• Dualpath machine displays
• Any reports using PC data

Note: Application code still uses old pc table. Phase 3 and 4 will update views and application code.

Timeline

Phase 2 Execution Timeline

• Script execution: 10-15 minutes
• Verification: 5-10 minutes
• Application testing: 1-2 hours
• Total: ~2-3 hours

Overall Migration Timeline

• Phase 1: Schema changes (COMPLETE)
• Phase 2: Data migration (CURRENT - Ready for testing)
• Phase 3: View updates (Pending)
• Phase 4: Application code updates (Pending)

Estimated total: 20-30 days from design to production deployment

Support and Troubleshooting

Common Issues

Issue: Script 01 fails with mapping error

Symptom: Error joining pc to machines on machinenumber Cause: Duplicate hostnames in pc or machines tables Fix: Investigate duplicates:

-- Find duplicate hostnames in pc
SELECT hostname, COUNT(*)
FROM pc WHERE isactive = 1
GROUP BY hostname HAVING COUNT(*) > 1;

-- Find duplicate hostnames in machines
SELECT hostname, COUNT(*)
FROM machines WHERE isactive = 1
GROUP BY hostname HAVING COUNT(*) > 1;

Issue: Communications migration shows wrong counts

Symptom: Verification shows fewer communications than expected Cause: Missing pc_to_machine_id_mapping entries Fix: Check mapping table:

-- Find unmapped PCs
SELECT p.pcid, p.hostname
FROM pc p
WHERE p.isactive = 1
  AND NOT EXISTS (
    SELECT 1 FROM pc_to_machine_id_mapping m WHERE m.pcid = p.pcid
  );

Issue: Dualpath relationships not bidirectional

Symptom: Verification shows one-way relationships Cause: Script 05 only ran first INSERT, not second Fix: Manually run second INSERT block from script 05

Getting Help

• Review VERIFY_PHASE2_MIGRATION.sql output
• Check backup tables for data comparison
• Review script comments and NOTES sections
• Consult PC_MACHINES_CONSOLIDATION_PLAN.md for design rationale

Next Steps

After Phase 2 is complete and verified:

1. Phase 3: Update 19 views that reference pc table

   • Views need to query machines table instead
   • Some views may need UNION with both tables during transition

2. Phase 4: Update ASP application files (50+ files estimated)

   • Update queries to use machines table
   • Update stored procedures
   • Update reports

3. Production Deployment

   • Schedule maintenance window
   • Execute on production database
   • Monitor application performance

File Manifest

migration_phase2/
├── README.md (this file)
├── 01_migrate_pcs_to_machines.sql (354 lines)
├── 02_migrate_network_interfaces_to_communications.sql (201 lines)
├── 03_migrate_comm_config_to_communications.sql (213 lines)
├── 04_migrate_warranties.sql (165 lines)
├── 05_migrate_dualpath_assignments.sql (178 lines)
└── VERIFY_PHASE2_MIGRATION.sql (562 lines)

Total: 7 files, 1,673 lines

Change Log

Date	Version	Changes
2025-11-06	1.0	Initial Phase 2 scripts created

---

Author: Database Migration Team Date: 2025-11-06 Status: Ready for DEV testing Next Review: After DEV testing complete