Re: Message from Maintainer to DBD::mysql users, developers
On 2006-03-29 09:58:16 -0800, Mark Hedges wrote:
On Wed, 29 Mar 2006, Tim Bunce wrote:
On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote:
Since mysql supports different charsets per table and even per column,
I'd like an option to automatically convert them to and from perl's
internal UTF-8 encoding.
(Actually, I'd like that to be the default behaviour, but it probably
would break a lot of existing scripts, so it should be an option at
first)
I think that translates into just asking DBD::mysql to set the
'connection charset' to utf8 and then mysql server will look after the
conversions for you.
Hmmm, I tried setting the default connection charset:
[...]
But the scalars selected from a utf8 field still do not have the
utf8 flag set in perl.
That has been my experience as well. DBD::mysql will just pass
everything through unaltered. So if you use UTF-8 as connection charset,
you have to encode('utf-8', ...) all queries and parameters, unless you
are sure that they are either plain ASCII or already have the UTF-8 bit
set. And you will get raw UTF-8 strings back, which you have to decode()
explicitely.
However, I notice that on Debian Sarge (on which I did my testing),
libdbd-mysql-perl depends on libmysqlclient12. So there may be a problem
with mixing releases (The server is 4.1, but libmysqlclient12 belongs to
4.0, which doesn't know about UTF-8).
hp
--
_ | Peter J. Holzer| If I wanted to be academically correct,
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgp7K0E14XpOu.pgp
Description: PGP signature
Re: Message from Maintainer to DBD::mysql users, developers
Martin, Thanks! I'll look for this patch and test it out, and hopefully do a dev release. I agree that this is a priority. regards, Patrick Martin J. Evans wrote: Tim Bunce wrote: On Wed, Mar 29, 2006 at 09:58:16AM -0800, Mark Hedges wrote: On Wed, 29 Mar 2006, Tim Bunce wrote: On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote: On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: I apologise for what might seem somewhat of a bit of neglect on my part to get some features into DBD::mysql, features such as UTF support, some bugs in 3.0002_4. I've been super busy on some other projects, but have finished one of them and have today started to go through my mail in order to start addressing some needs of DBD::mysql. I'm wondering if it might help to discuss within this list what priorities users would like to see addressed in DBD::mysql, so I could come out with some sort of road map. Sorry for the late reply, but I just stumbled across it again recently: Since mysql supports different charsets per table and even per column, I'd like an option to automatically convert them to and from perl's internal UTF-8 encoding. (Actually, I'd like that to be the default behaviour, but it probably would break a lot of existing scripts, so it should be an option at first) I think that translates into just asking DBD::mysql to set the 'connection charset' to utf8 and then mysql server will look after the conversions for you. Hmmm, I tried setting the default connection charset: /etc/my.cnf: [client] port= 3306 socket = /var/run/mysqld/mysqld.sock default-character-set=utf8 mysql show variables like 'character_set_connection'; +--+---+ | Variable_name| Value | +--+---+ | character_set_connection | utf8 | +--+---+ 1 row in set (0.00 sec) But the scalars selected from a utf8 field still do not have the utf8 flag set in perl. Perhaps DBD::mysql doesn't yet support utf8. My reply above assumed that it did. [...later...] I see no mention of utf8 or unicode in the DBD::mysql docs. That's sad. Tim. There was a patch for utf8 posted either here or on the mysql perl list within the last week. I'm at home now so can't easily find it right now but it sort of suggested to me that someone was using utf8. Martin
Re: Message from Maintainer to DBD::mysql users, developers
On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: I apologise for what might seem somewhat of a bit of neglect on my part to get some features into DBD::mysql, features such as UTF support, some bugs in 3.0002_4. I've been super busy on some other projects, but have finished one of them and have today started to go through my mail in order to start addressing some needs of DBD::mysql. I'm wondering if it might help to discuss within this list what priorities users would like to see addressed in DBD::mysql, so I could come out with some sort of road map. Sorry for the late reply, but I just stumbled across it again recently: Since mysql supports different charsets per table and even per column, I'd like an option to automatically convert them to and from perl's internal UTF-8 encoding. (Actually, I'd like that to be the default behaviour, but it probably would break a lot of existing scripts, so it should be an option at first) hp -- _ | Peter J. Holzer| If I wanted to be academically correct, |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpiLrJv7aGcd.pgp Description: PGP signature
Re: Message from Maintainer to DBD::mysql users, developers
On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote: On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: I apologise for what might seem somewhat of a bit of neglect on my part to get some features into DBD::mysql, features such as UTF support, some bugs in 3.0002_4. I've been super busy on some other projects, but have finished one of them and have today started to go through my mail in order to start addressing some needs of DBD::mysql. I'm wondering if it might help to discuss within this list what priorities users would like to see addressed in DBD::mysql, so I could come out with some sort of road map. Sorry for the late reply, but I just stumbled across it again recently: Since mysql supports different charsets per table and even per column, I'd like an option to automatically convert them to and from perl's internal UTF-8 encoding. (Actually, I'd like that to be the default behaviour, but it probably would break a lot of existing scripts, so it should be an option at first) I think that translates into just asking DBD::mysql to set the 'connection charset' to utf8 and then mysql server will look after the conversions for you. Tim.
Re: Message from Maintainer to DBD::mysql users, developers
On Wed, 29 Mar 2006, Tim Bunce wrote: On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote: On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: I apologise for what might seem somewhat of a bit of neglect on my part to get some features into DBD::mysql, features such as UTF support, some bugs in 3.0002_4. I've been super busy on some other projects, but have finished one of them and have today started to go through my mail in order to start addressing some needs of DBD::mysql. I'm wondering if it might help to discuss within this list what priorities users would like to see addressed in DBD::mysql, so I could come out with some sort of road map. Sorry for the late reply, but I just stumbled across it again recently: Since mysql supports different charsets per table and even per column, I'd like an option to automatically convert them to and from perl's internal UTF-8 encoding. (Actually, I'd like that to be the default behaviour, but it probably would break a lot of existing scripts, so it should be an option at first) I think that translates into just asking DBD::mysql to set the 'connection charset' to utf8 and then mysql server will look after the conversions for you. Hmmm, I tried setting the default connection charset: /etc/my.cnf: [client] port= 3306 socket = /var/run/mysqld/mysqld.sock default-character-set=utf8 mysql show variables like 'character_set_connection'; +--+---+ | Variable_name| Value | +--+---+ | character_set_connection | utf8 | +--+---+ 1 row in set (0.00 sec) But the scalars selected from a utf8 field still do not have the utf8 flag set in perl. Various abstraction layer plugins like Class::DBI::utf8 and DBIx::Class::UTF8Columns attempt to bandage this problem. But I don't want to use heavyweight systems for lightweight high-volume queries that I can type more easily as SQL. Mark
Re: Message from Maintainer to DBD::mysql users, developers
On Wed, Mar 29, 2006 at 09:58:16AM -0800, Mark Hedges wrote: On Wed, 29 Mar 2006, Tim Bunce wrote: On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote: On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: I apologise for what might seem somewhat of a bit of neglect on my part to get some features into DBD::mysql, features such as UTF support, some bugs in 3.0002_4. I've been super busy on some other projects, but have finished one of them and have today started to go through my mail in order to start addressing some needs of DBD::mysql. I'm wondering if it might help to discuss within this list what priorities users would like to see addressed in DBD::mysql, so I could come out with some sort of road map. Sorry for the late reply, but I just stumbled across it again recently: Since mysql supports different charsets per table and even per column, I'd like an option to automatically convert them to and from perl's internal UTF-8 encoding. (Actually, I'd like that to be the default behaviour, but it probably would break a lot of existing scripts, so it should be an option at first) I think that translates into just asking DBD::mysql to set the 'connection charset' to utf8 and then mysql server will look after the conversions for you. Hmmm, I tried setting the default connection charset: /etc/my.cnf: [client] port= 3306 socket = /var/run/mysqld/mysqld.sock default-character-set=utf8 mysql show variables like 'character_set_connection'; +--+---+ | Variable_name| Value | +--+---+ | character_set_connection | utf8 | +--+---+ 1 row in set (0.00 sec) But the scalars selected from a utf8 field still do not have the utf8 flag set in perl. Perhaps DBD::mysql doesn't yet support utf8. My reply above assumed that it did. [...later...] I see no mention of utf8 or unicode in the DBD::mysql docs. That's sad. Tim.
Re: Message from Maintainer to DBD::mysql users, developers
Tim Bunce wrote: On Wed, Mar 29, 2006 at 09:58:16AM -0800, Mark Hedges wrote: On Wed, 29 Mar 2006, Tim Bunce wrote: On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote: On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: I apologise for what might seem somewhat of a bit of neglect on my part to get some features into DBD::mysql, features such as UTF support, some bugs in 3.0002_4. I've been super busy on some other projects, but have finished one of them and have today started to go through my mail in order to start addressing some needs of DBD::mysql. I'm wondering if it might help to discuss within this list what priorities users would like to see addressed in DBD::mysql, so I could come out with some sort of road map. Sorry for the late reply, but I just stumbled across it again recently: Since mysql supports different charsets per table and even per column, I'd like an option to automatically convert them to and from perl's internal UTF-8 encoding. (Actually, I'd like that to be the default behaviour, but it probably would break a lot of existing scripts, so it should be an option at first) I think that translates into just asking DBD::mysql to set the 'connection charset' to utf8 and then mysql server will look after the conversions for you. Hmmm, I tried setting the default connection charset: /etc/my.cnf: [client] port= 3306 socket = /var/run/mysqld/mysqld.sock default-character-set=utf8 mysql show variables like 'character_set_connection'; +--+---+ | Variable_name| Value | +--+---+ | character_set_connection | utf8 | +--+---+ 1 row in set (0.00 sec) But the scalars selected from a utf8 field still do not have the utf8 flag set in perl. Perhaps DBD::mysql doesn't yet support utf8. My reply above assumed that it did. [...later...] I see no mention of utf8 or unicode in the DBD::mysql docs. That's sad. Tim. There was a patch for utf8 posted either here or on the mysql perl list within the last week. I'm at home now so can't easily find it right now but it sort of suggested to me that someone was using utf8. Martin
RE: [dbi] Message from Maintainer to DBD::mysql users, developers
Patrick,
Welcome back.
I posted the email below back in December (I think). I'm not sure my solution
is correct but it seems to work. I've included Tim's followup since it also
seems relevant.
BTW, DBD::mysql does not compile out of the box with a standards C compiler
due to an incorrect #if and a variable being declared in the code. I think I
posted this last issue to one of the mysql lists but the basic change I needed
was:
bash-2.05$ diff -u dbdimp.c /tmp/dbdimp.c.mysql30002_4
--- dbdimp.cSun Nov 6 21:32:01 2005
+++ /tmp/dbdimp.c.mysql30002_4 Wed Dec 14 11:41:25 2005
@@ -260,7 +260,7 @@
case MYSQL_TYPE_STRING:
case MYSQL_TYPE_BLOB:
case MYSQL_TYPE_TINY_BLOB:
-#ifdef MYSQL_VERSION_ID NEW_DATATYPE_VERSION
+#if (MYSQL_VERSION_ID NEW_DATATYPE_VERSION)
case MYSQL_TYPE_GEOMETRY:
#endif
case MYSQL_TYPE_TIMESTAMP:
@@ -2530,7 +2530,9 @@
}
else
{
-/*if (dbis-debug = 2)
+ int num_fields;
+
+ /*if (dbis-debug = 2)
PerlIO_printf(DBILOGFP,
- dbd_st_more_rows use_mysql_use_result=%d\n,
use_mysql_use_result);
@@ -2570,7 +2572,7 @@
/** Store the result in the current statement handle */
DBIc_ACTIVE_on(imp_sth);
-int num_fields=mysql_num_fields(imp_sth-result);
+num_fields=mysql_num_fields(imp_sth-result);
DBIc_NUM_FIELDS(imp_sth) = num_fields;
==
I colleague of mine has been briefly using DBD::mysql and was getting
a lot of FREE ERROR BIND! messages appearing on stderr. I was asked
to take a look and I've reproduced this with the following code
running against DBD::mysql 3.0002_4:
use DBI;
# DBI 1.50
# perl, v5.8.7 built for i686-linux
# mysql 5.0.15
#
my $dbh = DBI-connect('dbi:mysql:database=martin;host=localhost;',
'xxx', 'yyy');
$dbh-do(q/drop table if exists bindissue/);
$dbh-do(q/create table bindissue (a int)/);
my $sql = q/select a from bind where a = ?/;
my @data = $dbh-selectrow_array($sql, undef, 1);
which when run produces:
bash-2.05$ ./bind2.pl
FREE ERROR BIND!bash-2.05$
I presume because the result-set is empty.
This seems to be due to finish in the driver being called twice. I'm
not overly familiar with DBD::mysql but commenting out the finish call
(in dbd_st_fetch) below makes the problem go away:
if ((rc= mysql_stmt_fetch(imp_sth-stmt)))
{
if (rc == 1)
do_error(sth, mysql_stmt_errno(imp_sth-stmt),
mysql_stmt_error(imp_sth-stmt));
if (rc == 100)
{
/* Update row_num to affected_rows value */
imp_sth-row_num= mysql_stmt_affected_rows(imp_sth-stmt);
imp_sth-fetch_done=1;
}
/* MJE
if (!DBIc_COMPAT(imp_sth))
dbd_st_finish(sth, imp_sth);
*/
return Nullav;
}
May be the finish is required if rc == 1 but I've not got the time
right now to look into it. Perhaps someone who knows DBD::mysql better
might want to take a look (I can supply any other info on versions etc
if it helps).
As an aside, what exactly does DBIc_COMPAT do? The DBI::DBD document
refers to its use with FOR AN OLD PERL INTERFACE but the reason
was not clear to me.
==
Tim followed up with:
It stems from the days of perl4 db interfaces (oraperl, sybperl etc).
Many drivers, such as DBD::Oracle, shipped with modules that provided
backwards compatibility with the old perl4 APIs (ie Oraperl).
Setting $sth-{Compat} = 1 will make DBIc_COMPAT(imp_sth) true.
So that gives you a pure-perl workaround.
Tim.
p.s. I've no idea why it's used in DBD::mysql in this way.
==
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
On 31-Jan-2006 Patrick Galbraith wrote:
Greetings all!
I apologise for what might seem somewhat of a bit of neglect on my part
to get some features into DBD::mysql, features such as UTF support, some
bugs in 3.0002_4. I've been super busy on some other projects, but have
finished one of them and have today started to go through my mail in
order to start addressing some needs of DBD::mysql.
I'm wondering if it might help to discuss within this list what
priorities users would like to see addressed in DBD::mysql, so I could
come out with some sort of road map.
Just some thoughts, and a note to let all of you know that I'm here!
Kind regards,
Patrick Galbraith
Re: Message from Maintainer to DBD::mysql users, developers
On Monday 30 January 2006 02:24 pm, Patrick Galbraith wrote: Greetings all! I apologise for what might seem somewhat of a bit of neglect on my part to get some features into DBD::mysql, features such as UTF support, some bugs in 3.0002_4. I've been super busy on some other projects, but have finished one of them and have today started to go through my mail in order to start addressing some needs of DBD::mysql. I'm wondering if it might help to discuss within this list what priorities users would like to see addressed in DBD::mysql, so I could come out with some sort of road map. Just some thoughts, and a note to let all of you know that I'm here! Kind regards, Patrick Galbraith Welcome back! You may want to check out blobs in 3.0002_4. As I posted several weeks ago they don't work (at least for me); 3.0002 is fine. My message had a small test script that demostrated the failure; if you need it, let me know and I will repost. -- Aloha = Beau;
Message from Maintainer to DBD::mysql users, developers
Greetings all! I apologise for what might seem somewhat of a bit of neglect on my part to get some features into DBD::mysql, features such as UTF support, some bugs in 3.0002_4. I've been super busy on some other projects, but have finished one of them and have today started to go through my mail in order to start addressing some needs of DBD::mysql. I'm wondering if it might help to discuss within this list what priorities users would like to see addressed in DBD::mysql, so I could come out with some sort of road map. Just some thoughts, and a note to let all of you know that I'm here! Kind regards, Patrick Galbraith
Re: Message from Maintainer to DBD::mysql users, developers
On Tue, 31 Jan 2006 01:24:18 +0100, Patrick Galbraith wrote: Hi Patrick I'd like to see the readme contain instructions on which parts of MySQL to download and compile under Windows MS VC++ 6, and how to compile DBD::mysql in that environment. I am happy to test all this on my PC, and to document the process too. -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/index.html
