如何在每次在Amazon RDS上创建mysql连接时调用过程 [英] How to call a procedure on every time mysql connection created on amazon RDS

问题描述

我想为每个会话更改Amazon RDS的时区. 默认情况下，RDS时区为UTC. 我想将其更改为Asia/Calcutta.

I want to change timezone for amazon RDS for every session. RDS timezone by default is UTC. I want to change it to Asia/Calcutta.

要更改特定会话的时区，我已定义了过程

To change timezone for specific session I have defined procedure

DELIMITER |  
CREATE PROCEDURE mysql.store_time_zone ()  
   IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN     
       SET SESSION time_zone = '+5:30';  
   END IF 
|
DELIMITER ;

现在，我不知道每次创建新的连接/会话时都应该怎么称呼它.

Now I donot know how should I call it every time new connection/Session is created.

我已从给定链接中获取参考.我无法运行

I have taken reference from given link. I am not able to run

rds-modify-db-parameter-group PARAMGROUP --parameters "name=init_connect, value='CALL mysql.store_time_zone', method=immediate"

它给出了错误 错误1064(42000):您的SQL语法有错误；在第1行的"rds-modify-db-parameter-group PARAMGROUP --parameters"name = init_connect，value ="附近，请查看与您的MySQL服务器版本相对应的手册以获取正确的语法

It gives error ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rds-modify-db-parameter-group PARAMGROUP --parameters "name=init_connect, value=' at line 1

https://stackoverflow.com/a/15198369/3551179

先谢谢了.

推荐答案

您处于正确的轨道，但是由于另一答案中的措词不明确，您误解了一个步骤:

You are on the right track, but you have misunderstood one step, due to ambiguous phrasing in the other answer:

连接到您的实例，然后运行以下命令

Connect to your instance, and run the following command

因此，您实际上是在这样做:

So, you're essentially doing this:

mysql> rds-modify-db-parameter-group ...

但是您应该这样做:

$ rds-modify-db-parameter-group ...

这不是MySQL语句，而是shell命令. RDS实例没有外壳程序访问权限，因此您需要另一台计算机才能在其上运行此命令.通常，这可能是一个EC2实例，所以我假设这就是另一个管理者的作者您的实例"的意思，但是它可以是任何具有

It's not a MySQL statement, it's a shell command. RDS instances do not have shell access, so you need a different machine to run this command on. Typically, this might be an EC2 instance, so I assume that's what the other manager's author meant by "your instance," but it could be any machine that has the RDS command line interface tools installed.

您还可以从 RDS控制台.

还请注意，通过将init_connect设置为:

Note also that you could also do this a bit more concisely, avoiding the stored procedure altogether and even the test for rdsadmin, by setting init_connect to:

SET @@time_zone = '+5:30'

rdsadmin用户应不受init_connect的影响，因为它具有SUPER特权.

The rdsadmin user should be immune to init_connect because it has the SUPER privilege.

对于具有SUPER特权的用户，不会执行init_connect的内容.这样做是为了防止init_connect的错误值不会阻止所有客户端连接.例如，该值可能包含一个语法错误的语句，从而导致客户端连接失败.对于具有SUPER特权的用户不执行init_connect，将使他们打开连接并修复init_connect值.

The content of init_connect is not executed for users that have the SUPER privilege. This is done so that an erroneous value for init_connect does not prevent all clients from connecting. For example, the value might contain a statement that has a syntax error, thus causing client connections to fail. Not executing init_connect for users that have the SUPER privilege enables them to open a connection and fix the init_connect value.

http://dev.mysql. com/doc/refman/5.6/en/server-system-variables.html#sysvar_init_connect

如果您还是想测试rdsadmin ...

If you'd rather test for rdsadmin anyway...

SET @@time_zone = CASE
     WHEN CURRENT_USER() LIKE 'rdsadmin@%' THEN @@time_zone
    ELSE '+5:30' END

将变量设置为相同的变量会使值保持不变.

Setting a variable to the same variable leaves the value unchanged.

注意@@time_zone和SESSION time_zone是等效的.

这篇关于如何在每次在Amazon RDS上创建mysql连接时调用过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！