Logs and more logs, who has time to read them ?

While working on some new features in MariaDB 10.1, I noticed that a normal user couldn't disable the slow query log, which I thought was a bit silly.

While implementing and documenting this feature, I noticed that the information about the different logs is quite spread around and it's not that trivial to find out how to enable/disable the different logs.

To solve this, I created a new MariaDB kb entry, "Overview of the MariaDB logs that I hope MariaDB and MySQL users will be find useful.

Here follows a copy of the kb entry. If you have any comments or things that could be added, please do it in the kb entry so that it will benefit as many as possible!

Overview of MariaDB logs

There are many variables in MariaDB that you can use to define what to log and when to log.

This article will give you an overview of the different logs and how to enable/disable logging to these.

The error log

  • Always enabled
  • Usually a file in the data directory, but some distributions may move this to other locations.
  • All critical errors are logged here.
  • One can get warnings to be logged by setting log_warnings.
  • With the mysqld_safe --syslog option one can duplicate the messages to the system's syslog.

General query log

  • Enabled with --general-log
  • Logs all queries to a file or table.
  • Useful for debugging or auditing queries.
  • The super user can disable logging to it for a connection by setting SQL_LOG_OFF to 1.

Slow Query log

The binary log


If you know that your next query will be slow and you don't want to log it in the slow query log, do:


If you are a super user running a log batch job that you don't want to have logged (for example mysqldump), do:


mysqldump in MariaDB 10.1 will do this automatically if you run it with the --disable-log-querys option.

See also


For your eyes only (or Adding better encryption to MariaDB)

With MariaDB and MySQL we have always taken security seriously.

In MariaDB 10.0 we added roles to make it easier to administrate many users.

MariaDB and MySQL has also many different encryption functions, but what has been neglected in the past is to make encryption easy to use.

This is now about to change.

I recently had a meeting with Elmar Eperiesi-Beck from eperi about simplifying the usage of encryption. We agreed to start a close collaboration around encryption for MariaDB with an agenda to deliver something very secure and easy to use soon.

The things we are initially focusing on are:

  • Adding column level encryption.
    • This will be done at the field level, invisible for the storage engine.
  • Block level encryption for certain storage engines.
    • Initially we will target InnoDB and XtraDB.

MariaDB will initially support storing the security keys on a remote file systems, accessed only at startup, and later also support using a daemon for key management.

The above will make your encrypted data in MariaDB secure for:

  • Database users that has user access to the database.
  • Anyone that would attempt to steal the hard disk with the database.

By using the daemon approach a MariaDB installation will even be secure against database administrators, as they will not have any way to access the key data.

eperi has 11 years of experience with encryption and I am very happy to see them engage with MariaDB to provide better security to MariaDB users!


conference, conferences...

It's now 3 weeks since the MariaDB & MySQL community day in Santa Clara.

Thanks everyone for coming!

Personally I think it was a success, especially considering the short
time we had to put it together! 11 great speakers and 100+ participants.

We had a small issue with the camera that we used to record all talks: The slides are not very visible. We have been working on editing the videos for all talks to fix this and will update the conference page with both slides and videos for the talks as soon as the editing is finished. The first video is already available! Hope you like it!

We plan to have another MariaDB & MySQL community day in mid November in Florida and another one in Europa after the summer.

Please contact me at 'foundation 'at' mariadb (dot) org' if you want to participate in any of these events!

For the Santa Clara community day we didn't have time to involve the community in selecting the speakers. For the next community days we will work openly with the MariaDB community to select the speakers and plan the event!

I am now attending the LinuxFest Northwest conference where I have a talk about "MariaDB 10.0", which is now declared stable, and "How to make money with open source". Look me up if you want to talk with me about these topics or if you want to discuss, sponsor, or be part of developing any of the features we plan for MariaDB 10.1.


Scheduled talks for the MariaDB & MySQL community event in Santa Clara

