Agrégateur de flux

Moving On

Ivan's Blog - septembre 1, 2014 - 6:00am GMT
I have a difficult task of making this post interesting, helpful and personal at the same time. I think the main goal is to balance these aspects, and I really appreciate your comments and suggestions that I will add here.For the busy readers who may be put off by the length of this post, here is a very short summary: I spent 4 wonderful years, first as the head of Field Services, then as a CTO, I believe it is now time for a change, so I am leaving SkySQL. I am leaving behind a great company and very good friends, but I am not disappearing completely, and I will continue supporting the work I started and the projects I created with the help of such great people.For many, leaving a company is not easy, and it is extremely difficult if you have contributed to its creation and development since the beginning. Even more difficult is to depart from ideas and projects that you have shaped and designed, together with the people who have contributed to them and that I am sure they will continue to work on these projects with great success.The reasons for SkySQLIn the past 4 years, I have been asked many times why we had created SkySQL and how SkySQL is different from other providers, such as Percona and Oracle.
Since the beginning, the first and most important objective for SkySQL was to provide the best products and services around MySQL. In order to achieve this objective, we had created a network of partners and we were working closely with them to support our customers in the best possible way. The value added by SkySQL to the offering was a strong team of consultants and architects who could suggest and implement MySQL solutions, and a stellar Technical Support team who could provide the best possible answers to a large variety of technical and consultative issues that customers might encounter.
Having many options to choose from was certainly good, but it introduced another issue: not all the products could work well together. Customers demanded solutions from a single vendor that could go beyond the “typical” MySQL database + backup + monitor: they wanted to have a set of products that was tested and guaranteed to work together. This was the first motivation for the first big effort at SkySQL in terms of products and tools, when we defined the SkySQL Reference Architecture.The Reference Architecture was the result of many hours spent in meetings and solitary thinking in my home office during the Christmas holidays in 2010, when the business slowed down and I could dedicate more CPU cycles to the subject. We worked on the project for 4 months and we launched the Architecture as a concept at the MySQL/Percona Conference in 2011. We demonstrated the SkySQL Reference Architecture with a tool that users could access online, in order to automatically generate and activate a fully functional cluster of MySQL replicated servers with MONyog, MySQL Replication, a cluster software with resource agent in AWS. Severalnines had a similar approach for MySQL Cluster and later for MySQL Replication and Galera, but at that time only SkySQL had the full automation and a selection of different engines and uses, from the configuration to the MySQL prompt. Later, Percona introduced a web tool that could provide an optimised configuration file.The evolution of the Reference Architecture was the SkySQL Data Suite (SDS). The concept was similar, but the main difference was that for the first time we added SkySQL Intellectual Property to MySQL. The suite was packaged with an administration tool that was designed and built by SkySQL. The first target was the Cloud, specifically AWS and OpenStack. The initial idea was to have SDS seamlessly deployed on bare OS, on clouds or in hybrid environments. All the tools have been designed with programmable and user interfaces, in order to satisfy different customers’ needs. An independent presentation of SDS is available here.In 2013, the company merged with Monty Program, and we suddenly found ourselves in a position where software development was a fundamental part of our offering. We moved the focus of the Data Suite to MariaDB and we rebranded it as MariaDB Enterprise,but more importantly, we combined the value and the skills of our services team with the core team of the original development of MySQL. The merge resulted in a company with all the credentials needed to excel and innovate in the MySQL world. But the key question at this point was: is this enough to make MySQL even more successful? Is a better MariaDB (or indeed MySQL) the right answer to the data management needs in 2010s and beyond?The evolution of MySQL and MariaDBThe answer to the previous questions is not surprisingly a “no”. Indeed, users need a better MySQL (or MariaDB). Traditionally, they demanded more performance, more availability and more scalability, and many players have contributed in their own way to the cause.
Still, there is something missing. The competition from NoSQL solutions is, to say the least, intense. It is probably true that the MySQL adoption is not declining (as some analysts say), but the adoption of NoSQL is way bigger in absolute terms. And more important, the majority of the new initiatives and startups that once were the lymph that flowed in the MySQL Community, have now moved to NoSQL.
From a purely technical (and generic) perspective, when MySQL and NoSQL are tested and measured in a fair way, MySQL can provide in many cases better performance and robustness. Scalability, on the other hand, is a big issue as it has always been – it was an issue for bigger servers in the past, it is an issue for distributed systems now. The search for a better scalability is the primary reason why we have created MaxScale.You may have read a lot about MaxScale, or you may want to read more here and here. In simple terms, MaxScale is a highly scalable, lightweight proxy system aimed at distributing and scaling parts of a database server that do not need to reside in its core. There is a similarity to this approach in the NoSQL world and certainly in many home made solitions. The mongos / mongod binomial is a good example of what MaxScale can achieve with MySQL, but this is only half of the story. MaxScale is generic in nature, what makes it a relevant component of the IT infrastucture are its plugins. By loading different plugins you can make MaxScale a proxy for multiple client protocols, or a proxy for geographically replicated servers, or to integrate different replication technologies, and so on.I believe that we need MaxScale for MySQL and MariaDB. Incidentally, Max is the name of Monty’s son, so we have covered all his heirs (at least so far). In designing MaxScale, I wanted to provide a link between a technology that was good for servers available in the 90s and today’s infrastructures.A difficult choice?One might ask, if I feel so strong about MaxScale and its fundamental role, why am I leaving it behind? The fact is, I am not. The project is in good hands, thanks to the great work and dedication from Mark RiddochMassimiliano Pinto and Vilho Raatikka. The concept, the ideas and the architecture are here to stay. MaxScale is shaped today as we – Mark RiddochMassimo Brignoli and I – wanted it, as we have designed it during long hours of work and passionate discussions.
When your kids grow up and are ready to walk alone in the world, you need to let them go. MaxScale can now walk with MySQL and MariaDB, and SkySQL will take a good care of their path together. So now, I may move on and I have time to raise other kids.A look at the futureAs for me, I am technically embracing a wider range of technologies. I will not be focused only to MySQL, but rest assured that these 10 years will always remain in my heart. I will work on IT infrastructures and systems where databases play the central and most important role, but I will look at the customers’ needs as a whole. I will carry on my duty for the MySQL User Group in London, that has now reached the reasonable size of 40-50 attendees per session, every other month. I will not move my MySQL blog, so any MySQL-related post will be available on izoratti.blogspot.com and it will be aggregated on PlanetMySQL. But I will have a collection various topics in my personal blog www.ivanzoratti.com. I will cover more databases, HPC, OpenStack and OSs. I will also have a section dedicated to an important aspect of my life, which is the study of Kung Fu in its inner and outer styles. I started learning Kung Fu almost 30 years ago, first for 12 years, then I abandoned it for another 12 years, until I realised the importance of this practice in my life. I have to thank some of my best friends for that, they really helped me a lot in good and bad times.So, even if I will not wear a T-shirt with a seal (or a sea lion, as it is more fashionable these days), you will probably see me around at conferences and exhibitions, or perhaps you will not see me, but I will work, as I have done in these 10 years, behind the scenes to make MySQL the good and strong database that can help in creating the next Facebook or the next Twitter of this world.All the best to all of you.

