MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Unix’ Category

MySQL PNG Files

with one comment

LAMP (Linux, Apache, MySQL, Perl/PHP/Python) Architecture is very flexible. All the components can be positioned on the same server or different servers. The servers are divided into two types. The types are known as the Application or database tiers. Generally, the application tier holds the Apache Server, any Apache Modules, and local copies of Server Side Includes (SSI) programs.

In many development environments, you also deploy the client to the same machine. This means a single machine runs the database server, the application server, and the browser. The lab for this section assumes these configurations.

Before you test an installation, you should make sure that you’ve started the database and Apache server. In an Oracle LAMP configuration (known as an OLAP – Oracle, Linux, Apache, Perl/PHP/Python), you must start both the Oracle Listener and database. MySQL starts the listener when you start the database. You must also start the Apache Server. The Apache Server also starts an Apache Listener, which listens for incoming HTTP/HTTPS requests. It listens on Port 80 unless you override that setting in the httpd.conf file.

The URI reaches the server and is redirected to an Apache Module based on configuration information found in the httpd.conf file. Spawned or child processes of the Apache Module then read programs into memory from the file system and run them. If you’ve uploaded a file the locally stored program can move it from a secure cache location to another local area for processing. The started programs can run independently or include other files as libraries, and they can communicate to the database server.

Working though PHP test cases against the MySQL database for my AlmaLinux installation and configuration, I discovered that the php-gd library weren’t installed by default. I had to add it to get my PHP programs to upload and display PNG files.

The log file for applying the php-gd packages:

Display detailed console log →

Last metadata expiration check: 3:59:15 ago on Wed 28 Dec 2022 08:17:58 PM EST.
Dependencies resolved.
================================================================================
 Package         Architecture    Version               Repository          Size
================================================================================
Installing:
 php-gd          x86_64          8.0.20-3.el9          appstream           43 k
 
Transaction Summary
================================================================================
Install  1 Package
 
Total download size: 43 k
Installed size: 110 k
Downloading Packages:
php-gd-8.0.20-3.el9.x86_64.rpm                  196 kB/s |  43 kB     00:00    
--------------------------------------------------------------------------------
Total                                            39 kB/s |  43 kB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1 
  Installing       : php-gd-8.0.20-3.el9.x86_64                             1/1 
  Running scriptlet: php-gd-8.0.20-3.el9.x86_64                             1/1 
  Verifying        : php-gd-8.0.20-3.el9.x86_64                             1/1 
 
Installed:
  php-gd-8.0.20-3.el9.x86_64                                                    
 
Complete!

The balance of this page demonstrates how to upload, store, and manage Text (Character Large Data Streams) and BLOBs (Binary Large Objects). It provides MySQL equivalent instructions to those for manaing LOBs in an Oracle database. As covered in Chapter 8 in my Oracle Database 11g PL/SQL Programming book.

Before you begin these steps, you should have already installed Zend Server Community Edition. If you haven’t done so, please click here for instructions.

Create directories or folders, and position code →

This section provides you with instructions on how to position the code components in Windows, at least for the newbie. If you’re on Linux, you probably know how to do most if not all of this already. Likewise, if you already know how to put things in the right place, please choose your own locations.

  1. Create a LOB (Large Object) directory for the PHP files inside the htdocs directory.

oraclelobsetup1

    You can down the MySQL PHP Upload LOB Web Code zip file and unzip it into the directory you just created. It can co-exist with the Oracle equivalent if you’ve done that already.

Load a TEXT (like an Oracle CLOB) column to the MySQL database →

This is a copy of the three files required to load a large string to a MySQL database into a mediumtext data type. The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.

MySQLCredentials.inc

1
2
3
4
5
6
7

<?php
  // Connection variables.
  define('HOSTNAME',"localhost");
  define('USERNAME',"student");
  define('PASSWORD',"student");
  define('DATABASE',"sampledb");
?>

UploadItemDescMySQLForm.htm

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

<html>
<head>
<title>
  UploadItemDescMySQLForm.htm
</title>
</head>
<body>
<form id="uploadForm"
      action="UploadItemDescMySQL.php"
      enctype="multipart/form-data"
      method="post">
  <table border=0 cellpadding=0 cellspacing=0>
    <tr>
      <td width=125>Item Number</td>
      <td>
        <input id="id" name="id" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Item Title</td>
      <td>
        <input id="title" name="title" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Select File</td>
      <td>
        <input id="uploadfilename" name="userfile" type="file">
      </td>
    </tr>
    <tr>
      <td width=125>Click Button to</td>
      <td><input type="submit" value="Upload File"></td>
    </tr>
  </table>
</form>
</body>
</html>

UploadItemDescMySQL.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

<?php
  // Set database credentials.
  include_once("MySQLCredentials.inc");
 
  // Displayed moved file in web page.
  $item_desc = process_uploaded_file();
 
  // Return successful attempt to connect to the database.
  if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
    // Print user message.
    print "Sorry! The connection to the database failed. Please try again later.";
 
    // Assign the mysqli_error() and format double and single quotes.
    print mysqli_error();
 
    // Kill the resource.
    die();
  }
  else {
    // Declare input variables.
    $id = (isset($_POST['id']))    ? (int) $_POST['id'] : $id = 21;
    $title = (isset($_POST['title'])) ? $_POST['title'] : $title = "Harry #1";
 
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($c);
 
    // Declare a PL/SQL execution command.
    $sql = "Update item  set item_desc = ? where item_id = ?";
 
    // Prepate statement and link it to a connection.
    if (mysqli_stmt_prepare($stmt,$sql)) {
      mysqli_stmt_bind_param($stmt,"si",$item_desc,$id);
 
      // Execute it and print success or failure message.
      if (mysqli_stmt_execute($stmt)) {
        query_insert($id,$title);
      }
      else {
        print "You're target row doesn't exist.";
      }
    }
    // Disconnect from database.
    mysqli_close($c);
  }
 
  // Query results afret an insert.
  function query_insert($id,$title)
  {
    // Return successful attempt to connect to the database.
    if (!$c  = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
      // Print user message.
      print "Sorry! The connection to the database failed. Please try again later.";
 
      // Assign the OCI error and format double and single quotes.
      print mysqli_error();
 
      // Kill the resource.
      die();
    }
    else {
 
      // Initialize a statement in the scope of the connection.
      $stmt = mysqli_stmt_init($c);
 
      // Declare a SQL SELECT statement returning a CLOB.
      $sql = "SELECT item_desc FROM item WHERE item_id = ?";
 
      // Prepare statement.
      if (mysqli_stmt_prepare($stmt,$sql)) {
        mysqli_stmt_bind_param($stmt,"i",$id);
 
        // Execute it and print success or failure message.
        if (mysqli_stmt_execute($stmt)) {
 
          // Bind result to local variable.
	      mysqli_stmt_bind_result($stmt, $desc);
 
          // Read result.
          mysqli_stmt_fetch($stmt);
 
          // Format HTML table to display biography.
          $out = '<table border="1" cellpadding="3" cellspacing="0">';
          $out .= '<tr>';
          $out .= '<td align="center" class="e">'.$title.'</td>';
          $out .= '</tr>';
          $out .= '<tr>';
          $out .= '<td class="v">'.$desc.'</td>';
          $out .= '</tr>';
          $out .= '</table>';
 
          // Print the HTML table.
          print $out;
        }
      }
 
      // Disconnect from database.
      mysqli_close($c);
    }
  }
 
  // Manage file upload and return file as string.
  function process_uploaded_file()
  {
    // Declare a variable for file contents.
    $contents = "";
 
    // Define the upload file name for Windows or Linux.
    if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"]))
      $upload_file = "C:\\temp\\".$_FILES['userfile']['name'];
    else
      $upload_file = "/tmp/".$_FILES['userfile']['name'];
 
    // Check for and move uploaded file.
    if (is_uploaded_file($_FILES['userfile']['tmp_name']))
      move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file);
 
    // Open a file handle and suppress an error for a missing file.
    if ($fp = @fopen($upload_file,"r"))
    {
      // Read until the end-of-file marker.
      while (!feof($fp))
        $contents .= fgetc($fp);
      // Close an open file handle.
      fclose($fp);
    }
    // Return file content as string.
    return $contents;
  }
?>

Load a BLOB column to the MySQL database →

This is a copy of the four files required to load a large image to a MySQL database into a MEDIUMBLOB data type. The fourth file reads the binary image and translates it into an HTML header and image that can be read through a call to the src attribute of an img tag. You can find the call to the forth file in the UploadItemBlobMySQL.php.

The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.

MySQLCredentials.inc

1
2
3
4
5
6
7

<?php
  // Connection variables.
  define('HOSTNAME',"localhost");
  define('USERNAME',"student");
  define('PASSWORD',"student");
  define('DATABASE',"sampledb");
?>

UploadItemBlobMySQLForm.htm

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

<html>
<head>
<title>
  UploadItemBlobMySQLForm.htm
</title>
</head>
<body>
<form id="uploadForm"
      action="UploadItemBlobMySQL.php"
      enctype="multipart/form-data"
      method="post">
  <table border=0 cellpadding=0 cellspacing=0>
    <tr>
      <td width=125>Item Number</td>
      <td>
        <input id="id" name="id" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Item Title</td>
      <td>
        <input id="title" name="title" type="text">
      </td>
    </tr>
    <tr>
      <td width=125>Select File</td>
      <td>
        <input id="uploadfilename" name="userfile" type="file">
      </td>
    </tr>
    <tr>
      <td width=125>Click Button to</td>
      <td><input type="submit" value="Upload File"></td>
    </tr>
  </table>
</form>
</body>
</html>

UploadItemBlobMySQL.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

<?php
  // Set database credentials.
  include_once("MySQLCredentials.inc");
 
  // Displayed moved file in web page.
  $item_blob = process_uploaded_file();
 
  // Return successful attempt to connect to the database.
  if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
    // Print user message.
    print "Sorry! The connection to the database failed. Please try again later.";
 
    // Assign the mysqli_error() error and format double and single quotes.
    print mysqli_error();
 
    // Kill the resource.
    die();
  }
  else {
    // Declare input variables.
    $id = (isset($_POST['id'])) ? (int) $_POST['id'] : 1021;
    $title = (isset($_POST['title'])) ? $_POST['title'] : "Harry #1";
 
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($c);
 
   // Declare a PL/SQL execution command.
    $sql = "UPDATE item SET item_blob = ? WHERE item_id =  ?";
 
    // Prepare statement and link it to a connection.
    if (mysqli_stmt_prepare($stmt,$sql)) {
      mysqli_stmt_bind_param($stmt,"bi",$item_blob,$id);
 
      $start = 0;
      $chunk = 8192;
      while ($start < strlen($item_blob)) {
        mysqli_stmt_send_long_data($stmt,0,substr($item_blob,$start,$chunk));
        $start += $chunk;
      }
 
      // Execute the PL/SQL statement.
      if (mysqli_stmt_execute($stmt)) {
        query_insert($id,$title);
      }
      else {
        print "Your target row doesn't exist.";
      }
    }
    else { print "mysqli_stmt_prepare() failed."; }
    // Disconnect from database.
    mysqli_close($c);
  }
 
  // Query results afret an insert.
  function query_insert($id,$title)
  {
    // Return successful attempt to connect to the database.
    if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) {
      // Print user message.
      print "Sorry! The connection to the database failed. Please try again later.";
 
      // Assign the OCI error and format double and single quotes.
      print mysqli_error();
 
      // Kill the resource.
      die();
    }
    else {
 
      // Initialize a statement in the scope of the connection.
      $stmt = mysqli_stmt_init($c);
 
      // Declare a SQL SELECT statement returning a CLOB.
      $sql = "SELECT item_desc FROM item WHERE item_id = ?";
 
      // Prepare statement and link it to a connection.
      if (mysqli_stmt_prepare($stmt,$sql)) {
        mysqli_stmt_bind_param($stmt,"i",$id);
 
        // Execute the PL/SQL statement.
        if (mysqli_stmt_execute($stmt)) {
 
          // Bind result to local variable.
	      mysqli_stmt_bind_result($stmt, $data);
 
          // Read result.
          mysqli_stmt_fetch($stmt);
 
          // Format HTML table to display BLOB photo and CLOB description.
          $out = '<table border="1" cellpadding="5" cellspacing="0">';
          $out .= '<tr>';
          $out .= '<td align="center" class="e">'.$title.'</td>';
          $out .= '</tr>';
          $out .= '<tr><td class="v">';
          $out .= '<div>';
          $out .= '<div style="margin-right:5px;float:left">';
          $out .= '<img src="ConvertMySQLBlobToImage.php?id='.$id.'">';
          $out .= '</div>';
          $out .= '<div style="position=relative;">'.$data.'</div>';
          $out .= '</div>';
          $out .= '</td></tr>';
          $out .= '</table>';
 
          // Print the HTML table.
          print $out;
        }
        else {
          print "You're target row doesn't exist.";
        }
      }
 
      // Disconnect from database.
      mysqli_close($c);
    }
  }
 
  // Manage file upload and return file as string.
  function process_uploaded_file()
  {
    // Declare a variable for file contents.
    $contents = "";
 
    // Define the upload file name for Windows or Linux.
    if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"]))
      $upload_file = "C:\\TEMP\\".$_FILES['userfile']['name'];
    else
      $upload_file = "/tmp/".$_FILES['userfile']['name'];
 
    // Check for and move uploaded file.
    if (is_uploaded_file($_FILES['userfile']['tmp_name']))
      move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file);
 
    // Open a file handle and suppress an error for a missing file.
    if ($fp = @fopen($upload_file,"r"))
    {
      // Read until the end-of-file marker.
      while (!feof($fp))
        $contents .= fgetc($fp);
 
      // Close an open file handle.
      fclose($fp);
    }
 
    // Return file content as string.
    return $contents;
  }
?>

ConvertMySQLBlobToImage.php

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

<?php
  // Database credentials must be set manually because an include_once() function
  // call puts something ahead of the header, which causes a failure when rendering
  // an image.
 
  // Return successful attempt to connect to the database.
  if (!$c = @mysqli_connect("localhost","student","student","sampledb")) {
 
    // Print user message.
    print "Sorry! The connection to the database failed. Please try again later.";
 
    // Assign the OCI error and format double and single quotes.
    print mysqli_error();
 
    // Kill the resource.
    die();
  }
  else {
 
    // Declare input variables.
    $id = (isset($_GET['id'])) ? (int) $_GET['id'] : 1023;
 
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($c);
 
    // Declare a SQL SELECT statement returning a MediumBLOB.
    $sql = "SELECT item_blob FROM item WHERE item_id = ?";
 
    // Prepare statement and link it to a connection.
    if (mysqli_stmt_prepare($stmt,$sql)) {
      mysqli_stmt_bind_param($stmt,"i",$id);
 
      // Execute the PL/SQL statement.
      if (mysqli_stmt_execute($stmt)) {
 
        // Bind result to local variable.
	      mysqli_stmt_bind_result($stmt, $image);
 
        // Read result.
        mysqli_stmt_fetch($stmt);
      }
    } 
 
    // Disconnect from database.
    mysqli_close($c);
 
    // Print the header first.
    header('Content-type: image/x-png');
    imagepng(imagecreatefromstring($image));
  }
?>
  1. Create a temp directory for the upload target location, as qualified in the PHP code. The PHP code works regardless of whether you’re on Windows or Linux, but it does depend on the creation of this directory.

oraclelobsetup2

  1. Create a directory or folder for the large file source directories. This directory is probably on your test machine (laptop) but it mimics a client laptop and would work if your server was on a different machine.

oraclelobsetup3

  1. Inside the Upload directory, you should create the following two directories:

oraclelobsetup4

    You should download the CLOB Text File zip file and unzip it into the textfiles directory; then download the BLOB Image File zip file and unzip it into the imagefiles directory.

Assuming you’ve downloaded the zip files and extracted them into the correct locations, this section is done.

Prepare the MySQL database →

This section provides you with instructions on how to ensure everything will work once the PHP programs call the database. Even if you have one of my sample Video Store databases, you should verify and add appropriate columns. This post assumes you’ve downloaded the one of my basic Video Store models

  1. Navigate to the directory that you created for SQL scripts, which should be /home/student/Data/mysql. In that directory at the command prompt, connect as the student user, which should be student. You connect to the MySQL database, with the following syntax as student (if you need more help, check this blog post on configuring MySQL).
mysql -ustudent -pstudent

Once connected to the database, you run the files to create the database, like:

mysql> source /Data/mysql/create_mysql_store.sql
mysql> source /Data/mysql/seed_mysql_store.sql
  1. Navigate to the directory that you created for SQL scripts, which should be /home/student/Data/mysql. In that directory at the command prompt, connect as the student user, or whichever account you’re using. You should confirm that you have a item_desc column of TEXT data type, and an item_blob column of MEDIUMBLOB type in the item table. If you don’t have those columns, you can add them with the following statement:
ALTER TABLE item
ADD (item_desc TEXT, item_blob MEDIUMBLOB);

After ensuring that you have those two columns, you’ve completed this section.

Test the Configuration →

This section shows you how to test all that you’ve done. It works provided you created the directories and extracted the zip file contents to their respective directories. The virtual URL actually maps to the /var/www/html/lob directory.

  1. Enter the http://localhost/lob/UploadItemDescMySQLForm.htm URL, and complete the form by choosing a valid item_id column value and text file from your /home/student/Upload/TextFiles directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).

mysql_lob1

  1. This page displays after you successfully upload the text file to the database.

mysql_lob2

  1. Enter the http://localhost/lob/UploadItemBlobFormMySQL.htm URL, and complete the form by choosing a valid item_id column value and image file from your /home/student/Upload/ImageFiles directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).

mysql_lob3

  1. This page displays after you successfully upload the image file to the database.

mysql_lob4

Troubleshooting the Configuration →

This section shows you how to check why something isn’t working.

  1. The first thing to check are the credentials. They’re in the MySQLCredentials.inc file. They’re posted with a localhost machine name, student username, student password, and sampledb database.
  1. Not to be funny, but the second thing to check are credentials. Specifically, you need to check the credentials in the ConvertBlobToImage.php file. They’re individually entered in the connect string of this file because otherwise they put something in front of the header, which is disallowed to render the image.
  1. Check to see if the text or image file made it to the /var/www/html/lob/temp directory. If they made it that far but no further, check to see if you have valid procedures in the student schema.
  1. Check whether the TEXT and MEDIUMBLOB are loaded into the database. You use the LENGTH function, like this:
SELECT   i.item_id
,        length(i.item_desc)
,        length(i.item_blob)
FROM     item i
WHERE    i.item_desc IS NOT NULL
OR       i.item_blob IS NOT NULL;
  1. Check if the item_id value is found in the list of values.
  1. If you’re stumped, add a comment and explain what’s up.

If you find any problems, please let me know. I’ll be happy to fix them.

Written by maclochlainn

December 28th, 2022 at 10:59 pm

Posted in Database,LAMP,Linux,MySQL,MySQL 8,PHP,Unix

Tagged with , ,

AlmaLinux MySQL Workbench

without comments

AlmaLinux doesn’t natively support MySQL Workbench but these notes will help you install it. The great news is that MySQL Workbench works perfectly once you’ve installed all the dependent libraries. It’ll look like the following:

Disclaimer of sorts:

AlmaLinux is an open-source, community-driven project that intends to fill the gap left by the demise of the CentOS stable release. AlmaLinux is a 1:1 binary compatible fork of RHEL® 9 and it is built by the AlmaLinux OS Foundation as a standalone, completely free OS. The AlmaLinux OS Foundation will support future RHEL® releases by updating AlmaLinux. Ongoing development efforts are governed by the members of the community.

You can download MySQL Workbench from the following website:

https://dev.mysql.com/downloads/workbench

When you open this page, select the Red Hat Enterprise Linux 9 / Oracle Linux 9 (x86, 64-bit), RPM Package from the dropdown menu. Then, click the Download button. You may be prompted for your credentials or to create new credentials, but you can skip that by clicking on the No thanks, just start my download link.

When the download completes, open a terminal session as the student user. Navigate to the Downloads directory with the following command:

cd $HOME/Downloads

List the files in the $HOME/Downloads directory and you should see:

mysql-workbench-community-8.0.31-1.el9.x86_64.rpm

As the sudoer user or root, run the following command (naturally, exclude sudo if you’re the root user):

sudo dnf install -y mysql-workbench-community-8.0.31-1.el9.x86_64.rpm

It will most likely fail with an error message like this:

Last metadata expiration check: 2:50:04 ago on Thu 17 Nov 2022 09:33:15 AM EST.
Error: 
 Problem: conflicting requests
  - nothing provides gtkmm30-devel needed by mysql-workbench-community-8.0.31-1.el9.src
  - nothing provides libzip-devel needed by mysql-workbench-community-8.0.31-1.el9.src
  - nothing provides proj-devel needed by mysql-workbench-community-8.0.31-1.el9.src
  - nothing provides swig >= 3.0 needed by mysql-workbench-community-8.0.31-1.el9.src
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)

AlmaLinux doesn’t install these prerequisite packages. You’ll need to resolve these dependencies by installing them in the right order and groups before you can run the MySQL Workbench packages.

You can discover missing packages at the pkgs.org website. You need to resolve all four prerequisites before installing MySQL Workbench.

  1. Let’s start with the gtkmm30-devel package, which has eight separate dependencies. Assuming you’re still in your $HOME/Downloads directory, you can run the following command to get the gtkmm30-devel for AlmaLinux 9: 

    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/gtkmm30-devel-3.24.5-1.el9.x86_64.rpm

    It downloads the following package:

    gtkmm30-devel-3.24.5-1.el9.x86_64.rpm

    If you attempt to run it, the gtkmm30-devel package raises the following errors:

    sudo dnf install -y gtkmm30-devel-3.24.5-1.el9.x86_64.rpm
Last metadata expiration check: 0:41:13 ago on Thu 17 Nov 2022 02:39:59 PM EST.
Error: 
 Problem: conflicting requests
  - nothing provides pkgconfig(atkmm-1.6) >= 2.24.2 needed by gtkmm30-devel-3.24.5-1.el9.x86_64
  - nothing provides pkgconfig(cairomm-1.0) >= 1.12.0 needed by gtkmm30-devel-3.24.5-1.el9.x86_64
  - nothing provides pkgconfig(giomm-2.4) >= 2.54.0 needed by gtkmm30-devel-3.24.5-1.el9.x86_64
  - nothing provides pkgconfig(pangomm-1.4) >= 1.12.0 needed by gtkmm30-devel-3.24.5-1.el9.x86_64
(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)

    While you only get four errors, there are more packages required. You need to use the wget utility to download these packages. I would recommend you create a temporary gtkmm30 subdirectory inside your $HOME/Downloads directory and change to that directory before downloading these files.

    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/atkmm-devel-2.28.2-2.el9.x86_64.rpm
wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/cairomm-devel-1.14.2-10.el9.x86_64.rpm
wget https://repo.almalinux.org/almalinux/9/AppStream/x86_64/os/Packages/gdk-pixbuf2-devel-2.42.6-2.el9.x86_64.rpm
wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/glibmm24-devel-2.66.1-1.el9.x86_64.rpm
wget https://repo.almalinux.org/almalinux/9/AppStream/x86_64/os/Packages/gtk3-devel-3.24.31-2.el9.x86_64.rpm
wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/pangomm-devel-2.46.1-1.el9.x86_64.rpm
wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/libsigc++20-devel-2.10.7-2.el9.x86_64.rpm

    You need to run these as a set of prerequisites, so from your gtkmm30 subdirectory use the following dnf command as the sudoer user:

    sudo dnf install -y *.rpm

    The log file for this is:

    Display detailed console log →

    Last metadata expiration check: 0:09:20 ago on Sun 20 Nov 2022 12:52:28 AM EST.
Dependencies resolved.
================================================================================
 Package                  Arch      Version               Repository       Size
================================================================================
Installing:
 atkmm-devel              x86_64    2.28.2-2.el9          @commandline     45 k
 cairomm-devel            x86_64    1.14.2-10.el9         @commandline     62 k
 gdk-pixbuf2-devel        x86_64    2.42.6-2.el9          @commandline     64 k
 glibmm24-devel           x86_64    2.66.1-1.el9          @commandline    497 k
 gtk3-devel               x86_64    3.24.31-2.el9         @commandline    4.1 M
 libsigc++20-devel        x86_64    2.10.7-2.el9          @commandline     67 k
 pangomm-devel            x86_64    2.46.1-1.el9          @commandline     65 k
Upgrading:
 dbus-common              noarch    1:1.12.20-6.el9       baseos           14 k
 dbus-daemon              x86_64    1:1.12.20-6.el9       appstream       196 k
 dbus-libs                x86_64    1:1.12.20-6.el9       baseos          151 k
 dbus-tools               x86_64    1:1.12.20-6.el9       baseos           50 k
 fontconfig               x86_64    2.14.0-1.el9          appstream       274 k
 freetype                 x86_64    2.10.4-9.el9          baseos          387 k
 fribidi                  x86_64    1.0.10-6.el9.2        appstream        84 k
 harfbuzz                 x86_64    2.7.4-8.el9           baseos          624 k
 harfbuzz-icu             x86_64    2.7.4-8.el9           appstream        14 k
 libblkid                 x86_64    2.37.4-9.el9          baseos          107 k
 libfdisk                 x86_64    2.37.4-9.el9          baseos          154 k
 libmount                 x86_64    2.37.4-9.el9          baseos          133 k
 libselinux               x86_64    3.4-3.el9             baseos           85 k
 libselinux-utils         x86_64    3.4-3.el9             baseos          158 k
 libsepol                 x86_64    3.4-1.1.el9           baseos          315 k
 libsmartcols             x86_64    2.37.4-9.el9          baseos           63 k
 libtiff                  x86_64    4.4.0-2.el9           appstream       195 k
 libuuid                  x86_64    2.37.4-9.el9          baseos           27 k
 libxml2                  x86_64    2.9.13-2.el9          baseos          746 k
 pcre2                    x86_64    10.40-2.el9           baseos          236 k
 pcre2-syntax             noarch    10.40-2.el9           baseos          143 k
 pcre2-utf16              x86_64    10.40-2.el9           appstream       216 k
 pcre2-utf32              x86_64    10.40-2.el9           appstream       205 k
 python3-libselinux       x86_64    3.4-3.el9             appstream       185 k
 python3-libxml2          x86_64    2.9.13-2.el9          baseos          226 k
 util-linux               x86_64    2.37.4-9.el9          baseos          2.2 M
 util-linux-core          x86_64    2.37.4-9.el9          baseos          434 k
 util-linux-user          x86_64    2.37.4-9.el9          baseos           30 k
Installing dependencies:
 at-spi2-atk-devel        x86_64    2.38.0-4.el9          appstream       9.5 k
 at-spi2-core-devel       x86_64    2.40.3-1.el9          appstream       134 k
 atk-devel                x86_64    2.36.0-5.el9          appstream       172 k
 brotli                   x86_64    1.0.9-6.el9           appstream       313 k
 brotli-devel             x86_64    1.0.9-6.el9           appstream        31 k
 bzip2-devel              x86_64    1.0.8-8.el9           appstream       213 k
 cairo-devel              x86_64    1.17.4-7.el9          appstream       190 k
 cairo-gobject-devel      x86_64    1.17.4-7.el9          appstream        10 k
 dbus-devel               x86_64    1:1.12.20-6.el9       appstream        33 k
 fontconfig-devel         x86_64    2.14.0-1.el9          appstream       128 k
 freetype-devel           x86_64    2.10.4-9.el9          appstream       1.1 M
 fribidi-devel            x86_64    1.0.10-6.el9.2        appstream        25 k
 glib2-devel              x86_64    2.68.4-5.el9          appstream       475 k
 graphite2-devel          x86_64    1.3.14-9.el9          appstream        21 k
 harfbuzz-devel           x86_64    2.7.4-8.el9           appstream       305 k
 libX11-devel             x86_64    1.7.0-7.el9           appstream       940 k
 libXau-devel             x86_64    1.0.9-8.el9           appstream        13 k
 libXcomposite-devel      x86_64    0.4.5-7.el9           appstream        15 k
 libXcursor-devel         x86_64    1.2.0-7.el9           appstream        21 k
 libXdamage-devel         x86_64    1.1.5-7.el9           appstream       9.3 k
 libXext-devel            x86_64    1.3.4-8.el9           appstream        72 k
 libXfixes-devel          x86_64    5.0.3-16.el9          appstream        12 k
 libXft-devel             x86_64    2.3.3-8.el9           appstream        18 k
 libXi-devel              x86_64    1.7.10-8.el9          appstream        99 k
 libXinerama-devel        x86_64    1.1.4-10.el9          appstream        13 k
 libXrandr-devel          x86_64    1.5.2-8.el9           appstream        19 k
 libXrender-devel         x86_64    0.9.10-16.el9         appstream        16 k
 libXtst-devel            x86_64    1.2.3-16.el9          appstream        15 k
 libblkid-devel           x86_64    2.37.4-9.el9          appstream        17 k
 libdatrie-devel          x86_64    0.2.13-4.el9          appstream       132 k
 libepoxy-devel           x86_64    1.5.5-4.el9           appstream       133 k
 libffi-devel             x86_64    3.4.2-7.el9           appstream        29 k
 libglvnd-core-devel      x86_64    1:1.3.4-1.el9         appstream        17 k
 libglvnd-devel           x86_64    1:1.3.4-1.el9         appstream       155 k
 libicu-devel             x86_64    67.1-9.el9            appstream       830 k
 libmount-devel           x86_64    2.37.4-9.el9          appstream        18 k
 libpng-devel             x86_64    2:1.6.37-12.el9       appstream       290 k
 libselinux-devel         x86_64    3.4-3.el9             appstream       113 k
 libsepol-devel           x86_64    3.4-1.1.el9           appstream        40 k
 libthai-devel            x86_64    0.1.28-8.el9          appstream       117 k
 libtiff-devel            x86_64    4.4.0-2.el9           appstream       513 k
 libxcb-devel             x86_64    1.13.1-9.el9          appstream       1.0 M
 libxkbcommon-devel       x86_64    1.0.3-4.el9           appstream        61 k
 libxml2-devel            x86_64    2.9.13-2.el9          appstream       828 k
 pango-devel              x86_64    1.48.7-2.el9          appstream       141 k
 pcre-cpp                 x86_64    8.44-3.el9.3          appstream        26 k
 pcre-devel               x86_64    8.44-3.el9.3          appstream       470 k
 pcre-utf16               x86_64    8.44-3.el9.3          appstream       184 k
 pcre-utf32               x86_64    8.44-3.el9.3          appstream       174 k
 pcre2-devel              x86_64    10.40-2.el9           appstream       474 k
 perl-Filter              x86_64    2:1.60-4.el9          appstream        81 k
 perl-encoding            x86_64    4:3.00-462.el9        appstream        62 k
 perl-open                noarch    1.12-479.el9          appstream        25 k
 pixman-devel             x86_64    0.40.0-5.el9          appstream        17 k
 sysprof-capture-devel    x86_64    3.40.1-3.el9          appstream        59 k
 wayland-devel            x86_64    1.19.0-4.el9          appstream       132 k
 xorg-x11-proto-devel     noarch    2021.4-2.el9          appstream       262 k
 
