MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL Workbench’ Category

MySQL Workbench Fits

without comments

My students wanted an illustration of where MySQL Workbench fits within the MySQL database. So, I overlaid it in this drawing from my old book on comparative SQL syntax for Oracle and MySQL. Anybody else have a cool alternative illustration?

The idea is the lightening bolt transmits an interactive call and reading a script file submits a call paradigm.

More or less MySQL processes a static query in the panel, like the following Python program process the dynamic query (on lines 71-78) with parameters sent when calling the Python script.

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
#!/usr/bin/python
# ------------------------------------------------------------
#  Name: mysql-query2.py
#  Date: 20 Aug 2019
# ------------------------------------------------------------
#  Purpose:
#  -------
#    The program shows you how to provide agruments, convert
#    from a list to individual variables of the date type.
#
#    You can call the program:
#
#    ./mysql-query3.py 2001-01-01 2003-12-31
#
# ------------------------------------------------------------
 
# Import the library.
import sys
import mysql.connector
from datetime import datetime
from datetime import date
from mysql.connector import errorcode
 
# Capture argument list.
fullCmdArguments = sys.argv
 
# Assignable variables.
start_date = ""
end_date = ""
 
# Assign argument list to variable.
argumentList = fullCmdArguments[1:]
 
#  Check and process argument list.
# ============================================================
#  If there are less than two arguments provide default values.
#  Else enumerate and convert strings to dates.
# ============================================================
if (len(argumentList) < 2):
  # Set a default start date.
  if (isinstance(start_date,str)):
    start_date = date(1980, 1, 1)
 
  # Set the default end date.
  if (isinstance(end_date,str)):
    end_date = datetime.date(datetime.today())
else:
  # Enumerate through the argument list where beginDate precedes endDate as strings.
  try:
    for i, s in enumerate(argumentList):
      if (i == 0):
        start_date = datetime.date(datetime.fromisoformat(s))
      elif (i == 1):
        end_date = datetime.date(datetime.fromisoformat(s))
  except ValueError:
    print("One of the first two arguments is not a valid date (YYYY-MM-DD).")
 
#  Attempt the query.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='studentdb')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Set the query statement.
  query = ("SELECT CASE "
           "         WHEN item_subtitle IS NULL THEN CONCAT('''',item_title,'''') "
           "         ELSE CONCAT('''',item_title,': ',item_subtitle,'''') "
           "         END AS title, "
           "release_date "
           "FROM item "
           "WHERE release_date BETWEEN %s AND %s "
           "ORDER BY item_title")
 
  # Execute cursor.
  cursor.execute(query, (start_date, end_date))
 
  # Display the rows returned by the query.
  for (title, release_date) in cursor:
    print("{}, {:%d-%b-%Y}".format(title, release_date))
 
  # Close cursor.
  cursor.close()
 
# ------------------------------------------------------------
# Handle exception and close connection.
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
else:
  cnx.close()

You could call this type of script from the Linux CLI (Command-Line Interface), like this:

./mysql-query3.py '2003-01-01' '2003-12-31' 2>/dev/null

As always, I hope this helps those looking to understand things.

Written by maclochlainn

June 17th, 2023 at 3:02 pm

MySQL @SQL_MODE

without comments

Installing MySQL Workbench 8 on Windows, we discovered that the default configuration no longer sets ONLY_FULL_GROUP_BY as part of the default SQL_MODE parameter value. While I’ve written a stored function to set the SQL_MODE parameter value for a session, some students didn’t understand that such a call is only valid in the scope of a connection to the database server. They felt the function didn’t work because they didn’t understand the difference between connecting to the MySQL CLI and clicking the lightening bolt in MySQL Workbench.

So, here are the instructions to reset the default SQL_MODE parameter value for Windows. You need to edit the setting in the my.ini file, which is in the C:\ProgramData\MySQL\MySQL Server 8.0 directory. The default installation will have the following:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

You need to change it to the following in an editor with Administrative privileges:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

Then, you need to connect to the services by launching services.msc from the command prompt. In the list of services find MYSQL80 service and restart it. You can verify it by connecting to the MySQL 8.0.* server and running the following SQL query:

SELECT @@SQL_MODE:

That’s how you convert Windows to use only traditional group by behaviors in SQL. As always, I hope this helps those looking for a solution.

Written by maclochlainn

February 13th, 2023 at 12:55 pm

AlmaLinux Libraries

without comments

