We installed the master / slave replication scheme, and we have been having problems lately because some users wrote directly to the slave rather than the master, making the whole installation inconsistent.
So that these problems do not recur, we decided to remove the rights to insert, delete, update, etc. for users accessing the slave. The problems are that some stored procedures (for reading) require temporary tables.
I read that changing the read_only global variable to true will do what I want and let the stored procedures work correctly ( http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html #sysvar_read_only ), but I keep getting the error:
MySQL server works with the -read-only option, so it cannot complete this statement (1290)
The stored procedure I used (for testing purposes) is as follows:
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 temporary creation table and the drag table work fine with the readonly flag - if I comment on the INSERT line, it runs fine, but whenever I want to insert or delete from this temporary table, I get an error.
I am using Mysql 5.1.29-rc. My default storage engine is InnoDB.
, .