200 lines
8.2 KiB
Plaintext
200 lines
8.2 KiB
Plaintext
include/master-slave.inc
|
|
Warnings:
|
|
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
|
|
Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
|
|
[connection master]
|
|
# Create a table with three columns:
|
|
# 1) Normal POINT column without SRID specification
|
|
# 2) Normal POINT column with SRID 0
|
|
# 3) Normal POINT column with SRID 4326
|
|
CREATE TABLE t1 (no_srid POINT DEFAULT NULL,
|
|
srid_0 POINT SRID 0 DEFAULT NULL,
|
|
srid_4326 POINT SRID 4326 DEFAULT NULL);
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 no_srid NULL NULL point
|
|
def test t1 srid_0 0 point
|
|
def test t1 srid_4326 WGS 84 4326 point
|
|
# Insert some data with correct SRID into these columns
|
|
INSERT INTO t1 (no_srid, srid_0, srid_4326)
|
|
VALUES (ST_GeomFromText('POINT(0 0)', 0),
|
|
ST_GeomFromText('POINT(0 0)', 0),
|
|
ST_GeomFromText('POINT(0 0)', 4326));
|
|
INSERT INTO t1 (no_srid, srid_0, srid_4326)
|
|
VALUES (ST_GeomFromText('POINT(1 1)', 4326),
|
|
ST_GeomFromText('POINT(1 1)', 0),
|
|
ST_GeomFromText('POINT(1 1)', 4326));
|
|
include/sync_slave_sql_with_master.inc
|
|
[Connection Slave]
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 no_srid NULL NULL point
|
|
def test t1 srid_0 0 point
|
|
def test t1 srid_4326 WGS 84 4326 point
|
|
SELECT ST_SRID(no_srid) AS no_srid,
|
|
ST_SRID(srid_0) AS srid_0,
|
|
ST_SRID(srid_4326) AS srid_4326 FROM t1;
|
|
no_srid srid_0 srid_4326
|
|
0 0 4326
|
|
4326 0 4326
|
|
[Connection Master]
|
|
# Removing the SRID specification from a column should work just fine.
|
|
ALTER TABLE t1 CHANGE COLUMN srid_4326 no_srid_2 POINT DEFAULT NULL;
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 no_srid NULL NULL point
|
|
def test t1 no_srid_2 NULL NULL point
|
|
def test t1 srid_0 0 point
|
|
include/sync_slave_sql_with_master.inc
|
|
[Connection Slave]
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 no_srid NULL NULL point
|
|
def test t1 no_srid_2 NULL NULL point
|
|
def test t1 srid_0 0 point
|
|
SELECT ST_SRID(no_srid) AS no_srid,
|
|
ST_SRID(no_srid_2) AS no_srid_2,
|
|
ST_SRID(srid_0) AS srid_0 FROM t1;
|
|
no_srid no_srid_2 srid_0
|
|
0 4326 0
|
|
4326 4326 0
|
|
[Connection Master]
|
|
DELETE FROM t1;
|
|
include/sync_slave_sql_with_master.inc
|
|
[Connection Slave]
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
[Connection Master]
|
|
ALTER TABLE t1 CHANGE COLUMN srid_0 srid_4326 POINT SRID 4326 DEFAULT NULL;
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 no_srid NULL NULL point
|
|
def test t1 no_srid_2 NULL NULL point
|
|
def test t1 srid_4326 WGS 84 4326 point
|
|
INSERT INTO t1 (no_srid, no_srid_2, srid_4326)
|
|
VALUES (ST_GeomFromText('POINT(0 0)', 2000),
|
|
ST_GeomFromText('POINT(0 0)', 0),
|
|
ST_GeomFromText('POINT(0 0)', 4326));
|
|
INSERT INTO t1 (no_srid, no_srid_2, srid_4326)
|
|
VALUES (ST_GeomFromText('POINT(1 1)', 4326),
|
|
ST_GeomFromText('POINT(1 1)', 2000),
|
|
ST_GeomFromText('POINT(1 1)', 4326));
|
|
include/sync_slave_sql_with_master.inc
|
|
[Connection Slave]
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 no_srid NULL NULL point
|
|
def test t1 no_srid_2 NULL NULL point
|
|
def test t1 srid_4326 WGS 84 4326 point
|
|
SELECT ST_SRID(no_srid) AS no_srid,
|
|
ST_SRID(no_srid_2) AS no_srid_2,
|
|
ST_SRID(srid_4326) AS srid_4326 FROM t1;
|
|
no_srid no_srid_2 srid_4326
|
|
2000 0 4326
|
|
4326 2000 4326
|
|
[Connection Master]
|
|
# Cleanup
|
|
DROP TABLE t1;
|
|
include/sync_slave_sql_with_master.inc
|
|
# The SRID property on a generated column should work
|
|
[Connection Master]
|
|
CREATE TABLE t1 (col1 POINT SRID 4326,
|
|
col2 POINT AS (ST_SRID(col1, 0)) SRID 0);
|
|
INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(0 0)', 4326));
|
|
INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(1 1)', 4326));
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 col1 WGS 84 4326 point
|
|
def test t1 col2 0 point
|
|
include/sync_slave_sql_with_master.inc
|
|
[Connection Slave]
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 col1 WGS 84 4326 point
|
|
def test t1 col2 0 point
|
|
SELECT ST_SRID(col1) AS srid_4326, ST_SRID(col2) AS srid_0 FROM t1;
|
|
srid_4326 srid_0
|
|
4326 0
|
|
4326 0
|
|
[Connection Master]
|
|
DELETE FROM t1;
|
|
ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT SRID 2000;
|
|
ALTER TABLE t1 CHANGE COLUMN col2 col2 POINT AS (ST_SRID(col1, 4326)) SRID 4326;
|
|
INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(0 0)', 2000));
|
|
INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(1 1)', 2000));
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 col1 Anguilla 1957 / British West Indies Grid 2000 point
|
|
def test t1 col2 WGS 84 4326 point
|
|
include/sync_slave_sql_with_master.inc
|
|
[Connection Slave]
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 col1 Anguilla 1957 / British West Indies Grid 2000 point
|
|
def test t1 col2 WGS 84 4326 point
|
|
SELECT ST_SRID(col1) AS srid_2000, ST_SRID(col2) AS srid_4326 FROM t1;
|
|
srid_2000 srid_4326
|
|
2000 4326
|
|
2000 4326
|
|
[Connection Master]
|
|
# Cleanup
|
|
DROP TABLE t1;
|
|
include/sync_slave_sql_with_master.inc
|
|
# Column with SRID property on a MyISAM table should only be
|
|
# supported if the SRID represents a cartesian coordinate system.
|
|
[Connection Master]
|
|
CREATE TABLE t1 (col1 POINT, col2 POINT SRID 4326) ENGINE = MyISAM;
|
|
ERROR 42000: The storage engine for the table doesn't support geographic spatial reference systems
|
|
CREATE TABLE t1 (col1 POINT, col2 POINT SRID 2000) ENGINE = MyISAM;
|
|
INSERT INTO t1(col1, col2)
|
|
VALUES (ST_GeomFromText('POINT(0 0)'),
|
|
ST_GeomFromText('POINT(0 0)', 2000));
|
|
INSERT INTO t1(col1, col2)
|
|
VALUES (ST_GeomFromText('POINT(1 1)', 2000),
|
|
ST_GeomFromText('POINT(1 1)', 2000));
|
|
include/sync_slave_sql_with_master.inc
|
|
[Connection Slave]
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 col1 NULL NULL point
|
|
def test t1 col2 Anguilla 1957 / British West Indies Grid 2000 point
|
|
SELECT ST_SRID(col1) AS no_srid, ST_SRID(col2) AS srid_2000 FROM t1;
|
|
no_srid srid_2000
|
|
0 2000
|
|
2000 2000
|
|
[Connection Master]
|
|
DELETE FROM t1;
|
|
ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT SRID 2000;
|
|
ALTER TABLE t1 CHANGE COLUMN col2 col2 POINT SRID 0;
|
|
INSERT INTO t1(col1, col2)
|
|
VALUES (ST_GeomFromText('POINT(0 0)', 2000),
|
|
ST_GeomFromText('POINT(0 0)', 0));
|
|
INSERT INTO t1(col1, col2)
|
|
VALUES (ST_GeomFromText('POINT(1 1)', 2000),
|
|
ST_GeomFromText('POINT(1 1)', 0));
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 col1 Anguilla 1957 / British West Indies Grid 2000 point
|
|
def test t1 col2 0 point
|
|
include/sync_slave_sql_with_master.inc
|
|
[Connection Slave]
|
|
include/diff_tables.inc [master:t1, slave:t1]
|
|
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME SRS_NAME SRS_ID GEOMETRY_TYPE_NAME
|
|
def test t1 col1 Anguilla 1957 / British West Indies Grid 2000 point
|
|
def test t1 col2 0 point
|
|
SELECT ST_SRID(col1) AS srid_2000, ST_SRID(col2) AS srid_0 FROM t1;
|
|
srid_2000 srid_0
|
|
2000 0
|
|
2000 0
|
|
[Connection Master]
|
|
# Cleanup
|
|
DROP TABLE t1;
|
|
include/sync_slave_sql_with_master.inc
|
|
include/rpl_end.inc
|