I discovered a dependency for MySQL Workbench on AlmaLinux 8 installation. I neglected to fully cover it when I documented the installation in a VM of AlmaLinux 9. I go back later and update that entry but for now you need the following dependencies:

proj-6.3.2-4.el8.x86_64.rpm
proj-datumgrid-1.8-6.3.2.4.el8.noarch.rpm
proj-devel-6.3.2-4.el8.x86_64.rpm

Install like this:

sudo dnf install -y *.rpm

Log file:

Last metadata expiration check: 3:01:53 ago on Fri 10 Feb 2023 03:37:49 AM UTC.
Dependencies resolved.
==========================================================================================
 Package                Architecture   Version                 Repository            Size
==========================================================================================
Installing:
 proj                   x86_64         6.3.2-4.el8             @commandline         2.0 M
 proj-datumgrid         noarch         1.8-6.3.2.4.el8         @commandline         5.4 M
 proj-devel             x86_64         6.3.2-4.el8             @commandline          89 k
 
Transaction Summary
==========================================================================================
Install  3 Packages
 
Total size: 7.5 M
Installed size: 17 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                  1/1 
  Installing       : proj-datumgrid-1.8-6.3.2.4.el8.noarch                            1/3 
  Installing       : proj-6.3.2-4.el8.x86_64                                          2/3 
  Installing       : proj-devel-6.3.2-4.el8.x86_64                                    3/3 
  Running scriptlet: proj-devel-6.3.2-4.el8.x86_64                                    3/3 
  Verifying        : proj-6.3.2-4.el8.x86_64                                          1/3 
  Verifying        : proj-datumgrid-1.8-6.3.2.4.el8.noarch                            2/3 
  Verifying        : proj-devel-6.3.2-4.el8.x86_64                                    3/3 
 
Installed:
  proj-6.3.2-4.el8.x86_64                 proj-datumgrid-1.8-6.3.2.4.el8.noarch          
  proj-devel-6.3.2-4.el8.x86_64          
 
Complete!

A quick update while installing AlmaLinux for AWS.

Written by maclochlainn

February 9th, 2023 at 11:47 pm

AlmaLinux MySQL Workbench

without comments

AlmaLinux doesn’t natively support MySQL Workbench but these notes will help you install it. The great news is that MySQL Workbench works perfectly once you’ve installed all the dependent libraries. It’ll look like the following:

Disclaimer of sorts:

AlmaLinux is an open-source, community-driven project that intends to fill the gap left by the demise of the CentOS stable release. AlmaLinux is a 1:1 binary compatible fork of RHEL® 9 and it is built by the AlmaLinux OS Foundation as a standalone, completely free OS. The AlmaLinux OS Foundation will support future RHEL® releases by updating AlmaLinux. Ongoing development efforts are governed by the members of the community.

You can download MySQL Workbench from the following website:

https://dev.mysql.com/downloads/workbench

When you open this page, select the Red Hat Enterprise Linux 9 / Oracle Linux 9 (x86, 64-bit), RPM Package from the dropdown menu. Then, click the Download button. You may be prompted for your credentials or to create new credentials, but you can skip that by clicking on the No thanks, just start my download link.

When the download completes, open a terminal session as the student user. Navigate to the Downloads directory with the following command:

cd $HOME/Downloads

List the files in the $HOME/Downloads directory and you should see:

mysql-workbench-community-8.0.31-1.el9.x86_64.rpm

As the sudoer user or root, run the following command (naturally, exclude sudo if you’re the root user):

sudo dnf install -y mysql-workbench-community-8.0.31-1.el9.x86_64.rpm

It will most likely fail with an error message like this:

Last metadata expiration check: 2:50:04 ago on Thu 17 Nov 2022 09:33:15 AM EST.
Error: 
 Problem: conflicting requests
  - nothing provides gtkmm30-devel needed by mysql-workbench-community-8.0.31-1.el9.src
  - nothing provides libzip-devel needed by mysql-workbench-community-8.0.31-1.el9.src
  - nothing provides proj-devel needed by mysql-workbench-community-8.0.31-1.el9.src
  - nothing provides swig >= 3.0 needed by mysql-workbench-community-8.0.31-1.el9.src
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)

AlmaLinux doesn’t install these prerequisite packages. You’ll need to resolve these dependencies by installing them in the right order and groups before you can run the MySQL Workbench packages.

You can discover missing packages at the pkgs.org website. You need to resolve all four prerequisites before installing MySQL Workbench.

  1. Let’s start with the gtkmm30-devel package, which has eight separate dependencies. Assuming you’re still in your $HOME/Downloads directory, you can run the following command to get the gtkmm30-devel for AlmaLinux 9:

    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/gtkmm30-devel-3.24.5-1.el9.x86_64.rpm

    It downloads the following package:

    gtkmm30-devel-3.24.5-1.el9.x86_64.rpm

    If you attempt to run it, the gtkmm30-devel package raises the following errors:

    sudo dnf install -y gtkmm30-devel-3.24.5-1.el9.x86_64.rpm
    Last metadata expiration check: 0:41:13 ago on Thu 17 Nov 2022 02:39:59 PM EST.
    Error: 
     Problem: conflicting requests
      - nothing provides pkgconfig(atkmm-1.6) >= 2.24.2 needed by gtkmm30-devel-3.24.5-1.el9.x86_64
      - nothing provides pkgconfig(cairomm-1.0) >= 1.12.0 needed by gtkmm30-devel-3.24.5-1.el9.x86_64
      - nothing provides pkgconfig(giomm-2.4) >= 2.54.0 needed by gtkmm30-devel-3.24.5-1.el9.x86_64
      - nothing provides pkgconfig(pangomm-1.4) >= 1.12.0 needed by gtkmm30-devel-3.24.5-1.el9.x86_64
    (try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)

    While you only get four errors, there are more packages required. You need to use the wget utility to download these packages. I would recommend you create a temporary gtkmm30 subdirectory inside your $HOME/Downloads directory and change to that directory before downloading these files.

    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/atkmm-devel-2.28.2-2.el9.x86_64.rpm
    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/cairomm-devel-1.14.2-10.el9.x86_64.rpm
    wget https://repo.almalinux.org/almalinux/9/AppStream/x86_64/os/Packages/gdk-pixbuf2-devel-2.42.6-2.el9.x86_64.rpm
    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/glibmm24-devel-2.66.1-1.el9.x86_64.rpm
    wget https://repo.almalinux.org/almalinux/9/AppStream/x86_64/os/Packages/gtk3-devel-3.24.31-2.el9.x86_64.rpm
    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/pangomm-devel-2.46.1-1.el9.x86_64.rpm
    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/libsigc++20-devel-2.10.7-2.el9.x86_64.rpm

    You need to run these as a set of prerequisites, so from your gtkmm30 subdirectory use the following dnf command as the sudoer user:

    sudo dnf install -y *.rpm

    The log file for this is:

    Now return to your $HOME/Downloads directory and run the following command. You’ll notice that it installs and upgrades many more packages than you might expect.

    sudo dnf install -y gtkmm30-devel-3.24.5-1.el9.x86_64.rpm

    The log file for this is:

    All that done and you’ve only got the first of four dependencies resovled.

  2. Next, start with the libzip-devel package, which has a couple dependencies. Assuming you’re still in your $HOME/Downloads directory, you can run the following command to get the libzip-devel and its prerequisite packages for AlmaLinux 9:

    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/libzip-devel-1.7.3-7.el9.x86_64.rpm
    wget https://repo.almalinux.org/almalinux/9/AppStream/x86_64/os/Packages/cmake-filesystem-3.20.2-7.el9.x86_64.rpm
    wget https://repo.almalinux.org/almalinux/9/AppStream/x86_64/os/Packages/libzip-1.7.3-7.el9.x86_64.rpm

    You can run the prerequisites with the following command:

    sudo dnf install -y cmake*.rpm libzip-1.7.3*.rpm

    Now, you can run the libzip-devel package with this syntax:

    sudo dnf install -y libzip-devel*.rpm

    Having resolved the two dependencies, you can install the compression development kit. This completes the second step.

  3. Next, you need to apply the proj_devel package for AlmaLinux 9:

    wget https://download-ib01.fedoraproject.org/pub/epel/9/Everything/x86_64/Packages/p/proj-devel-8.2.0-1.el9.x86_64.rpm

    Now, you can run the proj-devel package with this syntax:

    sudo dnf install -y proj-devel-8.2.0-1.el9.x86_64.rpm
  4. Next, you need to apply the swig packages for AlmaLinux 9:

    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/swig-4.0.2-8.el9.x86_64.rpm
    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/swig-doc-4.0.2-8.el9.noarch.rpm
    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/swig-gdb-4.0.2-8.el9.x86_64.rpm
    sudo dnf install -y swig*.rpm
  5. Next, you need to apply the mysql-community-workbench packages for AlmaLinux 9. The download instructions where provided above. You apply the packages with the following command.

    sudo dnf install -y mysql-workbench-community-8.0.31-1.el9.x86_64.rpm

