...

View Full Version : please help me with writing event in mysql



madi
12-10-2011, 08:30 PM
I worte some query in my database that all of them run with out any problem. but when I put them in an event that do them regularly, I face with this error. I tried this event several times and I understood that from where I put "insert into" code it doesnt work. I cant understand what is the problem. please guide me.
error in mysql:
#1048 - Column 'userid' cannot be null
query:
select @year:=extract(year from CURDATE());
select @month:=extract(month from CURDATE());
select @day:=extract(day from CURDATE());
select @id:=thanksid2, @uid:=userid, @sname:=sendername,@rname:=recivername,@remail:=reciveremail,@text:=text,@attach:=attach,@score:=sco re,@cost:=cost,@syear:=sendyear,@smonth:=sendmonth,@sday:=sendday,@subj:=subject,@tempid:=tempid,@dr aftid:=draftid from thanksmsg2 where sendyear=@year and sendmonth=@month and sendday=@day;
insert into thanksmsg1(thanksid1,userid,sendername,recivername,reciveremail,text,attach,score,cost,sendyear,send month,sendday,subject,tempid,draftid)values(@id,@uid,@sname,@rname,@remail,@text,@attach,@score,@cos t,@syear,@smonth,@sday,@subj,@tempid,@draftid);
delete from thanksmsg2 where thanksid2=@id;
event:
CREATE EVENT madi ON SCHEDULE EVERY 1 DAY DO
select @year:=extract(year from CURDATE());
select @month:=extract(month from CURDATE());
select @day:=extract(day from CURDATE());
select @id:=thanksid2, @uid:=userid, @sname:=sendername,@rname:=recivername,@remail:=reciveremail,@text:=text,@attach:=attach,@score:=sco re,@cost:=cost,@syear:=sendyear,@smonth:=sendmonth,@sday:=sendday,@subj:=subject,@tempid:=tempid,@dr aftid:=draftid from thanksmsg2 where sendyear=@year and sendmonth=@month and sendday=@day;
insert into thanksmsg1(thanksid1,userid,sendername,recivername,reciveremail,text,attach,score,cost,sendyear,send month,sendday,subject,tempid,draftid)values(@id,@uid,@sname,@rname,@remail,@text,@attach,@score,@cos t,@syear,@smonth,@sday,@subj,@tempid,@draftid);
delete from thanksmsg2 where thanksid2=@id;

Old Pedant
12-11-2011, 07:21 AM
Why are you doing a select followed by an insert? Why not write it as a single statement?


INSERT INTO thanksmsg1( thanksid1, userid, sendername, recivername,reciveremail,
text, attach, score, cost, sendyear, sendmonth, sendday, subject,
tempid, draftid )
SELECT thanksid2, userid, sendername, recivername, reciveremail,
text, attach, score, cost, sendyear, sendmonth, sendday, subject,
tempid, draftid
FROM thanksmsg2
WHERE sendyear=YEAR(CURDATE())
AND sendmonth=MONTH(CURDATE())
AND sendday=DAY(CURDATE())
AND userid IS NOT NULL;

DELETE FROM thanksmsg2
WHERE sendyear=YEAR(CURDATE())
AND sendmonth=MONTH(CURDATE())
AND sendday=DAY(CURDATE());

I tacked on the userid IS NOT NULL on the assumption that indeed a NULL userid could be causing your problem. But it could also be that, in your original code, your SELECT was returning NO RECORDS AT ALL. But then you went ahead and did the INSERT anyway and...kablooey. If you use the above code, that would never happen. If there are no matching records from the SELECT, then nothing gets INSERTed, and there is no error. And then the DELETE will delete no records, but that's also not an error.

madi
12-21-2011, 11:19 AM
Hi,
I have another problem with this code:
I pasted your suggested code in mysql, they ran with out any problem but when I add "create event ..." , DELETE codes ,delete all of the records that condition is true for them before inseting them in next table and also another problem is that EVENT doesnt work regulary.
thanks for your help

Old Pedant
12-21-2011, 09:25 PM
We can't help with code we can't see.

I have no idea what "CREATE EVENT..." does in your code.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum