All Things Open to Zendcon next week

October 19, 2017

The week of the 23rd of October has two of my favorite shows and sadly, for me, they are at the same time on opposite sides of the country.

All Things Open has grown from a small show in Raleigh to a mega show with thousands of attendees.  It has an exciting and vibrant audience like the old OSCON shows had (but lost) and several tracks crammed with interesting sessions. This is a multi discipline show and there are always talks on new subjects that worth attending plus the latest on long time open source projects.  MySQL has sponsored this event since the beginning and continues this year.  Look for Andrew Rist in the MySQL booth.

I will be in Las Vegas for Zendcon and MySQL is also returning as a sponsor. This is a PHP centric show and has many sessions on the Zend Framework, other Rogue Wave products, and many PHP subjects. But is is just another PHP conference? No, it has a lot of heavy duty, in depth deep dives into what I would like to call industrial strength PHP development. This show has a lot of solid session for professional software developers. This is not he usual Vegas show with smoky passages and jarring slot machine noise and well worth the trip even if you are not a Viva Las Vegas type.

Advertisements

SwanseaCon

September 25, 2017

MySQL is proud to again sponsor SwanseaCon and I will be speaking on making MySQL Agile-ish. Recent advancements in MySQL such as the JSON data type and the document store help developers who need schema less data. With last week’s announcement of the MySQL 8 release candidate, atomic Data Description Language (DDL) changes will help bring more agility to projects using MySQL.

If you are in Swansea, I hope to see you at Liberty Stadium and please visit the MySQL booth!


NYC MySQL Innovation Day October 10th

September 15, 2017

You’re Invited to Oracle MySQL Innovation Day!

Join us to hear directly from MySQL’s lead engineers about MySQL’s latest advances and road map, which were developed with two goals in mind: to help you solve real-world challenges and innovate to get ahead.

You’ll get a view of our strategic direction,into specific capabilities, their use cases, and benefits in our interactive sessions.

Register for this no-cost event to:
Learn about MySQL’s advances and what’s ahead from Oracle MySQL’s leading engineers.
Discover new solutions and ways to sharpen your competitive edge.
Ask questions and engage in discussions with MySQL experts.
We look forward to seeing you there! Register Here!

Oracle MySQL Innovation Day
New York, NY
October, 10, 2017
8:00am – 4:00pm EST
Location:
Convene Conference Center
32 Old Slip
New York, NY10005

Agenda
8:00am – 9:00am

Registration & Breakfast
9:00am – 9:15am

Keynote: MySQL | State of the Dolphin
Tomas Ulin, Vice President MySQL Engineering
9:15am – 10:00am

MySQL 8.0: What’s New
Morgan Tocker, MySQL Senior Product Manager
10:00am – 10:45am

MySQL 8.0: CTEs & Window Functions
Manyi Lu, Director Software Development MySQL Optimizer Team

10:45am – 11:00am
Morning Break
11:00am – 11:45am

Developing Next Generation Applications for Web & Mobile
Morgan Tocker, MySQL Senior Product Manager

11:45am – 12:30pm
MySQL Enterprise Security
Mike Frank, MySQL Product Management Director

12:30pm – 1:30pm
Lunch

1:30pm – 2:15pm
MySQL Replication
Luis Soares, Principal Software Engineer

2:15pm – 3:00pm
MySQL InnoDB Cluster
Matt Lord, MySQL Senior Product Manager

3:00pm – 3:45pm
MySQL Performance Tuning
Manyi Lu, Director Software Development MySQL Optimizer Team


MySQL at Oracle Open World

September 7, 2017

Oracle Open World’s session catalog is online. Here are some of the MySQL centeric highlights!

MySQL Shell: The DevOps Tool for MySQL
Monday, Oct 02, 3:15 p.m. – 4:00 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 15

MySQL Document Store
Monday, Oct 02, 11:00 a.m. – 1:00 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

MySQL Performance Tuning 101
Tuesday, Oct 03, 11:30 a.m. – 12:30 p.m. | Hilton San Francisco Union Square (Ballroom Level) – Continental Ballroom 7

Using MySQL Containers
Monday, Oct 02, 2:15 p.m. – 3:00 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 15

MySQL Enterprise Edition: Complete Guide
Monday, Oct 02, 1:15 p.m. – 2:00 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 15

MySQL for Analytics
Monday, Oct 02, 2:15 p.m. – 3:00 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

Why We’re Excited About MySQL 8
Monday, Oct 02, 3:15 p.m. – 4:00 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

MySQL 8.0 Overview
Wednesday, Oct 04, 11:00 a.m. – 11:45 a.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

Running Workloads with Oracle MySQL Cloud Service
Wednesday, Oct 04, 3:30 p.m. – 4:15 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

