temporary tables within stored procedures on slave servers with readonly set
- by lau
Hi,
We have set up a replication scheme master/slave and we've had problems lately because some users wrote directly on the slave instead of the master, making the whole setup inconsistent.
To prevent these problems from happening again, we've decided to remove the insert, delete, update, etc... rights from the users accessing the slave. Problems is that some stored procedure (for reading) require temporary tables.
I read that changing the global variable read_only to true would do what I want and allow the stored procedures to work correctly ( http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_only ) but I keep getting the error :
The MySQL server is running with the --read-only option so it cannot execute this statement (1290)
The stored procedure that I used (for testing purpose) is this one :
DELIMITER $$
DROP PROCEDURE IF EXISTS test_readonly $$
CREATE DEFINER=dbuser@% PROCEDURE test_readonly()
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS temp
(
BT_INDEX int(11),
BT_DESC VARCHAR(10)
);
INSERT INTO temp (BT_INDEX, BT_DESC) VALUES (222,'walou'), (111,'bidouille');
DROP TABLE temp;
END $$
DELIMITER ;
The create temporary table and the drop table work fine with the readonly flag - if I comment the INSERT line, it runs fine- but whenever I want to insert or delete from that temporary table, I get the error message.
I use Mysql 5.1.29-rc. My default storage engine is InnoDB.
Thanks in advance, this problem is really driving me crazy.
