MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL Data Architect’ tag

MySQL & macOS Silicon

without comments

It’s a problem for my students who purchased the new Apple hardware that uses Apple Silicon because they can’t install a Docker MySQL instance. However, there is Homebrew formula that works on macOS Big Sur and the new Apple silicon. It supports:

  • Intel Silicon: macOS Big Sur, Catalina, and Mojave
  • Apple Silicon: macOS Big Sur

The Homebrew Formula does have conflicts that you may need to avoid. It is a solution for those with the new Apple silicon.

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

Written by maclochlainn

April 3rd, 2021 at 10:15 am

Express.js & MySQL

without comments

Sometimes, you just half to chuckle. A couple folks felt that I didn’t give enough information in my post showing how to configure a small Node.js application that could access a MySQL database. Specifically, they wanted me to explain the following:

  1. Configure your Express.js and MySQL development in a single Node.js application.
  2. How to convert the list of RowDataPacket objects as elements of data, which is really just simple JavaScript knowledge.
  3. How to bind variables into the query.

Like the other blog post, this one assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this earlier blog post.

Before you write the Node.js applicaiton, you need to setup a db developer directory. A global install of Node.js means you need to create a node_modules symbolic link to the /usr/local/lib/node_modules directory in the db directory (in Linux). You can use the following Linux command from within the db directory to create the appropriate symbolic link:

ln -s /usr/local/lib/node_modules `pwd`/node_modules

or, assuming you have a /home/some_user/db directory

ln -s /usr/local/lib/node_modules /home/some_user/node_modules

After creating the node_modules symbolic link, you need to run the following two npm commands. Please note that second command holds the secret-sauce for generating a package.json file that supports Express.js and the MySQL driver:

npm init --y
sudo npm install --save express mysql

Then, you need to replace the package.json file with the contents of the package-lock.json file from your last npm command.

Here’s a small sample program that uses Express.js, converts the RowDataPackets collection, and binds local variables into the query:

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
// Require libraries.
const express = require('express') 
const mysql = require('mysql')
 
// Create a mysql connection. 
const connection = mysql.createConnection({ 
  host: 'localhost', 
  user: 'student', 
  password: 'student', 
  database: 'studentdb' 
}) 
 
// Declare two local variables.
const start_date = '2001-01-01' 
const end_date = '2003-12-31' 
 
// Connect and display results in the console log.
connection.connect((err) => { 
if (err) 
  throw err 
else { 
  console.log('Connected to MySQL Server!\n') 
  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" 
                  ,[start_date, end_date], function (err, result) { 
    if (err) 
      throw err 
    else { 
      // Prints the index value in the RowDataPacket. 
      for(let element in result) { 
        console.log(result[element].item_title + ', ' + result[element].release_date) 
      } 
      console.log('') 
      console.log('Press Ctrl-C to terminate ...') 
    } 
  }) 
} 
})

Line 28 shows two question marks. They act as placeholders for binding variables. Then, on line 30 you see a collection of the start_date and end_date local variables, which is the second argument to the query() function.

Rather than define individual variables, you can pass them as a collection directly. For example, you replace lines 14 and 15 with this single line:

14
const dates = ['2001-01-01','2003-12-31']

Then, you can pass dates as the second argument to the query() function, like this:

