Feed aggregator

What exactly does log_warnings=2 log?

Chris on MySQL - June 6, 2014 - 7:29pm GMT

Have you ever wondered exactly does log_warnings=2 log? Well, I have, and finally decided to check the code. (The manual used to mention setting this to 2 for diagnosing some connection-related problems, but I didn’t run into that comment in my most recent search.)

Basically, in recent 5.6 source code, we find “log_warnings > 1″ in 7 files. In 5.5 source, it is only in 5 files. Here are the 7 files in 5.6:

filesort.cc (line 460)
log_event.cc (lines 4873, 10020, 11209)
rpl_master.cc (line 912)
rpl_rli_pdb.cc (lines 1538, 1596, 1735, 2066)
rpl_slave.cc (lines 3585, 4684, 5405, 5436)
sql_acl.cc (lines 9591, 9613, 11351)
sql_connect.cc (line 791)

Long story short, the main (most common) ones are when a filesort fails (filesort.cc) or a failed login occurs (sql_acl.cc). Then there are some replication-specific instances where it logs extra info, such as master/slave/binlog info, “ignored” errors, and some summary stats for multi-threaded slave worker threads (rpl_master.cc, rpl_rli_pdb.cc, rpl_slave.cc) (All of the extra replication logging was new to 5.6, fwiw.). If it fails to close a connection that it should close, it logs some info (sql_connect.cc). And lastly, another new option in 5.6 is password expiration support. So if that is enabled and expired for the current user (and log_warnings > 1), then a note is logged to the error log as well (sql_acl.cc line 11351).

Long story long, … I debated posting the relevant code snippets, but after seeing how boring that was going to be, I’ve omitted them.

Fwiw, the max log_warnings in 5.6 on 32-bit is 4294967295 and for 64-bit is 18446744073709547520. However, at least for now anyway, it seems there is no difference between setting this to 2 or 18446744073709547520, as the only current references/actions are for “log_warnings > 1″.

In 5.7, there is some new info added to the log_warnings definition, mostly because of the new 5.7 variable log_error_verbosity. Here is what is says about “log_warnings=2 (or higher)”:

“Setting log_warnings=2 (or higher) is equivalent to log_error_verbosity=3 (errors, warnings, notes), and the server sets log_warnings to 2 if a larger value is specified.”

Hope this helps.


MaxScale 0.7.0 Overview and Highlights

Chris on MySQL - June 6, 2014 - 6:24pm GMT

MaxScale 0.7.0 was recently released (it is the 4th alpha, with the beta on the near horizon), and is available for download here.

