Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PostGresql error: PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "70.json" #196

Open
Skipper-is opened this issue Aug 20, 2019 · 16 comments

Comments

@Skipper-is
Copy link
Contributor

commented Aug 20, 2019

PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "70.json" 4: WHERE (base.id IN ('70.json')) ^: SELECT base.id AS id, base.name AS name, base.type AS type, base.uid AS uid, base.timestamp AS timestamp, base.created AS created, base.changed AS changed, base.done AS done FROM {log} base WHERE (base.id IN (:db_condition_placeholder_0)) ; Array ( [:db_condition_placeholder_0] => 70.json ) in EntityAPIController->query() (line 187 of /var/www/html/farmOS/profiles/farm/modules/contrib/entity/includes/entity.controller.inc).
error

Getting this error when running GET /log/70.json to get log ID 70.
Get this error for all logs, but not for assets or users, or terms.
"70.json" is being passed to the SELECT by id query, instead of just the number

@mstenta

This comment has been minimized.

Copy link
Member

commented Aug 20, 2019

Thanks @Skipper-is

I'm able to replicate this in my local pgsql environment. Investigating...

@mstenta mstenta added the bug label Aug 20, 2019

@mstenta

This comment has been minimized.

Copy link
Member

commented Aug 20, 2019

Related issue that was fixed in Drupal core (a long time ago, but might provide clues for this issue): https://www.drupal.org/project/drupal/issues/1003788

@mstenta

This comment has been minimized.

Copy link
Member

commented Aug 20, 2019

And the restws module issue that led me there: https://www.drupal.org/project/restws/issues/1744506

Notably, in that issue, the restws module maintainer suggests:

Using the format extension like ".json" or ".xml" is not recommended anyway - use the accept headers when possible.

So instead of making a request to /log/X.json, make a request to /log/X with a Accept: application/json header.

Can you give that a try @Skipper-is ?

Maybe we should update the API docs to recommend that instead of tacking .json to the end.

@mstenta

This comment has been minimized.

Copy link
Member

commented Aug 20, 2019

I would prefer to find a solution than change the recommendations, personally.

@Skipper-is

This comment has been minimized.

Copy link
Contributor Author

commented Aug 20, 2019

Well, it doesn't crash.....
image
But that isn't what I needed

@Skipper-is

This comment has been minimized.

Copy link
Contributor Author

commented Aug 20, 2019

Forgot the header.
But this works:
/log.json?id=23

@Skipper-is

This comment has been minimized.

Copy link
Contributor Author

commented Aug 20, 2019

Confirming that /log/23 with Accept: application/json Header also works

@mstenta

This comment has been minimized.

Copy link
Member

commented Aug 20, 2019

I found the fix...

Basically, the same fix that was applied to Drupal core's entity controller needs to be applied to the Entity module's controller.

Specifically this: https://git.drupalcode.org/project/drupal/commit/ebc5d85#bee3bbb687ed6176305a6fc7923caf7fd908c823_186_186

So this is actually a bug in the Entity module. I'll prepare a patch.

@mstenta

This comment has been minimized.

Copy link
Member

commented Aug 20, 2019

I submitted a patch upstream to the Entity API module: https://www.drupal.org/project/entity/issues/3076175

Try patching your Entity API module in [webroot]/profiles/farm/modules/contrib/entity/includes/entity.controller.inc and adding the lines from this patch: https://www.drupal.org/files/issues/2019-08-20/entity_postgresql_cleanids-3076175-2.patch

That should allow you to access /log/23.json without the Accept header.

