Wednesday, November 28, 2018

The Symfony Demo Application and MySQL 8

The Symfony Frame work is very popular with PHP developers and it has a very nice Demo Application to help novices learn the intricacies involved. But the demo does not use MySQL. So this blog entry is about re configuring the demo so it works with MySQL 8. And I am using Ubuntu 18.04 LTS to you may have to adjust the following commands to work with your operating system.

This is not difficult but there are some steps that are not exactly self evident that this blog will show you how to get the demo working.  


The first thing to do is to make sure you have PHP 7.2 or better installed including the php7.2-intl (sudo apt-get install php7.2-intl) package as well as the PDO connector. I will admit I have been using PHP since it appeared and this is the first time I have had to install this package.

And you will want Composer to do the behind the scenes lifting for you and Doctrine to map the relations in the PHP code to the database.  Please see my previous blog on getting Doctrine to work with MySQL 8 (Big hint for the TL;DR crowd, set your .env to DATABASE_URL=mysql://account:password@localhost:3306/databasename ).

You will want to create an account on the MySQL server for use with this demo and then make sure it will have the proper rights to use the new schema.

CREATE USER 'demouser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'S3cr3t#'; 


GRANT ALL on 'databasename'.* to 'demouser'@'localhost'; 

The Demo

Now we can start work on the demo itself. 

The first thing to do in a terminal window is type composer create-project symfony/symfony-demo.  Composer will get the demo code ready for you.  Now cd cymfony-demo.   

Change the .env file (you may have to copy the .env-dist to .env and edit it) as noted above DATABASE_URL=mysql://demouser:S3c3t#@localhost:3306/databasename 

Now it is time to use Doctrine to build create the database, the schemas, and load the data.

$ php bin/console doctrine:database:create
$ php bin/console doctrine:schema:create
$ php bin/console doctrine:fixtures:load

Finally enter php bin/console server:run to start the demo. You will get a notice about the URL to use to get to the demo via a web browser. Use that URL in your web browser to get to the actual demo and below you can see that URL is 

Symfony Demo started
Running the Symfony Demo Application. Note that the 
application is listening on http:
Demo in Browser
The Symfony Demo to help you explore this popular PHP Freamwork

Monday, November 19, 2018

Updated: Doctrine and MySQL 8 - An Odd Connection Refused Error

I am currently working my way through the many PHP Frameworks to see how they get on with MySQL 8.  The Frameworks that can take advantage of the MySQL Improved Extension or mysqli can take advantage of the SHA256 Caching Authentication method. But those that are PDO based need to use the older MySQL Native Authentication method.

I wanted to check the PDO based frameworks and today I just happened to be wearing the very nice Symfony shirt I received as part of my presentation at Symfony USA.  So I started with a fresh install of Symfony.  All was going well until it came time to get it to work with MySQL 8 through Doctrine.


Symfony uses Doctrine as an ORM (Object Relational Mapper) and DBAL  (Database Abstraction Layer) as an intermediary to the database.  While I myself am not a big fan of ORMs Doctrine does manage version migration very nicely.  When I tried to tie the frame work and the database together I received a stern connection refused error.

So I double checked the database connection parameters, making sure that I could get to where I wanted using the old MySQL shell.  Yes, the account to be used is identified by the native passwords and I had spelled the account name correctly. Then I double checked for fat-fingering on my part on the .env file where the connection details are stored. Then I did some searching and found someone else had stumbled onto the answer.

What does not work:

What does work:

So a simple s/ got things going.  I double checked the /etc/hosts file to make sure that alias was there (it was).

From then on I was able to create a table with VARCHAR and JSON columns and go about my merry way.

Update: An Oracle MySQL Engineer who works with the PHP connectors informed me that libmysql and all derived clients interpret "localhost" to mean "don't use TCP/ip, but Unix domain socket". And there was a kind post on the Doctrine mailing list informing me that the problems was upstream from Doctrine. Thanks to all who responded to solve this mystery for me.

Tuesday, November 13, 2018

Pager for the New MySQL Shell

I love the new shell but the one thing I missed from the old shell was the ability to use a pager like more or less to throttle the output of the screen.  Low and behold the engineers have added paging in MySQL Shell 8.0.13!!

Turn Paging On

Things can scroll off the screen quickly and pagination programs keep the output to small chunks.  To pick the more program as you pagination program, simply enter \pager more or for less enter \pager less

See the first illustration

How to turn paging on and off with the MySQL Shell 8.0.13


A big thanks to the MySQL Engineers!!

Turn Paging Off

And sometimes you do not need pagination and simply typing \pager and return will clear the setting. See the first illustration.

And you can determine how many lines to be output before output stops. \pager more -6 stops every six lines of output.

Setting the output of more to six lines

Other Pagination Programs

You can declare your desire to use another program for pagination on the command line like so mysqlsh --pager=foo

Tip: if you set the pager to \pager less -S, it will page horizontally too, so you can view wide results even without \G

The Documentation

The manual page for the pager is ->Using a Pager

Friday, November 9, 2018

A Tale of Two JSON Implementations - MySQL and MariaDB

JSON has proven to be a very import data format with immense popularity. A good part of my time for the last two or so years has been dedicated to this area and I even wrote a book on the subject.  This is a comparison of the implementations of handling JSON data in MySQL and MariaDB. I had requests from the community and customers for this evaluation.

JSON Data Types Are Not All Equal

MySQL added a JSON data type in version 5.7 and it has proven to be very popular.  MariaDB has  JSON support  version 10.0.16 but is actually an alias to a longtext data type so that statement based replication from MySQL to MariaDB is possible.

MySQL stores  JSON documents are converted to an internal format that permits quick read access to document elements.  The binary format is structured to enable the server to look up sub-objects or nested values directly by key or array index without reading all values before or after them in the document.  From a practical standpoint the big thing most people notice is that the data is alphabetized.

MariaDB does no such optimization and their documentation states the claim that the performance is at least equivalent.

JSON Tables

The first comparison is 'how hard is it to put non-JSON data into a JSON column?'  The standard (see below under 'extra'), by the way, states that the data must be in the UTF8MB4 character set. And what is going on 'underneath the cover'?

The first step is to create tables to hold JSON data.


create table t1 (j json);
Query OK, 0 rows affected (0.1826 sec)

 MySQL  localhost:33060+ ssl  json  SQL > show create table t1;
| Table | Create Table                                                                                                   |
| t1    | CREATE TABLE `t1` (
  `j` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
1 row in set (0.0149 sec)

 MySQL  localhost:33060+ ssl  json  SQL > desc t1;
| Field | Type | Null | Key | Default | Extra |
| j     | json | YES  |     | NULL    |       |
1 row in set (0.0028 sec)


MariaDB [json]> create table t2 (j json);
Query OK, 0 rows affected (0.046 sec)

MariaDB [json]> show create table t2;
| Table | Create Table                                                                                                                     |
| t2    | CREATE TABLE `t2` (
 `j` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
1 row in set (0.000 sec)

MariaDB [json]> desc t2;
| Field | Type     | Null | Key | Default | Extra |
| j     | longtext | YES  | | NULL |      |
1 row in set (0.001 sec)

MariaDB [json]>

Conclusion: Both use UTF8MB4 and the underlying tables are roughly equivalent.

Checking Constraints

Ensuring that only JSON gets into the JSON columns is essential. MySQL does this by default for the JSON data type but MariaDB does not.


insert into t1 values('junk');

ERROR: 3140: Invalid JSON text: "Invalid value." at position 0 in value for column 't1.j'.


MariaDB [json]> insert into t2 values ('junk');

Opps! We now have a NON-JSON value in the table.

To be fair, MariaDB does let you establish a constraint check on the column to avoid this issue.

MariaDB [json]> drop table t2;
Query OK, 0 rows affected (0.046 sec)
MariaDB [json]> create table t2 (j json, check (json_valid(j)));
Query OK, 0 rows affected (0.049 sec)

MariaDB [json]> insert into t2 values ('junk');
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `json`.`t2`

Conclusion: MySQL does as advertised by rejecting non-JSON data by default while MariaDB can do so with a little extra work.

Loading Data

The only major complaint about JSON data is that there is just so much of it. So having a way to bulk load is important.


MySQL's new shell (mysqlsh) has Python, JavaScipt, and SQL modes.  It is very easy to use either the Python or JavaScript modes to write a quick script to read bulk data sets line by line.  Giuseppe 'Datacharmer' Maxia has a great example of converting data from MongoDB to MySQL using the shell and I have used that example code extensively in the past. But now 8.0.13 has a bulk loader built into the shell.
The New MySQL Shell's utility to bulk load JSON

This utility functions allows JSON data to be stores in a JSON Document Collection (MySQL Document Store) or in a JSON column of s structured table. Very handy.


Maria does not have an equivalent to the new MySQL Shell nor does it have a bulk loader utility for JSON data. 

I tired to use the Connect Storage Engine (not installed by default) and follow the examples on the CONNECT JSON Table Type page without luck. The Connect engine is supposed to have the capability to auto discover a table structure and define the table itself.  Mea Cupla for not getting this to work and I would encourage those who do use this feature to send me pointers PLEASE! But after several hours and tedious attempts to follow the examples exactly it was time to move on to something else.

Conclusion: MySQL does better loading data, especially in bulk.

JSON Functions

Both databases have functions and there are some differences.

  1. Functions That Create JSON Values
    1. JSON_ARRAY, JSON_OBJECT and JSON_QUOTE  are found in both and work the same.
  2. Functions That Search JSON Values
    1. JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_EXTRACT, JSON_KEYS, and JSON_SEARCH are found in both and work the same.  However only MySQL has the -> and ->>  shortcuts for JSON_EXTRACT and JSON_UNQUOTE(JSON_EXTRACT)).. 
  3. Functions That Modify JSON Values
    2. MariaDB has JSON_MERGE which has been deprecated in MYSQL 8.0.3 and replaced with JSON_MERGE_PRESERVE & JSON_MERGE_PATCH.   MySQL 8.0 supports the JSON Merge Patch format defined in RFC 7396 function.
  4. Functions That Return JSON Value Attributes
    1. JSON_DEPTH, JSON_LENGTH, JSON_TYPE, and JSON_VALID are found in both and work the same.
  5. Table Functions
    1. JSON_TABLE which allows you to use unstructured JSON data in a structured temporary table is in MySQL only.
  6. JSON Utility Functions
  7. Other Functions
    1. JSON_ARRAYAGG and JSON_OBJECTAGG are only in MySQL and are very handy for turning non JSON data into JSON.
    2. JSON_VALUE and JSON_QUERY are MariaDB specific and return an object/array or a scalar respectively.

Conclusion:  I tested both database's functions and found they worked as advertised.  JSON_PRETTY is much missed by my eyes when dealing with documents with several levels of embedding with MariaDB.  The merging functions are richer for MySQL especially for those who need to follow the merge patch standard. And JSON_TABLE is a valuable tool when trying to treat unstructured data as structured data temporarily especially combined with CTEs and Windowing Functions that were introduced in MySQL 8.   

Updating Data

Updating data can be expensive and JSON data can be expansive.


MySQL 5.7 required a complete re-write of the document.  If this is something you do a lot then you need to consider upgrading to MySQL 8.

MySQL 8.0's  the optimizer can perform a partial, in-place update of a JSON column instead of removing the old document and writing the new document in its entirety to the column. 
Replication. But there are conditions to this: 1) It has to be a JSON column, 2) The UPDATE statement uses any of the three functions JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() to update the column but a direct set like UPDATE mytable SET jcol = '{"a": 10, "b": 25'}) does not work, 3) The input column and the target column must be the same column, 4) All changes replace existing array or object values with new ones, and do not add any new elements to the parent object or array, and 5) The value being replaced must be at least as large as the replacement value. In other words, the new value cannot be any larger than the old one (An exception to this requirement occurs when a previous partial update has left sufficient space for the larger value. You can use the function JSON_STORAGE_FREE() see how much space has been freed by any partial update). If you can follow those rules the optimizer will do partial rewrites.


