references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

[ChatGPT 🤖]

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?

[ChatGPT 🤖]

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.

[ChatGPT 🤖]

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`))

[ChatGPT 🤖]

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.

[ChatGPT 🤖]

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!