PHP With MySQL & PostgreSQL: April 2019

Monday, April 29, 2019

MySQL 8.0 GIS Units of Measure - Meter, foot, Clarke's yard, or Indian Foot

Friday, April 26, 2019

Bye Bye to mysql_upgrade, change to skip_grant_tables, and One Year of MySQL 8.0 GA

The MySQL 8.0.16 Release Notes are very interesting and sadly not read enough. One thing that may have escaped attention is that you no longer have to run mysql_upgrade after updating the binaries.

Let me repeat: you no longer have to run mysql_upgrade after updating the binaries.

From the release notes:

Previously, after installation of a new version of MySQL, the MySQL server automatically upgrades the data dictionary tables at the next startup, after which the DBA is expected to invoke mysql_upgrade manually to upgrade the system tables in the mysql schema, as well as objects in other schemas such as the sys schema and user schemas.

The server, starting 8.0.16, does the work previously done by mysql_upgrade for itself. And mysql_upgrade itself is deprecated.

I have had to help too many folks who either forgot to run mysql_upgrade after an upgrade or did not know they could not run it properly do to a lack of permissions from their unprivileged user account.

One Year of 8.0

And speaking of MySQL 8.0, it has been out for OVER one year now. Woot!

Skip_grant_tables

Another change to note concerns the much abused skip_grant_tables option.

Previously, if the grant tables were corrupted, the MySQL server wrote a message to the error log but continued as if the --skip-grant-tables option had been specified. This resulted in the server operating in an unexpected state unless --skip-grant-tables had in fact been specified. Now, the server stops after writing a message to the error log unless started with --skip-grant-tables.

Thursday, April 25, 2019

MySQL 8.0.16 Check Constraints

Before MySQL 8.0.16 you could put constraint checks into your Data Definition Language (DDL) when creating tables but the server ignored them. There was much gnashing of teeth as taunts of "It is not a real database" from other databases taunted the MySQL Community.

But with 8.0.16 this has all changed. You can now have your data constraints checked by the server. Below is an example table with two constraints.

mysql>CREATE TABLE parts
            (id int, cost decimal(5,2) not null check (cost > 0),
   price decimal(5,2) not null check (price > 1.0)
          );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into parts (id,cost,price) values (1,1.10,2.25);
Query OK, 1 row affected (0.03 sec)

mysql> insert into parts (id,cost,price) values (1,1.10,0.25);
ERROR 3819 (HY000): Check constraint 'parts_chk_2' is violated.

mysql> insert into parts (id,cost,price) values (2,-1.1,4.25);
ERROR 3819 (HY000): Check constraint 'parts_chk_1' is violated.

The first data input above does passes the constraints since both the cost and price columns are greater than the minimum. But not so with the next inserts, So the constraints work!

Finding Out What The Error Message Means

Now seeing Check constraint 'parts_chk_1' is violated. tells me the data is out of value. But how do I find out what parts_chk_1 is?

The fist thing to check is the table itself.

