using replica database

[esaputras]

this link -> https://planetscale.com/docs/concepts/replicas said I can use SELECT on the replica using @replica and all the subsequent query would be using the replica database. But how do I know when I am using the replica or the primary one. For the example:

1. use @replica;
2. select * from table1;
3. select * from table2;

For this case I want to use the replica only until 2nd line and I want to use the primary on the 3rd one.

Does the use@replica only affect for 1 sql script? or it also would affect all of my query afterwards?

[mattrobenolt]

This applies to a whole session/connection.

So with your example, it very much depends on how your application manages that, but if a single MySQL connection is used for all 3 queries, both of the select statements would use the replica. If a disconnect happened between, they would not. Variables and other state like that is bound to a MySQL connection.

[orware]

Hello @esaputras,

Matt's comments above are 100% accurate as after the initial use @replica; from your first line the subsequent two queries would both occur against the replicas if the same connection was used afterward.

Matt can correct me if I'm wrong here but in a quick test using our web console the following did appear to behave as expected so it may be another tip you can use to do what you described.

Should you want to switch back and forth within the same connection, you can probably do the following:

1. use @replica;
2. select * from table1;
3. use @primary; (optionally, you may also simply run use; but use @primary; is more explicit)
4. select * from table2;

And this would help to allow your query against table2 occur back against your primary instance without necessarily having to fully disconnect/reconnect to reset things back to the primary again.

You should be able to run this query to also get a sense of your current state:

-- Should return:
-- 1 when in replica mode
-- 0 when in primary mode
select @@read_only;

For docs on this, please see: https://planetscale.com/docs/concepts/replicas

[mattrobenolt]

Matt can correct me if I'm wrong here but in a quick test using our web console the following did appear to behave as expected so it may be another tip you can use to do what you described.

This is correct. :) I missed the second part of "how to stop using @replica".

[esaputras]

That works! thanks

If I had multiple replica like the picture below, how I choose the replica that I wanted to use? Or will it just use nearest region replica? will it choose another replica if one replica connection already full

[mscoutermarsh]

Hi @esaputras,

For replicas in your primary region. Running use @replica will distribute the queries to your two replicas.

For querying replicas in different regions, you'll need to create a connection string for those replicas and explicitly connect to them and run queries (go to your main branch page, on right side you can click on a region and create credentials for it).

At the moment we don't auto route queries to the nearest replica, but it is something we are looking into.

[mscoutermarsh]

Often what we see people do is place different environment variables in different regions of their app.

For example, if they have an app deployed in us-east and us-west. Each app will have a different READ_ONLY_REPLICA_DATABASE_URL. Which they adjust based on which DB they want it to connect to.

Also please note: for the primary region, you can force a connect to hit the replica via connection string by adding @replica to the name of the database in the URL. We have an example here: https://planetscale.com/docs/concepts/replicas#2-append-replica-to-the-database-name