AWS EC2 TNS Listener
Having configured an AlmaLinux 8.6 with Oracle Database 11g XE, MySQL 8.0.30, and PostgreSQL 15, we migrated it to AWS EC2 and provisioned it. We used the older and de-supported Oracle Database 11g XE because it didn’t require any kernel modifications and had a much smaller footprint.
I had to address why attempting to connect with the sqlplus utility raised the following error after provisioning a copy with a new static IP address:
ERROR: ORA-12514: TNS:listener does NOT currently know OF service requested IN CONNECT descriptor |
A connection from SQL Developer raises a more addressable error, like:
ORA-17069 |
I immediately tried to check the connection with the tnsping utility and found that tnsping worked fine. However, when I tried to connect with the sqlplus utility it raised an ORA-12514 connection error.
There were no diagnostic steps beyond checking the tnsping utility. So, I had to experiment with what might block communication.
I changed the host name from ip-172-58-65-82.us-west-2.compute.internal to a localhost string in both the listener.ora and tnsnames.ora. The listener.ora file:
# listener.ora Network Configuration FILE: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe) (PROGRAM = extproc) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) ) DEFAULT_SERVICE_LISTENER = (XE) |
The tnsnames.ora file:
# tnsnames.ora Network Configuration FILE: XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) |
I suspected that it might be related to the localhost value. So, I checked the /etc/hostname and /etc/hosts files.
Then, I modified /etc/hostname file by removing the AWS EC2 damain address. I did it on a memory that Oracle’s TNS raises errors for dots or periods in some addresses.
The /etc/hostname file:
ip-172-58-65-82 |
The /etc/hosts file:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ip-172-58-65-82 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 ip-172-58-65-82 |
Now, we can connect to the Oracle Database 11g XE instance with the sqlplus utility. I believe this type of solution will work for other AWS EC2 provisioned Oracle databases.
MySQL @SQL_MODE
Installing MySQL Workbench 8 on Windows, we discovered that the default configuration no longer sets ONLY_FULL_GROUP_BY as part of the default SQL_MODE parameter value. While I’ve written a stored function to set the SQL_MODE parameter value for a session, some students didn’t understand that such a call is only valid in the scope of a connection to the database server. They felt the function didn’t work because they didn’t understand the difference between connecting to the MySQL CLI and clicking the lightening bolt in MySQL Workbench.
So, here are the instructions to reset the default SQL_MODE parameter value for Windows. You need to edit the setting in the my.ini file, which is in the C:\ProgramData\MySQL\MySQL Server 8.0 directory. The default installation will have the following:
# Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" |
You need to change it to the following in an editor with Administrative privileges:
# Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY" |
Then, you need to connect to the services by launching services.msc from the command prompt. In the list of services find MYSQL80 service and restart it. You can verify it by connecting to the MySQL 8.0.* server and running the following SQL query:
SELECT @@SQL_MODE: |
That’s how you convert Windows to use only traditional group by behaviors in SQL. As always, I hope this helps those looking for a solution.
AlmaLinux Libraries
I discovered a dependency for MySQL Workbench on AlmaLinux 8 installation. I neglected to fully cover it when I documented the installation in a VM of AlmaLinux 9. I go back later and update that entry but for now you need the following dependencies:
proj-6.3.2-4.el8.x86_64.rpm proj-datumgrid-1.8-6.3.2.4.el8.noarch.rpm proj-devel-6.3.2-4.el8.x86_64.rpm |
Install like this:
sudo dnf install -y *.rpm |
Log file:
Last metadata expiration check: 3:01:53 ago on Fri 10 Feb 2023 03:37:49 AM UTC. Dependencies resolved. ========================================================================================== Package Architecture Version Repository Size ========================================================================================== Installing: proj x86_64 6.3.2-4.el8 @commandline 2.0 M proj-datumgrid noarch 1.8-6.3.2.4.el8 @commandline 5.4 M proj-devel x86_64 6.3.2-4.el8 @commandline 89 k Transaction Summary ========================================================================================== Install 3 Packages Total size: 7.5 M Installed size: 17 M Downloading Packages: Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : proj-datumgrid-1.8-6.3.2.4.el8.noarch 1/3 Installing : proj-6.3.2-4.el8.x86_64 2/3 Installing : proj-devel-6.3.2-4.el8.x86_64 3/3 Running scriptlet: proj-devel-6.3.2-4.el8.x86_64 3/3 Verifying : proj-6.3.2-4.el8.x86_64 1/3 Verifying : proj-datumgrid-1.8-6.3.2.4.el8.noarch 2/3 Verifying : proj-devel-6.3.2-4.el8.x86_64 3/3 Installed: proj-6.3.2-4.el8.x86_64 proj-datumgrid-1.8-6.3.2.4.el8.noarch proj-devel-6.3.2-4.el8.x86_64 Complete! |
A quick update while installing AlmaLinux for AWS.
Data Engineer?
Students often ask me about data engineering. I try to explain some of the aspects, and how the tasks can be organized but I never laid out all the titles. I really like this illustration (click on image for larger size) from the Gartner Group because it does that. You can download the full “What Are the Essential Roles for Data and Analytics” paper here).
An excerpt from Gartner’s paper:
Data Engineer
Data engineering is the practice of making the appropriate data available to various data consumers (including data scientists, data and business analysts, citizen integrators, and line-of-business users). It is a discipline that involves collaboration across business and IT units. This key discipline requires skilled data engineers to support both IT and business teams.
Data engineers are primarily responsible for building, managing and operationalizing data pipelines in support of key D&A use cases. They are also primarily responsible for leading the tedious (and often complex) task of:
- Curating datasets and data pipelines created by nontechnical users (e.g., through self-service data preparation tools), data scientists or even IT resources.
- Operationalizing data delivery for production-level deployments.
I hope the summary is helpful and Gartner’s paper interesting.
Updating SQL_MODE
This is an update for MySQL 8 Stored PSM to add the ONLY_FULL_GROUP_BY mode to the global SQL_MODE variable when it’s not set during a session. Here’s the code:
/* Drop procedure conditionally on whether it exists already. */ DROP PROCEDURE IF EXISTS set_full_group_by; /* Reset delimter to allow semicolons to terminate statements. */ DELIMITER $$ /* Create a procedure to verify and set connection parameter. */ CREATE PROCEDURE set_full_group_by() LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT 'Set connection parameter when not set.' BEGIN /* Check whether full group by is set in the connection and if unset, set it in the scope of the connection. */ IF EXISTS (SELECT TRUE WHERE NOT REGEXP_LIKE(@@SESSION.SQL_MODE,'ONLY_FULL_GROUP_BY')) THEN SET @@GLOBAL.SQL_MODE := CONCAT(@@SESSION.sql_mode,',ONLY_FULL_GROUP_BY'); END IF; END; $$ /* Reset the default delimiter. */ DELIMITER ; |
You can call the set_full_group_by procedure with the CALL command:
CALL set_full_group_by(); |
You can see the SQL_MODE variable with the following query:
SELECT @@GLOBAL.SQL_MODE; |
It’ll return:
+---------------------------------------------------------------+ | @@GLOBAL.SQL_MODE | +---------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +---------------------------------------------------------------+ 1 row in set (0.00 sec) |
As always, I hope this helps those looking to solve this type of problem.
GROUP BY Quirk
It’s always interesting to see how others teach SQL courses. It can be revealing as to whether they understand SQL or only understand a dialect of SQL. In this case, one of my old students was taking a graduate course in SQL and the teacher was using MySQL. The teacher made an issue of using ANSI SQL:1999 or SQL3 and asked the following question, which I suspect is a quiz bank question from a textbook:
“How would you get all students’ names and for each student the number of courses that the
student has registered for?”
They referenced the MySQL 5.7 documentation for the GROUP BY and SQL:1999 as if MySQL implemented the ANSI SQL:1999 specification defined the standard. I didn’t know whether to laugh or cry because they were referring to MySQL 5.7 when we’re all using MySQL 8 and anybody who’s worked in more than MySQL knows that the behavior for a GROUP BY in MySQL can work without listing the necessary non-aggregated columns in the SELECT-list.
For example, their working solution, which is from the instructor and the author of their MySQL textbook the correct perspective of ANSI:1999 behavior. It doesn’t matter that their solution is actually based on ANSI:1992 not ANSI:1999 because it will only succeed because of a quirk of MySQL:
SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; |
While it works in MySQL, it doesn’t work because it conforms to an ANSI standard. It works in MySQL, notwithstanding that standard because it violates the standard.
In Oracle, PostgreSQL, and SQL Server, it raises an exception. For example, Oracle raises the following exception:
SELECT a.studentname * ERROR at line 1: ORA-00979: not a GROUP BY expression |
The correct way to write the GROUP BY is:
SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
Then, it would return:
Student Name Course IDs ------------------------------ ---------- Montgomery Scott 1 Leonard McCoy 2 James Tiberus Kirk 3 |
For reference, here’s a complete test case for MySQL:
/* Drop table conditionally. */ DROP TABLE IF EXISTS students; /* Create table. */ CREATE TABLE students ( studentID int unsigned primary key auto_increment , studentName varchar(30)); /* Drop table conditionally. */ DROP TABLE IF EXISTS courses; /* Create table. */ CREATE TABLE courses ( courseid int unsigned primary key auto_increment , coursename varchar(40)); /* Drop table conditionally. */ DROP TABLE IF EXISTS registeredcourses; /* Create table. */ CREATE TABLE registeredcourses ( courseid int unsigned , studentid int unsigned ); /* Insert into students. */ INSERT INTO students ( studentName ) VALUES ('James Tiberus Kirk') ,('Leonard McCoy') ,('Montgomery Scott'); /* Insert into courses. */ INSERT INTO courses ( coursename ) VALUES ('English Literature') ,('Physics') ,('English Composition') ,('Botany') ,('Mechanical Engineering'); /* Insert into registeredcourses. */ INSERT INTO registeredcourses ( studentid , courseid ) VALUES (1,1) ,(1,3) ,(1,4) ,(2,2) ,(2,5) ,(3,4); /* Check global sql_mode to ensure only_full_group_by is set. */ SELECT @@GLOBAL.SQL_MODE; /* Query with a column not found in the SELECT-list. */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; /* Query consistent with ANSI SQL:1992 */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
and, another complete test case for Oracle:
/* Drop tabhe unconditionallly. */ DROP TABLE students; /* Create table. */ CREATE TABLE students ( studentID NUMBER PRIMARY KEY , studentName VARCHAR(30)); /* Drop table unconditionally. */ DROP TABLE courses; /* Create table. */ CREATE TABLE courses ( courseid NUMBER PRIMARY KEY , coursename VARCHAR(40)); /* Drop table unconditionally. */ DROP TABLE registeredcourses; /* Create table. */ CREATE TABLE registeredcourses ( courseid NUMBER , studentid NUMBER ); /* Insert values in student. */ INSERT INTO students ( studentid, studentName ) VALUES (1,'James Tiberus Kirk'); INSERT INTO students ( studentid, studentName ) VALUES (2,'Leonard McCoy'); INSERT INTO students ( studentid, studentName ) VALUES (3,'Montgomery Scott'); /* Insert values in courses. */ INSERT INTO courses ( courseid, coursename ) VALUES (1,'English Literature'); INSERT INTO courses ( courseid, coursename ) VALUES (2,'Physics'); INSERT INTO courses ( courseid, coursename ) VALUES (3,'English Composition'); INSERT INTO courses ( courseid, coursename ) VALUES (4,'Botany'); INSERT INTO courses ( courseid, coursename ) VALUES (5,'Mechanical Engineering'); /* Insert values into registeredcourses. */ INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,1); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,3); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (1,4); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,2); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (2,5); INSERT INTO registeredcourses ( studentid, courseid ) VALUES (3,4); /* Non-ANSI SQL GROUP BY statement. */ SELECT a.studentname , COUNT(b.courseid) FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid GROUP BY a.studentid; /* ANSI SQL GROUP BY statement. */ SELECT a.studentname AS "Student Name" , COUNT(b.courseid) AS "Course IDs" FROM students a INNER JOIN registeredcourses b ON a.studentid = b.studentid INNER JOIN courses c ON b.courseid = c.courseid GROUP BY a.studentname; |
I hope this helps those learning the correct way to write SQL.
TDE on PostgreSQL
The scope of Transparent Data Encryption (TDE) in PostgreSQL only applies to columns. It does not encrypt other aspects of the database, like table-level and database-level encryption; and those who deploy PostgreSQL may need to implement additional security measures to protect these database components.
You need to know two key elements before exploring TDE in PostgreSQL: Scheme inside a database and extensions. Unlike many databases, PostgreSQL schemas are not synonymous with a database. You may have multiple scheme (or, alternatively schemas) inside any PostgreSQL database.
Creating an extension is a one time event. Therefore, it’s easier to show you that first. You create a pgcrypto extension with the following command:
CREATE EXTENSION pgcrypto; |
The public schema is the one most users deploy but for the purpose of hiding our AES encryption key this example creates a hidden schema. Unless you change the default find setting the hidden schema is not visible when connecting to the database.
You create the hidden schema with the following idimpotent (re-runnable) set of commands:
/* Drop dependent objects before dropping the schema. */ DROP TABLE IF EXISTS hidden.aes_key; DROP FUNCTION IF EXISTS hidden.get_aes_key; /* * Drop function with cascade to remove the * film_character_t trigger at same time. */ DROP FUNCTION IF EXISTS hidden.film_character_dml_f CASCADE; /* Drop the schema conditionally. */ DROP SCHEMA IF EXISTS hidden; /* Create the schema. */ CREATE SCHEMA hidden; |
Next, we need to create a aes_key table and get_aes_key function in the hidden schema. The table will store the AES encryption key and the function lets us create an AES encryption key.
/* Create an aes encryption key table. */ CREATE TABLE hidden.aes_key ( aes_key text ); /* Create a hidden function to build an AES encryption key. */ CREATE OR REPLACE FUNCTION hidden.get_aes_key() RETURNS text AS $$ BEGIN RETURN gen_random_bytes(16)::text; END; $$ LANGUAGE plpgsql; |
After creating the public get_key() function, you insert a single row to the aes_key table by prefacing it with the hidden schema name, like this:
/* Insert the AES encryption key into a table. */ INSERT INTO hidden.aes_key ( aes_key ) VALUES ( hidden.get_aes_key()); |
Having built the plumbing for our AES encryption key, let’s show you how to encrypt and decrypt string values. This example lets you create an idimpotent film_character table in the public schema, like:
/* Drop the table conditionally. */ DROP TABLE IF EXISTS film_character; /* Create the demonstration table for encrypting and decrypting strings. */ CREATE TABLE film_character ( character_id serial PRIMARY KEY , plain_text text , encrypted_text bytea ); |
After creating the AES encryption key table, function, and inserting a row of data, you need to create a public get_key() function, like:
/* Create a public function to retrieve the AES encryption key. */ CREATE OR REPLACE FUNCTION get_key() RETURNS text AS $$ DECLARE retval text; BEGIN SELECT aes_key INTO retval FROM hidden.aes_key; RETURN retval; END; $$ LANGUAGE plpgsql; |
The following INSERT statement write a plain text column and encrypted text column into the film_character table. The get_key() function hides how the pgp_sym_encrypt function encrypts the string.
/* Insert plain and encrypted text into a table. */ INSERT INTO film_character ( plain_text , encrypted_text ) VALUES ('Severus Snape' , pgp_sym_encrypt('Slytherin',get_key())); |
The following query displays the plain and encrypted text stored in a row of the film_character table.
/* Query plain and encrypted text from a table. */ SELECT character_id , plain_text , encrypted_text FROM film_character; |
It displays:
character_id | plain_text | encrypted_text --------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------- 1 | Severus Snape | \xc30d04070302fa1c4eebd90204cc7bd23901f1d4fa91b2455c3ef2987a305aebe01a4d94f9ebb467d6cb7a3846342ccd09cb55ac5e82a71cbaef93728fbeb4aaa9bf71b6fb93457758d1 (1 row) |
Last, the following query displays the plain and decrypted text with the pgp_sym_decrypt function in a query:
/* Query the plain and decrypted text from a table. */ SELECT character_id , plain_text , pgp_sym_decrypt(encrypted_text,get_key()) AS encrypted_text FROM film_character; |
The query returns the plain and decrypted values:
character_id | plain_text | encrypted_text --------------+---------------+----------------- 1 | Severus Snape | Slytherin (1 row) |
However, this approach exposes the method for encrypting the encrypted_text column’s string value. You can hide this by creating a film_character_dml_f function in the hidden schema and a film_character_t trigger in the public schema, like:
/* Create trigger function for insert or update. */ CREATE FUNCTION hidden.film_character_dml_f() RETURNS trigger AS $$ DECLARE /* Declare local variable. */ unencrypted_input VARCHAR(30); BEGIN unencrypted_input := new.encrypted_text::text; /* Encrypt the column. */ new.encrypted_text := pgp_sym_encrypt(unencrypted_input,get_key()); /* Return new record type. */ RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER film_character_t BEFORE INSERT OR UPDATE ON film_character FOR EACH ROW EXECUTE FUNCTION hidden.film_character_dml_f(); |
Now, you can insert the plain text data in an INSERT statement and the encryption occurs without disclosing how it happens. Here’s a sample statement:
INSERT INTO film_character ( plain_text , encrypted_text ) VALUES ('Harry Potter' ,'Gryffindor'); |
A query of the table shows you that both rows have an encrypted value in the encrypted_text column.
/* Query plain and encrypted text from a table. */ SELECT character_id , plain_text , encrypted_text FROM film_character; |
Displayed like:
character_id | plain_text | encrypted_text --------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 | Severus Snape | \xc30d040703026716034f140d83e76cd23a01f99168afebe50d760b85c69373e3947c74473115a939843887db8e102cd0b2524378f4d684e0ba91c20afc436a056cd983fc47794eef7d4904 2 | Harry Potter | \xc30d040703020d8cc71d1f84e1ef6fd24701fd308f669e28a6135beac130fc51a6ccb5cef3c5005f4f557207fe5c84c4aedbb5b098dc9a882a9b7d801c61e34cd90517b4628b5a18b96b3fc61663b48391146b8c0fa2a858 (2 rows) |
As always, I hope this code complete solution helps those trying to work with this technical stack.
DML Event Management
Data Manipulation Language (DML)
DML statements add data to, change data in, and remove data from tables. This section examines four DML statements—the INSERT, UPDATE, DELETE, and MERGE statements—and builds on concepts of data transactions. The INSERT statement adds new data, the UPDATE statement changes data, the DELETE statement removes data from the database, and the MERGE statement either adds new data or changes existing data.
Any INSERT, UPDATE, MERGE, or DELETE SQL statement that adds, updates, or deletes rows in a table locks rows in a table and hides the information until the change is committed or undone (that is, rolled back). This is the nature of ACID-compliant SQL statements. Locks prevent other sessions from making a change while a current session is working with the data. Locks also restrict other sessions from seeing any changes until they’re made permanent. The database keeps two copies of rows that are undergoing change. One copy of the rows with pending changes is visible to the current session, while the other displays committed changes only.
ACID Compliant Transactions
ACID compliance relies on a two-phase commit (2PC) protocol and ensures that the current session is the only one that can see new inserts, updated column values, and the absence of deleted rows. Other sessions run by the same or different users can’t see the changes until you commit them.
ACID Compliant INSERT Statements
The INSERT statement adds rows to existing tables and uses a 2PC protocol to implement ACID- compliant guarantees. The SQL INSERT statement is a DML statement that adds one or more rows to a table. Oracle supports a VALUES clause when adding a single-row, and support a subquery when adding one to many rows.
The figure below shows a flow chart depicting an INSERT statement. The process of adding one or more rows to a table occurs during the first phase of an INSERT statement. Adding the rows exhibits both atomic and consistent properties. Atomic means all or nothing: it adds one or more rows and succeeds, or it doesn’t add any rows and fails. Consistent means that the addition of rows is guaranteed whether the database engine adds them sequentially or concurrently in threads.
Concurrent behaviors happen when the database parallelizes DML statements. This is similar to the concept of threads as lightweight processes that work under the direction of a single process. The parallel actions of a single SQL statement delegate and manage work sent to separate threads. Oracle supports all ACID properties and implements threaded execution as parallel operations. All tables support parallelization.
After adding the rows to a table, the isolation property prevents any other session from seeing the new rows—that means another session started by the same user or by another user with access to the same table. The atomic, consistent, and isolation properties occur in the first phase of any INSERT statement. The durable property is exclusively part of the second phase of an INSERT statement, and rows become durable when the COMMIT statement ratifies the insertion of the new data.
ACID Compliant UPDATE Statements
An UPDATE statement changes column values in one-to-many rows. With a WHERE clause, you update only rows of interest, but if you forget the WHERE clause, an UPDATE statement would run against all rows in a table. Although you can update any column in a row, it’s generally bad practice to update a primary or foreign key column because you can break referential integrity. You should only update non-key data in tables—that is, the data that doesn’t make a row unique within a table.
Changes to column values are atomic when they work. For scalability reasons, the database implementation of updates to many rows is often concurrent, in threads through parallelization. This process can span multiple process threads and uses a transaction paradigm that coordinates changes across the threads. The entire UPDATE statement fails when any one thread fails.
Similar to the INSERT statement, UPDATE statement changes to column values are also hidden until they are made permanent with the application of the isolation property. The changes are hidden from other sessions, including sessions begun by the same database user.
It’s possible that another session might attempt to lock or change data in a modified but uncommitted row. When this happens, the second DML statement encounters a lock and goes into a wait state until the row becomes available for changes. If you neglected to set a timeout value for the wait state, such as this clause, the FOR UPDATE clause waits until the target rows are unlocked:
WAIT n |
As the figure below shows, actual updates are first-phase commit elements. While an UPDATE statement changes data, it changes only the current session values until it is made permanent by a COMMIT statement. Like the INSERT statement, the atomic, consistent, and isolation properties of an UPDATE statement occur during the first phase of a 2PC process. Changes to column values are atomic when they work. Any column changes are hidden from other sessions until the UPDATE statement is made permanent by a COMMIT or ROLLBACK statement, which is an example of the isolation property.
Any changes to column values can be modified by an ON UPDATE trigger before a COMMIT statement. ON UPDATE triggers run inside the first phase of the 2PC process. A COMMIT or ROLLBACK statement ends the transaction scope of the UPDATE statement.
The Oracle database engine can dispatch changes to many threads when an UPDATE statement works against many rows. UPDATE statements are consistent when these changes work in a single thread-of-control or across multiple threads with the same results.
As with the INSERT statement, the atomic, consistent, and isolation properties occur during the first phase of any UPDATE statement, and the COMMIT statement is the sole activity of the second phase. Column value changes become durable only with the execution of a COMMIT statement.
ACID Compliant DELETE Statements
A DELETE statement removes rows from a table. Like an UPDATE statement, the absence of a WHERE clause in a DELETE statement deletes all rows in a table. Deleted rows remain visible outside of the transaction scope where it has been removed. However, any attempts to UPDATE those deleted rows are held in a pending status until they are committed or rolled back.
You delete rows when they’re no longer useful. Deleting rows can be problematic when rows in another table have a dependency on the deleted rows. Consider, for example, a customer table that contains a list of cell phone contacts and an address table that contains the addresses for some but not all of the contacts. If you delete a row from the customer table that still has related rows in the address table, those address table rows are now orphaned and useless.
As a rule, you delete data from the most dependent table to the least dependent table, which is the opposite of the insertion process. Basically, you delete the child record before you delete the parent record. The parent record holds the primary key value, and the child record holds the foreign key value. You drop the foreign key value, which is a copy of the primary key, before you drop the primary key record. For example, you would insert a row in the customer table before you insert a row in the address table, and you delete rows from the address table before you delete rows in the customer table.
The figure below shows the logic behind a DELETE statement. Like the INSERT and UPDATE statements, acid, consistency, and isolation properties of the ACID-compliant transaction are managed during the first phase of a 2PC. The durability property is managed by the COMMIT or ROLLBACK statement.
There’s no discussion or diagrams for the MERGE statement because it does either an INSERT or UPDATE statement based on it’s internal logic. That means a MERGE statement is ACID compliant like an INSERT or UPDATE statement.
AlmaLinux Flask
This post shows how to install and test Flask with Python on AlmaLinux. You install the flask Python libraries with the following commands as the student user. The student user is in the sudoer group.
pip3 install flask_sslify --user student |
It produces the following log file:
Display detailed console log →
Collecting flask Downloading Flask-2.2.2-py3-none-any.whl (101 kB) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 101.5/101.5 kB 737.3 kB/s eta 0:00:00 Collecting student Downloading Student-0.0.1-py3-none-any.whl (2.5 kB) Collecting itsdangerous>=2.0 Downloading itsdangerous-2.1.2-py3-none-any.whl (15 kB) Collecting importlib-metadata>=3.6.0 Downloading importlib_metadata-5.2.0-py3-none-any.whl (21 kB) Collecting Jinja2>=3.0 Downloading Jinja2-3.1.2-py3-none-any.whl (133 kB) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 133.1/133.1 kB 1.9 MB/s eta 0:00:00 Collecting Werkzeug>=2.2.2 Downloading Werkzeug-2.2.2-py3-none-any.whl (232 kB) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 232.7/232.7 kB 18.5 MB/s eta 0:00:00 Collecting click>=8.0 Downloading click-8.1.3-py3-none-any.whl (96 kB) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 96.6/96.6 kB 16.2 MB/s eta 0:00:00 Collecting zipp>=0.5 Downloading zipp-3.11.0-py3-none-any.whl (6.6 kB) Collecting MarkupSafe>=2.0 Downloading MarkupSafe-2.1.1-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (25 kB) Installing collected packages: zipp, student, MarkupSafe, itsdangerous, click, Werkzeug, Jinja2, importlib-metadata, flask Successfully installed Jinja2-3.1.2 MarkupSafe-2.1.1 Werkzeug-2.2.2 click-8.1.3 flask-2.2.2 importlib-metadata-5.2.0 itsdangerous-2.1.2 student-0.0.1 zipp-3.11.0 |
You can use the following hello.py test program
# Import libraries. from flask import Flask, escape, request from markupsafe import escape # Define the application. app = Flask(__name__) # Define a base URI route and function. @app.route('/') def index(): return "Hello World!" # Define an application URI route and function. @app.route("/hello") def hello(): name = request.args.get("name","Simon") return f'Hello {escape(name)}!' # Define an about URI route and function. @app.route("/about") def about(): return "About Page." # Define an <username> variable rule for a route. @app.route("/user/<string:username>") def show_user_profile(username): return 'User [%s].' % escape(username) # Define an <username> variable rule for a route. @app.route("/year/<int:year>") def show_post(year): return 'Year [%d].' % year # Run the file. if __name__ == "__main__": app.run() |
You can start the Flask server with the following two commands in a separate shell session. This allows you to monitor activities and writes an activity log:
export FLASK_APP=hello.py flask run |
It also writes a compiled version of the hello.py program to the __pycache__ directory. If you make changes to the base file, you must delete the cached version in the __pycache__ directory.
You can test it by typing any of the following URL in a browser:
- The index page without a routing label in the URL:
http://localhost:5000/hello
It’ll print the following:
Hello World!
- The index page with a hello routing label in the URL:
http://localhost:5000/hello
It’ll print the following:
Hello Simon!
- The index page with a about routing label in the URL:
http://localhost:5000/about
It’ll print the following:
About Page.
- The index page with a user routing label in the URL:
http://localhost:5000/user/Somebody
It’ll print the following:
User [Somebody].
- The index page with a year routing label in the URL:
http://localhost:5000/year/1986
It’ll print the following:
Year [1986].
The activity log shows the following:
* Serving Flask app 'hello.py' * Debug mode: off WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead. * Running on http://127.0.0.1:5000 Press CTRL+C to quit 127.0.0.1 - - [29/Dec/2022 19:33:12] "GET / HTTP/1.1" 200 - 127.0.0.1 - - [29/Dec/2022 19:33:21] "GET /hello HTTP/1.1" 200 - 127.0.0.1 - - [29/Dec/2022 19:33:27] "GET /about HTTP/1.1" 200 - 127.0.0.1 - - [29/Dec/2022 19:33:48] "GET /year/1986 HTTP/1.1" 200 - 127.0.0.1 - - [29/Dec/2022 19:34:09] "GET /user/Somebody HTTP/1.1" 200 - |
If you stop the process with the Ctrl+Z, the process will not stop but not the listener process. As a sudoer user, you can find the open listener process with the following command:
sudo netstat -nlp | grep 5000 [sudo] password for student: |
It will return something like this:
tcp 0 0 127.0.0.1:5000 0.0.0.0:* LISTEN 143363/python3 |
You kill the process without prejudice by using the following command:
kill -9 143363 |
As always, I hope this helps those who are looking for step-by-step instructions.
MySQL PNG Files
LAMP (Linux, Apache, MySQL, Perl/PHP/Python) Architecture is very flexible. All the components can be positioned on the same server or different servers. The servers are divided into two types. The types are known as the Application or database tiers. Generally, the application tier holds the Apache Server, any Apache Modules, and local copies of Server Side Includes (SSI) programs.
In many development environments, you also deploy the client to the same machine. This means a single machine runs the database server, the application server, and the browser. The lab for this section assumes these configurations.
Before you test an installation, you should make sure that you’ve started the database and Apache server. In an Oracle LAMP configuration (known as an OLAP – Oracle, Linux, Apache, Perl/PHP/Python), you must start both the Oracle Listener and database. MySQL starts the listener when you start the database. You must also start the Apache Server. The Apache Server also starts an Apache Listener, which listens for incoming HTTP/HTTPS requests. It listens on Port 80
unless you override that setting in the httpd.conf
file.
The URI reaches the server and is redirected to an Apache Module based on configuration information found in the httpd.conf
file. Spawned or child processes of the Apache Module then read programs into memory from the file system and run them. If you’ve uploaded a file the locally stored program can move it from a secure cache location to another local area for processing. The started programs can run independently or include other files as libraries, and they can communicate to the database server.
Working though PHP test cases against the MySQL database for my AlmaLinux installation and configuration, I discovered that the php-gd library weren’t installed by default. I had to add it to get my PHP programs to upload and display PNG files.
The log file for applying the php-gd packages:
Display detailed console log →
Last metadata expiration check: 3:59:15 ago on Wed 28 Dec 2022 08:17:58 PM EST. Dependencies resolved. ================================================================================ Package Architecture Version Repository Size ================================================================================ Installing: php-gd x86_64 8.0.20-3.el9 appstream 43 k Transaction Summary ================================================================================ Install 1 Package Total download size: 43 k Installed size: 110 k Downloading Packages: php-gd-8.0.20-3.el9.x86_64.rpm 196 kB/s | 43 kB 00:00 -------------------------------------------------------------------------------- Total 39 kB/s | 43 kB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : php-gd-8.0.20-3.el9.x86_64 1/1 Running scriptlet: php-gd-8.0.20-3.el9.x86_64 1/1 Verifying : php-gd-8.0.20-3.el9.x86_64 1/1 Installed: php-gd-8.0.20-3.el9.x86_64 Complete! |
The balance of this page demonstrates how to upload, store, and manage Text (Character Large Data Streams) and BLOBs (Binary Large Objects). It provides MySQL equivalent instructions to those for manaing LOBs in an Oracle database. As covered in Chapter 8 in my Oracle Database 11g PL/SQL Programming book.
Before you begin these steps, you should have already installed Zend Server Community Edition. If you haven’t done so, please click here for instructions.
Create directories or folders, and position code →
This section provides you with instructions on how to position the code components in Windows, at least for the newbie. If you’re on Linux, you probably know how to do most if not all of this already. Likewise, if you already know how to put things in the right place, please choose your own locations.
- Create a
LOB
(Large Object) directory for the PHP files inside thehtdocs
directory.
- You can down the MySQL PHP Upload LOB Web Code zip file and unzip it into the directory you just created. It can co-exist with the Oracle equivalent if you’ve done that already.
Load a TEXT (like an Oracle CLOB) column to the MySQL database →
This is a copy of the three files required to load a large string to a MySQL database into a mediumtext
data type. The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.
MySQLCredentials.inc
1 2 3 4 5 6 7 | <?php // Connection variables. define('HOSTNAME',"localhost"); define('USERNAME',"student"); define('PASSWORD',"student"); define('DATABASE',"sampledb"); ?> |
UploadItemDescMySQLForm.htm
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | <html> <head> <title> UploadItemDescMySQLForm.htm </title> </head> <body> <form id="uploadForm" action="UploadItemDescMySQL.php" enctype="multipart/form-data" method="post"> <table border=0 cellpadding=0 cellspacing=0> <tr> <td width=125>Item Number</td> <td> <input id="id" name="id" type="text"> </td> </tr> <tr> <td width=125>Item Title</td> <td> <input id="title" name="title" type="text"> </td> </tr> <tr> <td width=125>Select File</td> <td> <input id="uploadfilename" name="userfile" type="file"> </td> </tr> <tr> <td width=125>Click Button to</td> <td><input type="submit" value="Upload File"></td> </tr> </table> </form> </body> </html> |
UploadItemDescMySQL.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | <?php // Set database credentials. include_once("MySQLCredentials.inc"); // Displayed moved file in web page. $item_desc = process_uploaded_file(); // Return successful attempt to connect to the database. if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) { // Print user message. print "Sorry! The connection to the database failed. Please try again later."; // Assign the mysqli_error() and format double and single quotes. print mysqli_error(); // Kill the resource. die(); } else { // Declare input variables. $id = (isset($_POST['id'])) ? (int) $_POST['id'] : $id = 21; $title = (isset($_POST['title'])) ? $_POST['title'] : $title = "Harry #1"; // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // Declare a PL/SQL execution command. $sql = "Update item set item_desc = ? where item_id = ?"; // Prepate statement and link it to a connection. if (mysqli_stmt_prepare($stmt,$sql)) { mysqli_stmt_bind_param($stmt,"si",$item_desc,$id); // Execute it and print success or failure message. if (mysqli_stmt_execute($stmt)) { query_insert($id,$title); } else { print "You're target row doesn't exist."; } } // Disconnect from database. mysqli_close($c); } // Query results afret an insert. function query_insert($id,$title) { // Return successful attempt to connect to the database. if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) { // Print user message. print "Sorry! The connection to the database failed. Please try again later."; // Assign the OCI error and format double and single quotes. print mysqli_error(); // Kill the resource. die(); } else { // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // Declare a SQL SELECT statement returning a CLOB. $sql = "SELECT item_desc FROM item WHERE item_id = ?"; // Prepare statement. if (mysqli_stmt_prepare($stmt,$sql)) { mysqli_stmt_bind_param($stmt,"i",$id); // Execute it and print success or failure message. if (mysqli_stmt_execute($stmt)) { // Bind result to local variable. mysqli_stmt_bind_result($stmt, $desc); // Read result. mysqli_stmt_fetch($stmt); // Format HTML table to display biography. $out = '<table border="1" cellpadding="3" cellspacing="0">'; $out .= '<tr>'; $out .= '<td align="center" class="e">'.$title.'</td>'; $out .= '</tr>'; $out .= '<tr>'; $out .= '<td class="v">'.$desc.'</td>'; $out .= '</tr>'; $out .= '</table>'; // Print the HTML table. print $out; } } // Disconnect from database. mysqli_close($c); } } // Manage file upload and return file as string. function process_uploaded_file() { // Declare a variable for file contents. $contents = ""; // Define the upload file name for Windows or Linux. if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"])) $upload_file = "C:\\temp\\".$_FILES['userfile']['name']; else $upload_file = "/tmp/".$_FILES['userfile']['name']; // Check for and move uploaded file. if (is_uploaded_file($_FILES['userfile']['tmp_name'])) move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file); // Open a file handle and suppress an error for a missing file. if ($fp = @fopen($upload_file,"r")) { // Read until the end-of-file marker. while (!feof($fp)) $contents .= fgetc($fp); // Close an open file handle. fclose($fp); } // Return file content as string. return $contents; } ?> |
Load a BLOB column to the MySQL database →
This is a copy of the four files required to load a large image to a MySQL database into a MEDIUMBLOB
data type. The fourth file reads the binary image and translates it into an HTML header and image that can be read through a call to the src
attribute of an img
tag. You can find the call to the forth file in the UploadItemBlobMySQL.php
.
The code is in clear text because somebody asked for it. They’re nervous about zip files. Click the title above to expand all the code text.
MySQLCredentials.inc
1 2 3 4 5 6 7 | <?php // Connection variables. define('HOSTNAME',"localhost"); define('USERNAME',"student"); define('PASSWORD',"student"); define('DATABASE',"sampledb"); ?> |
UploadItemBlobMySQLForm.htm
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | <html> <head> <title> UploadItemBlobMySQLForm.htm </title> </head> <body> <form id="uploadForm" action="UploadItemBlobMySQL.php" enctype="multipart/form-data" method="post"> <table border=0 cellpadding=0 cellspacing=0> <tr> <td width=125>Item Number</td> <td> <input id="id" name="id" type="text"> </td> </tr> <tr> <td width=125>Item Title</td> <td> <input id="title" name="title" type="text"> </td> </tr> <tr> <td width=125>Select File</td> <td> <input id="uploadfilename" name="userfile" type="file"> </td> </tr> <tr> <td width=125>Click Button to</td> <td><input type="submit" value="Upload File"></td> </tr> </table> </form> </body> </html> |
UploadItemBlobMySQL.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 | <?php // Set database credentials. include_once("MySQLCredentials.inc"); // Displayed moved file in web page. $item_blob = process_uploaded_file(); // Return successful attempt to connect to the database. if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) { // Print user message. print "Sorry! The connection to the database failed. Please try again later."; // Assign the mysqli_error() error and format double and single quotes. print mysqli_error(); // Kill the resource. die(); } else { // Declare input variables. $id = (isset($_POST['id'])) ? (int) $_POST['id'] : 1021; $title = (isset($_POST['title'])) ? $_POST['title'] : "Harry #1"; // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // Declare a PL/SQL execution command. $sql = "UPDATE item SET item_blob = ? WHERE item_id = ?"; // Prepare statement and link it to a connection. if (mysqli_stmt_prepare($stmt,$sql)) { mysqli_stmt_bind_param($stmt,"bi",$item_blob,$id); $start = 0; $chunk = 8192; while ($start < strlen($item_blob)) { mysqli_stmt_send_long_data($stmt,0,substr($item_blob,$start,$chunk)); $start += $chunk; } // Execute the PL/SQL statement. if (mysqli_stmt_execute($stmt)) { query_insert($id,$title); } else { print "Your target row doesn't exist."; } } else { print "mysqli_stmt_prepare() failed."; } // Disconnect from database. mysqli_close($c); } // Query results afret an insert. function query_insert($id,$title) { // Return successful attempt to connect to the database. if (!$c = @mysqli_connect(HOSTNAME,USERNAME,PASSWORD,DATABASE)) { // Print user message. print "Sorry! The connection to the database failed. Please try again later."; // Assign the OCI error and format double and single quotes. print mysqli_error(); // Kill the resource. die(); } else { // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // Declare a SQL SELECT statement returning a CLOB. $sql = "SELECT item_desc FROM item WHERE item_id = ?"; // Prepare statement and link it to a connection. if (mysqli_stmt_prepare($stmt,$sql)) { mysqli_stmt_bind_param($stmt,"i",$id); // Execute the PL/SQL statement. if (mysqli_stmt_execute($stmt)) { // Bind result to local variable. mysqli_stmt_bind_result($stmt, $data); // Read result. mysqli_stmt_fetch($stmt); // Format HTML table to display BLOB photo and CLOB description. $out = '<table border="1" cellpadding="5" cellspacing="0">'; $out .= '<tr>'; $out .= '<td align="center" class="e">'.$title.'</td>'; $out .= '</tr>'; $out .= '<tr><td class="v">'; $out .= '<div>'; $out .= '<div style="margin-right:5px;float:left">'; $out .= '<img src="ConvertMySQLBlobToImage.php?id='.$id.'">'; $out .= '</div>'; $out .= '<div style="position=relative;">'.$data.'</div>'; $out .= '</div>'; $out .= '</td></tr>'; $out .= '</table>'; // Print the HTML table. print $out; } else { print "You're target row doesn't exist."; } } // Disconnect from database. mysqli_close($c); } } // Manage file upload and return file as string. function process_uploaded_file() { // Declare a variable for file contents. $contents = ""; // Define the upload file name for Windows or Linux. if (preg_match(".Win32.",$_SERVER["SERVER_SOFTWARE"])) $upload_file = "C:\\TEMP\\".$_FILES['userfile']['name']; else $upload_file = "/tmp/".$_FILES['userfile']['name']; // Check for and move uploaded file. if (is_uploaded_file($_FILES['userfile']['tmp_name'])) move_uploaded_file($_FILES['userfile']['tmp_name'],$upload_file); // Open a file handle and suppress an error for a missing file. if ($fp = @fopen($upload_file,"r")) { // Read until the end-of-file marker. while (!feof($fp)) $contents .= fgetc($fp); // Close an open file handle. fclose($fp); } // Return file content as string. return $contents; } ?> |
ConvertMySQLBlobToImage.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | <?php // Database credentials must be set manually because an include_once() function // call puts something ahead of the header, which causes a failure when rendering // an image. // Return successful attempt to connect to the database. if (!$c = @mysqli_connect("localhost","student","student","sampledb")) { // Print user message. print "Sorry! The connection to the database failed. Please try again later."; // Assign the OCI error and format double and single quotes. print mysqli_error(); // Kill the resource. die(); } else { // Declare input variables. $id = (isset($_GET['id'])) ? (int) $_GET['id'] : 1023; // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($c); // Declare a SQL SELECT statement returning a MediumBLOB. $sql = "SELECT item_blob FROM item WHERE item_id = ?"; // Prepare statement and link it to a connection. if (mysqli_stmt_prepare($stmt,$sql)) { mysqli_stmt_bind_param($stmt,"i",$id); // Execute the PL/SQL statement. if (mysqli_stmt_execute($stmt)) { // Bind result to local variable. mysqli_stmt_bind_result($stmt, $image); // Read result. mysqli_stmt_fetch($stmt); } } // Disconnect from database. mysqli_close($c); // Print the header first. header('Content-type: image/x-png'); imagepng(imagecreatefromstring($image)); } ?> |
- Create a
temp
directory for the upload target location, as qualified in the PHP code. The PHP code works regardless of whether you’re on Windows or Linux, but it does depend on the creation of this directory.
- Create a directory or folder for the large file source directories. This directory is probably on your test machine (laptop) but it mimics a client laptop and would work if your server was on a different machine.
- Inside the Upload directory, you should create the following two directories:
- You should download the CLOB Text File zip file and unzip it into the
textfiles
directory; then download the BLOB Image File zip file and unzip it into the imagefiles
directory.Assuming you’ve downloaded the zip files and extracted them into the correct locations, this section is done.
Prepare the MySQL database →
This section provides you with instructions on how to ensure everything will work once the PHP programs call the database. Even if you have one of my sample Video Store databases, you should verify and add appropriate columns. This post assumes you’ve downloaded the one of my basic Video Store models
- Navigate to the directory that you created for SQL scripts, which should be
/home/student/Data/mysql
. In that directory at the command prompt, connect as thestudent
user, which should be student. You connect to the MySQL database, with the following syntax as student (if you need more help, check this blog post on configuring MySQL).
mysql -ustudent -pstudent |
Once connected to the database, you run the files to create the database, like:
mysql> source /Data/mysql/create_mysql_store.sql mysql> source /Data/mysql/seed_mysql_store.sql |
- Navigate to the directory that you created for SQL scripts, which should be
/home/student/Data/mysql
. In that directory at the command prompt, connect as thestudent
user, or whichever account you’re using. You should confirm that you have aitem_desc
column ofTEXT
data type, and anitem_blob
column ofMEDIUMBLOB
type in theitem
table. If you don’t have those columns, you can add them with the following statement:
ALTER TABLE item ADD (item_desc TEXT, item_blob MEDIUMBLOB); |
After ensuring that you have those two columns, you’ve completed this section.
Test the Configuration →
This section shows you how to test all that you’ve done. It works provided you created the directories and extracted the zip file contents to their respective directories. The virtual URL actually maps to the /var/www/html/lob
directory.
- Enter the
http://localhost/lob/UploadItemDescMySQLForm.htm
URL, and complete the form by choosing a validitem_id
column value and text file from your/home/student/Upload/TextFiles
directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).
- This page displays after you successfully upload the text file to the database.
- Enter the
http://localhost/lob/UploadItemBlobFormMySQL.htm
URL, and complete the form by choosing a validitem_id
column value and image file from your/home/student/Upload/ImageFiles
directory. Then, click the Upload File button (you can see a larger version of the image by clicking on it).
- This page displays after you successfully upload the image file to the database.
Troubleshooting the Configuration →
This section shows you how to check why something isn’t working.
- The first thing to check are the credentials. They’re in the
MySQLCredentials.inc
file. They’re posted with alocalhost
machine name,student
username,student
password, andsampledb
database.
- Not to be funny, but the second thing to check are credentials. Specifically, you need to check the credentials in the
ConvertBlobToImage.php
file. They’re individually entered in the connect string of this file because otherwise they put something in front of the header, which is disallowed to render the image.
- Check to see if the text or image file made it to the
/var/www/html/lob/temp
directory. If they made it that far but no further, check to see if you have valid procedures in thestudent
schema.
- Check whether the
TEXT
andMEDIUMBLOB
are loaded into the database. You use theLENGTH
function, like this:
SELECT i.item_id , length(i.item_desc) , length(i.item_blob) FROM item i WHERE i.item_desc IS NOT NULL OR i.item_blob IS NOT NULL; |
- Check if the
item_id
value is found in the list of values.
- If you’re stumped, add a comment and explain what’s up.
If you find any problems, please let me know. I’ll be happy to fix them.