0

I've got Mysql 5.5 I created a storred procedure

CREATE DEFINER=`root`@`%` PROCEDURE `refresh_mobileTemp`() 
BEGIN
DROP TABLE IF EXISTS mobileTemp;
CREATE TEMPORARY TABLE  mobileTemp AS 
(SELECT distinct
...
 );

END

running 

call refresh_mobileTemp();

The temp table is created.

Than I dropped the temp table and created an Event:

CREATE  EVENT `schedulerMobileTemp` 
 ON SCHEDULE every 10 SECOND
 ON COMPLETION PRESERVE ENABLE
DO 
  CALL refresh_mobileTemp();

every 10 seconds in process list appears a process that create the temp table enter image description herebut than if I call select * from mobileTemp it returns: Error Code: 1146. Table 'mobileTemp' doesn't exist

What I'm missing?

Thanks in advance

Nogothwen
  • 87
  • 8
  • 1
    Temporary tables live only inside the session that creates them, and only until that session finishes. Oh thats the MYSQL session by the way – RiggsFolly Apr 11 '17 at 09:36

1 Answers1

3

As explained in the manual:

A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed.

It means the temporary table is visible and usable only within the event that creates it.

Jocelyn
  • 11,209
  • 10
  • 43
  • 60