MacLochlainns Weblog

While PL/Python is an untrusted language inside PostgreSQL, I was installing it to test some of its features. First, we check to see if PL/Python is installed by attempting to create a PL/Python function:

CREATE FUNCTION pima(a integer, b integer)
RETURNS integer AS
$$
if a > b:
  return a
return b
$$ LANGUAGE plpython3u;

It likely should raise an error like this because PL/Python is an untrusted language. It’s untrusted because it runs with root privileges rather than a restricted user’s privilege. More or less, with PL/Python you can access the entire database. This makes PL/Python more of a threat than tool beyond experimentation in a test database.

ERROR:  language "plpython3u" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.

A quick query as the postgres user tells you whether or not PL/Python is installed:

SELECT * FROM pg_language;

It returns the following:

 lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl 
----------+----------+---------+--------------+---------------+-----------+--------------+--------
 internal |       10 | f       | f            |             0 |         0 |         2246 | 
 c        |       10 | f       | f            |             0 |         0 |         2247 | 
 sql      |       10 | f       | t            |             0 |         0 |         2248 | 
 plpgsql  |       10 | t       | t            |         14088 |     14089 |        14090 | 
(4 rows)

I attempted to add PL/Python with the following command:

CREATE EXTENSION plpython3u;

It raised the following error:

ERROR:  could not open extension control file "/usr/pgsql-11/share/extension/plpython3u.control": No such file or directory

The error basically appeared to occur because there’s a missing Python 3 package from what I could find on the web. I installed the missing postgresql-plpython3 package as the root superuser, which you also could install as a member of the sudoer list. The following shows how to install it as a sudoer member:

sudo yum install -y postgresql-plpython3

You should see something close to the following console output:

Last metadata expiration check: 0:43:53 ago on Fri 06 Nov 2020 10:42:28 AM MST.
Dependencies resolved.
============================================================================================================
 Package                           Architecture        Version                   Repository            Size
============================================================================================================
Installing:
 postgresql-plpython3              x86_64              11.7-2.fc30               updates               86 k
Installing dependencies:
 postgresql-server                 x86_64              11.7-2.fc30               updates              5.3 M
 
Transaction Summary
============================================================================================================
Install  2 Packages
 
Total download size: 5.3 M
Installed size: 23 M
Downloading Packages:
(1/2): postgresql-plpython3-11.7-2.fc30.x86_64.rpm                          218 kB/s |  86 kB     00:00    
(2/2): postgresql-server-11.7-2.fc30.x86_64.rpm                             3.0 MB/s | 5.3 MB     00:01    
------------------------------------------------------------------------------------------------------------
Total                                                                       2.1 MB/s | 5.3 MB     00:02     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                    1/1 
  Running scriptlet: postgresql-server-11.7-2.fc30.x86_64                                               1/2 
  Installing       : postgresql-server-11.7-2.fc30.x86_64                                               1/2 
warning: /var/lib/pgsql/.bash_profile created as /var/lib/pgsql/.bash_profile.rpmnew
 
  Running scriptlet: postgresql-server-11.7-2.fc30.x86_64                                               1/2 
  Installing       : postgresql-plpython3-11.7-2.fc30.x86_64                                            2/2 
  Running scriptlet: postgresql-plpython3-11.7-2.fc30.x86_64                                            2/2 
  Verifying        : postgresql-plpython3-11.7-2.fc30.x86_64                                            1/2 
  Verifying        : postgresql-server-11.7-2.fc30.x86_64                                               2/2 
 
Installed:
  postgresql-plpython3-11.7-2.fc30.x86_64                postgresql-server-11.7-2.fc30.x86_64               
 
Complete!

While I thought this might fix the problem, it didn’t and raised the following error:

ERROR:  could not open extension control file "/usr/pgsql-11/share/extension/plpython3u.control": No such file or directory

I found the plpython3u.control file on GitHub and put the following plpython3u.control file, after comparing it against the plpgsql.control file, into the /usr/pgsql-11/share/extension directory. At this point, I began wondering why it’s looking in the /usr/pgsql-11/share/extension directory instead of a /usr/plpython3u/share/extension directory (does not exist).

# plpython3u extension
comment = 'PL/Python3U untrusted procedural language'
default_version = '1.0'
module_pathname = '$libdir/plpython3'
relocatable = false
schema = pg_catalog
superuser = true

I retried creating the plpython3u extension:

CREATE EXTENSION plpython3u;

It raised the following error:

ERROR:  extension "plpython3u" has no installation script nor update path for version "1.0"

At this point, I could find no further help on the Internet. I did notice that there were these two *.sql files in the /usr/pgsql-11/share/extension directory:

• plpgsql–1.0.sql
• plpgsql–unpackaged–1.0.sql

I found this plpython3u--1.0.sql file on GitHub:

/* src/pl/plpython/plpython3u--1.0.sql */
 
CREATE FUNCTION plpython3_call_handler() RETURNS language_handler
  LANGUAGE c AS 'MODULE_PATHNAME';
 
CREATE FUNCTION plpython3_inline_handler(internal) RETURNS void
  STRICT LANGUAGE c AS 'MODULE_PATHNAME';
 
CREATE FUNCTION plpython3_validator(oid) RETURNS void
  STRICT LANGUAGE c AS 'MODULE_PATHNAME';
 
CREATE LANGUAGE plpython3u
  HANDLER plpython3_call_handler
  INLINE plpython3_inline_handler
  VALIDATOR plpython3_validator;
 
COMMENT ON LANGUAGE plpython3u IS 'PL/Python3U untrusted procedural language';

I retried creating the plpython3u extension, as a member of the sudoer list and got a new error:

ERROR:  permission denied to create extension "plpython3u"
HINT:  Must be superuser to create this extension.

