MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Linux’ Category

pgAdmin4 on Fedora 30

without comments

While attempting an install of pgAdmin and updating a Fedora 30 environment, I encountered a conflict on the upgrade of MySQL 8.0.17-1 to 8.0.17.2. The community-mysql-8.0.17-2.fc30.x86_64 had conflicts with:

  • mysql-community-client-8.0.17-1.fc30.x86_64 package
  • mysql-community-server-8.0.17-1.fc30.x86_64 package

I tried to update the system before install pgadmin4 with the following syntax:

dnf -y update && dnf -y install pgadmin4

The dnf utility raise the following MySQL package errors during transaction checking:

Display detailed console log →

Error: Transaction check error:
  file /usr/bin/mysql conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_config_editor conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqladmin conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlbinlog conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlcheck conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqldump conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlimport conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlpump conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlshow conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlslap conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/ibd2sdi conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/innochecksum conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/my_print_defaults conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisam_ftdump conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisamchk conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisamlog conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisampack conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_secure_installation conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_ssl_rsa_setup conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_tzinfo_to_sql conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_upgrade conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqld_pre_systemd conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/perror conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib/systemd/system/mysqld.service conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib/systemd/system/mysqld@.service conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/adt_null.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/auth_socket.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_audit_api_message_emit.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_log_filter_dragnet.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_log_sink_json.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_log_sink_syseventlog.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_validate_password.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/connection_control.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/ddl_rewriter.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/group_replication.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/ha_example.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/ha_mock.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/innodb_engine.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/keyring_file.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/keyring_udf.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/libmemcached.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/locking_service.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/mypluglib.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/mysql_clone.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/mysql_no_login.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/rewrite_example.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/rewriter.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/semisync_master.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/semisync_slave.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/validate_password.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/version_token.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/sbin/mysqld conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /var/lib/mysql conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /var/lib/mysql-keyring conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/libmysqlclient.so.21.1.17 conflicts between attempted installs of community-mysql-libs-8.0.17-2.fc30.x86_64 and mysql-community-libs-8.0.17-1.fc30.x86_64

Since I’m not sure what’s wrong or how to fix it, I’ve put it in my queue of things to get to later. However, when I figure it out I’ll update this blog page with the solution or work around. If anybody knows the fix and would like to share, please let me know.

I removed the pending update packages with the following command:

dnf clean packages

Then, I simply installed pgadmin4 with the following command:

dnf -y install pgadmin4

Display detailed console log →

 

The pgadmin4 configuration instructions can be found for several Linux versions at Josphat Mutai’s Computing for Geeks web page. On Fedora 30, you need to do the following:

  • Install, start, and enable Apache as the httpd service unless you already have done that.
  • Copy the /etc/httpd/conf.d/pgadmin4.conf.sample file to /etc/httpd/conf.d/pgadmin4.conf, which is a new file.
  • Restart the httpd service to incorporate the pgadmin4 configuration file.

After that, you create the following new directories as the root or sudo user:

  • /var/lib/pgadmin4
  • /var/log/pgadmin4

You can make both directories with a single mkdir command, like:

mkdir -p /var/lib/pgadmin4 /var/log/pgadmin4

As the root or sudo user, change the ownership of these two directories to the apache user with the following syntax:

chown -R apache:apache /var/lib/pgadmin4 /var/log/pgadmin4

You add the following four statements to the config_distro.py file in the /usr/lib/python3.7/site-packages/pgadmin4-web directory as the root or sudo user:

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'

You need to setup the pgadmin user with the following python3 command:

python3 /usr/lib/python3.7/site-packages/pgadmin4-web/setup.py

Enter the following values, a real email address and a password twice:

NOTE: Configuring authentication for SERVER mode.
 
Enter the email address and password to use for the initial pgAdmin user account:
 
Email address: admin@example.com   
Password: your_password
Retype password: your_password
pgAdmin 4 - Application Initialisation
======================================

Assuming you have an enabled firewall, you need to issue the following two commands as the root or sudo user:

rirewall-cmd --permanent --add-service=http
firewall-cmd --reload

You invoke pgAdmin4 from within a browser window with the following URL for a stand alone workstation (for a workstation on a DNS network you would enter pgadmin.domain.domain_type in lieu of localhost):

pgadmin/localhost/pgadmin4

You most likely will encounter an Internal Server Error, the recommended fix is reputed to be:

ausearch -c 'httpd' --raw | audit2allow -M my-httpd
semodule -X 300 -i my-httpd.pp

It didn’t work for me. At the end of the process, I have an Internal Server Error. It is something that I’ll try to fix next. The actual error message:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
 
Please contact the server administrator at root@localhost to inform them of the time this error occurred, and the actions you performed just before this error.
 
More information about this error may be available in the server error log.

If somebody figures out the last step before I do, that’s great. Let me and everybody else know the mystery.

On a positive note, the pgadmin4 package provided the psycopg2 library. I had looked for it as a psycopg2 package but it is in python3-psycopg2 package.

Written by maclochlainn

September 21st, 2019 at 5:29 pm

Posted in Linux,MySQL,MySQL 8,pgAdmin4,Postgres,Python,Python 2.7,Python 3.x

Tagged with , , ,

Postgres & Sequences

without comments

Many developers already know about the change from int to serial in Postgres. Serial is simpler because it automatically makes the column not null and links it to a sequence. The sequence is created with the following naming convention:

table_name_column_name_seq

The Postgres serial type always has a start with value of 1. Though, you can alter it immediately after creating the table.

You can create a serial surrogate key column like this:

CREATE TABLE new_hire
( new_hire_id  SERIAL        CONSTRAINT new_hire_pk PRIMARY KEY
, first_name   VARCHAR(20)   NOT NULL
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20)   NOT NULL
, hire_date    DATE          NOT NULL
, UNIQUE(first_name, middle_name, hire_date));

If you want the sequence to start with 1001, you can ALTER the sequence after creating the table. Before you change the sequence staring value, you may want to confirm the automatic name assigned to the sequence. You can do that with the following query:

SELECT pg_get_serial_sequence('new_hire','new_hire_id');

It should return:

     pg_get_serial_sequence      
---------------------------------
 public.new_hire_new_hire_id_seq
(1 row)

Here’s the syntax for changing the current starting value of a Postgres sequence:

ALTER SEQUENCE new_hire_new_hire_id_seq RESTART WITH 1001;

If you want more control, you still have it with the older syntax. The older way requires that you create the sequence before you create the table, like this:

CREATE SEQUENCE new_hire_s START WITH 1001;

Then, you create the new_hire table like this:

CREATE TABLE new_hire
( new_hire_id  INT           NOT NULL DEFAULT NEXTVAL('new_hire_s')
, first_name   VARCHAR(20)   NOT NULL
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20)   NOT NULL
, hire_date    DATE          NOT NULL
, PRIMARY KEY(new_hire_id)
, UNIQUE(first_name, middle_name, hire_date));

You probably will test this from a file because tables are tedious type and usually much larger than the sample new_hire table. If you wonder how to do that, typing help from the psql> prompt will not reveal it. The key is the \i command, like this:

\i new_hire.sql

