InvalidTextRepresentation: ERROR when trying to upgrade

Product Support

AI-generated summary

The discussion revolves around an error encountered when trying to upgrade Discourse, specifically an InvalidTextRepresentation: ERROR related to a string input for a category instead of an ID. depeters initially reported the issue, and after troubleshooting, eviltrout identified the problem and backported a patch to the beta version.

depeters was able to resolve the issue temporarily by manually deleting the failing migration section and running db:migrate. However, they eventually sought paid support from Jay, who was able to fix the issue.

A separate issue with Single Sign-On (SSO) emerged, where the return_path parameter was not being honored and was being replaced with a Discourse URL. depeters reported this issue, but was eventually able to resolve it and documented the solution in a separate topic. eviltrout suggested switching to stable releases of Discourse to avoid similar issues in the future.

1

I just tried to update and it is failing. Help please!

Caused by:

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "Item Comments"
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-1.0.2/lib/patches/db/pg.rb:110:in `async_exec'

ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "Item Comments"
: INSERT INTO reviewables (
  type,
  status,
  created_by_id,
  reviewable_by_moderator,
  topic_id,
  category_id,
  payload,
  created_at,
  updated_at
)
SELECT 'ReviewableQueuedPost',
  state - 1,
  user_id,
  true,
  topic_id,
  nullif(post_options->>'category', '')::int,
  json_build_object(
    'old_queued_post_id', id,
    'raw', raw
  )::jsonb || post_options::jsonb,
  created_at,
  updated_at
FROM queued_posts
2

I even tried to do a fresh install and restore a backup and THAT failed too!

Migrating the database...
[2019-04-06 01:43:31] EXCEPTION: An error has occurred, this and all later migrations canceled:

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "Item Comments"
: INSERT INTO reviewables (
  type,
  status,
3

Have you been using some plugin?

4

I only have these plugins:

5

Would this be related to any of your work @eviltrout?

t any rate I strongly suggest you rebuild without any third party plugins first.

6

It looks like your old queued posts had a string for a category (name perhaps?) instead of an id. This patch handles that case. I’ve backported it to beta, so updating should fix your instance.

I’m not sure why I didn’t see this is any of the test databases I ran it on.

https://github.com/discourse/discourse/commit/13c6bf54d0d08363ac36b0755a7a26386b6768e6

7

Thanks, @eviltrout. That sounds right to me.

What I ended up doing was to find the broken migration, delete the section that was failing from the running container (?!), run a db:migrate there, and then I was able to upgrade.

The immediate problem is solved (well it seems that there is now an unrelated problem with sso, but it’s not Discourse’s fault) , but in case you’d be interested, I just tried to restore that database on another server (I’ll delete it soon @depeters!) and it failed like this

466:in `start'                                                 /usr/local/lib/ruby/2.6.0/bundler/cli.rb:18:in `start'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-1.17.2/exe/bundle:30:in `block in <top (required)>'
/usr/local/lib/ruby/2.6.0/bundler/friendly_errors.rb:124:in `with_friendly_errors'
/usr/local/lib/ruby/gems/2.6.0/gems/bundler-1.17.2/exe/bundle:22:in `<top (required)>'
/usr/local/bin/bundle:23:in `load'
/usr/local/bin/bundle:23:in `<main>'
Trying to rollback...
Rolling back...
8

I paid Jay to get me back up and running. I saw in his tagline he offers paid support. I contacted him late on a Friday night and he had my discourse fixed in about an hour. I miss the days when the automated update just worked but I bet Jay prefers it this way. :laughing: Good for business :shushing_face:. Seriously though, thanks Jay, you saved my bacon.

9

One thing you might want to consider is switching to stable releases of Discourse. If you follow the most recent updates things do break from time to time (but you get the advantage of trying out new features before everyone else, and providing feedback.)

10

Unfortunately I am in fact having an SSO issue with discourse now. The return_sso_url is always the base discourse page. It should be my application where the process started. That value is in the returned route from discourse to may application along with the nonce. Any chance you have some insight into why this may have stopped working? Basically, the “return_path” parameter sent to discourse is not being honored and replaced with a discourse url.

11

Admittedly, I don’t pay close attention to changes in SSO, but I don’t know what might have changed in SSO that would affect that. The only thing that I could guess is that before you weren’t using https and now it’s redirecting from http to https and the path is getting dropped, but that seems like a long shot.

Sounds like a new topic.

12

Figured it out. I put my “figuring out” here: https://meta.discourse.org/t/issue-bleeding-edge-external-sso-prohibits-returning-to-host-app/24759/24