Pandas and The MySQL Shell

    Pandas is a Python software library for data analysis and manipulation.  It allows you to import data from CSV files, SQL, and Excel.  And it is open source.

    Pandas works well with the MySQL Shell.  After installing Pandas with pip install pandas on my Fedora test system, I was able to load Pandas.  The documentation is excellent at pandas.pydata.org   and I was quickly able to get one of their examples working from within mysqlsh.

Did I mention SQL as a source of data? Reading from MySQL is very easy.  

import mysql.connector as connection
import pandas as pd

try:
    mydb = connection.connect(host="localhost", database = 'world',user="panda", passwd="123Bear!")
    query = "SELECT * FROM country LIMIT 5;"
    result_dataFrame = pd.read_sql(query,mydb)
    mydb.close() #close the connection

except Exception as e:

    mydb.close()
    print(str(e))

    I will investigate Pandas more and report anything I find.

MySQL 8.0.26 And Interesting Items in the Release Notes

You may have noticed that MySQL 8.0.26 was released today (yea!)  and I recommend reading the release notes but here are some of the highlights.

• TLS version 1 and 1.1 are deprecated. Please use later versions such as 1.2 and 1.3 (you made need OpenSSL 1.1.1 or higher too).
• More information in the server log on client timeouts includes the timeout value, and client user and host when that information is available
• Internal functions to copy values between columns are improved and test at about11% faster (YMMV)

There are a lot of interesting bits of information in the release notes and they show the amount of work that the MySQL Engineering Teams put into a release to give you a better product.

But What Are Those Warnings?

But some of you may be seeing the following messages:

The syntax 'sync_master_info' is deprecated and will be removed in a future release. Please use sync_source_info instead.

'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

What does that mean?

The first warning is part of a continuing process of replacing offensive terms (see MySQL Terminology Updates) and the new syntax is sync_source_info, replacing sync_master_info. These changes are much more involved than a simple global search and replace in the source code.  According to the release notes most of the changes have been made along with the corresponding help text.

The second is a warning about upcoming changes in STRICT MODE (stricter mode?) adding settings.

From the MySQL Manual

The default SQL mode in MySQL 8.0 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.

And also peek at Strict SQL Mode 

And as of 8.0.26 you can set your session to strict mode only if you the privileges' to set restricted variables. 

As usual you can download from https://www.mysql.com/downloads/

Do You Really Need to Upgrade from MySQL Native Password to Caching SHA256 Password Authentication?

    If you have not read MDS, PHP and authentication by my brilliant colleague LeFred please do so. The TL;DR is that most recent versions of the PHP support the MySQL 8.0 standard for authentication, Caching SHA 256, but with PHP 7.3 and earlier you will have to use the older MySQL Native Password authentication.

    Someone reached out to me with a direct message to ask me if they really needed to change from MySQL native to Caching SHA 256.  

    Well, what versions of MySQL are you using?  The good news is that they are running 8.0.23 in production, '25 in test and development. But all the accounts are using MySQL Native Authentication.

    What versions of PHP?  There was some hesitation before this question was answered.  Dev is 8.0.8, test is a mixture 8.0.8, 7.4.21, and 7.4.21, and production 7.4.21. With one little exception of 7.4.20 that they are trying to move away from, they promised, by the end of the month.

    Do we have to re-do all the accounts to access MySQL to this new method?  Well, it depends.   In this case they had several projects using for the most part unique schemas. And each had separate usernames & authentication strings -- they were not using the MySQL root account for everything thing.  Passwords were rotated a few times each year.  

    No direct access from the internet to the instances?  Well, there is one application that directly connects through a dedicated port on the firewall. Everything else that touches the MySQL instances are within the firewall.  And nobody really seems to check on just who is using that connection or audits activity on that direct connection.  

    The Caching SHA 256 method is much more secure hashing method.  It is required to use either a TLS connection or an unencrypted connection that supports password exchange using an RSA key pair.  Please see SHA-256 Pluggable Authentication. By default MySQL 8.0 sets up secure connections between the clients and servers.

    I asked if there was anything in their data that was sensitive, proprietary, covered by privacy laws, or anything they just did not want made public. Well, yes there was.   

Do They Need to Upgrade?

    I told this person, no, you do not need to immediately upgrade from MySQL native to SHA 256 but I would suggest that they plan to upgrade over the next several months.  Other than being paranoid about the one direct connection through the firewall, it sounded like they were keeping on top of things.  

    Being paranoid about the security of your data should be the default mode of and DBA or developer.  Going with a lower standard than the MySQL 8.0 defaults means you are actively downgrading your protection.  

    I recommend reading Protecting MySQL Passwords With the sha256_password Plugin from the MySQL Server Team Blog for more technical details on using SHA 256 authentication. 

SERIAL Columns for Indexes

    SERIAL is a column definition attribute that I use frequently and it caught me by surprise when someone looking at a slide deck of a past presentation of mine asked about my use of it. This person is a long time user of MySQL but did not know this keyword. So I need to spread the word about SERIAL. This might save you some typing and some related fat fingering.

Defining Your Index Column

    When creating a table you want a PRIMARY KEY column. And even with the SERIAL attribute you have to designate which column this is. You cannot use more than one SERIAL designated column or you will get ERROR: 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key.  Why PRIMARY KEY is not added to the other good stuff include with SERIAL does not make sense to me but there are probably dozens of reasons plus the SQL standard arguing against me here.

    Most of the time INTEGERS are used as the data type for that key column. Having the system automatically increment the count for the column is not only handy but provides uniqueness, at least to some extent.  You want to avoid NULL values as they can cause a whole bucket of problems on an indexed field.  And it helps to use only positive values (up to 2^63 - 1).  And most of the time you have no idea how many records there will be so you use the BIGINT type just so that you should never run out of numbers (even though smaller indexes have some performance benefits you should be aware of).  

    So that means you have to specify an unsigned big integer, not nullable, auto incremented column. That is a lot of things to specify and I like to take shortcuts that make sense. That is why I like SERIAL.  In the following example it is easy to see that the six letter 'SERIAL' is a lot less typing than 'bigint unsigned NOT NULL AUTO_INCREMENT' string it encapsulates. 

 SQL > create table pp1 (id SERIAL PRIMARY KEY, doc_number INT UNSIGNED)\G
Query OK, 0 rows affected (0.0334 sec)
SQL > SHOW CREATE TABLE pp1\G
*************************** 1. row ***************************
       Table: pp1
Create Table: CREATE TABLE `pp1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `doc_number` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0049 sec)
SQL >

Anything that improves readability and reduces the opportunity for yours truly to fat finger is a big plus.