We have now a great set of talks for the MariaDB & MySQL community event in Santa Clara on 3rd of April!

You can find the current scheduled talks here.

Initially we had a few additional talks by other community members, who however had to cancel because of contractual reasons with Percona Live.

We can still fit in a few extra talks by adjusting the schedule. If you want to present something that you think is important for most of the MariaDB and MySQL community, please connect with us at 'foundation' 'at' mariadb (dot) org' or add a comment to this blog.

This is going to be the best event this year if you want to know more about MariaDB and what is happening around MariaDB and MySQL!

You will not only be able to attend great talks, you will also get to talk directly with many of the original creators of MariaDB and MySQL!

Don't worry if you happen to miss some of the talks. We plan to put all talks on YouTube, so that you can view them later at your convenience.

Because of the rush of setting up this conference we did not have time to have a proper community board choose and review the talks. We plan to fix this for the next MariaDB & MySQL community event. The vision is to organize 2-4 free community events per year where all companies in the MariaDB and MySQL community can participate on equal terms.

We are thinking about having the next MariaDB foundation conference in Europa and the following one on the USA east coast. These will be standalone events later this year.

Please contact me if you want to be part of organizing or participate in these or future events


MariaDB & MySQL community event 2014 in Santa Clara

I am happy to announce that the MariaDB Foundation is organising a MariaDB & MySQL community event in Santa Clara on Thursday the 3rd of April. The venue is the Hilton Santa Clara hotel, a short walk from the Percona Live 2014 event.

The community event is hosted by the MariaDB Foundation with support from AccelerationDB. This is a free community event to complement the Percona Live event. The community event will be a full day focusing on many things that are not presented at Percona Live.

If you are coming to the community event, why not also go to the expo hall ($75) in the convention center as well and support all the vendors there.

We were partly inspired to do this by Baron Schwartz blog post announcing the Percona Performance conference in 2009. We believe that there should be more free conferences about MariaDB and MySQL that will allow anyone to participate. Personally I would also like to see more conferences where the speakers are drawn from all the people that create and continue to innovate in the technology, rather than conferences where a majority of the speakers come from a single company.

The themes for this community event are MariaDB 10.0 GA, High availability and Performance.

In the next MariaDB & MySQL community event we plan to also host a MariaDB and MySQL bootcamp. We where not able to do it this year because of lack of funding and time (if anyone would like to help us do it this time, please contact me!).

We already have a lot of proposed talks from MariaDB developers, Galera developers and some other active community members.
Topics include:
  • MariaDB 10.0 GA, the new features
  • Spider, storage engine with built in sharding
  • Connect, storage engine that allows you to talk with the world (Oracle, PostgreSQL, files etc...)
  • Galera overview and case studies
  • Show case how to insert continuously 1M rows/seconds while doing concurrent reads with MariaDB and ScaleDB
  • How we optimized MariaDB; True case studies from the programmers vault
  • MySQL MHA and Continuent Tungsten shootout 2.0
  • MariaDB multi source replication capability
  • Scaling MySQL (case study)
  • Using ROLES to get more security
We are still looking for more speakers from different companies to make this the best free and community driven MariaDB and MYSQL event in 2014! If you want to talk at the community event, please send an email to 'foundation 'at' mariadb (dot) org'.

We will also organise a dinner that will happen on the same Thursday at the Taste restaurant, a very cool place right around the corner from Birks and Pedros. As the event is free, you will need to pay for the food but we hope to get some further sponsorships for some free drinks (in addition to the inevitable black vodka).

You can register to attend the conference and/or dinner here.

You can use the 'foundation 'at' mariadb (dot) org' email address if you want to sponsor the community event. As MariaDB foundation is a non profit organisation, all sponsorships will go to pay for the event venue, hotel and travel for speakers (who could not otherwise afford to attend); in the event there's anything left over the Foundation will use it for further community activities.


The final piece of the puzzle

