Bamboo and other API v1 notifications and integrations do not work in Data Center

Summary

API v1 notifications are not delivered to users or rooms in Hipchat.
Any integrations using the API v1 is not working, including the Atlassian Bamboo integration.

Environment

Hipchat Data Center 3.1.2 and older

Steps to Reproduce

1. Generate a v1 notification token via https://<fqdn>/admin/api
2. Send a test notification to the Default room by running:

   curl -XPOST 'https://<fqdn>/api_rooms/message?auth_token=<api_v1_token>&room_id=Default&from=TestUser&message=TestMessage'
   

Expected Results

Message should appear in the room

Actual Results

• If Gearman is down, the following error will be logged to web.log:

  2018-01-08T11:06:15.047600+00:00 hipchat web[9299]: api_rooms/message#REQ41496A uid-anon 33ms [error] Gearman - all servers failed
  

• Once Gearman is brought up, the notification would still fail with the following error in web.log as well:

  2018-01-09T10:53:50.624957+00:00 vwncova-hipchat web[29437]: api_rooms/message#REQ3CBD77 uid-anon 18ms [alert] PHP Error: pg_query(): Query failed: ERROR:  operator does not exist: ` integer\nLINE 2:             `group_id` = '1' AND\n                    ^\nHINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. on /hipchat-scm/web/system/libraries/drivers/Database/Pgsql.php:80\n\n
  0: in Kohana::exception_handler()\n
  1: /hipchat-scm/web/system/libraries/drivers/Database/Pgsql.php:80 -> pg_query()\n
  2: /hipchat-scm/web/system/libraries/Database.php:254 -> query()\n
  3: /hipchat-scm/web/application/models/room.php:988 -> query()\n
  4: /hipchat-scm/web/application/controllers/api.php:83 -> find_by_group_and_pretty_name()\n
  5: /hipchat-scm/web/application/controllers/api_rooms.php:440 -> get_room()\n
  6: in Api_Rooms_Controller::message()\n
  7: /hipchat-scm/web/system/core/Kohana.php:283 -> invokeArgs()\n
  8: in Kohana::instance()\n
  9: /hipchat-scm/web/system/core/Event.php:209 -> call_user_func()\n
  10: /hipchat-scm/web/system/core/Bootstrap.php:55 -> run()\n
  11: /hipchat-scm/web/www/index.php:109 -> require()\n\nurl: ...
  

Notes

The PHP error is caused by the move to Postgres in DC. One of the queries run by the API service is wrong and failing.

Workaround

• Bring up Gearman as indicated in Users are not receiving emails or push notifications on Data Center
• Fix the code manually in /hipchat-scm/web/application/models/room.php as follows:
  1. Go to the function find_by_group_and_pretty_name around line 990:

       public static function find_by_group_and_pretty_name($group_id, $name) {
         $db = Database::instance();
         $sql_id = $db->query('select id from rooms WHERE
                 `group_id` = ? AND
                 LOWER(`pretty_name`) = LOWER(?) AND
                 `is_deleted` = ?', $group_id, $name, 0)->result_array();
     

  2. Remove all the left quotation marks ` from the code of the function
  3. Restart PHP as root:

     /etc/init.d/php5-fpm restart