Unfortunately, the psql> command-line shell does not allow for embedding a means to log the activity of your script file. You must do that from the Linux command-line through redirecting the console to a log file.

The rules are pretty simple for logging file results:

 -a, --echo-all       echo all input from script
 -b, --echo-errors    echo failed commands
 -e, --echo-queries   echo commands sent to the server

Here’s an example connecting to a videodb database with a student user:

psql -d videodb -U student -W -f postgres_tables.sql -a &> postgres_tables.txt

As always, I hope this helps those looking for how to solve the puzzle of a new environment.

Written by maclochlainn

September 16th, 2019 at 12:39 am

Posted in Linux,Postgres,PostgreSQL,sql

Tagged with ,

Cassandra on Fedora 30

without comments

The first thing to do with Fedora 30 is to check what part of Apache Cassandra is installed. You can use the following rpm command to determine that:

rpm -qa | grep cassandra

My Fedora 30 returned the following values:

cassandra-java-libs-3.11.1-12.fc30.x86_64
cassandra-python2-cqlshlib-3.11.1-12.fc30.x86_64
cassandra-3.11.1-12.fc30.x86_64
python2-cassandra-driver-3.18.0-1.fc30.x86_64

Notably missing from the list of rpm list is the cassandra-server package. You install cassandra-server with the def utility:

dnf install -y cassandra-server

You should get an installation log like the following for the cassandra-server package:

Display detailed console log →

Last metadata expiration check: 0:26:07 ago on Wed 11 Sep 2019 09:10:08 PM MDT.
Package cassandra-3.11.1-12.fc30.x86_64 is already installed.
Dependencies resolved.
===========================================================================================================================
 Package                        Architecture         Version                                    Repository            Size
===========================================================================================================================
Installing:
 cassandra-server               x86_64               3.11.1-12.fc30                             fedora               180 k
Installing dependencies:
 sigar                          x86_64               1.6.5-0.20.git58097d9.fc27                 fedora                76 k
 
Transaction Summary
===========================================================================================================================
Install  2 Packages
 
Total download size: 255 k
Installed size: 738 k
Is this ok [y/N]: y
Downloading Packages:
(1/2): sigar-1.6.5-0.20.git58097d9.fc27.x86_64.rpm                                         131 kB/s |  76 kB     00:00    
(2/2): cassandra-server-3.11.1-12.fc30.x86_64.rpm                                          233 kB/s | 180 kB     00:00    
---------------------------------------------------------------------------------------------------------------------------
Total                                                                                      116 kB/s | 255 kB     00:02     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                   1/1 
  Installing       : sigar-1.6.5-0.20.git58097d9.fc27.x86_64                                                           1/2 
  Running scriptlet: sigar-1.6.5-0.20.git58097d9.fc27.x86_64                                                           1/2 
  Running scriptlet: cassandra-server-3.11.1-12.fc30.x86_64                                                            2/2 
  Installing       : cassandra-server-3.11.1-12.fc30.x86_64                                                            2/2 
  Running scriptlet: cassandra-server-3.11.1-12.fc30.x86_64                                                            2/2 
  Verifying        : cassandra-server-3.11.1-12.fc30.x86_64                                                            1/2 
  Verifying        : sigar-1.6.5-0.20.git58097d9.fc27.x86_64                                                           2/2 
 
Installed:
  cassandra-server-3.11.1-12.fc30.x86_64                      sigar-1.6.5-0.20.git58097d9.fc27.x86_64                     
 
Complete!

Fedora Magazine has a great Get Started with Apache Cassandra on Fedora article on all the steps required to setup clusters. This article only covers creating and enabling the Cassandra service, and setting up a single node Cassandra instance.

You start Cassandra with the following command as the root user:

systemctl start cassandra

You enable Cassandra with the following command as the root user:

systemctl enable cassandra

It creates the following symlink:

Created symlink /etc/systemd/system/multi-user.target.wants/cassandra.service → /usr/lib/systemd/system/cassandra.service.

You can connect to the Test cluster with the following command:

cqlsh

You should see the following:

Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.11.1 | CQL spec 3.4.4 | Native protocol v4]
Use HELP for help.

You can see the options by typing the help command:

Documented shell commands:
===========================
CAPTURE  CLS          COPY  DESCRIBE  EXPAND  LOGIN   SERIAL  SOURCE   UNICODE
CLEAR    CONSISTENCY  DESC  EXIT      HELP    PAGING  SHOW    TRACING
 
CQL help topics:
================
AGGREGATES               CREATE_KEYSPACE           DROP_TRIGGER      TEXT     
ALTER_KEYSPACE           CREATE_MATERIALIZED_VIEW  DROP_TYPE         TIME     
ALTER_MATERIALIZED_VIEW  CREATE_ROLE               DROP_USER         TIMESTAMP
ALTER_TABLE              CREATE_TABLE              FUNCTIONS         TRUNCATE 
ALTER_TYPE               CREATE_TRIGGER            GRANT             TYPES    
ALTER_USER               CREATE_TYPE               INSERT            UPDATE   
APPLY                    CREATE_USER               INSERT_JSON       USE      
ASCII                    DATE                      INT               UUID     
BATCH                    DELETE                    JSON            
BEGIN                    DROP_AGGREGATE            KEYWORDS        
BLOB                     DROP_COLUMNFAMILY         LIST_PERMISSIONS
BOOLEAN                  DROP_FUNCTION             LIST_ROLES      
COUNTER                  DROP_INDEX                LIST_USERS      
CREATE_AGGREGATE         DROP_KEYSPACE             PERMISSIONS     
CREATE_COLUMNFAMILY      DROP_MATERIALIZED_VIEW    REVOKE          
CREATE_FUNCTION          DROP_ROLE                 SELECT          
CREATE_INDEX             DROP_TABLE                SELECT_JSON

Here’s my script that creates Cassandra keyspace, which is more or less a database. You use the USE command to connect to the keyspace or database, like you would in MySQL. You do not have sequences in Cassandra because they’re not a good fit for a distributed architecture. Cassandra does not support a native procedural extension like relational databases. You must create User-defined functions (UDFs) by embedding the logic in Java.

This script does the following:

  • Creates a keyspace
  • Uses the keyspace
  • Conditionally drops tables and functions
  • Creates two tables
  • Inserts data into the two tables
  • Queries data from the tables

I also included a call to a UDF inside a query in two of the examples. One of the queries demonstrates how to return a JSON structure from a query. To simplify things and provide clarification of the scripts behaviors, the details are outlined below.

  • The first segment of the script creates the keyspace, changes the scope to use the keyspace, conditionally drop tables, create tables, and insert values into the tables: 

    /* Create a keyspace in Cassandra, which is like a database
   in MySQL or a schema in Oracle. */
CREATE KEYSPACE IF NOT EXISTS student
  WITH REPLICATION = {
     'class':'SimpleStrategy'
    ,'replication_factor': 1 }
  AND DURABLE_WRITES = true;
 
/* Use the keyspace or connect to the database. */
USE student;
 
/* Drop the member table from the student keyspace. */
DROP TABLE IF EXISTS member;
 
