Debian Codegraph @

mysql-test/suite/rpl/r/rpl_gis_ddl.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 a table with three columns:
7 # 1) Normal POINT column without SRID specification
8 # 2) Normal POINT column with SRID 0
9 # 3) Normal POINT column with SRID 4326
10 CREATE TABLE t1 (no_srid POINT DEFAULT NULL,
11 srid_0 POINT SRID 0 DEFAULT NULL,
12 srid_4326 POINT SRID 4326 DEFAULT NULL);
13 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
14 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
15 def test t1 no_srid NULL NULL point
16 def test t1 srid_0 0 point
17 def test t1 srid_4326 WGS 84 4326 point
18 # Insert some data with correct SRID into these columns
19 INSERT INTO t1 (no_srid, srid_0, srid_4326)
20 VALUES (ST_GeomFromText('POINT(0 0)', 0),
21 ST_GeomFromText('POINT(0 0)', 0),
22 ST_GeomFromText('POINT(0 0)', 4326));
23 INSERT INTO t1 (no_srid, srid_0, srid_4326)
24 VALUES (ST_GeomFromText('POINT(1 1)', 4326),
25 ST_GeomFromText('POINT(1 1)', 0),
26 ST_GeomFromText('POINT(1 1)', 4326));
27 include/sync_slave_sql_with_master.inc
28 [Connection Slave]
29 include/diff_tables.inc [master:t1, slave:t1]
30 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
31 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
32 def test t1 no_srid NULL NULL point
33 def test t1 srid_0 0 point
34 def test t1 srid_4326 WGS 84 4326 point
35 SELECT ST_SRID(no_srid) AS no_srid,
36 ST_SRID(srid_0) AS srid_0,
37 ST_SRID(srid_4326) AS srid_4326 FROM t1;
38 no_srid srid_0 srid_4326
39 0 0 4326
40 4326 0 4326
41 [Connection Master]
42 # Removing the SRID specification from a column should work just fine.
43 ALTER TABLE t1 CHANGE COLUMN srid_4326 no_srid_2 POINT DEFAULT NULL;
44 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
45 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
46 def test t1 no_srid NULL NULL point
47 def test t1 no_srid_2 NULL NULL point
48 def test t1 srid_0 0 point
49 include/sync_slave_sql_with_master.inc
50 [Connection Slave]
51 include/diff_tables.inc [master:t1, slave:t1]
52 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
53 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
54 def test t1 no_srid NULL NULL point
55 def test t1 no_srid_2 NULL NULL point
56 def test t1 srid_0 0 point
57 SELECT ST_SRID(no_srid) AS no_srid,
58 ST_SRID(no_srid_2) AS no_srid_2,
59 ST_SRID(srid_0) AS srid_0 FROM t1;
60 no_srid no_srid_2 srid_0
61 0 4326 0
62 4326 4326 0
63 [Connection Master]
64 DELETE FROM t1;
65 include/sync_slave_sql_with_master.inc
66 [Connection Slave]
67 include/diff_tables.inc [master:t1, slave:t1]
68 [Connection Master]
69 ALTER TABLE t1 CHANGE COLUMN srid_0 srid_4326 POINT SRID 4326 DEFAULT NULL;
70 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
71 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
72 def test t1 no_srid NULL NULL point
73 def test t1 no_srid_2 NULL NULL point
74 def test t1 srid_4326 WGS 84 4326 point
75 INSERT INTO t1 (no_srid, no_srid_2, srid_4326)
76 VALUES (ST_GeomFromText('POINT(0 0)', 2000),
77 ST_GeomFromText('POINT(0 0)', 0),
78 ST_GeomFromText('POINT(0 0)', 4326));
79 INSERT INTO t1 (no_srid, no_srid_2, srid_4326)
80 VALUES (ST_GeomFromText('POINT(1 1)', 4326),
81 ST_GeomFromText('POINT(1 1)', 2000),
82 ST_GeomFromText('POINT(1 1)', 4326));
83 include/sync_slave_sql_with_master.inc
84 [Connection Slave]
85 include/diff_tables.inc [master:t1, slave:t1]
86 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
87 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
88 def test t1 no_srid NULL NULL point
89 def test t1 no_srid_2 NULL NULL point
90 def test t1 srid_4326 WGS 84 4326 point
91 SELECT ST_SRID(no_srid) AS no_srid,
92 ST_SRID(no_srid_2) AS no_srid_2,
93 ST_SRID(srid_4326) AS srid_4326 FROM t1;
94 no_srid no_srid_2 srid_4326
95 2000 0 4326
96 4326 2000 4326
97 [Connection Master]
98 # Cleanup
99 DROP TABLE t1;
100 include/sync_slave_sql_with_master.inc
101 # The SRID property on a generated column should work
102 [Connection Master]
103 CREATE TABLE t1 (col1 POINT SRID 4326,
104 col2 POINT AS (ST_SRID(col1, 0)) SRID 0);
105 INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(0 0)', 4326));
106 INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(1 1)', 4326));
107 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
108 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
109 def test t1 col1 WGS 84 4326 point
110 def test t1 col2 0 point
111 include/sync_slave_sql_with_master.inc
112 [Connection Slave]
113 include/diff_tables.inc [master:t1, slave:t1]
114 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
115 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
116 def test t1 col1 WGS 84 4326 point
117 def test t1 col2 0 point
118 SELECT ST_SRID(col1) AS srid_4326, ST_SRID(col2) AS srid_0 FROM t1;
119 srid_4326 srid_0
120 4326 0
121 4326 0
122 [Connection Master]
123 DELETE FROM t1;
124 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT SRID 2000;
125 ALTER TABLE t1 CHANGE COLUMN col2 col2 POINT AS (ST_SRID(col1, 4326)) SRID 4326;
126 INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(0 0)', 2000));
127 INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(1 1)', 2000));
128 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
129 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
130 def test t1 col1 Anguilla 1957 / British West Indies Grid 2000 point
131 def test t1 col2 WGS 84 4326 point
132 include/sync_slave_sql_with_master.inc
133 [Connection Slave]
134 include/diff_tables.inc [master:t1, slave:t1]
135 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
136 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
137 def test t1 col1 Anguilla 1957 / British West Indies Grid 2000 point
138 def test t1 col2 WGS 84 4326 point
139 SELECT ST_SRID(col1) AS srid_2000, ST_SRID(col2) AS srid_4326 FROM t1;
140 srid_2000 srid_4326
141 2000 4326
142 2000 4326
143 [Connection Master]
144 # Cleanup
145 DROP TABLE t1;
146 include/sync_slave_sql_with_master.inc
147 include/rpl_end.inc