Replication NOW()

Hi guys,

I’ve never looked at replication before, though I thought I understood the basic principles. I’ve recently started with a new employer and I’ve been informed today that I can’t use functions like NOW() because we have replicated servers and the timestamps may not match between servers.

I honestly thought that replication would be a bit more advanced than this and as a result I have to recode some of my recent work that is yet to be deployed.

My question is whether it’s really this clear-cut, and whether I should start coding to suit, in case anything that isn’t currently replicated requires it at a later time.

One example would be that I have just written a stored procedure to tidy up parts of our database. It has to process records that are over 30 days, so I used DATE_SUB(CURDATE(), INTERVAL 30 DAY) to identify the date 30 days ago. I have had to change the SP to just accept a date and must calculate this date (and sometimes DATETIME) now in my PHP (or whatever I’m using to call the SP) and pass it as a parameter. Must I do this every time? Does replication really have such a blatant downside, or is using NOW(), CURDATE() etc lazy coding?

Cheers all

When you use replication, you must ALWAYS use deterministic queries.

Allot of beginners will use things like NOW(), DELETE FROM foo LIMIT 10, etc, and by using such queries, your databases will become out of sync (meaning that you won’t have the same data on every server).

So, it’s not that replication has such a blatant downside, it’s that when you go into more advanced you need to know more about what your doing than when you script away at a hobby site.

What I meant was that MySQL provides us with a bunch of handy functions which are apparently of no use at all if you use replication. That seems like a blatant downside to me.

That’s not always true. MySQL offers two types of replication (as of v5.1.5); “Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows.”
(See Chapter 16. Replication)

With the former (SBR) – which is the default format – the slave replicates the SQL queries that change the data. This has the potential to put the slave out of sync if the queries are nondeterministic (See 16.1.2.1.). There is really nothing to do about this, except to use absolute values or functions that always return the same value for the same input, regardless of where they are executed (deterministic).

The second type (RBR), however, replicates the actual data that is changed, not the query that changed it. This allows you to use nondeterministic functions/procedures without having to worry about it putting your databases out of sync. The downside is that the log files for this type of replication may become much larger, and thus the replication itself will take longer.

You can set which you use by changing the binlog_format config directive.

Atli: :tup: for that post.