Jump to content

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


Ninjakreborn
Go to solution Solved by Ninjakreborn,

Recommended Posts

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
Share on other sites

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
Share on other sites

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
Share on other sites

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
Share on other sites

  • Solution

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
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.