Transaction Summary
================================================================================
Install  64 Packages
Upgrade  28 Packages
 
Total size: 23 M
Total download size: 18 M
Downloading Packages:
(1/85): at-spi2-atk-devel-2.38.0-4.el9.x86_64.r  38 kB/s | 9.5 kB     00:00    
(2/85): atk-devel-2.36.0-5.el9.x86_64.rpm       334 kB/s | 172 kB     00:00    
(3/85): brotli-devel-1.0.9-6.el9.x86_64.rpm     354 kB/s |  31 kB     00:00    
(4/85): at-spi2-core-devel-2.40.3-1.el9.x86_64. 167 kB/s | 134 kB     00:00    
(5/85): cairo-devel-1.17.4-7.el9.x86_64.rpm     1.9 MB/s | 190 kB     00:00    
(6/85): cairo-gobject-devel-1.17.4-7.el9.x86_64 179 kB/s |  10 kB     00:00    
(7/85): brotli-1.0.9-6.el9.x86_64.rpm           413 kB/s | 313 kB     00:00    
(8/85): dbus-devel-1.12.20-6.el9.x86_64.rpm     549 kB/s |  33 kB     00:00    
(9/85): bzip2-devel-1.0.8-8.el9.x86_64.rpm      505 kB/s | 213 kB     00:00    
(10/85): fribidi-devel-1.0.10-6.el9.2.x86_64.rp 492 kB/s |  25 kB     00:00    
(11/85): fontconfig-devel-2.14.0-1.el9.x86_64.r 891 kB/s | 128 kB     00:00    
(12/85): graphite2-devel-1.3.14-9.el9.x86_64.rp 316 kB/s |  21 kB     00:00    
(13/85): glib2-devel-2.68.4-5.el9.x86_64.rpm    1.1 MB/s | 475 kB     00:00    
(14/85): harfbuzz-devel-2.7.4-8.el9.x86_64.rpm  850 kB/s | 305 kB     00:00    
(15/85): freetype-devel-2.10.4-9.el9.x86_64.rpm 1.7 MB/s | 1.1 MB     00:00    
(16/85): libXau-devel-1.0.9-8.el9.x86_64.rpm    170 kB/s |  13 kB     00:00    
(17/85): libXcomposite-devel-0.4.5-7.el9.x86_64 273 kB/s |  15 kB     00:00    
(18/85): libXcursor-devel-1.2.0-7.el9.x86_64.rp 344 kB/s |  21 kB     00:00    
(19/85): libXdamage-devel-1.1.5-7.el9.x86_64.rp 133 kB/s | 9.3 kB     00:00    
(20/85): libXfixes-devel-5.0.3-16.el9.x86_64.rp 256 kB/s |  12 kB     00:00    
(21/85): libXext-devel-1.3.4-8.el9.x86_64.rpm   768 kB/s |  72 kB     00:00    
(22/85): libXft-devel-2.3.3-8.el9.x86_64.rpm    324 kB/s |  18 kB     00:00    
(23/85): libXinerama-devel-1.1.4-10.el9.x86_64. 205 kB/s |  13 kB     00:00    
(24/85): libXi-devel-1.7.10-8.el9.x86_64.rpm    913 kB/s |  99 kB     00:00    
(25/85): libXrender-devel-0.9.10-16.el9.x86_64. 295 kB/s |  16 kB     00:00    
(26/85): libXrandr-devel-1.5.2-8.el9.x86_64.rpm 289 kB/s |  19 kB     00:00    
(27/85): libXtst-devel-1.2.3-16.el9.x86_64.rpm  261 kB/s |  15 kB     00:00    
(28/85): libblkid-devel-2.37.4-9.el9.x86_64.rpm 284 kB/s |  17 kB     00:00    
(29/85): libX11-devel-1.7.0-7.el9.x86_64.rpm    1.4 MB/s | 940 kB     00:00    
(30/85): libepoxy-devel-1.5.5-4.el9.x86_64.rpm  1.2 MB/s | 133 kB     00:00    
(31/85): libdatrie-devel-0.2.13-4.el9.x86_64.rp 876 kB/s | 132 kB     00:00    
(32/85): libffi-devel-3.4.2-7.el9.x86_64.rpm    426 kB/s |  29 kB     00:00    
(33/85): libglvnd-core-devel-1.3.4-1.el9.x86_64 233 kB/s |  17 kB     00:00    
(34/85): libmount-devel-2.37.4-9.el9.x86_64.rpm 280 kB/s |  18 kB     00:00    
(35/85): libglvnd-devel-1.3.4-1.el9.x86_64.rpm  976 kB/s | 155 kB     00:00    
(36/85): libpng-devel-1.6.37-12.el9.x86_64.rpm  1.9 MB/s | 290 kB     00:00    
(37/85): libselinux-devel-3.4-3.el9.x86_64.rpm  801 kB/s | 113 kB     00:00    
(38/85): libsepol-devel-3.4-1.1.el9.x86_64.rpm  636 kB/s |  40 kB     00:00    
(39/85): libthai-devel-0.1.28-8.el9.x86_64.rpm  630 kB/s | 117 kB     00:00    
(40/85): libicu-devel-67.1-9.el9.x86_64.rpm     1.3 MB/s | 830 kB     00:00    
(41/85): libtiff-devel-4.4.0-2.el9.x86_64.rpm   1.6 MB/s | 513 kB     00:00    
(42/85): libxkbcommon-devel-1.0.3-4.el9.x86_64. 710 kB/s |  61 kB     00:00    
(43/85): pango-devel-1.48.7-2.el9.x86_64.rpm    914 kB/s | 141 kB     00:00    
(44/85): pcre-cpp-8.44-3.el9.3.x86_64.rpm       425 kB/s |  26 kB     00:00    
(45/85): pcre-devel-8.44-3.el9.3.x86_64.rpm     1.8 MB/s | 470 kB     00:00    
(46/85): pcre-utf16-8.44-3.el9.3.x86_64.rpm     1.5 MB/s | 184 kB     00:00    
(47/85): libxml2-devel-2.9.13-2.el9.x86_64.rpm  1.2 MB/s | 828 kB     00:00    
(48/85): libxcb-devel-1.13.1-9.el9.x86_64.rpm   1.2 MB/s | 1.0 MB     00:00    
(49/85): pcre-utf32-8.44-3.el9.3.x86_64.rpm     1.4 MB/s | 174 kB     00:00    
(50/85): perl-Filter-1.60-4.el9.x86_64.rpm      704 kB/s |  81 kB     00:00    
(51/85): perl-encoding-3.00-462.el9.x86_64.rpm  916 kB/s |  62 kB     00:00    
(52/85): perl-open-1.12-479.el9.noarch.rpm      476 kB/s |  25 kB     00:00    
(53/85): pixman-devel-0.40.0-5.el9.x86_64.rpm   272 kB/s |  17 kB     00:00    
(54/85): sysprof-capture-devel-3.40.1-3.el9.x86 797 kB/s |  59 kB     00:00    
(55/85): pcre2-devel-10.40-2.el9.x86_64.rpm     1.3 MB/s | 474 kB     00:00    
(56/85): wayland-devel-1.19.0-4.el9.x86_64.rpm  1.0 MB/s | 132 kB     00:00    
(57/85): xorg-x11-proto-devel-2021.4-2.el9.noar 1.3 MB/s | 262 kB     00:00    
(58/85): dbus-daemon-1.12.20-6.el9.x86_64.rpm   1.3 MB/s | 196 kB     00:00    
(59/85): fontconfig-2.14.0-1.el9.x86_64.rpm     1.7 MB/s | 274 kB     00:00    
(60/85): harfbuzz-icu-2.7.4-8.el9.x86_64.rpm    217 kB/s |  14 kB     00:00    
(61/85): fribidi-1.0.10-6.el9.2.x86_64.rpm      700 kB/s |  84 kB     00:00    
(62/85): libtiff-4.4.0-2.el9.x86_64.rpm         1.5 MB/s | 195 kB     00:00    
(63/85): pcre2-utf32-10.40-2.el9.x86_64.rpm     1.2 MB/s | 205 kB     00:00    
(64/85): python3-libselinux-3.4-3.el9.x86_64.rp 1.5 MB/s | 185 kB     00:00    
(65/85): pcre2-utf16-10.40-2.el9.x86_64.rpm     995 kB/s | 216 kB     00:00    
(66/85): dbus-common-1.12.20-6.el9.noarch.rpm   203 kB/s |  14 kB     00:00    
(67/85): dbus-tools-1.12.20-6.el9.x86_64.rpm    856 kB/s |  50 kB     00:00    
(68/85): dbus-libs-1.12.20-6.el9.x86_64.rpm     1.3 MB/s | 151 kB     00:00    
(69/85): libblkid-2.37.4-9.el9.x86_64.rpm       1.6 MB/s | 107 kB     00:00    
(70/85): libfdisk-2.37.4-9.el9.x86_64.rpm       1.6 MB/s | 154 kB     00:00    
(71/85): freetype-2.10.4-9.el9.x86_64.rpm       1.5 MB/s | 387 kB     00:00    
(72/85): libmount-2.37.4-9.el9.x86_64.rpm       1.3 MB/s | 133 kB     00:00    
(73/85): libselinux-3.4-3.el9.x86_64.rpm        820 kB/s |  85 kB     00:00    
(74/85): harfbuzz-2.7.4-8.el9.x86_64.rpm        1.4 MB/s | 624 kB     00:00    
(75/85): libselinux-utils-3.4-3.el9.x86_64.rpm  1.4 MB/s | 158 kB     00:00    
(76/85): libuuid-2.37.4-9.el9.x86_64.rpm        448 kB/s |  27 kB     00:00    
(77/85): libsmartcols-2.37.4-9.el9.x86_64.rpm   793 kB/s |  63 kB     00:00    
(78/85): libsepol-3.4-1.1.el9.x86_64.rpm        1.6 MB/s | 315 kB     00:00    
(79/85): pcre2-syntax-10.40-2.el9.noarch.rpm    1.2 MB/s | 143 kB     00:00    
(80/85): pcre2-10.40-2.el9.x86_64.rpm           1.6 MB/s | 236 kB     00:00    
(81/85): python3-libxml2-2.9.13-2.el9.x86_64.rp 1.4 MB/s | 226 kB     00:00    
(82/85): libxml2-2.9.13-2.el9.x86_64.rpm        1.5 MB/s | 746 kB     00:00    
(83/85): util-linux-user-2.37.4-9.el9.x86_64.rp 618 kB/s |  30 kB     00:00    
(84/85): util-linux-core-2.37.4-9.el9.x86_64.rp 1.1 MB/s | 434 kB     00:00    
(85/85): util-linux-2.37.4-9.el9.x86_64.rpm     1.9 MB/s | 2.2 MB     00:01    
--------------------------------------------------------------------------------
Total                                           2.4 MB/s |  18 MB     00:07     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1 
  Installing       : xorg-x11-proto-devel-2021.4-2.el9.noarch             1/120 
  Upgrading        : libuuid-2.37.4-9.el9.x86_64                          2/120 
  Upgrading        : libblkid-2.37.4-9.el9.x86_64                         3/120 
  Running scriptlet: libblkid-2.37.4-9.el9.x86_64                         3/120 
  Upgrading        : harfbuzz-2.7.4-8.el9.x86_64                          4/120 
  Upgrading        : freetype-2.10.4-9.el9.x86_64                         5/120 
  Upgrading        : libxml2-2.9.13-2.el9.x86_64                          6/120 
  Upgrading        : pcre2-syntax-10.40-2.el9.noarch                      7/120 
  Upgrading        : pcre2-10.40-2.el9.x86_64                             8/120 
  Upgrading        : libsepol-3.4-1.1.el9.x86_64                          9/120 
  Upgrading        : libselinux-3.4-3.el9.x86_64                         10/120 
  Running scriptlet: libselinux-3.4-3.el9.x86_64                         10/120 
  Upgrading        : libmount-2.37.4-9.el9.x86_64                        11/120 
  Upgrading        : dbus-libs-1:1.12.20-6.el9.x86_64                    12/120 
  Installing       : libpng-devel-2:1.6.37-12.el9.x86_64                 13/120 
  Installing       : dbus-devel-1:1.12.20-6.el9.x86_64                   14/120 
  Installing       : libxml2-devel-2.9.13-2.el9.x86_64                   15/120 
  Upgrading        : libsmartcols-2.37.4-9.el9.x86_64                    16/120 
  Installing       : libsigc++20-devel-2.10.7-2.el9.x86_64               17/120 
  Installing       : libffi-devel-3.4.2-7.el9.x86_64                     18/120 
  Installing       : wayland-devel-1.19.0-4.el9.x86_64                   19/120 
  Upgrading        : util-linux-core-2.37.4-9.el9.x86_64                 20/120 
  Running scriptlet: util-linux-core-2.37.4-9.el9.x86_64                 20/120 
  Installing       : libxkbcommon-devel-1.0.3-4.el9.x86_64               21/120 
  Upgrading        : dbus-tools-1:1.12.20-6.el9.x86_64                   22/120 
  Installing       : libsepol-devel-3.4-1.1.el9.x86_64                   23/120 
  Upgrading        : pcre2-utf16-10.40-2.el9.x86_64                      24/120 
  Upgrading        : pcre2-utf32-10.40-2.el9.x86_64                      25/120 
  Installing       : pcre2-devel-10.40-2.el9.x86_64                      26/120 
  Installing       : libselinux-devel-3.4-3.el9.x86_64                   27/120 
  Upgrading        : fontconfig-2.14.0-1.el9.x86_64                      28/120 
  Running scriptlet: fontconfig-2.14.0-1.el9.x86_64                      28/120 
  Upgrading        : harfbuzz-icu-2.7.4-8.el9.x86_64                     29/120 
  Installing       : libblkid-devel-2.37.4-9.el9.x86_64                  30/120 
  Installing       : libmount-devel-2.37.4-9.el9.x86_64                  31/120 
  Upgrading        : libfdisk-2.37.4-9.el9.x86_64                        32/120 
  Upgrading        : util-linux-2.37.4-9.el9.x86_64                      33/120 
  Installing       : libXau-devel-1.0.9-8.el9.x86_64                     34/120 
  Installing       : libxcb-devel-1.13.1-9.el9.x86_64                    35/120 
  Installing       : libX11-devel-1.7.0-7.el9.x86_64                     36/120 
  Installing       : libXext-devel-1.3.4-8.el9.x86_64                    37/120 
  Installing       : libXfixes-devel-5.0.3-16.el9.x86_64                 38/120 
  Installing       : libXrender-devel-0.9.10-16.el9.x86_64               39/120 
  Installing       : libXi-devel-1.7.10-8.el9.x86_64                     40/120 
  Installing       : libXtst-devel-1.2.3-16.el9.x86_64                   41/120 
  Installing       : libXcursor-devel-1.2.0-7.el9.x86_64                 42/120 
  Installing       : libXrandr-devel-1.5.2-8.el9.x86_64                  43/120 
  Installing       : libXcomposite-devel-0.4.5-7.el9.x86_64              44/120 
  Installing       : libXdamage-devel-1.1.5-7.el9.x86_64                 45/120 
  Installing       : libXinerama-devel-1.1.4-10.el9.x86_64               46/120 
  Upgrading        : dbus-common-1:1.12.20-6.el9.noarch                  47/120 
  Running scriptlet: dbus-common-1:1.12.20-6.el9.noarch                  47/120 
  Upgrading        : libtiff-4.4.0-2.el9.x86_64                          48/120 
  Installing       : libtiff-devel-4.4.0-2.el9.x86_64                    49/120 
  Upgrading        : fribidi-1.0.10-6.el9.2.x86_64                       50/120 
  Installing       : fribidi-devel-1.0.10-6.el9.2.x86_64                 51/120 
  Installing       : sysprof-capture-devel-3.40.1-3.el9.x86_64           52/120 
  Installing       : pixman-devel-0.40.0-5.el9.x86_64                    53/120 
  Installing       : perl-Filter-2:1.60-4.el9.x86_64                     54/120 
  Installing       : perl-encoding-4:3.00-462.el9.x86_64                 55/120 
  Installing       : perl-open-1.12-479.el9.noarch                       56/120 
  Installing       : pcre-utf32-8.44-3.el9.3.x86_64                      57/120 
  Installing       : pcre-utf16-8.44-3.el9.3.x86_64                      58/120 
  Installing       : pcre-cpp-8.44-3.el9.3.x86_64                        59/120 
  Installing       : pcre-devel-8.44-3.el9.3.x86_64                      60/120 
  Installing       : glib2-devel-2.68.4-5.el9.x86_64                     61/120 
  Installing       : atk-devel-2.36.0-5.el9.x86_64                       62/120 
  Installing       : glibmm24-devel-2.66.1-1.el9.x86_64                  63/120 
  Installing       : at-spi2-core-devel-2.40.3-1.el9.x86_64              64/120 
  Installing       : at-spi2-atk-devel-2.38.0-4.el9.x86_64               65/120 
  Installing       : gdk-pixbuf2-devel-2.42.6-2.el9.x86_64               66/120 
  Installing       : libicu-devel-67.1-9.el9.x86_64                      67/120 
  Installing       : libglvnd-core-devel-1:1.3.4-1.el9.x86_64            68/120 
  Installing       : libglvnd-devel-1:1.3.4-1.el9.x86_64                 69/120 
  Installing       : libepoxy-devel-1.5.5-4.el9.x86_64                   70/120 
  Installing       : libdatrie-devel-0.2.13-4.el9.x86_64                 71/120 
  Installing       : libthai-devel-0.1.28-8.el9.x86_64                   72/120 
  Installing       : graphite2-devel-1.3.14-9.el9.x86_64                 73/120 
  Installing       : bzip2-devel-1.0.8-8.el9.x86_64                      74/120 
  Installing       : brotli-1.0.9-6.el9.x86_64                           75/120 
  Installing       : brotli-devel-1.0.9-6.el9.x86_64                     76/120 
  Installing       : harfbuzz-devel-2.7.4-8.el9.x86_64                   77/120 
  Installing       : freetype-devel-2.10.4-9.el9.x86_64                  78/120 
  Installing       : fontconfig-devel-2.14.0-1.el9.x86_64                79/120 
  Installing       : cairo-devel-1.17.4-7.el9.x86_64                     80/120 
  Installing       : cairo-gobject-devel-1.17.4-7.el9.x86_64             81/120 
  Installing       : cairomm-devel-1.14.2-10.el9.x86_64                  82/120 
  Installing       : libXft-devel-2.3.3-8.el9.x86_64                     83/120 
  Installing       : pango-devel-1.48.7-2.el9.x86_64                     84/120 
  Installing       : gtk3-devel-3.24.31-2.el9.x86_64                     85/120 
  Installing       : pangomm-devel-2.46.1-1.el9.x86_64                   86/120 
  Installing       : atkmm-devel-2.28.2-2.el9.x86_64                     87/120 
  Running scriptlet: dbus-daemon-1:1.12.20-6.el9.x86_64                  88/120 
  Upgrading        : dbus-daemon-1:1.12.20-6.el9.x86_64                  88/120 
  Running scriptlet: dbus-daemon-1:1.12.20-6.el9.x86_64                  88/120 
  Upgrading        : util-linux-user-2.37.4-9.el9.x86_64                 89/120 
  Upgrading        : python3-libselinux-3.4-3.el9.x86_64                 90/120 
  Upgrading        : libselinux-utils-3.4-3.el9.x86_64                   91/120 
  Upgrading        : python3-libxml2-2.9.13-2.el9.x86_64                 92/120 
  Cleanup          : python3-libxml2-2.9.13-1.el9_0.1.x86_64             93/120 
  Running scriptlet: dbus-daemon-1:1.12.20-5.el9.x86_64                  94/120 
  Cleanup          : dbus-daemon-1:1.12.20-5.el9.x86_64                  94/120 
  Running scriptlet: dbus-daemon-1:1.12.20-5.el9.x86_64                  94/120 
  Cleanup          : libselinux-utils-3.3-2.el9.x86_64                   95/120 
  Cleanup          : fontconfig-2.13.94-2.el9.x86_64                     96/120 
  Running scriptlet: fontconfig-2.13.94-2.el9.x86_64                     96/120 
  Cleanup          : dbus-tools-1:1.12.20-5.el9.x86_64                   97/120 
  Cleanup          : python3-libselinux-3.3-2.el9.x86_64                 98/120 
  Cleanup          : util-linux-user-2.37.4-3.el9.x86_64                 99/120 
  Cleanup          : util-linux-2.37.4-3.el9.x86_64                     100/120 
  Cleanup          : util-linux-core-2.37.4-3.el9.x86_64                101/120 
  Cleanup          : libmount-2.37.4-3.el9.x86_64                       102/120 
  Cleanup          : libfdisk-2.37.4-3.el9.x86_64                       103/120 
  Cleanup          : libselinux-3.3-2.el9.x86_64                        104/120 
  Cleanup          : harfbuzz-icu-2.7.4-5.el9.x86_64                    105/120 
  Cleanup          : pcre2-10.37-5.el9_0.x86_64                         106/120 
  Cleanup          : libblkid-2.37.4-3.el9.x86_64                       107/120 
  Cleanup          : freetype-2.10.4-6.el9.x86_64                       108/120 
  Cleanup          : pcre2-utf32-10.37-5.el9_0.x86_64                   109/120 
  Cleanup          : pcre2-utf16-10.37-5.el9_0.x86_64                   110/120 
  Cleanup          : pcre2-syntax-10.37-5.el9_0.noarch                  111/120 
  Running scriptlet: dbus-common-1:1.12.20-5.el9.noarch                 112/120 
  Cleanup          : dbus-common-1:1.12.20-5.el9.noarch                 112/120 
  Running scriptlet: dbus-common-1:1.12.20-5.el9.noarch                 112/120 
  Cleanup          : harfbuzz-2.7.4-5.el9.x86_64                        113/120 
  Cleanup          : libuuid-2.37.4-3.el9.x86_64                        114/120 
  Cleanup          : libsepol-3.3-2.el9.x86_64                          115/120 
  Cleanup          : libsmartcols-2.37.4-3.el9.x86_64                   116/120 
  Cleanup          : dbus-libs-1:1.12.20-5.el9.x86_64                   117/120 
  Cleanup          : libxml2-2.9.13-1.el9_0.1.x86_64                    118/120 
  Cleanup          : libtiff-4.2.0-3.el9.x86_64                         119/120 
  Cleanup          : fribidi-1.0.10-6.el9.x86_64                        120/120 
  Running scriptlet: fontconfig-2.14.0-1.el9.x86_64                     120/120 
  Running scriptlet: fribidi-1.0.10-6.el9.x86_64                        120/120 
Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26
Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26
 
Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26
Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26
 
  Verifying        : at-spi2-atk-devel-2.38.0-4.el9.x86_64                1/120 
  Verifying        : at-spi2-core-devel-2.40.3-1.el9.x86_64               2/120 
  Verifying        : atk-devel-2.36.0-5.el9.x86_64                        3/120 
  Verifying        : brotli-1.0.9-6.el9.x86_64                            4/120 
  Verifying        : brotli-devel-1.0.9-6.el9.x86_64                      5/120 
  Verifying        : bzip2-devel-1.0.8-8.el9.x86_64                       6/120 
  Verifying        : cairo-devel-1.17.4-7.el9.x86_64                      7/120 
  Verifying        : cairo-gobject-devel-1.17.4-7.el9.x86_64              8/120 
  Verifying        : dbus-devel-1:1.12.20-6.el9.x86_64                    9/120 
  Verifying        : fontconfig-devel-2.14.0-1.el9.x86_64                10/120 
  Verifying        : freetype-devel-2.10.4-9.el9.x86_64                  11/120 
  Verifying        : fribidi-devel-1.0.10-6.el9.2.x86_64                 12/120 
  Verifying        : glib2-devel-2.68.4-5.el9.x86_64                     13/120 
  Verifying        : graphite2-devel-1.3.14-9.el9.x86_64                 14/120 
  Verifying        : harfbuzz-devel-2.7.4-8.el9.x86_64                   15/120 
  Verifying        : libX11-devel-1.7.0-7.el9.x86_64                     16/120 
  Verifying        : libXau-devel-1.0.9-8.el9.x86_64                     17/120 
  Verifying        : libXcomposite-devel-0.4.5-7.el9.x86_64              18/120 
  Verifying        : libXcursor-devel-1.2.0-7.el9.x86_64                 19/120 
  Verifying        : libXdamage-devel-1.1.5-7.el9.x86_64                 20/120 
  Verifying        : libXext-devel-1.3.4-8.el9.x86_64                    21/120 
  Verifying        : libXfixes-devel-5.0.3-16.el9.x86_64                 22/120 
  Verifying        : libXft-devel-2.3.3-8.el9.x86_64                     23/120 
  Verifying        : libXi-devel-1.7.10-8.el9.x86_64                     24/120 
  Verifying        : libXinerama-devel-1.1.4-10.el9.x86_64               25/120 
  Verifying        : libXrandr-devel-1.5.2-8.el9.x86_64                  26/120 
  Verifying        : libXrender-devel-0.9.10-16.el9.x86_64               27/120 
  Verifying        : libXtst-devel-1.2.3-16.el9.x86_64                   28/120 
  Verifying        : libblkid-devel-2.37.4-9.el9.x86_64                  29/120 
  Verifying        : libdatrie-devel-0.2.13-4.el9.x86_64                 30/120 
  Verifying        : libepoxy-devel-1.5.5-4.el9.x86_64                   31/120 
  Verifying        : libffi-devel-3.4.2-7.el9.x86_64                     32/120 
  Verifying        : libglvnd-core-devel-1:1.3.4-1.el9.x86_64            33/120 
  Verifying        : libglvnd-devel-1:1.3.4-1.el9.x86_64                 34/120 
  Verifying        : libicu-devel-67.1-9.el9.x86_64                      35/120 
  Verifying        : libmount-devel-2.37.4-9.el9.x86_64                  36/120 
  Verifying        : libpng-devel-2:1.6.37-12.el9.x86_64                 37/120 
  Verifying        : libselinux-devel-3.4-3.el9.x86_64                   38/120 
  Verifying        : libsepol-devel-3.4-1.1.el9.x86_64                   39/120 
  Verifying        : libthai-devel-0.1.28-8.el9.x86_64                   40/120 
  Verifying        : libtiff-devel-4.4.0-2.el9.x86_64                    41/120 
  Verifying        : libxcb-devel-1.13.1-9.el9.x86_64                    42/120 
  Verifying        : libxkbcommon-devel-1.0.3-4.el9.x86_64               43/120 
  Verifying        : libxml2-devel-2.9.13-2.el9.x86_64                   44/120 
  Verifying        : pango-devel-1.48.7-2.el9.x86_64                     45/120 
  Verifying        : pcre-cpp-8.44-3.el9.3.x86_64                        46/120 
  Verifying        : pcre-devel-8.44-3.el9.3.x86_64                      47/120 
  Verifying        : pcre-utf16-8.44-3.el9.3.x86_64                      48/120 
  Verifying        : pcre-utf32-8.44-3.el9.3.x86_64                      49/120 
  Verifying        : pcre2-devel-10.40-2.el9.x86_64                      50/120 
  Verifying        : perl-Filter-2:1.60-4.el9.x86_64                     51/120 
  Verifying        : perl-encoding-4:3.00-462.el9.x86_64                 52/120 
  Verifying        : perl-open-1.12-479.el9.noarch                       53/120 
  Verifying        : pixman-devel-0.40.0-5.el9.x86_64                    54/120 
  Verifying        : sysprof-capture-devel-3.40.1-3.el9.x86_64           55/120 
  Verifying        : wayland-devel-1.19.0-4.el9.x86_64                   56/120 
  Verifying        : xorg-x11-proto-devel-2021.4-2.el9.noarch            57/120 
  Verifying        : atkmm-devel-2.28.2-2.el9.x86_64                     58/120 
  Verifying        : cairomm-devel-1.14.2-10.el9.x86_64                  59/120 
  Verifying        : gdk-pixbuf2-devel-2.42.6-2.el9.x86_64               60/120 
  Verifying        : glibmm24-devel-2.66.1-1.el9.x86_64                  61/120 
  Verifying        : gtk3-devel-3.24.31-2.el9.x86_64                     62/120 
  Verifying        : libsigc++20-devel-2.10.7-2.el9.x86_64               63/120 
  Verifying        : pangomm-devel-2.46.1-1.el9.x86_64                   64/120 
  Verifying        : dbus-daemon-1:1.12.20-6.el9.x86_64                  65/120 
  Verifying        : dbus-daemon-1:1.12.20-5.el9.x86_64                  66/120 
  Verifying        : fontconfig-2.14.0-1.el9.x86_64                      67/120 
  Verifying        : fontconfig-2.13.94-2.el9.x86_64                     68/120 
  Verifying        : fribidi-1.0.10-6.el9.2.x86_64                       69/120 
  Verifying        : fribidi-1.0.10-6.el9.x86_64                         70/120 
  Verifying        : harfbuzz-icu-2.7.4-8.el9.x86_64                     71/120 
  Verifying        : harfbuzz-icu-2.7.4-5.el9.x86_64                     72/120 
  Verifying        : libtiff-4.4.0-2.el9.x86_64                          73/120 
  Verifying        : libtiff-4.2.0-3.el9.x86_64                          74/120 
  Verifying        : pcre2-utf16-10.40-2.el9.x86_64                      75/120 
  Verifying        : pcre2-utf16-10.37-5.el9_0.x86_64                    76/120 
  Verifying        : pcre2-utf32-10.40-2.el9.x86_64                      77/120 
  Verifying        : pcre2-utf32-10.37-5.el9_0.x86_64                    78/120 
  Verifying        : python3-libselinux-3.4-3.el9.x86_64                 79/120 
  Verifying        : python3-libselinux-3.3-2.el9.x86_64                 80/120 
  Verifying        : dbus-common-1:1.12.20-6.el9.noarch                  81/120 
  Verifying        : dbus-common-1:1.12.20-5.el9.noarch                  82/120 
  Verifying        : dbus-libs-1:1.12.20-6.el9.x86_64                    83/120 
  Verifying        : dbus-libs-1:1.12.20-5.el9.x86_64                    84/120 
  Verifying        : dbus-tools-1:1.12.20-6.el9.x86_64                   85/120 
  Verifying        : dbus-tools-1:1.12.20-5.el9.x86_64                   86/120 
  Verifying        : freetype-2.10.4-9.el9.x86_64                        87/120 
  Verifying        : freetype-2.10.4-6.el9.x86_64                        88/120 
  Verifying        : harfbuzz-2.7.4-8.el9.x86_64                         89/120 
  Verifying        : harfbuzz-2.7.4-5.el9.x86_64                         90/120 
  Verifying        : libblkid-2.37.4-9.el9.x86_64                        91/120 
  Verifying        : libblkid-2.37.4-3.el9.x86_64                        92/120 
  Verifying        : libfdisk-2.37.4-9.el9.x86_64                        93/120 
  Verifying        : libfdisk-2.37.4-3.el9.x86_64                        94/120 
  Verifying        : libmount-2.37.4-9.el9.x86_64                        95/120 
  Verifying        : libmount-2.37.4-3.el9.x86_64                        96/120 
  Verifying        : libselinux-3.4-3.el9.x86_64                         97/120 
  Verifying        : libselinux-3.3-2.el9.x86_64                         98/120 
  Verifying        : libselinux-utils-3.4-3.el9.x86_64                   99/120 
  Verifying        : libselinux-utils-3.3-2.el9.x86_64                  100/120 
  Verifying        : libsepol-3.4-1.1.el9.x86_64                        101/120 
  Verifying        : libsepol-3.3-2.el9.x86_64                          102/120 
  Verifying        : libsmartcols-2.37.4-9.el9.x86_64                   103/120 
  Verifying        : libsmartcols-2.37.4-3.el9.x86_64                   104/120 
  Verifying        : libuuid-2.37.4-9.el9.x86_64                        105/120 
  Verifying        : libuuid-2.37.4-3.el9.x86_64                        106/120 
  Verifying        : libxml2-2.9.13-2.el9.x86_64                        107/120 
  Verifying        : libxml2-2.9.13-1.el9_0.1.x86_64                    108/120 
  Verifying        : pcre2-10.40-2.el9.x86_64                           109/120 
  Verifying        : pcre2-10.37-5.el9_0.x86_64                         110/120 
  Verifying        : pcre2-syntax-10.40-2.el9.noarch                    111/120 
  Verifying        : pcre2-syntax-10.37-5.el9_0.noarch                  112/120 
  Verifying        : python3-libxml2-2.9.13-2.el9.x86_64                113/120 
  Verifying        : python3-libxml2-2.9.13-1.el9_0.1.x86_64            114/120 
  Verifying        : util-linux-2.37.4-9.el9.x86_64                     115/120 
  Verifying        : util-linux-2.37.4-3.el9.x86_64                     116/120 
  Verifying        : util-linux-core-2.37.4-9.el9.x86_64                117/120 
  Verifying        : util-linux-core-2.37.4-3.el9.x86_64                118/120 
  Verifying        : util-linux-user-2.37.4-9.el9.x86_64                119/120 
  Verifying        : util-linux-user-2.37.4-3.el9.x86_64                120/120 
 
