Ninjakreborn Posted November 18, 2013 Share Posted November 18, 2013 I have been doing some database importing/exporting over the last few days. For the most part things have been smooth, but I ran into a strange error that I have never dealt with before. A tablet called "admin". It's throwing an error when trying to access it in PHPMyADmin: View 'c41ATSer3db.admin' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them I am not entirely sure what this means. I have done some Google Searches, and tried to run some code in SQL to try to fix this (per what I found on Google) and ran into further errors and syntax errors. I am not even sure this table is using a view. It's simply a database table with standard administrative fields (username, password, email address, and so forth). Thoughts? Link to comment https://forums.phpfreaks.com/topic/284026-references-invalid-tables-or-columns-or-functions-or-definerinvoker-of-view-lack-rights-to-use-them/ Share on other sites More sharing options...
Ninjakreborn Posted November 18, 2013 Author Share Posted November 18, 2013 Actually it is a view. All of them are type MyISAM except for er3-admins which is actually a view. I didn't setup this database, so I am unfamiliar with how to fix this error. Any advice is appreciated, I just wanted to add that extra bit of information. The exact error I am getting, is as follows: #1356 - View 'c41ATSer3db.er3-admins' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them. Link to comment https://forums.phpfreaks.com/topic/284026-references-invalid-tables-or-columns-or-functions-or-definerinvoker-of-view-lack-rights-to-use-them/#findComment-1458844 Share on other sites More sharing options...
Ninjakreborn Posted November 18, 2013 Author Share Posted November 18, 2013 OK I ran some code that shows me the definition. How could I fix this so that it works, maybe this'll help: select `g`.`groupID` AS `groupID`,`g`.`groupName` AS `groupName`,`g`.`groupPermissions` AS `groupPermissions`,`gd`.`permissionID` AS `permissionID`,`gd`.`type` AS `type`,`gd`.`groupKey` AS `groupKey`,`gd`.`value` AS `value`,`u`.`userID` AS `userID`,`u`.`userName` AS `userName`,`u`.`firstName` AS `firstName`,`u`.`lastName` AS `lastName`,`u`.`middleInitial` AS `middleInitial`,`u`.`password` AS `password`,`u`.`email` AS `email`,`u`.`address` AS `address`,`u`.`address2` AS `address2`,`u`.`city` AS `city`,`u`.`state` AS `state`,`u`.`zip` AS `zip`,`u`.`registeredBy` AS `registeredBy`,`u`.`registerDate` AS `registerDate`,`u`.`lastLogin` AS `lastLogin`,`u`.`permissions` AS `permissions`,`u`.`superUser` AS `superUser` from ((`c41ATSer3db`.`er3-groups` `g` join `c41ATSer3db`.`er3-group-definitions` `gd`) join `c41ATSer3db`.`er3-users` `u`) where ((`g`.`groupName` = 'Administrator') and (`gd`.`type` = 'user') and (`gd`.`groupKey` = `g`.`groupID`) and (`u`.`userID` = `gd`.`value`)) Link to comment https://forums.phpfreaks.com/topic/284026-references-invalid-tables-or-columns-or-functions-or-definerinvoker-of-view-lack-rights-to-use-them/#findComment-1458849 Share on other sites More sharing options...
Barand Posted November 18, 2013 Share Posted November 18, 2013 Apart from the join definitions I couldn't see obvious errors but then I don't know your table definitions select `g`.`groupID` AS `groupID`, `g`.`groupName` AS `groupName`, `g`.`groupPermissions` AS `groupPermissions`, `gd`.`permissionID` AS `permissionID`, `gd`.`type` AS `type`, `gd`.`groupKey` AS `groupKey`, `gd`.`value` AS `value`, `u`.`userID` AS `userID`, `u`.`userName` AS `userName`, `u`.`firstName` AS `firstName`, `u`.`lastName` AS `lastName`, `u`.`middleInitial` AS `middleInitial`, `u`.`password` AS `password`, `u`.`email` AS `email`, `u`.`address` AS `address`, `u`.`address2` AS `address2`, `u`.`city` AS `city`, `u`.`state` AS `state`, `u`.`zip` AS `zip`, `u`.`registeredBy` AS `registeredBy`, `u`.`registerDate` AS `registerDate`, `u`.`lastLogin` AS `lastLogin`, `u`.`permissions` AS `permissions`, `u`.`superUser` AS `superUser` from `c41ATSer3db`.`er3-groups` `g` join `c41ATSer3db`.`er3-group-definitions` `gd` ON `gd`.`groupKey` = `g`.`groupID` join `c41ATSer3db`.`er3-users` `u` ON `u`.`userID` = `gd`.`value` where (`g`.`groupName` = 'Administrator') and (`gd`.`type` = 'user') BTW, all those column aliases are superfluous. Link to comment https://forums.phpfreaks.com/topic/284026-references-invalid-tables-or-columns-or-functions-or-definerinvoker-of-view-lack-rights-to-use-them/#findComment-1458918 Share on other sites More sharing options...
Ninjakreborn Posted November 19, 2013 Author Share Posted November 19, 2013 Yes I know. This was something done by another developer, I am just doing some imports. I figured out the issue. Something about security type "Definer". I changed it to USER and re-created the view. It works fine now. Some Mysql implementations can't support the DEFINER security definition or something. Thanks! Link to comment https://forums.phpfreaks.com/topic/284026-references-invalid-tables-or-columns-or-functions-or-definerinvoker-of-view-lack-rights-to-use-them/#findComment-1458963 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.