Laravel 5 - Cannot find MySQL Stored Procedure

Question

ISSUE: Converting my L4 code to L5.2 and am receiving the following error: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION cr_debug.ENTER_MODULE2 does not exist (SQL: call POPULATE_DAYS_TABLE(20, "01/29/2016")) & SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION cr_debug.ENTER_MODULE2 does not exist

UPDATE: I don't know what the function FUNCTION cr_debug.ENTER_MODULE2 is. I have no idea what that is from. This appears to be what is causing the issue because that function doesn't exist in MySQL nor do I know where it came from. Using Agent Ransack in my Laravel 5.2 installation I only find the log files that mentions ENTER_MODULE2 and doing a text search in my Laravel 4 installation for ENTER_MODULE2 I only found a SQL Dump that mentions that text. That dump is listed below. I did find a reference to ENTER_MODULE2 actually in my Stored Procedure though. Checking it out and will post back later.

SQL DUMP:

DECLARE cr_stack_depth INTEGER DEFAULT cr_debug.ENTER_MODULE2('FIRST_DAY', 'contracts', 8, 100430)/*[cr_debug.1]*/;

  CALL cr_debug.UPDATE_WATCH3('`firstday`', `firstday`, 'DATE', cr_stack_depth)/*[cr_debug.2]*/;

CALL cr_debug.TRACE(4, 4, 0, 5, cr_stack_depth)/*[cr_debug.2]*/;

CALL cr_debug.TRACE(5, 5, 2, 67, cr_stack_depth)/*[cr_debug.2]*/;

SET @devart_debug_ret = ADDDATE(LAST_DAY(SUBDATE(firstday, INTERVAL 1 MONTH)), 1)/*[cr_debug.2]*/;

CALL cr_debug.LEAVE_MODULE(cr_stack_depth - 1)/*[cr_debug.2]*/;

RETURN @devart_debug_ret;

CALL cr_debug.TRACE(6, 6, 0, 3, cr_stack_depth)/*[cr_debug.2]*/;

CALL cr_debug.LEAVE_MODULE(cr_stack_depth - 1)/*[cr_debug.2]*/;

END ;;
DELIMITER ;

ATTEMPTED: I have searched Stackoverflow and Google to find what is happening. I have attempted to change call to EXEC and that created another error stating that my version of MySQL doesn't support call. I just installed the latest version MySQL two weeks ago. I have tried to namespace the procedure call to no avail. I have removed all spaces from function calls within the Stored Procedure (Example: IF (var) is now IF(var) or function (var) is now function(var). I have dropped the stored procedure and received the following different error: SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE contracts.POPULATE_DAYS_TABLE does not exist (SQL: call POPULATE_DAYS_TABLE(29, "01/29/2016"))

REQUEST: Please assist in helping figure out what is wrong and explain in detail what I am doing wrong with examples, if possible.

NOTES: I have severely shortened the ContractController.php file for brevity. If you need to see the stored procedures I can display those too. The stored procedures are in MySQL. This works in Laravel 4.

DEBUG INFORMATION: ERROR 1 OF 2 PDOException in Connection.php line 390: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION cr_debug.ENTER_MODULE2 does not exist