Upgraded:
  dbus-common-1:1.12.20-6.el9.noarch     dbus-daemon-1:1.12.20-6.el9.x86_64    
  dbus-libs-1:1.12.20-6.el9.x86_64       dbus-tools-1:1.12.20-6.el9.x86_64     
  fontconfig-2.14.0-1.el9.x86_64         freetype-2.10.4-9.el9.x86_64          
  fribidi-1.0.10-6.el9.2.x86_64          harfbuzz-2.7.4-8.el9.x86_64           
  harfbuzz-icu-2.7.4-8.el9.x86_64        libblkid-2.37.4-9.el9.x86_64          
  libfdisk-2.37.4-9.el9.x86_64           libmount-2.37.4-9.el9.x86_64          
  libselinux-3.4-3.el9.x86_64            libselinux-utils-3.4-3.el9.x86_64     
  libsepol-3.4-1.1.el9.x86_64            libsmartcols-2.37.4-9.el9.x86_64      
  libtiff-4.4.0-2.el9.x86_64             libuuid-2.37.4-9.el9.x86_64           
  libxml2-2.9.13-2.el9.x86_64            pcre2-10.40-2.el9.x86_64              
  pcre2-syntax-10.40-2.el9.noarch        pcre2-utf16-10.40-2.el9.x86_64        
  pcre2-utf32-10.40-2.el9.x86_64         python3-libselinux-3.4-3.el9.x86_64   
  python3-libxml2-2.9.13-2.el9.x86_64    util-linux-2.37.4-9.el9.x86_64        
  util-linux-core-2.37.4-9.el9.x86_64    util-linux-user-2.37.4-9.el9.x86_64   
