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

Displaying posts with tag: skysql (reset)

WebScaleSQL on Windows? I wish, but not quite yet, it seems …
+1 Vote Up -0Vote Down

For fun, I tried building WebScaleSQL on Windows, even though it’s not [yet?] a support platform.

Using the following (as I would to build MySQL/MariaDB):

cd c:\mysql\webscalesql-5.6.16
mkdir bld
cd bld
cmake ..
cmake --build . --config relwithdebinfo --target package

I end up with:

    238 Warning(s)
    110 Error(s)
Time Elapsed 00:05:08.53

Looking through the output, the main error is this:

fatal error C1083: Cannot open include file: 'atomic':
No such file or directory

Of course the directory does exist, and permissions are correct.

C:\mysql\webscalesql-5.6.16\include\atomic_stat.h contains the following line:

#include <atomic>

And this exists:

  [Read more...]
Resolving Error 1918, System Error Code 126, When Installing MySQL ODBC Driver
+1 Vote Up -0Vote Down

If you are installing MySQL ODBC Driver and encounter the following error:

Error 1918. Error installing ODBC driver MySQL ODBC 5.1 Driver,
ODBC error 13: The setup routines for the MySQL ODBC 5.1 Driver
could not be loaded due to system error code 126:
The specified module could not be found.
...\myodbc5S.dll).. Verify...

Then you will need to install the Microsoft Visual C++ 2010 Redistributable Package (select the appropriate one for your OS architecture below):

64-bit version:


32-bit version:


After installing that, then re-attempt

  [Read more...]
Resolving MySQL ODBC “architecture mismatch” Error
+0 Vote Up -0Vote Down

If you attempt to use ODBC to run a MySQL application and run into the following error:

[Microsoft][ODBC Driver Manager] The specified DSN contains an
architecture mismatch between the Driver and Application

This means there is a 64-bit versus 32-bit mismatch.

Most likely, you’re running 64-bit Windows, as well as 64-bit MySQL ODBC connector, but the application is 32-bit.

If this is the case, you will also need to install the 32-bit MySQL ODBC connector, and then create the connection from the 32-bit ODBC.

odbcad32.exe is the file to create the connections. Both 64-bit and 32-bit files have the same name, just differing locations.

This is the default location for the 64-bit ODBC:


This is the default location for the 32-bit ODBC:


And should you need to

  [Read more...]
Looking for Slave Consistency: Say Yes to –read-only and No to SUPER and –slave-skip-errors
+0 Vote Up -0Vote Down

The biggest concern with a slave is to ensure your data is consistent with the master! End of story!

