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

Creating a View

mkervin posted 3 years ago in General
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...
ansgar posted 3 years ago
Please post
- the results of SHOW GRANTS here
- the result of SHOW CREATE VIEW yourview
mkervin posted 3 years ago
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`
ansgar posted 3 years ago
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?
mkervin posted 3 years ago
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.
ansgar posted 3 years ago
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.
mkervin posted 3 years ago
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.
ansgar posted 3 years ago
Shoot them to the moon and go to some competiting hoster.

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