I just pushed the new CREATE OR REPLACE TABLE syntax into MariaDB 10.0, for the soon to be released 10.0.8-gamma (RC). (Before we had only CREATE OR REPLACE for views)

When using the new syntax, the CREATE statement will automatically DROP the old table if it existed.

This is the last feature (which is also a bug fix) depending on me that needed to be pushed before we could release 10.0 gamma (RC). Next, I will start working on speed optimizations and features in 10.1.

The CREATE OR REPLACE TABLE syntax was needed to make global transaction id (GTID) work reliably with CREATE ... SELECT, both in statement-based and row-based replication.

We (Kristian Nielsen and I) didn't think that the solution used in MySQL 5.6 (to give an error message "CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1") when using CREATE ... SELECT was good enough. We wanted something better.

The solution now implemented ensures that we can store DROP TABLE + CREATE TABLE + INSERT INTO TABLE under one GTID.  The GTID entry can also be re-executed in case of slave failure during execution.

While developing CREATE OR REPLACE, I noticed several possible problems in the replication code that were not properly taken care of (neither in MySQL or MariaDB):
  • Having different storage engines on master and slave for any table would not work well together with the GTID code and would cause inconsistencies between GTID's in the master and slave.
  • If CREATE SELECT would fail on the slave, there was no way the slave could continue as it could not roll back the CREATE statement.
  • Slave failure during a DROP TABLE would make the slave stop and it would be unable to restart without user intervention.
  • Having different replication modes on master and slave (like statement based on master and row based on slave) would cause inconsistencies in GTID generation.
To fix these and make the slave more robust, I introduced the following things:
  • While the slave is running a transaction, it will treat all tables as transactional tables when it comes to the caching of statements for the binary log.
  • Commits will only happen when the binary log says so.  This ensures that the slave will log and commit changes in the same order as the master, independent of the storage engine used.
  • CREATE is replayed on the slave as CREATE OR REPLACE.  This makes CREATE SELECT statements repeatable on the slave.
  • DROP TABLE statements are replayed on the slave as DROP TABLE IF EXISTS.  This makes DROP TABLE statements repeatable on the slave.
  • One can now have a mix of DDL and DML statments in the binary log (we use this fact to handle CREATE ... SELECT which is logged as BEGIN ; DROP; CREATE ; INSERTS ; COMMITS). This can be very useful also for other things in the future.
The end effect of the above is that the slave in 10.0.8 is going to be more robust than ever before.  In addition, the replication mode will not affect how GTID's are generated anymore.

I also added a variable 'slave-ddl-exec-mode' that one can set to STRICT if one prefers the old behavior that the the slave will fail if the DDL would fail on the slave for any reason, including if it fails to repeat a command on restart.

As a bonus, I also fixed that if one used LOCK TABLES with CREATE OR REPLACE TABLE, the lock will be held while the table is deleted and re-created and the lock is then added to the new table. This makes it possible to replace a table with an empty one without other users noticing it.

Here is an extract from the documentation of CREATE OR REPLACE :

The CREATE OR REPLACE TABLE syntax was added in MariaDB 10.0.8 to make replication more robust if it has to rollback and repeat statements like CREATE ... SELECT on slaves.

CREATE OR REPLACE TABLE table_name (a int);

is basically the same as:

CREATE TABLE table_name (a int);

with the following exceptions:
  • If table_name was locked with LOCK TABLES it will continue to be locked after the statement.
  • Temporary tables are only dropped if the TEMPORARY key word was used. (With DROP TABLE temporary tables are preferred to be dropped before normal tables).


MariaDB Foundation achivements 2012-12 - 2013-09

I recently read some comments that we at the MariaDB Foundation have not been very open about what we are doing.

We are very sorry about this. The problem is not that we are secret about what we are doing, the problem is that not many of us working at the MariaDB Foundation are very active bloggers.

I will try to address this concern by starting a monthly blog about the MariaDB development that MariaDB Foundation employees are doing. This together with Simon Phipps' state of the sea lion blog, which is published on the MariaDB Foundation blog, should hopefully give everyone a better idea of what we are doing.

At the MariaDB Foundation we are now 4 people working full time on code, documentation, and spreading MariaDB. Here are the things we have done since the Foundation was formed in December 2012:

Major development efforts based on code from the community:
  • MDEV-4568 Port Percona response time distribution as audit plugin
  • MDEV-4911 Add KILL query id, and add query id information to process list
  • MDEV-4684 Enhancement request: --init-command support for mysqlslap

Major new development efforts:
  • MDEV-4702 Reduce usage of LOCK_open
  • MDEV-4011 Per thread memory usage
  • MDEV-4902 sql_yacc.yy incompatible with bison 3
  • MDEV-4950 mysql_upgrade fails with disabled InnoDB (a bug that is not fixed in MySQL)
  • MDEV-21 LGPL C client library
  • LGPL Java client library.
  • Some work on a new LGPL ODBC driver for MySQL and MariaDB.
  • Better error messages (the error code in an error message now has descriptive text)
  • Faster connect (lots of small fixes).
  • MDEV-3941 CREATE TABLE xxx IF NOT EXISTS should not block if table exists.

The MariaDB Foundation has also done a lot of work on the merge of MySQL 5.6 to MariaDB 10.0. The most notable features done by the Foundation are:
  • All InnoDB changes
  • All performance schema changes
  • Cleaned up the HANDLER interfaces (to make life easier for storage engine writers).
  • MySQL-5.6 TIME/TIMESTAMP/DATETIME data types with fractional seconds
  • MySQL-5.6 temporal literals
  • utf16le character set
  • TO_BASE64 and FROM_BASE64 SQL functions
  • MySQL-5.6 collation customization improvements (in progress)
Note that the above is not a complete list of what is in MariaDB 10.0!

We are working with external entities and contributors to get features into MariaDB. Some of these have required a lot of work on the MariaDB Foundation side:
  • CONNECT storage engine
    • Porting to various operating systems (*BSD, Linux flavors, Solaris)
    • Porting to unixODBC
    • Fixing compiler warnings
    • Testing and writing mtr tests
    • Checking, reporting and fixing valgrind warnings
    • Reviewing the author's code
    • Adding MySQL/MariaDB security support (FILE privilege and secure_file_priv)
    • Adding ConnectSE support in embedded server
    • Globalization: making ConnectSE support multiple MariaDB character sets
    • Discussing many aspects of better integration of ConnectSE into MariaDB with the author.
    • Packaging (RPMs, DEBs, MSI)
  • TokuDB storage engine
    • Review of code
    • Some small integration changes.
  • Parallel slave
  • Multi source replication
    • Worked closely with the original author to extend the code from a working proof of concept to production level code.
  • MDEV-4438 Spider storage engine
    • Continuously working with Kentoku
  • MDEV-4425 REGEXP enhancements (a Google Summer of Code project, in progress)
    • Mentoring Sudheera Palihakkara (the student)
    • Student's code reviews (for RLIKE and RLIKE_REPLACE)
    • Code enhancements proposals
    • CMake related enhancements
    • Wrote RLIKE_SUBSTR and RLIKE_INSTR functions
  • Added support for --crash-script in mysqld_safe.

