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 中文
Previous 30 Newer Entries Showing entries 61 to 90 of 103 Next 13 Older Entries

Displaying posts with tag: bugs (reset)

Two quick performance tips with MySQL 5.1 partitions
+13 Vote Up -1Vote Down
While I was researching for my partitions tutorial, I came across two hidden problems, which may happen often, but are somehow difficult to detect and even more difficult to fix, unless you know what's going on, and why. I presented both cases during my tutorial, but there were no pictures to convey the mechanics of the problem. Here is the full story.

TO_DAYS() prunes two partitions instead of one

If you are partitioning by date, chances are that you are using TO_DAYS(). And depending on how you have partitioned your table, your queries are as fast as you expect them to be. However, there are cases where your query takes twice as long as it should, and of course this will not make you

  [Read more...]
The MySQL documentation is not always right
+1 Vote Up -0Vote Down

Let me premise this post with the statement I think the MySQL documentation is an excellent and highly accurate resource. I think the MySQL docs team do a great job, however like software and people, documentation is not perfect.

As members of the MySQL community you can always contribute to improve the process by reading the documentation and logging any issues as Documentation Bugs.

Some time ago in a discussion with a friend and colleague, we were talking about changes in historical defaults that had been improved finally in MySQL 5.4 The specific discussion was on the new default innodb_buffer_pool_size and we both agreed it increased significantly. One said 1GB, the other said 128MB. Who was right? Well we both were, and we were both inaccurate depending on versions.

Referencing the 5.4 Manual in

  [Read more...]
Cloud openness contemplated
+0 Vote Up -0Vote Down

I caught some of the keynotes and discussion at the Linux Foundation Collaboration Summit today, and was particularly interested in the panel discussion on open source and cloud computing. While we are used to hearing and talking about how important open source software is to cloud computing (open source giving to cloud computing), moderator John Mark Walker posed the question of whether cloud computing gives back? The discussion also rightfully focused on openness in cloud computing, how open source might or might not translate to cloud openness and the importance of data to be open as well.

The discussion also centered on some issues regarding open standards and how open is open

  [Read more...]
Missleading Innodb message on recovery
+1 Vote Up -0Vote Down

As I wrote about 2 years ago the feature of Innodb to store copy of master's position in Slave's Innodb tablespace got broken. There is a lot of discussions at the corresponding bug report while outcome of the fix remained uncertain for me (the bug is market duplicate while the bugs it seems to be duplicate for describe different issues).
Anyway. The customer came to me today having the following message in the error log after Slave crash while running MySQL 5.1.41 (extra virgin version without XtraDB or Percona Patches)

InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 10000000, file name mysql-bin.005000
InnoDB: Last MySQL binlog

  [Read more...]
OpenOffice File Menu “Randomly” Pops Up on Ubuntu.
+0 Vote Up -0Vote Down

If you are one of the few unfortunate blokes that’s pulling your hair out over random File Menu popups on OpenOffice — Word or Spreadsheet — and have been hitting your head against a brick wall trying to find the solution, I hear you.

I intially thought there was some interaction with Skype, but now I have to retract that statement. In all honesty, I have no clue what the problem is.

But I decided to just watch the “random” File Menu problem and time it. And now I am more confused than ever.

On my computer running 64-bit Ubuntu 9.10 (Karmic), I noticed that the File Menu toggles twice a second when OpenOffice has the focus. One toggle event happens precisely on the 11th second; the other toggle event happens around 35th to the 45th second. The second toggle seems to be related to when I launch OpenOffice; the first is always precisely on the 11th second.

  [Read more...]
Getting around optimizer limitations with an IN() list
+1 Vote Up -0Vote Down

There was a discussion on LinkedIn one month ago that caught my eye:

Database search by "within x number of miles" radius?

Anyone out there created a zipcode database and created a "search within x numer of miles" function ?
Thankful for any tips you can throw my way..


