MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

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 ,

MySQL Python Connector

with one comment

While building my student image on Fedora 30, I installed the MySQL PHP Connector (php-mysqlndrp) but neglected to install the Python Connector. This adds the installation and basic test of the Python Connector to the original blog post.

You use the following command with a wildcard as a privileged user. The wildcard is necessary because you need to load two libraries to support Python 2.7 and 3.7, which are installed on Fedora 30. You also need to be the root user or a user that is found in the sudoer’s list:

yum install -y mysql-connector-python*

Display detailed console log →

Last metadata expiration check: 0:35:46 ago on Tue 20 Aug 2019 05:36:29 PM MDT.
Dependencies resolved.
=====================================================================================================================================
 Package                                  Architecture       Version                    Repository                              Size
=====================================================================================================================================
Installing:
 mysql-connector-python                   x86_64             8.0.17-1.fc30              mysql-connectors-community             435 k
 mysql-connector-python-cext              x86_64             8.0.17-1.fc30              mysql-connectors-community             7.7 M
 mysql-connector-python3                  x86_64             8.0.17-1.fc30              mysql-connectors-community             429 k
 mysql-connector-python3-cext             x86_64             8.0.17-1.fc30              mysql-connectors-community             7.7 M
Installing dependencies:
 python2-protobuf                         noarch             3.6.1-3.fc30               fedora                                 563 k
 python3-protobuf                         noarch             3.6.1-3.fc30               fedora                                 568 k
 
Transaction Summary
=====================================================================================================================================
Install  6 Packages
 
Total download size: 17 M
Installed size: 89 M
Downloading Packages:
(1/6): python3-protobuf-3.6.1-3.fc30.noarch.rpm                                                      1.0 MB/s | 568 kB     00:00    
(2/6): python2-protobuf-3.6.1-3.fc30.noarch.rpm                                                      994 kB/s | 563 kB     00:00    
(3/6): mysql-connector-python-8.0.17-1.fc30.x86_64.rpm                                               481 kB/s | 435 kB     00:00    
(4/6): mysql-connector-python3-8.0.17-1.fc30.x86_64.rpm                                              612 kB/s | 429 kB     00:00    
(5/6): mysql-connector-python-cext-8.0.17-1.fc30.x86_64.rpm                                          3.8 MB/s | 7.7 MB     00:02    
(6/6): mysql-connector-python3-cext-8.0.17-1.fc30.x86_64.rpm                                         4.2 MB/s | 7.7 MB     00:01    
-------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                5.4 MB/s |  17 MB     00:03     
warning: /var/cache/dnf/mysql-connectors-community-8bcc2bd350b53f70/packages/mysql-connector-python-8.0.17-1.fc30.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
MySQL Connectors Community                                                                           7.0 MB/s |  27 kB     00:00    
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: A4A9 4068 76FC BD3C 4567 70C8 8C71 8D3B 5072 E1F5
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                             1/1 
  Installing       : python3-protobuf-3.6.1-3.fc30.noarch                                                                        1/6 
  Installing       : python2-protobuf-3.6.1-3.fc30.noarch                                                                        2/6 
  Installing       : mysql-connector-python-8.0.17-1.fc30.x86_64                                                                 3/6 
  Installing       : mysql-connector-python3-8.0.17-1.fc30.x86_64                                                                4/6 
  Installing       : mysql-connector-python3-cext-8.0.17-1.fc30.x86_64                                                           5/6 
  Installing       : mysql-connector-python-cext-8.0.17-1.fc30.x86_64                                                            6/6 
  Running scriptlet: mysql-connector-python-cext-8.0.17-1.fc30.x86_64                                                            6/6 
  Verifying        : python2-protobuf-3.6.1-3.fc30.noarch                                                                        1/6 
  Verifying        : python3-protobuf-3.6.1-3.fc30.noarch                                                                        2/6 
  Verifying        : mysql-connector-python-8.0.17-1.fc30.x86_64                                                                 3/6 
  Verifying        : mysql-connector-python-cext-8.0.17-1.fc30.x86_64                                                            4/6 
  Verifying        : mysql-connector-python3-8.0.17-1.fc30.x86_64                                                                5/6 
  Verifying        : mysql-connector-python3-cext-8.0.17-1.fc30.x86_64                                                           6/6 
 
