| Bug #17458 | SET NAMES not working properly on Windows | ||
|---|---|---|---|
| Submitted: | 16 Feb 2006 14:18 | Modified: | 28 May 2006 18:27 |
| Reporter: | Erik Fleischer | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.18 | OS: | Windows (Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
[17 Feb 2006 12:57]
Valeriy Kravchuk
Thank you for a problem report. Should it be MySQL server on Windows or PHP script on Windows to demonstrate the bug? Can you provide a dump for the table used in that test script?
[17 Feb 2006 17:00]
Erik Fleischer
Q: Should it be MySQL server on Windows or PHP script on Windows to demonstrate the bug? The problem reported occurs on a Windows (XP) test server where both the web server (Apache 2.0.55 with PHP 5.1.2) and the MySQL server (5.0.18) are running. When the exact same same code with the exact same data is run on a production Linux server (Apache 1.3.34, PHP 5.1.2, MySQL 4.1.14) this problem does NOT occur. I have conducted several tests that suggest that PHP has nothing to do with the problem. Apache could be responsible, but I don't really see how, so I'm placing the blame on MySQL. Q: Can you provide a dump for the table used in that test script? Certainly (see file I've uploaded), but please don't make it public as this is real data from a client's database. Also, the PHP code I provided in my report was slightly changed, because the original table and table field names are in Portuguese; I've also uploaded a PHP file that will reproduce the problem reported with the data supplied (provided the constant definitions for database connection are set appropriately).
[20 Feb 2006 20:09]
Peter Laursen
@Erik What are the PHP versions? I have had the most terrible problems with PHP 4.4.2 and 5.1.2. Similar issues with almost any non-english character! With PHP 4.3.2 I have no problems. May I guess that PHP on your LINUX machine is an older version than on Windows? Could be that latest PHP builds or PHP-connectors are buggy! (I am using the connectors distributed by MySQL)
[20 Feb 2006 20:17]
Peter Laursen
However my problems are WRITE problems with PHP. And I can write BLOBs perfectly, but not TEXTs and VARCHARS.
[20 Feb 2006 20:21]
Andrey Hristov
@Peter: Is the varchar varbinary? What exact problems do you experience during write operations?
[20 Feb 2006 21:13]
Erik Fleischer
As I mentioned in my previous post, both my test server and the production server are currently running PHP 5.1.2. I don't believe PHP has anything to do with the problem I reported because the ONLY thing that seems to be changing the encoding of my strings in I/O operations (client-server and server-client) is the "SET NAMES" command issued to MySQL on Windows (on the Linux production server SET NAMES 'utf8' works as expected and everything runs smoothly). Please not that EVERYTHING in this specific environment is UTF8-encoded: PHP files, MySQL database, all database tables, user interface (XHTML files), even JavaScript files. PHP is not very UTF8-friendly, but it doesn't change the encoding of any string: strings originally in ISO-8859-1 (a.k.a. Latin 1/Western European) stay that way, as do UTF-8 strings. I have VERY strongs reasons to believe that MySQL (on Windows) is the culprit in this case.
[21 Feb 2006 1:39]
Erik Fleischer
I've been insisting that the problem I reported only occurs on Windows, but that is not necessarily true. The problem occurs on a Windows server running MySQL 5.0.18, PHP 5.1.2 and Apache 2.0.55. It does not occur on a Linux server running Apache 1.3.34, PHP 5.1.2 and, perhaps more significantly, MySQL 4.1.14. So I don't know whether this is a problem with MySQL on Windows, or with MySQL 5 on Windows, or even MySQL 5.0.18 on Windows. All I know is that MySQL 4.1.14 on Linux does not present the same problem.
[21 Feb 2006 16:22]
Valeriy Kravchuk
Can you, please, run the same SQL statements as in your PHP script from mysql command line client of 5.0.18, and check for the proper results? This way we'll be able to get PHP out of the picture completely.
[21 Feb 2006 23:34]
Erik Fleischer
Ok, good idea. I've just tried "SET NAMES 'latin1'" and "SET NAMES 'utf8'" from the command line and the problem seems to continue. The query run after "SET NAMES 'latin1'" returns what seem to be UTF-8 strings, and the query after "SET NAMES 'utf8'" returns strings in which supposedly accented characters have more weird symbols than the first strings. For example, the first query (after "SET NAMES 'latin1'") returns "elaboraú├úo", and the second query (after "SET NAMES 'utf8'") returns "elabora├â┬º├â┬úo". This to me suggests that "SET NAMES 'utf8'" is causing UTF-8 strings to be re-encoded in UTF-8. But things get hairier... Because the above queries return very long strings that are difficult to analyze in DOS window (the command line), I decided to also run "SELECT ... INTO OUTFILE" queries after the two "SET NAMES" queries. When I did that, both resulting files were IDENTICAL (same SHA1 hash). Makes no sense to me; hope it does to you!
[22 Feb 2006 6:29]
Peter Laursen
Also here things get hairier: You might want to follow this discussion from another forum: http://www.webyog.com/forums/index.php?showtopic=1915&st=15 To clarify: This SQLyog program allows for 'HTTP-tunneling' - that is the program connects to a PHP script on the server, exchanges data using an XML-format and the PHP script connects to MySQL. With MySQL >= 4.1 it uses the SET NAMES command for the connection as the very first command. On one remote Linux (a recent Red Hat) server (MySQL 3.23.58, Apache 2.0.48, PHP 3.3.2) everything works. One another remote (a custom build from DEBIAN components) server (MySQL 4.0.26, Apache 1.3.x, PHP 4.4.2) and on two local Windows (tested with combinations of MySQL 4.0.x, 4.1.x, 5.0.x, 5.1.x and PHP 4.4.2 and 5.1.2 - Apache 2.0.59) I can't write Danish characters æøå and not ñ and German/Swedish etc UMLAUT (ä,ö,ü)etc) characters. There are only few (if any) problems with UPPERCASES - but attempting to write strings using LOWERCASES of these characters either result in an encoding error (garbage strings returned with 1064 error message) or returns 'Query was empty'. However some strings containing these characters can be written. Probles are exact reproducable on the same host, but different across hosts. Problems with char/varchar/text but not blob. And the most strange thing: a varbinary is as problematic as a varchar! I can't connect directly on port 3306 on the second LINUX server. But I can on the first and my locals (of course) and no such issue with direct connection.
[23 Mar 2006 17:28]
Valeriy Kravchuk
Erik, OK, if problem is repeatable form the command line, please, send the results of: SHOW VARIABLES LIKE 'charset%'; statement when you see the problem. And it is a good time to check with version 5.0.19.
[23 Mar 2006 18:54]
Erik Fleischer
Results of SHOW VARIABLES LIKE 'charset%': Empty set.
[23 Mar 2006 18:59]
Peter Laursen
Just let me add here, that the problems I reported seem to have been related to the client application that I use mostly (actually it is solved with the most recent release) - not MySQL.
[24 Mar 2006 8:48]
Valeriy Kravchuk
Sorry, my fault. I need the results of: SHOW VARIABLES LIKE 'character%';
[24 Mar 2006 12:48]
Erik Fleischer
Results of SHOW VARIABLES LIKE 'character%': +--------------------------+-------------------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\MySQL\MySQL Server 5.0\share\charsets\ | +--------------------------+-------------------------------------------+ 8 rows in set (0.00 sec) By the way, I've already upgraded to 5.0.19 and the problem continues.
[27 Mar 2006 13:00]
Valeriy Kravchuk
Looks like there is no reports table in your old dump, uploaded as private file. So, can you, please, upload new dump of this (or similar) table, with only one row that you are selecting: SELECT Report FROM reports WHERE Id = 123;
[27 Mar 2006 17:36]
Erik Fleischer
[27 Mar 15:00] Valeriy Kravchuk: Looks like there is no reports table in your old dump, uploaded as private file. So, can you, please, upload new dump of this (or similar) table, with only one row that you are selecting(...) REPLY: My old dump did contain the "reports" table, except it's called "relatorios" (as in the new dump I've just uploaded). My whole system is in Portuguese and I thought it would be easier to translate table and field names for the purposes of this discussion.
[28 Mar 2006 11:10]
Valeriy Kravchuk
Thank you for the clarification.
[28 Apr 2006 18:27]
Valeriy Kravchuk
Please, try to repeat with mysql command line client and newer version, 5.0.20a. Inform about the results.
[28 May 2006 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: I am retrieving data from a MySQL database whose character set is UTF-8 and whose tables all have UTF-8 as their character set, and where all data is properly encoded in UTF-8. The data retrieval is performed by a PHP (v. 5.1.2) script; using a PDO object or the old mysql_query function does not affect the problem. The file holding the script can be encoded in UTF-8 or ISO-8859-1; this makes no difference either. Prior do data retrieval, the following command is issued to the database to ensure that all i/o operations use the desired character set: SET NAMES "utf8" When the data retrieved from the database is output to the browser (preceded by a "Content-Type: text/html; charset=UTF-8" header to ensure that the browser switches to UTF-8), all accented characters have been converted to strange symbols. If 'SET NAMES "latin"' is used instead of 'SET NAMES "utf8"', the output is proper UTF-8, even though it should be ISO-8859-1. Upon closer analysis, the strings returned by the database when the query is preceded by 'SET NAMES "utf8"' are all actually doubly-encoded in UTF-8: they somehow get re-encoded to UTF-8 (which messes them up completely). When 'SET NAMES "latin1"' is used, the strings (which are all UTF-8) do not get converted to ISO-8859-1 as expected. This problem only happens on Windows; on Linux the output is proper UTF-8, as expected. How to repeat: <?php define('DB_HOST', 'localhost'); define('DB_USERNAME', 'username'); define('DB_PWD', 'secretpwd'); define('DB_NAME', 'mydb'); define('DB_DSN', 'mysql:host='.DB_HOST.';dbname='.DB_NAME); $dbo = new PDO(DB_DSN, DB_USERNAME, DB_PWD); $dbo->query('SET NAMES "utf8"'); $sql = 'SELECT Report FROM reports WHERE Id = 123'; $result = $dbo->query($sql); $row = $result->fetch(PDO::FETCH_OBJ); header('Pragma: private'); header('Cache-control: private, must-revalidate'); header('Content-Type: text/html; charset=utf-8'); print $row->Report; ?> Suggested fix: I imagine that somehow 'SET NAMES "utf8"' should NOT re-encode strings that are already UTF-8.