Monday, June 14, 2021

It Is Time To Double Check Your Backups

    It is time to double check your backups!   Every so often you should randomly check that data you have been backing up.  Set up some fresh bare metal or cloud based fresh metal and get your MySQL instance running.  Simple, easy, and no problem right?

    Well, I hope that is true. But please give it a try, just to put your mind at ease. 


    I had a call this morning from an old friend who received an automated message overnight that a software build failed. After some digging, the cause of the failure was tracked to an on premises system with hardware issues.  Luckily spare parts were on hand and the server was soon back on line and the build completed.

    Then the friend doubled checked their cron based backup scripts and the logs looked good. A spare machine was fired up and the friend decided to show some new hires how to restore a backup. The new machine was built with a later version of the Linux distribution the company favored. By default that distro enables a stricter version of selinux than used in their shop. Some coffee and RTFM time later, they were able to install their version of MySQL (8.0.24) and have it run.  They added their configuration settings to the configuration file and were ready to reinstall. Right?


    Do to some network configuration issues they could not reach their backup machine and they highly restricted access to this machine.  That is a great practice to secure your data. But in this case they had to wait for their security team to end their Monday morning standup to add the new machine to the allowed server list.  

    Then they only had ten gig of data to restore from a mysqldump.  Just a simple mysql -u root -p < 10gigbackup.sql and we are ready to roll, right? Alas, nope.  The accounts used for this server had not been setup on the server (Please see for how to do this easily with the MySQL Shell), were not part of the dump, and that took a little more time to get them setup.

  By now the new hires were referring to the internal documentation on database restorations and making revisions.  

  The restoration with the dump file finished.  Done? Nope.  The old friend randomly checked a few dozen records and was happy for about five minutes.  Then one of the new hires asked about Instance Dump Utility ( The new hire started asking some good questions.

  • Do we need to update our backup methods?
  • Should we be saving the account records with the data?
  • Is the new utility feature rich enough to switch to that as the backup methodology? 
  • Can any of this be automated?
  • How does this fit with our corporate methodology, including security practices? 

    Hopefully you can do a quick check on your backups even if it is for your peace of mind.

Tuesday, June 8, 2021

MySQL Group on Linkedin.Com passes 20,000 Members

     A few weeks ago the MySQL Group on passed 20,000 members. For those of you who do not know about, it is a business and employment oriented online platform that is mainly used for professional networking, and allows job seekers to post their resumes and employers to post jobs.

    You can find job positing, announcements, relevant blog posts, and more on the MySQL page. Please join us if you already have an account.

Friday, June 4, 2021

Dutch PHP Conference -- Learn about using indexes versus histograms with MySQL

 The Dutch PHP Conference is June 17th and 18th and I am lucky enough to be presenting again this years.  This is an amazing event to attend but this year it is virtual. At 10:00 to 13:00 CEST on the 17th I will be presenting on MySQL 8.0 Indexes, Histograms, and Other Ways to Speed Up Your Queries.  This talk starts with assuming you are new to adding indexes or histograms, do not know how to find your query plan, and have not been introduced to ways of speeding up your database queries.  Now if you have some knowledge in this area, this session will help you build on what you know already. 

Now if you want to learn more on indexes and histograms you can attend this conference too!  And this conference is FREE!  However for workshops, like my session, there are a limited number of virtual seats and you need to preregister

And like all my other session, I love to get your questions as they arise rather than have you wait, just looking for the perfect moment to quiz me, and then losing focus on the talk.  So if you have a question please provide it when you think of it!  

Dutch PHP Conference

Need a quick row number on your query output?

    Recently on Reddit someone asked how to automatically get a row number generated in front of the output from a query.  There is a ROW_NUMBER() function and it very simple to use with Windowing Functions.

    The sample data for this example is very simple.

> SELECT name, qty FROM sample;
| name | qty |
| a    |   5 |
| b    |   2 |
| c    |   4 |
3 rows in set (0.0019 sec)

    There are two way to write Windowing Functions.  The simplest is to add the ROW_NUMBER() function and  OVER() keyword to the query in the middle of the query. 

> select row_number() over () as 'no'
  from sample;
| no | name | qty |
|  1 | a    |   5 |
|  2 | b    |   2 |
|  3 | c    |   4 |
3 rows in set (0.0011 sec)

    Or define the window at the end of the query. I prefer this version for readability reasons.

> select row_number() over w as 'no'
from sample 
window w as();
| no | name | qty |
|  1 | a    |   5 |
|  2 | b    |   2 |
|  3 | c    |   4 |
3 rows in set (0.0009 sec)

    So now you know how to add a simple row number to your query output and two ways to format that request.

Tuesday, May 25, 2021

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.', 
    ' like sakila.', 
    TABLE_NAME,  ';') 
FROM information_schema.`TABLES` 

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.

Thursday, May 20, 2021

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 and the 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.

The world plugin

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

The available functions

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

Ready to run a function

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 table
   Simple function to query records in table
      session (object): Option session object or use current mysqlsh session
   if session is None:
      session = shell.get_session()
   if session is None:
      print("No session specified - pass a session or connect shell to database")
   if session is not None:
      r = session.run_sql("SELECT * FROM")

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")
   if session is not None:
      r = session.run_sql("SELECT * FROM")

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"})

Thursday, May 13, 2021

JSON Validation and ALGORITHM = Instant

Today I was presenting on JSON Schema validation and covering how MySQL implemented the 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
            _utf8mb4'{ "type":"object",
                "latitude":{"type":"number", "minimum":-90, "maximum":90},
               "longitude":{"type":"number", "minimum":-180, "maximum":180} 
      "required": ["latitude", "longitude"]\n }',doc)

And the answer (drum roll here):

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


And yes, it does work with ALGORITHM=COPY.