3 of the biggest things I see when dealing with out-of-sync slaves:

  • Many users do not use the --read-only option on their slaves.
  • Some of those who do often have numerous users with SUPER who can still perform writes.
  • Many users simply use --slave-skip-errors=… to avoid common errors.
  • Of course, if you have a slave, definitely use the --read-only option.

    However, SUPER users can still write on slaves with --read-only, so blindly granting SUPER to all users just to save a little time when creating users won’t help. I’d suggest to use SUPER as sparingly as possible (not to mention it’s good for security also).

    And the use of --slave-skip-errors=… is generally just a quick fix to avoid

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

    MySQL 5.7.4 was recently released (it is the latest MySQL 5.7, and is the “m14″ or “Milestone 14″ release), and is available for download here and here.

    The 5.7.4 changelog begins with the following, so I felt it appropriate to include it here as well.

    In Memoriam:

    “This release is dedicated to the memory of two young engineers of the MySQL Engineering family, Astha and Akhila, whom we lost while they were in their early twenties. This is a small remembrance and a way to recognize your contribution to the 5.7 release. You will be missed.”

      [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.5.37 Overview and Highlights
    +2 Vote Up -0Vote Down

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


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

    There was one new feature added (Solaris-specific + obscure), and only 21 bugs fixed.

    The new feature is this:

    • On Solaris, mysql_config –libs now includes -R/path/to/library so that libraries can be found at runtime.

    Out of the 21 bugs, most were benign, but there was one definitely worth mentioning (because it is a regression bug with performance degradation):

    • A regression introduced by Bug #14329288 would result in a performance degradation when a compressed table does not fit into memory. (Bug #18124788,
      [Read more...]
    Improve your Stored Procedure Error Handling with GET DIAGNOSTICS
    +1 Vote Up -0Vote Down

    In a previous post, I discussed debugging stored procedures with RESIGNAL, which is of great value when troubleshooting errors raised by your stored procedures, functions, triggers, and events as of MySQL/MariaDB 5.5.

    However, as of MySQL 5.6 and MariaDB 10.0, there is GET DIAGNOSTICS, which can be used to get the exact error details as well.

    RESIGNAL just outputs the error, as it comes from the server, for instance:

    ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

    You may not want the error just written to the console, or perhaps you want to at least control how it is written.

    It’s common to see exit handler code in the following form:

    SELECT ...;

    Where the SELECT outputs something not very useful in many cases.

    With GET DIAGNOSTICS, you can

      [Read more...]
    Quickly Debugging Stored Procedures, Functions, Triggers, and Events with RESIGNAL
    +2 Vote Up -0Vote Down

    I was recently debugging a stored procedure and could not easily identify the underlying reason for why it was failing.

    It had a standard exit handler catch-all for SQLEXCEPTION, which was:

    SELECT ...;

    When there was an error, it didn’t really output anything useful.

    As of MySQL 5.5, there is RESIGNAL:

    “RESIGNAL passes on the error condition information that is available during execution of a condition handler within a compound statement inside a stored procedure or function, trigger, or event.”


    There is also some good information about it here as well:

      [Read more...]
    InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN
    +0 Vote Up -0Vote Down

    I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

    Basically, you can start with a very simple InnoDB table – 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

    CREATE TABLE `t1` (
      `id1` int(10) unsigned NOT NULL,
      `id2` int(10) unsigned DEFAULT NULL,
      PRIMARY KEY (`id1`),
      KEY `id2` (`id2`)
    ) ENGINE=InnoDB;

    The query is:

    SELECT id1 FROM t1;

    This is a straight-forward query with no WHERE clause.

    Given no WHERE clause, we know there will be a full table or index scan. Let’s look at EXPLAIN:

    mysql> EXPLAIN SELECT id1 FROM t1\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t1
             type: index
    possible_keys: NULL
              key: id2
      [Read more...]
    MariaDB 10.0.9 Overview and Highlights
    +1 Vote Up -0Vote Down

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


    This is the second RC (“Release Candidate”) release of MariaDB 10.0, and 10th overall release of 10.0. All features planned for MariaDB 10.0 GA are included in this release.

    There were 6 notable changes in MariaDB 10.0.9:

  • InnoDB upgraded to version 5.6.15
  • Extended-keys optimization is now enabled by default.
  • MariaDB can be compiled to use the system’s PCRE library.
  • Added MASTER_GTID_WAIT() and
  •   [Read more...]
    MariaDB 5.5.36 Overview and Highlights
    +1 Vote Up -0Vote Down

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


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

  • Includes all bugfixes and updates from: MySQL 5.5.36
  • TokuDB is now included in RPM packages for CentOS 6 on x86-64
  • Note this release did not contain the bugfixes from XtraDB 5.5.36 yet. There is one bug fix in XtraDB 5.5.36 that is fairly serious, so if you are a *heavy* XtraDB+ user, I might wait for MariaDB 5.5.37 to be released, so you get this fix. I should note the bug was partially fixed in

      [Read more...]
    Shard-Query supports background jobs, query parallelism, and all SELECT syntax
    +0 Vote Up -0Vote Down

    SkySQL just blogged about a tool to schedule long running MySQL jobs, prevent too many queries from running simultaneously, and stores the results in tables.  It even uses Gearman.  You will note that the article says that it uses PAQU, which uses Shard-Query.

    I think PAQU was created for two reasons.  A) Shard-Query lacked support for fast aggregation of STDDEV and VARIANCE (this has been fixed), and B) their data set requires “cross-shard queries”.  From what I can see though, their type of cross-shard queries can be solved using subqueries in the FROM clause using Shard-Query, instead of using a customized (forked) version of Shard-Query.  It is unfortunate, because my recent improvements to Shard-Query have to be ported into PAQU by the PAQU authors.

    I’d like to encourage you to look at Shard-Query

      [Read more...]
    An update on the MariaDB Audit Plugin and a new version of it
    +0 Vote Up -0Vote Down

    I’m happy to announce that a new version of the MariaDB Audit Plugin is available. Version 1.1.5 can be downloaded here. As you can see the Audit Plugin is available from SkySQL, who has developed the plugin.

    However, now with the Audit Plugin being GA for a couple of months since 7th of November last year and customers using it in production, SkySQL has decided to contribute the Audit Plugin to the MariaDB project and I’m happy to tell you that starting from MariaDB versions 5.5.37 and 10.0.9 the Audit Plugin will be included by default. Notice that these versions of MariaDB aren’t yet released.

    The MariaDB Audit Plugin introduces the capabilities of tracking user access to data. By having the Audit Plugin available by default in MariaDB, all users can

      [Read more...]
    Correlating OS Thread IDs from SEMAPHORES Section to TRANSACTIONS Section
    +3 Vote Up -0Vote Down

    I’m frequently tracking semaphores waits, and if you’ve examined them before, it can be a little matching up the threads listed in the SEMAPHORES section with the transactions in the TRANSACTIONS section.

    Semaphore waits are related to internal synchronization between threads in mysqld, and not directly to row locks or other items associated with user queries, so that’s why the SEMAPHORES section only reports the OS thread id.

    Fortunately, the TRANSACTIONS sections also reports the OS thread handle, but in hex format.

    Here is an example semaphore wait:

    --Thread 1079654736 has waited at ibuf0ibuf.c line 3549
    for 943.00 seconds the semaphore:
    X-lock (wait_ex) on RW-latch at 0x7f2a48830bf8 '&block->lock'
    a writer (thread id 1079654736) has reserved it in mode wait exclusive
    number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
    Last time read locked
      [Read more...]
    Getting Started with the Spider Storage Engine
    +1 Vote Up -0Vote Down

    If you’re like me, you’ve probably heard of the Spider storage engine, but not used it yet.

    While it has been available for some years now, I just simply haven’t used it before until now.

    I suspect that has to do with ease of installation. Previously, one had to compile it with MySQL in order to use it, which excludes a lot of people. However, in MariaDB 10.0 (as of 10.0.4), it is very easy to add and use.

    And with MariaDB 10.0.8 being declared RC, combined with Spider’s sheer usefulness, I only suspect its usage will become more and more widespread.

    What is the Spider storage engine, and why will it be useful?

    “The Spider storage engine is a storage engine with built-in sharding features. It supports partitioning and xa transactions, and allows tables of different MariaDB instances to be handled as if they were on the same instance. It

      [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.5.36 Overview and Highlights
    +1 Vote Up -0Vote Down

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


    I was reading through the changelogs to review the changes and fixes, and to summarize, I must say this release is mostly uneventful.

    There was one new feature added (for building, so not really applicable to everyone), and only 17 bugs fixed.

    The new feature is this:

    • CMake now supports a -DTMPDIR=dir_name option to specify the default tmpdir value. If unspecified, the value defaults to P_tmpdir in . Thanks to Honza Horak for the patch. (Bug #68338, Bug #16316074)

    Out of the 17 bugs, there was only 1 I thought worth mentioning (because it is a wrong results bug):

    • COUNT(DISTINCT) sometimes produced an incorrect result
      [Read more...]
    Exploring MySQL Metadata Lock Instrumentation in Closer Detail
    +2 Vote Up -0Vote Down

    I recently wrote a post on tracking metadata locks (MDL) in MySQL 5.7, and I wanted to take a moment to expand on it by explaining a couple of the associated variables in more detail.

    First off, once you have enabled the performance_schema *and* the metadata lock instrumentation, you can verify it with:

    mysql> SELECT * FROM performance_schema.setup_instruments
        -> WHERE NAME = 'wait/lock/metadata/sql/mdl';
    | NAME                       | ENABLED | TIMED |
    | wait/lock/metadata/sql/mdl | YES     | YES   |

    “ENABLED” will report “YES” if it is enabled properly, and “NO” if not.

    “TIMED” (referring to event timing) reports

      [Read more...]
    Tracking Metadata Locks (MDL) in MariaDB 10.0
    +1 Vote Up -1Vote Down

    I recently blogged about tracking metadata locks in the latest MySQL, and now I want to discuss how to track these metadata locks in MariaDB.

    In MySQL 5.7, there is a table named `metadata_locks` added to the performance_schema (performance_schema must be enabled *and* the metadata_locks instrument must be specifically enabled as well.

    In the MariaDB 10.0 implementation (as of 10.0.7), there is a table named METADATA_LOCK_INFO added to the *information_schema*. This is a new plugin, so the plugin must be installed, but that is very simple with:

    INSTALL SONAME 'metadata_lock_info';

    Then, you will have the table.

    To see it in action:

    Connection #1:

    mysql> create table t (id int) engine=myisam;
    mysql> begin;
    mysql> select * from t;

    Connection #2:

    mysql> alter table t
      [Read more...]
    What is the ibtmp1 file in MySQL 5.7?
    +3 Vote Up -0Vote Down

    If you’re running MySQL 5.7, you might have noticed the ibtmp1 file located in the datadir, and you might be wondering exactly what this file is.

    In 5.7, InnoDB added a separate tablespace for all non-compressed InnoDB temporary tables. This new tablespace is named ibtmp1 and is located in the datadir by default.

    “The new tablespace is always recreated on server startup. … A newly added configuration file option, innodb_temp_data_file_path, allows for a user-defined temporary data file path. For related information, see InnoDB Temporary Table Undo Logs.”

    You can read that, and the full changelog entry, here:


    And the full variable description is here:

      [Read more...]
    Tracking Metadata Locks (MDL) in MySQL 5.7
    +3 Vote Up -1Vote Down

    I’ve blogged about metadata locks (MDL) in the past (1 2 3) and in particular discussed how best to track them down and troubleshoot threads stuck waiting on metadata locks.

    If you’ve had any experience with these, you’ll know finding them isn’t always the most straight-forward task.

    So I was glad to see metadata lock instrumentation added to MySQL 5.7.3 as part of performance_schema, which makes tracking these down a breeze! (Note this is only in 5.7.3 currently, and therefore is some time from being GA as of today)!

    To use these, performance_schema must be enabled (i.e., performance_schema=1 in your config file).

    But, also, the metadata_locks instrument is disabled by default, so even if you enable the

      [Read more...]
    5.7 Upgrade and Resolving ERROR 1130 Host ‘localhost’ is Not Allowed to Connect
    +3 Vote Up -0Vote Down

    I recently upgraded an instance to 5.7.3 the other day, and ran into an error, so I wanted to share the resolution for it here.

    In my case, I was upgrading 5.7.1 to 5.7.3. However, this will apply to anyone wanting to upgrade from pre-5.7.2 (including 5.6/5.5) to 5.7.2+.

    I performed the upgrade, in-place, and restarted mysqld. This was fine. However, then I attempted to connect via the command-line, and received the following error:

    shell> mysql -uroot -ppass -P3310
    ERROR 1130 (HY000): Host 'localhost' is not allowed
    to connect to this MySQL server

    Searching the net, you’ll mostly find RTM replies, which were all accurate as far as I could tell. In all of those prior reported cases, the issues were expected behavior and the issues were ultimately user error.

    Of course I double-checked my config and data files. I knew I didn’t change anything in the user

      [Read more...]
    Upcoming MariaDB Enterprise and MaxScale Webinar
    +1 Vote Up -0Vote Down

    As many of you know, both MariaDB Enterprise and MaxScale have been released and are now available for use.

    Since they are both so new, I just wanted to let everyone know Ivan Zoratti will conducting a webinar next week discussing both of these technologies.

    I’m looking forward to it, and should anyone out there be interested in either MDBE or MaxScale, we hope you’ll attend, and get any questions you might have answered.

    When: February 6, 2014 – 6:00pm CET

    Sign up now here:


    MariaDB Enterprise 1.0 is Here
    +1 Vote Up -0Vote Down

    I know it was officially announced Monday, but I just wanted to take a moment and let everyone know MariaDB Enterprise 1.0 is now available, in case you missed the previous article.

    What does MariaDB Enterprise consist of?

    “MariaDB Enterprise is composed of several components including MariaDB Manager, which is a set of management tools and an API with which you can easily provision, monitor, and manage a highly available MariaDB Galera Cluster for multi-master, synchronous replication. Galera is a powerful technology that can eliminate single points of failure for your database infrastructure, but it is relatively new and can be a challenge to configure for administrators who aren’t familiar with it.”

      [Read more...]
    MySQL Workbench Stuck in Fetching Mode
    +3 Vote Up -0Vote Down

    Another obscure issue I ran into not long ago was when using MySQL Workbench, and clicking on a table, it became stuck in fetching mode.

    What triggered the issue was a recent MySQL upgrade, but MySQL itself, not Workbench.

    After checking the error log, we saw an error like:

    Incorrect definition of table mysql.proc: expected column
    'comment' at position 15 to have type text, found type char(64)

    Instantly, I knew mysql_upgrade needed to be ran in order to fix the “Incorrect definition” issue, and turns out that is the root cause for Workbench getting stuck in the “fetching” mode.

    So the solution is to run mysql_upgrade. Should that not fix the table for some reason, then you can also fix it alternatively with:

    ALTER TABLE mysql.proc MODIFY `comment` text

    Hope this helps.


    Be careful if you use file-level symbolic links and myisamchk
    +2 Vote Up -0Vote Down

    I ran into a rather obscure bug the other day, but while uncommon, it can cause damage you would not otherwise expect if you use file-level symbolic links. So this is just a warning about that.

    Specifically, if you create a table with the .MYI and .MYD files in a different directory, using symbolic links – either manually or using CREATE TABLE .. INDEX DIRECTORY=”" DATA DIRECTORY=”", and then run myisamchk on the table and specify .MYI, you will corrupt the table.

    Creating these manually is not so common, but the CREATE TABLE .. INDEX DIRECTORY=”" DATA DIRECTORY=”" is much more common, which creates file-level symbolic links (for the .MYI and .MYD files, respectively) in the datadir and stores the actual file(s) in the location specified. So it leaves you with this setup.

    Therefore, if you later run myisamchk on one of these files, do not specify .MYI in the

      [Read more...]
    A Close Encounter with MaxScale
    +1 Vote Up -0Vote Down

    MaxScale is the new proxy server from the SkySQL/MariaDB team. It provides Connection Load Balancing (CLB) and Statement Load Balancing (SLB) out of the box. This post is a [relatively] quick “how to” install, configure and test SLB with the read/write splitting module.

    Step 1 - Server preparation

    If you do not have many HW resources, you may run everything on a single Linux instance, but the best way to test MaxScale is to use at least 4 servers: one for MaxScale and for the client apps, one as Master and two as slaves - so, 4 in total. In this post I am going a bit further, I will use 5 servers:
    Max 0 - For client apps (
    Max 1 - The master server (
    Max 2 - The first slave (
    Max 3 - The second slave (
    Max 4 - The third slave (
    Max 6 - The MaxScale

      [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...]
    #002b64 is the new black (or #181818)
    +0 Vote Up -2Vote Down

    For my 25 readers (Italians would know better, citing The Betrothed by Alessandro Manzoni): if you are a techie and you do not know me , you can stop reading. No tech tips inside, nor 5 gazillion queries per second improvement in this post, sorry!

    On the other hand, if curiosity is what drives you through the posts of PlanetMySQL  you may find bits in this post of some interest, or even helpful.

    Few days ago I opened my blog and found the last post, dated April 2013.

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