Attribute config error

[benj-d]

Just looking for some advice, i have a warehouse and Drupal 9 website running locally. Everything appeared ok until i tried to submit a record. I receive the below error.

I think it is a result of some kind of mismatch between the warehouse and iform. The data entry page includes "related to attribute [sample:general]" which is missing from the log entry (below). I have not tried to do anything fancy and am just using the initial set-up terms and a single record entry prebuilt form.

I can't find any reference to sample:general on the warehouse, docs or iform code.

--- error: #44: Exception during submit.. There was an SQL error: ERROR: trailing junk after numeric literal at or near "21W" LINE 1: ...ap_sq_1km_id=19, map_sq_2km_id=20, map_sq_10km_id=21WHERE sa... ^ - UPDATE cache_occurrences_functional SET map_sq_1km_id=19, map_sq_2km_id=20, map_sq_10km_id=21WHERE sample_id=7 AND (map_sq_1km_id IS NULL OR map_sq_1km_id<>19 OR map_sq_2km_id IS NULL OR map_sq_2km_id<>20 OR map_sq_10km_id IS NULL OR map_sq_10km_id<>21) at line 368 in file C:\xampp\htdocs\indicia\system\libraries\drivers\Database\Pgsql.php --- error: Missing i18n entry general_errors.Validation error for language en_GB

[JimBacon]

The error message is actually really useful on this occasion. The problem is a missing space before the word WHERE in the query. Looks like a bug to me. What we need to do is work out where this is arising. I'll just do a quick search of the code to see if I can spot it.

[johnvanbreda]

@JimBacon the code is in application/helpers/PostgreSQL.php, insertMapSquares() function. I can see the likely problem, but what I don't understand is why this code is not triggering the error all the time. It's old code, not a recently introduced bug.

[benj-d]

cheers @JimBacon i did wonder at that but assumed it couldn't be the issue as @johnvanbreda says it hasn't caused a problem for anyone else. I do wonder if it's because i am running the masters and perhaps most everyone else is running a fixed development branch, though i assume (again!) the masters were all tested and passed? I'll amend the SQL and see if it runs.

[johnvanbreda]

@benj-d Ok, I think it's a genuine missing space in the code that creates the query, but PostgreSQL versions previously tolerated this whereas 15 doesn't. I'll submit a fix to the develop branch.

The master branch is the branch we run on the live BRC warehouse so it would indeed be tested, but not on all PG versions.

[benj-d]

Hi Both, i added a space to precede both "Where" statements in https://github.com/Indicia-Team/warehouse/blob/3e077fd469ad98eaeae85c0c919505174c04afd4/application/helpers/postgreSQL.php#L316 cleared the indicia cache and tried to submit an observation. The observation has submitted fine and appears in the warehouse.

I can't explain why this has occurred for me but not others but it appears to have fixed it for me... (for now!)

Before; $db->query("UPDATE cache_occurrences_functional SET " . implode(', ', $updateFieldSQL) . "WHERE sample_id={$s->id} " . 'AND (' . implode(' OR ', $updateFilterSQL) . ')'); $db->query("UPDATE cache_samples_functional SET " . implode(', ', $updateFieldSQL) . **"WHERE** id={$s->id} " . 'AND (' . implode(' OR ', $updateFilterSQL) . ')');

After; $db->query("UPDATE cache_occurrences_functional SET " . implode(', ', $updateFieldSQL) . " WHERE sample_id={$s->id} " . 'AND (' . implode(' OR ', $updateFilterSQL) . ')'); $db->query("UPDATE cache_samples_functional SET " . implode(', ', $updateFieldSQL) . **" WHERE** id={$s->id} " . 'AND (' . implode(' OR ', $updateFilterSQL) . ')');

I'll leave this open for now in case you want to investigate and fix it properly! Thanks for pointing me in the right direction.

[johnvanbreda]

Thanks for your help @benj-d