A lot of basic system maintenence:
  • Reported a lot of bugs (this is not a full list):
    • MDEV-4489 Replication of big5, cp932, gbk, sjis strings makes wrong values on slave
    • MDEV-4634 Crash in CONVERT_TZ
    • MDEV-4635 Crash in UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y'))
    • MDEV-4651 Crash in my_decimal2decimal in a ORDER BY query
    • MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))
    • MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
    • MDEV-4654 Wrong warning for CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY
    • MDEV-4655 Difference in how GREATEST and COALESCE process arguments
    • MDEV-4714 Wrong TIME value from a long string
    • MDEV-4724 MAKETIME does not preserve microseconds
    • MDEV-4838 Wrong metadata for DATE_ADD('string', INVERVAL)
    • MDEV-4841 "Wrong character set of ADDTIME() and DATE_ADD()"
    • MDEV-4842 STR_TO_DATE does not work with UCS2/UTF16/UTF32
    • MDEV-4843 Wrong data type for TIMESTAMP('2001-01-01','10:10:10')
    • MDEV-4844 Redundant warning in SELECT TIME('00:00:00.')
    • MDEV-4848 Wrong metadata or column type for LEAST(1.0,'10')
    • MDEV-4850 MATCH uses a wrong fulltext index with mismatching collation
    • MDEV-4857 Wrong result of HOUR('1 00:00:00')
    • MDEV-4858 Wrong error message for a huge unsigned value inserted into a TIME column
    • MDEV-4859 Wrong value and data type of "SELECT MAX(time_column) + 1 FROM t1"
    • MDEV-4861 TIME/DATETIME arithmetics does not preserve INTERVAL precision
    • MDEV-4862 Wrong result of MAKETIME(0, 0, 59.9)
    • MDEV-4863 COALESCE(time) returns wrong results in numeric context
    • MDEV-4868 Length of CURRENT_TIME is too long
    • MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1)
    • MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types
    • MDEV-4871 Temporal literals do not accept nanoseconds
    • MDEV-4872 Inserting a huge number into a TIME column produces a wrong result
    • MDEV-4898 Too many warnings when inserting a bad value into a TIMESTAMP column
    • MDEV-4900 Bad value inserted into a TIME field on truncation
  • Fixed a LOT of bugs (the following is not a full list):
    • MDEV-4489 Replication of big5, cp932, gbk, sjis strings makes wrong values on slave
    • MDEV-4512 Valgrind warnings in my_long10_to_str_8bit on INTERVAL and DATE_ADD with incorrect types
    • MDEV-4518 Server crashes in is_white_space when it's run with query cache...
    • MDEV-4634 Crash in CONVERT_TZ
    • MDEV-4635 Crash in UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y'))
    • MDEV-4639 my_datetime_to_str: Conditional jump or move depends on uninitialized value
    • MDEV-4651 Crash in my_decimal2decimal in a ORDER BY query
    • MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00'))
    • MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5')
    • MDEV-4724 MAKETIME does not preserve microseconds
    • MDEV-4804 Date comparing false result
    • MDEV-4819 Upgrade from MySQL 5.6 does not work
    • MDEV-4841 "Wrong character set of ADDTIME() and DATE_ADD()"
    • MDEV-4843 Wrong data type for TIMESTAMP('2001-01-01','10:10:10')
    • MDEV-4863 COALESCE(time) returns wrong results in numeric context
    • MDEV-4871 Temporal literals do not accept nanoseconds
    • Fixed MySQL bug #69861 LAST_INSERT_ID is replicated incorrectly if replication filters are used.
    • Fixed issues with partitions and create temporary table SELECT
    • If one declared several continue handler for the same condition on different level of stored procedures, all of them where executed. Now we only execute the innermost of them (the most relevant).
    • Don't abort InnoDB if one can't allocate resources for AIO
    • Added some fixes that should make MyISAM & Aria REPAIR work with more than 4G records.
    • MDEV-4352 LOAD DATA was not multi-source safe
    • MDEV-4394 Sporadic failures in multi_source tests
    • MDEV-4033 Unable to use slave's temporary directory /tmp - Can't create/write to file '/tmp/SQL_LOAD-' (Errcode: 17 "File exists")
    • Increase default value of max_binlog_cache_size and max_binlog_stmt_cache_size to ulonglong_max.
    • MDEV-4319 mysqlbinlog output ambiguous escaping (patch by Ian Good)
    • OPTION is now a valid identifier (not a reserved word)
    • Fixed issue with LOCK TABLE + ALTER TABLE ENABLE KEYS + SHOW commands.
    • MDEV-628 Querying myisam table metadata may corrupt the table.
    • Replace with an auto_increment primary key and another unique key didn't replicate correctly with REPLACE.
    • BUG#51763 Can't delete rows from MEMORY table with HASH key.
    • MDEV-389 Server crash inserting record on a temporary table after truncating it.
    • Fixed crashing bug in GROUP_CONCAT with ROLLUP
    • MDEV-4013 Password length in replication setup
    • MDEV-4009 main.delayed sporadically fails with "query 'REPLACE DELAYED t1 VALUES (5)' failed: 1317: Query execution was interrupted"
    • Fixed CREATE TABLE IF EXIST generates warnings instead of errors
    • Removed lock wait timeout warning when using CREATE TABLE IF EXISTS
  • Code cleanups and code rewrites.
  • Continued work to ensure that MariaDB works on many operating systems.
    • Did lots of small fixes for Solaris.
  • Review of code from the community.
  • Removed valgrind and compiler warnings.
  • Added new test cases.
  • QA & benchmarking.

