Witam!

Wkońcu natrafiłem na problem dotyczący kasowania danych z bazy mysql. Temat dotyczy bardziej bazy niż php ale mam nadzieję, że mi pomożecie.. Mam kilka tabel. Kasując informacje z jednej, muszę usunąć odpowiednie dane z innych tabeli. Wiem, że instrukcji DELETE nie można użyć tak jak select. Dla każdej tabeli należy wywołać osobno instukcję DELETE. Poczytałem sobi o trancakcjach i blokowaniu ale nie wiem jak to zastosować w praktyce. Chodzi mi o to, że gdy skasuję z tablei kont użytkownika, a z drugiej tabeli (tam gdzie mam dane o użytkownikach) nie uda się skaować odpowiedniego wpisu (np z nieznanych przyczyn) to tabelę z kontami doprowadzę do stanu z przed wykonani instrukcji DELETE, po to aby baza się nie posypała. Jak to zrobić?

Mozna to zrealizowac na 2 sposoby.

1. Tabela typu INNO DB i pomiedzy tabelami sa zrobione relacje, za DELETE zakladasz CASCADE, powoduje to ze jesli usuniesz rekord gdzie jego klucz podstawowy jest kluczem obcym w innej tabeli to sie odrazu usuwa rekord powiazany. Mysle ze zrozumiales o co chodzi.

2. Transaction. Oczywiscie rowniez tabela typu INNO DB.
- Przed usunieciem rekordow startujesz transakcje:
mysql_query('BEGIN', $szConnection);

nastpnie usuwasz rekordy, sprawdzasz czy funkcje mysql_query zwrocila true, jesli zwroci usuwasz kolejne rekordy, jesli zwroci false wycofujesz transakcje

mysql_query('ROLLBACK', $szConnection);

Jesli na koncu wszystko jest ok to zatwierdzasz:
mysql_query('COMMIT', $szConnection);

Jesli cos poszlo nie tak to oczywiscie mozesz wycofac

Co to jest ta tabela typu INNO DB? I kiedy stosować taki sposób? Tylko przy usuwaniu? Czy może też przy UPDATE?

CASCADE? Możesz podać więcej szczegółów?



Do Gościa:
Przy usuwaniu i uzupełnianiu. INNO DB obsługują transakcje

Z tego co zauwazylem to INNO DB to taki typ tabeli w ktorej mozna zrealizowac transakcje i relacje miedzy tabelami. Ten typ tabeli stosuje wszedzie nawet jestli mam tylko select, juz z przyzwyczajenia. Polecam przy INSERT UPDATE DELETE poniewaz wtedy mamy do dyspozycji transakcje i mozliwosc relacji.


Podam moze przyklady:
Mamy 2 tabele ze soba powiazane

USER
Use_Id
Use_Imie

KLASA
Kla_Id
Kla_Nazwa
Kla_Use_Id

Update:
Zmieniamy np Use_Id w tabeli USER i atumatycznie zmienia nam sie wartosc pola Kla_Use_Id w tabeli KLASA, gdzie id bylo takie samo jak id usera

Delete:
Usuwamy rekord usera z tabeli USER i automatycznie usuwa sie rekord powiazany z klasa, o takim samym id

Mysle ze teraz to jasniej przedstawilem, zawsze google pozostaja.

Ale jak zrobić takie powiązanie, że jak usuwam rekord z jednej tabeli to z rugiej jest usuwany automatycznie?
W tabelach które podałeś zamiast Kla_Use_id powinien być Use_id... Przecież ma to być klucz obcy. Co lepsze transakcje czy to cascadde?

Kla_Use_Id to tylko nazwa pola, powiazania robi sie inaczej, np w phpMyAdminie masz cos takiego klikasz na tebelke dajesz widod relacji (czy cos w tym stylu) i tam wybierasz co bedzie kluczem obcym i typ dla on update i on delete.


Transakcje - bo jak sie pomylisz przy usuwaniu z polaczen cascadowych to poleca Ci inne rekordy. Tak naprawde to zadko uzylem cascade moze 1, 2 projekty reszta to jest restrict, bo przynajmniej wiem ze cos trzyma w innej tabeli.

No dobra ale jak to zrobić, żę usuwając jeden wpis z tabeli A automatycznie zostanie usunięty wpis w drugiej tabeli? Zamiast DELETE pisać CASCADE?

Jak już ustawisz wszystkie relacje między tabelami to wystarczy zwykły DELETE.

Nie wystarczy ze zrobisz:

[SQL] pobierz, plaintext 
DELETE 
FROM user WHERE use_id = 1
[SQL] pobierz, plaintext 

To automatycznie usuwa sie rekord z 2 tabeli (czyli klasa o id powiazanym).

PYTANIE 1
Hmm.. Chyba źle to robiłem. Najpierw napiszę dlaczego się nie zrozumieliśmy - przynajmniej mi się tak wydaje. Tworząc bazę popełniałem chyba błąd. Założmy że miałem dwie tabele KONTA i DANE.


TABELA konta
konta_id PRIMARY KEY
login
haslo

TABELA dane
dane_id PRIMARY KEY
konta_id
imie
nazwisko
adres
telefon

i np kiedy chciałem pobrać dane użytkownika o loginie root robiłem to tak:

[SQL] pobierz, plaintext 
SELECT  konta.login, dane.imie, dane.nazwisko 
FROM konta, dane WHERE konta.login="root" AND konta.konta_id = dane.konta_id
[SQL] pobierz, plaintext 

Wtedy wypisywało mi dane które potrzebowałem - no niby ok..
I TUTAJ POJAWIA SI TEN HACZYK - CHYBA
W książce do mysqla poczytałem o kluczach obcych. Dopiero wczoraj bo ten rozdział był dość daleko w książce. Jeśli usune z tabeli konta wiersz w którym zapisany jest login i hasło roota, to w tabeli dane pozostaje wpis dotyczący roota (tzn imie, nazwisko, adres, telefon roota. jeśli w tabeli konta root miał wartośc primary kay 1, to wlasnie ten wpsi jest w tabeli dane), zależności względem tabeli konta nie są usuwane z tabeli dane. I dlatego nie zrozumiałem co pisaliście. Ale dlaczego tak się dzieje? Czy może to byc wina (tak mi się wydaje) tego, że tworząć tabelę nie pisałem np dla tabeli dane CONSTRAINT i REFERENCE ? Jeśli tak to jak utworzyć taką tabele poprawnie?

PYTANIE 2
Wracając do transakcji i blokowania. Jeszcze raz bardzo dokładnie pprzeczytałem ten rozdział. Poprawcie mnie jeśli się mylę:

Domyślnymi tabelami w MySQL jest MyISAM. On nie obsługuje transakcji, ale obsługuje blokowanie.
Jeśli chcę korzystać z transakcji to należy dodać odpowiedni wpis do pliku konfiguracyjnego albo na koncu polecenia tworzącego tabelę dopisać type = InnoDB.
Tabele INNODB obsługują transakcje i blokowanie.

Ale mam jeszcze pewne wątpliwości.
a)Czy nie lepiej używać MyISAM i używać blokowanie tabeli?
b)Czy lepiej przejść na tabelą INNODB i korzystać z transakcji?
c)Jeśli korzystam z transakcji to korzytsać z opcji blokowania wierszy poprzez np

[SQL] pobierz, plaintext 
SELECT ... FOR UPDATE
[SQL] pobierz, plaintext 

d)Jeśli chcę korzytsać z transakcji, ale nie wiem jaka wartość AUTOCOMMIT będzie ustawiona na serwerze, to czy muszę za każdym razem przed wykonaniem transakcji wykonywać zapytanie (w kodzie php)

[SQL] pobierz, plaintext 
SET AUTOCOMMIT = 0;
[SQL] pobierz, plaintext 

?
e) Jeśli nie zmienię wartości AUTOCOMMIT na 0 ( a domyślnie jest 1) to mogę korzystać z transakcji stosując zapis BEGIN WORK, COMMIT albo ROLLBACK? Czy nie wystąpi błąd?

Proszę o dokładną odpowiedź
POZDRAWIAM

Wsumie sam sobie odpowiedziales, dlatego sie dodaje powiazania zeby np automatycznie sie to usuwalo, lub poprostu wymuszalo usuniecie. Oczywiscie dochodza do tego operacje optymalizatora ale to juz inny problem. Zapytanie oczywiscie masz poprawnie.



Wszystko ok, tylko zeby mozna bylo utworzyc ta tabele to jednak musi byc w configu ustawione.


nie


tak



