Error in event creation using MySQL

Question

Am using the below code to create an EVENT in MYSQL. In this time i want to drop and create a table using a query.

Drop  Event  if exists EVT_UP_TIMESHEET;
CREATE EVENT EVT_UP_TIMESHEET
  ON SCHEDULE EVERY '1' Day
  STARTS '2012-08-01 12:00:00'
DO

  Drop table if exists tbl_temp;

 create table tbl_temp as ( SELECT e.userid            AS Employee_ID,

       e.memo              AS Employee_Name,
   e.Department        AS Department,
.....

It returns the following error:

ERROR : Table tbl_temp already exists.

Please help me to do this.

Try to call just DROP TABLE .. and look if it will be dropped. — hsz
– hsz, Commented Aug 31, 2012 at 6:43
yes when i execute the drop comment the table dropped successfully. — romi
– romi, Commented Aug 31, 2012 at 6:49

Himanshu · Accepted Answer · 2012-08-31 09:31:39Z

1

Use CREATE TABLE IF NOT EXISTS tbl_temp instead of create table tbl_temp

Or to delete the table you can use TRUNCATE TABLE instead of DROP TABLE and to create you can use INSERT...SELECT instead of CREATE TABLE.

edited Aug 31, 2012 at 9:31

answered Aug 31, 2012 at 6:43

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

romi Over a year ago

I want to drop that table every day and create again.

Devart · Accepted Answer · 2012-08-31 06:59:01Z

0

As a workaround - try to use TRUNCATE TABLE and INSERT...SELECT statements instead of DROP/CREATE TABLE.

answered Aug 31, 2012 at 6:59

Devart

120k23 gold badges172 silver badges189 bronze badges

4 Comments

romi Over a year ago

I have encounter the new error index i_emp_temp is already exists "create index i_emp_tmp on tbl_temp (employee_id,punchdate);"

Devart Over a year ago

Create table with all its indexes once. And do not change table structure from the event.

romi Over a year ago

K i accept this. But the table doesn't truncate.Records inserted double time.

Devart Over a year ago

Hm, it looks like a MySQL bug.

Collectives™ on Stack Overflow

Error in event creation using MySQL

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related