InvalidTextRepresentation: ERROR when trying to upgrade

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

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,

Have you been using some plugin?

I only have these plugins:

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

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

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.

4 Likes

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...
5 Likes

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.

5 Likes

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.)

3 Likes

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.

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.

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

1 Like