Bug – Unable To Edit View

[ErikEgon]

Just downloaded the latest version of SQLYog: 9.1.0.1. Altered a table, by dropping three unneeded columns. One view which is based on that table cannot be changed anymore. When I try, using the gui, an error is presented:

SHOW CREATE TABLE 'schema'.'viewname'

Error number: 1356

View 'schema'.'viewname' references invalid table(s) or …. etc

This behavior is not changed, if I remember correctly. After clicking OK I am not presented the view's source code to remove the non-existing columns; the dialog just closes. Until yesterday the source-code of the view was shown in an editor window so that I could correct the errors.

For the time being I retrieved the source from the INFORMATION_SCHEMA.VIEWS table.

Edit: the ABOUT tells me I'm running 9.10.

[peterlaursen]

This is a server error (and probably a server bug). All 4-digit error messages starting with '1' are server errors. Just execute SHOW CREATE etc. with any client. You will get exactly the same error.

Do you tell that some behavior in SQLyog was changed between latest and a previous build? I doubt. I think you did not exactly the same changes as before.

1) Please post a reproducible test case consisting of statements: CREATE TABLE(s) > CREATE VIEW > ALTER (or DROP) TABLE(s) > SHOW CREATE VIEW

2) Please also tell the server version.

[ErikEgon]

'peterlaursen' wrote:

This is a server error (and probably a server bug). All 4-digit error messages starting with '1' are server errors. Just execute SHOW CREATE etc. with any client. You will get exactly the same error.

You are right. I did this on an Ubuntu DEV server, 5.1.41-3ubuntu12.10. When I tried to reproduce this on a UAT environment at the hosting company I did NOT get any errors at all, just when browsing table data. They are using 5.0.90 (not sure what linux flavour they're on).

Sorry for the BUG report. Mistake on my part. I'll have a look with the admin what is causing the error.

Thanks for the quick reply, though!

[peterlaursen]

Not reproducible with this simple test case:

CREATE TABLE k (a VARCHAR(20));

CREATE TABLE l (b VARCHAR(20));

CREATE VIEW m AS SELECT k.a, l.b FROM k,l;

DROP TABLE k;

SHOW CREATE VIEW m;

Also the SQLyog GUI opens as it should from the view context-menu. But of course

SELECT * FROM m;

returns

“Error Code : 1356

View 'test.m' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them”

[peterlaursen]

And also if you try to show data for the View in the GRID, SQLyog will execute “SHOW FULL FILEDS FROM from `test`.`m`” what returns the same error.

[ErikEgon]

When on the command-line this error appears:

mysql> SHOW CREATE VIEW vw_organisatoren;


ERROR 1356 (HY000): View 'vgdagen2011.vw_organisatoren' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

This error is shown both for the owner of the schema/view and for root. No errors in the errorlogs on /var/log/mysql

[ErikEgon]

'peterlaursen' wrote:

And also if you try to show data for the View in the GRID, SQLyog will execute “SHOW FULL FILEDS FROM from `test`.`m`” what returns the same error.

Does not work. Am I looking at a wrongly installed mysql server?

Query : SHOW FULL FILEDS FROM 'vgdagen2011.vw_organisatoren' 





Error Code : 1064


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 'FILEDS FROM 'vgdagen2011.vw_organisatoren'' at line 1





Execution Time : 00:00:00:000


Transfer Time  : 00:00:00:000


Total Time     : 00:00:00:000

[ErikEgon]

'peterlaursen' wrote:

Not reproducible with this simple test case:

CREATE TABLE k (a VARCHAR(20));

CREATE TABLE l (b VARCHAR(20));

CREATE VIEW m AS SELECT k.a, l.b FROM k,l;

DROP TABLE k;

SHOW CREATE VIEW m;

Also the SQLyog GUI opens as it should from the view context-menu. But of course

SELECT * FROM m;

returns

“Error Code : 1356

View 'test.m' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them”

This reacts (in the GUI, that is) exactly as you predicted.

[peterlaursen]

It is reproducible. But it is a server bug! I have reported to MySQL bugs system: http://bugs.mysql.com/bug.php?id=61718

I think we could change our code to SELECT from I_S (at least with recent servers. In 5.0 we should avoid it as it may be terrible slow). But let us wait for the conclusion from the bug report.. They have just openend it.

[peterlaursen]

We have decided to implement like this: If SHOW CREATE VIEW returns an error we will prompt user if we should try to generate a CREATE VIEW statement dynamically from Information_Schema and do if user confirms.

There is one problem though: It is not always possible to recreate the ALGORITHM clause as it is not available in I_S. Refer: http://bugs.mysql.com/bug.php?id=61749

So what we will do is that if I_S.VIEWS.IS_UPDATABLE = YES we will make the ALGORITHM clause 'MERGE' and if it is = NO we will make the ALGORITHM clause 'TEMPTABLE'. This may be different from how the VIEW was created. We have no option to handle ALGORITHM = UNDEFINED (whether explicitly defined by user or implicitly by server due to an impossible 'MERGE') . But it will not affect how the VIEW works – only how the VIEW definition is displayed.

[Author]

Posts