github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/snapshot/nonsys_restore_to_nonsys_account.result (about) 1 drop account if exists acc01; 2 create account acc01 admin_name = 'test_account' identified by '111'; 3 drop database if exists test; 4 create database test; 5 use test; 6 create table clu01(col1 int, col2 decimal); 7 insert into clu01 values(1,2); 8 drop snapshot if exists sp01; 9 create snapshot sp01 for account acc01; 10 insert into clu01 values(2,3); 11 restore account acc01 from snapshot sp01; 12 select * from clu01; 13 col1 col2 14 1 2 15 select count(*) from clu01; 16 count(*) 17 1 18 drop table clu01; 19 drop database test; 20 drop snapshot sp01; 21 drop database if exists test01; 22 create database test01; 23 use test01; 24 drop table if exists rs01; 25 create table rs01 (col1 int, col2 decimal(6), col3 varchar(30)); 26 insert into rs01 values (1, null, 'database'); 27 insert into rs01 values (2, 38291.32132, 'database'); 28 insert into rs01 values (3, null, 'database management acc01tem'); 29 insert into rs01 values (4, 10, null); 30 insert into rs01 values (1, -321.321, null); 31 insert into rs01 values (2, -1, null); 32 select count(*) from rs01; 33 count(*) 34 6 35 drop snapshot if exists sp01; 36 create snapshot sp01 for account acc01; 37 select count(*) from rs01 {snapshot = 'sp01'}; 38 count(*) 39 6 40 insert into rs01 values (2, -1, null); 41 insert into rs01 values (1, -321.321, null); 42 select * from rs01; 43 col1 col2 col3 44 1 null database 45 2 38291 database 46 3 null database management acc01tem 47 4 10 null 48 1 -321 null 49 2 -1 null 50 2 -1 null 51 1 -321 null 52 select count(*) from mo_catalog.mo_tables{snapshot = 'sp01'} where reldatabase = 'test01'; 53 count(*) 54 1 55 select * from mo_catalog.mo_database{snapshot = 'sp01'} where datname = 'test01'; 56 dat_id datname dat_catalog_name dat_createsql owner creator created_time account_id dat_type 57 277400 test01 def create database test01 2 2 2024-05-13 16:01:50 25 58 select attname from mo_catalog.mo_columns{snapshot = 'sp01'} where att_database = 'test01'; 59 attname 60 __mo_fake_pk_col 61 __mo_rowid 62 col1 63 col2 64 col3 65 restore account acc01 from snapshot sp01; 66 select count(*) from rs01; 67 count(*) 68 6 69 select * from rs01; 70 col1 col2 col3 71 1 null database 72 2 38291 database 73 3 null database management acc01tem 74 4 10 null 75 1 -321 null 76 2 -1 null 77 select count(*) from rs01 {snapshot = 'sp01'}; 78 count(*) 79 6 80 select count(*) from mo_catalog.mo_tables{snapshot = 'sp01'} where reldatabase = 'test01'; 81 count(*) 82 1 83 select * from mo_catalog.mo_database{snapshot = 'sp01'} where datname = 'test01'; 84 dat_id datname dat_catalog_name dat_createsql owner creator created_time account_id dat_type 85 277400 test01 def create database test01 2 2 2024-05-13 16:01:50 25 86 select attname from mo_catalog.mo_columns{snapshot = 'sp01'} where att_database = 'test01'; 87 attname 88 __mo_fake_pk_col 89 __mo_rowid 90 col1 91 col2 92 col3 93 drop snapshot sp01; 94 drop database test01; 95 drop database if exists test02; 96 create database test02; 97 use test02; 98 drop table if exists rs02; 99 create table rs02 (col1 int, col2 datetime); 100 insert into rs02 values (1, '2020-10-13 10:10:10'); 101 insert into rs02 values (2, null); 102 insert into rs02 values (1, '2021-10-10 00:00:00'); 103 insert into rs02 values (2, '2023-01-01 12:12:12'); 104 insert into rs02 values (2, null); 105 insert into rs02 values (3, null); 106 insert into rs02 values (4, '2023-11-27 01:02:03'); 107 select * from rs02; 108 col1 col2 109 1 2020-10-13 10:10:10 110 2 null 111 1 2021-10-10 00:00:00 112 2 2023-01-01 12:12:12 113 2 null 114 3 null 115 4 2023-11-27 01:02:03 116 drop table if exists rs03; 117 create table rs03 (col1 int, col2 float, col3 decimal, col4 enum('1','2','3','4')); 118 insert into rs03 values (1, 12.21, 32324.32131, 1); 119 insert into rs03 values (2, null, null, 2); 120 insert into rs03 values (2, -12.1, 34738, null); 121 insert into rs03 values (1, 90.2314, null, 4); 122 insert into rs03 values (1, 43425.4325, -7483.432, 2); 123 drop snapshot if exists sp02; 124 create snapshot sp02 for account acc01; 125 select count(*) from mo_catalog.mo_tables{snapshot = 'sp02'} where reldatabase = 'test02'; 126 count(*) 127 2 128 select * from mo_catalog.mo_database{snapshot = 'sp02'} where datname = 'test02'; 129 dat_id datname dat_catalog_name dat_createsql owner creator created_time account_id dat_type 130 277404 test02 def create database test02 2 2 2024-05-13 16:01:50 25 131 select attname from mo_catalog.mo_columns{snapshot = 'sp02'} where att_database = 'test02'; 132 attname 133 __mo_fake_pk_col 134 __mo_rowid 135 col1 136 col2 137 __mo_fake_pk_col 138 __mo_rowid 139 col1 140 col2 141 col3 142 col4 143 use test02; 144 insert into rs02 select * from rs02; 145 select count(*) from rs02; 146 count(*) 147 14 148 select count(*) from rs02{snapshot = 'sp02'}; 149 count(*) 150 7 151 delete from rs03 where col1 = 1; 152 select count(*) from rs03; 153 count(*) 154 2 155 select count(*) from rs03{snapshot = 'sp02'}; 156 count(*) 157 5 158 restore account acc01 from snapshot sp02; 159 show databases; 160 Database 161 information_schema 162 mo_catalog 163 mysql 164 system 165 system_metrics 166 test02 167 select count(*) from rs02; 168 count(*) 169 7 170 select count(*) from rs03; 171 count(*) 172 5 173 use test02; 174 drop table rs02; 175 drop table rs03; 176 drop snapshot sp02; 177 drop database if exists test03; 178 create database test03; 179 use test03; 180 drop table if exists pri01; 181 create table pri01( 182 deptno int unsigned comment '部门编号', 183 dname varchar(15) comment '部门名称', 184 loc varchar(50) comment '部门所在位置', 185 primary key(deptno) 186 ) comment='部门表'; 187 insert into pri01 values (10,'ACCOUNTING','NEW YORK'); 188 insert into pri01 values (20,'RESEARCH','DALLAS'); 189 insert into pri01 values (30,'SALES','CHICAGO'); 190 insert into pri01 values (40,'OPERATIONS','BOSTON'); 191 drop table if exists aff01; 192 create table aff01( 193 empno int unsigned auto_increment COMMENT '雇员编号', 194 ename varchar(15) comment '雇员姓名', 195 job varchar(10) comment '雇员职位', 196 mgr int unsigned comment '雇员对应的领导的编号', 197 hiredate date comment '雇员的雇佣日期', 198 sal decimal(7,2) comment '雇员的基本工资', 199 comm decimal(7,2) comment '奖金', 200 deptno int unsigned comment '所在部门', 201 primary key(empno), 202 constraint `c1` foreign key (deptno) references pri01 (deptno) 203 ); 204 insert into aff01 values (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 205 insert into aff01 values (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 206 insert into aff01 values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 207 insert into aff01 values (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 208 insert into aff01 values (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 209 insert into aff01 values (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 210 insert into aff01 values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 211 insert into aff01 values (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 212 insert into aff01 values (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 213 insert into aff01 values (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 214 insert into aff01 values (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 215 insert into aff01 values (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 216 insert into aff01 values (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 217 insert into aff01 values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 218 select count(*) from pri01; 219 count(*) 220 4 221 select count(*) from aff01; 222 count(*) 223 14 224 show create table pri01; 225 Table Create Table 226 pri01 CREATE TABLE `pri01` (\n`deptno` INT UNSIGNED NOT NULL COMMENT '部门编号',\n`dname` VARCHAR(15) DEFAULT NULL COMMENT '部门名称',\n`loc` VARCHAR(50) DEFAULT NULL COMMENT '部门所在位置',\nPRIMARY KEY (`deptno`)\n) COMMENT='部门表' 227 show create table aff01; 228 Table Create Table 229 aff01 CREATE TABLE `aff01` (\n`empno` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '雇员编号',\n`ename` VARCHAR(15) DEFAULT NULL COMMENT '雇员姓名',\n`job` VARCHAR(10) DEFAULT NULL COMMENT '雇员职位',\n`mgr` INT UNSIGNED DEFAULT NULL COMMENT '雇员对应的领导的编号',\n`hiredate` DATE DEFAULT NULL COMMENT '雇员的雇佣日期',\n`sal` DECIMAL(7,2) DEFAULT NULL COMMENT '雇员的基本工资',\n`comm` DECIMAL(7,2) DEFAULT NULL COMMENT '奖金',\n`deptno` INT UNSIGNED DEFAULT NULL COMMENT '所在部门',\nPRIMARY KEY (`empno`),\nCONSTRAINT `c1` FOREIGN KEY (`deptno`) REFERENCES `pri01` (`deptno`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 230 drop snapshot if exists sp04; 231 create snapshot sp04 for account acc01; 232 show snapshots where snapshot_name = 'sp04'; 233 snapshot_name timestamp snapshot_level account_name database_name table_name 234 sp04 2024-05-13 08:01:52.160466 account acc01 235 select count(*) from mo_catalog.mo_tables{snapshot = 'sp04'} where reldatabase = 'test03'; 236 count(*) 237 2 238 select * from mo_catalog.mo_database{snapshot = 'sp04'} where datname = 'test03'; 239 dat_id datname dat_catalog_name dat_createsql owner creator created_time account_id dat_type 240 277410 test03 def create database test03 2 2 2024-05-13 16:01:51 25 241 select attname from mo_catalog.mo_columns{snapshot = 'sp04'} where att_database = 'test03'; 242 attname 243 __mo_rowid 244 deptno 245 dname 246 loc 247 __mo_rowid 248 comm 249 deptno 250 empno 251 ename 252 hiredate 253 job 254 mgr 255 sal 256 select * from aff01{snapshot = 'sp04'}; 257 empno ename job mgr hiredate sal comm deptno 258 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 259 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 260 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 261 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 262 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 263 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 264 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 265 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 266 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 267 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 268 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 269 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 270 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 271 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 272 select * from pri01{snapshot = 'sp04'}; 273 deptno dname loc 274 10 ACCOUNTING NEW YORK 275 20 RESEARCH DALLAS 276 30 SALES CHICAGO 277 40 OPERATIONS BOSTON 278 drop database test03; 279 select * from test03.aff01{snapshot = 'sp04'}; 280 empno ename job mgr hiredate sal comm deptno 281 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 282 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 283 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 284 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 285 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 286 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 287 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 288 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 289 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 290 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 291 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 292 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 293 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 294 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 295 select * from test03.pri01{snapshot = 'sp04'}; 296 deptno dname loc 297 10 ACCOUNTING NEW YORK 298 20 RESEARCH DALLAS 299 30 SALES CHICAGO 300 40 OPERATIONS BOSTON 301 select count(*) from test03.aff01{snapshot = 'sp04'}; 302 count(*) 303 14 304 restore account acc01 from snapshot sp04; 305 use test03; 306 show create table aff01; 307 Table Create Table 308 aff01 CREATE TABLE `aff01` (\n`empno` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '雇员编号',\n`ename` VARCHAR(15) DEFAULT null COMMENT '雇员姓名',\n`job` VARCHAR(10) DEFAULT null COMMENT '雇员职位',\n`mgr` INT UNSIGNED DEFAULT null COMMENT '雇员对应的领导的编号',\n`hiredate` DATE DEFAULT null COMMENT '雇员的雇佣日期',\n`sal` DECIMAL(7,2) DEFAULT null COMMENT '雇员的基本工资',\n`comm` DECIMAL(7,2) DEFAULT null COMMENT '奖金',\n`deptno` INT UNSIGNED DEFAULT null COMMENT '所在部门',\nPRIMARY KEY (`empno`),\nCONSTRAINT `c1` FOREIGN KEY (`deptno`) REFERENCES `pri01` (`deptno`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 309 show create table pri01; 310 Table Create Table 311 pri01 CREATE TABLE `pri01` (\n`deptno` INT UNSIGNED NOT NULL COMMENT '部门编号',\n`dname` VARCHAR(15) DEFAULT null COMMENT '部门名称',\n`loc` VARCHAR(50) DEFAULT null COMMENT '部门所在位置',\nPRIMARY KEY (`deptno`)\n) COMMENT='部门表' 312 select count(*) from aff01; 313 count(*) 314 14 315 drop database test03; 316 drop snapshot sp04; 317 drop database if exists test01; 318 create database test01; 319 use test01; 320 create table t1(col1 int, col2 decimal); 321 insert into t1 values(1,2); 322 insert into t1 values(2,3); 323 insert into t1 values(3,4); 324 create table t2(cool1 int primary key , col2 decimal); 325 insert into t2 select * from t1; 326 create table t3 like t2; 327 select count(*) from t1; 328 count(*) 329 3 330 select count(*) from t2; 331 count(*) 332 3 333 select count(*) from t3; 334 count(*) 335 0 336 drop database if exists test02; 337 create database test02; 338 use test02; 339 create table t1(col1 int, col2 decimal); 340 insert into t1 values(1,2); 341 insert into t1 values(2,3); 342 insert into t1 values(3,4); 343 create table t2(col1 int primary key , col2 decimal); 344 insert into t2 select * from t1; 345 create table t3 like t2; 346 insert into t3 select * from t2; 347 select count(*) from t1; 348 count(*) 349 3 350 select count(*) from t2; 351 count(*) 352 3 353 select count(*) from t3; 354 count(*) 355 3 356 drop database if exists test03; 357 create database test03; 358 use test03; 359 create table t1(col1 int, col2 decimal); 360 insert into t1 values(1,2); 361 insert into t1 values(2,3); 362 insert into t1 values(3,4); 363 create table t2(cool1 int primary key , col2 decimal); 364 insert into t2 select * from t1; 365 create table t3 like t2; 366 insert into t3 select * from t2; 367 insert into t3 select * from t2; 368 Duplicate entry '1' for key 'cool1' 369 select count(*) from t1; 370 count(*) 371 3 372 select count(*) from t2; 373 count(*) 374 3 375 select count(*) from t3; 376 count(*) 377 3 378 drop snapshot if exists snap01; 379 create snapshot snap01 for account acc01; 380 select count(*) from test01.t1 {snapshot = 'snap01'}; 381 count(*) 382 3 383 select count(*) from test02.t2 {snapshot = 'snap01'}; 384 count(*) 385 3 386 select count(*) from test03.t3 {snapshot = 'snap01'}; 387 count(*) 388 3 389 drop database test01; 390 drop database test02; 391 show databases; 392 Database 393 information_schema 394 mo_catalog 395 mysql 396 system 397 system_metrics 398 test03 399 select * from test01.t1; 400 invalid database test01 401 select count(*) from test03.t3; 402 count(*) 403 3 404 restore account acc01 from snapshot snap01; 405 show databases; 406 Database 407 information_schema 408 mo_catalog 409 mysql 410 system 411 system_metrics 412 test01 413 test02 414 test03 415 select count(*) from test01.t1; 416 count(*) 417 3 418 select * from test01.t1; 419 col1 col2 420 1 2 421 2 3 422 3 4 423 select count(*) from test02.t2; 424 count(*) 425 3 426 select * from test02.t2; 427 col1 col2 428 1 2 429 2 3 430 3 4 431 select count(*) from test03.t3; 432 count(*) 433 3 434 select * from test03.t3; 435 cool1 col2 436 1 2 437 2 3 438 3 4 439 show create table test01.t1; 440 Table Create Table 441 t1 CREATE TABLE `t1` (\n`col1` INT DEFAULT null,\n`col2` DECIMAL(38,0) DEFAULT null\n) 442 show create table test02.t2; 443 Table Create Table 444 t2 CREATE TABLE `t2` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT null,\nPRIMARY KEY (`col1`)\n) 445 show create table test03.t2; 446 Table Create Table 447 t2 CREATE TABLE `t2` (\n`cool1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT null,\nPRIMARY KEY (`cool1`)\n) 448 drop database test01; 449 drop database test02; 450 drop database test03; 451 drop snapshot snap01; 452 drop snapshot if exists sp05; 453 create snapshot sp05 for account acc01; 454 create database db01; 455 restore account acc01 FROM snapshot sp05; 456 show databases; 457 Database 458 information_schema 459 mo_catalog 460 mysql 461 system 462 system_metrics 463 drop snapshot sp05; 464 drop database if exists db01; 465 create database db01; 466 use db01; 467 drop table if exists table01; 468 create table table01(col1 int primary key , col2 decimal, col3 char, col4 varchar(20), col5 text, col6 double); 469 insert into table01 values (1, 2, 'a', '23eiojf', 'r23v324r23rer', 3923.324); 470 insert into table01 values (2, 3, 'b', '32r32r', 'database', 1111111); 471 drop table if exists table02; 472 create table table02 (col1 int unique key, col2 varchar(20)); 473 insert into table02 (col1, col2) values (133, 'database'); 474 drop snapshot if exists sp07; 475 create snapshot sp07 for account acc01; 476 drop table table01; 477 insert into table02 values(134, 'database'); 478 drop snapshot if exists sp08; 479 create snapshot sp08 for account acc01; 480 show snapshots; 481 SNAPSHOT_NAME TIMESTAMP SNAPSHOT_LEVEL ACCOUNT_NAME DATABASE_NAME TABLE_NAME 482 sp08 2024-05-17 04:45:35.375952 account acc01 483 sp07 2024-05-17 04:45:35.300373 account acc01 484 restore account acc01 from snapshot sp08; 485 select * from table02; 486 col1 col2 487 133 database 488 134 database 489 select * from db01.table01; 490 SQL parser error: table "table01" does not exist 491 select count(*) from table02; 492 count(*) 493 2 494 restore account acc01 from snapshot sp07; 495 select * from table01; 496 col1 col2 col3 col4 col5 col6 497 1 2 a 23eiojf r23v324r23rer 3923.324 498 2 3 b 32r32r database 1111111.0 499 select * from table02; 500 col1 col2 501 133 database 502 select count(*) from table01; 503 count(*) 504 2 505 select count(*) from table02; 506 count(*) 507 1 508 drop snapshot sp07; 509 drop snapshot sp08; 510 drop database db01; 511 drop database if exists db02; 512 create database db02; 513 use db02; 514 drop table if exists table01; 515 create table table01(col1 int primary key , col2 decimal, col3 char, col4 varchar(20), col5 text, col6 double); 516 insert into table01 values (1, 2, 'a', '23eiojf', 'r23v324r23rer', 3923.324); 517 insert into table01 values (2, 3, 'b', '32r32r', 'database', 1111111); 518 drop table if exists table02; 519 create table table02 (col1 int unique key, col2 varchar(20)); 520 insert into table02 (col1, col2) values (133, 'database'); 521 drop snapshot if exists sp09; 522 create snapshot sp09 for account acc01; 523 drop table table01; 524 insert into table02 values(134, 'database'); 525 drop snapshot if exists sp10; 526 create snapshot sp10 for account acc01; 527 show snapshots; 528 SNAPSHOT_NAME TIMESTAMP SNAPSHOT_LEVEL ACCOUNT_NAME DATABASE_NAME TABLE_NAME 529 sp10 2024-05-17 04:45:36.113777 account acc01 530 sp09 2024-05-17 04:45:35.904463 account acc01 531 restore account acc01 from snapshot sp09; 532 select * from table02; 533 col1 col2 534 133 database 535 select * from db02.table01; 536 col1 col2 col3 col4 col5 col6 537 1 2 a 23eiojf r23v324r23rer 3923.324 538 2 3 b 32r32r database 1111111.0 539 select count(*) from table02; 540 count(*) 541 1 542 select count(*) from table01; 543 count(*) 544 2 545 restore account acc01 from snapshot sp10; 546 select * from db02.table01; 547 SQL parser error: table "table01" does not exist 548 select count(*) from table01; 549 SQL parser error: table "table01" does not exist 550 select * from db02.table02; 551 col1 col2 552 133 database 553 134 database 554 select count(*) from table02; 555 count(*) 556 2 557 drop snapshot sp09; 558 drop snapshot sp10; 559 drop database db02; 560 drop database if exists db03; 561 create database db03; 562 use db03; 563 drop table if exists ti1; 564 drop table if exists tm1; 565 drop table if exists ti2; 566 drop table if exists tm2; 567 create table ti1(a INT not null, b INT, c INT); 568 create table tm1(a INT not null, b INT, c INT); 569 create table ti2(a INT primary key AUTO_INCREMENT, b INT, c INT); 570 create table tm2(a INT primary key AUTO_INCREMENT, b INT, c INT); 571 show create table ti1; 572 Table Create Table 573 ti1 CREATE TABLE `ti1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL\n) 574 show create table tm1; 575 Table Create Table 576 tm1 CREATE TABLE `tm1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL\n) 577 show create table ti2; 578 Table Create Table 579 ti2 CREATE TABLE `ti2` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL,\nPRIMARY KEY (`a`)\n) 580 show create table tm2; 581 Table Create Table 582 tm2 CREATE TABLE `tm2` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL,\nPRIMARY KEY (`a`)\n) 583 drop snapshot if exists sp11; 584 create snapshot sp11 for account acc01; 585 insert into ti1 values (1,1,1), (2,2,2); 586 insert into ti2 values (1,1,1), (2,2,2); 587 select * from ti1; 588 a b c 589 1 1 1 590 2 2 2 591 select * from tm1; 592 a b c 593 select * from ti2; 594 a b c 595 1 1 1 596 2 2 2 597 select * from tm2; 598 a b c 599 drop snapshot if exists sp12; 600 create snapshot sp12 for account acc01; 601 insert into tm1 values (1,1,1), (2,2,2); 602 insert into tm2 values (1,1,1), (2,2,2); 603 select * from ti1 {snapshot = 'sp12'}; 604 a b c 605 1 1 1 606 2 2 2 607 select * from tm1; 608 a b c 609 1 1 1 610 2 2 2 611 select * from ti2 {snapshot = 'sp12'}; 612 a b c 613 1 1 1 614 2 2 2 615 select * from tm2; 616 a b c 617 1 1 1 618 2 2 2 619 drop snapshot if exists sp13; 620 create snapshot sp13 for account acc01; 621 alter table ti1 add constraint fi1 foreign key (b) references ti2(a); 622 alter table tm1 add constraint fm1 foreign key (b) references tm2(a); 623 drop snapshot if exists sp14; 624 create snapshot sp14 for account acc01; 625 show create table ti1 {snapshot = 'sp14'}; 626 Table Create Table 627 ti1 CREATE TABLE `ti1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL,\nCONSTRAINT `fi1` FOREIGN KEY (`b`) REFERENCES `ti2` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 628 show create table tm1 {snapshot = 'sp13'}; 629 Table Create Table 630 tm1 CREATE TABLE `tm1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL\n) 631 show create table ti1 {snapshot = 'sp14'}; 632 Table Create Table 633 ti1 CREATE TABLE `ti1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL,\nCONSTRAINT `fi1` FOREIGN KEY (`b`) REFERENCES `ti2` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 634 show create table tm1 {snapshot = 'sp13'}; 635 Table Create Table 636 tm1 CREATE TABLE `tm1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL\n) 637 alter table ti1 drop foreign key fi1; 638 alter table tm1 drop foreign key fm1; 639 truncate ti1; 640 truncate tm1; 641 drop snapshot if exists sp15; 642 create snapshot sp15 for account acc01; 643 show create table ti1 {snapshot = 'sp14'}; 644 Table Create Table 645 ti1 CREATE TABLE `ti1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL,\nCONSTRAINT `fi1` FOREIGN KEY (`b`) REFERENCES `ti2` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 646 show create table tm1 {snapshot = 'sp15'}; 647 Table Create Table 648 tm1 CREATE TABLE `tm1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL\n) 649 show create table ti1 {snapshot = 'sp14'}; 650 Table Create Table 651 ti1 CREATE TABLE `ti1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL,\nCONSTRAINT `fi1` FOREIGN KEY (`b`) REFERENCES `ti2` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 652 show create table tm1 {snapshot = 'sp15'}; 653 Table Create Table 654 tm1 CREATE TABLE `tm1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL\n) 655 select count(*) from ti1; 656 count(*) 657 0 658 select count(*) from tm1; 659 count(*) 660 0 661 select count(*) from ti2; 662 count(*) 663 2 664 select count(*) from tm2; 665 count(*) 666 2 667 restore account acc01 from snapshot sp11; 668 show databases; 669 Database 670 db03 671 information_schema 672 mo_catalog 673 mysql 674 system 675 system_metrics 676 select * from db03.ti1; 677 a b c 678 select * from db03.tm1; 679 a b c 680 select * from db03.ti2; 681 a b c 682 select * from db03.tm2; 683 a b c 684 show create table db03.ti1; 685 Table Create Table 686 ti1 CREATE TABLE `ti1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT null,\n`c` INT DEFAULT null\n) 687 show create table db03.tm1; 688 Table Create Table 689 tm1 CREATE TABLE `tm1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT null,\n`c` INT DEFAULT null\n) 690 show create table db03.ti2; 691 Table Create Table 692 ti2 CREATE TABLE `ti2` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` INT DEFAULT null,\n`c` INT DEFAULT null,\nPRIMARY KEY (`a`)\n) 693 show create table db03.tm2; 694 Table Create Table 695 tm2 CREATE TABLE `tm2` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` INT DEFAULT null,\n`c` INT DEFAULT null,\nPRIMARY KEY (`a`)\n) 696 restore account acc01 from snapshot sp14; 697 show databases; 698 Database 699 db03 700 information_schema 701 mo_catalog 702 mysql 703 system 704 system_metrics 705 select * from db03.ti1; 706 a b c 707 1 1 1 708 2 2 2 709 select * from db03.tm1; 710 a b c 711 1 1 1 712 2 2 2 713 select * from db03.ti2; 714 a b c 715 1 1 1 716 2 2 2 717 select * from db03.tm2; 718 a b c 719 1 1 1 720 2 2 2 721 show create table db03.ti1; 722 Table Create Table 723 ti1 CREATE TABLE `ti1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT null,\n`c` INT DEFAULT null,\nCONSTRAINT `fi1` FOREIGN KEY (`b`) REFERENCES `ti2` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 724 show create table db03.tm1; 725 Table Create Table 726 tm1 CREATE TABLE `tm1` (\n`a` INT NOT NULL,\n`b` INT DEFAULT null,\n`c` INT DEFAULT null,\nCONSTRAINT `fm1` FOREIGN KEY (`b`) REFERENCES `tm2` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 727 show create table db03.ti2; 728 Table Create Table 729 ti2 CREATE TABLE `ti2` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` INT DEFAULT null,\n`c` INT DEFAULT null,\nPRIMARY KEY (`a`)\n) 730 show create table db03.tm2; 731 Table Create Table 732 tm2 CREATE TABLE `tm2` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` INT DEFAULT null,\n`c` INT DEFAULT null,\nPRIMARY KEY (`a`)\n) 733 show snapshots; 734 SNAPSHOT_NAME TIMESTAMP SNAPSHOT_LEVEL ACCOUNT_NAME DATABASE_NAME TABLE_NAME 735 sp15 2024-05-17 04:45:37.101762 account acc01 736 sp14 2024-05-17 04:45:36.930036 account acc01 737 sp13 2024-05-17 04:45:36.831314 account acc01 738 sp12 2024-05-17 04:45:36.720739 account acc01 739 sp11 2024-05-17 04:45:36.632035 account acc01 740 drop database db03; 741 drop snapshot sp15; 742 drop snapshot sp14; 743 drop snapshot sp13; 744 drop snapshot sp12; 745 drop snapshot sp11; 746 drop database if exists db04; 747 create database db04; 748 use db04; 749 create table table01(col1 int primary key ); 750 insert into table01 values (1); 751 insert into table01 values (2); 752 drop database if exists db05; 753 create database db05; 754 use db05; 755 create table table01(col1 int primary key ); 756 insert into table01 values (1); 757 insert into table01 values (2); 758 insert into table01 values (3); 759 drop database if exists db06; 760 create database db06; 761 use db06; 762 create table table01(col1 int primary key ); 763 insert into table01 values (1); 764 insert into table01 values (2); 765 insert into table01 values (3); 766 insert into table01 values (4); 767 select * from db04.table01; 768 col1 769 1 770 2 771 select * from db05.table01; 772 col1 773 1 774 2 775 3 776 select * from db06.table01; 777 col1 778 1 779 2 780 3 781 4 782 drop snapshot if exists sp13; 783 create snapshot sp13 for account acc01; 784 insert into db04.table01 values (200); 785 insert into db05.table01 values (400); 786 restore account acc01 database db04 from snapshot sp13; 787 show databases; 788 Database 789 db04 790 db05 791 db06 792 information_schema 793 mo_catalog 794 mysql 795 system 796 system_metrics 797 use db04; 798 select count(*) from db04.table01; 799 count(*) 800 2 801 select * from db04.table01; 802 col1 803 1 804 2 805 use db05; 806 select count(*) from db05.table01; 807 count(*) 808 4 809 select * from db05.table01; 810 col1 811 1 812 2 813 3 814 400 815 use db06; 816 select count(*) from db06.table01; 817 count(*) 818 4 819 select * from db06.table01; 820 col1 821 1 822 2 823 3 824 4 825 drop snapshot sp13; 826 drop database db04; 827 drop database db05; 828 drop database db06; 829 drop database if exists db07; 830 create database db07; 831 use db07; 832 create table table01 (col1 int, col2 enum ('a','b','c')); 833 insert into table01 values(1,'a'); 834 insert into table01 values(2, 'b'); 835 create table table02 (col1 int unique key, col2 enum ('a','b','c')); 836 insert into table02 values(1,'a'); 837 insert into table02 values(2, 'b'); 838 insert into table02 values(3, 'b'); 839 drop database if exists db08; 840 create database db08; 841 use db08; 842 create table index01(col1 int,key key1(col1)); 843 insert into index01 values (1); 844 insert into index01 values (2); 845 show create table db07.table02; 846 Table Create Table 847 table02 CREATE TABLE `table02` (\n`col1` INT DEFAULT NULL,\n`col2` ENUM('a','b','c') DEFAULT NULL,\nUNIQUE KEY `col1` (`col1`)\n) 848 drop snapshot if exists sp14; 849 create snapshot sp14 for account acc01; 850 use db07; 851 drop table table01; 852 alter table table02 add column newC int first; 853 show create table table02; 854 Table Create Table 855 table02 CREATE TABLE `table02` (\n`newc` INT DEFAULT NULL,\n`col1` INT DEFAULT NULL,\n`col2` ENUM('a','b','c') DEFAULT NULL,\nUNIQUE KEY `col1` (`col1`)\n) 856 show create table table02 {snapshot = 'sp14'}; 857 Table Create Table 858 table02 CREATE TABLE `table02` (\n`col1` INT DEFAULT NULL,\n`col2` ENUM('a','b','c') DEFAULT NULL,\nUNIQUE KEY `col1` (`col1`)\n) 859 drop database db08; 860 restore account acc01 database db07 from snapshot sp14; 861 restore account acc01 database db08 from snapshot sp14; 862 show databases; 863 Database 864 db07 865 db08 866 information_schema 867 mo_catalog 868 mysql 869 system 870 system_metrics 871 use db07; 872 show tables; 873 Tables_in_db07 874 table01 875 table02 876 show create table table01 {snapshot = 'sp14'}; 877 Table Create Table 878 table01 CREATE TABLE `table01` (\n`col1` INT DEFAULT NULL,\n`col2` ENUM('a','b','c') DEFAULT NULL\n) 879 show create table table01; 880 Table Create Table 881 table01 CREATE TABLE `table01` (\n`col1` INT DEFAULT null,\n`col2` ENUM('a','b','c') DEFAULT null\n) 882 select * from table01; 883 col1 col2 884 1 a 885 2 b 886 drop snapshot if exists sp15; 887 create snapshot sp15 for account acc01; 888 restore account acc01 database db07 from snapshot sp15; 889 restore account acc01 database db08 from snapshot sp15; 890 use db08; 891 show tables; 892 Tables_in_db08 893 index01 894 select * from index01; 895 col1 896 1 897 2 898 show create table index01; 899 Table Create Table 900 index01 CREATE TABLE `index01` (\n`col1` INT DEFAULT null,\nKEY `key1` (`col1`)\n) 901 use db07; 902 show tables; 903 Tables_in_db07 904 table01 905 table02 906 show create table table01 {snapshot = 'sp14'}; 907 Table Create Table 908 table01 CREATE TABLE `table01` (\n`col1` INT DEFAULT NULL,\n`col2` ENUM('a','b','c') DEFAULT NULL\n) 909 show create table table01; 910 Table Create Table 911 table01 CREATE TABLE `table01` (\n`col1` INT DEFAULT null,\n`col2` ENUM('a','b','c') DEFAULT null\n) 912 show snapshots; 913 SNAPSHOT_NAME TIMESTAMP SNAPSHOT_LEVEL ACCOUNT_NAME DATABASE_NAME TABLE_NAME 914 sp15 2024-05-17 04:45:39.30566 account acc01 915 sp14 2024-05-17 04:45:38.940086 account acc01 916 drop snapshot sp14; 917 drop snapshot sp15; 918 drop database db07; 919 drop database if exists db08; 920 create database db08; 921 use db08; 922 create table db08 (col1 int); 923 insert into db08 values(1), (100), (20000); 924 drop snapshot if exists sp15; 925 create snapshot sp15 for account acc01; 926 insert into db08 (col1) values (3000); 927 restore account acc01 database db from snapshot sp15; 928 invalid database db 929 drop snapshot sp15; 930 drop database db08; 931 drop database if exists db09; 932 create database db09; 933 use db09; 934 drop table if exists index01; 935 create table index01( 936 col1 int not null, 937 col2 date not null, 938 col3 varchar(16) not null, 939 col4 int unsigned not null, 940 primary key (col1) 941 ); 942 insert into index01 values(1, '1980-12-17','Abby', 21); 943 insert into index01 values(2, '1981-02-20','Bob', 22); 944 insert into index01 values(3, '1981-02-20','Bob', 22); 945 select count(*) from index01; 946 count(*) 947 3 948 drop table if exists index02; 949 create table index02(col1 char, col2 int, col3 binary); 950 insert into index02 values('a', 33, 1); 951 insert into index02 values('c', 231, 0); 952 alter table index02 add key pk(col1) comment 'primary key'; 953 select count(*) from index02; 954 count(*) 955 2 956 drop database if exists db10; 957 create database db10; 958 use db10; 959 drop table if exists index03; 960 create table index03 ( 961 emp_no int not null, 962 birth_date date not null, 963 first_name varchar(14) not null, 964 last_name varchar(16) not null, 965 gender varchar(5) not null, 966 hire_date date not null, 967 primary key (emp_no) 968 ) partition by range columns (emp_no)( 969 partition p01 values less than (100001), 970 partition p02 values less than (200001), 971 partition p03 values less than (300001), 972 partition p04 values less than (400001) 973 ); 974 insert into index03 values (9001,'1980-12-17', 'SMITH', 'CLERK', 'F', '2008-12-17'), 975 (9002,'1981-02-20', 'ALLEN', 'SALESMAN', 'F', '2008-02-20'); 976 drop snapshot if exists sp16; 977 create snapshot sp16 for account acc01; 978 use db09; 979 delete from index02 where col1 = 'a'; 980 select * from index02; 981 col1 col2 col3 982 c 231 0 983 update index01 set col1 = 1000 where col1 = 1; 984 select * from index01; 985 col1 col2 col3 col4 986 2 1981-02-20 Bob 22 987 3 1981-02-20 Bob 22 988 1000 1980-12-17 Abby 21 989 use db10; 990 truncate index03; 991 select * from index03; 992 emp_no birth_date first_name last_name gender hire_date 993 restore account acc01 database db09 table index01 from snapshot sp16; 994 restore account acc01 database db10 table index03 from snapshot sp16; 995 use db09; 996 select * from index02; 997 col1 col2 col3 998 c 231 0 999 select * from index01; 1000 col1 col2 col3 col4 1001 1 1980-12-17 Abby 21 1002 2 1981-02-20 Bob 22 1003 3 1981-02-20 Bob 22 1004 show create table index01; 1005 Table Create Table 1006 index01 CREATE TABLE `index01` (\n`col1` INT NOT NULL,\n`col2` DATE NOT NULL,\n`col3` VARCHAR(16) NOT NULL,\n`col4` INT UNSIGNED NOT NULL,\nPRIMARY KEY (`col1`)\n) 1007 show create table index02; 1008 Table Create Table 1009 index02 CREATE TABLE `index02` (\n`col1` CHAR(1) DEFAULT NULL,\n`col2` INT DEFAULT NULL,\n`col3` BINARY(1) DEFAULT NULL,\nKEY `pk` (`col1`) COMMENT 'primary key'\n) 1010 use db10; 1011 select * from index03; 1012 emp_no birth_date first_name last_name gender hire_date 1013 9001 1980-12-17 SMITH CLERK F 2008-12-17 1014 9002 1981-02-20 ALLEN SALESMAN F 2008-02-20 1015 show snapshots; 1016 SNAPSHOT_NAME TIMESTAMP SNAPSHOT_LEVEL ACCOUNT_NAME DATABASE_NAME TABLE_NAME 1017 sp16 2024-05-17 04:45:40.39858 account acc01 1018 drop database db09; 1019 drop database db10; 1020 drop snapshot sp16; 1021 drop database if exists db11; 1022 create database db11; 1023 use db11; 1024 drop table if exists pri01; 1025 create table pri01 (col1 int, col2 decimal); 1026 insert into pri01 (col1, col2) values (1,2378.328839842); 1027 insert into pri01 values (234, -3923.2342342); 1028 select * from pri01; 1029 col1 col2 1030 1 2378 1031 234 -3923 1032 drop snapshot if exists sp17; 1033 create snapshot sp17 for account acc01; 1034 alter table pri01 add constraint primary key(col1); 1035 insert into pri01 values (23423, 32432543.3242); 1036 insert into pri01 values (234222, -3923.2342342); 1037 drop snapshot if exists sp18; 1038 create snapshot sp18 for account acc01; 1039 restore account acc01 database db11 table pri01 from snapshot sp18; 1040 show create table pri01; 1041 Table Create Table 1042 pri01 CREATE TABLE `pri01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT null,\nPRIMARY KEY (`col1`)\n) 1043 select * from pri01; 1044 col1 col2 1045 1 2378 1046 234 -3923 1047 23423 32432543 1048 234222 -3923 1049 select count(*) from pri01; 1050 count(*) 1051 4 1052 restore account acc01 database db11 table pri01 from snapshot sp17; 1053 show create table pri01; 1054 Table Create Table 1055 pri01 CREATE TABLE `pri01` (\n`col1` INT DEFAULT null,\n`col2` DECIMAL(38,0) DEFAULT null\n) 1056 select * from pri01; 1057 col1 col2 1058 1 2378 1059 234 -3923 1060 select count(*) from pri01; 1061 count(*) 1062 2 1063 restore account acc01 database db11 table pri01 from snapshot sp18; 1064 show create table pri01; 1065 Table Create Table 1066 pri01 CREATE TABLE `pri01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT null,\nPRIMARY KEY (`col1`)\n) 1067 select * from pri01; 1068 col1 col2 1069 1 2378 1070 234 -3923 1071 23423 32432543 1072 234222 -3923 1073 select count(*) from pri01; 1074 count(*) 1075 4 1076 insert into pri01 values(23420, 32432543.3242); 1077 select * from pri01; 1078 col1 col2 1079 1 2378 1080 234 -3923 1081 23423 32432543 1082 234222 -3923 1083 23420 32432543 1084 show columns from pri01; 1085 Field Type Null Key Default Extra Comment 1086 col1 INT(32) NO PRI null 1087 col2 DECIMAL128(38) YES null 1088 select count(*) from mo_catalog.mo_tables{snapshot = 'sp18'} where reldatabase = 'db11'; 1089 count(*) 1090 1 1091 select * from mo_catalog.mo_database{snapshot = 'sp18'} where datname = 'db11'; 1092 dat_id datname dat_catalog_name dat_createsql owner creator created_time account_id dat_type 1093 279542 db11 def create database db11 2 2 2024-05-17 12:45:40 46 1094 select attname from mo_catalog.mo_columns{snapshot = 'sp18'} where att_database = 'db11'; 1095 attname 1096 __mo_rowid 1097 col1 1098 col2 1099 drop database db11; 1100 drop snapshot sp18; 1101 drop snapshot sp17; 1102 drop database if exists db12; 1103 create database db12; 1104 use db12; 1105 create table table01 (col int); 1106 create table table02 (col char); 1107 create table table03 (col char); 1108 create table table04 (col binary); 1109 drop snapshot if exists sp19; 1110 create snapshot sp19 for account acc01; 1111 insert into table01 values (1); 1112 insert into table02 values ('1'); 1113 insert into table03 values ('3'); 1114 insert into table04 values ('1'); 1115 restore account acc01 database db12 table table01 from snapshot sp19; 1116 restore account acc01 database db12 table table02 from snapshot sp19; 1117 restore account acc01 database db12 table table03 from snapshot sp19; 1118 restore account acc01 database db12 table table04 from snapshot sp19; 1119 select * from table01; 1120 col 1121 select * from table02; 1122 col 1123 select * from table03; 1124 col 1125 select * from table04; 1126 col 1127 drop database db12; 1128 drop snapshot sp19; 1129 drop account acc01;