| joshuaspence | |
| Sep 28 2016, 2:03 AM |
| F1851871: daemon.png | |
| Sep 28 2016, 2:03 AM |
Our repositories are getting stuck in a weird state and I think it is caused by D16575. All of our repositories are mirrored from an internal GitLab installation. The GitLab host goes offline for a period of 30 seconds every 4 hours in order to perform a backup. When this happens, it looks like the repository daemons back off and increase the update frequency. Our most active repository currently has an update frequency of 1d, 14h.
I clicked "Update Now" and the update frequency for our most active repository has dropped back down to 15 seconds.
I think this is:
https://thewebfellas.com/blog/conditional-duplicate-key-updates-with-mysql
Which I would never have guessed.
Yeah, ON DUPLICATE KEY UPDATE is sensitive to order. Setup:
mysql> CREATE TEMPORARY TABLE msg (key1 VARCHAR(32) NOT NULL, key2 VARCHAR(32) NOT NULL, val INT UNSIGNED NOT NULL, UNIQUE KEY `key_1` (key1)); Query OK, 0 rows affected (0.01 sec)
Here's the "value, type" assignment order:
mysql> INSERT INTO msg (key1, key2, val) VALUES ('dog', 'woof', 1) ON DUPLICATE KEY UPDATE val = IF(key2 = VALUES(key2), val + VALUES(val), VALUES(val)), key2 = VALUES(key2);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM msg;
+------+------+-----+
| key1 | key2 | val |
+------+------+-----+
| dog | woof | 1 |
+------+------+-----+
1 row in set (0.00 sec)
mysql> INSERT INTO msg (key1, key2, val) VALUES ('dog', 'woof', 1) ON DUPLICATE KEY UPDATE val = IF(key2 = VALUES(key2), val + VALUES(val), VALUES(val)), key2 = VALUES(key2);
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM msg;
+------+------+-----+
| key1 | key2 | val |
+------+------+-----+
| dog | woof | 2 |
+------+------+-----+
1 row in set (0.00 sec)
mysql> INSERT INTO msg (key1, key2, val) VALUES ('dog', 'bark', 1) ON DUPLICATE KEY UPDATE val = IF(key2 = VALUES(key2), val + VALUES(val), VALUES(val)), key2 = VALUES(key2);
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM msg;
+------+------+-----+
| key1 | key2 | val |
+------+------+-----+
| dog | bark | 1 |
+------+------+-----+
1 row in set (0.00 sec)This is what I intend to do: the third insert resets the count back to 1.
This is what we're actually doing: "type, value" assignment order:
mysql> INSERT INTO msg (key1, key2, val) VALUES ('dog', 'bark', 1) ON DUPLICATE KEY UPDATE key2 = VALUES(key2), val = IF(key2 = VALUES(key2), val + VALUES(val), VALUES(val));
Query OK, 2 rows affected (0.01 sec)
mysql> SELECT * FROM msg;
+------+------+-----+
| key1 | key2 | val |
+------+------+-----+
| dog | bark | 2 |
+------+------+-----+
1 row in set (0.00 sec)
mysql> INSERT INTO msg (key1, key2, val) VALUES ('dog', 'woof', 1) ON DUPLICATE KEY UPDATE key2 = VALUES(key2), val = IF(key2 = VALUES(key2), val + VALUES(val), VALUES(val));
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM msg;
+------+------+-----+
| key1 | key2 | val |
+------+------+-----+
| dog | woof | 3 |
+------+------+-----+
1 row in set (0.00 sec)That doesn't reset the value.
I'll reorder the clauses in the query.