A few people commented that some solutions wouldn't scale. To understand why these sorts of geographic search queries are problematic in MySQL, it's best to show some execution plans on dummy data:

  [Read more...]
MySQL Wishlist
+0 Vote Up -0Vote Down

While I worked on several web projects I collected a few wishes for MySQL (this collection is more user specific - a MySQL internal wishlist will come later) - which I use in most cases. I still fixed a few things, but don't know if they are good enough for production (because I just started hacking MySQL).

Read the rest »

How many partitions can you have ?
+0 Vote Up -1Vote Down

I had an interesting case recently. The customer dealing with large MySQL data warehouse had the table which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements. The performance was extremely slow. I turned out it is caused by hundreds of daily partitions created for this table. What is the most interesting (and surprising) not every statement is affected equally as you can see from the benchmarks above:

I got the following test table created:

  • CREATE TABLE `p10` (
  •     `id` int(10) UNSIGNED NOT NULL,
  •     `c`  int(10) UNSIGNED NOT NULL,
  •      PRIMARY KEY (`id`),
  •      KEY(c)
  •      )
  •   [Read more...]
    Rare evil MySQL Bug
    +0 Vote Up -0Vote Down

    There is the rare bug which I ran into every so often. Last time I've seen it about 3 years ago on MySQL 4.1 and I hoped it is long fixed since... but it looks like it is not. I now get to see MySQL 5.4.2 in the funny state.

    When you see bug happening you would see MySQL log flooded with error messages like this:

    091119 23:03:34 [ERROR] Error in accept: Resource temporarily unavailable
    091119 23:03:34 [ERROR] Error in accept: Resource temporarily unavailable
    091119 23:03:34 [ERROR] Error in accept: Resource temporarily unavailable
    091119 23:03:34 [ERROR] Error in accept: Resource temporarily unavailable

    filling out disk space

    Depending on the case you may be able to connect to MySQL through Unix Socket or TCP/IP or neither.
    It also looks like there is a correlation between having a lot of tables and

      [Read more...]
    MySQL Cluster 7.0.8a and 6.3.27a released (source)
    +0 Vote Up -0Vote Down
    Fixes regression - bug 47844.

    Configurator and Sandbox scripts are updated to use this version.

    If you build from source then you can upgrade using these scripts:
    Upgrade script from 7.0.7 -> 7.0.8a is here.
    Upgrade script from 7.0.8 -> 7.0.8a is here.
    Upgrade script from 6.3.26-> 6.3.27a is here.

    Follow the instructions in this

      [Read more...]
    MySQL Cluster 7.0.8/6.3.27 - serious regression!!!
    +0 Vote Up -0Vote Down
    A regression in MySQL Cluster 7.0.8 and 6.3.27 has caused them to be removed from download.

    If you have a mysql server with higher node id than 49, then it will cause cluster to crash.

    An updated version of MySQL Cluster 7.0.8, called 7.0.8a will be released very soon.

    The Configurator has been reverted back to use 7.0.7 and 6.3.26.
    Data node stuck in phase 101 - what to do?
    +0 Vote Up -0Vote Down
    Sometimes I have seen and heard about that a data node gets stuck in start phase 101.

    Unfortunately it is difficult to reproduce this (found no way yet), so no bug fix is in the pipe yet.

    What happens is that in sp 101, the starting data node (actually a block called SUMA) should reconnect to the mysql server and take over the event handling (sending events to the mysql server), but it never gets the reconnect to the mysql server(s). A better explanation is here :)

    If you see your data node stuck here then try the following:
    1) Restart the mysql servers (one by one), the data node should now start
    2) Restart the mysql servers and restart the data node.

    Only do 2) if 1) does not work.

    And if you

      [Read more...]
    OPTIMIZE TABLE on Cluster (revisited)
    +4 Vote Up -0Vote Down
    Jonas just wrote a patch to this bug on OPTIMIZE TABLE, and the issue that was also discussed in this blog post. Jonas also fixed this bug when he was at it.

    Before, OPTIMIZE TABLE hardly freed up any pages and to defragment you had to do a rolling restart of the data nodes.

    Now, there is only a 2% discrepancy between OPTIMIZE TABLE and doing a rolling restart. This is great stuff.
    This will fix will make it into 6.3.26 and 7.0.7.

    See below for details:

    Creating two tables, t5 and t6:

    CREATE TABLE `t5` (
    `id` varchar(32) CHARACTER SET utf8 NOT NULL,

      [Read more...]
    Upgrade 6.3.x to 7.0.6
    +2 Vote Up -1Vote Down
    Currently a number of users has reported upgrade issues on 6.3.x to 7.0.6 (below is an example what might happen to you).

    Thus, we don't recommend you to upgrade from 6.3.x to 7.0.6 in a production system yet.
    However, we are very interested in bug reports and and help with testing.
    We are also of course working on fixing these issues and will keep you posted when things has improved. Sorry for the inconvenience.

    Currently the "best practice" to upgrade is to:
    1) backup / mysqldump
    2) start cluster 7.0.6 with initial so it is completely blank
    3) restore backup / load dump

    ERROR 1005 (HY000): Can't create table 'test.#sql-3d25_aa905' (Errno: 140)

    mysql> show warnings;
    | Level | Code | Message

      [Read more...]
    Crashes while using MyISAM with multiple key caches
    +3 Vote Up -1Vote Down

    Over last couple of years I have ran into random MySQL crashes in production when multiple key caches were used. Unfortunately this never was frequent or critical enough issue so I could spend time creating repeatable test case and search of the bug in the MySQL database did not find anything. Recently we had this problem again and now discussed it with Monty's team - this time we found the bug for this issue.

    It is no surprise why I could not find the bug easily - the bug is not really related to multiple key caches but to online key cache resize. It is just this code most actively used in case you're using multiple key caches. It is very rare one would resize single key cache in production and it only triggers crash sometimes, while if you're using multiple key caches there are

      [Read more...]
    Limit on General Query Log Size?
    +0 Vote Up -0Vote Down

    I ran into a rather interesting situation today with a client. It seems that the mysqld daemon stopped with no errors in the error log. I ran through the obvious problems … not enough disk space, memory utilization etc and came up empty.

    The server was running MySQL 4.1 on Fedora Core 5. We can save the discussion about running your database on reasonable up to date hardware and operating system for another post. Core 5 runs the GNU/Linux kernel 2.4 along with the ext3 filesystem and so the thought was in the back of my mind that it might be an issue with file size. Well, as Sun’s own documentation shows this shouldn’t be the case.

    During the investigation it was uncovered that the general query log was not only enabled but 16 gigabytes in size. Aside

      [Read more...]
    Failing by choice. Another bug-vs-feature debate ends
    +3 Vote Up -1Vote Down

    A long standing bug

    Among the many outstanding bugs for MySQL, there is one that has sparked a fierce discussion, not only in the bug report itself, but also in blogs, forums, mailing lists.
    Bug #19027: MySQL 5.0 starts even with Fatal InnoDB errors was neglected for long time, until finally it got fixed, and it is available in MySQL 5.1.36.
    First off, what is it about?
    In short, if an engine doesn't initialize correctly, the MySQL server starts anyway, without the offending engine. Depending on how you use the failing engine, this could be either a minor annoyance or a complete disaster.
    Annoyance: ARCHIVE fails to initialize, and you create tables with the default engine (usually MyISAM), and after a while you realize that the disk is filling up faster than you expected. You will find

      [Read more...]
    iptables trick to limit concurrent tcp connections
    +0 Vote Up -0Vote Down
    This is sort of a self-documenting post, and a self-support group about ill-behaved tomcat apps. Sometimes, you have multiple nodes accesing your MySQL server (or any kind of server, for that matter) concurrently. Eventually, software in one or more of these nodes might do nasty things (you know who you are buddy:)) MySQL provides a […] Related posts:
  • Using MySQL Proxy to benchmark query performance By transparently sitting between client and server on each request,...
  • Related posts brought to you by Yet Another Related Posts Plugin.
    Cluster fails to start - self diagnosis
    +0 Vote Up -0Vote Down
    If the Cluster fails to start, what can you do and what to check?

    Initial system start
    If it is the first time you start up the cluster and it fails, then check the following:
    • Network - Check /etc/hosts
      You should have (on the line for localhost) localhost
      and nothing else! Then the normal IP address host mapping follows..
      REDHAT and others can add a lot of other things to that line, and this results in that the nodes won't be able to connect to each other (they will be stuck in phase 0 or phase 1)
    • Network - Check if you can ping the machines
    • Network - Check if you have any firewalls enables ( e.g check with /sbin/iptables -L)
      Disable the firewall in that case. Exactly how depends on OS and Linux distribution.
      On Redhat systems, then SELinux might be enabled. Googling

      [Read more...]
    OurDelta mysqld_safe patch makes it back into MySQL
    +2 Vote Up -2Vote Down

    Last year, Erik Ljungstrom (sensei in the #ourdelta IRC channel on Freenode) created patch for a bug Arjen identified with the handling of (among others) the open-files-limit option; the patch was first included in the OurDelta build of MySQL 5.0.67.

    Now, Sun engineer Guilhem Bichot has committed the (modified) patch into the 6.0-maria tree, so it should appear in 6.0 and potentially 5.4. That’s good.

    See http://bugs.mysql.com/40368 for details and history.

    max_rows in cluster
    +0 Vote Up -0Vote Down
    If you are going to load a lot of records into Cluster, don't forget to set max_rows!
    My colleague, Yves at BigDBAhead, has also blogged about this, but I also ran into the same problem recently.

    I did try to populate 100M records on a 4 node cluster, and the data nodes went down with the following error message in the error logs:

    "2304 Array index out of range"

    So the error message is crap - and my opinion is that there should be a proper error message propagated up to the mysql server. There is a bug report on this.

    Simplified, what the error message means is that you have run out of "index slots" in the Hash Table storing the hashes of the Primary Keys. This is because each table is divided into a number of partitions, and

      [Read more...]
    Another usability bug bites the dust
    +0 Vote Up -0Vote Down
    In MySQL 5.1.33 there is a fix for an apparently innocuous bug.
    Bug #36540 CREATE EVENT and ALTER EVENT statements fail with large server_id.
    This is a usability bug, that makes the DBA life unnecessarily hard. The reason for having a large server_id is because a DBA might want to use the IP address as server ID, to make sure that there are unique IDs, and to have an easy way of identifying the server through the IP.
    All is well until you mix the server_id assignment with event creation:

    select version();
    | version() |
    | 5.1.32 |
    1 row in set (0.00 sec)

    set global server_id =inet_aton('');
    Query OK, 0 rows affected (0.00 sec)

    select @@server_id;

      [Read more...]
    Don’t Quote Your Numbers
    Employee +0 Vote Up -0Vote Down

    It’s a fairly simple rule, and something that should be obeyed for your health and sanity.

    There are a couple of bugs which you could run into, when quoting large numbers. First of all, Bug #34384. This is concerning quoting large INTs in the WHERE condition of an UPDATE or DELETE. It seems that this will cause a table scan, which is going to be slooooow on big tables.

    Similarly, there is the more recently discovered Bug #43319. You can run into this if you quote large INTs in the IN clause of a SELECT … WHERE. For example:

    mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999')\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            TABLE: NULL
             type: NULL
    possible_keys: NULL
      [Read more...]
    Triggers, table locks and NDB
    +0 Vote Up -0Vote Down
    At a customer yesterday, I confirmed what Jonas suspected and what is probably related to bug 42474. Scroll down and look for the output of SHOW PROCESSLIST.
    It seems that TRIGGERs causes Table Locks to be taken when used with Ndb cluster tables!! I have created a bug report .

    If we do an update on a table that has an update trigger the trigger will upon execution lock the entire table that is affected by the trigger.

    This was verified by having several threads updating random records in a table. When one update gets to execute, the trigger will block the other updates from happening. This was shown using SHOW FULL PROCESSLIST¸ which shows a bunch of

      [Read more...]
    Funniest bug ever
    +0 Vote Up -0Vote Down

    Recently my attention was brought to this bug which is a nightmare bug for any consultant.

    Working with production systems we assume reads are reads and if we're just reading we can't break anything. OK may be we can crash the server with some select query which runs into some bug but not cause the data loss.

    This case teaches us things can be different - reads can in fact cause certain writes (updates) inside which add risk, such as exposed by this bug.

    This is why transparency is important - to understand how safe something is it is not enough to know what is this logically but also what really happens inside and so what can go wrong.

    Entry posted by peter | 19 comments

    Add to:

      [Read more...]
    What's going on in MySQL land?
    Employee +0 Vote Up -0Vote Down

    Enough of one-sided stories. Let's see a different angle of MySQL 5.1.

    First, let me thank my colleague Chris Powers for taking a stand in defense of the management. But saying "everyone does so" is not a good explanation. The truth is much more complex and requires some narrative.

    MySQL 5.1 didn't start on the right foot. The effort to produce its features was underestimated, mostly because, at the time when it was designed, the company was still unearthing the architectural bugs that were haunting MySQL 5.0.

    MySQL 5.0 was GA in October 2005. One month later, MySQL 5.1 started its alpha stage, while a rain of bugs fell on the freshly released server.

      [Read more...]
    Quality of 5.1 GA release
    +0 Vote Up -0Vote Down

    With all due respect to Monty (and I mean that — much respect is due), I have some serious issues with his portrayal of the 5.1 release.  I hate to make my first entry on Planet MySQL about a controversy, but he encouraged people to blog about their experience with 5.1, so that’s what I’ll do here.

    Overall Quality

    As a long time user, I am very confident that the quality of 5.1 GA far exceeds that of the initial 5.0 GA release (5.0.15).  In fact, I would go further and suggest that the MySQL organization has if anything been too conservative about declaring 5.1 GA.

    It’s obviously true that there are still many bugs open.  However no software is bug free, especially not those with codebase as large as MySQL.  So the question is not if they are bug free,

      [Read more...]
    Yet another replication trap
    +0 Vote Up -0Vote Down

    When I filed Bug#39197 replication breaks with large load with InnoDB, flush logs, and slave stop/start, I genuinely thought that it was a serious problem. I was a bit puzzled, to tell the truth, because the scenario that I was using seemed common enough for this bug to be found already.
    Anyway, it was verified independently, but there was a catch. The script in the master was using SET storage_engine=InnoDB to create the tables necessary for the test. That looked good enough to me. The script was indeed creating InnoDB tables on the master. The trouble was that the "SET"

      [Read more...]
    What Bugs makes you to recomend upgrade most frequently ?
    +0 Vote Up -0Vote Down

    What bug makes you to recommend upgrading most frequently ? For me it is this bug which makes it quite painful to automate various replication tasks.

    It is not the most critical bug by far but this makes it worse - critical bugs would usually cause upgrades already or were worked around while such stuff as causing things like "sometimes my slave clone script does not work" may hang on for years.

    Entry posted by peter | One comment

    Add to:

      [Read more...]
    +0 Vote Up -0Vote Down

    How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table... which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting AUTO_INCREMENT set for the Merge Table itself. Neither of these expectations really true:

  • Query OK, 0 rows affected (0.01 sec)
  • mysql> CREATE TABLE a2 LIKE a1;
  • Query OK, 0 rows affected (0.00 sec)
  • mysql>
  •   [Read more...]
    Previous 30 Newer Entries Showing entries 61 to 90 of 103 Next 13 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.