in Connection.php line 390
at PDOStatement->execute(array()) in Connection.php line 390
at Connection->Illuminate\Database\{closure}(object(MySqlConnection), 'call POPULATE_DAYS_TABLE(20, "01/29/2016")', array()) in Connection.php line 644
at Connection->runQueryCallback('call POPULATE_DAYS_TABLE(20, "01/29/2016")', array(), object(Closure)) in Connection.php line 611
at Connection->run('call POPULATE_DAYS_TABLE(20, "01/29/2016")', array(), object(Closure)) in Connection.php line 391
at Connection->statement('call POPULATE_DAYS_TABLE(20, "01/29/2016")')
at call_user_func_array(array(object(MySqlConnection), 'statement'), array('call POPULATE_DAYS_TABLE(20, "01/29/2016")')) in DatabaseManager.php line 317
at DatabaseManager->__call('statement', array('call POPULATE_DAYS_TABLE(20, "01/29/2016")')) in Facade.php line 218
at Facade::__callStatic('statement', array('call POPULATE_DAYS_TABLE(20, "01/29/2016")')) in computer.php line 15
at computer::storedProcedureCall('20', '01/29/2016') in ContractController.php line 56
at ContractController->store()
at call_user_func_array(array(object(ContractController), 'store'), array()) in Controller.php line 76
at Controller->callAction('store', array()) in ControllerDispatcher.php line 146
at ControllerDispatcher->call(object(ContractController), object(Route), 'store') in ControllerDispatcher.php line 94
at ControllerDispatcher->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in ControllerDispatcher.php line 96
at ControllerDispatcher->callWithinStack(object(ContractController), object(Route), object(Request), 'store') in ControllerDispatcher.php line 54
at ControllerDispatcher->dispatch(object(Route), object(Request), 'App\Http\Controllers\ContractController', 'store') in Route.php line 174
at Route->runController(object(Request)) in Route.php line 140
at Route->run(object(Request)) in Router.php line 703
at Router->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in Router.php line 705
at Router->runRouteWithinStack(object(Route), object(Request)) in Router.php line 678
at Router->dispatchToRoute(object(Request)) in Router.php line 654
at Router->dispatch(object(Request)) in Kernel.php line 246
at Kernel->Illuminate\Foundation\Http\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in CheckForMaintenanceMode.php line 44
at CheckForMaintenanceMode->handle(object(Request), object(Closure))
at call_user_func_array(array(object(CheckForMaintenanceMode), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 124
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in Kernel.php line 132
at Kernel->sendRequestThroughRouter(object(Request)) in Kernel.php line 99
at Kernel->handle(object(Request)) in index.php line 53

ERROR 2 OF 2 QueryException in Connection.php line 651: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION cr_debug.ENTER_MODULE2 does not exist (SQL: call POPULATE_DAYS_TABLE(20, "01/29/2016"))

in Connection.php line 651
at Connection->runQueryCallback('call POPULATE_DAYS_TABLE(20, "01/29/2016")', array(), object(Closure)) in Connection.php line 611
at Connection->run('call POPULATE_DAYS_TABLE(20, "01/29/2016")', array(), object(Closure)) in Connection.php line 391
at Connection->statement('call POPULATE_DAYS_TABLE(20, "01/29/2016")')
at call_user_func_array(array(object(MySqlConnection), 'statement'), array('call POPULATE_DAYS_TABLE(20, "01/29/2016")')) in DatabaseManager.php line 317
at DatabaseManager->__call('statement', array('call POPULATE_DAYS_TABLE(20, "01/29/2016")')) in Facade.php line 218
at Facade::__callStatic('statement', array('call POPULATE_DAYS_TABLE(20, "01/29/2016")')) in computer.php line 15
at computer::storedProcedureCall('20', '01/29/2016') in ContractController.php line 56
at ContractController->store()
at call_user_func_array(array(object(ContractController), 'store'), array()) in Controller.php line 76
at Controller->callAction('store', array()) in ControllerDispatcher.php line 146
at ControllerDispatcher->call(object(ContractController), object(Route), 'store') in ControllerDispatcher.php line 94
at ControllerDispatcher->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in ControllerDispatcher.php line 96
at ControllerDispatcher->callWithinStack(object(ContractController), object(Route), object(Request), 'store') in ControllerDispatcher.php line 54
at ControllerDispatcher->dispatch(object(Route), object(Request), 'App\Http\Controllers\ContractController', 'store') in Route.php line 174
at Route->runController(object(Request)) in Route.php line 140
at Route->run(object(Request)) in Router.php line 703
at Router->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in Router.php line 705
at Router->runRouteWithinStack(object(Route), object(Request)) in Router.php line 678
at Router->dispatchToRoute(object(Request)) in Router.php line 654
at Router->dispatch(object(Request)) in Kernel.php line 246
at Kernel->Illuminate\Foundation\Http\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 52
at Pipeline->Illuminate\Routing\{closure}(object(Request)) in CheckForMaintenanceMode.php line 44
at CheckForMaintenanceMode->handle(object(Request), object(Closure))
at call_user_func_array(array(object(CheckForMaintenanceMode), 'handle'), array(object(Request), object(Closure))) in Pipeline.php line 124
at Pipeline->Illuminate\Pipeline\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 32
at Pipeline->Illuminate\Routing\{closure}(object(Request))
at call_user_func(object(Closure), object(Request)) in Pipeline.php line 102
at Pipeline->then(object(Closure)) in Kernel.php line 132
at Kernel->sendRequestThroughRouter(object(Request)) in Kernel.php line 99
at Kernel->handle(object(Request)) in index.php line 53

ContractController.php:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Http\Requests;
use App\Http\Controllers\Controller;
use App\Library\customer;
use App\Library\computer;

class ContractController extends Controller
{
    Protected $layout = 'master';
    public function store()
    {
        // Receive input from Form
        $input = \Input::only([
            'contractterm_id', 'businesstype_id', 'company', 'bcity', 'bphone', 'bstate', 'bstraddr',
            'bzip', 'firstname', 'lastname', 'mobile', 'hcity',
            'hphone', 'hstate', 'hstraddr', 'hzip'
        ]);

        $morepcs_array = \Input::only('addtpcmake','addtpcmodel','addtpcserial','addtpcname');
    /*  
        // Debugging Code
        foreach ($morepcs_array as $textbox_name => $textbox)
            {
                echo "<br><br>".$textbox_name;
                echo "<br>textbox quantity: ".sizeof($textbox);
                foreach($textbox as $value) {
                    if ($value == NULL) {
                        echo "<br>NULL";
                    }
                    else {
                        echo "<br>".$value;
                    }
                }
            }
    */  
        $customer = new customer($input); // Create new customer object. Store $input into this object.
        $computer = new computer; // Create new computer object
        $computer->addtpcs = \Input::get('addtpcs'); // Get the form data for addtpcs and prepare to store it in a database table named addtpcs.
        $computer->save(); // Save the information into the database table addtpcs from the computer object.
        $customer->computer()->associate($computer); // Using the associate function, store the id from the computer table in the database to the customer_id table in the database.
        $customer->save(); // send all of the data to the customer table in the database.

        // $startdate = new day;
        $startdate = \Input::get('contract_date');
        // $customer->startdate()->save($startdate);

        //Log::info('$startdate from controller before it is passed to the StoredProcedureCall method: ' . $startdate);
        //Log::info('$customer->id from controller before it is passed to the StoredProcedureCall method: ' . $customer->id);
        //Computer::storedProcedureCall($customer->id,$startdate);
        //$days = Day::all()->last(); // Never do this.  For testing purposes only.
        // Figure out total contract cost based upon Contract Term and Business Type
        computer::storedProcedureCall($customer->id,$startdate); // Call the MySQL stored procedure.
}

computer.php:

Class computer extends \Eloquent {
 protected $guarded = array();

 public function customer() {
    return $this->hasMany('App\Library\customer');
 }

 public static function storedProcedureCall($customer, $contract_date) {
         //Log::info('$contract_date after it is received from the controller: ' . $contract_date);
         //Log::info('$data after it is received from the controller: ' . $data);
         return \DB::statement('call POPULATE_DAYS_TABLE(' . $customer . ', "'.$contract_date.'")');
    }
 public static $rules = array();
  }

ERROR LOG:

[2016-01-31 16:36:20] local.ERROR: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION cr_debug.ENTER_MODULE2 does not exist in C:\Apache24\B2B_Contracts\vendor\laravel\framework\src\Illuminate\Database\Connection.php:390
Stack trace:
#0 C:\Apache24\B2B_Contracts\vendor\laravel\framework\src\Illuminate\Database\Connection.php(390): PDOStatement->execute(Array)
#1 C:\Apache24\B2B_Contracts\vendor\laravel\framework\src\Illuminate\Database\Connection.php(644): Illuminate\Database\Connection->Illuminate\Database\{closure}(Object(Illuminate\Database\MySqlConnection), 'call POPULATE_D...', Array)
#2 C:\Apache24\B2B_Contracts\vendor\laravel\framework\src\Illuminate\Database\Connection.php(611): Illuminate\Database\Connection->runQueryCallback('call POPULATE_D...', Array, Object(Closure))
#3 C:\Apache24\B2B_Contracts\vendor\laravel\framework\src\Illuminate\Database\Connection.php(391): Illuminate\Database\Connection->run('call POPULATE_D...', Array, Object(Closure))
#4 [internal function]: Illuminate\Database\Connection->statement('call POPULATE_D...')

MORE ERRORS - SAME LOG FILE:

Next Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION cr_debug.ENTER_MODULE2 does not exist (SQL: call POPULATE_DAYS_TABLE(23, "01/29/2016")) in C:\Apache24\B2B_Contracts\vendor\laravel\framework\src\Illuminate\Database\Connection.php:651
Stack trace:
#0 C:\Apache24\B2B_Contracts\vendor\laravel\framework\src\Illuminate\Database\Connection.php(611): Illuminate\Database\Connection->runQueryCallback('call POPULATE_D...', Array, Object(Closure))
#1 C:\Apache24\B2B_Contracts\vendor\laravel\framework\src\Illuminate\Database\Connection.php(391): Illuminate\Database\Connection->run('call POPULATE_D...', Array, Object(Closure))
#2 [internal function]: Illuminate\Database\Connection->statement('call POPULATE_D...')
#3 C:\Apache24\B2B_Contracts\vendor\laravel\framework\src\Illuminate\Database\DatabaseManager.php(317): call_user_func_array(Array, Array)

Answer 1

Really Really strange however somehow MySQL created a bunch of functions that were added to my stored procedure which was causing it to break.

UPDATE 02/01/16: Shadow pointed out that the additional debugging was from dbforge studio which I did indeed download to debug the stored procedures years ago. The answer remains the same though. If you run into this issue, either restore from backups, remove the debugging garbage or reinstall the debugging software.

An example of this is the store procedure was supposed to look something like:

REAL STORED PROCEDURE

BEGIN
SET contract_date = STR_TO_DATE(contract_date, '%m/%d/%Y'); /* Changes parameter sent it to an actual date */
SELECT DATE_FORMAT(contract_date, '%Y-%m-%d') INTO @start_date; /* Store proper date format for Contract End Dates */

MODIFIED MYSQL STORED PROCEDURE

BEGIN
DECLARE cr_stack_depth INTEGER DEFAULT cr_debug.ENTER_MODULE2('POPULATE_DAYS_TABLE', 'contracts', 7, 100430)/*[cr_debug.1]*/;
CALL cr_debug.UPDATE_WATCH3('`customer_id`', `customer_id`, 'INT', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.UPDATE_WATCH3('`contract_date`', `contract_date`, 'TEXT', cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.TRACE(4, 4, 0, 5, cr_stack_depth)/*[cr_debug.2]*/;
CALL cr_debug.TRACE(5, 5, 0, 59, cr_stack_depth)/*[cr_debug.2]*/;
SET contract_date = STR_TO_DATE(contract_date, '%m/%d/%Y');
CALL cr_debug.UPDATE_WATCH3('contract_date', contract_date, '', cr_stack_depth)/*[cr_debug.1]*/; /* Changes parameter sent it to an actual date */
CALL cr_debug.TRACE(6, 6, 0, 63, cr_stack_depth)/*[cr_debug.2]*/;
SELECT DATE_FORMAT(contract_date, '%Y-%m-%d') INTO @start_date;

The resolution to this was to delete the stored procedures in MySQL and re-import them from backups that I had. If you don't have backups then you will have to manually remove all of the garbage that was added to them.

Answer 2

Due to weirdness in the call to the stored procedure you can try passing it into the underlying PDO object like..

$db = \DB::connection()->getPdo();
$stmt=$db->prepare($sql);
$stmt->bindParams($params);
$stmt->execute();

Just like you would do in native PHP. Redacted for brevity but I hope you get the idea