Talked about MariaDB and Open Source/Free Software at a lot of conferences:
  • Percona Live London 2012
  • SkySQL Roadshow, Stuttgart
  • Vista developer Conference, Riga
  • PHP Conference, Pasila, Finland
  • SkySQL Roadshow, Amsterdam
  • Percona Live, Santa Clara
  • SkySQL Roadshow, London
  • MySQL Meetup, NYC
  • Suomen Arkistointi Seminaari, Mikkeli, Finland
  • SkySQL Roadshow, Berlin
  • Ali Baba Conference, Hangzhou, China
  • OSCON, Portland
  • Debconf, Switzerland
  • Froscon, St Augustin, Germany
  • Haaga-Helia Talks, Finland
  • IDCEE Conference, Ukraina
  • Latinux, Brazil & Peru
  • Highload Conference, Moscow
  • Foscon, Gotenburg, Sweden
  • Percona Live 2013, London

Other things people from the MariaDB foundation have done:
  • Created +200 and updated +3000 articles in the MariaDB Knowledge Base. Some of the notable things are:
    • Complete documentation of all system variables
    • Complete documentation of all status variables
    • Clients and utilities
    • Geographic functions
    • Performance schema
    • Log files
    • Internationalization, character sets, collation
    • XtraDB and InnoDB content
    • Global Transaction ID (from Kristian's content)
    • Triggers
    • Stored Functions
    • Stored Procedures
    • Views
    • Dynamic Columns
    • Fulltext indexes
    • Replication
    • Date and time
    • Updating fill_help_tables.sql so that MariaDB HELP output will point
    • the Knowledge Base
    • Reformatted most articles for improved readability
    • Replaced many of the external links with links to newly created internal articles
  • Visited a lot of companies to talk about the MariaDB Foundation.
  • Worked closely with OS distributions to get MariaDB accepted as a standard part of their distributions. Partly thanks to the MariaDB Foundation, the following distributions now have MariaDB packages:
    • GNU/Linux KDu
    • Mageia
    • Gentoo
    • openSUSE
    • Slackware
    • Arch Linux
    • ALTLinux
    • MariaDB Debian Live Images
    • Parabola GNU/Linux
    • TurboLinux
    • The Chakra Project
    • Fedora
    • Red Hat Enterprise Linux
    • FreeBSD
    • OpenBSD
    • Debian now has MariaDB packages in 'unstable'.
    • For a full list and to see which distributions are replacing MySQL with MariaDB, see this page.
  • Updated SHOW AUTHORS and SHOW CONTRIBUTORS to give more merit to active contributors and sponsors.

We are very thankfull to the MariaDB Foundation members and supporters that have made this work possible!

The current members are:
If you want the MariaDB foundation to do even more work like the above, you should ensure that your company becomes member in the MariaDB Foundation! You can also sponsor the foundation as a private person!