Bug in SQLite cache: tiles written to wrong .db when metatile spans shard boundary

[amarinsek]

When using the SQLite cache with sharding (<xcount> / <ycount>) and metatiling, MapCache sometimes logs:

tileset <tileset>: failed to re-get tile <x> <y> <z> from cache after set

and returns cache misses for specific tiles (very often along shard boundaries). The tiles are not present in the expected .db file.

---

Why does this happen

In lib/cache_sqlite.c, the multi-tile write path:

static void _mapcache_cache_sqlite_multi_set(mapcache_context *ctx, mapcache_cache *pcache, mapcache_tile *tiles, int ntiles)

opens a single SQLite connection based on the first tile in the batch:

mapcache_pooled_connection *pc = mapcache_sqlite_get_conn(ctx,cache,&tiles[0],0);
...
conn = SQLITE_CONN(pc);
...
for (i = 0; i < ntiles; i++) {
  mapcache_tile *tile = &tiles[i];
  _single_sqlitetile_set(ctx,cache, tile,conn);
}

If metatiling is enabled (or any batch write includes tiles from different shards), some tiles in that batch belong to different shard databases (because <dbfile> uses {x}, {y} governed by <xcount>/<ycount>). Those tiles still get written using the first tile’s connection/DB, so they end up in the wrong db file. Immediately after “set”, “get” tries to pull tile from the correct db file (computed from that individual tile), doesn’t find the record, and the code emits:

failed to re-get tile X Y Z from cache after set

In short: multi-set assumes one-DB-per-batch, which breaks when a batch spans shard boundaries.

---

How to reproduce

• MapCache 1.14.1
• SQLite cache with sharding, e.g.:

<cache name="test_cache" type="sqlite3">
  <dbfile>/mnt/data/mapcache/cache/test_cache/{z}/{tileset}-{z}-{x}-{y}.db</dbfile>
  <xcount>256</xcount>
  <ycount>256</ycount>
  <detect_blank/>
</cache>

• Tileset using that cache; metatiling > 1 (e.g. 8×8). Request a tile on/near a 256×256 shard edge:

/mapcache/tms/1.0.0/test_tileset@GoogleMapsCompatible2x/19/284682/338176.jpg

Expected: tile is cached and returned.
Actual: MapCache logs “failed to re-get tile … after set” and returns a miss; the corresponding tile is absent from the expected .db file. If metatile is set to 1 1, the problem disappears (no cross-shard batches).

---

Proposed fix

Group tiles by their destination DB file and write each group in its own transaction & connection.

That ensures every tile is written to the correct shard DB. The change is isolated to _mapcache_cache_sqlite_multi_set:

1. Build a hash: dbfile -> array of tiles.

2. For each group:

   • Open connection via mapcache_sqlite_get_conn(...) using the first tile in the group.
   • BEGIN TRANSACTION
   • _single_sqlitetile_set(...) for each tile in that group.
   • END TRANSACTION (or ROLLBACK on error)
   • Release connection.

This mirrors the single-tile _mapcache_cache_sqlite_set behavior but at the group level, and preserves transactional semantics.

---

Patch (drop-in replacement for _mapcache_cache_sqlite_multi_set)

static void _mapcache_cache_sqlite_multi_set(mapcache_context *ctx, mapcache_cache *pcache, mapcache_tile *tiles, int ntiles)
{
  mapcache_cache_sqlite *cache = (mapcache_cache_sqlite*)pcache;

  /* Build groups: dbfile -> array(mapcache_tile*) */
  apr_hash_t *groups = apr_hash_make(ctx->pool);
  int i;

  for (i = 0; i < ntiles; i++) {
    mapcache_tile *tile = &tiles[i];
    char *tile_dbfile = NULL;
    apr_array_header_t *arr;

    _mapcache_cache_sqlite_filename_for_tile(ctx, cache, tile, &tile_dbfile);
    if (GC_HAS_ERROR(ctx)) return;

    arr = apr_hash_get(groups, tile_dbfile, APR_HASH_KEY_STRING);
    if (!arr) {
      arr = apr_array_make(ctx->pool, 8, sizeof(mapcache_tile*));
      apr_hash_set(groups, tile_dbfile, APR_HASH_KEY_STRING, arr);
    }
    APR_ARRAY_PUSH(arr, mapcache_tile*) = tile;
  }

  /* Write each group in its own transaction/connection */
  for (apr_hash_index_t *hi = apr_hash_first(ctx->pool, groups); hi; hi = apr_hash_next(hi)) {
    const void *key; apr_ssize_t klen; void *val;
    apr_array_header_t *arr;
    mapcache_pooled_connection *pc = NULL;
    struct sqlite_conn *conn = NULL;
    int j;

    apr_hash_this(hi, &key, &klen, &val);
    arr = (apr_array_header_t*)val;
    if (!arr || arr->nelts == 0) continue;

    /* Open connection for this shard (first tile decides the dbfile) */
    {
      mapcache_tile *first = APR_ARRAY_IDX(arr, 0, mapcache_tile*);
      pc = mapcache_sqlite_get_conn(ctx, cache, first, 0);
      if (GC_HAS_ERROR(ctx)) {
        if (pc) mapcache_sqlite_release_conn(ctx, pc);
        return;
      }
      conn = SQLITE_CONN(pc);
      sqlite3_exec(conn->handle, "BEGIN TRANSACTION", 0, 0, 0);
    }

    /* Write tiles of the group */
    for (j = 0; j < arr->nelts; j++) {
      mapcache_tile *tile = APR_ARRAY_IDX(arr, j, mapcache_tile*);
      _single_sqlitetile_set(ctx, cache, tile, conn);
      if (GC_HAS_ERROR(ctx)) break;
    }

    /* Commit or rollback this group */
    if (GC_HAS_ERROR(ctx)) {
      sqlite3_exec(conn->handle, "ROLLBACK TRANSACTION", 0, 0, 0);
      mapcache_sqlite_release_conn(ctx, pc);
      return;
    } else {
      sqlite3_exec(conn->handle, "END TRANSACTION", 0, 0, 0);
      mapcache_sqlite_release_conn(ctx, pc);
    }
  }
}