24 ms·
Just look back at how long it too for us to get upsert, we're finally getting a better replication story in recent years. Coincidentally, using those two featu
by icebraining 9y ago
Just look back at how long it too for us to get upsert, we're finally getting a better replication story in recent years.
Coincidentally, using those two features together on MySQL can have fun results:
"Because the results of INSERT ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging INSERT ... SELECT ON DUPLICATE KEY UPDATE statements for the master and the slave to diverge. (...) An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)."
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate....
- true_religion 9y ago> An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. Insanity. Also pretty much the reason I stopped using MySQL. I am not a DBA. A very small part of my job is actually interacting with the database, and I have to trust that statement execution will be consistent in all cases. That's something PostgresQL has been able to give for the past 10 years. MySQL is still not there yet, despite being backed by a "database" company with over $30 billion in revenue per year. If Oracle, with all their domain experience and reputation incentive can't get MySQl into a sane and stable state, then its fundamentals must be built on sand.
- wvenable 9y ago> and I have to trust that statement execution will be consistent in all cases. I'm pretty sure no database engine guarantees the ordering of un-ordered SELECT statements.
- pgaddict 9y agoI don't want to be bashing MySQL, but this nicely illustrates the project's approach to engineering - if there's an issue, put it into the docs as a known limitation. Furthermore, MySQL manual actually has "Restrictions and Limitations" (which is an interesting read too), yet this "unsafe" nature of "INSERT ... ON DUPLICATE KEY UPDATE" is not mentioned there. I'd say that if PostgreSQL learned something from MySQL, it's "Never accept feature that is incompatible with existing features, even if it's promised to be fixed soon."
- pizza234 9y agoYou're wrong, and interestingly, you're omitting the key part: > Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode The problem you're writing about is not related to MySQL itself, it's a problem implicit in statement-based replication. Row/mixed mode replication has been available in MySQL since 5.1 (almost 10 years ago). DBAs must know how replication works, as RDBMSs are not toys. It's naive to think that replication in other RDBMs itjustworks! - see, for example, the replication bug that caused slave corruption to the famous transportation company. The choice to raise a warning rather than stopping replication is arguable, but it's far from being "insanity".
- icebraining 9y agoYou do realize that I didn't write "insanity", right? All I said is "can have fun results". In any case, there are two issues described in my post.
- pizza234 9y agoApologies if I confusingly replied to two posts in one, but there is common thread between the two, that is, a judgement on a product/feature, based on a radically uninformed knowledge of the subject. I've spent a few minutes after a Google search; results follow. Bug #11765650: this was fixed 5 and half years ago; see https://lists.mysql.com/commits/143383; https://lists.mysql.com/commits/143383; Bug #58637: see https://bugs.mysql.com/bug.php?id=58637 https://bugs.mysql.com/bug.php?id=58637 - this is not a bug; statement-based replication is obsolete, and it's superseded by mixed-mode - if users insist on using the former, they're asking for trouble.
- icebraining 9y agoIf users insist? Statement-based replication remained the default until 2015, full five years after the bug was reported. Even today, the "16.2.1 Replication Formats" page says nothing about it being obsolete, or provide indication of level of danger in choosing it. It's like walking on a minefield where the markers are camouflaged.