Deploying Percona Kubernetes Operators with OpenEBS Local
Storage

Deploying Percona Kubernetes Operators with OpenEBS Local Storage

Deploying Percona Kubernetes Operators with OpenEBS Local StorageNetwork volumes in
Kubernetes provide great flexibility, but still, nothing beats
local volumes from direct-attached storage in the sense of database
performance.

I want to explore ways to deploy both Percona
Kubernetes Operators
(Percona Kubernetes Operator for
Percona XtraDB Cluster
and Percona Kubernetes
Operator for Percona Server for MongoDB
) using local
volumes, both on the bare-metal deployments or in cloud
deployments.

Simple Ways

There are two ways available out of the box to deploy using
local storage, which you can use immediately.

You can specify in cluster deployment yaml volume specification,
using either hostPath:

volumeSpec:
      hostPath:
        path: /data
        type: Directory

Or

emptyDir

(which will be equal somewhat to ephemeral storage in EC2):

volumeSpec:
      emptyDir: {}

While this will work, it is a very rigid way to force local
storage, and it is not really the “Kubernetes way”, as we will
lose the capability to manage data volumes. We want to see a more
uniform way with Persistent Volumes and Persistent Volumes
Claims.

Persistent Volumes

Recognizing limitations with hostPath and emptyDir, Kubernetes
introduced
Local Persistent Volumes
.

Unfortunately, while this will work for the simple deployment of
single pods, it does not work with dynamically created volumes
which we need for Operators. We need the support of
Dynamic Volume Provisioning
.

There are several projects to combine Dynamic Volume
Provisioning with Local Persistent Volumes, but I did not have much
success with them, and the only project which worked for me is
OpenEBS.

OpenEBS provides much more than just Local Persistent Volumes,
but in this blog, I want to touch only OpenEBS Local PV
Hostpath.

OpenEBS Local PV Hostpath

This is actually quite simple, and this is what I like about
OpenEBS. First, we will define storage classes:

For the local nvme storage:

apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: localnvme
  annotations:
    openebs.io/cas-type: local
    cas.openebs.io/config: |
      - name: StorageType
        value: hostpath
      - name: BasePath
        value: /data/openebs
provisioner: openebs.io/local
reclaimPolicy: Delete
volumeBindingMode: WaitForFirstConsumer

And for the local ssd:

apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: localssd
  annotations:
    openebs.io/cas-type: local
    cas.openebs.io/config: |
      - name: StorageType
        value: hostpath
      - name: BasePath
        value: /mnt/data/ebs
provisioner: openebs.io/local
reclaimPolicy: Delete
volumeBindingMode: WaitForFirstConsumer

And after that we can deploy Operators using StorageClass, for
example  in cluster deployment yaml:

volumeSpec:
      persistentVolumeClaim:
        storageClassName: localnvme
        accessModes: [ "ReadWriteOnce" ]
        resources:
          requests:
            storage: 200Gi

The cluster will be deployed using localnvme StorageClass, and
using space on /data/openebs.

Now we can observe used volumes with kubectl get pv:

NAME                                       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS   CLAIM                        STORAGECLASS       REASON   AGE
pvc-325e11ff-9082-432c-b484-c0c7a3d1c949   200Gi      RWO            Delete           Bound    pxc/datadir-cluster2-pxc-2   localssd                    2d18h
pvc-3f940d14-8363-450e-a238-a9ff09bee5bc   200Gi      RWO            Delete           Bound    pxc/datadir-cluster3-pxc-1   localnvme                   43h
pvc-421cbf46-73a6-45f1-811e-1fca279fe22d   200Gi      RWO            Delete           Bound    pxc/datadir-cluster2-pxc-1   localssd                    2d18h
pvc-53ee7345-bc53-4808-97d6-2acf728a57d7   200Gi      RWO            Delete           Bound    pxc/datadir-cluster3-pxc-0   localnvme                   43h
pvc-b98eca4a-03b0-4b5f-a152-9b45a35767c6   200Gi      RWO            Delete           Bound    pxc/datadir-cluster2-pxc-0   localssd                    2d18h
pvc-fbe499a9-ecae-4196-b29e-c4d35d69b381   200Gi      RWO            Delete           Bound    pxc/datadir-cluster3-pxc-2   localnvme                   43h

There we can see data volumes from two deployed clusters.
OpenEBS Local PV Hostpath is now my go-to method to deploy clusters
with the local storage.

PostgreSQL 13 New Feature: dropdb –force

postgresql dropdb --force

There have been many big features added to PostgreSQL 13, like
Parallel Vacuum, de-duplication of indexes, etc., and a complete
list can be found at PostgreSQL 13
release notes
. Along with the big features, there are also
small ones added, including dropdb –force.

Dropdb –force

A new command-line option is added to dropdb command, and a
similar SQL option “FORCE” is also added in DROP DATABASE.
Using the option -f or –force with dropdb command or FORCE with
DROP DATABASEto drop the database, it will terminate all existing
connections with the database. Similarly, DROP DATABASE FORCE will
do the same.

In the first terminal, create a test database and a database
test, and connect to the database.

vagrant@vagrant:~$ createdb test;
vagrant@vagrant:~$ psql test
psql (13.0)
Type "help" for help.

In the second terminal, try to drop the test database and you
will get the error message that the test database is being used by
another user.

vagrant@vagrant:/usr/local/pgsql.13/bin$ psql postgres
psql (13.0)
Type "help" for help.
postgres=# drop database test;
ERROR:  database "test" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Now try the same command with the FORCE option. You will see
that the database is dropped successfully.

postgres=# drop database test WITH ( FORCE );
DROP DATABASE

Note: you can also use the command line dropdb test
-f
.

The session on the first terminal will be terminated.

test=# \d
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!?> 

Looking for more info on other PostgreSQL 13 changes? Check out
Ibrar’s previous post,
Postgresql_fdw Authentication Changes in PostgreSQL 13
!

