Using MySQL Router on Windows

Last time we used the New MySQL Shell to set up a sandbox instance of InnoDB Cluster in Six Steps. Now to make that cluster function we need to start up MySQL Router to proxy connections. And since we do not show how to do things with Windows, we will configure Router on that Operating System.

Cleaning Up From Last Time

If you followed the instructions from last time you may find you InnoDB Cluster non functional.  If you have rebooted then you will find that the component pieces of the cluster have not been started.  To clean things up you will have to do the following:

• dba.killSandboxInstance(3310)
• dba.deleteSandboxInstance(3310)
• dba.deleteSandboxInstance(3320)
• dba.deleteSandboxInstance(3310)

Then recreate the sandbox instance using the instructions from the last post.

Lets Get Ready To Route

With Windows, we will  use the MySQL Installer to configure Router.  If Router is not installed you can also use the MySQL Installer to add the program. Click on 'Reconfigure' to set up MySQL Router.

MySQL Installer - Note that Router is installed on this system. Click on 'Reconfigure' to set up MySQL Router

We are now ready to explore the configuration options for MySQL Router.

We need to specify the host which, in the case of our sandbox, is 'localhost', and provide the account and password to test the connection.

The host needs to be specified and in our case it is the localhost. You will want to test the connection after supplying the account with password and port before selecting the 'Test' button. And remember we set the port when we established the sandbox instance with the
dba.deploySandboxInstance(3310) command.

We need to supply the host, port, account & password before testing the connection

If you want to change the proxy ports at this time you certainly can.

We can also change the ports for the proxy at this time too!

 Born To Run

So we have MySQL Router configured and it is time to get it running.  Time to select the 'Execute' button in the lower right corner.

When you are ready to start MySQL Router select 'Execute'

The MySQL Router program will start.

MySQL Router starts up and since we are on Windows, it registers as a Windows Service,

Log filed are available for those who want the details.

The first part of the log details the settings of MySQL Router

And the second half of the log shows us the proxy ports and that MySQL Router is a Windows Service

Special Account

Did you see it? The special account that appears like a hidden Easter Egg?  It was in the first part of the log. Router set up an account  mysql_router1_5vbo3umtvi6y@'%' for cluster management. It also created a configuration file for later user.

More on this in a future post.

But Is It Working?!?

Start another MySQL shell and connect to the read only proxy port.

 \c root@localhost:6447
Creating a session to 'root@localhost:6447'
Please provide the password for 'root@localhost:6447': ******
Save password for 'root@localhost:6447'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 24
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
JS > \sql
Switching to SQL mode... Commands end with ;
SQL > select @@port;
+--------+
| @@port |
+--------+
|   3330 |
+--------+
1 row in set (0.0002 sec)

So we are talking to the instance at port 3330!  Woo-hoo.  Without a load connecting again will probably not switch us to the other server at 3320 but we will try.  And so we start another shell.  i will confess that I kept seeing good ol' 3330 after several attempts and got luck when I tried the read only port for the X Protocol.

SQL > select @@port;
+--------+
| @@port |
+--------+
|   3330 |
+--------+
1 row in set (0.0003 sec)
SQL > \c root@localhost:6449
Creating a session to 'root@localhost:6449'
Please provide the password for 'root@localhost:6449': ******
Save password for 'root@localhost:6449'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 39 (X protocol)
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 SQL > select @@port;
+--------+
| @@port |
+--------+
|   3320 |
+--------+
1 row in set (0.0004 sec)

Conclusion

From the last blog we know we can set up an sandbox InnoDB cluster with six commands. And this time we were able to set up MySQL Router in about three minutes.   So ten minutes to a highly available MySQL InnoDB Cluster.  This many not impress your but for someone like me who started with the basic asynchronous replication with early version of MySQL this is amazing. 

Setting up a InnoDB Sandbox Cluster in SIX steps

I have not used the new InnoDB clone feature that is now part of MySQL 8.0.17 but wanted to see how it worked.  Setting a sandbox cluster with the new shell is easy and quick.  How easy and quick?
 well, it takes six commands and just a few minutes.

The Steps

1. mysqlsh> dba.deploySandboxInstance(3310)
A new MySQL sandbox instance will be created on this host in
C:\Users\dstokes\MySQL\mysql-sandboxes\3310

Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.

Please enter a MySQL root password for the new instance: ******
Deploying new MySQL instance...

