/usr/portage

Updating Gentoo to MySQL 4.1 2

First of a clean method. But this is too boring. Not really, I was just too stupid to differ «–buildpkg» und «–buildpkgonly». Too stupid. If you’d done this, you should not run into any problems if you were not intelligent enough to specify the USE-flag «utf8» which enables one of the killer-features in MySQL 4.1: internal use of unicode, what a fine thing! Enabling this without backing up your databases leads to a shortening of all varchar-fields. So the only solution is to get the structure of the old databases from the backup, doing some scripting magic (see below) and alter all varchar-fields. But, you won’t regret your upgrade. MySQL 4.1 provides a lot of nice things, especially the abovely mentioned unicode-issues and, as another serious highlight: combined queries.

Script:
cat structure.sql | sed -e "s:^CREATE TABLE \(.*\) (:ALTER TABLE \1:g" -e 's:^ \([^ ]*\) \(.*\):CHANGE `\1` `\1` \2:g' -e "s:^) TYPE=MyISAM::g" | grep -v "CHANGE UNIQUE" | grep -v "PRIMARY KEY" | grep -v "CHANGE KEY" | grep -v "KEY KEY" | grep -v "^–" | grep -E "(varchar|;|USE|ALTER)" | tr "\n" "#" | sed -e "s:,#;:;##:g" -e "s:ALTER TABLE [a-Za-z0-9_-+] #;::g" | tr "#" "\n" | sed -e 's:^ALTER TABLE \(.*\):ALTER TABLE `\1`:g' > test.sql

Filed under , & two comments & one trackback

Trackbacks

Trackback specific URI for this entry

  1. Trackback from WEBLOG (Bernds Blog)
    posted on October 21st 2005, 10:57:55 pm Umlautprobleme

    Tja, hier hat’s jetzt wohl was mit den Umlauten zerbröselt… Ich erklär mir das damit, dass dirch unser MySQL-Datenbank-Update (4.0 auf 4.1) jetzt alles alte beibehalten hat, die neuen Einträge aber als UTF-8 speichert. D.h. man hat ne böse Mischu

Comments

  1. Yetzt opines:
    published on October 26th 2005, 03:41:36 pm *

    Do not forget the ‘char’ Fields, they also shrink.

    Reply

Add a Comment & let me know what you think