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 172 Next 30 Older Entries

Displaying posts with tag: MySQL 5.6 (reset)

TIMESTAMP Columns, Amazon RDS 5.6, and You
+1 Vote Up -0Vote Down

This comes from an issue that I worked on recently, wherein a customer reported that their application was working fine under stock MySQL 5.6 but producing erroneous results when they tried running it on Amazon RDS 5.6. They had a table which, on the working server, contained two TIMESTAMP columns, one which defaulted to CURRENT_TIMESTAMP and the other which defaulted to ’0000-00-00 00:00:00′, like so:

CREATE TABLE mysql56 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ts2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
);

However, under Amazon RDS, the same table looked like this:

CREATE TABLE rds56 ( 
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT NULL, 
);

They

  [Read more...]
MySQL 5.6.19 Overview and Highlights
+2 Vote Up -0Vote Down

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

  [Read more...]
Using MySQL 5.6 Performance Schema in multi-tenant environments
+0 Vote Up -0Vote Down

Hosting a shared MySQL instance for your internal or external clients (“multi-tenant”) was always a challenge. Multi-tenants approach or a “schema-per-customer” approach is pretty common nowadays to host multiple clients on the same MySQL sever. One of issues of this approach, however, is the lack of visibility: it is hard to tell how many resources (queries, disk, cpu, etc) each user will use.

Percona Server contains userstats Google patch, which will allow you to get the resource utilization per user. The new MySQL 5.6 performance_schema has even more instrumentation which can give you a better visibility on per-user or

  [Read more...]
MySQL High Availability with Oracle Clusterware
Employee +1 Vote Up -0Vote Down

MySQL has an extensive range of high-availability solutions (http://www.mysql.com/products/enterprise/high_availability.html) to suit many different use cases and deployment needs.  This list spans from the time-tested – yet continuously-improved – MySQL replication to the just-released MySQL Fabric, giving users many certified solutions for highly available MySQL deployments.  The list is growing yet again, with Oracle Clusterware adding support for MySQL.

Oracle’s Clusterware product is the foundation for the Oracle RAC, and has been battle-tested for high availability support for Oracle database, as well as other Oracle applications.  This technology is now available as part of the MySQL Enterprise subscription, and – like all Oracle commercial products – is

  [Read more...]
MySQL 5.6 Performance on POWER8
+0 Vote Up -0Vote Down

The following sentence is brought to you by IBM Legal: The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions.

My previous post covered the work needed to get MySQL 5.6.17 running reliably on modern POWER systems. The patch to MySQL 5.6.17 that’s needed is available here.

For those who don’t know, POWER8 is the latest Power Architecture processors from IBM (my employer). These chips will be

  [Read more...]
MySQL 5.6 on POWER (patch available)
+0 Vote Up -0Vote Down

The following sentence is brought to you by IBM Legal. The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions.

Okay, now that is out of the way….

If you’re the kind of person who follows the MySQL bugs database closely or subscribes to the MySQL Internals mailing list, you may have worked out that I’ve spent a small amount of time poking at MySQL on modern POWER systems.

Unlike Intel CPUs, POWER CPUs require explicit memory barriers to synchronize memory state between

  [Read more...]
Faking a slave: Subscribing to mysql row-based-replication changes
Employee +0 Vote Up -1Vote Down

In complex systems it's often useful to be able to receive notification when rows have been modified in MySQL so that you can invalidate various external caches or indexes. For example: memcached, Sphinx, Lucene.

In the case of MySQL's default statement-based replication this can be quite tricky to do, as it would likely require an SQL parser to determine what was intended to be modified. However, this is made much simpler with Row-based Replication (something I recommend switching to).

A C++ library exists to be able to listen to MySQL replication, but what I want to demonstrate is that it is also very simple to be able to do this with mysqlbinlog:


shell> mysqlbinlog

  [Read more...]
Batch mode and expired passwords
Employee +3 Vote Up -0Vote Down

A series of related discussions triggered by difficulty in setting passwords via scripts using the mysql command-line client when an account has an expired password caused me to look into the interaction between expired passwords and batch mode, and this blog post resulted.  I hope it’s a useful explanation of the behavior and the workaround to those troubled by it, and amplifies the excellent documentation in the user manual.

The ability to flag accounts as having expired passwords first

  [Read more...]
GTIDs in MySQL 5.6: New replication protocol; new ways to break replication
+1 Vote Up -0Vote Down

One of the MySQL 5.6 features many people are interested in is Global Transactions IDs (GTIDs). This is for a good reason: Reconnecting a slave to a new master has always been a challenge while it is so trivial when GTIDs are enabled. However, using GTIDs is not only about replacing good old binlog file/position with unique identifiers, it is also using a new replication protocol. And if you are not aware of it, it can bite.

Replication protocols: old vs new

The old protocol is pretty straightforward: the slave connects to a given binary log file at a specific offset, and the master sends all the transactions from there.

The new protocol is slightly different: the slave first sends the range of GTIDs it has

  [Read more...]
MySQL 5.6 + GTID & MariaDB 10 replication
+1 Vote Up -0Vote Down

While at the keynote of Tomas Ulin at Percona Live MySQL Conference & Expo Santa Clara 2014, he asked the audience what they were running, and most of the audience was on MySQL 5.5 while about 15% of the audience was on MySQL 5.6. This number is steadily increasing I’m sure, so one thing that becomes important is that people will probably start turning on Global Transaction Identifiers (GTIDs). 

As you may already know, MariaDB 10 has a different implementation of Global Transaction ID. To me, this poses a problem in a mixed use environment (or even a migration scenario). Which is why

  [Read more...]
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 +3 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`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    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:

    http://dev.mysql.com/downloads/mysql/5.6.html

    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?

    Setup

    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).

    It

      [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.
    plugin-load=authentication_pam.so
    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':
    Create












      [Read more...]
    Showing entries 1 to 30 of 172 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.