MacLochlainns Weblog

Having recently installed a fresh copy of MySQL 8.0.21 on Windows, I took careful note of parsing changes in the MySQL Shell. It was tedious that we lost multiple statement processing, which is supported in the MySQL Client and MySQL Workbench because it uses MySQL Client.

It was frustrating when I subsequently discovered that the MySQL Shell took away the ability to write log files by removing the TEE and NOTEE commands. I suspected that since MySQL Workbench was still using the MySQL Client that it should be in the code tree. In fact, the mysql.exe client is in this directory:

C:\Program Files\MySQL\MySQL Server 8.0\bin

So, I immediately created a batch file to put the MySQL Client into my %PATH% environment variable when needed. I used this time tested DOS command:

copy con mysqlclient.bat
set PATH=%PATH%;C:\Program Files\MySQL\MySQL Server 8.0\bin;.
^Z

Now you can call the MySQL client with the following syntax if you have a student user and studentdb database:

mysql -ustudent -p -Dstudentdb

This way, I can just call it in a PowerShell utility before running migration script files that should generate log files for review. It also leaves the MySQL Client out of general use.

While I’m switching labs next term after more than a decade with more comprehensive lab set, I’m hoping the new exercises build the students’ core SQL skill set. Next term, I hope to see whether the change is successful. I’ve opted for using Alan Beaulieu’s Learning SQL: Generate, Manipulate, and Retrieve Data, 3rd Edition, because it’s a great book and uses the MySQL database.

One exercise that the students will lose is a data migration exercise from a badly designed common_lookup table to a well designed common_lookup table. The starting point is shown below on the left and the fixed version is on the right.

There are several problems with the original common_lookup table’s design. The first problem is that the common_lookup_context column does not uniquely identify a location within the data model for at least one list of lookup values. While it uses table names generally, it has no way to support two or more lists within the same table. It also uses a 'MULTIPLE' string for a list of values that supports two tables. The two tables supported by 'MULTIPLE' string are the address and telephone tables.

The lab instructions have the students add the following three columns to the table:

• common_lookup_table
• common_lookup_column
• common_lookup_code

Together the combination of the common_lookup_table and common_lookup_column columns create a non-unique super key. The super key identifies micro subtables. The combination of the common_lookup_table, common_lookup_column, and common_lookup_type columns creates a unique natural key that defines all possible values for a lookup list based on a column in a table.

The lab asked the students to use the existing data, string literal values, and some simple rules to populate the new common_lookup_table and common_lookup_column columns with data. The rules or steps were:

1. Migrate valid table names from the common_lookup_context column to the common_lookup_table column.
2. Migrate a literal 'ADDRESS' value into the common_lookup_table column when the common_lookup_context column holds a 'MULTIPLE' string value.
3. Migrate valid table names from the common_lookup_context column to the common_lookup_column column by appending a '_TYPE' string literal to the common_lookup_context column values, except for those three rows that have a ‘VISA_CARD’, ‘MASTER_CARD’, or ‘DISCOVER_CARD’. The three exempted rows should update the common_lookup_column column with a 'CREDIT_CARD_TYPE' string literal.
4. Migrate a literal 'ADDRESS_TYPE' value into the common_lookup_column column when the common_lookup_context column holds a 'MULTIPLE' string value.
5. After these changes, insert two new rows in the common_lookup table. They should contain 'TELEPHONE' and 'TELEPHONE_TYPE' string literal values. One of the rows should contain 'HOME' for the common_lookup_type column value and the other 'WORK' for the same column.
6. Then, the students were asked to update the foreign key column value in the telephone_type column of the telephone table.

It was a tremendous learning experience for those who did it because there were so many steps required to migrate the structure and data. Years ago, I would offer students half their final grade if they could complete the first four steps in a single UPDATE statement. Occasionally, I had students complete it. We worked through the problem with a small group of students today in one of my two weekly tutoring sessions. I thought it might be nice to document the solution, which use CASE operators in the SET clause of the UPDATE statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

UPDATE common_lookup
SET    common_lookup_table =
         CASE
           WHEN NOT common_lookup_context = 'MULTIPLE' THEN
             common_lookup_context
           ELSE
             'ADDRESS'
         END