Jesli masz autocommit = 0 i masz tabele Inno DB to musisz zatwiedzac transakcje zeby rekord sie dodal. Dka autocommit = 1 mozesz poprostu wykonac zwyklego INSERTA bez transakcji i sie doda. Nie musisz w kodzie tego umieszczac jak stosujesz transakcje, wazne jest by byl start i zatwierdzenie lub wycofanie.

Czyli podsumowujac nie wazne jaki masz autocommit dla transakcji, bo wazne jest start i koniec.

Jak cos to pisz postaram sie odpowiedziec, jak bede wiedzial oczywiscie.

Jak dokładnie (np na przykładzie mojej tabeli) takie powiązanie utworzyć?

A co to za problem?



Czyli w książce jest błąd? Bo napisali że albo dodać type albo ustawić w pliku i wtedy wszystkie tworzone tabele będą INNODB..




Bo mi chodzi o to, że piszę aplikacje dla firmy. Ja poprawię tabele na INNODB. Wszędzie tam gdzie będę korzystał z instrukcji DELETE, UPDATE , INSERT będę korzystał z transakcji. Będę używał BEGIN i COMMIT lub ROLLBACK. Ale nie wiem jaką wartość ustawią sobie goście na serwerze w firmie. Jeśli będzie AUTOCOMMIT=0 to wszystko OK. ALe jeśli pozostawią sobie domyślną wartość 1 to co wtedy? Kod będzie poprawny? Rozumiesz - AUTOCOMMIT=1 a ja i tak używam BEGIN i COMMIT..

Aha i jeszcze jedno. W tabelach MyISAM można blokować tabele - niby taki rodzaj zabezpieczenia. A w tabelach INNODB jest blokowanie wierszy. No i w książce w pewnym momencie podany jest przykład na jawne blokowanie wierszy przykład:
KLIENT A:

[SQL] pobierz, plaintext 
SELECT 0 
FROM customer WHERE customer_id = 15 FOR UPDATE;
[SQL] pobierz, plaintext 

->polecenie wykonało się (zablokowaie wiersza 15tego)

W między czasie KLIENT B:

[SQL] pobierz, plaintext 
UPDATE customer SET fname = 'Annie' WHERE customer_id = 8;
[SQL] pobierz, plaintext 

-> polecenie wykonało się

[SQL] pobierz, plaintext 
UPDATE customer SET fname = 'Dave' WHERE customer_id = 15;
[SQL] pobierz, plaintext 

-> polecenie nie wykonało się ponieważ klient A zablokował ten wiersz.

KLIENT A

[SQL] pobierz, plaintext 
COMMIT;
[SQL] pobierz, plaintext 

KLIENT B
Teraz wykonało się wstrzymane polecenie (UPDATE .... )

[SQL] pobierz, plaintext 
COMMIT;
[SQL] pobierz, plaintext 

A dalej w podrozdziale dotyczącym zakleszczeń opisywany jest przykład użytkownika A i B. Obydwaj stosują BEGIN i COMMIT i dochodzi do zakleszczenia. I już sam nie wiem, czy stosując transakcje automatycznie stosowane jest blokowanie wierszy? Czy żeby bardziej zabezpieczyć program przed błędami, warto stosować blokowanie wierszy i transakcje? Czy to będzie wogóle poprawne?

Dla konta_id z tabeli dane tworzysz index, nastepnie ustawiasz mu powiazanie. Np w phpMyAdminie jest to widok relacyjny i wybierasz tam wskazanie z klucza obcego do klucza podstawowego, czyli jaki klucz podstawowy ma byc kluczem obcym w tabeli dane.


Zle napisalem, raczej to nie jest problem ............ Poczytaj sobie na temat optymalizatora w MySQLu to zrozumiesz idea dzialania wyboru rekordow itd. Zobaczysz ze relacje przyspieszaja dzialanie bazy. Takie przynajmniej jest zalozenie. Kiedys dopadlem wspaniala ksiazeczke tylko oparta na ORACLE, tam troche inaczej dziala, ale jak sie czyta jak mozna baze dostroic to az wlosy staja.
Troche odbieglem od tematu ale polecam zebys sie zainteresowal tym zagadnieniem.


Moze to od wersjie bazy zalezy, ale pamietam ze pod kazdym klientem MySQLa nie dalo sie nic zrobic, po przestawieniu configu poszlo (oczywiscie linux)



