06-10-2008, 12:06 AM
|
|||
|
|||
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Marc wrote:
> Jerry Stuckle schrieb: >> Marc wrote: >>> Jerry Stuckle schrieb: >>>> Marc wrote: >>>>> Jerry Stuckle schrieb: >>>>>> Marc wrote: >>>>>>> Jerry Stuckle schrieb: >>>>>>>> Marc wrote: >>>>>>>>> alex schrieb: >>>>>>>>>> >>>>>>>>>> I made a simple script in PHP to dump data from tables which >>>>>>>>>> had non standard characters. But the resulting page is viewed >>>>>>>>>> correctly in iso-8859-1 encoding and not in UTF-8. >>>>>>>>>> >>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>> >>>>>>>>> Hello, >>>>>>>>> >>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I have read >>>>>>>>> all answers and followed the link reading them all carefully. I >>>>>>>>> suppose I understand the matter. >>>>>>>>> >>>>>>>>> I have apparently the same problem as Alex has. But my >>>>>>>>> connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>> >>>>>>>>> The problem accrued as I've moved from my webspace to a vServer. >>>>>>>>> >>>>>>>>> The old configuration shown by phpMyAdmin on MySQL 4.1.13 is: >>>>>>>>> >>>>>>>>> character set client utf8 >>>>>>>>> (Globaler Wert) latin1 >>>>>>>>> character set connection utf8 >>>>>>>>> (Globaler Wert) latin1 >>>>>>>>> character set database latin1 >>>>>>>>> character set results utf8 >>>>>>>>> (Globaler Wert) latin1 >>>>>>>>> character set server latin1 >>>>>>>>> character set system utf8 >>>>>>>>> collation connection utf8_general_ci >>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>> collation database latin1_swedish_ci >>>>>>>>> collation server latin1_swedish_ci >>>>>>>>> >>>>>>>>> The new configuration shown by phpMyAdmin on my new MySQL >>>>>>>>> 5.0.32 is exactly the same, the I set "SET NAMES latin1" via >>>>>>>>> mysql-shell. >>>>>>>>> >>>>>>>>> BUT: >>>>>>>>> >>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>> >>>>>>>>> character_set_client latin1 >>>>>>>>> character_set_connection latin1 >>>>>>>>> character_set_database latin1 >>>>>>>>> character_set_filesystem binary >>>>>>>>> character set results latin1 >>>>>>>>> character set server latin1 >>>>>>>>> character set system utf8 >>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>> collation database latin1_swedish_ci >>>>>>>>> collation server latin1_swedish_ci >>>>>>>>> >>>>>>>>> asking for the values by PHP gives: >>>>>>>>> >>>>>>>>> character_set_client utf8 >>>>>>>>> character_set_connection utf8 >>>>>>>>> character_set_database latin1 >>>>>>>>> character_set_filesystem binary >>>>>>>>> character_set_results utf8 >>>>>>>>> character_set_server latin1 >>>>>>>>> character_set_system utf8 >>>>>>>>> collation_connection utf8_general_ci >>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>> >>>>>>>>> I need the character_set_client and character_set_connection in >>>>>>>>> latin1. >>>>>>>>> >>>>>>>>> The HTTP header sends to the browser ISO-8859-15. That should >>>>>>>>> stay so, because about 2.000 html pages are coded in this. I >>>>>>>>> don't care for the DB contect, then I have it local in some >>>>>>>>> programm. My only wish is to establish a >>>>>>>>> working-encoding-connection between the PHP and DB. >>>>>>>>> >>>>>>>>> Can You please tell what exactly I have to do? Please, >>>>>>>>> understand I can't reprogramm the application, I even don't >>>>>>>>> want to, because it worked before. I'm quit sure it's not >>>>>>>>> correct setted up. >>>>>>>>> >>>>>>>>> Thanks a lot in advance!!! >>>>>>>>> >>>>>>>>> Regards >>>>>>>>> >>>>>>>>> Marc >>>>>>>>> >>>>>>>> >>>>>>>> Oops, I didn't notice you crossposted this to comp.databases.mysql. >>>>>>>> >>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>> database. The first question is - what charset is the data in >>>>>>>> the database in? And exactly what are these "non-standard" >>>>>>>> characters? >>>>>>>> >>>>>>> >>>>>>> Hello Jerry, >>>>>>> >>>>>>> sorry for crossposting. I think it's not only MYSQL problem, but >>>>>>> also PHP problem. >>>>>>> >>>>>>> I have German umlauts in there. I guess the old DB used latin1 >>>>>>> and the new one uses UTF-8 for internal corresponding. >>>>>>> >>>>>>> As I have found it, it acctually doesn't matter how the DB >>>>>>> corresponds internal, then it's able to encode by request sent by >>>>>>> client. And that's the point, I guess PHP doesn't say which >>>>>>> encoding it would like to use. Or maybe it's enough to tell DB >>>>>>> which encoding it should normally use talking to anything from >>>>>>> outside. >>>>>>> >>>>>>> Right? >>>>>>> >>>>>>> So, I'm sorry for crossposting but I guess it's a mysql-php-problem. >>>>>>> >>>>>>> Thanks a lot for Your responce. >>>>>>> >>>>>>> >>>>>>> Regards, >>>>>>> >>>>>>> Marc >>>>>>> >>>>>> >>>>>> Marc, >>>>>> >>>>>> No, it's my fault for not noticing the cross-posting in the first >>>>>> place. >>>>>> >>>>>> You probably now have a mismatch between your connection and your >>>>>> table. Besides the charset used by the table, you have a charset >>>>>> used by the connection. I suspect you have a mismatch here. After >>>>>> connecting, call: >>>>>> >>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>> >>>>>> This should also set your connection to latin1. >>>>>> >>>>> >>>>> Jerry, >>>>> >>>>> that's what I guess the problem is. >>>>> >>>>> I've tried to call the command at the mysql-shell... and got error >>>>> 1064 (42000). Should it be called via the php script perhaps? >>>>> That's not that comfortable way for me... >>>>> >>>>> Regards, >>>>> >>>>> Marc >>>>> >>>> >>>> This statement is connection specific. You need to call it after >>>> connecting, and every time after connecting. Calling it from the >>>> command line only sets it for that connection; it won't set it for >>>> your PHP script, since it uses a different connection. >>>> >>>> But your error message by itself is meaningless. What is the >>>> statement you're entering, and what is the entire error message you >>>> get? It works fine for me. >>>> >>> >>> >>> I have connected with my server via putty, have logged in. Then >>> logged in into mysql: >>> >>> # mysql --password=mypassword --user=myuser [enter] >>> >>> Welcome to the MySQL monitor. Commands end with ; or \g. >>> Your MySQL connection id is 246 >>> Server version: 5.0.32-Debian_7etch5-log Debian etch distribution >>> >>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>> >>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>> >>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte >>> Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES >>> 'latin1'")' in Zeile 1 >>> mysql> >>> >>> How can I change the connection permamently for php? Is it possible? >>> Without changing the scripts... >>> >> >> No, mysql_query() is a PHP function call, not a MySQL statement. What >> you need is just >> >> mysql>SET NAMES 'latin1'; >> >> The easiest way to change startup values is to go into MySQL >> Administrator, go to Service Control and click on the Configure >> Service tab. Then set the Localization section to the charset you wish. >> > > I have added this call to my php script. It didn't work! > > I use MySQL on Linux! So there is no Service Control... > > I don't understand it, the MySQL docs say the standard install comes > with latin1... But it seems to be not a rule... > > Maybe I should recompile MySQL?! > > Thanks a lot for Your hints! > Marc, Since you changed servers, the other possibility is the charset the web page itself is sent as. What is the character encoding your web server is sending for the page? -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== 
|
|
Today
|
||||
|
||||
Sponsored Links
|
|
06-10-2008, 12:37 AM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Jerry Stuckle schrieb:
> Marc wrote: >> Jerry Stuckle schrieb: >>> Marc wrote: >>>> Jerry Stuckle schrieb: >>>>> Marc wrote: >>>>>> Jerry Stuckle schrieb: >>>>>>> Marc wrote: >>>>>>>> Jerry Stuckle schrieb: >>>>>>>>> Marc wrote: >>>>>>>>>> alex schrieb: >>>>>>>>>>> >>>>>>>>>>> I made a simple script in PHP to dump data from tables which >>>>>>>>>>> had non standard characters. But the resulting page is viewed >>>>>>>>>>> correctly in iso-8859-1 encoding and not in UTF-8. >>>>>>>>>>> >>>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>>> >>>>>>>>>> Hello, >>>>>>>>>> >>>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I have read >>>>>>>>>> all answers and followed the link reading them all carefully. >>>>>>>>>> I suppose I understand the matter. >>>>>>>>>> >>>>>>>>>> I have apparently the same problem as Alex has. But my >>>>>>>>>> connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>>> >>>>>>>>>> The problem accrued as I've moved from my webspace to a vServer. >>>>>>>>>> >>>>>>>>>> The old configuration shown by phpMyAdmin on MySQL 4.1.13 is: >>>>>>>>>> >>>>>>>>>> character set client utf8 >>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>> character set connection utf8 >>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>> character set database latin1 >>>>>>>>>> character set results utf8 >>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>> character set server latin1 >>>>>>>>>> character set system utf8 >>>>>>>>>> collation connection utf8_general_ci >>>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>> >>>>>>>>>> The new configuration shown by phpMyAdmin on my new MySQL >>>>>>>>>> 5.0.32 is exactly the same, the I set "SET NAMES latin1" via >>>>>>>>>> mysql-shell. >>>>>>>>>> >>>>>>>>>> BUT: >>>>>>>>>> >>>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>>> >>>>>>>>>> character_set_client latin1 >>>>>>>>>> character_set_connection latin1 >>>>>>>>>> character_set_database latin1 >>>>>>>>>> character_set_filesystem binary >>>>>>>>>> character set results latin1 >>>>>>>>>> character set server latin1 >>>>>>>>>> character set system utf8 >>>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>> >>>>>>>>>> asking for the values by PHP gives: >>>>>>>>>> >>>>>>>>>> character_set_client utf8 >>>>>>>>>> character_set_connection utf8 >>>>>>>>>> character_set_database latin1 >>>>>>>>>> character_set_filesystem binary >>>>>>>>>> character_set_results utf8 >>>>>>>>>> character_set_server latin1 >>>>>>>>>> character_set_system utf8 >>>>>>>>>> collation_connection utf8_general_ci >>>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>>> >>>>>>>>>> I need the character_set_client and character_set_connection >>>>>>>>>> in latin1. >>>>>>>>>> >>>>>>>>>> The HTTP header sends to the browser ISO-8859-15. That should >>>>>>>>>> stay so, because about 2.000 html pages are coded in this. I >>>>>>>>>> don't care for the DB contect, then I have it local in some >>>>>>>>>> programm. My only wish is to establish a >>>>>>>>>> working-encoding-connection between the PHP and DB. >>>>>>>>>> >>>>>>>>>> Can You please tell what exactly I have to do? Please, >>>>>>>>>> understand I can't reprogramm the application, I even don't >>>>>>>>>> want to, because it worked before. I'm quit sure it's not >>>>>>>>>> correct setted up. >>>>>>>>>> >>>>>>>>>> Thanks a lot in advance!!! >>>>>>>>>> >>>>>>>>>> Regards >>>>>>>>>> >>>>>>>>>> Marc >>>>>>>>>> >>>>>>>>> >>>>>>>>> Oops, I didn't notice you crossposted this to >>>>>>>>> comp.databases.mysql. >>>>>>>>> >>>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>>> database. The first question is - what charset is the data in >>>>>>>>> the database in? And exactly what are these "non-standard" >>>>>>>>> characters? >>>>>>>>> >>>>>>>> >>>>>>>> Hello Jerry, >>>>>>>> >>>>>>>> sorry for crossposting. I think it's not only MYSQL problem, but >>>>>>>> also PHP problem. >>>>>>>> >>>>>>>> I have German umlauts in there. I guess the old DB used latin1 >>>>>>>> and the new one uses UTF-8 for internal corresponding. >>>>>>>> >>>>>>>> As I have found it, it acctually doesn't matter how the DB >>>>>>>> corresponds internal, then it's able to encode by request sent >>>>>>>> by client. And that's the point, I guess PHP doesn't say which >>>>>>>> encoding it would like to use. Or maybe it's enough to tell DB >>>>>>>> which encoding it should normally use talking to anything from >>>>>>>> outside. >>>>>>>> >>>>>>>> Right? >>>>>>>> >>>>>>>> So, I'm sorry for crossposting but I guess it's a >>>>>>>> mysql-php-problem. >>>>>>>> >>>>>>>> Thanks a lot for Your responce. >>>>>>>> >>>>>>>> >>>>>>>> Regards, >>>>>>>> >>>>>>>> Marc >>>>>>>> >>>>>>> >>>>>>> Marc, >>>>>>> >>>>>>> No, it's my fault for not noticing the cross-posting in the first >>>>>>> place. >>>>>>> >>>>>>> You probably now have a mismatch between your connection and your >>>>>>> table. Besides the charset used by the table, you have a >>>>>>> charset used by the connection. I suspect you have a mismatch >>>>>>> here. After connecting, call: >>>>>>> >>>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>>> >>>>>>> This should also set your connection to latin1. >>>>>>> >>>>>> >>>>>> Jerry, >>>>>> >>>>>> that's what I guess the problem is. >>>>>> >>>>>> I've tried to call the command at the mysql-shell... and got error >>>>>> 1064 (42000). Should it be called via the php script perhaps? >>>>>> That's not that comfortable way for me... >>>>>> >>>>>> Regards, >>>>>> >>>>>> Marc >>>>>> >>>>> >>>>> This statement is connection specific. You need to call it after >>>>> connecting, and every time after connecting. Calling it from the >>>>> command line only sets it for that connection; it won't set it for >>>>> your PHP script, since it uses a different connection. >>>>> >>>>> But your error message by itself is meaningless. What is the >>>>> statement you're entering, and what is the entire error message you >>>>> get? It works fine for me. >>>>> >>>> >>>> >>>> I have connected with my server via putty, have logged in. Then >>>> logged in into mysql: >>>> >>>> # mysql --password=mypassword --user=myuser [enter] >>>> >>>> Welcome to the MySQL monitor. Commands end with ; or \g. >>>> Your MySQL connection id is 246 >>>> Server version: 5.0.32-Debian_7etch5-log Debian etch distribution >>>> >>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>>> >>>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>>> >>>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte >>>> Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES >>>> 'latin1'")' in Zeile 1 >>>> mysql> >>>> >>>> How can I change the connection permamently for php? Is it possible? >>>> Without changing the scripts... >>>> >>> >>> No, mysql_query() is a PHP function call, not a MySQL statement. >>> What you need is just >>> >>> mysql>SET NAMES 'latin1'; >>> >>> The easiest way to change startup values is to go into MySQL >>> Administrator, go to Service Control and click on the Configure >>> Service tab. Then set the Localization section to the charset you wish. >>> >> >> I have added this call to my php script. It didn't work! >> >> I use MySQL on Linux! So there is no Service Control... >> >> I don't understand it, the MySQL docs say the standard install comes >> with latin1... But it seems to be not a rule... >> >> Maybe I should recompile MySQL?! >> >> Thanks a lot for Your hints! >> > > Marc, > > Since you changed servers, the other possibility is the charset the web > page itself is sent as. What is the character encoding your web server > is sending for the page? > At the beginning there was file 'charset' with AddDefaultCharset UTF-8 at my apache2 config dir. So I didn't get it I have this problem. But then I mentioned umlaut problems at the admin backend, so I've begun to look for the source of it. After deleting this file, I have find out, that DB data won't be shown correctly. The HTML pages have a HEADER with ISO-8859-15. I have set a new 'charset' with ISO encoding - no result.
|
|
06-10-2008, 12:55 AM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Marc wrote:
> Jerry Stuckle schrieb: >> Marc wrote: >>> Jerry Stuckle schrieb: >>>> Marc wrote: >>>>> Jerry Stuckle schrieb: >>>>>> Marc wrote: >>>>>>> Jerry Stuckle schrieb: >>>>>>>> Marc wrote: >>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>> Marc wrote: >>>>>>>>>>> alex schrieb: >>>>>>>>>>>> >>>>>>>>>>>> I made a simple script in PHP to dump data from tables which >>>>>>>>>>>> had non standard characters. But the resulting page is >>>>>>>>>>>> viewed correctly in iso-8859-1 encoding and not in UTF-8. >>>>>>>>>>>> >>>>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>>>> >>>>>>>>>>> Hello, >>>>>>>>>>> >>>>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I have read >>>>>>>>>>> all answers and followed the link reading them all carefully. >>>>>>>>>>> I suppose I understand the matter. >>>>>>>>>>> >>>>>>>>>>> I have apparently the same problem as Alex has. But my >>>>>>>>>>> connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>>>> >>>>>>>>>>> The problem accrued as I've moved from my webspace to a vServer. >>>>>>>>>>> >>>>>>>>>>> The old configuration shown by phpMyAdmin on MySQL 4.1.13 is: >>>>>>>>>>> >>>>>>>>>>> character set client utf8 >>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>> character set connection utf8 >>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>> character set database latin1 >>>>>>>>>>> character set results utf8 >>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>> character set server latin1 >>>>>>>>>>> character set system utf8 >>>>>>>>>>> collation connection utf8_general_ci >>>>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>> >>>>>>>>>>> The new configuration shown by phpMyAdmin on my new MySQL >>>>>>>>>>> 5.0.32 is exactly the same, the I set "SET NAMES latin1" via >>>>>>>>>>> mysql-shell. >>>>>>>>>>> >>>>>>>>>>> BUT: >>>>>>>>>>> >>>>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>>>> >>>>>>>>>>> character_set_client latin1 >>>>>>>>>>> character_set_connection latin1 >>>>>>>>>>> character_set_database latin1 >>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>> character set results latin1 >>>>>>>>>>> character set server latin1 >>>>>>>>>>> character set system utf8 >>>>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>> >>>>>>>>>>> asking for the values by PHP gives: >>>>>>>>>>> >>>>>>>>>>> character_set_client utf8 >>>>>>>>>>> character_set_connection utf8 >>>>>>>>>>> character_set_database latin1 >>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>> character_set_results utf8 >>>>>>>>>>> character_set_server latin1 >>>>>>>>>>> character_set_system utf8 >>>>>>>>>>> collation_connection utf8_general_ci >>>>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>>>> >>>>>>>>>>> I need the character_set_client and character_set_connection >>>>>>>>>>> in latin1. >>>>>>>>>>> >>>>>>>>>>> The HTTP header sends to the browser ISO-8859-15. That should >>>>>>>>>>> stay so, because about 2.000 html pages are coded in this. I >>>>>>>>>>> don't care for the DB contect, then I have it local in some >>>>>>>>>>> programm. My only wish is to establish a >>>>>>>>>>> working-encoding-connection between the PHP and DB. >>>>>>>>>>> >>>>>>>>>>> Can You please tell what exactly I have to do? Please, >>>>>>>>>>> understand I can't reprogramm the application, I even don't >>>>>>>>>>> want to, because it worked before. I'm quit sure it's not >>>>>>>>>>> correct setted up. >>>>>>>>>>> >>>>>>>>>>> Thanks a lot in advance!!! >>>>>>>>>>> >>>>>>>>>>> Regards >>>>>>>>>>> >>>>>>>>>>> Marc >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Oops, I didn't notice you crossposted this to >>>>>>>>>> comp.databases.mysql. >>>>>>>>>> >>>>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>>>> database. The first question is - what charset is the data in >>>>>>>>>> the database in? And exactly what are these "non-standard" >>>>>>>>>> characters? >>>>>>>>>> >>>>>>>>> >>>>>>>>> Hello Jerry, >>>>>>>>> >>>>>>>>> sorry for crossposting. I think it's not only MYSQL problem, >>>>>>>>> but also PHP problem. >>>>>>>>> >>>>>>>>> I have German umlauts in there. I guess the old DB used latin1 >>>>>>>>> and the new one uses UTF-8 for internal corresponding. >>>>>>>>> >>>>>>>>> As I have found it, it acctually doesn't matter how the DB >>>>>>>>> corresponds internal, then it's able to encode by request sent >>>>>>>>> by client. And that's the point, I guess PHP doesn't say which >>>>>>>>> encoding it would like to use. Or maybe it's enough to tell DB >>>>>>>>> which encoding it should normally use talking to anything from >>>>>>>>> outside. >>>>>>>>> >>>>>>>>> Right? >>>>>>>>> >>>>>>>>> So, I'm sorry for crossposting but I guess it's a >>>>>>>>> mysql-php-problem. >>>>>>>>> >>>>>>>>> Thanks a lot for Your responce. >>>>>>>>> >>>>>>>>> >>>>>>>>> Regards, >>>>>>>>> >>>>>>>>> Marc >>>>>>>>> >>>>>>>> >>>>>>>> Marc, >>>>>>>> >>>>>>>> No, it's my fault for not noticing the cross-posting in the >>>>>>>> first place. >>>>>>>> >>>>>>>> You probably now have a mismatch between your connection and >>>>>>>> your table. Besides the charset used by the table, you have a >>>>>>>> charset used by the connection. I suspect you have a mismatch >>>>>>>> here. After connecting, call: >>>>>>>> >>>>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>>>> >>>>>>>> This should also set your connection to latin1. >>>>>>>> >>>>>>> >>>>>>> Jerry, >>>>>>> >>>>>>> that's what I guess the problem is. >>>>>>> >>>>>>> I've tried to call the command at the mysql-shell... and got >>>>>>> error 1064 (42000). Should it be called via the php script >>>>>>> perhaps? That's not that comfortable way for me... >>>>>>> >>>>>>> Regards, >>>>>>> >>>>>>> Marc >>>>>>> >>>>>> >>>>>> This statement is connection specific. You need to call it after >>>>>> connecting, and every time after connecting. Calling it from the >>>>>> command line only sets it for that connection; it won't set it for >>>>>> your PHP script, since it uses a different connection. >>>>>> >>>>>> But your error message by itself is meaningless. What is the >>>>>> statement you're entering, and what is the entire error message >>>>>> you get? It works fine for me. >>>>>> >>>>> >>>>> >>>>> I have connected with my server via putty, have logged in. Then >>>>> logged in into mysql: >>>>> >>>>> # mysql --password=mypassword --user=myuser [enter] >>>>> >>>>> Welcome to the MySQL monitor. Commands end with ; or \g. >>>>> Your MySQL connection id is 246 >>>>> Server version: 5.0.32-Debian_7etch5-log Debian etch distribution >>>>> >>>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>>>> >>>>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>>>> >>>>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte >>>>> Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES >>>>> 'latin1'")' in Zeile 1 >>>>> mysql> >>>>> >>>>> How can I change the connection permamently for php? Is it >>>>> possible? Without changing the scripts... >>>>> >>>> >>>> No, mysql_query() is a PHP function call, not a MySQL statement. >>>> What you need is just >>>> >>>> mysql>SET NAMES 'latin1'; >>>> >>>> The easiest way to change startup values is to go into MySQL >>>> Administrator, go to Service Control and click on the Configure >>>> Service tab. Then set the Localization section to the charset you >>>> wish. >>>> >>> >>> I have added this call to my php script. It didn't work! >>> >>> I use MySQL on Linux! So there is no Service Control... >>> >>> I don't understand it, the MySQL docs say the standard install comes >>> with latin1... But it seems to be not a rule... >>> >>> Maybe I should recompile MySQL?! >>> >>> Thanks a lot for Your hints! >>> >> >> Marc, >> >> Since you changed servers, the other possibility is the charset the >> web page itself is sent as. What is the character encoding your web >> server is sending for the page? >> > > > At the beginning there was file 'charset' with AddDefaultCharset UTF-8 > at my apache2 config dir. So I didn't get it I have this problem. But > then I mentioned umlaut problems at the admin backend, so I've begun to > look for the source of it. After deleting this file, I have find out, > that DB data won't be shown correctly. > > The HTML pages have a HEADER with ISO-8859-15. > > I have set a new 'charset' with ISO encoding - no result. > OK, let's back up a bit. What was the charset you used on the previous system? Was it actually latin1? Or could it have been something else? Also, how did you move your data from one system to another? Finally - to set the charset permanently, put it in your my.cfg file under the [mysqld] section, i.e. character_set_client = latin1 -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
|
|
06-10-2008, 09:51 AM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
AnrDaemon schrieb:
> Greetings, Marc. > In reply to Your message dated Tuesday, June 10, 2008, 01:45:36, > >>>>>>> You have the answer, but apparently lost it in your explanation. I'll quote: >>>>>>> >>>>>>>> The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is >>>>>>>> exactly the same, the I set "SET NAMES latin1" via mysql-shell. >>>>>>> You must tell to server, what encoding you want, every time you connecting to >>>>>>> database. >>>>>>> Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need' >>>>>>> statement right after the moment you have connected to database in your script. >>>>>> thanks a lot for this advice. But it's not possible to change the whole >>>>>> project for this. That's why I would like to set it generally up. On the >>>>>> other side, I don't need any other encoding. >>>>> You lost the point. >>>>> It is not "changing the whole project", it is the action that project must do >>>>> once in only one script - one that establishing connection to database. >>>>> It is, literally, one line of code to add to whole your project, and it will >>>>> serve you to the end of times. >>>> that seems that easy, but it cannot be the cleanest way. I don't feel so >>>> comfortable about it. It has worked already. I just would like to set >>>> everything right up. >>> It has worked already just because server was configured to work that way. >>> It was a 4.0 server most likely, or server config have had an init_connect >>> option set. Your argument "it was worked already" pointless, because >>> environment have changed. >>> >>> You have only two ways to solve your problem. >>> 1. I think it is right way. >>> Adapt environment for your needs (by changing connection encoding every time >>> you connect to database, it is one line of code in one single file, or in few >>> files, if you are not using some sort of abstraction layer) - it will work as >>> long as you are using MySQL servers for your database. They may have any >>> settings on their own, but you'll have what you need in your script all the >>> time. >>> >>> 2. Hard and one that I not considering acceptable for myself. >>> Change your scripts to handle current database output encoding. It will strike >>> just about every line of your code, in simple words - it will require to >>> rewrite about half of your project. >>> >>>> I'm sending the data from some external DB by one php script and I'm >>>> trying to add this simple line to it... It's gonna take some time, I to >>>> new on this stuff. I'm gonna report. >>> It's simple as find-replace  >>> Locate the point in your (key word - your) script, where you are using >>> mysql_connect(). >>> Add >>> mysql_query('SET NAMES latin1'); >>> Right after calling mysql_connect(). >>> >>> It should work. >>> >>> If you are using some sort of abstraction layer, it may be a bit different, >>> but not harder. >>> > > >> Adding mysql_query didn't work, I mean it didn't change anything. The >> Umlauts are still wrong. > > You're stating your troubles without any confirmation. > Please show us few lines of your code where you have changed it, and explain, > how are you tested that it "didn't change anything". > > I think you have checked it with your other script, which have not been > changed, thus not showing you what you want. > >> I'm really fed up. It makes me sick. > > I have feeling of your anger, but that's not what makes man wise. > > I'm really sorry for giving my angery. I use xt commerce. There are only 2 files calling mysql_connect(). So I have done what You have told, I have three times added mysql_query... 1st: mysql_query('SET NAMES latin1'); 2nd: mysql_query('SET NAMES latin1',@link); 3rd: mysql_query('SET NAMES latin1',@@link); I'm attaching the original files. So I have done it like this: O R I G I N A L: function xtc_db_connect($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') { global $$link; if (USE_PCONNECT == 'true') { $$link = mysql_pconnect($server, $username, $password); } else { $$link = mysql_connect($server, $username, $password); } if ($$link) mysql_select_db($database); return $$link; } C H A N G E D: function xtc_db_connect($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') { global $$link; if (USE_PCONNECT == 'true') { $$link = mysql_pconnect($server, $username, $password); } else { $$link = mysql_connect($server, $username, $password); mysql_query('SET NAMES latin1'); } if ($$link) mysql_select_db($database); return $$link; } My can reach my site at: http://www.multitasking-berlin.de Going on any product you can't see the umlauts, but switching the browser to UTF-8 you can. The point is, I don't need that much the data, I have it offline and can upload it any time, but uploading works only via PHP and this way causes the umlauts problem... ********************* 1st file in original: ********************* <?php // include(DIR_WS_CLASSES.'/adodb/adodb.inc.php'); function xtc_db_connect($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') { global $$link; if (USE_PCONNECT == 'true') { $$link = mysql_pconnect($server, $username, $password); } else { $$link = mysql_connect($server, $username, $password); } if ($$link) mysql_select_db($database); return $$link; } ?> ********************* 2nd file in original: ********************* <?php defined( '_VALID_XTC' ) or die( 'Direct Access to this location is not allowed.' ); function xtc_db_connect($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') { global $$link; if (USE_PCONNECT == 'true') { $$link = mysql_pconnect($server, $username, $password); } else { $$link = mysql_connect($server, $username, $password); } if ($$link) mysql_select_db($database); return $$link; } // db connection for Servicedatabase function service_xtc_db_connect($server_service = SERVICE_DB_SERVER, $username_service = SERVICE_DB_SERVER_USERNAME, $password_service = SERVICE_DB_SERVER_PASSWORD, $database_service = SERVICE_DB_DATABASE, $link_service = 'db_link_service') { global $$link_service; if (SERVICE_USE_PCONNECT == 'true') { $$link_service = mysql_pconnect($server_service, $username_service, $password_service); } else { $$link_service = mysql_connect($server_service, $username_service, $password_service); } if ($$link_service) mysql_select_db($database_service); return $$link_service; } function xtc_db_close($link = 'db_link') { global $$link; return mysql_close($$link); } // db connection for Servicedatabase function service_xtc_db_close($link_service = 'db_link_service') { global $$link_service; return mysql_close($$link_service); } function xtc_db_error($query, $errno, $error) { die('<font color="#000000"><b>' . $errno . ' - ' . $error . '<br><br>' . $query . '<br><br><small><font color="#ff0000">[TEP STOP]</font></small><br><br></b></font>'); } function xtc_db_query($query, $link = 'db_link') { global $$link, $logger; if (STORE_DB_TRANSACTIONS == 'true') { if (!is_object($logger)) $logger = new logger; $logger->write($query, 'QUERY'); } $result = mysql_query($query, $$link) or xtc_db_error($query, mysql_errno(), mysql_error()); if (STORE_DB_TRANSACTIONS == 'true') { if (mysql_error()) $logger->write(mysql_error(), 'ERROR'); } return $result; } // db connection for Servicedatabase function service_xtc_db_query($query, $link_service = 'db_link_service') { global $$link_service, $logger_service; if (STORE_DB_TRANSACTIONS == 'true') { if (!is_object($logger_service)) $logger_service = new logger_service; $logger_service->write($query, 'QUERY'); } $result = mysql_query($query, $$link_service) or xtc_db_error($query, mysql_errno(), mysql_error()); if (STORE_DB_TRANSACTIONS == 'true') { if (mysql_error()) $logger_service->write(mysql_error(), 'ERROR'); } return $result; } function xtc_db_perform($table, $data, $action = 'insert', $parameters = '', $link = 'db_link') { reset($data); if ($action == 'insert') { $query = 'insert into ' . $table . ' ('; while (list($columns, ) = each($data)) { $query .= $columns . ', '; } $query = substr($query, 0, -2) . ') values ('; reset($data); while (list(, $value) = each($data)) { switch ((string)$value) { case 'now()': $query .= 'now(), '; break; case 'null': $query .= 'null, '; break; default: $query .= '\'' . xtc_db_input($value) . '\', '; break; } } $query = substr($query, 0, -2) . ')'; } elseif ($action == 'update') { $query = 'update ' . $table . ' set '; while (list($columns, $value) = each($data)) { switch ((string)$value) { case 'now()': $query .= $columns . ' = now(), '; break; case 'null': $query .= $columns .= ' = null, '; break; default: $query .= $columns . ' = \'' . xtc_db_input($value) . '\', '; break; } } $query = substr($query, 0, -2) . ' where ' . $parameters; } return xtc_db_query($query, $link); } function xtc_db_fetch_array($db_query) { return mysql_fetch_array($db_query, MYSQL_ASSOC); } function xtc_db_result($result, $row, $field = '') { return mysql_result($result, $row, $field); } function xtc_db_num_rows($db_query) { return mysql_num_rows($db_query); } function xtc_db_data_seek($db_query, $row_number) { return mysql_data_seek($db_query, $row_number); } function xtc_db_insert_id() { return mysql_insert_id(); } function xtc_db_free_result($db_query) { return mysql_free_result($db_query); } function xtc_db_fetch_fields($db_query) { return mysql_fetch_field($db_query); } function xtc_db_output($string) { return htmlspecialchars($string); } function xtc_db_input($string) { return addslashes($string); } function xtc_db_prepare_input($string) { if (is_string($string)) { return trim(stripslashes($string)); } elseif (is_array($string)) { reset($string); while (list($key, $value) = each($string)) { $string[$key] = xtc_db_prepare_input($value); } return $string; } else { return $string; } } ?>
|
|
06-10-2008, 10:31 AM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Jerry Stuckle schrieb:
> Marc wrote: >> Jerry Stuckle schrieb: >>> Marc wrote: >>>> Jerry Stuckle schrieb: >>>>> Marc wrote: >>>>>> Jerry Stuckle schrieb: >>>>>>> Marc wrote: >>>>>>>> Jerry Stuckle schrieb: >>>>>>>>> Marc wrote: >>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>> Marc wrote: >>>>>>>>>>>> alex schrieb: >>>>>>>>>>>>> >>>>>>>>>>>>> I made a simple script in PHP to dump data from tables >>>>>>>>>>>>> which had non standard characters. But the resulting page >>>>>>>>>>>>> is viewed correctly in iso-8859-1 encoding and not in UTF-8. >>>>>>>>>>>>> >>>>>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>>>>> >>>>>>>>>>>> Hello, >>>>>>>>>>>> >>>>>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I have read >>>>>>>>>>>> all answers and followed the link reading them all >>>>>>>>>>>> carefully. I suppose I understand the matter. >>>>>>>>>>>> >>>>>>>>>>>> I have apparently the same problem as Alex has. But my >>>>>>>>>>>> connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>>>>> >>>>>>>>>>>> The problem accrued as I've moved from my webspace to a >>>>>>>>>>>> vServer. >>>>>>>>>>>> >>>>>>>>>>>> The old configuration shown by phpMyAdmin on MySQL 4.1.13 is: >>>>>>>>>>>> >>>>>>>>>>>> character set client utf8 >>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>> character set connection utf8 >>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>> character set database latin1 >>>>>>>>>>>> character set results utf8 >>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>> character set server latin1 >>>>>>>>>>>> character set system utf8 >>>>>>>>>>>> collation connection utf8_general_ci >>>>>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>> >>>>>>>>>>>> The new configuration shown by phpMyAdmin on my new MySQL >>>>>>>>>>>> 5.0.32 is exactly the same, the I set "SET NAMES latin1" via >>>>>>>>>>>> mysql-shell. >>>>>>>>>>>> >>>>>>>>>>>> BUT: >>>>>>>>>>>> >>>>>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>>>>> >>>>>>>>>>>> character_set_client latin1 >>>>>>>>>>>> character_set_connection latin1 >>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>> character set results latin1 >>>>>>>>>>>> character set server latin1 >>>>>>>>>>>> character set system utf8 >>>>>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>> >>>>>>>>>>>> asking for the values by PHP gives: >>>>>>>>>>>> >>>>>>>>>>>> character_set_client utf8 >>>>>>>>>>>> character_set_connection utf8 >>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>> character_set_results utf8 >>>>>>>>>>>> character_set_server latin1 >>>>>>>>>>>> character_set_system utf8 >>>>>>>>>>>> collation_connection utf8_general_ci >>>>>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>>>>> >>>>>>>>>>>> I need the character_set_client and character_set_connection >>>>>>>>>>>> in latin1. >>>>>>>>>>>> >>>>>>>>>>>> The HTTP header sends to the browser ISO-8859-15. That >>>>>>>>>>>> should stay so, because about 2.000 html pages are coded in >>>>>>>>>>>> this. I don't care for the DB contect, then I have it local >>>>>>>>>>>> in some programm. My only wish is to establish a >>>>>>>>>>>> working-encoding-connection between the PHP and DB. >>>>>>>>>>>> >>>>>>>>>>>> Can You please tell what exactly I have to do? Please, >>>>>>>>>>>> understand I can't reprogramm the application, I even don't >>>>>>>>>>>> want to, because it worked before. I'm quit sure it's not >>>>>>>>>>>> correct setted up. >>>>>>>>>>>> >>>>>>>>>>>> Thanks a lot in advance!!! >>>>>>>>>>>> >>>>>>>>>>>> Regards >>>>>>>>>>>> >>>>>>>>>>>> Marc >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Oops, I didn't notice you crossposted this to >>>>>>>>>>> comp.databases.mysql. >>>>>>>>>>> >>>>>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>>>>> database. The first question is - what charset is the data >>>>>>>>>>> in the database in? And exactly what are these "non-standard" >>>>>>>>>>> characters? >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Hello Jerry, >>>>>>>>>> >>>>>>>>>> sorry for crossposting. I think it's not only MYSQL problem, >>>>>>>>>> but also PHP problem. >>>>>>>>>> >>>>>>>>>> I have German umlauts in there. I guess the old DB used latin1 >>>>>>>>>> and the new one uses UTF-8 for internal corresponding. >>>>>>>>>> >>>>>>>>>> As I have found it, it acctually doesn't matter how the DB >>>>>>>>>> corresponds internal, then it's able to encode by request sent >>>>>>>>>> by client. And that's the point, I guess PHP doesn't say which >>>>>>>>>> encoding it would like to use. Or maybe it's enough to tell DB >>>>>>>>>> which encoding it should normally use talking to anything from >>>>>>>>>> outside. >>>>>>>>>> >>>>>>>>>> Right? >>>>>>>>>> >>>>>>>>>> So, I'm sorry for crossposting but I guess it's a >>>>>>>>>> mysql-php-problem. >>>>>>>>>> >>>>>>>>>> Thanks a lot for Your responce. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Regards, >>>>>>>>>> >>>>>>>>>> Marc >>>>>>>>>> >>>>>>>>> >>>>>>>>> Marc, >>>>>>>>> >>>>>>>>> No, it's my fault for not noticing the cross-posting in the >>>>>>>>> first place. >>>>>>>>> >>>>>>>>> You probably now have a mismatch between your connection and >>>>>>>>> your table. Besides the charset used by the table, you have a >>>>>>>>> charset used by the connection. I suspect you have a mismatch >>>>>>>>> here. After connecting, call: >>>>>>>>> >>>>>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>>>>> >>>>>>>>> This should also set your connection to latin1. >>>>>>>>> >>>>>>>> >>>>>>>> Jerry, >>>>>>>> >>>>>>>> that's what I guess the problem is. >>>>>>>> >>>>>>>> I've tried to call the command at the mysql-shell... and got >>>>>>>> error 1064 (42000). Should it be called via the php script >>>>>>>> perhaps? That's not that comfortable way for me... >>>>>>>> >>>>>>>> Regards, >>>>>>>> >>>>>>>> Marc >>>>>>>> >>>>>>> >>>>>>> This statement is connection specific. You need to call it after >>>>>>> connecting, and every time after connecting. Calling it from the >>>>>>> command line only sets it for that connection; it won't set it >>>>>>> for your PHP script, since it uses a different connection. >>>>>>> >>>>>>> But your error message by itself is meaningless. What is the >>>>>>> statement you're entering, and what is the entire error message >>>>>>> you get? It works fine for me. >>>>>>> >>>>>> >>>>>> >>>>>> I have connected with my server via putty, have logged in. Then >>>>>> logged in into mysql: >>>>>> >>>>>> # mysql --password=mypassword --user=myuser [enter] >>>>>> >>>>>> Welcome to the MySQL monitor. Commands end with ; or \g. >>>>>> Your MySQL connection id is 246 >>>>>> Server version: 5.0.32-Debian_7etch5-log Debian etch distribution >>>>>> >>>>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>>>>> >>>>>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>>>>> >>>>>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte >>>>>> Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES >>>>>> 'latin1'")' in Zeile 1 >>>>>> mysql> >>>>>> >>>>>> How can I change the connection permamently for php? Is it >>>>>> possible? Without changing the scripts... >>>>>> >>>>> >>>>> No, mysql_query() is a PHP function call, not a MySQL statement. >>>>> What you need is just >>>>> >>>>> mysql>SET NAMES 'latin1'; >>>>> >>>>> The easiest way to change startup values is to go into MySQL >>>>> Administrator, go to Service Control and click on the Configure >>>>> Service tab. Then set the Localization section to the charset you >>>>> wish. >>>>> >>>> >>>> I have added this call to my php script. It didn't work! >>>> >>>> I use MySQL on Linux! So there is no Service Control... >>>> >>>> I don't understand it, the MySQL docs say the standard install comes >>>> with latin1... But it seems to be not a rule... >>>> >>>> Maybe I should recompile MySQL?! >>>> >>>> Thanks a lot for Your hints! >>>> >>> >>> Marc, >>> >>> Since you changed servers, the other possibility is the charset the >>> web page itself is sent as. What is the character encoding your web >>> server is sending for the page? >>> >> >> >> At the beginning there was file 'charset' with AddDefaultCharset UTF-8 >> at my apache2 config dir. So I didn't get it I have this problem. But >> then I mentioned umlaut problems at the admin backend, so I've begun >> to look for the source of it. After deleting this file, I have find >> out, that DB data won't be shown correctly. >> >> The HTML pages have a HEADER with ISO-8859-15. >> >> I have set a new 'charset' with ISO encoding - no result. >> > > OK, let's back up a bit. What was the charset you used on the previous > system? Was it actually latin1? Or could it have been something else? > > Also, how did you move your data from one system to another? > > Finally - to set the charset permanently, put it in your my.cfg file > under the [mysqld] section, i.e. > > character_set_client = latin1 > Well, to say the truth I have no idea what the previous system was set like. I can access it only via Plesk, and the MySQL via phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin I have posted already. If there is anything else I can find out, please tell me what and where. I made an export by phpMyAdmin and then called following on the mysql-shell at the new system: /usr/bin/mysql --host=localhost --password=password --user=user DBname < /home/user/DBname.sql Viewing the export file via SuperEdi all umlauts are correct and it seems all data is latin1-encoded, there is always this statement in there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables. Viewing the old DB all umlauts are OK, doing it at the new one, none is OK. I have tried to set the charset permanently, but the server says following after restart: Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning] /usr/sbin/mysqld: ignoring option '--character-set-client$ Jun 10 10:13:15 v213880518 mysqld[20563]: Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB: Started; log sequence number 0 2196292 Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note] /usr/sbin/mysqld: bereit für Verbindungen. Jun 10 10:13:16 v213880518 mysqld[20563]: Version: '5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock' Port: 3$ So, I habe no idea why it doesn't work.
|
|
06-10-2008, 10:36 AM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Marc wrote:
> Jerry Stuckle schrieb: >> Marc wrote: >>> Jerry Stuckle schrieb: >>>> Marc wrote: >>>>> Jerry Stuckle schrieb: >>>>>> Marc wrote: >>>>>>> Jerry Stuckle schrieb: >>>>>>>> Marc wrote: >>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>> Marc wrote: >>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>> alex schrieb: >>>>>>>>>>>>>> >>>>>>>>>>>>>> I made a simple script in PHP to dump data from tables >>>>>>>>>>>>>> which had non standard characters. But the resulting page >>>>>>>>>>>>>> is viewed correctly in iso-8859-1 encoding and not in UTF-8. >>>>>>>>>>>>>> >>>>>>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>>>>>> >>>>>>>>>>>>> Hello, >>>>>>>>>>>>> >>>>>>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I have >>>>>>>>>>>>> read all answers and followed the link reading them all >>>>>>>>>>>>> carefully. I suppose I understand the matter. >>>>>>>>>>>>> >>>>>>>>>>>>> I have apparently the same problem as Alex has. But my >>>>>>>>>>>>> connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>>>>>> >>>>>>>>>>>>> The problem accrued as I've moved from my webspace to a >>>>>>>>>>>>> vServer. >>>>>>>>>>>>> >>>>>>>>>>>>> The old configuration shown by phpMyAdmin on MySQL 4.1.13 is: >>>>>>>>>>>>> >>>>>>>>>>>>> character set client utf8 >>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>> character set connection utf8 >>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>> character set database latin1 >>>>>>>>>>>>> character set results utf8 >>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>> collation connection utf8_general_ci >>>>>>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>> >>>>>>>>>>>>> The new configuration shown by phpMyAdmin on my new MySQL >>>>>>>>>>>>> 5.0.32 is exactly the same, the I set "SET NAMES latin1" >>>>>>>>>>>>> via mysql-shell. >>>>>>>>>>>>> >>>>>>>>>>>>> BUT: >>>>>>>>>>>>> >>>>>>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>>>>>> >>>>>>>>>>>>> character_set_client latin1 >>>>>>>>>>>>> character_set_connection latin1 >>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>> character set results latin1 >>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>> >>>>>>>>>>>>> asking for the values by PHP gives: >>>>>>>>>>>>> >>>>>>>>>>>>> character_set_client utf8 >>>>>>>>>>>>> character_set_connection utf8 >>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>> character_set_results utf8 >>>>>>>>>>>>> character_set_server latin1 >>>>>>>>>>>>> character_set_system utf8 >>>>>>>>>>>>> collation_connection utf8_general_ci >>>>>>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>>>>>> >>>>>>>>>>>>> I need the character_set_client and >>>>>>>>>>>>> character_set_connection in latin1. >>>>>>>>>>>>> >>>>>>>>>>>>> The HTTP header sends to the browser ISO-8859-15. That >>>>>>>>>>>>> should stay so, because about 2.000 html pages are coded in >>>>>>>>>>>>> this. I don't care for the DB contect, then I have it local >>>>>>>>>>>>> in some programm. My only wish is to establish a >>>>>>>>>>>>> working-encoding-connection between the PHP and DB. >>>>>>>>>>>>> >>>>>>>>>>>>> Can You please tell what exactly I have to do? Please, >>>>>>>>>>>>> understand I can't reprogramm the application, I even don't >>>>>>>>>>>>> want to, because it worked before. I'm quit sure it's not >>>>>>>>>>>>> correct setted up. >>>>>>>>>>>>> >>>>>>>>>>>>> Thanks a lot in advance!!! >>>>>>>>>>>>> >>>>>>>>>>>>> Regards >>>>>>>>>>>>> >>>>>>>>>>>>> Marc >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Oops, I didn't notice you crossposted this to >>>>>>>>>>>> comp.databases.mysql. >>>>>>>>>>>> >>>>>>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>>>>>> database. The first question is - what charset is the data >>>>>>>>>>>> in the database in? And exactly what are these >>>>>>>>>>>> "non-standard" characters? >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Hello Jerry, >>>>>>>>>>> >>>>>>>>>>> sorry for crossposting. I think it's not only MYSQL problem, >>>>>>>>>>> but also PHP problem. >>>>>>>>>>> >>>>>>>>>>> I have German umlauts in there. I guess the old DB used >>>>>>>>>>> latin1 and the new one uses UTF-8 for internal corresponding. >>>>>>>>>>> >>>>>>>>>>> As I have found it, it acctually doesn't matter how the DB >>>>>>>>>>> corresponds internal, then it's able to encode by request >>>>>>>>>>> sent by client. And that's the point, I guess PHP doesn't say >>>>>>>>>>> which encoding it would like to use. Or maybe it's enough to >>>>>>>>>>> tell DB which encoding it should normally use talking to >>>>>>>>>>> anything from outside. >>>>>>>>>>> >>>>>>>>>>> Right? >>>>>>>>>>> >>>>>>>>>>> So, I'm sorry for crossposting but I guess it's a >>>>>>>>>>> mysql-php-problem. >>>>>>>>>>> >>>>>>>>>>> Thanks a lot for Your responce. >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Regards, >>>>>>>>>>> >>>>>>>>>>> Marc >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Marc, >>>>>>>>>> >>>>>>>>>> No, it's my fault for not noticing the cross-posting in the >>>>>>>>>> first place. >>>>>>>>>> >>>>>>>>>> You probably now have a mismatch between your connection and >>>>>>>>>> your table. Besides the charset used by the table, you have >>>>>>>>>> a charset used by the connection. I suspect you have a >>>>>>>>>> mismatch here. After connecting, call: >>>>>>>>>> >>>>>>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>>>>>> >>>>>>>>>> This should also set your connection to latin1. >>>>>>>>>> >>>>>>>>> >>>>>>>>> Jerry, >>>>>>>>> >>>>>>>>> that's what I guess the problem is. >>>>>>>>> >>>>>>>>> I've tried to call the command at the mysql-shell... and got >>>>>>>>> error 1064 (42000). Should it be called via the php script >>>>>>>>> perhaps? That's not that comfortable way for me... >>>>>>>>> >>>>>>>>> Regards, >>>>>>>>> >>>>>>>>> Marc >>>>>>>>> >>>>>>>> >>>>>>>> This statement is connection specific. You need to call it >>>>>>>> after connecting, and every time after connecting. Calling it >>>>>>>> from the command line only sets it for that connection; it won't >>>>>>>> set it for your PHP script, since it uses a different connection. >>>>>>>> >>>>>>>> But your error message by itself is meaningless. What is the >>>>>>>> statement you're entering, and what is the entire error message >>>>>>>> you get? It works fine for me. >>>>>>>> >>>>>>> >>>>>>> >>>>>>> I have connected with my server via putty, have logged in. Then >>>>>>> logged in into mysql: >>>>>>> >>>>>>> # mysql --password=mypassword --user=myuser [enter] >>>>>>> >>>>>>> Welcome to the MySQL monitor. Commands end with ; or \g. >>>>>>> Your MySQL connection id is 246 >>>>>>> Server version: 5.0.32-Debian_7etch5-log Debian etch distribution >>>>>>> >>>>>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>>>>>> >>>>>>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>>>>>> >>>>>>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte >>>>>>> Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES >>>>>>> 'latin1'")' in Zeile 1 >>>>>>> mysql> >>>>>>> >>>>>>> How can I change the connection permamently for php? Is it >>>>>>> possible? Without changing the scripts... >>>>>>> >>>>>> >>>>>> No, mysql_query() is a PHP function call, not a MySQL statement. >>>>>> What you need is just >>>>>> >>>>>> mysql>SET NAMES 'latin1'; >>>>>> >>>>>> The easiest way to change startup values is to go into MySQL >>>>>> Administrator, go to Service Control and click on the Configure >>>>>> Service tab. Then set the Localization section to the charset you >>>>>> wish. >>>>>> >>>>> >>>>> I have added this call to my php script. It didn't work! >>>>> >>>>> I use MySQL on Linux! So there is no Service Control... >>>>> >>>>> I don't understand it, the MySQL docs say the standard install >>>>> comes with latin1... But it seems to be not a rule... >>>>> >>>>> Maybe I should recompile MySQL?! >>>>> >>>>> Thanks a lot for Your hints! >>>>> >>>> >>>> Marc, >>>> >>>> Since you changed servers, the other possibility is the charset the >>>> web page itself is sent as. What is the character encoding your web >>>> server is sending for the page? >>>> >>> >>> >>> At the beginning there was file 'charset' with AddDefaultCharset >>> UTF-8 at my apache2 config dir. So I didn't get it I have this >>> problem. But then I mentioned umlaut problems at the admin backend, >>> so I've begun to look for the source of it. After deleting this file, >>> I have find out, that DB data won't be shown correctly. >>> >>> The HTML pages have a HEADER with ISO-8859-15. >>> >>> I have set a new 'charset' with ISO encoding - no result. >>> >> >> OK, let's back up a bit. What was the charset you used on the >> previous system? Was it actually latin1? Or could it have been >> something else? >> >> Also, how did you move your data from one system to another? >> >> Finally - to set the charset permanently, put it in your my.cfg file >> under the [mysqld] section, i.e. >> >> character_set_client = latin1 >> > > Well, to say the truth I have no idea what the previous system was set > like. I can access it only via Plesk, and the MySQL via phpMyAdmin > 2.8.2.4. The system variables received via phpMyAdmin I have posted > already. If there is anything else I can find out, please tell me what > and where. > > I made an export by phpMyAdmin and then called following on the > mysql-shell at the new system: > > /usr/bin/mysql --host=localhost --password=password --user=user DBname < > /home/user/DBname.sql > > Viewing the export file via SuperEdi all umlauts are correct and it > seems all data is latin1-encoded, there is always this statement in > there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE > latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables. > > > Viewing the old DB all umlauts are OK, doing it at the new one, none is OK. > Yes, but chances are you used utf-8 to do the import, since that was the default for the connection. > > I have tried to set the charset permanently, but the server says > following after restart: > > > Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started > Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning] > /usr/sbin/mysqld: ignoring option '--character-set-client$ > Jun 10 10:13:15 v213880518 mysqld[20563]: > Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB: > Started; log sequence number 0 2196292 > Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note] > /usr/sbin/mysqld: bereit für Verbindungen. > Jun 10 10:13:16 v213880518 mysqld[20563]: Version: > '5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock' Port: 3$ > > > So, I habe no idea why it doesn't work. > How did you try to change the charset? And what *exactly* did you enter? Again - a message without knowing exactly what you did is worthless. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
|
|
06-10-2008, 10:51 AM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Jerry Stuckle schrieb:
> Marc wrote: >> Jerry Stuckle schrieb: >>> Marc wrote: >>>> Jerry Stuckle schrieb: >>>>> Marc wrote: >>>>>> Jerry Stuckle schrieb: >>>>>>> Marc wrote: >>>>>>>> Jerry Stuckle schrieb: >>>>>>>>> Marc wrote: >>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>> Marc wrote: >>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>> alex schrieb: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I made a simple script in PHP to dump data from tables >>>>>>>>>>>>>>> which had non standard characters. But the resulting page >>>>>>>>>>>>>>> is viewed correctly in iso-8859-1 encoding and not in UTF-8. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Hello, >>>>>>>>>>>>>> >>>>>>>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I have >>>>>>>>>>>>>> read all answers and followed the link reading them all >>>>>>>>>>>>>> carefully. I suppose I understand the matter. >>>>>>>>>>>>>> >>>>>>>>>>>>>> I have apparently the same problem as Alex has. But my >>>>>>>>>>>>>> connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>>>>>>> >>>>>>>>>>>>>> The problem accrued as I've moved from my webspace to a >>>>>>>>>>>>>> vServer. >>>>>>>>>>>>>> >>>>>>>>>>>>>> The old configuration shown by phpMyAdmin on MySQL 4.1.13 is: >>>>>>>>>>>>>> >>>>>>>>>>>>>> character set client utf8 >>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>> character set connection utf8 >>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>> character set database latin1 >>>>>>>>>>>>>> character set results utf8 >>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>> collation connection utf8_general_ci >>>>>>>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>> >>>>>>>>>>>>>> The new configuration shown by phpMyAdmin on my new MySQL >>>>>>>>>>>>>> 5.0.32 is exactly the same, the I set "SET NAMES latin1" >>>>>>>>>>>>>> via mysql-shell. >>>>>>>>>>>>>> >>>>>>>>>>>>>> BUT: >>>>>>>>>>>>>> >>>>>>>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>>>>>>> >>>>>>>>>>>>>> character_set_client latin1 >>>>>>>>>>>>>> character_set_connection latin1 >>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>> character set results latin1 >>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>> >>>>>>>>>>>>>> asking for the values by PHP gives: >>>>>>>>>>>>>> >>>>>>>>>>>>>> character_set_client utf8 >>>>>>>>>>>>>> character_set_connection utf8 >>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>> character_set_results utf8 >>>>>>>>>>>>>> character_set_server latin1 >>>>>>>>>>>>>> character_set_system utf8 >>>>>>>>>>>>>> collation_connection utf8_general_ci >>>>>>>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>>>>>>> >>>>>>>>>>>>>> I need the character_set_client and >>>>>>>>>>>>>> character_set_connection in latin1. >>>>>>>>>>>>>> >>>>>>>>>>>>>> The HTTP header sends to the browser ISO-8859-15. That >>>>>>>>>>>>>> should stay so, because about 2.000 html pages are coded >>>>>>>>>>>>>> in this. I don't care for the DB contect, then I have it >>>>>>>>>>>>>> local in some programm. My only wish is to establish a >>>>>>>>>>>>>> working-encoding-connection between the PHP and DB. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Can You please tell what exactly I have to do? Please, >>>>>>>>>>>>>> understand I can't reprogramm the application, I even >>>>>>>>>>>>>> don't want to, because it worked before. I'm quit sure >>>>>>>>>>>>>> it's not correct setted up. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Thanks a lot in advance!!! >>>>>>>>>>>>>> >>>>>>>>>>>>>> Regards >>>>>>>>>>>>>> >>>>>>>>>>>>>> Marc >>>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> Oops, I didn't notice you crossposted this to >>>>>>>>>>>>> comp.databases.mysql. >>>>>>>>>>>>> >>>>>>>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>>>>>>> database. The first question is - what charset is the data >>>>>>>>>>>>> in the database in? And exactly what are these >>>>>>>>>>>>> "non-standard" characters? >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Hello Jerry, >>>>>>>>>>>> >>>>>>>>>>>> sorry for crossposting. I think it's not only MYSQL problem, >>>>>>>>>>>> but also PHP problem. >>>>>>>>>>>> >>>>>>>>>>>> I have German umlauts in there. I guess the old DB used >>>>>>>>>>>> latin1 and the new one uses UTF-8 for internal corresponding. >>>>>>>>>>>> >>>>>>>>>>>> As I have found it, it acctually doesn't matter how the DB >>>>>>>>>>>> corresponds internal, then it's able to encode by request >>>>>>>>>>>> sent by client. And that's the point, I guess PHP doesn't >>>>>>>>>>>> say which encoding it would like to use. Or maybe it's >>>>>>>>>>>> enough to tell DB which encoding it should normally use >>>>>>>>>>>> talking to anything from outside. >>>>>>>>>>>> >>>>>>>>>>>> Right? >>>>>>>>>>>> >>>>>>>>>>>> So, I'm sorry for crossposting but I guess it's a >>>>>>>>>>>> mysql-php-problem. >>>>>>>>>>>> >>>>>>>>>>>> Thanks a lot for Your responce. >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Regards, >>>>>>>>>>>> >>>>>>>>>>>> Marc >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Marc, >>>>>>>>>>> >>>>>>>>>>> No, it's my fault for not noticing the cross-posting in the >>>>>>>>>>> first place. >>>>>>>>>>> >>>>>>>>>>> You probably now have a mismatch between your connection and >>>>>>>>>>> your table. Besides the charset used by the table, you have >>>>>>>>>>> a charset used by the connection. I suspect you have a >>>>>>>>>>> mismatch here. After connecting, call: >>>>>>>>>>> >>>>>>>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>>>>>>> >>>>>>>>>>> This should also set your connection to latin1. >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Jerry, >>>>>>>>>> >>>>>>>>>> that's what I guess the problem is. >>>>>>>>>> >>>>>>>>>> I've tried to call the command at the mysql-shell... and got >>>>>>>>>> error 1064 (42000). Should it be called via the php script >>>>>>>>>> perhaps? That's not that comfortable way for me... >>>>>>>>>> >>>>>>>>>> Regards, >>>>>>>>>> >>>>>>>>>> Marc >>>>>>>>>> >>>>>>>>> >>>>>>>>> This statement is connection specific. You need to call it >>>>>>>>> after connecting, and every time after connecting. Calling it >>>>>>>>> from the command line only sets it for that connection; it >>>>>>>>> won't set it for your PHP script, since it uses a different >>>>>>>>> connection. >>>>>>>>> >>>>>>>>> But your error message by itself is meaningless. What is the >>>>>>>>> statement you're entering, and what is the entire error message >>>>>>>>> you get? It works fine for me. >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> I have connected with my server via putty, have logged in. Then >>>>>>>> logged in into mysql: >>>>>>>> >>>>>>>> # mysql --password=mypassword --user=myuser [enter] >>>>>>>> >>>>>>>> Welcome to the MySQL monitor. Commands end with ; or \g. >>>>>>>> Your MySQL connection id is 246 >>>>>>>> Server version: 5.0.32-Debian_7etch5-log Debian etch distribution >>>>>>>> >>>>>>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>>>>>>> >>>>>>>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>>>>>>> >>>>>>>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die korrekte >>>>>>>> Syntax im Handbuch nachschlagen bei 'mysql_query("SET NAMES >>>>>>>> 'latin1'")' in Zeile 1 >>>>>>>> mysql> >>>>>>>> >>>>>>>> How can I change the connection permamently for php? Is it >>>>>>>> possible? Without changing the scripts... >>>>>>>> >>>>>>> >>>>>>> No, mysql_query() is a PHP function call, not a MySQL statement. >>>>>>> What you need is just >>>>>>> >>>>>>> mysql>SET NAMES 'latin1'; >>>>>>> >>>>>>> The easiest way to change startup values is to go into MySQL >>>>>>> Administrator, go to Service Control and click on the Configure >>>>>>> Service tab. Then set the Localization section to the charset >>>>>>> you wish. >>>>>>> >>>>>> >>>>>> I have added this call to my php script. It didn't work! >>>>>> >>>>>> I use MySQL on Linux! So there is no Service Control... >>>>>> >>>>>> I don't understand it, the MySQL docs say the standard install >>>>>> comes with latin1... But it seems to be not a rule... >>>>>> >>>>>> Maybe I should recompile MySQL?! >>>>>> >>>>>> Thanks a lot for Your hints! >>>>>> >>>>> >>>>> Marc, >>>>> >>>>> Since you changed servers, the other possibility is the charset the >>>>> web page itself is sent as. What is the character encoding your >>>>> web server is sending for the page? >>>>> >>>> >>>> >>>> At the beginning there was file 'charset' with AddDefaultCharset >>>> UTF-8 at my apache2 config dir. So I didn't get it I have this >>>> problem. But then I mentioned umlaut problems at the admin backend, >>>> so I've begun to look for the source of it. After deleting this >>>> file, I have find out, that DB data won't be shown correctly. >>>> >>>> The HTML pages have a HEADER with ISO-8859-15. >>>> >>>> I have set a new 'charset' with ISO encoding - no result. >>>> >>> >>> OK, let's back up a bit. What was the charset you used on the >>> previous system? Was it actually latin1? Or could it have been >>> something else? >>> >>> Also, how did you move your data from one system to another? >>> >>> Finally - to set the charset permanently, put it in your my.cfg file >>> under the [mysqld] section, i.e. >>> >>> character_set_client = latin1 >>> >> >> Well, to say the truth I have no idea what the previous system was set >> like. I can access it only via Plesk, and the MySQL via phpMyAdmin >> 2.8.2.4. The system variables received via phpMyAdmin I have posted >> already. If there is anything else I can find out, please tell me what >> and where. >> >> I made an export by phpMyAdmin and then called following on the >> mysql-shell at the new system: >> >> /usr/bin/mysql --host=localhost --password=password --user=user DBname >> < /home/user/DBname.sql >> >> Viewing the export file via SuperEdi all umlauts are correct and it >> seems all data is latin1-encoded, there is always this statement in >> there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE >> latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables. >> >> >> Viewing the old DB all umlauts are OK, doing it at the new one, none >> is OK. >> > > Yes, but chances are you used utf-8 to do the import, since that was the > default for the connection. Yes, that's probabely the point. I still don't know how to set it to latin1. >> >> I have tried to set the charset permanently, but the server says >> following after restart: >> >> >> Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started >> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning] >> /usr/sbin/mysqld: ignoring option '--character-set-client$ >> Jun 10 10:13:15 v213880518 mysqld[20563]: >> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB: >> Started; log sequence number 0 2196292 >> Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note] >> /usr/sbin/mysqld: bereit für Verbindungen. >> Jun 10 10:13:16 v213880518 mysqld[20563]: Version: >> '5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock' >> Port: 3$ >> >> >> So, I habe no idea why it doesn't work. >> > > How did you try to change the charset? And what *exactly* did you > enter? Again - a message without knowing exactly what you did is > worthless. > I'm sorry. I connected to my vServer via putty. I opened my.cnf entering: mcedit /etc/mysql/my.cnf I added to my.cnf, as You told me, as follows [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/german skip-external-locking character_set_client = latin1 #character_set_connection = latin1 # i tried it once with this, but it didn't work at all... By the way, I also set default-character-set at the [client], see: [client] port = 3306 socket = /var/run/mysqld/mysqld.sock #set names = latin1 #character-set-client = latin1 doesn't work, so pitty! #character-set-connection = latin1 doesn't work, so pitty! default-character-set = latin1 #default-collation = latin1_german2_ci doesn't work I hope it's a better posting. I'm sorry once more.
|
|
06-10-2008, 10:52 AM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Marc wrote:
> Jerry Stuckle schrieb: >> Marc wrote: >>> Jerry Stuckle schrieb: >>>> Marc wrote: >>>>> Jerry Stuckle schrieb: >>>>>> Marc wrote: >>>>>>> Jerry Stuckle schrieb: >>>>>>>> Marc wrote: >>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>> Marc wrote: >>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>>> alex schrieb: >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> I made a simple script in PHP to dump data from tables >>>>>>>>>>>>>>>> which had non standard characters. But the resulting >>>>>>>>>>>>>>>> page is viewed correctly in iso-8859-1 encoding and not >>>>>>>>>>>>>>>> in UTF-8. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Hello, >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I have >>>>>>>>>>>>>>> read all answers and followed the link reading them all >>>>>>>>>>>>>>> carefully. I suppose I understand the matter. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I have apparently the same problem as Alex has. But my >>>>>>>>>>>>>>> connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> The problem accrued as I've moved from my webspace to a >>>>>>>>>>>>>>> vServer. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> The old configuration shown by phpMyAdmin on MySQL 4.1.13 >>>>>>>>>>>>>>> is: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> character set client utf8 >>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>> character set connection utf8 >>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>> character set database latin1 >>>>>>>>>>>>>>> character set results utf8 >>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>>> collation connection utf8_general_ci >>>>>>>>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> The new configuration shown by phpMyAdmin on my new MySQL >>>>>>>>>>>>>>> 5.0.32 is exactly the same, the I set "SET NAMES latin1" >>>>>>>>>>>>>>> via mysql-shell. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> BUT: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> character_set_client latin1 >>>>>>>>>>>>>>> character_set_connection latin1 >>>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>>> character set results latin1 >>>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> asking for the values by PHP gives: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> character_set_client utf8 >>>>>>>>>>>>>>> character_set_connection utf8 >>>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>>> character_set_results utf8 >>>>>>>>>>>>>>> character_set_server latin1 >>>>>>>>>>>>>>> character_set_system utf8 >>>>>>>>>>>>>>> collation_connection utf8_general_ci >>>>>>>>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I need the character_set_client and >>>>>>>>>>>>>>> character_set_connection in latin1. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> The HTTP header sends to the browser ISO-8859-15. That >>>>>>>>>>>>>>> should stay so, because about 2.000 html pages are coded >>>>>>>>>>>>>>> in this. I don't care for the DB contect, then I have it >>>>>>>>>>>>>>> local in some programm. My only wish is to establish a >>>>>>>>>>>>>>> working-encoding-connection between the PHP and DB. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Can You please tell what exactly I have to do? Please, >>>>>>>>>>>>>>> understand I can't reprogramm the application, I even >>>>>>>>>>>>>>> don't want to, because it worked before. I'm quit sure >>>>>>>>>>>>>>> it's not correct setted up. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Thanks a lot in advance!!! >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Regards >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Marc >>>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> Oops, I didn't notice you crossposted this to >>>>>>>>>>>>>> comp.databases.mysql. >>>>>>>>>>>>>> >>>>>>>>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>>>>>>>> database. The first question is - what charset is the >>>>>>>>>>>>>> data in the database in? And exactly what are these >>>>>>>>>>>>>> "non-standard" characters? >>>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> Hello Jerry, >>>>>>>>>>>>> >>>>>>>>>>>>> sorry for crossposting. I think it's not only MYSQL >>>>>>>>>>>>> problem, but also PHP problem. >>>>>>>>>>>>> >>>>>>>>>>>>> I have German umlauts in there. I guess the old DB used >>>>>>>>>>>>> latin1 and the new one uses UTF-8 for internal corresponding. >>>>>>>>>>>>> >>>>>>>>>>>>> As I have found it, it acctually doesn't matter how the DB >>>>>>>>>>>>> corresponds internal, then it's able to encode by request >>>>>>>>>>>>> sent by client. And that's the point, I guess PHP doesn't >>>>>>>>>>>>> say which encoding it would like to use. Or maybe it's >>>>>>>>>>>>> enough to tell DB which encoding it should normally use >>>>>>>>>>>>> talking to anything from outside. >>>>>>>>>>>>> >>>>>>>>>>>>> Right? >>>>>>>>>>>>> >>>>>>>>>>>>> So, I'm sorry for crossposting but I guess it's a >>>>>>>>>>>>> mysql-php-problem. >>>>>>>>>>>>> >>>>>>>>>>>>> Thanks a lot for Your responce. >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> Regards, >>>>>>>>>>>>> >>>>>>>>>>>>> Marc >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Marc, >>>>>>>>>>>> >>>>>>>>>>>> No, it's my fault for not noticing the cross-posting in the >>>>>>>>>>>> first place. >>>>>>>>>>>> >>>>>>>>>>>> You probably now have a mismatch between your connection and >>>>>>>>>>>> your table. Besides the charset used by the table, you >>>>>>>>>>>> have a charset used by the connection. I suspect you have a >>>>>>>>>>>> mismatch here. After connecting, call: >>>>>>>>>>>> >>>>>>>>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>>>>>>>> >>>>>>>>>>>> This should also set your connection to latin1. >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Jerry, >>>>>>>>>>> >>>>>>>>>>> that's what I guess the problem is. >>>>>>>>>>> >>>>>>>>>>> I've tried to call the command at the mysql-shell... and got >>>>>>>>>>> error 1064 (42000). Should it be called via the php script >>>>>>>>>>> perhaps? That's not that comfortable way for me... >>>>>>>>>>> >>>>>>>>>>> Regards, >>>>>>>>>>> >>>>>>>>>>> Marc >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> This statement is connection specific. You need to call it >>>>>>>>>> after connecting, and every time after connecting. Calling it >>>>>>>>>> from the command line only sets it for that connection; it >>>>>>>>>> won't set it for your PHP script, since it uses a different >>>>>>>>>> connection. >>>>>>>>>> >>>>>>>>>> But your error message by itself is meaningless. What is the >>>>>>>>>> statement you're entering, and what is the entire error >>>>>>>>>> message you get? It works fine for me. >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> I have connected with my server via putty, have logged in. Then >>>>>>>>> logged in into mysql: >>>>>>>>> >>>>>>>>> # mysql --password=mypassword --user=myuser [enter] >>>>>>>>> >>>>>>>>> Welcome to the MySQL monitor. Commands end with ; or \g. >>>>>>>>> Your MySQL connection id is 246 >>>>>>>>> Server version: 5.0.32-Debian_7etch5-log Debian etch distribution >>>>>>>>> >>>>>>>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>>>>>>>> >>>>>>>>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>>>>>>>> >>>>>>>>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die >>>>>>>>> korrekte Syntax im Handbuch nachschlagen bei 'mysql_query("SET >>>>>>>>> NAMES 'latin1'")' in Zeile 1 >>>>>>>>> mysql> >>>>>>>>> >>>>>>>>> How can I change the connection permamently for php? Is it >>>>>>>>> possible? Without changing the scripts... >>>>>>>>> >>>>>>>> >>>>>>>> No, mysql_query() is a PHP function call, not a MySQL >>>>>>>> statement. What you need is just >>>>>>>> >>>>>>>> mysql>SET NAMES 'latin1'; >>>>>>>> >>>>>>>> The easiest way to change startup values is to go into MySQL >>>>>>>> Administrator, go to Service Control and click on the Configure >>>>>>>> Service tab. Then set the Localization section to the charset >>>>>>>> you wish. >>>>>>>> >>>>>>> >>>>>>> I have added this call to my php script. It didn't work! >>>>>>> >>>>>>> I use MySQL on Linux! So there is no Service Control... >>>>>>> >>>>>>> I don't understand it, the MySQL docs say the standard install >>>>>>> comes with latin1... But it seems to be not a rule... >>>>>>> >>>>>>> Maybe I should recompile MySQL?! >>>>>>> >>>>>>> Thanks a lot for Your hints! >>>>>>> >>>>>> >>>>>> Marc, >>>>>> >>>>>> Since you changed servers, the other possibility is the charset >>>>>> the web page itself is sent as. What is the character encoding >>>>>> your web server is sending for the page? >>>>>> >>>>> >>>>> >>>>> At the beginning there was file 'charset' with AddDefaultCharset >>>>> UTF-8 at my apache2 config dir. So I didn't get it I have this >>>>> problem. But then I mentioned umlaut problems at the admin backend, >>>>> so I've begun to look for the source of it. After deleting this >>>>> file, I have find out, that DB data won't be shown correctly. >>>>> >>>>> The HTML pages have a HEADER with ISO-8859-15. >>>>> >>>>> I have set a new 'charset' with ISO encoding - no result. >>>>> >>>> >>>> OK, let's back up a bit. What was the charset you used on the >>>> previous system? Was it actually latin1? Or could it have been >>>> something else? >>>> >>>> Also, how did you move your data from one system to another? >>>> >>>> Finally - to set the charset permanently, put it in your my.cfg file >>>> under the [mysqld] section, i.e. >>>> >>>> character_set_client = latin1 >>>> >>> >>> Well, to say the truth I have no idea what the previous system was >>> set like. I can access it only via Plesk, and the MySQL via >>> phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin I >>> have posted already. If there is anything else I can find out, please >>> tell me what and where. >>> >>> I made an export by phpMyAdmin and then called following on the >>> mysql-shell at the new system: >>> >>> /usr/bin/mysql --host=localhost --password=password --user=user >>> DBname < /home/user/DBname.sql >>> >>> Viewing the export file via SuperEdi all umlauts are correct and it >>> seems all data is latin1-encoded, there is always this statement in >>> there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE >>> latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables. >>> >>> >>> Viewing the old DB all umlauts are OK, doing it at the new one, none >>> is OK. >>> >> >> Yes, but chances are you used utf-8 to do the import, since that was >> the default for the connection. > > Yes, that's probabely the point. I still don't know how to set it to > latin1. > >>> >>> I have tried to set the charset permanently, but the server says >>> following after restart: >>> >>> >>> Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started >>> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning] >>> /usr/sbin/mysqld: ignoring option '--character-set-client$ >>> Jun 10 10:13:15 v213880518 mysqld[20563]: >>> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB: >>> Started; log sequence number 0 2196292 >>> Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note] >>> /usr/sbin/mysqld: bereit für Verbindungen. >>> Jun 10 10:13:16 v213880518 mysqld[20563]: Version: >>> '5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock' >>> Port: 3$ >>> >>> >>> So, I habe no idea why it doesn't work. >>> >> >> How did you try to change the charset? And what *exactly* did you >> enter? Again - a message without knowing exactly what you did is >> worthless. >> > > I'm sorry. > > I connected to my vServer via putty. > > I opened my.cnf entering: mcedit /etc/mysql/my.cnf > > I added to my.cnf, as You told me, as follows > > [mysqld] > # > # * Basic Settings > # > user = mysql > pid-file = /var/run/mysqld/mysqld.pid > socket = /var/run/mysqld/mysqld.sock > port = 3306 > basedir = /usr > datadir = /var/lib/mysql > tmpdir = /tmp > language = /usr/share/mysql/german > skip-external-locking > character_set_client = latin1 > #character_set_connection = latin1 # i tried it once with this, > but it didn't work at all... > > > By the way, I also set default-character-set at the [client], see: > > > [client] > port = 3306 > socket = /var/run/mysqld/mysqld.sock > #set names = latin1 > #character-set-client = latin1 doesn't work, so pitty! > #character-set-connection = latin1 doesn't work, so pitty! > default-character-set = latin1 > #default-collation = latin1_german2_ci doesn't work > > > I hope it's a better posting. I'm sorry once more. > Here is your problem - it is character_set_client, not character-set-client. See the difference? (That's what the message said, also). -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
|
|
06-10-2008, 12:04 PM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Jerry Stuckle schrieb:
> Marc wrote: >> Jerry Stuckle schrieb: >>> Marc wrote: >>>> Jerry Stuckle schrieb: >>>>> Marc wrote: >>>>>> Jerry Stuckle schrieb: >>>>>>> Marc wrote: >>>>>>>> Jerry Stuckle schrieb: >>>>>>>>> Marc wrote: >>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>> Marc wrote: >>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>>>> alex schrieb: >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> I made a simple script in PHP to dump data from tables >>>>>>>>>>>>>>>>> which had non standard characters. But the resulting >>>>>>>>>>>>>>>>> page is viewed correctly in iso-8859-1 encoding and not >>>>>>>>>>>>>>>>> in UTF-8. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Hello, >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I have >>>>>>>>>>>>>>>> read all answers and followed the link reading them all >>>>>>>>>>>>>>>> carefully. I suppose I understand the matter. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> I have apparently the same problem as Alex has. But my >>>>>>>>>>>>>>>> connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> The problem accrued as I've moved from my webspace to a >>>>>>>>>>>>>>>> vServer. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> The old configuration shown by phpMyAdmin on MySQL >>>>>>>>>>>>>>>> 4.1.13 is: >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> character set client utf8 >>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>> character set connection utf8 >>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>> character set database latin1 >>>>>>>>>>>>>>>> character set results utf8 >>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>>>> collation connection utf8_general_ci >>>>>>>>>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> The new configuration shown by phpMyAdmin on my new >>>>>>>>>>>>>>>> MySQL 5.0.32 is exactly the same, the I set "SET NAMES >>>>>>>>>>>>>>>> latin1" via mysql-shell. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> BUT: >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> character_set_client latin1 >>>>>>>>>>>>>>>> character_set_connection latin1 >>>>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>>>> character set results latin1 >>>>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> asking for the values by PHP gives: >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> character_set_client utf8 >>>>>>>>>>>>>>>> character_set_connection utf8 >>>>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>>>> character_set_results utf8 >>>>>>>>>>>>>>>> character_set_server latin1 >>>>>>>>>>>>>>>> character_set_system utf8 >>>>>>>>>>>>>>>> collation_connection utf8_general_ci >>>>>>>>>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> I need the character_set_client and >>>>>>>>>>>>>>>> character_set_connection in latin1. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> The HTTP header sends to the browser ISO-8859-15. That >>>>>>>>>>>>>>>> should stay so, because about 2.000 html pages are coded >>>>>>>>>>>>>>>> in this. I don't care for the DB contect, then I have it >>>>>>>>>>>>>>>> local in some programm. My only wish is to establish a >>>>>>>>>>>>>>>> working-encoding-connection between the PHP and DB. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Can You please tell what exactly I have to do? Please, >>>>>>>>>>>>>>>> understand I can't reprogramm the application, I even >>>>>>>>>>>>>>>> don't want to, because it worked before. I'm quit sure >>>>>>>>>>>>>>>> it's not correct setted up. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Thanks a lot in advance!!! >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Regards >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Marc >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Oops, I didn't notice you crossposted this to >>>>>>>>>>>>>>> comp.databases.mysql. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>>>>>>>>> database. The first question is - what charset is the >>>>>>>>>>>>>>> data in the database in? And exactly what are these >>>>>>>>>>>>>>> "non-standard" characters? >>>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> Hello Jerry, >>>>>>>>>>>>>> >>>>>>>>>>>>>> sorry for crossposting. I think it's not only MYSQL >>>>>>>>>>>>>> problem, but also PHP problem. >>>>>>>>>>>>>> >>>>>>>>>>>>>> I have German umlauts in there. I guess the old DB used >>>>>>>>>>>>>> latin1 and the new one uses UTF-8 for internal corresponding. >>>>>>>>>>>>>> >>>>>>>>>>>>>> As I have found it, it acctually doesn't matter how the DB >>>>>>>>>>>>>> corresponds internal, then it's able to encode by request >>>>>>>>>>>>>> sent by client. And that's the point, I guess PHP doesn't >>>>>>>>>>>>>> say which encoding it would like to use. Or maybe it's >>>>>>>>>>>>>> enough to tell DB which encoding it should normally use >>>>>>>>>>>>>> talking to anything from outside. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Right? >>>>>>>>>>>>>> >>>>>>>>>>>>>> So, I'm sorry for crossposting but I guess it's a >>>>>>>>>>>>>> mysql-php-problem. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Thanks a lot for Your responce. >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> Regards, >>>>>>>>>>>>>> >>>>>>>>>>>>>> Marc >>>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> Marc, >>>>>>>>>>>>> >>>>>>>>>>>>> No, it's my fault for not noticing the cross-posting in the >>>>>>>>>>>>> first place. >>>>>>>>>>>>> >>>>>>>>>>>>> You probably now have a mismatch between your connection >>>>>>>>>>>>> and your table. Besides the charset used by the table, >>>>>>>>>>>>> you have a charset used by the connection. I suspect you >>>>>>>>>>>>> have a mismatch here. After connecting, call: >>>>>>>>>>>>> >>>>>>>>>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>>>>>>>>> >>>>>>>>>>>>> This should also set your connection to latin1. >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Jerry, >>>>>>>>>>>> >>>>>>>>>>>> that's what I guess the problem is. >>>>>>>>>>>> >>>>>>>>>>>> I've tried to call the command at the mysql-shell... and got >>>>>>>>>>>> error 1064 (42000). Should it be called via the php script >>>>>>>>>>>> perhaps? That's not that comfortable way for me... >>>>>>>>>>>> >>>>>>>>>>>> Regards, >>>>>>>>>>>> >>>>>>>>>>>> Marc >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> This statement is connection specific. You need to call it >>>>>>>>>>> after connecting, and every time after connecting. Calling >>>>>>>>>>> it from the command line only sets it for that connection; it >>>>>>>>>>> won't set it for your PHP script, since it uses a different >>>>>>>>>>> connection. >>>>>>>>>>> >>>>>>>>>>> But your error message by itself is meaningless. What is the >>>>>>>>>>> statement you're entering, and what is the entire error >>>>>>>>>>> message you get? It works fine for me. >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> I have connected with my server via putty, have logged in. >>>>>>>>>> Then logged in into mysql: >>>>>>>>>> >>>>>>>>>> # mysql --password=mypassword --user=myuser [enter] >>>>>>>>>> >>>>>>>>>> Welcome to the MySQL monitor. Commands end with ; or \g. >>>>>>>>>> Your MySQL connection id is 246 >>>>>>>>>> Server version: 5.0.32-Debian_7etch5-log Debian etch distribution >>>>>>>>>> >>>>>>>>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>>>>>>>>> >>>>>>>>>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>>>>>>>>> >>>>>>>>>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die >>>>>>>>>> korrekte Syntax im Handbuch nachschlagen bei 'mysql_query("SET >>>>>>>>>> NAMES 'latin1'")' in Zeile 1 >>>>>>>>>> mysql> >>>>>>>>>> >>>>>>>>>> How can I change the connection permamently for php? Is it >>>>>>>>>> possible? Without changing the scripts... >>>>>>>>>> >>>>>>>>> >>>>>>>>> No, mysql_query() is a PHP function call, not a MySQL >>>>>>>>> statement. What you need is just >>>>>>>>> >>>>>>>>> mysql>SET NAMES 'latin1'; >>>>>>>>> >>>>>>>>> The easiest way to change startup values is to go into MySQL >>>>>>>>> Administrator, go to Service Control and click on the Configure >>>>>>>>> Service tab. Then set the Localization section to the charset >>>>>>>>> you wish. >>>>>>>>> >>>>>>>> >>>>>>>> I have added this call to my php script. It didn't work! >>>>>>>> >>>>>>>> I use MySQL on Linux! So there is no Service Control... >>>>>>>> >>>>>>>> I don't understand it, the MySQL docs say the standard install >>>>>>>> comes with latin1... But it seems to be not a rule... >>>>>>>> >>>>>>>> Maybe I should recompile MySQL?! >>>>>>>> >>>>>>>> Thanks a lot for Your hints! >>>>>>>> >>>>>>> >>>>>>> Marc, >>>>>>> >>>>>>> Since you changed servers, the other possibility is the charset >>>>>>> the web page itself is sent as. What is the character encoding >>>>>>> your web server is sending for the page? >>>>>>> >>>>>> >>>>>> >>>>>> At the beginning there was file 'charset' with AddDefaultCharset >>>>>> UTF-8 at my apache2 config dir. So I didn't get it I have this >>>>>> problem. But then I mentioned umlaut problems at the admin >>>>>> backend, so I've begun to look for the source of it. After >>>>>> deleting this file, I have find out, that DB data won't be shown >>>>>> correctly. >>>>>> >>>>>> The HTML pages have a HEADER with ISO-8859-15. >>>>>> >>>>>> I have set a new 'charset' with ISO encoding - no result. >>>>>> >>>>> >>>>> OK, let's back up a bit. What was the charset you used on the >>>>> previous system? Was it actually latin1? Or could it have been >>>>> something else? >>>>> >>>>> Also, how did you move your data from one system to another? >>>>> >>>>> Finally - to set the charset permanently, put it in your my.cfg >>>>> file under the [mysqld] section, i.e. >>>>> >>>>> character_set_client = latin1 >>>>> >>>> >>>> Well, to say the truth I have no idea what the previous system was >>>> set like. I can access it only via Plesk, and the MySQL via >>>> phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin I >>>> have posted already. If there is anything else I can find out, >>>> please tell me what and where. >>>> >>>> I made an export by phpMyAdmin and then called following on the >>>> mysql-shell at the new system: >>>> >>>> /usr/bin/mysql --host=localhost --password=password --user=user >>>> DBname < /home/user/DBname.sql >>>> >>>> Viewing the export file via SuperEdi all umlauts are correct and it >>>> seems all data is latin1-encoded, there is always this statement in >>>> there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE >>>> latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables. >>>> >>>> >>>> Viewing the old DB all umlauts are OK, doing it at the new one, none >>>> is OK. >>>> >>> >>> Yes, but chances are you used utf-8 to do the import, since that was >>> the default for the connection. >> >> Yes, that's probabely the point. I still don't know how to set it to >> latin1. >> >>>> >>>> I have tried to set the charset permanently, but the server says >>>> following after restart: >>>> >>>> >>>> Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started >>>> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning] >>>> /usr/sbin/mysqld: ignoring option '--character-set-client$ >>>> Jun 10 10:13:15 v213880518 mysqld[20563]: >>>> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB: >>>> Started; log sequence number 0 2196292 >>>> Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note] >>>> /usr/sbin/mysqld: bereit für Verbindungen. >>>> Jun 10 10:13:16 v213880518 mysqld[20563]: Version: >>>> '5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock' >>>> Port: 3$ >>>> >>>> >>>> So, I habe no idea why it doesn't work. >>>> >>> >>> How did you try to change the charset? And what *exactly* did you >>> enter? Again - a message without knowing exactly what you did is >>> worthless. >>> >> >> I'm sorry. >> >> I connected to my vServer via putty. >> >> I opened my.cnf entering: mcedit /etc/mysql/my.cnf >> >> I added to my.cnf, as You told me, as follows >> >> [mysqld] >> # >> # * Basic Settings >> # >> user = mysql >> pid-file = /var/run/mysqld/mysqld.pid >> socket = /var/run/mysqld/mysqld.sock >> port = 3306 >> basedir = /usr >> datadir = /var/lib/mysql >> tmpdir = /tmp >> language = /usr/share/mysql/german >> skip-external-locking >> character_set_client = latin1 >> #character_set_connection = latin1 # i tried it once with >> this, but it didn't work at all... >> >> >> By the way, I also set default-character-set at the [client], see: >> >> >> [client] >> port = 3306 >> socket = /var/run/mysqld/mysqld.sock >> #set names = latin1 >> #character-set-client = latin1 doesn't work, so pitty! >> #character-set-connection = latin1 doesn't work, so pitty! >> default-character-set = latin1 >> #default-collation = latin1_german2_ci doesn't work >> >> >> I hope it's a better posting. I'm sorry once more. >> > > Here is your problem - it is character_set_client, not > character-set-client. See the difference? > > (That's what the message said, also). > I'm sorry, it seems You are not that right. Trying to call MySQL at the shell via putty I get following error message: v213880518:/# mysql --password=password --user=user mysql: unknown variable 'character_set_client=latin1' It doesn't matter how I use it in [client], neither character_set_client nor character-set-client work. So that's why they are commented. So, once more, there are two blocks in my.cnf: [mysqld] and [client]. Should I use only one of them to set the charsets or both? And with which option?
|
|
06-10-2008, 02:30 PM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Marc wrote:
> Jerry Stuckle schrieb: >> Marc wrote: >>> Jerry Stuckle schrieb: >>>> Marc wrote: >>>>> Jerry Stuckle schrieb: >>>>>> Marc wrote: >>>>>>> Jerry Stuckle schrieb: >>>>>>>> Marc wrote: >>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>> Marc wrote: >>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>>>>> alex schrieb: >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> I made a simple script in PHP to dump data from tables >>>>>>>>>>>>>>>>>> which had non standard characters. But the resulting >>>>>>>>>>>>>>>>>> page is viewed correctly in iso-8859-1 encoding and >>>>>>>>>>>>>>>>>> not in UTF-8. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Hello, >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I have >>>>>>>>>>>>>>>>> read all answers and followed the link reading them all >>>>>>>>>>>>>>>>> carefully. I suppose I understand the matter. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> I have apparently the same problem as Alex has. But my >>>>>>>>>>>>>>>>> connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> The problem accrued as I've moved from my webspace to a >>>>>>>>>>>>>>>>> vServer. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> The old configuration shown by phpMyAdmin on MySQL >>>>>>>>>>>>>>>>> 4.1.13 is: >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> character set client utf8 >>>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>>> character set connection utf8 >>>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>>> character set database latin1 >>>>>>>>>>>>>>>>> character set results utf8 >>>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>>>>> collation connection utf8_general_ci >>>>>>>>>>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> The new configuration shown by phpMyAdmin on my new >>>>>>>>>>>>>>>>> MySQL 5.0.32 is exactly the same, the I set "SET NAMES >>>>>>>>>>>>>>>>> latin1" via mysql-shell. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> BUT: >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> character_set_client latin1 >>>>>>>>>>>>>>>>> character_set_connection latin1 >>>>>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>>>>> character set results latin1 >>>>>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> asking for the values by PHP gives: >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> character_set_client utf8 >>>>>>>>>>>>>>>>> character_set_connection utf8 >>>>>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>>>>> character_set_results utf8 >>>>>>>>>>>>>>>>> character_set_server latin1 >>>>>>>>>>>>>>>>> character_set_system utf8 >>>>>>>>>>>>>>>>> collation_connection utf8_general_ci >>>>>>>>>>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>>>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> I need the character_set_client and >>>>>>>>>>>>>>>>> character_set_connection in latin1. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> The HTTP header sends to the browser ISO-8859-15. That >>>>>>>>>>>>>>>>> should stay so, because about 2.000 html pages are >>>>>>>>>>>>>>>>> coded in this. I don't care for the DB contect, then I >>>>>>>>>>>>>>>>> have it local in some programm. My only wish is to >>>>>>>>>>>>>>>>> establish a working-encoding-connection between the PHP >>>>>>>>>>>>>>>>> and DB. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Can You please tell what exactly I have to do? Please, >>>>>>>>>>>>>>>>> understand I can't reprogramm the application, I even >>>>>>>>>>>>>>>>> don't want to, because it worked before. I'm quit sure >>>>>>>>>>>>>>>>> it's not correct setted up. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Thanks a lot in advance!!! >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Regards >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Marc >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Oops, I didn't notice you crossposted this to >>>>>>>>>>>>>>>> comp.databases.mysql. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>>>>>>>>>> database. The first question is - what charset is the >>>>>>>>>>>>>>>> data in the database in? And exactly what are these >>>>>>>>>>>>>>>> "non-standard" characters? >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Hello Jerry, >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> sorry for crossposting. I think it's not only MYSQL >>>>>>>>>>>>>>> problem, but also PHP problem. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I have German umlauts in there. I guess the old DB used >>>>>>>>>>>>>>> latin1 and the new one uses UTF-8 for internal >>>>>>>>>>>>>>> corresponding. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> As I have found it, it acctually doesn't matter how the >>>>>>>>>>>>>>> DB corresponds internal, then it's able to encode by >>>>>>>>>>>>>>> request sent by client. And that's the point, I guess PHP >>>>>>>>>>>>>>> doesn't say which encoding it would like to use. Or maybe >>>>>>>>>>>>>>> it's enough to tell DB which encoding it should normally >>>>>>>>>>>>>>> use talking to anything from outside. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Right? >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> So, I'm sorry for crossposting but I guess it's a >>>>>>>>>>>>>>> mysql-php-problem. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Thanks a lot for Your responce. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Regards, >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Marc >>>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> Marc, >>>>>>>>>>>>>> >>>>>>>>>>>>>> No, it's my fault for not noticing the cross-posting in >>>>>>>>>>>>>> the first place. >>>>>>>>>>>>>> >>>>>>>>>>>>>> You probably now have a mismatch between your connection >>>>>>>>>>>>>> and your table. Besides the charset used by the table, >>>>>>>>>>>>>> you have a charset used by the connection. I suspect you >>>>>>>>>>>>>> have a mismatch here. After connecting, call: >>>>>>>>>>>>>> >>>>>>>>>>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>>>>>>>>>> >>>>>>>>>>>>>> This should also set your connection to latin1. >>>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> Jerry, >>>>>>>>>>>>> >>>>>>>>>>>>> that's what I guess the problem is. >>>>>>>>>>>>> >>>>>>>>>>>>> I've tried to call the command at the mysql-shell... and >>>>>>>>>>>>> got error 1064 (42000). Should it be called via the php >>>>>>>>>>>>> script perhaps? That's not that comfortable way for me... >>>>>>>>>>>>> >>>>>>>>>>>>> Regards, >>>>>>>>>>>>> >>>>>>>>>>>>> Marc >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> This statement is connection specific. You need to call it >>>>>>>>>>>> after connecting, and every time after connecting. Calling >>>>>>>>>>>> it from the command line only sets it for that connection; >>>>>>>>>>>> it won't set it for your PHP script, since it uses a >>>>>>>>>>>> different connection. >>>>>>>>>>>> >>>>>>>>>>>> But your error message by itself is meaningless. What is >>>>>>>>>>>> the statement you're entering, and what is the entire error >>>>>>>>>>>> message you get? It works fine for me. >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> I have connected with my server via putty, have logged in. >>>>>>>>>>> Then logged in into mysql: >>>>>>>>>>> >>>>>>>>>>> # mysql --password=mypassword --user=myuser [enter] >>>>>>>>>>> >>>>>>>>>>> Welcome to the MySQL monitor. Commands end with ; or \g. >>>>>>>>>>> Your MySQL connection id is 246 >>>>>>>>>>> Server version: 5.0.32-Debian_7etch5-log Debian etch >>>>>>>>>>> distribution >>>>>>>>>>> >>>>>>>>>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>>>>>>>>>> >>>>>>>>>>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>>>>>>>>>> >>>>>>>>>>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die >>>>>>>>>>> korrekte Syntax im Handbuch nachschlagen bei >>>>>>>>>>> 'mysql_query("SET NAMES 'latin1'")' in Zeile 1 >>>>>>>>>>> mysql> >>>>>>>>>>> >>>>>>>>>>> How can I change the connection permamently for php? Is it >>>>>>>>>>> possible? Without changing the scripts... >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> No, mysql_query() is a PHP function call, not a MySQL >>>>>>>>>> statement. What you need is just >>>>>>>>>> >>>>>>>>>> mysql>SET NAMES 'latin1'; >>>>>>>>>> >>>>>>>>>> The easiest way to change startup values is to go into MySQL >>>>>>>>>> Administrator, go to Service Control and click on the >>>>>>>>>> Configure Service tab. Then set the Localization section to >>>>>>>>>> the charset you wish. >>>>>>>>>> >>>>>>>>> >>>>>>>>> I have added this call to my php script. It didn't work! >>>>>>>>> >>>>>>>>> I use MySQL on Linux! So there is no Service Control... >>>>>>>>> >>>>>>>>> I don't understand it, the MySQL docs say the standard install >>>>>>>>> comes with latin1... But it seems to be not a rule... >>>>>>>>> >>>>>>>>> Maybe I should recompile MySQL?! >>>>>>>>> >>>>>>>>> Thanks a lot for Your hints! >>>>>>>>> >>>>>>>> >>>>>>>> Marc, >>>>>>>> >>>>>>>> Since you changed servers, the other possibility is the charset >>>>>>>> the web page itself is sent as. What is the character encoding >>>>>>>> your web server is sending for the page? >>>>>>>> >>>>>>> >>>>>>> >>>>>>> At the beginning there was file 'charset' with AddDefaultCharset >>>>>>> UTF-8 at my apache2 config dir. So I didn't get it I have this >>>>>>> problem. But then I mentioned umlaut problems at the admin >>>>>>> backend, so I've begun to look for the source of it. After >>>>>>> deleting this file, I have find out, that DB data won't be shown >>>>>>> correctly. >>>>>>> >>>>>>> The HTML pages have a HEADER with ISO-8859-15. >>>>>>> >>>>>>> I have set a new 'charset' with ISO encoding - no result. >>>>>>> >>>>>> >>>>>> OK, let's back up a bit. What was the charset you used on the >>>>>> previous system? Was it actually latin1? Or could it have been >>>>>> something else? >>>>>> >>>>>> Also, how did you move your data from one system to another? >>>>>> >>>>>> Finally - to set the charset permanently, put it in your my.cfg >>>>>> file under the [mysqld] section, i.e. >>>>>> >>>>>> character_set_client = latin1 >>>>>> >>>>> >>>>> Well, to say the truth I have no idea what the previous system was >>>>> set like. I can access it only via Plesk, and the MySQL via >>>>> phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin I >>>>> have posted already. If there is anything else I can find out, >>>>> please tell me what and where. >>>>> >>>>> I made an export by phpMyAdmin and then called following on the >>>>> mysql-shell at the new system: >>>>> >>>>> /usr/bin/mysql --host=localhost --password=password --user=user >>>>> DBname < /home/user/DBname.sql >>>>> >>>>> Viewing the export file via SuperEdi all umlauts are correct and it >>>>> seems all data is latin1-encoded, there is always this statement in >>>>> there: CREATE DATABASE `shop` DEFAULT CHARACTER SET latin1 COLLATE >>>>> latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the tables. >>>>> >>>>> >>>>> Viewing the old DB all umlauts are OK, doing it at the new one, >>>>> none is OK. >>>>> >>>> >>>> Yes, but chances are you used utf-8 to do the import, since that was >>>> the default for the connection. >>> >>> Yes, that's probabely the point. I still don't know how to set it to >>> latin1. >>> >>>>> >>>>> I have tried to set the charset permanently, but the server says >>>>> following after restart: >>>>> >>>>> >>>>> Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started >>>>> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 [Warning] >>>>> /usr/sbin/mysqld: ignoring option '--character-set-client$ >>>>> Jun 10 10:13:15 v213880518 mysqld[20563]: >>>>> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB: >>>>> Started; log sequence number 0 2196292 >>>>> Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note] >>>>> /usr/sbin/mysqld: bereit für Verbindungen. >>>>> Jun 10 10:13:16 v213880518 mysqld[20563]: Version: >>>>> '5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock' >>>>> Port: 3$ >>>>> >>>>> >>>>> So, I habe no idea why it doesn't work. >>>>> >>>> >>>> How did you try to change the charset? And what *exactly* did you >>>> enter? Again - a message without knowing exactly what you did is >>>> worthless. >>>> >>> >>> I'm sorry. >>> >>> I connected to my vServer via putty. >>> >>> I opened my.cnf entering: mcedit /etc/mysql/my.cnf >>> >>> I added to my.cnf, as You told me, as follows >>> >>> [mysqld] >>> # >>> # * Basic Settings >>> # >>> user = mysql >>> pid-file = /var/run/mysqld/mysqld.pid >>> socket = /var/run/mysqld/mysqld.sock >>> port = 3306 >>> basedir = /usr >>> datadir = /var/lib/mysql >>> tmpdir = /tmp >>> language = /usr/share/mysql/german >>> skip-external-locking >>> character_set_client = latin1 >>> #character_set_connection = latin1 # i tried it once with >>> this, but it didn't work at all... >>> >>> >>> By the way, I also set default-character-set at the [client], see: >>> >>> >>> [client] >>> port = 3306 >>> socket = /var/run/mysqld/mysqld.sock >>> #set names = latin1 >>> #character-set-client = latin1 doesn't work, so pitty! >>> #character-set-connection = latin1 doesn't work, so pitty! >>> default-character-set = latin1 >>> #default-collation = latin1_german2_ci doesn't work >>> >>> >>> I hope it's a better posting. I'm sorry once more. >>> >> >> Here is your problem - it is character_set_client, not >> character-set-client. See the difference? >> >> (That's what the message said, also). >> > > I'm sorry, it seems You are not that right. > > Trying to call MySQL at the shell via putty I get following error message: > > v213880518:/# mysql --password=password --user=user > mysql: unknown variable 'character_set_client=latin1' > > It doesn't matter how I use it in [client], neither character_set_client > nor character-set-client work. So that's why they are commented. > > So, once more, there are two blocks in my.cnf: [mysqld] and [client]. > Should I use only one of them to set the charsets or both? And with > which option? > Ah, I missed you had those in [client]. I shouldn't look at code before the first cup of coffee :-). They go in the [mysqld] section. That's where you set the mysql parameters. [client] is for client use. I know it isn't completely intuitive. But that's how it works. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
|
|
06-10-2008, 03:32 PM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Jerry Stuckle schrieb:
> Marc wrote: >> Jerry Stuckle schrieb: >>> Marc wrote: >>>> Jerry Stuckle schrieb: >>>>> Marc wrote: >>>>>> Jerry Stuckle schrieb: >>>>>>> Marc wrote: >>>>>>>> Jerry Stuckle schrieb: >>>>>>>>> Marc wrote: >>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>> Marc wrote: >>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>>>>>> alex schrieb: >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> I made a simple script in PHP to dump data from >>>>>>>>>>>>>>>>>>> tables which had non standard characters. But the >>>>>>>>>>>>>>>>>>> resulting page is viewed correctly in iso-8859-1 >>>>>>>>>>>>>>>>>>> encoding and not in UTF-8. >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Hello, >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I >>>>>>>>>>>>>>>>>> have read all answers and followed the link reading >>>>>>>>>>>>>>>>>> them all carefully. I suppose I understand the matter. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> I have apparently the same problem as Alex has. But my >>>>>>>>>>>>>>>>>> connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> The problem accrued as I've moved from my webspace to >>>>>>>>>>>>>>>>>> a vServer. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> The old configuration shown by phpMyAdmin on MySQL >>>>>>>>>>>>>>>>>> 4.1.13 is: >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> character set client utf8 >>>>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>>>> character set connection utf8 >>>>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>>>> character set database latin1 >>>>>>>>>>>>>>>>>> character set results utf8 >>>>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>>>>>> collation connection utf8_general_ci >>>>>>>>>>>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> The new configuration shown by phpMyAdmin on my new >>>>>>>>>>>>>>>>>> MySQL 5.0.32 is exactly the same, the I set "SET NAMES >>>>>>>>>>>>>>>>>> latin1" via mysql-shell. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> BUT: >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> character_set_client latin1 >>>>>>>>>>>>>>>>>> character_set_connection latin1 >>>>>>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>>>>>> character set results latin1 >>>>>>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> asking for the values by PHP gives: >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> character_set_client utf8 >>>>>>>>>>>>>>>>>> character_set_connection utf8 >>>>>>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>>>>>> character_set_results utf8 >>>>>>>>>>>>>>>>>> character_set_server latin1 >>>>>>>>>>>>>>>>>> character_set_system utf8 >>>>>>>>>>>>>>>>>> collation_connection utf8_general_ci >>>>>>>>>>>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>>>>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> I need the character_set_client and >>>>>>>>>>>>>>>>>> character_set_connection in latin1. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> The HTTP header sends to the browser ISO-8859-15. That >>>>>>>>>>>>>>>>>> should stay so, because about 2.000 html pages are >>>>>>>>>>>>>>>>>> coded in this. I don't care for the DB contect, then I >>>>>>>>>>>>>>>>>> have it local in some programm. My only wish is to >>>>>>>>>>>>>>>>>> establish a working-encoding-connection between the >>>>>>>>>>>>>>>>>> PHP and DB. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Can You please tell what exactly I have to do? Please, >>>>>>>>>>>>>>>>>> understand I can't reprogramm the application, I even >>>>>>>>>>>>>>>>>> don't want to, because it worked before. I'm quit sure >>>>>>>>>>>>>>>>>> it's not correct setted up. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Thanks a lot in advance!!! >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Regards >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Marc >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Oops, I didn't notice you crossposted this to >>>>>>>>>>>>>>>>> comp.databases.mysql. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>>>>>>>>>>> database. The first question is - what charset is the >>>>>>>>>>>>>>>>> data in the database in? And exactly what are these >>>>>>>>>>>>>>>>> "non-standard" characters? >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Hello Jerry, >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> sorry for crossposting. I think it's not only MYSQL >>>>>>>>>>>>>>>> problem, but also PHP problem. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> I have German umlauts in there. I guess the old DB used >>>>>>>>>>>>>>>> latin1 and the new one uses UTF-8 for internal >>>>>>>>>>>>>>>> corresponding. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> As I have found it, it acctually doesn't matter how the >>>>>>>>>>>>>>>> DB corresponds internal, then it's able to encode by >>>>>>>>>>>>>>>> request sent by client. And that's the point, I guess >>>>>>>>>>>>>>>> PHP doesn't say which encoding it would like to use. Or >>>>>>>>>>>>>>>> maybe it's enough to tell DB which encoding it should >>>>>>>>>>>>>>>> normally use talking to anything from outside. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Right? >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> So, I'm sorry for crossposting but I guess it's a >>>>>>>>>>>>>>>> mysql-php-problem. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Thanks a lot for Your responce. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Regards, >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Marc >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Marc, >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> No, it's my fault for not noticing the cross-posting in >>>>>>>>>>>>>>> the first place. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> You probably now have a mismatch between your connection >>>>>>>>>>>>>>> and your table. Besides the charset used by the table, >>>>>>>>>>>>>>> you have a charset used by the connection. I suspect you >>>>>>>>>>>>>>> have a mismatch here. After connecting, call: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> This should also set your connection to latin1. >>>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> Jerry, >>>>>>>>>>>>>> >>>>>>>>>>>>>> that's what I guess the problem is. >>>>>>>>>>>>>> >>>>>>>>>>>>>> I've tried to call the command at the mysql-shell... and >>>>>>>>>>>>>> got error 1064 (42000). Should it be called via the php >>>>>>>>>>>>>> script perhaps? That's not that comfortable way for me... >>>>>>>>>>>>>> >>>>>>>>>>>>>> Regards, >>>>>>>>>>>>>> >>>>>>>>>>>>>> Marc >>>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> This statement is connection specific. You need to call it >>>>>>>>>>>>> after connecting, and every time after connecting. Calling >>>>>>>>>>>>> it from the command line only sets it for that connection; >>>>>>>>>>>>> it won't set it for your PHP script, since it uses a >>>>>>>>>>>>> different connection. >>>>>>>>>>>>> >>>>>>>>>>>>> But your error message by itself is meaningless. What is >>>>>>>>>>>>> the statement you're entering, and what is the entire error >>>>>>>>>>>>> message you get? It works fine for me. >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> I have connected with my server via putty, have logged in. >>>>>>>>>>>> Then logged in into mysql: >>>>>>>>>>>> >>>>>>>>>>>> # mysql --password=mypassword --user=myuser [enter] >>>>>>>>>>>> >>>>>>>>>>>> Welcome to the MySQL monitor. Commands end with ; or \g. >>>>>>>>>>>> Your MySQL connection id is 246 >>>>>>>>>>>> Server version: 5.0.32-Debian_7etch5-log Debian etch >>>>>>>>>>>> distribution >>>>>>>>>>>> >>>>>>>>>>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>>>>>>>>>>> >>>>>>>>>>>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>>>>>>>>>>> >>>>>>>>>>>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die >>>>>>>>>>>> korrekte Syntax im Handbuch nachschlagen bei >>>>>>>>>>>> 'mysql_query("SET NAMES 'latin1'")' in Zeile 1 >>>>>>>>>>>> mysql> >>>>>>>>>>>> >>>>>>>>>>>> How can I change the connection permamently for php? Is it >>>>>>>>>>>> possible? Without changing the scripts... >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> No, mysql_query() is a PHP function call, not a MySQL >>>>>>>>>>> statement. What you need is just >>>>>>>>>>> >>>>>>>>>>> mysql>SET NAMES 'latin1'; >>>>>>>>>>> >>>>>>>>>>> The easiest way to change startup values is to go into MySQL >>>>>>>>>>> Administrator, go to Service Control and click on the >>>>>>>>>>> Configure Service tab. Then set the Localization section to >>>>>>>>>>> the charset you wish. >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> I have added this call to my php script. It didn't work! >>>>>>>>>> >>>>>>>>>> I use MySQL on Linux! So there is no Service Control... >>>>>>>>>> >>>>>>>>>> I don't understand it, the MySQL docs say the standard install >>>>>>>>>> comes with latin1... But it seems to be not a rule... >>>>>>>>>> >>>>>>>>>> Maybe I should recompile MySQL?! >>>>>>>>>> >>>>>>>>>> Thanks a lot for Your hints! >>>>>>>>>> >>>>>>>>> >>>>>>>>> Marc, >>>>>>>>> >>>>>>>>> Since you changed servers, the other possibility is the charset >>>>>>>>> the web page itself is sent as. What is the character encoding >>>>>>>>> your web server is sending for the page? >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> At the beginning there was file 'charset' with AddDefaultCharset >>>>>>>> UTF-8 at my apache2 config dir. So I didn't get it I have this >>>>>>>> problem. But then I mentioned umlaut problems at the admin >>>>>>>> backend, so I've begun to look for the source of it. After >>>>>>>> deleting this file, I have find out, that DB data won't be shown >>>>>>>> correctly. >>>>>>>> >>>>>>>> The HTML pages have a HEADER with ISO-8859-15. >>>>>>>> >>>>>>>> I have set a new 'charset' with ISO encoding - no result. >>>>>>>> >>>>>>> >>>>>>> OK, let's back up a bit. What was the charset you used on the >>>>>>> previous system? Was it actually latin1? Or could it have been >>>>>>> something else? >>>>>>> >>>>>>> Also, how did you move your data from one system to another? >>>>>>> >>>>>>> Finally - to set the charset permanently, put it in your my.cfg >>>>>>> file under the [mysqld] section, i.e. >>>>>>> >>>>>>> character_set_client = latin1 >>>>>>> >>>>>> >>>>>> Well, to say the truth I have no idea what the previous system was >>>>>> set like. I can access it only via Plesk, and the MySQL via >>>>>> phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin I >>>>>> have posted already. If there is anything else I can find out, >>>>>> please tell me what and where. >>>>>> >>>>>> I made an export by phpMyAdmin and then called following on the >>>>>> mysql-shell at the new system: >>>>>> >>>>>> /usr/bin/mysql --host=localhost --password=password --user=user >>>>>> DBname < /home/user/DBname.sql >>>>>> >>>>>> Viewing the export file via SuperEdi all umlauts are correct and >>>>>> it seems all data is latin1-encoded, there is always this >>>>>> statement in there: CREATE DATABASE `shop` DEFAULT CHARACTER SET >>>>>> latin1 COLLATE latin1_swedish_ci; or DEFAULT CHARSET=latin1 in the >>>>>> tables. >>>>>> >>>>>> >>>>>> Viewing the old DB all umlauts are OK, doing it at the new one, >>>>>> none is OK. >>>>>> >>>>> >>>>> Yes, but chances are you used utf-8 to do the import, since that >>>>> was the default for the connection. >>>> >>>> Yes, that's probabely the point. I still don't know how to set it to >>>> latin1. >>>> >>>>>> >>>>>> I have tried to set the charset permanently, but the server says >>>>>> following after restart: >>>>>> >>>>>> >>>>>> Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started >>>>>> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 >>>>>> [Warning] /usr/sbin/mysqld: ignoring option '--character-set-client$ >>>>>> Jun 10 10:13:15 v213880518 mysqld[20563]: >>>>>> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 InnoDB: >>>>>> Started; log sequence number 0 2196292 >>>>>> Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note] >>>>>> /usr/sbin/mysqld: bereit für Verbindungen. >>>>>> Jun 10 10:13:16 v213880518 mysqld[20563]: Version: >>>>>> '5.0.32-Debian_7etch5-log' Socket: '/var/run/mysqld/mysqld.sock' >>>>>> Port: 3$ >>>>>> >>>>>> >>>>>> So, I habe no idea why it doesn't work. >>>>>> >>>>> >>>>> How did you try to change the charset? And what *exactly* did you >>>>> enter? Again - a message without knowing exactly what you did is >>>>> worthless. >>>>> >>>> >>>> I'm sorry. >>>> >>>> I connected to my vServer via putty. >>>> >>>> I opened my.cnf entering: mcedit /etc/mysql/my.cnf >>>> >>>> I added to my.cnf, as You told me, as follows >>>> >>>> [mysqld] >>>> # >>>> # * Basic Settings >>>> # >>>> user = mysql >>>> pid-file = /var/run/mysqld/mysqld.pid >>>> socket = /var/run/mysqld/mysqld.sock >>>> port = 3306 >>>> basedir = /usr >>>> datadir = /var/lib/mysql >>>> tmpdir = /tmp >>>> language = /usr/share/mysql/german >>>> skip-external-locking >>>> character_set_client = latin1 >>>> #character_set_connection = latin1 # i tried it once with >>>> this, but it didn't work at all... >>>> >>>> >>>> By the way, I also set default-character-set at the [client], see: >>>> >>>> >>>> [client] >>>> port = 3306 >>>> socket = /var/run/mysqld/mysqld.sock >>>> #set names = latin1 >>>> #character-set-client = latin1 doesn't work, so pitty! >>>> #character-set-connection = latin1 doesn't work, so pitty! >>>> default-character-set = latin1 >>>> #default-collation = latin1_german2_ci doesn't work >>>> >>>> >>>> I hope it's a better posting. I'm sorry once more. >>>> >>> >>> Here is your problem - it is character_set_client, not >>> character-set-client. See the difference? >>> >>> (That's what the message said, also). >>> >> >> I'm sorry, it seems You are not that right. >> >> Trying to call MySQL at the shell via putty I get following error >> message: >> >> v213880518:/# mysql --password=password --user=user >> mysql: unknown variable 'character_set_client=latin1' >> >> It doesn't matter how I use it in [client], neither >> character_set_client nor character-set-client work. So that's why they >> are commented. >> >> So, once more, there are two blocks in my.cnf: [mysqld] and [client]. >> Should I use only one of them to set the charsets or both? And with >> which option? >> > > Ah, I missed you had those in [client]. I shouldn't look at code before > the first cup of coffee :-). > > They go in the [mysqld] section. That's where you set the mysql > parameters. [client] is for client use. > > I know it isn't completely intuitive. But that's how it works. > You are absolutely right, it's not intuitive, not at all. ;-) I'm reading again the MySQL docs, chapter 9th, Internationalization and Localization. I'm also studying again: http://www.drummy.org/archives/37/1/ - an explaintion about the umlaut problem comming from the mySQLDumper developer. I have to loose it, before I get sick Thanks a lot for all Your hints!
|
|
06-10-2008, 07:26 PM
|
|||
|
|||
|
Re: Mysql database in UTF8, PHP shoud show latin1, but it doesn't
Greetings, Marc.
In reply to Your message dated Tuesday, June 10, 2008, 13:51:36, > AnrDaemon schrieb: >> Greetings, Marc. >> In reply to Your message dated Tuesday, June 10, 2008, 01:45:36, >> >>>>>>>> You have the answer, but apparently lost it in your explanation. I'll quote: >>>>>>>> >>>>>>>>> The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is >>>>>>>>> exactly the same, the I set "SET NAMES latin1" via mysql-shell. >>>>>>>> You must tell to server, what encoding you want, every time you connecting to >>>>>>>> database. >>>>>>>> Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need' >>>>>>>> statement right after the moment you have connected to database in your script. >>>>>>> thanks a lot for this advice. But it's not possible to change the whole >>>>>>> project for this. That's why I would like to set it generally up. On the >>>>>>> other side, I don't need any other encoding. >>>>>> You lost the point. >>>>>> It is not "changing the whole project", it is the action that project must do >>>>>> once in only one script - one that establishing connection to database. >>>>>> It is, literally, one line of code to add to whole your project, and it will >>>>>> serve you to the end of times. >>>>> that seems that easy, but it cannot be the cleanest way. I don't feel so >>>>> comfortable about it. It has worked already. I just would like to set >>>>> everything right up. >>>> It has worked already just because server was configured to work that way. >>>> It was a 4.0 server most likely, or server config have had an init_connect >>>> option set. Your argument "it was worked already" pointless, because >>>> environment have changed. >>>> >>>> You have only two ways to solve your problem. >>>> 1. I think it is right way. >>>> Adapt environment for your needs (by changing connection encoding every time >>>> you connect to database, it is one line of code in one single file, or in few >>>> files, if you are not using some sort of abstraction layer) - it will work as >>>> long as you are using MySQL servers for your database. They may have any >>>> settings on their own, but you'll have what you need in your script all the >>>> time. >>>> >>>> 2. Hard and one that I not considering acceptable for myself. >>>> Change your scripts to handle current database output encoding. It will strike >>>> just about every line of your code, in simple words - it will require to >>>> rewrite about half of your project. >>>> >>>>> I'm sending the data from some external DB by one php script and I'm >>>>> trying to add this simple line to it... It's gonna take some time, I to >>>>> new on this stuff. I'm gonna report. >>>> It's simple as find-replace >>>> Locate the point in your (key word - your) script, where you are using >>>> mysql_connect(). >>>> Add >>>> mysql_query('SET NAMES latin1'); >>>> Right after calling mysql_connect(). >>>> >>>> It should work. >>>> >>>> If you are using some sort of abstraction layer, it may be a bit different, >>>> but not harder. >>>> >> >> >>> Adding mysql_query didn't work, I mean it didn't change anything. The >>> Umlauts are still wrong. >> >> You're stating your troubles without any confirmation. >> Please show us few lines of your code where you have changed it, and explain, >> how are you tested that it "didn't change anything". >> >> I think you have checked it with your other script, which have not been >> changed, thus not showing you what you want. >> >>> I'm really fed up. It makes me sick. >> >> I have feeling of your anger, but that's not what makes man wise. >> >> > I'm really sorry for giving my angery. > I use xt commerce. There are only 2 files calling mysql_connect(). So I > have done what You have told, I have three times added mysql_query... Well, you have it done almost the right way, but I'll change it a bit for more correct handling of possible errors. function xtc_db_connect($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') { global $$link; if (USE_PCONNECT == 'true') { $$link = mysql_pconnect($server, $username, $password); } else { $$link = mysql_connect($server, $username, $password); } if ($$link) { // Set encoding if we successfully connected to database mysql_query('SET NAMES latin1', $$link); mysql_select_db($database); } return $$link; } -- Sincerely Yours, AnrDaemon <anrdaemon@freemail.ru>
|
|
06-12-2008, 03:01 PM
|
|||
|
|||
|
After a server crash
AnrDaemon schrieb:
> Greetings, Marc. > In reply to Your message dated Tuesday, June 10, 2008, 13:51:36, > >> AnrDaemon schrieb: >>> Greetings, Marc. >>> In reply to Your message dated Tuesday, June 10, 2008, 01:45:36, >>> >>>>>>>>> You have the answer, but apparently lost it in your explanation. I'll quote: >>>>>>>>> >>>>>>>>>> The new configuration shown by phpMyAdmin on my new MySQL 5.0.32 is >>>>>>>>>> exactly the same, the I set "SET NAMES latin1" via mysql-shell. >>>>>>>>> You must tell to server, what encoding you want, every time you connecting to >>>>>>>>> database. >>>>>>>>> Just add execution of 'SET NAMES latin1 COLLATE whatever_collation_you_need' >>>>>>>>> statement right after the moment you have connected to database in your script. >>>>>>>> thanks a lot for this advice. But it's not possible to change the whole >>>>>>>> project for this. That's why I would like to set it generally up. On the >>>>>>>> other side, I don't need any other encoding. >>>>>>> You lost the point. >>>>>>> It is not "changing the whole project", it is the action that project must do >>>>>>> once in only one script - one that establishing connection to database. >>>>>>> It is, literally, one line of code to add to whole your project, and it will >>>>>>> serve you to the end of times. >>>>>> that seems that easy, but it cannot be the cleanest way. I don't feel so >>>>>> comfortable about it. It has worked already. I just would like to set >>>>>> everything right up. >>>>> It has worked already just because server was configured to work that way. >>>>> It was a 4.0 server most likely, or server config have had an init_connect >>>>> option set. Your argument "it was worked already" pointless, because >>>>> environment have changed. >>>>> >>>>> You have only two ways to solve your problem. >>>>> 1. I think it is right way. >>>>> Adapt environment for your needs (by changing connection encoding every time >>>>> you connect to database, it is one line of code in one single file, or in few >>>>> files, if you are not using some sort of abstraction layer) - it will work as >>>>> long as you are using MySQL servers for your database. They may have any >>>>> settings on their own, but you'll have what you need in your script all the >>>>> time. >>>>> >>>>> 2. Hard and one that I not considering acceptable for myself. >>>>> Change your scripts to handle current database output encoding. It will strike >>>>> just about every line of your code, in simple words - it will require to >>>>> rewrite about half of your project. >>>>> >>>>>> I'm sending the data from some external DB by one php script and I'm >>>>>> trying to add this simple line to it... It's gonna take some time, I to >>>>>> new on this stuff. I'm gonna report. >>>>> It's simple as find-replace >>>>> Locate the point in your (key word - your) script, where you are using >>>>> mysql_connect(). >>>>> Add >>>>> mysql_query('SET NAMES latin1'); >>>>> Right after calling mysql_connect(). >>>>> >>>>> It should work. >>>>> >>>>> If you are using some sort of abstraction layer, it may be a bit different, >>>>> but not harder. >>>>> >>> >>>> Adding mysql_query didn't work, I mean it didn't change anything. The >>>> Umlauts are still wrong. >>> You're stating your troubles without any confirmation. >>> Please show us few lines of your code where you have changed it, and explain, >>> how are you tested that it "didn't change anything". >>> >>> I think you have checked it with your other script, which have not been >>> changed, thus not showing you what you want. >>> >>>> I'm really fed up. It makes me sick. >>> I have feeling of your anger, but that's not what makes man wise. >>> >>> > >> I'm really sorry for giving my angery. > >> I use xt commerce. There are only 2 files calling mysql_connect(). So I >> have done what You have told, I have three times added mysql_query... > > Well, you have it done almost the right way, but I'll change it a bit for more > correct handling of possible errors. > > function xtc_db_connect($server = DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE, $link = 'db_link') { > global $$link; > > if (USE_PCONNECT == 'true') { > $$link = mysql_pconnect($server, $username, $password); > } else { > $$link = mysql_connect($server, $username, $password); > } > > if ($$link) { > // Set encoding if we successfully connected to database > mysql_query('SET NAMES latin1', $$link); > > mysql_select_db($database); > } > > return $$link; > } > > Thanks a lot for Your help. It didn't work, I don't really now why. Last night I had a server crash, a very bad one. I set the server new up and was more then very carefull importing my old DB to MySQL. It works now. My export file was utf-encoded. I saved it as ANSI. Then I copied it on my system calling >mysql --host=localhost --password=pd --user=u --default-character-set=latin1 < /root/db.sql. On this way I got a clean DB. Then I changed in apache2 settings dir the charset file, from AddDefaultCharset UTF-8 to AddDefaultCharset ISO-8859-1. After it I uploaded all my files. It works now. Really, thanks a lot!! Regards, Marc
|
|
06-12-2008, 03:02 PM
|
|||
|
|||
|
After a server crash
Marc schrieb:
> Jerry Stuckle schrieb: >> Marc wrote: >>> Jerry Stuckle schrieb: >>>> Marc wrote: >>>>> Jerry Stuckle schrieb: >>>>>> Marc wrote: >>>>>>> Jerry Stuckle schrieb: >>>>>>>> Marc wrote: >>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>> Marc wrote: >>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>>>>> Jerry Stuckle schrieb: >>>>>>>>>>>>>>>>>> Marc wrote: >>>>>>>>>>>>>>>>>>> alex schrieb: >>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>> I made a simple script in PHP to dump data from >>>>>>>>>>>>>>>>>>>> tables which had non standard characters. But the >>>>>>>>>>>>>>>>>>>> resulting page is viewed correctly in iso-8859-1 >>>>>>>>>>>>>>>>>>>> encoding and not in UTF-8. >>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>> At this moment i'm clueless were the problem lies. >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> Hello, >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> well, first of all, I'm newbie with MYSQL & Co., I >>>>>>>>>>>>>>>>>>> have read all answers and followed the link reading >>>>>>>>>>>>>>>>>>> them all carefully. I suppose I understand the matter. >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> I have apparently the same problem as Alex has. But >>>>>>>>>>>>>>>>>>> my connection is in UTF-8 and I need ISO-8859-1. >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> The problem accrued as I've moved from my webspace to >>>>>>>>>>>>>>>>>>> a vServer. >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> The old configuration shown by phpMyAdmin on MySQL >>>>>>>>>>>>>>>>>>> 4.1.13 is: >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> character set client utf8 >>>>>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>>>>> character set connection utf8 >>>>>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>>>>> character set database latin1 >>>>>>>>>>>>>>>>>>> character set results utf8 >>>>>>>>>>>>>>>>>>> (Globaler Wert) latin1 >>>>>>>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>>>>>>> collation connection utf8_general_ci >>>>>>>>>>>>>>>>>>> (Globaler Wert) latin1_swedish_ci >>>>>>>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> The new configuration shown by phpMyAdmin on my new >>>>>>>>>>>>>>>>>>> MySQL 5.0.32 is exactly the same, the I set "SET >>>>>>>>>>>>>>>>>>> NAMES latin1" via mysql-shell. >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> BUT: >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> Making "SHOW VARIABLES" at mysql-shell gives: >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> character_set_client latin1 >>>>>>>>>>>>>>>>>>> character_set_connection latin1 >>>>>>>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>>>>>>> character set results latin1 >>>>>>>>>>>>>>>>>>> character set server latin1 >>>>>>>>>>>>>>>>>>> character set system utf8 >>>>>>>>>>>>>>>>>>> collation connection latin1_swedish_ci >>>>>>>>>>>>>>>>>>> collation database latin1_swedish_ci >>>>>>>>>>>>>>>>>>> collation server latin1_swedish_ci >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> asking for the values by PHP gives: >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> character_set_client utf8 >>>>>>>>>>>>>>>>>>> character_set_connection utf8 >>>>>>>>>>>>>>>>>>> character_set_database latin1 >>>>>>>>>>>>>>>>>>> character_set_filesystem binary >>>>>>>>>>>>>>>>>>> character_set_results utf8 >>>>>>>>>>>>>>>>>>> character_set_server latin1 >>>>>>>>>>>>>>>>>>> character_set_system utf8 >>>>>>>>>>>>>>>>>>> collation_connection utf8_general_ci >>>>>>>>>>>>>>>>>>> collation_database latin1_swedish_ci >>>>>>>>>>>>>>>>>>> collation_server latin1_swedish_ci >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> I need the character_set_client and >>>>>>>>>>>>>>>>>>> character_set_connection in latin1. >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> The HTTP header sends to the browser ISO-8859-15. >>>>>>>>>>>>>>>>>>> That should stay so, because about 2.000 html pages >>>>>>>>>>>>>>>>>>> are coded in this. I don't care for the DB contect, >>>>>>>>>>>>>>>>>>> then I have it local in some programm. My only wish >>>>>>>>>>>>>>>>>>> is to establish a working-encoding-connection between >>>>>>>>>>>>>>>>>>> the PHP and DB. >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> Can You please tell what exactly I have to do? >>>>>>>>>>>>>>>>>>> Please, understand I can't reprogramm the >>>>>>>>>>>>>>>>>>> application, I even don't want to, because it worked >>>>>>>>>>>>>>>>>>> before. I'm quit sure it's not correct setted up. >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> Thanks a lot in advance!!! >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> Regards >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>> Marc >>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Oops, I didn't notice you crossposted this to >>>>>>>>>>>>>>>>>> comp.databases.mysql. >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> You indicated you had "non-standard" characters in the >>>>>>>>>>>>>>>>>> database. The first question is - what charset is the >>>>>>>>>>>>>>>>>> data in the database in? And exactly what are these >>>>>>>>>>>>>>>>>> "non-standard" characters? >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Hello Jerry, >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> sorry for crossposting. I think it's not only MYSQL >>>>>>>>>>>>>>>>> problem, but also PHP problem. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> I have German umlauts in there. I guess the old DB used >>>>>>>>>>>>>>>>> latin1 and the new one uses UTF-8 for internal >>>>>>>>>>>>>>>>> corresponding. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> As I have found it, it acctually doesn't matter how the >>>>>>>>>>>>>>>>> DB corresponds internal, then it's able to encode by >>>>>>>>>>>>>>>>> request sent by client. And that's the point, I guess >>>>>>>>>>>>>>>>> PHP doesn't say which encoding it would like to use. Or >>>>>>>>>>>>>>>>> maybe it's enough to tell DB which encoding it should >>>>>>>>>>>>>>>>> normally use talking to anything from outside. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Right? >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> So, I'm sorry for crossposting but I guess it's a >>>>>>>>>>>>>>>>> mysql-php-problem. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Thanks a lot for Your responce. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Regards, >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Marc >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Marc, >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> No, it's my fault for not noticing the cross-posting in >>>>>>>>>>>>>>>> the first place. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> You probably now have a mismatch between your connection >>>>>>>>>>>>>>>> and your table. Besides the charset used by the table, >>>>>>>>>>>>>>>> you have a charset used by the connection. I suspect >>>>>>>>>>>>>>>> you have a mismatch here. After connecting, call: >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> mysql_query("SET NAMES 'latin1'"); >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> This should also set your connection to latin1. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Jerry, >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> that's what I guess the problem is. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> I've tried to call the command at the mysql-shell... and >>>>>>>>>>>>>>> got error 1064 (42000). Should it be called via the php >>>>>>>>>>>>>>> script perhaps? That's not that comfortable way for me... >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Regards, >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Marc >>>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> This statement is connection specific. You need to call >>>>>>>>>>>>>> it after connecting, and every time after connecting. >>>>>>>>>>>>>> Calling it from the command line only sets it for that >>>>>>>>>>>>>> connection; it won't set it for your PHP script, since it >>>>>>>>>>>>>> uses a different connection. >>>>>>>>>>>>>> >>>>>>>>>>>>>> But your error message by itself is meaningless. What is >>>>>>>>>>>>>> the statement you're entering, and what is the entire >>>>>>>>>>>>>> error message you get? It works fine for me. >>>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> I have connected with my server via putty, have logged in. >>>>>>>>>>>>> Then logged in into mysql: >>>>>>>>>>>>> >>>>>>>>>>>>> # mysql --password=mypassword --user=myuser [enter] >>>>>>>>>>>>> >>>>>>>>>>>>> Welcome to the MySQL monitor. Commands end with ; or \g. >>>>>>>>>>>>> Your MySQL connection id is 246 >>>>>>>>>>>>> Server version: 5.0.32-Debian_7etch5-log Debian etch >>>>>>>>>>>>> distribution >>>>>>>>>>>>> >>>>>>>>>>>>> Type 'help;' or '\h' for help. Type '\c' to clear the buffer. >>>>>>>>>>>>> >>>>>>>>>>>>> mysql> mysql_query("SET NAMES 'latin1'"); [enter] >>>>>>>>>>>>> >>>>>>>>>>>>> ERROR 1064 (42000): Fehler in der SQL-Syntax. Bitte die >>>>>>>>>>>>> korrekte Syntax im Handbuch nachschlagen bei >>>>>>>>>>>>> 'mysql_query("SET NAMES 'latin1'")' in Zeile 1 >>>>>>>>>>>>> mysql> >>>>>>>>>>>>> >>>>>>>>>>>>> How can I change the connection permamently for php? Is it >>>>>>>>>>>>> possible? Without changing the scripts... >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> No, mysql_query() is a PHP function call, not a MySQL >>>>>>>>>>>> statement. What you need is just >>>>>>>>>>>> >>>>>>>>>>>> mysql>SET NAMES 'latin1'; >>>>>>>>>>>> >>>>>>>>>>>> The easiest way to change startup values is to go into MySQL >>>>>>>>>>>> Administrator, go to Service Control and click on the >>>>>>>>>>>> Configure Service tab. Then set the Localization section to >>>>>>>>>>>> the charset you wish. >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> I have added this call to my php script. It didn't work! >>>>>>>>>>> >>>>>>>>>>> I use MySQL on Linux! So there is no Service Control... >>>>>>>>>>> >>>>>>>>>>> I don't understand it, the MySQL docs say the standard >>>>>>>>>>> install comes with latin1... But it seems to be not a rule... >>>>>>>>>>> >>>>>>>>>>> Maybe I should recompile MySQL?! >>>>>>>>>>> >>>>>>>>>>> Thanks a lot for Your hints! >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Marc, >>>>>>>>>> >>>>>>>>>> Since you changed servers, the other possibility is the >>>>>>>>>> charset the web page itself is sent as. What is the character >>>>>>>>>> encoding your web server is sending for the page? >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> At the beginning there was file 'charset' with >>>>>>>>> AddDefaultCharset UTF-8 at my apache2 config dir. So I didn't >>>>>>>>> get it I have this problem. But then I mentioned umlaut >>>>>>>>> problems at the admin backend, so I've begun to look for the >>>>>>>>> source of it. After deleting this file, I have find out, that >>>>>>>>> DB data won't be shown correctly. >>>>>>>>> >>>>>>>>> The HTML pages have a HEADER with ISO-8859-15. >>>>>>>>> >>>>>>>>> I have set a new 'charset' with ISO encoding - no result. >>>>>>>>> >>>>>>>> >>>>>>>> OK, let's back up a bit. What was the charset you used on the >>>>>>>> previous system? Was it actually latin1? Or could it have been >>>>>>>> something else? >>>>>>>> >>>>>>>> Also, how did you move your data from one system to another? >>>>>>>> >>>>>>>> Finally - to set the charset permanently, put it in your my.cfg >>>>>>>> file under the [mysqld] section, i.e. >>>>>>>> >>>>>>>> character_set_client = latin1 >>>>>>>> >>>>>>> >>>>>>> Well, to say the truth I have no idea what the previous system >>>>>>> was set like. I can access it only via Plesk, and the MySQL via >>>>>>> phpMyAdmin 2.8.2.4. The system variables received via phpMyAdmin >>>>>>> I have posted already. If there is anything else I can find out, >>>>>>> please tell me what and where. >>>>>>> >>>>>>> I made an export by phpMyAdmin and then called following on the >>>>>>> mysql-shell at the new system: >>>>>>> >>>>>>> /usr/bin/mysql --host=localhost --password=password --user=user >>>>>>> DBname < /home/user/DBname.sql >>>>>>> >>>>>>> Viewing the export file via SuperEdi all umlauts are correct and >>>>>>> it seems all data is latin1-encoded, there is always this >>>>>>> statement in there: CREATE DATABASE `shop` DEFAULT CHARACTER SET >>>>>>> latin1 COLLATE latin1_swedish_ci; or DEFAULT CHARSET=latin1 in >>>>>>> the tables. >>>>>>> >>>>>>> >>>>>>> Viewing the old DB all umlauts are OK, doing it at the new one, >>>>>>> none is OK. >>>>>>> >>>>>> >>>>>> Yes, but chances are you used utf-8 to do the import, since that >>>>>> was the default for the connection. >>>>> >>>>> Yes, that's probabely the point. I still don't know how to set it >>>>> to latin1. >>>>> >>>>>>> >>>>>>> I have tried to set the charset permanently, but the server says >>>>>>> following after restart: >>>>>>> >>>>>>> >>>>>>> Jun 10 10:13:15 v213880518 mysqld_safe[20556]: started >>>>>>> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 >>>>>>> [Warning] /usr/sbin/mysqld: ignoring option '--character-set-client$ >>>>>>> Jun 10 10:13:15 v213880518 mysqld[20563]: >>>>>>> Jun 10 10:13:15 v213880518 mysqld[20563]: 080610 10:13:15 >>>>>>> InnoDB: Started; log sequence number 0 2196292 >>>>>>> Jun 10 10:13:16 v213880518 mysqld[20563]: 080610 10:13:16 [Note] >>>>>>> /usr/sbin/mysqld: bereit für Verbindungen. >>>>>>> Jun 10 10:13:16 v213880518 mysqld[20563]: Version: >>>>>>> '5.0.32-Debian_7etch5-log' Socket: >>>>>>> '/var/run/mysqld/mysqld.sock' Port: 3$ >>>>>>> >>>>>>> >>>>>>> So, I habe no idea why it doesn't work. >>>>>>> >>>>>> >>>>>> How did you try to change the charset? And what *exactly* did you >>>>>> enter? Again - a message without knowing exactly what you did is >>>>>> worthless. >>>>>> >>>>> >>>>> I'm sorry. >>>>> >>>>> I connected to my vServer via putty. >>>>> >>>>> I opened my.cnf entering: mcedit /etc/mysql/my.cnf >>>>> >>>>> I added to my.cnf, as You told me, as follows >>>>> >>>>> [mysqld] >>>>> # >>>>> # * Basic Settings >>>>> # >>>>> user = mysql >>>>> pid-file = /var/run/mysqld/mysqld.pid >>>>> socket = /var/run/mysqld/mysqld.sock >>>>> port = 3306 >>>>> basedir = /usr >>>>> datadir = /var/lib/mysql >>>>> tmpdir = /tmp >>>>> language = /usr/share/mysql/german >>>>> skip-external-locking >>>>> character_set_client = latin1 >>>>> #character_set_connection = latin1 # i tried it once with >>>>> this, but it didn't work at all... >>>>> >>>>> >>>>> By the way, I also set default-character-set at the [client], see: >>>>> >>>>> >>>>> [client] >>>>> port = 3306 >>>>> socket = /var/run/mysqld/mysqld.sock >>>>> #set names = latin1 >>>>> #character-set-client = latin1 doesn't work, so pitty! >>>>> #character-set-connection = latin1 doesn't work, so pitty! >>>>> default-character-set = latin1 >>>>> #default-collation = latin1_german2_ci doesn't work >>>>> >>>>> >>>>> I hope it's a better posting. I'm sorry once more. >>>>> >>>> >>>> Here is your problem - it is character_set_client, not >>>> character-set-client. See the difference? >>>> >>>> (That's what the message said, also). >>>> >>> >>> I'm sorry, it seems You are not that right. >>> >>> Trying to call MySQL at the shell via putty I get following error >>> message: >>> >>> v213880518:/# mysql --password=password --user=user >>> mysql: unknown variable 'character_set_client=latin1' >>> >>> It doesn't matter how I use it in [client], neither >>> character_set_client nor character-set-client work. So that's why >>> they are commented. >>> >>> So, once more, there are two blocks in my.cnf: [mysqld] and [client]. >>> Should I use only one of them to set the charsets or both? And with >>> which option? >>> >> >> Ah, I missed you had those in [client]. I shouldn't look at code >> before the first cup of coffee :-). >> >> They go in the [mysqld] section. That's where you set the mysql >> parameters. [client] is for client use. >> >> I know it isn't completely intuitive. But that's how it works. >> > > > You are absolutely right, it's not intuitive, not at all. ;-) > > I'm reading again the MySQL docs, chapter 9th, Internationalization and > Localization. > > I'm also studying again: http://www.drummy.org/archives/37/1/ - an > explaintion about the umlaut problem comming from the mySQLDumper > developer. > > I have to loose it, before I get sick > > Thanks a lot for all Your hints! Thanks a lot for Your help. It didn't work, I don't really now why. Last night I had a server crash, a very bad one. I set the server new up and was more then very carefull importing my old DB to MySQL. It works now. My export file was utf-encoded. I saved it as ANSI. Then I copied it on my system calling >mysql --host=localhost --password=pd --user=u --default-character-set=latin1 < /root/db.sql. On this way I got a clean DB. Then I changed in apache2 settings dir the charset file, from AddDefaultCharset UTF-8 to AddDefaultCharset ISO-8859-1. After it I uploaded all my files. It works now. Really, thanks a lot!! Regards, Marc PS: sorry for doubleposting!
|
|
|
 |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Latin1 to UTF8 Conversion | Craig Hutchinson | Newsgroup comp.databases.mysql | 5 | 03-31-2009 07:26 PM |
| Re: Database Lock vs Database Freeze | data _null_; | Newsgroup comp.soft-sys.sas | 0 | 04-24-2007 01:29 PM |
| Re: Database Lock vs Database Freeze | Bob_Abelson@HGSI.COM | Newsgroup comp.soft-sys.sas | 0 | 04-24-2007 01:20 PM |
Linear Mode
