distal-attribute
distal-attribute
distal-attribute
distal-attribute

Creating a View

User, date Message
Written by mkervin
3 years ago
Category: General
12 posts since Thu, 31 Mar 11
Hello all, I am having a problem creating a view in MySQL (latest version). I can create the view but when I attempt to run it I receive an error 1356 I don't have permissions or a reference is invalid etc. I assure you I am logged in with all permissions I checked that three times on our server. When I open User Manager to add me to the users I receive an error 1142 I don't have select command priveledges...what am I missing. I created stored procedures etc. all without a problem but I can't create a view. I checked my server and I do have the ability to create views for the user I am logging in as too. Thank you...
Written by ansgar
3 years ago
4800 posts since Fri, 07 Apr 06
Please post
- the results of SHOW GRANTS here
- the result of SHOW CREATE VIEW yourview
Written by mkervin
3 years ago
12 posts since Thu, 31 Mar 11
GRANT USAGE ON *.* TO '54106_mkervin'@'' IDENTIFIED BY PASSWORD 'mypassword'
GRANT ALL PRIVILEGES ON `54106_models`.* TO '54106_mkervin'@''



diecast_models CREATE ALGORITHM=MERGE DEFINER=`54106_mkervin`@`` SQL SECURITY DEFINER VIEW `diecast_models` AS select `54106_models`.`tblboxnum`.`MODID` AS `MODID`,`54106_models`.`tblboxnum`.`TBLMINT_ID` AS `TBLMINT_ID`,`54106_models`.`tblboxnum`.`TBLMAKE_ID` AS `TBLMAKE_ID`,`54106_models`.`tblboxnum`.`TBLSCALE_ID` AS `TBLSCALE_ID`,`54106_models`.`tblboxnum`.`TRUCK` AS `TRUCK`,`54106_models`.`tblboxnum`.`DISCOUNTINUED` AS `DISCOUNTINUED`,`54106_models`.`tblboxnum`.`MOTORCYCLE` AS `MOTORCYCLE`,`54106_models`.`tblboxnum`.`LE` AS `LE`,`54106_models`.`tblboxnum`.`LEUNITSAVAILABLE` AS `LEUNITSAVAILABLE`,`54106_models`.`tblboxnum`.`RACECAR` AS `RACECAR`,`54106_models`.`tblboxnum`.`TEAM` AS `TEAM`,`54106_models`.`tblboxnum`.`VETTE` AS `VETTE`,`54106_models`.`tblboxnum`.`MISC` AS `MISC`,`54106_models`.`tblboxnum`.`BOAT` AS `BOAT`,`54106_models`.`tblboxnum`.`FIREEQUIP` AS `FIREEQUIP`,`54106_models`.`tblboxnum`.`MILITARY` AS `MILITARY`,`54106_models`.`tblboxnum`.`AMOUR` AS `AMOUR`,`54106_models`.`tblboxnum`.`FRAMTRAC` AS `FRAMTRAC`,`54106_models`.`tblboxnum`.`XMAS` AS `XMAS`,`54106_models`.`tblboxnum`.`DIECASTENGINE` AS `DIECASTENGINE`,`54106_models`.`tblboxnum`.`COKE` AS `COKE`,`54106_models`.`tblboxnum`.`BUDWEISER` AS `BUDWEISER`,`54106_models`.`tblboxnum`.`WRECKER` AS `WRECKER`,`54106_models`.`tblboxnum`.`TOON` AS `TOON`,`54106_models`.`tblboxnum`.`EIGHT_TEEN` AS `EIGHT_TEEN`,`54106_models`.`tblboxnum`.`TWENTY_FOUR` AS `TWENTY_FOUR`,`54106_models`.`tblboxnum`.`PREVIEW_SOCIETY` AS `PREVIEW_SOCIETY`,`54106_models`.`tblboxnum`.`WOODY` AS `WOODY`,`54106_models`.`tblboxnum`.`HOT_RODS` AS `HOT_RODS`,`54106_models`.`tblboxnum`.`CUSTOMS` AS `CUSTOMS`,`54106_models`.`tblboxnum`.`PACE_CARS` AS `PACE_CARS`,`54106_models`.`tblboxnum`.`DIORAMA` AS `DIORAMA`,`54106_models`.`tblboxnum`.`PREVIEW_EXCLUSIVE` AS `PREVIEW_EXCLUSIVE`,`54106_models`.`tblboxnum`.`MODELYEAR` AS `MODELYEAR`,`54106_models`.`tblboxnum`.`MODEL` AS `MODEL`,`54106_models`.`tblboxnum`.`COLOR` AS `COLOR`,`54106_models`.`tblboxnum`.`PARTNUM` AS `PARTNUM`,`54106_models`.`tblboxnum`.`IMAGE_PATH` AS `IMAGE_PATH`,`54106_models`.`tblboxnum`.`REVIEW_PATH` AS `REVIEW_PATH`,`54106_models`.`tblboxnum`.`YEAR_ISSUED` AS `YEAR_ISSUED`,`54106_models`.`tblboxnum`.`NEW_RELEASE_DATE` AS `NEW_RELEASE_DATE`,`54106_models`.`tblboxnum`.`PRICE` AS `PRICE`,`54106_models`.`tblboxnum`.`SHIPPING_HANDLING` AS `SHIPPING_HANDLING` from `tblboxnum`
Written by ansgar
3 years ago
4800 posts since Fri, 07 Apr 06
Looks ok so far - you have all privileges in the 54106_models database, and the view just selects from tblboxnum in that database. Perhaps the table is corrupted? What was the exact error message?
Written by mkervin
3 years ago
12 posts since Thu, 31 Mar 11
SQL Error 1356 invalid table or columns. Functions of definer/invoker of view lack rights to use them.

And it doesn't matter what fields or tables I try to create a View from I received the same error.
Written by ansgar
3 years ago
4800 posts since Fri, 07 Apr 06
See here for some explanation:
http://w3mentor.com/learn/mysql/mysql-errors/error-1356-hy000-view-%E2%80%98viewname%E2%80%99-references-invalid-tables-or-columns-or-functions-or-definerinvoker-of-view-lack-rights-to-use-them/

... but that page looks wrong as your view has SQL SECURITY DEFINER, not INVOKER. Hm.
Written by mkervin
3 years ago
12 posts since Thu, 31 Mar 11
Just to bring this to conclusion after three days on the phone with the hosting company they finally informed me that they don't allow views on their server. I can understand why but three days of support that didn't have a clue? But thanks for taking the time to address my question; it was/is appreciated.
Written by ansgar
3 years ago
4800 posts since Fri, 07 Apr 06
Shoot them to the moon and go to some competiting hoster.
 

Please login to leave a reply, or register at first.