Dear friends,
several people on this list have brought up the issue of non-sticking passwords in the new MySql bundle. There were suggestions to reinstall, revert bundles to original state etc –– I have done that, but I am still prompted for password with every query and with every change of table in the database browser.
What is the solution for this problem?
Secondly, I mentioned before that the mysql bundle is not happily working with non-latin unicode -- queries containing, for instance, Cyrilic or Greek are not executed as they should (they return no values), and tables containing non-latin utf8 text display question marks instead of text.
I am kindly asking for any tips -- the mysql bundle has become unusable for me, and I need it very badly.
All best, Tench
What I did was roll back to the sql bundle where i found one that was working for me and then draged it to my desktop and doubleclicked it. and now svn up's dont kill it either.
mines now been working just as good as it has been for the last year.
Kim
Tench wrote:
Dear friends,
several people on this list have brought up the issue of non-sticking passwords in the new MySql bundle. There were suggestions to reinstall, revert bundles to original state etc –– I have done that, but I am still prompted for password with every query and with every change of table in the database browser.
What is the solution for this problem?
Secondly, I mentioned before that the mysql bundle is not happily working with non-latin unicode -- queries containing, for instance, Cyrilic or Greek are not executed as they should (they return no values), and tables containing non-latin utf8 text display question marks instead of text.
I am kindly asking for any tips -- the mysql bundle has become unusable for me, and I need it very badly.
All best, Tench______________________________________________________________________ For new threads USE THIS: textmate@lists.macromates.com (threading gets destroyed and the universe will collapse if you don't) http://lists.macromates.com/mailman/listinfo/textmate
On 17. Jul 2007, at 06:15, Tench wrote:
several people on this list have brought up the issue of non- sticking passwords in the new MySql bundle. There were suggestions to reinstall, revert bundles to original state etc –– I have done that, but I am still prompted for password with every query and with every change of table in the database browser.
Did you check the key chain? If you have the time, please join #textmate at freenode.net and help us (ciaran) troubleshoot this.
In fact anyone who has a reproducible problem with this bundle and can spare a few minutes getting it fixed, please join the IRC chat.
Secondly, I mentioned before that the mysql bundle is not happily working with non-latin unicode -- queries containing, for instance, Cyrilic or Greek are not executed as they should (they return no values), and tables containing non-latin utf8 text display question marks instead of text.
So the stuff in your database is not UTF-8?
The reason the query failing is then likely because your query text is sent as UTF-8 but then does not match because the data in your table is in another encoding.
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.
[1]: http://lists.macromates.com/pipermail/textmate/2007-July/ 020707.html
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
Thanks Chris that patch Fixed my problems
Kim
Chris Adams wrote:
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
For new threads USE THIS: textmate@lists.macromates.com (threading gets destroyed and the universe will collapse if you don't) http://lists.macromates.com/mailman/listinfo/textmate
Just to confirm -- this patch also fixes my problems with non-latin databases. I hope it will make it into the official release.
On Jul 18, 2007, at 11:09 AM, Kim Hunter wrote:
Thanks Chris that patch Fixed my problems
Kim
Chris Adams wrote:
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
_ For new threads USE THIS: textmate@lists.macromates.com (threading gets destroyed and the universe will collapse if you don't) http://lists.macromates.com/mailman/listinfo/textmate
For new threads USE THIS: textmate@lists.macromates.com (threading gets destroyed and the universe will collapse if you don't) http://lists.macromates.com/mailman/listinfo/textmate
On 17. Jul 2007, at 20:17, Chris Adams wrote:
[...] 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: [...]
For me the ‘character_set_server’ shows as latin1 (on both my remote and local database, it seems to be the default, and prior to MySQL 4.1.1 UTF-8 was not supported AFAIK).
So if I issue a ‘SET NAMES utf8;’ then it will actually break things because I store utf-8 text in my tables, and with the client and connection encoding set to non-latin1 (utf-8) MySQL will re-encode my table values (from latin1) to utf-8 (it should just transfer them “verbatim”, since my client interprets it as utf-8 regardless of the MySQL encoding variables).
So my database setup is wrong, i.e. my server encoding does not match the actual one used. But ignoring that I can’t change it (as my.cfg is off limits on the server), if I actually did change it, I would break all clients which are presently not paying respect to MySQL’s encoding stuff (these just transfer bytes back and forth), because they will not tell MySQL that they are sending/expecting utf-8, and so MySQL will convert the data received/sent to what it think it is/ expects (latin1).
Here db clients include third party web applications.
I am not experienced with this stuff, so I would love to be told wrong, but I think there is a lot of databases out there which has a server encoding of ‘latin1’ but actually store ‘utf8’ and none of the clients issue any commands to tell the server about what encoding they use/expect -- and this presently works fine because as long as server and client has the same setting, no conversion is done by MySQL, and MySQL is 8-bit clean (so it is safe to store utf-8 as latin1 etc.).
Your fix (for the ideal setup) breaks this -- if the majority of servers out there as the proper encoding set, it seems we should take the fix, otherwise I think a more practical solution would be to ensure that we set the same encoding as the server use, and let the user configure a per-connection encoding, which we then convert to ourself (I believe this is what CocoaMySQL does).
Comments?
On Jul 18, 2007, at 5:42 AM, Allan Odgaard wrote:
Your fix (for the ideal setup) breaks this -- if the majority of servers out there as the proper encoding set, it seems we should take the fix, otherwise I think a more practical solution would be to ensure that we set the same encoding as the server use, and let the user configure a per-connection encoding, which we then convert to ourself (I believe this is what CocoaMySQL does).
Unfortunately, I think you're right to expect things to break. I know about this for the same reason as I've flushed out a fair number of bugs making things pure utf8, including a few
The naive patch does meet the "Everything is utf8" goal but can't help with improperly encoded data. One approach might be to "SELECT @@character_set_client, @@character_set_database" and require the user to specify the desired encoding if they don't match since that's probably a reliable check for encoding problems.
One other approach involves something I've been thinking about for awhile: mysql has an existing way to specify the database options - the .my.cnf file. I originally was thinking about modifying the code to use the default username/password from that file if the user hadn't configured a value and it occurs to me that we could check for "default-character-set=utf8" in the mysql section of that file, probably in conjunction with either hostname=localhost or the existence of --host/--database options which match the target database.
Chris
-
Allan Odgaard -
Chris Adams -
Kim Hunter -
Tench