Thursday, February 7, 2019

Miami and Santo Domingo with MySQL Document Store

The next two weeks are a big change for me compared to the last two weeks.  The last two weeks I was in Belgium and now I am in Miami before heading to the Dominican Republic.  The one thing in common is that I am speaking on the MySQL Document Store.

SunshinePHP has become a favorite conference for many PHP developers for many reasons and this morning I will be running a three hour tutorial on using the PECL MySQL X DevAPI and the MySQL Document Store with PHP and then have a one hour talk later in the show. Plus I am joined by the amazing Kathy Forte who will speak on Driving Innovation With MySQL 8.0 and Docker.

Pycaribbean is a new show hoping to foster a growing developer community into the area.  And i will be showing how to use the MySQL Document Store with Python.

Wednesday, January 30, 2019

What the Heck is a Lateral Derived Table?

MySQL 8.0.14 came with Lateral Derived Tables as a new feature.  But what is that and how do you use them?

Let's start what a derived table is.  According to the manual (link below) A derived table is an expression that generates a table within the scope of a query FROM clause.   You are probably used to using a subquery or JSON_TABLE where that query feeds data into another query.

Derived Tables

Derived tables can return a scalar, column, row, or table. But there are limits to their use.
A derived table cannot be a correlated subquery and a derived table cannot contain references to other tables of the same SELECT. And previous to MySQL 8.0.14, a derived table cannot contain outer references.

Okay, so what is an 'outer reference'? The SQL-92 standard states that derived tables cannot depend on other tables in the same FROM clause.and cannot contain references to columns of other FROM clause tables.  But SQL-99 changed that with the keyword LATERAL (think sudo) so you can reference previously mention table.

Overly Simplified Example


The following is a very contrived query to illustrate the use of the LATERAL keyword.  The  query behind the LATERAL is returning the country.Code column to the query in front of the LATERAL. I am using the good old World database from the MySQL example data sets.

SELECT Name, Population, District, x.cc 
FROM city,
 LATERAL (SELECT Code AS cc FROM country 
         WHERE city.CountryCode = Code) AS x 
 WHERE District = 'Texas' ORDER BY name;

Now if you remove that the LATERAL from the above query, you will see the following error message. (Please do try the above query with and without LATERAL as a learning exercise)

Error Code: 1054. Unknown column 'city.CountryCode' in 'where clause'

So with the LATERAL keyword in place, the left query (the one before the LATERAL) provides that city.CountryCode column to the right query (the one after the lateral). In other words the query behind the LATERAL keyword is depending on the previous query.  Since we named the derived table as x we need to remember to reference it in the other table with too.

Quick Conclusion


So with lateral derived tables and Common Table Expressions (CTEs), it has become much easier to write sunqueries with MySQL.

Tuesday, January 22, 2019

The MySQL Track SCaLE 17x

The MySQL Track for the Southern California Linux Expo 17x has been published! On Friday march 9th you can learn about the MySQL Document Store, How to Deploy MySQL in Kubernetes, lean how Facebook achieves consistency with MySQL, MySQL Query Optimization, Analytics with MySQL, and MySQL Security. 

There are actually four days of the expo but the MySQL Track is on Friday, but you should stay around for the full weekend.  And MySQL is a sponsor and we will have a big booth with lots of swag, Be sure to stop by to say hello!

Monday, January 21, 2019

MySQL Shell 8.0.14 now with BSON

I have been asked the MySQL Shell handles BSON and now with today's release of version 8.0.,14, it does.  Version 8.0.13 added a very hand and blazing fast JSON bulk loader you get the ability to map and how the data is converted. The details are at the Conversions for representations of BSON data types page

From the 8.0.14 release notes:

 * The MySQL Shell JSON import utility can now process BSON
       (binary JSON) data types that are represented in JSON
       documents. The data types used in BSON documents are not
       all natively supported by JSON, but can be represented
       using extensions to the JSON format. The import utility
       can process documents that use JSON extensions to
       represent BSON data types, convert them to an identical
       or compatible MySQL representation, and import the data
       value using that representation. The resulting converted
       data values can be used in expressions and indexes, and
       manipulated by SQL statements and X DevAPI functions.
       To convert JSON extensions for BSON types into MySQL
       types in this way, you must specify the convertBsonTypes
       option when you run the import utility. Additional
       options are available to control the mapping and
       conversion for specific BSON data types. If you import
       documents with JSON extensions for BSON types and do not
       use this option, the documents are imported in the same
       way as they are represented in the input file.

One of the handier items is  the --decimalAsDouble switch to
convert the value of the BSON decimal type to a MySQL DOUBLE type instead of  a string.

One of the things to note is that the default option is that the documents are imported in the same way they were in the input file.  There ware ways to convert, decimals, dates, time stamps in the like but for those of you needed a quick upload of data from a MongoDB, this will be very useful.

Wednesday, January 9, 2019

Belgium! PHP Benelux, Pre-FOSDEM MySQL Day, and FOSDEM

I am getting ready to hit the road for the first time in 2019.  And the first stop is Belgium.

PHP Benelux is the first stop January 25 & 26, 2019 in Antwerp.  This is my firs time to this show but I have heard others rave about it for years and I am looking for to this show.

Next is an event on February 1st that is quickly filling up. The Pre-FOSDEM MySQL Day   Come hear session from the MySQL Engineering Teams, some other great technical speakers, and talk to them!  Great event but please reserve your space ASAP as this is a very popular event!

◾10:00–10:30 MySQL Shell – A devops-engineer day with MySQL’s development and administration tool by Miguel Araújo
◾10:35–11:05 MySQL Shell : The best DBA tool? by Frédéric Descamps
◾11:25–11:55 Tuning MySQL 8.0 InnoDB for high load by Dimitri Kravtchuk
◾13:30–14:00 New index features in MySQL 8.0 by Erik Frøseth
◾14:40–15:10 Regular expressions with full Unicode support by Martin Hansson
◾16:50–17:20 8 Group replication features that will make you smile by Tiago Vale
◾17:25–17:55 Document Store & PHP by David Stokes

And finally on Feb 2nd - 3rd is the chaotic FOSDEM.,  No pre-registration but we have a small room that gets packed quickly. And drop by the MySQL booth to talk MySQL and get some SWAG.

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.  


Preliminaries


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#'; 

and  

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 http://127.0.0.1:8000. 


Symfony Demo started
Running the Symfony Demo Application. Note that the 
application is listening on http:127.0.0.1:8000
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.

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:
DATABASE_URL=mysql://account:password@127.0.0.1:3306/databasename

What does work:
DATABASE_URL=mysql://account:password@localhost:3306/databasename

So a simple s/127.0.0.1/hostname/ 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.