Kategorie: MySQL

Wenn man die Process List von MySQL sich ständig aktualisiert anzeigen lassen will, kann man den watch Lionux Befehl auf der Console dafür benutzen. Login, Host und Passwort müssen ggf. geändert werden:

watch -n 1 "mysql -u root --password='myPassword' -h localhost -s -e 'SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE 1 = 1;'"

Es ist sehr einfach möglich in MYSQL Variablen zu verwenden für MySQL Statements. Diese müssen definiert werden mit der SET Funktion und dem @-Prefix:

SET @a= 'a';

Und kann dann benutzt werden für die Dauer der DB-Session:

UPDATE my_table SET x = @a WHERE y = @a;

Wenn man loggen will, wer wann und welche Änderungen an einer Tabelle „tabelle1“ gemacht hat, sollte man sich eine Tabelle namens „tabelle1_history“ und automatisch per MySQL Trigger auf Update und Delete Ereignisse reagieren:

Hinweis: __XYZ__ Platzhalter sind zu ersetzen

Trigger auf Update Aktionen

CREATE DEFINER=`__USER__`@`%` TRIGGER `__DB__`.`__TRIGGER_NAME_DEL__` BEFORE DELETE ON `__DB__`.`__TABLE__` FOR EACH ROW BEGIN
    insert into
        __DB__.__TABLE_HISTORY__
        (
        dataset_id,
        my_data1
        my_data2
        created_on
        created_by
        )

    VALUES (
        OLD.id,
        OLD.my_data1
        OLD.my_data2
        OLD.created_on,
        OLD.created_by
  );

Trigger auf Delete Aktionen

CREATE DEFINER=`__USER__`@`%` TRIGGER `__DB__`.`__TRIGGER_NAME_UPDATE__` BEFORE UPDATE ON `__DB__`.`__TABLE__` FOR EACH ROW BEGIN
        insert into
        __DB__.__TABLE_HISTORY__
        (
        dataset_id,
        my_data1
        my_data2
        created_on
        created_by
        )

    VALUES (
        OLD.id,
        OLD.my_data1
        OLD.my_data2
        OLD.created_on,
        OLD.created_by
  );

Ein kniffeliges Problem ist letztens aufgetreten bei einer partitionierten MySQL Datenbank. Es kam immer wieder bei hoher Last zu Deadlock Meldungen seitens MySQL. Immer waren 2 Queries davon betroffen, die dieselbe Form hatten:

--Query1:
UPDATE myTable SET createdOn='2016-05-23 16:08:29', foo='y' WHERE id=1
--Query2:
UPDATE myTable SET createdOn='2016-05-23 16:08:29', foo='x' WHERE id=2

Im MySQL Error Log war ein solcher Eintrag zu finden:

mysql tables in use 1, locked 1
LOCK WAIT 106 lock struct(s), heap size 8400, 53 row lock(s)

Tip: Mit der Systemvariablen innodb_print_all_deadlocks (ab MySQL 5.5) kann man Deadlocks in InnoDB in das MySQL Error Log loggen und besser debuggen.

Eigentlich sollte es bei den beiden Queires nicht zu einem Deadlock kommen, da beide nur jeweils ein Row-Level Lock auf ihrer jeweilige Zeile haben sollten.

Dies ist auch der Fall, wenn es einen Index auf der Spalte id gibt. In diesem Fall allerdings war durch die Partitionierung der Primary (Unique) Index auf  2 Spalten gelegt worden (kombinierten Index): id und createdOn.

Dadurch, dass der Index auf beiden Soalten gelegt worden ist, kam es zu den dem Dealocks. Die Lösung war, das createdOn aus dem Update Statement zu entfernen, weil es an der Stelle auch keinen Sinn gemacht hat.

Wenn man bspw. eine Spalte inserted einfügen will und diese inital mit dem schon vorhandenen last_change Werten ausstatten will, kann man folgenden Code benutzen:

UPDATE table_name dt1, table_name dt2 SET dt1.inserted = dt2.last_change WHERE dt1.id = dt2.id

Beim Importieren großer Datenmengen unter Xampp stößt man über den Weg von phpMyAdmin schnell auf Grenzen der Skriptlaufzeit oder auf Performance-Grenzen:

Fatal error: Maximum execution time exceeded

Diese kann zwar angepasst werden, aber ein solcher Import kann Stunden dauern. Der Import über die Konsole ist um den Faktor 10 schneller. Statt einer Stunde benötigt man damit nur ein paar Minuten und hat keine Probleme mit der Skriptlaufzeit oder ähnlichem. Am schnellsten und unkompliziertesten ist ein Import über die MySQL-Konsole:

• einfach im Xampp Control Panel auf shell klicken und dann in der Konsole die folgenden Befehle eingeben:

mysql -u root -p

Passwort eingeben oder einfachmit ENTER bestätigen, wenn kein passwort festgelegt wurde. Root ist der Benutzer der Datenbank, kann abweichen. Dann die Datenbank auswählen, die importiert werden soll, ggf. vorher anlegen, wenn diese nicht existent.