Announcement

Announcement Module
Collapse
No announcement yet.

slow log entry due to legitimate "Impossible WHERE"

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • slow log entry due to legitimate "Impossible WHERE"

    I have turned on the mysql logging of slow queries + full joins. One of the logged queries produces this "extra" message using EXPLAIN: "Impossible WHERE noticed after reading const tables"

    This is the query:
    SELECT * FROM setup where season = 2007;

    "season" is the primary key. The point of this query is to determine if the data row even exists.

    So my problem is: I don't want this query logged in the slow-query log. Yes, I realize that you could write a query that NEVER succeeds (e.g. "WHERE 0") but in this case it could.

    Is there anything I can do to prevent this particular type of query of being logged? A log file full of legitimate queries doesn't really help that much.

    Thanks,
    John

  • #2
    Have you investigated why you get a "Impossible WHERE" from that query?
    Because it sounds very strange since that query is as straight forward as can be.

    Comment


    • #3
      It's not a mystery. You get that error because 2007 is missing out of the primary index. It knows that because there is no entry in the primary index, this query can never succeed and gives that error.

      However, it is legitimate that this primary entry may not be defined. Furthermore, it's really not a good error because you could insert a row later that would succeed.

      Comment


      • #4
        Yes of course, but it only shows:
        * When you are using MyISAM tables
        * Have less than 2 rows in the table

        And since I'm using InnoDB tables by default I didn't get that error.

        No there doesn't exist any way to tell mysql to filter it out of the slow-query log.

        But since you will never get that error with any query that actually takes any time to process you could just filter out that string when you are looking at the slow query log.

        If you are on unix you can do something like this:
        Quote:


        grep -v 'Impossible WHERE noticed after reading const tables' [your_slow_query_log]



        Because if you really have an impossible where then MySQL only reports "Impossible WHERE".

        Or you can insert a couple of dummy rows in that table. It's not a nice solution but you will avoid the error message.

        Comment


        • #5
          Well, it appears that you are misinformed because this is an innodb table, although in rare cases it may only have one row in it. I still believe what I read is true: It's based on the primary key as I said before. Other unrelated queries in the slow log support that as well.

          As for the rest, if these legitimate queries can't be prevented from being put on the log in the first place, then I might as well turn off the logging other than those queries that really take a long time. It's just not very useful if it's going to log good queries as bad ones.

          Comment


          • #6
            Right you are again, damn I feel like a Junior here.

            You really have to do a test matrix to test all outcomes to be certain of something. )

            A unique index (like primary key) will always give the error that you are describing.
            My guess is that if you have a normalized database and you are asking for something using the primary key it _should_ be there.
            And if it is not then something is wrong hence error.


            But if you use a _normal_ index things are a bit different.
            * With less than 2 rows in the table.
            MyISAM will always report "impossible where noticed ..."
            InnoDB will only report "using where".
            (Which I actually think is a bug because it is also reporting 1 rows and the table is empty).

            * With 2 rows and more either one is not reporting anything.


            Regarding what is useful in the log file an what is not it is of course up to you do decide.

            But I think more information is better than no information.
            Because it is so easy to filter out things afterwards, while it is impossible to find out what happened if it wasn't logged in the first place.

            Try out using mysqldumpslow on your current log file with the "impossible where noticed" entries still left. See how much it affects what you see.

            Comment


            • #7
              Quote:

              My guess is that if you have a normalized database and you are asking for something using the primary key it _should_ be there.


              The database is normalized, but there is no guarantee this row of data exists. The whole point of the query is to test if this data exists and react accordingly. The non-existence of this row does not indicate an error.

              While it may not be up to me to say what should or should not be logged, I certainly believe there should be more granularity in the configuration file. I should be able to turn off logging of this type error.

              I want the slow-query log to log problems, not grow to gigabyte size with normal queries or require filtering. As a user, I'm certainly entitled to decree something useful or not, as it's my point of view. It's current implementation is not very useful to me.

              Comment

              Working...
              X