,      common_lookup_column =
         CASE
           WHEN common_lookup_table = 'MEMBER' AND
                common_lookup_type IN ('VISA_CARD','MASTER_CARD','DISCOVER_CARD') THEN
             'CREDIT_CARD_TyPE'
           WHEN NOT common_lookup_context = 'MULTIPLE' THEN
             CONCAT(common_lookup_context,'_TYPE')
           ELSE
             'ADDRESS_TYPE'
         END;

As a rule, students would solve Step #6, which migrates the foreign key values of the telephone table’s telephone_type column to the new rows inserted into the common_lookup table. Most would accomplish that step with two UPDATE statements. Very few could see how to create a single UPDATE statement for both conditions and migrate from a now obsolete foreign key value that pointed to the rows of the address table’s rows in the common_lookup table to a valid foreign key value pointed to the telephone table’s rows in the common_lookup table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

UPDATE   telephone
SET      telephone_type =
           CASE
             WHEN common_lookup_type = 'HOME' THEN
               (SELECT common_lookup_id
                FROM common_lookup
                WHERE common_lookup_table = 'TELEPHONE'
                AND common_lookup_type = 'HOME')
             ELSE
               (SELECT common_lookup_id
                FROM common_lookup
                WHERE common_lookup_table = 'TELEPHONE'
                AND common_lookup_type = 'WORK')
             END
