`ON DUPLICATE KEY UPDATE` doesn't work

I was struggling with this problem for a long time but I noticed that this SQL statement doesn't work with MySQL 5.7 - the row won't be updated as the data on the right is the data from the conflicting row, not the new version.

There is also another problem when using Hubot with Slack, there be a lot informations in brain related to users, some of them in UTF8mb4 and MySQL will fail to store those data (which is hard to notice as there is no logging).

To overcome these problems I have prepared my own version of the hubot-mysql-brain:

tag = 'hubot-mysql-brain'
rowId = 0
mysql = require 'mysql'

module.exports = (robot) ->
  url = process.env.MYSQL_URL
  table = process.env.MYSQL_TABLE or 'brain'

  robot.logger.info("#{tag}: Using #{url} to connect to brain")

  conn = mysql.createConnection(url)

  load_data = () ->
    conn.query "SELECT `data` FROM `#{table}` WHERE `id`= #{rowId}", (err, rows) ->
      if err or rows.length == 0
        if err
          robot.logger.error err
        if rows.length == 0
          robot.logger.info "#{tag}: No data in brain yet"
        robot.brain.mergeData {}
      else
        robot.logger.info "Mering data into brain: #{rows[rowId].data}"
        data = JSON.parse rows[0].data
        robot.brain.mergeData data

  conn.connect (err) ->
    if err?
      robot.logger.error "#{tag}: Error\n#{err}"
    else
      robot.logger.info "#{tag}: Connected to MySQL brain (table: #{table})"
      load_data()

  robot.brain.on 'save', (data = {}) ->
    brain =
      _private: data['_private']

    vals = { 'id': 0, 'data': JSON.stringify brain }
    conn.query "INSERT INTO `#{table}` SET ? ON DUPLICATE KEY UPDATE `data` = '#{JSON.stringify brain}'", vals, (err, _) ->
      if err
        robot.logger.error err
      return

First I store only _private in the brain, then I use slightly modified INSERT to fix the problem with updating the data, plus I have added a bunch of logging. WDYT?

chickenzord / hubot-mysql-brain

`ON DUPLICATE KEY UPDATE` doesn't work #5