Archive for the ‘sql’ Category
MySQL Floating Types
I’m glad that testing new MySQL releases is so frequent for me. Each testing cycle let me catch warning messages about deprecated behaviors before they’re removed. This one announced the deprecation of digits for floating point data types, like double. The following column definition for a table in my code tree triggered the warning message:
, amount DOUBLE(10,2) |
MySQL 8 (8.0.21) raised the following warning message:
Warning (code 1681): Specifying number of digits for floating point data types is deprecated and will be removed in a future release. |
Recognizing the deprecation, I redefined the column as:
, amount DOUBLE |
I’m glad the MySQL development team is focused on alerting us to deprecations through warning messages. Naturally, I fixed all of the tables proactively. If you want more detail you can read this MySQL web page on Problems with Floating Point Values.
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.
Misleading Oracle Errors
It’s always interesting when you get in a hurry, have multiple terminal sessions open and type the wrong thing in the wrong terminal session. This is especially true when working with the Oracle database.
In this case, it was implementing a Docker Container of Oracle Database 18c on macOS. I typed the following to connect as the privileged system user:
sqlplus system/cangetin |
It generated the following error stack:
[student@localhost ~]$ sqlplus system/cangetin SQL*Plus: Release 18.0.0.0.0 - Production on Tue Sep 15 15:02:30 2020 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. ERROR: ORA-27140: attach to post/wait facility failed ORA-27300: OS system dependent operation:invalid_egid failed with status: 1 ORA-27301: OS failure message: Operation not permitted ORA-27302: failure occurred at: skgpwinit6 ORA-27303: additional information: startup egid = 54321 (oinstall), current egid = 54322 (dba) |
Really, that’s suppose to help an end-user or even an average DBA? Shouldn’t it really return an error that says the OS user isn’t the owner of the database? Naturally, there’s nothing wrong with connecting as the system privileged user when you’re OS account is not the owner provided you use the network service identifier, like
sqlplus system/cangetin@xe |
It works fine with the xe network service identifier. I hope this helps anybody confused by the error stack.
Create Student User
It’s amazing how old some of my students’ computers are. The oldest with least memory are the foreign students. Fortunately, I kept copies of the old Oracle Database 10g XE. I give it to some students who need to run the smallest possible option. Then, again I have students who get emotional about having to use Unix or Linux as an operating system, which means I now also support Oracle Database 18c.
Anyway, I had to write a script that would support building a small 200 MB student schema in any of the Express Edition databases from 10g to 18c. Here’s the script for those who would like to use it. It sets up a student schema for Oracle Database 10g and 11g databases and a c##student schema for Oracle’s Containized Database 12c and 18c.
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 | DECLARE /* Control variable. */ container BOOLEAN := FALSE; /* Weakly structured system reference cursor. */ container_sql SYS_REFCURSOR; /* Constant required for pre-container databases to avoid a a compile time error. */ sql_statement CONSTANT VARCHAR2(50) := 'SELECT cdb FROM v$database WHERE cdb = ''YES'''; BEGIN /* Check if the current user is the superuser. */ FOR i IN (SELECT USER FROM dual) LOOP /* Perform tasks as superuser. */ IF i.USER = 'SYSTEM' THEN /* Check for a container-enabled column, which enables this to work in both pre-container Oracle databases, like 10g and 11g. */ FOR j IN (SELECT DISTINCT column_name FROM dba_tab_columns WHERE column_name = 'CDB') LOOP /* Check for a container database, set control variable and exit when found. */ OPEN container_sql FOR sql_statement; LOOP container := TRUE; EXIT WHEN container_sql%FOUND; END LOOP; END LOOP; /* Conditionally drop existing user and role. */ IF container THEN /* Conditionally drop a container user. */ FOR j IN (SELECT username FROM dba_users WHERE username = 'C##STUDENT') LOOP EXECUTE IMMEDIATE 'DROP USER c##student CASCADE'; END LOOP; /* Conditionally rop the container c##studentrole role. */ FOR j IN (SELECT ROLE FROM dba_roles WHERE ROLE = 'C##STUDENTROLE') LOOP EXECUTE IMMEDIATE 'DROP ROLE c##studentrole'; END LOOP; /* Create a container user with 200 MB of space. */ EXECUTE IMMEDIATE 'CREATE USER c##student'||CHR(10) || 'IDENTIFIED BY student'||CHR(10) || 'DEFAULT TABLESPACE users'||CHR(10) || 'QUOTA 200M ON users'||CHR(10) || 'TEMPORARY TABLESPACE temp'; /* Create a container role. */ EXECUTE IMMEDIATE 'CREATE ROLE c##studentrole CONTAINER = ALL'; /* Grant privileges to a container user. */ EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10) || 'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10) || 'CREATE SESSION, CREATE TABLE,'||CHR(10) || 'CREATE TRIGGER, CREATE TYPE,'||CHR(10) || 'CREATE VIEW TO c##studentrole'; /* Grant role to user. */ EXECUTE IMMEDIATE 'GRANT c##studentrole TO c##student'; ELSE /* Conditonally drop the non-container database user. */ FOR j IN (SELECT username FROM dba_users WHERE username = 'STUDENT') LOOP EXECUTE IMMEDIATE 'DROP USER student CASCADE'; END LOOP; /* Create the student database. */ EXECUTE IMMEDIATE 'CREATE USER student'||CHR(10) || 'IDENTIFIED BY student'||CHR(10) || 'DEFAULT TABLESPACE users'||CHR(10) || 'QUOTA 200M ON users'||CHR(10) || 'TEMPORARY TABLESPACE temp'; /* Grant necessary privileges to the student database. */ EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10) || 'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10) || 'CREATE SESSION, CREATE TABLE,'||CHR(10) || 'CREATE TRIGGER, CREATE TYPE,'||CHR(10) || 'CREATE VIEW TO student'; END IF; ELSE /* Print an message that the user lacks privilegs. */ dbms_output.put_line('You must be the SYSTEM user to drop and create a user.'); END IF; END LOOP; END; / |
As always, I hope this helps those looking for a solution beyond Quest’s Toad for Oracle, APEX, or SQL Developer. Let me know if you like it.
PostgreSQL Write JSON File
In the process of working through issues to support ETL transformations from JSON with Python. The first step was extracting a the data from a PostgreSQL table into a JSON file. The syntax wasn’t exactly clear and the PostgreSQL Tutorial was a bit misleading but I worked it out. My initial sample deals with writing the data from the item table of my videodb database to a item.json file in the /tmp directory.
The query retrieves the data but casts the creation_date and last_update_date timestamp data types into date data types, as follows:
SELECT row_to_json( (SELECT ROW FROM (SELECT item_id , item_barcode , item_type , item_title , item_subtitle , item_rating , item_release_date , created_by , creation_date::DATE , last_updated_by , last_update_date::DATE) ROW)) AS DATA FROM item; |
The internal SELECT-list creates the name element of name:value pairs in the JSON structure. Naturally, it does exclude the type casting ::date portion of the query from the name elements. The row_to_json() maps the name list to the list of value results from in each row, and returns a result set.
The query returns the following 21 JSON objects:
{"item_id":1001,"item_barcode":"9736-05640-4","item_type":1016,"item_title":"The Hunt for Red October","item_subtitle":"Special Collector's Edition","item_rating":"PG","item_release_date":"1990-03-02","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1002,"item_barcode":"24543-02392","item_type":1016,"item_title":"Star Wars I","item_subtitle":"Phantom Menace","item_rating":"PG","item_release_date":"1999-05-04","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1003,"item_barcode":"24543-5615","item_type":1015,"item_title":"Star Wars II","item_subtitle":"Attack of the Clones","item_rating":"PG","item_release_date":"2002-05-16","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1004,"item_barcode":"24543-05539","item_type":1016,"item_title":"Star Wars II","item_subtitle":"Attack of the Clones","item_rating":"PG","item_release_date":"2002-05-16","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1005,"item_barcode":"24543-20309","item_type":1016,"item_title":"Star Wars III","item_subtitle":"Revenge of the Sith","item_rating":"PG13","item_release_date":"2005-05-19","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1006,"item_barcode":"86936-70380","item_type":1016,"item_title":"The Chronicles of Narnia","item_subtitle":"The Lion, the Witch and the Wardrobe","item_rating":"PG","item_release_date":"2002-05-16","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1007,"item_barcode":"91493-06475","item_type":1019,"item_title":"RoboCop","item_subtitle":"","item_rating":"Mature","item_release_date":"2003-07-24","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1008,"item_barcode":"93155-11810","item_type":1019,"item_title":"Pirates of the Caribbean","item_subtitle":"","item_rating":"Teen","item_release_date":"2003-06-30","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1009,"item_barcode":"12725-00173","item_type":1019,"item_title":"The Chronicles of Narnia","item_subtitle":"The Lion, the Witch and the Wardrobe","item_rating":"Everyone","item_release_date":"2003-06-30","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1010,"item_barcode":"45496-96128","item_type":1017,"item_title":"MarioKart","item_subtitle":"Double Dash","item_rating":"Everyone","item_release_date":"2003-11-17","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1011,"item_barcode":"08888-32214","item_type":1018,"item_title":"Splinter Cell","item_subtitle":"Chaos Theory","item_rating":"Teen","item_release_date":"2003-04-08","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1012,"item_barcode":"14633-14821","item_type":1018,"item_title":"Need for Speed","item_subtitle":"Most Wanted","item_rating":"Everyone","item_release_date":"2004-11-15","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1013,"item_barcode":"10425-29944","item_type":1019,"item_title":"The DaVinci Code","item_subtitle":"","item_rating":"Teen","item_release_date":"2006-05-19","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1014,"item_barcode":"52919-52057","item_type":1019,"item_title":"Cars","item_subtitle":"","item_rating":"Everyone","item_release_date":"2006-04-28","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1015,"item_barcode":"9689-80547-3","item_type":1020,"item_title":"Beau Geste","item_subtitle":"","item_rating":"PG","item_release_date":"1992-03-01","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1016,"item_barcode":"53939-64103","item_type":1020,"item_title":"I Remember Mama","item_subtitle":"","item_rating":"NR","item_release_date":"1998-01-05","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1017,"item_barcode":"24543-01292","item_type":1020,"item_title":"Tora! Tora! Tora!","item_subtitle":"The Attack on Pearl Harbor","item_rating":"G","item_release_date":"1999-11-02","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1018,"item_barcode":"43396-60047","item_type":1020,"item_title":"A Man for All Seasons","item_subtitle":"","item_rating":"G","item_release_date":"1994-06-28","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1019,"item_barcode":"43396-70603","item_type":1020,"item_title":"Hook","item_subtitle":"","item_rating":"PG","item_release_date":"1991-12-11","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1020,"item_barcode":"85391-13213","item_type":1020,"item_title":"Around the World in 80 Days","item_subtitle":"","item_rating":"G","item_release_date":"1992-12-04","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"}
{"item_id":1021,"item_barcode":"85391-10843","item_type":1020,"item_title":"Camelot","item_subtitle":"","item_rating":"G","item_release_date":"1998-05-15","created_by":1001,"creation_date":"2019-12-13","last_updated_by":1001,"last_update_date":"2019-12-13"} |
Moving on to the next step you enclose the query in the copy command syntax, like:
COPY (SELECT row_to_json( (SELECT ROW FROM (SELECT item_id , item_barcode , item_type , item_title , item_subtitle , item_rating , item_release_date , created_by , creation_date::DATE , last_updated_by , last_update_date::DATE) ROW)) AS DATA FROM item) TO '/tmp/sample.json'; |
When you run it, it will fail with the following error message:
ERROR: must be superuser or a member of the pg_write_server_files role to COPY to a file HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone. |
You can fix this problem by connecting as the postgres user and granting the privilege to the student user:
GRANT pg_write_server_files TO student; |
It then runs successfully as the student user and creates the sample.json file in the /tmp directory. As always, I hope this helps those looking for a solution.
PostgreSQL Creating Schema
The process of creating a schema requires you grant the CREATE ON DATABASE privilege to the user as the postgres user. You use the following syntax:
GRANT CREATE ON DATABASE videodb TO student; |
As the student user, you create the app schema with the following syntax:
CREATE SCHEMA app; |
Then, you can query the result as follows:
SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname; |
You should see the following:
nspname | nspowner | nspacl
--------------------+----------+-------------------------------------
app | 16390 |
information_schema | 10 | {postgres=UC/postgres,=U/postgres}
pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
pg_temp_1 | 10 |
pg_toast | 10 |
pg_toast_temp_1 | 10 |
public | 10 | {postgres=UC/postgres,=UC/postgres}
(7 rows) |
If you create a revision_history table without a schema name, it is automatically placed in the public schema, which means an attempt to describe the table will return an error. For example, you create a revision_history table with the following command:
CREATE TABLE app.revision_history ( revision_history_id serial , session_id VARCHAR , TABLE_NAME VARCHAR , revision_id INTEGER ); |
You describe the revision_history table with the following command:
\d revision_history |
It will show the following because there is no revision_history table in the public schema and the default search path only include a schema that shares the name with the student user and the public schema.
Did not find any relation named "revision_history". |
You can show the search path with the following:
show search_path; |
It should return the following, which is a schema that shares the user’s name and public.
search_path ----------------- "$user", public (1 row) |
You set the search path as follows:
SET search_path TO app, "$user", public; |
After you set the search_path, a standard attempt to describe the table will find the table whether it is in the app or public schema. That means the following command:
\d revision_history |
Shows:
Table "app.revision_history"
Column | Type | Collation | Nullable | Default
---------------------+-------------------+-----------+----------+---------------------------------------------------------------
revision_history_id | integer | | not null | nextval('revision_history_revision_history_id_seq'::regclass)
session_id | character varying | | |
table_name | character varying | | |
revision_id | integer | | | |
As always, I hope this helps somebody looking for an answer.
PostgreSQL WITH to INSERT
This demonstrates how you insert results from a common table expression (CTE) in a leading WITH clause. I thought it would be a nice add since the existing tutorials didn’t have an example.
Create the message table, like this:
CREATE TABLE message ( message_id SERIAL , message_text VARCHAR ); |
Now, here’s a CTE with a two fabricated rows:
WITH cte AS ( SELECT 'x-ray' AS msg UNION ALL SELECT 'MRI' AS msg ) INSERT INTO message ( message_text ) ( SELECT msg FROM cte ); |
Then, you can query the results from the message table, like this:
SELECT * FROM message; |
You should get the following results:
message_id | message_text
------------+--------------
1 | x-ray
2 | MRI
(2 rows) |
Unfortunately, if you re-ran that it would insert a duplicate set of rows. You can avoid that behavior by using correlation between the subquery in the WITH clause and target table of the INSERT statement.
The following only allows you to INSERT new rows in the table.
WITH cte AS ( SELECT 'x-ray' AS msg UNION ALL SELECT 'MRI' AS msg ) INSERT INTO message ( message_text ) ( SELECT msg FROM cte WHERE NOT EXISTS (SELECT NULL FROM message WHERE message.message_text = cte.msg )); |
You can add revision control column to the message table to verify there’s no change with subsequent attempts. The first step requires you create an independent message_rev_id_seq sequence, like this:
CREATE SEQUENCE message_rev_id_seq INCREMENT 1 MINVALUE -9223372036854775807 MAXVALUE 9223372036854775806 START -9223372036854765807; |
You can alter the table by using the standard rev_id column name:
ALTER TABLE message ADD COLUMN rev_id BIGINT NOT NULL DEFAULT NEXTVAL('public.message_rev_id_seq'); |
The ALTER statement will automatically add the first two values of the message_rev_id_seq to the existing rows. The ALTER statement is an immediate action because all Data Definition Language (DDL) commands are 1-Phase Commit operations.
The next step is to create an update_message_rev_id function that return a trigger. You do this using PL/pgSQL but you must specify the language value explicitly when returning a trigger data type, like:
1 2 3 4 5 6 7 8 | CREATE OR REPLACE FUNCTION update_message_rev_id() RETURNS TRIGGER AS $$ BEGIN NEW.rev_id = NEXTVAL(‘public.message_rev_id_seq’); RETURN NEW; END; $$ LANGUAGE plpgsql; |
The last step requires you create an update_message_revision trigger that calls the update_message_rev_id function, like:
CREATE TRIGGER update_message_revision BEFORE UPDATE ON message FOR EACH ROW EXECUTE PROCEDURE update_message_rev_id(); |
You can test it by running the correlated INSERT statement again. You’ll find that no rows are touched by the INSERT statement because the revision numbers are unchanged. You should see these two rows:
message_id | message_text | rev_id
------------+--------------+----------------------
1 | x-ray | -9223372036854765807
2 | MRI | -9223372036854765806
(2 rows) |
As always, I hope this helps those looking for a example of how to solve a problem.
Subscribe via RSS