The release contains a number of new enhancements as well as 8 bugs fixes.


  1. Galera Support: Enhanced support for Galera cluster to allow Galera to be used as a High Available Cluster with no write contention between the nodes. MaxScale will control access to a Galera Cluster such that one node is designated as the master node to which all write operations will be sent. Read operations will be sent to any of the remaining nodes that are part of the cluster. Should the currently elected master node fail MaxScale will automatically promote one of the remaining nodes to become the new master node.
  2. Multiple Slave Connections: The Read/Write Split query router has been enhanced to allow multiple slaves connections to be created. The number of slave connections is configurable via a parameter in the MaxScale configuration file. Adding multiple connections allows for better load balancing between the slaves and in a pre-requisite for providing improved fault tolerance within the Read/Write Splitter. The selection of which slave to use for a particular read operation can be controlled via options in the router configuration.
  3. Debug Interface Enhancements: A number of new list commands have been added to the debug interface to allow more concise tabular output of certain object types within the interface.
    MaxScale> help list
    Available options to the list command:
       filters    List all the filters defined within MaxScale
       listeners  List all the listeners defined within MaxScale
       modules    Show all currently loaded modules
       services   List all the services defined within MaxScale
       servers    List all the servers defined within MaxScale
       sessions   List all the active sessions within MaxScale

    Those objects that are defined in the configuration file can now be referenced by the names used in the configuration file rather than by using memory addresses. This means that services, servers, monitors and filters can all now be referenced using meaningful names provided by the user. Internal objects such as DCB’s and sessions, which are not named in the configuration file still require the use of memory addresses. Two modes of operation of the interface are now available, user mode and developer mode. The user mode restricts access to the feature that allow arbitrary structures to be examined and checks all memory address for validity before allowing access.

  4. Maintenance Mode for Servers: MaxScale now provides a maintenance mode for servers, this mode allows servers to be set such that no new connections will be opened to that server. Also, servers in maintenance mode are not monitored by MaxScale. This allows an administrator to set a server into maintenance mode when it is required to be taken out of use. The connections will then diminish over time and since no new connections are created, the administrator can remove the node from use to perform some maintenance activities.

    Nodes are placed into maintenance mode via the debug interface using the set server command.

    MaxScale> set server datanode3 maintenance

    Nodes are taken out of maintenance using the clear server command.

    MaxScale> clear server datanode3 maintenance
  5. Configurable Monitoring Interval: All monitor plugins now provide a configuration parameter that can be set to control how frequently the MaxScale monitoring is performed.
  6. Replication Lag Heartbeat Monitor: The mysqlmon monitor module now implements a replication heartbeat protocol that is used to determine the lag between updates to the master and those updates being applied to the slave. This information is then made available to routing modules and may be used to determine if a particular slave node may be used or which slave node is most up to date.
  7. Filters API: The first phase of the filter API is available as part of this release. This provides filtering for the statements from the client application to the router. Filtering for the returned results has not yet been implemented and will be available in a future version.

    Three example filters are including in the release

    1. Statement counting Filter – a simple filter that counts the number of SQL statements executed within a session. Results may be viewed via the debug interface.
    2. Query Logging Filter – a simple query logging filter that write all statements for a session into a log file for that session.
    3. Query Rewrite Filter – an example of how filters can alter the query contents. This filter allows a regular expression to be defined, along with replacement text that should be substituted for every match of that regular expression.
  8. MariaDB 10 Replication Support: The mysqlmon monitor module has been updated to support the new syntax for show all slaves status in MariaDB in order to correctly determine the master and slave state of each server being monitor. Determination of MariaDB 10 is automatically performed by the monitor and no configuration is required.
  9. API Versioning: The module interface has been enhanced to allow the API version in use to be reported, along with the status of the module and a short description of the module. The status allows for differentiation of the release status of a plugin to be identified independently of the core of MaxScale. plugins may be designated as “in development”, “alpha”, “beta” or “GA”.
    MaxScale> list modules
    Module Name     | Module Type | Version | API   | Status
    regexfilter     | Filter      | V1.0.0  | 1.0.0 | Alpha
    MySQLBackend    | Protocol    | V2.0.0  | 1.0.0 | Alpha
    telnetd         | Protocol    | V1.0.1  | 1.0.0 | Alpha
    MySQLClient     | Protocol    | V1.0.0  | 1.0.0 | Alpha
    mysqlmon        | Monitor     | V1.2.0  | 1.0.0 | Alpha
    readwritesplit  | Router      | V1.0.2  | 1.0.0 | Alpha
    readconnroute   | Router      | V1.0.2  | 1.0.0 | Alpha
    debugcli        | Router      | V1.1.1  | 1.0.0 | Alpha
  10. Linking: Following reported issues with incompatibilities between MaxScale and the shared library used by MySQL this version of MaxScale will be statically linked with the MariaDB 5.5 embedded library that it requires. This library is used for internal purposes only and does not result in MaxScale support for other versions of MySQL or MariaDB being affected.

Bug Fixes:

  1. mysql/galera monitors hang when backend fails (443)
  2. Read/Write Splitter closes connection without sending COM_QUIT (424)
  3. Internal thread deadlock (438)
  4. Sessions in invalid state (436)
  5. Router options for Read/Write Split module (359)
  6. Some automated tests have invalid SQL syntax (435)
  7. rwsplit.sh test script has incorrect bash syntax (431)
  8. MaxScale crashes after prolonged use (425)

If you’re not too familiar with MaxScale, you can view a short, 4 minute, overview (as well as read some details) here.

Hope this helps.


MaxScale 0.6.0 Overview and Highlights

Chris on MySQL - June 5, 2014 - 2:34am GMT

MaxScale 0.6.0 was recently released (it is the 3rd alpha, with the beta on the near horizon), and is available for download here.

The particular release only contains 2 great additions and 2 important fixes (and note development continues with the 1.0 (GA) features, but these have not been put into this alpha version).

Important Additions:

  1. A feature-complete read/write splitting module, i.e. read and write operations are now balanced in a smarter way to master or slave servers.
  2. New client-based features, such as a version string that provides compatibility with the major connectors, the ability to connect through the root user and the use of the Unix socket when MaxScale is co-located with a client application on the same server.

Important Bug Fixes:

  1. The new parameter “version_string” parameter has been added to service section. This allows a specific version string to be set for each service, this version string is used in the handshake from MaxScale to clients and is reported as the server version to clients.

    The version_string is optional, the default value will be taken from the embedded MariaDB library which supplies the parser to MaxScale.

  2. Statements are not routed to master if a transaction is started implicitly by setting autocommit=0. In such cases statements were previously routed as if they were not part of a transaction.

    This fix changes the behavior so that is autocommit is disabled, all statements are routed to the master and in case of session variable updates, to both master and slave.