Script to manage MaxScale

Karlsson on databases and stuff - août 14, 2014 - 3:34pm GMT
MaxScale 1.0 from SkySQL is now in Beta and there are some cool features in it, I guess some adventurous people has already put it into production. There are still some rough edges and stuff to be fixed, but it is clearly close to GA. One thing missing though are something to manage starting and stopping MaxScale in a somewhat controlled way, which is what this blog is all about.

I have developed two simple scripts that should help you manage MaxScale in a reasonable way, but before we go into the actual scripts, there are a few things I need to tell you. To begin with, if you haven't yet downloaded MaxScale 1.0 beta, you can get it from MariaDB.com, just go to Resources->MaxScale and to get to the downloads you first need to register (which is free). Here are downloads to rpms and source, but if you are currently looking for a tarball, there seems to be none, well actually there is, the first link under "Source Tarball" actually is a binary tarball. I have reported this so by the time you read this, this might have been fixed. Of course you can always get the source from github and build it yourself.

Anyway, for MaxScale to start, you need a configuration file and you have to set the MaxScale home directory. If you are on CentOS or RedHat and install the rpms (which is how I set it us), MaxScale is installed in /usr/local/skysql/maxscale, and this is also what MAXSCALE_HOME needs to be set to. MaxScale can take a configuration file argument, but if this isn't passed, then MAXSCALE_HOME/etc/MaxScale.cnf will be used. In addition, you will probably have to add MAXSCALE_HOME/lib to your LD_LIBRARY_PATH variable.

All in all, there some environment variables to set before we can start MaxScale, and this is the job of the first script, maxenv.sh. For this to work, it has to be placed in the bin directory under MAXSCALE_HOME. In this script we also set MAXSCALE_USER, and this is used by MaxScale start / stop script to be explained later, and this is the linux user that will run MaxScale. You can set this to an empty string to run maxscale as the current user, which is the normal way that MaxScale runs, but in that case you need to make sure that the user in question has write access to MAXSCALE_HOME and subdirectories.

So, here we go, here is maxenv.sh, and you can copy this into the bin directory under your MAXSCALE_HOME directory and use it like that (note that I set the user to run MaxScale to mysql, so if you don't have that user, then create that or modify maxenv.sh accordingly):
#!/bin/bash
#
export MAXSCALE_HOME=$(cd `dirname ${BASH_SOURCE[0]}`/..; pwd)
export MAXSCALE_USER=mysql
PATH=$PATH:$MAXSCALE_HOME/bin
# Add MaxScale lib directory to LD_LIBRARY_PATH, unless it is already there.
if [ `echo $LD_LIBRARY_PATH | awk -v RS=: '{print $0}' | grep -c "^$MAXSCALE_HOME/lib$"` -eq 0 ]; then
   export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$MAXSCALE_HOME/lib
fi
export MAXSCALE_PIDFILE=$MAXSCALE_HOME/log/maxscale.pid


Now it's time for maxctl, which is the script that starts and stops MaxScale, and this also must be placed in the MAXSCALE_HOME/bin directory, and note that this script relies on maxenv.sh above, so to use maxctl you also need maxenv.sh as above. The script is rather long, but there are probably a thing or two missing anyway, but for me this has been useful:

#!/bin/bash
#
# Script to start and stop MaxScale.
#
# Set up the environment
. $(cd `dirname $0`; pwd -P)/maxenv.sh

# Set default variables
NOWAIT=0
HELP=0
QUIET=0
MAXSCALE_PID=0

# Get pid of MaxScale if it is running.
# Check that the pidfile exists.
if [ -e $MAXSCALE_PIDFILE ]; then
   MAXSCALE_PID=`cat $MAXSCALE_PIDFILE`
# Check if the process is running.
   if [ `ps --no-heading -p $MAXSCALE_PID | wc -l` -eq 0 ]; then
      MAXSCALE_PID=0
   fi
fi


# Function to print output
printmax() {
   if [ $QUIET -eq 0 ]; then
      echo $* >&2
   fi
}

# Function to print help
helpmax() {
    echo "Usage: $0 start|stop|status|restart"
    echo "Options:"
    echo "-f - MaxScale config file"
    echo "-h - Show this help"
    echo "-n - Don't wait for operation to finish before exiting"
    echo "-q - Quiet operation"
}


# Function to start maxscale
startmax() {
# Check if MaxScale is already running.
   if [ $MAXSCALE_PID -ne 0 ]; then
      printmax "MaxScale is already running"
      exit 1
   fi

# Check that we are running as root if a user to run as is specified.
   if [ "x$MAXSCALE_USER" != "x" -a `id -u` -ne 0 ]; then
      printmax "$0 must be run as root"
      exit 1
   fi

# Check that we can find maxscale
   if [ ! -e $MAXSCALE_HOME/bin/maxscale ]; then
      printmax "Cannot find MaxScale executable ($MAXSCALE_HOME/bin/maxscale)"
      exit 1
   fi

# Check that the config file exists, if specified.
   if [ "x$MAXSCALE_CNF" != "x" -a ! -e "$MAXSCALE_CNF" ]; then
      printmax "MaxScale configuration file ($MAXSCALE_CNF) not found"
      exit 1
   fi

# Start MaxScale
   if [ "x$MAXSCALE_USER" == "x" ]; then
      $MAXSCALE_HOME/bin/maxscale -c $MAXSCALE_HOME ${MAXSCALE_CNF:+-f $MAXSCALE_CNF}
   else
      su $MAXSCALE_USER -m -c "$MAXSCALE_HOME/bin/maxscale -c $MAXSCALE_HOME ${MAXSCALE_CNF:+-f $MAXSCALE_CNF}"
   fi
}


# Function to stop maxscale
stopmax() {
   NOWAIT=1
   if [ "x$1" == "-n" ]; then
      NOWAIT=0
   fi

# Check that we are running as root if a user to run as is specified.
   if [ "x$MAXSCALE_USER" != "x" -a `id -u` -ne 0 ]; then
      printmax "$0 must be run as root"
      exit 1
   fi

# Check that the pidfile exists.
   if [ ! -e $MAXSCALE_PIDFILE ]; then
      printmax "Can't find MaxScale pidfile ($MAXSCALE_PIDFILE)"
      exit 1
   fi
   MAXSCALE_PID=`cat $MAXSCALE_PIDFILE`

# Kill MaxScale
   kill $MAXSCALE_PID

   if [ $NOWAIT -ne 0 ]; then
# Wait for maxscale to die.
      while [ `ps --no-heading -p $MAXSCALE_PID | wc -l` -ne 0 ]; do
      usleep 100000
      done
      MAXSCALE_PID=0
   fi
}


# Function to show the status of MaxScale
statusmax() {
# Check that the pidfile exists.
   if [ $MAXSCALE_PID -ne 0 ]; then
      printmax "MaxScale is running (pid: $MAXSCALE_PID user: `ps -p $MAXSCALE_PID --no-heading -o euser`)"
      exit 0
   fi
   printmax "MaxScale is not running"
   exit 1
}

# Process options.
while getopts ":f:hnq" OPT; do
   case $OPT in
      f)
         MAXSCALE_CNF=$OPTARG
         ;;
      h)
         helpmax
         exit 0
         ;;
      n)
         NOWAIT=1
         ;;
      q)
         QUIET=1
         ;;
      \?)
         echo "Invalid option: -$OPTARG"
         ;;
   esac
done

# Process arguments following options.
shift $((OPTIND - 1))
OPER=$1

# Check that an operation was passed
if [ "x$1" == "x" ]; then
   echo "$0: your must enter an operation: start|stop|restart|status" >&2
   exit 1
fi


# Handle the operations.
case $OPER in
   start)
      startmax
      ;;
   stop)
      stopmax
      ;;
   status)
      statusmax
      ;;
   restart)
      if [ $MAXSCALE_PID -ne 0 ]; then
         NOWAITSAVE=$NOWAIT
         NOWAIT=0
         stopmax
         NOWAIT=$NOWAITSAVE
      fi
      startmax
      ;;
   *)
      echo "Unknown operation: $OPER. Use start|stop|restart|status"
      exit 1
esac


To use this script, you call it with one of the 4 main operations: start, stop, restart or status. If you are to run as a specific user, you have top run it as root (or using sudo):
sudo /usr/local/skysql/maxscale/maxctl start
Also, there are some options you can pass:
  • -h - Print help
  • -f - Run with the spcified configuration file instead of MAXSCALE_HOME/etc/MaxScale.cnf
  • -n - Don't want for stop to finish before returning. Stopping MaxScale by sending a SIGTERM is how it is to be done, but it takes a short time for MaxScale to stop completely. By default maxctl will wait until MaxScale is stopped before returning, by passing this option MaxScale will return immediately though.
  • -q - Quiet operation
/Karlsson

MariaDB 10.0.13 Overview and Highlights

Chris on MySQL - août 12, 2014 - 7:22pm GMT

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

https://downloads.mariadb.org/mariadb/10.0.13/

This is the fourth GA release of MariaDB 10.0, and 14th overall release of MariaDB 10.0.

This is primarily a bug-fix release.

Here are the main items of note:

  1. InnoDB upgraded to 5.6.19.
  2. XtraDB upgraded to 5.6.19-67.0.
  3. TokuDB upgraded to 7.1.7.
  4. Performance_Schema upgraded to 5.6.20.
  5. Connect engine supports partitioning.
  6. Many plugins have had their maturity level increased (from beta to gamma or from gamma to stable).
  7. filesort-with-small-limit-optimization is now visible through the slow query log and a new status variable, sort_priority_queue_sorts.
  8. New variables aria_pagecache_file_hash_size and key_cache_file_hash_size for determining the number of hash buckets for open and changed files for Aria and MyISAM respectively.

You can read more about the 10.0.13 release here:

https://mariadb.com/kb/en/mariadb-10013-release-notes/

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

https://mariadb.com/kb/en/mariadb-10013-changelog/

Hope this helps.

 

MariaDB 5.5.39 Overview and Highlights

Chris on MySQL - août 6, 2014 - 3:57pm GMT

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

https://downloads.mariadb.org/mariadb/5.5.39/

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:

If interested, the official MariaDB 5.5.39 release notes are here:

https://mariadb.com/kb/en/mariadb/development/release-notes/mariadb-5539-release-notes/

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

https://mariadb.com/kb/en/mariadb/development/changelogs/mariadb-5539-changelog/

Hope this helps.

 

MariaDB 5.5.39 Overview and Highlights

Chris on MySQL - août 6, 2014 - 12:40am GMT

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

https://downloads.mariadb.org/mariadb/5.5.39/

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:

If interested, the official MariaDB 5.5.39 release notes are here:

https://mariadb.com/kb/en/mariadb/development/release-notes/mariadb-5539-release-notes/

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

https://mariadb.com/kb/en/mariadb/development/changelogs/mariadb-5539-changelog/

Hope this helps.

 

MySQL 5.6.20 Overview and Highlights

Chris on MySQL - août 5, 2014 - 4:53pm GMT

