CODEWITHSUNDEEP
phpstored-proceduresmysqli
Pattle
Pattle

Reputation: 6016

MySQL trying to run stored procedure as different user

I have the following code that is executing a store procedure using this function

$proc->bind_param("i", $eventId_);
if (!$proc->execute())
{
    die("Database execution failed. Error: \n" . $proc->error);
}

But I am getting an error 

Database execution failed. Error: There is no 'db_jamce'@'%' registered

What's strange is that the the database user 'db_jamce' isn't anywhere in the codebase so it must be a database issue. It's asthough the store procedure is trying to be executing as a different user that no longer exists. Has anyone else had this kind of problem before?

Upvotes: 0

Views: 1040

Answers (1)

PatomaS
PatomaS

Reputation: 1603

Usually, you would do steps like this to use the stored procedure. Check the users on both places, the PHP files and the SQL procedure on the database.

I usually have a separate process for the connection, but this is more or less a representation of the sequence

<?php
    // initialize variables
    $handler = false;
    $server = false;
    $user = false;
    $password = false;
    $database = false;

    // assign right values for variables. Specially the user that is going to execute the procedure.

    // create a handler for this session with the DB
    $handler = mysqli_init();

    // connect using the right values
    $var_to_check_state_of_connection = mysqli_real_connect( $handler, $server, $user, $password, $database );

    // not neccessary in your case, but is good practice
    mysqli_set_charset( $handler, "utf8" );

    // initialize the statement
    $var_to_check_state_of_statement = mysqli_stmt_init( $handler );

    // prepare the statement for execution
    $var_to_check_state_of_prepare = mysqli_stmt_prepare( $handler, "call right_procedure( ? )" );

    // bind param
    $var_to_check_state_of_binding = mysqli_stmt_bind_param( $handler, 's', $your_variable );

    // execute the statement
    $var_to_check_state_of_execution = mysqli_stmt_execute( $handler );
?>

You also have to check the procedure itself, because it may have a DEFINER set, like this:

CREATE DEFINER = 'user'@'localhost' PROCEDURE `your_procedure`
...
SQL SECURITY DEFINER

The security parameter may be INVOKER.

Upvotes: 1

Related Questions