500 error when attempting to add term into taxonomic name term field

[therobyouknow]

update
seen on several sites:

• Ian Kitching's
• Barry's
• Fungi

Therefore this is a common issue, changed title to reflect that.

Error so far seen on more than on place where Taxonomic Name field present: attempting to add terms in taxonomic name field associated with an image, and with the field associated with Bibliographic content. Conclusion so far on that basis is that the error will occur for all instances of Taxonomic Name field where attempt to add term.

raised on this site:
https://agromyzidae.myspecies.info/ - "500 error when editing image taxonomic name term field".

problem seen by Barry (site owner) here:

"It has happened in the past but it’s not an issue I regularly encounter and it seems to sort itself out eventually."

confirming I can recreate error scenario:

https://agromyzidae.myspecies.info/taxonomy/term/70#overlay=file/50/edit%3Fdestination%3Dtaxonomy/term/70

but fine here:

https://myriatrix.myspecies.info/#overlay=file/8829/edit%3Fdestination%3Dscratchpads-front

This site is running 2.11.1 like Barry's latest release, so I would not necessarily expect the release to have caused regression. The particular part of the release where I might have thought there would be an issue would be the update of Drupal core, because taxonomy is part of core. But given that another site running 2.11.1 doesn't show the issue could discount that issue somewhat.

Going back to site where issue seen, from web browser console log if I use the URI endpoint involved in doing the autocomplete in the taxonomic name field: https://agromyzidae.myspecies.info/taxonomy/autocomplete/field_taxonomic_name/h

I can see the fatal error:

db log / database logging switched on

can see this error directly related to issue:

[therobyouknow]

Wondering if these are related: #6622

This looks like as a result of the release 2.11.1 and in particular I'd say it is the standard Drupal core update. My guess is that our custom Scratchpads code made some assumptions about the core code and these assumptions don't now apply with the core update. This on the basis of the error above which references a custom Scratchpads code file.

Core updates are important and there is a limitation to how we can give test coverage before releasing, to avoid such issues.

[therobyouknow]

In 2.11.1 release, when one attempts to add a term to the Taxonomy Name field, a 500 error is displayed as a pop up, because the autocomplete failed.

The cause is something wrong with this query to provide the list of suggestions as you type (called autocomplete). This is being done in scratchpads_taxonomic_name_field_autocomplete in sites/all/modules/custom/scratchpads/scratchpads_taxonomic_name_field/scratchpads_taxonomic_name_field.module

SELECT v.name AS vocab_name, t.tid AS tid, t.name AS name, t.vid AS vidFROM {taxonomy_term_data} tINNER JOIN {taxonomy_vocabulary} v ON t.vid = v.vidINNER JOIN {taxonomy_term_hierarchy} h ON h.tid = t.tidWHERE (t.name NOT IN (:db_condition_placeholder_0, :db_condition_placeholder_1, :db_condition_placeholder_2)) AND (t.vid IN (:db_condition_placeholder_3)) AND (t.name LIKE :db_condition_placeholder_4 ESCAPE '\') UNION SELECT v.name AS vocab_name, t.tid AS tid, t.name AS name, t.vid AS vidFROM {taxonomy_term_data} tINNER JOIN {taxonomy_vocabulary} v ON t.vid = v.vidINNER JOIN {taxonomy_term_hierarchy} h ON h.tid = t.tidWHERE (t.name NOT IN (:db_condition_placeholder_5, :db_condition_placeholder_6, :db_condition_placeholder_7)) AND (t.vid IN (:db_condition_placeholder_8)) AND (t.name LIKE :db_condition_placeholder_9 ESCAPE '\') LIMIT 10 OFFSET 0 UNION SELECT v.name AS vocab_name, t.tid AS tid, t.name AS name, t.vid AS vidFROM {taxonomy_term_data} tINNER JOIN {taxonomy_vocabulary} v ON t.v

The above query causes this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10 OFFSET 0' at line 10

Observations so far are that in several places in that query, variables and SQL operands aren't separated by spaces, e.g. tINNER JOIN. This may be a glitch in how the query is outputted as a string and may not be present in the actual query. But I couldn't rule that out. Though if that were the case, I would have expected the error to be flagged up for an earlier occurence near the beginning rather than referring to LIMIT 10 etc which is towards the end.

We could run this same analysis on 2.10.1 which doesn't have this issue and see if the query as string is similar with variables and operands without spaces, if that was the case then I'd regard this as a glitch with how the query is output as a string and not the real query being run (and perhaps raise a Drupal core issue about it?).

My current thinking that the cause of this issue is that scratchpads_taxonomic_name_field_autocomplete has been written on versions of Drupal core that have core taxonomy functionality that behaves in a particular way. And that upgrading to 7.94 departed a bit from this which broke this function.

The comments above the function say:

/**
 * Almost identical to the original function, but with added brackets,
 * vocabulary name and tweak to return exact matches first.
 */

So I'm guessing that this custom function is a copy of a core function (from a core release earlier than 7.82, in our last release) modified to work with paranthesis like brackets {, [ and ( etc. Therefore I'm now wondering if I can find that same core taxonomy function in 7.94 copy it and tweak it in the same way to solve this issue. The assumption here is that the core function would have been updated between 7.82 core in our last release and 7.94. Re-using this updated function will provide the updated usage of taxonomy upon which to (re)build our custom function. I'm not expecting this to be a trivial task though, as code could have moved around in the latest function, for example.

My analysis above was done by temporarily adding some extra lines of code on my local developer personal copy to sites/all/modules/custom/scratchpads/scratchpads_taxonomic_name_field/scratchpads_taxonomic_name_field.module

$tagsReturnQueryRange = $query_no_prefix_or_suffix->range(0, 10);
$queryAsString = $tagsReturnQueryRange->__toString();