Make Someone Else do the Work - Managing Oracle Database 19c
Users in Active Directory (part 1 ...

One of my least favorite database administration activities is
managing users – creating users, changing passwords, granting
roles – zero fun. Even further down on my scale of fun ways to
spend an afternoon is the governance that goes with user management
– which users have left the organization? Do they still need the
privileges they have? It’s important work, but it is a constant
never-ending drumbeat of repetitive tasks that seems to always
interrupt more interesting activities.

Fortunately, with a few built-in features of the database (in
other words, not extra-cost options, not extra-cost software that
wehave to maintain) we can make someone else do that
tedious work and free up our time for higher value tasks.

The solution? Microsoft Active Directory. I haven’t worked
with many organizations in the past 15 years that do NOT have
Active Directory running. And fortunately, the Oracle Database
plays well with Active Directory and has ever since Oracle 9i. Even
better, starting with Oracle Database 18c and improving in Oracle
Database 19c that integration with Active Directory has become
easier to set up and requires even less maintenance.

To integrate with Active Directory we are going to use two
database features –
Kerberos authentication
, and
Centrally Managed Users
 (note: Centrally Managed Users is an
Enterprise Edition feature). When I started to write this I
realized I was going WAY beyond a reasonable length for a blog, so
I’m splitting this up across two different entries, one for
Kerberos, the other for Centrally Managed Users. If you’d prefer
to see the details on Kerberos in video form, please take a look at
this YouTube video.

At the end of this post I've got a link to another video that
goes into more depth on implementation and troubleshooting.

We will use Kerberos to authenticate database users. Each Active
Directory domain controller is also a Kerberos Distribution Center.
Kerberos can be used standalone to authenticate database users (in
place of a password). A lot of times I’ll advise my clients to
just use Kerberos because that does the work of placing
password management on the Active Directory team’s plate, and
also gets me out of having to worry about immediately
deprovisioning a database user when someone leaves the company.
Once the account is deleted from Active Directory, that account can
no longer login to the database. We can clean it up when we get
around to it. Even if we are going to configure Centrally
Managed Users (CMU) I’ll usually configure Kerberos first because
once that’s done, adding on CMU is just a few more minutes worth
of work.

To setup Kerberos we will need to make changes in three
places:

  • Database Server
  • Client Workstation
  • Active Directory

On the Database Server we configure a new network file called a
krb5.conf. This file tells the Kerberos libraries where the
Kerberos Distribution Center (from here on out I’m just going to
call it the domain controller) is located, what port it is
listening on, and which alias to send to that server. A typical
krb5.conf file looks like this:

[libdefaults]
    default_realm = DBSECLABS.COM
    clockskew = 6000
    passwd_check_s_address = false
    noaddresses = true
    forwardable = yes
[realms]
    DBSECLABS.COM = {
        kdc = DBSECLABS.COM:88
    }
[domain_realm]
    DBSECLABS.COM = DBSECLABS.COM
    .DBSECLABS.COM = DBSECLABS.COM
    dbseclabs.com = DBSECLABS.COM
    .dbseclabs.com = DBSECLABS.COM

 

The file has three sections:

  • libdefaults – parameters that control how Kerberos is going
    to behave
  • realms – for each Kerberos domain, where are the domain
    controllers? What port do they listen for Kerberos requests on
    (almost always port 88) pro tip: Notice I don’t actually have
    a server name or IP address, instead I use the Active Directory
    domain name, and DNS sends me to whichever domain controller is
    available. This builds in fault tolerance and lets me survive the
    constant maintenance of domain controllers
  • domain_realm – these are aliases for the domains
    listed in the realms section. The value on the left side of the
    equal sign is the alias, the value on the right is the entry in the
    realms section that this alias should direct requests to. Kerberos
    is case sensitive, so I list my aliases in both upper and lower
    case because I never can be sure what format a client will
    use.

The next change we need to make on the database server is to
install a Kerberos keytab file. The AD administrator needs to:

1. Create a service account for our database server – this is
just a regular Active Directory user account nothing special.
Because it’s a service account, I usually set “Password never
expires” but follow your organizations standards

Active Directory Users and Computers

2. Have your Active Directory administrator create a keytab for
you. The command they will use to do this will look like this:

ktpass -princ
ORACLE/<DATABASE_SERVER_HOST_NAME>.<DATABASE_SERVER_HOST_DOMAIN>@<ACTIVE
DIRECTORY DEFAULT DOMAIN> -pass <ACTIVE DIRECTORY USERS
PASSWORD> -mapuser <ACTIVE_DIRECTORY_USER_NAME> -crypto
ALL -ptype KRB5_NT_PRINCIPAL -out database.keytab

Copy the keytab to our database server. I usually put the keytab
in $ORACLE_HOME/network/admin directory. If I’m using the new
read-only $ORACLE_HOME feature I put it in
$ORACLE_BASE_HOME/network/admin instead.

The last file we work with is our sqlnet.ora file. In this file
we will add seven new parameters:

#Kerberos Parameters

SQLNET.AUTHENTICATION_SERVICES=(beq,kerberos5pre,kerberos5)
SQLNET.FALLBACK_AUTHENTICATION=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=ORACLE

SQLNET.KERBEROS5_CONF=/oracle/19/dbhome_1/network/admin/krb5.conf
SQLNET.KERBEROS5_CLOCKSKEW=6000
SQLNET.KERBEROS5_CONF_MIT=TRUE
#Following parameter is server-side only

SQLNET.KERBEROS5_KEYTAB=/oracle/19/dbhome_1/network/admin/database.keytab
 

These parameters are described in the
Database Net Services Reference
guide so I’ll save
space by not defining them here. One of the entries,
sqlnet.kerberos5_conf, points to the krb5.conf file we discussed
earlier. Another, sqlnet.kerberos5_keytab, points to the keytab
file generated above.

Copy the krb5.conf file to the client workstation and update the
client's sqlnet.ora with the relevant parameters:

#Kerberos Parameters
SQLNET.AUTHENTICATION_SERVICES=(kerberos5)
SQLNET.FALLBACK_AUTHENTICATION=TRUE
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=ORACLE

SQLNET.KERBEROS5_CONF=/oracle/19/dbhome_1/network/admin/krb5.conf
SQLNET.KERBEROS5_CLOCKSKEW=6000
SQLNET.KERBEROS5_CONF_MIT=TRUE

 

That’s it – now we are ready to begin using Kerberos. 
First, we create an externally authenticated database user
(identified by the Kerberos principle name) in the database and
grant that user the CREATE SESSION privilege. 

SQL>  create user RUSS identified externally as
'rlowenth@DBSECLABS.COM';
User created.
 
SQL> grant create session to RUSS;
Grant succeeded.

 

Now we get a Kerberos ticket from Active Directory and use it to
login to the database. The "rlowenth" you see in the Kerberos
principal name above, and the okinit command below, is my Active
Directory username.

[oracle@russ-test ~]$ okinit rlowenth
Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production
on 18-SEP-2020 22:41:01
Copyright (c) 1996, 2019 Oracle.  All rights reserved.
Configuration file :
/opt/oracle/product/19c/dbhome_1/network/admin/krb5.conf.
Password for rlowenth@DBSECLABS.COM:
 
[oracle@russ-test ~]$ oklist
Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production
on 18-SEP-2020 22:41:07
Copyright (c) 1996, 2019 Oracle.  All rights reserved.
Configuration file :
/opt/oracle/product/19c/dbhome_1/network/admin/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_1001
Default principal: rlowenth@DBSECLABS.COM
 
Valid starting     Expires            Service principal
09/18/20 22:41:05  09/19/20 08:41:05 
krbtgt/DBSECLABS.COM@DBSECLABS.COM
        renew until 09/19/20 22:41:01
 
[oracle@russ-test ~]$ sqlplus /@pdb1
 
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 18
22:41:20 2020 Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 –
Production Version 19.8.0.0.0
SQL> select sys_context('userenv','authentication_method')
from dual;
 
SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')

---------------------------------------------------------
KERBEROS

 

As you can see, we can now login to the database using Kerberos.
If our client is on a Windows desktop we could just use the
in-memory ticket created when we logged in and skip the okinit
step. For more information, this YouTube
video
 

 

AskTom Database Security Kerberos Session recording

 

from the monthly Database
Security community calls
talks more more about Kerberos
implementation and troubleshooting – if you are not already
subscribed to the monthly
Database Security community calls
you might want to do that
now.

In my next post, I’ll take this integration with Active
Directory a step farther, and enable Centrally Managed Users

Postgresql_fdw Authentication Changes in PostgreSQL
13

Postgresql_fdw Authentication Changes in PostgreSQL 13

Postgresql_fdw Authentication Changes in PostgreSQL 13PostgreSQL 13 is released
with some cool features, such as index enhancement, partition
enhancements, and many others. Along with these enhancements, there
are some security-related enhancements that require some
explanation. There are two major ones: one is related to libpq and
the other is related to postgres_fdw. As it is known that postgres_fdw
is considered to be a “reference implementation” for other
foreign data wrappers, all other foreign data wrappers follow their
footsteps in development.  This is a community-supported
foreign-data wrapper. The blog will explain the security changes in
postgresq_fdw.

1 – The superuser can permit the non-superusers to establish a
password-less connection on postgres_fdw

Previously, only the superuser can establish a password-less
connection with PostgreSQL using postgres_fdw. No other
password-less authentication method was allowed. It had been
observed that in some cases there is no password required, so it
does not make sense to have that limitation. Therefore, PostgreSQL
13 introduced a new option (password_required) where superusers can
give permission to non-superusers to use a password-less connection
on postgres_fdw.

postgres=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

postgres=# CREATE SERVER postgres_svr FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres');
CREATE SERVER

postgres=# CREATE FORIENG TABLE foo_for(a INT) SERVER postgres_svr OPTIONS(table_name 'foo');
CREATE FOREIGN TABLE

postgres=# create user MAPPING FOR vagrant SERVER postgres_svr;
CREATE USER MAPPING
postgres=# SELECT * FROM foo_for;
 a 
---
 1
 2
 3
(3 rows)

When we perform the same query from a non-superuser, then we
will get this error message:

ERROR:  password is requiredDETAIL:  Non-superusers must provide a password in the user
mapping

postgres=# CREATE USER nonsup;
CREATE ROLE

postgres=# create user MAPPING FOR nonsup SERVER postgres_svr;
CREATE USER MAPPING

postgres=# grant ALL ON foo_for TO nonsup;
GRANT

vagrant@vagrant:/work/data$ psql postgres -U nonsup;
psql (13.0)
Type "help" for help.

postgres=> SELECT * FROM foo_for;
2020-09-28 13:00:02.798 UTC [16702] ERROR:  password is required
2020-09-28 13:00:02.798 UTC [16702] DETAIL:  Non-superusers must provide a password in the user mapping.
2020-09-28 13:00:02.798 UTC [16702] STATEMENT:  SELECT * FROM foo_for;
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the user mapping.

Now perform the same query from non-superuser after setting the
new parameter password_required ‘false’ while creating the user
mapping.

vagrant@vagrant:/work/data$ psql postgres
psql (13.0)
Type "help" for help.

postgres=# DROP USER MAPPING FOR nonsup SERVER postgres_svr;
DROP USER MAPPING

postgres=# CREATE USER MAPPING FOR nonsup SERVER postgres_svr OPTIONS(password_required 'false');
CREATE USER MAPPING

vagrant@vagrant:/work/data$ psql postgres -U nonsup;
psql (13.0)
Type "help" for help.

postgres=> SELECT * FROM foo_for;
 a 
---
 1
 2
 3
(3 rows)

2 – Authentication via an SSL certificate

A new option is provided to use an SSL certificate for
authentication in postgres_fdw. To achieve this, the two new
options added to use that feature are sslkey and sslcert.

Before performing this task we need to configure SSL for server
and client. There are many blogs available (How
to Enable SSL authentication for an EDB Postgres Advanced
Server
and SSL
Certificates For PostgreSQL
) to setup SSL for PostgreSQL, and
this blog tries to configure SSL with minimum requirements.

Step 1: Generate Key in $PGDATA

vagrant@vagrant$  openssl genrsa -des3 -out server.key 1024
Generating RSA private key, 1024 bit long modulus (2 primes)
.+++++
..................+++++
e is 65537 (0x010001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:


vagrant@vagrant$ openssl rsa -in server.key -out server.key
Enter pass phrase for server.key:
writing RSA key

Step 2:  Change the mode of the server.key

vagrant@vagrant$  chmod og-rwx server.key

Step 3: Generate the certificate

vagrant@vagrant$ openssl req -new -key server.key -days 3650 -out server.crt -x509
-----
Country Name (2 letter code) [AU]:PK
State or Province Name (full name) [Some-State]:ISB
Locality Name (eg, city) []:Islamabad
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Percona
Organizational Unit Name (eg, section) []:Dev
Common Name (e.g. server FQDN or YOUR name) []:localhost
Email Address []:ibrar.ahmad@gmail.com


vagrant@vagrant$ cp server.crt root.crt

Now we need to generate the client certificate.

Step 4: Generate a Client key

vagrant@vagrant$ openssl genrsa -des3 -out /tmp/postgresql.key 1024
Generating RSA private key, 1024 bit long modulus (2 primes)
..........................+++++
.....................................................+++++
e is 65537 (0x010001)
Enter pass phrase for /tmp/postgresql.key:
Verifying - Enter pass phrase for /tmp/postgresql.key:



vagrant@vagrant$ openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key
Enter pass phrase for /tmp/postgresql.key:
writing RSA key


vagrant@vagrant$ openssl req -new -key /tmp/postgresql.key -out 
-----
Country Name (2 letter code) [AU]:PK
State or Province Name (full name) [Some-State]:ISB
Locality Name (eg, city) []:Islamabad
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Percona
Organizational Unit Name (eg, section) []:Dev
Common Name (e.g. server FQDN or YOUR name) []:127.0.0.1
Email Address []:ibrar.ahmad@gmail.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:pakistan
An optional company name []:Percona 

Step 5:  Copy root.crt to the client

vagrant@vagrant$ cp data5555/root.crt /tmp/

Step 6: Test the connection using a certificate

vagrant@vagrant$ psql 'host=localhost port=5555 dbname=postgres user=ibrar sslmode=verify-full sslcert=/tmp/postgresql.crt sslkey=/tmp/postgresql.key sslrootcert=/tmp/root.crt'
psql (13.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=> \q

Now we are ready, and we can create a foreign server in
PostgreSQL with certificates.

postgres=# CREATE server postgres_ssl_svr foreign data wrapper postgres_fdw options (dbname 'postgres', host 'localhost', port '5555', sslcert '/tmp/postgresql.crt', sslkey '/tmp/postgresql.key', sslrootcert '/tmp/root.crt');
CREATE SERVER

postgres=# create user MAPPING FOR vagrant SERVER postgres_ssl_svr;
CREATE USER MAPPING

postgres=# create foreign table foo_ssl_for(a int) server postgres_ssl_svr options(table_name 'foo');
CREATE FOREIGN TABLE

Now we are ready and set to query a foreign table by
postgres_fdw using certificate authentication.

postgres=# select * from foo_ssl_for;

 a 
---
 1
 2
 3
(3 rows)

Note:  Only superusers can modify user mappings options
sslcert and sslkey settings.

Our white paper “Why Choose PostgreSQL?”
looks at the features and benefits of PostgreSQL and presents some
practical usage examples. We also examine how PostgreSQL can be
useful for companies looking to migrate from Oracle.


Download PDF

MySQL 101: Tuning MySQL After Upgrading Memory

Tuning MySQL After Upgrading Memory

Tuning MySQL After Upgrading MemoryIn this post, we will
discuss what to do when you add more memory to your instance.
Adding memory to a server where MySQL is running is common practice
when scaling resources.

First, Some Context

Scaling resources is just adding more resources to your
environment, and this can be split in two main ways: vertical
scaling and horizontal scaling.

Vertical scaling is increasing hardware capacity for a given
instance, thus having a more powerful server, while horizontal
scaling is adding more servers, a pretty standard approach for load
balancing and sharding.

As traffic grows, working datasets are getting bigger, and thus
we start to suffer because the data that doesn’t fit into memory
has to be retrieved from disk. This is a costly operation, even
with modern NVME drives, so at some point, we will need to deal
with either of the scaling solutions we mentioned.

In this case, we will discuss adding more RAM, which is usually
the fastest and easiest way to scale hardware vertically, and also
having more memory is probably the main benefit for MySQL.

How to Calculate Memory Utilization

First of all, we need to be clear about what variables allocate
memory during MySQL operations, and we will cover only commons ones
as there are a bunch of them. Also, we need to know that some
variables will allocate memory globally, and others will do a
per-thread allocation.

For the sake of simplicity, we will cover this topic considering
the usage of the standard storage engine:
InnoDB.

We have globally allocated variables:

key_buffer_size: MyISAM setting should be set
to 8-16M, and anything above that is just wrong because we
shouldn’t use MyISAM tables unless for a particular reason. A
typical scenario is MyISAM being used by system tables only, which
are small (this is valid for versions up to 5.7), and in MySQL 8
system tables were migrated to the InnoDB engine. So the impact of
this variable is negligible.

query_cache_size: 0 is default and removed in
8.0, so we won’t consider it.

innodb_buffer_pool_size: which is the cache
where InnoDB places pages to perform operations. The bigger, the
better. 🙂

Of course, there are others, but their impact is minimal when
running with defaults.

Also, there are other variables that are allocated on each
thread (or open connection):
read_buffer_size, read_rnd_buffer_size, sort_buffer_size,
join_buffer_size and tmp_table_size, and few others. All of them,
by default, work very well as allocation is small and efficient.
Hence, the main potential issue becomes where we allocate many
connections that can hold these buffers for some time and add extra
memory pressure. The ideal situation is to control how many
connections are being opened (and used) and try to reduce that
number to a sufficient number that doesn’t hurt the
application.

But let’s not lose the focus, we have more memory, and we need
to know how to tune it properly to make the best usage.

The most memory-impacting setting we need to focus on is
innodb_buffer_pool_size, as this is where almost all magic happens
and is usually the more significant memory consumer. There is an
old rule of thumb that says, “size of this setting should be set
around 75% of available memory”, and some cloud vendors setup
this value to total_memory*0.75.

I said “old” because that rule was good when running
instances with 8G or 16G of RAM was common, so allocating roughly
6G out of 8G or 13G out of 16G used to be logical.

But what if we run into an instance with 100G or even 200G?
It’s not uncommon to see this type of hardware nowadays, so we
will use 80G out of 100G or 160G out of 200G? Meaning, will we
avoid allocating something between 20G to 40G of memory and leave
that for filesystem cache operations? While these filesystem
operations are not useless, I don’t see OS needing more than
4G-8G for this purpose on a dedicated DB. Also, it is recommended
to use the O_DIRECT flushing method for InnoDB to bypass the
filesystem cache.

Example

Now that we understand the primary variables allocating memory
let’s check a good use case I’m currently working on. Assuming
this system:

$ free -m
      total      used     free    shared    buff/cache    available
Mem: 385625    307295    40921         4         37408        74865

So roughly 380G of RAM, a nice amount of memory. Now let’s
check what is the maximum potential allocation considering max used
connections.
*A little disclaimer here, while this query is not entirely
accurate and thus it can diverge from real results, we can have a
sense of what is potentially going to be allocated, and we can take
advantage of performance_schema database, but this may require
enabling some instruments disabled by default:

mysql > show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections |    67 |
+----------------------+-------+
1 row in set (0.00 sec)

So with a maximum of 67 connections used, we can get:

mysql > SELECT ( @@key_buffer_size
-> + @@innodb_buffer_pool_size
-> + 67 * (@@read_buffer_size
-> + @@read_rnd_buffer_size
-> + @@sort_buffer_size
-> + @@join_buffer_size
-> + @@tmp_table_size )) / (1024*1024*1024) AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 316.4434      |
+---------------+
1 row in set (0.00 sec)

So far, so good, we are within memory ranges, now let’s see
how big the innodb_buffer_pool_size is and if it is well sized:

mysql > SELECT (@@innodb_buffer_pool_size) / (1024*1024*1024) AS BUFFER_POOL_SIZE;
+------------------+
| BUFFER_POOL_SIZE |
+------------------+
| 310.0000         |
+------------------+
1 row in set (0.01 sec)

So the buffer pool is 310G, roughly 82% of total memory, and
total usage so far was around 84% which leaves us around 60G of
memory not being used. Well, being used by filesystem cache, which,
in the end, is not used by InnoDB.

Ok now, let’s get to the point, how to properly configure
memory to be used effectively by MySQL. From pt-mysql-summary we
know that the buffer pool is fully filled:

Buffer Pool Size | 310.0G
Buffer Pool Fill | 100%

Does this mean we need more memory? Maybe, so let’s check how
many disk operations we have in an instance we know with a working
dataset that doesn’t fit in memory (the very reason why we
increased memory size) using with this command:

mysqladmin -r -i 1 -c 60 extended-status | egrep "Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads"
| Innodb_buffer_pool_read_requests | 99857480858|
| Innodb_buffer_pool_reads         | 598600690  |
| Innodb_buffer_pool_read_requests | 274985     |
| Innodb_buffer_pool_reads         | 1602       |
| Innodb_buffer_pool_read_requests | 267139     |
| Innodb_buffer_pool_reads         | 1562       |
| Innodb_buffer_pool_read_requests | 270779     |
| Innodb_buffer_pool_reads         | 1731       |
| Innodb_buffer_pool_read_requests | 287594     |
| Innodb_buffer_pool_reads         | 1567       |
| Innodb_buffer_pool_read_requests | 282786     |
| Innodb_buffer_pool_reads         | 1754       |

Innodb_buffer_pool_read_requests: page reads satisfied from
memory (good)
Innodb_buffer_pool_reads: page reads from disk (bad)

As you may notice, we still get some reads from the disk, and we
want to avoid them, so let’s increase the buffer pool size to
340G (90% of total memory) and check again:

mysqladmin -r -i 1 -c 60 extended-status | egrep "Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads"
| Innodb_buffer_pool_read_requests | 99937722883 |
| Innodb_buffer_pool_reads         | 599056712   |
| Innodb_buffer_pool_read_requests | 293642      |
| Innodb_buffer_pool_reads         | 1           |
| Innodb_buffer_pool_read_requests | 296248      |
| Innodb_buffer_pool_reads         | 0           |
| Innodb_buffer_pool_read_requests | 294409      |
| Innodb_buffer_pool_reads         | 0           |
| Innodb_buffer_pool_read_requests | 296394      |
| Innodb_buffer_pool_reads         | 6           |
| Innodb_buffer_pool_read_requests | 303379      |
| Innodb_buffer_pool_reads         | 0           |

Now we are barely going to disk, and IO pressure was released;
this makes us happy – right?

Summary

If you increase the memory size of a server, you mostly need to
focus on innodb_buffer_pool_size, as this is the most critical
variable to tune. Allocating 90% to 95% of total available memory
on big systems is not bad at all, as OS requires only a few GB to
run correctly, and a few more for memory swap should be enough to
run without problems.

Also, check your maximum connections required (and used,) as
this is a common mistake causing memory issues, and if you need to
run with 1000 connections opened, then allocating 90% of the memory
of the buffer pool may not be possible, and some additional actions
may be required (i.e., adding a proxy layer or a connection
pool).

From MySQL 8, we have a new variable called
innodb_dedicated_server
, which will auto-calculate the memory
allocation. While this variable is really useful for an initial
approach, it may under-allocate some memory in systems with more
than 4G of RAM as it sets the buffer pool size = (detected server
memory * 0.75), so in a 200G server, we have only 150 for the
buffer pool.

Conclusion

Vertical scaling is the easiest and fastest way to improve
performance, and it is also cheaper – but not magical. Tuning
variables properly requires analysis and understanding of how
memory is being used. This post focused on the essential variables
to consider when tuning memory allocation, specifically
innodb_buffer_pool_size and max_connections. Don’t over-tune when
it’s not necessary and be cautious of how these two affect your
systems.

MySQL 101: Tuning MySQL After Upgrading Memory

Tuning MySQL After Upgrading Memory

Tuning MySQL After Upgrading MemoryIn this post, we will
discuss what to do when you add more memory to your instance.
Adding memory to a server where MySQL is running is common practice
when scaling resources.

First, Some Context

Scaling resources is just adding more resources to your
environment, and this can be split in two main ways: vertical
scaling and horizontal scaling.

Vertical scaling is increasing hardware capacity for a given
instance, thus having a more powerful server, while horizontal
scaling is adding more servers, a pretty standard approach for load
balancing and sharding.

As traffic grows, working datasets are getting bigger, and thus
we start to suffer because the data that doesn’t fit into memory
has to be retrieved from disk. This is a costly operation, even
with modern NVME drives, so at some point, we will need to deal
with either of the scaling solutions we mentioned.

In this case, we will discuss adding more RAM, which is usually
the fastest and easiest way to scale hardware vertically, and also
having more memory is probably the main benefit for MySQL.

How to Calculate Memory Utilization

First of all, we need to be clear about what variables allocate
memory during MySQL operations, and we will cover only commons ones
as there are a bunch of them. Also, we need to know that some
variables will allocate memory globally, and others will do a
per-thread allocation.

For the sake of simplicity, we will cover this topic considering
the usage of the standard storage engine:
InnoDB.

We have globally allocated variables:

key_buffer_size: MyISAM setting should be set
to 8-16M, and anything above that is just wrong because we
shouldn’t use MyISAM tables unless for a particular reason. A
typical scenario is MyISAM being used by system tables only, which
are small (this is valid for versions up to 5.7), and in MySQL 8
system tables were migrated to the InnoDB engine. So the impact of
this variable is negligible.

query_cache_size: 0 is default and removed in
8.0, so we won’t consider it.

innodb_buffer_pool_size: which is the cache
where InnoDB places pages to perform operations. The bigger, the
better. 🙂

Of course, there are others, but their impact is minimal when
running with defaults.

Also, there are other variables that are allocated on each
thread (or open connection):
read_buffer_size, read_rnd_buffer_size, sort_buffer_size,
join_buffer_size and tmp_table_size, and few others. All of them,
by default, work very well as allocation is small and efficient.
Hence, the main potential issue becomes where we allocate many
connections that can hold these buffers for some time and add extra
memory pressure. The ideal situation is to control how many
connections are being opened (and used) and try to reduce that
number to a sufficient number that doesn’t hurt the
application.

But let’s not lose the focus, we have more memory, and we need
to know how to tune it properly to make the best usage.

The most memory-impacting setting we need to focus on is
innodb_buffer_pool_size, as this is where almost all magic happens
and is usually the more significant memory consumer. There is an
old rule of thumb that says, “size of this setting should be set
around 75% of available memory”, and some cloud vendors setup
this value to total_memory*0.75.

I said “old” because that rule was good when running
instances with 8G or 16G of RAM was common, so allocating roughly
6G out of 8G or 13G out of 16G used to be logical.

But what if we run into an instance with 100G or even 200G?
It’s not uncommon to see this type of hardware nowadays, so we
will use 80G out of 100G or 160G out of 200G? Meaning, will we
avoid allocating something between 20G to 40G of memory and leave
that for filesystem cache operations? While these filesystem
operations are not useless, I don’t see OS needing more than
4G-8G for this purpose on a dedicated DB. Also, it is recommended
to use the O_DIRECT flushing method for InnoDB to bypass the
filesystem cache.

Example

Now that we understand the primary variables allocating memory
let’s check a good use case I’m currently working on. Assuming
this system:

$ free -m
      total      used     free    shared    buff/cache    available
Mem: 385625    307295    40921         4         37408        74865

So roughly 380G of RAM, a nice amount of memory. Now let’s
check what is the maximum potential allocation considering max used
connections.
*A little disclaimer here, while this query is not entirely
accurate and thus it can diverge from real results, we can have a
sense of what is potentially going to be allocated, and we can take
advantage of performance_schema database, but this may require
enabling some instruments disabled by default:

mysql > show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections |    67 |
+----------------------+-------+
1 row in set (0.00 sec)

So with a maximum of 67 connections used, we can get:

mysql > SELECT ( @@key_buffer_size
-> + @@innodb_buffer_pool_size
-> + 67 * (@@read_buffer_size
-> + @@read_rnd_buffer_size
-> + @@sort_buffer_size
-> + @@join_buffer_size
-> + @@tmp_table_size )) / (1024*1024*1024) AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 316.4434      |
+---------------+
1 row in set (0.00 sec)

So far, so good, we are within memory ranges, now let’s see
how big the innodb_buffer_pool_size is and if it is well sized:

mysql > SELECT (@@innodb_buffer_pool_size) / (1024*1024*1024) AS BUFFER_POOL_SIZE;
+------------------+
| BUFFER_POOL_SIZE |
+------------------+
| 310.0000         |
+------------------+
1 row in set (0.01 sec)

So the buffer pool is 310G, roughly 82% of total memory, and
total usage so far was around 84% which leaves us around 60G of
memory not being used. Well, being used by filesystem cache, which,
in the end, is not used by InnoDB.

Ok now, let’s get to the point, how to properly configure
memory to be used effectively by MySQL. From pt-mysql-summary we
know that the buffer pool is fully filled:

Buffer Pool Size | 310.0G
Buffer Pool Fill | 100%

Does this mean we need more memory? Maybe, so let’s check how
many disk operations we have in an instance we know with a working
dataset that doesn’t fit in memory (the very reason why we
increased memory size) using with this command:

mysqladmin -r -i 1 -c 60 extended-status | egrep "Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads"
| Innodb_buffer_pool_read_requests | 99857480858|
| Innodb_buffer_pool_reads         | 598600690  |
| Innodb_buffer_pool_read_requests | 274985     |
| Innodb_buffer_pool_reads         | 1602       |
| Innodb_buffer_pool_read_requests | 267139     |
| Innodb_buffer_pool_reads         | 1562       |
| Innodb_buffer_pool_read_requests | 270779     |
| Innodb_buffer_pool_reads         | 1731       |
| Innodb_buffer_pool_read_requests | 287594     |
| Innodb_buffer_pool_reads         | 1567       |
| Innodb_buffer_pool_read_requests | 282786     |
| Innodb_buffer_pool_reads         | 1754       |

Innodb_buffer_pool_read_requests: page reads satisfied from
memory (good)
Innodb_buffer_pool_reads: page reads from disk (bad)

As you may notice, we still get some reads from the disk, and we
want to avoid them, so let’s increase the buffer pool size to
340G (90% of total memory) and check again:

mysqladmin -r -i 1 -c 60 extended-status | egrep "Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads"
| Innodb_buffer_pool_read_requests | 99937722883 |
| Innodb_buffer_pool_reads         | 599056712   |
| Innodb_buffer_pool_read_requests | 293642      |
| Innodb_buffer_pool_reads         | 1           |
| Innodb_buffer_pool_read_requests | 296248      |
| Innodb_buffer_pool_reads         | 0           |
| Innodb_buffer_pool_read_requests | 294409      |
| Innodb_buffer_pool_reads         | 0           |
| Innodb_buffer_pool_read_requests | 296394      |
| Innodb_buffer_pool_reads         | 6           |
| Innodb_buffer_pool_read_requests | 303379      |
| Innodb_buffer_pool_reads         | 0           |

Now we are barely going to disk, and IO pressure was released;
this makes us happy – right?

Summary

If you increase the memory size of a server, you mostly need to
focus on innodb_buffer_pool_size, as this is the most critical
variable to tune. Allocating 90% to 95% of total available memory
on big systems is not bad at all, as OS requires only a few GB to
run correctly, and a few more for memory swap should be enough to
run without problems.

Also, check your maximum connections required (and used,) as
this is a common mistake causing memory issues, and if you need to
run with 1000 connections opened, then allocating 90% of the memory
of the buffer pool may not be possible, and some additional actions
may be required (i.e., adding a proxy layer or a connection
pool).

From MySQL 8, we have a new variable called
innodb_dedicated_server
, which will auto-calculate the memory
allocation. While this variable is really useful for an initial
approach, it may under-allocate some memory in systems with more
than 4G of RAM as it sets the buffer pool size = (detected server
memory * 0.75), so in a 200G server, we have only 150 for the
buffer pool.

Conclusion

Vertical scaling is the easiest and fastest way to improve
performance, and it is also cheaper – but not magical. Tuning
variables properly requires analysis and understanding of how
memory is being used. This post focused on the essential variables
to consider when tuning memory allocation, specifically
innodb_buffer_pool_size and max_connections. Don’t over-tune when
it’s not necessary and be cautious of how these two affect your
systems.

Use MySQL BLOB column with PHP to store .pdf file

Like always I am sharing new things I learn here on my blog. I
was recently working on a requirement for a LAMP stack web
application reporting dashboard in which I needed to store – and
eventually – retrieve a .pdf file. I have read in several places
(this
fantastic book is a great resource
) that a viable option is
storing images or documents (.pdf in this case) in the actual
database table as opposed to on the server file system. MySQL has
the BLOB datatype that can be used to store files such as .pdf,
.jpg, .txt, and the like. In this blog post, I cover how I
accomplished uploading and storing the actual .pdf file in a BLOB
column in MySQL using PHP. Any corrections, tips, pointers, and
recommendations for best practices are always welcome. We all learn
as we go!!!

image of notebooks folded upPhoto
by
Laika Notebooks
on
Unsplash

Self-Promotion:

If you enjoy the content written here, by all means, share this
blog and your favorite post(s) with others who may benefit from or
like it as well. Since
coffee is my favorite drink, you can even buy me one if you would
like!

I am using a table named ‘project_pdf’ with 3 columns (see
accompanying screenshot) to store the data:

  • ‘id’: type INTEGER
  • ‘project_name’: type TEXT
  • ‘pdf_doc’: type BLOB

phpMyAdmin database table descriptionTable
structure for the project_pdf table.

With the below simple HTML web form, we can collect the
‘project_name’ and enable the .pdf file attachment upload:

image of web form in the browserSimple
web form to upload a pdf to the database.

Below is the HTML source code for the above web form:

  <link rel="stylesheet"
href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"
integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z"
crossorigin="anonymous"/>
    <title>Upload PDF</title>
    </p><h4 class="text-center" style="margin-top:
100px;">Upload A PDF To The Database</h4>
<div class="d-flex justify-content-center align-self-center"
style="margin-top: 115px;">
    <form action="submit.php" method="POST"
accept-charset="utf-8" enctype="multipart/form-data">
        <div class="formgroup container-fluid">
            <label for="project_name">Project
Name</label>
            <input type="text" name="project_name"/>
        </div>
        <div class="formgroup container-fluid">
            <input type="file" name="pdf_file"
accept=".pdf"/>
            <input type="hidden" name="MAX_FILE_SIZE"
value="67108864"/> <!--64 MB's worth in bytes-->
        </div>
        <div >
            <label for="submit">Submit To
Database</label><br />
            <input type="submit" name="submit"/>
        </div>
    </form>
</div>
    <script
src="https://code.jquery.com/jquery-3.5.1.slim.min.js"
integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj"
crossorigin="anonymous"></script>
    <script
src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"
integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN"
crossorigin="anonymous"></script>
    <script
src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"
integrity="sha384-B4gt1jrGC7Jh4AgTPSdUtOBvfO8shuf57BaghqFfPlYxofvL8/KUEfYiJOMMV+rV"
crossorigin="anonymous"></script>
Looking at the code…

We will use this PHP code stored in a script submit.php –
which is the form action – along with some best practices
utilizing if/else conditional blocks and wrapping all of the
database operations in a try/catch block to complete the .pdf
INSERT in the MySQL BLOB column:

<!?php
if (isset($_POST['submit']) &&
!empty($_FILES['pdf_file']['name'])) {
     //a $_FILES 'error' value of zero means success. Anything else
and something wrong with attached file.
    if ($_FILES['pdf_file']['error'] != 0) {
        echo 'Something wrong with the file.';
    } else { //pdf file uploaded okay.
        //project_name supplied from the form field
        $project_name =
htmlspecialchars($_POST['project_name']);
        //attached pdf file information
        $file_name = $_FILES['pdf_file']['name'];
        $file_tmp = $_FILES['pdf_file']['tmp_name'];
        if ($pdf_blob = fopen($file_tmp, "rb")) {
            try {
                include
__DIR__."/includes/DatabaseConnection.php";
                $insert_sql = "INSERT INTO `project_pdf`
(`project_name`, `pdf_doc`)
                              VALUES(:project_name,
:pdf_doc);";
                $stmt = $pdo->prepare($insert_sql);
                $stmt->bindParam(':project_name',
$project_name);
                $stmt->bindParam(':pdf_doc', $pdf_blob,
PDO::PARAM_LOB);
                if ($stmt->execute() === FALSE) {
                    echo 'Could not save information to the
database';
                } else {
                    echo 'Information saved';
                }
            } catch (PDOException $e) {
                echo 'Database Error '. $e-&gt;getMessage(). '
in '. $e-&gt;getFile().
                    ': '. $e-&gt;getLine();
            }
        } else {
            //fopen() was not successful in opening the .pdf file
for reading.
            echo 'Could not open the attached pdf file';
        }
    }
} else {
    //submit button was not clicked. No direct script
navigation.
    header('Location: choose_file.php');
}
Verify the button is clicked and a file is attached

The first if/else block verifies that the ‘submit’ button
from the form has been clicked and that the ‘pdf_file’ field
has an attachment using the PHP functions isset() and empty() (the
converse of truth by negating with the not ! operator for the
empty() function):

1 isset($_POST['submit']) &&
!empty($_FILES['pdf_file']['name']))

Tip: More validation can be implemented here to
verify the file type is an actual .pdf since that file type is what
we are expecting to store in the database.

Informational: Visit the official PHP online
documentation for more information on isset() and empty().

Check PHP $_FILES array for errors

The $_FILES array provides several related error codes for file
attachments. A value of 0 (zero) means everything is okay with the
file and it is successfully uploaded. In this particular if/else,
block if that value is not 0 (zero), then we echo in the browser
that something is wrong with the file upload:

1 $_FILES['pdf_file']['error'] != 0

Related: See the official PHP online
documentation for more information on file upload errors.

PHP $_POST and $_FILES Data

The $_POST associative array has the value for the
‘project_name’ input field captured in the form and sent
through the HTTP POST method. Likewise, the $_FILES associative
array has several values for a file or attachment. I am assigning 2
of them to variables, but using only one – ['tmp_name'] – in
the subsequent code:

  • ['name'] – The actual file name from the client. (Could be
    used in a file name column if needed
  • ['tmp_name'] – Temporary file name of the uploaded file as
    stored on the server.
1
2
3
$project_name = htmlspecialchars($_POST['project_name']);
$file_name = $_FILES['pdf_file']['name'];
$file_tmp = $_FILES['pdf_file']['tmp_name'];

Related: Read more about POST upload methods in the official
online PHP documentation.

Read in .pdf binary data and prepare to store in MySQL BLOB column
with PHP

The call to fopen() reads in the file in binary format ("rb")
into a ‘$pdf_blob’ variable. If fopen() cannot open the file,
this if/else block echo‘s the message in the else block to the
browser:

1 $pdf_blob = fopen($file_tmp, "rb")

MySQL database connection and prepared statements

Finally, we look at the entire try/catch block.

I have all database connection information stored in a separate
file named DatabaseConnection.php and include it in the script at
this point using the include directive.

Since we are introducing user-supplied input from the web form
into the database, we use prepared statements leveraging the
PHP PDO methods:

  • prepare()
  • bindParam()
  • execute()

INSERT .pdf file into MySQL BLOB column with PHP

If the call to execute() is not successful, we echo a message to
the browser that the information could not be saved. Otherwise, the
data is successfully inserted and we echo ‘Information
saved’:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
try {
    include __DIR__."/includes/DatabaseConnection.php";
    $insert_sql = "INSERT INTO `project_pdf` (`project_name`,
`pdf_doc`)
                  VALUES(:project_name, :pdf_doc);";
    $stmt = $pdo->prepare($insert_sql);
    $stmt->bindParam(':project_name', $project_name);
    $stmt->bindParam(':pdf_doc', $pdf_blob, PDO::PARAM_LOB);
    if ($stmt->execute() === FALSE) {
        echo 'Could not save information to the database';
    } else {
        echo 'Information saved';
    }
} catch (PDOException $e) {
    echo 'Database Error '. $e->getMessage(). ' in '.
$e->getFile().
        ': '. $e->getLine();   
}

Note: It is generally not a good practice to
echo any sort of database error information to the browser. Errors
should be written to a log file instead. However, for the purpose
of this blog post as a learning experience, I echo out any
exceptions that may arise in the catch block.

Using the form to store .pdf in MySQL BLOB column with PHP

Let’s try out the form and PHP code to verify the upload
works. Here is a simple sample .pdf file I created for a demo
run:

image content of pdf documentContents of SamplePDF.pdf document to upload with form.

See this screenshot in which I fill in the ‘project name’
field with a generic ‘First Project’ name and attach the
SimplePDF.pdf file:

image of web form for submitFilled
out web form with pdf attachment for upload.

Upon clicking the ‘Submit’ button, the information is
successfully stored in the database and the success message is
displayed in the browser:

image of browser messageMessage
displayed in the browser after successful pdf upload.

Here is the data saved in the MySQL database table from the
successful upload via our web form and the PHP code:

phpMyAdmin image of record in database tableThe
project_pdf table with inserted pdf and project name.
It works!!!

In the next blog post, I will cover how to retrieve the .pdf
file from the database and display it in the browser. If you see
anything in the code that I can improve on or any mistake, please
let me know via the comments section below.

Additional PHP/MySQL Reading

Be sure and visit these other blog posts I have written on PHP
and MySQL:

Like what you have read? See anything incorrect? Please comment
below and thanks for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope
you discovered something interesting and enlightening. Please share
your findings here, with someone else you know who would get the
same value out of it as well.

Visit the Portfolio-Projects page to see blog
post/technical writing I have completed for clients.

To receive email notifications (Never Spam)..

MySQL Shell Parsing

I’ve been experimenting with the mysqlsh since installing it
last week. It’s been interesting. Overall, I’m totally
impressed but I did find a problem with how it parses stored
procedures.

First thought is always, is it my code? I checked the file by
running it as a script file through MySQL Workbench. It ran
perfectly in MySQL Workbench but failed repeatedly when run from
the mysqlsh utility. Next step, reduce the code to a small test
case, retest it, and log a bug if it is
replicated. My test case in a test.sql file generates the following
errors when run from the mysqlsh utility:

 MySQL  localhost:33060+ ssl  studentdb  SQL > source test.sql
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL ' at line 2
Query OK, 0 rows affected (0.0002 sec)
Query OK, 0 rows affected (0.0003 sec)
ERROR: 1305: PROCEDURE studentdb.test does not exist

The same file generates this series of successful messages when
run as a script file from MySQL Workbench:

Preparing...
Importing test.sql...
Finished executing script
Statement
CREATE PROCEDURE test
pv_input1
One
Operation completed successfully

For those who are curious enough to review the test case, here
it is:

-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$

SELECT 'CREATE PROCEDURE test' AS "Statement";
CREATE PROCEDURE test
( pv_input1  CHAR(12)
, pv_input2  CHAR(19)) MODIFIES SQL DATA
BEGIN
  SELECT pv_input1;
END;
$$

-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;

-- Call the test procedure.
CALL test('One','Two');

Now, I’ll just have to wait to see if it was a parsing error
when they update the bug. I hope this saves others time but
understand stored procedures aren’t widely written for MySQL.

MySQL Configuration

I’ve been using MySQL 8 on Linux for a couple years but the
Linux repository version didn’t install the new MySQL Shell. So,
I discovered the new MySQL Shell when I installed MySQL 8 (8.0.21)
on Windows to teach my student how to use it to learn SQL commands.
I encourage you to read the full
MySQL Shell document
.

The following is a tutorial to provision a student user and
studentdb database in MySQL. It uses the MySQL Shell (mysqlsh) and
stages for uploads of comma-separated values files.

After installing MySQL on the Windows 10 OS, open the Window OS
Command Line Interface (CLI) shell by entering the following in the
search field at the bottom left:

cmd

It launches a CLI interface to the Windows OS. The cmd (command)
utility opens the CLI in the following directory (where you
substitute your user’s name for the username placeholder
below):

C:\Users\username

At the command prompt, you would enter mysql to launch the old
MySQL CLI. Instead of that, you enter mysqlsh to launch the new
MySQL CLI as the root user. The command to launch the MySQL Shell
as the root user is:

mysqlsh -uroot -p

It should return the following and prompt you for a single
character entry to save the password for the Operating System user.
As a rule, in a development instance that’s a good idea and
practice.

MySQL Shell 8.0.21

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost:33060': ********
Save password for 'root@localhost:33060'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use  to set one.
 MySQL  localhost:33060+ ssl  JS >

The prompt will not accept SQL commands because it always
initializes in the JavaScript (JS) context. The MySQL Shell
supports three interactive interfaces: JavaScript, Python, and
SQL.

You can verify the integrity of the shell from the JavaScript
(JS) context as the root user with the following command:

 MySQL  localhost:33060+ ssl  JS > shell.status()

As the root user, it should return something like this:

MySQL Shell version 8.0.21

Connection Id:                9
Default schema:
Current schema:
Current user:                 root@localhost
SSL:                          Cipher in use: TLS_AES_256_GCM_SHA384 TLSv1.3
Using delimiter:              ;
Server version:               8.0.21 MySQL Community Server - GPL
Protocol version:             X protocol
Client library:               8.0.21
Connection:                   localhost via TCP/IP
TCP port:                     33060
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Enabled (DEFLATE_STREAM)
Uptime:                       20 hours 4 min 19.0000 sec

You can switch to the SQL context as the root or any other user
with this command. The switch only changes your form of interaction
with the server and you remain connected as the root user:

 MySQL  localhost:33060+ ssl  JS > \sql

You can verify that your session is still owned by the root user
with the following SELECT statement:

 MySQL  localhost:33060+ ssl  SQL > SELECT user();

It returns:

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.0005 sec)

The next step shows you how to setup a sample studentdb
database. Some syntax has changed from prior MySQL releases. Here
are the three steps:

  1. Create the studentdb database with the following command as the
    MySQL root user:
     MySQL  localhost:33060+ ssl  SQL > 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  localhost:33060+ ssl  SQL > 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  localhost:33060+ ssl  SQL > CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'student';
     MySQL  localhost:33060+ ssl  SQL > GRANT ALL ON studentdb.* TO 'student'@'localhost';
    
  4. Our sample database uses large file uploads with MySQL’s LOAD
    command, which means you need to grant one additional global
    privilege:
     MySQL  localhost:33060+ ssl  SQL > GRANT FILE ON *.* TO 'student'@'localhost';
    

The MySQL FILE privilege is a global privilege to read and write
files on the local server. MySQL 8 installation on Windows 10 sets
the following directory as the target for uploading files in the
my.ini file:

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

You can find the setting in the C:\ProgramData\MySQL\MySQL
Server 8.0\my.ini file. You can find this value without referencing
the my.ini file by querying the data:

show variables like 'secure_file_priv';

A new installation should return:

+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set (0.2253 sec)

You can test the ability to use the LOAD command with the
following avenger.csv test file. Copy it into the
C:\ProgramData\MySQL\MySQL Server 8.0\Uploads directory and make
sure the directory permissions are set to read-only for Everyone
(check the Microsoft OS documentation if these concepts are new to
you).

1,'Anthony','Stark','Iron Man'
2,'Thor','Odinson','God of Thunder'
3,'Steven','Rogers','Captain America'
4,'Bruce','Banner','Hulk'
5,'Clinton','Barton','Hawkeye'
6,'Natasha','Romanoff','Black Widow'

Open another cmd (command) CLI and put the following code into a
file that you save as avenger.sql in the C:\Users\username
directory.

-- Conditionally drop objects.
SELECT 'AVENGER' AS "Drop Table";
DROP TABLE IF EXISTS avenger;

-- Create an avenger table.
CREATE TABLE avenger
( avenger_id      INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, first_name      VARCHAR(20)
, last_name       VARCHAR(20)
, character_name  VARCHAR(20));

-- Load the data from a file, don't forget the \n after the \r on Windows or it won't work.
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/avenger.csv'
-- LOAD DATA INFILE 'avenger.csv'
INTO TABLE avenger
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '/'
LINES TERMINATED BY '\r\n';
 
-- Select the uploaded records.
SELECT * FROM avenger;

From the cmd (command) shell, launch the MySQL Shell as the
student user with the following syntax:

mysqlsh -ustudent -p -Dstudentdb

At the MySQL Shell (mysqlsh) prompt switch from the default
JavaScript (JS) context to SQL context and run the following
query:

 MySQL  localhost:33060+ ssl  studentdb  SQL > \sql
 MySQL  localhost:33060+ ssl  studentdb  SQL > SELECT user(), database();

If you did everything above correctly, it should return:

+-------------------+------------+
| user()            | database() |
+-------------------+------------+
| student@localhost | studentdb  |
+-------------------+------------+
1 row in set (0.0003 sec)

Again, assuming you did everything above correctly, you should
be able to run your avenger.sql script file from the MySQL Shell
(mysqlsh) prompt, like:

 MySQL  localhost:33060+ ssl  studentdb  SQL > source avenger.sql

It should return output like the following:

Query OK, 0 rows affected (0.0003 sec)
+------------+
| Drop Table |
+------------+
| AVENGER    |
+------------+
1 row in set (0.0003 sec)
Query OK, 0 rows affected (0.0595 sec)
Query OK, 0 rows affected (0.0002 sec)
Query OK, 0 rows affected (0.1293 sec)
Query OK, 0 rows affected (0.0002 sec)
Query OK, 6 rows affected (0.0046 sec)

Records: 6  Deleted: 0  Skipped: 0  Warnings: 0
Query OK, 0 rows affected (0.0004 sec)
+------------+------------+------------+-------------------+
| avenger_id | first_name | last_name  | character_name    |
+------------+------------+------------+-------------------+
|          1 | 'Anthony'  | 'Stark'    | 'Iron Man'        |
|          2 | 'Thor'     | 'Odinson'  | 'God of Thunder'  |
|          3 | 'Steven'   | 'Rogers'   | 'Captain America' |
|          4 | 'Bruce'    | 'Banner'   | 'Hulk'            |
|          5 | 'Clinton'  | 'Barton'   | 'Hawkeye'         |
|          6 | 'Natasha'  | 'Romanoff' | 'Black Widow'     |
+------------+------------+------------+-------------------+
6 rows in set (0.0005 sec)

You now have a student user and studentdb database like my
students. I hope it was fun to build.

You can find the my.ini file in the C:\ProgramData\MySQL\MySQL
Server 8.0 directory of a standard Windows 10 file system. Its a
good idea not to change anything unless you know what you’re
doing, and remember you need to restart the Microsoft MySQL80
Service for any change to be made effective in your database
operations.