Professional Web Applications Themes

MySQL Annoyance - MySQL

Hi all, I've finally had enough of this problem that I thought I'd see if there was a workaround. Every time I create a new view through the MySQL Administrator tool, and then select "Update View", the SQL generated resembles nothing like what I entered to begin with. All the line breaks are gone, comments are gone, table/column names are now enclosed in quotes, and my WHERE clauses are all replaced with JOINS. This makes updating of the view extremely time-consuming. Is there a way to turn this behaviour off, so that the VIEW statement is exactly as I entered ...

  1. #1

    Default MySQL Annoyance

    Hi all,

    I've finally had enough of this problem that I thought I'd see if there was a workaround.

    Every time I create a new view through the MySQL Administrator tool, and then select "Update View", the SQL generated resembles nothing like what I entered to begin with. All the line breaks are gone, comments are gone, table/column names are now enclosed in quotes, and my WHERE clauses are all replaced with JOINS.

    This makes updating of the view extremely time-consuming.

    Is there a way to turn this behaviour off, so that the VIEW statement is exactly as I entered it (as is done with Stored Procedures)?

    --

    Murdoc Guest

  2. #2

    Default Re: MySQL Annoyance

    Murdoc wrote:
    > Hi all,
    >
    > I've finally had enough of this problem that I thought I'd see if there was a workaround.
    >
    > Every time I create a new view through the MySQL Administrator tool, and then select "Update View", the SQL generated resembles nothing like what I entered to begin with. All the line breaks are gone, comments are gone, table/column names are now enclosed in quotes, and my WHERE clauses are all replaced with JOINS.
    >
    > This makes updating of the view extremely time-consuming.
    >
    > Is there a way to turn this behaviour off, so that the VIEW statement is exactly as I entered it (as is done with Stored Procedures)?
    >
    Unfortunately, there is no way to turn this behavior off. It is the same thing that happens to
    stored procedures, functions, and triggers.
    However, someone has given this matter some thought, and if you are inclined to try some hacks, read this enlightening
    article.

    [url]http://rpbouman.blogspot.com/2006/05/mysql-view-metadata-original-source.html[/url]

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: MySQL Annoyance

    Giuseppe Maxia wrote:
    > Murdoc wrote:
    > > Hi all,
    > >
    > > I've finally had enough of this problem that I thought I'd see if there was a workaround.
    > >
    > > Every time I create a new view through the MySQL Administrator tool, and then select "Update View", the SQL generated resembles nothing like what I entered to begin with. All the line breaks are gone, comments are gone, table/column names are now enclosed in quotes, and my WHERE clauses are all replaced with JOINS.
    > >
    > > This makes updating of the view extremely time-consuming.
    > >
    > > Is there a way to turn this behaviour off, so that the VIEW statement is exactly as I entered it (as is done with Stored Procedures)?
    > >
    >
    > Unfortunately, there is no way to turn this behavior off. It is the same thing that happens to
    > stored procedures, functions, and triggers.
    > However, someone has given this matter some thought, and if you are inclined to try some hacks, read this enlightening
    > article.
    >
    > [url]http://rpbouman.blogspot.com/2006/05/mysql-view-metadata-original-source.html[/url]
    >
    > ciao
    > gmax
    You say that the same thing happens with stored procedures, but any comments, etc. that I insert are retained through the MySQL Admin tool.

    As I said, it's more an annoyance than anything. I've just had to resort to copying the original SQL commands into a .txt file, and copying this into the editing window whenever I need to modify the view. Tediious, but it works.

    --

    Murdoc Guest

  4. #4

    Default Re: MySQL Annoyance

    Murdoc wrote:
    > Giuseppe Maxia wrote:
    >
    >> Murdoc wrote:
    >>> Hi all,
    >>>
    >>> I've finally had enough of this problem that I thought I'd see if there was a workaround.
    >>>
    >>> Every time I create a new view through the MySQL Administrator tool, and then select "Update View", the SQL generated resembles nothing like what I entered to begin with. All the line breaks are gone, comments are gone, table/column names are now enclosed in quotes, and my WHERE clauses are all replaced with JOINS.
    >>>
    >>> This makes updating of the view extremely time-consuming.
    >>>
    >>> Is there a way to turn this behaviour off, so that the VIEW statement is exactly as I entered it (as is done with Stored Procedures)?
    >>>
    >> Unfortunately, there is no way to turn this behavior off. It is the same thing that happens to
    >> stored procedures, functions, and triggers.
    >> However, someone has given this matter some thought, and if you are inclined to try some hacks, read this enlightening
    >> article.
    >>
    >> [url]http://rpbouman.blogspot.com/2006/05/mysql-view-metadata-original-source.html[/url]
    >>
    >> ciao
    >> gmax
    >
    > You say that the same thing happens with stored procedures, but any comments, etc. that I insert are retained through the MySQL Admin tool.
    >
    > As I said, it's more an annoyance than anything. I've just had to resort to copying the original SQL commands into a .txt file, and copying this into the editing window whenever I need to modify the view. Tediious, but it works.
    >
    Perhaps the Admin tool will store the original text somewhere, but the normal behavior is this:

    mysql > create procedure test1()
    -> -- this is a comment
    -> select table_name from information_schema.tables
    -> -- this is another comment
    -> where table_schame=schema();
    Query OK, 0 rows affected (0.05 sec)

    mysql > show create procedure test1\G
    *************************** 1. row ***************************
    Procedure: test1
    sql_mode:
    Create Procedure: CREATE DEFINER=`gmax``%` PROCEDURE `test1`()
    select table_name from information_schema.tables
    where table_schame=schema()
    1 row in set (0.00 sec)

    mysql > select * from mysql.proc where name='test1'\G
    *************************** 1. row ***************************
    db: test
    name: test1
    type: PROCEDURE
    specific_name: test1
    language: SQL
    sql_data_access: CONTAINS_SQL
    is_deterministic: NO
    security_type: DEFINER
    param_list:
    returns:
    body: select table_name from information_schema.tables
    where table_schame=schema()
    definer: gmax%
    created: 2006-08-20 15:24:51
    modified: 2006-08-20 15:24:51
    sql_mode:
    comment:

    As you can see, no comments are stored in the `procs` table. Therefore, there is no way
    for the DBMS to retrieve them.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  5. #5

    Default Re: MySQL Annoyance

    Hi people,

    The MySQL command line client scans and preprocesses the text. You can
    tell, because as you are editing the prompt changes accordingly. Any
    comments that are entered in this way don't even reach the server.

    However, other clients such as the MySQL Query Browser just pass the
    entire text to the server. Any comments are retained by the server, and
    stored, just like the other text.

    For views, it's a bit different. Of course, entering them via the
    MySQL command line client will lose all comments - that's just the wat
    the MySQL command line client works. But in addition, MySQL will feed
    the SELECT statement that defines the view through the query optimizer
    and this yields a let's say 'canonical' representation of the
    statement: the unqualified tablenames are resolved, * wildcard is
    resolved, and also, comments and whitespace are stripped.

    That is the representation that can be accessed from the information
    schema. Note that the original statement is nor discarded all together,
    and the blog entry mentioned by Giuseppw shows how to reclaim the
    original statement.

    roland.bouman@gmail.com Guest

  6. #6

    Default Re: MySQL Annoyance

    [email]roland.bouman[/email] wrote:
    > Hi people,
    >
    > The MySQL command line client scans and preprocesses the text. You can
    > tell, because as you are editing the prompt changes accordingly. Any
    > comments that are entered in this way don't even reach the server.
    >
    > However, other clients such as the MySQL Query Browser just pass the
    > entire text to the server. Any comments are retained by the server, and
    > stored, just like the other text.
    >
    > For views, it's a bit different. Of course, entering them via the
    > MySQL command line client will lose all comments - that's just the wat
    > the MySQL command line client works. But in addition, MySQL will feed
    > the SELECT statement that defines the view through the query optimizer
    > and this yields a let's say 'canonical' representation of the
    > statement: the unqualified tablenames are resolved, * wildcard is
    > resolved, and also, comments and whitespace are stripped.
    >
    > That is the representation that can be accessed from the information
    > schema. Note that the original statement is nor discarded all together,
    > and the blog entry mentioned by Giuseppw shows how to reclaim the
    > original statement.
    That seems to be an awful lot of work to go through in order to achieve what, I believe, would be a standard operation. Really, I don't care how MySQL optimises the query when the view is run, or even if it optimises it every time it is updated, but if I enter the query in one particular form (table names unquoted, using WHERE clauses instead of JOIN ON), then I should expect that this is the representation I receive when I, um, view the view's query?

    At the very least, I would expect it to be configurable. Oh well, looks like I'll just have to stick with notepad.

    --

    Murdoc Guest

  7. #7

    Default Re: MySQL Annoyance

    Murdoc wrote:
    > [email]roland.bouman[/email] wrote:
    >
    >> Hi people,
    >>
    >> The MySQL command line client scans and preprocesses the text. You can
    >> tell, because as you are editing the prompt changes accordingly. Any
    >> comments that are entered in this way don't even reach the server.
    >>
    >> However, other clients such as the MySQL Query Browser just pass the
    >> entire text to the server. Any comments are retained by the server, and
    >> stored, just like the other text.
    >>
    >> For views, it's a bit different. Of course, entering them via the
    >> MySQL command line client will lose all comments - that's just the wat
    >> the MySQL command line client works. But in addition, MySQL will feed
    >> the SELECT statement that defines the view through the query optimizer
    >> and this yields a let's say 'canonical' representation of the
    >> statement: the unqualified tablenames are resolved, * wildcard is
    >> resolved, and also, comments and whitespace are stripped.
    >>
    >> That is the representation that can be accessed from the information
    >> schema. Note that the original statement is nor discarded all together,
    >> and the blog entry mentioned by Giuseppw shows how to reclaim the
    >> original statement.
    >
    > That seems to be an awful lot of work to go through in order to achieve what, I believe, would be a standard operation. Really, I don't care how MySQL optimises the query when the view is run, or even if it optimises it every time it is updated, but if I enter the query in one particular form (table names unquoted, using WHERE clauses instead of JOIN ON), then I should expect that this is the representation I receive when I, um, view the view's query?
    >
    > At the very least, I would expect it to be configurable. Oh well, looks like I'll just have to stick with notepad.
    >
    Please notice that we are as displeased as you are about this issue. Don't get mad at us just because you have been
    shown a workaround.
    The matter has already been represented to the developers, and apparently it doesn't rank high enough in their list of
    priorities.
    You can help to increase such rank. Go to [url]http://bugs.mysql.com[/url] and file a feature request.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

Similar Threads

  1. Strange annoyance in Illustrator 10
    By G_A@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 6
    Last Post: June 8th, 09:33 PM
  2. An annoyance in Save For Web
    By Brendon_Burgess@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 0
    Last Post: April 23rd, 10:24 PM
  3. another annoyance
    By Getho in forum Macromedia Freehand
    Replies: 0
    Last Post: April 2nd, 03:56 AM
  4. SQL annoyance
    By Dan in forum PERL Beginners
    Replies: 3
    Last Post: January 4th, 05:35 PM
  5. Minor Annoyance in 4.0 LE
    By Lisa Sexton in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 9
    Last Post: August 13th, 07:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139