I searched the MariaDB docs and Jira but found no mention of partial JSON column updates.  If anyone has links, please send them to me. So it appears that MariaDB does a full rewrite.

Conclusion:  MySQL is more efficient here.


Efficient replication is a must and goes double for JSON with the potential of very large document payloads having to cross network connections.


In MySQL 5.7 an update to a JSON column was written to the binary log as the complete document. In MySQL 8.0, it is possible to log partial updates to JSON documents.   In statement based replication JSON partial updates are always logged as partial updates. 

But in row based replication they are logged as complete documents. To enable the logging of partial updates, set binlog_row_value_options=PARTIAL_JSON. Please note that the replication master has this variable set, partial updates received from that master are handled and applied by a replication slave regardless of the slave's own setting for the variable.


I searched the MariaDB docs and Jira but found no mention of partial JSON Replication updates.  If anyone has links, please send them to me. So expect the full document to be replicated.

Conclusion: MySQL can be more efficient.

X DevAPI / Document Store

Only MySQL has the Document Store and the underlying Document Store and it is proving to be very popular with customers.  The ability to use a database as a NoSQL Document Store and a relational database is popular.  Not having embedded strings in their code and having the IDEs help is driving developers to this approach. 

MariaDB's CTO said at Zendcon that they will support if customers demand.

