Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
åäö in mysql problem
View unanswered posts
View posts from last 24 hours

Goto page Previous  1, 2, 3, 4, 5  Next  
Reply to topic    Gentoo Forums Forum Index Other Things Gentoo
View previous topic :: View next topic  
Author Message
SoylentGreen
l33t
l33t


Joined: 19 Aug 2005
Posts: 904
Location: The Hostel

PostPosted: Thu Apr 13, 2006 8:39 pm    Post subject: Reply with quote

sure on the forum. well, i was not able to. i got an "invalid_session" :twisted:
Back to top
View user's profile Send private message
napoleonb
Tux's lil' helper
Tux's lil' helper


Joined: 14 Jan 2004
Posts: 141

PostPosted: Thu Apr 13, 2006 8:47 pm    Post subject: Reply with quote

SoylentGreen wrote:
sure on the forum. well, i was not able to. i got an "invalid_session" :twisted:


Do you have "weird" characters in your user name?

One more thing you should check:

In httpd.conf, there's the AddDefaultCharset ISO-8859-1 directive which will force ISO-8859-1 on every pages on your server even if you change the charset in the meta tag (I found it out a while back). Check if it's there, comment it and restart Apache.
Back to top
View user's profile Send private message
EvilGenius
n00b
n00b


Joined: 27 Apr 2005
Posts: 18
Location: Stralsund / Germany

PostPosted: Thu Apr 13, 2006 9:14 pm    Post subject: Reply with quote

Well, going back did not help. Even with the previously backed-up database-files... Well, sh****
Back to top
View user's profile Send private message
llongi
Retired Dev
Retired Dev


Joined: 15 Apr 2004
Posts: 459
Location: Switzerland

PostPosted: Thu Apr 13, 2006 9:36 pm    Post subject: Reply with quote

Ok, fixed PHP versions are in Portage now, please emerge --sync in an hour or so. The fixed versions are dev-lang/php-4.4.2-r1 and dev-lang/php-5.1.2-r1, they are both in ~arch (unstable), so you'll have to do
Code:
echo dev-lang/php >> /etc/portage/package.keywords

Now those new versions do respect the charset you set under the [client] part in /etc/mysql/my.cnf, you can even create a [php-$SAPI] line and add then settings that will only be valid for PHP (where $SAPI can be cli, cgi, or apache2handler).

The upgrade path that should fix all your problems is quite simple:

1) emerge --sync

2) emerge the new PHP's and the lastest MySQL (-r1's), this time without
changing anything in the eclass or the ebuild

3) set the correct charset in /etc/mysql/my.cnf for your uses, if your server
uses latin1 in the [mysqld] section, simply add "default-character-set=latin1"
to the [client] section, and PHP should be back to displaying your characters
as it always was.

Have fun, and _please_ report back if this worked ok for you or not, thanks!
_________________
Best regards, Luca.
Back to top
View user's profile Send private message
llongi
Retired Dev
Retired Dev


Joined: 15 Apr 2004
Posts: 459
Location: Switzerland

PostPosted: Thu Apr 13, 2006 9:39 pm    Post subject: Reply with quote

SoylentGreen wrote:

hmm... though.. no GLSA - why?


Because the security team decided it's not serious enough to warrant a GLSA, not all security issues get GLSA's (see the bug for the vote of the security team on it in this case).

SoylentGreen wrote:

thanks for listening and for your effort!
keep up your good work <seriously meant!>


Sure, thanks! :)
_________________
Best regards, Luca.
Back to top
View user's profile Send private message
EvilGenius
n00b
n00b


Joined: 27 Apr 2005
Posts: 18
Location: Stralsund / Germany

PostPosted: Thu Apr 13, 2006 10:38 pm    Post subject: Reply with quote

THX!!!! Works great now!
Back to top
View user's profile Send private message
gipsy
n00b
n00b


Joined: 16 Nov 2003
Posts: 3

PostPosted: Thu Apr 13, 2006 11:05 pm    Post subject: Reply with quote

Puuuuuhhhh ... you saved my day (or better my night ... good night ...) :roll:
Back to top
View user's profile Send private message
llongi
Retired Dev
Retired Dev


Joined: 15 Apr 2004
Posts: 459
Location: Switzerland

