Archive for the ‘MySQL Shell’ Category
MySQL on Ubuntu
Fresh install of Ubuntu on my MacBook Pro i7 because Apple said the OS X was no longer upgradable. Time to install and configure MySQL Server. These are the steps to install MySQL on the Ubuntu Desktop.
Installation
- Update the Ubuntu OS by checking for, inspecting, and upgrading any available updates with the following commands:
sudo apt update sudo apt list sudo apt upgrade
- Check for available MySQL Server packages with this command:
apt-cache search binaries | grep -i mysql
It should return:
mysql-server - MySQL database server binaries and system database setup mysql-server-8.0 - MySQL database server binaries and system database setup mysql-server-core-8.0 - MySQL database server binaries default-mysql-server - MySQL database server binaries and system database setup (metapackage) default-mysql-server-core - MySQL database server binaries (metapackage) mariadb-server-10.6 - MariaDB database core server binaries mariadb-server-core-10.6 - MariaDB database core server files
- Check for more details on the MySQL packages with this command:
apt info -a mysql-server-8.0
- Install MySQL Server packages with this command:
sudo apt install mysql-server-8.0
- Start the MySQL Server service with this command:
sudo systemctl start mysql.service - Before you can run the mysql_secure_installation script, you must set the root password. If you skip this step the mysql_secure_installation script will enter an infinite loop and lock your terminal session. Log in to the mysql monitor with the following command:
sudo mysqlEnter a password with the following command (password is an insecure example):
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'C4nGet1n!';
Quit the mysql monitor session:
quit; - Run the mysql_secure_installation script with this command:
sudo mysql_secure_installationHere’s the typical output from running the mysql_secure_installation script:
Securing the MySQL server deployment. Enter password for user root: VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component? Press y|Y for Yes, any other key for No: Y There are three levels of password validation policy: LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 Using existing password for root. Estimated strength of the password: 100 Change the password for root ? ((Press y|Y for Yes, any other key for No) : N ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y Success. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y Success. All done!
Configuration
The next step is configuration. It requires setting up the sample sakila and studentdb database. The syntax has changed from prior releases. Here are the new three steps:
- Grant the root user the privilege to grant to others, which root does not have by default. You use the following syntax as the MySQL root user:
mysql> GRANT ALL ON *.* TO 'root'@'localhost';
- Download the sakila database, which you can download from this site. Click on the sakila database’s TGZ download.
When you download the sakila zip file it creates a sakila-db folder in the /home/student/Downloads directory. Copy the sakila-db folder into the /home/student/Data/sakila directory. Then, change to the /home/student/Data/sakila/sakila-db directory, connect to mysql as the root user, and run the following command:
mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-schema.sql mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-data.sql
- Create the studentdb database with the following command as the MySQL root user:
mysql> CREATE DATABASE studentdb; - Create the user with a clear English password and grant the user student full privileges on the sakila and studentdb databases:
mysql> CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Stud3nt!'; mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost'; mysql> GRANT ALL ON sakila.* TO 'student'@'localhost';
You can now connect to a sandboxed sakila database with the student user’s credentials, like:
mysql -ustudent -p -Dsakila |
or, you can now connect to a sandboxed studentdb database with the student user’s credentials, like:
mysql -ustudent -p -Dstudentdb |
MySQL Workbench Installation
sudo snap install mysql-workbench-community |
You have now configure the MySQL Server 8.0.
Updating SQL_MODE
This is an update for MySQL 8 Stored PSM to add the ONLY_FULL_GROUP_BY mode to the global SQL_MODE variable when it’s not set during a session. Here’s the code:
/* Drop procedure conditionally on whether it exists already. */ DROP PROCEDURE IF EXISTS set_full_group_by; /* Reset delimter to allow semicolons to terminate statements. */ DELIMITER $$ /* Create a procedure to verify and set connection parameter. */ CREATE PROCEDURE set_full_group_by() LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT 'Set connection parameter when not set.' BEGIN /* Check whether full group by is set in the connection and if unset, set it in the scope of the connection. */ IF EXISTS (SELECT TRUE WHERE NOT REGEXP_LIKE(@@SESSION.SQL_MODE,'ONLY_FULL_GROUP_BY')) THEN SET @@GLOBAL.SQL_MODE := CONCAT(@@SESSION.sql_mode,',ONLY_FULL_GROUP_BY'); END IF; END; $$ /* Reset the default delimiter. */ DELIMITER ; |
You can call the set_full_group_by procedure with the CALL command:
CALL set_full_group_by(); |
You can see the SQL_MODE variable with the following query:
SELECT @@GLOBAL.SQL_MODE; |
It’ll return:
+---------------------------------------------------------------+ | @@GLOBAL.SQL_MODE | +---------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +---------------------------------------------------------------+ 1 row in set (0.00 sec) |
As always, I hope this helps those looking to solve this type of problem.
MySQL RegExp Default
We had an interesting set of questions regarding the REGEXP comparison operator in MySQL today in both sections of Database Design and Development. They wanted to know the default behavior.
For example, we built a little movie table so that we didn’t change their default sakila example database. The movie table was like this:
CREATE TABLE movie ( movie_id int unsigned primary key auto_increment , movie_title varchar(60)) auto_increment=1001; |
Then, I inserted the following rows:
INSERT INTO movie ( movie_title ) VALUES ('The King and I') ,('I') ,('The I Inside') ,('I am Legend'); |
Querying all results with this query:
SELECT * FROM movie; |
It returns the following results:
+----------+----------------+ | movie_id | movie_title | +----------+----------------+ | 1001 | The King and I | | 1002 | I | | 1003 | The I Inside | | 1004 | I am Legend | +----------+----------------+ 4 rows in set (0.00 sec) |
The following REGEXP returns all the rows because it looks for a case insensitive “I” anywhere in the string.
SELECT movie_title FROM movie WHERE movie_title REGEXP 'I'; |
The implicit regular expression is actually:
WHERE movie_title REGEXP '^.*I.*$'; |
It looks for zero-to-many of any character before and after the “I“. You can get any string beginning with an “I” with the “^I“; and any string ending with an “I” with the “I$“. Interestingly, the “I.+$” should only match strings with one or more characters after the “I“, but it returns:
+----------------+ | movie_title | +----------------+ | The King and I | | The I Inside | | I am Legend | +----------------+ 3 rows in set (0.00 sec) |
This caught me by surprise because I was lazy. As pointed out in the comment, it only appears to substitute a “.*“, or zero-to-many evaluation for the “.+” because it’s a case-insensitive search. There’s another lowercase “i” in the “The King and I” and that means the regular expression returns true because that “i” has one-or-more following characters. If we convert it to a case-sensitive comparison with the keyword binary, it works as expected because it ignores the lowercase “i“.
WHERE binary movie_title REGEXP '^.*I.*$'; |
This builds on my 10-year old post on Regular Expressions. As always, I hope these notes helps others discovering features and behaviors of the MySQL database, and Bill thanks for catching my error.
MySQL and JavaScript
Sometimes students truly amaze me with their curiosity. The question was simple from their perspective while we were discussing MySQL’s builtin string functions. How would you do something like this JavaScript logic without using literals or session variables?
// Declare a string and substring. var myStr = 'Get me from the string.' var mySubstr = 'me' // Assign the substring to variable by rescuing it from the larger string. var rescued = myStr.substring(myStr.indexOf(mySubstr),myStr.indexOf(mySubstr) + mySubstr.length) // Print the result. print(rescued) |
tested with MongoDB, like
mongo --nodb --norc < parsing.js |
returning:
MongoDB shell version v4.0.20 me bye |
They thought the question would show SQL’s limits as a problem solving and programming language because they didn’t see how MySQL could assign a variable for evaluation in the builtin functions.
They were surprised to see how I showed them that they could do it. Since they disallowed session variables, I built a sample table and inserted the string value in a text column before writing a query with a Common Table Expression (CTE).
The MySQL steps are:
-- Stable testing scenario with table values requires a test table. DROP TABLE IF EXISTS sample; CREATE TABLE sample ( text VARCHAR(100) ); -- Insert the literal string into the testing table. INSERT INTO sample ( text ) VALUES ('Get me from the string.'); -- Test using a WITH clause to place a variable in context for use -- in the query, relying on the fact that a Cartesian set of one -- column and row becomes a new column in all rows of the other -- table's set. WITH struct AS (SELECT 'me' AS result) SELECT SUBSTR(text,INSTR(text,struct.result),LENGTH(struct.result)) AS rescued FROM sample CROSS JOIN struct; |
It returns the following:
+---------+ | rescued | +---------+ | me | +---------+ 1 row in set (0.00 sec) |
Wow, SQL works like a programming language was the response of the class. It’s like anything else in technology, new stuff isn’t as cool as old stuff until you learn how to use it.
MySQL Transaction Scope
The idea of ACID transactions are a basic feature of SQL’s individual Data Manipulation Language (DML) commands, like the INSERT, UPDATE, and DELETE statements. Transactions across two or more tables are a natural extension of ACID compliance features provided by DML commands. However, they require a structured programming approach, like a store procedure or like API implemented in an imperative language.
Surprisingly, transaction management wasn’t covered well in Alan Beaulieu’s Learning SQL because he only provided pseudo code logic. While I thought troubleshoot some broken MySQL SQL/PSM logic would be a good learning experience for students, it wasn’t. So, I wrote this sample code to show how to achieve an all or nothing transaction across four tables.
The code for this example on transaction management lets you perform the important tasks necessary to effect transaction management:
- You must disable autocommit in the scope of the session.
- You must use an imperative programming language like SQL/PSM or Python, et cetera.
- You must identify an error thrown in a series of DML transactions, rollback all completed work, and exit the scope of the program unit.
This SQL defines the four tables:
/* Drop and create four tables. */ DROP TABLE IF EXISTS one, two, three, four; CREATE TABLE one ( id int primary key auto_increment, msg varchar(10)); CREATE TABLE two ( id int primary key auto_increment, msg varchar(10)); CREATE TABLE three ( id int primary key auto_increment, msg varchar(10)); CREATE TABLE four ( id int primary key auto_increment, msg varchar(10)); |
Unfortunately, there’s no way to simply transaction management from the MySQL Command-Line Interface (CLI) because you need to build the logic that manages success and failure. It requires that you create a procedure using MySQL’s SQL/PSM (Persistent Stored Module) or another imperative programming language. You might think why can’t you just write an anonymous block program, like you can do in other stored procedural languages. The answer is simple. You can’t write anonymous blocks in MySQL’s SQL/PSM because they adhere to ANSI SQL-2003 (or more accurately ISO/IEC 9075-4:2003).
The following code block does:
- Conditionally drops the locking() procedure.
- Sets the default semicolon (
;) delimiter to a double-dollar ($$), which lets you use the semicolon as statement and block terminators. - Declares a
locking()procedure with the following:- Sets a parameter list with four IN-mode parameters.
- Declares an
EXIThandler that undoes any writes before an error in a sequence of DDL commands, like theINSERT,UPDATE, orDELETEstatements. TheEXIThandler then aborts completion of the rest of the procedure. (MySQL 13.6.7.2 Declare … Handler Statement) - Disables autocommit in the scope of the session.
- Starts a transaction context and inserts data into four tables as a transaction. The continue handler picks up processing when one of the
INSERTstatements fails with a 1406 error code. The 1406 error code represents an error that occurs because the data is too long for a column’s width. - When all elements of the procedure complete, you commit the work.
/* Conditionally drop procedure. */ DROP PROCEDURE IF EXISTS locking; /* Set delimiter to $$ to allow ; inside the procedure. */ DELIMITER $$ /* Create a transaction procedure. */ CREATE PROCEDURE locking(IN pv_one varchar(10) ,IN pv_two varchar(10) ,IN pv_three varchar(10) ,IN pv_four varchar(10)) BEGIN /* Declare an EXIT Handler when a string is too long for a column. Undo all prior writes with a ROLLBACK statement. */ DECLARE EXIT HANDLER FOR 1406 BEGIN ROLLBACK; END; /* Disable autocommit. */ SET AUTOCOMMIT=0; /* Start transaction scope. */ START TRANSACTION; /* A series of INSERT statement. */ INSERT INTO one (msg) VALUES (pv_one); INSERT INTO two (msg) VALUES (pv_two); INSERT INTO three (msg) VALUES (pv_three); INSERT INTO four (msg) VALUES (pv_four); /* Commit transaction set. */ COMMIT; END; $$ /* Reset delimiter to ; for SQL statements. */ DELIMITER ; |
The next block tests the locking() procedure. The first and third calls are successful but the second one fails because the third parameter is too long for the msg column in the three table. The error triggers the EXIT handler in the locking() procedure.
/* Call locking procedure. */ CALL locking('Donald','Goofy','Mickey','Pluto'); CALL locking('Squirrel','Chipmunk','Monkey business','Raccoon'); CALL locking('Curly','Larry','Moe','Shemp'); |
The query block below:
/* Select from tables, which should be empty. */ SELECT * FROM one; SELECT * FROM two; SELECT * FROM three; SELECT * FROM four; |
Returns the following, which shows only the first and third test cases succeed:
+----+--------+ | id | msg | +----+--------+ | 1 | Donald | | 2 | Curly | +----+--------+ 2 rows in set (0.01 sec) +----+-------+ | id | msg | +----+-------+ | 1 | Goofy | | 2 | Larry | +----+-------+ 2 rows in set (0.00 sec) +----+--------+ | id | msg | +----+--------+ | 1 | Mickey | | 2 | Moe | +----+--------+ 2 rows in set (0.00 sec) +----+-------+ | id | msg | +----+-------+ | 1 | Pluto | | 2 | Shemp | +----+-------+ 2 rows in set (0.00 sec) |
As always, I hope this helps those trying to write transactions across multiple tables.
MySQL sakila Database
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:
- Go to https://dev.mysql.com/doc/index-other.html and download the files for the “
sakiladatabase” under the Example Database section. - 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). - Type
source c:\temp\sakila-db\sakila-schema.sql
and press enter.
- 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.
MySQL Client in 8.0.21+
Having recently installed a fresh copy of MySQL 8.0.21 on Windows, I took careful note of parsing changes in the MySQL Shell. It was tedious that we lost multiple statement processing, which is supported in the MySQL Client and MySQL Workbench because it uses MySQL Client.
It was frustrating when I subsequently discovered that the MySQL Shell took away the ability to write log files by removing the TEE and NOTEE commands. I suspected that since MySQL Workbench was still using the MySQL Client that it should be in the code tree. In fact, the mysql.exe client is in this directory:
C:\Program Files\MySQL\MySQL Server 8.0\bin |
So, I immediately created a batch file to put the MySQL Client into my %PATH% environment variable when needed. I used this time tested DOS command:
copy con mysqlclient.bat set PATH=%PATH%;C:\Program Files\MySQL\MySQL Server 8.0\bin;. ^Z |
Now you can call the MySQL client with the following syntax if you have a student user and studentdb database:
mysql -ustudent -p -Dstudentdb |
This way, I can just call it in a PowerShell utility before running migration script files that should generate log files for review. It also leaves the MySQL Client out of general use.
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.
Subscribe via RSS