Feed Aggregator

… 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 them as “the kid on the new block”. The reason is that I don’t feel as a new kid in the MySQL village (or is it a city?), let alone in DBMS country.

Ever since I left university (Technical University of Berlin, Germany), I have been involved in SQL DBMS development. After my previous product’s team had been dissolved in Berlin and maintenance moved to Riga, Latvia, as a cost-cutting measure, I joined MySQL AB in 2004 as a member of the Build Team. Some of you will remember my name …

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;
+----+--------------------------------+--------------+------+
| id | left(data, 30)                 | length(data) | ts   |
+----+--------------------------------+--------------+------+
|  1 | Anhang                         |            6 | NULL |
|  2 | Anhang                         |            6 | NULL |
|  3 | Anhangblablablablablablablabla |      2400006 | NULL |
|  4 | Anhang                         |            6 | NULL |
+----+--------------------------------+--------------+------+

Max_packet_size …

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 files'
Limit                     Soft Limit           Hard Limit           Units    
Max open files            1024                 4096                 files    

Unfortunately all common Linux distributions nowadays have too small Max open files hard limit for database systems (also true for Oracle, PostgreSQL and others).

The number of open files your MySQL database should use you can set with the following variable in your my.cnf:

[mysqld_safe]
open_files_limit               = 8192

Now it …

Hello all, on some Linux systems I get the following warning during my MySQL database start-up:

[Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 8192)
[Warning] Buffered warning: Changed limits: max_connections: 214 (requested 505)
[Warning] Buffered warning: Changed limits: table_cache: 400 (requested 512)

What does it mean and is that something I should care about?

Hello all,

I am getting some MySQL warnings in the error log during the start-up:

[Warning] 'user' entry 'root@master' ignored in --skip-name-resolve mode.
[Warning] 'proxies_priv' entry '@ root@laptop4' ignored in --skip-name-resolve mode.

Please suggest how can I prevent such warnings in the MySQL error log?

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;
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                      |
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Warning | 1285 | MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work | …

In Percona Server we have a STATUS variable indicating roughly possible recovery time:

Combined size of InnoDB log files defines how many changes not reflected in the tablespace we may have where innodb_checkpoint_age shows how much changes we actually have at the current moment, being an actual driving factor of recovery time. If you have very large log files allocated but for your workload innodb_checkpoint_age stays low chances are recovery will be quick.
[ InnoDB crash recovery speed in MySQL 5.6
]