Instance localhost:3310 successfully deployed and started.
Use shell.connect('root@localhost:3310'); to connect to the instance.

 MySQL  localhost:33060+ ssl  so  JS > dba.deploySandboxInstance(3320)
A new MySQL sandbox instance will be created on this host in
C:\Users\dstokes\MySQL\mysql-sandboxes\3320

Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.

Please enter a MySQL root password for the new instance: ******
Deploying new MySQL instance...

Instance localhost:3320 successfully deployed and started.
Use shell.connect('root@localhost:3320'); to connect to the instance.

 2.  mysqlsh> dba.deploySandboxInstance(3330)
A new MySQL sandbox instance will be created on this host in
C:\Users\dstokes\MySQL\mysql-sandboxes\3330

Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.

Please enter a MySQL root password for the new instance: ******
Deploying new MySQL instance...

Instance localhost:3330 successfully deployed and started.
Use shell.connect('root@localhost:3330'); to connect to the instance.


3. mysqlsh> \c root@localhost:3310
Creating a session to 'root@localhost:3310'
Please provide the password for 'root@localhost:3310': ******
Save password for 'root@localhost:3310'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 12
Server version: 8.0.17 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

4. mysqlsh > var cluster = dba.createCluster('DaveTest')
A new InnoDB cluster will be created on instance 'localhost:3310'.

Validating instance at localhost:3310...
NOTE: Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as 127.0.0.1:3310

Instance configuration is suitable.
Creating InnoDB cluster 'DaveTest' on 'localhost:3310'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

 5. mysqlsh > cluster.addInstance('root@localhost:3320')
Please provide the password for 'root@localhost:3320': ******
Save password for 'root@localhost:3320'? [Y]es/[N]o/Ne[v]er (default No): y

NOTE: The target instance 'localhost:3320' has not been pre-provisioned (GTID set is
empty). The Shell is unable to decide whether incremental distributed state
recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through
automatic clone provisioning, which will completely overwrite the state of
'localhost:3320' with a physical snapshot from an existing cluster member. To
use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental distributed state recovery may be safely used if you are sure
all updates ever executed in the cluster were done with GTIDs enabled, there
are no purged transactions and the new instance contains the same GTID set as
the cluster or a subset of it. To use this method by default, set the
'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): c
Validating instance at localhost:3320...
NOTE: Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as 127.0.0.1:3320

Instance configuration is suitable.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.

NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.

* Waiting for clone to finish...
NOTE: localhost:3320 is being cloned from 127.0.0.1:3310
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ============================================================    0%  In Progress
    REDO COPY  ============================================================    0%  Not Started

NOTE: localhost:3320 is shutting down...

* Waiting for server restart... ready
* 127.0.0.1:3320 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 58.51 MB transferred in about 1 second (~inf TB/s)

State recovery already finished for 'localhost:3320'

The instance 'localhost:3320' was successfully added to the cluster.

 6. mysqlsh>cluster.addInstance('root@localhost:3320')
<output omitted but much like the above in step 5>

And the Sandbox Cluster is Ready!

With those five steps we have a sandbox InnoDB Cluster we can use for testing.

mysqlsh > cluster.status()
{
    "clusterName": "DaveTest",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "127.0.0.1:3310",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "127.0.0.1:3310": {
                "address": "127.0.0.1:3310",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "127.0.0.1:3320": {
                "address": "127.0.0.1:3320",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            },
            "127.0.0.1:3330": {
                "address": "127.0.0.1:3330",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.17"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "127.0.0.1:3310"
}


Now we can fire up MySQL Router and start using our sandbox cluster.

Some 'Small' Changes in MySQL 8.0.17

I would like to point out some 'small' things you may not have noticed in the MySQL 8.0.17 Release Notes.  They are small changes compared to things like MVIs, InnoDB cloning, and the like but these are the types of changes that are subtle that may catch you unaware.

1. Host names have grown from 60 to 255 characters.  However your SSL/TLS package may not be able to handle the longer names.

2. If you are an old C/C++ programmer, 'C-style &&, ||, and ! operators that are synonyms for the standard SQL AND, OR, and NOT operators, respectively, are deprecated and support for them will be removed in a future MySQL version'.

3. The ZERO fill attribute is being deprecated as is unsigned FLOAT, DOUBLE, and DECIMAL. You can no longer AUTO_INCREMENT FLOAT and DOUBLE columns.

4. The optimizer will rewrite your WHERE foo to WHERE foo != 0 to ensure complete predicates are being used to plan your query.

JSON Schema Validation with MySQL 8.0.17

JSON has become the standard document interchange format over the last several years.  MySQL 5.7 added a native JSON data type and it has been  greatly enhanced with version 8.0.  But many in the relational world have complained the the NoSQL approach does not allow you to have rigor on your data. That is to make sure an integer value is really an integer and within specified ranges or string of the proper length. And there was no way to make sure that email addresses are not listed under a combination of E-mail, e-mail, eMail, and eMAIL.  JSON is great for many things but traditional, normalized data was better for making certain that your data matched what was specified.

If only there was a way to enforce come rigor on JSON data! Or a way to annotate (pronounced 'document') your JSON data. Well there is. MySQL 8.0.17 has added the ability to validate JSON documents against a schema following the guidelines of the JSON-Schema.org's fourth draft standard. You can find both the manual page 12.17.7 JSON Schema Validation Functions and the JSON Schema information online.

Valid JSON and Really Valid JSON

As you are probably already aware, MySQL will reject an invalid JSON document when using the JSON data type.  But there is a difference between syntactically valid and validation against a schema. With schema validation you can define how the data should be formatted. This will help with automated testing and help ensure the quality of your data.

Overly Simple Example

Lets create a simple document schema that looks at a key named 'myage' and set up rules that the minimum value is 28 and the maximum value is 99.

set @s='{"type": "object",
     "properties": {
       "myage": {
       "type" : "number",
       "minimum": 28,
       "maximum": 99
   }
}
}';

And here is our test document where we use a value for 'myage' what is between the minimum and the maximum.

set @d='{  "myage": 33}';

Now we use JSON_SCHEMA_VALID() to test if the test document passes the validation test, with 1 or true as a pass and 0 or false as a fail.

select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)


Now try with a non-numeric value.

set @d='{  "myage": "foo"}';
Query OK, 0 rows affected (0.00 sec)

mysql> select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
|                        0 |
+--------------------------+

And a value below the minimum.

mysql> set @d='{  "myage": 16}';
Query OK, 0 rows affected (0.00 sec)

mysql> select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

Validity Report

We can use JSON_SCHEMA_VALIDATION_REPORT() to get more information on why a document is failing with JSON_SCHEMA_VALID().

mysql> select JSON_SCHEMA_VALIDATION_REPORT(@s,@d)\G
*************************** 1. row ***************************
JSON_SCHEMA_VALIDATION_REPORT(@s,@d): {"valid": false, "reason": "The JSON document location '#/myage' failed requirement 'minimum' at JSON Schema location '#/properties/myage'", "schema-location": "#/properties/myage", "document-location": "#/myage", "schema-failed-keyword": "minimum"}
1 row in set (0.00 sec)

And, you should note, the response is in JSON format. And you can neaten the output up with JSON_PRETTY() wrapped around the above query.

select JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@s,@d))\G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@s,@d)): {
  "valid": false,
  "reason": "The JSON document location '#/myage' failed requirement 'minimum' at JSON Schema location '#/properties/myage'",
  "schema-location": "#/properties/myage",
  "document-location": "#/myage",
  "schema-failed-keyword": "minimum"
}

Required Keys

If you want to make sure certain keys are included in a document, you can use a the required option in your schema definition.  So if you are working with GIS information, you can specify requiring longitude and latitude.

""required": ["latitude", "longitude"]


So we can no have required fields and specify their value ranges. And we can verify BEFORE committing the JSON document to the MySQL server that the data conforms to our schema.

Using JSON SCHEMA Validation with Check Constraint

