Hi,
Just want to check if any one counter this error “references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them” In AWS RDS? This error was trigger when merging patient record. By the way the database we used was imported in our AWS Prod come from our Dev site which is located from another hosting (merging is working fine in here) and we just imported this to AWS RDS, Everything is working expect the merging patient record.
Thanks
Casper
The error:
references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
is commonly seen when a view or stored procedure references tables or functions that:
Don’t exist, or
Exist but aren’t accessible due to permissions, or
Are owned by a different DEFINER user which does not exist or doesn’t have the required privileges in the new AWS RDS environment.
Since you mentioned:
The database was imported into AWS RDS from a different hosting environment and merging works fine in DEV.
This strongly indicates a DEFINER issue or missing privileges. Let’s break this down and fix it.
Step-by-Step Fix
Step 1: Identify the Failing View / Procedure
Run this to identify any invalid views or routines:
SHOW FULL TABLES IN your_database_name WHERE TABLE_TYPE = ‘VIEW’;
Then for each view, run:
SHOW CREATE VIEW your_view_name;
Look for something like:
CREATE ALGORITHM=UNDEFINED DEFINER=devuser@% SQL SECURITY DEFINER VIEW …
If the DEFINER user (devuser) does not exist in RDS, this will fail.
Step 2: Update DEFINER for Views, Functions, Procedures
You must change all views/procedures/functions that use a DEFINER from the old host (devuser@%) to the current RDS user (e.g., admin or whatever you use to connect).
Example for View:
– Step 1: Dump the view
SHOW CREATE VIEW your_view_name;
– Step 2: Replace DEFINER
– Change this:
– CREATE DEFINER=devuser@% VIEW …
– To this:
– CREATE DEFINER=admin@% VIEW …
– Or remove DEFINER completely (safer in many cases)
– Step 3: Drop and recreate
DROP VIEW your_view_name;
CREATE VIEW your_view_name AS …;
For Stored Procedures:
SHOW CREATE PROCEDURE your_procedure_name;
Update the DEFINER as you did with views.
Step 3: Regrant Permissions (Optional)
Make sure your RDS user has the right permissions:
GRANT ALL PRIVILEGES ON your_database_name.* TO ‘admin’@‘%’;
FLUSH PRIVILEGES;
Or grant only what’s needed (SELECT, INSERT, EXECUTE, etc.)
Step 4: Disable DEFINER in Future Exports (Optional)
When exporting from your DEV environment next time, use --skip-definer to avoid this entirely:
mysqldump --skip-definer --no-tablespaces --routines --triggers -u root -p your_db > clean_dump.sql
Quick Fix for Already Exported SQL File
If you have a dump file from DEV, run this before importing to AWS RDS:
sed -i ‘s/DEFINER=[^]*@[^]*//g’ dumpfile.sql
This removes all DEFINER=… statements so RDS won’t try to enforce them.