Installed:
  mysql-connector-python-8.0.17-1.fc30.x86_64                    mysql-connector-python-cext-8.0.17-1.fc30.x86_64                   
  mysql-connector-python3-8.0.17-1.fc30.x86_64                   mysql-connector-python3-cext-8.0.17-1.fc30.x86_64                  
  python2-protobuf-3.6.1-3.fc30.noarch                           python3-protobuf-3.6.1-3.fc30.noarch                               
 
Complete!

Leveraging the MySQL Connector/Python Coding Examples documentation, Section 5.1 Connecting to MySQL Using Connector/Python here’s a test of the connection to MySQL 8.

# Import the library.
import mysql.connector
from mysql.connector import errorcode
 
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='127.0.0.1',
                                database='studentdb')
 
  # Print the value.
  print("Database connection resolved.")
 
# 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(e)
 
# Close the connection when the try block completes.
else:
  cnx.close()

Leveraging the MySQL Connector/Python Coding Examples documentation, Section 5.4 Querying Data Using Connector/Python here’s a test of the connection to MySQL 8.

# Import the library.
import datetime
import mysql.connector
from mysql.connector import errorcode
 
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")
 
  # Set the start and end date.
  start_date = datetime.date(1991, 1, 1)
  end_date = datetime.date(2004, 12, 31)
 
  # 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))
 
  # Close cursor.
  cursor.close()
 
# ------------------------------------------------------------
# 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.
else:
  cnx.close()

If you run the above in Python 2.7 it works fine. It fails to parse successfully in Python 3.x because the print() function requires the parentheses all the time. You would need to re-write the except block, like this with the parentheses:

# 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

While it works without the parentheses in Python 2.7, it also works with the parentheses. That means the best practice is to write cross compatible code by always using the parentheses with the print() function.

As always, I hope this helps somebody.j

Written by maclochlainn

August 21st, 2019 at 1:44 am

Posted in MySQL,MySQL 8,MySQL Connect,MySQL Connect/Python,Python,Python 2.7,Python 3.x

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 , ,

Misleading ORA- Message

without comments

Oracle error messages are more or less the best in the industry but time-to-time they saddle you with a bad or misleading message. For example, I was running one of the code modules from my Oracle Database 12c PL/SQL Programming book for a class exercise and got this error message:

BEGIN
*
ERROR AT line 1:
ORA-22288: FILE OR LOB operation  failed
ORA-06512: AT "STUDENT.LOAD_CLOB_FROM_FILE", line 71
ORA-06512: AT line 11

Oddly enough, it was simple to identify generally. It failed on a call to the DBMS_LOB.LOADCLOBFROMFILE procedure. However, the better question is why did it fail because the virtual directory resolved and the permissions worked.

The first test was to try another file, which worked perfectly with the same code. That meant it had to be something with the physical file. I took a look and sure enough I found a character set problem, like the following:

… he reveals that the Nazgûl, or Ringwraiths, have left Mordor to capture the Ring and kill whoever carries it.

and,

The group flees to the elvish realm of Lothlórien …

The “û” and “ó” characters were incompatible with the default NLS_LANG setting of the database and a CLOB limits the use of non-standard character sets. It’s ashamed that Oracle didn’t through a character set error, which would have expedited resolution of the problem.

As always, I hope this helps those looking for solutions.

Written by maclochlainn

August 17th, 2019 at 4:52 pm

Posted in Database Design,DBA,Oracle,Oracle 11g,Oracle 12c,Oracle 18c,Oracle DBA,Oracle Developer,Oracle XE,pl/sql

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 ,