After applying the dependent and mysql-community-workbench packages, you can launch MySQL Workbench by clicking the Activities symbol in the upper left hand corner. That displays the nine-dots for Show Applications icon. Click the Show Applications icon and choose the MySQL Workbench icon to launch MySQL Workbench.

You’ll be prompted with the following dialog. Just click Don’t show this message again checkbox and the OK button to launch MySQL Workbench.

As always, I hope this helps those looking to solve a real problem.

Written by maclochlainn

November 20th, 2022 at 11:31 pm

MySQL Workbench Keys

without comments

As I teach students how to create tables in MySQL Workbench, it’s always important to review the meaning of the checkbox keys. Then, I need to remind them that every table requires a natural key from our prior discussion on normalization. I explain that a natural key is a compound candidate key (made up of two or more column values), and that it naturally defines uniqueness for each row in a table.

Then, we discuss surrogate keys, which are typically ID column keys. I explain that surrogate keys are driven by sequences in the database. While a number of databases disclose the name of sequences, MySQL treats the sequence as an attribute of the table. In Object-Oriented Analysis and Design (OOAD), that makes the sequence a member of the table by composition rather than aggregation. Surrogate keys are also unique in the table but should never be used to determine uniqueness like the natural key. Surrogate keys are also candidate keys, like a VIN number uniquely identifies a vehicle.

In a well designed table you always have two candidate keys: One describes the unique row and the other assigns a number to it. While you can perform joins by using either candidate key, you always should use the surrogate key for joins statements. This means you elect, or choose, the surrogate candidate key as the primary key. Then, you build a unique index for the natural key, which lets you query any unique row with human decipherable words.

The column attribute table for MySQL Workbench is:

Key Meaning
PK Designates a primary key column.
NN Designates a not-null column constraint.
UQ Designates a column contains a unique value for every row.
BIN Designates a VARCHAR data type column so that its values are stored in a case-sensitive fashion. You can’t apply this constraint to other data types.
UN Designates a column contains an unsigned numeric data type. The possible values are 0 to the maximum number of the data type, like integer, float, or double. The value 0 isn’t possible when you also select the PK and AI check boxes, which ensures the column automatically increments to the maximum value of the column.
ZF Designates a zero fill populates zeros in front of any number data type until all space is consumed, which acts like a left pad function with zeros.
AI Designates AUTO_INCREMENT and should only be checked for a surrogate primary key value.

All surrogate key columns should check the PK, NN, UN, and AI checkboxes. The default behavior checks only the PK and NN checkboxes and leaves the UN and AI boxes unchecked. You should also click the UN checkbox with the AI checkbox for all surrogate key columns. The AI checkbox enables AUTO_INCREMENT behavior. The UN checkbox ensure you have the maximum number of integers before you would migrate the table to a double precision number. More or less, this is what I wrote in MySQL Workbench Data Modeling & Development as the primary product guide in 2013, and what you find in the MySQL Workbench Manual 8.1.10.2 Columns Tab section.

Active tables grow quickly and using a signed int means you run out of rows more quickly. This is an important design consideration because using a unsigned int adds a maintenance task later. The maintenance task will require changing the data type of all dependent foreign key columns before changing the primary key column’s data type. Assuming you’re design uses referential integrity constraints, implemented as a foreign keys, you will need to:

  • Remove any foreign key constraints before changing the referenced primary key and dependent foreign key column’s data types.
  • Change the primary and foreign key column’s data types.
  • Add back foreign key constraints after changing the referenced primary key and dependent foreign key column’s data types.

While fixing a less optimal design is a relatively simple scripting exercise for most data engineers, you can avoid this maintenance task. Implement all surrogate primary key columns and foreign key columns with the signed int as their initial data type.

The following small ERD displays a multi-language lookup table, which is preferable to a monolinquistic enum data type.:

A design uses a lookup table when there are known lists of selections to make. There are known lists that occur in most if not all business applications. Maintaining that list of values is an application setup task and requires the development team to build an entry and update form to input and maintain the lists.

While some MySQL examples demonstrate these types of lists by using the MySQL enum data type. However, the MySQL enum type doesn’t support multilingual implementations, isn’t readily portable to other relational database, and has a number of limitations.

