Probleme bei Wechsel von MySQL 4 auf MySQL 5

[kraven]

Hi,

ich dachte mir, es könnte mal nicht schaden, die Datenbank meines Blogs auf MySQL 5 zu wechseln. Das Blog läuft aktuell mit S9Y 1.7.3, PHP 5.4.9 und eben MySQL 4.1.22. Für den DB-Export und -Import habe ich mich am S9Y Handbuch S. 450 - 456 orientiert. Nach dem ersten Import in eine neu angelegte MySQL 5.5.28 DB kam folgende Meldung:

Error

SQL query:

--
-- Database: `db_alt`
--
-- --------------------------------------------------------
--
-- Table structure for table `s_access`
--
CREATE TABLE IF NOT EXISTS `s_access` (
`groupid` int( 10 ) unsigned NOT NULL default '0',
`artifact_id` int( 10 ) unsigned NOT NULL default '0',
`artifact_type` varchar( 64 ) NOT NULL default '',
`artifact_mode` varchar( 64 ) NOT NULL default '',
`artifact_index` varchar( 64 ) NOT NULL default '',
KEY `accessgroup_idx` ( `groupid` ) ,
KEY `accessgroupT_idx` ( `artifact_id` , `artifact_type` , `artifact_mode` ) ,
KEY `accessforeign_idx` ( `artifact_id` )
) TYPE = MYISAM ;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyISAM' at line 10

Dann habe ich irgendwo gelesen, dass es nicht mehr TYPE, sondern ENGINE heißt. Nach entsprechender Änderung der Inhalte im vi und dem nächsten Import (1x mit MYSQL4 Kompatibilitätsmodus und 1x mit NONE) kam diese Meldung und von den 29 Tabellen waren 16 importiert worden:

Error

SQL query:

-- --------------------------------------------------------
--
-- Table structure for table `s_permalinks`
--
CREATE TABLE IF NOT EXISTS `s_permalinks` (
`permalink` varchar( 255 ) NOT NULL default '',
`entry_id` int( 10 ) unsigned NOT NULL default '0',
`type` varchar( 200 ) NOT NULL default '',
`data` text,
KEY `pl_idx` ( `permalink` ) ,
KEY `ple_idx` ( `entry_id` ) ,
KEY `plt_idx` ( `type` ) ,
KEY `plcomb_idx` ( `permalink` , `type` )
) ENGINE = MYISAM ;

MySQL said: Documentation
#1071 - Specified key was too long; max key length is 1000 bytes

Wäre nett, wenn mir jemand mitteilen könnte, was ich besser machen könnte oder was ich nun zu tun habe.

Thx
Kai

[Timbalu]

Interessant ist, dass du mit 1.7 vorher schon auf mysql4 warst, ohne Probleme(?).
Hast du das lösen können?
Ansonsten fiele mir noch http://board.s9y.org/viewtopic.php?f=10&t=18151 ein [Lösung inside] und dass man sich eventuell ein neues test Blog in die mysql 5 DB installiert, dessen Tabellen Strukturen kopiert und mit den alten Values für dein upzudatendes Blog auffüllt. Das birgt aber Risiken, wenn irgendetwas in der 4'er DB im Zuge der Serendipity upgrades nicht richtig upgedated wurde.

[garvinhicking]

Hi!

Das Hauptproblem ist, dass Du einen SQL-Dump in einem MySQL4 Format hast, und das teilweise in MySQL5 nicht problemlos importiert.

Die SQL-Statements:

Code: Select all

KEY `pl_idx` ( `permalink` ) ,
KEY `ple_idx` ( `entry_id` ) ,
KEY `plt_idx` ( `type` ) ,
KEY `plcomb_idx` ( `permalink` , `type` )

müssen für MySQL5 mit UTF-8 Tabellen so geändert werden, dass die Summe der Länge aller Datentypen weniger ist als 333.

Um das zu tun, geht man so vor - man schaut, wie lang der jeweilige KEY der genannten Spalte ist:

permalink: varchar(255)
type: varchar(200)
entry_id: int(10)

Die obigen statements definieren jeweils einen Key für:

permalink: 255 Zeichen (geht klar)
entry_id: 10 Zeichen (geht klar)
type: 200 Zeichen (geht klar)

