Problem

Chat history exports fail soon after starting with the following error:

400 Incorrect JSON value: Expecting value: line 1 column 1 (char 0)

Diagnosis

Environment

• Hipchat Server 2.2.6 (this can also affect older releases)

• The export fails when trying to export the user list.  The following error may be observed in the /var/log/hipchat/hcapp.log:

  2018-01-10T20:36:19.959227-08:00 chat scissortail-worker:5847:INFO GID: 1 JID: 212a880b-efbf-4fbb-80a2-9f59942164f7 - Exporting full user list.
  2018-01-10T20:36:20.147463-08:00 chat scissortail-web:4858:INFO GID: 1 JID: 212a880b-efbf-4fbb-80a2-9f59942164f7 - get_export_job_status 212a880b-efbf-4fbb-80a2-9f59942164f7
  2018-01-10T20:36:20.179416-08:00 chat.devlnk.net gunicorn.scissortail-web.access: [4858] - - "GET /export/group/1/latest HTTP/1.1" 200 772 "-" "-"
  2018-01-10T20:36:20.515065-08:00 chat scissortail-worker:5847:ERROR GID: 1 JID: 212a880b-efbf-4fbb-80a2-9f59942164f7 - Error when getting http://localhost:8080/v2/user?expand=items&max-results=1000&include-deleted=true&include-offline=true&include-guests=true&start-index=0: 400 | {#012  "error": {#012    "code": 400,#012    "message": "Incorrect JSON value: Expecting value: line 1 column 1 (char 0)",#012    "type": "Bad Request"#012  }#012} 

  
  
  

Diagnostic Steps

• The /var/log/hipchat.coral.err.log contains many instances of the following errors:
  
  

  2018-01-10T20:23:27.236370-08:00 chat coral-2: [/sync/users#REQLas58j] ERROR: Not able to hydrate 851 from db
  
  2018-01-10T20:36:20.273207-08:00 chat coral-2: [/user#REQOr4Wrl] WARN: Sending API error: 400 - Incorrect JSON value: Expecting value: line 1 column 1 (char 0)

• The "851" in the above example refers to a user id within the Hipchat Database's users table. 

Cause

• The "metadata" value for the user id in question was invalid, which caused the user list export to fail. 
• This was due to a server administrator making changes directly in the Hipchat database to a user account. 

• The metadata value was set to parenthesis instead of curly brackets.  Here is an example of what the account looked like in the database.  Note the metadata value:

  select * from users where id=851\G
  
  
  *************************** 850. row ***************************
            id: 851
       version: WTJJ0WIA
   external_id: {hcs}joe.user
          name: User, Joe 
      nickname: NULL
  mention_name: JoeUser
         email: joe.user@example.com
      password: p
         title: 
     photo_url: NULL
     confirmed: 0
       created: 2016-07-21 23:57:19
    is_deleted: 0
    is_enabled: 1
      is_guest: 0
      timezone: UTC
      metadata: ()

Resolution

Fix the metadata value to be curly braces instead of parenthesis, then attempt a full sync of the user directory following the steps below:

1. Take a snapshot of the Hipchat Serever virtual machine before making any changes to the databse. 

2. Log in to the Hipchat command line and access the database:

   DBPASS=$(awk '/"pass"/ {gsub(/[",]/,"");print $2}' /hipchat/config/site.json) && mysql -uroot -p$DBPASS hipchat

3. Correct the metadata value for the user id in question.  In this example, we changed it for user id 851.  Please substitute the user id accordingly for your situation:

   update users set metadata='{}' where id=851;

4. Retry the export operation.