Debian Codegraph @

mysql-test/suite/rpl/r/rpl_replica_stored_generated_columns.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 CREATE TABLE `log_data` (
7 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
8 `series` INT UNSIGNED NOT NULL,
9 `tm_ts` VARCHAR(14) DEFAULT NULL,
10 `data` BLOB,
11 `v1_gcol` INT GENERATED ALWAYS AS (`series` + 1) VIRTUAL NOT NULL,
12 `s1_gcol` INT GENERATED ALWAYS AS (`series` + 1) STORED NOT NULL,
13 `v2_gcol` INT GENERATED ALWAYS AS (`series` + 2) VIRTUAL NOT NULL,
14 `s2_gcol` INT GENERATED ALWAYS AS (`series` + 2) STORED NOT NULL,
15 PRIMARY KEY (`id`),
16 KEY `v1_idx` (`v1_gcol`),
17 KEY `s1_idx` (`s1_gcol`)
18 ) ENGINE = InnoDB;
19 include/sync_slave_sql_with_master.inc
20 ALTER TABLE
21 `log_data`
22 MODIFY
23 `v1_gcol` INT GENERATED ALWAYS AS (`series` - 1) VIRTUAL NOT NULL,
24 MODIFY
25 `s1_gcol` INT GENERATED ALWAYS AS (`series` - 1) STORED NOT NULL,
26 MODIFY
27 `v2_gcol` INT GENERATED ALWAYS AS (`series` + 3) VIRTUAL NOT NULL,
28 MODIFY
29 `s2_gcol` INT GENERATED ALWAYS AS (`series` + 3) STORED NOT NULL,
30 ADD
31 COLUMN `s_len` INT GENERATED ALWAYS AS (
32 LENGTH(`data`)
33 ) STORED,
34 ADD
35 COLUMN `s_date` DATE GENERATED ALWAYS AS (
36 CAST(`tm_ts` AS DATE)
37 ) STORED,
38 ADD
39 COLUMN `v_date` DATE GENERATED ALWAYS AS (
40 CAST(`tm_ts` AS DATE)
41 ) VIRTUAL,
42 ADD
43 COLUMN `s_hash` char(40) GENERATED ALWAYS AS (
44 SHA(
45 HEX(`data`)
46 )
47 ) STORED,
48 ADD
49 COLUMN `v_hash` char(40) GENERATED ALWAYS AS (
50 SHA(
51 HEX(`data`)
52 )
53 ) VIRTUAL,
54 ADD
55 KEY `k_s_len` (s_len),
56 ADD
57 KEY `k_s_date` (s_date),
58 ADD
59 KEY `k_v_date`(v_date),
60 ADD
61 KEY `k_s_hash`(s_hash),
62 ADD
63 KEY `k_v_hash`(v_hash);
64 [connection master]
65 INSERT INTO `log_data` (`series`, `tm_ts`, `data`)
66 SELECT *
67 FROM
68 (
69 WITH RECURSIVE sequence AS (
70 SELECT
71 1 AS level
72 UNION ALL
73 SELECT
74 level + 1
75 FROM
76 sequence
77 WHERE
78 level < 10
79 )
80 SELECT
81 1,
82 CURDATE()+ 0,
83 REPEAT(
84 rand(),
85 3
86 )
87 FROM
88 sequence
89 ) AS F;
90 include/sync_slave_sql_with_master.inc
91 include/assert.inc [Stored columns not NULL count is 10]
92 include/assert.inc [Stored columns with series equal to 4 count is 10]
93 include/assert.inc [Stored columns with series equal to 0 count is 10]
94 [connection master]
95 UPDATE `log_data` SET `tm_ts` = "20210101", `series` = 2;
96 include/sync_slave_sql_with_master.inc
97 include/assert.inc [Stored columns with s_date equal to 2021-01-01 count is 10]
98 include/assert.inc [Stored columns with series equal to 5 count is 10]
99 include/assert.inc [Stored columns with series equal to 1 count is 10]
100 [connection master]
101 DELETE FROM `log_data` WHERE `id` < 11;
102 include/sync_slave_sql_with_master.inc
103 include/assert.inc [Count is 0]
104 [connection master]
105 DROP TABLE `log_data`;
106 include/rpl_end.inc