News

FromDual has the pleasure to announce the release of the new version 1.3.1 of its popular MySQL, Galera Cluster, MariaDB and Percona Server multi-instance environment MyEnv.

The new MyEnv can be downloaded here.

In the inconceivable case that you find a bug in the MyEnv please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for MyEnv).

Upgrade from 1.1.x or higher to 1.3.1

# …

Typically, on a single machine (be it a physical or a virtual one) only a single MySQL instance (process) is running. This is perfectly ok for all those situations where a single instance is sufficient, like for storing small amounts of data (RedHat using MySQL for postfix, KDE using it for akonadi, …), as well as those where a dedicated machine per MySQL instance is appropriate (high CPU load, memory fully loaded, availability requirements).

But there are also those users who want to (or would like …

When analysing MySQL configuration and status information at customers it is always interesting to see how the applications behave. This can partially be seen by the output of the SHOW GLOBAL STATUS command. See also Reading MySQL fingerprints.

Today we wanted to know where the high Com_create_table and the twice as high Com_drop_table is coming from. One suspect was TEMPORARY TABLES. But are real temporary tables counted as Com_create_table and Com_drop_table at all? This is what we want to find out today. …

Recently I was working on a clients question and stumbled over an issue with replication and mixed character sets. The client asked, wether it is possible to replicate data to a table on a MySQL slave, where one column had a different character set, than the column in the same table on the master.

I set up two servers with identical table definitions and changed the character set on one column on the slave from latin1 to utf8.

Master:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL …

This week I was preparing the exercises for our MySQL/MariaDB for Beginners training. One of the exercises of the training is about MySQL spatial (GIS) features. I always tell customers: “With these features you can answer questions like: Give me all points of interest around me!”

Now I wanted to try out how it really works and if it is that easy at all…

To get myself an idea of what I want to do I did a little sketch first:

A customer reported last month that MySQL does a full table scan (FTS) if a query was filtered by a INT value on a VARCHAR column. First I told him that this is not true any more because MySQL has fixed this behaviour long time ago. He showed me that I was wrong:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `data` (`data`)
) ENGINE=InnoDB; …

I recently managed to get my certification as MySQL 5.6 DBA, and was asked to write a blog about it, because I had trouble getting the informations I needed.

You may have figured out too, that Oracle does not really supply you with information about the certification. At least, there is the MySQL documentation. It contains all the information you need.

Further, I recommend to use a virtual linux machine in combination with our tool MyEnv. This way you can simulate multiple scenarios, including replication …

• max_user_connections Can be changed online in MySQL. Cannot be changed in MariaDB if value was set to 0.
• PERFORMANCE_SCHEMA is enabled in MySQL 5.6 and 5.7 by default. In MariaDB disabled by default.
• Replication incompatibilities from MariaDB (Master) to MySQL (Slave)
• MariaDB 10.2.0 still contains XtraDB 5.6. So MySQL 5.7 features cannot be used.
• MariaDB 10.2 Window Functions are missing in MySQL 5.7
• SHOW PROCESSLIST has additional column Progress in MariaDB.
• Progress indication in: mysqldump …

MariaDB 10.2 has introduced some Window Functions for analytical queries.

See also: Window Functions, Window Functions, Window function and Rows and Range, Preceding and Following

Function ROW_NUMBER()

Simulate a row number (sequence) top 3

SELECT ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY category_id) AS num
     , category.category_id
  FROM category
 LIMIT 3
;

or

SELECT ROW_NUMBER() OVER (ORDER BY category_id) AS num
     , category.category_id
  FROM category
 LIMIT 3
;
+-----+-------------+
| num | …

One of our customers recently ran into a problem, where he wanted to have a preferred donor for SST, whenever a node came up. The problem was, that the node did not come up, when the preferred donor was not running.

In the documentation, you can find the parameter wsrep_sst_donor, which prefers the specified node as SST donor. This is great, as long as the donor is actually running.

