1

I am trying to transfer data from remote Server to localhost through MySQL.

In the remote server I created a database HOD and a table tab.

In the localhost I created the database "HOD" and a table "tab". I also created a stored procedure fed_procedure and federated table "fed_tab" to pull the data from remote table. The federated table "fed_tab" gets data from the remote server but the table "tab" updates only when I call the stored procedure using CALL fed_procedure(); I want the table "tab" to update automatically every 5 minutes. So I created an event schedule to update the table "tab" every 5 minutes. But when I execute it, I am getting the error :Access denied for user 'root'@'%' to database 'HOD'". How can I do this?

Below is the stored procedure I created and the event scheduler.

DELIMITER $$

DROP PROCEDURE IF EXISTS `HOD`.`fed_procedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `fed_procedure`()
BEGIN

  declare tab_indx integer(11);

  set tab_indx = (SELECT tab_ndx FROM tab order by tab_ndx desc limit 1);
  insert into tab select * FROM fed_tab where tab_ndx>tab_indx;

END $$

DELIMITER ;

The event scheduler is below

CREATE EVENT fed_procedure ON SCHEDULE EVERY 5 minute
do
CALL fed_procedure()
marc_s
  • 8,932
  • 6
  • 45
  • 51
Ramkumar S
  • 35
  • 4

1 Answers1

1

You can create an event as follows for calling stored procedure in every 5 minutes. 

CREATE EVENT myEvent 
ON SCHEDULE EVERY INTERVAL 5 minute 
DO CALL myProcedure();

Here i am creating full process of executing an event with example.

step 1: checking user permissions.user should contain grant permission as     follows.

   show grants for root@'localhost'\G
   Result:`GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION.
step2: creating an database with two tables as you mentioned tab and fed_tab.

step3: Creating and executing  stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `HOD`.`fed_procedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `fed_procedure`()
BEGIN

  declare tab_indx integer(11);

  set tab_indx = (SELECT id FROM tab order by id desc limit 1);
  insert into tab select * FROM fed_tab where id>tab_indx;

END $$

DELIMITER ;

step4: Creating an event. 

 CREATE EVENT myEvent 
ON SCHEDULE EVERY  5 minute
DO CALL fed_procedure();

it is successfully executing in my machine.the main problem of yours is permission of root user.

Ravi Kumar
  • 176
  • 1
  • 3
  • 11