Zadziala ok. Masz ponizej kod sam sobie zobacz jak to sie odbywa. Wlaczaj i wylaczaj autocommit, mozesz tez komentowac samego commita i zobaczysz czy dodaje itd. Najlepiej jak zrobisz sobie kilka kombinacji tego kodu i sam sie przekonasz jak to jest. Czlowiek nie uwierzy i nie zrozumie jak na wlasne oczy nie zobaczy (heheheh)

[SQL] pobierz, plaintext 
SET AUTOCOMMIT= 0;
BEGIN;
INSERT 
INTO a (a) VALUES (3);
COMMIT;
[SQL] pobierz, plaintext 

To sie zdarza, opisane jest w jaki sposob dochodzi do tego. Jak mozesz podaj autorow i nazwe ksiazeczki chetnie zapoznam sie z tym tematem.

Odnosnie blokowanie i transakcji, mysle ze wystarcza tylko transakcje, jesli robisz dobra obsluge wszystkich zwracanych bledow i baza jest dobrze zaprojektowana to nie dojdzie do sytuacji jak zakleszczenie. Tak sobie mysle ze to moga byc 2 startujace transakcje blokujace 2 rekordy a nastepnie nawzajem przez siebie zablokowanych rekordow uzywaja i transakcje poprostu wisza.

Nie używam phpmyadmina do takich rzeczy. Wolę z palca - nauczę się. Czy takie coś będzie poprawne?

[SQL] pobierz, plaintext 
CREATE TABLE dane
(
dane_id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
konta_id int int NOT NULL,
imie varchar(30),
nazwisko varchar(30),
adres varchar (50),
telefon varchar(20)
INDEX (konta_id),
CONSTRAINT dane_konta_id_fk FOREIGN KEY(konta_id) REFERENCES konta(konta_id)
} TYPE = InnoDB
[SQL] pobierz, plaintext 

A tak apropo po co ten INDEX? PRzecież index służy do przyspieszania wyszukiwania.. Ale raczej nie jest niezbędny..?


"Bazy danych i MySQL - od podstaw" Richard Stones i Neil Matthew (strona 272-277)
To jest dokładnie ta



Mógłbyś podać przykład obsługi takiego błędu? Czy robić w pętle? Np próbuj usunąć podany wiersz dopóki się nie uda..

Tak mi się też wydaje. Tylko troszkę namieszali i nie wiedziałem czy stosując transakcje należy stosować blokadę. Swoją drogą to na tabelach MyISAM też da się ładnie obejść problem niespójnośći

[SQL] pobierz, plaintext 
CREATE TABLE `bb` (
`a` int( 11 ) NOT NULL AUTO_INCREMENT ,
`b` int( 11 ) NOT NULL,
PRIMARY KEY ( `a` ),
KEY `b` ( `b` ) ,
CONSTRAINT `bb_ibfk` FOREIGN KEY ( `b` ) REFERENCES `aa` ( `a` )   ) TYPE = InnoDB
[SQL] pobierz, plaintext 

No wlasnie musi to byc.


Jesli zapytanie zwroci blad cofiesz transakcje i pokazujesz ladny blad userowi.


Zawsze sie da Kwestia tylko jak to zaimplementujesz.

Ok ostatnie pytanie. Zrobiłem dwie tabele:

[SQL] pobierz, plaintext 
CREATE TABLE konto
(
konto_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
login VARCHAR(20),
haslo VARCHAR(20)
) TYPE = InnoDB;
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE TABLE dane
(
dane_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
konto_id INT NOT NULL,
imie VARCHAR(20),
nazwisko VARCHAR(20),
CONSTRAINT FOREIGN KEY(konto_id) REFERENCES konto(konto_id)
) TYPE = InnoDB;
[SQL] pobierz, plaintext 

Żeby nikt się nie czepił, że brak INDEX to poźniej skasowałem te tabele i utowrzyłem jeszcze raz, tylko że druga wyglądała tak:

[SQL] pobierz, plaintext 
CREATE TABLE dane
(
dane_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
konto_id INT NOT NULL,
imie VARCHAR(20),
nazwisko VARCHAR(20),
INDEX(konto_id),
CONSTRAINT FOREIGN KEY(konto_id) REFERENCES konto(konto_id)
) TYPE = InnoDB;
[SQL] pobierz, plaintext 

Wypełniłem tabelę konto podając login i hasło. konto_id zostało nadane automatycznie.

Do tabeli dane wstawiłem tak:

[SQL] pobierz, plaintext 
INSERT dane(konto_id,imie,nazwisko) VALUE (1,'a','b');
[SQL] pobierz, plaintext 