... und dann kommt das Problemkind:

permalink & type: 255+200 = 400 --> mehr als 333, geht nicht klar.

Jetzt muss man für dieses Statement das SQL so anpassen:

Code: Select all

KEY `plcomb_idx` ( `permalink`(255) , `type`(78) )

Das ergibt in der Summe 333. Jetzt kann man nicht pauschal sagen welche Zeilen man wo wie einsetzt, das ist etwas "Erfahrungs" bzw. Glückssache. Die Länge benutzt MySQL nämlich nur für die Indizierung der Datenbankinhalte, um Daten schneller zu finden.

Wenn in einem Feld (z.b. Type - eigentlich 200 ZEichen möglich) nun die Inidzierung uaf 78 Zeichen verkürzt, heißt das, dass alles was nach dem 78 Zeichen kommt, von MySQL bei einer Datenbankabfrage gezielt angeguckt werden muss.

Dafür mal ein simples Beispiel.

Stell Dir vor, wir haben eine Datenbanktabelle in der steht in der Spalte "Namen" ein varchar(20) und wir haben folgende Namen:

Code: Select all

Schneider
Schneider-Müller-Landfeld
Schnorrmann
Schneider-Müller-Landsky
Hicking

Wenn wir jetzt einen Index auf name(20) setzen (also ohne Einschränkung der Feldlänge) und eine Datenbankabfrage machen:

Code: Select all

SELECT * FROM Namen where Name LIKE 'Schneider%'

dann kann MYSQL dank des Indexes sofort sagen, dass dalle Namen ausser "Schnorrmann" zutreffen, und das geht sehr schnell weil es nur in den Index gucken muss und nicht in die "echte" Datenbank.

Verändern wir den Fall jetzt so, dass der Index auf name(4) liegt, dann würde ein Index der Namen so aussehen:

Code: Select all

Schn
Schn
Schn
Schn
Hick

Wenn ich also danach dieselbe Datenbankabfrage nach "Schneider%" ausführe, muss MySQL nicht nur in den Index gucken, sondern auch in alle Datenbankeinträge der Felder, wo der Schlüssel schon "Schn" zurückgibt (in diesem Fall also alle Datensätze ausser den "Hick").

Bei der üblichen Größe von Blogdatenbanken ist das jetzt allerdings kein wirkliches Problem - selbst wenn MySQL aufgrund eines verkürzten Indexes 1000 Datenbankzeilen analysieren muss geht das immer noch in Milisekundenbruchteilen vonstatten.

Daher macht man so nichts wirklich schlimmes falsch, wenn man den SQL-Dump so bearbeitet und einfach beliebige Zahlen in den Indexen einträgt, hauptsache die Summe liegt unter 333.

Mein Vorgehen bei sowas ist immer, ich schaue dass ich die höchsten Zahlen auf die Datenbankfelder verteile, bei denen ich die längsten Angaben erwarte, und ich kürze die Zahlen dort, wo ich wenig erwartet ("Typ" hat meistens nur wenige Felder - die Datenbankspalte ist einfach für zukunftkompatibilität großzügiger ausgelegt).

Für die, die Fragen: MySQL sagt ja "maximal 1000 Zeichen", waurm trage ich 333 ein? Der Grund liegt darin, dass in der UTF-8 Art der Datenspeicherung MySQL für jedes Einzelzeichen 3 Byte verbraucht - 1000 / 3 = 333 (abgerundet).

Viele Grüße,
Garvin

[kraven]

Interessant ist, dass du mit 1.7 vorher schon auf mysql4 warst, ohne Probleme(?). Hast du das lösen können?

Ja, ich habe seit 2003 immer schön brav die S9Y Updates mit der MySQL4 DB eingespielt und damit gab es auch mit der 1.7.3 keine Probleme.

Ansonsten fiele mir noch http://board.s9y.org/viewtopic.php?f=10&t=18151 ein

Danke an Dich und @Garvin für Eure Hinweise und erhellenden Erklärungen. Mit der Änderung des Statements für plcomb_idx in der sql-Datei ging dann der Import ohne Probleme über die Bühne Ich hatte mir vorher nach Garvins Ergänzung vorsichtshalber nochmal alle Tabellen angeschaut, aber da war sonst nichts zu finden.

Ciao
Kai