I'm having a pretty weird issue here. We're using Pentaho Kettle to manage the schema of our database (which in turn uses the MySQL JDBC mysql-connector-java-5.1.17.jar). When trying to create a view which includes another view (interfaces in this case), we get:
2012/06/26 11:46:55 - SQL2 - ERROR : Couldn't execute SQL: CREATE OR REPLACE VIEW `test_delete2` as select * from interfaces
2012/06/26 11:46:55 - SQL2 - ERROR : ANY command denied to user 'ncim'@'xxx.xxx.xxx..xx' for table '/var/mysql/mysql2018/tmp/#sql_4e67_0'
However, using the same statement and user, but via the mysql command line client, works as expected. Also, creating views with JDBC over ordinary tables works as well.
This is with MariaDB 5.2.10 on Solaris 10 on the server side, but we're having the same issue with Oracle MySQL too.
Any good ideas what could cause this?
PS: I'm aware that creating views over views isn't the best idea ever, but assume for the moment that it can't be avoided in this case.
I found that issuing the use database statement before creating the view solved my problem. This seems to be related to a MySql bug report Bug #91122 "Failed to create a view containing a from subquery(No selected database)"
Basically if you try to create a view with the dbname.view_name syntax then the error can appear. However, if you issue the use database statement beforehand, then everything is good.