MySQL

Es gibt 22 Beiträge in MySQL.

Doppelte Einträge in MySQL Tabelle finden

Ich denke der Titel verrät bereits um was es in diesem Artikel geht. Ausgangspunkt ist folgende Tabelle.

CREATE TABLE `xyz` (
    `a` VARCHAR(50) NOT NULL,
    `b` INT(10) NOT NULL DEFAULT '0',
    `c` DATETIME NOT NULL
);

Nun soll die Spalte b einen UNIQUE Key bekommen. Das Problem ist aber, das es in dieser Spalte bereits viele Dopplungen gibt. Um sie zu finden nehme ich folgende Abfrage.

SELECT `b` FROM `xyz` WHERE `b` 
IN (
   SELECT `b` 
   FROM `xyz` 
   GROUP BY `b` 
   HAVING Count( 1 ) >1
);

Die Abfrage zeigt mir jetzt alle Einträge der Spalte b die mehr als einmal vorkommen. Jetzt kann man die Einträge entweder manuell bereinigen oder die SELECT Abfrage in eine DELETE Abfrage umbauen. Fertig.

HTML Tag mit SQL entfernen

Die Verwaltung von Datenbankinhalten kann unter Umständen recht aufwändig werden. Gerade wenn man, wie in meinem aktuellen Fall, aus ca. 1,8 Mio Datensätze (Text) HTML Tags entfernen will. Als ersten kam mir die Überlegung hierfür ein Script in PERL oder PHP zu schreiben. Da ich allerdings nicht alle Applikationen anpassen will und kann, musste die Funktion vom MySQL übernommen werden.

Dank Google habe ich hier eine vorgefertigte Funktion für MySQL gefunden.

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER |
CREATE FUNCTION fnStripTags( Dirty varchar(4000) )
RETURNS varchar(4000)
DETERMINISTIC 
BEGIN
  DECLARE iStart, iEnd, iLength int;
  WHILE Locate( '<', Dirty ) > 0 And Locate( '>', Dirty, Locate( '<', Dirty )) > 0 DO
    BEGIN
      SET iStart = Locate( '<', Dirty ), iEnd = Locate( '>', Dirty, Locate('<', Dirty ));
      SET iLength = ( iEnd - iStart) + 1;
      IF iLength > 0 THEN
        BEGIN
          SET Dirty = Insert( Dirty, iStart, iLength, ' ');
        END;
      END IF;
    END;
  END WHILE;
  RETURN Dirty;
END;
|
DELIMITER ;

Im folgenden Beispiel wird der übergebene Text ohne HTML ausgegeben.

mysql> SELECT fnStripTags(„Testtext mit <b>fettem HTML Text</b>.“);

Datenbankgröße mit SQL ermitteln

Jeder der eine oder mehrere Datenbanken betreibt, sollte neben der Performance auch die belegten und benötigten Ressourcen im Blick haben. Mit einer einfachen Abfrage kann man sich direkt vom MySQL Server die Speicherbelegung anzeigen lassen.

SELECT 
  table_schema AS 'Datenbankname',
  Round( SUM( data_length ) / 1024 / 1024, 3 ) AS 'Daten (MB)',
  Round( SUM( index_length ) / 1024 / 1024, 3 ) AS 'Index (MB)',
  Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Gesamt (MB)',
  Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Freier Speicher (MB)'
FROM information_schema.tables
GROUP BY table_schema ;

Das Resultat sieht dann in etwa so aus …

+--------------------+------------+------------+-------------+----------------------+
| Datenbankname      | Daten (MB) | Index (MB) | Gesamt (MB) | Freier Speicher (MB) |
+--------------------+------------+------------+-------------+----------------------+
| shopdb             |   2457.729 |   1218.272 |    3676.002 |                4.000 |
| testdb             |      0.460 |      0.324 |       0.784 |                0.000 |
| information_schema |      0.000 |      0.008 |       0.008 |                0.000 |
| logging            |   1239.394 |    702.597 |    1941.991 |                0.000 |
| mysql              |      0.492 |      0.087 |       0.579 |                0.000 |
+--------------------+------------+------------+-------------+----------------------+
5 rows in set (0.00 sec)

(Die Abfrage stammt von http://www.artfulsoftware.com/infotree/queries.php und wurde von mir leicht angepasst.)

Fehlerhafte Query bei MySQL Replikation überspringen

Replikationen können nur fehlerfrei arbeiten, wenn auch die zugreifenden Applikationen auf die Replikation abgestimmt sind. Ist das nicht der Fall wird die Replikation mit einem Fehler unterbrochen.

Mit der Anweisung SQL_SLAVE_SKIP_COUNTER hat man die Möglichkeit, die nächsten N Ereignisse auf dem Master zu überspringen. Anschließend muss der Slave-Thread neu gestartet werden.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Wenn die Replikation nun wieder ohne Fehler läuft, sollte man die Konsistenz der Datenbankreplikation prüfen um evtl. Lücken aufzudecken.

Hinweis: Die Anweisung funktioniert nur, wenn der Slave-Thread nicht läuft.

Weitere Informationen zum Thema:
http://dev.mysql.com/doc/refman/5.1/de/set-global-sql-slave-skip-counter.html

MySQL Dump Importfortschritt anzeigen

Der Titel klingt ein wenig holprig, aber es geht hier um eine wirklich nette Funktion, die sich bestimmt jeder irgendwann schon einmal gewünscht hat. Eine „Fortschrittsanzeige“ für das Einspielen von MySQL Dumps (siehe Screenshot).

Fortschrittsanzeige bei MySQL Dump Import

Hierfür wird ein kleines Programm namens bar benutzt, welches Fortschrittsanzeigen für Dateitransfers generiert. Auf SourceForge kann man sich die aktuelle Version herunterladen. Ubuntu Nutzer (ab Natty/11.04 ) können auch die Paketquellen zur Installation verwenden:

$ sudo apt-get install bar

Ein simpler Import eines MySQL Dump würde dann in etwa so aussehen:

$ bar -if=/pfad/zum/dump.sql | mysql

Natürlich macht dieser Umweg nur Sinn wenn das Dump auch eine entsprechende Größe aufweist. ;-)

Hinweis: Die Angabe der Logindaten entfällt bei mir, weil ich eine ~/.my.cnf verwende.

Weitere Links zum Thema:
http://clpbar.sourceforge.net/
http://manpages.ubuntu.com/manpages/natty/en/man1/bar.1.html