No i wszystko ok. Probuję skasować wiersz z tabeli konto i dostaję komunikat że nie można bo wiersz ma wiersz podrzędny i klucz obcy nie pozwala.
Więc kasuje wiersz z tabeli dane i wiersz udaje się skasować. Ale z tego co pisałeś wcześniej, odpowiedni wiersz z tabeli konto powinien zostać skasowany automatycznie.. NIESTETY on nie jest kasowany... Więc jak to jest??


A co do INDEX to w książce napisane jest tak:
INDEX (lista kolumn) - tworzy indeks dla podanych kolumn. Opcja ta jest bardzo przydatna w przypadku dużej tabeli i wielu operacji wyszukiwania w kolumnie.Unikaj dodawania indeksów dla wszystkich kolumn tabeli. Dodanie indeksu zwiększa ilość przechowywanych informacji oraz znacznie zmniejsza wydajność operacji wstawiania i usuwania wierszy. Zwykle nie powinno się dodawać indeksów do tabel, jeśli nie występują problemy z wydajnością. Należy przejżeć swoją aplikację w celu wyszukania miejsc, w których dodanie indeksu da najlepszy efekt.

Ale typ powiazania ma byc CASCADE czyli.

[SQL] pobierz, plaintext 
CREATE TABLE dane
(
dane_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
konto_id INT NOT NULL,
imie VARCHAR(20),
nazwisko VARCHAR(20),
CONSTRAINT FOREIGN KEY(konto_id) REFERENCES konto(konto_id) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE = InnoDB;
[SQL] pobierz, plaintext 

Indeksy powinno sie stosowac gdzie dane powtarzaja sie mniej niz 20%

Dziękuje za pomoc


Zaraz sprawdze. Ale moim zdaniem lepsze będą transakcje. Bo przy takich rozwiązaniach, żęby usunąć login i hasło z tabeli konta, trzeba usuwać dane (imie, nazwisko, adres, itd) z drugiej tabeli żeby usunąć login i hasło. Moim zdaniem to jest trochę tak od "pupy strony". Nie przemyślane. Lepiej samemu wykonać 2x indstrukcje DELETE niż raz ale od tylca. Co Ty o tym myślisz?

PS. W MySQL>4.0 tablice InooDB są domyślnie włączone



Dziękuje za pomoc.

Działa

Ostatnie pytanie. Uruchamiam transakcje kasowanie wpsiu z tabeli konto (login, hasło) i drugie zapytanie kasujące z tabeli dane (kasuje odpowiednie informacje o usuwanym konci).
Jak sprawdzić i zrobić obsługę wyjątku:

Jeśli pierwsze zapytanie się nie uda to cofam transakcje i powiadamiam użytkownika.
Jeśli pierwsze się uda a drugie zapytanie się nie uda to cofam transakcje i powiadamiam użytkownika.
Jeśli pierwsze zapytanie się udało i drugie też to zapisuję transakcję..

No calkowicie sie zgadzam z tym, poprostu nie masz kontroli nad tym wszystkim. Albo usuniesz cos a tu poszly Ci inne rekordy ktorych nie chciales usunac.



NO wlasnie tak. Ewentualnie mozesz dodac przetwazanie bledow z bazy. Ale to juz zalezy od specyfikacji aplikacji i projektu.

Proste pytanka:

1. czy polecenie "FOR UPDATE" bedzie dzialalo jak trzeba (czyli zablokuje wiersz), jesli nie skorzystam z transakcji (czyli bedzie wiersz zablokowany do czasu update'u)?

2. jesli bedzie w transakcji, to blokada wiersza jest (o ile dobrze zrozumialem) do czasu wykonania zapytania COMMIT. Czy bedzie ona rowniez dzialac do czasu wykonania zapytania ROLLBACK lub do czasu, kiedy skrypt skonczy sie wykonywac (po tym czasie blokada "znika")?

ad. 1) Nie, zostanie zignorowane. Blokowanie wierszy działa w tranzakcjach InnoDB i...
ad. 2) ... FOR UPDATE zostanie zdjęty w momencie zatwierdzenia lub cofnięcia tranzakcji, jeśli otworzyłeś tranzakcję w skrypcie i zakończysz skrypt bez zatwierdzenia tranzakcji to również jest ona cofana i lock zdejmowany.

Więcej możesz przeczytać tutaj: http://dev.mysql.com/doc/refman/4.1/en/inn...lock-modes.html