I am using master/slave databases for my app and have 2 slave , one for sphinx and one for users and as per rule 1 master
Slave databases are set read only. Issue is that when I rebuild sphinx on slave it gives an error
The MySQL server is running with the --read-only option so it cannot execute this statement
How can I solve this issue?
As determined in the comments, this is happening because delta indices are in play, and they require write access as part of resetting the delta flag columns when running a full index.
If you wish to persist with read-only access, you could investigate real-time indices as an alternative to SQL-backed indices with deltas, as real-time indices allow updates as the underlying ActiveRecord data changes. If you're running Thinking Sphinx v4, then the rake tasks won't change at all, you just need to use the different index types.
If you're using TS v3, then the tasks were named differently (regenerate instead of rebuild, generate instead of index). The documentation does distinguish between the two major releases.
But yes, if you wish to continue with SQL-backed indices and deltas, then write access to the database is necessary.