Wednesday, June 24, 2020

Writing mysqlsh Scripts

    The new MySQL Shell or mysqlsh has provisions for loading user plugins in Python or JavaScript that are loaded when the shell starts up. I am just taking my initial steps into this area and wanted to share with you how easy it is to create a plug-in to produce a report.

    The ability to write and run your own scripts for information that you want on a regular basis is very useful. The idea behind this was a discussion with a community member who had issues with a locked account that unknowingly locked and knowing when passwords where changed.  This is a typical bit of work for a DBA that would be better automated and saved as a script for future uses.

The Query

    The query collects several vital facets of information for dealing with passwords and accounts.

SELECT concat(User, '@',Host) as User, 
       password_expired as Expired, 
       password_last_changed as Changed, 
       password_lifetime as Lifetime, 
       account_locked as Locked, 
       User_attributes FROM mysql.user

    Nothing too exotic as far as a query goes.

The Code

    Most of the Python code deals with registering the registering the passwordData function that houses the query as report named userList. The query itself is in purple below and the registration 'boilerplate is in green.  The shell.register_report is what ties the new function to the extension called userList.

# Define a passwordData function that generates a MySQL Shell report

def passwordData(session):
  query = "SELECT concat(User, '@',Host) as User, password_expired as Expired, password_last_changed as Changed, password_lifetime as Lifetime, account_locked as Locked, User_attributes FROM mysql.user"

  result = session.sql(query).execute();
  report = []
  if (result.has_data()):
    report = [result.get_column_names()]
    for row in result.fetch_all():

  return {"report": report}

# Register the userList function as a MySQL Shell report

shell.register_report("userList", "list", passwordData, {"brief":"Lists the user password status on the target server."})

# Check if global object 'ext' has already been registered
if 'ext' in globals():
    global_obj = ext
    # Otherwise register new global object named 'ext'
    global_obj = shell.create_extension_object()
    shell.register_global("ext", global_obj,
        {"brief":"MySQL Shell extension plugins."})

# Add the 'userList' extension object as a member of the 'ext' global object
    plugin_obj = global_obj.process
except IndexError:

The Report

    The output is pretty much as expected and could use some careful tailoring.  It would be nice to add information on how many days until a password expires and parsing the User_attributes field for key/values in a pretty fashion.  But in a few minutes work I had a quick little utility that will save me time in the future.

 JS > \show userList
| User                       | Expired | Changed             | Lifetime | Locked | User_attributes                                                                  |
| demo@localhost             | N       | 2020-04-09 10:42:06 | NULL     | N      | NULL                                                                             |
| foobar@localhost           | N       | 2020-04-06 09:38:50 | NULL     | N      | {"Password_locking": {"failed_login_attempts": 3, "password_lock_time_days": 2}} |
| mysql.infoschema@localhost | N       | 2020-01-13 11:57:33 | NULL     | Y      | NULL                                                                             |
| mysql.session@localhost    | N       | 2020-01-13 11:57:33 | NULL     | Y      | NULL                                                                             |
| mysql.sys@localhost        | N       | 2020-06-12 10:20:33 | NULL     | Y      | NULL                                                                             |
| root@localhost             | N       | 2020-01-13 11:57:37 | NULL     | N      | NULL                                                                             |

You Try

    I recommended grabbing LeFred's Github repo and reading through his code.  You can create your own directory under your ~/.mysqlsh/plugins/ext/ directory and using the above code saved as as an exercise.  

    I would love to see what similar scripts or reports others are creating so please share!

Wednesday, June 10, 2020

Virtual Southeast Linuxfest sessions on JSON Document Validation & MySQL 8.0 New Features

The Southeast Linuxfest will be virtual this year and I am happy to announce that I have two sessions.

Friday, June 12th 

(All times US EDT, GMT-4.  Time between talks is used for Q&A, discussion, wanton displays of old hardware and boxed Linux software hoarding, and breaks.)

6:00 - 7:00 PM:   Dave Stokes - MySQL JSON Document Validation

JSON is a popular data interchange format and MySQL has had a native JSON Data Type for a few years.  But until recently there was no way to make sure JSON data included required fields, data type checking, or range checking.  But thanks to the's designs, there is now a way to ensure that the JSON data going into your MySQL Database is correct.  You will learn how to use constraint checks with the new JSON validation functions to catch bad data before it gets into your server.  

 Saturday, June 13th

3:30 - 4:30 PM:  David Stokes - What’s New With MySQL
MySQL has been releasing new features each quarter for the past few years but do you know what is included in those releases?  Well, this session will cover in reverse order the new features such as hash joins, dual password, multi-valued indexes, derived tables, and more.  There is some really cool stuff that can save you a lot of grief if you know about them.

Wednesday, June 3, 2020

Removing Constraint Checks

Constraint Checks that actually checked the constraints were introduced last year with MySQL 8.0.16 and they really do help you keep bad data out of your database.  I have found them very handy in the past year but today I spotted a question on a website about how to remove a Constraint Check.

What is a Constraint Check?

It is an integrity check.  In the following example a constraint is set up to make sure the calues of the column 'is' are greater than one hundred.

   CONSTRAINT id_gt_100 CHECK (('id' > 100))

A simple test with a value in range runs perfectly fine.

INSERT INTO c1 (id) VALUES (10000);
Query OK, 1 row affected (0.0037 sec)

But you will receive an error if the value of 'is' is less than  100.

INSERT INTO c1 (id) VALUES (10);
ERROR: 3819: Check constraint 'id_gt_100' is violated.

Remembers it is cheaper and easier to keep bad data out of your databases than it is to correct it later.

Stopping the Check

If you like the check but have some reason to turn it off, say to bulk load some data in a situation where you can clean up the upload later, then use ALTER TABLE to turn off enforcement.

Query OK, 0 rows affected (0.0150 sec)

Records: 0  Duplicates: 0  Warnings: 0
INSERT INTO c1 (id) VALUES (10);
Query OK, 1 row affected (0.0038 sec)

Or you can remove the constraint check.

Query OK, 0 rows affected (0.0091 sec)

Records: 0  Duplicates: 0  Warnings: 0
*************************** 1. row ***************************
       Table: c1
Create Table: CREATE TABLE `c1` (
  `id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0005 sec)

Further reading 

MySQL 8.0.16 Introducing CHECK constraint

MySQL Manual CHECK Constraints