Thursday, November 7, 2019

Want to talk about MySQL at SCaLE 18x?

The Call For Papers for SCaLE 18x ends soon and I would love to have your talk on MySQL be part of the MySQL track! The track is usually on the Friday of the show!

I am 'curating' the MySQL track again and would love to have you talk on MySQL.  Do you have a story about using MySQL, some trick admin skills you would like to share, a beginners guide to X & MySQL, or a case study? Well then, please submit.

What is SCaLE?  Well it is roughly 5,000 people in Pasadena, California next March 5-8 2020 at the convention center in the heart of the City.  It is the only big open source in Southern California and features multiple tracks on subjects ranging from AI to how to work remotely.  The expo hall is a few acres of the best tech and projects that you will find anywhere.

Do I really have to write a paper? Well, no.  You do need to fill out a form online on your proposal that you would like to talk and make sure that you mark it for the MySQL track if it is MySQL related!  The dead line is the end of November!

Fear of public speaking? Well, this is a great event if you are timid or think you might be. This is like the large user group with techies who want to hear your story, see you succeed, and want to hear you opinion.

What else do I get?  Well you get a badge that says 'speaker' along with the ability to go to as many sessions as you can at the most exciting open source show.  And if you are on the MySQL track maybe I will get you some specialized MySQL swag!

So if you have an idea for a talk please submit it before November 30th!

Need help or want an extra set of eyes for you proposal -- then contact me! Please! I want you MySQL talk next March!

Tuesday, November 5, 2019

London Open Source DB: MySQL 2020

The London Open Source Database Meetup is hosting me on December 4th -RSVP
and I will be talking about  New MySQL Features and a Brief Look into 2020!

MySQL has made lot of progress with version 8.0 and it has become the fasted adopted new version of MySQL in history. The new CI/CD release pattern had been delivering a lot of new features on a regular basis that you may have not noticed. There is JSON Document Validation thanks to the good people at JSON-schema.org, random passwords, hash joins, explain analyze, constraint checks, and more. So if you need to caught up this is you chance and if you are wondering what is in the near term future this is your opportunity.

Monday, November 4, 2019

MySQL Day in Austin November 12th! RSVP Today!!

Attend this half-day event to hear why MySQL is the open source database of choice for business leaders, developers and system architects. Please RSVP here!


With the official release of version 8.0, MySQL now offers SQL and NoSQL capabilities.  We ill
demonstrate how MySQL helps our customers shorten time to market, reduce IT costs, and increase revenue growth – all while providing enterprise grade security via advanced encryption authentication, firewall, and more.


Takeaway tips and techniques on:

  • Containers
  • Securing your data - GDPR
  • MySQL without the SQL

Date and Time: Tue, November 12, 2019  9:30 AM – 1:00 PM CST
Location: Oracle 2300 Cloud Way Austin, TX 78741 

I hope to see all y'all there!


Thursday, October 31, 2019

MySQL Random Password Generation

Many years ago I was working at a university and had to create accounts for students every semester.  Each account needed a random password and there were several hacks used to do that.  One of the new features in MySQL 8.0.18 is the ability to have the system generate a random password.

