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