--------------------------------------------------
From: "Tomasz Chiliński" <tomasz.chilinski(a)chilan.com>
Sent: Wednesday, March 13, 2013 11:31 PM
To: "LoLe" <lole11(a)o2.pl>
Subject: Re: [lms] Helpdesk a usuwanie zgłoszeń
> W dniu 13.03.2013 23:18, LoLe napisał(a):
>>> W dniu 13.03.2013 04:34, LoLe napisał(a):
>>>> Mam wersję LMS git z około 08.12.2012 i ostatnio zauważyłem że kiedy
>>>> dodam zgłoszenie a później je usunę to w tabelach rtmessages i
>>>> rtticketcategories zostają wpisy dot tego złgoszenia.
>>>> Czy to u mnie tak jest czy to jest błąd który nie został jeszcze
>>>> wykryty?
>>>
>>> To u Ciebie błąd - prawdopodobnie nie w pełni aktualnych schemat bazy
>>> danych.
>>> Rekordy ze wspomnianych tabel usuwane są automatycznie przy usuwaniu
>>> rekordów
>>> z tabeli rttickets, gdyż wiąże je relacja.
>>>
>> No masz rację. Tylko teraz zastanawiam się gdzie popełniłem błąd. Po
>> przebadaniu okazuje się że klucze obce mam założone tylko na tabelach
>> nodeassignments, macs oraz records.
>>
>> Patrząc w plik ze strukturami lms.mysql z katalogu doc widzę że tylko
>> te tabele miały definiowane klucze obce w postaci FOREIGN KEY ....:
>
> Zastanawiałem się jak to możliwe, że mysql przyjął składnię opisu relacji
> niezgodną ze swoją dokumentacją:
> [CONSTRAINT [symbol]] FOREIGN KEY
> [index_name] (index_col_name, ...)
> REFERENCES tbl_name (index_col_name,...)
> [ON DELETE reference_option]
> [ON UPDATE reference_option]
>
> a że było pisane jak w postgresql to pewnie to spowodowało problem ;-)
>
> Fani mysql pewnie teraz wykażą wyższość mysql nad innymi bazami danych
> polegającą na iluzorycznej "auto korekcie" definicji tabel ;-)
>
>> CREATE TABLE nodeassignments (
>> id int(11) NOT NULL auto_increment,
>> nodeid int(11) NOT NULL,
>> assignmentid int(11) NOT NULL,
>> PRIMARY KEY (id),
>> FOREIGN KEY (nodeid) REFERENCES nodes (id) ON DELETE CASCADE ON
>> UPDATE CASCADE,
>> FOREIGN KEY (assignmentid) REFERENCES assignments (id) ON DELETE
>> CASCADE ON UPDATE CASCADE,
>> UNIQUE KEY nodeid (nodeid, assignmentid),
>> INDEX assignmentid (assignmentid)
>> ) ENGINE=InnoDB;
>>
>> a te na których klucze obce nie pozakładały się są zapisane jako
>> REFERENCES ... tak:
>>
>> CREATE TABLE rtattachments (
>> messageid int(11) NOT NULL
>> REFERENCES rtmessages (id) ON DELETE CASCADE ON UPDATE CASCADE,
>> filename varchar(255) NOT NULL DEFAULT '',
>> contenttype varchar(255) NOT NULL DEFAULT '',
>> INDEX messageid (messageid)
>> ) ENGINE=InnoDB;
>>
>> Czy ja coś skopałem przy instalacji?
>
> --
> Pozdrawiam
> Tomasz Chiliński, Chilan
Skleciłem coś co może przydać się innym.
Delety rekordów sierot zrobiłem wg własnych potrzeb. Być może u innych
trzeba będzie gdzie inndziej czyścić.
Oczywiście używacie tego na własną odpowiedzialność.
Może też niech ktoś zerknie na to czy jest ok i sie wypowie.
DELETE FROM `assignments` WHERE customerid not in (select id from
customers);
ALTER TABLE `assignments` ADD FOREIGN KEY ( `customerid` ) REFERENCES
`lms`.`customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `assignments` ADD FOREIGN KEY ( `numberplanid` ) REFERENCES
`lms`.`numberplans` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ;
ALTER TABLE `location_districts` ADD FOREIGN KEY ( `stateid` ) REFERENCES
`lms`.`location_states` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `location_boroughs` ADD FOREIGN KEY ( `districtid` )
REFERENCES `lms`.`location_districts` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE ;
ALTER TABLE `location_cities` ADD FOREIGN KEY ( `boroughid` ) REFERENCES
`lms`.`location_boroughs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `location_streets` ADD FOREIGN KEY ( `cityid` ) REFERENCES
`lms`.`location_cities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `location_streets` ADD INDEX ( `typeid` ) ;
ALTER TABLE `location_streets` ADD FOREIGN KEY ( `typeid` ) REFERENCES
`lms`.`location_street_types` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ;
ALTER TABLE `netdevices` ADD FOREIGN KEY ( `location_city` ) REFERENCES
`lms`.`location_cities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ;
ALTER TABLE `netdevices` ADD FOREIGN KEY ( `location_street` ) REFERENCES
`lms`.`location_streets` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ;
ALTER TABLE `netdevices` ADD FOREIGN KEY ( `channelid` ) REFERENCES
`lms`.`ewx_channels` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ;
ALTER TABLE `nodes` ADD FOREIGN KEY ( `location_city` ) REFERENCES
`lms`.`location_cities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ;
ALTER TABLE `nodes` ADD FOREIGN KEY ( `location_street` ) REFERENCES
`lms`.`location_streets` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ;
ALTER TABLE `nodelocks` ADD INDEX ( `nodeid` );
ALTER TABLE `nodelocks` ADD FOREIGN KEY ( `nodeid` ) REFERENCES
`lms`.`nodes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `rtattachments` ADD FOREIGN KEY ( `messageid` ) REFERENCES
`lms`.`rtmessages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
DELETE FROM `rtmessages` WHERE ticketid NOT IN (SELECT id FROM rttickets);
ALTER TABLE `rtmessages` ADD FOREIGN KEY ( `ticketid` ) REFERENCES
`lms`.`rttickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
DELETE from `rtnotes` WHERE ticketid NOT IN (SELECT id FROM rttickets);
ALTER TABLE `rtnotes` ADD FOREIGN KEY ( `ticketid` ) REFERENCES
`lms`.`rttickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `rtnotes` ADD FOREIGN KEY ( `userid` ) REFERENCES
`lms`.`users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `rttickets` ADD FOREIGN KEY ( `queueid` ) REFERENCES
`lms`.`rtqueues` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `rtrights` ADD INDEX ( `queueid` );
ALTER TABLE `rtrights` ADD FOREIGN KEY ( `userid` ) REFERENCES
`lms`.`users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `rtrights` ADD FOREIGN KEY ( `queueid` ) REFERENCES
`lms`.`rtqueues` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `rtcategoryusers` ADD INDEX ( `categoryid` );
ALTER TABLE `rtcategoryusers` ADD FOREIGN KEY ( `userid` ) REFERENCES
`lms`.`users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `rtcategoryusers` ADD FOREIGN KEY ( `categoryid` ) REFERENCES
`lms`.`rtcategories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
Delete FROM `rtticketcategories` where `ticketid` not in (select id from
rttickets);
ALTER TABLE `rtticketcategories` ADD INDEX ( `categoryid` );
ALTER TABLE `rtticketcategories` ADD FOREIGN KEY ( `ticketid` ) REFERENCES
`lms`.`rttickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `rtticketcategories` ADD FOREIGN KEY ( `categoryid` )
REFERENCES `lms`.`rtcategories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
;
delete FROM `promotionschemas` where `promotionid` not in (select id from
promotions);
ALTER TABLE `promotionschemas` ADD FOREIGN KEY ( `promotionid` )
REFERENCES `lms`.`promotions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `promotionschemas` ADD FOREIGN KEY ( `ctariffid` ) REFERENCES
`lms`.`tariffs` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE ;
delete FROM `promotionassignments` WHERE `promotionschemaid` not in (select
id from promotionschemas);
delete FROM `promotionassignments` WHERE `tariffid` not in (select id from
tariffs);
ALTER TABLE `promotionassignments` ADD FOREIGN KEY ( `promotionschemaid` )
REFERENCES `lms`.`promotionschemas` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE
;
ALTER TABLE `promotionassignments` ADD FOREIGN KEY ( `tariffid` )
REFERENCES `lms`.`tariffs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `customerassignments` ADD FOREIGN KEY ( `customergroupid` )
REFERENCES `lms`.`customergroups` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE ;
ALTER TABLE `customerassignments` ADD FOREIGN KEY ( `customerid` )
REFERENCES `lms`.`customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `sourcefiles` ADD FOREIGN KEY ( `userid` ) REFERENCES
`lms`.`users` (`id`);
DELETE FROM `cashimport` where `customerid` not in (select id from
customers);
ALTER TABLE `cashimport` ADD FOREIGN KEY ( `customerid` ) REFERENCES
`lms`.`customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ;
ALTER TABLE `cashimport` ADD FOREIGN KEY ( `sourceid` ) REFERENCES
`lms`.`cashsources` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ;
ALTER TABLE `cashimport` ADD FOREIGN KEY ( `sourcefileid` ) REFERENCES
`lms`.`sourcefiles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ;
ALTER TABLE `imessengers` ADD FOREIGN KEY ( `customerid` ) REFERENCES
`lms`.`customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `customercontacts` ADD FOREIGN KEY ( `customerid` ) REFERENCES
`lms`.`customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `excludedgroups` ADD FOREIGN KEY ( `customergroupid` )
REFERENCES `lms`.`customergroups` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE ;
ALTER TABLE `managementurls` ADD INDEX ( `netdevid` );
delete FROM `managementurls` WHERE `netdevid` not in (select id from
netdevices);
ALTER TABLE `managementurls` ADD FOREIGN KEY ( `netdevid` ) REFERENCES
`lms`.`netdevices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