30
                  ,dates, function (err, result) {

Lines 35 and 36 show you how to convert a collection of RowDataPacket objects into elements of data. The for loop assigns the index value to the element variable, which lets you address a single RowDataPacket object instance. The dot (“.“) notation lets you use the name in a name-value pair to reference its value.

It displays the following:

Connected to MySQL Server! 
 
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 
 
Press Ctrl-C to terminate ...

Naturally, I’ll get around to writing something up that shows how to leverage MySQL data into a dynamic form with Handlebars at some point in the near future.

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

Written by maclochlainn

July 28th, 2020 at 1:48 pm

MySQL Workbench on Fedora

with 7 comments

The early release of Fedora 20 disallowed installation of MySQL Workbench but the current version allows it. Almost like Tom Cruise’s Edge of Tomorrow without the drama. All you need to do is follow my earlier instructions for installing MySQL on Fedora 20. I’d check your kernel to know whether it’s supported. You can check that with this command:

<shell> uname -r

My Fedora is at the following version:

3.14.8-200.fc20.x86_64

Then, you can install MySQL Workbench with yum, like this:

<shell> sudo yum install mysql-workbench

It generates the following log file, and if you have Oracle 11g XE installed you can ignore the mime-type error:

Loaded plugins: langpacks, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package mysql-workbench-community.x86_64 0:6.1.7-1.fc20 will be installed
--> Processing Dependency: libzip.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libvsqlitepp.so.3()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libtinyxml.so.0()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: liblua-5.1.so()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libgtkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libgdkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libctemplate.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Running transaction check
---> Package compat-lua-libs.x86_64 0:5.1.5-1.fc20 will be installed
---> Package ctemplate.x86_64 0:2.2-5.fc20 will be installed
---> Package gtkmm24.x86_64 0:2.24.4-2.fc20 will be installed
---> Package libzip.x86_64 0:0.11.2-1.fc20 will be installed
---> Package tinyxml.x86_64 0:2.6.2-4.fc20 will be installed
---> Package vsqlite++.x86_64 0:0.3.13-3.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package                    Arch    Version        Repository              Size
================================================================================
Installing:
 mysql-workbench-community  x86_64  6.1.7-1.fc20   mysql-tools-community   24 M
Installing for dependencies:
 compat-lua-libs            x86_64  5.1.5-1.fc20   updates                158 k
 ctemplate                  x86_64  2.2-5.fc20     fedora                 174 k
 gtkmm24                    x86_64  2.24.4-2.fc20  fedora                 748 k
 libzip                     x86_64  0.11.2-1.fc20  updates                 59 k
 tinyxml                    x86_64  2.6.2-4.fc20   updates                 49 k
 vsqlite++                  x86_64  0.3.13-3.fc20  updates                 58 k
 
Transaction Summary
================================================================================
Install  1 Package (+6 Dependent packages)
 
Total download size: 26 M
Installed size: 119 M
Is this ok [y/d/N]: y
Downloading packages:
(1/7): compat-lua-libs-5.1.5-1.fc20.x86_64.rpm              | 158 kB  00:01     
(2/7): ctemplate-2.2-5.fc20.x86_64.rpm                      | 174 kB  00:01     
(3/7): tinyxml-2.6.2-4.fc20.x86_64.rpm                      |  49 kB  00:00     
(4/7): gtkmm24-2.24.4-2.fc20.x86_64.rpm                     | 748 kB  00:01     
(5/7): vsqlite++-0.3.13-3.fc20.x86_64.rpm                   |  58 kB  00:00     
(6/7): libzip-0.11.2-1.fc20.x86_64.rpm                      |  59 kB  00:02     
(7/7): mysql-workbench-community-6.1.7-1.fc20.x86_64.rpm    |  24 MB  00:08     
--------------------------------------------------------------------------------
Total                                              2.9 MB/s |  26 MB  00:08     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : gtkmm24-2.24.4-2.fc20.x86_64                                 1/7 
  Installing : libzip-0.11.2-1.fc20.x86_64                                  2/7 
  Installing : vsqlite++-0.3.13-3.fc20.x86_64                               3/7 
  Installing : ctemplate-2.2-5.fc20.x86_64                                  4/7 
  Installing : compat-lua-libs-5.1.5-1.fc20.x86_64                          5/7 
  Installing : tinyxml-2.6.2-4.fc20.x86_64                                  6/7 
  Installing : mysql-workbench-community-6.1.7-1.fc20.x86_64                7/7 
Error in file "/usr/share/applications/oraclexe-startdb.desktop": "Application/database" is an invalid MIME type ("Application" is an unregistered media type)
  Verifying  : tinyxml-2.6.2-4.fc20.x86_64                                  1/7 
  Verifying  : compat-lua-libs-5.1.5-1.fc20.x86_64                          2/7 
  Verifying  : ctemplate-2.2-5.fc20.x86_64                                  3/7 
  Verifying  : vsqlite++-0.3.13-3.fc20.x86_64                               4/7 
  Verifying  : mysql-workbench-community-6.1.7-1.fc20.x86_64                5/7 
  Verifying  : libzip-0.11.2-1.fc20.x86_64                                  6/7 
  Verifying  : gtkmm24-2.24.4-2.fc20.x86_64                                 7/7 
 
Installed:
  mysql-workbench-community.x86_64 0:6.1.7-1.fc20                               
 
Dependency Installed:
  compat-lua-libs.x86_64 0:5.1.5-1.fc20     ctemplate.x86_64 0:2.2-5.fc20       
  gtkmm24.x86_64 0:2.24.4-2.fc20            libzip.x86_64 0:0.11.2-1.fc20       
  tinyxml.x86_64 0:2.6.2-4.fc20             vsqlite++.x86_64 0:0.3.13-3.fc20    
 
Complete!

After successfully installing MySQL Workbench, you can launch it with the following command:

<shell> mysql-workbench

It should launch the following MySQL Workbench home page (click on it to see the full size image):

FedoraMySQLWorkbenchHome

Written by maclochlainn

September 13th, 2014 at 12:00 am