WHERE    telephone_type = 
           (SELECT common_lookup_id
            FROM common_lookup
            WHERE common_lookup_table = 'ADDRESS'
            AND common_lookup_type IN ('HOME','WORK');

As always, I hope this helps those looking for new ideas in SQL.

I’m glad that testing new MySQL releases is so frequent for me. Each testing cycle let me catch warning messages about deprecated behaviors before they’re removed. This one announced the deprecation of digits for floating point data types, like double. The following column definition for a table in my code tree triggered the warning message:

, amount                DOUBLE(10,2)

MySQL 8 (8.0.21) raised the following warning message:

Warning (code 1681): Specifying number of digits for floating point data types is deprecated and will be removed in a future release.

Recognizing the deprecation, I redefined the column as:

, amount                DOUBLE

I’m glad the MySQL development team is focused on alerting us to deprecations through warning messages. Naturally, I fixed all of the tables proactively. If you want more detail you can read this MySQL web page on Problems with Floating Point Values.

I’ve been experimenting with the mysqlsh since installing it last week. It’s been interesting. Overall, I’m totally impressed but I did find a problem with how it parses stored procedures.

First thought is always, is it my code? I checked the file by running it as a script file through MySQL Workbench. It ran perfectly in MySQL Workbench but failed repeatedly when run from the mysqlsh utility. Next step, reduce the code to a small test case, retest it, and log a bug if it is replicated. My test case in a test.sql file generates the following errors when run from the mysqlsh utility:

 MySQL  localhost:33060+ ssl  studentdb  SQL > source test.sql
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL ' at line 2
Query OK, 0 rows affected (0.0002 sec)
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1305: PROCEDURE studentdb.test does not exist

The same file generates this series of successful messages when run as a script file from MySQL Workbench:

Preparing...
Importing test.sql...
Finished executing script
Statement
CREATE PROCEDURE test
pv_input1
One
Operation completed successfully

For those who are curious enough to review the test case, here it is:

-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$
 
SELECT 'CREATE PROCEDURE test' AS "Statement";
CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL DATA
BEGIN
  SELECT CONCAT(pv_input1,', ',pv_input2) AS message;
END;
$$
 
-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;
 
-- Call the test procedure.
CALL test('One','Two');

The reply in the bug explained the behavior difference between MySQL Workbench and the MySQL Shell (mysqlsh) environments. MySQL Workbench uses the MySQL client, which supports multiple client statements with the CLIENT_MULTI_STATEMENTS option. Recognizing that, the logging entry SELECT statement should move to a position before setting the DELIMITER, like:

-- Set a label for the log file.
SELECT 'CREATE PROCEDURE test' AS "Statement";
 
-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$
 
CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL DATA
BEGIN
  SELECT CONCAT(pv_input1,', ',pv_input2) AS message;
END;
$$
 
-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;
 
-- Call the test procedure.
CALL test('One','Two');

The new test case only submits one statement at a time. The logging query is submitted by the semicolon, and the test procedure by the double dollar ($$) symbol set.

So, I was correct identifying a parsing behavior difference between MySQL Workbench and MySQL Shell. It appears to be a difference by design but the MySQL Shell documentation fails to explain it can’t manage multiple statements. I hope identifying this saves others time.

It’s also true that the MySQL client software supports TEE and NOTEE to write log files. Unfortunately, MySQL Shell (mysqlsh) doesn’t support the TEE and NOTEE syntax. You can only do minimal logging with the control of standard error (stderr) by using the application and AdminAPI log utilities, which are covered in Chapter 8 of the MySQL Shell 8.0 documentation.

It’s always interesting when I upgrade from one release to the next. I learn new things, and in the case of MySQL’s installation and maintenance I become more grateful for the great team of developers working to produce MySQL 8.

A warning that caught my eye in MySQL 8 (8.0.21) was this one on Unicode with the utf8 character code:

Warning (code 3719): 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

Currently, a character alias for utf8mb3 is an alias for the deprecated utf8mb3 (a 3-byte character set) until it is removed. When the utf8mb3 character set is removed in a subsequent release the utf8 alias will represent the utf8mb4 (a 4-byte character set), which includes BMP and supplemental character support.

It appears to me that its probably a good time to proactively provision disk space for an upgrade to utf8mb4 (a 4-byte character set). It also probably time to use the utf8mb4 character set rather than the utf8 character set alias. The MySQL Server team wrote a blog entry on the when to use which one. Naturally, all this will have a substantial impact on disk space allocated to the database.

I’ve been using MySQL 8 on Linux for a couple years but the Linux repository version didn’t install the new MySQL Shell. So, I discovered the new MySQL Shell when I installed MySQL 8 (8.0.21) on Windows to teach my student how to use it to learn SQL commands. I encourage you to read the full MySQL Shell document.

The following is a tutorial to provision a student user and studentdb database in MySQL. It uses the MySQL Shell (mysqlsh) and stages for uploads of comma-separated values files.

After installing MySQL on the Windows 10 OS, open the Window OS Command Line Interface (CLI) shell by entering the following in the search field at the bottom left:

cmd

It launches a CLI interface to the Windows OS. The cmd (command) utility opens the CLI in the following directory (where you substitute your user’s name for the username placeholder below):

C:\Users\username

At the command prompt, you would enter mysql to launch the old MySQL CLI. Instead of that, you enter mysqlsh to launch the new MySQL CLI as the root user. The command to launch the MySQL Shell as the root user is:

mysqlsh -uroot -p

It should return the following and prompt you for a single character entry to save the password for the Operating System user. As a rule, in a development instance that’s a good idea and practice.

MySQL Shell 8.0.21
 
Copyright (c) 2016, 2020, 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'
Please provide the password for 'root@localhost:33060': ********
Save password for 'root@localhost:33060'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS >

The prompt will not accept SQL commands because it always initializes in the JavaScript (JS) context. The MySQL Shell supports three interactive interfaces: JavaScript, Python, and SQL.

You can verify the integrity of the shell from the JavaScript (JS) context as the root user with the following command:

 MySQL  localhost:33060+ ssl  JS > shell.status()

As the root user, it should return something like this:

MySQL Shell version 8.0.21
 
Connection Id:                9
Default schema:
Current schema:
Current user:                 root@localhost
SSL:                          Cipher in use: TLS_AES_256_GCM_SHA384 TLSv1.3
Using delimiter:              ;
Server version:               8.0.21 MySQL Community Server - GPL
Protocol version:             X protocol
Client library:               8.0.21
Connection:                   localhost via TCP/IP
TCP port:                     33060
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Enabled (DEFLATE_STREAM)
Uptime:                       20 hours 4 min 19.0000 sec

You can switch to the SQL context as the root or any other user with this command. The switch only changes your form of interaction with the server and you remain connected as the root user:

 MySQL  localhost:33060+ ssl  JS > \sql

You can verify that your session is still owned by the root user with the following SELECT statement:

 MySQL  localhost:33060+ ssl  SQL > SELECT user();

It returns:

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.0005 sec)

The next step shows you how to setup a sample studentdb database. Some syntax has changed from prior MySQL releases. Here are the three steps:

1. Create the studentdb database with the following command as the MySQL root user:

   MySQL localhost:33060+ ssl SQL > CREATE DATABASE studentdb;

   MySQL localhost:33060+ ssl SQL > CREATE DATABASE studentdb;

2. Grant the root user the privilege to grant to others, which root does not have by default. You use the following syntax as the MySQL root user:

   MySQL localhost:33060+ ssl SQL > GRANT ALL ON *.* TO 'root'@'localhost';

   MySQL localhost:33060+ ssl SQL > GRANT ALL ON *.* TO 'root'@'localhost';

3. Create the user with a clear English password and grant the user student full privileges on the studentdb database:

   MySQL localhost:33060+ ssl SQL > CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'student'; MySQL localhost:33060+ ssl SQL > GRANT ALL ON studentdb.* TO 'student'@'localhost';

   MySQL localhost:33060+ ssl SQL > CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'student'; MySQL localhost:33060+ ssl SQL > GRANT ALL ON studentdb.* TO 'student'@'localhost';

4. Our sample database uses large file uploads with MySQL’s LOAD command, which means you need to grant one additional global privilege:

   MySQL localhost:33060+ ssl SQL > GRANT FILE ON *.* TO 'student'@'localhost';

   MySQL localhost:33060+ ssl SQL > GRANT FILE ON *.* TO 'student'@'localhost';

The MySQL FILE privilege is a global privilege to read and write files on the local server. MySQL 8 installation on Windows 10 sets the following directory as the target for uploading files in the my.ini file:

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

You can find the setting in the C:\ProgramData\MySQL\MySQL Server 8.0\my.ini file. You can find this value without referencing the my.ini file by querying the data:

show variables like 'secure_file_priv';

A new installation should return:

+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set (0.2253 sec)

You can test the ability to use the LOAD command with the following avenger.csv test file. Copy it into the C:\ProgramData\MySQL\MySQL Server 8.0\Uploads directory and make sure the directory permissions are set to read-only for Everyone (check the Microsoft OS documentation if these concepts are new to you).

1,'Anthony','Stark','Iron Man'
2,'Thor','Odinson','God of Thunder'
3,'Steven','Rogers','Captain America'
4,'Bruce','Banner','Hulk'
5,'Clinton','Barton','Hawkeye'
6,'Natasha','Romanoff','Black Widow'

Open another cmd (command) CLI and put the following code into a file that you save as avenger.sql in the C:\Users\username directory.

-- Conditionally drop objects.
SELECT 'AVENGER' AS "Drop Table";
DROP TABLE IF EXISTS avenger;
 
-- Create an avenger table.
CREATE TABLE avenger
( avenger_id      INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, first_name      VARCHAR(20)
, last_name       VARCHAR(20)
, character_name  VARCHAR(20)) ENGINE=InnoDB DEFAULT=utf8;
 
-- Load the data from a file, don't forget the \n after the \r on Windows or it won't work.
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/avenger.csv'
-- LOAD DATA INFILE 'avenger.csv'
INTO TABLE avenger
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '/'
LINES TERMINATED BY '\r\n';
 
-- Select the uploaded records.
SELECT * FROM avenger;

From the cmd (command) shell, launch the MySQL Shell as the student user with the following syntax:

mysqlsh -ustudent -p -Dstudentdb

At the MySQL Shell (mysqlsh) prompt switch from the default JavaScript (JS) context to SQL context and run the following query:

 MySQL  localhost:33060+ ssl  studentdb  SQL > \sql
 MySQL  localhost:33060+ ssl  studentdb  SQL > SELECT user(), database();

If you did everything above correctly, it should return:

+-------------------+------------+
| user()            | database() |
+-------------------+------------+
| student@localhost | studentdb  |
+-------------------+------------+
1 row in set (0.0003 sec)

Again, assuming you did everything above correctly, you should be able to run your avenger.sql script file from the MySQL Shell (mysqlsh) prompt, like:

 MySQL  localhost:33060+ ssl  studentdb  SQL > source avenger.sql

It should return output like the following:

Query OK, 0 rows affected (0.0003 sec)
+------------+
| Drop Table |
+------------+
| AVENGER    |
+------------+
1 row in set (0.0003 sec)
Query OK, 0 rows affected (0.0595 sec)
Query OK, 0 rows affected (0.0002 sec)
Query OK, 0 rows affected (0.1293 sec)
Query OK, 0 rows affected (0.0002 sec)
Query OK, 6 rows affected (0.0046 sec)
 
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0
Query OK, 0 rows affected (0.0004 sec)
+------------+------------+------------+-------------------+
| avenger_id | first_name | last_name  | character_name    |
+------------+------------+------------+-------------------+
|          1 | 'Anthony'  | 'Stark'    | 'Iron Man'        |
|          2 | 'Thor'     | 'Odinson'  | 'God of Thunder'  |
|          3 | 'Steven'   | 'Rogers'   | 'Captain America' |
|          4 | 'Bruce'    | 'Banner'   | 'Hulk'            |
|          5 | 'Clinton'  | 'Barton'   | 'Hawkeye'         |
|          6 | 'Natasha'  | 'Romanoff' | 'Black Widow'     |
+------------+------------+------------+-------------------+
6 rows in set (0.0005 sec)

You now have a student user and studentdb database like my students. I hope it was fun to build.

You can find the my.ini file in the C:\ProgramData\MySQL\MySQL Server 8.0 directory of a standard Windows 10 file system. Its a good idea not to change anything unless you know what you’re doing, and remember you need to restart the Microsoft MySQL80 Service for any change to be made effective in your database operations.

While preparing a set of student instructions to create a MySQL 8 (8.0.21) Windows 10 instance I found an error with LOAD command and the --secure-file_priv variable set in the my.ini file. After granting the global FILE permission to the previously provisioned student user:

GRANT FILE ON *.* TO 'student'@'localhost';

Any attempt to run the following command failed:

LOAD DATA INFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\avenger.csv'
INTO TABLE avenger
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

and, raise this error message:

ERROR: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The following covers my exploration to try and fix this error without removing a designated directory for secure file uploads. While MySQL 5.7 contains the request for he server-file-priv variable, there is nothing in the MySQL System Server Variables document on how to troubleshoot the server-file-priv variable when set. Somehow, I think there should be some mention of how to resolve this error without unsetting the server-file-privy variable.

I checked and fixed all Windows 10 sharing and read-write privileges on the secure-file-priv designated directory. They Windows 10 settings allowed for global sharing and both read and write privileges, but the LOAD command failed to load the file contents from the authorized Uploads directory.

The MySQL FILE privilege is a global privilege to read and write files on the local server. MySQL 8 installation on Windows 10 sets the following directory as the target for uploading files in the my.ini file:

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

You can find the setting in the C:\ProgramData\MySQL\MySQL Server 8.0\my.ini file. You can find this value without referencing the my.ini file by querying the data:

show variables like 'secure_file_priv';

A new installation should return:

+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set (0.2253 sec)

You can find the my.ini file in the C:\ProgramData\MySQL\MySQL Server 8.0 directory of a standard Windows 10 file system.

1. Using the Windows’ File Explorer, I checked the Uploads directory’s privileges by right clicking the Uploads directory to check the Properties of the directory in the File Explorer dialog box:

   

2. The General tab indicates that the files are Read-only, as shown:

   

   I unchecked the Read-only checkbox. Then, I retested it with the same negative results.

3. The Sharing tab indicates that the files are Shared, as shown:

   

4. Clicking the Share … button, the files in this directory are shared with Read/Write permissions to Everyone, as shown below.

   

5. The Security tab indicates that the files Everyone has Full control of the files in this directory, as shown:

   

Unfortunately, with all these set appropriately the secure-file-priv variable appears to block reading files from the designated secure directory. It appeared that I may have to remove the secure-file-priv setting from the my.ini file and reboot the server. Then, I found my error in the SQL LOAD command. I wasn’t backquoting the backslashes.

The only way that the LOAD command would work required the following steps:

1. I put the avenger.csv file in the following directory pointed to by the secure-file-privs value in the my.ini.

   C:\ProgramData\MySQL\MySQL Server 8.0\Uploads

   C:\ProgramData\MySQL\MySQL Server 8.0\Uploads