Installed:
  at-spi2-atk-devel-2.38.0-4.el9.x86_64                                         
  at-spi2-core-devel-2.40.3-1.el9.x86_64                                        
  atk-devel-2.36.0-5.el9.x86_64                                                 
  atkmm-devel-2.28.2-2.el9.x86_64                                               
  brotli-1.0.9-6.el9.x86_64                                                     
  brotli-devel-1.0.9-6.el9.x86_64                                               
  bzip2-devel-1.0.8-8.el9.x86_64                                                
  cairo-devel-1.17.4-7.el9.x86_64                                               
  cairo-gobject-devel-1.17.4-7.el9.x86_64                                       
  cairomm-devel-1.14.2-10.el9.x86_64                                            
  dbus-devel-1:1.12.20-6.el9.x86_64                                             
  fontconfig-devel-2.14.0-1.el9.x86_64                                          
  freetype-devel-2.10.4-9.el9.x86_64                                            
  fribidi-devel-1.0.10-6.el9.2.x86_64                                           
  gdk-pixbuf2-devel-2.42.6-2.el9.x86_64                                         
  glib2-devel-2.68.4-5.el9.x86_64                                               
  glibmm24-devel-2.66.1-1.el9.x86_64                                            
  graphite2-devel-1.3.14-9.el9.x86_64                                           
  gtk3-devel-3.24.31-2.el9.x86_64                                               
  harfbuzz-devel-2.7.4-8.el9.x86_64                                             
  libX11-devel-1.7.0-7.el9.x86_64                                               
  libXau-devel-1.0.9-8.el9.x86_64                                               
  libXcomposite-devel-0.4.5-7.el9.x86_64                                        
  libXcursor-devel-1.2.0-7.el9.x86_64                                           
  libXdamage-devel-1.1.5-7.el9.x86_64                                           
  libXext-devel-1.3.4-8.el9.x86_64                                              
  libXfixes-devel-5.0.3-16.el9.x86_64                                           
  libXft-devel-2.3.3-8.el9.x86_64                                               
  libXi-devel-1.7.10-8.el9.x86_64                                               
  libXinerama-devel-1.1.4-10.el9.x86_64                                         
  libXrandr-devel-1.5.2-8.el9.x86_64                                            
  libXrender-devel-0.9.10-16.el9.x86_64                                         
  libXtst-devel-1.2.3-16.el9.x86_64                                             
  libblkid-devel-2.37.4-9.el9.x86_64                                            
  libdatrie-devel-0.2.13-4.el9.x86_64                                           
  libepoxy-devel-1.5.5-4.el9.x86_64                                             
  libffi-devel-3.4.2-7.el9.x86_64                                               
  libglvnd-core-devel-1:1.3.4-1.el9.x86_64                                      
  libglvnd-devel-1:1.3.4-1.el9.x86_64                                           
  libicu-devel-67.1-9.el9.x86_64                                                
  libmount-devel-2.37.4-9.el9.x86_64                                            
  libpng-devel-2:1.6.37-12.el9.x86_64                                           
  libselinux-devel-3.4-3.el9.x86_64                                             
  libsepol-devel-3.4-1.1.el9.x86_64                                             
  libsigc++20-devel-2.10.7-2.el9.x86_64                                         
  libthai-devel-0.1.28-8.el9.x86_64                                             
  libtiff-devel-4.4.0-2.el9.x86_64                                              
  libxcb-devel-1.13.1-9.el9.x86_64                                              
  libxkbcommon-devel-1.0.3-4.el9.x86_64                                         
  libxml2-devel-2.9.13-2.el9.x86_64                                             
  pango-devel-1.48.7-2.el9.x86_64                                               
  pangomm-devel-2.46.1-1.el9.x86_64                                             
  pcre-cpp-8.44-3.el9.3.x86_64                                                  
  pcre-devel-8.44-3.el9.3.x86_64                                                
  pcre-utf16-8.44-3.el9.3.x86_64                                                
  pcre-utf32-8.44-3.el9.3.x86_64                                                
  pcre2-devel-10.40-2.el9.x86_64                                                
  perl-Filter-2:1.60-4.el9.x86_64                                               
  perl-encoding-4:3.00-462.el9.x86_64                                           
  perl-open-1.12-479.el9.noarch                                                 
  pixman-devel-0.40.0-5.el9.x86_64                                              
  sysprof-capture-devel-3.40.1-3.el9.x86_64                                     
  wayland-devel-1.19.0-4.el9.x86_64                                             
  xorg-x11-proto-devel-2021.4-2.el9.noarch                                      
 
Complete!

    Now return to your $HOME/Downloads directory and run the following command. You’ll notice that it installs and upgrades many more packages than you might expect.

    sudo dnf install -y gtkmm30-devel-3.24.5-1.el9.x86_64.rpm

    The log file for this is:

    Display detailed console log →

    Last metadata expiration check: 1:22:32 ago on Sun 20 Nov 2022 12:52:28 AM EST.
Dependencies resolved.
================================================================================
 Package             Architecture Version              Repository          Size
================================================================================
Installing:
 gtkmm30-devel       x86_64       3.24.5-1.el9         @commandline       605 k
 
Transaction Summary
================================================================================
Install  1 Package
 
Total size: 605 k
Installed size: 4.7 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26
Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26
  Preparing        :                                                        1/1 
  Installing       : gtkmm30-devel-3.24.5-1.el9.x86_64                      1/1 
  Running scriptlet: gtkmm30-devel-3.24.5-1.el9.x86_64                      1/1 
  Verifying        : gtkmm30-devel-3.24.5-1.el9.x86_64                      1/1 
 
Installed:
  gtkmm30-devel-3.24.5-1.el9.x86_64                                             
 
Complete!

    All that done and you’ve only got the first of four dependencies resovled.

  2. Next, start with the libzip-devel package, which has a couple dependencies. Assuming you’re still in your $HOME/Downloads directory, you can run the following command to get the libzip-devel and its prerequisite packages for AlmaLinux 9: 

    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/libzip-devel-1.7.3-7.el9.x86_64.rpm
wget https://repo.almalinux.org/almalinux/9/AppStream/x86_64/os/Packages/cmake-filesystem-3.20.2-7.el9.x86_64.rpm
wget https://repo.almalinux.org/almalinux/9/AppStream/x86_64/os/Packages/libzip-1.7.3-7.el9.x86_64.rpm

    You can run the prerequisites with the following command:

    sudo dnf install -y cmake*.rpm libzip-1.7.3*.rpm

    Now, you can run the libzip-devel package with this syntax:

    sudo dnf install -y libzip-devel*.rpm

    Having resolved the two dependencies, you can install the compression development kit. This completes the second step.

  3. Next, you need to apply the proj_devel package for AlmaLinux 9:

    wget https://download-ib01.fedoraproject.org/pub/epel/9/Everything/x86_64/Packages/p/proj-devel-8.2.0-1.el9.x86_64.rpm

    Now, you can run the proj-devel package with this syntax:

    sudo dnf install -y proj-devel-8.2.0-1.el9.x86_64.rpm
  4. Next, you need to apply the swig packages for AlmaLinux 9:

    wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/swig-4.0.2-8.el9.x86_64.rpm
wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/swig-doc-4.0.2-8.el9.noarch.rpm
wget https://repo.almalinux.org/almalinux/9/CRB/x86_64/os/Packages/swig-gdb-4.0.2-8.el9.x86_64.rpm
    sudo dnf install -y swig*.rpm
  5. Next, you need to apply the mysql-community-workbench packages for AlmaLinux 9. The download instructions where provided above. You apply the packages with the following command.

    sudo dnf install -y mysql-workbench-community-8.0.31-1.el9.x86_64.rpm

    Display detailed console log →

    Last metadata expiration check: 1:06:04 ago on Sun 20 Nov 2022 03:28:30 PM EST.
Dependencies resolved.
================================================================================
 Package                      Arch      Version           Repository       Size
================================================================================
Installing:
 mysql-workbench-community    x86_64    8.0.31-1.el9      @commandline     39 M
Upgrading:
 proj                         x86_64    8.2.0-1.el9       epel            2.5 M
 
Transaction Summary
================================================================================
Install  1 Package
Upgrade  1 Package
 
Total size: 41 M
Total download size: 2.5 M
Downloading Packages:
proj-8.2.0-1.el9.x86_64.rpm                     388 kB/s | 2.5 MB     00:06    
--------------------------------------------------------------------------------
Total                                           354 kB/s | 2.5 MB     00:07     
Extra Packages for Enterprise Linux 9 - x86_64  1.6 MB/s | 1.6 kB     00:00    
Importing GPG key 0x3228467C:
 Userid     : "Fedora (epel9) <epel@fedoraproject.org>"
 Fingerprint: FF8A D134 4597 106E CE81 3B91 8A38 72BF 3228 467C
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-9
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26
Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26
  Preparing        :                                                        1/1 
  Upgrading        : proj-8.2.0-1.el9.x86_64                                1/3 
  Installing       : mysql-workbench-community-8.0.31-1.el9.x86_64          2/3 
  Running scriptlet: mysql-workbench-community-8.0.31-1.el9.x86_64          2/3 
  Cleanup          : proj-4.8.0-4.el7.x86_64                                3/3 
  Running scriptlet: proj-4.8.0-4.el7.x86_64                                3/3 
  Verifying        : mysql-workbench-community-8.0.31-1.el9.x86_64          1/3 
  Verifying        : proj-8.2.0-1.el9.x86_64                                2/3 
  Verifying        : proj-4.8.0-4.el7.x86_64                                3/3 
 
Upgraded:
  proj-8.2.0-1.el9.x86_64                                                       
Installed:
  mysql-workbench-community-8.0.31-1.el9.x86_64                                 
 
Complete!

After applying the dependent and mysql-community-workbench packages, you can launch MySQL Workbench by clicking the Activities symbol in the upper left hand corner. That displays the nine-dots for Show Applications icon. Click the Show Applications icon and choose the MySQL Workbench icon to launch MySQL Workbench.

You’ll be prompted with the following dialog. Just click Don’t show this message again checkbox and the OK button to launch MySQL Workbench.

As always, I hope this helps those looking to solve a real problem.

Written by maclochlainn

November 20th, 2022 at 11:31 pm

Posted in AlmaLinux,Database,DBA,Linux,MySQL,MySQL 8,MySQL Workbench,Unix

Tagged with ,

AlmaLinux LAMP

without comments


After installing and configuring MySQL 8.0.30, I installed the Apache Web Server, PHP and the MySQLi packages. Here are the step-by-step instructions after installing and configuring the MySQL Server and provisioning a student user and the sakila and studentdb databases (blog for those steps). After installing the major components, I completed the HTTPS configuration steps for Apache 2.

The installation steps are:

  1. Install the Apache packages as the sudoer user with this command: 

    sudo dnf install -y httpd
  2. Enable Apache as the sudoer user with this command: 

    chkconfig httpd on

    This returns the following completion message:

    Note: Forwarding request to 'systemctl enable httpd.service'.
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.

    A quick Linux syntax note in the event you want to confirm the link or link target later. You can use the following syntax as a sudoer user to find the link:

    ls `find /etc -type l | grep httpd.service 2>/dev/null`

    and the following syntax as a sudoer user to find the link’s target:

    readlink `find /etc -type l | grep httpd.service 2>/dev/null`
  3. You still need to start the Apache service unless you reboot the operating system as the sudoer user with this command: 

    apachectl start
  4. At this point, you need to check the firewall settings because Apache can’t even read localhost at this point. If you’re new to these firewall commands, you should consider reviewing Korbin Brown’s tutorial. As the sudoer user check the Apache available services with this command: 

    firewall-cmd --zone=public --list-services

    It should return:

    cockpit dhcpv6-client ssh

    Add the following services and ports with these commands:

    firewall-cmd --zone=public --add-port 80/tcp --permanent
firewall-cmd --zone=public --add-port 443/tcp --permanent
firewall-cmd --zone=public --add-port 8080/tcp --permanent
firewall-cmd --zone=public --add-service=http --permanent
firewall-cmd --zone=public --add-service=https --permanent

    Check the open ports with the following command:

    firewall-cmd --zone=public --list-ports

    It should return:

    80/tcp 443/tcp 8080/tcp

    Check the open services with the following command:

    firewall-cmd --zone=public --list-services

    It should return:

    cockpit dhcpv6-client http https ssh
  5. Create the hello.htm file in the /var/www/html directory as the root user:

    Restart the Apache service as the sudoer user:

    apache restart

    <html>
<body>
Hello World!
</body>
</html>

    Then, you can launch the Firefox browser and type the following:

    localhost/hello.htm

    It should print “Hello World!” in the browser.

  6. Install the php package as the sudoer user with the following command: 

    sudo dnf install -y php

    Create the info.php file in the /var/www/html directory as the root user:

    <?php
  phpinfo();
?>

    apache restart

    Then, you can launch the Firefox browser and type the following:

    localhost/info.php

    It should return the following in the browser.

  7. Install the php_mysqli package as the sudoer user with the following command: 

    dnf install -y php-mysqli

    Create the mysqli_check.php file in the /var/www/html directory as the root user:

    <html>
<header>
<title>Static Query Object Sample</title>
</header>
<body>
<?php
  if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
    print 'mysqli not installed.'; }
  else {
    print 'mysqli installed.'; }
  if (!function_exists('pdo_init') && !extension_loaded('pdo')) {
    print '<p>pdo not installed.</p>'; }
  else {
    print '<p>pdo installed.</p>'; }
?>
</script>
</body>
</html>

    apache restart

    Then, you can launch the Firefox browser and type the following:

    localhost/mysqli_check.php

    It should print the following in the browser.

    mysqli installed.
 
pdo installed.
  8. Check if the mod_ssl module is installed. You can use the following command:: 

    rpm -qa | grep mod_ssl

    Assuming it’s not installed, you install it like this:

    dnf install -y mod_ssl

    Recheck after installing mod_ssl with the following command::

    rpm -qa | grep mod_ssl

    It should print:

    mod_ssl-2.4.51-7.el9_0.x86_64
  9. AlmaLinux and Apache require you to resolve the ServerName values and the public and private keys. Run this command on AlmaLinux to begin verifying and configuring the ServerName values and the public and private keys: 

    httpd -M | grep ssl

    Assuming a new installation consistent with were MySQL and Apache were just configured, you should get the following message:

    AH00558: httpd: Could not reliably determine the server's fully qualified domain name, using localhost.localdomain. Set the 'ServerName' directive globally to suppress this message
 ssl_module (shared)

    Recheck the failure for more detail with this command:

    sudo systemctl status httpd.service -l --no-pager

    It should print:

    ● httpd.service - The Apache HTTP Server
     Loaded: loaded (/usr/lib/systemd/system/httpd.service; enabled; vendor preset: disabled)
    Drop-In: /usr/lib/systemd/system/httpd.service.d
             └─php-fpm.conf
     Active: active (running) since Sun 2022-11-13 22:39:07 EST; 1h 37min ago
       Docs: man:httpd.service(8)
   Main PID: 1351 (httpd)
     Status: "Total requests: 0; Idle/Busy workers 100/0;Requests/sec: 0; Bytes served/sec:   0 B/sec"
      Tasks: 213 (limit: 23280)
     Memory: 43.1M
        CPU: 2.733s
     CGroup: /system.slice/httpd.service
             ├─1351 /usr/sbin/httpd -DFOREGROUND
             ├─1443 /usr/sbin/httpd -DFOREGROUND
             ├─1452 /usr/sbin/httpd -DFOREGROUND
             ├─1456 /usr/sbin/httpd -DFOREGROUND
             └─1459 /usr/sbin/httpd -DFOREGROUND
 
Nov 13 22:39:06 localhost.localdomain systemd[1]: Starting The Apache HTTP Server...
Nov 13 22:39:07 localhost.localdomain httpd[1351]: AH00558: httpd: Could not reliably determine the server's fully qualified domain name, using localhost.localdomain. Set the 'ServerName' directive globally to suppress this message
Nov 13 22:39:07 localhost.localdomain systemd[1]: Started The Apache HTTP Server.
Nov 13 22:39:07 localhost.localdomain httpd[1351]: Server configured, listening on: port 80

    It takes the next set of steps to fix the ServerName values.

    • Generically, on Linux you need to find the files to modify. You can use the following command from within the /etc directory to find the configuration files in the /etc directory that include ServerName in them. Their values will be proceeded by a # symbol because they’re comments by default. 

      find /etc -type f | xargs grep -i ServerName

      It should return the following:

      ./httpd/conf.d/ssl.conf:#ServerName www.example.com:443
./httpd/conf/httpd.conf:# ServerName gives the name and port that the server uses to identify itself.
./httpd/conf/httpd.conf:#ServerName www.example.com:80
./dnsmasq.conf:# tftp_servername (the third option to dhcp-boot) and in that
    • Add the following line to the ssl.conf file as the root user: 

      ServerName localhost:443
    • Add the following line to the httpd.conf file as the root user: 

      ServerName localhost:443
    • After adding the two values, restart Apache with the following command: 

      sudo apachectl restart
    • Rerun the systemctl command to get the status of the httpd service with this command: 

      sudo systemctl status httpd.service -l --no-pager

      It should print:

      ● httpd.service - The Apache HTTP Server
     Loaded: loaded (/usr/lib/systemd/system/httpd.service; enabled; vendor preset: disabled)
    Drop-In: /usr/lib/systemd/system/httpd.service.d
             └─php-fpm.conf
     Active: active (running) since Mon 2022-11-14 00:37:03 EST; 3min 23s ago
       Docs: man:httpd.service(8)
   Main PID: 53596 (httpd)
     Status: "Total requests: 0; Idle/Busy workers 100/0;Requests/sec: 0; Bytes served/sec:   0 B/sec"
      Tasks: 213 (limit: 23280)
     Memory: 34.0M
        CPU: 183ms
     CGroup: /system.slice/httpd.service
             ├─53596 /usr/sbin/httpd -DFOREGROUND
             ├─53597 /usr/sbin/httpd -DFOREGROUND
             ├─53598 /usr/sbin/httpd -DFOREGROUND
             ├─53599 /usr/sbin/httpd -DFOREGROUND
             └─53600 /usr/sbin/httpd -DFOREGROUND
 
Nov 14 00:37:03 localhost.localdomain systemd[1]: Starting The Apache HTTP Server...
Nov 14 00:37:03 localhost.localdomain systemd[1]: Started The Apache HTTP Server.
Nov 14 00:37:03 localhost.localdomain httpd[53596]: Server configured, listening on: port 443, port 80
  10. Your next step requires setting up an SSL Certificate. Consistent with the design to build a standalone test system that uses a DHCP assigned IP address to resolve a localhost server name, you require the following two tasks to create an openssl self-signed certificate.

    • On the new instance, you create a private subdirectory with this command: 

      sudo mkdir /etc/ssl/private

    • Then, you can build a self-signed certificate with this command: 
      sudo openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout /etc/ssl/private/apache-selfsigned.key -out /etc/ssl/certs/apache-selfsigned.crt

      The openssl command will prompt you for these values to create a private key:

      You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
  11. Your last step requires three tasks to configure Apache to use SSL.

    • You need to create the following sites-available directory with the following command as the root user: 

      mkdir /etc/httpd/sites-available

    • Add the following localhost.conf/etc/httpd/sites-available directory: 
      <VirtualHost *:443>
   ServerName localhost
   DocumentRoot /var/www/html
 
   SSLEngine on
   SSLCertificateFile /etc/ssl/certs/localhost.crt
   SSLCertificateKeyFile /etc/ssl/private/apache-selfsigned.key
</VirtualHost>

    • Restart Apache with the following command: 

      sudo apachectl restart
  12. After configuring everything, let’s test our self-signed HTTPS skunkworks. Launch the default Firefox browser and enter the following URL, which uses the mysql_check.php file from step #7: 

    https://localhost/mysqli_check.php

    It will raise a warning message about a potential security risk, which is caused by our self-signed certificate. Click the Advanced… button and will see the option to Accept the Risk and Continue. If you want to use the self-signed and contained AlmaLinux LAMP stack for developer testing, accept the risk.

    Having assumed the risk, the confirmation of the configuration will be displayed as follows:

As always, I hope this helps those looking to install MySQL, PHP, on AlmaLinux.

Written by maclochlainn

October 30th, 2022 at 11:16 pm

Posted in AlmaLinux,Apache,Linux,MySQL,MySQL 8,mysqli,Unix

Tagged with , ,

Node.js MySQL Error

without comments

While I blogged about how to setup Node.js and MySQL almost two years ago, it was interesting when a student ran into a problem. The student said they’d configured the environment but were unable to use Node.js to access MySQL.

The error is caused by this import statement:

const mysql = require('mysql')

The student got the following error, which simply says that they hadn’t installed the Node.js package for MySQL driver.

internal/modules/cjs/loader.js:638
    throw err;
    ^
 
