#!/usr/bin/env bash
# MySQL 8.0 Online DDL + 元数据锁（MDL）演示脚本 v1.0 — surinch.com / 盈尺
# 只在测试实例上运行！脚本会新建并删除库 ddl_lab，造 ROWS 行合成数据（非真实业务数据）。
# 用法：MYSQL_CMD="mysql -h127.0.0.1 -uroot -p你的密码" ROWS=1000000 bash mysql-online-ddl-mdl-demo-v1.0.sh
set -euo pipefail
MYSQL_CMD=${MYSQL_CMD:-mysql}
ROWS=${ROWS:-1000000}
q() { $MYSQL_CMD -N -B -e "$1"; }
t() { # 计时执行一条 SQL，输出 label,秒数,结果
  local label=$1 sql=$2 s e out rc
  s=$(date +%s.%N); out=$($MYSQL_CMD ddl_lab -N -B -e "$sql" 2>&1) && rc=ok || rc=error; e=$(date +%s.%N)
  printf '%s,%.3f,%s,%s\n' "$label" "$(echo "$e - $s" | bc)" "$rc" "$(echo "$out" | tr '\n,' '  ' | sed 's/mysql: \[Warning\][^.]*\.//' | cut -c1-140)"
}
echo "# version: $(q 'select version()')  rows: $ROWS"
q "DROP DATABASE IF EXISTS ddl_lab; CREATE DATABASE ddl_lab;"
$MYSQL_CMD ddl_lab -e "
CREATE TABLE orders (id INT PRIMARY KEY AUTO_INCREMENT, uid INT NOT NULL, amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(16) NOT NULL, created_at DATETIME NOT NULL) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth = 10000000;
INSERT INTO orders (uid, amount, status, created_at)
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < $ROWS)
SELECT 1 + (n*7919) % 5000, ROUND(((n*104729) % 50000)/100, 2), ELT(1 + n % 4,'paid','shipped','refund','new'),
       '2026-01-01' + INTERVAL (n % 280) DAY FROM seq;
ANALYZE TABLE orders;" >/dev/null
echo "# table size: $(q "select concat(round((data_length+index_length)/1024/1024,1),' MB') from information_schema.tables where table_schema='ddl_lab' and table_name='orders'")"
echo "label,seconds,result,message"
t add_col_instant   "ALTER TABLE orders ADD COLUMN note1 VARCHAR(64) NULL, ALGORITHM=INSTANT"
t instant_row_versions  "SELECT TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES WHERE NAME='ddl_lab/orders'"
t add_col_inplace   "ALTER TABLE orders ADD COLUMN note2 VARCHAR(64) NULL, ALGORITHM=INPLACE, LOCK=NONE"
t add_col_copy      "ALTER TABLE orders ADD COLUMN note3 VARCHAR(64) NULL, ALGORITHM=COPY"
t add_index_inplace "ALTER TABLE orders ADD INDEX idx_uid (uid), ALGORITHM=INPLACE, LOCK=NONE"
t widen_decimal_inplace "ALTER TABLE orders MODIFY amount DECIMAL(12,2) NOT NULL, ALGORITHM=INPLACE"
t widen_decimal_copy    "ALTER TABLE orders MODIFY amount DECIMAL(12,2) NOT NULL, ALGORITHM=COPY"

echo "# --- MDL 演示：会话A 开事务读一行不提交；会话B 做 INSTANT 加列；会话C 普通查询 ---"
( { echo "BEGIN; SELECT id FROM orders WHERE id=1;"; sleep 14; echo "COMMIT;"; } | $MYSQL_CMD ddl_lab -N -B >/dev/null 2>&1 ) &
A=$!; sleep 3
( t mdl_B_alter_lock_wait_5s "SET SESSION lock_wait_timeout=5; ALTER TABLE orders ADD COLUMN note4 VARCHAR(64) NULL, ALGORITHM=INSTANT" > B.out ) &
B=$!; sleep 1
( t mdl_C_plain_select "SELECT COUNT(*) FROM orders WHERE id <= 10" > C.out ) &
C=$!; sleep 1.5
echo "# 等待期间的 MDL 视图（只读诊断 SQL 的输出）："
SQL_FILE=${SQL_FILE:-$(dirname "$0")/mysql-mdl-wait-root-blocker-readonly-v1.0.sql}
$MYSQL_CMD -t < "$SQL_FILE" 2>/dev/null
wait $B $C; cat B.out C.out; wait $A || true
echo "# --- 对照：没有长事务时，同样的 INSTANT 加列 ---"
t mdl_alter_no_blocker "SET SESSION lock_wait_timeout=5; ALTER TABLE orders ADD COLUMN note4 VARCHAR(64) NULL, ALGORITHM=INSTANT"
rm -f B.out C.out
q "DROP DATABASE ddl_lab;"