Archive for the ‘MySQL Developer’ tag
Apache on Fedora 30
There was an option during the Fedora 30 Workstation installation to add the Apache Web Server, but you need to set it to start automatically. Unfortunately, there was no option to install PHP, which I thought odd because of how many web developers learn the trade first on PHP with a LAMP (Linux, Apache, MySQL, Perl/PHP/Python) stack. You see how to fix that shortcoming in this post and how to install and test PHP, mysqli, and pdo to support MySQL 8.
Before you do that make sure you install MySQL 8. You can find my prior blog post on that here.
You set Apache to start automatically, on the next boot of the operating system, with the following command:
chkconfig httpd on |
It creates a symbolic link:
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service. |
However, that command only starts the Apache server the next time you boot the server. You use the following command as the root user to start the Apache server:
apachectl start |
You can verify the installation with the following command as the root user:
ps -ef | grep httpd | grep -v grep |
It should return:
root 5433 1 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5434 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5435 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5436 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5437 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5438 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5442 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND |
and, then verify the listening port with the following command as the root user:
netstat -tulpn | grep :80 |
It should return the following when both the Apache server is listening on port 80 and the Oracle multi-protocol server is listening on port 8080:
tcp6 0 0 :::80 :::* LISTEN 119810/httpd tcp6 0 0 :::8080 :::* LISTEN 1403/tnslsnr |
You can also enter the following URL in the browser to see the Apache Test Page:
http://localhost |
It should display the test page, like this:
You can also create a hello.htm file in the /var/www/html directory to test the ability to read an HTML file. I would suggest the traditional hello.htm file:
<html> <body> Hello World! </body> </html> |
You can call it by using this URL in the browser:
http://localhost/hello.htm |
It should display the test page, like this:
Now, let’s install PHP. You use the following command as a privileged user, which is one found in the sudoer’s list:
yum install -y php |
Display detailed console log →
Last metadata expiration check: 0:37:02 ago on Fri 16 Aug 2019 11:03:54 AM MDT. Dependencies resolved. ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: php x86_64 7.3.8-1.fc30 updates 2.8 M Installing dependencies: nginx-filesystem noarch 1:1.16.0-3.fc30 updates 11 k php-cli x86_64 7.3.8-1.fc30 updates 4.3 M php-common x86_64 7.3.8-1.fc30 updates 1.1 M Installing weak dependencies: php-fpm x86_64 7.3.8-1.fc30 updates 1.5 M Transaction Summary ============================================================================= Install 5 Packages Total download size: 9.6 M Installed size: 43 M Downloading Packages: (1/5): nginx-filesystem-1.16.0-3.fc30.noarch 34 kB/s | 11 kB 00:00 (2/5): php-common-7.3.8-1.fc30.x86_64.rpm 1.1 MB/s | 1.1 MB 00:00 (3/5): php-7.3.8-1.fc30.x86_64.rpm 2.0 MB/s | 2.8 MB 00:01 (4/5): php-fpm-7.3.8-1.fc30.x86_64.rpm 2.2 MB/s | 1.5 MB 00:00 (5/5): php-cli-7.3.8-1.fc30.x86_64.rpm 1.7 MB/s | 4.3 MB 00:02 ----------------------------------------------------------------------------- Total 3.0 MB/s | 9.6 MB 00:03 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : php-common-7.3.8-1.fc30.x86_64 1/5 Installing : php-cli-7.3.8-1.fc30.x86_64 2/5 Running scriptlet: nginx-filesystem-1:1.16.0-3.fc30.noarch 3/5 Installing : nginx-filesystem-1:1.16.0-3.fc30.noarch 3/5 Installing : php-fpm-7.3.8-1.fc30.x86_64 4/5 Running scriptlet: php-fpm-7.3.8-1.fc30.x86_64 4/5 Installing : php-7.3.8-1.fc30.x86_64 5/5 Running scriptlet: php-7.3.8-1.fc30.x86_64 5/5 Running scriptlet: php-fpm-7.3.8-1.fc30.x86_64 5/5 Verifying : nginx-filesystem-1:1.16.0-3.fc30.noarch 1/5 Verifying : php-7.3.8-1.fc30.x86_64 2/5 Verifying : php-cli-7.3.8-1.fc30.x86_64 3/5 Verifying : php-common-7.3.8-1.fc30.x86_64 4/5 Verifying : php-fpm-7.3.8-1.fc30.x86_64 5/5 Installed: php-7.3.8-1.fc30.x86_64 php-fpm-7.3.8-1.fc30.x86_64 nginx-filesystem-1:1.16.0-3.fc30.noarch php-cli-7.3.8-1.fc30.x86_64 php-common-7.3.8-1.fc30.x86_64 Complete! |
Before you test the installation of PHP in a browser, you must restart the Apache HTTP Server. You can do that with the following command as a privileged user:
sudo apachectl restart |
After verifying the connection, you can test it by creating the traditional info.php program file in the /var/www/http directory. The file should contain the following:
1 2 3 | <?php phpinfo(); ?> |
It should display the PHP Version 7.3.8 web page, which ships with Fedora 30:
The next step shows you how to install mysqli and pdo with the yum utility. While it’s unnecessary to check for the older mysql library (truly deprecated), its good practice to know how to check for a conflicting library before installing a new one. Also, I’d prefer newbies get exposed to using the yum utility’s shell environment.
You start the yum shell, as follows:
yum shell |
With the yum shell, you would remove a mysql package with the following command:
> remove php-mysql |
The command will remove the package or tell you that there is no package to remove. Next, you install the php-mysqli package with this command:
install php-mysqli |
You will then be prompted to confirm the installation of the php-mysqli library. Finally, you exit the yum shell with this command:
> quit |
If you want to see the whole interactive shell, click on the link below.
Display detailed console log →
Last metadata expiration check: 0:53:05 ago on Fri 16 Aug 2019 11:03:54 AM MDT. > remove php-mysql No match for argument: php-mysql No packages marked for removal. > install php-mysqlnd > run ============================================================================= Package Architecture Version Repository Size ============================================================================= Installing: php-mysqlnd x86_64 7.3.8-1.fc30 updates 195 k Installing dependencies: php-pdo x86_64 7.3.8-1.fc30 updates 91 k Transaction Summary ============================================================================= Install 2 Packages Total download size: 286 k Installed size: 1.4 M Is this ok [y/N]: y Downloading Packages: (1/2): php-pdo-7.3.8-1.fc30.x86_64.rpm 136 kB/s | 91 kB 00:00 (2/2): php-mysqlnd-7.3.8-1.fc30.x86_64.rpm 183 kB/s | 195 kB 00:01 ----------------------------------------------------------------------------- Total 24 kB/s | 286 kB 00:11 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : php-pdo-7.3.8-1.fc30.x86_64 1/2 Installing : php-mysqlnd-7.3.8-1.fc30.x86_64 2/2 Running scriptlet: php-mysqlnd-7.3.8-1.fc30.x86_64 2/2 Verifying : php-mysqlnd-7.3.8-1.fc30.x86_64 1/2 Verifying : php-pdo-7.3.8-1.fc30.x86_64 2/2 Installed: php-mysqlnd-7.3.8-1.fc30.x86_64 php-pdo-7.3.8-1.fc30.x86_64 Last metadata expiration check: 0:53:54 ago on Fri 16 Aug 2019 11:03:54 AM MDT. > quit Leaving Shell The downloaded packages were saved in cache until the next successful transaction. You can remove cached packages by executing 'dnf clean packages'. |
You need to restart the Apache HTTP listener for these changes to take place, which you do with the same command as shown earlier:
sudo apachectl restart |
I wrote the mysqli_check.php script to verify installation of both the mysqli and pdo libraries. The full code should be put in a mysqli_check.php file in the /var/www/html directory for testing.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
print 'mysqli not installed.'; }
else {
print 'mysqli installed.'; }
if (!function_exists('pdo_init') && !extension_loaded('pdo')) {
print '<p>pdo not installed.</p>'; }
else {
print '<p>pdo installed.</p>'; }
?>
</script>
</body>
</html> |
You can test it with the following URL from the local browser:
http://localhost/mysqli_check.php |
It should print the following to the web page when you’ve successfully install the mysqli and pdo libraries:
mysqli installed. pdo installed. |
If you plan to use PHP to display and render graphics, you need to install php-gd library. You can do that with the yum utility and this prior blog post explains it. Don’t forget to restart the Apache HTTP Server after you add the php-gd library.
For example, one of my sample PHP programs loads a PNG image into a BLOB column as raw binary text. Then, the program reads it and renders it with PHP to produce the following web page.
As always, I hope this helps those looking for a complete solution without cost.
MySQL on Fedora 30
While updating my class image to Fedora 30, I noticed that it installed the Akonadi Server. The documentation on the Akonadi server lacked some straightforward documentation. It also offered a bundled set of software that limited how to approach MySQL development.
So, I removed all those packages with the following syntax:
dnf remove `rpm -qa | grep akonadi` |
Display detailed console log →
After removing those Akonadi packages, I installed the MySQL Community Edition from the Fedora repo with this syntax:
yum install -y community-mysql* |
Display detailed console log →
Having installed MySQL Community Edition, I wanted to start the mysql service with this command:
sudo service mysqld start |
Unfortunately, the service utility wasn’t installed. That surprised me. While I could have run this command:
systemctl start mysqld.service |
A better solution was to install any missing code components. I determined that the service utility is part of the initscripts package; and I installed it with the following command:
sudo yum install -y initscripts |
Display detailed console log →
Fedora Modular 30 - x86_64 30 kB/s | 18 kB 00:00 Fedora Modular 30 - x86_64 - Updates 40 kB/s | 17 kB 00:00 Fedora 30 - x86_64 - Updates 43 kB/s | 17 kB 00:00 Fedora 30 - x86_64 58 kB/s | 19 kB 00:00 google-chrome-unstable 12 kB/s | 1.3 kB 00:00 google-chrome 16 kB/s | 1.3 kB 00:00 Dependencies resolved. ============================================================================= Package Architecture Version Repository Size ============================================================================= Installing: initscripts x86_64 10.02-1.fc30 updates 202 k Transaction Summary ============================================================================= Install 1 Package Total download size: 202 k Installed size: 1.1 M Downloading Packages: initscripts-10.02-1.fc30.x86_64.rpm 296 kB/s | 202 kB 00:00 ----------------------------------------------------------------------------- Total 162 kB/s | 202 kB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : initscripts-10.02-1.fc30.x86_64 1/1 Running scriptlet: initscripts-10.02-1.fc30.x86_64 1/1 Verifying : initscripts-10.02-1.fc30.x86_64 1/1 Installed: initscripts-10.02-1.fc30.x86_64 Complete! |
Then, I ran the mysql_secure_installation script to secure the installation:
mysql_secure_installation |
The script set the root user’s password, remove the anonymous user, disallow remote root login, and remove the test databases. Then, I verified connecting to the MySQL database with the following syntax:
mysql -uroot -ppassword |
I enabled the MySQL Service to start with each reboot of the Fedora instance. I used the following command:
systemctl enable mysqld.service |
It creates the following link:
ln -s '/etc/systemd/system/multi-user.target.wants/mysqld.service' '/usr/lib/systemd/system/mysqld.service' |
The next step requires setting up a sample studentdb database. The syntax has changed from prior releases. Here are the three steps:
- Create the
studentdbdatabase with the following command as the MySQLrootuser:mysql> CREATE DATABASE studentdb; - Grant the
rootuser the privilege to grant to others, which root does not have by default. You use the following syntax as the MySQLrootuser:mysql> GRANT ALL ON *.* TO 'root'@'localhost';
- Create the user with a clear English password and grant the user
studentfull privileges on thestudentdbdatabase:mysql> CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'student'; mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost';
If you fail to specify mysql_native_password when creating the user and use the older syntax like the following example:
mysql> CREATE USER 'student'@'localhost' IDENTIFIED BY 'student'; mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost'; |
The GRANT command will raise the following error:
ERROR 1410 (42000): You are not allowed to create a user with GRANT |
MySQL 5.7.* and mysqli
After installing MySQL 5.7.22 and PHP 7.1.17 on Fedora 27, you need to install the mysqli library. You need to verify if the mysqli library is installed. You can do that with the following mysqli_check.php program:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <html> <header> <title>Check mysqli Install</title> </header> <body> <?php if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) { print 'mysqli not installed.'; } else { print 'mysqli installed.'; } ?> </script> </body> </html> |
You test preceding PHP program with the following URL in a browser:
http://localhost/mysqli_check.php |
If the mysqli program isn’t installed, you can install it as follows by opening the yum interactive shell:
[root@localhost html]# yum shell Last metadata expiration check: 1:26:46 ago on Wed 22 Aug 2018 08:05:50 PM MDT. > remove php-mysql No match for argument: php-mysql Error: No packages marked for removal. > install php-mysqlnd > run ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: php-mysqlnd x86_64 7.1.20-1.fc27 updates 246 k Upgrading: php x86_64 7.1.20-1.fc27 updates 2.8 M php-cli x86_64 7.1.20-1.fc27 updates 4.2 M php-common x86_64 7.1.20-1.fc27 updates 1.0 M php-fpm x86_64 7.1.20-1.fc27 updates 1.5 M php-json x86_64 7.1.20-1.fc27 updates 73 k php-pdo x86_64 7.1.20-1.fc27 updates 138 k php-pgsql x86_64 7.1.20-1.fc27 updates 135 k Transaction Summary ================================================================================================ Install 1 Package Upgrade 7 Packages Total download size: 10 M Is this ok [y/N]: y |
After you type y and the return key, you should see a detailed log of the installation. Click the link below to see the yum installation log detail.
Display detailed console log →
Downloading Packages: (1/8): php-pdo-7.1.20-1.fc27.x86_64.rpm 214 kB/s | 138 kB 00:00 (2/8): php-mysqlnd-7.1.20-1.fc27.x86_64.rpm 325 kB/s | 246 kB 00:00 (3/8): php-json-7.1.20-1.fc27.x86_64.rpm 342 kB/s | 73 kB 00:00 (4/8): php-pgsql-7.1.20-1.fc27.x86_64.rpm 1.1 MB/s | 135 kB 00:00 (5/8): php-common-7.1.20-1.fc27.x86_64.rpm 1.0 MB/s | 1.0 MB 00:01 (6/8): php-7.1.20-1.fc27.x86_64.rpm 3.7 MB/s | 2.8 MB 00:00 (7/8): php-fpm-7.1.20-1.fc27.x86_64.rpm 1.6 MB/s | 1.5 MB 00:00 (8/8): php-cli-7.1.20-1.fc27.x86_64.rpm 3.7 MB/s | 4.2 MB 00:01 ------------------------------------------------------------------------------------------------ Total 4.2 MB/s | 10 MB 00:02 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Running scriptlet: php-json-7.1.20-1.fc27.x86_64 1/1 Upgrading : php-json-7.1.20-1.fc27.x86_64 1/15 Upgrading : php-common-7.1.20-1.fc27.x86_64 2/15 Upgrading : php-pdo-7.1.20-1.fc27.x86_64 3/15 Upgrading : php-cli-7.1.20-1.fc27.x86_64 4/15 Upgrading : php-7.1.20-1.fc27.x86_64 5/15 Installing : php-mysqlnd-7.1.20-1.fc27.x86_64 6/15 Upgrading : php-pgsql-7.1.20-1.fc27.x86_64 7/15 Upgrading : php-fpm-7.1.20-1.fc27.x86_64 8/15 Running scriptlet: php-fpm-7.1.20-1.fc27.x86_64 8/15 Cleanup : php-7.1.17-1.fc27.x86_64 9/15 Cleanup : php-cli-7.1.17-1.fc27.x86_64 10/15 Running scriptlet: php-fpm-7.1.17-1.fc27.x86_64 11/15 Cleanup : php-fpm-7.1.17-1.fc27.x86_64 11/15 Running scriptlet: php-fpm-7.1.17-1.fc27.x86_64 11/15 Cleanup : php-pgsql-7.1.17-1.fc27.x86_64 12/15 Cleanup : php-pdo-7.1.17-1.fc27.x86_64 13/15 Cleanup : php-common-7.1.17-1.fc27.x86_64 14/15 Cleanup : php-json-7.1.17-1.fc27.x86_64 15/15 Running scriptlet: php-json-7.1.17-1.fc27.x86_64 15/15 Running as unit: run-ra7f965317617476a93de3931549ab242.service Running as unit: run-r1272914e525d42798b0c3a76d4e2ba67.service Verifying : php-mysqlnd-7.1.20-1.fc27.x86_64 1/15 Verifying : php-pdo-7.1.20-1.fc27.x86_64 2/15 Verifying : php-common-7.1.20-1.fc27.x86_64 3/15 Verifying : php-json-7.1.20-1.fc27.x86_64 4/15 Verifying : php-pgsql-7.1.20-1.fc27.x86_64 5/15 Verifying : php-fpm-7.1.20-1.fc27.x86_64 6/15 Verifying : php-cli-7.1.20-1.fc27.x86_64 7/15 Verifying : php-7.1.20-1.fc27.x86_64 8/15 Verifying : php-cli-7.1.17-1.fc27.x86_64 9/15 Verifying : php-common-7.1.17-1.fc27.x86_64 10/15 Verifying : php-fpm-7.1.17-1.fc27.x86_64 11/15 Verifying : php-json-7.1.17-1.fc27.x86_64 12/15 Verifying : php-pdo-7.1.17-1.fc27.x86_64 13/15 Verifying : php-pgsql-7.1.17-1.fc27.x86_64 14/15 Verifying : php-7.1.17-1.fc27.x86_64 15/15 Installed: php-mysqlnd.x86_64 7.1.20-1.fc27 Upgraded: php.x86_64 7.1.20-1.fc27 php-cli.x86_64 7.1.20-1.fc27 php-common.x86_64 7.1.20-1.fc27 php-fpm.x86_64 7.1.20-1.fc27 php-json.x86_64 7.1.20-1.fc27 php-pdo.x86_64 7.1.20-1.fc27 php-pgsql.x86_64 7.1.20-1.fc27 Last metadata expiration check: 2:02:29 ago on Wed 22 Aug 2018 08:05:50 PM MDT. |
After you install the mysqli library, you exit the yum interactive shell with the quit command as shown:
> quit Leaving Shell The downloaded packages were saved in cache until the next successful transaction. You can remove cached packages by executing 'dnf clean packages'. |
You can now retest by re-running the mysqli_check.php program with the following URL:
http://localhost/mysqli_check.php |
Image processing is not generally installed by default. You should use the following yum command to install the PHP Image processing library:
yum install -y php-gd |
Or, you can use dnf (Dandified yum), like:
dnf install -y php-gd |
Click the link below to see the yum installation log detail.
Display detailed console log →
Dependencies resolved. ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: php-gd x86_64 7.1.20-1.fc27 updates 89 k Transaction Summary ================================================================================================ Install 1 Package Total download size: 89 k Installed size: 200 k Is this ok [y/N]: y Downloading Packages: php-gd-7.1.20-1.fc27.x86_64.rpm 96 kB/s | 89 kB 00:00 ------------------------------------------------------------------------------------------------ Total 55 kB/s | 89 kB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : php-gd-7.1.20-1.fc27.x86_64 1/1 Verifying : php-gd-7.1.20-1.fc27.x86_64 1/1 Installed: php-gd.x86_64 7.1.20-1.fc27 Complete! |
If you encounter an error trying to render an image like this:
Call to undefined function imagecreatefromstring() in ... |
The php-gd package is not enabled. You can verify the contents of the php-gd package with the following rpm command on Fedora or CentOS:
rpm -ql php-gd |
On PHP 7.1, it should return:
/etc/php-zts.d/20-gd.ini /etc/php.d/20-gd.ini /usr/lib/.build-id /usr/lib/.build-id/50 /usr/lib/.build-id/50/11f0ec947836c6b0d325084841c05255197131 /usr/lib/.build-id/b0/10bf6f48ca6c0710dcc5777c07059b2acece77 /usr/lib64/php-zts/modules/gd.so /usr/lib64/php/modules/gd.so |
Then, you might choose to follow some obsolete note from ten or more years ago to include gd.so in your /etc/php.ini file. That’s not necessary.
The most common reason for incurring this error is tied to migrating old PHP 5 code forward. Sometimes folks used logic like the following to print a Portable Network Graphics (png) file stored natively in a MySQL BLOB column:
header('Content-Type: image/x-png'); imagepng(imagecreatefromstring($image)); |
If it was stored as a Portable Network Graphics (png) file, all you needed was:
header('Content-Type: image/x-png'); print $image; |
As always, I hope this helps those looking for a solution.
MySQL on Fedora 27
While updating my class image to Fedora 27, I noticed that it installed the Akonadi Server. The documentation on the Akonadi server lacked some straightforward documentation. It also offered a bundled set of software that limited how to approach MySQL development.
So, I removed all those packages with the following syntax:
dnf remove `rpm -qa | grep akonadi` |
After removing those Akonadi packages, I installed the MySQL Community Edition from the Fedora repo with this syntax:
yum install -y community-mysql* |
Having installed MySQL Community Edition, I started the service with this command:
sudo service mysql start |
Then, I ran the mysql_secure_installation script to secure the installation:
mysql_secure_installation |
The script set the root user’s password, remove the anonymous user, disallow remote root login, and remove the test databases. Then, I verified connecting to the MySQL database with the following syntax:
mysql -uroot -ppassword |
I enabled the MySQL Service to start with each reboot of the Fedora instance. I used the following command:
systemctl enable mysqld.service |
It creates the following link:
ln -s '/etc/systemd/system/multi-user.target.wants/mysqld.service' '/usr/lib/systemd/system/mysqld.service' |
Now, I need to install and configure Apache, PHP, and upgrade Oracle Database 11g XE’s APEX 4 to 5.
PostgreSQL Identity Columns
It’s interesting to see the way different databases implement automatic numbering. Oracle Database 12c is the closest to PostgreSQL in some significant ways. However, its probably more accurate to say Oracle Database 12c copied PostgreSQL’s implementation. At least, that’s my conjecture because Oracle added a way to reset the START WITH value of the indirect sequence. However, I prefer the MySQL approach because the automatic numbering sequence is a property of the table and a simple clause of the CREATE TABLE statement.
Both PostgreSQL and Oracle Database 12c implement automatic numbering as indirect sequences. Indirect sequences are those created by a table when you designate a column as an identity column in Oracle or as a serial column in PostgreSQL. The difference is that PostgreSQL doesn’t provide a syntax version inside the CREATE TABLE semantic.
MySQL provides such syntax. You set an auto numbering column in MySQL by appending the AUTO_INCREMENT clause to the table creation statement when you want it to start with a number other than 1, like this:
CREATE TABLE auto ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , text_field VARCHAR(30) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8; |
Oracle disallows you to changing a sequence created as a background activity of the CREATE TABLE statement; and Oracle disallows you dropping an indirect sequence without changing the table that created it, which is exactly how they handle indexes created for unique constraints. Unfortunately, Oracle also disallows altering the START WITH value of any sequence.
If you want to change the START WITH value on an Oracle Database 12c indirect sequence, you must export the table, drop the table, and recreate the table with a new START WITH value before importing the data back into the table. The syntax for setting an IDENTITY column value higher than 1 is:
CREATE TABLE auto ( auto_id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1001) , text_field VARCHAR2(30) , CONSTRAINT auto_pk PRIMARY KEY (auto_id)); |
You can only create a PostgreSQL table with automatic numbering by using the SERIAL data type, which always sets the initial value to 1. You can reset the SERIAL sequence value in PostgreSQL with the ALTER statement. Unlike Oracle Database 12c, PostgreSQL does let you modify the START WITH value of any sequence. The trick is understanding how to find the sequence name. The name is always the combination of the table name, an underscore, an id string, an underscore, and a seq string. This behavior makes a great case for choosing id as the name of any auto numbering columns in a table.
CREATE TABLE auto ( id SERIAL CONSTRAINT auto_pk PRIMARY KEY , text_field VARCHAR(30)); ALTER SEQUENCE auto_id_seq RESTART WITH 1001; |
You can see the table and assigned sequence with the following command in PostgreSQL:
\d+ auto |
It should display:
Table "public.auto"
Column | Type | Modifiers | Storage | Stats target | Description
------------+-----------------------+---------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('auto_id_seq'::regclass) | plain | |
text_field | character varying(30) | | extended | |
Indexes:
"auto_pk" PRIMARY KEY, btree (id)
Has OIDs: no |
As always, I hope this helps those trying to sort through how to start identity columns above the initial value of 1.
Fedora LAMP Steps
I posted earlier in the year how to configure a Fedora instance to test PHP code on a local VM. However, I’ve got a few questions on how to find those posts. Here’s a consolidation with links on those steps:
- Go to this blog post and install the
httpdandphplibraries with theyuminstaller. - In the same blog post as step 1 (you can put the sample PHP code into the
/var/www/htmldirectory for testing), connect to theyumshell and remove thephp-mysqllibrary and then install themysqlndlibrary. - Go to this blog post and install the
php-gdlibraries, which enable you to render PNG images stored as binary streams in MySQL.
As always, I hope that helps.
Create MySQL Index
Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the INSERT, UPDATE, REPLACE INTO, and DELETE statements. Indexes are also called fast access paths.
In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve SQL statement performance. The downside is that they impose overhead on every INSERT, UPDATE, REPLACE INTO, and DELETE statement, because the database maintains them by inserting, updating, or deleting items for each related change in the tables that the indexes support.
Indexes have two key properties—usability and visibility. Indexes are both usable and visible by default. That means they are visible to the MySQL cost-based optimizer and usable when statements run against the tables they support.
You have the ability to make any index invisible, in which case queries and DML statements won’t use the index because they won’t see it. However, the cost-based optimizer still sees the index and maintains it with any DML statement change. That means making an index invisible isn’t quite like making the index unusable or like dropping it temporarily. An invisible index becomes overhead and thus is typically a short-term solution to run a resource-intensive statement that behaves better without the index while avoiding the cost of rebuilding it after the statement runs.
It is also possible to make an index unusable, in which case it stops collecting information and becomes obsolete and the database drops its index segment. You rebuild the index when you change it back to a usable index.
Indexes work on the principal of a key. A key is typically a set of columns or expressions on which you can build an index, but it’s possible that a key can be a single column. An index based on a set of columns is a composite, or concatenated, index.
Indexes can be unique or non-unique. You create a unique index anytime you constrain a column by assigning a primary key or unique constraint, but they’re indirect indexes. You create a direct unique index on a single column with the following syntax against two non-unique columns:
1 2 | CREATE INDEX common_lookup_u1 ON common_lookup (common_lookup_table) USING BTREE; |
You could convert this to a non-unique index on two columns by using this syntax:
1 2 | CREATE INDEX common_lookup_u1 ON common_lookup (common_lookup_table, common_lookup_column) USING BTREE; |
Making the index unique is straightforward;, you only need to add a UNIQUE key wordk to the CREATE INDEX statement, like
1 2 3 4 | CREATE UNIQUE INDEX common_lookup_u1 ON common_lookup ( common_lookup_table , common_lookup_column , common_lookup_type) USING BTREE; |
Most indexes use a B-tree (balanced tree). A B-tree is composed of three types of blocks—a root branch block for searching next-level blocks, branch blocks for searching other branch blocks, or and leaf blocks that store pointers to row values. B-trees are balanced because all leaf-blocks are at the same level, which means the length of search is the same to any element in the tree. All branch blocks store the minimum key prefix required to make branching decisions through the B-tree.
ORDER BY CASE
Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a CASE statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort.
I gave my students this ORDER BY clause as an example:
CASE WHEN filter = 'Debit' THEN 1 WHEN filter = 'Credit' THEN 2 WHEN filter = 'Total' THEN 3 END; |
It raises the following error in MySQL for students:
ERROR 1064 (42000): 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 'ORDER BY CASE WHEN filter = 'Debit' THEN 1 WHEN filter = 'Credit' THEN' at line 6 |
It raises the following error in Oracle for some students:
CASE * ERROR AT line 7: ORA-01785: ORDER BY item must be the NUMBER OF a SELECT-list expression |
So, I built a little test case to replicate the problem and error message they encountered:
SQL> SELECT 'Debit' AS filter FROM dual 2 UNION ALL 3 SELECT 'Credit' AS filter FROM dual 4 UNION ALL 5 SELECT 'Total' AS filter FROM dual 6 ORDER BY 7 CASE 8 WHEN filter = 'Debit' THEN 1 9 WHEN filter = 'Credit' THEN 2 10 WHEN filter = 'Total' THEN 3 11 END; |
They said, great but how can you fix it? That’s simple, with a Common Table Expression (CTE) in Oracle or with an inline view in MySQL. The Oracle CTE solution is:
1 WITH results AS 2 (SELECT 'Debit' AS filter FROM dual 3 UNION ALL 4 SELECT 'Credit' AS filter FROM dual 5 UNION ALL 6 SELECT 'Total' AS filter FROM dual) 7 SELECT filter 8 FROM results 9 ORDER BY 10 CASE 11 WHEN filter = 'Debit' THEN 1 12 WHEN filter = 'Credit' THEN 2 13 WHEN filter = 'Total' THEN 3 14 END; |
There are two MySQL solutions. One simply removes the FROM dual clauses from the query components and the other uses an inline view in the FROM clause. This is the inline view:
SELECT filter FROM (SELECT 'Debit' AS filter FROM dual UNION ALL SELECT 'Credit' AS filter FROM dual UNION ALL SELECT 'Total' AS filter FROM dual) resultset ORDER BY CASE WHEN filter = 'Debit' THEN 1 WHEN filter = 'Credit' THEN 2 WHEN filter = 'Total' THEN 3 END; |
This is the solution without the FROM dual clauses:
SELECT 'Debit' AS filter UNION ALL SELECT 'Credit' AS filter UNION ALL SELECT 'Total' AS filter ORDER BY CASE WHEN filter = 'Debit' THEN 1 WHEN filter = 'Credit' THEN 2 WHEN filter = 'Total' THEN 3 END; |
Both MySQL solutions yield the following:
+--------+ | filter | +--------+ | Debit | | Credit | | Total | +--------+ 3 rows in set (0.00 sec) |
It puts the fabricating query inside a result set, and then lets you use the column alias to filter the set. If you have a better approach, please share it.
Bash Arrays & MySQL
Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure ones.
It seems a quick refresher on how to use arrays in bash shell may be helpful. While it’s essential in a Linux environment, it’s seems not everyone masters the bash shell.
Especially, since I checked my Learning the Bash Shell (2nd Edition) and found a typo on how you handle arrays in the bash shell, and it’s a mistake that could hang newbies up (on page 161). Perhaps I should update my copy because I bought it in 1998. 😉 It was good then, and the new edition is probably better. The error is probably corrected in the current Learning the Bash Shell, but if not, the following examples show you how to use arrays in loops.
Naturally, these do presume some knowledge of working with bash shell, like the first line always is the same in any bash shell script. That you open an if-statement with an if and close it with a fi, and that you else-if is elif; and that a semicolon between a for-statement and the do statement is required when they’re on the same line because they’re two statements.
If you’re new to bash shell arrays, click on the link below to expand a brief tutorial. It takes you through three progressive examples of working with bash arrays.
Working with bash Arrays ↓
A basic example of working with an array in bash shell is the following list1.sh script:
1 2 3 4 5 6 7 8 9 10 11 12 13 | #!/usr/bin/bash # Print script name. echo $0 #!/usr/bin/bash # Define an array. declare -a cmd=("one" "two" "three") # Call the array elements. for i in ${cmd[*]}; do echo ${i} done |
Line 8 declares the cmd array by assigning three strings. Line 12 returns the elements of the array to the ${i} variable, which lets you manage them one at a time. You use the chmod command to make the list1.sh executable, like this:
chmod 755 list1.sh |
Then, you can run it like this from the present working directory (pwd):
./list1.sh |
It should print:
one two three |
The list2.sh example changes the cmd array declaration from list1.sh. It declares the cmd array as an empty array, and then it assigns elements by index numbers (using a zero-based index), as shown below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | #!/usr/bin/bash # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="one" cmd[1]="two" cmd[2]="three" # Call the array elements. for i in ${cmd[*]}; do echo ${i} done |
Lines 7 through 9 assign values to the elements of the cmd array. You would chmod the file, and run the file as qualified above for the list1.sh script.
The last pre-implementation example requires that you create three demonstration scripts, the one.sh, two.sh, and three.sh scripts. You should put them in the same directory as the list3.sh script.
The demonstration scripts should all have the same code, like this:
1 2 3 4 | #!/usr/bin/bash # Print script name. echo $0 |
Line 4 returns command line parameter $0 or ${0}, which is always the command line program’s file name. The file name may be provided as a relative or absolute file name, and if that’s new to you please check out The Linux Command Line: A Complete Introduction (also downloadable as a PDF for free).
The list3.sh script should contain the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | #!/usr/bin/bash <code> # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="one.sh" cmd[1]="two.sh" cmd[2]="three.sh" # Call the array elements. for i in ${cmd[*]}; do `pwd`/${i} done |
When you run the list3.sh script from the /home/student/Code/bash directory with a local syntax, the script should return the fully qualified file names of the subshell programs. The output should look like this:
/home/student/Code/bash/one.sh /home/student/Code/bash/two.sh /home/student/Code/bash/three.sh |
The list3.sh script provides the present working directory (pwd) and the one.sh, two.sh, and three.sh scripts return only their executable name. For example, if you ran one.sh with the following syntax:
./one.sh |
It returns
./one.sh |
Only one more trick needs to be qualified before our main MySQL examples. That trick is how you pass parameters to a bash shell script. For reference, this is the part that’s insecure because user command histories are available inside the Linux OS.
Here’s a hello_whom.sh script to demonstrates the concept of parameter passing:
1 2 3 4 5 6 7 8 9 10 | #!/usr/bin/bash # This says hello to the argument while managing no argument. if [[ ${#} = 1 ]]; then echo 'The '${0}' program says: "Hello '${1}'!"' elif [[ ${#} > 1 ]]; then echo 'The '${0}' program wants to know if you have more than one name?' else echo 'The '${0}' program wants to know if you have a name?' fi |
If you need more on how parameters are passed and managed, you can check a prior blob post on Handling bash Parameters, or check the bash help pages. The following leverages bash arrays to run scripts and query the MySQL database from the command line.
You will need the three batch SQL files first, so here they are:
Setup SQL Files ↓
The actor.sql file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop an actor table. DROP TABLE IF EXISTS actor; -- Create an actor table. CREATE TABLE actor ( actor_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , actor_name VARCHAR(30) NOT NULL ); -- Insert two rows. INSERT INTO actor (actor_name) VALUES ('Chris Hemsworth'); INSERT INTO actor (actor_name) VALUES ('Chris Pine'); INSERT INTO actor (actor_name) VALUES ('Chris Pratt'); |
The film.sql file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop a film table. DROP TABLE IF EXISTS film; -- Create a film table. CREATE TABLE film ( film_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , film_name VARCHAR(30) NOT NULL ); -- Insert rows. INSERT INTO film (film_name) VALUES ('Thor'); INSERT INTO film (film_name) VALUES ('Thor: The Dark World'); INSERT INTO film (film_name) VALUES ('Star Trek'); INSERT INTO film (film_name) VALUES ('Star Trek into Darkness'); INSERT INTO film (film_name) VALUES ('Guardians of the Galaxy'); |
The movie.sql file:
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 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop an movie table. DROP TABLE IF EXISTS movie; -- Create an movie table. CREATE TABLE movie ( movie_id int unsigned PRIMARY KEY AUTO_INCREMENT , actor_id int unsigned NOT NULL , film_id int unsigned NOT NULL , CONSTRAINT actor_fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) , CONSTRAINT film_fk FOREIGN KEY (film_id) REFERENCES film(film_id)); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Hemsworth') ,(SELECT film_id FROM film WHERE film_name = 'Thor')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Hemsworth') ,(SELECT film_id FROM film WHERE film_name = 'Thor: The Dark World')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pine') ,(SELECT film_id FROM film WHERE film_name = 'Star Trek')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pine') ,(SELECT film_id FROM film WHERE film_name = 'Star Trek into Darkness')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pratt') ,(SELECT film_id FROM film WHERE film_name = 'Guardians of the Galaxy')); |
The following list_mysql.sh shell script expects to receive the username, password, database and fully qualified path in that specific order. The script names are entered manually because this should be a unit test script. Naturally, you can extend the script to manage those parameters but as mentioned I see this type of solution as a developer machine only script to simplify unit testing. Anything beyond that is risky!
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 | #!/usr/bin/bash # Assign user and password username="${1}" password="${2}" database="${3}" directory="${4}" # List the parameter values passed. echo "Username: " ${username} echo "Password: " ${password} echo "Database: " ${database} echo "Directory: " ${directory} echo "" # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="actor.sql" cmd[1]="film.sql" cmd[2]="movie.sql" # Call the array elements. for i in ${cmd[*]}; do mysql -s -u${username} -p${password} -D${database} < ${directory}/${i} > /dev/null 2>/dev/null done # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p${password} -D${database} <<<'show tables' 2>/dev/null | # Read through the piped result until it's empty but format the title. while IFS='\n' read list; do if [[ ${list} = "Tables_in_sampledb" ]]; then echo $list echo "----------------------------------------" else echo $list fi done echo "" # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p${password} -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null | # Read through the piped result until it's empty but format the title. while IFS='\n' read actor_name; do if [[ ${actor_name} = "Actors in Films" ]]; then echo $actor_name echo "----------------------------------------" else echo $actor_name fi done |
The IFS (Internal Field Separator) works with whitespace by default. The IFS on lines 33 and 47 sets the IFS to a line return ('\n'). That’s the trick to display the data, and you can read more about the IFS in this question and answer post.
You can run this script with the following input parameters from the local directory where you deploy it. The a parameters are: (1) username, (2) password, (3) database, and (4) a fully qualified path to the SQL setup files.
./list_mysql.sh student student sampledb "/home/student/Code/bash/mysql" |
With valid input values, the list_mysql.sh bash script generates the following output, which confirms inputs and verifies actions taken by the scripts with queries:
Username: student Password: student Database: sampledb Directory: /home/student/Code/bash/mysql Tables_in_sampledb ---------------------------------------- actor film movie Actors in Films ---------------------------------------- Chris Hemsworth in Thor Chris Hemsworth in Thor: The Dark World Chris Pine in Star Trek Chris Pine in Star Trek into Darkness Chris Pine in Guardians of the Galaxy |
If you forgot to provide the required inputs to the list_mysql.sh bash script, it alternatively returns the following output:
Username: Password: Database: Directory: ./list_mysql.sh: line 25: /actor.sql: No such file or directory ./list_mysql.sh: line 25: /film.sql: No such file or directory ./list_mysql.sh: line 25: /movie.sql: No such file or directory |
The secure way removes the password at a minimum! The refactored program will require you to manually enter the password for all elements of the array (three in this sample), and twice for the two queries. Here’s the refactored code:
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 | #!/usr/bin/bash # Assign user and password username="${1}" database="${2}" directory="${3}" # List the parameter values passed. echo "Username: " ${username} echo "Database: " ${database} echo "Directory: " ${directory} echo "" # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="actor.sql" cmd[1]="film.sql" cmd[2]="movie.sql" # Call the array elements. for i in ${cmd[*]}; do mysql -s -u${username} -p -D${database} < ${directory}/${i} > /dev/null 2>/dev/null done # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p -D${database} <<<'show tables' 2>/dev/null | # Read through the piped result until it's empty. while IFS='\n' read list; do if [[ ${list} = "Tables_in_sampledb" ]]; then echo $list echo "----------------------------------------" else echo $list fi done echo "" # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null | # Read through the piped result until it's empty. while IFS='\n' read actor_name; do if [[ ${actor_name} = "Actors in Films" ]]; then echo $actor_name echo "----------------------------------------" else echo $actor_name fi done |
Please let me know if you think there should be any more scaffolding for newbies in this post. As always, I hope this helps those looking for this type of solution.
MySQL OCP Exams
Planning out my year, I decided to take the Oracle OCP and MySQL OCP exams. I checked for review books and was pleasantly surprised to find the soon to be released OCP MySQL Database Administrator Exam Guide (Exam 1Z0-883). However, I noticed that the book was actually prepared for the obsolete and discountinued Exams 1Z0-870, 1Z0-873, and 1Z0-874. As it turns out, Steve O’Hearn has informed me that there isn’t a book and that the posting in Amazon.com is in error.
There isn’t an alternative review book for the OCP MySQL 5.6 Developer or Database Administrator Exams. The question that I have is simple: “How relevant is this book because it was prepared for the older exams?” There isn’t a table of content published on the Amazon.com site. If there was a table of contents it could help me determine how close the book’s content is to the new exam.
As a preparation to figure out the value of the book as a study guide, I’ve reviewed the current Oracle MySQL Training Objectives (listed below). The new MySQL OCP Developer and Administrator exams have the following descriptions and objectives:
- MySQL 5.6 Developer 1Z0-882. Oracle provides the following outline for their MySQL for Developer (Ed 3) training course:
Course Objectives
- Describe the MySQL client/server architecture
- Use MySQL client programs and common options
- Program MySQL applications with Java and PHP connectors
- Use a “NoSQL” approach to store and retrieve data
- Design efficient tables
- Create and delete database objects
- Use expressions in SQL statements
- Examine database metadata
- Use SQL statements to modify table data
- Maintain database integrity with transactions
- Write multiple table queries
- Create “virtual tables” containing specific data
- Create user-defined variables, prepared statements, and stored routines
- Create and manage triggers
- Identify and deal with errors and exceptions in client programs
- Write queries that are fast and effective, even under heavy loads
- MySQL 5.6 Database Administrator 1Z0-883. Oracle provides the following outline for their MySQL for Database Administrators (Ed 3.1) training course:
Course Objectives
- Describe the MySQL Architecture
- Install and Upgrade MySQL
- Use the INFORMATION_SCHEMA database to access metadata
- Perform the MySQL start and shutdown operations
- Configure MySQL server options at runtime
- Use available tools including MySQL Workbench
- Evaluate data types and character sets for performance issues
- Understand data locking in MySQL
- Understand the use of the InnoDB storage engine with MySQL
- Maintain integrity of a MySQL installation
- Use triggers for administration tasks
- Use Enterprise Audit and Pluggable Authentication
- Configure advanced replication topologies for high availability
- Describe introductory performance tuning techniques
- Perform backup and restore operations
- Automate administrative tasks with scheduled events
As always, I hope this helps those who read it; and, in this case I hope it helps you make an effective decision on preparation resources for the MySQL 5.6 OCP exams.
Subscribe via RSS