mstenta added a commit that referenced this issue Aug 20, 2019
Patch Entity API module to fix Issue #3076175: PostgreSQL: PDOExcepti…
…on:Invalid text representation when attempting to load an entity with a string or non-scalar ID (see GitHub farmOS issue #196)

@mstenta mstenta closed this Aug 20, 2019

@mstenta

This comment has been minimized.

Copy link
Member

commented Aug 20, 2019

I added the patch to farmOS's Drush make file, so it will be automatically included in farmOS's Entity API module moving forward.

@mstenta mstenta added the postgresql label Aug 22, 2019

@mstenta

This comment has been minimized.

Copy link
Member

commented Aug 22, 2019

Reopening this - the patch caused issues elsewhere in farmOS, so I rolled it back.

@mstenta mstenta reopened this Aug 22, 2019

@mstenta

This comment has been minimized.

Copy link
Member

commented Aug 22, 2019

OK, I think I have it worked out pretty well. There is a new patch posted to this comment: https://www.drupal.org/project/entity/issues/3076175#comment-13231819

I'd like to test it a bit more myself in farmOS before merging it into farmOS, as it does have the potential to break things.

I'd love to hear back from the maintainers of the Entity API module about that patch as well, in case they have any insights about other potential issues. But I don't think that module is actively maintained since it is part of Drupal 8 core now... So we may just have to patch it in farmOS as a crutch until we can upgrade to D8.

@Skipper-is

This comment has been minimized.

Copy link
Contributor Author

commented Aug 23, 2019

The same error is also triggered when deleting custom log types in admin/config/log/types:
PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "survey" LINE 1: ...tity WHERE entity_type = 'log_type' AND entity_id = 'survey' ^: DELETE FROM {farm_quick_entity} WHERE entity_type = :entity_type AND entity_id = :entity_id; Array ( [:entity_type] => log_type [:entity_id] => survey ) in farm_quick_entity_delete() (line 252 of /var/www/html/farmOS/profiles/farm/modules/farm/farm_quick/farm_quick.module).
Actually, that looks different, looks like it is passing the name of the log type to the DB, rather than the ID

@mstenta

This comment has been minimized.

Copy link
Member

commented Aug 23, 2019

That makes sense - the core issue lies in the way the Entity API module deals with entities that have a name-based ID in addition to a numerical ID, which is what custom entity types have.

My patch in that issue cover both cases that you came across. I do feel more confident with it now, if you want to test it out. Make sure you get the most recent patch from that thread.

@Skipper-is

This comment has been minimized.

Copy link
Contributor Author

commented Sep 9, 2019

Similar error now whenever I click on a field in the main map view:
PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "SEPARATOR" LINE 3: ...id ORDER BY ss_log.timestamp DESC, ss_log.id DESC SEPARATOR ... ^: SELECT ss_asset_current_log.field_farm_asset_target_id AS asset_id FROM (SELECT ss_fdffa.field_farm_asset_target_id AS field_farm_asset_target_id, SUBSTRING_INDEX(GROUP_CONCAT(ss_log.id ORDER BY ss_log.timestamp DESC, ss_log.id DESC SEPARATOR ','), ',', 1) AS ss_current_log_id FROM {log} ss_log INNER JOIN {field_data_field_farm_asset} ss_fdffa ON ss_fdffa.entity_type = 'log' AND ss_fdffa.entity_id = ss_log.id AND ss_fdffa.deleted = 0 INNER JOIN {field_data_field_farm_membership} ss_fdffm ON ss_fdffm.entity_type = 'log' AND ss_fdffm.entity_id = ss_log.id AND ss_fdffm.deleted = 0 WHERE (ss_log.timestamp <= 1568056967) AND (ss_log.done = 1) GROUP BY ss_fdffa.field_farm_asset_target_id ORDER BY ss_log.timestamp DESC, ss_log.id DESC) ss_asset_current_log INNER JOIN {log} ss_current_log ON ss_current_log.id = ss_asset_current_log.ss_current_log_id INNER JOIN {field_data_field_farm_membership} ss_current_log_fdffm ON ss_current_log_fdffm.entity_type = 'log' AND ss_current_log_fdffm.entity_id = ss_current_log.id AND ss_current_log_fdffm.deleted = 0 INNER JOIN {field_data_field_farm_group} ss_current_log_fdffg ON ss_current_log_fdffg.entity_type = 'field_collection_item' AND ss_current_log_fdffg.bundle = 'field_farm_membership' AND ss_current_log_fdffg.entity_id = ss_current_log_fdffm.field_farm_membership_value AND ss_current_log_fdffg.deleted = 0 INNER JOIN {farm_asset} ss_current_log_fa ON ss_asset_current_log.field_farm_asset_target_id = ss_current_log_fa.id WHERE (ss_current_log_fdffg.field_farm_group_target_id = 46) AND (ss_current_log_fa.archived = 0) ; Array ( ) in farm_group_members() (line 695 of /var/www/html/farmOS/profiles/farm/modules/farm/farm_group/farm_group.module).

@Skipper-is

This comment has been minimized.

Copy link
Contributor Author

commented Sep 9, 2019

I have tried to apply the patch,
`Checking patch entity.module...
Checking patch includes/entity.controller.inc...
error: while searching for:
}
}

// Load any remaining entities from the database. This is the case if $ids
// is set to FALSE (so we load all entities), if there are any ids left to
// load or if loading a revision.

error: patch failed: includes/entity.controller.inc:241
error: includes/entity.controller.inc: patch does not apply
`

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
2 participants
You can’t perform that action at this time.