MySQL DBA Primer
Monday, Oct 02, 11:00 a.m. – 12:00 p.m. | Hilton San Francisco Union Square (Ballroom Level) – Continental Ballroom 7

Oracle Enterprise Manager for MySQL
Monday, Oct 02, 11:00 a.m. – 1:00 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 15

MySQL 8.0: Performance, Scalability, and Benchmarks
Wednesday, Oct 04, 5:30 p.m. – 6:15 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

What’s New in MySQL NDB Cluster
Monday, Oct 02, 1:15 p.m. – 2:00 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

MySQL 8.0: What’s New in Security
Wednesday, Oct 04, 1:00 p.m. – 1:45 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

Solving Performance Problems with MySQL Enterprise Monitor
Wednesday, Oct 04, 4:45 p.m. – 5:45 p.m. | Hilton San Francisco Union Square (Ballroom Level) – Continental Ballroom 7

MySQL 8.0: What’s New in InnoDB
Wednesday, Oct 04, 4:30 p.m. – 5:15 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

MySQL 8.0: What’s New in the Optimizer
Wednesday, Oct 04, 12:00 p.m. – 12:45 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

Split Your Database: Seamless Database Growth with MySQL
Tuesday, Oct 03, 11:30 a.m. – 12:15 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 15

StorageTapper: Real-Time MySQL Change Data Streaming at Uber
Tuesday, Oct 03, 12:45 p.m. – 1:30 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

MySQL 8.0: What’s New in Replication
Wednesday, Oct 04, 2:00 p.m. – 2:45 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

MySQL InnoDB Cluster in a Nutshell: Hands-on Lab
Monday, Oct 02, 4:15 p.m. – 5:15 p.m. | Hilton San Francisco Union Square (Ballroom Level) – Continental Ballroom 7

How to Set Up Orchestrator to Manage Thousands of MySQL Servers
Tuesday, Oct 03, 12:45 p.m. – 1:30 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 15

General Session: MySQL – State of the Dolphin
Monday, Oct 02, 4:45 p.m. – 6:30 p.m. | Marriott Marquis (Golden Gate Level) – Golden Gate A

Core MySQL Kernel Development and Architecture in Tencent Games
Wednesday, Oct 04, 1:00 p.m. – 1:45 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 15

Triggerless, Painless, Trusted MySQL Online Schema Migrations at GitHub
Wednesday, Oct 04, 12:00 p.m. – 12:45 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 15

Using MySQL Flexible Schema (Document Store/JSON) for IoT
Tuesday, Oct 03, 11:30 a.m. – 12:15 p.m. | Marriott Marquis (Yerba Buena Level) – Salon 14

MySQL Automatic Diagnostics: System, Mechanism, and Usage
Sunday, Oct 01, 3:45 p.m. – 4:30 p.m. | Moscone South – Room 160

Self-Service Database Monitoring for Gaming Needs
Wednesday, Oct 04, 11:00 a.m. – 11:45 a.m. | Marriott Marquis (Yerba Buena Level) – Salon 15

DevOps and Cloud-Native Microservices Workshop with MySQL Database
Monday, Oct 02, 4:30 p.m. – 6:30 p.m. | Hilton San Francisco Union Square (Lobby Level) – Golden Gate 2/3

Rest Services with the Play Framework, MySQL, and a Security Level with JWT
Sunday, Oct 01, 5:00 p.m. – 5:45 p.m. | Moscone West – Room 2002: Java User Group Track

Cloud Foundry Service Broker: Bridging Your Cloud-Native Apps
Wednesday, Oct 04, 1:45 p.m. – 2:30 p.m. | Moscone West – Room 2005

DBA Types
Sunday, Oct 01, 1:45 p.m. – 2:30 p.m. | Marriott Marquis (Yerba Buena Level) – Nob Hill A/B

Oracle Cloud Performance Best Practices: Tuning, Diagnostics, Issue Resolution
Tuesday, Oct 03, 5:45 p.m. – 6:30 p.m. | Marriott Marquis (Golden Gate Level) – Golden Gate A

Innovation, Oracle Cloud, Big Data, and the Internet of Things
Monday, Oct 02, 11:00 a.m. – 11:45 a.m. | Moscone West – Room 2001


PNWPHP and Swanseacon

September 5, 2017

Busy month with PNWPHP at Washington University’s Kane Hall September 7th to the 9th. MySQL is sponsoring and I will be manning the booth. This is an exciting event on the PHP calendar and they have a great speaker lineup. Ticket are still available if you are in the area.

Swanseacon returns for a second year and I will be speaking on making MySQL Agile-ish. MySQL is proud to be sponsoring for a second year and I am looking forward to the wonderful Welsh audience again. Tickets are going quickly.

And do not forget that Oracle Open World is the first five days in October. MySQL will also have some post show events and I will provide more details when I get them.