PostPosted: Fri Apr 14, 2006 11:57 am    Post subject: Reply with quote

Glad to hear that it works now, I assume it does so for all the others too then. ;)

Btw, update: dev-php5/pecl-pdo-mysql-1.0.1-r1 is in the tree with this fix applied, for all the people out there using the "pdo-external" flag on dev-lang/php, and pecl-pdo-msyql. ;)
_________________
Best regards, Luca.
Back to top
View user's profile Send private message
SoylentGreen
l33t
l33t


Joined: 19 Aug 2005
Posts: 904
Location: The Hostel

PostPosted: Fri Apr 14, 2006 12:02 pm    Post subject: Reply with quote

CHTEKK wrote:
Glad to hear that it works now, I assume it does so for all the others too then. ;)

compiling right now, with fingers crossed :lol:

btw: 1139 views on this thread sofar. must ahve affected a bunch of us <g>
Back to top
View user's profile Send private message
SoylentGreen
l33t
l33t


Joined: 19 Aug 2005
Posts: 904
Location: The Hostel

PostPosted: Fri Apr 14, 2006 12:37 pm    Post subject: Reply with quote

CHTEKK wrote:

3) set the correct charset in /etc/mysql/my.cnf for your uses, if your server
uses latin1 in the [mysqld] section, simply add "default-character-set=latin1"
to the [client] section, and PHP should be back to displaying your characters
as it always was.

client section? you mean like this:
Code:

# The following options will be passed to all MySQL clients
[client]
#password                                       = your_password
port                                            = 3306
socket                                          = /var/run/mysqld/mysqld.sock
default-character-set=latin1

[mysql]

8O
thats not enough. every umlaut was displayed with a "?". however, i simply took my old "my.cnf" file (already posted here earyler) and everything looks OK now. thanks :P

guess the bugreport at bugs.gentoo.org could be closed now finally.

happy easter!
Back to top
View user's profile Send private message
SoylentGreen
l33t
l33t


Joined: 19 Aug 2005
Posts: 904
Location: The Hostel

PostPosted: Fri Apr 14, 2006 12:40 pm    Post subject: Reply with quote

argh, restarted apache2 after i already restarted mysql, and the umlautrs are gone :twisted:

wtf ??????

this is what i get in phpBB typing a message containing umlauts:
Code:

Could not insert new word matches

DEBUG MODE

INSERT INTO phpbb_search_wordmatch (post_id, word_id, title_match) SELECT 311, word_id, 0 FROM phpbb_search_wordlist WHERE word_text IN ('öäüß')

Line : 251
File : functions_search.php

:twisted: :twisted: :twisted: :twisted:

//edit: got it now. take your old my.cnf, and add "default-character-set=latin1" additionally(!!) to the client section. that works (phew)

for those who care: phpBB, coppermine and wordpress behave all fine. well done, devs!
Back to top
View user's profile Send private message
tkhobbes
Guru
Guru


Joined: 12 Nov 2004
Posts: 367
Location: Switzerland

PostPosted: Fri Apr 14, 2006 2:25 pm    Post subject: Reply with quote

I am really stucked here. I emerged the new dev-lang/php-5.1.2-r1, and here's the error from my nice little media-wiki (of course, this appears as web-page in some html-formatting):

Code:
Database error
From ZuhauseWiki
A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:

    (SQL query hidden)

from within function "MathRenderer::_recall". MySQL returned error "1267: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' (localhost)".


Now, here's the history:
Yesterday (?), a new version of mysql was emerged. After that, the my.cnf was suggested by etc-update to being updated. What it basically did was replacing all the "latin1"-stuff by some "utf-8". I was able to add some content to my mediawiki then, but as of today, I get the error message above.
As mentioned, the installation of the new php-version did not help.
Also, the error message above only appears for mediawiki-pages that have some <math>-contents on it, apparently.

This is my /etc/mysql/my.cnf:

Code:
[client]
#password                                       = your_password
port                                            = 3306
socket                                          = /var/run/mysqld/mysqld.sock

[mysql]
character-sets-dir=latin1
default-character-set=latin1

[mysqladmin]
character-sets-dir=latin1
default-character-set=latin1