Conclusion:  MySQL is the sole choice here.

Overall Conclusion

I have been using MySQL's JSON data type since the 5.7 DMRs and know them well.  MariaDB's implementation seems very familiar and works as expected.  MySQL is superior in the partial updates of data and replication, functions such as JSON_TABLE, the X DevAPI, and bulk loading of data. 


The IETF's standard is here and makes pretty quick reading.

Tuesday, November 6, 2018

Common Table Expressions: A Shocking Difference Between MySQL and MariaDB

Common Table Expressions (CTEs) are a very useful tool and frankly a big improvement on sub-queries.  But there are differences in how they are implemented in MySQL and MariaDB.  That  is not too surprising since the code fork many years ago. Different engineers implementing the same idea will have different approaches (and sometimes results). But differences in implementation are often important and, in this case, shockingly different.

Jesper Wisborg Krogh at Oracle OpenWorld and CodeOne gave a series of presentations and hands on labs that were excellent. He is an amazing Support Engineer and a great presenter of material at conferences.  In the lab for Common Table Expressions he did point out to me an interesting problem in MariaDB's implementation of CTEs. 

The Problem In a Nutshell

On the PostgreSQL Wiki, there is a
an SQL query (requires PostgreSQL 8.4 or MySQL 8.0) that produces an ASCII-art image of the Mandelbrot set written entirely in SQL 2008 conforming SQL.

