What Does This Query Really Do?

Computers are dumb. And they will do exactly what you ask them to do.  The trick often is to think as dumb as the computer. Sadly it is all to easy to assume that the computer is 'thinking' like you are and blunder into a head scratching puzzle.  Recently there was a post on MySQL Community Space Groundbreakers Developer Community site that shows that sometimes what is intended is not what you want but you are getting exactly what you asked. 

Quiz -- What happens if you run the following query?

SELECT concat('CREATE TABLE if does not exists sakila1.', 
    TABLE_NAME, 
    ' like sakila.', 
    TABLE_NAME,  ';') 
FROM information_schema.`TABLES` 
WHERE TABLE_SCHEMA = 'sakila'

A) You will create copies of the tables in the sakila schema in sakila1 schema.

B) You will create the SQL queries to create copies of the tables in the sakila schema in the sakila1 schema.

C) This will fail as you have to create sakila1 before you can run this query.

D) This query has a syntax error

Your answer??

To help you take this quiz, here is the output of the query.  And yes, I have sakila schema but not a sakila1 schema. 

The author of this query was expecting the query to create copies of all the tables in the sakila schema in the sakila1 schema. 

Since the query ran, we can eliminate D as an answer.  

And since it ran without the sakila1 schema already created, we can eliminate C.

A simple SHOW SCHEMAS will show that A is not the answer.  Yes, even if you create the new schema before running the query.

The answer is that this query creates the query to do the work but does not perform the actual work.  So the query does exactly as asked but not as intended.

So what if you want this new schema?

Well, you could save the output from the query in a file and then execute that file.

Or put the query above in a CTE or subquery and use the output to do the intended work.

Extending the MySQL Shell with Python

One of the problems that comes with age is that there is so much 'baggage' filling valuable brain area that it sometimes requires a mental sweeping before trying to learn a new skill. I am still getting rid of FORTRAN and COBOL factoids but need to come up to speed on extending MySQL for some talks I am giving.  So away with the PROCEDURE DIVISION stuff... 

The MySQL Shell or Mysqlsh is very extensible and it is easy to create a plugin for some handy routines.  Your routines can go in functions and there are examples below.  The tricky part is that you have to tell Mysqlsh that you are creating an extension and then hook up your function(s) to that extension object. Finally that extension object has to be registered with the shell.

So below are two functions which query the MySQL World Database for the contents of the world.city and the world.country tables.  The first thing for both of these functions is to make sure we are connected to the MySQL database instance or, in other words, that our shell has a session establish with the server. Then it is a quick query and dump the results.

Now the tricky stuff!

We need an object for our extension plugin_obj = shell.create_extension_object()
and then we provide the informationfor our functions with shell.add_extension_object_member(plugin_obj, "city", show_tables, {"brief" : "Dave's demo 2 - city"} ) and 
shell.add_extension_object_member(plugin_obj, "countryinfo", show_country, {"brief" : "Dave's demo 2 - country"} ) before we tell the shell to use our new plugin  with shell.register_global("world", plugin_obj, {"brief": "A 2nd demo for Dave"}) to tell the shell we want these functions available under the name world. 

When a new Mysqlsh is session is started, we can use \h to list the available plugins. And we look for our new plugin.

If you type 'world' and then a TAB key, you will be able to see that the two functions are ready!

The you can type the name of the function desired and then the query will run.

I have omitted the output from the query. Please note that you can use plugins written in either JavaScript or Python and that you do can run programs written in either language from the other language mode.

The Code

def show_tables(session=None):
   """ Lists all the records in the world.city table
   Simple function to query records in table
   Args:
      session (object): Option session object or use current mysqlsh session
   Returns:
Nothing
   """
   if session is None:
      session = shell.get_session()
   if session is None:
      print("No session specified - pass a session or connect shell to database")
      return
   if session is not None:
      r = session.run_sql("SELECT * FROM world.city")
      shell.dump_rows(r)

def show_country(session=None):

   """  Yada - yada -> see above function

   """
   if session is None:
      session = shell.get_session()
   if session is None:
      print("No session specified - pass a session or connect shell to database")
      return
   if session is not None:
      r = session.run_sql("SELECT * FROM world.country")
      shell.dump_rows(r)

plugin_obj = shell.create_extension_object()

shell.add_extension_object_member(plugin_obj, "city", show_tables, {"brief" : "Dave's demo 2 - city"} )
shell.add_extension_object_member(plugin_obj, "countryinfo", show_country, {"brief" : "Dave's demo 2 - country"} )

shell.register_global("world", plugin_obj, {"brief": "A 2nd demo for Dave"})

JSON Validation and ALGORITHM = Instant

Today I was presenting on JSON Schema validation and covering how MySQL implemented the JSON-Schema.org ideas on making sure JSON data meets criteria before being allowed into the database.  I was asked by Percona Live attendee Nickolay Ihalainin if the JSON_SCHEMA_VALID function worked with ALTER TABLE's ALGORITHM = INSTANT option.

I admitted that I did not know as a I had not tested. While I was answering questions, Nickolay did this test.

 alter table foo17  add CHECK
    (json_schema_valid(
            _utf8mb4'{ "type":"object",
            "properties":{
                "latitude":{"type":"number", "minimum":-90, "maximum":90},
               "longitude":{"type":"number", "minimum":-180, "maximum":180} 
             },
      "required": ["latitude", "longitude"]\n }',doc)
),
ALGORITHM=INSTANT; 

And the answer (drum roll here):

ERROR: 1845: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY.

Dang!

And yes, it does work with ALGORITHM=COPY.

MySQL Track

May 13th is the day for the MySQL Tracks at the Percona Live  conference. 

Besides an announcement for an exciting new product, you will find sessions on:

• MySQL Server Component Manifest Files
• Insights into the new Oracle MySQL Database Service
• Oracle MySQL Database Service with HeatWave for Real-Time Analytics
• MySQL Architectures in a Nutshell
• Successfully run your MySQL NDB Cluster in Kubernetes
• Validating JSON
• Migration from 5.6 to 8.xx.xx
• Dbdeployer in action - Optimised MySQL sandboxes
• MySQL Shell for DBAs (including Dump & Load)

May is Double Check Time

     Early in my career my boss would ask me to audit the equipment each May.  I had to double check the hardware and software versions to ensure they were up to date, or as close as we could get them.  I was working at a university and things quieted down after the students left in June which gave us a window for maintenance. And to this day the first Monday in May is when I double check the status of my equipment.

    MySQL 8.0.24 came out last month and I had not upgraded my various computers.  Fedora & Ubuntu have also recently sent out new versions and I am up to date on one and not the other.  My favorite email program had an update and the two browsers I use most also had updates.  And my corporate IT folks have some updates they wanted me to run.  

    Why do this effort?  First is that there are bug fixes, updates, and new features.  A lot of engineering talent's time is wasted by folks not performing updates. The following is a example of a conversation that I have regularly.  

"Hey Dave, when will MySQL have feature X? I really, really need it!" 

"We added it two years ago in version 8.0.14." 

    I understand the 'if it is not broke do not fix it mentality' on a lot of things but not software updates that can be vital to your organization.  There may be a security path you need, or a memory leak plugged, or a more efficient function in that update.  

   I can understand a reluctance to upGrade (not upDate) until a new release has proven itself. By the way for those of you wanting to wait for MySQL 8.0 to mature should note that it has been out for over three years! So those wanting histograms, window functions, and many new other features should be well on their upgrade path.

  So take some time week and double check to see what you are running.  This is an area where you do not want surprises.