/* Create a member table in the student keyspace. */
CREATE TABLE member
( member_number       VARCHAR
, member_type         VARCHAR
, credit_card_number  VARCHAR
, credit_card_type    VARCHAR
, PRIMARY KEY ( member_number ));
 
/* Conditionally drop the contact table from the student keyspace. */
DROP TABLE IF EXISTS contact;
 
/* Create a contact table in the student keyspace. */
CREATE TABLE contact
( contact_number      VARCHAR
, contact_type        VARCHAR
, first_name          VARCHAR
, middle_name         VARCHAR
, last_name           VARCHAR
, member_number       VARCHAR
, PRIMARY KEY ( contact_number ));
 
/* Insert a row into the member table. */
INSERT INTO member
( member_number, member_type, credit_card_number, credit_card_type )
VALUES
('SFO-12345','GROUP','2222-4444-5555-6666','VISA');
 
/* Insert a row into the contact table. */
INSERT INTO contact
( contact_number, contact_type, first_name, middle_name, last_name, member_number )
VALUES
('CUS_00001','FAMILY','Barry', NULL,'Allen','SFO-12345');
 
/* Insert a row into the contact table. */
INSERT INTO contact
( contact_number, contact_type, first_name, middle_name, last_name, member_number )
VALUES
('CUS_00002','FAMILY','Iris', NULL,'West-Allen','SFO-12345');
 
/* Insert a row into the member table. */
INSERT INTO member
( member_number, member_type, credit_card_number, credit_card_type )
VALUES
('SFO-12346','GROUP','3333-8888-9999-2222','VISA');
 
/* Insert a row into the contact table. */
INSERT INTO contact
( contact_number, contact_type, first_name, middle_name, last_name, member_number )
VALUES
('CUS_00003','FAMILY','Caitlin','Marie','Snow','SFO-12346');

    The following queries the member table:

    /* Select all columns from the member table. */
SELECT * FROM member;

    It returns the following:

     member_number | credit_card_number  | credit_card_type | member_type
---------------+---------------------+------------------+-------------
     SFO-12345 | 2222-4444-5555-6666 |             VISA |       GROUP
     SFO-12346 | 3333-8888-9999-2222 |             VISA |       GROUP
  • Create a concatenate User-defined function (UDF) for Cassandra. The first step requires you to edit the cassandra.yaml file, which you find in the /etc/cassandra/default.conf directory. There is a single parameter that you need to edit, and it is the enable_user_defined_functions parameter. By default the parameter is set to false, and you need to enable it to create UDFs.

    If you open the cassandra.yaml file as the root user, you should find the parameter on line 987, like:

    983
984
985
986
987

    # If unset, all GC Pauses greater than gc_log_threshold_in_ms will log at
# INFO level
# UDFs (user defined functions) are disabled by default.
# As of Cassandra 3.0 there is a sandbox in place that should prevent execution of evil code.
enable_user_defined_functions: false

    After you make the edit, the cassandra.yaml file should look like this:

    983
984
985
986
987

    # If unset, all GC Pauses greater than gc_log_threshold_in_ms will log at
# INFO level
# UDFs (user defined functions) are disabled by default.
# As of Cassandra 3.0 there is a sandbox in place that should prevent execution of evil code.
enable_user_defined_functions: true

    After you make the change, you can create your own UDF. The following UDF formats the first, middle, and last name so there’s only one whitespace between the first and last name when there middle name value is null.

    This type of function must use a CALLED ON NULL INPUT clause in lieu of a RETURNS NULL ON NULL INPUT clause. The latter would force the function to return a null value if any one of the parameters were null.

    /* Drop the concatenate function because a replace disallows changing a
   RETURNS NULL ON NULL INPUT with a CALLED ON NULL INPUT without raising
   an "89: InvalidRequest" exception. */
DROP FUNCTION concatenate;
 
/* Create a user-defined function to concatenate names. */
CREATE OR REPLACE FUNCTION concatenate (first_name VARCHAR, middle_name VARCHAR, last_name VARCHAR)
CALLED ON NULL INPUT
RETURNS VARCHAR
LANGUAGE java
AS $$
  /* Concatenate first and last names when middle name is null, and
     first, middle, and last names when middle name is not null. */
  String name;
 
  /* Check for null middle name. */
  if (middle_name == null) {
    name = first_name + " " + last_name; }
  else {
    name = first_name + " " + middle_name + " " + last_name; }
 
  return name;
$$;

    • Query the values from the contact table with the UDF function in the SELECT-list:

    /* Query the contact information. */
SELECT member_number
,      contact_number
,      contact_type
,      concatenate(first_name, middle_name, last_name) AS full_name
FROM   contact;

    It returns the following:

     member_number | contact_number | contact_type | full_name
---------------+----------------+--------------+--------------------
     SFO-12345 |      CUS_00001 |       FAMILY |        Barry Allen
     SFO-12345 |      CUS_00002 |       FAMILY |    Iris West-Allen
     SFO-12346 |      CUS_00003 |       FAMILY | Caitlin Marie Snow

    Query the values from the contact table with a JSON format:

    /* Query the contact information and return in a JSON format. */
SELECT JSON
       contact_number
,      contact_type
,      concatenate(first_name, middle_name, last_name) AS full_name
FROM   contact;

    It returns the following:

     [json]
-------------------------------------------------------------------------------------------------
{"contact_number": "CUS_00001", "contact_type": "FAMILY", "full_name": "Barry Allen"}
{"contact_number": "CUS_00002", "contact_type": "FAMILY", "full_name": "Iris West-Allen"}
{"contact_number": "CUS_00003", "contact_type": "FAMILY", "full_name": "Caitlin Marie Snow"}

Written by maclochlainn

September 12th, 2019 at 1:17 am

Posted in Cassandra,cqlsh,Fedora,Java,Java 8,Linux

Tagged with ,

Django on Fedora 30

without comments

It seemed opportune to add Django to the Fedora 30 instance that I build and maintain for my students. Here are the instructions, which I developed with the prior Fedora 28/29 instructions.

  1. Check your Python3 installation with the following command: 

    python3 -V

    It should return this but if it doesn’t you should install python3:

    Python 3.7.4

  2. Check whether pip3 is installation by installing it when its not: 

    sudo def -y install python3-php

    It should return:

    Last metadata expiration check: 0:44:52 ago on Tue 10 Sep 2019 11:02:33 AM MDT.
Package python3-pip-19.0.3-3.fc30.noarch is already installed.
Dependencies resolved.
Nothing to do.
Complete!

  3. Check whether Django is installation by installing it when its not with pip3 installation utility: 

    sudo pip3 install --user Django

    It should return the following if installed:

    Requirement already satisfied: Django in /usr/lib/python3.7/site-packages (2.1.10)
Requirement already satisfied: pytz in /usr/lib/python3.7/site-packages (from Django) (2018.5)

  4. Check your django-admin account location with the which utility: 

    which django-admin

    It should return the following on Fedora 30 when installed:

    /usr/bin/django-admin

  5. Create a Django test application with the django-admin utility by creating a project directory. My directory is a bit deep. For reference, it is: 

    /home/student/Code/python/django/projects

    Change to that projects directory, and run the following command:

    django-admin startproject test_app

    After that command change directory with the cd command into the test_app subdirectory in your projects directory. Run the manage.py program with the following command:

    python3 manage.py migrate

    You should see the following:

    Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying sessions.0001_initial... OK