Error: Cannot find module 'mysql'
    at Function.Module._resolveFilename (internal/modules/cjs/loader.js:636:15)
    at Function.Module._load (internal/modules/cjs/loader.js:562:25)
    at Module.require (internal/modules/cjs/loader.js:692:17)
    at require (internal/modules/cjs/helpers.js:25:18)
    at Object.<anonymous> (/home/student/Data/cit325/oracle-s/lib/Oracle12cPLSQLCode/Introduction/query.js:4:15)
    at Module._compile (internal/modules/cjs/loader.js:778:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:789:10)
    at Module.load (internal/modules/cjs/loader.js:653:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:593:12)
    at Function.Module._load (internal/modules/cjs/loader.js:585:3)

I explained they could fix the problem with the following two Node.js Package Manager (NPM) commands:

npm init --y 
npm install --save mysql

The student was able to retest the code with success. The issue was simply that the Node.js couldn’t find the NPM MySQL module.

Written by maclochlainn

June 12th, 2022 at 1:58 pm

Posted in Database,DBA,JavaScript,Linux,Microsoft Windows 10,MySQL,MySQL 8,NodeJS,sql,Unix,Windows OS

Tagged with ,

Oracle Partitioned Tables

without comments

Oracle Partitioned Tables

Learning Outcomes

  • Learn about List Partitioning.
  • Learn about Range Partitioning.
  • Learn about Hash Partitioning.
  • Learn about Composite Partitioning.

Lesson Material

Partitioning is the process of breaking up a data source into a series of data sources. Partitioned tables are faster to access and transact against. Partitioning data becomes necessary as the amount of data grows in any table. It speeds the search to find rows and insert, update, or delete rows.

Oracle Database 21c supports four types of table partitioning: list, range, hash, and composite partitioning.

List Partitioning

A list partition works by identifying a column that contains a value, such as a STATE column in an ADDRESS table. Partitioning clauses follow the list of columns and constraints.

A list partition could use a STATE column, like the following (the complete example is avoided to conserve space, and the three dots represent the balance of partitions not shown):

CREATE TABLE franchise
( franchise_id    NUMBER CONSTRAINT pk_franchise PRIMARY KEY
, franchise_name  VARCHAR(20)
, city            VARCHAR(20)
, state           VARCHAR(20))
PARTITION BY LIST(state)
( PARTITION offshore VALUES('Alaska', 'Hawaii')
, PARTITION west VALUES('California', 'Oregon', 'Washington')
, PARTITION desert VALUES ('Arizona','New Mexico')
, PARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming')
, ... );

This can be used with other values such as ZIP codes with great effect, but the maintenance of list partitioning can be considered costly. Cost occurs when the list of values changes over time. Infrequent change means low cost, while frequent change means high costs. In the latter case, you should consider other partitioning strategies. Although an Oracle database supports partitioning on a variable-length string, MySQL performs list partitioning only on integer columns.

Range Partitioning

Range partitioning is very helpful on any column that contains a continuous metric, such as dates or time. It works by stating a minimum set that is less than a certain value, and then a group of sets of higher values until you reach the top most set of values. This type of partition helps you improve performance by letting you search ranges rather than complete data sets. Range partitioning is also available in MySQL.

A range example based on dates could look like this:

PARTITION BY RANGE(rental_date)
( PARTITION rental_jan2011
  VALUES LESS THAN TO_DATE('31-JAN-11','DD-MON-YY')
, PARTITION rental_feb2011
  VALUES LESS THAN TO_DATE('28-FEB-11','DD-MON-YY')
, PARTITION rental_mar2011
  VALUES LESS THAN TO_DATE('31-MAR-11','DD-MON-YY')
, ... );

The problem with this type of partitioning, however, is that the new months require constant management. Many North American businesses simply add partitions for all months in the year as an annual maintenance task during the holidays in November or December. Companies that opt for bigger range increments reap search and access benefits from range partitioning, while minimizing ongoing maintenance expenses.

Hash Partitioning

Hash partitioning is much easier to implement than list or range partitioning. Many DBAs favor it because it avoids the manual maintenance of list and range partitioning. Oracle Database 21c documentation recommends that you implement a hash for the following reasons:

  • There is no concrete knowledge about how much data maps to a partitioning range.
  • The sizes of partitions are unknown at the outset and difficult to balance as data is added to the database.
  • A range partition might cluster data in an ineffective way.

This next statement creates eight partitions and stores them respectively in one of the eight tablespaces. The hash partition manages nodes and attempts to balance the distribution of rows across the nodes.

PARTITION BY HASH(store)
PARTITIONS 8
STORE IN (tablespace1, tablespace2, tablespace3, tablespace4
         ,tablespace5, tablespace6, tablespace7, tablespace8);

As you can imagine the maintenance for this type of partitioning is low. Some DBAs choose this method to get an initial sizing before adopting a list or range partitioning plan. Maximizing the physical resources of the machine ultimately rests with the DBAs who manage the system. Developers need to stand ready to assist DBAs with analysis and syntax support.

Composite Partitioning

Composite partitioning requires a partition and subpartition. The composites are combinations of two types of partitioning—typically, list and range partitioning, or range and hash composite partitioning. Which of these you should choose depends on a few considerations. List and range composite partitioning is done for historical information and is well suited for data warehouses. This method lets you partition on unordered or unrelated column values.

A composite partition like this uses the range as the partition and the list as the subpartition, like the following:

PARTITION BY RANGE (rental_date)
 SUBPARTITION BY LIST (state)
 (PARTITION FQ1_1999 VALUES LESS THAN (TO_DATE('1-APR-2011','DD-MON-YYYY'))
  (SUBPARTITION offshore VALUES('Alaska', 'Hawaii')
  , SUBPARTITION west VALUES('California', 'Oregon', 'Washington')
  , SUBPARTITION desert VALUES ('Arizona','New Mexico')
  , SUBPARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming')
  , ... )
,(PARTITION FQ2_1999 VALUES LESS THAN (TO_DATE('1-APR-2011','DD-MON-YYYY'))
  (SUBPARTITION offshore VALUES('Alaska', 'Hawaii')
  , SUBPARTITION west VALUES('California', 'Oregon', 'Washington')
  , SUBPARTITION desert VALUES ('Arizona','New Mexico')
  , SUBPARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming')
  , ... )
, ... )

Range and hash composite partitioning is done for historical information when you also need to stripe data. Striping is the process of creating an attribute in a table that acts as a natural subtype or separator of data. Users typically view data sets of one subtype, which means organizing the data by stripes (subtypes) can speed access based on user access patterns.

Range is typically the partition and the hash is the subpartition in this composite partitioning schema. The syntax for this type of partition is shown next:

PARTITION BY RANGE (rental_date)
 SUBPARTITION BY HASH(store)
  SUBPARTITIONS 8 STORE IN (tablespace1, tablespace2, tablespace3
                           ,tablespace4, tablespace5, tablespace6
                           ,tablespace7, tablespace8)
   ( PARTITION rental_jan2011
     VALUES LESS THAN TO_DATE('31-JAN-11','DD-MON-YY')
   , PARTITION rental_feb2011
     VALUES LESS THAN TO_DATE('28-FEB-11','DD-MON-YY')
   , PARTITION rental_mar2011
     VALUES LESS THAN TO_DATE('31-MAR-11','DD-MON-YY')
   , ... )

Written by maclochlainn

May 30th, 2022 at 9:38 pm

Posted in Database,DBA,DevOps,Linux,Oracle,Oracle 21c,sql,Unix,Windows 10

Tagged with ,

Logging Table Function

with one comment

It is interesting when somebody remembers a presentation from 10 years ago. They asked if it was possible in PL/pgSQL to write an autonomous procedure to log data when calling a table view function. The answer is two fold. PL/pgSQL doesn’t support autonomous functions or procedures like the Oracle database but it doesn’t need to because unless you invoke a transaction it auto commits writes.

Logging table functions are important for security auditing and compliance management against laws, like SOX, HIPAA, and FERPA. All too many systems lack the basic ability to audit who queries records without raising an error and blocking the access. That means the bad actor or actress gains the ability to probe the system for weaknesses before determining an attack vector. It’s often better to capture the unauthorized access and take direct action to protect both the the data and systems.

While the example lets an unauthorized person access the information in the first version of the student_query, it blocks access by reporting no rows returned in the latter. Both versions of the query log the data and thereby collect the evidence necessary to act against the hack.

This blog post shows you how to write it and test it. Follow the following steps:

  1. Create the necessary tables and data to work with a logging PL/pgSQL table view function: 

    /* Conditionally drop and create table. */
DROP TABLE IF EXISTS student;
CREATE TABLE student
( student_id      SERIAL
, first_name      VARCHAR(20)
, last_name       VARCHAR(20)
, hogwarts_house  VARCHAR(10));
 
/* Conditionally drop and create table. */
DROP TABLE IF EXISTS logger;
CREATE TABLE logger
( logger_id        SERIAL
, app_user         VARCHAR(30)
, queried_student  VARCHAR(30)
, query_time       TIMESTAMP );
 
/* Insert one record into table. */
INSERT INTO student
( first_name, last_name, hogwarts_house )
VALUES
 ( 'Harry', 'Potter', 'Gryffindor' )
,( 'Hermione', 'Granger', 'Gryffindor' )
,( 'Ronald', 'Weasily', 'Gryffindor' )
,( 'Draco', 'Malfoy', 'Slytherin' )
,( 'Vincent', 'Crabbe', 'Slytherin' )
,( 'Susan', 'Bones', 'Hufflepuff' )
,( 'Hannah', 'Abbott', 'Hufflepuff' )
,( 'Luna', 'Lovegood', 'Ravenclaw' )
,( 'Cho', 'Chang', 'Ravenclaw' )
,( 'Gilderoy', 'Lockhart', 'Ravenclaw' );
  2. While not necessary if you’re very familiar with PL/pgSQL, it may be helpful to review:

    • The SET command that lets you assign a value to a session-level variable, which you can later use in a PL/pgSQL block.
    • The SELECT-INTO statement in a DO-block.

    Here’s a test script that demonstrates both:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

    /* Set a session-level variable. */
SET credential.app_user = 'Draco Malfoy';
 
/* Secure the value from a session-level variable. */
SELECT current_setting('credential.app_user');
 
/* 
DO
$$
DECLARE
  input   VARCHAR(30) := 'Hermione';
  output  VARCHAR(30);
BEGIN
  /* Sample for partial name construction of full name. */
  SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name
  INTO   output
  FROM   student s
  WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||input||'%';
 
  /* Show result of local assignment via a query. */
  RAISE NOTICE '[%][%]', current_setting('credential.app_user'), output;
END;
$$;

    There’s an important parsing trick to this sample program. It uses the LIKE operator rather than the SIMILAR TO operator because the parser fails to recognize the SIMILAR TO operator.

    The DO-block returns the following output:

    NOTICE:  [Draco Malfoy][Hermione Granger]
  3. This creates the student_query logging table function, which takes a partial portion of a students first and last name to return the student information. While the example only returns the name and the Hogwarts House it lays a foundation for a more complete solution. 

    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

    CREATE OR REPLACE
  FUNCTION student_query (partial_name  VARCHAR)
  RETURNS TABLE ( first_naem      VARCHAR(20)
                , last_name       VARCHAR(20)
                , hogwarts_house  VARCHAR(10) ) AS
$$
DECLARE
  queried   VARCHAR;
  by_whome  VARCHAR;
BEGIN
  /* Query separately because embedding in insert statement fails. */
  SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name
  FROM   student s INTO queried
  WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%';
 
  /* Log the query with the credentials of the user. */  
  INSERT INTO logger
  ( app_user
  , queried_student
  , query_time )
  VALUES
  ( current_setting('credential.app_user')
  , queried
  , NOW());
 
  /* Return the result set without disclosing the query was recorded. */
  RETURN QUERY
  SELECT s.first_name
  ,      s.last_name
  ,      s.hogwarts_house
  FROM   student s
  WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%';
END;
$$ LANGUAGE plpgsql;
  4. You can test the function by calling it, like this: 

    SELECT * FROM student_query('Hermione');

    It displays:

     first_naem | last_name | hogwarts_house
------------+-----------+----------------
 Hermione   | Granger   | Gryffindor
(1 row)

    You can check the logging table and discover who looked up another student’s records.

    SELECT * FROM logger;

    It displays:

     logger_id |   app_user   | queried_student  |         query_time
-----------+--------------+------------------+----------------------------
         1 | Draco Malfoy | Hermione Granger | 2022-05-29 22:51:50.398987
(1 row)
  5. Assuming you’ve built an authorized_user function that returns a Boolean, you can add a call to it in the WHERE clause. For simplicity, let’s implement the function to deny all users, like: 

    1
2
3
4
5
6
7
8
9
10

    CREATE OR REPLACE
  FUNCTION authorized_user
  (user_name  VARCHAR) RETURNS BOOLEAN AS
$$
DECLARE
  lv_retval  BOOLEAN := FALSE;
BEGIN
  RETURN lv_retval;
END;
$$  LANGUAGE plpgsql;

    You can now replace the query on lines 28 through 32 with the new one below. The added clause on line 33 denies access to unauthorized users because there aren’t any.

    28
29
30
31
32
33

      SELECT s.first_name
  ,      s.last_name
  ,      s.hogwarts_house
  FROM   student s
  WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%'
  AND    authorized_user(current_setting('credential.app_user'));

    While it returns:

     first_naem | last_name | hogwarts_house
------------+-----------+----------------
(0 rows)

    The logger table shows two entries. One for the query that returned a value and one for the version that didn’t.

     logger_id |   app_user   | queried_student  |         query_time
-----------+--------------+------------------+----------------------------
         1 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:39.82063
         2 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:40.736945
(2 rows)

    In both cases the bad actor Draco Malfoy’s unauthorized access is captured and he was denied any information without alerting him to the security precaution in a logging table function.

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

Written by maclochlainn

May 29th, 2022 at 11:16 pm

Posted in Database,Database Design,DBA,DevOps,Linux,Microsoft Windows 10,PL/pgSQL,Postgres,PostgreSQL,PostgreSQL 14,Unix,Windows 10,Windows OS

PostgreSQL Table Function

without comments

A quick tutorial on how to write a PL/pgSQL Table function. The functions is simple. It returns the list of conquistadors that were originally German. It does that by filtering on the lang column in the table. For example, you use ‘de‘ for German.

I’ll stage this with the same conquistador table used in the last post. Don’t forget to use the chcp command to the Active Console Code Page to 4-byte Unicode before you run the script file, like:

chcp 65001

Then, connect to the psql shell and run the following script file:

/* Conditionally drop the conquistador table. */
DROP TABLE IF EXISTS conquistador;
 
/* Create the conquistador table. */
CREATE TABLE conquistador
( conquistador_id   SERIAL
, conquistador      VARCHAR(30)
, actual_name       VARCHAR(30)
, nationality       VARCHAR(30)
, lang              VARCHAR(2));
 
/* Insert some conquistadors into the table. */
INSERT INTO conquistador
( conquistador
, actual_name
, nationality
, lang )
VALUES
 ('Juan de Fuca','Ioánnis Fokás','Greek','el')
,('Nicolás de Federmán','Nikolaus Federmann','German','de')
,('Sebastián Caboto','Sebastiano Caboto','Venetian','it')
,('Jorge de la Espira','Georg von Speyer','German','de')
,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it')
,('Wenceslao Linck','Wenceslaus Linck','Bohemian','cs')
,('Fernando Consag','Ferdinand Konšcak','Croatian','sr')
,('Américo Vespucio','Amerigo Vespucci','Italian','it')
,('Alejo García','Aleixo Garcia','Portuguese','pt');

Now, you can build another script file to create the getConquistador function, like:

/* Drop the funciton conditionally. */
DROP FUNCTION IF EXISTS getConquistador;

Create the getConquistador function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE FUNCTION getConquistador (IN lang_in VARCHAR(2))
  RETURNS TABLE
    ( conquistador      VARCHAR(30)
    , actual_name       VARCHAR(30)
    , nationality       VARCHAR(30)) AS
$$
BEGIN
  RETURN QUERY
  SELECT c.conquistador
  ,      c.actual_name
  ,      c.nationality
  FROM   conquistador c
  WHERE  c.lang = lang_in;
END;
$$ LANGUAGE plpgsql;

Then, you can test it like:

SELECT * FROM getConquistador('de');

It will return the following:

     conquistador      |    actual_name     | nationality
-----------------------+--------------------+-------------
 Nicolás de Federmán   | Nikolaus Federmann | German
 Jorge de la Espira    | Georg von Speyer   | German
(2 rows)

As always, I hope this helps with a technique that’s useful.

Written by maclochlainn

May 28th, 2022 at 5:59 pm

Posted in Database,DBA,DevOps,Linux,Microsoft Windows 10,Microsoft Windows 11,PL/pgSQL,Postgres,PostgreSQL,PostgreSQL 14,psql,Unix,Windows 10

Tagged with ,

PL/pgSQL Date Function

with 2 comments

This post provides an example of using PostgreSQL’s REGEXP_MATCH function, which works very much like the REGEXP_LIKE function in Oracle and a verify_date function that converts a string data type to date data type.

Here’s a basic function to show how to use a generic REGEXP_MATCH function:

1
2
3
4
5
6
7
8
9
10
11

DO
$$
DECLARE
  lv_date_in  DATE := '2022-10-22';
BEGIN
 
  IF (REGEXP_MATCH('2022-10-02','^[0-9]{4,4}-[0-9]{2,2}-[0-9]{2,2}$') IS NOT NULL) THEN
    RAISE NOTICE '[%]', 'Truth';
  END IF;
END;
$$;

The following is a verify_date function, which takes a string with the ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format and returns a BOOLEAN true or false value.

CREATE FUNCTION verify_date
  ( IN pv_date_in  VARCHAR(10)) RETURNS BOOLEAN AS
  $$
  DECLARE
    /* Local return variable. */
    lv_retval  BOOLEAN := FALSE;
  BEGIN
    /* Check for a YYYY-MM-DD or YYYY-MM-DD string. */
    IF REGEXP_MATCH(pv_date_in,'^[0-9]{2,4}-[0-9]{2,2}-[0-9]{2,2}$') IS NOT NULL THEN
 
      /* Case statement checks for 28 or 29, 30, or 31 day month. */
      CASE
        /* Valid 31 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND
              SUBSTRING(pv_date_in,6,2) IN ('01','03','05','07','08','10','12') AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 31) OR
             (LENGTH(pv_date_in) = 8 AND
              SUBSTRING(pv_date_in,4,2) IN ('01','03','05','07','08','10','12') AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 31) THEN 
          lv_retval := TRUE;
 
        /* Valid 30 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND
              SUBSTRING(pv_date_in,6,2) IN ('04','06','09','11') AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 30) OR
             (LENGTH(pv_date_in) = 8 AND
              SUBSTRING(pv_date_in,4,2) IN ('04','06','09','11') AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 30) THEN 
          lv_retval := TRUE;
 
        /* Valid 28 or 29 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND SUBSTRING(pv_date_in,6,2) = '02') OR
             (LENGTH(pv_date_in) =  8 AND SUBSTRING(pv_date_in,4,2) = '02') THEN
          /* Verify 4-digit year. */
          IF (LENGTH(pv_date_in) = 10 AND
              MOD(TO_NUMBER(SUBSTRING(pv_date_in,1,4),'99'),4) = 0 AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 29) OR
             (LENGTH(pv_date_in) =  8 AND
              MOD(TO_NUMBER(SUBSTRING(TO_CHAR(TO_DATE(pv_date_in,'YYYY-MM-DD'),'YYYY-MM-DD'),1,4),'99'),4) = 0 AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 29) THEN
            lv_retval := TRUE;
          ELSE /* Not a leap year. */
            IF (LENGTH(pv_date_in) = 10 AND
                TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 28) OR
               (LENGTH(pv_date_in) = 8 AND
                TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 28)THEN
              lv_retval := TRUE;
            END IF;
          END IF;
       NULL;
      END CASE;
    END IF;
 
    /* Return date. */
    RETURN lv_retval;
  END;
$$ LANGUAGE plpgsql;

The following four SQL test cases:

SELECT verify_date('2020-07-04') AS "verify_date('2020-07-04')";
SELECT verify_date('71-05-31')   AS "verify_date('71-05-31')";
SELECT verify_date('2024-02-29') AS "verify_date('2024-02-29')";
SELECT verify_date('2019-04-31') AS "verify_date('2019-04-31')";

Return the following:

 verify_date('2020-07-04')
---------------------------
 t
(1 row)
 
 
 verify_date('71-05-31')
-------------------------
 t
(1 row)
 
 
 verify_date('2024-02-29')
---------------------------
 t
(1 row)
 
 
 verify_date('2019-04-31')
---------------------------
 f
(1 row)

As always, I hope the example code fills somebody’s need.

Written by maclochlainn

May 25th, 2022 at 1:47 am

Posted in Database,DBA,Linux,PL/pgSQL,Postgres,PostgreSQL,PostgreSQL 14,Unix,Windows OS

Tagged with ,

PL/SQL List to Struct

without comments

Every now and then, I get questions from folks about how to tune in-memory elements of their PL/SQL programs. This blog post address one of those core issues that some PL/SQL programmers avoid.

Specifically, it addresses how to convert a list of values into a structure (in C/C++ its a struct, in Java its an ArrayList, and PL/SQL it’s a table of scalar or object types). Oracle lingo hides the similarity by calling either an Attribute Definition Type (ADT) or User-Defined Type (UDT). The difference in the Oracle space is that an ADT deals with a type defined in DBMS_STANDARD package, which is more or less like a primitive type in Java.

Oracle does this for two reasons:

The cast_strings function converts a list of strings into a record data structure. It lets the list of strings have either a densely or sparsely populated list of values, and it calls the verify_date function to identify a DATE data type and regular expressions to identify numbers and strings.

You need to build a UDT object type and lists of both ADT and UDT data types.

/* Create a table of strings. */
CREATE OR REPLACE
  TYPE tre AS TABLE OF VARCHAR2(20);
/
 
/* Create a structure of a date, number, and string. */
CREATE OR REPLACE
  TYPE struct IS OBJECT
  ( xdate     DATE
  , xnumber  NUMBER
  , xstring  VARCHAR2(20));
/
 
/* Create a table of tre type. */
CREATE OR REPLACE
  TYPE structs IS TABLE OF struct;
/

The cast_strings function is defined below:

CREATE OR REPLACE
  FUNCTION cast_strings
  ( pv_list  TRE ) RETURN struct IS
 
  /* Declare a UDT and initialize an empty struct variable. */
  lv_retval  STRUCT := struct( xdate => NULL
                             , xnumber => NULL
					         , xstring => NULL); 
  BEGIN  
    /* Loop through list of values to find only the numbers. */
    FOR i IN 1..pv_list.LAST LOOP
      /* Ensure that a sparsely populated list can't fail. */
      IF pv_list.EXISTS(i) THEN
        /* Order if number evaluation before string evaluation. */
        CASE
          WHEN lv_retval.xnumber IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:digit:]]*$') THEN
            lv_retval.xnumber := pv_list(i);
          WHEN verify_date(pv_list(i)) THEN
            IF lv_retval.xdate IS NULL THEN
              lv_retval.xdate := pv_list(i);
            ELSE
              lv_retval.xdate := NULL;
            END IF;
          WHEN lv_retval.xstring IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:alnum:]]*$') THEN
            lv_retval.xstring := pv_list(i);
          ELSE
            NULL;
        END CASE;
      END IF;
    END LOOP;
 
    /* Print the results. */
    RETURN lv_retval;
  END;
