FromDual TechFeed (en)

A MySQL customer of FromDual has different manufacturing plants spread across the globe. They are operated by local companies. FromDuals customer wants to maintain the manufacturing receipts centralized in a MySQL database in the Head Quarter in Europe. Each manufacturing plant should only see their specific data.

Manufacturing log information should be reported backup to European Head Quarter MySQL database.

The process was designed as follows:

Preparation of Proof of Concept (PoC)

To simulate all cases …

We finally got the results. The table does NOT have a Primary Key and/or an index on lSessionId:

CREATE TABLE IF NOT EXISTS `genLogins` (
  `lSessionId` varchar(128) NOT NULL,
  `lUid` int(8) NOT NULL DEFAULT '0',
  `lLang` char(2) NOT NULL,
  `lTimeout` int(10) NOT NULL DEFAULT '0',
  `lReloadsess` enum('y','n') NOT NULL DEFAULT 'n',
  `lTsexpire` int(10) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Please also read: Disadvantages of explicitly NOT using …

We typically suggest our customers to use our MySQL/Galera Cluster my.cnf configuration template to avoid MySQL configuration and performance problems.

And we are paranoid as well. Thus we enable all useful logging:

wsrep_log_conflicts = 1

But this has also some consequences of more visibility…

If you monitor carefully your Galera Cluster for example with the FromDual Performance Monitor for MySQL and MariaDB, you might probably see some strange values increasing from time to time:

mysql< SHOW …

From time to time we see at Galera Cluster customer engagements the following, for me scary, warning in the MySQL error log:

[Warning] WSREP: last inactive check more than PT1.5S ago (PT7.06159S), skipping check

We mostly see this in VMware set-ups. Some further enquiry with the Galera developers did not give a satisfying answer:

This can be seen on bare metal as well - with poorly configured mysqld, O/S, or simply being overloaded. All it means is that this thread could not get CPU time for 7.1 seconds. …

Before I forget it and have to search again here a short note about how to rename a MySQL Partition:

My dream:

ALTER TABLE history RENAME PARTITION p2015_kw10 INTO p2015_kw09;

In reality:

ALTER TABLE history
REORGANIZE PARTITION p2015_kw10 INTO (
PARTITION p2015_kw09 VALUES LESS THAN (UNIX_TIMESTAMP('2015-03-02 00:00:00'))
);

Caution: REORGANIZE PARTITION causes a full copy of the whole partition!

Hint: I assume it would be very easy for MySQL or MariaDB to make this DDL command an in-place …

This error is NOT serious. It happens, when one MPM run overpasses an older one or when you start a second run manually.

We will fix this issue in one of our next releases when we completely have redesigned the MPM agent…

MySQL Enterprise Backup (MEB) has the capability to make real incremental (differential and cumulative?) backups. The actual releases are quite cool and you should really look at it…

Unfortunately the original MySQL documentation is much too complicated for my simple mind. So I did some testing and simplified it a bit for our customers…

If you want to dive into the original documentation please look here: Making an Incremental Backup .

If you want to use MySQL Enterprise Backup please let us …

Hi burps

In Zabbix we have “hosts”. In MPM a host is either a server (DBSERVER01) or a MySQL Instance (mysqld).

The host section [DBSERVER01] or [mysqld] overwrites the [default] section.

The MPM Agent is triggered by the mpm Module in the Server section. So it needs only ONE mpm module per MPM Agent.

In your case it most probably makes sense to have 2 sections: One for the Server and one for the Database:

[DBSERVER01] Modules = mpm server

[mysqld] Modules = process mysql myisam innodb …

MySQL Enterprise Monitor (MEM) has by default no Event Handlers created and activated. These Event Handlers you have to define yourself according to your needs.

In this article we discuss how to create MySQL Enterprise Monitor Event Handlers with MEM v.3.0.18. For other (older) versions the steps may vary…

Task: Event Handler for maximum Connections reached

We would like to be notified by MySQL Enterprise Monitor when the number of connections is near to max_connections.

For this we search first …

FromDual has the pleasure to announce the release of the new version 1.0.0 of its widely used Nagios and Icinga plugins for MySQL, Galera Cluster, MariaDB and Percona Server.

All plugins are basically renewed and should now work all correctly.

The new Nagios/Icinga plugins can be downloaded here.

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

Any feedback, statements and testimonials are welcome as well! Please send them to …

At FromDual, we are currently preparing for our participation in the “Chemnitzer Linux-Tage” in March.
While we don’t yet know whether the programme committee accepted our proposed talks, we will have a booth and hope for interesting exchanges with others from the MySQL, database, Linux, … world. Of course, we will also mention that we are looking for additional colleagues - there are so many tasks that we need more people to handle them all. (In case you got curious, look here: …

MySQL provides some great enterprise features beside the MySQL Server. The ones we are asked the most at customers are:

• MySQL Enterprise Backup (MEB)
• MySQL Enterprise Monitor (MEM) and
• MySQL Enterprise Workbench (MWB)

MySQL Enterprise Backup (MEB)

MySQL Enterprise Backup (MEB) is an alternative to the mysqldump backup utility. Its big advantage is its fast backup but even faster restore performance. This is a must for all MySQL users having bigger databases than let’s say 10 to 20 Gigabytes and/or …

Sometimes we face the situation where we have a full MySQL database backup done with mysqldump and then we have to restore and recover just one single table out of our huge mysqldump file.
Further our mysqldump backup was taken hours ago so we want to recover all the changes on that table since our backup was taken up to the end.

In this blog article we cover all the steps needed to achieve this goal for MySQL and MariaDB.

Recommendation: It is recommended to do theses steps on a testing system and then …

… show that this is expected behavior:

• Galera will replicate a DDL statement first, before it gets executed locally.
• Local execution will fail, because the "super" privilege is a global one, it cannot be granted for a specific database "db.*" but only for all of them "*.*"
• Of course, execution on the other nodes will fail in the same way.

For some time already, FromDual’s “Our Team” page lists me, and it even reveals that I joined in September, 2014. Also for some time, the list of FromDual blogs contains an entry “Jörg’s Blog”, but it doesn’t lead to any entries. It is high time to fix this and create entries, starting with an introduction of myself.

Often, in such introductions people use the phrase of “the new kid on the block”. I won’t. If I am to use those words, I will arrange …

We recently run into some troubles with max_allowed_packet size problems during backups with the FromDual Backup/Recovery Manager and thus I investigated a bit more in the symptoms of such problems.

Read more about: max_allowed_packet.

A general rule for max_allowed_packet size to avoid problems is: All clients and the server should have set the same value for max_allowed_packet size!

I prepared some data for the test which looked as follows:

mysql> SELECT id, LEFT(data, 30), LENGTH(data), ts FROM test; …

Hello oli,

every user/process on a UNIX system has some user limits. There are some soft (S) and hard (H) user limits. Soft limits a user can change himself up to the hard limits.

You can find you own user limits as follows:

shell> ulimit -aS | grep 'open'
open files                      (-n) 1024

shell> ulimit -aH | grep 'open'
open files                      (-n) 4096

and the user limits of an already running process as follows:

shell> cat /proc/`pidof mysqld`/limits | egrep 'Limit|open …

Hello oli,

it looks like you have enabled skip_name_resolve in your my.cnf.
This prevents MySQL to do (DNS) host lookups. If you have created some users with explicit host names MySQL wants to tell you now that you cannot use these users any more with this configuration. Example:

mysql> GRANT ALL ON *.* TO 'root'@'master';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS; …

Looking at Percona Server source code we can find the following:

storage/innobase/srv/srv0srv.cc

export_vars.innodb_checkpoint_age     = (log_sys->lsn - log_sys->last_checkpoint_lsn);
export_vars.innodb_checkpoint_max_age = log_sys->max_checkpoint_age;

Looking at the code we can see how output of SHOW ENGINE INNODB STATUS\G is produced:

storage/innobase/log/log0log.cc

void log_print(FILE* file)
{

 fprintf(file,
         "Log sequence number " LSN_PF "\n"
         "Log flushed up to   " …

For processing SELECT queries MySQL needs some times the help of temporary tables. These temporary tables can be created either in memory or on disk.

The number of creations of such temporary tables can be found with the following command:

mysql> SHOW GLOBAL STATUS LIKE 'created_tmp%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 4     |
| Created_tmp_tables      | 36    |
+-------------------------+-------+ …