Next, your would create an admin account. You’re done.

Written by maclochlainn

September 10th, 2019 at 12:47 pm

Posted in Django,Fedora,LAMP,Linux,Python,Python 3.x

Tagged with

Python MySQL Query

without comments

Somebody asked me how to expand a prior example with the static variables so that it took arguments at the command line for the variables. This example uses Python 3 new features in the datetime package.

There’s a small trick converting the string arguments to date data types. Here’s a quick example that shows you how to convert the argument list into individual date data type variables:

#!/usr/bin/python3
 
# include standard modules
import sys
from datetime import datetime
 
# Capture argument list.
fullCmdArguments = sys.argv
 
# Assignable variables.
beginDate = ""
endDate = ""
 
# Assign argument list to variable.
argumentList = fullCmdArguments[1:]
 
# Enumerate through the argument list where beginDate precedes endDate as strings.
try:
  for i, s in enumerate(argumentList):
    if (i == 0):
      beginDate = datetime.date(datetime.fromisoformat(s))
    elif (i == 1):
      endDate = datetime.date(datetime.fromisoformat(s))
except ValueError:
  print("One of the first two arguments is not a valid date (YYYY-MM-DD).")
 
# Print the processed values and types.
print("Begin Date: [",beginDate,"][",type(beginDate),"]")
print("End Date:   [",endDate,"][",type(endDate),"]")

Assume you call this arguments.py. Then, you call it with valid conforming date format value like the following command-line example:

./arguments.py 2001-01-01 2003-12-31

It returns the arguments after they have been converted to date data types. The results should look like this:

Begin Date:  1991-01-01 [ <class 'datetime.date'> ]
End Date:    2004-12-31 [ <class 'datetime.date'> ]

The next Python example accepts dynamic arguments at the command line to query the MySQL database:

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

#!/usr/bin/python3
 
# Import the library.
import sys
import mysql.connector
from datetime import datetime
from datetime import date
from mysql.connector import errorcode
 
# Capture argument list.
fullCmdArguments = sys.argv
 
# Assignable variables.
start_date = ""
end_date = ""
 
# Assign argument list to variable.
argumentList = fullCmdArguments[1:]
 
#  Check and process argument list.
# ============================================================
#  If there are less than two arguments provide default values.
#  Else enumerate and convert strings to dates.
# ============================================================
if (len(argumentList) < 2):
  # Set a default start date.
  if (isinstance(start_date,str)):
    start_date = date(1980, 1, 1)
 
  # Set the default end date.
  if (isinstance(end_date,str)):
    end_date = datetime.date(datetime.today())
else:
  # Enumerate through the argument list where beginDate precedes endDate as strings.
  try:
    for i, s in enumerate(argumentList):
      if (i == 0):
        start_date = datetime.date(datetime.fromisoformat(s))
      elif (i == 1):
        end_date = datetime.date(datetime.fromisoformat(s))
  except ValueError:
    print("One of the first two arguments is not a valid date (YYYY-MM-DD).")
 
#  Attempt the query.
# ============================================================
#  Use a try-catch block to manage the connection.
# ============================================================
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='studentdb')
  # Create cursor.
  cursor = cnx.cursor()
 
  # Set the query statement.
  query = ("SELECT CASE "
           "         WHEN item_subtitle IS NULL THEN item_title "
           "         ELSE CONCAT(item_title,': ',item_subtitle) "
           "         END AS title, "
           "release_date "
           "FROM item "
           "WHERE release_date BETWEEN %s AND %s "
           "ORDER BY item_title")
 
  # Execute cursor.
  cursor.execute(query, (start_date, end_date))
 
  # Display the rows returned by the query.
  for (item_name, release_date) in cursor:
    print("{}, {:%d-%b-%Y}".format(item_name, release_date))
 
#  Handle exception and close connection.
# ============================================================
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print("Error code:", e.errno)        # error number
    print("SQLSTATE value:", e.sqlstate) # SQLSTATE value
    print("Error message:", e.msg)       # error message
 
# Close the connection when the try block completes.
finally:
  cnx.close()

You can call the python-mysql-query.py program with the following syntax:

./python-mysql-query.py 2001-01-01 2003-12-31

It returns the films between 1 Jan 2001 and 31 Dec 2003, like this:

Clear and Present Danger: Special Collector's Edition, 06-May-2003
Die Another Day: 2-Disc Ultimate Version, 03-Jun-2003
Die Another Day, 03-Jun-2003
Die Another Day, 03-Jun-2003
Golden Eye, 03-Jun-2003
Golden Eye: Special Edition, 03-Jun-2003
Harry Potter and the Chamber of Secrets, 28-May-2002
Harry Potter and the Chamber of Secrets: Two-Disc Special Edition, 28-May-2002
Harry Potter and the Sorcerer's Stone, 28-May-2002
Harry Potter and the Sorcerer's Stone: Two-Disc Special Edition, 28-May-2002
Harry Potter and the Sorcerer's Stone: Full Screen Edition, 28-May-2002
MarioKart: Double Dash, 17-Nov-2003
Pirates of the Caribbean, 30-Jun-2003
RoboCop, 24-Jul-2003
Splinter Cell: Chaos Theory, 08-Apr-2003
Star Wars II: Attack of the Clones, 16-May-2002
Star Wars II: Attack of the Clones, 16-May-2002
The Chronicles of Narnia: The Lion, the Witch and the Wardrobe, 30-Jun-2003
The Chronicles of Narnia: The Lion, the Witch and the Wardrobe, 16-May-2002

As always, I hope this helps somebody who wants to learn how to use Python with the MySQL database.

Written by maclochlainn

September 6th, 2019 at 10:31 pm

Posted in Fedora,Linux,MySQL,MySQL 8,Python,Python 3.x

Tagged with ,

Postgres 11 Video DB

without comments

Installing PostgreSQL 11 on Fedora, Version 30, requires an update to my previous instructions to create a sandboxed user. A sandboxed user can only access a non-data dictionary database with a password. In the real world, rather than a personal test instance you would configure users to include aspects of networking. However, this post is only showing you how to connect from the local server.

This post builds on my PostgreSQL Installation blog post and shows you how to create a tablespace, database, role, and user. It also shows you how to change the default configuration for how users connect to the database.

The following steps create a tablespace, database, role, and user:

  1. Create tablespace

The directory for the data dictionary changes with PostgreSQL has changed. You can find it with the following command:

postgres=# show data_directory;

This will return the following:

     data_directory     
------------------------
 /var/lib/pgsql/11/data
(1 row)

You need to create a physical videoDB subdirectory in the /var/lib/pgsql/11 directory. You can use the following syntax from the /var/lib/pgsql/11 directory to create the videoDB subdirectory:

mkdir videoDB
  1. Create Tablespace

You can create a video_db tablespace with the following syntax:

CREATE TABLESPACE video_db
  OWNER postgres
  LOCATION '/var/lib/pgsql/11/videoDB';

This will return the following:

CREATE TABLESPACE

You can query whether you successfully create the video_db tablespace with the following:

SELECT * FROM pg_tablespace;

It should return the following:

  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 video_db   |       10 |        | 
(3 rows)
  1. Create a Database

You can create a videodb database with the following syntax:

CREATE DATABASE videodb
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = video_db
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;

You can verify the creation of the videodb with the following command:

postgres# \l

It should show you a display like the following:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 videodb   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | dba=CTc/postgres
(4 rows)

Then, you can assign comment to the database with the following syntax:

COMMENT ON DATABASE videodb IS 'Video Database';
  1. Create a Role, Grant, and User

In this section you create a dba role, grant privileges on a videodb database to a role, and create a user with the role that you created previously with the following three statements. There are three steps in this sections.

  • The first step creates a dba role: 

    CREATE ROLE dba WITH SUPERUSER;
  • The second step grants all privileges on a videodb database to a dba role: 

    GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;
  • The third step creates a student user with the dba role: 

    CREATE USER student
  WITH ROLE dba
       ENCRYPTED PASSWORD 'student';
  • It is possible that you may (and should if this is a new instance you are building) encounter an error when you try to connect as a sandboxed user. The syntax to connect as the student user is: 

    psql -d videodb -U student -W

    You may encounter this error:

    psql: FATAL:  Peer authentication failed for user "student"

    You can fix this in PostgreSQL 11 by changing the user access parameters in the pg_hba.conf configuration file. The file is found in the /var/lib/pgsql/11/data directory and you need to edit it as the root or postgres user.

    The default entry is:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# "local" is for Unix domain socket connections only
local   all             all                                    peer

    You should replace it with the following:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# "local" is for Unix domain socket connections only
local   postgres         all                                    peer
local   videodb          student                                md5

    After you save those changes in the pg_hba.conf file, you need to restart the PostgreSQL (postgresql-11) service. You can do that with the following command as the root user:

    service postgresql-11 restart

  1. Connect to the videodb as the student user

Once the postgresql-11 service is restarted, you can connect with the sandboxed student user with this syntax:

psql -d videodb -U student -W

If you did everything correctly, you should see the following after correctly providing the student password for the student user:

psql (11.4, server 11.5)
Type "help" for help.
 
videodb=>

After connecting to the videodb database, you can query the current database, like

SELECT current_database();

It should return the following:

 current_database 
------------------
 videodb
(1 row)

This has shown you how to create a videodb database, dba role, and student user.

Written by maclochlainn

September 5th, 2019 at 12:21 am

Posted in Fedora,Linux,PostgreSQL,psql,Unix

Tagged with ,

PostgreSQL on Fedora 30

with one comment

Installing PostreSQL 11 on Fedora 30 wasn’t straight forward but there were some instructions that helped. The first step requires you to update the yum repository, like this as the root user:

rpm -Uvh https://yum.postgresql.org/11/fedora/fedora-30-x86_64/pgdg-fedora-repo-latest.noarch.rpm

Then, you install the PostgreSQL with this command as the root user:

dnf install postgresql11-server

After installing the PostreSQL Server I got a few errors with the symbolic links failing to resolve in the log files. Then, I realized they only failed to create symbolic links because the fresh installation deploys executables directly to the /usr/bin directory.

Display detailed console log →

Retrieving https://yum.postgresql.org/11/fedora/fedora-30-x86_64/pgdg-fedora-repo-latest.noarch.rpm
warning: /var/tmp/rpm-tmp.MD4lRU: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:pgdg-fedora-repo-42.0-4          ################################# [100%]
[root@localhost ~]# dnf install postgresql11-server
PostgreSQL 11 30 - x86_64                                                         215 kB/s | 585 kB     00:02    
PostgreSQL 10 30 - x86_64                                                         199 kB/s | 541 kB     00:02    
PostgreSQL 9.6 30 - x86_64                                                        295 kB/s | 515 kB     00:01    
PostgreSQL 9.5 30 - x86_64                                                        179 kB/s | 495 kB     00:02    
PostgreSQL 9.4 30 - x86_64                                                        269 kB/s | 469 kB     00:01    
Last metadata expiration check: 0:00:01 ago on Mon 19 Aug 2019 02:25:56 AM MDT.
Dependencies resolved.
==================================================================================================================
 Package                           Architecture         Version                        Repository            Size
==================================================================================================================
Installing:
 postgresql11-server               x86_64               11.5-1PGDG.f30                 pgdg11               4.8 M
Installing dependencies:
 postgresql11                      x86_64               11.5-1PGDG.f30                 pgdg11               1.7 M
 postgresql11-libs                 x86_64               11.5-1PGDG.f30                 pgdg11               374 k
 
Transaction Summary
==================================================================================================================
Install  3 Packages
 
Total download size: 6.9 M
Installed size: 32 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): postgresql11-libs-11.5-1PGDG.f30.x86_64.rpm                                213 kB/s | 374 kB     00:01    
(2/3): postgresql11-11.5-1PGDG.f30.x86_64.rpm                                     698 kB/s | 1.7 MB     00:02    
(3/3): postgresql11-server-11.5-1PGDG.f30.x86_64.rpm                              1.5 MB/s | 4.8 MB     00:03    
------------------------------------------------------------------------------------------------------------------
Total                                                                             2.2 MB/s | 6.9 MB     00:03     
warning: /var/cache/dnf/pgdg11-cde8ad453ae6cd5b/packages/postgresql11-11.5-1PGDG.f30.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
PostgreSQL 11 30 - x86_64                                                         1.6 MB/s | 1.7 kB     00:00    
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                          1/1 
  Installing       : postgresql11-libs-11.5-1PGDG.f30.x86_64                                                  1/3 
  Running scriptlet: postgresql11-libs-11.5-1PGDG.f30.x86_64                                                  1/3 
  Installing       : postgresql11-11.5-1PGDG.f30.x86_64                                                       2/3 
  Running scriptlet: postgresql11-11.5-1PGDG.f30.x86_64                                                       2/3 
failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink
failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink
failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb: /usr/bin/createdb exists and it is not a symlink
failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser: /usr/bin/createuser exists and it is not a symlink
failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb: /usr/bin/dropdb exists and it is not a symlink
failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser: /usr/bin/dropuser exists and it is not a symlink
failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump: /usr/bin/pg_dump exists and it is not a symlink
failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall: /usr/bin/pg_dumpall exists and it is not a symlink
failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore: /usr/bin/pg_restore exists and it is not a symlink
failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb: /usr/bin/reindexdb exists and it is not a symlink
failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb: /usr/bin/vacuumdb exists and it is not a symlink
 
  Running scriptlet: postgresql11-server-11.5-1PGDG.f30.x86_64                                                3/3 
  Installing       : postgresql11-server-11.5-1PGDG.f30.x86_64                                                3/3 
  Running scriptlet: postgresql11-server-11.5-1PGDG.f30.x86_64                                                3/3 
  Verifying        : postgresql11-11.5-1PGDG.f30.x86_64                                                       1/3 
  Verifying        : postgresql11-libs-11.5-1PGDG.f30.x86_64                                                  2/3 
  Verifying        : postgresql11-server-11.5-1PGDG.f30.x86_64                                                3/3 
 
