037、TiDB特性_管理DDL

• DML不会阻塞其他会话DDL
• 建议客户端应该处理异常：ERROR 8028（HY000）
• 监控与取消操作
  • ADMIN SHOW DDL;
  • ADMIN SHOW DDL JOBS;
  • ADMIN SHOW DDL JOB QUERIES job_id;
  • ADMIN CANCEL DDL JOBS job_id;

/* source 07-demo-online-ddl-add-column-01-setup.sql */

/* target_table(id, name1) */
DROP TABLE IF EXISTS test.target_table;
CREATE TABLE test.target_table(
  id bigint PRIMARY KEY AUTO_RANDOM,
  name1 char(20));

/* Seed */
DROP TABLE IF EXISTS test.seed;
CREATE TABLE test.seed(name char(20));

insert into test.seed (name) values ('BEFORE-DDL'),('BEFORE-DDL'),('BEFORE-DDL'),('BEFORE-DDL'),('BEFORE-DDL');
insert into test.seed select * from test.seed;
insert into test.seed select * from test.seed;
insert into test.seed select * from test.seed;
insert into test.seed select * from test.seed;
insert into test.seed select * from test.seed;
insert into test.seed select * from test.seed;
select count(*) as "|320|" from test.seed;

DESC test.target_table;

/* source 07-demo-online-ddl-add-column-01-workload-before.sql */

/* Toggle autocommit with caution, you might create a very long TX. */
/* SET @@autocommit = 0; */
/* SET @@transaction_isolation='read-committed'; */
/* One Error is expected. */

BEGIN;
INSERT INTO test.target_table (id, name1) SELECT NULL, name FROM test.seed;
INSERT INTO test.target_table (id, name1) SELECT NULL, name FROM test.seed;
INSERT INTO test.target_table (id, name1) SELECT NULL, name FROM test.seed;
COMMIT;
SELECT name1 as "|NAME1|", count(*) as "|BEFORE-DDL-GOAL: 192000|" 
FROM test.target_table 
GROUP BY name1;

#!/bin/bash

# ./07-demo-online-ddl-add-column-01-workload-before.sh

mysql -h 127.0.0.1 -P 4000 -u root < 07-demo-online-ddl-add-column-01-setup.sql

sleep 5;

for i in $(seq 1 200); do
  mysql -h 127.0.0.1 -P 4000 -u root < 07-demo-online-ddl-add-column-01-workload-before.sql
done;