I retried creating the plpython3u extension as the postgres user, who is the owning user:

CREATE EXTENSION plpython3u;

It raised the following error:

ERROR:  could not access file "$libdir/plpython3": No such file or directory

While I intend to finish this, that’s as far as I got. It appears from some of the things I’ve read I need to recompile or configure items that would destabilize what I have working at the moment. Finishing this will need to wait for me to build another test environment from scratch. If you catch this post and know the remaining steps, I invite you to add them in the comments.

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.

A quick example of installing and running a sample hello.py file with the Flask application with the Command Line Interface (CLI) documentation on the Flask website. The first thing you need to do is install the flask module with the pip3 utility on Fedora Linux (or most other platforms):

pip3 install flask --user student

You should see a successful log like this for student user:

Requirement already satisfied: flask in /usr/local/lib64/python3.7/site-packages (1.1.2)
Collecting student
  Downloading https://files.pythonhosted.org/packages/b5/af/be416c18e4fe63a582e06fb0d47bf059bd0f4f413e5a6cfe893747ebaf79/Student-0.0.1-py3-none-any.whl
Requirement already satisfied: click>=5.1 in /usr/lib/python3.7/site-packages (from flask) (7.1.1)
Requirement already satisfied: itsdangerous>=0.24 in /usr/local/lib/python3.7/site-packages (from flask) (1.1.0)
Requirement already satisfied: Werkzeug>=0.15 in /usr/local/lib/python3.7/site-packages (from flask) (1.0.1)
Requirement already satisfied: Jinja2>=2.10.1 in /usr/lib/python3.7/site-packages (from flask) (2.10.1)
Requirement already satisfied: MarkupSafe>=0.23 in /usr/lib64/python3.7/site-packages (from Jinja2>=2.10.1->flask) (1.1.1)
Installing collected packages: student
Successfully installed student-0.0.1

The smallest footprint hello.py program is:

# Import statement.
from flask import Flask
 
# Application defintion.
app = Flask(__name__)
 
# Basic URI rotuing.
@app.route("/")
 
# Define a hello function.
def hello():
  return "Hello World!"
 
# Run the program.
if __name__ == "__main__":
  app.run()

You can run the program from the command line with two commands:

export FLASK_APP=hello.py
flask run

It will show you the following in the terminal session:

 * Serving Flask app "hello.py"
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

Type the following URL in a local browser:

http://localhost:5000/

Flask will add the following to the console log:

127.0.0.1 - - [26/Oct/2020 00:37:49] "GET / HTTP/1.1" 200 -

You can jazz the hello.py program up with a an argument list, like this modified example based on this comparison article on Python frameworks:

# Import libraries.
from flask import Flask, escape, request
 
# Define the application.
app = Flask(__name__)
 
# Define a base URI route and function.
@app.route('/')
def index():
  return "Index Page"
 
# Define an application URI route and function.
@app.route("/hello")
def hello():
  name = request.args.get("name","Simon")
  return f'Hello {escape(name)}!'
 
# Define an about URI route and function.
@app.route("/about")
def about():
  return "About Page"
 
# Run the file.
if __name__ == "__main__":
  app.run()

It prints 'Hello Simon!' in a web page. If you try to stop your Flask server with a Ctrl+Z instead of a Ctrl+C, the next time you go to start it you will receive an error message like:

OSError: [Errno 98] Address already in use

You’ll need to find the Linux process ID and kill the process with prejudice. You can find the process with the following Linux command:

sudo netstat -nlp | grep 5000
[sudo] password for student:

It prompts you for your sudoer password, and then returns a line like:

tcp        0      0 127.0.0.1:5000          0.0.0.0:*               LISTEN      76802/python3

You kill the process with the following Linux command:

kill -9 76802

As always, I hope this helps those looking for the missing pieces.

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.

If you’ve worked with the Oracle database a while, you probably noticed that some utilities write to stdout for both standard output and what should be standard error (stderr). One of those commands is the tnsping utility.

You can wrap the tnsping command to send the TNS-03505 error to stdout with the following code. I put Bash functions like these in a library.sh script, which I can source when automating tasks.

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

#!/usr/bin/bash
 
tnsping()
{
  if [ ! -z ${1} ]; then
    # Set default return value.
    stdout=`$ORACLE_HOME/bin/tnsping ${1} | tail -1`
 
    # Check stdout to return 0 for success and 1 for failure.
    if [[ `echo ${stdout} | cut -c1-9` = 'TNS-03505' ]]; then
      python -c 'import os, sys; arg = sys.argv[1]; os.write(2,arg + "\n")' "${stdout}"
    else
      echo "${1}"
    fi
  fi
}

You should notice that the script uses a Python call to redirect the error message to standard out (stdout) but you can redirect in Bash shell with the following:

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

#!/usr/bin/bash
 
tnsping()
{
  if [ ! -z ${1} ]; then
    # Set default return value.
    stdout=`$ORACLE_HOME/bin/tnsping ${1} | tail -1`
 
    # Check stdout to return 0 for success and 1 for failure.
    if [[ `echo ${stdout} | cut -c1-9` = 'TNS-03505' ]]; then
      echo ${stdout} 1>&2
    else
      echo "${1}"
    fi
  fi
}

Interactively, we can now test a non-existent service name like wrong with this syntax:

tnsping wrong

It’ll print the standard error to console, like:

TNS-03505: Failed to resolve name

or, you can suppress standard error (stderr) by redirecting it to the traditional black hole, like:

tnsping wrong 2>/dev/null

After redirecting standard error (stderr), you simply receive nothing back. That lets you evaluate in another script whether or not the utility raises an error.

In an automating Bash shell script, you use the source command to put the Bash function in scope, like this:

source library.sh

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