Archive for the ‘Fedora’ Category
Wrap Oracle SQL*Plus
One of the key problems with Oracle’s deployment is that you can not use the up-arrow key to navigate the sqlplus command-line history. Here’s little Bash shell function that you can put in your .bashrc file. It requires you to have your system administrator install the rlwrap package, which wraps the sqlplus command-line history.
You should also set the $ORACLE_HOME environment variable before you put this function in your .bashrc file.
sqlplus () { # Discover the fully qualified program name. path=`which rlwrap 2>/dev/null` file='' # Parse the program name from the path. if [ -n ${path} ]; then file=${path##/*/} fi; # Wrap when there is a file and it is rewrap. if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then rlwrap sqlplus "${@}" else echo "Command-line history unavailable: Install the rlwrap package." $ORACLE_HOME/bin/sqlplus "${@}" fi } |
If you port this shell script to an environment where rlwrap is not installed, it simply prints the error message and advises you to install the rlwrap package.
As always, I hope this helps those looking for a solution.
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.
MySQL 8.0.20 Update
After I updated a Fedora 30 instance, I could no longer connect to the MySQL database. An attempt to connect raised the following error:
Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) |
My guess was correct, the mysqld.service got removed during the update (a synonym for upgrade). So, I ran the following command as a sudoer user:
sudo systemctl enable mysqld.service |
It creates the following symbolic link:
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service. |
That would start the MySQL Daemon (mysqld) on the next restart of the OS. However, I didn’t want to restart to have access to the service. I simply started it with the following command:
sudo systemctl start mysqld.service |
Then, I could connect to the MySQL database. As always, I hope this helps those looking for a solution.
MySQL Linux to Windows
My students want to transfer their course setup MySQL files from the Linux VM that I provide to Microsoft Windows 10. This post explains how because I found a couple small errors in the Google’d posts they would most likely see.
The first step is required because when I didn’t assign a name or domain to the the Fedora VM, which allows it to run as localhost on any student machine. In tandem, I didn’t assign a static IP address but opted for dynamic IP assignment. That means, the first step to securely copy the files requires you to find the assigned IP address. You can do that with the following Linux command:
ifconfig -a | grep 'inet[[:blank:]]' | head -1 | cut -c 14-30 |
It would return something like:
192.168.147.198 |
After you have discovered the IP address, you need to download PuTTy from their web site because includes the pscp (PuTTy Secure Copy) utility. I recommend you click on the MSI (Microsoft Installer Package) file, and install it on your Windows machine. As a rule, you should accept the default location, which is C:\Program Files\PuTTy.
While you could alter your system-level %PATH% environment variable after you install the software, I recommend you only include it in the %PATH% within the scope of a Command (cmd) shell. Navigate to your Windows Start and enter cmd in the search field. It should launch the Command Prompt terminal, which is a terminal emulator.
In the terminal editor, type the following case sensitive command to add the PuTTy directory to your path (yes, it’s case sensitive):
SET PATH=%PATH%;%ProgramFiles%\PuTTy |
Now, you can securely copy the files and directory structure from Linux to Windows with the following command (where you replace the traditional server name with the dynamically assigned IP address). You should also be in the target directory where you want the files and directories copied:
C:\Data\cit225>pscp -r student@192.168.147.198:/home/student/Data/cit225/mysql . |
After entering the password for the student on the Linux VM, you should see the following copy over:
Raiders2.png | 99 kB | 99.5 kB/s | ETA: 00:00:00 | 100% LordOfTheRings3.png | 119 kB | 119.8 kB/s | ETA: 00:00:00 | 100% HarryPotter4.png | 103 kB | 103.9 kB/s | ETA: 00:00:00 | 100% Raiders1.png | 92 kB | 92.4 kB/s | ETA: 00:00:00 | 100% Raiders3.png | 123 kB | 123.9 kB/s | ETA: 00:00:00 | 100% LordOfTheRings2.png | 111 kB | 111.7 kB/s | ETA: 00:00:00 | 100% LordOfTheRings1.png | 103 kB | 104.0 kB/s | ETA: 00:00:00 | 100% HarryPotter2.png | 118 kB | 118.7 kB/s | ETA: 00:00:00 | 100% HarryPotter7.png | 150 kB | 150.2 kB/s | ETA: 00:00:00 | 100% HarryPotter3.png | 106 kB | 106.1 kB/s | ETA: 00:00:00 | 100% HarryPotter5.png | 82 kB | 82.5 kB/s | ETA: 00:00:00 | 100% HarryPotter6.png | 129 kB | 129.9 kB/s | ETA: 00:00:00 | 100% HarryPotter1.png | 118 kB | 118.8 kB/s | ETA: 00:00:00 | 100% HarryPotter8.png | 150 kB | 150.9 kB/s | ETA: 00:00:00 | 100% HarryPotter8.txt | 8 kB | 8.5 kB/s | ETA: 00:00:00 | 100% HarryPotter3.txt | 5 kB | 5.8 kB/s | ETA: 00:00:00 | 100% HarryPotter5.txt | 7 kB | 7.9 kB/s | ETA: 00:00:00 | 100% HarryPotter1.txt | 6 kB | 6.6 kB/s | ETA: 00:00:00 | 100% HarryPotter2.txt | 7 kB | 7.8 kB/s | ETA: 00:00:00 | 100% Raiders3.txt | 5 kB | 5.6 kB/s | ETA: 00:00:00 | 100% HarryPotter4.txt | 7 kB | 7.5 kB/s | ETA: 00:00:00 | 100% HarryPotter7.txt | 5 kB | 5.4 kB/s | ETA: 00:00:00 | 100% HarryPotter6.txt | 7 kB | 7.4 kB/s | ETA: 00:00:00 | 100% LOTRFellowship.txt | 4 kB | 5.0 kB/s | ETA: 00:00:00 | 100% apply_store_base.sql | 1 kB | 1.6 kB/s | ETA: 00:00:00 | 100% query_membership.sql | 0 kB | 0.3 kB/s | ETA: 00:00:00 | 100% apply_mysql_lab1.sql | 1 kB | 1.9 kB/s | ETA: 00:00:00 | 100% configure_mysql_web.sql | 37 kB | 37.1 kB/s | ETA: 00:00:00 | 100% seed_mysql_store_ri2.sql | 58 kB | 58.5 kB/s | ETA: 00:00:00 | 100% cleanup_mysql_store.sql | 5 kB | 5.4 kB/s | ETA: 00:00:00 | 100% create_mysql_store_ri2.sq | 21 kB | 21.1 kB/s | ETA: 00:00:00 | 100% |
My students will need to repeat this step to transfer all of the sample PHP files that demonstrate web application patterns. They also need to inspect individual files to ensure any path referencing commands are manually converted to their new Windows equivalent.
They can move the physical files as the root superuser with the following pscp command provide you haven’t stored the files somewhere other than the default location:
C:\Data\temp>pscp -r root@192.168.147.198:/var/lib/mysql . |
As always, I hope this helps those trying to sort things out.
SQL Developer Error
It’s been a couple releases trying to fix the following error thrown by SQL Developer on Fedora 30 and shown as the following dialog:
When you click the Detail button it shows the following error stack:
java.lang.NoClassDefFoundError: javafx/embed/swing/JFXPanel at oracle.dbtools.raptor.javafx.ui.JFXPanelFactory.createJFXPanelImpl(JFXPanelFactory.java:58) at oracle.dbtools.raptor.javafx.ui.JFXPanelFactory.createJFXPanel(JFXPanelFactory.java:34) at oracle.dbtools.raptor.startpage.StartPageViewer.createGUIComponent(StartPageViewer.java:179) at oracle.dbtools.raptor.startpage.StartPageViewer.getEditorContent(StartPageViewer.java:136) at oracle.ide.editor.AsynchronousEditor$2.run(AsynchronousEditor.java:345) at oracle.ide.editor.AsynchronousEditor$5.run(AsynchronousEditor.java:555) at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1443) at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:68) at org.openide.util.lookup.Lookups.executeWith(Lookups.java:303) at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2058) Caused by: java.lang.ClassNotFoundException: javafx.embed.swing.JFXPanel cannot be found by oracle.sqldeveloper_19.2.0 at org.eclipse.osgi.internal.loader.BundleLoader.findClassInternal(BundleLoader.java:501) at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:421) at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:412) at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:107) at org.netbeans.modules.netbinox.NetbinoxLoader.loadClass(NetbinoxLoader.java:81) at java.lang.ClassLoader.loadClass(ClassLoader.java:352) ... 10 more |
I thought applying the Open Java FX package might fix the problem. I installed the package like the following:
yum install -y openjfx |
The installation log:
Last metadata expiration check: 4:03:29 ago on Tue 21 Apr 2020 06:42:26 PM MDT. Dependencies resolved. ============================================================================================= Package Architecture Version Repository Size ============================================================================================= Installing: openjfx x86_64 8.0.202-8.b07.fc30 updates 8.8 M Transaction Summary ============================================================================================= Install 1 Package Total download size: 8.8 M Installed size: 11 M Downloading Packages: openjfx-8.0.202-8.b07.fc30.x86_64.rpm 2.5 MB/s | 8.8 MB 00:03 --------------------------------------------------------------------------------------------- Total 2.1 MB/s | 8.8 MB 00:04 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : openjfx-8.0.202-8.b07.fc30.x86_64 1/1 Running scriptlet: openjfx-8.0.202-8.b07.fc30.x86_64 1/1 Verifying : openjfx-8.0.202-8.b07.fc30.x86_64 1/1 Installed: openjfx-8.0.202-8.b07.fc30.x86_64 Complete! |
After installing the software, I determined the new JAR files. Then, I added them to my $CLASSPATH environment variable, like:
export CLASSPATH=/usr/share/java/mysql-connector-java.jar:/usr/lib/jvm/openjfx/rt/lib/ext/fxrt.jar:/usr/lib/jvm/openjfx/rt/lib/jfxswt.jar:. |
While it appears to load faster with these JAR files, it still raises the same Dialog error. I simply have to continue to look for a complete fix.
Fedora 30 pgAdmin4 Install
Last September, the pgAdmin4 installation stymied me. I wasn’t sure what was I had done wrong in the installation but I was on a deadline to release my Fedora 30 Linux virtualization. That meant I had to move on and leave it for later. Today, I’m building the new image and returned to the task.
I installed pgadmin4 with the following command:
dnf -y install pgadmin4 |
The pgadmin4 configuration instructions can be found for several Linux versions at Josphat Mutai’s Computing for Geeks web page. On Fedora 30, you need to do the following:
- Install, start, and enable Apache as the
httpdservice unless you already have done that. - Copy the
/etc/httpd/conf.d/pgadmin4.conf.samplefile to/etc/httpd/conf.d/pgadmin4.conf, which is a new file. - Restart the
httpdservice to incorporate thepgadmin4configuration file.
After that, my instructions vary from the original web page because they didn’t work. You actually need to create four directories as the sudo or root user:
/var/lib/pgadmin4/var/lib/pgadmin4/sessions/var/lib/pgadmin4/storage/var/log/pgadmin4
You can make both directories with a single mkdir command, like:
mkdir -p /var/lib/pgadmin4 /var/lib/pgadmin4/sessions /var/lib/pgadmin4/storage /var/log/pgadmin4 |
As the root or sudo user, change the ownership of these two directories to the apache user with the following syntax:
chown -R apache:apache /var/lib/pgadmin4 /var/lib/pgadmin4/sessions /var/lib/pgadmin4/storage /var/log/pgadmin4 |
You add the following four statements to the config_distro.py file in the /usr/lib/python3.7/site-packages/pgadmin4-web directory as the root or sudo user:
LOG_FILE = '/var/log/pgadmin4/pgadmin4.log' SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db' SESSION_DB_PATH = '/var/lib/pgadmin4/sessions' STORAGE_DIR = '/var/lib/pgadmin4/storage' |
You need to setup the pgadmin user with the following python3 command:
python3 /usr/lib/python3.7/site-packages/pgadmin4-web/setup.py |
Enter the following values, a real email address and a password twice:
NOTE: Configuring authentication for SERVER mode. Enter the email address and password to use for the initial pgAdmin user account: Email address: admin@example.com Password: your_password Retype password: your_password pgAdmin 4 - Application Initialisation ====================================== |
Before you move on, you should check ownership of the pgadmin4 directories in the /var/lib and /var/log directories and their files by long listing them as follows:
- Check the
/var/libdirectory:ll /var/lib/pgadmin4
It should display:
total 148 -rw-r--r--. 1 root root 1296 Apr 11 12:12 my-httpd.pp -rw-r--r--. 1 root root 332 Apr 11 12:12 my-httpd.te -rw-------. 1 apache apache 131072 Apr 11 12:16 pgadmin4.db drwx------. 2 apache apache 4096 Apr 11 12:15 sessions drwxr-xr-x. 2 apache apache 4096 Apr 10 17:33 storage
- Check the
/var/logdirectory:ll /var/log/pgadmin4
It should display:
total 4 -rw-r--r--. 1 apache apache 1174 Apr 11 12:15 pgadmin4.log
Assuming you have an enabled firewall, you need to issue the following two commands as the root or sudo user:
firewall-cmd --permanent --add-service=http firewall-cmd --reload |
You invoke pgAdmin4 from within a browser window with the following URL for a stand alone workstation (for a workstation on a DNS network you would enter pgadmin.domain.domain_type in lieu of localhost):
pgadmin/localhost/pgadmin4 |
You most likely will encounter an Internal Server Error, the recommended fix is reputed to be:
ausearch -c 'httpd' --raw | audit2allow -M my-httpd semodule -i my-httpd.pp |
After completing the installation, you should be able to run pgadmin4, by typing in the following URL into a web browser:
http://localhost/pgadmin4 |
You should see the pgAmin4 web page if everything works. If it fails to launch, you should check the Apache error log. The error_log file is found in the /var/log/httpd directory. This is a type of error you may get if the ownership privileges aren’t assigned to the apache user and apache group.
Display sample log →
[Sat Apr 11 12:06:25.433570 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] mod_wsgi (pid=16086): Failed to exec Python script file '/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi'. [Sat Apr 11 12:06:25.433611 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] mod_wsgi (pid=16086): Exception occurred processing WSGI script '/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi'. [Sat Apr 11 12:06:25.433720 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] Traceback (most recent call last): [Sat Apr 11 12:06:25.433741 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi", line 36, in <module> [Sat Apr 11 12:06:25.433745 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] from pgAdmin4 import app as application [Sat Apr 11 12:06:25.433749 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.py", line 109, in <module> [Sat Apr 11 12:06:25.433752 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] app = create_app() [Sat Apr 11 12:06:25.433756 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgadmin/__init__.py", line 345, in create_app [Sat Apr 11 12:06:25.433759 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] os.chmod(config.SQLITE_PATH, 0o600) [Sat Apr 11 12:06:25.433768 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] PermissionError: [Errno 13] Permission denied: '/var/lib/pgadmin4/pgadmin4.db' [Sat Apr 11 12:06:28.234643 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] mod_wsgi (pid=16086): Failed to exec Python script file '/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi'. [Sat Apr 11 12:06:28.234694 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] mod_wsgi (pid=16086): Exception occurred processing WSGI script '/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi'. [Sat Apr 11 12:06:28.234781 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] Traceback (most recent call last): [Sat Apr 11 12:06:28.234816 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi", line 36, in <module> [Sat Apr 11 12:06:28.234820 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] from pgAdmin4 import app as application [Sat Apr 11 12:06:28.234824 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.py", line 109, in <module> [Sat Apr 11 12:06:28.234826 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] app = create_app() [Sat Apr 11 12:06:28.234830 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgadmin/__init__.py", line 345, in create_app [Sat Apr 11 12:06:28.234832 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] os.chmod(config.SQLITE_PATH, 0o600) [Sat Apr 11 12:06:28.234841 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] PermissionError: [Errno 13] Permission denied: '/var/lib/pgadmin4/pgadmin4.db' |
As always, I hope my notes are helpful to those who want to work with pgadmin4 and the PostgreSQL database.
Java & MySQL 8.0.19
It’s the in-between term time and we’re all stuck at home. I decided to update the image for my Fedora 30 virtual machine. I had a work around to the update issue that I had encountered last October in Bug #96969 but it was not required with the current version. However, after updating from MySQL 8.0.17 to MySQL 8.0.19, I found that my Java connection example failed.
The $CLASSPATH value was correct:
/usr/share/java/mysql-connector-java.jar:. |
The first error that I got was the my reference to MySQL JDBC driver was incorrect. The error message is quite clear:
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. 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. |
I changed the MySQL Driver reference as instructed by the error message:
29 30 31 | // Create instance of MySQLDriver. Class.forName ("com.mysql.cj.jdbc.Driver").newInstance(); conn = DriverManager.getConnection (url, username, password); |
After the change, I got the following error while retesting my little MySQL Java driver connection test program. Initially, I thought this required a change in the Java environment but that wasn’t it.
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. |
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' |
Then, running my MySQL Driver connection test program worked like a charm. It returns the following:
Database connection established MySQLDriver Version [8.0.19] Database connection terminated |
Here’s the MySQL Connector Java code if you’d like to use the MySQLDriver.java file:
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 | // Import classes. import java.sql.*; /* You can't include the following on Linux without raising an exception. */ // import com.mysql.jdbc.Driver; public class MySQLDriver { public MySQLDriver() { /* Declare variables that require explicit assignments because they're addressed in the finally block. */ Connection conn = null; Statement stmt = null; ResultSet rset = null; /* Declare other variables. */ String url; String username = "student"; String password = "student"; String database = "studentdb"; String hostname = "localhost"; String port = "3306"; String sql; /* Attempt a connection. */ try { // Set URL. url = "jdbc:mysql://" + hostname + ":" + port + "/" + database; // Create instance of MySQLDriver. Class.forName ("com.mysql.cj.jdbc.Driver").newInstance(); conn = DriverManager.getConnection (url, username, password); // Query the version of the database. sql = "SELECT version()"; stmt = conn.createStatement(); rset = stmt.executeQuery(sql); System.out.println ("Database connection established"); // Read row returns for one column. while (rset.next()) { System.out.println("MySQLDriver Version [" + rset.getString(1) + "]"); } } catch (SQLException e) { System.err.println ("Cannot connect to database server:"); System.out.println(e.getMessage()); } catch (ClassNotFoundException e) { System.err.println ("Cannot find MySQL driver class:"); System.out.println(e.getMessage()); } catch (InstantiationException e) { System.err.println ("Cannot instantiate class:"); System.out.println(e.getMessage()); } catch (IllegalAccessException e) { System.err.println ("Illegal access exception:"); System.out.println(e.getMessage()); } finally { if (conn != null) { try { rset.close(); stmt.close(); conn.close(); System.out.println ("Database connection terminated"); } catch (Exception e) { /* ignore close errors */ } } } } /* Unit test. */ public static void main(String args[]) { new MySQLDriver(); } } |
As always, I hope this helps those who encounter similar problems.
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 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.
PostgreSQL Upsert Intro
Oracle and SQL Server use the MERGE statement, MySQL uses the REPLACE INTO statement or ON DUPLICATE KEY, but PostgreSQL uses an upsert. The upsert isn’t a statement per se. It is like MySQL’s INSERT statement with the ON DUPLICATE KEY clause. PostgreSQL uses an ON CONFLICT clause in the INSERT statement and there anonymous block without the $$ delimiters.
The general behaviors of upserts is covered in the PostgreSQL Tutorial. It has the following prototype:
INSERT INTO TABLE_NAME(column_list) VALUES(value_list) ON CONFLICT target action; |
The target can be a column name, an ON CONSTRAINT constraint name, or a WHERE predicate, while the action can be DO NOTHING (or ignore) or a DO UPDATE statement. I wrote the following example to show how to leverage a unique constraint with a DO NOTHING and DO UPDATE behavior.
My example conditionally drops a table, creates a table with a unique constraint, inserts a few rows, updates with a DO UPDATE clause, updates with DO NOTHING clause, and queries the results with a bit of formatting.
- Conditionally drop the
testtable./* Suppress warnings from the log file. */ SET client_min_messages = 'error'; /* Conditionally drop table. */ DROP TABLE IF EXISTS test;
- Create the
testtable./* Create a test table. */ CREATE TABLE test ( test_id SERIAL , first_name VARCHAR(20) , middle_name VARCHAR(20) , last_name VARCHAR(20) , updated INTEGER DEFAULT 0 , update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP , CONSTRAINT test_uq_key UNIQUE (first_name,middle_name,last_name));
- Insert six rows into the
testtable./* Insert six rows. */ INSERT INTO test ( first_name, middle_name, last_name ) VALUES ('Harry','James','Potter') ,('Ginerva','Molly','Potter') ,('Lily','Luna','Potter') ,('Albus','Severus','Potter') ,('James',NULL,'Potter') ,('Lily',NULL,'Potter');
- Create a five second delay.
/* Sleep for 5 seconds. */ DO $$ BEGIN PERFORM pg_sleep(5); END; $$;
- Use the
INSERTstatement with aDO UPDATEclause that increments theupdatedcolumn of thetesttable./* Upsert on unique key constraint conflict. */ INSERT INTO test ( first_name , middle_name , last_name ) VALUES ('Harry' ,'James' ,'Potter') ON CONFLICT ON CONSTRAINT test_uq_key DO UPDATE SET updated = excluded.updated + 1 , update_time = CURRENT_TIMESTAMP;
- Use the
INSERTstatement with aDO NOTHINGclause./* Upsert on unique key constraint ignore update. */ INSERT INTO test ( first_name , middle_name , last_name ) VALUES ('Harry' ,'James' ,'Potter') ON CONFLICT ON CONSTRAINT test_uq_key DO NOTHING;
- Query the
testtable./* Formatted query to demonstrate result of UPSERT statement. */ SELECT test_id , last_name || ', ' || CASE WHEN middle_name IS NOT NULL THEN first_name || ' ' || middle_name ELSE first_name END AS full_name , updated , date_trunc('second',update_time AT TIME ZONE 'MST') AS "timestamp" FROM test ORDER BY last_name , first_name , CASE WHEN middle_name IS NOT NULL THEN middle_name ELSE 'A' END;
Display results:
test_id | full_name | updated | timestamp ---------+-----------------------+---------+--------------------- 4 | Potter, Albus Severus | 0 | 2019-11-24 19:23:10 2 | Potter, Ginerva Molly | 0 | 2019-11-24 19:23:10 1 | Potter, Harry James | 1 | 2019-11-24 19:23:15 5 | Potter, James | 0 | 2019-11-24 19:23:10 6 | Potter, Lily | 0 | 2019-11-24 19:23:10 3 | Potter, Lily Luna | 0 | 2019-11-24 19:23:10 (6 rows)
As always, I hope this helps those looking for clear examples to solve problems.
Subscribe via RSS