Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
Out of the Mysql 4.1.14 mess
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Other Things Gentoo
View previous topic :: View next topic  
Author Message
arkepp
n00b
n00b


Joined: 23 Jul 2002
Posts: 74
Location: NYC / USA (or Norway)

PostPosted: Tue May 09, 2006 9:43 pm    Post subject: Out of the Mysql 4.1.14 mess Reply with quote

I'm still stuck with MySQL 4.1.14 after the whole mess with charset here and there. As far as I can understand, all my data is encoded in latin1.

Last night I tried dumping the entire database to a .sql file, upgrading to 4.1.19 (default configuration files -> utf8) and reading the SQL back in. That didn't work, I think I had the same issues as I had when upgrading straight to 4.1.19. I have also tried running 4.1.19 with latin1 in all the configuration settings, but that didn't do any good either (I assume I missed at least one).

So basically I want to get back to "default", i.e. run with standard configuration files and still be able to access my data. If utf8 is what we will be running from now on then i'd rather just convert everything. Can anyone suggest a procedure or a link?

Things I am wondering whether I have to change:
1) Change USE-flags?
2) Recompile PHP?
3) Run a conversion-script on the dumped SQL-file (suggestions?)
4) Change collation on all the tables?
Back to top
View user's profile Send private message
trupoet
Apprentice
Apprentice


Joined: 08 Aug 2002
Posts: 160

PostPosted: Fri May 12, 2006 11:36 pm    Post subject: Reply with quote

I dont know if this will fix everything but if you dump all your databases to a file and then cat it thru sed like so:

cat dumpfile.sql | sed 's/CHARSET=latin1/CHARSET=utf8/g' > newdumpfile.sql

Or the other way around if thats whats needed. Then upload the new dump file.

I myself have just stuck with MySQL 4.1.14 with no revision and that seems to work for now, uses the latin1 charset still.
Back to top
View user's profile Send private message
steveb
Advocate
Advocate


Joined: 18 Sep 2002
Posts: 4564

PostPosted: Sat May 13, 2006 1:18 am    Post subject: Reply with quote

I use this self made bash script to change the characterset and collation to utf8:
Code:
#!/bin/bash
[[ "$1" == "debug" ]] && _do_debug="1" || _do_debug="0"
mysql_user="root";
mysql_password="xxxxxxxxxxxxx";
mysql_server="localhost";
mysql_binary="/usr/bin/mysql";
mysql_character="utf8";
mysql_collate="utf8_general_ci";
mysql_exclude_dbs="information_schema mysql";

${mysql_binary} --host=${mysql_server} --user=${mysql_user} --password=${mysql_password} --silent --skip-column-names --batch -e "SHOW DATABASES;" | while read foo
do
        _do_process="1";
        for bar in ${mysql_exclude_dbs};
        do
                if [[ "${foo}" == "${bar}" ]];
                then
                        _do_process="0";
                        break;
                fi;
        done;

        if [[ ${_do_process} == "1" ]];
        then
                _sql_command="ALTER DATABASE \`${foo}\` DEFAULT CHARACTER SET ${mysql_character} COLLATE ${mysql_collate};";
                echo ${_sql_command};
                [[ "${_do_debug}" == "0" ]] && ${mysql_binary} --host=${mysql_server} --batch --user=${mysql_user} --password=${mysql_password} -e "${_sql_command}";
                ${mysql_binary} --host=${mysql_server} --user=${mysql_user} --password=${mysql_password} --silent --skip-column-names --batch -e "USE ${foo};SHOW TABLES;" | while read bar
                do
                        _sql_command="USE \`${foo}\`;ALTER TABLE \`${bar}\` CONVERT TO CHARACTER SET ${mysql_character} COLLATE ${mysql_collate};";
                        echo ${_sql_command};
                        [[ "${_do_debug}" == "0" ]] && ${mysql_binary} --host=${mysql_server} --batch --user=${mysql_user} --password=${mysql_password} -e "${_sql_command}";
                done;
        fi;
done;


If you want to see what the script is doing, then execute it with "debug" as parameter. It will then show all the commands issued against MySQL but not execute them.


cheers

SteveB
Back to top
View user's profile Send private message
llongi
Retired Dev
Retired Dev


Joined: 15 Apr 2004
Posts: 459
Location: Switzerland

PostPosted: Sat May 13, 2006 10:07 am    Post subject: Reply with quote

Btw, you can now safely update to the newer MySQL versions, if you want to keep latin1 as default-charset, just make sure that the "latin1" USE flag is enabled for dev-db/mysql when emerging, and you will have a MySQL with the same "charset stuff" like 4.1.14 was.
_________________
Best regards, Luca.
Back to top
View user's profile Send private message
arkepp
n00b
n00b


Joined: 23 Jul 2002
Posts: 74
Location: NYC / USA (or Norway)

PostPosted: Sun May 14, 2006 3:37 am    Post subject: Reply with quote

Thank you all for replying :) I'll bump the old thread in case anyone there is still stuck.

I'll try the USE-flag for now and then clean up when I go to 5.x.

4.1.14 hangs occasionally here (runs a pretty big website with phpBB). Worked fine before 4.1.14 so it'll probably be nice to be able to move on.
Back to top
View user's profile Send private message
trossachs
Veteran
Veteran


Joined: 22 Jan 2004
Posts: 1204
Location: London

PostPosted: Mon Nov 06, 2006 9:24 pm    Post subject: Reply with quote

Just a question: I'm currently using Mysql v4.1.14 and feel that the time has now come to upgrade to v5.0.26-r1. Is there any good howto I can use as such a jump in versions is bound to cause me serious issues? Or is this not the case?
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Other Things Gentoo All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum