Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 162 Next 30 Older Entries

Displaying posts with tag: MySQL 5.6 (reset)

MySQL 5.6.17 Overview and Highlights
+1 Vote Up -0Vote Down

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

For this release, I counted 7 “Functionality Added” and/or “Incompatible Change” fixes:

  • Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument.
  • Incompatible Change: The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now are deprecated and setting the sql_mode value to include any of them generates a warning. In MySQL 5.7, these modes do nothing. Instead, their effects are included in the effects of
  •   [Read more...]
    MySQL 5.6.17 – now with better encryption
    Employee +1 Vote Up -0Vote Down

    Joro wrote recently about MySQL 5.6.17‘s new support for AES-256 encryption, and it’s a great improvement for people need to encrypt their data at rest.  The new session block_encryption_mode variable controls what variant of AES is used, and for backwards compatibility, the default behavior remains 128-bit key length with ECB block cipher mode.  If you’re happy with that level of encryption, nothing changes – your existing code will work the same on 5.6.17 as it has on earlier versions (note that users of

      [Read more...]
    MySQL 5.6 Certification Now GA!
    Employee +2 Vote Up -0Vote Down

    The MySQL 5.6 certification exams (Developer and DBA) are now GA!  A big thanks to all those who sat for the extended length beta exams – and subsequently waited for several months for your results.  Your feedback and the data collected from many beta testers helped ensure the final exams are high quality. A huge thanks also goes to the MySQL staff at Oracle who produced, edited and verified exam questions, in addition to their normal job

      [Read more...]
    Many-table joins in MySQL 5.6
    +3 Vote Up -0Vote Down

    I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good at handling joins with such a large number of tables. The good news is that MySQL 5.6 brings welcome improvements.

    Isolating the problem

    As always with a slow query, finding the execution plan with EXPLAIN is the 1st step to understand where time is spent. Here the plan was very good with almost all joins using the primary key or a unique key, but perhaps the most interesting part was that EXPLAIN was very slow as well. This indicates that the optimizer takes a lot of time finding the optimal execution plan. To double check, we can run SHOW

      [Read more...]
    Magic deadlock: what locks are really set by InnoDB?
    +0 Vote Up -0Vote Down
    Megabytes of text had been written already on InnoDB locking and deadlocks. Still, even very simple cases of deadlocks while working with a table having only one row sometimes make people wonder what happened and why.

    Today I want to check if this topic is explained well in the manual and existing blog posts and understood properly. So, it's an exercise for my dear readers and those who like to report bugs as much as I do.

    Let's consider a very simple example. In session #1 with default transaction isolation level execute the following:
    CREATE TABLE `tt` (
      `id` int(11) NOT NULL,
      `c1` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `c1` (`c1`)
    insert into tt values(1,1); -- insert a row there
    select * from tt; -- check that

      [Read more...]
    MySQL 5.6.16 Overview and Highlights
    +6 Vote Up -0Vote Down

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


    As opposed to the latest 5.5 release, this latest 5.6 release has quite a few more bug fixes, but that’s expected since 5.5 has been GA for much longer.

    There were 2 minor functionality changes:

    • Previously, ALTER TABLE in MySQL 5.6 could alter a table such that the result had temporal columns in both 5.5 and 5.6 format. Now ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm, so specifying ALGORITHM=INPLACE in these cases results in an error. (Bug #17246318)
    • CMake now supports a -DTMPDIR=dir_name
      [Read more...]
    MySQL 5.6.16 Community Release Notes
    Employee +7 Vote Up -0Vote Down

    Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.16.

    In particular:

    • Thanks to Honza Horak for suggesting we make tmpdir configurable at build-time, and for providing a patch. Bug #68338.
    • Thanks to Hartmut Holzgraefe for reporting a memory leak when using the InnoDB memcached API and replication. Bug #70757.
    • Thanks to Justin Swanhart for reporting that InnoDB reported an incorrect operating system error code when it failed to initialize. Bug #70867.
    • Thanks to Yoshinori Matsunobu who reported that
      [Read more...]
    Fun with Bugs #29 - important bug fixes in MySQL 5.6.16
    +3 Vote Up -0Vote Down
    As you should already know, Oracle had released MySQL 5.6.16 officially on January 31, 2014. You can find all the details in official release notes. In this post I decided to concentrate on important fixes for bugs reported by community in 4 most important categories: InnoDB, replication, partitioning and optimizer.

    Let's start with Bug #70768, "Persistent optimizer statistics often causes LOCK_open stalls". It's well known that persistent statistics for InnoDB tables stored in two (again, InnoDB) tables in mysql database may cause various problems, not only bad plans based on outdated statistics there. One of these problems seems solved in 5.6.16, but note that the bug report itself

      [Read more...]
    Percona Replication Manager (PRM) now supporting 5.6 GTID
    +0 Vote Up -0Vote Down

    Over the last few days, I integrated the MySQL 5.6 GTID version of the Percona Replication Manager (PRM) work of Frédéric Descamps, a colleague at Percona. The agent supports the GTID replication mode of MySQL 5.6 and if the master suffers a hard crash, it picks the slave having applied the highest transaction ID from the dead master. Given the nature of GTID-based replication, that causes all the other slaves to resync appropriately to their new master which is pretty cool and must yet be matched by the regular PRM agent.

    For now, it is part of a separate agent, mysql_prm56, which may be integrated with the regular agent in the future. To use it, download the agent with the link above, the pacemaker configuration is similar to the one of the

      [Read more...]
    MySQL in Oracle Critical Patch Update Advisory January 2014
    +0 Vote Up -0Vote Down
    Oracle has released the Critical Patch Update (CPU) advisory for January 2014.

    The affected MySQL products are:
    • Oracle MySQL Enterprise Monitor, versions 2.3, 3.0 
    • Oracle MySQL Server, versions 5.1, 5.5, 5.6
    So this means that you should consider updating MySQL. For MySQL Enterprise the updates should be available on My Oracle Support and for the Community version the new versions are on the regular download locations. I guess the official repositories are already updated.

    For MySQL 5.6 you should upgrade to 5.6.15
    For MySQL 5.5 you should upgrade to 5.5.35
    For MySQL 5.1 you should upgrade to 5.1.73

    If you use the MySQL release from your

      [Read more...]
    Multiple column index vs multiple indexes with MySQL 5.6
    +4 Vote Up -0Vote Down

    A question often comes when talking about indexing: should we use multiple column indexes or multiple indexes on single columns? Peter Zaitsev wrote about it back in 2008 and the conclusion then was that a multiple column index is most often the best solution. But with all the recent optimizer improvements, is there anything different with MySQL 5.6?


    For this test, we will use these 2 tables (same structure as in Peter’s post):

    CREATE TABLE t1000merge (
      id int not null auto_increment primary key,
      i int(11) NOT NULL,
      j int(11) NOT NULL,
      val char(10) NOT NULL,
      KEY i (i),
      KEY j (j)
    ) ENGINE=InnoDB;
    CREATE TABLE t1000idx2 (
      id int not null auto_increment primary key,
      i int(11) NOT
      [Read more...]
    It can be a bright 2014
    +1 Vote Up -0Vote Down

    In many parts of the MySQL world, whether you have users, developers or administrators of MySQL, the season holidays are for family, relax, prayers, travel, or they are simply a moment where you can enjoy what you like or care most about.

    For me, this time is dedicated to my family, but also to deeper thoughts around the strategies to adopt in short and long term. My work nowadays, as the work of many others, is ruled by quick decisions, by the "time to market” - whatever “market" means in a specific context. Decisions must be made in meetings that are time-boxed in one hour or even less. In the end, you accumulate so much work and high priority tasks that you do not have enough time to prepare the topics adequately.

    I thought I could summarise my thoughts for the past year and for the near future, from a technical and from a business perspective.

      [Read more...]
    Fun with Bugs #28 - regression bugs in MySQL 5.6
    +0 Vote Up -0Vote Down
    2013 was a great year for MySQL Community. New MySQL 5.6 GA release with its increased throughput, scalability and new features as well as more interaction and cooperation with MySQL Community from Oracle side brought us a lot of new perspectives and good feelings over the year.

    Unfortunately new MySQL 5.6 GA release also reminded about old and well known problem with new MySQL versions. They all introduce new regression bugs. MySQL 5.6 had not become an exception.

    Note that according to good old tradition (that I hope will be followed in 2014) bugs that demonstrate a regression (make some feature that previously worked stop functioning as intended in a new release) are marked with "regression" tag at http://bugs.mysql.com.

      [Read more...]
    MySQL 5.6 system variables in the MariaDB 10 server
    +4 Vote Up -0Vote Down

    Since MariaDB aims to be a compatible/drop-in replacement to MySQL, its crucial that in 10.0 we support all the 5.6 options/system variables, else we have to clearly document them in the Knowledgebase article MariaDB versus MySQL – Compatibility.

    To this extent, Sergey Vojtovich (svoj) has created MDEV-5277 as a tracker. There is also plenty of discussion on this topic at the maria-developers mailing list. I encourage current users of MySQL 5.6 to take a look at the list and comment either in Jira or on the mailing list to

      [Read more...]
    How to recover an orphaned .ibd file with MySQL 5.6
    +0 Vote Up -0Vote Down

    A few years ago Yves Trudeau and Aleksandr Kuzminsky wrote posts about different ways for recovering orphaned .ibd files:

    Today I want to show you how to do that in more easy and quick way. In my example I’ll restore a “payment.ibd” file (payment table) from

      [Read more...]
    Teaser on my upcoming Percona Live London 2013 session
    +0 Vote Up -0Vote Down
    As you probably know already, I have a session on PERFORMANCE_SCHEMA at the conference, scheduled at 12 November 4:00pm - 4:50pm @ Orchard 1. Presentation is mostly ready, but I had not decided yet when to publish it. In the meantime, for those really interested, here is a teaser.

    Below I list one link for each slide (in order of presentation) having more than one of them mentioned or listed in my notes. Now try to guess what I am going to say there and why. Note that it's not a tutorial (my half a day tutorial on PERFORMANCE_SCHEMA was not accepted by the conference committee, and this is probably good decisions, as I am usually very good in explaining what's bad or what should never be done and much worse in "best practices"). So,

      [Read more...]
    My Favorite Hidden Docs Page
    Employee +2 Vote Up -0Vote Down

    There’s a lot to love about the MySQL product documentation – a lot of hard work from a number of very talented Oracle staff goes into it (not to mention the helpful suggestions and feedback from the community).  There is, however, one page I find myself coming back to again and again, despite the fact that it’s somewhat hard to find.  This is the MySQL Server options/variable reference page.  It’s a helpful table that lists every MySQL Server option or variable, what version it was introduced or deprecated in, whether it’s dynamic or not, whether variables have session/global/both scope, and links to the version-specific documentation for that variable (useful to know when default values changed, for example).


      [Read more...]
    InnoDB adaptive flushing in MySQL 5.6: checkpoint age and io capacity
    +0 Vote Up -0Vote Down

    In MySQL 5.6 InnoDB has a dedicated thread (page_cleaner) that’s responsible for performing flushing operations. Page_cleaner performs flushing of the dirty pages from the buffer pool based on two factors:
    - access pattern  -  the least recently used pages will be flushed by LRU flusher from LRU_list when buffer pool has no free pages anymore;
    - age – the oldest modified non-flushed pages are part of flush_list structure and will be flushed by flush_list flusher based on several heuristics.

    There is a good overview of the page_cleaner and also here you may find some details about

      [Read more...]
    MySQL 5.6 New Replication Features: Webinar followup Q&A
    +0 Vote Up -0Vote Down

    I want to thank all attendees of my webinar, “MySQL 5.6 New Replication Features: Benefits, Challenges and Limitations“. We had questions that I didn’t have the time to answer:

    Q: If I run on Amazon’s RDS, do I need to worry about enabling crash-safe slaves, or is that already in place?

    A: Crash-safe replication is already configured for read replicas using MySQL 5.6.

    Q: How the relay log purge will manage in case of

      [Read more...]
    Using the PAM authentication plugin
    +2 Vote Up -0Vote Down
    The procedure for using the PAM authentication plugin as documented doesn't work flawlessly on Ubuntu.

    So here is how it works on Ubuntu (and probably also on other Debian based systems).

    Please note that the PAM authentication plugin is an enterprise feature.

    1. Make sure the plugin is loaded

    This can be done by adding the following to the mysqld section of my.cnf (Don't forget to restart). You could also use INSTALL PLUGIN to load it without restart.
    2.  Add a user which will use the plugin

    mysql> CREATE USER 'dveeden'@'localhost' IDENTIFIED WITH authentication_pam;
    Query OK, 0 rows affected (0.00 sec)
    3. Add a pam config file for 'mysql':

      [Read more...]
    Recalculating InnoDB Persistent Statistics - a Story of the Bug Report
    +0 Vote Up -0Vote Down
    One of the first posts in this blog was about reporting MySQL bugs "properly", in a way that maximizes chances for it to be processed really soon. I had written the following there:
    "Ideally, you should provide a complete test case and/or instructions that any reader can use to reproduce your problem"
    Indeed, if one can just copy/paste something to mysql command line client or run some file attached to see the problem, chances are high for the bug to be processed really soon. We all like to get low hanging fruits from time to time, and Oracle engineers who work on bugs are not exceptions. But does this mean that bug without clear test case has no value and is going to be ignored?

    It should NOT be the case. Let's

      [Read more...]
    time for standards 2
    +2 Vote Up -0Vote Down
    I was a bit wrong in my previous post. MySQL 5.6 does allow you to supply a fsp with CURRENT_TIMESTAMP (thanks Roy).

    | 2013-10-27 10:38:59 | 2013-10-27 10:38:59.182530 |
    1 row in set (0.00 sec)

    It however feels a bit weird to me as the CURRENT_TIMESTAMP is often used without () and doesn't look like a function. So when I tried to use a CURRENT_TIMESTAMP with a fsp of 6 it was not behaving how I expected it to be:

      [Read more...]
    time for standards
    +0 Vote Up -0Vote Down
    MySQL 5.6 includes support for microsecode timestamp resolution, which is a great new feature.

    To get the current timestamp in MySQL 5.5 you could use NOW(), SYSDATE() or CURRENT_TIMESTAMP.

    | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 |
    1 row in set (0.01 sec)

    If we run the same statement in MySQL 5.6 the output is the same. This is great for compatibility, but what if we want those microsecond

      [Read more...]
    Exam Cram: General MySQL Syntax for Developers (Section 2)
    Employee +3 Vote Up -0Vote Down

    The General MySQL Syntax section of the MySQL 5.6 Developer certification exam is a bit meatier than the MySQL Architecture section covered in my last post, but it’s still likely to be very familiar to experienced MySQL developers (or DBAs):

    • Explain MySQL implementation of identifiers including case sensitivity, qualified names, aliases and use of reserved words
    • Identify MySQL data type properties and appropriate usage
    • Recognize and use common functions and expressions for all MySQL data types
    • Identify and use comment syntax
    • Describe and utilize prepared statements
    • Describe transactions and transaction isolation levels
      [Read more...]
    MySQL 5.6’s new replication features: Benefits, Limitations and Challenges
    +1 Vote Up -0Vote Down

    On Wednesday I’ll be leading a webinar exploring MySQL 5.6’s new replication features. And yes, as usual I’ll deliver news on the good, the bad and the ugly (that is to say the benefits, limitations and challenges).

    The webinar, appropriately titled, “New Replication Features in MySQL 5.6: Benefits, Limitations, and Challenges“, is scheduled for Oct. 23 at 10 a.m. Pacific Daylight Time. You can

      [Read more...]
    Using the new spatial functions in MySQL 5.6 for geo-enabled applications
    +1 Vote Up -0Vote Down

    Geo-enabled (or location enabled) applications are very common nowadays and many of them use MySQL. The common tasks for such applications are:

    • Find all points of interests (i.e. coffee shops) around (i.e. a 10 mile radius) the given location (latitude and longitude). For example we want to show this to a user of the mobile application when we know his/her approximate location. (This usually means we need to calculate a distance between 2 points on Earth).
    • Find a ZIP code (U.S. Postal address) for the given location or determine if this location is within the given area. Another example is to find a school district for the given property.

    MySQL had the spatial functions originally (implementation

      [Read more...]
    FAQ: InnoDB extended secondary keys
    Employee +4 Vote Up -0Vote Down
    MySQL 5.6 introduced a new feature called extended secondary keys. We get a lot of questions about it and find that most of them come from a few incorrect assumption. In this post I'll try to get rid of the confusion once and for all. Famous last words... here goes:

    Q1: Do I need to do anything to enable extended secondary keys?

    No, nothing at all. It's on by default and I can't see any sensible reason why you would want to disable it. However, it is possible to disable it by tuning the optimizer_switch: SET optimizer_switch='use_index_extensions={on|off}'.

    Q2: Does extended secondary keys only work with InnoDB?

    No, it should work with any storage engine that uses the primary key columns as reference to the row, which means most storage engines with clustered primary keys. I say

      [Read more...]
    MySQL Certification Study: Write a basic .NET application that uses MySQL
    +6 Vote Up -0Vote Down
    I've registered for the MySQL 5.6 beta exams, just like many more on Planet MySQL have done.

    One of the topics for the MySQL 5.6 Developer exam is: "Write a basic .NET application that uses MySQL".

    I have used Connector/Net a few times the last few years, but the last time I looked at some code involving Connector/Net was when I took the MySQL 5 Developer exam, which was long ago.

    I'm using Linux on both my laptop and on servers for most projects. So I don't use or see many .NET code.

    So I need a short refresh.

    To use Connector/Net on Linux you will need to install Mono. The base for .NET is CIL, which exists in at least 4 versions: 1.0, 2.0 and 4.0. To compile C# code with mono you need the command which correspondends with the CIL version you are using.
    • CIL 1.0: mcs
    • CIL 2.0: gmcs
    • CIL

      [Read more...]
    Exam Cram: MySQL Architecture for Developers (Section 1)
    Employee +3 Vote Up -0Vote Down

    The first section in the exam topics for both the MySQL 5.6 Developer and DBA exam deals is titled MySQL Architecture, but each has a different emphasis.  This blog will focus on those objectives listed for the Developer exam:

    • Use MySQL client programs to interface with the MySQL Server interactively and in batch
    • Describe SQL Modes and their impact on behavior of MySQL
    • Identify characteristics which have session scope

    For those of us who have spent time pulling both DBA and developer duties, this section is likely to be relatively straight-forward – perhaps even easy.  I’ll walk through the exercises and documents I’ve used to review below to kick off my

      [Read more...]
    Exam Cram: Preparing for the MySQL 5.6 certification exams
    Employee +3 Vote Up -0Vote Down

    As noted in earlier posts, exams for the MySQL 5.6 Developer and DBA certifications are entering a beta period, allowing candidates to register for the exams at steep discounts from normal certification exam fees.  I’ve registered to take both exams late this month, and – like Moritz – I’m starting to prepare for the exams now.  For those considering sitting for the exams, my plan is to document my exam preparation in a series of blog entries.  This won’t be a

      [Read more...]
    Showing entries 1 to 30 of 162 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.