[mysqlcheck]
character-sets-dir=latin1
default-character-set=latin1

[mysqldump]
character-sets-dir=latin1
default-character-set=latin1

[mysqlimport]
character-sets-dir=latin1
default-character-set=latin1

[mysqlshow]
character-sets-dir=latin1
default-character-set=latin1

[myisamchk]
character-sets-dir=latin1

[myisampack]
character-sets-dir=latin1

# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log                                         = /var/log/mysql/mysql.err

# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations.
[mysqld]
character-set-server            = latin1
default-character-set           = latin1
user                                            = mysql
...

_________________
My systems and some screenshots: http://www.hobbes.ch/techie/
My Gentoo client installation page: http://www.hobbes.ch/techie/gentoo-client/
My Gentoo Server installation: http://www.hobbes.ch/category/server
Back to top
View user's profile Send private message
llongi
Retired Dev
Retired Dev


Joined: 15 Apr 2004
Posts: 459
Location: Switzerland

PostPosted: Fri Apr 14, 2006 2:54 pm    Post subject: Reply with quote

Try adding "default-character-set=latin1" to the [client] section of your my.cnf, so that it looks like:

Code:

[client]
#password                                     = your_password
port                                               = 3306
socket                                           = /var/run/mysqld/mysqld.sock
default-character-set=latin1


Also, a script I made the other day to test what charsets are currently used, as seen by the PHP side of things is the following:

Code:

<?php
$link = mysql_connect('localhost', 'root', 'asdasd');
$charset = mysql_client_encoding($link);
echo "The current character set is: $charset\n";
echo "Get the charset data:\n";
$charsets = mysql_query('SHOW variables LIKE "%char%"');
while ($rowcharset = mysql_fetch_array($charsets, MYSQL_ASSOC)) {
        echo $rowcharset['Variable_name'] . " -_-_-  " . $rowcharset['Value'] . "\n";
}
echo "Get the collation data:\n";
$collations = mysql_query('SHOW variables LIKE "%colla%"');
while ($rowcollation = mysql_fetch_array($collations, MYSQL_ASSOC)) {
    echo $rowcollation['Variable_name'] . " -_-_-  " . $rowcollation['Value'] . "\n";
}
?>


You need to change "root" with a valid user and "asdasd" with a valid password for that user to connect to MySQL.
TO ALL: if you still experience problems after having set the correct values in my.cnf back to latin1, please post the output of this script too so it's easier to debug your problems, thanks!
_________________
Best regards, Luca.
Back to top
View user's profile Send private message
tkhobbes
Guru
Guru


Joined: 12 Nov 2004
Posts: 367
Location: Switzerland

PostPosted: Fri Apr 14, 2006 3:01 pm    Post subject: Reply with quote

ah, so that is what was meant in the previous post! ok, it's fixed now, thanks a lot - I spent the whole day on this problem... :)
_________________
My systems and some screenshots: http://www.hobbes.ch/techie/
My Gentoo client installation page: http://www.hobbes.ch/techie/gentoo-client/
My Gentoo Server installation: http://www.hobbes.ch/category/server
Back to top
View user's profile Send private message
Pissed
n00b
n00b


Joined: 16 Apr 2006
Posts: 1

PostPosted: Sun Apr 16, 2006 8:53 am    Post subject: Reply with quote

Yes, seems to be fixed for now, although this somehow broke my eaccelerator. Nevertheless shit like this just shouldn't be happening if this is meant to be a production quality distro.
Back to top
View user's profile Send private message
SoylentGreen
l33t
l33t


Joined: 19 Aug 2005
Posts: 904
Location: The Hostel

PostPosted: Sun Apr 16, 2006 12:33 pm    Post subject: Reply with quote

Pissed wrote:
Yes, seems to be fixed for now, although this somehow broke my eaccelerator. Nevertheless shit like this just shouldn't be happening if this is meant to be a production quality distro.

now come on.. thats why backups have been invented :lol:
Back to top
View user's profile Send private message
llongi
Retired Dev
Retired Dev


Joined: 15 Apr 2004
Posts: 459
Location: Switzerland

PostPosted: Sun Apr 16, 2006 1:34 pm    Post subject: Reply with quote

SoylentGreen wrote:
Pissed wrote:
Yes, seems to be fixed for now, although this somehow broke my eaccelerator. Nevertheless shit like this just shouldn't be happening if this is meant to be a production quality distro.

now come on.. thats why backups have been invented :lol:


Exactly, as well as test-systems, basic-production-system rules like "don't just upgrade the main software on which your system relies without having a rollback plan and without testing it first", and most importantly: sysadmins. :) It's what humans are here to do, if software would just upgrade and work "like that", why are we even here? ;)
_________________
Best regards, Luca.
Back to top
View user's profile Send private message
SoylentGreen
l33t
l33t


Joined: 19 Aug 2005
Posts: 904
Location: The Hostel

PostPosted: Sun Apr 16, 2006 1:50 pm    Post subject: Reply with quote

CHTEKK wrote:
if software would just upgrade and work "like that", why are we even here? ;)

because of july 28th?
http://www.sysadminday.com/

<SCNR>
Back to top
View user's profile Send private message
alejandrobiondo
n00b
n00b


Joined: 28 Mar 2006
Posts: 40

PostPosted: Tue Apr 18, 2006 12:34 am    Post subject: Reply with quote

I'm having the exact same problem but with Spanish character set (I don't speak swedish :-) )

But downgrading to the 4.1.14 does not solve my problem!!!

Does anybody has another suggestion?
Back to top
View user's profile Send private message
SoylentGreen
l33t
l33t


Joined: 19 Aug 2005
Posts: 904
Location: The Hostel

PostPosted: Tue Apr 18, 2006 10:51 am    Post subject: Reply with quote

a) take your old my.cnf (the one that used to work, and you hopefully backed up).
b) add ONE line to the CLIENT section:
default-character-set=latin1

instead of latin1, put in there what you have in all the other character-sets of your my.cnf

that does not work? and you updated php as well (you have to)?
Back to top
View user's profile Send private message
justinCOD
n00b
n00b


Joined: 08 Mar 2004
Posts: 73

PostPosted: Tue Apr 18, 2006 8:37 pm    Post subject: Reply with quote

I am having similiar problems with Drupal, not character related but when modifying a users account through Drupal. I get the error:

Code:
user error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='


I changed all the utf8 references in my.cnf back to latin1 and am currently rebuilding the -r1 php (already had the -r1 mysql).

My question was, my mysql variables look like this after changing the my.cnf:

Code:
mysql> show variables like 'colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+


Should it still be utf8 for "character_set_system"? All my databases are in the latin1 format btw too.

How will these my.cnf settings effect new databases that get created in the future with utf8? Or will new databases still get created in latin1 since I changed the settings back?

Thx.
Back to top
View user's profile Send private message
justinCOD
n00b
n00b


Joined: 08 Mar 2004
Posts: 73

PostPosted: Tue Apr 18, 2006 9:14 pm    Post subject: Reply with quote

After rebuild of php I am still broken. Output of your script:

Code:
The current character set is: utf8
Get the charset data:
character_set_client -_-_- utf8
character_set_connection -_-_- utf8
character_set_database -_-_- latin1
character_set_results -_-_- utf8
character_set_server -_-_- latin1
character_set_system -_-_- utf8
character_sets_dir -_-_- /usr/share/mysql/charsets/
Get the collation data:
collation_connection -_-_- utf8_general_ci
collation_database -_-_- latin1_swedish_ci
collation_server -_-_- latin1_swedish_ci


It appears php is just not getting the settings? As I look through my site I am noticing more stuff broken including character/image display.

My my.cnf:

Code:
# /etc/mysql/my.cnf: The global mysql configuration file.
# $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1-r1,v 1.3 2006/0
2/01 20:25:56 vivo Exp $

# The following options will be passed to all MySQL clients
[client]
#password                                       = your_password
port                                            = 3306
socket                                          = /var/run/mysqld/mysqld.sock
default-character-set=latin1

[mysql]
character-sets-dir=latin1
default-character-set=latin1

[mysqladmin]
character-sets-dir=latin1
default-character-set=latin1

[mysqlcheck]
character-sets-dir=latin1
default-character-set=latin1

[mysqldump]
character-sets-dir=latin1
default-character-set=latin1

