Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2005
    Posts
    63

    Unanswered: Replication: Subsequent queries from different slaves

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Not entirely sure if this answers your question but have you ever tried something like this;
    Code:
    USE MyDatabase1
    GO
    
    SELECT * FROM MyTable
    
    USE MyDatabase2
    GO
    
    SELECT * FROM MyOtherTable
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2005
    Posts
    63
    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.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •