SELECT from TEXT column returns undef

I recently upgraded to mysql 5.4.1-beta 64bit running on Snow Leopard,
with perl5.10.0 and DBD::mysql 4.012.

Since the upgrade, SELECTs from a MEDIUMTEXT column return undef. I
get the impression this is because some size limit is exceeded (the
return value should be < 30000 characters). This used to work happily
before, so I'm wondering where I find any settings to fix this? Funny
enough, I can run the queries successfully in Sequel (a mysql admin
app for mac). Makes me think the problem must be somewhere in the
client connection library. Any help would be greatly appreciated!

Best,
Benjamin

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: SELECT from TEXT column returns undef

Just to clarify: I did of course compile DBD::mysql in 64bit against =20
the 5.4.1 libraries. This was the main reason I moved to 5.4.1 in the =20=

first place: I couldn't compile 64bit DBD::mysql to run with 64bit =20
perl without having a 64bit mysql, hence the move to 5.4.1 beta.

I have no problems querying the database, the only issue I have is =20
that clearly somewhere there is a package maximum size set, which =20
causes TEXT columns of certain length to return undef.

Also not that I DID set max_allowed_packet =3D 32M in my.cnf. Any other =20=

place where the problem might stem from?

Ben

On 10 Sep 2009, at 14:51, Yair Lapin wrote:

> You must upgrade also your drivers DBD::mysql and check that you =20
> have installed the correct mysqlclient libraries for the version =20
> 5.4.1. Another thing, check the mysql libraries; if they are 64bit =20
> and your perl must be also 64 bit else you have a problem. You can't =20=

> compile 64bit libraries with into perl 32bit.
> This is the reason I don't upgrade mysql, I prefer to throw the =20
> machine and build new one from the beginning. You must rebuild all =20
> the drivers, PHP also.
>
> -----Original Message-----
> From: Benjamin Schuster-Boeckler [mailto:bendaboots@gmx.de]
> Sent: Thursday, September 10, 2009 2:53 PM
> To: perl@lists.mysql.com
> Subject: SELECT from TEXT column returns undef
>
> I recently upgraded to mysql 5.4.1-beta 64bit running on Snow Leopard,
> with perl5.10.0 and DBD::mysql 4.012.
>
> Since the upgrade, SELECTs from a MEDIUMTEXT column return undef. I
> get the impression this is because some size limit is exceeded (the
> return value should be < 30000 characters). This used to work happily
> before, so I'm wondering where I find any settings to fix this? Funny
> enough, I can run the queries successfully in Sequel (a mysql admin
> app for mac). Makes me think the problem must be somewhere in the
> client connection library. Any help would be greatly appreciated!
>
> Best,
> Benjamin
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: =
http://lists.mysql.com/perl?unsub=3Dyairl@savion.huji.ac.il

--
Benjamin Schuster-Böckler
Frundsbergstrasse 23a
82064 Strasslach
Deutschland



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: SELECT from TEXT column returns undef

I tried a few things now, none of which fixed it:

I cleaned, fixed and upgraded the tables using

mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables
mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-=20=

names

I recompiled, tested and installed DBD::mysql 4.012. All tests are =20
fine, except the custom procedure test, but I don't think this has =20
anything to do with my issue.

I completely rebuilt the database, ie made a mysqldump (which works =20
fine and returns the large column correctly), then droped the DB, and =20=

created a new one to copy the data back into.


Now, the interesting thing is that I see a SPORADIC correct behaviour. =20=

Ever so often, and seemingly completely randomly, the query returns =20
data. The next time I try, it doesn't. This issue is getting weirder =20
and weirder...

TA,
Ben

On 10 Sep 2009, at 15:13, Yair Lapin wrote:

> I don't know what else may cause it. I had exactly the same problem; =20=

> it lost mainly big data when I forgot to compile again the drivers =20
> with the new mysqlclients libraries.
>
> -----Original Message-----
> From: Benjamin Schuster-Boeckler [mailto:bendaboots@gmx.de]
> Sent: Thursday, September 10, 2009 4:02 PM
> To: perl@lists.mysql.com
> Cc: Yair Lapin
> Subject: Re: SELECT from TEXT column returns undef
>
> Just to clarify: I did of course compile DBD::mysql in 64bit against
> the 5.4.1 libraries. This was the main reason I moved to 5.4.1 in the
> first place: I couldn't compile 64bit DBD::mysql to run with 64bit
> perl without having a 64bit mysql, hence the move to 5.4.1 beta.
>
> I have no problems querying the database, the only issue I have is
> that clearly somewhere there is a package maximum size set, which
> causes TEXT columns of certain length to return undef.
>
> Also not that I DID set max_allowed_packet =3D 32M in my.cnf. Any =
other
> place where the problem might stem from?
>
> Ben
>
> On 10 Sep 2009, at 14:51, Yair Lapin wrote:
>
>> You must upgrade also your drivers DBD::mysql and check that you
>> have installed the correct mysqlclient libraries for the version
>> 5.4.1. Another thing, check the mysql libraries; if they are 64bit
>> and your perl must be also 64 bit else you have a problem. You can't
>> compile 64bit libraries with into perl 32bit.
>> This is the reason I don't upgrade mysql, I prefer to throw the
>> machine and build new one from the beginning. You must rebuild all
>> the drivers, PHP also.
>>
>> -----Original Message-----
>> From: Benjamin Schuster-Boeckler [mailto:bendaboots@gmx.de]
>> Sent: Thursday, September 10, 2009 2:53 PM
>> To: perl@lists.mysql.com
>> Subject: SELECT from TEXT column returns undef
>>
>> I recently upgraded to mysql 5.4.1-beta 64bit running on Snow =20
>> Leopard,
>> with perl5.10.0 and DBD::mysql 4.012.
>>
>> Since the upgrade, SELECTs from a MEDIUMTEXT column return undef. I
>> get the impression this is because some size limit is exceeded (the
>> return value should be < 30000 characters). This used to work happily
>> before, so I'm wondering where I find any settings to fix this? Funny
>> enough, I can run the queries successfully in Sequel (a mysql admin
>> app for mac). Makes me think the problem must be somewhere in the
>> client connection library. Any help would be greatly appreciated!
>>
>> Best,
>> Benjamin
>>
>> --
>> MySQL Perl Mailing List
>> For list archives: http://lists.mysql.com/perl
>> To unsubscribe: =
http://lists.mysql.com/perl?unsub=3Dyairl@savion.huji.ac.il
>
> --
> Benjamin Schuster-Böckler
> Frundsbergstrasse 23a
> 82064 Strasslach
> Deutschland
>

