an unsystematic journey through the source-code of MySQL and MariaDB: 2013

Monday, 2 December 2013

update: working with the source-code

In my post working with the source code I gave some links to the files I used in the past and will use for my examples here. Unfortunately I found that one of the links is dead now. So as a correction of my post: you can find the source-code of the version of MySQL used in this blog here: http://downloads.mysql.com/archives/community/

Select this item:

When you scroll down you'll find this entry:

This is the file I'm working with.

Please excuse the mistake.

Thursday, 28 November 2013

how to add a storage-engine: DUMMY

so let's write some code of our own, let's start with a simple storage-engine. This engine is silly, it is the equivalent of the device /dev/null. It simply does nothing but it contains the minimal amout of code needed for the implementation of such an engine.

All examples in this post are tested with MySQL, the steps are similar for MariaDB (the names can change a bit).

all the files

So let's start: I prepared everything so simply download this file.

Extract this file to the storage-folder of the MySQL-source (or the same folder of the MariaDB-source).

After extracting the data I entered this line on a console:
MySQL

august@AMD4:~/workspace/MySQL-Src/mysql-5.5.8$ cmake  -DWITH_DEBUG=1  -DMYSQL_DATADIR=/home/august/MySQL/data  -DCMAKE_INSTALL_PREFIX=/home/august/MySQL/pgm

MariaDB

august@AMD4:~/workspace/MariaDB/mariadb-10.0.4$ cmake  -DCMAKE_BUILD_TYPE=Debug  -DMYSQL_DATADIR=/home/august/MariaDB/data  -DCMAKE_INSTALL_PREFIX=/home/august/MariaDB/pgm

a hint: these lines are valid for my PC. Please look for the configuration of your PC and set the parameters accordingly.

Now I switched back to Eclipse, recompiled the source and this storage engine is included in the server-code:

in the workspace: $HOME/workspace/MySQL-Src/mysql-5.5.8/storage/dummy/ha_dummy.so
in the installed-version: $HOME/MySQL/pgm/lib/plugin/ha_dummy.so

and that's all folks.

so let's see if it works

You can find all the output presented here in: storage/dummy/Skripte/Tests.sql
you can read it there or continue reading here.

In Eclipse one can start the server via a click on the Run-icon. In the console of Eclipse the output should end with these lines:

131125 15:33:04 [Note] /home/august/workspace/MySQL-Src/mysql-5.5.8/sql/mysqld: ready for connections.
Version: '5.5.8-debug'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Source distribution

Opening a console and let's see:

august@AMD4:~/MySQL/pgm$ bin/mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.8-debug Source distribution



Copyright (c) 2000, 2010, 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 '\h' for help. Type '\c' to clear the current input statement.

mysql>

First of all we look for the installed plugins:

mysql> show plugins;
+-----------------------+--------+--------------------+----------------+---------+
| Name                  | Status | Type               | Library        | License |
+-----------------------+--------+--------------------+----------------+---------+
| binlog                | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| mysql_native_password | ACTIVE | AUTHENTICATION     | NULL           | GPL     |
| mysql_old_password    | ACTIVE | AUTHENTICATION     | NULL           | GPL     |
| MEMORY                | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| MRG_MYISAM            | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| CSV                   | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| MyISAM                | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| PERFORMANCE_SCHEMA    | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| InnoDB                | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| INNODB_TRX            | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_LOCKS          | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_LOCK_WAITS     | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMP            | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMP_RESET      | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMPMEM         | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMPMEM_RESET   | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| partition             | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| FCWTEXT               | ACTIVE | STORAGE ENGINE     | ha_fcwtext.so  | GPL     |
| DBF                   | ACTIVE | STORAGE ENGINE     | ha_dbf.so      | GPL     |
| MYC                   | ACTIVE | STORAGE ENGINE     | ha_myc.so      | GPL     |
| MYC2                  | ACTIVE | STORAGE ENGINE     | ha_myc2.so     | GPL     |
| maxtempl              | ACTIVE | STORAGE ENGINE     | ha_maxtempl.so | GPL     |
+-----------------------+--------+--------------------+----------------+---------+
22 rows in set (0,00 sec)

mysql>

then we add our little dummy-engine to the server and check:

mysql> install plugin dummy soname 'ha_dummy.so';
Query OK, 0 rows affected (0,09 sec)