These numbers are not available in plain MySQL. How can we calculate this value in plain MySQL?

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   " LSN_PF "\n"
         "Pages flushed up to " LSN_PF "\n"
         "Last checkpoint at  " LSN_PF "\n",
         log_sys->lsn,
         log_sys->flushed_to_disk_lsn,
         log_buf_pool_get_oldest_modification(),
         log_sys->last_checkpoint_lsn);

 fprintf(file,
         "Max checkpoint age    " LSN_PF "\n"
         "Checkpoint age target " LSN_PF "\n"
         "Modified age          " LSN_PF "\n"
         "Checkpoint age        " LSN_PF "\n",
         log_sys->max_checkpoint_age, …

Table of Contents

• MariaDB Lifetime Support Matrix for MariaDB Releases
• MariaDB Corp Enterprise Support Subscriptions
• MySQL Lifetime Support Matrix for MySQL Releases
• Oracle Corp Enterprise Support Subscriptions for MySQL
• PostgreSQL Versioning Policy

Lifetime Support Matrix for MariaDB Releases

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    |
+-------------------------+-------+

There are 2 different reasons why MySQL is creating a temporary disk table instead of a temporary memory table:

• The result is bigger than the smaller one of the MySQL variables max_heap_table_size and tmp_table_size.
• The result contains columns of type BLOB or TEXT.

In the following example we can see how the temporary disk table can be avoided without changing the column types:

mysql> CREATE TABLE test (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, data TEXT
, type TINYINT UNSIGNED
); …

After properly installing and testing a Galera Cluster we see that the set-up is not finished yet. It needs something in front of the Galera Cluster that balances the load over all nodes.
So we install a load balancer in front of the Galera Cluster. Typically nowadays HAProxy is chosen for this purpose. But then we find, that the whole Galera Cluster is still not high available in case the load balancer fails or dies. So we need a second load balancer for high availability.
But how should we properly failover when the HAProxy load balancer dies? For this purpose we put a Virtual IP (VIP) in front of the HAProxy load balancer pair. The Virtual IP is controlled and failovered with Keepalived.

Installation of HAProxy and Keepalived

First some preparations: For installing socat we need the repoforge repository:

shell> cd /download
shell> wget http://pkgs.repoforge.org/rpmforge-release/rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm
shell> yum localinstall rpmforge-release-0.5.3-1.el6.rf.x86_64.rpm 
shell> …

We have recently seen a case where the following command was executed on a Galera Cluster node:

SQL> GRANT SUPER ON userdb.* TO root@127.0.0.111;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

2014-12-09 14:53:55 7457 [Warning] Did not write failed 'GRANT SUPER ON userdb.* TO root@127.0.0.111' into binary log while granting/revoking privileges in databases.
2014-12-09 14:53:55 7457 [ERROR] Slave SQL: Error 'Incorrect usage of DB GRANT and GLOBAL PRIVILEGES' on query. Default database: ''. Query: 'GRANT SUPER ON userdb.* TO root@127.0.0.111', Error_code: 1221
2014-12-09 14:53:55 7457 [Warning] WSREP: RBR event 1 Query apply warning: 1, 17
2014-12-09 14:53:55 7457 [Warning] WSREP: Ignoring error for TO isolated action: source: c5e54ef5-7faa-11e4-97b0-5e5c695f08a5 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 4 trx_id: -1 seqnos (l: 4, g: 17, s: 15, d: 15, ts: 113215863294782)

According to the error message it looks like this command is done in Total Order Isolation (TOI) …

Overview

FromDual provides software repositories for their MariaDB/MySQL related software:

• DEB Repository for Debian and Ubuntu
• RPM Repository for Redhat, Oracle Linux, Rocky Linux, AlmaLinux, CentOS and Fedora
• RPM Repository for SLES and OpenSuSE

DEB Repository for Debian and Ubuntu

For Debian and Ubuntu: Create a file /etc/apt/sources.list.d/FromDual.list and add the following information to it:

source /etc/os-release
# ID              : debian or ubuntu
# VERSION_CODENAME: buster bullseye focal jammy

apt update
apt install gpg curl
mkdir -p /usr/share/keyrings
chmod 755 /usr/share/keyrings
curl https://support.fromdual.com/admin/download/repos/${ID}/${VERSION_CODENAME}/fromdual-repo.pgp | gpg --dearmor --yes --output /usr/share/keyrings/fromdual-repo.pgp

cat >/etc/apt/sources.list.d/FromDual.list <<_EOF
#
# /etc/apt/sources.list.d/FromDual.list
#
deb [signed-by=/usr/share/keyrings/fromdual-repo.pgp] https://support.fromdual.com/admin/download/repos/${ID}/${VERSION_CODENAME} /
_EOF

Then …

Sometimes, MySQL tablespace file(s) might be deleted by mistake, e.g. delete the shared tablespace (ibdata1) or an individual tablespace (table_name.ibd).

In this post I will show you how to recover those files (on Linux OS) having only one condition, MySQL service should still be running. If MySQL service stopped after deleting that file, this method will not work, so it is extremely important to act as quick as possible to avoid data loss.

The following is a simple table creation (innodb_file_per_table is enabled) and the records count inside that table:

SQL> SHOW CREATE TABLE t<br>G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

SQL> SELECT COUNT(*) FROM t;
+----------+
| COUNT(*) |
+----------+
|       22 |
+----------+
1 row in set (0.02 sec)

Now, lets delete the individual tablespace …

Global Transaction ID (GTID) is one of the major features that were introduced in MySQL 5.6 which provides a lot of benefits. I have talked about the GTID concept, implementation and possible troubleshooting at Percona Live London 2014, you can download the slides from our presentations repository or from my session at Percona Live.

On the other hand, there are some important things you should consider before deploying GTID in production, I’m going to list them here in this blog post.

Table of Contents

• Migration to GTID replication
• Non transactionally safe statement will raise errors now
• MySQL Performance in GTID
• mysql_upgrade script
• Errant transactions!
• Filtration on the slave
• Conclusion

Migration to GTID replication

It is required to shutdown MySQL service on all servers in the replication setup in order to perform the migration from classic replication (based on binary logs information) to the transaction-based (GTID) replication which means that the migration process requires downtime.

The online …

A test page in English…

A few weeks ago, we received an interesting Galera Cluster support case from one of our customers that the application is not working well and they face a lot of troubles in their Galera Cluster setup.

After some investigations, we found a lot of insert queries in state “query end” and lasting for long time without being completed. Also some other queries which were sleeping for long time having the info of “XA COMMIT”:

SQL> SHOW PROCESSLIST;

27  user    host:33214  foodmart    Query   14440   sleeping    XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x

SQL> SHOW ENGINE INNODB STATUS;

TRANSACTIONS
============
---TRANSACTION 2DE71D, ACTIVE 14459 sec
9 lock struct(s), heap size 1248, 1 row lock(s), undo log entries 115
MySQL thread id 27, OS thread handle 0x7fc21a42c700, query id 96187 host host-ip foodmart sleeping
XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0x41544f4d ONE …

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

You can download MyEnv from here.

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

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 1.1.1 to 1.1.2

# cd ${HOME}/product
# tar xf /download/myenv-1.1.2.tar.gz
# rm -f myenv
# ln -s myenv-1.1.2 myenv

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

cd ${HOME}/product/myenv
ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/

Changes in MyEnv 1.1.2

MyEnv

• The MySQL Backup Manager was removed from MyEnv and put into its own package which can be downloaded from FromDual Backup/Recovery Manager.
• OEM Agent is checked now based on process list and not oemagentctl status any more. Makes it much faster.
• Alias cdc for NDB Cluster directory removed. …

Precaution: Before you try this out on your production system do a BACKUP first! FromDual Backup Manager can help you with this.

Situation

A MySQL user has delete its InnoDB table files for example like this:

shell> rm -f $datadir/test/test.*

Analysis

We do some analysis first:

mysql> DROP TABLE test;
ERROR 1051 (42S02): Unknown table 'test'

mysql> CREATE TABLE test (id INT) ENGINE = InnoDB;
ERROR 1050 (42S01): Table '`test`.`test`' already exists

The MySQL error log shows us the following information:

141022 17:09:04  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
141022 17:09:04  InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './test/test.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and …