headers
Benjamin Schuster-Boeckler | 10 Sep 2009 13:52
Picon
Picon
Gravatar

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 <at> m.gmane.org
Permalink | Reply | 0 comments |
headers
Benjamin Schuster-Boeckler | 10 Sep 2009 15:02
Picon
Picon
Gravatar

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 <at> gmx.de]
> Sent: Thursday, September 10, 2009 2:53 PM
> To: perl <at> lists.mysql.com
> Subject: SELECT from TEXT column returns undef
(Continue reading)

Permalink | Reply | 0 comments |
headers
Benjamin Schuster-Boeckler | 10 Sep 2009 16:23
Picon
Picon
Gravatar

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- 
names

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

I completely rebuilt the database, ie made a mysqldump (which works  
fine and returns the large column correctly), then droped the DB, and  
created a new one to copy the data back into.

Now, the interesting thing is that I see a SPORADIC correct behaviour.  
Ever so often, and seemingly completely randomly, the query returns  
data. The next time I try, it doesn't. This issue is getting weirder  
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;  
> it lost mainly big data when I forgot to compile again the drivers  
> with the new mysqlclients libraries.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Benjamin Schuster-Boeckler | 10 Sep 2009 17:45
Picon
Picon
Gravatar

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  
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 = ?");
(Continue reading)

Permalink | Reply | 0 comments |

Gmane