github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/snapshot/sys_restore_to_newnonsys_account.result (about) 1 drop account if exists acc01; 2 create account acc01 admin_name = 'test_account' identified by '111'; 3 drop account if exists acc02; 4 create account acc02 admin_name = 'test_account' identified by '111'; 5 drop database if exists acc_test01; 6 create database acc_test01; 7 use acc_test01; 8 drop table if exists s3t; 9 create table s3t (col1 int, col2 int, col3 int, primary key(col1, col2)); 10 insert into s3t select result, 2, 12 from generate_series(1, 30000, 1) g; 11 select count(*) from s3t; 12 count(*) 13 30000 14 select sum(col1) from s3t; 15 sum(col1) 16 450015000 17 select avg(col1) from s3t; 18 avg(col1) 19 15000.5 20 select count(col3) from s3t where col1 > 1000; 21 count(col3) 22 29000 23 alter table s3t add column col4 int after col2; 24 drop snapshot if exists sp01; 25 create snapshot sp01 for account acc01; 26 show snapshots; 27 SNAPSHOT_NAME TIMESTAMP SNAPSHOT_LEVEL ACCOUNT_NAME DATABASE_NAME TABLE_NAME 28 sp01 2024-05-13 03:34:34.860675 account acc01 29 use acc_test01; 30 insert into s3t values (300001, 34, 23, 1); 31 select count(*) from s3t; 32 count(*) 33 30001 34 select * from s3t where col1 = 23; 35 col1 col2 col4 col3 36 23 2 null 12 37 restore account acc01 from snapshot sp01 to account acc02; 38 show databases; 39 Database 40 acc_test01 41 information_schema 42 mo_catalog 43 mysql 44 system 45 system_metrics 46 use acc_test01; 47 show tables; 48 Tables_in_acc_test01 49 s3t 50 select count(*) from s3t; 51 count(*) 52 30000 53 select sum(col1) from s3t; 54 sum(col1) 55 450015000 56 select avg(col1) from s3t; 57 avg(col1) 58 15000.5 59 select count(col3) from s3t where col1 > 1000; 60 count(col3) 61 29000 62 use acc_test01; 63 alter table s3t drop column col4; 64 show create table s3t; 65 Table Create Table 66 s3t CREATE TABLE `s3t` (\n`col1` INT NOT NULL,\n`col2` INT NOT NULL,\n`col3` INT DEFAULT NULL,\nPRIMARY KEY (`col1`,`col2`)\n) 67 drop snapshot if exists sp02; 68 create snapshot sp02 for account acc01; 69 restore account acc01 from snapshot sp02 to account acc02; 70 use acc_test01; 71 show create table s3t; 72 Table Create Table 73 s3t CREATE TABLE `s3t` (\n`col1` INT NOT NULL,\n`col2` INT NOT NULL,\n`col3` INT DEFAULT null,\nPRIMARY KEY (`col1`,`col2`)\n) 74 restore account acc01 from snapshot sp01 to account acc02; 75 show databases; 76 Database 77 acc_test01 78 information_schema 79 mo_catalog 80 mysql 81 system 82 system_metrics 83 use acc_test01; 84 show tables; 85 Tables_in_acc_test01 86 s3t 87 select count(*) from s3t; 88 count(*) 89 30000 90 select sum(col1) from s3t; 91 sum(col1) 92 450015000 93 select avg(col1) from s3t; 94 avg(col1) 95 15000.5 96 select count(col3) from s3t where col1 > 1000; 97 count(col3) 98 29000 99 show create table s3t; 100 Table Create Table 101 s3t CREATE TABLE `s3t` (\n`col1` INT NOT NULL,\n`col2` INT NOT NULL,\n`col4` INT DEFAULT null,\n`col3` INT DEFAULT null,\nPRIMARY KEY (`col1`,`col2`)\n) 102 drop database acc_test01; 103 drop database acc_test01; 104 drop snapshot sp01; 105 drop snapshot sp02; 106 drop database if exists acc_test02; 107 create database acc_test02; 108 use acc_test02; 109 drop table if exists pri01; 110 create table pri01( 111 deptno int unsigned comment '部门编号', 112 dname varchar(15) comment '部门名称', 113 loc varchar(50) comment '部门所在位置', 114 primary key(deptno) 115 ) comment='部门表'; 116 insert into pri01 values (10,'ACCOUNTING','NEW YORK'); 117 insert into pri01 values (20,'RESEARCH','DALLAS'); 118 insert into pri01 values (30,'SALES','CHICAGO'); 119 insert into pri01 values (40,'OPERATIONS','BOSTON'); 120 select count(*) from pri01; 121 count(*) 122 4 123 drop table if exists aff01; 124 create table aff01( 125 empno int unsigned auto_increment COMMENT '雇员编号', 126 ename varchar(15) comment '雇员姓名', 127 job varchar(10) comment '雇员职位', 128 mgr int unsigned comment '雇员对应的领导的编号', 129 hiredate date comment '雇员的雇佣日期', 130 sal decimal(7,2) comment '雇员的基本工资', 131 comm decimal(7,2) comment '奖金', 132 deptno int unsigned comment '所在部门', 133 primary key(empno), 134 constraint `c1` foreign key (deptno) references pri01 (deptno) 135 ); 136 insert into aff01 values (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 137 insert into aff01 values (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 138 insert into aff01 values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 139 insert into aff01 values (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 140 insert into aff01 values (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 141 insert into aff01 values (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 142 insert into aff01 values (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 143 insert into aff01 values (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 144 insert into aff01 values (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 145 insert into aff01 values (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 146 insert into aff01 values (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 147 insert into aff01 values (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 148 insert into aff01 values (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 149 insert into aff01 values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 150 select count(*) from aff01; 151 count(*) 152 14 153 drop database if exists acc_test03; 154 create database acc_test03; 155 use acc_test03; 156 drop table if exists table01; 157 create table table01(col1 int primary key , col2 decimal, col3 char, col4 varchar(20), col5 text, col6 double); 158 insert into table01 values (1, 2, 'a', '23eiojf', 'r23v324r23rer', 3923.324); 159 insert into table01 values (2, 3, 'b', '32r32r', 'database', 1111111); 160 create table table02 (col1 int unique key, col2 varchar(20)); 161 insert into table02 (col1, col2) values (133, 'database'); 162 create table table03(a INT primary key AUTO_INCREMENT, b INT, c INT); 163 create table table04(a INT primary key AUTO_INCREMENT, b INT, c INT); 164 insert into table03 values (1,1,1), (2,2,2); 165 insert into table04 values (0,1,2), (2,3,4); 166 select count(*) from table01; 167 count(*) 168 2 169 select count(*) from table02; 170 count(*) 171 1 172 select count(*) from table03; 173 count(*) 174 2 175 select count(*) from table04; 176 count(*) 177 2 178 drop database if exists acc_test04; 179 create database acc_test04; 180 use acc_test04; 181 drop table if exists index03; 182 create table index03 ( 183 emp_no int not null, 184 birth_date date not null, 185 first_name varchar(14) not null, 186 last_name varchar(16) not null, 187 gender varchar(5) not null, 188 hire_date date not null, 189 primary key (emp_no) 190 ) partition by range columns (emp_no)( 191 partition p01 values less than (100001), 192 partition p02 values less than (200001), 193 partition p03 values less than (300001), 194 partition p04 values less than (400001) 195 ); 196 insert into index03 values (9001,'1980-12-17', 'SMITH', 'CLERK', 'F', '2008-12-17'), 197 (9002,'1981-02-20', 'ALLEN', 'SALESMAN', 'F', '2008-02-20'); 198 select count(*) from acc_test02.pri01; 199 count(*) 200 4 201 select count(*) from acc_test02.aff01; 202 count(*) 203 14 204 select * from acc_test02.pri01; 205 deptno dname loc 206 10 ACCOUNTING NEW YORK 207 20 RESEARCH DALLAS 208 30 SALES CHICAGO 209 40 OPERATIONS BOSTON 210 show create table acc_test02.pri01; 211 Table Create Table 212 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='部门表' 213 show create table acc_test02.aff01; 214 Table Create Table 215 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) 216 select count(*) from acc_test03.table01; 217 count(*) 218 2 219 select count(*) from acc_test03.table02; 220 count(*) 221 1 222 select count(*) from acc_test03.table03; 223 count(*) 224 2 225 select count(*) from acc_test03.table04; 226 count(*) 227 2 228 show create table acc_test03.table01; 229 Table Create Table 230 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,\nPRIMARY KEY (`col1`)\n) 231 show create table acc_test03.table02; 232 Table Create Table 233 table02 CREATE TABLE `table02` (\n`col1` INT DEFAULT NULL,\n`col2` VARCHAR(20) DEFAULT NULL,\nUNIQUE KEY `col1` (`col1`)\n) 234 show create table acc_test03.table03; 235 Table Create Table 236 table03 CREATE TABLE `table03` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL,\nPRIMARY KEY (`a`)\n) 237 show create table acc_test03.table04; 238 Table Create Table 239 table04 CREATE TABLE `table04` (\n`a` INT NOT NULL AUTO_INCREMENT,\n`b` INT DEFAULT NULL,\n`c` INT DEFAULT NULL,\nPRIMARY KEY (`a`)\n) 240 select count(*) from acc_test04.index03; 241 count(*) 242 2 243 show create table acc_test04.index03; 244 Table Create Table 245 index03 CREATE TABLE `index03` (\n`emp_no` INT NOT NULL,\n`birth_date` DATE NOT NULL,\n`first_name` VARCHAR(14) NOT NULL,\n`last_name` VARCHAR(16) NOT NULL,\n`gender` VARCHAR(5) NOT NULL,\n`hire_date` DATE NOT NULL,\nPRIMARY KEY (`emp_no`)\n) partition by range columns (emp_no) (partition p01 values less than (100001), partition p02 values less than (200001), partition p03 values less than (300001), partition p04 values less than (400001)) 246 drop snapshot if exists sp04; 247 create snapshot sp04 for account acc01; 248 insert into acc_test02.pri01 values (50,'ACCOUNTING','NEW YORK'); 249 insert into acc_test02.aff01 values (9000,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,50); 250 truncate table acc_test03.table01; 251 drop table acc_test03.table02; 252 delete from acc_test03.table03 where a = 1; 253 update acc_test03.table04 set a = 6000 where a = 1; 254 select count(*) from acc_test02.pri01; 255 count(*) 256 5 257 select count(*) from acc_test02.aff01; 258 count(*) 259 15 260 select * from acc_test03.table01; 261 col1 col2 col3 col4 col5 col6 262 select count(*) from acc_test03.table03; 263 count(*) 264 1 265 select * from acc_test03.table04; 266 a b c 267 0 1 2 268 2 3 4 269 show create table acc_test04.index03; 270 Table Create Table 271 index03 CREATE TABLE `index03` (\n`emp_no` INT NOT NULL,\n`birth_date` DATE NOT NULL,\n`first_name` VARCHAR(14) NOT NULL,\n`last_name` VARCHAR(16) NOT NULL,\n`gender` VARCHAR(5) NOT NULL,\n`hire_date` DATE NOT NULL,\nPRIMARY KEY (`emp_no`)\n) partition by range columns (emp_no) (partition p01 values less than (100001), partition p02 values less than (200001), partition p03 values less than (300001), partition p04 values less than (400001)) 272 restore account acc01 from snapshot sp04 to account acc02; 273 show databases; 274 Database 275 acc_test02 276 acc_test03 277 acc_test04 278 information_schema 279 mo_catalog 280 mysql 281 system 282 system_metrics 283 select count(*) from acc_test02.pri01; 284 count(*) 285 4 286 select count(*) from acc_test02.aff01; 287 count(*) 288 14 289 select count(*) from acc_test03.table01; 290 count(*) 291 2 292 select count(*) from acc_test03.table02; 293 count(*) 294 1 295 select count(*) from acc_test03.table03; 296 count(*) 297 2 298 select count(*) from acc_test03.table04; 299 count(*) 300 2 301 select count(*) from acc_test03.table04; 302 count(*) 303 2 304 drop database acc_test03; 305 drop snapshot sp04; 306 drop database if exists test01; 307 create database test01; 308 use test01; 309 drop table if exists table01; 310 create table table01(col1 int primary key , col2 decimal, col3 char, col4 varchar(20), col5 text, col6 double); 311 insert into table01 values (1, 2, 'a', '23eiojf', 'r23v324r23rer', 3923.324); 312 insert into table01 values (2, 3, 'b', '32r32r', 'database', 1111111); 313 drop table if exists table02; 314 create table table02 (col1 int unique key, col2 varchar(20)); 315 insert into table02 (col1, col2) values (133, 'database'); 316 select * from table01; 317 col1 col2 col3 col4 col5 col6 318 1 2 a 23eiojf r23v324r23rer 3923.324 319 2 3 b 32r32r database 1111111.0 320 select * from table02; 321 col1 col2 322 133 database 323 drop snapshot if exists sp07; 324 create snapshot sp07 for account acc01; 325 use test01; 326 drop table table01; 327 insert into table02 values(134, 'database'); 328 drop snapshot if exists sp08; 329 create snapshot sp08 for account acc01; 330 show snapshots; 331 SNAPSHOT_NAME TIMESTAMP SNAPSHOT_LEVEL ACCOUNT_NAME DATABASE_NAME TABLE_NAME 332 sp08 2024-05-13 03:35:03.806089 account acc01 333 sp07 2024-05-13 03:35:03.712779 account acc01 334 restore account acc01 from snapshot sp07 to account acc02; 335 restore account acc01 from snapshot sp08 to account acc02; 336 use test01; 337 show tables; 338 Tables_in_test01 339 table02 340 select * from table01; 341 SQL parser error: table "table01" does not exist 342 select * from table02; 343 col1 col2 344 133 database 345 134 database 346 show create table table02; 347 Table Create Table 348 table02 CREATE TABLE `table02` (\n`col1` INT DEFAULT NULL,\n`col2` VARCHAR(20) DEFAULT NULL,\nUNIQUE KEY `col1` (`col1`)\n) 349 drop database test01; 350 drop database test01; 351 Can't drop database 'test01'; database doesn't exist 352 drop snapshot sp07; 353 drop snapshot sp08; 354 drop database if exists test02; 355 create database test02; 356 use test02; 357 drop table if exists table01; 358 create table table01(col1 int primary key , col2 decimal, col3 char, col4 varchar(20), col5 text, col6 double); 359 insert into table01 values (1, 2, 'a', '23eiojf', 'r23v324r23rer', 3923.324); 360 insert into table01 values (2, 3, 'b', '32r32r', 'database', 1111111); 361 drop table if exists table02; 362 create table table02 (col1 int unique key, col2 varchar(20)); 363 insert into table02 (col1, col2) values (133, 'database'); 364 select * from table01; 365 col1 col2 col3 col4 col5 col6 366 1 2 a 23eiojf r23v324r23rer 3923.324 367 2 3 b 32r32r database 1111111.0 368 select * from table02; 369 col1 col2 370 133 database 371 drop snapshot if exists sp09; 372 create snapshot sp09 for account acc01; 373 use test02; 374 drop table table01; 375 insert into table02 values(134, 'database'); 376 alter table table02 add column new decimal after col2; 377 drop snapshot if exists sp10; 378 create snapshot sp10 for account acc01; 379 restore account acc01 from snapshot sp10 to account acc02; 380 use test02; 381 show create table table01; 382 no such table test02.table01 383 show create table table02; 384 Table Create Table 385 table02 CREATE TABLE `table02` (\n`col1` INT DEFAULT null,\n`col2` VARCHAR(20) DEFAULT null,\n`new` DECIMAL(38,0) DEFAULT null,\nUNIQUE KEY `col1` (`col1`)\n) 386 select * from table02; 387 col1 col2 new 388 133 database null 389 134 database null 390 select * from table01; 391 SQL parser error: table "table01" does not exist 392 restore account acc01 from snapshot sp09 to account acc02; 393 drop database test02; 394 drop snapshot sp09; 395 drop snapshot sp10; 396 drop database if exists test01; 397 create database test01; 398 use test01; 399 drop table if exists rs01; 400 create table rs01 (col1 int, col2 decimal(6), col3 varchar(30)); 401 insert into rs01 values (1, null, 'database'); 402 insert into rs01 values (2, 38291.32132, 'database'); 403 insert into rs01 values (3, null, 'database management system'); 404 insert into rs01 values (4, 10, null); 405 insert into rs01 values (1, -321.321, null); 406 insert into rs01 values (2, -1, null); 407 select count(*) from rs01; 408 count(*) 409 6 410 drop snapshot if exists sp03; 411 create snapshot sp03 for account acc01; 412 use test01; 413 delete from rs01 where col1 = 4; 414 insert into rs01 values (10, -1, null); 415 select count(*) from rs01; 416 count(*) 417 6 418 restore account acc01 from snapshot sp03 to account sys; 419 internal error: non-sys account's snapshot can't restore to sys account 420 drop snapshot sp03; 421 drop database test01; 422 drop account acc01; 423 drop account acc02;