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

Displaying posts with tag: bugs (reset)

Warnings are now actual problems
+0 Vote Up -0Vote Down

Yesterday, I reached a happy milestone in HailDB development. All compiler warnings left in the api/ directory (the public interface to the database engine) are now either probable/possible bugs (that we need to look at closely) or are warnings due to unfinished code (that we should finish).

There’s still a bunch of compiler warnings that we’ve inherited (HailDB compiles with lots of warnings enabled) that we have to get through, but a lot will wait until after we update the core to be based on InnoDB 1.1.

Federated tables bug
+0 Vote Up -0Vote Down

Scenario
Recently came across this bug when trying out the federated storage engine for the first time in MySQL 5.1. Had a security table with user information on a remote server & database that needed to be joined to a local table housing site-specific permissions but only containing user IDs. I definitely wanted to use the “create server” method for the new table in case we later decided to link to a different table in the same remote database. A terrific little feature of the MySQL federated storage engine, to be sure.

Problem
The local server was the master in a replication pair. After executing the create server statement on the master, I proceeded to create the new federated table pointed to the new remote server. That’s when my mysql replication


  [Read more...]
Two subtle bugs in OUTER JOIN queries
+7 Vote Up -0Vote Down

OUTER JOIN queries in SQL are susceptible to two very subtle bugs that I’ve observed a number of times in the real world. Daniel and I have been hammering out ways to automatically detect queries that suffer from these bugs, in a relatively new Maatkit tool called mk-query-advisor. It’s part of our series of advisor tools for MySQL. I wrote a blog post about it a while ago. Automated analysis of bad query patterns is a good thing to write tools to do, because catching buggy queries is hard work if you do it manually.

Let’s dive right in and analyze these subtle bugs. Warning: if you don’t understand how SQL handles NULL, you’re not going to understand the following. Many

  [Read more...]
Joining on range? Wrong!
+4 Vote Up -0Vote Down

The problem I am going to describe is likely to be around since the very beginning of MySQL, however unless you carefully analyse and profile your queries, it might easily go unnoticed. I used it as one of the examples in our talk given at phpDay.it conference last week to demonstrate some pitfalls one may hit when designing schemas and queries, but then I thought it could be a good idea to publish this on the blog as well.

To demonstrate the issue let’s use a typical example – a sales query. Our data is a tiny store directory consisting of three very simple tables:

PLAIN TEXT SQL:
  • CREATE TABLE `products` (
  •   `prd_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  •   `prd_name` varchar(32) NOT NULL,
  •   [Read more...]
    mysql_upgrade and Innodb Tables
    +1 Vote Up -1Vote Down

    Upgrading from MySQL 5.0 to MySQL 5.1 or Percona Server 5.1 you may run into issues with mysql_upgrade - it will identify some tables to be upgraded and will attempt to run REPAIR TABLE for them. This will fail with "The storage engine for the table doesn't support repair" error message. This seems to confuse a lot of people and I've seen people doing failsafe upgrade path of dumping and reloading complete database confused by this error message, which of course works, but can take quite a lot of time.

    Another solution is to simply run ALTER TABLE tbl ENGINE=INNODB which will rebuild table with new MySQL version and normally will fix issues identified by mysql_upgrade.
    You can use mysqlcheck -A --check-upgrade to identify tables which need to be fixed such a way.

    With Oracle intentions to


      [Read more...]
    The Linux Bloke chuckles that Linux runs some Windows software (including Windows itself!) better than Windows does!!!
    +0 Vote Up -0Vote Down

    Our Universe is full of ironies. But some ironies are just too hard to take.

    As you may have guessed (!!!), I am an avid Linux developer and user. Though once upon a time I did develop under Windows. Yes, believe it. And on one particular case, I got to be on a first-name basis with some of the Microsoft Software Engineers to resolve issues we were having with their OLE crap — what the Holy Gods of Microsoft decided to redub as “Active-X”.

    But I digress. For the past 10 years, I have been solid Linux and have defenestrated Windows for the most part. But as you know, you can never really completely eliminate Windows.  Despite your best efforts, it will always be (for now, at least) the 500 pound gorilla in any room you care to be in. The installed software base there is just staggering, and most have no Linux options.

    But then that’s why projects like Wine and the


      [Read more...]
    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...]
    10 Newer Entries Showing entries 61 to 70 of 109 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.