Das neueste Dabiq-Magazin soll für den Übertritt zum Islam als Religion werben, aber die Attraktion basiert vermutlich auf anderen Bedürfnissen

Erdogan baut sein Machtsystem weiter aus - mit unabsehbaren Folgen

Der EU-Kommissionschef bestreitet, Alkoholiker zu sein

Bei manchen entsteht der Verdacht, US-Geheimdienste könnten ihre Finger im Spiel haben, ganz abseitig wäre dies nicht

Eine Sonntasgzeitung meldet Rekordnachfrage nach dem "kleinen Waffenschein"

Wie eine Merkel-Phrase Geschichte macht

Die britische Fischerei und der Brexit

Der türkische Präsident will dem Parlament ein "kleines Paket" mit Verfassungsänderungen vorschlagen

In Japan wird mal wieder massiv Geld in eine lahmende Wirtschaft gepumpt. Mit unerfreulichen Nebenwirkungen ist zu rechnen

Bundesverfassungsgericht lehnt Erdoğan-Live-Schaltung bei Demo in Köln wegen Formfehler ab

Ist Boris Palmer ein Rassist?

Im Kampf gegen den IS bezahlt die Bevölkerung Rojavas einen hohen Blutzoll

Statt Horrorszenarien, vor denen gewarnt wurde, ist Business as usual angesagt, die Verhandlungen mit dem "engen Partner der EU" laufen längst. Fürchten muss der aber den Austritt von Schottland

Spannungen im Südchinesischen Meer halten an. ASEAN ohne gemeinsame Stellungnahme zum Konflikt zwischen ihren Mitgliedern und China

Außenminister Steinmeier warnt davor, innenpolitische Spannungen aus der Türkei nach Deutschland zu tragen. Die Polizei spricht von "stark emotionalisierten Teilnehmern"

Unsere Meere sind angereichert mit Radioaktivität. Sie stammt unter anderem vom Reaktorunfall aus Fukushima, aber auch aus früheren Tests mit Atomwaffen und verklappten Fässern mit Atommüll

Polen und Litauer nach dem Brexit: Abschiebungen und Hate-Crimes nehmen zu

Wie die Staatsapparate aus einer Existenzkrise, in die sie durch die Selbstenttarnung des NSU geraten waren, gestärkt daraus hervorgegangen sind

In this blog post, we’ll look at the MariaDB 10.2 CHECK and DEFAULT clauses.

MariaDB 10.2 includes some long-awaited features. In this blog, we are going to discuss the improvements to some table definitions: the DEFAULT clause and the CHECK constraints. These clauses describe columns default values and rules for data validation.

Note that MariaDB 10.2 is still in alpha stage. This article describes the current state of these features, which could change before MariaDB 10.2 becomes GA.

The DEFAULT clause

The DEFAULT clause has always been supported in MariaDB/MySQL, but traditionally it only accepted literal values (like “hello world” or “2”). MariaDB 10.2 removes this limitation, so DEFAULT can now accept most SQL expressions. For example:

• fiscal_year SMALLINT DEFAULT (YEAR(NOW()))
• valid_until DATE DEFAULT (NOW() + INTERVAL 1 YEAR)
• owner VARCHAR(100) DEFAULT (USER())

Additionally, MariaDB 10.2 allows you to set a DEFAULT value for the TEXT and BLOB columns. This was not possible in previous versions. While this might look like a small detail, it can be hard to add a column to an existing table that is used by production applications, if it cannot have a default value.

The DEFAULT clause has some very reasonable limitations. For example, it cannot contain a subquery or a stored function. An apparently strange limitation is that we can mention another column in DEFAULT only if it comes first in the CREATE TABLE command.

Note that DEFAULT can make use of non-deterministic functions even if the binary log uses the STATEMENT format. In this case, default non-deterministic values will be logged in the ROW format.

CHECK constraints

CHECK constraints are SQL expressions that are checked when a row is inserted or updated. If this expression result is false (0, empty string, empty date) or NULL, the statement will fail with an error. The error message states which CHECK failed in a way that is quite easy to parse:

ERROR 4022 (23000): CONSTRAINT `consistent_dates` failed for `test`.`author`

Some example of CHECK constraints:

• CONSTRAINT non_empty_name CHECK (CHAR_LENGTH(name) > 0)
• CONSTRAINT consistent_dates CHECK (birth_date IS NULL OR death_date IS NULL OR birth_date < death_date)
• CONSTRAINT past_date CHECK (birth_date < NOW())

A possible trick is checking that a column is different from its default value. This forces users to assign values explicitly.

CHECK constraints cannot be added or altered. It is only possible to drop them. This is an important limitation for production servers.

Another limitation is that CHECK metadata are not accessible via the INFORMATION_SCHEMA. The only way to find out if a table has CHECK clauses is parsing the output of SHOW CREATE TABLE.

The exact behavior of CHECK constraints in a replication environment depends on the master binary log format. If it is STATEMENT, the slaves will apply CHECK constraints to events received from the master. If it is ROW, only the master will need to apply constraints, because failed statements will not be replicated.

Thus, in all cases, we recommend having identical constraints on master and slaves, and only using deterministic constraints.

Performance

While I didn’t run a professional benchmark, I can say that both DEFAULT and CHECK clauses don’t have a noticeable impact on a simple test where we insert one million rows (on my local machine).

However, these clauses evaluate an SQL expression each time a row is inserted or updated. The overhead is at least equal to the SQL expression performance. If high-performing writes are important, you will probably not want to use complex data validation.

To check how fast an expression is, we can use the BENCHMARK() function:

MariaDB [(none)]> SELECT BENCHMARK(10000000, (555 / 100 * 20)); +---------------------------------------+ | BENCHMARK(10000000, (555 / 100 * 20)) | +---------------------------------------+ | 0 | +---------------------------------------+ 1 row in set (1.36 sec) MariaDB [(none)]> SELECT BENCHMARK(100000000, MD5('hello world')); +------------------------------------------+ | BENCHMARK(100000000, MD5('hello world')) | +------------------------------------------+ | 0 | +------------------------------------------+ 1 row in set (14.84 sec)

In this example, we executed the specified expressions ten million times. BENCHMARK() always returns 0, but what we want to check is the execution time. We can see for example that evaluating MD5(‘hello world’) takes less than 0.000002 seconds. In some cases, we may want to retry the same expressions with different parameters (longer strings, higher numbers, etc.) to check if the execution times varies.

Unfortunately, we don’t have a status variable which tells us how many times MariaDB evaluated CHECK clauses. If our workload performs many writes, that variable could help us to find out if CHECK constraints are slowing down inserts. Maybe the MariaDB team can take this as a suggestion for the future.

Mohammed Daleel in Sofia