Can't delete events (resolved)

[freymann]

We were away over the (Canadian) long weekend, so I programmed LMCE to turn on/off some house lights. One item I wanted was to turn on a small night light in the kitchen at sunset, and turn it back off at sunrise.

As far as I know, that went fine, but now that we're back, I can't delete those two events in the web admin.

If I click on the "Respond to Events" link in the left menu, I see:

Code: [Select]

                            Sunset  Advanced Edit Delete
Sunset - Kitchen Light ON Sunset Advanced Edit Delete
Sunrise - Kitchen Light OFF Sunrise Advanced Edit Delete

The first entry doesn't have a description and I remember seeing that a while ago and trying to delete it.

The other two ware what I created before we left and now I can't delete them either.

When I click on the "Delete" link, I get a pop-up asking me if I'm sure I want to delete the event, I click on "OK" and the screen refreshes with no changes.

If I click on "Advanced" I see the entire list, but the "Delete" link has no effect there either.

Any suggestions on how to delete these things?

[jondecker76]

If I remember right, I've had this happen before.. I think I kept clicking delete maybe 4 or 5 times in a row and they finally disappeared (maybe they are being added in the database multiple times?)

[freymann]

I think it's possible that a Sunrise/Sunset timed event may produce a whack of entries (one per day or a range of days)...

The SQL to delete the events looks rather confusing at first glance. I haven't had time to familiarize myself with all the tables and keys.

I will likely spend some time on the php and mysql code. No sense leaving these two events active when they are no longer needed, especially since I yanked a light module from the living room to use over the weekend in the kitchen!

[freymann]

I had a look over the code used to delete "respond to events" events..

/var/www/pluto-admin/operations/events/respondToEvents.php

I had 3 "respond to events" I wanted to get rid of with the "dID" of 8, 26 and 27.

Manually running the MySQL Query for any of the 3 id's resulted in an empty result set. So I got to looking at the query more closely.

This doesn't work:

Code: [Select]

SELECT EventHandler.*, Criteria.FK_CriteriaParmNesting
FROM EventHandler
INNER JOIN Criteria ON FK_Criteria=PK_Criteria
WHERE PK_EventHandler=? AND TimedEvent IS NULL

However, this does:

Code: [Select]

SELECT EventHandler.*, Criteria.FK_CriteriaParmNesting
FROM EventHandler, Criteria
WHERE PK_EventHandler=? AND TimedEvent IS NULL

The reason?  FK_Criteria equals NULL and never matches the PK_Criteria field which must a number int(11).

This also means the third mysql command fails:

Code: [Select]

$deleteCriteria='DELETE FROM Criteria WHERE PK_Criteria=?';
dbADO->Execute($deleteCriteria,$rowEH['FK_Criteria']);

once again because PK_Criteria gets set to NULL and mysql errors out.

I made a backup copy of this script, adjusted the initial Mysql query, rem'd out the two lines above, and went back to the web admin and deleted the three 'respond to events' entries.

[jondecker76]

I'm at work so I can't see the table structure for this SQL statement, but looking at the change briefly - it would almost appear that Criteria.FK_CriteriaParmNesting would return every FK_CriteriaParamNesting, therefore wiping the entire CriteriaParamNesting table when it loops through to delete entries (which you may not notice if you don't have any nested criteria! (I may be wrong, but it sure looks to me like it would)

I'm going to look at SVN really quick and see if something sticks out at me

[jondecker76]

took a peek at the svn online (I'm at work).. it seems to me (without being able to test, and not having a copy of the table structure in front of me ) that the following should work in all cases:

Code: [Select]

SELECT EventHandler.*, Criteria.FK_CriteriaParamNesting
FROM EventHandler LEFT JOIN Criteria ON EventHandler.FK_Criteria = Criteria.PK_Criteria
WHERE EventHandler.PK_EventHandler=? AND EventHandler.TimedEvent IS NULL;


*note, I'm just assuming that TimedEvent is a field of the EventHandler table

Basically, the wrong join is bing used, and the SQL statement is pretty sloppy by not specifying which tables the fields belong to.

Also, because the LEFT JOIN will return null results, some of the DELETE statments may need skipped by checking for null results, for example:

Code: [Select]

if($rowEH['FK_CriteriaParmNesting']!=NULL) {
     $deleteCriteriaParms='DELETE FROM CriteriaParm WHERE FK_CriteriaParmNesting=?';
     $dbADO->Execute($deleteCriteriaParms,$rowEH['FK_CriteriaParmNesting']);
}

But I'd have to test it to be sure

[freymann]

Code: [Select]

SELECT EventHandler.*, Criteria.FK_CriteriaParamNesting
FROM EventHandler LEFT JOIN Criteria ON EventHandler.FK_Criteria = Criteria.PK_Criteria
WHERE EventHandler.PK_EventHandler=? AND EventHandler.TimedEvent IS NULL;


Code: [Select]

if($rowEH['FK_CriteriaParmNesting']!=NULL) {
     $deleteCriteriaParms='DELETE FROM CriteriaParm WHERE FK_CriteriaParmNesting=?';
     $dbADO->Execute($deleteCriteriaParms,$rowEH['FK_CriteriaParmNesting']);
}


I think that would clean things up ;-)

TimedEvent is a field in the EventHandler table. Adjusting for the NULLs would also fix up the mysql error returned on the 3rd query. Nice!

The NULL value returned is FK_Criteria and that effects the 3rd query:

Code: [Select]

if($rowEH['FK_Criteria']!=NULL) {
   $deleteCriteria='DELETE FROM Criteria WHERE PK_Criteria=?';
   $dbADO->Execute($deleteCriteria,$rowEH['FK_Criteria']);
}

I still don't think you'll match anything because on my 3 'respond to events' events, FK_Criteria was always NULL and therefore NEVER matched anything...

Therefore this code:

INNER JOIN Criteria ON FK_Criteria=PK_Criteria

fails to return any results and nothing gets deleted. You can append the table name before the two field names but NULL still does not equal an integer.

[jondecker76]

thats why I suggested the LEFT JOIN as it will return null entries if there are no matches in the related table, as opposed to the INNER JOIN which only returns results that are equal in both tables. (maybe you didn't notice the subtle change in my post)

Then, the added if statement around the SQL DELETE statement will ensure it only executes if the given result is not null. While I didn't test it or double check it for accuracy, assuming I didn't make any typo's etc, it will work.