A lookup table is the better solution to using an enum data type. It typically follows this pattern:

  • Identify the target table and column where a list is useful. Use the table_name and column_name columns as a super key to identify the location where the list belongs.
  • Identify a unique type identifier for the list. Store the unique type value in the type column of the lookup table.
  • Use a lang column to enable multilingual lists.

The combination of the table_name, column_name, type, and lang let you identify unique sets. You can find a monolingual implementation in these two older blog posts:

The column view of the lookup table shows the appropriate design checkboxes:

While most foreign keys use copies of surrogate keys, there are instances when you copy the natural key value from another table rather than the surrogate key. This is done when your application will frequently query the dependent lookup table without a join to the lang table, which means the foreign key value should be a human friendly foreign key value that works as a super key.

A super key is a column or set of columns that uniquely identifies a rows in the scope of a relation. For this example, the lang column identifies rows that belong to a language in a multilingual data model. Belonging to a language is the relation between the lookup and language table. It is also a key when filtering rows with a specific lang value from the lookup table.

You navigate to the foreign key tab to create a lookup_fk foreign key constraint, like:

With this type of foreign key constraint, you copy the lang value from the language table when inserting the lookup table values. Then, your HTML forms can use the lookup table’s meaning column in any of the supported languages, like:

SELECT lookup_id
,      type
,      meaning
FROM   lookup
WHERE  table_name = 'some_table_name'
AND    column_name = 'some_column_name'
AND    lang = 'some_lang_name';

The type column value isn’t used in the WHERE clause to filter the data set because it is unique within the relation of the table_name, column_name, and lang column values. It is always non-unique when you exclude the lang column value, and potentially non-unique for another combination of the table_name and column_name column values.

As a rule, most foreign key references are to the lookup table’s surrogate primary key because the meaning column’s value is too long to copy into the referencing table or subject to change in the base or translated languages. Small values, with intrinsic meaning, are stored in a code column in many implementations, like the lang column. Those typically follow the same implementation rule as the lang column and are copied into the referencing table.

If I’ve left questions, let me know. Other wise, I hope this helps qualify a best design practice.

Written by maclochlainn

October 16th, 2022 at 5:53 pm

SQL Handling Nulls

without comments

Interesting questions always come via my students. For example, “Why does the selective aggregation sample return null values as totals from the SUM() function in MySQL?”

First, here’s the code to build the sample table for the problem:

DROP TABLE IF EXISTS transaction;
CREATE TABLE transaction
( transaction_id      int unsigned primary key auto_increment
, transaction_date    date
, transaction_amount  double );
 
INSERT INTO transaction
( transaction_date, transaction_amount )
VALUES
 ('2021-01-10', 56)
,('2021-02-14',23.02)
,('2021-03-31',31.06)
,('2021-01-01',.25)
,('2020-01-02', 52)
,('2020-02-08',22.02)
,('2020-03-26',32.06)
,('2020-01-12',.75);;

Now, here’s the selective aggregation query:

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
            END) AS "Jan"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN transaction_amount
           END) AS "Feb"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN transaction_amount
           END) AS "Mar"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) IN (1,2,3) THEN transaction_amount
           END) AS "1FQ"
FROM     transaction
WHERE    transaction_date BETWEEN '2020-01-15' AND '2021-03-15'
GROUP BY EXTRACT(YEAR FROM transaction_date)
ORDER BY 1;

It produces the following correct results (at least in response to the query above):

+------+-------+-------+-------+-------+
| Year | Jan   | Feb   | Mar   | 1FQ   |
+------+-------+-------+-------+-------+
| 2020 |  NULL | 22.02 | 32.06 | 54.08 |
| 2021 | 56.25 | 23.02 |  NULL | 79.27 |
+------+-------+-------+-------+-------+
2 rows in set (0.02 sec)

Why do you get null values for January 2020 and March 2021? That’s because the query returns null values when the conditions in the SELECT-list aren’t met for a row return. This happens:

  • When a row is returned for the month of February or March a null is returned in the January column.
  • When a row is returned for the month of January or March a null is returned in the February column.
  • When a row is returned for the month of January or February a null is returned in the March column.

That means there needs to be an ELSE clause in each of the CASE statements that sets the return value to zero. For example, the following query includes the ELSE clause and some nice formatting tricks:

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        CONCAT('$',LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
             ELSE 0
            END),2),8,' ')) AS "Jan"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "Feb"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "Mar"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) IN (1,2,3) THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "1FQ"
