Page MenuHomePhabricator

MySQL warnings after storage adjustment
Closed, DuplicatePublic

Description

I'm getitng the follow warnings showing up very frequently in our MySQL logs after running ./bin/storage adjust:

141102 19:21:15 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `repository_statusmessage`
          (repositoryID, statusType, statusCode, parameters, epoch)
          VALUES (359, 'init', 'working', '{\"message\":\"Updating the working copy for repository \'CI\'.\"}', 1414974075)
          ON DUPLICATE KEY UPDATE
            statusCode = VALUES(statusCode),
            parameters = VALUES(parameters),
            epoch = VALUES(epoch)
141102 19:21:15 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `repository_statusmessage`
          (repositoryID, statusType, statusCode, parameters, epoch)
          VALUES (364, 'init', 'working', '{\"message\":\"Updating the working copy for repository \'VMM\'.\"}', 1414974075)
          ON DUPLICATE KEY UPDATE
            statusCode = VALUES(statusCode),
            parameters = VALUES(parameters),
            epoch = VALUES(epoch)

Event Timeline

joshuaspence raised the priority of this task from to Needs Triage.
joshuaspence updated the task description. (Show Details)
joshuaspence added a project: Phabricator.
joshuaspence added a subscriber: joshuaspence.

(Admittedly these may have been happening before storage adjustment as well)

I think this warning is incorrect (i.e., the statement is not actually unsafe, because the autoincrement primary key can never collide).

MariaDB 10.0 removed this warning: https://mariadb.com/kb/en/mariadb/insert-on-duplicate-key-update/

This answer suggests that switching the replication mode to MIXED might resolve it: http://dba.stackexchange.com/questions/31344/mysql-statement-based-replication-unsafe-statements

We could remove the id column on this table safely (and fairly easily, I think) to silence the warning.