Sunday, March 10, 2019

MySQL and PHP Basics Part I

I have had some requests to write some blogs on the basics of using PHP and MySQL together.  This will not be a series for the experienced as it will start at a level where I will go into a lot of details but expect very few prerequisites from the reader.  If this is not you, please move on. If it is you and you read something you do not understand, please contact me to show me where I assumed too much.

PHP and MySQL are both in their mid twenties and both vital in the worlds of developers.  With the big improvements in PHP 7 and MySQL 8, I have found a lot of developers flocking to both but stymied by the examples they see as their are many details not explained. So let's get to the explaining!

1. Use the latest software

If you are not using PHP 7.2 or 7.3 (or maybe 7.1) then you are missing out in features and performance.  The PHP 5.x series is deprecated, no longer support, and is quickly disappearing.  

MySQL 8.0 is likewise a big advancement but many sites are using earlier versions.  If you are not on 5.6 with plans to upgrade to 5.7 then you are about to be left behind.  If you are running an earlier version then you are using antique code. Also get your MySQL from MySQL as your Linux distro may not be current, especially for the smaller ones.  The APT and DEB repos can be  found here and there are Docket containers available too. 

In many cases it is a fight to keep your software core tools up to date, or fairly up to to date.  The time and heartache in fighting problems resolved in a later version of software or creating a work around for a feature not in your older version will eventually bite you hard and should be viewed as a CRM (Career Limiting Move).  BTW hiring managers look for folks with current skills not skill for a decade old version of the skills.

2. Do not pick one connector over another (yet!)

PHP is a very rich environment for developers and it has three viable options for connecting to MySQL databases.  Please note that the older mysql connector is deprecated, no longer support, and is to be avoided.  It was replaced by the mysqli or mysqlnd (native driver) and is officially supported by Oracle's MySQL Engineers.  Next is the PDO (public data objects) connector that is designed to be database agnostic but there is no overall maintainer who watches out for the code but Oracle MySQL Engineers do try to fix MySQL related issues if they do not impinge on other PDO code. And the newest, using the new MySQL X DevAPI and X protocol is the X DevAPI connector which supports both SQL and NoSQL interfaces.

The good news for developers is that you can install all three, side by side, with no problem.  For those staring out the ability to transpose from connector can be a little confusing as they work just a bit differently from each other but the ability to use two or more is a good skill to have.  Such much like being able to drive a car with an automatic or manual transmission, it does give you more professional skills.

Next time we will install PHP, MySQL, the three connectors, and some other cool stuff so you can start using PHP to access your MySQL servers.

Friday, March 8, 2019

SCaLE 17x

The Southern California Linux Expo has a MySQL Track this year and if you are around Pasadena, you should be here.  Besides the amazing expo hall, this is the biggest open source show on the left coast.  The MySQL track features talks on the MySQL Document Store, Kuberneties, query optimization, analytics, consistency at Facebook, and MySQL Security. 

Thursday, February 28, 2019

Giving back to the Community -- ChickTech Austin

I am doing something this weekend that is way out of my comfort zone and I want to encourage you to do something similar.  On Saturday I will be teaching a pop up workshop for Chick Tech Austin titled An Introduction to Databases with MySQL.  

Why is this out of my comfort zone?  I regularly speak to computer professional on databases and programming and have done so for years.  But this Saturday the class is made up of young women between the ages of fourteen and eighteen.For us oldsters, the class is made up of people half the age of MySQL, Python, and Java.  Their parents are probably younger than Structured Query Language! But these young folks are going to be inheriting our code bases based on technologies used today and integrated with future innovations. 

Another point of reference is that the Hudson continuous integration tool came out fourteen years ago and it seems like the CI tools world has really blossomed since.  And that is time life span of some folks in the class.

Teaching up and coming developers how to use Structured Query Language and other long lived technologies is vital.  These cogs of our infrastructures have lasted as they provide utility and fulfill needed function.  SQL may have many oddities but it has lasted so very long because it is so very useful. But fewer developers each year seem to get any formal training in SQL, sets, relational calculus, symbolic logic, or the basics of relational databases.  Yet the cries of help on sites like Quora and Stackoverflow for relatively simple SQL and database questions seem to grow exponentially.

What I am asking (or pleading) you to do is to pass on your knowledge of basics to others.  You should be able to speak on something basic you do on a regular basis to a novice for at least five minutes.  Find a local user group, Meetup, or other organization and offer them a short presentation.  Can't find one, start one! Feynman said that teaching really sharpens your own skill sets. 

Real novices need very clear examples.  Saying, "Oh it is like a Generator in PHP" is not going to help them frame of reference wise.  Take something you do every day (use the vi editor, explain a query, use css) and write a very simple guide and/or cheat sheet.  Thee is an audience for you information.

Or contact local groups and ask what subjects they would want as a presentation.  A simple 'How I set up PHP with Apache' covering the steps you took and what you discovered on the way may be old hat to you but there are folks stumbling around who would welcome your guidance.  Organizers of meetups and user groups are dying for presentations and even a five to ten minutes talk is welcome.  Ask your local groups what they would like to see and if you do not know the subject, plunge in and build your own skills.  Nobody is expecting perfection and often talking about how to dig yourself out of the potholes you smacked into is the real value of your presentation.

I wish there was a formalized way so that anyone with X years of experience could share their knowledge.  But there is not.  So I am asking you to get up off your backside, find a group near you, and offer your expertise.  Think of this as meta-documentation for the future generations who will have to suffer with your code and systems long after you have logged off for the last time.

And if you want to talk about an introduction to databases, I will send you my materials and help you prep for the presentation.

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.