SO the next logical step is to use the CONSTRAINT CHECK option on table creation to assure that we are not only getting a valid JSON document but a verified JSON document.

 
CREATE TABLE `testx` (
`col` JSON,
CONSTRAINT `myage_inRange`
CHECK (JSON_SCHEMA_VALID('{"type": "object",
"properties": {
"myage": {
"type" : "number",
"minimum": 28,
"maximum": 99
}
},"required": ["myage"]
}', `col`) = 1)
);

And the proof that it works.

mysql> insert into testx values('{"myage":27}');
ERROR 3819 (HY000): Check constraint 'myage_inRange' is violated.
mysql> insert into testx values('{"myage":97}');
Query OK, 1 row affected (0.02 sec)
 

So two of the big criticisms on using JSON in a relational database are now gone. We can add rigor and value checks.  While not as easy to do as with normalized  relational data, this is a huge win for those using JSON.
 

More on JSON Schema

I highly recommend going through the basics of JSON Schema as they is a lot of material that can not be covered in a simple blog.

Three New JSON Functions in MySQL 8.0.17

MySQL 8.0.17 adds three new functions to the JSON repertoire.  All three can take advantage of the new Multi-Value Index feature or can be used on JSON arrays.

JSON_CONTAINS(target, candiate[, path])

This function indicates with a 1 or 0 if a  candidate document is contained in the target document. The optional path argument lets you seek information in embedded documents.  And please note the 'haystack' is before the 'needle' for this function.

mysql> SELECT JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Moe": 1}');
+------------------------------------------------------+
| JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Moe": 1}') |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Shemp": 1}');
+--------------------------------------------------------+
| JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Shemp": 1}') |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Objects as must match both key and value. Be careful as an array is considered to be contained in a target array only if every element in the candidate is contained in some element of the target. So JSON_CONTAINS("[1,2,3]","[2,3]") will return a '1' while JSON_CONTAINS("[1,2,3]","[3,4]") will return a '0'.

You can always use JSON_CONTAINS_PATH() to test if any matches exist on the entire path and JSON_CONTAINS() for a simple match.

JSON_OVERLAPS(document1, document2)

 This functions compares two JSON documents and returns 1 if it has any key/value pairs or array elements in common.

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[2,3,4,5]");
+----------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[2,3,4,5]") |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[2,4,6]");
+--------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[2,4,6]") |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

So what is the difference between these two new functions? JSON_CONTAINS() requires ALL elements of the array searched for to be present while JSON_OVERLAPS() looks for any matches. So think JSON_CONTAINS() as the AND operation on KEYS while JSON_OVERLAP is the OR operator.

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[1,3,5,9]");
+----------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[1,3,5,9]") |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT JSON_CONTAINS("[1,3,5,7]","[1,3,5,9]");
+----------------------------------------+
| JSON_CONTAINS("[1,3,5,7]","[1,3,5,9]") |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set (0.00 sec)

value MEMBER OF(json_array)

This function returns a 1 if the value is an element of the json_array.


mysql> SELECT 3 MEMBER OF('[1, 3, 5, 7, "Moe"]');
+------------------------------------+
| 3 MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 2 MEMBER OF('[1, 3, 5, 7, "Moe"]');
+------------------------------------+
| 2 MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.00 sec)

This function does not convert to and from strings for you so do not try something like this.

mysql> SELECT "3" MEMBER OF('[1, 3, 5, 7, "Moe"]');
+--------------------------------------+
| "3" MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+--------------------------------------+
|                                    0 |
+--------------------------------------+

So "3" is not equal to 3.  And you may have to explicitly cast the value as an array or use JSON_ARRAY().

mysql> SELECT CAST('[3,4]' AS JSON) MEMBER OF ('[[1,2],[3,4]]');
+---------------------------------------------------+
| CAST('[3,4]' AS JSON) MEMBER OF ('[[1,2],[3,4]]') |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY(3,4) MEMBER OF ('[[1,2],[3,4]]');
+---------------------------------------------+
| JSON_ARRAY(3,4) MEMBER OF ('[[1,2],[3,4]]') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)

Use with Multi-Value Indexes

Queries using JSON_CONTAINS(), JSON_OVERLAPS(), or MEMBER OF() on JSON columns of an InnoDB table can be optimized to use Multi-Valued Indexes.  More on MVIs in another blog post!

MySQL Workbench and OpenStreetMap Data Visualization

I have a presentation next month on MySQL and GIS.  MySQL 8.0 has benefited greatly from the three dimensional libraries from Boost.Geometry. There are many facets to the Geographic Data world that it is damn near impossible not to lurch down one rabbit hole into another in an unending spiral of acronyms, standards, projections, and functions.  But thankfully I have MySQL Workbench to aid me.

Texas

I wanted some test data to use for some GIS exercises and was very happy to find many useful sets curated by the OpenStreetMaps folks. Shapefiles are used to hold the various data points of an item of interest.  I had assumed that the data would have some sort of longitude/latitude pairs but was wondering what I would need to do to work with that data and what ever came bundled with it.  I download the Texas data and then loaded it into the database.

