We cannot rely on ORDER BY id

It seems that, once you have master-master replication, you cannot assume that ORDER BY id means anything.

Sadly, various bits of Moodle code rely on it. Try searching for

• ORDER BY id - 14 matches in core
• get_record.*\s["']id['"] - 190 matches in core, but no all relevant.

The situation seems to be:

• MySQL - you would be insane to use master-master replication here. (Cough, moodle.org, Cough!) http://dev.mysql.com/doc/refman/5.7/en/replication-features-auto-increment.html
• Postgres - does not offer master-master replication: http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
• Oracle - by default it does not work. You can specify and ORDER option when creating a sequence, but there is a performance hit. http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm http://stackoverflow.com/questions/13513936/oracle-sequence-value-are-not-ordered
• MS SQL - impossible to guarantee order of IDENTITY columns with master-master replication: http://technet.microsoft.com/en-us/library/ms152543.aspx

So, the only viable long-term solution is to remove all ORDER BY id, but that will be a big job, so I expect this will have to become an Epic in due course, in order to add appropriate developer debug warnings, and then find and fix all problems.

Long chat log discussion here: https://moodle.org/local/chatlogs/index.php?conversationid=14503#c520410

The issue that triggered all this: MDL-43246