Example

  SQL > create user 'Foo'@'%' IDENTIFIED BY RANDOM PASSWORD;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| Foo  | %    | Ld]5/Fkn[Kk29/g/M;>n |
+------+------+----------------------+
1 row in set (0.0090 sec)

Another Example

SQL > ALTER USER 'Foo'@'%' IDENTIFIED BY RANDOM PASSWORD;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| Foo  | %    | !rN<NCxjE5ncC6mB*2:@ |
+------+------+----------------------+
1 row in set (0.0102 sec)

Yet Another Example

 SQL > SET PASSWORD FOR 'Foo'@'%' TO RANDOM;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| Foo  | %    | o{EC-pniUAapyzUjE0sn |
+------+------+----------------------+
1 row in set (0.0102 sec)

This will be handy for many and works with your auth_string setting.  Details can be found at https://dev.mysql.com/doc/refman/8.0/en/password-management.html#random-password-generation


Wednesday, September 25, 2019

Upgrading from MySQL 5.7 to MySQL 8.0 Part II

Reserved Words and UTF8MB4 Issues

The upgrade checker utility in the new MySQL Sell will warn you about potential problems.  The two most common problems I have seen are finding out one of your column names is a reserved word and character sets.

Reserved Words

You will spot reserved word issues on the second step of the upgrade check:
2) Usage of db objects with names conflicting with reserved keywords in 8.0
  Warning: The following objects have names that conflict with reserved
    keywords that are new to 8.0. Ensure queries sent by your applications use
    `quotes` when referring to them or they will result in errors.
  More information:
    https://dev.mysql.com/doc/refman/en/keywords.html

  davetest.reserved_word.over - Column name

In the above we are being told that in the schema 'davetest' in the table 'reserved_word' that there is a column named 'over' that conflicts with the newly added reserved word OVER.

There are two possible fixes.  One is to quote with back ticks (` not ') the column name for the rest of the lifetime of that table. You can use the back ticked item but have to remember to ALWAYS back tick the column name. Two is to rename 'over' to something else that is not a reserved word. 

UTF8 May Not Equal UTF8MB4

One of the big changes in MySQL 8.0 is the support for UTF8MB4 which includes support for version 9 of the Unicode character set. Before 8.0 UTF8 really meant UTF8MB3.  3 does not have full support for the forth plane which means no Chinese, Japanese, or Korean language support or support for emojis.

What We Used To Do


The past several years had most folks creating databases & schemas with UTF8 like the following:

CREATE DATABASE mydb  CHARACTER SET utf8  COLLATE utf8_general_ci;

This provides better support for CJK than say Latin1 but still falls short.  And if you run the util.checkForServerUpgrade(), the third step will tell you the following:

util.checkForServerUpgrade('root@localhost:3306') output for #3
And it tell you which schemas and tables should be updated to UTF8MB4.
The schema and table that need to be updated to UTF8MB4

The Fix

The good news is that is relatively easy, if not a little tedious, to convert.  First start with the database/schema.

ALTER SCHEMA davetest CHARACTER SET UTFMB4;

And for each table you will have to change the default and then change each column that uses a character set collation:

ALTER TABLE mytest DEFAULT CHARACTER SET UTF8MBF,
MODIFY COLUMN name CHARACTER SET utf8mb4 COLLATE utf8mb4_900_ai_ci;

Yes, this conversion is a little painful and tedious. But it does let you have access to all the Unicode characters.






Monday, September 23, 2019

Upgrading from MySQL 5.7 to MySQL 8.0 Part I

MySQL 8.0 has been the fastest in uptake of any version of MySQL.  But there are still a few of your still running 5.7.  To help you get from 5.7 to 8.0 you can use the new MySQL Shell or mysql.  The mysqlsh program has a built in upgrade checker to make sure you have an easy upgrade and to warn you of any potential problems before your smack into them.

The first step is to install mysqlsh and it can be as simple as using the MySQL apt repo and then running sudo apt-get install mysql-shell. See A Quick Guide to Using the MySQL Apt Repo.

Second is connecting with the new shell. Now something to note is that you have to specify the port for the standard protocol as the new shell wants to default to using the new protocol's port at 33060.  Simply specify the root account, the host, and that 3306 port.

Next run the upgrade checker utility with util.checkForServerUpgrade("root@localhost:3306")

And the upgrade checker will tell what, if anything needs to be updated. The example below is kind of boring as it does not have any major problems to solve. But it does show how to check what your 5.7 needs before your upgrade.

$ mysqlsh root@localhost:3306
Please provide the password for 'root@localhost:3306': ******
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No):
MySQL Shell 8.0.17

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9
Server version: 5.7.27 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL localhost:3306 ssl JS>util.checkForServerUpgrade("root@localhost:3306")
Please provide the password for 'root@localhost:3306': ******
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No):

The MySQL server at localhost:3306, version 5.7.27 - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.17...

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with reserved keywords in 8.0
  No issues found

3) Usage of utf8mb3 charset
  No issues found

4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

5) Partitioned tables using engines with non native partitioning
  No issues found

6) Foreign key constraint names longer than 64 characters
  No issues found

7) Usage of obsolete MAXDB sql_mode flag
  No issues found

8) Usage of obsolete sql_mode flags
  No issues found

9) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Circular directory references in tablespace data file paths
  No issues found

12) Usage of removed functions
  No issues found

13) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

14) Removed system variables for error logging to the system log configuration
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging

15) Removed system variables
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

16) System variables with new default values
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

17) Schema inconsistencies resulting from file removal or corruption
  No issues found

18) Issues reported by 'check table x for upgrade' command
  No issues found

19) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations.
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:
   
    [mysqld]
    default_authentication_plugin=mysql_native_password
   
    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication

Errors:   0
Warnings: 1
Notices:  0

No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.

Next Time 


Next time I will look at solving some of the more common errors.




Thursday, September 19, 2019

My MySQL Account Has TWO Passwords

Dear Dave,

My DBA told me that an account I use to talk to my MySQL database instance has TWO passwords! How does that happen? Do I have to provide both passwords every time? 

Sincerely,

A Confused User Who Does Not Want to Type Two Passwords All The Time


Dear Confused User,

Dual Password Support was added in MySQL 8.0.14 and it is a very handy thing. User accounts can now have a primary password and a secondary password. Image a scenario where you are rotating passwords as directed by your company policy but it takes a while to change the credentials in all your code despite your best EMACS magic. The ability to keep the servers up and active as your do your security due diligence is very helpful.

And you do not need to type both password! Just one and hopefully the newer one.

How Do You Establish A Secondary Password?


Very simple.

ALTER USER 'dave'@'deardave.xyz' IDENTIFIED BY 'deardave2' RETAIN CURRENT PASSWORD;

In the mysql.user table there is now a JSON column named User_attributes that now has the secondary password:

 {"additional_password": "$A$005$;H7\u001b\u001b\u0006<`qFRU\tNRxT Z\u0003Ya/iej8Az8LoXGTv.dtf9K3RdJuaLFtXZHBs3/DntG2"} 


And How Do I Get Rid Of A Secondary Password


So you are done with updating the password and want to remove the old password? Simply use another ALTER TABLE.

ALTER USER 'dave'@'deardave'xyz' DISCARD OLD PASSWORD

Things To Note


RETAIN CURRENT PASSWORD keeps the old password in the additional_password key/value pair. The new password becomes the primary. And if the new password is empty both the primary and secondary passwords will be empty.

If you try to add a secondary password to an account without a password it will fail.

If the account has a secondary password by you change the primary password