Datenbank-Migration:
MySQL 5.x zu 8.0

Mit folgendem PHP-Code lässt sich eine MySQL-Datenbank von Version 5.x zu 8.0 migrieren, so wie das bei manchen Webhosting-Anbietern wie 1blu aktuell notwendig ist:

• IP-Adresse, Tabellen-Prefix und Datenbank-Zugangsdaten hinterlegen
• PHP-Datei im Root-Verzeichnis des Webspaces als db_migrate.php hochladen
• Preflight Check durchführen
• Bei WordPress-Websites Wartungsmodus über den Button Enable Maintenance aktivieren
• Datenbank kopieren und überprüfen
• Datenbank-Zugangsdaten in der wp-config.php auf die neue Datenbank umstellen
• Website / WordPress gründlich testen
• db_migrate.php wieder löschen

<?php
error_reporting(E_ALL);
ini_set('display_errors', 0);
ini_set('max_execution_time', 120);

if (($_SERVER['REMOTE_ADDR'] ?? '') !== 'XXX.XXX.XXX.XXX') {
    http_response_code(403);
    exit('Forbidden');
}

const TABLE_PREFIX = 'XXXXX_';

$databases = [
    'old' => [
        'host'    => 'XXXXXXXXXXXXX',
        'user'    => 'XXXXXXXXXXXXX',
        'pass'    => 'XXXXXXXXXXXXX',
        'name'    => 'XXXXXXXXXXXXX',
        'charset' => 'utf8mb4',
    ],
    'new' => [
        'host'    => 'XXXXXXXXXXXXX',
        'user'    => 'XXXXXXXXXXXXX',
        'pass'    => 'XXXXXXXXXXXXX',
        'name'    => 'XXXXXXXXXXXXX',
        'charset' => 'utf8mb4',
    ],
];