mysql> show plugins;
+-----------------------+--------+--------------------+----------------+---------+
| Name                  | Status | Type               | Library        | License |
+-----------------------+--------+--------------------+----------------+---------+
| binlog                | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| mysql_native_password | ACTIVE | AUTHENTICATION     | NULL           | GPL     |
| mysql_old_password    | ACTIVE | AUTHENTICATION     | NULL           | GPL     |
| MEMORY                | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| MRG_MYISAM            | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| CSV                   | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| MyISAM                | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| PERFORMANCE_SCHEMA    | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| InnoDB                | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| INNODB_TRX            | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_LOCKS          | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_LOCK_WAITS     | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMP            | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMP_RESET      | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMPMEM         | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| INNODB_CMPMEM_RESET   | ACTIVE | INFORMATION SCHEMA | NULL           | GPL     |
| partition             | ACTIVE | STORAGE ENGINE     | NULL           | GPL     |
| FCWTEXT               | ACTIVE | STORAGE ENGINE     | ha_fcwtext.so  | GPL     |
| DBF                   | ACTIVE | STORAGE ENGINE     | ha_dbf.so      | GPL     |
| MYC                   | ACTIVE | STORAGE ENGINE     | ha_myc.so      | GPL     |
| MYC2                  | ACTIVE | STORAGE ENGINE     | ha_myc2.so     | GPL     |
| maxtempl              | ACTIVE | STORAGE ENGINE     | ha_maxtempl.so | GPL     |
| DUMMY                 | ACTIVE | STORAGE ENGINE     | ha_dummy.so    | GPL     |
+-----------------------+--------+--------------------+----------------+---------+
23 rows in set (0,00 sec)

mysql>

OK, it's there (scroll to the end of the list and you will see it - I changed the output to bold).

So let's use our silly engine:

mysql> create database Test;
Query OK, 1 row affected (0,01 sec)

mysql> use Test;
Database changed

mysql> create table DummyTest (
    ->    col1 char(10),
    ->    col2 decimal(5,2),
    ->    col3 varchar(32)
    ->    ) engine=DUMMY
    -> ;
Query OK, 0 rows affected (0,04 sec)

mysql> show tables;
+----------------+
| Tables_in_Test |
+----------------+
| DummyTest      |
+----------------+
1 row in set (0,00 sec)

mysql> describe DummyTest;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | char(10)     | YES  |     | NULL    |       |
| col2  | decimal(5,2) | YES  |     | NULL    |       |
| col3  | varchar(32)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0,00 sec)

mysql>

Let's look at the folder and see what happened on the file-system-level:

august@AMD4:~/MySQL/data/Test$ ls -l
insgesamt 16
-rw-rw---- 1 august august   65 Nov 20 16:50 db.opt
-rw-rw---- 1 august august 8620 Nov 20 16:53 DummyTest.frm
august@AMD4:~/MySQL/data/Test$

As you can see there are only 2 files: one contains internal-information about the database Test and the other contains create-information about the table DummyTest. There is no data- or index-file, it's really a silly engine.

Back to MySQL we can play with this table:

mysql> insert into DummyTest values ( 'ABC', 5.2, 'Test' );
Query OK, 1 row affected (0,00 sec)

mysql> select * from DummyTest;
Empty set (0,00 sec)

mysql> select count(*) from DummyTest;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0,00 sec)

mysql> delete from DummyTest where col1='ABC';
Query OK, 0 rows affected (0,00 sec)

