zf2 The supplied parameters to DbTable failed to produce a valid sql statement, please check table and column names for validity

Question

On authentication I am getting an error message "The supplied parameters to DbTable failed to produce a valid sql statement, please check table and column names for validity." my login controller code is

$form= new login();
        $request=$this->getRequest();
        if ($request->isPost())
        {

            $formValidator = new rotaryfilter();
            $post=$request->getPost();
            $form->setInputFilter($formValidator->getInputFilter());
            $form->setData($request->getPost());


      if($form->isValid())
            {
                $formValidator->exchangeArray($form->getData());

                $dbAdapter = $this->serviceLocator->get('Zend\Db\Adapter\Adapter');

                $authAdapter = new DbTable($dbAdapter,'Login','username','pwd');


                $authAdapter->setIdentity($formValidator->username)
                            ->setCredential($formValidator->pwd);
                //->setCredentialTreatment('MD5(?)');
                $authService = $this->serviceLocator->get('auth_service');
                $authService->setAdapter($authAdapter);

                $result = $authService->authenticate();

                if($result->isValid())
                {
                    echo 'valid';
                    exit();
                }
                else { echo 'invalid';exit();}

            }
        }
        return array('form'=> $form);

and my module.php contains

public function getServiceConfig()
{
    return array(
            'factories' => array(

                'auth_service' => function ($sm) {
                    $authService = new AuthenticationService(new SessionStorage('auth'));

                    return $authService;
                },

                    'General\Model\Login' =>  function($sm) {
                        $tableGateway = $sm->get('LoginGateway');
                        $table = new Login($tableGateway);
                        return $table;
                    },
                    'LoginGateway' => function ($sm) {
                        $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                        $resultSetPrototype = new ResultSet();
                        $resultSetPrototype->setArrayObjectPrototype(new rotaryfilter());
                        return new TableGateway('Login', $dbAdapter, null, $resultSetPrototype);
                    },

                    ),);
}

Answer 1

This may seems old but I was able to solve this error. This error is caused from you MySQL version.

This one works for me. All you need to do is to remove the driver_options from your db setup, this code is usually located at your global.php or .local.php from your Config file.

Change FROM:

'db' => array(
    'driver'         => 'Pdo_Mysql',
    'dsn'            => 'mysql:dbname=dbName;host=localhost',
    'username'       => 'dbUser',
    'password'       => 'dbPass',
    'driver_options' => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
    ),
),

TO

'db' => array(
    'driver'         => 'Pdo_Mysql',
    'dsn'            => 'mysql:dbname=dbName;host=localhost',
    'username'       => 'dbUser',
    'password'       => 'dbPass',
),

Thank you. This solution solved my problem.