function get_old(array $databases): mysqli {
    $db = $databases['old'];
    $conn = new mysqli($db['host'], $db['user'], $db['pass'], $db['name']);
    if ($conn->connect_errno) {
        throw new RuntimeException("OLD DB connection failed: " . $conn->connect_error);
    }
    $conn->set_charset($db['charset']);
    $conn->query("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
    try { $conn->query("SET SESSION sql_mode = ''"); } catch (Throwable $e) {}
    return $conn;
}

function get_new(array $databases): mysqli {
    $db = $databases['new'];
    $conn = new mysqli($db['host'], $db['user'], $db['pass'], $db['name']);
    if ($conn->connect_errno) {
        throw new RuntimeException("NEW DB connection failed: " . $conn->connect_error);
    }
    $conn->set_charset($db['charset']);
    $conn->query("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
    try { $conn->query("SET SESSION sql_mode = ''"); } catch (Throwable $e) {}
    try { $conn->query("SET SESSION innodb_strict_mode = 0"); } catch (Throwable $e) {}
    return $conn;
}

function json_out(array $data): never {
    header('Content-Type: application/json');
    echo json_encode($data, JSON_UNESCAPED_UNICODE);
    exit;
}

function adapt_ddl_for_mysql8(string $ddl, bool $isView = false): string {
    if ($isView) {
        $ddl = preg_replace('/\bDEFINER\s*=\s*\S+\s*/i', '', $ddl);
        $ddl = preg_replace('/\bSQL\s+SECURITY\s+DEFINER\b/i', 'SQL SECURITY INVOKER', $ddl);
        return $ddl;
    }
    // Upgrade MEDIUMINT/INT UNSIGNED → BIGINT UNSIGNED to prevent overflow
    // when migrating across MySQL versions (auto-increment IDs, FKs, composite PKs)
    $ddl = preg_replace('/\b(MEDIUMINT|INT)\(\d+\)\s+unsigned/i', 'BIGINT unsigned', $ddl);
    $ddl = preg_replace('/\b(MEDIUMINT|INT)\s+unsigned/i', 'BIGINT unsigned', $ddl);
    // Strip remaining integer display widths deprecated in MySQL 8.0
    $ddl = preg_replace('/\b(TINYINT|SMALLINT|BIGINT)\(\d+\)/i', '$1', $ddl);
    return $ddl;
}

// ── API Actions ──────────────────────────────────────────────────

if (isset($_GET['action'])) {
    try {
        switch ($_GET['action']) {

            case 'maintenance': {
                $enable = ($_GET['enable'] ?? '1') === '1';
                $file = __DIR__ . '/.maintenance';
                if ($enable) {
                    file_put_contents($file, "<?php \$upgrading = time(); ?>\n");
                    json_out(['ok' => true, 'enabled' => true]);
                } else {
                    if (file_exists($file)) @unlink($file);
                    json_out(['ok' => true, 'enabled' => false]);
                }
            }

            case 'preflight': {
                $checks = [];
                $allOk = true;

                try {
                    $old = get_old($databases);
                    $cs = $old->character_set_name();
                    $checks[] = ['name' => 'OLD connection (utf8mb4)', 'ok' => $cs === 'utf8mb4', 'detail' => "charset: $cs"];

                    $r = $old->query("SELECT @@max_allowed_packet");
                    $pkt = ($r && $rw = $r->fetch_row()) ? (int)$rw[0] : 0;
                    $checks[] = ['name' => 'OLD max_allowed_packet', 'ok' => $pkt >= 1048576,
                                 'detail' => number_format($pkt / 1048576, 1) . ' MB'];

                    $r = $old->query("SELECT @@sql_mode");
                    $sm = ($r && $rw = $r->fetch_row()) ? $rw[0] : '';
                    $checks[] = ['name' => 'OLD sql_mode', 'ok' => true, 'detail' => $sm ?: '(empty)'];

                    $r = $old->query("SHOW TABLES LIKE '" . $old->real_escape_string(TABLE_PREFIX) . "%'");
                    $tc = $r ? $r->num_rows : 0;
                    $checks[] = ['name' => 'OLD tables (' . TABLE_PREFIX . '*)', 'ok' => $tc > 0, 'detail' => "$tc tables"];

                    $r = $old->query("SELECT CHARACTER_SET_NAME, COUNT(*) as cnt
                        FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = '" . $old->real_escape_string($databases['old']['name']) . "'
                          AND TABLE_NAME LIKE '" . $old->real_escape_string(TABLE_PREFIX) . "%'
                          AND CHARACTER_SET_NAME IS NOT NULL
                        GROUP BY CHARACTER_SET_NAME ORDER BY cnt DESC");
                    $csList = [];
                    if ($r) while ($rw = $r->fetch_assoc()) $csList[] = $rw['CHARACTER_SET_NAME'] . ': ' . $rw['cnt'];
                    $checks[] = ['name' => 'OLD column charsets', 'ok' => true, 'detail' => implode(', ', $csList) ?: 'none'];

                    $old->close();
                } catch (Throwable $e) {
                    $checks[] = ['name' => 'OLD connection', 'ok' => false, 'detail' => $e->getMessage()];
                    $allOk = false;
                }

                try {
                    $new = get_new($databases);
                    $cs = $new->character_set_name();
                    $checks[] = ['name' => 'NEW connection (utf8mb4)', 'ok' => $cs === 'utf8mb4', 'detail' => "charset: $cs"];

                    $r = $new->query("SELECT @@max_allowed_packet");
                    $pkt = ($r && $rw = $r->fetch_row()) ? (int)$rw[0] : 0;
                    $checks[] = ['name' => 'NEW max_allowed_packet', 'ok' => $pkt >= 1048576,
                                 'detail' => number_format($pkt / 1048576, 1) . ' MB'];

                    $r = $new->query("SELECT @@sql_mode");
                    $sm = ($r && $rw = $r->fetch_row()) ? $rw[0] : '';
                    $checks[] = ['name' => 'NEW sql_mode', 'ok' => true, 'detail' => $sm ?: '(empty)'];

                    $new->close();
                } catch (Throwable $e) {
                    $checks[] = ['name' => 'NEW connection', 'ok' => false, 'detail' => $e->getMessage()];
                    $allOk = false;
                }

                foreach ($checks as $c) { if (!$c['ok']) $allOk = false; }
                json_out(['ok' => true, 'all_ok' => $allOk, 'checks' => $checks]);
            }

            case 'list_tables': {
                $old = get_old($databases);
                $res = $old->query("SHOW FULL TABLES LIKE '" . $old->real_escape_string(TABLE_PREFIX) . "%'");
                $baseTables = [];
                $views = [];
                while ($row = $res->fetch_row()) {
                    $isView = (strtoupper($row[1]) === 'VIEW');
                    $cnt = 0;
                    if (!$isView) {
                        $cntRes = $old->query("SELECT COUNT(*) FROM `{$row[0]}`");
                        $cnt = (int)($cntRes ? $cntRes->fetch_row()[0] : 0);
                    }
                    $entry = [
                        'name'    => $row[0],
                        'rows'    => $cnt,
                        'is_view' => $isView,
                    ];
                    if ($isView) {
                        $views[] = $entry;
                    } else {
                        $baseTables[] = $entry;
                    }
                }
                $old->close();
                json_out(['ok' => true, 'tables' => array_merge($baseTables, $views)]);
            }

            case 'create_table': {
                $table = $_GET['table'] ?? '';
                if (!$table || strpos($table, TABLE_PREFIX) !== 0) {
                    json_out(['ok' => false, 'error' => 'Invalid table name']);
                }

                $old = get_old($databases);
                $new = get_new($databases);
                $new->query("SET foreign_key_checks = 0");

                $res = $old->query("SHOW CREATE TABLE `" . $old->real_escape_string($table) . "`");
                if (!$res) {
                    json_out(['ok' => false, 'error' => 'Cannot read structure: ' . $old->error]);
                }
                $row = $res->fetch_assoc();
                $isView = isset($row['Create View']);
                $ddl = $isView ? $row['Create View'] : $row['Create Table'];

                $ddl = adapt_ddl_for_mysql8($ddl, $isView);

                if ($isView) {
                    $new->query("DROP VIEW IF EXISTS `" . $new->real_escape_string($table) . "`");
                } else {
                    $new->query("DROP TABLE IF EXISTS `" . $new->real_escape_string($table) . "`");
                }

                if (!$new->query($ddl)) {
                    json_out(['ok' => false, 'error' => 'CREATE failed: ' . $new->error, 'is_view' => $isView]);
                }

                $old->close();
                $new->close();
                json_out(['ok' => true, 'table' => $table, 'is_view' => $isView]);
            }

            case 'copy_batch': {
                $table  = $_GET['table']  ?? '';
                $offset = (int)($_GET['offset'] ?? 0);
                $batch  = (int)($_GET['batch']  ?? 500);
                if (!$table || strpos($table, TABLE_PREFIX) !== 0) {
                    json_out(['ok' => false, 'error' => 'Invalid table name']);
                }

                $old = get_old($databases);
                $new = get_new($databases);
                $new->query("SET foreign_key_checks = 0");
                $new->query("SET unique_checks = 0");

                $pkRes = $old->query("SHOW KEYS FROM `" . $old->real_escape_string($table) . "` WHERE Key_name = 'PRIMARY'");
                $pkCols = [];
                if ($pkRes) {
                    while ($pkRow = $pkRes->fetch_assoc()) {
                        $pkCols[(int)$pkRow['Seq_in_index']] = '`' . $pkRow['Column_name'] . '`';
                    }
                    ksort($pkCols);
                }
                $orderBy = !empty($pkCols) ? ' ORDER BY ' . implode(',', $pkCols) : '';

                $res = $old->query("SELECT * FROM `" . $old->real_escape_string($table) . "`$orderBy LIMIT $offset, $batch");
                if (!$res) {
                    json_out(['ok' => false, 'error' => 'SELECT failed: ' . $old->error]);
                }

                $numFields = $res->field_count;

                $fields = $res->fetch_fields();
                $numericTypes = [
                    MYSQLI_TYPE_TINY, MYSQLI_TYPE_SHORT, MYSQLI_TYPE_LONG,
                    MYSQLI_TYPE_LONGLONG, MYSQLI_TYPE_INT24,
                    MYSQLI_TYPE_FLOAT, MYSQLI_TYPE_DOUBLE, MYSQLI_TYPE_DECIMAL,
                    MYSQLI_TYPE_NEWDECIMAL,
                ];
                $binaryIdx = [];
                foreach ($fields as $i => $f) {
                    if ($f->charsetnr === 63 && !in_array($f->type, $numericTypes)) {
                        $binaryIdx[$i] = true;
                    }
                }

                $copied = 0;
                $valueSets = [];
                $new->begin_transaction();

                while ($row = $res->fetch_row()) {
                    $vals = [];
                    for ($i = 0; $i < $numFields; $i++) {
                        if ($row[$i] === null) {
                            $vals[] = 'NULL';
                        } elseif (isset($binaryIdx[$i])) {
                            $vals[] = "X'" . bin2hex($row[$i]) . "'";
                        } else {
                            $vals[] = "'" . $new->real_escape_string($row[$i]) . "'";
                        }
                    }
                    $valueSets[] = '(' . implode(',', $vals) . ')';
                    $copied++;

                    if (count($valueSets) >= 50) {
                        $sql = "INSERT INTO `" . $new->real_escape_string($table) . "` VALUES " . implode(',', $valueSets);
                        if (!$new->query($sql)) {
                            $new->rollback();
                            json_out([
                                'ok' => false,
                                'error' => 'INSERT failed at offset ' . ($offset + $copied - count($valueSets)) . ': ' . $new->error,
                            ]);
                        }
                        $valueSets = [];
                    }
                }

                if (count($valueSets) > 0) {
                    $sql = "INSERT INTO `" . $new->real_escape_string($table) . "` VALUES " . implode(',', $valueSets);
                    if (!$new->query($sql)) {
                        $new->rollback();
                        json_out([
                            'ok' => false,
                            'error' => 'INSERT failed at offset ' . ($offset + $copied - count($valueSets)) . ': ' . $new->error,
                        ]);
                    }
                }

                $new->commit();

                $old->close();
                $new->close();
                json_out([
                    'ok'     => true,
                    'table'  => $table,
                    'offset' => $offset,
                    'copied' => $copied,
                    'done'   => $copied < $batch,
                ]);
            }

            case 'verify': {
                $old = get_old($databases);
                $new = get_new($databases);

                $res = $old->query("SHOW TABLES LIKE '" . $old->real_escape_string(TABLE_PREFIX) . "%'");
                $results = [];
                $allGood = true;

                while ($row = $res->fetch_row()) {
                    $table = $row[0];

                    $oldCount = $old->query("SELECT COUNT(*) FROM `$table`")->fetch_row()[0];

                    $chk = $new->query("SHOW TABLES LIKE '" . $new->real_escape_string($table) . "'");
                    if ($chk->num_rows === 0) {
                        $results[] = [
                            'table'    => $table,
                            'old_rows' => (int)$oldCount,
                            'new_rows' => null,
                            'match'    => false,
                            'error'    => 'Table missing in NEW DB',
                        ];
                        $allGood = false;
                        continue;
                    }

                    $newCount = $new->query("SELECT COUNT(*) FROM `$table`")->fetch_row()[0];
                    $match = ((int)$oldCount === (int)$newCount);
                    if (!$match) $allGood = false;

                    $entry = [
                        'table'    => $table,
                        'old_rows' => (int)$oldCount,
                        'new_rows' => (int)$newCount,
                        'match'    => $match,
                    ];

                    if ($match && (int)$oldCount > 0) {
                        $pkRes = $old->query("SHOW KEYS FROM `$table` WHERE Key_name = 'PRIMARY'");
                        $pkCol = null;
                        if ($pkRes && ($pkRow = $pkRes->fetch_assoc())) {
                            $pkCol = $pkRow['Column_name'];
                        }
                        if ($pkCol) {
                            $esc = $old->real_escape_string($pkCol);
                            $oldInfo = $old->query("SELECT MIN(`$esc`) as mn, MAX(`$esc`) as mx FROM `$table`");
                            $newInfo = $new->query("SELECT MIN(`$esc`) as mn, MAX(`$esc`) as mx FROM `$table`");
                            if ($oldInfo && $newInfo) {
                                $ov = $oldInfo->fetch_assoc();
                                $nv = $newInfo->fetch_assoc();
                                $rangeMatch = ($ov['mn'] === $nv['mn'] && $ov['mx'] === $nv['mx']);
                                $entry['pk_range_match'] = $rangeMatch;
                                if (!$rangeMatch) {
                                    $allGood = false;
                                    $entry['old_pk_range'] = $ov['mn'] . '..' . $ov['mx'];
                                    $entry['new_pk_range'] = $nv['mn'] . '..' . $nv['mx'];
                                }
                            }
                        }
                    }

                    $results[] = $entry;
                }

                $old->close();
                $new->close();
                json_out(['ok' => true, 'all_good' => $allGood, 'tables' => $results]);
            }

            case 'verify_serialized': {
                $target = $_GET['db'] ?? 'new';
                $conn = ($target === 'old') ? get_old($databases) : get_new($databases);

                $checkTargets = [
                    TABLE_PREFIX . 'options'  => ['option_value', "option_value LIKE 'a:%' OR option_value LIKE 'O:%'", 'option_name'],
                    TABLE_PREFIX . 'postmeta' => ['meta_value',   "meta_value LIKE 'a:%' OR meta_value LIKE 'O:%'",    'meta_key'],
                    TABLE_PREFIX . 'usermeta' => ['meta_value',   "meta_value LIKE 'a:%' OR meta_value LIKE 'O:%'",    'meta_key'],
                ];
                $totalChecked = 0;
                $totalFailed  = 0;
                $failedEntries = [];

                foreach ($checkTargets as $tbl => [$col, $where, $nameCol]) {
                    $chk = $conn->query("SHOW TABLES LIKE '" . $conn->real_escape_string($tbl) . "'");
                    if (!$chk || $chk->num_rows === 0) continue;

                    $r = $conn->query("SELECT `$nameCol`, `$col` FROM `$tbl`
                        WHERE $where ORDER BY LENGTH(`$col`) DESC LIMIT 200");
                    if (!$r) continue;

                    while ($rw = $r->fetch_row()) {
                        $totalChecked++;
                        if (@unserialize($rw[1]) === false && $rw[1] !== 'b:0;') {
                            $totalFailed++;
                            if (count($failedEntries) < 20) {
                                $failedEntries[] = [
                                    'table'   => $tbl,
                                    'key'     => $rw[0],
                                    'size'    => strlen($rw[1]),
                                    'preview' => mb_substr($rw[1], 0, 120),
                                ];
                            }
                        }
                    }
                }

                $conn->close();
                json_out([
                    'ok'      => true,
                    'db'      => $target,
                    'checked' => $totalChecked,
                    'failed'  => $totalFailed,
                    'entries' => $failedEntries,
                ]);
            }

            default:
                json_out(['ok' => false, 'error' => 'Unknown action']);
        }
    } catch (Throwable $e) {
        json_out(['ok' => false, 'error' => $e->getMessage()]);
    }
}

$maintenanceActive = file_exists(__DIR__ . '/.maintenance');
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>DB Migration &mdash; OLD &rarr; NEW</title>
<style>
  * { box-sizing: border-box; }
  body {
    font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
    margin: 2em; color: #222; background: #f8f8f8;
  }
  h1 { border-bottom: 2px solid #333; padding-bottom: .3em; }
  .controls { margin-bottom: 1.2em; display: flex; align-items: center; gap: .7em; flex-wrap: wrap; }
  button {
    padding: .5em 1.2em; font-size: .95em; border-radius: 6px; border: none;
    color: #fff; cursor: pointer;
  }
  button:disabled { background: #999 !important; cursor: not-allowed; }
  #preflightBtn { background: #2c3e50; }
  #startBtn     { background: #1a5276; }
  #resyncBtn    { background: #d35400; }
  #verifyBtn    { background: #27ae60; }
  #verifySerBtn { background: #16a085; }
  #maintBtn     { background: <?= $maintenanceActive ? '#c0392b' : '#8e44ad' ?>; }
  label { font-size: .95em; }
  input[type=number] { width: 80px; padding: .3em; border-radius: 4px; border: 1px solid #aaa; }
  .progress-bar {
    width: 100%; background: #ddd; border-radius: 4px; height: 26px;
    margin-bottom: 1em; overflow: hidden;
  }
  .progress-fill {
    height: 100%; background: #27ae60; transition: width 0.3s;
    text-align: center; color: #fff; font-size: .85em; line-height: 26px;
    min-width: 40px;
  }
  #log {
    background: #fff; border: 1px solid #ddd; border-radius: 6px;
    padding: 1em; max-height: 55vh; overflow-y: auto; font-size: .88em;
    font-family: "SF Mono", Menlo, Consolas, monospace; white-space: pre-wrap;
  }
  .ok  { color: #27ae60; }
  .err { color: #c0392b; font-weight: bold; }
  .inf { color: #2c3e50; }
  .dim { color: #888; }
  .verify-table {
    border-collapse: collapse; width: 100%; margin-top: .5em; font-size: .88em;
  }
  .verify-table th, .verify-table td {
    border: 1px solid #ddd; padding: 5px 10px; text-align: left;
  }
  .verify-table th { background: #eee; }
  .row-ok  { background: #eafaf1; }
  .row-bad { background: #fdedec; }
  .warn { color: #888; font-size: .85em; margin-top: 2em; }
</style>
</head>
<body>
<h1>Database Migration: OLD &rarr; NEW</h1>
<p class="inf">Only tables with prefix <code>u3zo_</code> will be copied.
Connection charset: <code>utf8mb4</code>. The OLD database is read-only (SELECT only).</p>

<div class="controls">
  <button id="preflightBtn" onclick="runPreflight()">Preflight Check</button>
  <button id="startBtn" onclick="startMigration()">Start Full Copy</button>
  <button id="resyncBtn" onclick="resyncMismatches()">Re-sync Mismatches</button>
  <button id="verifyBtn" onclick="runVerify()">Verify Copy</button>
  <button id="verifySerBtn" onclick="runVerifySerialized()">Verify Serialized</button>
  <button id="maintBtn"
          data-enabled="<?= $maintenanceActive ? '1' : '0' ?>"
          onclick="toggleMaintenance()">
    <?= $maintenanceActive ? 'Disable Maintenance' : 'Enable Maintenance' ?>
  </button>
  <label>Batch: <input type="number" id="batchSize" value="500" min="50" max="5000"></label>
</div>

<div class="progress-bar"><div class="progress-fill" id="progress" style="width:0%">0 %</div></div>
<div id="log"></div>

<p class="warn">Delete this file immediately after migration. It contains database credentials.</p>

<script>
const logEl       = document.getElementById('log');
const progress    = document.getElementById('progress');
const actionBtns  = document.querySelectorAll('button[id]');

function setAllButtons(disabled) {
    actionBtns.forEach(b => { if (b.id !== 'maintBtn') b.disabled = disabled; });
}

function log(msg, cls = 'inf') {
    logEl.innerHTML += '<span class="' + cls + '">' + msg + '</span>\n';
    logEl.scrollTop = logEl.scrollHeight;
}

function pct(p, etaSec) {
    const v = Math.round(p);
    progress.style.width = Math.max(v, 2) + '%';
    let text = v + ' %';
    if (etaSec !== undefined && etaSec > 0 && isFinite(etaSec)) {
        const m = Math.floor(etaSec / 60);
        const s = Math.ceil(etaSec % 60);
        text += m > 0 ? (' \u2014 ~' + m + 'm ' + s + 's left') : (' \u2014 ~' + s + 's left');
    }
    progress.textContent = text;
}

async function api(params) {
    const qs = new URLSearchParams(params).toString();
    const r = await fetch('db_migrate.php?' + qs);
    if (!r.ok) throw new Error('HTTP ' + r.status);
    return r.json();
}

// ── Preflight ────────────────────────────────────────────────

async function runPreflight() {
    setAllButtons(true);
    logEl.innerHTML = '';
    pct(0);
    log('Running preflight checks...\n');

    let res;
    try { res = await api({ action: 'preflight' }); }
    catch (e) { log('NETWORK ERROR: ' + e.message, 'err'); setAllButtons(false); return; }
    if (!res.ok) { log('ERROR: ' + res.error, 'err'); setAllButtons(false); return; }

    for (const c of res.checks) {
        const icon = c.ok ? '\u2713' : '\u2717';
        log('  ' + icon + ' ' + c.name + ': ' + c.detail, c.ok ? 'ok' : 'err');
    }
    log('');
    if (res.all_ok) {
        log('\u2713 ALL PREFLIGHT CHECKS PASSED \u2014 ready to migrate', 'ok');
        pct(100);
    } else {
        log('\u2717 SOME CHECKS FAILED \u2014 review above before migrating', 'err');
    }
    setAllButtons(false);
}

// ── Copy helpers ─────────────────────────────────────────────

async function copyTable(name, rows, idx, total, batch, ps) {
    log('[' + idx + '/' + total + '] ' + name + '  (' + rows.toLocaleString() + ' rows)');

    const createRes = await api({ action: 'create_table', table: name });
    if (!createRes.ok) {
        log('  \u2717 CREATE failed: ' + createRes.error, 'err');
        return { ok: false, isView: createRes.is_view || false };
    }
    if (createRes.is_view) {
        log('  \u2713 View created', 'ok');
        return { ok: true, isView: true };
    }
    log('  \u2713 Structure created', 'ok');

    if (rows === 0) { log('  \u2013 Empty table, skipped data copy', 'dim'); return { ok: true }; }

    let offset = 0, done = false;
    while (!done) {
        let res;
        try { res = await api({ action: 'copy_batch', table: name, offset: offset, batch: batch }); }
        catch (e) { log('  \u2717 Network error at offset ' + offset + ': ' + e.message, 'err'); return { ok: false }; }
        if (!res.ok) { log('  \u2717 Error at offset ' + offset + ': ' + res.error, 'err'); return { ok: false }; }
        offset += res.copied;
        ps.migrated += res.copied;
        done = res.done;
        if (ps.totalRows > 0) {
            const elapsed = (performance.now() - ps.startTime) / 1000;
            const rate = ps.migrated / elapsed;
            const remain = (ps.totalRows - ps.migrated) / rate;
            pct((ps.migrated / ps.totalRows) * 100, remain);
        }
        if (!done && offset % (batch * 4) === 0) {
            log('  ... ' + offset.toLocaleString() + ' / ' + rows.toLocaleString(), 'dim');
        }
    }
    log('  \u2713 ' + offset.toLocaleString() + ' rows copied', 'ok');
    return { ok: true };
}

// ── Full Copy ────────────────────────────────────────────────

async function startMigration() {
    const maintBtn = document.getElementById('maintBtn');
    if (maintBtn.dataset.enabled !== '1') {
        if (!confirm('Maintenance mode is NOT enabled.\nThe live site may modify data during copy, causing inconsistencies.\n\nContinue anyway?')) {
            return;
        }
    }
    setAllButtons(true);
    logEl.innerHTML = '';
    pct(0);
    const batch = parseInt(document.getElementById('batchSize').value) || 500;

    log('Fetching table list (prefix: ' + 'u3zo_' + ')...');
    let listRes;
    try { listRes = await api({ action: 'list_tables' }); }
    catch (e) { log('NETWORK ERROR: ' + e.message, 'err'); setAllButtons(false); return; }
    if (!listRes.ok) { log('ERROR: ' + listRes.error, 'err'); setAllButtons(false); return; }

    const tables = listRes.tables;
    const ps = {
        migrated: 0,
        totalRows: tables.reduce((s, t) => s + t.rows, 0),
        startTime: performance.now(),
    };
    log('Found ' + tables.length + ' tables, ' + ps.totalRows.toLocaleString() + ' total rows.\n', 'ok');

    const failedViews = [];

    for (let i = 0; i < tables.length; i++) {
        const t = tables[i];
        const result = await copyTable(t.name, t.rows, i + 1, tables.length, batch, ps);
        if (!result.ok && result.isView) failedViews.push(t);
    }

    if (failedViews.length > 0) {
        log('\n--- Retrying ' + failedViews.length + ' failed VIEW(s) ---\n', 'inf');
        for (const v of failedViews) {
            log('Retrying VIEW: ' + v.name);
            const r = await api({ action: 'create_table', table: v.name });
            if (r.ok) { log('  \u2713 View created', 'ok'); }
            else { log('  \u2717 Still failing: ' + r.error, 'err'); }
        }
    }

    const elapsed = ((performance.now() - ps.startTime) / 1000).toFixed(1);
    log('\n========================================', 'ok');
    log('Copy complete!  ' + ps.migrated.toLocaleString() + ' rows in ' + elapsed + 's', 'ok');
    log('Click "Verify Copy" to validate, or "Re-sync Mismatches" for targeted fixes.\n', 'inf');
    pct(100);
    setAllButtons(false);
}

// ── Re-sync ──────────────────────────────────────────────────

async function resyncMismatches() {
    setAllButtons(true);
    logEl.innerHTML = '';
    pct(0);
    const batch = parseInt(document.getElementById('batchSize').value) || 500;

    log('Running verification to find mismatches...\n');
    let vRes;
    try { vRes = await api({ action: 'verify' }); }
    catch (e) { log('NETWORK ERROR: ' + e.message, 'err'); setAllButtons(false); return; }
    if (!vRes.ok) { log('ERROR: ' + vRes.error, 'err'); setAllButtons(false); return; }

    const mismatched = vRes.tables.filter(t =>
        !t.match || t.pk_range_match === false || t.error
    );

    if (mismatched.length === 0) {
        log('\u2713 No mismatches found \u2014 everything is in sync!', 'ok');
        pct(100);
        setAllButtons(false);
        return;
    }

    log('Found ' + mismatched.length + ' table(s) needing re-sync:\n', 'inf');
    for (const t of mismatched) {
        const reason = t.error ? t.error
            : !t.match ? 'row count: ' + (t.old_rows||0) + ' vs ' + (t.new_rows||0)
            : 'PK range: ' + (t.old_pk_range||'?') + ' vs ' + (t.new_pk_range||'?');
        log('  \u2022 ' + t.table + '  (' + reason + ')', 'dim');
    }
    log('');

    const ps = {
        migrated: 0,
        totalRows: mismatched.reduce((s, t) => s + (t.old_rows || 0), 0),
        startTime: performance.now(),
    };
    const failedViews = [];

    for (let i = 0; i < mismatched.length; i++) {
        const t = mismatched[i];
        const result = await copyTable(t.table, t.old_rows || 0, i + 1, mismatched.length, batch, ps);
        if (!result.ok && result.isView) failedViews.push(t);
    }

    if (failedViews.length > 0) {
        log('\n--- Retrying ' + failedViews.length + ' failed VIEW(s) ---\n', 'inf');
        for (const v of failedViews) {
            log('Retrying VIEW: ' + v.table);
            const r = await api({ action: 'create_table', table: v.table });
            if (r.ok) { log('  \u2713 View created', 'ok'); }
            else { log('  \u2717 Still failing: ' + r.error, 'err'); }
        }
    }

    const elapsed = ((performance.now() - ps.startTime) / 1000).toFixed(1);
    log('\n========================================', 'ok');
    log('Re-sync complete!  ' + ps.migrated.toLocaleString() + ' rows across '
        + mismatched.length + ' table(s) in ' + elapsed + 's', 'ok');

    log('\nRunning final verification...\n', 'inf');
    await doVerify();

    setAllButtons(false);
}

// ── Verify ───────────────────────────────────────────────────

async function doVerify() {
    let vRes;
    try { vRes = await api({ action: 'verify' }); }
    catch (e) { log('NETWORK ERROR: ' + e.message, 'err'); return; }
    if (!vRes.ok) { log('ERROR: ' + vRes.error, 'err'); return; }

    let html = '<table class="verify-table">';
    html += '<tr><th>Table</th><th>OLD rows</th><th>NEW rows</th><th>Row match</th><th>PK Range</th></tr>';
    for (const t of vRes.tables) {
        const cls = t.match && t.pk_range_match !== false ? 'row-ok' : 'row-bad';
        const rowIcon = t.match ? '\u2713' : '\u2717';
        let csText = '\u2013';
        if (t.error) {
            csText = '<span class="err">' + t.error + '</span>';
        } else if (t.pk_range_match === true) {
            csText = '<span class="ok">\u2713 match</span>';
        } else if (t.pk_range_match === false) {
            csText = '<span class="err">\u2717 mismatch (' + t.old_pk_range + ' vs ' + t.new_pk_range + ')</span>';
        }
        const newRows = t.new_rows !== null ? t.new_rows.toLocaleString() : '\u2013';
        html += '<tr class="' + cls + '"><td>' + t.table + '</td><td>' + t.old_rows.toLocaleString() + '</td>'
              + '<td>' + newRows + '</td><td>' + rowIcon + '</td><td>' + csText + '</td></tr>';
    }
    html += '</table>';

    logEl.innerHTML += html;
    logEl.scrollTop = logEl.scrollHeight;

    if (vRes.all_good) {
        log('\n\u2713 ALL TABLES VERIFIED SUCCESSFULLY', 'ok');
    } else {
        log('\n\u2717 SOME TABLES STILL HAVE MISMATCHES \u2014 check table above', 'err');
        log('Enable maintenance mode, then re-sync again.', 'inf');
    }
}

async function runVerify() {
    setAllButtons(true);
    log('\n--- Verification ---\n');
    log('Comparing row counts and checksums between OLD and NEW...\n');
    await doVerify();
    setAllButtons(false);
}

// ── Verify Serialized ────────────────────────────────────────

async function runVerifySerialized() {
    setAllButtons(true);
    log('\n--- Serialized Data Verification (NEW DB) ---\n');

    let res;
    try { res = await api({ action: 'verify_serialized', db: 'new' }); }
    catch (e) { log('NETWORK ERROR: ' + e.message, 'err'); setAllButtons(false); return; }
    if (!res.ok) { log('ERROR: ' + res.error, 'err'); setAllButtons(false); return; }

    log('Checked: ' + res.checked + ' serialized values in ' + res.db.toUpperCase() + ' DB');
    if (res.failed === 0) {
        log('\u2713 All values unserialize() successfully', 'ok');
    } else {
        log('\u2717 ' + res.failed + ' / ' + res.checked + ' values FAILED unserialize()', 'err');
        for (const e of res.entries) {
            log('  \u2022 ' + e.table + ' / ' + e.key + ' (' + e.size + ' bytes)', 'dim');
            log('    ' + e.preview.substring(0, 100), 'dim');
        }
    }
    setAllButtons(false);
}

// ── Maintenance Mode ─────────────────────────────────────────

async function toggleMaintenance() {
    const btn = document.getElementById('maintBtn');
    const enabling = btn.dataset.enabled !== '1';

    let res;
    try { res = await api({ action: 'maintenance', enable: enabling ? '1' : '0' }); }
    catch (e) { log('NETWORK ERROR: ' + e.message, 'err'); return; }
    if (!res.ok) { log('ERROR: ' + (res.error || 'unknown'), 'err'); return; }

    btn.dataset.enabled = res.enabled ? '1' : '0';
    btn.textContent = res.enabled ? 'Disable Maintenance' : 'Enable Maintenance';
    btn.style.background = res.enabled ? '#c0392b' : '#8e44ad';
    log(res.enabled
        ? '\u2717 Maintenance mode ENABLED \u2014 site shows "briefly unavailable"'
        : '\u2713 Maintenance mode DISABLED \u2014 site is live',
        res.enabled ? 'err' : 'ok');
}
</script>
</body>
</html>