Wenn man ein DELETE Statement braucht, in dem man einen JOIN auf auf die Tabelle benötigt, aus welcher man die Daten löschen möchte, gibt MySQL einen Fehler aus, dass man nicht auf die Tabelle joinen darf, aus welcher man löschen möchte.
Als Beispiel, doppelte Warengruppen aus der Wortmann Artikelpflege löschen:

DELETE
FROM P_WGR
WHERE EXISTS (
  SELECT sys_uid 
  FROM P_WGR AS DUP 
  WHERE P_WGR.hwgr = DUP.hwgr 
  AND P_WGR.wgrname = DUP.wgrname 
  AND P_WGR.sys_uid <> DUP.sys_uid 
  AND liefnr = '700758') 
ORDER BY hwgr;

So lässt sich dieses aber umgehen:

DELETE
FROM p_wgr 
USING p_wgr, p_wgr AS DUP
WHERE p_wgr.sys_uid > DUP.sys_uid 
AND p_wgr.hwgr = DUP.hwgr 
AND p_wgr.liefnr = '700758'

Tabellen kopieren bei InnoDB

Habe mal ein wenig im Internet dazu geschaut und fast überall wird gesagt, man soll Tabellen bei InnoDB lieber per Dump kopieren. Bei MyISAM stellt das Kopieren kein Problem dar, aber bei InnoDB ist es sehr riskant und kann zu Problemen führen. Das Problem ist wohl, dass in der ibdata der Tablespace gespeichert wird und dieser nicht mit kopiert werden kann. http://serverfault.com/questions/367255/linux-mysql-is-it-safe-to-copy-mysql-db-files-with-cp-command-from-one-db-to

Es gibt hier noch eine Anleitung wir man den Table Space manuell setzen kann, wofür aber einiges an manueller Arbeit pro Tabelle vorgenommen werden muss,
was sich vermutlich am ehesten für Back Recovery eignet. http://www.chriscalender.com/tag/innodb-error-tablespace-id-in-file/

Nachdem ich noch etwas weiter gelesen habe, habe ich herausgefunden , dass ab MySQL 5.6.6 wohl eine Möglichkeit besteht den Tablespace aus der Ibdata zu extrahieren: https://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html

Hierfür muss jedoch pro Tabelle der Befehl FLUSH TABLES tabellenname FOR EXPORT; durchgeführt werden. Dadurch wird eine cfg Datei angelegt, die dann mit kopiert werden muss. Allerdings wird die Tabelle durch den Befehl gelockt und muss, nachdem sie kopiert wurde, per Befehl UNLOCK TABLES; wieder freigegeben werden, was eine Anwendung im laufenden Betrieb sehr unpraktikabel macht.

Table Engine zwischen MyISAM und InnoDB wechseln

Folgender SQL Befehl generiert für eine bestimmte Datenbank eine Reihe von SQL Befehlen, welche die Table Engine ändern. Diese können einfach in die Zwischenablage exportiert und anschließend in Heidi-SQL ausgeführt werden.

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=MYISAM;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = 'Datenbankname'
AND     `ENGINE` = 'InnoDB'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

MySQL Locks

Um in einer Datenbank aktive Locks anzeigen zu lassen kann in MySQL eine Protokollierung in der metadata_locks Tabelle aktiviert werden.

Aktivieren:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

Deaktivieren:

UPDATE performance_schema.setup_instruments SET ENABLED = 'NO', TIMED = 'NO'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

Es kann dann einfach auf die Tabelle zugegriffen werden:

SELECT * FROM performance_schema.metadata_locks;

MySQL Collation

MySQL Collation ändern

Um die Standard Collation des MySQL Server zu ändern muss diese über die MySQL Workbench konfiguriert werden:

Mit folgenden SQL Befehlen kann die Collation einer Datenbank bzw einer Tabelle geändert werden:

 ALTER DATABASE DBNAME COLLATE latin1_german1_ci;
 ALTER TABLE DBNAME.TABELLENAME COLLATE latin1_german1_ci;
 ALTER TABLE DBNAME.TABELLENAME MODIFY FELDNAME FELDTYP COLLATE latin1_german1_ci;