If you’re not too familiar with MaxScale, you can view a short, 4 minute, overview here. (And read the 50-foot overview.)

Also, just to throw my 2 cents in, if you’re looking for a read-write splitting load balancer, then you should definitely check out MaxScale (though it is much more robust than that). But that is just one feature that many are already using successfully in production environments. And it will not suffer from the limitations that other read-write splitting load-balancing solutions have encountered which have prevented their wide-spread use. And as I noted, the beta is coming soon (and GA not too far behind that), with even more read-write splitting enhancements.

And if interested in some more about MaxScale, there are some great posts here (some with excellent step-by-step setup instructs):

Hope this helps.


MariaDB 10.0.11 Overview and Highlights

Chris on MySQL - May 30, 2014 - 1:37am GMT

MariaDB 10.0.11 was recently released, and is available for download here:


This is the second GA release of MariaDB 10.0, and 12th overall release of MariaDB 10.0.

This is primarily a bug-fix release.

Here are the main items of note:

  1. Updated TokuDB engine to version 7.1.6
  2. Updated Spider storage engine to version 3.2 (now Gamma)
  3. Updated XtraDB storage engine to version 5.6.17-65.0
  4. Updated InnoDB storage engine to version 5.6.17
  5. Updated performance_schema to version 5.6.17
  6. Updated Connect, and OQGraph engines.
  7. Online ALTER TABLE works for partitioned tables
  8. New system variable default_regex_flags. To make MariaDB RLIKE operator behave in a non-standard but backward compatible way use
    SET @@default_regex_flags='DOTALL';
  9. As per the MariaDB Deprecation Policy, this will be the last release of MariaDB 10.0 for both Ubuntu 12.10 “Quantal” and Mint 14 “Nadia”.

You can read more about the 10.0.11 release here:


And if interested, you can review the full list of changes in 10.0.11 (changelogs) here:


Hope this helps.


Another reason for Xtrabackup error “log block numbers mismatch”

Chris on MySQL - May 7, 2014 - 2:15pm GMT

I ran into an issue the other day where Xtrabackup was not completing and threw the following error:

xtrabackup: error: log block numbers mismatch:
xtrabackup: error: expected log block no. 134860907,
but got no. 176803931 from the log file.
xtrabackup: Error: xtrabackup_copy_logfile() failed.

When researching this error, I found that this error is generally caused when the following 2 conditions must are met, and this is generally when “xtrabackup cannot catch up with log writing activity on the server, so the log wraps around before xtrabackup can copy records before they are overwritten”:

  1. The log block numbers must wrap around, which only happens once per 1 GB of log writes.
  2. The wrap-around point must be between the last checkpoint and the current log tail at the time the backup starts.

Discussed here:

When this does occur as a result of the above (and not a bug), then it’s usually an I/O issue, either on the MySQL-side or the disk-side. One you can check with the /dev/null redirect of the actual data (i.e., if it then completes, perhaps you have some IO issue, so investigate that, and/or perform during a period of low-activity).

In this specific case, however, it was a new version of Xtrabackup, low-load, 6G InnoDB log files, …

So what was the problem?

Here the user was trying to use the “xtrabackup” binary to dump a MySQL 5.6 instance. Once switched to “xtrabackup_56″ binary, as described here, it worked as expected.

Hope this helps.


MariaDB 5.5.37 Overview and Highlights

Chris on MySQL - April 23, 2014 - 3:55pm GMT

MariaDB 5.5.37 was recently released (it is the latest MariaDB 5.5), and is available for download here:


This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a few items worth mentioning:

  • Includes all bugfixes and updates from MySQL 5.5.37
  • XtraDB updated to the version from Percona Server 5.5.36-34.0
  • TokuDB updated to version 7.1.5
  • Default compression for TokuDB is now TOKUDB_ZLIB (instead of TOKUDB_UNCOMPRESSED)
  • The MariaDB Audit Plugin now included.

Given the number of InnoDB and XtraDB fixes (in 5.5.37 and 5.5.36 respectively), if you are using InnoDB or XtraDB+ in MariaDB 5.5, then I would definitely recommend upgrading to MariaDB 5.5.37.

If interested, there is more about the 5.5.37 release here:


And the full list of fixed bugs and changes in MariaDB 5.5.37 can be found here:


Hope this helps.


Syndicate content