DBD::mysql::st execute failed (Errcode: 28)

kingquattro has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I am using Perl DBI to query a table and convert it into a mldbm indexed lookup file (this is required, I can't make too many sql lookup statements).

Recently while updating my lookup file I ran into this error

DBD::mysql::st execute failed: Error writing file '/tmp/MYj3WD8H' (Errcode: 28)

I can't seem to resolve this, and am hoping someone can point me in right direction.

The error occurs during $sth->execute() statement. I have tried to use limit statement in my mysql query but that works for first few iteration and then fails with same error.

I can't figure out how to change the location of tmpdir so as to avoid this issue (/tmp does not have enough space for select statement to execute successfully).

I tried to add tmpdir= in connect statement as follows but that didn't help either.

$dbh = DBI->connect("DBI:mysql:database=$DBNAME;tmpdir=$TMPDIR;host=$HOST,$USER, $PASS", {PrintError=>1, RaiseError =>1, AutoCommit =>1});

Any suggestions?

Thank you

Comment on DBD::mysql::st execute failed (Errcode: 28)

Replies are listed 'Best First'.
Re: DBD::mysql::st execute failed (Errcode: 28) by nikosv (Deacon) on Jan 10, 2014 at 22:05 UTC
turning DBI tracing on and check what's happening behind the scenes. take a look at : http://www.effectiveperlprogramming.com/2010/04/use-dbi_trace-to-follow-dbis-work/	[reply]
Re: DBD::mysql::st execute failed (Errcode: 28) by wazat (Monk) on Jan 11, 2014 at 05:15 UTC
Have you tried addressing this on the mysql server side? On the server side you can set TMPDIR env var; or start the DB with --tmpdir; or set in your server options file. `[mysqld] tmpdir=C:/temp` [download]	[reply] [d/l]
Re^2: DBD::mysql::st execute failed (Errcode: 28) by kingquattro (Novice) on Jan 11, 2014 at 17:08 UTC
Hi, I tried DBI TRACE at level 5 and that didn't help, there isn't much information on why Errcode: 28 is raised. I can't change anything on my mysql server side either. The script run on a cluster, where I don't have control over mysql server settigns. I can't write to my.cnf file either because it won't be read when running job on a cluster node. Any other suggestions? Thank you	[reply]
Re: DBD::mysql::st execute failed (Errcode: 28) by taint (Chaplain) on Jan 10, 2014 at 18:47 UTC
Greetings, kingquattro. While I haven't yet a complete (coded) solution. I'd like to get the ball rolling by saying; Since disk space appears to you, to have an affect on your current strategy. I'm initially thinking to make the query/modifications in memory. The perldoc covers this. But at the momment, I can't recall where Give me a minute, and I'll see if I can update this with a more complete solution. This will at least give you something to think about, and maybe you already have a solution that will work in this way (memory based modification). :) --Chris Ąλɐp ʇɑəɹ⅁ ɐ əʌɐɥ puɐ ʻꜱdləɥ ꜱᴉɥʇ ədoH	[reply]