FROM     transaction
WHERE    transaction_date BETWEEN '2020-01-15' AND '2021-03-15'
GROUP BY EXTRACT(YEAR FROM transaction_date)
ORDER BY 1;

It returns:

+------+-----------+----------+----------+----------+
| Year | Jan       | Feb      | Mar      | 1FQ      |
+------+-----------+----------+----------+----------+
| 2020 | $    0.00 |    22.02 |    32.06 |    54.08 |
| 2021 | $   56.25 |    23.02 |     0.00 |    79.27 |
+------+-----------+----------+----------+----------+
2 rows in set (0.01 sec)

As always, I hope this helps answer a question that somebody is trying to sort out.

Written by maclochlainn

June 23rd, 2021 at 12:23 pm

Customer ERD

without comments

Now that we’ve migrated to MySQL for our core database course, I’m building MySQL Workbench analysis problems. We start with a lecture trying to flush out a simple address, and then ask them to figure out how to link it to a customer table.

Designing it, I emphasized how it resolves the issue of a city occurring in multiple counties and states, like Fremont. Fremont occurs 17 times in the US and once in Haiti:

Naturally, I left two other design issues in the problem. I’m working through it for the first time with classes tomorrow. I hope it works well. Any comments?

Written by maclochlainn

May 9th, 2021 at 11:46 pm

Put MySQL in PATH

without comments

After downloading and installing MySQL 8.0.24 yesterday, I opened a command shell. In the command shell, I could access the MySQL Shell (mysqlsh.exe) but not the MySQL Client (mysql.exe). Typing in the following:

C:\WINDOWS\system32>mysql

It returned:

'mysql' is not recognized as an internal or external command,
operable program or batch file.

The MySQL Client (mysql.exe) was installed because MySQL Workbench relies on it. However, the MySQL Microsoft Software Installer (MSI) does not put the mysql.exe file’s directory in the common Windows %PATH% environment variable. You can find the required %PATH% directory variable by opening the File Manager and searching for the mysql.exe file.

You should return several directories and programs but the directory you want is:

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

You can test it by using the SET command in the Microsoft Operating System, like this:

SET PATH=C:\Program Files\MySQL\MySQL Server 8.0\bin;%PATH%

You can now call the mysql.exe program in your current shell session with the following syntax:

mysql -uroot -p

You will be prompted for the password and then connected to the database as follows:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.24 MySQL Community Server - GPL
 
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

Unfortunately, the SET command only sets the %PATH% environment variable in the current session. You can set the system %PATH% environment variable globally by following these steps:

  1. In Search, search for and then select: System (Control Panel)
  2. In Settings dialog enter “Environment” in the search box and it will display:


  3. Chose “Edit the system environment variables” option. You will see the following “System Properties” dialog:
  4. Click the “Environment Variable” button to display the “Environment Variables” dialog. Click on the Path system variable before clicking the Edit button beneath.

  5. Click the “New” button and enter “C:\Program Files\MySQL\MySQL Server 8.0\bin, and click the “OK” button. The next time you query the computer system’s %PATH% environment variable, it will show you the list of path locations that the operating system looks at for command files. It’s actually stored as a semicolon-delimited list in Windows 10 (and, as a colon-delimited list in Linux or Unix).

  6. The next time you open a command shell, the %PATH% environment variable will find the mysql.exe program. As always, I hope these instructions help the reader.

Written by maclochlainn

April 23rd, 2021 at 12:51 am

MySQL macOS Docker

without comments

While you can download MySQL as a DMG package, a number of users would prefer to install it as a Docker instance. You won’t find the macOS downloads on the same web site as other downloads. You can use the following macOS download site.

After installing Docker on your macOS, you can pull a copy of the current MySQL Server with the following command:

docker pull mysql/mysql-server

You should create a mysql directory inside your ~/Documents directory with this command:

mkdir ~/Documents/mysql

Then, you should use the cd command to change into the ~/Documents/mysql directory and run this command:

pwd

It should return the following directory:

/Users/<user_name>/Documents/mysql

Use the /Users/<user_name>/Documents/mysql as the in this command:

docker run --name=mysql1 --volume=<path_to_folder>:/var/lib/mysql -p 33060:3306/tcp -d mysql/mysql-server

The --name option value is mysql1 and it becomes the container value. Docker mounts the column in the ~/Documents/mysql folder. All data from the Docker container under the /var/lib/mysql directory will persist in this directory. This directory will still contain the database when the container is shut down.