The problem can be fixed by adding a comma to the end of the value of wsrep_sst_donor, what would look like this: …

Time flies, and my blogging frequency is quite low. More frequent would be better, but knowing myself I’ll rather not promise anything ;-)

Still, it is appropriate to write some notes about CeBIT, the “Chemnitzer Linuxtage 2016”, and future events.

CeBIT

CeBIT was running from March 14 to 18 (Monday till Friday) in Hannover, Germany, and I will leave the general assessment to the various marketing departments as well as to the regular visitors (to which I do not belong).

In order to meet …

To synchronize the data between the Galera Cluster and a new or re-entering Galera node Galera Cluster uses 2 different mechanisms:

• For full synchronization of data: Snapshot State Transfer (SST).
• For delta synchronization of data: Incremental State Transfer (IST).

The Incremental State Transfer (IST) is relevant when a node is already known to the Galera Cluster and just left the cluster short time ago. This typically happens in a maintenance window during a rolling cluster restart.

The Galera Cache is a …

FromDual has the pleasure to announce the release of the new version 1.0.1 of the FromDual Nagios and Icinga plugins for MySQL, Galera Cluster and MariaDB.

Download

The new FromDual Nagios plugins can be downloaded here.

In the inconceivable case that you find a bug in the FromDual Nagios plugins please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for Nagios plugins). …

or

xfs Users, Take Care!

Recently, we had a customer ask: Why do many files holding my data take up vastly more space than their size is? That question may sound weird to you, but it is for real, and the customer’s observation was correct. For a start, let’s make sure we are using the same terms.

• The size of a file is the number of bytes it will deliver if it is read sequentially from start to end.
• The space it takes up is the sum of all disk pages which are used to hold the file’s data, …

The FOSDEM 2016 in Brussels (Belgium) January 29/30 is over and was very interesting and IMHO a big success.

For all those who could not participate at FOSDEM 2016 our presentation slides about PERFORMANCE_SCHEMA and sys schema are available here:

PERFORMANCE_SCHEMA and sys schema - What can we do with it? (PDF, 406 kbyte)

FromDual has the pleasure to announce the release of the new version 0.3 of the FromDual Ops Center for MySQL and MariaDB.

The FromDual Ops Center for MySQL and MariaDB (focmm) is an application for DBA’s and system administrators to manage MySQL and MariaDB database farms.

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks.

More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center can be downloaded here.

In the …

FromDual has the pleasure to announce the release of the new version 1.3.0 of its popular MySQL, Galera Cluster, MariaDB and Percona Server multi-instance environment MyEnv.

The new MyEnv can be downloaded here.

In the inconceivable case that you find a bug in the MyEnv please report it to our bug tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for MyEnv).

Upgrade from 1.1.x or higher to 1.3.0

# …

Most of you know, that it is possible to synchronize MySQL and MariaDB servers using replication. But with the latest releases, it is also possible to use more than just two servers as a multi-master setup.

Most of you know that both MySQL and MariaDB support replication in a hierarchical master-slave-setup, to propagate changes across all connected servers.

But with the latest releases, a slave can have more than one master.

The keyword: Multi-Source replication

It is supported from MySQL 5.7 and MariaDB …

Hi oli

Since MySQL 5.6.2 there is a new MySQL variable called binlog_rows_query_log_events. If you enable this variable MySQL will write the original SQL statements into the binary logs. More information about this variable you can find here:

http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_rows_query_log_events

To see the original queries you can either run SHOW BINLOG EVENTS ... in the MySQL client or you can run mysqlbinlog -vv binary-log.000001

I hope, this …

“Split Brain” is the term commonly used for a cluster whose nodes have different contents, rather than identical as they should have. Typically, a “split brain” situation is the DBA’s nightmare, and the Galera software is designed to avoid it. Galera is very successful in that avoidance, and it needs some special steps by the DBA to achieve “split brain”. Here is how to do it - or, for most DBAs, what to avoid doing to not get a split-brain cluster.

Galera’s …