2. Updated the SQL LOAD statement to backquote the backslashes:

   LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\avenger.csv' INTO TABLE avenger FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';

   LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\avenger.csv' INTO TABLE avenger FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';

   The LOAD command worked and put the CSV file contents into the avenger table, as shown in the query results below:

   +------------+------------+------------+-------------------+ | avenger_id | first_name | last_name | character_name | +------------+------------+------------+-------------------+ | 1 | 'Anthony' | 'Stark' | 'Iron Man' | | 2 | 'Thor' | 'Odinson' | 'God of Thunder' | | 3 | 'Steven' | 'Rogers' | 'Captain America' | | 4 | 'Bruce' | 'Banner' | 'Hulk' | | 5 | 'Clinton' | 'Barton' | 'Hawkeye' | | 6 | 'Natasha' | 'Romanoff' | 'Black Widow' | +------------+------------+------------+-------------------+ 6 rows in set (0.0005 sec)

   +------------+------------+------------+-------------------+ | avenger_id | first_name | last_name | character_name | +------------+------------+------------+-------------------+ | 1 | 'Anthony' | 'Stark' | 'Iron Man' | | 2 | 'Thor' | 'Odinson' | 'God of Thunder' | | 3 | 'Steven' | 'Rogers' | 'Captain America' | | 4 | 'Bruce' | 'Banner' | 'Hulk' | | 5 | 'Clinton' | 'Barton' | 'Hawkeye' | | 6 | 'Natasha' | 'Romanoff' | 'Black Widow' | +------------+------------+------------+-------------------+ 6 rows in set (0.0005 sec)

I got in a rush and over thought it. However, this is how you make it work. Naturally, you can point the secure-file-privs variable to another location of your choice.

I should also note that MySQL is smart enough to change forward slashes to backslashes in the Windows OS. That means you could also use the following SQL LOAD statement:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/avenger.csv' INTO TABLE avenger FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '/' LINES TERMINATED BY '\r\n';

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/avenger.csv' INTO TABLE avenger FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '/' LINES TERMINATED BY '\r\n';

As always, I hope this helps those looking for a solution.

MySQL will be used for our online sections because the VMware instance and Docker configurations where too large to effectively download this term.

MySQL 8.0.21 Installation Steps

After you download the MySQL 8 MSI file, you will perform the following 24 steps to install MySQL on Windows 10. If you want a full developer install you must install Microsoft Excel and Visual Studio first.

1. The first thing you need to do is grant privileges to allow the MySQL Installer application to work in Windows 10. Click the Yes button to authorize the MySQL Installer to run.

2. The next thing you need to do is grant privileges to allow the MySQL Installer Launcher application to work in Windows 10. Click the Yes button to authorize the MySQL Installer to run.

3. Now you start the install by choosing a setup type. As a rule, I recommend you install the Developer Default. It is the default selection and preselected for you. Click the Next button to verify that you can install what you’ve selected.

4. The next workflow step checks requirements and lists any unmet requirements. The workflow lists the requirements for MySQL for Excel 1.3.8 as unmet because Microsoft Excel is not installed. Click the Next button when there are no unmet requirements in the list. Click the Back button to remove MySQL for Excel 1.3.8 from the setup selection.

5. Returning to the setup type workflow, you should select the Custom radio button. Click the Next button to view the list of selected types.

6. Use the green arrow pointing to the left to remove MySQL for Excel 1.3.8 from the list. Click the Next button to continue the MySQL Installer to install the selected MySQL libraries.

7. This dialog will display for several minutes as each of the MySQL Installer modules is. Click the Next button to move forward in the MySQL Installer workflow.

8. This dialog qualifies that there are three products to configure. Click the Next button to begin configuration of these products.

9. This dialog lets you choose between a Standalone MySQL Server / Classic MySQL Replication and InnoDB Cluster. You should choose the Standalone MySQL Server for a developer installation on a laptop or desktop. Click the Next button to continue the MySQL Installer configuration.

10. This dialog lets you choose set the type and networking values. They should be preselected as they are in the screen image. Click the Next button to continue the MySQL Installer configuration.

11. This dialog lets you choose between SHA256-based and the older MD5 encryption. Click Use Strong Password Encryption for Authentication (RECOMMENDED) radio button. Click the Next button to continue the MySQL Installer configuration.

12. This dialog lets you enter the MySQL Root Password. Click the Next button to continue the MySQL Installer configuration.

13. This dialog lets you enter a Windows Service Name and install a Standard System Account or Custom User account. Click the Next button to continue the MySQL Installer configuration.