/

There are three test cases for this function:

  • The first use-case checks whether the input parameter is a sparsely or densely populated list: 

    DECLARE
  /* Declare an input variable of three or more elements. */
  lv_list    TRE := tre('Berlin','25','09-May-1945','45');
 
  /* Declare a variable to hold the compound type values. */
  lv_struct  STRUCT;
BEGIN
  /* Make the set sparsely populated. */
  lv_list.DELETE(2);
 
  /* Test the cast_strings function. */
  lv_struct := cast_strings(lv_list);
 
  /* Print the values of the compound variable. */
  dbms_output.put_line(CHR(10));
  dbms_output.put_line('xstring ['||lv_struct.xstring||']');
  dbms_output.put_line('xdate   ['||TO_CHAR(lv_struct.xdate,'DD-MON-YYYY')||']');
  dbms_output.put_line('xnumber ['||lv_struct.xnumber||']');
END;
/

    It should return:

    xstring [Berlin]
xdate   [09-MAY-1945]
xnumber [45]

    The program defines two numbers and deletes the first number, which is why it prints the second number.

  • The second use-case checks with a list of only one element: 

    SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate
,      xnumber
,      xstring
FROM   TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945'))));

    It should return:

    XDATE                   XNUMBER XSTRING
-------------------- ---------- --------------------
25-NOV-1945                  25 catch22

    The program returns a structure with values converted into their appropriate data type.

  • The third use-case checks with a list of two elements: 

    SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate
,      xnumber
,      xstring
FROM   TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945'))
                    ,cast_strings(tre('31-APR-2017','1918','areodromes'))));

    It should return:

    XDATE                   XNUMBER XSTRING
-------------------- ---------- --------------------
25-NOV-1945                  25 catch22
                           1918 areodromes

    The program defines calls the cast_strings with a valid set of values and an invalid set of values. The invalid set of values contains a bad date in the set of values.

As always, I hope this helps those looking for how to solve this type of problem.

Written by maclochlainn

May 23rd, 2022 at 12:18 am

Posted in Database,Database Design,DBA,DevOps,Linux,Oracle,Oracle 12c,Oracle 18c,Oracle 21c,Oracle DBA,Oracle Developer,Oracle XE,Unix,Windows OS

Tagged with ,

PL/SQL CASE Not Found

without comments

I was working on some test cases for my students and changing the behavior of a verify_date function that I wrote years ago to validate and returns valid dates when they’re passed as strings. The original program returned today’s date when the date was invalid.

The new function returns a BOOLEAN value of false by default and true when the string validates as a date. Unfortunately, I introduced a mistake that didn’t use to exist in Oracle 11g, which was the version when I wrote the original function.

The test cases in Oracle 21c raises the following error when an invalid date is passed to the CASE statement by the cast_strings function that calls the new verify_date function:

FROM   TABLE(structs(cast_strings(tre('31-APR-2017','1917','dirk'))))
                     *
ERROR AT line 2:
ORA-06592: CASE NOT found WHILE executing CASE statement
ORA-06512: AT "C##STUDENT.VERIFY_DATE", line 30
ORA-06512: AT "C##STUDENT.CAST_STRINGS", line 18

As you can see, the test case uses ’31-APR-2017′ as an incorrect date to verify the use-case. The error occurred because the ELSE clause in the CASE statement wasn’t provided. Previously, the ELSE clause was optional and setting the lv_retval return variable to FALSE in the DECLARE block made it unnecessary.

The fixed code follows:

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

CREATE OR REPLACE
  FUNCTION verify_date
  ( pv_date_in  VARCHAR2) RETURN BOOLEAN IS
 
  /* Local variable to ensure case-insensitive comparison. */
  lv_date_in  VARCHAR2(11);
 
  /* Local return variable. */
  lv_date  BOOLEAN := FALSE;
BEGIN
  /* Convert string input to uppercase month. */
  lv_date_in := UPPER(pv_date_in);
 
  /* Check for a DD-MON-RR or DD-MON-YYYY string. */
  IF REGEXP_LIKE(lv_date_in,'^[0-9]{2,2}-[ADFJMNOS][ACEOPU][BCGLNPRTVY]-([0-9]{2,2}|[0-9]{4,4})$') THEN
    /* Case statement checks for 28 or 29, 30, or 31 day month. */
    CASE
      /* Valid 31 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) IN ('JAN','MAR','MAY','JUL','AUG','OCT','DEC') AND
           TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 31 THEN 
        lv_date := TRUE;
      /* Valid 30 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) IN ('APR','JUN','SEP','NOV') AND
           TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 30 THEN 
        lv_date := TRUE;
      /* Valid 28 or 29 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) = 'FEB' THEN
        /* Verify 2-digit or 4-digit year. */
        IF (LENGTH(pv_date_in) = 9 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,2)) + 2000,4) = 0 OR
            LENGTH(pv_date_in) = 11 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,4)),4) = 0) AND
            TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 29 THEN
          lv_date := TRUE;
        ELSE /* Not a leap year. */
          IF TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 28 THEN
            lv_date := TRUE;
          END IF;
        END IF;
      ELSE
        NULL;
    END CASE;
  END IF;
  /* Return date. */
  RETURN lv_date;
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN lv_date;
END;
/

The new ELSE clause in on lines 31 and 32, and the converted function works. I also added a local lv_date_in variable to hold an uppercase version of an input string to: ensure a case-insensitive comparison of the month value, and avoid a having to pass the input as an IN OUT mode parameter. Typically, I leave off exception handlers because mistyping or copying for newer programmers becomes easier, but in this case I added an exception handler for strings that are larger than 11-characters.

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

Written by maclochlainn

May 22nd, 2022 at 5:41 pm

Posted in Database,Database Design,DBA,DevOps,Linux,Oracle,Oracle 12c,Oracle 18c,Oracle 21c,pl/sql,Unix,Windows OS

Tagged with ,

« Older Entries
Newer Entries »