mysql> update DummyTest set col2=7.2 where col3 = 'Test';
Query OK, 0 rows affected (0,01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql>

And because we do not need this engine we do some cleanup:

mysql> truncate table DummyTest;
Query OK, 0 rows affected (0,00 sec)

mysql> drop table DummyTest;
Query OK, 0 rows affected (0,00 sec)

mysql> show tables;
Empty set (0,00 sec)

mysql> drop database Test;
Query OK, 0 rows affected (0,06 sec)

mysql> uninstall plugin DUMMY;
Query OK, 0 rows affected (0,03 sec)

mysql> quit
Bye
august@AMD4:~/MySQL/pgm$

That's all. As you can see this silly engine was integrated into the MySQL-server. The steps needed to integrate it into the MariaDB-server are similar but I didn't test this.

the code explained

Thanks to OOP and inheritance our little engine includes all the code needed for a storage engine.

To do it manually: in Eclipse I went to the folder storage and created a new folder: dummy
In this folder I created a new file ha_dummy.h as a Header File and also a new file ha_dummy.cc as Source File.

Next I entered all the code you will see in the file ha_dumm.h and ha_dumm.cc

a bit more detailed

The class ha_dummy is derived from the class handler as you can see in the header-file.
We must implement the functions included in the definition of our class because they are defined as pure virtual functions in handler.h. In this engine they are implemented as functions with simply a return-value of OK and nothing more, but there is one exception: in handler.h the function write_row() is defined as returning the constant "unknown command". One does not need to implement this function but in this case an INSERT-statement will result in an error-message. For behaving like the NULL-device it should accept everything (and throw it away) so I implemented this function.

One needs at least 3 more functions which are outside the scope of this class. These are needed for initialising the plugin, creating the class and destroying the plugin. You will see a structure at the end of the file ha_dummy.cc, where two of them are given to the server-code. The 3rd function is given back in the init-code.

Next I switched to another storage engine in the storage-folder and copied the file CMakeList.txt to the dummy-folder. Open this file and change all references of the (old) storage engine to dummy. I took the file from the example-engine, copied it to the dummy-folder and modified this file so that it now looks like this:

SET(DUMMY_PLUGIN_DYNAMIC "ha_dummy")
SET(DUMMY_SOURCES ha_dummy.cc ha_dummy.h)
MYSQL_ADD_PLUGIN(dummy ${DUMMY_SOURCES} STORAGE_ENGINE MODULE_ONLY)

this means changing all occurrences of EXAMPLE into DUMMY (and example into dummy). Save this file.
another hint: do not modify the file in the example-folder! Take the newly copied file from the dummy-folder.

For generating the MAKE-files I switched to a console and entered the line as described above.

Then I switched back to Eclipse, recompiled the source and this storage engine is included in the server. And then I did the checks as described above.

Thursday, 14 November 2013

working with the source-code

For my journey I use these sources:

MySQL: version 5.5.8
MariaDB: version 10.0.4

Things change, and in the case of these two products they change quite often (it's hard to stay in sync with the development-teams of these products). But I don't want to look at the recent progress so I will stay with these version for a while (the MySQL-source I used is a bit outdated, I know, but for my journey it is sufficient). Sometime in the future I will switch to a newer version, but in this case I will report this.

MySQL

This is the file with the sources I use: mysql-5.5.8.tar.gz
You will find it here: http://downloads.mysql.com/archives.php?p=mysql-5.5&o=other
Please look for this entry on the page:

MariaDB

This is the file with the sources I use: mariadb-10.0.4.tar.gz
You will find it here:https://downloads.mariadb.org/mariadb/10.0.4/
This is the place on the page where you find the download-link:

download the 2 files (MySQL- and MariaDB-sources) to any place you can remember later.

how to use thes files

As you already may know I use Linux so I used nautilus (the file-manager of Ubuntu) and did the following steps:

created a directory MySQL-Src
copied the gz-file with the MySQL-sources into this directory
double-clicked on the gz-file in this directory
file-roller (the archive-manager of Ubuntu) opened
I extracted the files from the archive

now I have a directory-structure like this:

$HOME/workspace/MySQL-Src/mysql-5.5.8

and in the last subdirectory I see all the directories and the files of MySQL.

And now for MariaDB: the steps are similar. as a result the structure of the duirectories look like:

$HOME/workspace/MariaDB/mariadb-10.0.4

As you can see the pathnames are not identically named: MySQL-Src and MariaDB. Please excuse the confusion.

On Microsoft Windows I used TotalCommander for the same purpose with similar steps.

installation

Here you will find a description on how to install the software from the source-files:

http://dev.mysql.com/doc/refman/5.5/en/source-installation.html

I needed some adjustments to make the software work on my machine and in the way I wanted it. On a console I did this step:

MariaDB

august@AMD4:~/workspace/MariaDB/mariadb-10.0.4$ cmake -DCMAKE_BUILD_TYPE=Debug -DMYSQL_DATADIR=/home/august/MariaDB/data -DCMAKE_INSTALL_PREFIX=/home/august/MariaDB/pgm

MySQL

august@AMD4:~/workspace/MySQL-Src/mysql-5.5.8$ cmake -DWITH_DEBUG=1 -DMYSQL_DATADIR=/home/august/MySQL/data -DCMAKE_INSTALL_PREFIX=/home/august/MySQL/pgm

to check:

cmake . -LH

But I didn't want to work in a console, I prefer Eclipse. So I started Eclipse and created two new projects:

File -> Import -> Existing Code as MAKEFILE Project

Select the location of your project as described above.

In the project-explorer of Eclipse I marked one of these newly created projects and right-clicked on it. Select Properties and in the dialog look for Build, Now I can change the make-command to build the server and do the installation of the just created software:

As you can see it now executes a make install instead of the usual make all.

For starting the server via Eclipse I need to give it some additional information. So I opened the dialog for the Debug Configurations and added the directories for the data and programs as parameters:

and I also did this for the Run Configuration, for MySQL and MariaDB.

Sometime I encountered the message that the server couldn't start:

In this case I opened a console and entered these two lines:

sudo mkdir /var/run/mysqld
sudo chmod 0777 /var/run/mysqld

And that's all. Now let the journey begin.

environment

From time to time I will make some tests and comparisons and try to explain the results adding some opinions of my own. If you want to check my results and compare them to your results you should know the environment I'm working in:

my PC is equipped with:

an AMD A10-5800K APU with Radeon(tm) HD Graphics, 3.8 GHz, 4 cores
has 32 GB RAM
a 2TB Toshiba hard-disk, SATA III, 7200rpm
a 120 GB SSD Samsung 840 Pro

The operating system is Linux. in my case this is Ubuntu in the version 13.04 (64 bit)

For my SW-developments I use Eclipse Kepler.

Nothing is static in this business, a lot has changed in the years I worked in it. So it is safe to say that things will change in the future (this i one of the reasons why this business is so fascinating). I will inform you when I change any of the components listed above.

And the first change happenend some days ago: I changed the OS on my PC to Ubuntu 13.10.

Update

2014/08/19: ooops, I forgot to tell you this: about 4 months ago I upgraded the OS of my PC to Ubuntu 14.04. This resulted in compile-errors of the source-code so I had to upgrade the source-code of MariaDB to the version 10.0.10.

Thursday, 7 November 2013

copyrights

some of the names I will use more often in this little blog are MySQL and MariaDB. These name (and other names too) belong to companies and I will respect their rights:


MySQL: © Oracle and/or its affiliates. All rights reserved. 

MariaDB: © MySQL AB & MySQL Finland AB & TCX DataKonsult AB

Micorsoft Windows: © Microsoft Corporation. All rights reserved. 
Micorsoft Word: © Microsoft Corporation. All rights reserved. 

Linux: ™ by Linus Torvalds, the trademark is owned by The Linux Foundation
the source-code is published under the terms of the GPL
Ubuntu: © 2013 Canonical Ltd. Ubuntu and Canonical are registered trademarks of Canonical Ltd.

Eclipse: © 2013 The Eclipse Foundation. All Rights Reserved.

LibreOffice: is licensed under the terms of the LGPLv3.

in case I forgot a company or a product please drop me a line and I will add this company/product to this list.

I will present some of my own code here. Use it as it is, but I'm not responsible for any problems with this code. The code is only a proof of concept and it is definitely not production ready (after years of programming in commercial environments I do know the difference).

the code is © by me but I publish it under the terms of the GNU GPL which you can find here:

http://www.gnu.org/licenses/gpl-2.0.html

And finally the little drawing at the end of the page is © Heike Kunkel, you can contact her via: pinselstil at web dot de

Thursday, 31 October 2013

about me

for years I worked as a programmer mostly in the backend-area and with a variety of databases. The languages were C/C++, Java and some others. the databases were different versions of Oracle, MS SQL-Server, Sybase, to name a few. I've also used a variety of tools and libraries.

Here you can find a list of projects I worked in:

http://www.xing.com/profile/August_Quint

http://de.linkedin.com/in/augustquint

If you have any questions regarding these projects just drop me a line.

Some years ago I worked for a company named "EDV Studio Ploenzke" (now owned by CSC). During this time Mr. Bubec made a drawing of me:

© Bubec (http://www.bubec.de)

Monday, 28 October 2013

introduction

sometimes the introduction is the hardest part .....

For years I worked as a programmer, in the last 20 years I worked mostly in the backend-area and with a variety of databases. And I always wanted to know how the database is doing all these things I gave to it in the form of data or instructions.

Now I take the chance to look into the code of MySQL/MariaDB and see how these things are handled by the DB-software, only following my curiosity.

This is not a complete overview of the source-code of these products, this is a very personal tour. If you like you are invited to follow me. Currently I do not know where I will end.

You will find a general overview of the MySQL and MariaDB-code here:
https://mariadb.com/kb/en/recommended-mariadb-mysql-books/
on the site look for this entry:

in these books you will find a more complete overview of the internals of these database-systems.

I will start my tour with a look into the storage engines, especially the code of some of these engines.

I will present some of my observations and play a bit with the code. I will also write my own code and present it here. My own code is not production ready, it's more of a proof of concept.

If you want to use my code you will need the source-code of MySQL or MariaDB and compile it for yourself. In a later post I will explain which version I used, where I found it and what I did to use it.