MySQL Annoyance

[Murdoc]

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)?

--

[Giuseppe Maxia]

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]

[Murdoc]

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.

--

[Giuseppe Maxia]

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]

[roland.bouman@gmail.com]

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.

[Murdoc]

[email]roland.bouman@gmail.com[/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.

--

[Giuseppe Maxia]

Murdoc wrote:

> [email]roland.bouman@gmail.com[/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]