mysql> show create table parts;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| parts | CREATE TABLE `parts` (
`id` int(11) DEFAULT NULL,
`cost` decimal(5,2) NOT NULL,
`price` decimal(5,2) NOT NULL,
CONSTRAINT `parts_chk_1` CHECK ((`cost` > 0)),
CONSTRAINT `parts_chk_2` CHECK ((`price` > 1.0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

And you can also see the information in the IS.

mysql> select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE    |
+--------------------+-------------------+-----------------+-----------------+
| def                | davetest          | xxx_chk_1       | (`age` > 18)    |
| def                | davetest          | parts_chk_1     | (`cost` > 0)    |
| def                | davetest          | parts_chk_2     | (`price` > 1.0) |
| def                | davetest          | client_chk_1    | (`age` > 17)    |
+--------------------+-------------------+-----------------+-----------------+
4 rows in set (0.00 sec)

Saturday, April 13, 2019

Putting the 'Fun' In Functional Indexes

I gave a thirty minute talk at Drupalcon this week on the features in MySQL 8.0 that would be of interest to developers and for such a short talk (and I cut slides to allow for audience questions) I could only cover the highlights. One attendee gently chastised me over no including their favorite new MySQL 8.0 feature -- functional indexes.

What is a Functional Index?

The manual says MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. Use of functional key parts enables indexing of values not stored directly in the table.

There are some cool examples in the documentation on setting up some functional indexes, as can seen below.

CREATE TABLE t1 (
col1 INT, col2 INT,
INDEX func_index ((ABS(col1)))
);
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

But there are no example queries or examples with query plans provided. So let us add some data.

select * from t1 where (col1 + col2) ;
+------+------+
| col1 | col2 |
+------+------+
| 10 | 10 |
| 20 | 11 |
| 30 | 12 |
| 40 | 15 |
| 50 | 18 |
+------+------+
5 rows in set (0.0008 sec)

And then lets look at a query plan.

explain select * from t1 where (col1 + col2) > 40\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: idx1
key: idx1
key_len: 9
ref: NULL
rows: 3
filtered: 100
Extra: Using where
1 row in set, 1 warning (0.0006 sec)
Note (code 1003): /* select#1 */
select `so`.`t1`.`col1` AS `col1`,
`so`.`t1`.`col2` AS `col2`
from `so`.`t1`
where ((`so`.`t1`.`col1` + `so`.`t1`.`col2`) > 40)

explain select * from t1 where (col1 * 40) > 90\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: functional_index
key: functional_index
key_len: 9
ref: NULL
rows: 5
filtered: 100
Extra: Using where
1 row in set, 1 warning (0.0006 sec)
Note (code 1003): /* select#1 */
select `so`.`t1`.`col1` AS `col1`,`so`.`t1`.`col2` AS `col2`
from `so`.`t1` where ((`so`.`t1`.`col1` * 40) > 90)

It is interesting to note that in the above case we are told the key's name is 'functional_index' (this is the one created by the ALTER TABLE command and not explicitly given a name).

Implementation

Functional indexes are implemented as hidden virtual generated columns which means you will have to follow the rules for virtual generated columns And while this functional index takes up no space because virtual generated columns are only computed at select time the index itself does take up space.

Be sure to read the manual for all the restrictions and limitations.

Do They Work With JSON Data?

Well, yes, functional indexes do work with JSON data. Once again the manual provides an interesting example.

CREATE TABLE employees (
data JSON, INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin)) ); INSERT INTO employees VALUES ('{ "name": "james", "salary": 9000 }'), ('{ "name": "James", "salary": 10000 }'), ('{ "name": "Mary", "salary": 12000 }'), ('{ "name": "Peter", "salary": 8000 }');

EXPLAIN SELECT * FROM employees WHERE data->>'$.name' = 'James';

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees partitions: NULL type: ref possible_keys: idx key: idx key_len: 123 ref: const rows: 1 filtered: 100 Extra: NULL 1 row in set, 1 warning (0.0008 sec) Note (code 1003): /* select#1 */

select `so`.`employees`.`data` AS `data`

from `so`.`employees`

where ((cast(json_unquote(json_extract(`so`.`employees`.`data`,_utf8mb4'$.name'))

as char(30) charset utf8mb4) collate utf8mb4_bin) = 'James')

And we can see that it does use the index idx that was defined.

States

One of the examples you will see from other databases for functional indexes include

forcing lower case names. So I created a table with US state names.

create table states (id int, name char(30), primary key (id));

I did find that the syntax was a little fussy on creating the index as it needed an extra set of

parenthesis more than I originally thought it would

create index state_name_lower on states ((lower(name)));

explain select name from states where name = 'texas'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: states

partitions: NULL

type: ref

possible_keys: state_name

key: state_name

key_len: 121

ref: const

rows: 1

filtered: 100

Extra: Using where; Using index

1 row in set, 1 warning (0.0008 sec)

Note (code 1003): /* select#1 */

select `so`.`states`.`name` AS `name`

from `so`.`states` where (`so`.`states`.`name` = 'texas')

So please give functional indexes a try and please let me know if you find an interesting or unusual way to use them.

Wednesday, April 3, 2019

MySQL Workbench Spatial Viewer or How to See your GEOMETRY Data

The past couple of blog entries have been on Geographic Information Systems and Geometric Data. Visualizing that data with MySQL Workbench makes it easier for me to see what the results really mean.

Workbench 8.0.15 will draw the polygon with the Spatial View Option

So how do you get there?

Start Workbench, create a new SQL Tab in your favorite scratch schema, and crate the table below.

CREATE TABLE `test` (
`id` INT NOT NULL AUTO_INCREMENT,
`geom` GEOMETRY NULL,
PRIMARY KEY (`id`));

Next add some data.

INSERT INTO `test`
(`geom`)
VALUES
(st_geomfromtext
('polygon((0 0,0 3,3 0, 2 2,0 0),(1 1,1 2,2 1,2 2, 1 1))')
);

The run the query.

select geom from test;

However the result will default to the Result Grid. Look off to the right hand side of the results window to see a series of stacked icons and the default is the Result Grid. And that 'BLOB' is the result of the query. But that result is not exactly visually stunning.

Output from the query in the results grid view

The 'BLOB' is the result of the query.

Click on the Form Editor icon. It is directly below the Result Grid Icon

Select the Form Editor Icon

And you should see the image from the top of this blog entry.

Bonus!

Now scroll down below the Form Editor icon and select Spatial View.

Spatial View of the Query

Tuesday, April 2, 2019

MySQL 8.0 GIS -- Inserting Data & Fun Functions

The last blog entry was very popular and there were lots of requests for some introductory information on the spatial data types.

Well Known Text Or Binary

I am going to use the GEOMETRY data type over POINT, LINESTRING, or POLYGON as it can store any of those three while the other three can only contain data matching their name (so POINT can holds only point data, etc.). The values are stored in an internal geometry format but it takes wither WKT or WKB formatted data.

Those are Well-Known Text (WKT) or Well-Known Binary (WKB) formats repectively. I am hoping most of your are better with text than binary so the following examples demonstrate how to insert geometry values into a table by converting WKT values to internal geometry format.

So let us start with a simple table.

mysql> create table geom (type text, g geometry);
Query OK, 0 rows affected (0.04 sec)

We can use the ST_GeomFromText function to take some strings and convert into the internal format.

mysql> insert into geom values
('point', st_geomfromtext('point(1 1)'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into geom values
('linestring', st_geomfromtext('linestring(0 0,1 1, 2 2)'));
Query OK, 1 row affected (0.01 sec)

There are type specific functions for POINT, LINESTRING, and POLYGON that we can also take advantage of for this work.

mysql> SET @g =
'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO geom
VALUES ('polygon',ST_PolygonFromText(@g));
Query OK, 1 row affected (0.00 sec)

If you do a SELECT * FROM geom; you will get the g column output in binary. Thankfully we can use ST_AsText() to provide us with something more readable.

mysql> select type, st_astext(g) from geom;
+------------+----------------------------------------------------------+
| type | st_astext(g) |
+------------+----------------------------------------------------------+
| point | POINT(1 1) |
| linestring | LINESTRING(0 0,1 1,2 2) |
| polygon | POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5)) |
+------------+----------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Put the 'fun' in Function

There are functions that can be use with each of these type to provide information., For instance there are the x & Y coordinates for a point.

mysql> select type, st_x(g), st_y(g) from geom where type='point';
+-------+---------+---------+
| type | st_x(g) | st_y(g) |
+-------+---------+---------+
| point | 1 | 1 |
+-------+---------+---------+
1 row in set (0.00 sec)

We can even use our linestring data to get the minimal bounding rectangle for the given coordinates (basically if we had to put an envelope around the points the results are the coordinates points to enclose those x,y coordinates)

mysql> select st_astext(st_envelope(g))
from geom where type='linestring';
+--------------------------------+
| st_astext(st_envelope(g)) |
+--------------------------------+
| POLYGON((0 0,2 0,2 2,0 2,0 0)) |
+--------------------------------+
1 row in set (0.00 sec)

And we can get the area of a polygon too.

mysql> select type, st_area((g)) from geom where type='polygon';
+---------+--------------+
| type | st_area((g)) |
+---------+--------------+
| polygon | 96 |
+---------+--------------+
1 row in set (0.00 sec)

And find the mathematical center of that polygon.

mysql> select type,
st_astext(st_centroid(g))
from geom where type='polygon';
+---------+--------------------------------------------+
| type | st_astext(st_centroid(g)) |
+---------+--------------------------------------------+
| polygon | POINT(4.958333333333333 4.958333333333333) |
+---------+--------------------------------------------+
1 row in set (0.00 sec)

Plus we can get the linestring data if we wanted to draw out polygon.

mysql> select type,
st_astext(st_exteriorring(g))
from geom where type='polygon';
+---------+-------------------------------------+
| type | st_astext(st_exteriorring(g)) |
+---------+-------------------------------------+
| polygon | LINESTRING(0 0,10 0,10 10,0 10,0 0) |
+---------+-------------------------------------+
1 row in set (0.00 sec)