Forums

Hi - I'm getting the following error while trying to run a stored procedure in MYSQL Workbench and also from within a Db mysql command line within python anywhere: ERROR 1142 (42000): INSERT command denied to user 'stevenpj'@'10.0.0.69' for table 'consultantsServices'

This works great on my localhost environment. I was also able to create and see the stored procedures from within mysql workbench just fine. But, I'm getting this error for all of my stored proc.

I'm just doing a simple call: CALL sp_insertConsultantServices(@consultantServiceID,60, 195, 'Interview', 1);

Any idea what could be causing this?

Are you following https://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere/ Are you able to do other things from your Workbench?

Yes. I’m. Able to drop tables, alter tables and add data manually to all tables from within MySQL workbench via ssh.

FYI. I’m getting error from python anywhere GUI command line as well

What happens if you try to insert into that table normally from outside a stored procedure?

I am able to insert to all of my tables just fine via my webpage and python routes. I am also able to insert manually while SSH into server using MySQL workbench

Are the inserts exactly the same? I'm wondering if there's some subtle difference in the way the table is referenced in the stored procedure that is (for example) making MySQL think that it should be using a table of that name in a different database, or something like that. For example, if the SP accidentally did something like

insert into someotherdatabase.consultantsServices ....

then that might cause the error you're seeing.

No, it's only referencing the database I'm using for this project. Here's the procedure:

CREATE DEFINER=`stevenpj`@`%` PROCEDURE `sp_insertConsultantServices`(
  out consultantServiceIDP int unsigned,
  consultantServiceDurationP int unsigned,
  consultantServicePriceP int unsigned ,
  serviceDescriptionP varchar(255) ,
  consultantIDP int unsigned

)
BEGIN
-- CALL `interview`.`sp_insertConsultantServices`(@consultantServiceID,30, 30, 'Tax Advice', 4);
-- select @consultantServiceID;

declare var_serviceID int;
declare var_consultantServiceID int default 0;
set var_serviceID=1;
start transaction;
-- Get signup source id from the signup source description
set var_serviceID=(select serviceID from services where serviceDescription=serviceDescriptionP) ;

-- verify if valid id
IF (var_serviceID=0 or var_serviceID is null)
then 
    set var_serviceID=1; 
end if;
-- Insert data into consultantServicess
INSERT INTO `interview`.`consultantsServices`
(
  consultantServiceDuration ,
  consultantServicePrice ,
  serviceID  ,
  consultantID )
VALUES
(
  consultantServiceDurationP ,
  consultantServicePriceP ,
  var_serviceID  ,
  consultantIDP
  );
-- get last inserted id for ConsultantsServices
SET var_consultantServiceID = LAST_INSERT_ID();
-- if successful complete transaction else rollback
IF var_consultantServiceID > 0 THEN
    SET consultantServiceIDP = var_consultantServiceID;
    -- commit if everything goes fine
    COMMIT;
else
    ROLLBACK;
end if;

END

[formatted by admin]

This

INSERT INTO `interview`.`consultantsServices`

looks like it is referencing a database and that database name would not be valid on PythonAnywhere

Sorry, but why would it not be a valid name?

Because every database on PythonAnywhere is named in the form "username$databasename". The names of your databases are shown on your "Databases" page.