Installed:
  postgresql11-server-11.5-1PGDG.f30.x86_64                   postgresql11-11.5-1PGDG.f30.x86_64                  
  postgresql11-libs-11.5-1PGDG.f30.x86_64                    
 
Complete!

After installing the PostgreSQL Server 11, you need to initialize the database. You use the following command to initialize the database as the root user:

/usr/pgsql-11/bin/postgresql-11-setup initdb

It should return the following:

Initializing database ... OK

The PostgreSQL Server 11 database installs in the /var/lib/pgsql/11/data directory. You can list the contents, which should mirror these:

drwx------. 5 postgres postgres  4096 Aug 19 02:45 base
drwx------. 2 postgres postgres  4096 Aug 19 02:45 global
drwx------. 2 postgres postgres  4096 Aug 19 02:45 log
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_commit_ts
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_dynshmem
-rw-------. 1 postgres postgres  4269 Aug 19 02:45 pg_hba.conf
-rw-------. 1 postgres postgres  1636 Aug 19 02:45 pg_ident.conf
drwx------. 4 postgres postgres  4096 Aug 19 02:45 pg_logical
drwx------. 4 postgres postgres  4096 Aug 19 02:45 pg_multixact
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_notify
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_replslot
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_serial
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_snapshots
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_stat
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_stat_tmp
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_subtrans
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_tblspc
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_twophase
-rw-------. 1 postgres postgres     3 Aug 19 02:45 PG_VERSION
drwx------. 3 postgres postgres  4096 Aug 19 02:45 pg_wal
drwx------. 2 postgres postgres  4096 Aug 19 02:45 pg_xact
-rw-------. 1 postgres postgres    88 Aug 19 02:45 postgresql.auto.conf
-rw-------. 1 postgres postgres 23895 Aug 19 02:45 postgresql.conf

You need to enable and start the postgresql-11.service with the following commands as the root user:

systemctl enable postgresql-11.service
systemctl start postgresql-11.service

You can login to test the configuration as the root user, like this:

su - postgres -c "psql"

You will see something like this:

psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)
psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)
psql (11.4, server 11.5)
Type "help" for help.
 
postgres=#