Northeast PHP

August 4, 2017

Next week Northeast PHP returns to Price Edward Island and Charltottetown. Last year this event moved from Cambridge to Canada and found a wonderful audience. This year the conference is back and bigger than ever. And the local government is extremely supportive and had provided the organizers with a grant to help this year.

I will be presenting on Basic PHP Database Programming and Getting JSON Data in and Out of MySQL. Tickets are still available and there is a student discount.


MySQL 8 and Common Table Expressions – An Introduction

July 31, 2017

MySQL 8 will have Common Table Expressions also known as CTEs.  I have had people asking me when  MySQL would have CTEs for years and I am glad to report they are ready for testing.

What Does a CTE look like?

The keyword WITH is the big hint that the statement is a CTE.  For now think of a CTE as a collections of views or subqueries that you combine together for the desired results. After the WITH clause, one or more comma separated sub-clauses that are subqueries. And CTEs can reference other CTEs.  For a first example, let us combine the world_x example database’s city and country table to produce a list of all cities and their corresponding countries.

WITH
  cte1 as (select Name as CName, CountryCode as CCode from city),

  cte2 as (select Name as Name, Code from country)
SELECT cte1.CName, cte2.Name from cte1
  join cte2 on (cte1.CCode = cte2.Code)
  order by cte1.CName;

So cte1 gets all the city tables Name  and CountryCode data and cte2 grabs the country  Name and Code columns.  The CTE part of this query is in blue. The city CountryCode is a corresponding key to the country Code column.  The part of the query in green is how the CTE is queried. You could consider it a short lived temp table. And here is the abbreviated output.

+————————+———————-+
| CName               | Name              |
+————————+———————-+
| A Coruña (La Coruña) | Spain |
| Aachen              | Germany        |
| Aalborg             | Denmark        |
| Aba                    | Nigeria            |
……

Got a Simpler Example?

mysql>WITH cte as (SELECT ID, Name, District FROM city)
select Name, ID from cte limit 5;

+—————-+—-+
| Name      | ID |
+—————-+—-+
| Kabul      | 1 |
| Qandahar | 2 |
| Herat       | 3 |
| Mazar-e-Sharif | 4 |
| Amsterdam | 5 |
+—————-+—-+
5 rows in set (0.00 sec)

This second example has a CTE named cte (and the Nobel Prize for hyper creating naming goes to …). From this CTE we can then select what information we desire from it. Sp in this way it is kinda of like a View. In the above example, the CTE grabs three columns of data but from it we only take two columns. And you can name you CTE what every you want within the normal constraints.

Plus Recursive!

And CTEs can also be recursive. A recursive common table expression is a CTE with a subquery that refers to its own name. At the beginning it will proclaim WITH RECURSIVE. You will often find them used with hierarchical data such as organization charts and assembly build sheets. The following example counts down from 10

mysql> WITH RECURSIVE rcte (x) AS
(SELECT 10
UNION ALL
SELECT x – 1 FROM rcte WHERE x > 1)
SELECT * FROM rcte;
+——+
| x |
+——+
| 10 |
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+——+
10 rows in set (0.00 sec)

Where to use CTEs

CTEs are often easier to read than embedded subqueries. They disappear after the query is executed which makes them easier to manage than using temp tables.  And many think they are easier to understand than subqueries which hopefully means easier maintenance.

Performance

So lets run EXPLAIN on the first example, minus the ORDER BY:

mysql> EXPLAIN with cte1 as (select Name as CName, CountryCode as CCode from city), cte2 as (select Name as Name, Code from country) select cte1.CName, cte2.Name from cte1 join cte2 on (cte1.CCode = cte2.Code);
+—-+————-+———+————+——–+—————+———+———+————————–+——+———-+——-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———+————+——–+—————+———+———+————————–+——+———-+——-+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4079 | 100.00 | NULL |
| 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 9 | world_x.city.CountryCode | 1 | 100.00 | NULL |
+—-+————-+———+————+——–+—————+———+———+————————–+——+———-+——-+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\g
+——-+——+——————————————————————————————————————————————————————————————————————+
| Level | Code | Message |
+——-+——+——————————————————————————————————————————————————————————————————————+
| Note | 1003 | /* select#1 */ select `world_x`.`city`.`Name` AS `CName`,`world_x`.`country`.`Name` AS `Name` from `world_x`.`city` join `world_x`.`country` where (`world_x`.`country`.`Code` = `world_x`.`city`.`CountryCode`) |
+——-+——+——————————————————————————————————————————————————————————————————————+
1 row in set (0.00 sec)

The show warnings lets us peek at the actual query the MySQL optimizer will run. The optimizer was able to rewrite the query as a fairly simple join. So in this case at least, the performance will be fine.