MySQL on Fedora 30

with one comment

While updating my class image to Fedora 30, I noticed that it installed the Akonadi Server. The documentation on the Akonadi server lacked some straightforward documentation. It also offered a bundled set of software that limited how to approach MySQL development.

So, I removed all those packages with the following syntax:

dnf remove `rpm -qa | grep akonadi`

Display detailed console log →

 

After removing those Akonadi packages, I installed the MySQL Community Edition from the Fedora repo with this syntax:

yum install -y community-mysql*

Display detailed console log →

 

Having installed MySQL Community Edition, I wanted to start the mysql service with this command:

sudo service mysqld start

Unfortunately, the service utility wasn’t installed. That surprised me. While I could have run this command:

systemctl start mysqld.service

A better solution was to install any missing code components. I determined that the service utility is part of the initscripts package; and I installed it with the following command:

sudo yum install -y initscripts

Display detailed console log →

Fedora Modular 30 - x86_64                    30 kB/s |  18 kB     00:00    
Fedora Modular 30 - x86_64 - Updates          40 kB/s |  17 kB     00:00    
Fedora 30 - x86_64 - Updates                  43 kB/s |  17 kB     00:00    
Fedora 30 - x86_64                            58 kB/s |  19 kB     00:00    
google-chrome-unstable                        12 kB/s | 1.3 kB     00:00    
google-chrome                                 16 kB/s | 1.3 kB     00:00    
Dependencies resolved.
=============================================================================
 Package            Architecture  Version               Repository      Size
=============================================================================
Installing:
 initscripts        x86_64        10.02-1.fc30          updates        202 k
 
Transaction Summary
=============================================================================
Install  1 Package
 
Total download size: 202 k
Installed size: 1.1 M
Downloading Packages:
initscripts-10.02-1.fc30.x86_64.rpm          296 kB/s | 202 kB     00:00    
-----------------------------------------------------------------------------
Total                                        162 kB/s | 202 kB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                     1/1 
  Installing       : initscripts-10.02-1.fc30.x86_64                     1/1 
  Running scriptlet: initscripts-10.02-1.fc30.x86_64                     1/1 
  Verifying        : initscripts-10.02-1.fc30.x86_64                     1/1 
 
Installed:
  initscripts-10.02-1.fc30.x86_64                                            
 
Complete!

Then, I ran the mysql_secure_installation script to secure the installation:

mysql_secure_installation

The script set the root user’s password, remove the anonymous user, disallow remote root login, and remove the test databases. Then, I verified connecting to the MySQL database with the following syntax:

mysql -uroot -ppassword

I enabled the MySQL Service to start with each reboot of the Fedora instance. I used the following command:

systemctl enable mysqld.service

It creates the following link:

ln -s '/etc/systemd/system/multi-user.target.wants/mysqld.service' '/usr/lib/systemd/system/mysqld.service'

The next step requires setting up a sample studentdb database. The syntax has changed from prior releases. Here are the three steps:

  1. Create the studentdb database with the following command as the MySQL root user: 

    mysql> CREATE DATABASE studentdb;
  2. Grant the root user the privilege to grant to others, which root does not have by default. You use the following syntax as the MySQL root user: 

    mysql> GRANT ALL ON *.* TO 'root'@'localhost';
  3. Create the user with a clear English password and grant the user student full privileges on the studentdb database: 

    mysql> CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'student';
mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost';

If you fail to specify mysql_native_password when creating the user and use the older syntax like the following example:

mysql> CREATE USER 'student'@'localhost' IDENTIFIED BY 'student';
mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost';

The GRANT command will raise the following error:

ERROR 1410 (42000): You are not allowed to create a user with GRANT

Written by maclochlainn

August 16th, 2019 at 1:02 am

Posted in MySQL,MySQL 8

Tagged with ,

« Older Entries
Newer Entries »