--
Benjamin Schuster-Böckler
Frundsbergstrasse 23a
82064 Strasslach
Deutschland



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: SELECT from TEXT column returns undef

Sorry for the flooding, but to elaborate even further on this:

I found out that doing one query for a non-text column and then =20
querying the text column resolves the issue:

instead of

my $query2 =3D $dbh->prepare(< SELECT DISTINCT f.chromosome, start, end, name, strand, SUBSTR=20
(sequence, start, end-start+1) AS sequence
FROM feature f, sequence s
WHERE f.type =3D ? AND f.chromosome =3D s.chromosome AND f.chromosome =3D =
? =20
AND version =3D ?
ORDER BY chromosome, start
EOF
);

I know do

my $query2 =3D $dbh->prepare(< SELECT DISTINCT f.chromosome, start, end, name, strand
FROM feature f, sequence s
WHERE f.type =3D ? AND f.chromosome =3D s.chromosome AND f.chromosome =3D =
? =20
AND version =3D ?
ORDER BY chromosome, start
EOF
);
my $query3 =3D $dbh->prepare("SELECT SUBSTR(sequence, ?, ?) AS sequence =20=

FROM sequence WHERE chromosome=3D? AND version =3D ?");

That magically fixed it. Why, I don't know...

Best,
Benjamin

On 10 Sep 2009, at 15:13, Yair Lapin wrote:

> I don't know what else may cause it. I had exactly the same problem; =20=

> it lost mainly big data when I forgot to compile again the drivers =20
> with the new mysqlclients libraries.
>
> -----Original Message-----
> From: Benjamin Schuster-Boeckler [mailto:bendaboots@gmx.de]
> Sent: Thursday, September 10, 2009 4:02 PM
> To: perl@lists.mysql.com
> Cc: Yair Lapin
> Subject: Re: SELECT from TEXT column returns undef
>
> Just to clarify: I did of course compile DBD::mysql in 64bit against
> the 5.4.1 libraries. This was the main reason I moved to 5.4.1 in the
> first place: I couldn't compile 64bit DBD::mysql to run with 64bit
> perl without having a 64bit mysql, hence the move to 5.4.1 beta.
>
> I have no problems querying the database, the only issue I have is
> that clearly somewhere there is a package maximum size set, which
> causes TEXT columns of certain length to return undef.
>
> Also not that I DID set max_allowed_packet =3D 32M in my.cnf. Any =
other
> place where the problem might stem from?
>
> Ben
>
> On 10 Sep 2009, at 14:51, Yair Lapin wrote:
>
>> You must upgrade also your drivers DBD::mysql and check that you
>> have installed the correct mysqlclient libraries for the version
>> 5.4.1. Another thing, check the mysql libraries; if they are 64bit
>> and your perl must be also 64 bit else you have a problem. You can't
>> compile 64bit libraries with into perl 32bit.
>> This is the reason I don't upgrade mysql, I prefer to throw the
>> machine and build new one from the beginning. You must rebuild all
>> the drivers, PHP also.
>>
>> -----Original Message-----
>> From: Benjamin Schuster-Boeckler [mailto:bendaboots@gmx.de]
>> Sent: Thursday, September 10, 2009 2:53 PM
>> To: perl@lists.mysql.com
>> Subject: SELECT from TEXT column returns undef
>>
>> I recently upgraded to mysql 5.4.1-beta 64bit running on Snow =20
>> Leopard,
>> with perl5.10.0 and DBD::mysql 4.012.
>>
>> Since the upgrade, SELECTs from a MEDIUMTEXT column return undef. I
>> get the impression this is because some size limit is exceeded (the
>> return value should be < 30000 characters). This used to work happily
>> before, so I'm wondering where I find any settings to fix this? Funny
>> enough, I can run the queries successfully in Sequel (a mysql admin
>> app for mac). Makes me think the problem must be somewhere in the
>> client connection library. Any help would be greatly appreciated!
>>
>> Best,
>> Benjamin
>>
>> --
>> MySQL Perl Mailing List
>> For list archives: http://lists.mysql.com/perl
>> To unsubscribe: =
http://lists.mysql.com/perl?unsub=3Dyairl@savion.huji.ac.il
>
> --
> Benjamin Schuster-Böckler
> Frundsbergstrasse 23a
> 82064 Strasslach
> Deutschland
>

--
Benjamin Schuster-Böckler
Frundsbergstrasse 23a
82064 Strasslach
Deutschland



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org