You will need a copy of the ogr2org utility This wonderful program reads the raw shapefile and converts it into SQL. You may want to get the source but hopefully a version may be available for you Linux distribution.  There is a copy of the ogr2ogr program that comes with MySQL Workbench but I have not been able to get it to convert the data and load that data into a MySQL instance, with either Windows, Fedora, or Ubuntu.

$ogr2ogr -overwrite -progress -f "MySQL" mysql:texas,user=dave,password=****** gis_osm_natural_a_free_1.shp
0...10...20...30...40...50...60...70...80...90...100 - done.
$ ogr2ogr -overwrite -progress -f "MySQL" mysql:texas,user=dave,password=***** gis_osm_natural_free_1.shp
0...10...20...30...40...50...60...70...80...90...100 - done.

The Data

The ogr2org utility reads the shape files and puts all the attributes into a a table. 

mysql> DESC gis_osm_natural_free_1;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| OGR_FID | int(11)      | NO   | PRI | NULL    | auto_increment |
| SHAPE   | geometry     | NO   | MUL | NULL    |                |
| osm_id  | varchar(10)  | YES  |     | NULL    |                |
| code    | decimal(4,0) | YES  |     | NULL    |                |
| fclass  | varchar(28)  | YES  |     | NULL    |                |
| name    | varchar(100) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

And a sample line from that table.

mysql> select OGR_FID,ST_ASTEXT(SHAPE) as Shape, osm_id, code, fclass, name FROM gis_osm_natural_free_1 limit 1;

+---------+-------------------------------+----------+------+--------+--------------------+

| OGR_FID | Shape                         | osm_id   | code | fclass | name               |

+---------+-------------------------------+----------+------+--------+--------------------+

|       1 | POINT(29.3060929 -94.7679897) | 80029566 | 4141 | beach  | Stewart Beach Park |

+---------+-------------------------------+----------+------+--------+--------------------+

1 row in set (0.00 sec)

The SHAPE column has the desired longitude and latitude. So now we have this data but I had zero clue to where Stewart Beach Park was located.

MySQL Workbench to the Rescue

Workbench makes it easy to 'see' where the data was.  You can see in the below example that Stewart Beach Park is the first row returned.

MySQL Workbench has an amazing number of features including the ability to help display GIS data.

If you right click on the 'BLOB' graphic under the shape column you will see an option to Show Point In Browser.  

The location of Stewart Beach Park on the Galveston Sea Wall

So Stewart Beach Park is right on the Galveston Sea Wall and around the corner from the cruise ship terminals. 

Mid-Atlantic Developer's Conference

I will be speaking on MySQL & GIS at the Mid-Atlantic Developer's Conference and you can still attend but tickets are going quickly. 

Plot your Location MySQL Workbench and OpenStreetMap

MySQL has added a lot of functionality for Geographical Information System (GIS) data in the last two releases. This has given us better ways to save and explore GIS data. MySQL Workbench is an amazing tool that many do not realize has vastly upped its game for supporting GIS information.  But did you know you can use MySQL Workbench with OpenStreetMap to plot locations?

1. Get your Longitude and Latitude

These, believe it or not, is the hard part.  Go to  https://www.latlong.net/ and type in the name of your location.  It will report back your longitude and your latitude.  For the example I will be using the information for the city of Justin, Texas.

Using www.longlat.net to find the location of Justin, Texas

2. Create Table and Add Data 

Now create a table using MySQL Workbench in your favorite test schema to store your data.

CREATE TABLE city (id int unsigned auto_increment primary key, 
             name char(25), 
  loc point);

And then add the information and as before the example is for my home town.

INSERT INTO city (name,loc) VALUES 
('Justin', ST_GeomFromText('point(33.084843 -97.296127)',4326));

3. Query With Workbench

Now query your data from workbench.  If you only have the one record SELECT * FROM city; will suffice or use SELECT loc FROM city WHERE name='<yourcitynamehere>';

Running the query to get the longitude and latitude of Jsutin, Texas using MySQL Workbench. Note the 'BLOB' under the loc column

4. Open In a Browser

If you right click on the 'BLOB' icon under the 'loc' column, one of the choice is show point in browser. Pick that choice and you will get redirected to OpenStreetMap.org

The OpenStreetMap.org map of Justin, Texas