Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.Problem/Motivation
I'm using xmlsitemap 1.0.0 on Drupal 9.1.4 with Postgres 12.6.
In the database I see:
- taxonomy_term_field_data.tid | bigint
- node_field_data.nid | bigint
- xmlsitemap.id | varchar
Which causes xmlsitemap_xmlsitemap_index_links() (called via cron) to fail with:
[error] Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does NOT exist: bigint = character varying
LINE 5: ...'page')) AND ("node_field_data"."nid" NOT IN (SE...
^
HINT: No operator matches the given name AND argument types. You might need TO add explicit type casts.
and
[error] Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: bigint = character varying
LINE 5: ...'category')) AND ("taxonomy_term_field_data"."tid" NOT IN (SE...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
For the node example, the query being executed is:
SELECT base_table.vid AS vid, base_table.nid AS nid
FROM node base_table
INNER JOIN node_field_data node_field_data
ON node_field_data.nid = base_table.nid
WHERE (node_field_data.type IN ('news', 'page'))
AND (node_field_data.nid NOT IN
(SELECT x.id AS id
FROM xmlsitemap x
WHERE type = 'node'))
GROUP BY base_table.vid, base_table.nid
LIMIT 100 OFFSET 0;
Steps to reproduce
drush xmlsitemap:index
Proposed resolution
Revert potential support for entities with string IDs.
Remaining tasks
User interface changes
API changes
Data model changes
| Comment | File | Size | Author |
|---|---|---|---|
| #7 | xmlsitemap-bigint-issue.patch | 1007 bytes | Afxsoft |
Issue fork xmlsitemap-3201480
Show commands
Start within a Git clone of the project using the version control instructions.
Or, if you do not have SSH keys set up on git.drupalcode.org:
changes
Comments
Comment #2
RoSk0I traced the issue to the #2249723: Upgrade XML Sitemap to Drupal 8 and https://drupal.org/sandbox/andreimariusdincu/2222965 where the actual port to D8 was happening, but non of the issues in the sandbox are actually explicitly talking about support for entities with string IDs.
As far as I know most if not all content entities would use
intas key and only content entities are supported as perxmlsitemap_is_entity_type_supported().Updating proposed resolution to revert that change and make id key of xmlsitemap table int again.
Would be good to hear back from maintainers about proposed resolution.
Comment #3
RoSk0Comment #4
Dave ReidIt is still entirely possible that content entities may have string IDs. Core even has test coverage for this using a testing entity type named entity_test_string_id.
For reference:
#2555027: Support non-numeric entity ID's
#3057609: Add content moderation support for content entities with string IDs
#3109722: Entity IDs can be strings in Drupal 8
Comment #5
RoSk0Hm, link to the core issue #3057609: Add content moderation support for content entities with string IDs just confirms that the support is only theoretical rather than actual.
What do you see as a solution to this issue?
Comment #7
Afxsoft CreditAttribution: Afxsoft commentedHere's a patch implementing solution
Comment #8
jweowu CreditAttribution: jweowu at Catalyst IT commentedAt a glance, that's still assuming that ids should be integers, which Dave is saying isn't a valid assumption -- although it should hold for many (most?) cases.
If you're going to do this, though, I'd just directly modify the subquery to cast the value to an int in the first place.
Comment #9
Dave ReidFor large amounts of data this will result in a possible very large array, which is why we use the subquery, which this change defeats the purpose of.
Comment #10
Dave ReidOur new xmlsitemap_post_update_reindex_future_revision_content() function also fails in the same exact way, since we want to join an entity table against the xmlsitemap table in a subquery. Merging that issue into this one since it will need the same fix. Cross-linking some other modules or core issues about the same issue, I can't find a solution that anyone has done yet.
Comment #12
mparker17I've tested both this issue's patch ( https://git.drupalcode.org/project/xmlsitemap/-/merge_requests/9.diff ) and the
add-entity-query-methodbranch (which fixes the same issue at a lower level, i.e.:XmlSitemapLinkStorage).Honestly, I like the approach in the
add-entity-query-methodbranch better - and it fixes the error I was getting (see below) - but since there's no merge request and no corresponding issue, I don't have a way to provide any feedback on the branch except here (sorry!)I'm going to tentatively mark this ticket as RTBC because the patch in this issue fixes the error I was getting while rebuilding my xmlsitemap...
Comment #15
Dave ReidI moved my branch code to a MR associated with this issue for better review.
Comment #17
Dave ReidGiven that it's passing tests, I'm going to commit this and include it in the next release here.