#3 "explain query" truncates queries

closed-fixed
None
5
2007-10-01
2007-08-28
Peter Poeml
No

Hi,

I find that "explain query" doesn't work, because truncates the query, which leads to an error message. Here is a screenshot:

-----
EXPLAIN
DELETE FROM file_server WHERE serverid = 44 AND timestamp_scanner <= (SELECT last_scan FROM server WHERE id = 44 limit 1)

The query could not be explained: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM file_server WHERE serverid = 44 AND timestamp_scanner <= (SELECT las' at line 2 at /usr/bin/innotop line 4826, <FIN> line 1.

Press e to explain, f for full query, o for optimized query
-----

I am using innotop 1.4.3, perl-DBD-mysql-3.0002, perl-5.8.8, MySQL-5.0.26.

Thanks for innotop. It is great.

Discussion

  • Baron Schwartz
    Baron Schwartz
    2007-09-10

    Logged In: YES
    user_id=541183
    Originator: NO

    Hi,

    Apologies for the delay, I didn't get an email from this submission.

    This issue could be because InnoDB Status doesn't show the full query, and truncates it at (depending on the version) either 300 or 600 bytes. If you use the 'explain' feature from Q mode, this issue doesn't happen. Do you think this is what caused the problem?

     
  • Baron Schwartz
    Baron Schwartz
    2007-09-10

    • assigned_to: nobody --> bps7j
     
  • Baron Schwartz
    Baron Schwartz
    2007-09-19

    • status: open --> closed
     
  • Baron Schwartz
    Baron Schwartz
    2007-09-19

    Logged In: YES
    user_id=541183
    Originator: NO

    No response. I don't think this is an innotop bug.

     
  • Peter Poeml
    Peter Poeml
    2007-09-19

    Logged In: YES
    user_id=78531
    Originator: YES

    Baron,

    I had tested it again a few times, but sometimes it worked, sometimes not.

    I am not sure what to make of this. Anyways, I can reproduce it frequently.

    Peter

     
  • Peter Poeml
    Peter Poeml
    2007-09-19

    • status: closed --> open
     
  • Baron Schwartz
    Baron Schwartz
    2007-09-19

    Logged In: YES
    user_id=541183
    Originator: NO

    Hi Peter,

    I re-read your original message and think I understand it better. The issue isn't truncation, though it appears to be. Your query above is a DELETE, which can't be EXPLAIN-ed. innotop tries to rewrite a few simple types of non-SELECT queries into SELECT for EXPLAIN, but if it can't, you'll get a syntax error as you showed. The resulting error message doesn't include the full text of the query, making it look like it's been truncated at "SELECT las", but that's just the error message, not the actual EXPLAIN query.

    I have been considering ways to rewrite DELETE and UPDATE queries into SELECT queries that mention all the same columns, but haven't found any yet without writing a fairly complicated SQL parser and transformer. If you have any suggestions, please let me know.

     
  • Peter Poeml
    Peter Poeml
    2007-09-19

    Logged In: YES
    user_id=78531
    Originator: YES

    Ah. Stupid me!

    Okay, if it doesn't work on other requests than SELECT, I would suggest to not do it.

    Maybe "delete from" could be rewritten into "select * from", but everything else is probably difficult.

    Anyway, either skipping those queries, or warning that a rewritten query might not work, would be fine I think.

    The current error message is a bit confusing anyway :-)

    Thanks.

     
  • Baron Schwartz
    Baron Schwartz
    2007-09-24

    Logged In: YES
    user_id=541183
    Originator: NO

    I agree with you. I'll certainly make the error message more helpful. Thanks for the suggestion.

     
  • Baron Schwartz
    Baron Schwartz
    2007-10-01

    Logged In: YES
    user_id=541183
    Originator: NO

    This is fixed in revision 370 and will be released with the next release. Thanks for the bug report.

     
  • Baron Schwartz
    Baron Schwartz
    2007-10-01

    • status: open --> closed-fixed