[TxMt] mysql bundle, password issues, utf8 encoding

[Chris Adams]

On Jul 17, 2007, at 1:20 AM, Allan Odgaard wrote:
> As for not showing the non-UTF-8 characters from your tables  
> correctly, I am afraid this falls under the “we assume all text is  
> UTF-8”-policy [1]. Did this work for you with the previous database  
> browser?
>
> I believe some databases have the notion of an encoding, but I also  
> believe it is wrong most of the time, and encoding could be a per- 
> table thing, even per row -- so I doubt this will be addressed, sorry.

I believe there's a simple fix for this problem. MySQL has a number  
of places where you set the character set - the server, database,  
table and individual columns can all be set individually - but  
there's another less-publicized issue: the MySQL client library  
defaults to latin1 for communication, which mangles your otherwise  
clean UTF-8 path. If you run "SHOW VARIABLES LIKE '%character%'"  
it'll show something like this:
+-------------------------- 
+------------------------------------------------------------+
| Variable_name            |  
Value                                                      |
+-------------------------- 
+------------------------------------------------------------+
| character_set_client     |  
latin1                                                     |
| character_set_connection |  
latin1                                                     |
| character_set_database   |  
utf8                                                       |
| character_set_filesystem |  
binary                                                     |
| character_set_results    |  
latin1                                                     |
| character_set_server     |  
utf8                                                       |
| character_set_system     |  
utf8                                                       |
| character_sets_dir       | /usr/local/mysql-5.0.37-osx10.4-i686/ 
share/mysql/charsets/ |
+-------------------------- 
+------------------------------------------------------------+

The solution for this is for the database client library to issue a  
"SET NAMES utf8" command after connecting:

mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set%';
+-------------------------- 
+------------------------------------------------------------+
| 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       | /usr/local/mysql-5.0.37-osx10.4-i686/ 
share/mysql/charsets/ |
+-------------------------- 
+------------------------------------------------------------+
8 rows in set (0.00 sec)

This diff should take care of this - I'm not familiar enough with the  
Ruby libraries to know if there's a better place to do this but it's  
obviously a trivial patch:

Index: Support/lib/connectors/mysql.rb
===================================================================
--- Support/lib/connectors/mysql.rb     (revision 7747)
+++ Support/lib/connectors/mysql.rb     (working copy)
@@ -98,6 +98,7 @@
      if args[0] != :INIT then
        real_connect(*args)
      end
+    query('SET NAMES utf8;')
    end

    def real_connect(host=nil, user=nil, passwd=nil, db=nil,  
port=nil, socket=nil, flag=nil)


Chris
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2423 bytes
Desc: not available
URL: <http://lists.macromates.com/textmate/attachments/20070717/2ad24118/attachment.p7s>