[mysqlimport]
character-sets-dir=latin1
default-character-set=latin1

[mysqlshow]
character-sets-dir=latin1
default-character-set=latin1

[myisamchk]
character-sets-dir=latin1

[myisampack]
character-sets-dir=latin1

# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log                                         = /var/log/mysql/mysql.err

# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations.
[mysqld]
character-set-server            = latin1
default-character-set           = latin1
user                                            = mysql
port                                            = 3306
socket                                          = /var/run/mysqld/mysqld.sock
pid-file                                        = /var/run/mysqld/mysqld.pid
log-error                                       = /var/log/mysql/mysqld.err
basedir                                         = /usr
datadir                                         = /var/lib/mysql
skip-locking
#skip-innodb
key_buffer                                      = 16M
max_allowed_packet                      = 1M
table_cache                             = 64
sort_buffer_size                        = 512K
net_buffer_length                       = 8K
read_buffer_size                        = 256K
read_rnd_buffer_size            = 512K
myisam_sort_buffer_size         = 8M
language                                        = /usr/share/mysql/english

...cut for relevance...


The mysql output from my previous reply is still accurate. I must be missing something somewhere.

Justin
Back to top
View user's profile Send private message
justinCOD
n00b
n00b


Joined: 08 Mar 2004
Posts: 73

PostPosted: Wed Apr 19, 2006 12:53 am    Post subject: Reply with quote

Figured out what I did wrong. I forgot to restart apache and it was still using the 4.4.2 version and not 4.4.2-r1. Once I restarted apache my php websites are working fine.

I still have a question for issues going forward. Now that my "my.cnf" is setup to use latin1 again, what if I create a database using UTF8 collation? Will I have problems with that then? The different collations are confusing me I think.

Thanks.
Back to top
View user's profile Send private message
dodger10k
Tux's lil' helper
Tux's lil' helper


Joined: 27 Jun 2003
Posts: 97

PostPosted: Wed Apr 19, 2006 8:42 am    Post subject: Reply with quote

Hi all,

I updated my mysql & php but still I do have the exact problems with umlauts. I do have utf8 collation in the database, searched through this forum and followed everything that was posted (especially here), so currently there's php 4.4.2-r1 running with mysql 4.1.14-r1.

Still everything is totally messed up.

Here's the output of the php-debug-script posted here:

Quote:

The current character set is: utf8
Get the charset data: character_set_client -_-_- utf8
character_set_connection -_-_- utf8
character_set_database -_-_- latin1
character_set_results -_-_- utf8
character_set_server -_-_- latin1
character_set_system -_-_- utf8
character_sets_dir -_-_- /usr/share/mysql/charsets/
Get the collation data: collation_connection -_-_- utf8_general_ci
collation_database -_-_- latin1_swedish_ci
collation_server -_-_- latin1_swedish_ci


As this is a major problem, can please please please anyone help me out?

Thanks,

Oliver
Back to top
View user's profile Send private message
doc.twn
n00b
n00b


Joined: 20 Nov 2002
Posts: 15
Location: Luxembourg

PostPosted: Wed Apr 19, 2006 10:03 am    Post subject: Reply with quote

same here

updated all my servers to mysql-4.1.14-r1 and php-4.4.2-r1

debug script outputs this:

Code:

The current character set is: latin1
Get the charset data:
character_set_client -_-_-  latin1
character_set_connection -_-_-  latin1
character_set_database -_-_-  latin1
character_set_results -_-_-  latin1
character_set_server -_-_-  latin1
character_set_system -_-_-  utf8
character_sets_dir -_-_-  /usr/share/mysql/charsets/
Get the collation data:
collation_connection -_-_-  latin1_swedish_ci
collation_database -_-_-  latin1_swedish_ci
collation_server -_-_-  latin1_swedish_ci


but the special chars on my websites are still incorrect. all our hosting servers are broken, several hundered websites are crippled. gentoo updates are like a mine-field, you have to be extremely careful or you might just blow yourself up.

i tried all of the fixes i've found in this thread or on bugzilla, nothing fixed the problems.
_________________
Keyboard not found, Press F1 to continue...
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
Goto page Previous  1, 2, 3, 4, 5  Next
Page 3 of 5

 
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