Die Änderung der Datenbanken und Tabellen muss einzeln vorgenommen werden. Zu diesem Zweck gibt es das Makro CollationChange.mac, was diese Änderung für alle Datenbanken und Tabellen vornimmt, die mit OBS_MYSQL beginnen.

Im OBS\Data\Debug Verzeichnis wird dann eine Protkolldatei dazu angelegt: CollationChange.txt

MySQL Processlist

Da der Befehl SHOW PROCESSLIST keine Filterung erlaubt, verwenden Sie ab der MySQL Version 5.1.7 diesen Befehl:

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY id;

oder diesen Befehl für alle Connections die nicht im Sleep-Modus sind:

SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time DESC, id;

oder diesen Befehl für alle nicht schlafenden Connections die über 2 Sekunden dauern:

SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' AND time >= 2 ORDER BY time DESC, id;

oder diesen Befehl für das vertikale Layout:

SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' AND time >= 2 ORDER BY time DESC, id \G

MySQL REGEXP

Hier sind Informationen zu Regular Expressions:
https://dev.mysql.com/doc/refman/5.7/en/regexp.html#operator_regexp

@@ Zeile 81: / Zeile 81: @@
+==MySQL Collation==
+====MySQL Collation ändern====
+Um die Standard Collation des MySQL Server zu ändern muss diese über die MySQL Workbench konfiguriert werden:<br/>
+[[Image:MySQLCollation.png|600px]]
+Mit folgenden SQL Befehlen kann die Collation einer Datenbank bzw einer Tabelle geändert werden:
+<source lang="SQL">
+ ALTER DATABASE DBNAME COLLATE latin1_german1_ci;
+ ALTER TABLE DBNAME.TABELLENAME COLLATE latin1_german1_ci;
+ ALTER TABLE DBNAME.TABELLENAME MODIFY FELDNAME FELDTYP COLLATE latin1_german1_ci;
+</source>
+Die Änderung der Datenbanken und Tabellen muss einzeln vorgenommen werden. Zu diesem Zweck gibt es das Makro '''CollationChange.mac''', was diese Änderung für alle Datenbanken und Tabellen vornimmt, die mit OBS_MYSQL beginnen.
+Im OBS\Data\Debug Verzeichnis wird dann eine Protkolldatei dazu angelegt: '''CollationChange.txt'''
+==MySQL Processlist==
+Da der Befehl SHOW PROCESSLIST keine Filterung erlaubt, verwenden Sie ab der MySQL Version 5.1.7 diesen Befehl:
+<source lang="SQL">
+SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY id;
+</source>
+oder diesen Befehl für alle Connections die nicht im Sleep-Modus sind:<br/>
+<source lang="SQL">
+SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time DESC, id;
+</source>
+oder diesen Befehl für alle nicht schlafenden Connections die über 2 Sekunden dauern:<br/>
+<source lang="SQL">
+SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' AND time >= 2 ORDER BY time DESC, id;
+</source>
+oder diesen Befehl für das vertikale Layout:<br/>
+<source lang="SQL">
+SELECT user, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' AND time >= 2 ORDER BY time DESC, id \G
+</source>
+==MySQL REGEXP==
+Hier sind Informationen zu Regular Expressions: <br/>
+https://dev.mysql.com/doc/refman/5.7/en/regexp.html#operator_regexp

OBS/Adminhilfe/MySQL Tipps: Unterschied zwischen den Versionen

Aktuelle Version vom 28. Januar 2020, 11:45 Uhr

Inhaltsverzeichnis

MySQL Tipps

MySQL DELETE Statements

Tabellen kopieren bei InnoDB

Table Engine zwischen MyISAM und InnoDB wechseln

MySQL Locks

MySQL Collation

MySQL Collation ändern

MySQL Processlist

MySQL REGEXP

Navigationsmenü

OBS/Adminhilfe/MySQL Tipps: Unterschied zwischen den Versionen

Aktuelle Version vom 28. Januar 2020, 11:45 Uhr

MySQL Tipps

MySQL DELETE Statements

Tabellen kopieren bei InnoDB

Table Engine zwischen MyISAM und InnoDB wechseln

MySQL Locks

MySQL Collation

MySQL Collation ändern

MySQL Processlist

MySQL REGEXP

Navigationsmenü

Suche