SQLite3 Unable to write to database file on Amazon AWS but only in certain instances

Question

I have setup and EC2 instance on AWS and as part of this instance I am using a SQLite3 database to handle certain data. All of the database operations are routed through a single PHP file, with a single connection:

function dataQuery($query)
{
    // establish database connection
    try
    {
        $dbh = new PDO(DBW); // try windows first
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
        $errorCode = $e->getCode();

        // windows not available, try linux
        if('14' == $errorCode)
        {
            try
            {
                $dbh = new PDO(DBL); // try linux
                $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            }
            catch(PDOException $e)
            {
                echo $e->getMessage();
                $errorCode = $e->getCode();
            }
        }
    }

    // try to run query
    try
    {
        $queryResults = $dbh->query($query);
        if($queryResults != null)
        {
            $results = $queryResults->fetchAll(PDO::FETCH_OBJ); // always return an object
            $queryResults = NULL; // closes the connection
            return $results;
        }
    }
    catch(PDOException $e)
    {
        $errorMsg = $e->getMessage();
        return $errorMsg;
    }
}

DBW and DBL are constants defined just a couple of lines earlier. The error I am getting:

could not find driver

"Why is this strange?". you ask. "Because the problem is not consistent.", I answer. Let me elaborate...

The first action taking place when someone goes to the website is a login which requires reading from and writing to the database. This works fine (as the application does on any 'normal' server).

2|Jay Blanchard|jayblanchard@thewebsite.com|foo|{"roles":["admin", "surveyor"]}|2015-06-04 15:32:29|69.1.164.40

There are a couple of other places where the reads always occur and writes don't. Sometimes writing works.

I have followed over a dozen links all pretty much saying the file permissions are wrong. I have carefully followed the file permissions instances on servers where this is working and faithfully replicated them:

drwxrwxrwx 10 ubuntu ubuntu 4096 Jun  4 15:32 application-gateway

And inside that directory the database file itself:

-rwxrw-rw-  1 ubuntu ubuntu  65536 Jun  4 15:32 application.db

All of the files attempting to access the database have ubuntu as the group and owner.

I have followed other links to make sure the PDO extensions is properly included in /etc/php5/apache2/conf.d/

lrwxrwxrwx 1 root root   32 Jun  3 15:29 05-opcache.ini -> ../../mods-available/opcache.ini
lrwxrwxrwx 1 root root   28 Jun  3 15:29 10-pdo.ini -> ../../mods-available/pdo.ini
lrwxrwxrwx 1 root root   29 Jun  3 15:29 20-json.ini -> ../../mods-available/json.ini
lrwxrwxrwx 1 root root   31 Jun  3 15:33 20-mysqli.ini -> ../../mods-available/mysqli.ini
lrwxrwxrwx 1 root root   30 Jun  3 15:33 20-mysql.ini -> ../../mods-available/mysql.ini
lrwxrwxrwx 1 root root   34 Jun  3 15:33 20-pdo_mysql.ini -> ../../mods-available/pdo_mysql.ini
lrwxrwxrwx 1 root root   33 Jun  3 15:29 20-readline.ini -> ../../mods-available/readline.ini
lrwxrwxrwx 1 root root   35 Jun  3 20:17 pdo_sqlite.ini -> ../../mods-available/pdo_sqlite.ini

I am not sure what I am missing here. The driver is obviously there and works for read. The driver doesn't change positions during a write and some writes are occuring.

Confirming the PDO driver is available to PHP -

Have I got the AWS instance configured wrong?

Answer 1

After hours of reading and kibitzing and scratching my head, I finally figured out what was causing the issue.

The application uses a mixture of web and command-line calls to the database depending on the action being taken. These include full-time services, CRON jobs and exec's of PHP scripts from other scripts outside of the web-server. I was able to track the issue down to the difference between PHP for the web and PHP CLI.

While all of these services use the script, shown above, to connect to the database and run their queries, the location of drivers is dependent on the origination of the request. If the request originates from a command line call the included file (above) treats everything as if resources should be available from the command line.

The drivers for SQLite3 needed to be included in the configuration files for PHP CLI. Once the reference to the SQLite drivers was made properly in the PHP CLI, everything began to work as designed.

ubuntu@foo:/etc/php5/cli/conf.d$ ls -la
total 8
drwxr-xr-x 2 root root 4096 Jun  4 19:16 .
drwxr-xr-x 3 root root 4096 Jun  3 15:29 ..
lrwxrwxrwx 1 root root   32 Jun  3 15:29 05-opcache.ini -> ../../mods-available/opcache.ini
lrwxrwxrwx 1 root root   28 Jun  3 15:29 10-pdo.ini -> ../../mods-available/pdo.ini
lrwxrwxrwx 1 root root   29 Jun  3 15:29 20-json.ini -> ../../mods-available/json.ini
lrwxrwxrwx 1 root root   31 Jun  3 15:33 20-mysqli.ini -> ../../mods-available/mysqli.ini
lrwxrwxrwx 1 root root   30 Jun  3 15:33 20-mysql.ini -> ../../mods-available/mysql.ini
lrwxrwxrwx 1 root root   34 Jun  3 15:33 20-pdo_mysql.ini -> ../../mods-available/pdo_mysql.ini
lrwxrwxrwx 1 root root   35 Jun  4 19:15 20-pdo_sqlite.ini -> ../../mods-available/pdo_sqlite.ini
lrwxrwxrwx 1 root root   33 Jun  3 15:29 20-readline.ini -> ../../mods-available/readline.ini
lrwxrwxrwx 1 root root   32 Jun  4 19:16 20-sqlite3.ini -> ../../mods-available/sqlite3.ini