MySQL 5.6.20 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there is 1 “Security Fix”, 1 “InnoDB Important Change”, and 7 “Functionality Added or Changed” fixes, all of which should be read in case they might affect you (though for this release, these mostly appear to be minor – some [default] changes, build notes/changes, and deprecations):

  1. Security Fix: The linked OpenSSL library for the MySQL 5.6 Commercial Server has been updated from version 1.0.1g to version 1.0.1h. Versions of OpenSSL prior to and including 1.0.1g are reported to be vulnerable to CVE-2014-0224. This change does not affect the Oracle-produced MySQL Community build of MySQL Server 5.6, which uses the yaSSL library instead.
  2. InnoDB Important Change: Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation. As a result of the redo log BLOB write limit introduced for MySQL 5.6, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). Failing to do so could result in “Row size too large” errors. No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data. (Bug #69477)
  3. Replication: The new system variable binlog_impossible_mode controls what happens if the server cannot write to the binary log, for example, due to a file error. For backward compatibility, the default for binlog_impossible_mode is IGNORE_ERROR, meaning the server logs the error, halts logging, and continues updates to the database. Setting this variable to ABORT_SERVER makes the server halt logging and shut down if it cannot write to the binary log. (Bug #51014)
  4. CMake support was updated to handle CMake version 3.
  5. New Debian7, Ubuntu12.04, and Ubuntu14.04 distribution support that was introduced with 5.6.17 now comes with the platform-specific packaging source placed under the packaging directory, in the deb-precise, deb-wheezy, and deb-trusty directories.
  6. Support for LinuxThreads has been removed from the source code. LinuxThreads was superseded by NPTL in Linux 2.6. (Bug #72888)
  7. By default, mysql_install_db creates a my.cnf file in the installation base directory using a template. This may be undesireable for some deployments. To enable this behavior to be suppressed, mysql_install_db now supports a –keep-my-cnf option to preserve any existing my.cnf file and not create a new my.cnf file. (Bug #71600)
  8. The mysqlhotcopy utility is now deprecated and will be removed in a future version of MySQL. Among the reasons for this: It works only for the MyISAM and ARCHIVE storage engines; it works on Unix but not Windows. Alternatives include mysqldump and MySQL Enterprise Backup.
  9. The timed_mutexes system variable has no effect and is deprecated.

In addition to those, there were 52 other bug fixes:

  • 11 InnoDB
  • 09 Replication
  • 01 Partitioning
  • 31 Miscellaneous

There were 2 regression bugs fixed, so check if they might have affected you:

  1. InnoDB: A regression introduced in MySQL 5.6.5 would cause full-text search index tables to be created in the system tablespace (space 0) even though innodb_file_per_table was enabled.
  2. When a SELECT included a derived table in a join in its FROM list and the SELECT list included COUNT(DISTINCT), the COUNT() returned 1 even if the underlying result set was empty.

Conclusions:

I’ve not too much to say about this release regarding whether one should upgrade or not. I will say that if you are running a *Commercial* version of MySQL 5.6, pre-5.6.20, and you use SSL, then you should upgrade. Aside from that, it more depends if you’re affected by one of these bugs or not.

The full 5.6.20 changelogs can be viewed here (which has more details about all of the bugs listed above):

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html

Hope this helps.

 

What is HandlerSocket? And why would you use it? Part 1

Karlsson on databases and stuff - août 4, 2014 - 11:16am GMT
HandlerSocket is included with MariaDB and acts like a simple NoSQL interface to InnoDB, XtraDB and Spider and I will describe it a bit more in this and a few upcoming blogs.

So, what is HandlerSocket? Adam Donnison wrote a great blog on how to get started with it, but if you are developing MariaDB applications using C, C++, PHP or Java what good does HandlerSocket do you?

HandlerSocket in itself is a MariaDB plugin, of a type that is not that common as is is a daemon plugin. Adam shows in his blog how to enable it and install it, so I will not cover that here. Instead I will describe what it does, and doesn't do.

A daemon plugin is a process that runs "inside" the MariaDB. A daemon plugin can implement anything really, as long as it is relevant to MariaDB. One daemon plugin is for examples the Job Queue Daemon and another then is HandlerSocket. A MariaDB Plugin has access to the MariaDB internals, so there is a lot of things that can be implemented in a daemon plugin, even if it is a reasonable simple concept.

Inside MariaDB there is an internal "handler" API which is used as an interface to the MariaDB Storage Engines, although not all engines supports this interface. MariaDB then has a means to "bypass" the SQL layer and access the Handler interface directly, and this is done by using the HANDLER commands with MariaDB. Note that we are not talking about a different API to uise the handler commands, instead they are executed using the same SQL interface as you are used it's, it's just that you use a different set of commands. One limitation of these commands is that they only allow reads, even though the internal Handler interface supports writes as well as reads.

When you use the HANDLER commands, you have to know not only the tables name that you access, but also the index you will be using when you access that table (assuming then you want to use an index, but you probably do). A simple example of using the HANDLER commands follows here:

# Open the orders table, using the alias o
HANDLER orders OPEN AS o;

# Read an order record using the primary key.
HANDLER o READ `PRIMARY` = (156);

# Close the order table.
HANDLER o CLOSE;

In the above example, I guess this is just overcomplication something basically simple, as all this does is the same as
SELECT * FROM orders WHERE id = 156;

The advantage of using the handler interface though is performance, for large datasets using the Handler interface is much faster.

All this brings up three questions:
  • First, if we are bypassing the SQL layer, by using the HANDLER Commands, would it not be faster to bypass the SQL level protocol altogether, and just use a much simple protocol?
  • Secondly, while we are at it, why don't we allow writes, as this is the biggest issues, we can always speed reads by scaling out anyway?
  • And last, how much faster is this, really?
The answer to the first two questions then is the Handler Socket plugin, which was the whole deal with this blog, as this use a separate, very simple, protocol and allows writes! For the third question, this is where I come in, I have done some simple INSERT style benchmarks using HandlerSocket, and I have some results for you in my next blog. So don't touch that dial, I'll be right back!

/Karlsson

Troubleshooting TokuDB Corruption

Chris on MySQL - août 2, 2014 - 3:42am GMT

I recently ran across some TokuDB table corruption, which was not easily identifiable at first, and the error log entry was not too verbose either, so I wanted to share that experience here.

Basically, TokuDB crashed, and then mysqld had problems restarting afterward. Just for reference, the error log had the following in the stack trace:

/usr/lib64/mysql/plugin/ha_tokudb.so(+0x71c48)[0x7fb25be75c48]
/usr/lib64/mysql/plugin/ha_tokudb.so(+0x71cbd)[0x7fb25be75cbd]
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z29toku_deserialize_bp_from_disk...
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z23toku_ftnode_pf_callback...
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z30toku_cachetable_pf_pinned_pair..
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z24toku_ft_flush_some_child...
/usr/lib64/mysql/plugin/ha_tokudb.so(_Z28toku_ftnode_cleaner_callback...
/usr/lib64/mysql/plugin/ha_tokudb.so(_ZN7cleaner11run_cleaner...
/usr/lib64/mysql/plugin/ha_tokudb.so(+0xcfeee)[0x7fb25bed3eee]
/lib64/libpthread.so.0(+0x79d1)[0x7fb39310b9d1]
/lib64/libc.so.6(clone+0x6d)[0x7fb391828b5d]

The crash was not really the problem here; that is another story.

But rather mysqld not starting back up was an issue.

Given this crash, and mysqld not starting back up, we needed to check every fractal tree file in the data directory.

I’m not an expert with this, but what I did, and you may find helpful too, is to iteratively check each fractal tree file with “tokuftdump –nodata tokutablename”.

If it does not return anything, then there is a problem with the table – not too scientific – but was effective in this case.

If you have just a couple files to check, this is easy enough to run per file. If there are a lot to check, then a simple shell loop with the check (and output the name at the time) can help.

Any corrupt keys should be then dropped and recreated.

That should get you back going.

On a side note, when looking more into “tokuftdump –nodata”, I did run across these 2 commands (one-liners), which allow you to check the compression format of the tables also using tokuftdump –nodata. Thus, it illustrates the use of this command for another feature, and it’s easily modifiable if you’re interested:

Pre-MariaDB 5.5.37:

mysql -sNe 'SELECT dictionary_name, internal_file_name FROM
information_schema.tokudb_file_map WHERE dictionary = "main"
OR dictionary LIKE "key-%"'
|perl -F'\t' -ane '@out=qx(tokuftdump --nodata --rootnode /var/lib/mysql/$F[1]);
foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/)
{ print $F[0]."\t".$1."\n"; last } }'

MariaDB 5.5.37+ (b/c “dictionary” column changed to “table_dictionary_name”):

mysql -sNe 'SELECT dictionary_name, internal_file_name FROM
information_schema.tokudb_file_map WHERE table_dictionary_name = "main"
OR table_dictionary_name LIKE "key-%"'
|perl -F'\t' -ane '@out=qx(tokuftdump --nodata --rootnode /var/lib/mysql/$F[1]);
foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/)
{ print $F[0]."\t".$1."\n"; last } }'

For reference, these are documented here.

Hope this helps.

 

Troubleshooting TokuDB ERROR 1126 – API Version Mismatch or bitmap_free

Chris on MySQL - août 2, 2014 - 3:40am GMT

When setting up TokuDB, you may encounter error 1126.

I’ve seen 2 recent invocations of it, so I wanted to share them both here in case you run into this issue:

MariaDB [(none)]> install soname 'ha_tokudb';
ERROR 1126 (HY000): Can't open shared library
'/usr/lib/mysql/plugin/ha_tokudb.so' (errno: 2, undefined symbol: bitmap_free)
MariaDB [(none)]> install soname 'ha_tokudb';
ERROR 1126 (HY000): Can't open shared library 'ha_tokudb.so'
(errno: 8, API version for STORAGE ENGINE plugin TokuDB not
supported by this version of the server)

The latter is a bit more descriptive, but the former is fairly cryptic.

Given the latter, as you may have guessed it, if you run into either of these, you have the wrong version of ha_tokudb.so in your plugin directory – that is, it is not the correct version for your MariaDB/MySQL.

In both cases, 10.0.11 was used. In the first case, the ha_tokudb.so was from 5.5.37. In the second case, the ha_tokudb.so was from 10.0.12.

Hope this helps.

 

Disabling Transparent Hugepages for TokuDB

Chris on MySQL - août 2, 2014 - 1:37am GMT

If you want to use TokuDB with MariaDB, MySQL, or Percona Server, you will need to disable support for transparent hugepages in Linux.

Fortunately, this is very easy to check, and to change.

An easy way to check is with:

cat /sys/kernel/mm/transparent_hugepage/enabled

This will return something like:

[always] madvise never

Note the word surrounded by “[]” is what this option is set to. So the above is set to “always”. To disable it, we want it set to “never”.

I’ve found the easiest way to change/set this is to add the below to your /etc/rc.local file (and then reboot your system):

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi

There is another method listed here if you use grub to boot your kernel.

If you try to enable TokuDB without first changing this, then from the command line, you would see the following error:

MariaDB [(none)]> install soname 'ha_tokudb';
ERROR 1123 (HY000): Can't initialize function 'TokuDB';
Plugin initialization function failed.

Or, in the error log, you would see this *slightly* more clear message:

TokuDB: Transparent huge pages are enabled, according to
/sys/kernel/mm/transparent_hugepage/enabled.  TokuDB will be disabled.
To use TokuDB disable huge pages in your kernel or, for testing, set
the environment variable TOKU_HUGE_PAGES_OK to 1
140714 17:25:57 [ERROR] TokuDB:  Huge pages are enabled, disable them before continuing
140714 17:25:57 [ERROR] Plugin 'TokuDB' init function returned error.
140714 17:25:57 [ERROR] Plugin 'TokuDB' registration as a STORAGE ENGINE failed.

If interested in how to enable TokuDB in MariaDB, please visit here or here.

Or, if you’re interested in why TokuDB requires hugepages to be disabled, PeterZ wrote a nice post about that here.

Hope this helps.

 

Enabling TokuDB in MariaDB is a Breeze

Chris on MySQL - août 2, 2014 - 1:37am GMT

TokuDB is gaining more and more popularity and many people are finding it very helpful for certain cases.

Using/enabling it in some distributions can be quite a pain, but enabling it in MariaDB is easy as 1-2-…, well, only 1-2, since that’s all there is to it!

1. Ensure you have the correct MariaDB version:

  • MariaDB 5.5.36+
  • MariaDB 10.0.9+
  • Note: Linux 64-bit systems only – specific packages include: Ubuntu, Debian, Fedora, CentOS, Red Hat
  • Note: If using the Linux tarball – it must be the version built with glibc 2.14+

2. Run this command:

INSTALL SONAME 'ha_tokudb';

or update my.cnf file with:

[mysqld]
plugin-load=ha_tokudb

There is one requirement from TokuDB, which is to have transparent hugepages disabled on Linux, so some folks may indeed have a 3rd step, if you have this enabled. Luckily, this is very easy to check and change as well.

When you have it enabled correctly, you will see it listed when invoking SHOW ENGINES:

MariaDB [(none)]> show engines;
+--------+---------+------------------------------------------------...
| Engine | Support | Comment                                        ...
+--------+---------+------------------------------------------------...
| ...
| TokuDB | YES     | Tokutek TokuDB Storage Engine with Fractal Tree...
| ...

If interested, there are more details and specifics on enabling TokuDB in MariaDB here. (For instance, if you’re running 5.5.33-5.5.35 or 10.0.5-10.0.8, you may also be able to enable TokuDB, but in a slightly different manner. But there are a few more other details as well.)

Hope this helps.

 

MySQL 5.5.39 Overview and Highlights

Chris on MySQL - août 1, 2014 - 9:27pm GMT

MySQL 5.5.39 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://downloads.skysql.com/archive/index/p/mysql/v/5.5.39

This release, similar to the last 5.5 release, is mostly uneventful.

There were two “Functionality Added or Changed” and 24 bugs fixed.

The “Functionality Added or Changed” changes are:

  • CMake support was updated to handle CMake version 3.
  • The timed_mutexes system variable has no effect and is deprecated.

Out of the 24 bugs, most seemed rather minor or obscure, but here are the ones I think are worth noting (crashing, security, wrong results, deadlock):

  • InnoDB: Opening a parent table that has thousands of child tables could result in a long semaphore wait condition.
  • Partitioning: Selecting from a table having multiple columns in its primary key and partitioned by LIST COLUMNS(R), where R was the last (rightmost) column listed in the primary key definition, returned an incorrect result. (Bug #71095)
  • Replication: When using row-based replication, updating or deleting a row on the master that did not exist on the slave led to failure of the slave when it tried to process the change. This problem occurred with InnoDB tables lacking a primary key. (Bug #72085)
  • Replication: A group of threads involved in acquiring locks could deadlock in a certain scenario. (Bug #69954)
  • ALTER TABLE on a partitioned table could result in the wrong storage engine being written into the table’s .frm file and displayed in SHOW CREATE TABLE.
  • MyISAM temporary files could be used to mount a code-execution attack.
  • An assertion could be raised when creating a index on a prefix of a TINYBLOB or GEOMETRY column in an InnoDB column.
  • Deadlock could occur if three threads simultaneously performed INSTALL PLUGIN, SHOW VARIABLES, and mysql_change_user(). (Bug #71236, Bug #72870)
  • MySQL did not compile with Bison 3. A workaround is to downgrade to Bison 2. (Bug #71250)

For reference, the full 5.5.39 changelog can be viewed here:

http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-39.html

Hope this helps.

 

MariaDB 10.1.0 Overview and Highlights

Chris on MySQL - juillet 19, 2014 - 12:37am GMT

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

https://downloads.mariadb.org/mariadb/10.1.0/

This is the first alpha release of MariaDB 10.1, so there are a lot of new changes and functionalities added, which cover a wide variety of areas such as: Performance, InnoDB/XtraDB, WebScaleSQL, Optimizer, Security, Storage Engine functionality, & Administration Improvements.

These are 9 of the most notable changes in MariaDB 10.1.0 (but do check out the release notes and changelogs below for the full list of changes):

  1. InnoDB: Allow > 16K pages on InnoDB – InnoDB now allows page size to be configured as 16K, 32K and 64K. Note that single row size must be < 16K. This feature will allow especially more blob columns to be created.
  2. InnoDB: Force PK option – Added a new dynamic configuration variable innodb_force_primary_key default off. If option is true, create table without primary key or unique key where all keyparts are NOT NULL is not accepted. Instead an error message is printed. Variable value can be changed with set global innodb_force_primary_key = .
  3. Security: Enhance security using special compilation options – MariaDB is now compiled with security hardening options by default. It is an additional protection layer that makes new, yet unknown, security vulnerabilities more difficult to exploit.
  4. Optimizer: ANALYZE $stmt – Check how close the optimizer’s estimates about the query plan are to the reality. (more about this here also)
  5. WebScaleSQL: Added numerous improvements from WebScaleSQL (including MDEV-6314, MDEV-6315, MDEV-6323, MDEV-6325, MDEV-6329, and more).
  6. Performance: Disable Performance Schema by default.
  7. Performance: Merge 10.0.10-FusionIO to 10.1 – Atomic writes, multi-threaded flushing and page compression are available for Fusion-IO devices in 10.1.0.
  8. Storage Engine: Don’t create frm files for temporary tables – Temporary tables no longer create frm files on disk. Which means that if the temporary table is created in the MEMORY engine, it will not touch the disk at all.
  9. Administration: GUI-friendly cmake options to enable/disable plugins – MariaDB now uses PLUGIN_xxx cmake options to enable or disable plugins, not a combination of WITH_xxx, WITHOUT_xxx, WITH_PLUGINX_xxx, WITHOUT_PLUGIN_xxx, WITH_xxx_STORAGE_ENGINE, WITHOUT_xxx_STORAGE_ENGINE.

You can read more about the 10.1.0 release here:

https://mariadb.com/kb/en/mariadb-1010-release-notes/

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

https://mariadb.com/kb/en/mariadb-1010-changelog/

Hope this helps.

 

MariaDB Replication, MaxScale and the need for a binlog server

Karlsson on databases and stuff - juillet 11, 2014 - 6:24pm GMT
IntroductionThis is an introduction to MariaDB Replication and to why we need a binlogs server and what this is. The first part is an introduction to replication basics, and if you know this already, then you want want to skip past the first section or two.
MariaDB ReplicationMySQL and MariaDB has a simple but very effective replication system built into it. The replication system is asynchronous and is based on a pull, instead of a push, system. What this means in short is that the Master keeps track of the DML operations and other things that might change the state of the master database and this is stored in what is called the binlog. The slave on the other hand is responsible for getting the relevant information from the master to keep up to speed. The binlogs consist of a number of files that the master generates, and the traditional way of dealing with slaves is to point them to the master, specifying a starting point in the binlogs consisting of a filename and a position.

When a slave is started it gets the data from the binlogs, one record at the time, from the given position in the master binlogs and in the process updates the current binlogs file and position. So the master keep track of the transactions and the slave follows behind as fast as it can. The slave has two types of threads, the IO thread that gets data from the master and to a separate relay log on the slave, and an SQL thread that applies the data from the relay log to the slave database.
This really is less complicated than it sounds, in a way, but the implementation of it on the other hand is probably more complicated than one might think. There are also some issues with this setup, some which is fixed by the recent GTID implementation in MySQL 5.6 and more significantly in MariaDB 10.

In a simple setup with just 1 master and a few slaves, this is all there is to it: Take a backup from the master and take to keep track of the binlogs position when this is done, then recover this backup to a slave and then set the slave starting position at the position when the backup was executed. Now the slave will catch up with the operations that has happened since the backup was run and eventually it will catch up with the master and then poll for any new events.

One that that is not always the case with all database systems is that the master and the slave are only different in the sense of the configuration. Except this, these servers run the same software and the same operations can be applied to them, so running DML on a slave is no different than on a master, but if there is a collision between some data that was entered manually on the slave and some data the arrives through replication, say there is a duplicate primary key, then replication will stop.
What do we use replication forReplication is typically used for one or more of three purposes:
•    Read Scale-out - In this case the slaves are used for serving data that is read, whereas all writes go to the master. As in most web applications there is a much larger amount of reads than writes, this makes for good scalability and we have fewer writes to be handled by the single master, whereas the many reads can be server by one or more slaves.
•    Backup - Using a slave for backup is usually a pretty good idea. This allows for cold backups even, as if we shut down the slave for backing it up, it will catch up with the master once restarted after the backup is done. Having a full database setup on a slave for backup also means that recovery times of we need to do that, is fast and also allows for partial recovery if necessary.
•    High Availability - As the master and Slave are kept in sync, at least with some delay, one can sure use the slave to fail over to should the master fail. The asynchronous nature of replication does mean though that failover is a bit more complex than one might think. There might be data in the relay slave log that has not yet been applied and might cause issues when the slave is treated as a master. Also, there might be data in the binlogs on the master which means that when the master is again brought on-line, it might be out of sync with the slave: some data that never was picked up by the slave might be on the master and data that entered the system after the slave was switched to a master is not on the old master.
The nature of replicated dataIn the old days, the way replication worked was by just sending any statement that modified data on the master to the slave. This way of working is still available, but over time it was realized that this was a bit difficult, in particular with some storage engines.

This led to the introduction of Row-based Replication (RBR) where the data to be replicated is transferred not as a SQL statement but as a binary representation of the data to be modified.

Replicating the SQL statement is called Statement Based Replication (SBR). An example of a statement that can cause issues when using SBR is:
DELETE FROM test.tab1 WHERE id > 10 LIMIT 5
In this case RBR will work whereas when using statement based replication we cannot determine which rows will be deleted. There are more examples of such non-deterministic SQL statements where SBR fails but RBR works.

A third replication format mode is available, MIXED, where MariaDB decided ona statement by statement base which replication format is best.
Scaling replicationEventually many users ended up having many slaves attached to that single master. And for a while, this was not a big issue, the asynchronous nature of things means that the load on the master was limited when using replication, but with enough nodes, eventually this turned in to being an issue.

The solution then was to introduce an "intermediate master". This is a slave that is also a master to other slaves, and this is configured having log_slave_updates on, which means that data that is applied on the slave from the relay log and into the slave, are also written to the binlog.

This is a pretty good idea, but there are some issues also. To begin with, on the intermediate master, data has to be written several times, once in the relay log, once in the database (and if InnoDB is used, a transaction log is also written) and then we have to write it to the binlog.

Another issue that is in effect here is the single threaded nature of replication (this is different in MariaDB 10 and MySQL 5.7 and up), which means that a slave on a master that runs many threads, might get into a situation where the slave can't keep up with the master, even though the slave is similarly configured as the master. Also, a run running statement on the master will hold up replication for as long as that statement runs on the slave, and if we have an intermediate master, then the delay will be doubled (once on the intermediate master and once on the actual slave).

The combined effect of the duplication of the delay and the requirement to write data so many times, leads to the result that an intermediate master maybe isn't such a good idea after all.
As for the replication use-cases, intermediate masters are sometimes used as alternative masters when failing over. This might seems like a good idea, but the issue is that the binlogs on the intermediate master doesn't look the same as the binlogs on the actual master. This is fixed by using Global Transaction IDs though, but these have different issues and unless you are running MariaDB 10 or MySQL 5.6, this isn't really an option (and even with MySQL 5.6, there are big issues with this).

What we need then is something else. Something that is a real intermediate master. Something that looks like a slave to the master and as a master to the slave, but doesn't have to write data three times first and that doesn't have to apply all the replication data itself so it doesn't introduce delays into the replication chain.

The slave that attaches to this server should see the same replication files as it would see it it connected to the real master.
MaxScale and the Plugin architectureSo let's introduce MaxScale then, and the plugin architecture. MaxScale has been described before, but one that that might not be fully clear is the role of the plugins. MaxScale relies much more on the plugins that most other architectures, fact is, without the plugins, MaxScale can't do anything, everything is a plugin!

The MaxScale core is a multi-threaded epoll based kernel with 5 different types of plugins (note that there might be more than one plugin of each type, and this is mostly the case actually:
•    Protocols - These implement communication protocols, including debugging and monitoring protocols. From this you realize that without appropriate protocol plugins, MaxScale will not be able to be accessed at all, so these modules are key. Among the current protocols are MariaDB / MySQL Client and Server protocols.
•    Authentication - This type of plugin authenticates users connecting to MaxScale. Currently MariaDB / MySQL Authentication is supported.
•    Router - This is a key type of module that determines how SQL traffic is routed an managed.
•    Filter - This is an optional type of pluging there the SQL traffic can be modfied, checked or rejected,
•    Monitor - This type of modules is there to monitor the servers that MaxScale connects to, and this data is used by the routing mode.

Before we end this discussion on MaxScale, note that there might be several configurations through one single MaxScale setup, so MaxScale can listen to one prot for one set up servers and routine setup, and on another port for a different setup.

With this we have an idea how MaxScale work, so let's see if we can tie it all up.
MaxScale as a Binlog serverAs can be seen from the description of MaxScale a lot of what is needed to create a Binlog server to use as an intermediate server for slaves is there. What is needed is a router module that acts as a slave to the assigned master, downloads the binlogs from there, using the usual MariaDB / MySQL Replication protocol. This routing plugin also needs to serve the slaves with the downloaded binlogs files. In theory, and also in practice, the slaves will not know if it is connected to the real master or to MaxScale.

Using MaxScale this way as an intermediate Master, a slave that connects to the MaxScale can work from the same Binlog files and positions as when connected directly to the master, as the files are the same for all intents and purposes. There will be no extra delays for long running SQL statements as these aren't applied on MaxScale, the replication data is just copied from the master, plain and simple. As for parallel slaves, this should work better in when using MaxScale as a Binlog server, but this is yet to be tested.

So there should be many advantages to using MaxScale as a binlog server compared to using an intermediate MariaDB / MySQL server. On the other hand, this solution is not for everyone, many just doesn't drive replication that hard that the load on the master is an issue so that an intermediate Master is requited. On the other, many use an Intermediate Master also for HA, and in this case it would have be advantageous to use MaxScale instead of that Intermediate master, the latter which could still server the role as a fail-over HA server.

Now, there one issue with all of this that many of you might have spotted: That cool Binlog server plugin module for MaxScale doesn't exists. Well, I am happy to say that you are wrong, it does exist and it works. A Pilot for such a module has been developed by SkySQL together with Booking.com that had just this need for an intermediate server that wasn't just yet another MariaDB / MySQL server. For the details on the specific usecase, see the blog by Jean-François Gagné.

MariaDB 10.0.12 Overview and Highlights

Chris on MySQL - juin 20, 2014 - 7:06pm GMT

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

https://downloads.mariadb.org/mariadb/10.0.12/

This is the third GA release of MariaDB 10.0, and 13th overall release of MariaDB 10.0.

This is primarily a bug-fix release.

Here are the main items of note:

  1. Updated Spider storage engine to version 3.2.4.
  2. Updated Connect engine.
  3. Updated pcre to version 8.35.
  4. Exists-to-In optimization is now enabled by default.
  5. performance_schema is now disabled by default.

You can read more about the 10.0.12 release here:

https://mariadb.com/kb/en/mariadb-10012-release-notes/

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

https://mariadb.com/kb/en/mariadb-10012-changelog/

Hope this helps.

 

MyQuery 3.5.5 Released

Karlsson on databases and stuff - juin 16, 2014 - 1:10pm GMT
I have just released version 3.5.5 of MyQuery, which is a minor feature, cleanup and bugfix release. If you don't know MyQuery since before, this is an Open-Source Windows based MySQL and MariaDB ad-hoc query tool. What makes MyQuery slightly different from all the other similar tools is that MyQuery has a focus on SQL-scripting, allowing statements in a script to be run one at the time, to restart a script where it left of in the case of an error and some other features like this. MyQuery features colour coded syntax as it uses Scintilla for editing, and this is highly configurable. Also, multiple editing tabs are supported. Another thing that makes MyQuery stand out a bit is that it is highly flexible. If you have SQL statements that you run often to monitor the state of the server or your application, then it is real easy to implement this as a simple tool accessible from the MyQuery menu.

There is much more to MyQuery than this, so if you think this sounds cool, then download it from sourceforge and give it a shot, you might like it!

/Karlsson

MySQL 5.5.38 Overview and Highlights

Chris on MySQL - juin 13, 2014 - 2:28am GMT

MySQL 5.5.38 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://downloads.skysql.com/archive/index/p/mysql/v/5.5.38

This release, similar to the last 5.5 release, is mostly uneventful.

Out of the mere 13 bugs, most were benign, but I thought there were two worth mentioning (just because one is a slight performance improvement for InnoDB, and the other relates to slave consistency, so always important to see if it could affect you):

  1. InnoDB: For each insert, memset would be called three times to allocate memory for system fields. To reduce CPU usage, the three memset calls are now combined into a single call. (Bug #17858679, Bug #71014)
  2. Replication: When used on a table employing a transactional storage engine, a failed TRUNCATE TABLE was still written to the binary log and thus replayed on the slave. This could lead to inconsistency when the master retained data that was removed on the slave. Now in such cases TRUNCATE TABLE is logged only when it executes successfully. (Bug #17942050, Bug #71070)

For reference, the full 5.5.38 changelog can be viewed here:

http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-38.html

Hope this helps.

 

MariaDB 5.5.38 Overview and Highlights

Chris on MySQL - juin 12, 2014 - 5:11am GMT

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

https://downloads.mariadb.org/mariadb/5.5.38/

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.38
  • XtraDB updated to the version from Percona Server 5.5.37-35.0
  • TokuDB updated to version 7.1.6
  • MariaDB on Power: With this release of MariaDB we’re introducing binary tarball builds created on a Power7 box running Red Hat 6. They’re listed on the downloads page in the “OS/CPU” column as “Linux ppc64″. We welcome your feedback on these new packages!

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

https://mariadb.com/kb/en/mariadb-5538-release-notes/

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

https://mariadb.com/kb/en/mariadb-5538-changelog/

Hope this helps.

 

MySQL 5.6.19 Overview and Highlights

Chris on MySQL - juin 11, 2014 - 5:26pm GMT

MySQL 5.6.19 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

I should actually call this post “5.6.18 and 5.6.19 Overview and Highlights”.

The 5.6 “Release Notes” Index provides an entry/changelog for 5.6.18 and says it was released 2014-04-11. However, it’s not available in the community download archives. This isn’t mentioned in the 5.6.18 changelogs, but it is in the 5.6.19 changelogs, where it says:

“There is no MySQL Community Server 5.6.18. That version number was used for an out-of-schedule release of the Enterprise Edition to address the OpenSSL ‘Heartbleed’ issue. This issue did not affect the Community Edition because it uses yaSSL, not OpenSSL, so a new release of the Community Server was not needed, and 5.6.17 is followed by 5.6.19.”

While Heartbleed did not affect the *community* versions of MySQL 5.6, it does affect the *commercial* versions of MySQL 5.6 (less than 5.6.18). This is because the *community* version of MySQL 5.6 was built using yaSSL (unaffected SSL library), whereas the *commercial* version of MySQL 5.6 was build using OpenSSL (vulnerable SSL library). So take note of this if you’re running *commercial* MySQL 5.6.

There is also an important “known limitation” of this release, which affects MySQL 5.6.10 through and including 5.6.18, which is:

“If you have InnoDB tables with full-text search indexes and you are upgrading from MySQL 5.6.10 to a MySQL version up to and including MySQL 5.6.18, the server will fail to start after the upgrade (Bug#72079). This bug is fixed in MySQL 5.6.19. As a workaround, remove full-text search indexes prior to upgrading and rebuild full-text search indexes after the upgrade is completed.”

So beware of this if you’re running 5.6.10+, using InnoDB full-text search indexes, and plan on upgrading to 5.6.11 – 5.6.18.

There was also one other bug fixed in the 5.6.18 release notes, but just a rather obscure one related to running a collated subquery on an ARCHIVE table containing an AUTO_INCREMENT column.

Now, as for 5.6.19, there are more fixes:

2 Functionality Added or Changed, which all seem quite minor:

  1. The obsolete and unmaintained charset2html utility has been removed from MySQL distributions. (Bug #71897, Bug #18352347)
  2. The mysqlbug, mysql_waitpid, and mysql_zap utilities have been deprecated and will be removed in MySQL 5.7.

And I counted 32 bug fixes (6 InnoDB, 7 Replication, and 19 misc. bugs).

I found the more important/noteworthy bug fixes to be:

  • InnoDB: After upgrading from 5.6.10 to MySQL versions up to and including MySQL 5.6.18, InnoDB would attempt to rename obsolete full-text search auxiliary tables on server startup, resulting in an assertion failure. (Bug #18634201, Bug #72079)
  • InnoDB: The fix for Bug#17699331 caused a high rate of read/write lock creation and destruction which resulted in a performance regression. (Bug #18345645, Bug #71708). This regression bug affects versions 5.6.16 – 5.6.18.
  • InnoDB: For each insert, memset would be called three times to allocate memory for system fields. To reduce CPU usage, the three memset calls are now combined into a single call. (Bug #17858679, Bug #71014)
  • InnoDB: Enabling the InnoDB Table Monitor would result in a ib_table->stat_initialized assertion failure. (Bug #17039528, Bug #69641)
  • InnoDB: With innodb_max_dirty_pages_pct=0 buffer pool flushing would not be initiated until the percentage of dirty pages reached at least 1%, which would leave up to 1% of dirty pages unflushed. (Bug #13029450, Bug #62534)
  • Replication: Log rotation events could cause group_relay_log_pos to be moved forward incorrectly within a group. This meant that, when the transaction was retried, or if the SQL thread was stopped in the middle of a transaction following one or more log rotations (such that the transaction or group spanned multiple relay log files), part or all of the group was silently skipped. This issue has been addressed by correcting a problem in the logic used to avoid touching the coordinates of the SQL thread when updating the log position as part of a relay log rotation whereby it was possible to update the SQL thread’s coordinates when not using a multi-threaded slave, even in the middle of a group. (Bug #18482854)
  • Replication: In certain cases, the server mishandled triggers and stored procedures that tried to modify other tables when called by CREATE TABLE … SELECT. This is now handled correctly as an error. (Bug #18137535)
  • Replication: When used on a table employing a transactional storage engine, a failed TRUNCATE TABLE was still written to the binary log and thus replayed on the slave. This could lead to inconsistency when the master retained data that was removed on the slave. Now in such cases TRUNCATE TABLE is logged only when it executes successfully. (Bug #17942050, Bug #71070)
  • For indexes on prefixes or character string columns, index corruption could occur for assignment of binary data to the column due to improper character counting. (Bug #18359924)
  • mysqldump could create table definitions in the dump file that resulted in Too many columns errors when reloading the dump file. (Bug #17477959)
  • On Windows, REPAIR TABLE and OPTIMIZE TABLE failed for MyISAM tables with .MYD files larger than 4GB. (Bug #69683, Bug #17235179)

Conclusions:

  • If you’re running 5.6 *commercial*, and not running 5.6.18 or 5.6.19, you need to upgrade now.
  • If you’re running 5.6.10 through 5.6.18 and using InnoDB full-text indexes, and if you upgrade to a 5.6 version less than 5.6.19, you will encounter problems (though there is a work-around mentioned above).
  • The InnoDB fixes were somewhat specific, so I would only consider the regression bug that affected 5.6.16 – 5.6.18 to be an overall worrier, but read through them to be sure none of the others affect you.
  • Similarly, the couple replication fixes were also somewhat specific, so read through those to see if they could affect you.

The full changelogs can be viewed here (which has more details about all of the bugs listed above), 5.6.18 and 5.6.19 listed respectively:

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-18.html
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-19.html

Hope this helps.

 

MariaDB is the default implementation of MySQL in Red Hat Enterprise Linux 7

Chris on MySQL - juin 11, 2014 - 3:23am GMT

It was announced that MariaDB is now the default implementation of MySQL in the newly released Red Hat Enterprise Linux 7.

News of the Red Hat Enterprise Linux 7 can be found here.

And mention of MariaDB replacing MySQL as the default implementation is referenced in their release notes here.

The full change log entry reads:

MariaDB 5.5

  • MariaDB is the default implementation of MySQL in Red Hat Enterprise Linux 7. MariaDB is a community-developed fork of the MySQL database project, and provides a replacement for MySQL. MariaDB preserves API and ABI compatibility with MySQL and adds several new features; for example, a non-blocking client API library, the Aria and XtraDB storage engines with enhanced performance, better server status variables, and enhanced replication.

    Detailed information about MariaDB can be found at https://mariadb.com/kb/en/what-is-mariadb-55/.

 

Syndiquer le contenu