Introduction While doing my High-Performance Java Persistence training, I came to realize that it’s worth explaining how a relational database works, as otherwise, it is very difficult to grasp many transaction-related concepts like atomicity, durability, and checkpoints. In this post, I’m going to give a high-level explanation of how a relational database works internally while … Continue reading How does a relational database work →

Whenever we want a query’s results sorted, you may think of using the clause “order by.” You are correct: most of the time, MySQL will return the results in expected order with “order by.”

Be careful, though. Sometimes MySQL may return results in the non-deterministic order even with “order by.”

Indeed, if a query is ordered by a non-unique column, it may return results in an unexpected order. I tested the below example on MySQL 5.1.73, 5.5.54 and 5.6.19 and got the same result. However, when I applied the same example on MySQL 5.7.17, it returned the results in an unexpected order differently.

Follow me step-by-step and see how MySQL returns results in a non-deterministic order. Step 1-4 is for MySQL 5.1.73, 5.5.54 and 5.6.19, Step 5 is for MySQL 5.7.17. After the example, I will explain the reason behind this output.

Step 1. Create the table as …

The PAM authentication plug-in is an extension included in MySQL Enterprise Edition (since 5.5) and in MariaDB (since 5.2).

• Pluggable authentication module
• Understanding PAM

MySQL authentication against pam_unix

• The PAM Authentication Plugin

Check if plug-in is available:

# ll lib/plugin/auth*so
-rwxr-xr-x 1 mysql mysql 42937 Sep 18  2015 lib/plugin/authentication_pam.so
-rwxr-xr-x 1 mysql mysql 25643 Sep 18  2015 lib/plugin/auth.so
-rwxr-xr-x 1 mysql mysql 12388 Sep 18  2015 lib/plugin/auth_socket.so
-rwxr-xr-x 1 mysql mysql 25112 …

Moving your data into a public cloud service is a big decision. All the major cloud vendors offer cloud database services, with Amazon RDS for MySQL being probably the most popular.

In this blog, we’ll have a close look at what it is, how it works, and compare its pros and cons.

RDS (Relational Database Service) is an Amazon Web Services offering. In short, it is a Database as a Service, where Amazon deploys and operates your database. It takes care of tasks like backup and patching the database software, as well as high availability. A few databases are supported by RDS, we are here mainly interested in MySQL though - Amazon supports MySQL and MariaDB. There is also Aurora, which is Amazon’s clone of MySQL, improved, especially in area of replication and high availability.

Deploying MySQL via RDS

Let’s take a look at the deployment of MySQL via RDS. We picked MySQL and then we are presented with couple of …

Description

In this part of the webinar series on best practices for Galera Cluster, we will discuss schema changes and DDL.

We will show how Galera Cluster executes DDLs in a safe, consistent manner across all the nodes in the cluster, and the differences with stand-alone MySQL. We will discuss how to prepare for and successfully carry out a schema upgrade and the considerations that need to be taken into account during the process.

Time: 9-10 AM PST (Pacific time zone), 21st of  February

Speakers: Philip Stoev, Quality and Release Manager, Codership                Sakari Keskitalo, COO, Codership

REGISTER TO THE USA TIMEZONE WEBINAR

As intuitive and streamlined as ecommerce technology might seem from the user's perspective, it involves so much data that engineering ingenuity and smart database management must constantly deliver in order to keep up. At organizations like Shopify—responsible for the easy and reliable transactions at top brands around the world—that excellence of performance involves deep monitoring of their MySQL core and their Redis caching infrastructure, plus insightful query profiling, packet captures, and the admittance of developers to platforms that measure database performance.

Shopify’s motto is “Make commerce better for everyone.” That mantra applies whether the shopping's done online, on mobile, or in-store. For Shopify's engineering team, better means a fast, reliable application that delivers a positive …

Updated 2/10/2017

If you've been watching the evolution of database technologies over the past few years, you've seen how quickly JSON has quickly cemented its position in major database servers. Due to its use in the web front-end, JSON has overtaken XML in APIs, and it’s spread through all the layers in the stack one step at a time.

Most major databases supported XML in some fashion for a while, too, but developer uptake wasn’t universal. JSON adoption among developers is nearly universal today, however. (The king is dead, long live the king!) But how good is JSON support in the databases we know and love? We’ll do a comparison in this blog post.

…

Okay, so you’ve read the first post on enabling MariaDB’s data at rest encryption, and now you are ready to create an encrypted table.

And just to get it out of the way for those interested, you can always check your encrypted (and non-encrypted) table stats via:

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION;

ENCRYPTION_SCHEME=1 means the table is encrypted and ENCRYPTION_SCHEME=0 means they are not.

But let’s get into some specific examples.

I find the following 4 tables interesting, as the first 3 essentially all create the same table, and the 4th shows how to create a non-encrypted table once you have encryption enabled.

CREATE TABLE t10 (id int) ENGINE=INNODB;
CREATE TABLE t11 (id int) ENGINE=INNODB ENCRYPTED=YES;
CREATE TABLE t12 (id int) ENGINE=INNODB …

Encryption is becoming more and more prevalent and increasingly necessary in today’s world, so I wanted to provide a good overall “getting started” article on using MariaDB’s data at rest encryption (DARE) for anyone out there interested in setting this up in their environment.

MariaDB’s data encryption at rest manual page covers a lot of the specifics, but I wanted to create a quick start guide and also note a few items that might not be immediately obvious.

And due to the number of my examples, I’m splitting this into two posts. The first will focus solely on setting up encryption so you can use it. The second will focus on using it with a number of examples and common use cases.

Also, I feel that I should mention from the outset that, currently, this data at rest encryption only applies to InnoDB/XtraDB tables and Aria …

In my post yesterday, I shared a little known trick for sorting NULLs last when using ORDER BY ASC.

To summarize briefly, NULLs are treated as less than 0 when used in ORDER BY, However, sometimes you do not want that behavior, and you need the NULLs listed last, even though you want your numbers in ascending order.

So a query like the following returns the NULLs first (expected behavior):

SELECT * FROM t1 ORDER BY col1 ASC, col2 ASC;
+--------+------+
| col1   | col2 |
+--------+------+
| apple  | NULL |
| apple  |    5 |
| apple  |   10 |
| banana | NULL |
| banana |    5 |
| banana |   10 |
+--------+------+

The trick I mentioned in my post is to rewrite the query like:

SELECT * FROM t1 ORDER BY col1 ASC, -col2 DESC;

The difference is that we added a minus sign (-) in front of the column …