The error message appear to indicate there’s a bug (at least Bug #15798 is similar). Specifically, a missing function in the libya.so.5 library. Determining that impact took some time because of what else I had in the queue.

The Bug (at least Bug #15798 gave part of the fix. The problem was figuring out where the LD_LIBRARY_PATH should really be set, and I sorted that out.

If you inspect the postgres home directory (/var/lib/pgsql), you’ll find the following .bash_profile file:

[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/11/data
export PGDATA
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

Then, you create the .pgsql_profile file in that directory. You should put the following command in the file:

export set LD_LIBRARY_PATH=/usr/lib64 needle < /dev/null

Then, when you login as the postgres user:

psql -U postgres

You will see:

psql (11.4, server 11.5)
Type "help" for help.
 
postgres=#

or, you can login to test the configuration as the root user with the syntax used earlier:

su - postgres -c "psql"

You need to put the LD_LIBRARY_PATH environment variable in the .bashrc of users who will access the PostgreSQL 11 database.

As always, I hope this helps those working it from the ground up.

Written by maclochlainn

August 19th, 2019 at 3:06 am

Posted in Fedora,LAMP,Linux,PostgreSQL,psql

Tagged with , ,

MongoDB on Fedora 30

without comments

I found that MongoDB instructions changed significantly from a year ago. More or less, there was no pre-configured yum repository. Fortunately, the following web page was very helpful though not specific about Fedora.

Installing MongoDB 4.0 on Fedora 30 requires updating the yum repository. You need to create the mongodb.repo file as the root user in the /etc/yum.repos.d directory. The mongodb.repo file should contain the following:

[Mongodb]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/amazon/2013.03/mongodb-org/4.0/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-4.0.asc

After you create the appropriate mongodb.repo file, you can run the following command to install the MongoDB database.

def install mongodb-org

It will produce the following log:

MongoDB Repository                            31 kB/s |  21 kB     00:00    
Dependencies resolved.
=============================================================================
 Package                 Arch        Version              Repository    Size
=============================================================================
Installing:
 mongodb-org             x86_64      4.0.12-1.amzn1       Mongodb      5.8 k
Installing dependencies:
 mongodb-org-mongos      x86_64      4.0.12-1.amzn1       Mongodb       11 M
 mongodb-org-server      x86_64      4.0.12-1.amzn1       Mongodb       20 M
 mongodb-org-shell       x86_64      4.0.12-1.amzn1       Mongodb       13 M
 mongodb-org-tools       x86_64      4.0.12-1.amzn1       Mongodb       29 M
 
Transaction Summary
=============================================================================
Install  5 Packages
 
Total download size: 73 M
Installed size: 213 M
Is this ok [y/N]: y
Downloading Packages:
(1/5): mongodb-org-4.0.12-1.amzn1.x86_64.rpm  18 kB/s | 5.8 kB     00:00    
(2/5): mongodb-org-shell-4.0.12-1.amzn1.x86_ 4.6 MB/s |  13 MB     00:02    
(3/5): mongodb-org-mongos-4.0.12-1.amzn1.x86 1.8 MB/s |  11 MB     00:06    
(4/5): mongodb-org-tools-4.0.12-1.amzn1.x86_ 6.6 MB/s |  29 MB     00:04    
(5/5): mongodb-org-server-4.0.12-1.amzn1.x86 2.5 MB/s |  20 MB     00:08    
-----------------------------------------------------------------------------
Total                                        9.0 MB/s |  73 MB     00:08     
warning: /var/cache/dnf/Mongodb-f722cd88d61a4e38/packages/mongodb-org-4.0.12-1.amzn1.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID e52529d4: NOKEY
MongoDB Repository                           3.0 kB/s | 1.7 kB     00:00    
Importing GPG key 0xE52529D4:
 Userid     : "MongoDB 4.0 Release Signing Key <packaging@mongodb.com>"
 Fingerprint: 9DA3 1620 334B D75D 9DCB 49F3 6881 8C72 E525 29D4
 From       : https://www.mongodb.org/static/pgp/server-4.0.asc
Is this ok [y/N]: y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                     1/1 
  Installing       : mongodb-org-tools-4.0.12-1.amzn1.x86_64             1/5 
  Installing       : mongodb-org-shell-4.0.12-1.amzn1.x86_64             2/5 
  Running scriptlet: mongodb-org-server-4.0.12-1.amzn1.x86_64            3/5 
  Installing       : mongodb-org-server-4.0.12-1.amzn1.x86_64            3/5 
  Running scriptlet: mongodb-org-server-4.0.12-1.amzn1.x86_64            3/5 
  Installing       : mongodb-org-mongos-4.0.12-1.amzn1.x86_64            4/5 
  Installing       : mongodb-org-4.0.12-1.amzn1.x86_64                   5/5 
  Running scriptlet: mongodb-org-4.0.12-1.amzn1.x86_64                   5/5 
  Verifying        : mongodb-org-4.0.12-1.amzn1.x86_64                   1/5 
  Verifying        : mongodb-org-mongos-4.0.12-1.amzn1.x86_64            2/5 
  Verifying        : mongodb-org-server-4.0.12-1.amzn1.x86_64            3/5 
  Verifying        : mongodb-org-shell-4.0.12-1.amzn1.x86_64             4/5 
  Verifying        : mongodb-org-tools-4.0.12-1.amzn1.x86_64             5/5 
 
Installed:
  mongodb-org-4.0.12-1.amzn1.x86_64                                          
  mongodb-org-mongos-4.0.12-1.amzn1.x86_64                                   
  mongodb-org-server-4.0.12-1.amzn1.x86_64                                   
  mongodb-org-shell-4.0.12-1.amzn1.x86_64                                    
  mongodb-org-tools-4.0.12-1.amzn1.x86_64                                    
 
Complete!

You create a MongoDB service with the following syntax as a privileged user in the sudoer list:

sudo systemctl enable mongodb.service

You can then start the mongod service with the following command as a privileged user in the sudoer list:

sudo systemctl start mongod.service

You confirm that it started with the following command as the same privileged user:

sudo service mongod status

It should produce a log file like this:

● mongod.service - SYSV: Mongo is a scalable, document-oriented database.
   Loaded: loaded (/etc/rc.d/init.d/mongod; generated)
   Active: active (running) since Fri 2019-08-16 14:57:22 MDT; 2min 57s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 128115 ExecStart=/etc/rc.d/init.d/mongod start (code=exited, stat>
    Tasks: 27 (limit: 4661)
   Memory: 47.0M
   CGroup: /system.slice/mongod.service
           └─128131 /usr/bin/mongod -f /etc/mongod.conf
 
Aug 16 14:57:21 localhost.localdomain systemd[1]: Starting SYSV: Mongo is a >
Aug 16 14:57:21 localhost.localdomain runuser[128127]: pam_unix(runuser:sess>
Aug 16 14:57:22 localhost.localdomain runuser[128127]: pam_unix(runuser:sess>
Aug 16 14:57:22 localhost.localdomain mongod[128115]: [30B blob data]
Aug 16 14:57:22 localhost.localdomain systemd[1]: Started SYSV: Mongo is a s>
lines 1-15/15 (END)

You close the service log with a “q“. You can determine your version with the following command:

mongod --version

It should show you something like this:

db version v4.0.12
git version: 5776e3cbf9e7afe86e6b29e22520ffb6766e95d4
OpenSSL version: OpenSSL 1.0.0-fips 29 Mar 2010
allocator: tcmalloc
modules: none
build environment:
    distmod: amazon
    distarch: x86_64
    target_arch: x86_64

You can connect to the MongoDB shell with the following command:

mongo

Inside the MongoDB shell, you can run standard MongoDB commands, like:

> use mydb;
switched to db mydb
> db.version()
4.0.12
> db.stats()
{
        "db" : "mydb",
        "collections" : 0,
        "views" : 0,
        "objects" : 0,
        "avgObjSize" : 0,
        "dataSize" : 0,
        "storageSize" : 0,
        "numExtents" : 0,
        "indexes" : 0,
        "indexSize" : 0,
        "fileSize" : 0,
        "fsUsedSize" : 0,
        "fsTotalSize" : 0,
        "ok" : 1
}
> quit()

As always, I hope this helps those looking for the missing steps.

Written by maclochlainn

August 16th, 2019 at 3:11 pm

Posted in Linux,MongoDB,Unix

Tagged with ,

Apache on Fedora 30

with one comment

There was an option during the Fedora 30 Workstation installation to add the Apache Web Server, but you need to set it to start automatically. Unfortunately, there was no option to install PHP, which I thought odd because of how many web developers learn the trade first on PHP with a LAMP (Linux, Apache, MySQL, Perl/PHP/Python) stack. You see how to fix that shortcoming in this post and how to install and test PHP, mysqli, and pdo to support MySQL 8.

Before you do that make sure you install MySQL 8. You can find my prior blog post on that here.

You set Apache to start automatically, on the next boot of the operating system, with the following command:

chkconfig httpd on

It creates a symbolic link:

Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.

However, that command only starts the Apache server the next time you boot the server. You use the following command as the root user to start the Apache server:

apachectl start

You can verify the installation with the following command as the root user:

ps -ef | grep httpd | grep -v grep

It should return:

root      5433     1  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5434  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5435  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5436  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5437  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5438  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND
apache    5442  5433  0 17:03 ?        00:00:00 /usr/sbin/httpd -DFOREGROUND

and, then verify the listening port with the following command as the root user:

netstat -tulpn | grep :80

It should return the following when both the Apache server is listening on port 80 and the Oracle multi-protocol server is listening on port 8080:

tcp6       0      0 :::80                   :::*                    LISTEN      119810/httpd        
tcp6       0      0 :::8080                 :::*                    LISTEN      1403/tnslsnr

You can also enter the following URL in the browser to see the Apache Test Page:

http://localhost

It should display the test page, like this:

You can also create a hello.htm file in the /var/www/html directory to test the ability to read an HTML file. I would suggest the traditional hello.htm file:

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

You can call it by using this URL in the browser:

http://localhost/hello.htm

It should display the test page, like this:

Now, let’s install PHP. You use the following command as a privileged user, which is one found in the sudoer’s list:

yum install -y php

Display detailed console log →

Last metadata expiration check: 0:37:02 ago on Fri 16 Aug 2019 11:03:54 AM MDT.
Dependencies resolved.
=============================================================================
 Package               Arch        Version                Repository    Size
=============================================================================
Installing:
 php                   x86_64      7.3.8-1.fc30           updates      2.8 M
Installing dependencies:
 nginx-filesystem      noarch      1:1.16.0-3.fc30        updates       11 k
 php-cli               x86_64      7.3.8-1.fc30           updates      4.3 M
 php-common            x86_64      7.3.8-1.fc30           updates      1.1 M
Installing weak dependencies:
 php-fpm               x86_64      7.3.8-1.fc30           updates      1.5 M
 
Transaction Summary
=============================================================================
Install  5 Packages
 
Total download size: 9.6 M
Installed size: 43 M
Downloading Packages:
(1/5): nginx-filesystem-1.16.0-3.fc30.noarch  34 kB/s |  11 kB     00:00    
(2/5): php-common-7.3.8-1.fc30.x86_64.rpm    1.1 MB/s | 1.1 MB     00:00    
(3/5): php-7.3.8-1.fc30.x86_64.rpm           2.0 MB/s | 2.8 MB     00:01    
(4/5): php-fpm-7.3.8-1.fc30.x86_64.rpm       2.2 MB/s | 1.5 MB     00:00    
(5/5): php-cli-7.3.8-1.fc30.x86_64.rpm       1.7 MB/s | 4.3 MB     00:02    
-----------------------------------------------------------------------------
Total                                        3.0 MB/s | 9.6 MB     00:03     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                     1/1 
  Installing       : php-common-7.3.8-1.fc30.x86_64                      1/5 
  Installing       : php-cli-7.3.8-1.fc30.x86_64                         2/5 
  Running scriptlet: nginx-filesystem-1:1.16.0-3.fc30.noarch             3/5 
  Installing       : nginx-filesystem-1:1.16.0-3.fc30.noarch             3/5 
  Installing       : php-fpm-7.3.8-1.fc30.x86_64                         4/5 
  Running scriptlet: php-fpm-7.3.8-1.fc30.x86_64                         4/5 
  Installing       : php-7.3.8-1.fc30.x86_64                             5/5 
  Running scriptlet: php-7.3.8-1.fc30.x86_64                             5/5 
  Running scriptlet: php-fpm-7.3.8-1.fc30.x86_64                         5/5 
  Verifying        : nginx-filesystem-1:1.16.0-3.fc30.noarch             1/5 
  Verifying        : php-7.3.8-1.fc30.x86_64                             2/5 
  Verifying        : php-cli-7.3.8-1.fc30.x86_64                         3/5 
  Verifying        : php-common-7.3.8-1.fc30.x86_64                      4/5 
  Verifying        : php-fpm-7.3.8-1.fc30.x86_64                         5/5 
 
Installed:
  php-7.3.8-1.fc30.x86_64                     php-fpm-7.3.8-1.fc30.x86_64    
  nginx-filesystem-1:1.16.0-3.fc30.noarch     php-cli-7.3.8-1.fc30.x86_64    
  php-common-7.3.8-1.fc30.x86_64             
 
Complete!

Before you test the installation of PHP in a browser, you must restart the Apache HTTP Server. You can do that with the following command as a privileged user:

sudo apachectl restart

After verifying the connection, you can test it by creating the traditional info.php program file in the /var/www/http directory. The file should contain the following:

1
2
3

<?php
  phpinfo();
?>

It should display the PHP Version 7.3.8 web page, which ships with Fedora 30:

The next step shows you how to install mysqli and pdo with the yum utility. While it’s unnecessary to check for the older mysql library (truly deprecated), its good practice to know how to check for a conflicting library before installing a new one. Also, I’d prefer newbies get exposed to using the yum utility’s shell environment.

You start the yum shell, as follows:

yum shell

With the yum shell, you would remove a mysql package with the following command:

> remove php-mysql

The command will remove the package or tell you that there is no package to remove. Next, you install the php-mysqli package with this command:

install php-mysqli

You will then be prompted to confirm the installation of the php-mysqli library. Finally, you exit the yum shell with this command:

> quit

If you want to see the whole interactive shell, click on the link below.

Display detailed console log →

Last metadata expiration check: 0:53:05 ago on Fri 16 Aug 2019 11:03:54 AM MDT.
> remove php-mysql
No match for argument: php-mysql
No packages marked for removal.
> install php-mysqlnd
> run
=============================================================================
 Package            Architecture  Version               Repository      Size
=============================================================================
Installing:
 php-mysqlnd        x86_64        7.3.8-1.fc30          updates        195 k
Installing dependencies:
 php-pdo            x86_64        7.3.8-1.fc30          updates         91 k
 
Transaction Summary
=============================================================================
Install  2 Packages
 
Total download size: 286 k
Installed size: 1.4 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): php-pdo-7.3.8-1.fc30.x86_64.rpm       136 kB/s |  91 kB     00:00    
(2/2): php-mysqlnd-7.3.8-1.fc30.x86_64.rpm   183 kB/s | 195 kB     00:01    
-----------------------------------------------------------------------------
Total                                         24 kB/s | 286 kB     00:11     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                     1/1 
  Installing       : php-pdo-7.3.8-1.fc30.x86_64                         1/2 
  Installing       : php-mysqlnd-7.3.8-1.fc30.x86_64                     2/2 
  Running scriptlet: php-mysqlnd-7.3.8-1.fc30.x86_64                     2/2 
  Verifying        : php-mysqlnd-7.3.8-1.fc30.x86_64                     1/2 
  Verifying        : php-pdo-7.3.8-1.fc30.x86_64                         2/2 
 
Installed:
  php-mysqlnd-7.3.8-1.fc30.x86_64         php-pdo-7.3.8-1.fc30.x86_64        
 
Last metadata expiration check: 0:53:54 ago on Fri 16 Aug 2019 11:03:54 AM MDT.
> quit
Leaving Shell
The downloaded packages were saved in cache until the next successful transaction.
You can remove cached packages by executing 'dnf clean packages'.

You need to restart the Apache HTTP listener for these changes to take place, which you do with the same command as shown earlier:

sudo apachectl restart

I wrote the mysqli_check.php script to verify installation of both the mysqli and pdo libraries. The full code should be put in a mysqli_check.php file in the /var/www/html directory for testing.

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

<html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
  /* HTML element styles. */
  table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
  th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
  td {border:solid 1px gray;}
 
  /* Class tag element styles. */
  .ID {min-width:50px;text-align:right;}
  .Label {min-width:200px;text-align:left;}
</style>
</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>

You can test it with the following URL from the local browser:

http://localhost/mysqli_check.php

It should print the following to the web page when you’ve successfully install the mysqli and pdo libraries:

mysqli installed.
pdo installed.

If you plan to use PHP to display and render graphics, you need to install php-gd library. You can do that with the yum utility and this prior blog post explains it. Don’t forget to restart the Apache HTTP Server after you add the php-gd library.

For example, one of my sample PHP programs loads a PNG image into a BLOB column as raw binary text. Then, the program reads it and renders it with PHP to produce the following web page.

As always, I hope this helps those looking for a complete solution without cost.

Written by maclochlainn

August 16th, 2019 at 12:26 pm

Posted in Apache,LAMP,Linux,pdo,PHP,Unix

Tagged with ,

Oracle Error Bash f(x)

without comments

My students always struggle initially with basic Linux skills. I wrote little function for their .bashrc file to help them avoid the frustration. It finds and displays all errors by file name, line number and error message for a collection of log files in a single directory (or folder).

errors()
{
  # Determine if any log files exist and check for errors.
  label="File Name:Line Number:Error Code"
  list=`ls ./*.$1 | wc -l`
  if [[ $list} -eq 1 ]]; then
    echo ${label}
    echo "--------------------------------------------------"
    filename=`ls *.txt`
    echo ${filename}:`find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\-`
  elif [[ ${list} -gt 1 ]]; then
    echo ${label}
    echo "--------------------------------------------------"
    find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\-
  fi
}

Let’s say you name your log files with a file extension of .txt, then you would call the function like this:

errors txt

It would return output like the following:

common_lookup_lab.txt:229:ORA-02275: such a referential constraint already exists in the table
common_lookup_lab.txt:239:ORA-02275: such a referential constraint already exists in the table

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

Written by maclochlainn

August 13th, 2019 at 8:17 pm

Posted in bash,Linux,Oracle,Oracle 11g,Oracle 12c,Oracle XE,Unix

Tagged with ,

« Older Entries
Newer Entries »