SQL connection error from PHP after many operations

Question

I'm currently looping to create a MBTiles map, and add information to my database each time. Here's how I configured my connection and execute actions during the loop:

if ($pdo_mbtiles == null) {
    echo "Opening new database connection".PHP_EOL;
    $pdo_mbtiles = new PDO('sqlite:'.$filename,
            '',
            '',
            array(
                PDO::ATTR_PERSISTENT => true
                )
            );
    $pdo_mbtiles->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo_mbtiles->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}


$q = $pdo_mbtiles->prepare("INSERT INTO tiles (zoom_level, tile_column, tile_row,tile_data) VALUES (:zoom_level, :tile_column, :tile_rowTMS, :tile_data)");
$q->bindParam(':zoom_level', $zoom_level);
$q->bindParam(':tile_column', $tile_column);
$q->bindParam(':tile_rowTMS', $tile_rowTMS);
$q->bindParam(':tile_data', $tile_data, PDO::PARAM_LOB);
$q->execute();

After 1018 times looping (this number doesn't change no matter how many times I try), I get this error message:

SQLSTATE[HY000]: General error: 14 unable to open database file

I checked the solution written here: How to prevent SQLITE SQLSTATE[HY000] [14]? but the echoed message only appears at the first time of the loop, so I assume the PDO connection isn't closed.

I didn't find other documentation relevant to this error code.

What could possibly go wrong here?

I tried to move the prepare and bind commands in a condition as follows. The exception isn't raised, but only the first tile is saved (or every tile is saved on top of the first one, not sure):

if ($pdo_mbtiles == null) {
    echo "Opening new database connection".PHP_EOL;
    $pdo_mbtiles = new PDO('sqlite:'.$filename,
            '',
            '',
            array(
                PDO::ATTR_PERSISTENT => true
                )
            );
    $pdo_mbtiles->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo_mbtiles->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}

if ($q == null) {
    $q = $pdo_mbtiles->prepare("INSERT INTO tiles (zoom_level, tile_column, tile_row,tile_data) VALUES (:zoom_level, :tile_column, :tile_rowTMS, :tile_data)");
    $q->bindParam(':zoom_level', $zoom_level);
    $q->bindParam(':tile_column', $tile_column);
    $q->bindParam(':tile_rowTMS', $tile_rowTMS);
    $q->bindParam(':tile_data', $tile_data, PDO::PARAM_LOB);
}
$q->execute();

Here are the files during the generation:

And here after the exception is raised:

Also, when the exception is raised, I do a var_dump of my $pdo_mbtiles, and here's the result (exactly the same as when I do it with a success):

object(PDO)#116 (0) {
}

Edit: Still trying to solve this problem, I simplified the code to create the MBTiles file. No success yet, but here's a sample if anyone want's to reproduce the issue. You can download it from https://www.dropbox.com/s/33vqamc9tn4c3ux/sample_PHP_MBTiles_generation_bug.zip?dl=0

Jehad

Try moving the 2 setAttributes() calls inside the if as they only need running once when you connect and not every time round the loop. Or add them to the attribute array you already use in the connection – Anadem

Just tried, but the problem is still here. – Jehad

Try using closeCursor(). – Chesney

closeCursor doesn't help either. – Jehad

Question edited to include file permissions details. – Jehad

In the end, the error wasn't related to the database. It just couldn't open it because I opened fopen() to populate the database each time, without closing it. The error should have been something thrown by PHP like 'too many connections opened' instead of thrown by PDO saying that the database couldn't be accessed. – Jehad

Answer 1

The error message was misleading. After many hours of debugging, I found that it was completely unrelated to my database connection. It's juste that I used fopen() to get tiles data, and didn't fclose() after registration, thus reaching the limit of 1024.

1024 is because I used six require or require_once statements, so 1018 tile requests + 6 require = 1024 opened connections.

Jehad

Answer 2

I suggest you to reuse the connection if it is open.

Create a property: private $pdo;

And check if it's null before creating a new object:

function opendatabase(){
    try{
        if($this->pdo==null){
          $this->pdo =new PDO("sqlite:database/database.db","","",array(
                PDO::ATTR_PERSISTENT => true
            ));
        }
        return $this->pdo;
    }catch(PDOException $e){
        logerror($e->getMessage(), "opendatabase");
        print "Error in openhrsedb ".$e->getMessage();
    }
}

Exhibition

If that was the problem, I would get the error message at the beginning of the loop. It only appears when I've already run through it 1018 times. The problem doesn't appear for smaller files. – Jehad

Is MBTiles map using any APIs with limit view? – Exhibition

I tried several sources, all stop at 1018, but I usually get other error messages about the connection rather than the database when that's the case. – Jehad

Also seen help available on this dev.mysql.com/doc/refman/5.0/en/error-messages-server.html – Exhibition

Also go through community.sitepoint.com/t/… this will provide better scope – Exhibition

You've updated your answer, but again, if the problem was about permissions, it would not wait 1018 connections to say it doesn't work. I'll read your links. – Jehad

Your first link doesn't have any reference to error HY000 14, so I read the second link, but it suggests to pay attention to quotes. I get other error messages when adding quotes as I can't remove any. – Jehad

I edited my question to include file permissions details. – Jehad

Updated answer and suggested to reuse the connection – Exhibition

If you read my question, you would have noticed that I linked to this suggestion, applied it to my code, and still doesn't solve it... – Jehad