Hi All,

If I insert a row into one db, is it possible to do fetch the last inserted ID from another slave db?

Similarly, if I fetch multiple rows using sql_calc_found_rows from one db, is it possible to SELECT FOUND_ROWS() from another slave db?

TIA

Not entirely sure if this answers your question but have you ever tried something like this;

What you are suggesting is to use a different database within the same instance (connection). I was asking about using a different instance (multiple slaves in a replication).

Thanks, anyway.

How are you calling fetch last inserted id? My understanding is that this function is limited to returning the last row id inserted by a program. Of course your program doesn't run on the master which means the internal setting of the slave doesn't have the same last id inserted information.

In answer to your FOUND_ROWS() question.... no. If you perform the query on one database and then do SELECT FOUND_ROWS() on another you're not going to get the right result (if one at all). Try it and see.

n.b. there is a possibility I am wrong on all counts. These answers are based on research I have done in the past and NOT testing I have performed.

The other question is why do you want to do this?

You might run a number of different instances to create a database "farm" so multiplying the throughput of your whole system by allowing other users to run their queries on different instances. I don't understand why you'd want to connect to more than one instance at a time though.

If it's just to check that the replication is still happening then there must be better ways of checking things are still working.

Mike

It's occured to me that in the instance of running an insert query, it's always going to be on the master server (unless you're inserting into slaves, not advisable). Which means the lifespan of the program (if web based) is going to be limited to the connection you've created to the master. So, only the master is going to give you the last_insert_id . You could change sql connection mid program run, but why would you want to?