InnoDB holepunch experiments

After excellent blogs by Mark Callaghan (see links below), I decided to use some of my time to experiment how different filesystems behave if the holepunch feature is used in MariaDB 10.1. First of all, MariaDB 10.1 does not use holepunch by default even if a table is page compressed (a term used in MariaDB). The holepunch feature in MariaDB is enabled with the innodb-use-trim=1 configuration variable and naturally requires support of the fallocate system call with the FALLOC_FL_PUNCH_HOLE and FALLOC_FL_KEEP_SIZE parameters. Support for these is checked during the cmake build phase.

I used CentOS Linux release 7.1.1503 (Core) using the 3.10.0-229.el7.x86_64 Linux kernel and few SSD drives in a RAID-0 setup (Intel X25-E Extreme SSDSA2SH032 G1GN 2.5-inch 32GB SATA II SLC Internal Solid State Drive (SSD)). In the system I used ext4, btrfs (v3.16.2), and xfs as file systems. Note that NVMFS, where this feature was designed, can’t be currently used on normal SSDs as a file system.

I used LinkBench with 2.5x database size resulting is 26G of uncompressed tables and 16G of holepunch tables using zip compression.

Filesystem Time to drop uncompressed db Time to drop holepunch compressed db
ext4 6.43sec 6.53sec
btrfs 1.74sec 13.82sec
xfs 6.66sec 1 min 1.73sec

Clearly, dropping holepunch compressed tables in xfs takes magnitudes longer than uncompressed meaning that the holepunch feature is not useful on xfs if the workload requires dropping big tables. In ext4 there is basically no difference and in btrfs the difference is 10x. However, the problem could be that the database is too small, thus I also tested LinkBench with a 5x database size.

Filesystem Time to drop uncompressed db Time to drop holepunch compressed db
ext4 7.75sec 26.55sec
btrfs 11.69sec 33.18sec

Similar test was done using a normal HD and 20x LinkBench using only ext4. Firstly, loading holepunch compressed tables took significantly longer than an uncompressed database. Similarly, dropping the database was significantly different:

Filesystem Time to drop uncompressed db Time to drop holepunch compressed db
ext4 40.44sec 5 minutes 55.65sec

Finally, similar test was done using ioMemory SX300-1600 with VSL driver 4.2.1 build 1137 and NVMFS 1.1.1 using 20X LinkBench database. In this setting no significant difference was found.

Filesystem Time to drop uncompressed db Time to drop holepunch compressed db
nvmfs 3.30sec 3.65sec


Based on my and other experiments its clear that many of these filesystem have a large overhead dealing with large sparse files. This conclusion leads to need for alternative design for holepunch i.e. where one could use this the feature without punch hole operation resulting in a slightly denser file than otherwise would be possible based on the true Page Compression architecture.



A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The purpose of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table. Consider following simple example:

As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys:

MariaDB foreign key syntax is documented at (and MySQL at While most of the syntax is parsed and checked when the CREATE TABLE or ALTER TABLE clause is parsed, there are still several error cases that can happen inside InnoDB. Yes, InnoDB has its own internal foreign key constraint parser (in dict0dict.c function dict_create_foreign_constraints_low()).

However, the error messages shown in CREATE or ALTER TABLE, and SHOW WARNINGS in versions of MariaDB prior to 5.5.45 and 10.0.21 are not very informative or clear. There are additional error messages if you issue SHOW ENGINE INNODB STATUS, which help, but were not an ideal solution. In this blog I’ll present a few of the most frequent error cases using MariaDB 5.5.44 and how these error messages are improved in MariaDB 5.5.45 and 10.0.21. I will use the default InnoDB (i.e. XtraDB) but innodb_plugin works very similarly.

Constraint name not unique

Foreign name constraint names must be unique in a database. However, the error message is unclear and leaves a lot unclear:

These messages are not very helpful because there are two foreign key constraints. Looking into SHOW ENGINE INNODB STATUS we get a better message:

In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

No index

There should be an index for columns in a referenced table that contains referenced columns as the first columns.

Fine but again we have no idea which foreign key it was. As before, there is a better message in the SHOW ENGINE INNODB STATUS output:

In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

Referenced table not found

A table that is referenced on foreign key constraint should exist in InnoDB data dictionary. If not:

Both messages are first referring to an internal table name and the foreign key error message is referring to an incorrect name. In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

Temporary tables

Temporary tables can’t have foreign key constraints because temporary tables are not stored to the InnoDB data dictionary.

These error messages do not really help the user, because the actual reason for the error is not printed and the foreign key error references an internal table name. In MariaDB 5.5.45 and 10.0.21 this is clearly improved:

Column count does not match

There should be exactly the same number of columns in both the foreign key column list and the referenced column list. However, this currently raises the following error:

The error message is not clear and the foreign key error refers to an internal table name. In MariaDB 5.5.45 and 10.0.21 there is additional information:

Incorrect cascading

A user may define a foreign key constraint with ON UPDATE SET NULL or ON DELETE SET NULL. However, this requires that the referenced columns are not defined as NOT NULL. Currently, the error message on this situation is:

Both error messages are not very useful, because the first does not really tell how the foreign key constraint is incorrectly formed and later does not say which column has the problem. This is improved in MariaDB 5.5.45 and 10.0.21:

Incorrect types

Column types for foreign key columns and referenced columns should match and use the same character set. If they do not, you currently get:

But do we have an index for the referenced column f1 in the table t2? So if there are multiple columns in both the foreign key column list and the referenced column list, where do we look for the error? In MariaDB 5.5.45 and 10.0.21 this is improved by:


There are several different ways to incorrectly define a foreign key constraint. In many cases when using earlier versions of MariaDB (and MySQL), the error messages produced by these cases were not very clear or helpful. In MariaDB 5.5.45 and 10.0.21 there are clearly improved error messages to help out the user. Naturally, there is always room for further improvements, so feedback is more than welcome!




Fortran (FORmula TRANslating System) is a general-purpose, imperative programming language that is especially suited to numeric computation and scientific computing. History of FORTRAN can be tracked late 1953 when John W. Backus submitted a proposal to his superiors at IBM. The First FORTRAN compiler appeared in April 1957.

Some notable historical steps where:

  • FORTRAN II in 1958
  • FORTRAN III in 1958,
  • FORTRAN IV in 1962.
  • FORTRAN 66 or X3.9-1966 become the first industry-standard
  • FORTRAN 77 or X3.9-1978. This is the version of the Fortran I learned 1996.
  • Fortran 90 was released as ISO/IEC standard 1539:1991 and ANSI Standard in 1992
  • Fortran 95 was released as ISO/IEC standard 1539-1:1997
  • Fortan 2003 was released as ISO/IEC 1539-1:2004
  • Fortran 2008 was released as ISO/IEC 1539-1:2010 is most recent standard
  • Fortran 2015 is planned in late 2016.

More comprehensive history and introduction can be found e.g. from

Thus Fortran programming language is not dead ! I did use Fortran in same day as I started writing this blog (05/07/2015). There is some historical reason why I decided to learn Fortran. In department of computer science, university of Helsinki there is course named Software Project where students design, implement and test larger application. I participated on this course 1996 and my application was Ringing Software for Ringing Centre, Natural History Museum, University of Helsinki. Their original software used magnetic tapes and Fortran66/77 programs. Our assignment was to transform this to use Oracle database and UNIX. At that time we decided to use Fortran77 (with some Fortran90 extensions, mainly structures) and ProFortran precompiler from Oracle.


There is version of GNU Fortran named GFortran. The GFortran compiler is fully compliant with the Fortran 95 Standard and includes legacy F77 support. In addition, a significant number of Fortran 2003 and Fortran 2008 features are implemented.

To my experience GFortran is very good compiler and really includes most of the legacy support you need (and lot of new stuff I really do not need). However, I found one example that is useful but not supported, variable length format. Consider following:

Here format (2x, I4) is repeated times and depends runtime values. This can be transformed to:

This is because format can be a string variable and above produces format (A,44(2X,I4),A) (assuming years 1971 and 2014). But, in my opinion the first one is more clearer and simpler. Additionally, I learned to use pre-Fortran90 STRUCTURE and RECORD extensions, like

This can naturally expressed using TYPE:

I mostly use Fortran90 and free-form (longer line lengths than  allowed by standard Fortran77) but only limited amount of new features. Thus code might look like Fortran77 mostly:

Naturally, there is number of commercial Fortran compilers like Intel Fortran and NAG .

Clearly one of the bad features of Fortran are implicit types. If a variable is undeclared, Fortran 77 uses a set of implicit rules to establish the type. This means all variables starting with the letters i-n are integers and all others are real. Many old Fortran 77 programs uses these implicit rules, but you should not! The probability of errors in your program grows dramatically if you do not consistently declare your variables. Therefore, always put following in start of your Fortran program:

SQL and Fortran

Fortran does not natively understand SQL-clauses, but you can use e.g. embedded SQL. Embedded SQL is SQL-clauses inside a host language like Fortran. Lets take a example:

Naturally, normal Fortran compiler will not understand clauses starting with EXEC SQL. Thus, you need to first use precompiler. Precompiler changes embedded SQL-clauses (above include clauses are copied to resulting file) and other SQL-clauses are transformed to CALL-clauses to provided database server API-calls. Naturally, this means that you software will work only for precompiled (and then compiled) database provider.

Currently, there are precompilers at least for Oracle and DB2 databases (see However, OS support is diminishing. E.g. Oracle Fortran Precompiler does not anymore work on Linux 64bit when using Oracle >10g. This in my opinion is bad because porting your Fortran software from Oracle to e.g. DB2 is not trivial especially if you have application with 100000 lines of Fortran code.

This fact has lead on my experience to situation where some of the system is re-implemented using Java and some of the code modified to pure Fortran so that it read input from files (generated using pure SQL) and by removing all embedded SQL-clauses.

Fortran and MariaDB

There is no connectors for Fortran to MariaDB /MySQL. However, you could use ODBC, however the free ODBC modules FLIBS and fodbc fail to compile in my 64-bit Linux and after some hacking with fodbc, it did not really work. Naturally, you could write your own fodbc for MariaDB/MySQL but currently I do not have a real need or enough free time to do so. Alternative way of doing this is create C-language interface between Fortran code and ODBC driver.

Lets take very simple example where Fortran program connects to MariaDB database, selects a version and disconnects.

As you note string variables need special handling as Fortran has constant strings. Therefore, we need to add C string end character before calling C-routines and then remove trailer before using string in Fortran again. And then simple C-interface (no real error handling):

And, if you compile these and run the resulting program you might see something like following:

Future of Fortran ?

There is clearly need languages like Fortran. It has some very nice features like formatted I/O and mathematical functions. However, learning Fortran might be up to you because it is not taught as first (or second) programming language on most universities or similar schools. Thus number of people who can use Fortran on programming or teach it is decreasing rapidly. However, my experience is that learning Fortran is simple if you can master at least one programming language (ok, I had learn already C/C++, COBOL, PL/I, Basic on my earlier studies). So you want to learn Fortran ? If Internet resources are not enough there is number of books. Book I have used is obsolete (Fortran 77 and Finish-language version Fortran 90/95) but e.g. is a good one.