14. This dialog lets you apply the configuration of the MySQL Server or Custom User product. Click the Next button to continue the MySQL Server configuration.

15. This dialog lets you watch the progress of the MySQL Server or Custom User configuration. Click the Finish button to complete the MySQL Server configuration.

16. This dialog lets you choose the next MySQL Router product for configuration. Click the Next button to begin the MySQL Router configuration.

17. This dialog allows you to configure the MySQL Router Configuration product. Leave the Hostname and Password fields blank when you do not want to configure the MySQL Router Configuration product. Click the Finish button to complete the MySQL Router workflow.

18. This dialog lets you choose the next Samples and Examples product for configuration. Click the Next button to begin the Samples and Examples configuration.

19. This dialog lets you create a Windows 10 MySQL Service. You enter the root password that you entered in Step #12. After you enter root password, click the Check button to verify the root password. The Check button enables the Next button when the root account’s password is validated. Click the now enabled Next button to create the MySQL Service.

20. This dialog lets you create a Windows 10 MySQL Service. Click the now enabled Next button to create the MySQL Service.

21. This dialog applies all the configurations selected. Click the Execute button to proceed with the configuration.

23. This dialog lets you watch the progress of the configuration. Click the Finish button to complete the product configurations.

23. This dialog the product configurations. You should note that the MySQL Router was not configured or needed. Click the Next button to complete the installation.

24. This dialog completes the workflow and launches MySQL Workbench and Shell. Click the Finish button to complete the installation and configuration processes.

After you install MySQL, the following shows you how to provision a studentdb database. It also shows you how to enable the global file privilege and how to read data from an external comma-separated values (CSV) file.

It’s always interesting when I answer questions. This question was how to you perform a correlated UPDATE statement. My answer was assuming you’re updating the rating_id foreign key column in the rating table with the value from an item_rating column in the item table where on or another column value in the rating table match the item_rating column value in the item table match, you would write a correlated UPDATE statement like:

UPDATE item i
SET    i.rating_id = r.rating_id
WHERE  EXISTS
        (SELECT NULL
         FROM   rental r
         WHERE  r.rating = i.item_rating
         OR     r.description = i.item_rating);

This works in Oracle, MySQL, MariaDB, and MS SQL Server. I thought my work was done but I was wrong. The individual was trying to write a correlated UPDATE statement for PostgreSQL. The statement returned the following error:

ERROR:  syntax error at or near "WHERE"
LINE 3: WHERE  EXISTS
       ^

I did didn’t find an article to point the individual to after quick Google and DuckDuckGo searches. So, I thought I’d provide how you do it in PostgreSQL:

UPDATE item i
SET    rating_id = r.rating_id
FROM   rating r
WHERE  r.rating = i.item_rating
OR     r.description = i.item_rating;

In short, PostgreSQL doesn’t do what most expect because the UPDATE statement supports a FROM clause. Let’s give them the prize for different dialect. While I hope that I’m not a syntax bigot because I use MySQL more, I think the default syntax should always be supported in SQL dialects. After all, MySQL has a far superior named-notation INSERT statement alternative to the standard with the assignment method but MySQL also supports the standard syntax.

While I’ve shown you how to do it in PostgreSQL, what do you think? Should PostgreSQL be as responsible as MySQL is in maintaining standard SQL approaches?

A student question: Does JavaScript make context switching for web-based applications obsolete? Wow! I asked what that meant. He said, it means JavaScript replaces all other server-side programming languages, like PHP, C#, or Python. I asked the student why he believed that. His answer was that’s what two interviewing managers told him.

I thought it would be interesting to put the idea to a test. Below is a Node.js script that acts as a utility that queries the MySQL database with substitution variables in query. It also returns a standard out (stdout) stream of the MySQL query’s results. It also supports three flag and value pairs as arguments, and optionally writes the results of the MySQL query to a log file while still returning result as the stdout value. All errors are written to the standard error (stderr) stream.

The Node.js solution is completely portable between Windows and Linux. You can deploy it to either platform without any edits for Windows case insensitive Command-Line Interface (CLI). Clearly, Node.js offers a replacement for direct interaction with the .NET components in PowerShell. This appears to mean basic Linux shell or PowerShell knowledge is all that’s required to write and deploy JavaScript programs as server-side programming solutions. It means anything that you would have done with the .NET you can do with JavaScript. Likewise, you can replace PHP, C#, Python, or Ruby server-side scripts with JavaScript programs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

