SMesa SMesa - 7 months ago 17
SQL Question

MySQL Error 1146 On Stored Procedure

I'm trying to set a value to a table, using the following stored procedure.

Create Definer = Current_User Procedure sp_set_drivers_cost (In driver_id Int)
Not Deterministic
Begin
-- Variable that will holds the driver's total cost.
Declare driver_cost Numeric(65,2) Default 0;
-- Declaring loop_counter and a varialbe that will hold the sum of the cost.
Declare loop_counter, temp_table_sum Int Default 1;
-- Variable that will hold the driver's total cost.
Declare local_cost Numeric(65,2) Default 0;

Declare temp1 int;

-- Dropping table if exists.
Drop Table If Exists MyDB.temp_policy_id;

-- Creating temp table that will hold the policy ids that the given driver is covered for.
Create Table MyDB.temp_policy_id (
id int not null auto_increment,
policy_id int not null default 0,
Primary Key (id)
);

-- Inserting the policy ids for the given driver id;
Insert Into MyDB.temp_policy_id(policy_id)
Select fk_policy_id
From link_drivers_policies
Where fk_driver_id = driver_id;

-- Counting the rows of the temp table.
Set temp_table_sum = (Select Count(*) From temp_policy_id);

-- Looping through the rows of the table in order to get the total cost for each driver.
While loop_counter <= temp_table_sum Do
Set temp1 = (Select policy_id From temp_policy_id Where id = loop_counter);
-- Getting the total cost, for the given driver, based on the policies they are covered by.
Set local_cost = local_cost + (Select cost From policies Where id = temp1);

-- Incrementing the loop counter by 1.
Set loop_counter = loop_counter + 1;
End While;

-- Updating the drivers cost.
Update MyDB.drivers
Set cost = local_cost
Where id = driver_id;

Drop Table MyDB.temp_policy_id;
End//


When I try to test the procedure, by calling it like "call sp_set_drivers_cost" I get the following error:
call sp_set_drivers_cost(1) Error Code: 1146. Table 'MyDB.MyDB' doesn't exist 0.437 sec.

I've tried childinsh solutions, like tingering with the variables that return numeric values, omitting the DB name, and restarting the MySQL service(I'm on Windows 7). Furthermore, the temporary table that I create I within the procedure exists within the database. So my guess is that the error happens within the loop, or during the "Update" statement. I'm using InnoDB. Thank you.

UPDATE
While trying to update the table "drivers" I also get the same error.

I tried to implement the solution posted here, as pointed out by @Norbert van Nobelen, but to no avail. Furthermore, I'd like to state that I've used tab to indent my code in the sections where I've written the rest of the stored procedures, and no bugs where present.

Answer

Since it is unclear where the issue comes from, you ended up in the "Why is there no debugger for stored procedures" situation.

To debug a stored procedure which compiles nicely and then does not run, you can follow two approaches:

  • Add logging lines to the procedure
  • Take it apart and run it step by step

Option 1: Add logging lines

As logging line is just a SELECT statement. This gets logged on the command line when you call the procedure. For example:

Create Definer = Current_User Procedure sp_set_drivers_cost (In driver_id Int)
Not Deterministic
Begin
-- Variable that will holds the driver's total cost. 
Declare driver_cost Numeric(65,2) Default 0;
-- Declaring loop_counter and a varialbe that will hold the sum of the cost.
Declare loop_counter, temp_table_sum Int Default 1;
-- Variable that will hold the driver's total cost.
Declare local_cost Numeric(65,2) Default 0;

Declare temp1 int;

-- Dropping table if exists.
Drop Table If Exists MyDB.temp_policy_id;

-- Creating temp table that will hold the policy ids that the given driver is covered for.
Create Table MyDB.temp_policy_id (
    id int not null auto_increment,
    policy_id int not null default 0,
    Primary Key (id)
 ); 
SELECT "Step 1 finished";
...

That way you can follow what executed and what not.

Option 2: Take your procedure apart step by step

This is similar to the log lines except that you would re-create your procedure for every step you have:

Create Definer = Current_User Procedure sp_set_drivers_cost (In driver_id Int)
Not Deterministic
Begin
-- Variable that will holds the driver's total cost. 
Declare driver_cost Numeric(65,2) Default 0;
-- Declaring loop_counter and a varialbe that will hold the sum of the cost.
Declare loop_counter, temp_table_sum Int Default 1;
-- Variable that will hold the driver's total cost.
Declare local_cost Numeric(65,2) Default 0;

Declare temp1 int;

-- Dropping table if exists.
Drop Table If Exists MyDB.temp_policy_id;

-- Creating temp table that will hold the policy ids that the given driver is covered for.
Create Table MyDB.temp_policy_id (
    id int not null auto_increment,
    policy_id int not null default 0,
    Primary Key (id)
 ); 
END;

CALL temp_policy_id;

And then the next part:

Create Definer = Current_User Procedure sp_set_drivers_cost (In driver_id Int)
Not Deterministic
Begin
-- Variable that will holds the driver's total cost. 
Declare driver_cost Numeric(65,2) Default 0;
-- Declaring loop_counter and a varialbe that will hold the sum of the cost.
Declare loop_counter, temp_table_sum Int Default 1;
-- Variable that will hold the driver's total cost.
Declare local_cost Numeric(65,2) Default 0;

Declare temp1 int;

-- Dropping table if exists.
Drop Table If Exists MyDB.temp_policy_id;

-- Creating temp table that will hold the policy ids that the given driver is covered for.
Create Table MyDB.temp_policy_id (
    id int not null auto_increment,
    policy_id int not null default 0,
    Primary Key (id)
 ); 

 -- Inserting the policy ids for the given driver id;
 Insert Into MyDB.temp_policy_id(policy_id)
 Select fk_policy_id
 From link_drivers_policies
 Where fk_driver_id = driver_id;
END;

CALL temp_policy_id;

Etc until your error shows. Most of the time when the exact line with the issue is known, the solution becomes more clear.

Comments