#!/bin/bash
# MySQL 8.0 → 8.4 默认值变化复现脚本 v1.0（surinch.com，2026-11-04）
# !!! 仅限测试实例：会重建表、重启 mysqld、改全局参数。不要在生产或任何有数据的实例上运行 !!!
# 用法：bash mysql-84-upgrade-defaults-lab-v1.0.sh <mysqld 路径> <my.cnf> <buffer_pool, 如 128M> <change_buffering: default|all|none>
# 前提：my.cnf 只配置路径与端口；库 upg_lab 已用下方 SQL 造好 300 万行 orders_base 和 nums(1..1000000)
set -euo pipefail
MYSQLD=$1; CNF=$2; BP=$3; CB=$4
M="mysql --defaults-file=$CNF"
start(){ nohup "$MYSQLD" --defaults-file="$CNF" "$@" >/dev/null 2>&1 & for i in $(seq 1 60); do $M -e "select 1" >/dev/null 2>&1 && return; sleep 1; done; echo "mysqld 未启动"; exit 1; }

: <<'SETUP_SQL'
CREATE DATABASE upg_lab; USE upg_lab;
CREATE TABLE d10 (n INT); INSERT INTO d10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE nums (n INT PRIMARY KEY);
INSERT INTO nums SELECT a.n+b.n*10+c.n*100+d.n*1000+e.n*10000+f.n*100000+1 FROM d10 a,d10 b,d10 c,d10 d,d10 e,d10 f;
CREATE TABLE orders_base (id BIGINT PRIMARY KEY, order_no CHAR(32) NOT NULL, shop_id INT NOT NULL, status TINYINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, note VARCHAR(64) NOT NULL,
  KEY idx_order_no(order_no), KEY idx_shop_status(shop_id, status)) ENGINE=InnoDB;
INSERT INTO orders_base SELECT n+k*1000000, MD5(n+k*1000000), CRC32(n+k*1000000)%5000, (n+k*1000000)%5,
  ((n+k*1000000)%99991)/100, '2025-01-01' + INTERVAL (n+k*1000000)*10 SECOND, REPEAT('x',40)
  FROM nums, (SELECT 0 k UNION ALL SELECT 1 UNION ALL SELECT 2) kk;
DELIMITER //
CREATE PROCEDURE bench_insert(IN start_id BIGINT, IN total INT, IN batch INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  WHILE done < total DO
    INSERT INTO orders SELECT start_id+done+n, MD5(start_id+done+n), CRC32(start_id+done+n)%5000, (start_id+done+n)%5,
      ((start_id+done+n)%99991)/100, '2025-01-01' + INTERVAL (start_id+done+n)*10 SECOND, REPEAT('x',40)
    FROM nums WHERE n <= batch;
    SET done = done + batch;
  END WHILE;
END//
DELIMITER ;
SETUP_SQL

# 1) 用大缓冲池重置 orders，慢关机把 change buffer 合并干净
$M -e shutdown 2>/dev/null || true; sleep 8
start --innodb-buffer-pool-size=1G
$M upg_lab -e "DROP TABLE IF EXISTS orders; CREATE TABLE orders LIKE orders_base; INSERT INTO orders SELECT * FROM orders_base; ANALYZE TABLE orders" >/dev/null
$M -e "SET GLOBAL innodb_fast_shutdown=0"; $M -e shutdown; sleep 10
# 2) 用目标缓冲池和 change_buffering 冷启动
if [ "$CB" = default ]; then start --innodb-buffer-pool-size=$BP; else start --innodb-buffer-pool-size=$BP --innodb-change-buffering=$CB; fi
st(){ $M -N -e "SELECT SUM(IF(variable_name='Innodb_buffer_pool_reads',variable_value,0)) FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_reads'"; }
b=$(st); s=$(date +%s.%N)
# 3) 写入 20 万行（批 1000），order_no / shop_id 是随机分布的二级索引键
$M upg_lab -e "CALL bench_insert(3000000, 200000, 1000)"
e=$(date +%s.%N); a=$(st)
$M -N -e "SELECT CONCAT_WS(' ', version(), CONCAT('cb=',@@innodb_change_buffering), CONCAT('ahi=',@@innodb_adaptive_hash_index), CONCAT('flush=',@@innodb_flush_method), CONCAT('bp=',@@innodb_buffer_pool_size DIV 1048576,'M'))"
python3 -c "import sys;s,e,r=sys.argv[1:];print(f'secs={float(e)-float(s):.2f} buffer_pool_reads={r}')" "$s" "$e" "$((a-b))"