// Declare constants. 
const fs = require('fs') 
const util = require('util') 
const express = require('express') 
const mysql = require('mysql') 
const connection = mysql.createConnection({ 
  host: 'localhost', 
  user: 'student', 
  password: 'student', 
  database: 'studentdb' 
}) 
 
// Declare local variables for case insensitive use. 
var data = '' 
var buffer = Buffer.alloc(0) 
var path = '' 
 
// Declare default query variables dates. 
var startDate = new Date('1980-01-01') 
var endDate = new Date() 
 
// Set default endDate value as tomorrow. 
endDate.setDate(new Date().getDate() + 1) 
 
// Define a regular expression for valid file names. 
var regexp = /^([0-9a-zA-Z]+|[0-9a-zA-Z]+\.+[0-9a-zA-Z]{3})$/ 
 
// Assign dynamic variables from arguments. 
var argv = process.argv.slice(2) 
 
// Check for paired values, evaluate and assign them to local variables. 
if ((argv.length % 2) == 0) { 
 for (let i = 0; i < argv.length; i += 2) { 
   // Assign a file name to write to the output path. 
   if ((argv[i].toLowerCase() == '-f') && (regexp.test(argv[i+1]))) { 
     // Assign present working for Windows or Linux. 
     if (process.platform == 'win32') 
       path = '.\\' + argv[1] 
     else 
       path = './' + argv[1] 
   } 
   // Assign a start date from the input string. 
   else if (argv[i].toLowerCase() == '-b') { 
     startDate = new Date(argv[i+1]) 
   } 
   // Assign a end date from the input string. 
   else if (argv[i].toLowerCase() == '-e') { 
     endDate = new Date(argv[i+1]) 
   } 
 } 
} 
else { 
 console.error('Arguments must be in pairs: flag and value.') 
} 
 
// Define and run MySQL query. 
connection.query("SELECT   i.item_title " + 
                 ",        date_format(i.release_date,'%d-%M-%Y') AS release_date " + 
                 "FROM     item i JOIN common_lookup cl " + 
                 "ON       i.item_type = cl.common_lookup_id " + 
                 "WHERE    cl.common_lookup_type = 'BLU-RAY' " + 
                 "AND      i.release_date BETWEEN ? AND ? " + 
                 "ORDER BY i.release_date" 
                ,[startDate, endDate], function (err, result) { 
 if (err) { 
   console.error('Query contains error ...') 
   console.error('-> ' + err) 
 } 
 else { 
   // Prints the index value in the RowDataPacket. 
   for(let element in result) { 
     data += result[element].item_title + ', ' + result[element].release_date + '\n' 
   } 
   // Write file when data string is not empty. 
   if (data.length > 0 ) { 
     buffer = Buffer.alloc(data.length,data) 
 
     // Check for a defined path before writing a file. 
     if (path.length > 0) { 
       // Open the file. 
       fs.open(path, 'w', function(err, fd) { 
         if (err) { 
           console.error('Could not open [' + path + '] file [' + err + ']') 
         } 
         else { 
           // Write the file. 
           fs.write(fd, buffer, 0, buffer.length, null, function(err) { 
             if (err) 
               console.error('Error writing [' + path + '] file [' + err + ']') 
             fs.close(fd, function() { 
               if (fs.existsSync(path)) { 
                 process.exit(0) 
               } 
             }) 
           }) 
         }   
       }) 
     } 
     // Set standard out (stdout). 
     console.log(data) 
   } 
   else { 
     console.error('Query returned no rows.') 
   } 
 } 
}) 
 
// Close MySQL connection. 
connection.end()

You can call this code with the default values, like

node app.js

You can call this code with a user defined file name, and a custom start and end date values, like

node app.js -f output.csv -b '2001-01-01' -e '2004-12-31'

The latter command returns the following by querying my MySQL studentdb video store:

Star Wars II, 16-May-2002 
Harry Potter and the Chamber of Secrets, 28-May-2002 
Harry Potter and the Sorcerer's Stone, 28-May-2002 
Die Another Day, 03-June-2003 
Harry Potter and the Prisoner of Azkaban, 23-October-2004

As always, I hope this helps somebody trying to sort it out.