MySQL VIEW error "Table doesn't exist"

mysql> select table_name
    -> from information_schema.tables
    -> where table_type='VIEW';
+---------------+
| table_name    |
+---------------+
| ClinicalDrugs |
| Studies       |
| StudySites    |
| pkAnalytes    |
| pkSamples     |
| pkdetails     |
+---------------+
6 rows in set (0.00 sec)
mysql> select * from chassisDb.pkDetails;
ERROR 1146 (42S02): Table 'chassisDb.pkDetails' doesn't exist

The listing gives the named view but we are told it does not exist. I was also able to provoke

mysql> select * from viewname;
ERROR 1356 (HY000): View 'dbname.viewname' references invalid table(s) or 
column(s) or function(s) or definer/invoker of view lack rights to use them

If you try to access the view from JDBC then you will see

java.sql.SQLException: View 'chassisDb.Studies' references invalid table(s) or 
column(s) or function(s) or definer/invoker of view lack rights to use them
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)

This is caused by MySQL's weird, newish, security defaults for the CREATE VIEW command. 

create view Studies as 
select * from Entry, Study where Study.EntryId = Entry.Id;

is translated, after defaults are applied, to

CREATE VIEW SQL SECURITY DEFINER Studies AS 
SELECT  Entry.Id, Study.Id FROM Entry, Study WHERE Study.EntryId = Entry.Id;

If you now try to use this view as any user other than the definer of the view you will get the error above. What you wanted, and have to write explicitly to avoid the default, is

CREATE VIEW SQL SECURITY INVOKER Studies AS 
SELECT  Entry.Id, Study.Id FROM Entry, Study WHERE Study.EntryId = Entry.Id;

This page in the hope that it helps!

Simple MySQL reporting database

We have created an XML based repository of clinical trials study data.

To report upon this we create an isomorphic MySQL database using HyperJAXB. Our main configuration of hyperjaxb is to use an information preserving naming convention plugin.

The builds are under Continuous Integration. The database is created during the course of the Maven build, after this views are created and the database dumped.

mysqldump chassisDb  --complete-insert --skip-opt --add-drop-table  > $file

The dump file is then copied to an Amazon Web Services T1 instance, which only has MySQL installed. Two users exist: reader and uploader.

These two users have usernames and passwords configured in .my.cnf in their home directories containing

[client]
user=user
password=password

uploader has a cron job configured to update the database from any uploaded files:

mysql chassisDb < chassisDb_20120119.sql 

The database can now be accessed using the Pentaho reporting tool.