-- Based on:

    SELECT i + 1
      FROM x
     WHERE i < 101
Z(Ix, Iy, Cx, Cy, X, Y, I) AS (
    SELECT Ix, Iy, X, Y, X, Y, 0
      FROM (SELECT CAST(-2.2 + 0.031 * i AS DECIMAL(13, 10)) AS X,
                  i AS Ix FROM x) AS xgen
           CROSS JOIN (
               SELECT CAST(-1.5 + 0.031 * i AS DECIMAL(13, 10)) AS Y,
                      i AS iY FROM x
           ) AS ygen
    SELECT Ix, Iy, Cx, Cy,
           CAST(X * X - Y * Y + Cx AS DECIMAL(13, 10)) AS X,
           CAST(Y * X * 2 + Cy AS DECIMAL(13, 10)), I + 1
      FROM Z
     WHERE X * X + Y * Y < 16.0
           AND I < 27
Zt (Ix, Iy, I) AS (
    SELECT Ix, Iy, MAX(I) AS I
      FROM Z
     GROUP BY Iy, Ix
     ORDER BY Iy, Ix
               ' .,,,-----++++%%%%@@@@#### ',
               GREATEST(I, 1),
           ) ORDER BY Ix SEPARATOR ''
       ) AS 'Mandelbrot Set'

The code is best run on the new MySQL Shell or MySQL Workbench but works well on the old MySQL shell but with desegregated output.

An abbreviated image of  the Mandelbot SQL output (See above for listing) , truncated for size. Produced with the new MySQL Shell (mysqlsh) on MySQL 8.0.13 

But then Jesper mention he had tested the SQL the night before the lab and it runs quickly on MySQL - 0.7445 seconds on my Windows laptop.
The Mandelbrot SQL code ran in 0.74445 seconds on MySQL 8.0.13

But not on MariaDB.  Jesper said he ran the same code on MariaDB 10.3 but killed it after fifteen minutes.  It was late and he had to get up early to get to San Francisco.

Double Check

With a fresh install of Fedora 29 and MariaDB 10.3.10, I ran the Mandelbrot SQL code.  And I waited for the result.  After an hour I went to lunch. But the query was still running when I returned.  I went on to other work an occasionally checking back and running SHOW PROCESSLIST  from time to time to make sure it had not died. 

But after two hours I hit control-C as I had other tasks for that system.  There are some interesting Recursive CTE problems listed on Jira, but nothing obviously relevant.

But I was able to confirm that MySQL's implementation of Recursive CTEs works well but I can not say that about MariaDB's implementation.