Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 181 10 Older Entries

Displaying posts with tag: MySQL 5.6 (reset)

When your query is blocked, but there is no blocking query - Part 2
+0 Vote Up -0Vote Down
In my previous post I talked about a transaction which blocked other transactions without doing anything. I talked about finding data from the blocking transaction using SYS and performance_schema.

But what are the possible solutions?

The first solution is to (automatically) kill the blocking transactions. Long running transactions can also stall the purging in InnoDB. See this blog post by Mark Leith about a possible solution.

The second solution would be make the application end the transaction sooner and/or to commit more often. Depending on your application this might or might not work. I consider this the





  [Read more...]
When your query is blocked, but there is no blocking query
+0 Vote Up -0Vote Down
When I queried information_schema.innodb_trx (introduced in 5.1 with the InnoDB Plugin) I noticed there were a few transactions in LOCK WAIT state.

Example:
mysql [information_schema] > select trx_id,trx_state 
    -> from information_schema.innodb_trx;
+--------+-----------+
| trx_id | trx_state |
+--------+-----------+
| 7974 | LOCK WAIT |
| 7962 | RUNNING |
+--------+-----------+
2 rows in set (0.00 sec)

Then I made a query to join a few information_schema and performance_schema tables to find out which query was blocking my transactions. It turned out that the blocking transaction had a trx_query=NONE. So my query was block by a transaction doing nothing. That's not really helpful.

Let's try to recreate the situation and see exactly what happened. I'll use













  [Read more...]
Disabling old_passwords=1
+2 Vote Up -0Vote Down
It is possible to disallow users from using old_passwords=1. This can be done by adding 'maximum-old_passwords=0' to your my.cnf

This prevents users from generating passwords hashes in pre-4.1 format. In MySQL 5.7 old_passwords=1 is already disabled, so this is only useful on 5.6 and earlier.

Be aware that this also restricts old_passwords=2 which is needed for sha256 hashes.

mysql> select @@old_passwords;
+-----------------+
| @@old_passwords |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)

mysql> set old_passwords=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |


















  [Read more...]
MySQL 5.6.21 Overview and Highlights
+1 Vote Up -0Vote Down

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

For this release, there was 1 “InnoDB Notes” and 1 “Functionality Added or Changed” bug fix (and 0 “Security Fix”), so not much there, but of course they should be noted:

  • InnoDB Note: The –skip-innodb option is now deprecated and its use results in a warning. It will be removed in a future MySQL release. This also applies to its synonyms (–innodb=OFF, –disable-innodb, and so forth).
  • Functionality Added: Internally, spatial data types such as Geometry are represented as BLOB values, so when invoked with the –hex-blob option, mysqldump now displays spatial values in hex. (Bug
  •   [Read more...]
    Downgrading from MySQL 5.6 to MySQL 5.5
    +2 Vote Up -0Vote Down
    Last week I had to downgrade from MySQL 5.6 to 5.5. The reason for this was that the application used a very old Connector/J and that's incompatible with MySQL 5.6 because the removal of SET OPTION syntax.

    We're now planning to upgrade Connector/J to be able to upgrade to 5.6 again.

    There are two methods of downgrading:
    • Dump/Restore with mysqldump. This is easy and reliable, but can take more time.
    • In place (replace binaries, don't change data). This fast, but won't work if file formats have changed.
    As expected this is documented in the MySQL Reference Manual.

    I went for the in place method. I expected this to work without many issues as this database was not using the fancy new features like fulltext indexes for






      [Read more...]
    The Road to MySQL 5.6: Default Options
    +0 Vote Up -0Vote Down
    When you're testing out a new version of MySQL in a non-production environment there is a temptation to go wild and turn on all kinds of new features.  Especially if you're reading the changelogs or the manual and scanning through options.  You want to start with the most reasonable set of defaults, right?  Maybe you're even doing benchmarks to optimize performance using all the new bells and whistles.

    Resist the temptation!  If your goal is to upgrade your production environment then what you really want is to isolate changes.  You want to preform the upgrade with as little to no impact as possible.  Then you can start turning on features or making changes one-by-one.

    Why?  Anytime you're doing a major upgrade to something as fundamental as your core RDBMS, there are many ways things can go wrong.  Performance



      [Read more...]
    Using MySQL 5.6 Global Transaction IDs (GTIDs) in production: Q&A
    +1 Vote Up -0Vote Down

    Thank you to all of you who attended my webinar last week about Global Transaction IDs (GTIDs), which were introduced in MySQL 5.6 to make the reconfiguration of replication straightforward. If you missed my webinar, you can still listen to the recording and download the sides (free). We had a lot of questions during the webinar, so let me try to answer them here. Please let me know in the comments if additional

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

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

  • 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
  •   [Read more...]
    Examining the TokuDB MySQL storage engine file structure
    +1 Vote Up -0Vote Down

    As we know different storage engines in MySQL have different file structures. Every table in MySQL 5.6 must have a .frm file in the database directory matching the table name. But where the rest of the data resides depends on the storage engine.

    For MyISAM we have .MYI and .MYD files in the database directory (unless special settings are in place); for InnoDB we might have data stored in the single table space (typically ibdata1 in the database directory) or as file per table (or better said file per partition) producing a single file with .ibd extension for each table/partition. TokuDB as of this version (7.1.7) has its own innovative approach to storing the table contents.

    I have created the table in the database test having the following

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