128 lines
3.9 KiB
PHP
128 lines
3.9 KiB
PHP
<?php
|
||
declare(strict_types=1);
|
||
|
||
/**
|
||
* 解决宝塔面板备份偶发报错：
|
||
* “备份文件中缺少表: xxx__view_backup”
|
||
*
|
||
* 原因通常是：数据库存在 VIEW，但宝塔的备份校验按“表”去匹配，
|
||
* 或其内部会期望存在 xxx__view_backup 之类的“视图备份表”标记。
|
||
*
|
||
* 本脚本会：
|
||
* - 扫描当前库所有 VIEW
|
||
* - 为每个 VIEW 创建一个同名的备份表：<view_name>__view_backup
|
||
* - 在该表写入 SHOW CREATE VIEW 的结果（若权限不足则写入空串）
|
||
*
|
||
* 用法（在 server 目录执行）：
|
||
* php db/fix_bt_backup_view_tables.php
|
||
*/
|
||
|
||
require_once __DIR__ . '/../vendor/autoload.php';
|
||
|
||
if (class_exists(\Dotenv\Dotenv::class) && is_file(dirname(__DIR__) . '/.env')) {
|
||
if (method_exists(\Dotenv\Dotenv::class, 'createUnsafeMutable')) {
|
||
\Dotenv\Dotenv::createUnsafeMutable(dirname(__DIR__))->load();
|
||
} else {
|
||
\Dotenv\Dotenv::createMutable(dirname(__DIR__))->load();
|
||
}
|
||
}
|
||
|
||
$host = getenv('DB_HOST') ?: '127.0.0.1';
|
||
$port = getenv('DB_PORT') ?: '3306';
|
||
$dbName = getenv('DB_NAME') ?: '';
|
||
$user = getenv('DB_USER') ?: '';
|
||
$pass = getenv('DB_PASSWORD') ?: '';
|
||
|
||
if ($dbName === '' || $user === '') {
|
||
fwrite(STDERR, "Missing DB_NAME/DB_USER in .env\n");
|
||
exit(1);
|
||
}
|
||
|
||
$dsn = "mysql:host={$host};port={$port};dbname={$dbName};charset=utf8mb4";
|
||
$pdo = new PDO($dsn, $user, $pass, [
|
||
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
||
]);
|
||
|
||
$views = $pdo
|
||
->query("SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME")
|
||
->fetchAll(PDO::FETCH_COLUMN);
|
||
|
||
if (!$views) {
|
||
echo "No views found; nothing to do.\n";
|
||
exit(0);
|
||
}
|
||
|
||
$created = 0;
|
||
$updated = 0;
|
||
$failed = 0;
|
||
|
||
foreach ($views as $viewName) {
|
||
$viewName = (string) $viewName;
|
||
$backupTable = $viewName . '__view_backup';
|
||
|
||
if (!preg_match('/^[a-zA-Z0-9_]+$/', $backupTable)) {
|
||
$failed++;
|
||
echo "[skip] invalid name: {$backupTable}\n";
|
||
continue;
|
||
}
|
||
|
||
try {
|
||
$pdo->exec(
|
||
"CREATE TABLE IF NOT EXISTS `{$backupTable}` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`view_name` VARCHAR(255) NOT NULL,
|
||
`create_sql` LONGTEXT NOT NULL,
|
||
`updated_at` DATETIME NOT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_view_name` (`view_name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
|
||
);
|
||
$created++;
|
||
} catch (Throwable $e) {
|
||
$failed++;
|
||
echo "[fail] create table {$backupTable}: " . $e->getMessage() . "\n";
|
||
continue;
|
||
}
|
||
|
||
$createSql = '';
|
||
try {
|
||
$stmt = $pdo->query("SHOW CREATE VIEW `{$viewName}`");
|
||
$row = $stmt ? $stmt->fetch(PDO::FETCH_ASSOC) : false;
|
||
if ($row) {
|
||
// SHOW CREATE VIEW 返回字段名可能是 "Create View" 或类似
|
||
foreach ($row as $k => $v) {
|
||
if (is_string($k) && stripos($k, 'create') !== false && is_string($v)) {
|
||
$createSql = $v;
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
} catch (Throwable $e) {
|
||
// 权限不足也不阻断，只是留空，保证备份表存在
|
||
$createSql = '';
|
||
}
|
||
|
||
try {
|
||
$stmt = $pdo->prepare(
|
||
"INSERT INTO `{$backupTable}` (`view_name`, `create_sql`, `updated_at`)
|
||
VALUES (:view_name, :create_sql, :updated_at)
|
||
ON DUPLICATE KEY UPDATE
|
||
`create_sql` = VALUES(`create_sql`),
|
||
`updated_at` = VALUES(`updated_at`)"
|
||
);
|
||
$stmt->execute([
|
||
'view_name' => $viewName,
|
||
'create_sql' => $createSql,
|
||
'updated_at' => date('Y-m-d H:i:s'),
|
||
]);
|
||
$updated++;
|
||
echo "[ok] {$viewName} -> {$backupTable}\n";
|
||
} catch (Throwable $e) {
|
||
$failed++;
|
||
echo "[fail] upsert {$backupTable}: " . $e->getMessage() . "\n";
|
||
}
|
||
}
|
||
|
||
echo "Done. created={$created}, updated={$updated}, failed={$failed}\n";
|
||
|