Archive for the ‘MySQL’ Category
MySQL Shell Parsing
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.
MySQL Unicode Warning
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.
MySQL Provisioning
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:
- Create the
studentdbdatabase with the following command as the MySQLrootuser:MySQL localhost:33060+ ssl SQL > CREATE DATABASE studentdb;
- Grant the
rootuser the privilege to grant to others, which root does not have by default. You use the following syntax as the MySQLrootuser:MySQL localhost:33060+ ssl SQL > GRANT ALL ON *.* TO 'root'@'localhost';
- Create the user with a clear English password and grant the user
studentfull privileges on thestudentdbdatabase: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';
- 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';
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.
MySQL File Privilege
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.
- Using the Windows’ File Explorer, I checked the
Uploadsdirectory’s privileges by right clicking theUploadsdirectory to check the Properties of the directory in the File Explorer dialog box:
- 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.
- The Sharing tab indicates that the files are Shared, as shown:
- Clicking the Share … button, the files in this directory are shared with Read/Write permissions to Everyone, as shown below.
- 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:
- I put the
avenger.csvfile in the following directory pointed to by thesecure-file-privsvalue in themy.ini.C:\ProgramData\MySQL\MySQL Server 8.0\Uploads - Updated the SQL
LOADstatement 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';
The
LOADcommand worked and put the CSV file contents into theavengertable, 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)
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'; |
As always, I hope this helps those looking for a solution.
MySQL 8.0 Install
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.
- 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.
- 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.
- 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.
- 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.
- Returning to the setup type workflow, you should select the Custom radio button. Click the Next button to view the list of selected types.
- 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.
- 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.
- This dialog qualifies that there are three products to configure. Click the Next button to begin configuration of these products.
- 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.
- 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.
- 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.
- This dialog lets you enter the MySQL Root Password. Click the Next button to continue the MySQL Installer configuration.
- 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.
- 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.
- 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.
- This dialog lets you choose the next MySQL Router product for configuration. Click the Next button to begin the MySQL Router configuration.
- This dialog allows you to configure the MySQL Router Configuration product. Leave the
HostnameandPasswordfields blank when you do not want to configure the MySQL Router Configuration product. Click the Finish button to complete the MySQL Router workflow.
- This dialog lets you choose the next Samples and Examples product for configuration. Click the Next button to begin the Samples and Examples configuration.
- This dialog lets you create a Windows 10 MySQL Service. You enter the
rootpassword that you entered in Step #12. After you enterrootpassword, click the Check button to verify therootpassword. The Check button enables the Next button when therootaccount’s password is validated. Click the now enabled Next button to create the MySQL Service.
- This dialog lets you create a Windows 10 MySQL Service. Click the now enabled Next button to create the MySQL Service.
- This dialog applies all the configurations selected. Click the Execute button to proceed with the configuration.
- This dialog lets you watch the progress of the configuration. Click the Finish button to complete the product configurations.
- 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.
- 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.
Correlated Updates
It’s always interesting when I answer questions. This question was how to you perform a correlated UPDATE statement. My answer was assuming you’re updating the rating_id foreign key column in the rating table with the value from an item_rating column in the item table where on or another column value in the rating table match the item_rating column value in the item table match, you would write a correlated UPDATE statement like:
UPDATE item i SET i.rating_id = r.rating_id WHERE EXISTS (SELECT NULL FROM rental r WHERE r.rating = i.item_rating OR r.description = i.item_rating); |
This works in Oracle, MySQL, MariaDB, and MS SQL Server. I thought my work was done but I was wrong. The individual was trying to write a correlated UPDATE statement for PostgreSQL. The statement returned the following error:
ERROR: syntax error at or near "WHERE"
LINE 3: WHERE EXISTS
^ |
I did didn’t find an article to point the individual to after quick Google and DuckDuckGo searches. So, I thought I’d provide how you do it in PostgreSQL:
UPDATE item i SET rating_id = r.rating_id FROM rating r WHERE r.rating = i.item_rating OR r.description = i.item_rating; |
In short, PostgreSQL doesn’t do what most expect because the UPDATE statement supports a FROM clause. Let’s give them the prize for different dialect. While I hope that I’m not a syntax bigot because I use MySQL more, I think the default syntax should always be supported in SQL dialects. After all, MySQL has a far superior named-notation INSERT statement alternative to the standard with the assignment method but MySQL also supports the standard syntax.
While I’ve shown you how to do it in PostgreSQL, what do you think? Should PostgreSQL be as responsible as MySQL is in maintaining standard SQL approaches?
MySQL JSON Server
A student question: Does JavaScript make context switching for web-based applications obsolete? Wow! I asked what that meant. He said, it means JavaScript replaces all other server-side programming languages, like PHP, C#, or Python. I asked the student why he believed that. His answer was that’s what two interviewing managers told him.
I thought it would be interesting to put the idea to a test. Below is a Node.js script that acts as a utility that queries the MySQL database with substitution variables in query. It also returns a standard out (stdout) stream of the MySQL query’s results. It also supports three flag and value pairs as arguments, and optionally writes the results of the MySQL query to a log file while still returning result as the stdout value. All errors are written to the standard error (stderr) stream.
The Node.js solution is completely portable between Windows and Linux. You can deploy it to either platform without any edits for Windows case insensitive Command-Line Interface (CLI). Clearly, Node.js offers a replacement for direct interaction with the .NET components in PowerShell. This appears to mean basic Linux shell or PowerShell knowledge is all that’s required to write and deploy JavaScript programs as server-side programming solutions. It means anything that you would have done with the .NET you can do with JavaScript. Likewise, you can replace PHP, C#, Python, or Ruby server-side scripts with JavaScript programs.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | // Declare constants. const fs = require('fs') const util = require('util') const express = require('express') const mysql = require('mysql') const connection = mysql.createConnection({ host: 'localhost', user: 'student', password: 'student', database: 'studentdb' }) // Declare local variables for case insensitive use. var data = '' var buffer = Buffer.alloc(0) var path = '' // Declare default query variables dates. var startDate = new Date('1980-01-01') var endDate = new Date() // Set default endDate value as tomorrow. endDate.setDate(new Date().getDate() + 1) // Define a regular expression for valid file names. var regexp = /^([0-9a-zA-Z]+|[0-9a-zA-Z]+\.+[0-9a-zA-Z]{3})$/ // Assign dynamic variables from arguments. var argv = process.argv.slice(2) // Check for paired values, evaluate and assign them to local variables. if ((argv.length % 2) == 0) { for (let i = 0; i < argv.length; i += 2) { // Assign a file name to write to the output path. if ((argv[i].toLowerCase() == '-f') && (regexp.test(argv[i+1]))) { // Assign present working for Windows or Linux. if (process.platform == 'win32') path = '.\\' + argv[1] else path = './' + argv[1] } // Assign a start date from the input string. else if (argv[i].toLowerCase() == '-b') { startDate = new Date(argv[i+1]) } // Assign a end date from the input string. else if (argv[i].toLowerCase() == '-e') { endDate = new Date(argv[i+1]) } } } else { console.error('Arguments must be in pairs: flag and value.') } // Define and run MySQL query. connection.query("SELECT i.item_title " + ", date_format(i.release_date,'%d-%M-%Y') AS release_date " + "FROM item i JOIN common_lookup cl " + "ON i.item_type = cl.common_lookup_id " + "WHERE cl.common_lookup_type = 'BLU-RAY' " + "AND i.release_date BETWEEN ? AND ? " + "ORDER BY i.release_date" ,[startDate, endDate], function (err, result) { if (err) { console.error('Query contains error ...') console.error('-> ' + err) } else { // Prints the index value in the RowDataPacket. for(let element in result) { data += result[element].item_title + ', ' + result[element].release_date + '\n' } // Write file when data string is not empty. if (data.length > 0 ) { buffer = Buffer.alloc(data.length,data) // Check for a defined path before writing a file. if (path.length > 0) { // Open the file. fs.open(path, 'w', function(err, fd) { if (err) { console.error('Could not open [' + path + '] file [' + err + ']') } else { // Write the file. fs.write(fd, buffer, 0, buffer.length, null, function(err) { if (err) console.error('Error writing [' + path + '] file [' + err + ']') fs.close(fd, function() { if (fs.existsSync(path)) { process.exit(0) } }) }) } }) } // Set standard out (stdout). console.log(data) } else { console.error('Query returned no rows.') } } }) // Close MySQL connection. connection.end() |
You can call this code with the default values, like
node app.js |
You can call this code with a user defined file name, and a custom start and end date values, like
node app.js -f output.csv -b '2001-01-01' -e '2004-12-31' |
The latter command returns the following by querying my MySQL studentdb video store:
Star Wars II, 16-May-2002 Harry Potter and the Chamber of Secrets, 28-May-2002 Harry Potter and the Sorcerer's Stone, 28-May-2002 Die Another Day, 03-June-2003 Harry Potter and the Prisoner of Azkaban, 23-October-2004 |
As always, I hope this helps somebody trying to sort it out.
Express.js & MySQL
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:
- Configure your Express.js and MySQL development in a single Node.js application.
- How to convert the list of
RowDataPacketobjects as elements of data, which is really just simple JavaScript knowledge. - 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.
Node.js & MySQL
These are my notes for creating a small Node.js application that queries a MySQL database. The post will show you how to:
- Configure your Node.js development directory.
- Build a small application to test a MySQL connection.
- Build a small application that connects to the MySQL database and queries data.
This blog post 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. Then, create a node_modules symbolic link to the /usr/local/lib/node_modules directory in the db directory. You can use the following command from the db directory:
ln -s /usr/local/lib/node_modules `pwd`/node_modules |
After creating the node_modules symbolic link, you need to run the following two npm commands:
npm init --y npm install --save mysql |
The first command sets up a generic package.json file, and the second adds the mysql package and supporting packages to the package.json file. These two steps configure the Node.js side of these examples.
They both require that you create the student user with a native password, like so:
CREATE USER 'student'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'student'; GRANT ALL PRIVILEGES ON studentdb.* TO 'student'@'localhost'; FLUSH PRIVILEGES; |
The following example shows you how to check a connection to the MySQL database:
const mysql = require('mysql') const connection = mysql.createConnection({ host: 'localhost', user: 'student', password: 'student', database: 'studentdb' }) connection.connect((err) => { if (err) throw err else console.log('Connected to MySQL Server!\n') console.log('User configured wiht mysql_native_password.\n'); console.log('Press Ctrl-C to terminate ...') }) |
You extend the previous example by adding a query component and returning the query result value to the console’s log (leveraging the w3school’s Node.js and MySQL tutorial example):
const mysql = require('mysql') const connection = mysql.createConnection({ host: 'localhost', user: 'student', password: 'student', database: 'studentdb' }) connection.connect((err) => { if (err) throw err else { console.log('Connected to MySQL Server!\n') connection.query('SELECT DISTINCT item_title FROM item', function (err, result) { if (err) throw err else console.log(result) console.log('Press Ctrl-C to terminate ...') }) } }) |
It should display the following:
Connected to MySQL Server!
[ RowDataPacket { item_title: 'The Hunt for Red October' },
RowDataPacket { item_title: 'Star Wars I' },
RowDataPacket { item_title: 'Star Wars II' },
RowDataPacket { item_title: 'Star Wars III' },
RowDataPacket { item_title: 'The Chronicles of Narnia' },
RowDataPacket { item_title: 'RoboCop' },
RowDataPacket { item_title: 'Pirates of the Caribbean' },
RowDataPacket { item_title: 'MarioKart' },
RowDataPacket { item_title: 'Splinter Cell' },
RowDataPacket { item_title: 'Need for Speed' },
RowDataPacket { item_title: 'The DaVinci Code' },
RowDataPacket { item_title: 'Cars' },
RowDataPacket { item_title: 'Beau Geste' },
RowDataPacket { item_title: 'I Remember Mama' },
RowDataPacket { item_title: 'Tora! Tora! Tora!' },
RowDataPacket { item_title: 'A Man for All Seasons' },
RowDataPacket { item_title: 'Hook' },
RowDataPacket { item_title: 'Around the World in 80 Days' },
RowDataPacket { item_title: 'Harry Potter and the Sorcerer\'s Stone' },
RowDataPacket { item_title: 'Camelot' },
RowDataPacket { item_title: 'Casino Royale' },
RowDataPacket { item_title: 'Die Another Day' },
RowDataPacket { item_title: 'Golden Eye' },
RowDataPacket { item_title: 'Tomorrow Never Dies' },
RowDataPacket { item_title: 'The World Is Not Enough' },
RowDataPacket { item_title: 'Brave Heart' },
RowDataPacket { item_title: 'Christmas Carol' },
RowDataPacket { item_title: 'Scrooge' },
RowDataPacket { item_title: 'Clear and Present Danger' },
RowDataPacket { item_title: 'Harry Potter and the Chamber of Secrets' },
RowDataPacket { item_title: 'Harry Potter and the Prisoner of Azkaban' },
RowDataPacket { item_title: 'Harry Potter and the Goblet of Fire' },
RowDataPacket { item_title: 'Harry Potter and the Order of the Phoenix' },
RowDataPacket { item_title: 'Harry Potter and the Half Blood Prince' },
RowDataPacket { item_title: 'Harry Potter and the Deathly Hallows, Part 1' },
RowDataPacket { item_title: 'Harry Potter and the Deathly Hallows, Part 2' },
RowDataPacket { item_title: 'Tron' },
RowDataPacket { item_title: 'The Avengers' },
RowDataPacket { item_title: 'Thor: The Dark World' } ]
Press Ctrl-C to terminate ... |
As always, I hope this helps those looking to learn
MySQL 8.0.20 JDBC
In April, when I updated from MySQL 8.0.17 to MySQL 8.0.19, I found that my Java connection example failed. That’s because of a change in the JDBC driver, which I blogged about then. Starting yesterday, I began updating a base Fedora 30 configuration again to MySQL 8.0.20. I wrote a testing program for the Java JDBC file last time, and when I ran it this time it told me that I didn’t have the JDBC driver installed, or in the $CLASSPATH. My Java diagnostic script, MySQLDriver.java, returned the following error message:
Error: Could not find or load main class MySQLDriver |
The Java JDBC test program code is in the prior post. It simply loads the user, password, database, host, and port statically for my student image, which you can update for your MySQL deployment.
You can find the MySQL 8.0 Connector/J installation in Chapter 4 of the MySQL Connector/J 8.0 Developer Guide. The link to page where you can download the Connector/J driver is in Chapter 4, but to save time you can click on the following download link. You’ll find Ubuntu, Debian, SUSE Enterprise Linux Server, Red Hat Enterprise Linux/Oracle Linux, Fedora, Platform Independent, and Source Code at that download link.
I downloaded Fedora 30, 31, and 32 because eventually they get deprecated and sometimes I need the deprecated versions. Since Python 2.7 is deprecated, keeping an old Fedora 30 with both Python 2.7 and 3.7 is very attractive as a developer, keeping these Java libraries is also a potential convenience. On the Fedora 30 platform, you should download:
mysql-connector-java-8.0.20-1.fc30.noarch.rpm |
You install the RPM package with the following syntax:
rpm -ivh mysql-connector-java-8.0.20-1.fc30.noarch.rpm |
It installs a single mysql-connector-java.jar file in the /usr/share/java directory. Then, you need to export the Java Archive file like so in your .bashrc file:
# Set the Java CLASSPATH path. export CLASSPATH=/usr/share/java/mysql-connector-java.jar:. |
Now, my MySQLDriver Java program returned:
Cannot connect to database server: The server time zone value 'MDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support. |
As qualified in the prior post, it required me to add the following line to my /etc/my.cnf configuration file, which synchronizes the database’s timezone with the operating system.
# Synchronize the MySQL clock with the computer system clock. default-time-zone='+00:00' |
You need to restart the MySQL Daemon to apply the change, like:
sudo systemctl restart mysqld.service |
Then, running my MySQL Driver connection test program worked like a charm. It returns the following:
Database connection established MySQLDriver Version [8.0.20] Database connection terminated |
As always, I hope this helps those looking for a quick solution.
Subscribe via RSS