The docker run command maps the localhost’s 33060 port to the 3306 port on the Docker container. You will use the 33060 port to connect to the Docker instance of MySQL. It raises a dialog box asking for permission to access the directory. You need to allow Docker to write to the ~/Documents/mysql directory.

You can verify that the Docker container is running with the following command:

docker ps

It should return:

CONTAINER ID   IMAGE                COMMAND                  CREATED         STATUS                   PORTS                                      NAMES
142b5c491cd8   mysql/mysql-server   "/entrypoint.sh mysq…"   7 minutes ago   Up 6 minutes (healthy)   33060-33061/tcp, 0.0.0.0:33060->3306/tcp   mysql1

You can get the MySQL generated root password with this Docker command:

docker logs mysql1 2>&1 | grep GENERATED

It returns something like the following:

[Entrypoint] GENERATED ROOT PASSWORD: vop#3GNYqK3nC@S@N3haf3nox5E

Use the following Docker command to connect to the Docker container:

docker exec -it mysql1 /bin/bash

It launches a Bash shell inside the Docker container:

bash-4.2#

Start the mysql Command-Line Interface (CLI):

mysql -uroot -p

You are then prompted for a password:

Enter password:

After successfully entering the password, you’ll see the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 8.0.22
 
Copyright (c) 2000, 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 '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

Unless you want to remember that hugely complex root password, you should consider changing it to something simple like, 'cangetin' with the following command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'cangetin';

Next, you should check for the installed databases with this command:

show databases;

It will return:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

Exiting mysql, you can see the contents of the root user’s directory with this list command:

ls -al

It should return:

total 84
drwxr-xr-x   1 root root 4096 Jan 12 03:41 .
drwxr-xr-x   1 root root 4096 Jan 12 03:41 ..
-rwxr-xr-x   1 root root    0 Jan 12 03:41 .dockerenv
lrwxrwxrwx   1 root root    7 Oct 12 22:06 bin -> usr/bin
dr-xr-xr-x   2 root root 4096 Apr 11  2018 boot
drwxr-xr-x   5 root root  340 Jan 12 03:41 dev
drwxr-xr-x   2 root root 4096 Oct 19 05:47 docker-entrypoint-initdb.d
-rwxr-xr-x   1 root root 7496 Oct 19 05:37 entrypoint.sh
drwxr-xr-x   1 root root 4096 Jan 12 03:41 etc
-rw-r--r--   1 root root   86 Jan 12 03:41 healthcheck.cnf
-rwxr-xr-x   1 root root 1073 Oct 19 05:37 healthcheck.sh
drwxr-xr-x   2 root root 4096 Apr 11  2018 home
lrwxrwxrwx   1 root root    7 Oct 12 22:06 lib -> usr/lib
lrwxrwxrwx   1 root root    9 Oct 12 22:06 lib64 -> usr/lib64
drwxr-xr-x   2 root root 4096 Apr 11  2018 media
drwxr-xr-x   2 root root 4096 Apr 11  2018 mnt
-rw-r--r--   1 root root    0 Jan 12 03:41 mysql-init-complete
drwxr-xr-x   2 root root 4096 Apr 11  2018 opt
dr-xr-xr-x 127 root root    0 Jan 12 03:41 proc
dr-xr-x---   1 root root 4096 Jan 12 04:21 root
drwxr-xr-x   1 root root 4096 Oct 19 05:47 run
lrwxrwxrwx   1 root root    8 Oct 12 22:06 sbin -> usr/sbin
drwxr-xr-x   2 root root 4096 Apr 11  2018 srv
dr-xr-xr-x  13 root root    0 Jan 12 03:41 sys
drwxrwxrwt   1 root root 4096 Jan 12 03:41 tmp
drwxr-xr-x   1 root root 4096 Oct 12 22:06 usr
drwxr-xr-x   1 root root 4096 Oct 12 22:06 var

At this point, you have to make a choice about how you will access the MySQL database. You have a couple options:

  • Create an individual student user that can access the MySQL-Server as a micro-service, which would only be a MySQL user connecting through MySQL workbench. At least, that’s the only connection option unless you likewise install the mysql client on your host macOS. The mysql client lets you connect from the host operating system through the Command-Line Interface (CLI).
  • Create a local student user account inside the Docker container. It will have access to the container file system and mimic the behavior of a non-root user on a server.

Let’s create both for this demonstration. Reconnect as the root user and issue the following two commands:

CREATE USER 'student'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'student';
CREATE USER 'student'@'%.%.%.%'   IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'student';

The first version of the student user lets you access the database from inside the Docker container. The second version of the student user lets you access the database from MySQL Workbench deployed on your base macOS.

You can add a sakila database and grant all privileges to the student user with the following command as the root user:

CREATE DATABASE sakila;
GRANT ALL ON sakila.* TO 'student'@'localhost';
GRANT ALL ON sakila.* TO 'student'@'%.%.%.%';

You need to get the sakila database from the Internet within the Docker container. Exit the mysql client with the following command:

quit;

As the root user, install the wget and tar Linux utilities with this command:

yum install -y wget tar

As the student user, you can use the wget command to grab a copy of the sakila database and store the database locally. Use the cd command to get to your ${HOME} directory, like:

cd

Use this syntax to get a copy of the sakila database:

wget http://downloads.mysql.com/docs/sakila-db.tar.gz

Use the ls command to verify the download, then run the following set of Linux commands from the Linux CLI:

tar -xzf sakila-db.tar.gz
cd sakila-db

Run the following two commands from the sakila-db directory:

mysql -ustudent -p < sakila-schema.sql
mysql -ustudent -p < sakila-data.sql

or, you can connect as the student user to the MySQL client and run them there:

source sakila-schema.sql
source sakila-data.sql

You create a non-root student user for the Docker container from the macOS host opearting system. Which means you need to quit; the mysql client, and exit the root user’s session with the Docker container.

At the terminal in your macOS, issue the following Docker command to create a student account in the mysql1 container:

docker exec mysql1 bash -c "useradd -u 501 -g mysql -G users \
>      -d /home/student -s /bin/bash -c "Student" -n student"

Now, you can connect as the student user to the mysql1 container, with the following Docker command:

docker exec -it --user student mysql1 bash

The first time you connect, you will be a the / (root) directory. Use the following cd command to go to the student user’s home directory:

cd

Then, type the following command to set the student user’s home directory as the default. You need to use this command because vim isn’t installed in the default Docker container, which would let you interactively edit files. It appends the necessary Bash shell command to the end of the .bashrc file.

echo 'cd ${HOME}' >> .bashrc

With this change, the student user will always be available form its home directory next time you connect to the mysql1 container. You can use scp to move files into the student user’s home (/home/student) directory. However, you can create a quick test.sql file like this:

echo "select user();" > test.sql

Connect to the mysql CLI with as the student user:

mysql -ustudent -p

Call your test.sql file from the Linux CLI, like:

mysql -ustudent -p < test.sql

or, you can run the test.sql program as follows form the MySQL command-line:

source test.sql

It will return:

+-------------------+
| user()            |
+-------------------+
| student@localhost |
+-------------------+
1 row in set (0.00 sec)

That’s the basic setup of the Docker MySQL Container on the macOS. You can do much more once you’ve configured it like this. For example, you can add vim to your library repository as the root user with the following command:

yum install -y vim

It just takes a minute or a bit more. Adding vim opens up so much flexibility for you inside the Docker container, it’s impossible for me to resist. 😉

Written by maclochlainn

January 11th, 2021 at 10:20 pm

MySQL sakila Database

without comments

While I thought my instructions were clear, it appears there should have been more in my examples for using the MySQL MSI. A key thing that happened is that students opted not to install:

Samples and Examples 8.0.22

Unfortunately, they may not have read the Preface of Alan Beaulieu’s Learning SQL, 3rd Edition where he explains how to manually download the files from the MySQL web site. Here are those, very clear, instructions (pg. XV) with my additions in italics for the MySQL Shell:

First, you will need to launch the mysql command-line client or the mysqlsh command-line shell, and provide a password, and then perform the following steps:

  1. Go to https://dev.mysql.com/doc/index-other.html and download the files for the “sakila database” under the Example Database section.
  2. Put the files in the local directory such as C:\temp\sakila-db (used for the next two steps, but overwrite with your directory path).
  3. Type

    source c:\temp\sakila-db\sakila-schema.sql

    and press enter.

  4. Type

    source c:\temp\sakila-db\sakila-data.sql

    and press enter.

These instructions let you create the sakila database without rerunning the MSI to add a product. Naturally, you can avoid these steps by using the GUI approach provided in the MySQL MSI file.

As always, I hope this helps those looking for how to solve problems.

Written by maclochlainn

January 9th, 2021 at 11:22 am