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
querying the text column resolves the issue:
instead of
my $query2 = $dbh->prepare(<<EOF
SELECT DISTINCT f.chromosome, start, end, name, strand, SUBSTR
(sequence, start, end-start+1) AS sequence
FROM feature f, sequence s
WHERE f.type = ? AND f.chromosome = s.chromosome AND f.chromosome = ?
AND version = ?
ORDER BY chromosome, start
EOF
);
I know do
my $query2 = $dbh->prepare(<<EOF
SELECT DISTINCT f.chromosome, start, end, name, strand
FROM feature f, sequence s
WHERE f.type = ? AND f.chromosome = s.chromosome AND f.chromosome = ?
AND version = ?
ORDER BY chromosome, start
EOF
);
my $query3 = $dbh->prepare("SELECT SUBSTR(sequence, ?, ?) AS sequence
FROM sequence WHERE chromosome=? AND version = ?");
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;
> it lost mainly big data when I forgot to compile again the drivers
> with the new mysqlclients libraries.
>
> -----Original Message-----
> From: Benjamin Schuster-Boeckler [mailto:bendaboots@stripped]
> Sent: Thursday, September 10, 2009 4:02 PM
> To: perl@stripped
> 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 = 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@stripped]
>> Sent: Thursday, September 10, 2009 2:53 PM
>> To: perl@stripped
>> 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=1
>
> --
> Benjamin Schuster-Böckler
> Frundsbergstrasse 23a
> 82064 Strasslach
> Deutschland
>
--
Benjamin Schuster-Böckler
Frundsbergstrasse 23a
82064 Strasslach
Deutschland
