从属服务器上存储过程中的临时表设置为只读

发布于 2024-07-19 18:49:58 字数 1050 浏览 3 评论 0原文

我们设置了主/从复制方案，最近遇到了问题，因为有些用户直接在从机而不是主机上写入，使得整个设置不一致。

为了防止这些问题再次发生，我们决定删除访问从站的用户的插入、删除、更新等权限。问题是某些存储过程（用于读取）需要临时表。

我读到，将全局变量 read_only 更改为 true 将执行我想要的操作，并允许存储过程正常工作（ http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_only ）但我不断得到错误：

MySQL 服务器正在使用 --read-only 选项运行，因此无法执行此语句（1290）

我使用的存储过程（用于测试目的）是这样的：

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 ;

创建临时表和删除表与只读标志一起工作正常 - 如果我注释 INSERT 行，它运行正常 - 但每当我想要从临时表中插入或删除，我收到错误消息。

我使用Mysql 5.1.29-rc。我的默认存储引擎是InnoDB。

预先感谢，这个问题真的让我发疯。

原文

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.

分享到QQ

分享到微博