PDOStatement: Field 'id' doesn't have default value in lib\db\sql.php on line 235

[William Marquez]

Hello all, 

First off, let me apologize ahead of time if anything I write seems incoherent or ignorant. I am 100% new to F3 and I recently inherited a buggy app from a developer who left my organization. 

That being said, I've narrowed down my error to a line that is set as follows:

public function drop()
{

        $type = new Types($this->db);

        $type->getbyID($this->f3->get('PARAMS.id'));
        .
        .
        .

I have the routing for the page the causes the error set to:

GET|POST /@id/drop=Controller->drop

Other pages in this application have the exact same setup and work without any problem. However, in the method drop for some reason, it causes the following error:

Fatal error: PDOStatement: Field 'id' doesn't have a default value in D:\...\...\...\...\lib\db\sql.php on line 235 

I'm starting to get a little frustrated because I've already fixed 4 or 5 other errors in this application that made perfect sense, but this one's had me scratching my head for a few days now. Has anyone else encountered this situation that can explain to me I might be doing wrong? 

Thank you!!

[xfra35]

This error is thrown by your SQL engine, not by the framework.

Can you share the contents of the getById() method and tell us which SQL engine you're using?

[ikkez]

I heard this error before,.. does this script try to add a column to a table? if you alter an existing table and add a column that is not nullable and has no default value, you probably get this error message (mysql 5.4 or 5.5 i guess?!).  add a default value to the table column as the error suggests.

[William Marquez]

The method getbyID is defined below, and we're using MySQL 5.1.46 community edition.

    /**

     * This function searches by typeid.

     * @param string $typeid

     * @return query

     */

    public function getbyID($typeid) {

        $this->load(array('lower(typeid) =?', strtolower($typeid)));

        return $this->query;

    }

Thank you!

[William Marquez]

From what I gather, it's only trying to read from the table using the id parameter to search for the field typeid in the table. However, you're correct in that the table doesn't allow typeid to be null and has no default value for typeid. My only concern adding a default value is that typeid is the primary key for the table. Also, the field will hold values like 'Biology 1', 'Organic Chem', 'College Algebra', etc. 

[richgoldmd]

I think something else is happening here - the error refers to the field 'id' - this is a table column, not the parameter being passed, if I read it right. 

I'm wondering if the process is somewhere else creating a record and that is what is failing. 

If you put 

error_log($this->$db->log());

in your code somewhere before the method in question returns we can inspect the actual SQL that is being sent to the db. You probably need to wrap the calls in an exception handler to have the opportunity so:

try {
... existing code...
} catch (PDOException $e) {
   error_log($this->$db->log());
   throw $e; // propagte unless you have other plans
}

Of course for this to work, PDO exceptions have to be enabled when you create the db connection:

// Somewhere in your DB instantiation code....

new DB\SQL(
 "mysql:host={$db['host']};port={$db['port']};dbname={$db['dbname']}",
 $db['user'],
 $db['pass'],
 array(\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION)

[rmaj-red23]

Thank you very much! Your suggestion helped me determine that the previous developer created a view while developing the app and didn't put it on the production DB! Now the application works well enough to start using!