I am developing a api in Node.js using MySQL and sequelize.
When I try execute a specfic update query I get and error:
Error Code: 1142. UPDATE command denied to user
‘ncuser_2040’@’xxx-xx-xx-xxx’ for table ‘shopping_items’
I have checked another update statenent on the same api and it works ok.
This is the statement:
UPDATE my_recipes_db.shopping_items SET picked = true WHERE master_list = true
and ingredientName in (select * FROM (select ingredientName FROM my_recipes_db.shopping_items
WHERE owner_id = ownerId and shopping_list_name = shoppingList) as t);
In my search I found out that some update statements cant be executed with MySQL but this statement works fine in MYSQL Workbench for my localhost but not for my remote database hosted with Nodechef.
I havent had any other denied permissions on this app either.
Any ideas plz?
Malcolm
UPDATE:
SHOW GRANTS localhost
‘GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO root@localhost WITH GRANT OPTION’
‘GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON . TO root@localhost WITH GRANT OPTION’
‘GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT OPTION’
Version: ‘8.0.25’
Remotely:
‘GRANT USAGE ON . TO ‘ncuser_2040’@’%”
GRANT ALL PRIVILEGES ON my-recipes.* TO ‘ncuser_2040’@’%’
Version: ‘5.7.18-log’
>Solution :
Your grants show that you have privilege on my-recipes.* on the remote database, but in your query you ran successfully on your local instance, you reference tables in a schema named my_recipes_db. In other words, you don’t have any privileges on the remote instance to use a schema named my_reciped_db, so you get an error that you’re not allowed to use UPDATE.
I’d suggest you name your schemas the same on both local and remote MySQL instances, to avoid this confusion.
I would also recommend you use the same version of MySQL locally as the one you will deploy to on the hosted server. It’s probably not the issue in this case, but it might cause incompatibility surprises in other cases.