Doctrine2, Error Messages, and You

Today, I’m going to share a nugget discovered while developing an API for exploring usage of AngularJS (v1) with Symfony 2.7. Specifically, I’ll be talking about a rather tricky error message encountered while creating some CRUD functionality for an admin portal.

(Author’s note – the Symfony/AngularJS exploration may become a post series at a later date. I’ll try to update this post with a link if so)

What Do You Mean I Can’t Insert This %&$@? Record?

So, starting out, I built out an Entity for my application. Signature as such:

<?php
namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Represents calendar events. May be of current or historical nature
 *
 * @author Daniel Lakes
 * @ORM\Entity(repositoryClass="AppBundle\Repository\EventRepository")
 */
class Event
{
    /**
     * @var int
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\Id()
     * @ORM\Column(type="integer")
     */
    protected $id;

    /**
     * @var string
     * @ORM\Column(type="string")
     */
    protected $title;

    /**
     * Some more properties here...
     */

    /**
     * @var boolean
     * @ORM\Column(type="boolean")
     */
    protected $repeat;
}

Anyone who’s worked with Doctrine should feel right at home. Couple of string fields, a nullable column. Nothing to write home about.

So, imagine my surprise, while running the doctrine:generate:crud command, when I get the following message:

An exception occurred while executing 'INSERT INTO event (date_time, end_date_time, title,
description, detail_url, repeat, all_day) VALUES (?, ?, ?, ?, ?, ?, ?)'
with params ["2010-01-01 00:00:00", null, "asdfa", "asdfasdf", "www.google.com", 0, 0]:

I almost did a spit take. This is Doctrine 101-level stuff.

Of course, following the old adage about insanity, I repeated the call 5-6 more times, just to make sure something weird didn’t happen. You know, gremlins and all.

OK…maybe the error feedback from the DB layer will be helpful:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax to
use near 'repeat, all_day) VALUES ('2010-01-01 00:00:00', NULL, 'asdfa', 'asdfasdf', 'www.' at line 1

At immediate glance, rather cryptic. Lacking perception at the time, I decide this requires examining the query Doctrine’s generating. Oh boy…

Down the Rabbit Hole

If you’ve developed at all with Doctrine (or any other ORM), you’re likely familiar with some of the gaps that appear in translation. Being well familiar with this scenario, I knew of some quick checks to make.

1. Do all of your nullable fields have nullable=true in the annotations? How about in your setters? Doubly so for any object-based values (here’s looking at you, \DateTime).
2. Does your object actually make it to the persistence layer?
3. Do all of your values match:

• a) what you input
• b) expected types?

So, I ran a quick mental check through those. Sure enough, everything was good.

This is the part where I forgot the one of the core tenants of programming, KISS and Occam’s Razor. It couldn’t _ possibly_ be my bad code. No…instead I decide it must be an issue with my database install. Ops/SysAdmin stuff isn’t part of my normal wheelhouse, and I’ve had brittleness issues with my VM before. Besides, with me using MariaDB v10, who knows, perhaps there’s a BC-break I’m not aware of. Right?

So, 10 minutes and a re-provisioned box later, I give it a spin. Still no dice.

…And Out the Other Side

At this point, I’m just about to throw in the towel. Fortunately, a brush with reserved keywords from about a year ago pops into my head. Taking a leap, I decide to pull up MySQL’s reserved keywords list. Scrolling down, sure enough, there’s REPEAT (R) plain as day. I update the column name to repeatable and the query runs without issue.

As far as how the column got set to repeat originally? My best guess is that the table creation query used identifiers `repeat`, whereas the insert call did not. That’s just an educated guess, though. I decided not to gaze into that particular abyss.

Lessons Learned

• Its You, Stupid – In general, this little exploit served to beat into my brain (again) looking for the simple solution. The problem is generally your code/domain knowledge/configuration/etc. Yes, PEBKAC holds true even for programmers. Don’t go lobbing bombs at third parties until you’ve definitively ruled you out. You can thank me later.
• Error Messages 101 – Also, trust the error message. MySQL really was trying to help…(check [...] for the right syntax to use near 'repeat[...]), I was just too stubborn to listen.