Gossamer Forum: General: Perl Programming: "MySQL Server has gone away" error... driving me mad!

Mar 23, 2005, 3:58 AM

Andy

Veteran / Moderator (17297 posts)

Mar 23, 2005, 3:58 AM

Post #1 of 28

Shortcut

"MySQL Server has gone away" error... driving me mad!

Hi,

Can anyone see any reason why this would give me a "MySQL server has gone away" error?

Code:
my $dbh = DBI->connect( "dbi:mysql:$dbnames->{$site_to_go_on};host=$dbhosts->{$site_to_go_on}",$dbuser->{$site_to_go_on},$dbpass->{$site_to_go_on}, { RaiseError => 1, AutoCommit => 0 }) || die "Database connection not made: $DBI::errstr"; 

# first, we need to enter our values into lsql_Links 
my $article  = $DB->table('Articles')->select( ['Content'], { LinkID => $link_id } )->fetchrow; 
my $sql = qq{ INSERT INTO lsql_Links (ID, Title, LinkOwner, Add_Date, Mod_Date, Description, upload_id, isPremium,PremiumDescription) VALUES ('', ?, 'new_admin', CURDATE(), CURDATE(), ? , ?, ?, ?) }; 
my $sth = $dbh->prepare( $sql ); 
$sth->execute($hit->{Title},$article,$hit->{ID},$hit->{isPremium},$hit->{PremiumDescription})  || die "Database connection not made: $DBI::errstr"; 
$sth->finish(); 
$dbh->disconnect();

Its driving me mad. All the other scripts work with almost exactly the same format... so I can't see why its giving me this error :/

The error line refers to the $sth->execute part.

TIA

Andy (mod)
andy@ultranerds.co.uk

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

Mar 24, 2005, 12:59 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 12:59 AM

Post #2 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

Bump :(

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 2:11 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 2:11 AM

Post #3 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

Mmm.. its defiantly this line thats giving the error;

Code:
my $dbh = DBI->connect( "dbi:mysql:$dbnames->{$site_to_go_on};host=$dbhosts->{$site_to_go_on}",$dbuser->{$site_to_go_on},$dbpass->{$site_to_go_on}, { RaiseError => 1, AutoCommit => 0 }) || die "Database connection not made: $DBI::errstr"; 

# first, we need to enter our values into lsql_Links 
my $article  = $DB->table('Articles')->select( ['Content'], { LinkID => $link_id } )->fetchrow; 
my $sql = qq{ INSERT INTO lsql_Links (ID, Title, LinkOwner, Add_Date, Mod_Date, Description, upload_id, isPremium,PremiumDescription) VALUES ('', ?, 'new_admin', CURDATE(), CURDATE(), ? , ?, ?, ?) }; 
my $sth = $dbh->prepare( $sql )->execute($hit->{Title},$article,$hit->{ID},$hit->{isPremium},$hit->{PremiumDescription}) || die $DBI::errstr;

I've even tried trimming it onto one line;

my $dbh = DBI->connect( "dbi:mysql:$dbnames->{$site_to_go_on};host=$dbhosts->{$site_to_go_on}",$dbuser->{$site_to_go_on},$dbpass->{$site_to_go_on}, { RaiseError => 1, AutoCommit => 0 }) || die "Database connection not made: $DBI::errstr";

my $sth = $dbh->prepare(q{INSERT INTO lsql_Links (ID, Title, LinkOwner, Add_Date, Mod_Date, Description, upload_id, isPremium,PremiumDescription) VALUES ('', ?, 'new_admin', CURDATE(), CURDATE(), ? , ?, ?, ?)})->execute($hit->{Title},$article,$hit->{ID},$hit->{isPremium},$hit->{PremiumDescription}) || die $DBI::errstr;

.. and that *still* gives the "mySQL Server has gone away" error... ARGHHHHHHH!

TIA

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 4:52 AM

Chaz

Enthusiast (819 posts)

Mar 24, 2005, 4:52 AM

Post #4 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

Code:
my $sth = $dbh->prepare( $sql )->execute($hit->{Title},$article,$hit->{ID},$hit->{isPremium},$hit->{PremiumDescription}) || die $DBI::errstr;

That's setting $sth to the return value of execute which is probably not what you want. You need something like this for that to work:

Code:
(my $sth = $dbh->prepare( $sql ))->execute($hit->{Title},$article,$hit->{ID},$hit->{isPremium},$hit->{PremiumDescription}) || die $DBI::errstr;

But then you're not getting the rv of the execute to test.

Unfortunately, I don't think that is going to solve your problem. When the server decides to go away like that it's really hard to figure out what you did to make it mad :)

~Charlie

Mar 24, 2005, 4:55 AM

Chaz

Enthusiast (819 posts)

Mar 24, 2005, 4:55 AM

Post #5 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

One thing you might want to do is turn on DBI's trace[1] and see if that gives you any more info.

[1] http://search.cpan.org/...-1.48/DBI.pm#TRACING

~Charlie

Mar 24, 2005, 5:46 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 5:46 AM

Post #6 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

Hi Charlie,

Thanks for the replies :)

I'm still strugging with this one :(

Code:
$sth->execute() || die "\n\nSQL: ".$sql."\n\n $DBI::errstr";

.. which now gives this error :/

Quote:

[Thu Mar 24 13:41:30 2005] distribute.pl: DBD::mysql::st execute failed: MySQL server has gone away at distribute.pl line 209.
Content-type: text/html

A fatal error has occured:
DBD::mysql::st execute failed: MySQL server has gone away at distribute.pl line 209.
Please enable debugging in setup for more details.
DBD::mysql::st execute failed: MySQL server has gone away at distribute.pl line 209.
[Thu Mar 24 13:41:30 2005] distribute.pl: Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

The reason thats confusing me so much, is that it should be printing something like;

Quote:

[Thu Mar 24 13:41:30 2005] distribute.pl: DBD::mysql::st execute failed: MySQL server has gone away at distribute.pl line 209
SQL: ....
Content-type: text/html

A fatal error has occured:
DBD::mysql::st execute failed: MySQL server has gone away at distribute.pl line 209.
Please enable debugging in setup for more details.
DBD::mysql::st execute failed: MySQL server has gone away at distribute.pl line 209.
[Thu Mar 24 13:41:30 2005] distribute.pl: Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

..but it doesn't :(

I know the server isn't running mod_perl, so it shouldn't be a cache thing. I've also tried deleting the file, running again (which gives a "file not found" error), then reuploading it... and trying again (to the same avail :().

Afraid I wouldn't have a clue where to start with in regards to TRACE :(

TIA

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 6:09 AM

Chaz

Enthusiast (819 posts)

Mar 24, 2005, 6:09 AM

Post #7 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

To turn on trace you just need to call the trace method with the level you want to use:

Code:
my $dbh = DBI->connect(...); 
$dbh->trace(3); 

# rest of your code here.

Then check your logs. Start with a level of 3 and go up as needed from there if you don't get enough info. Again, I'm not really sure what that will do for you. The error you're getting is coming from MySQL and I'm not sure if you will actually get anything worth while traced *before* you get to that error. Do you also have access to the MySQL logs to see what might be going on?

~Charlie

Last edited by:

Chaz: Mar 24, 2005, 6:09 AM

Mar 24, 2005, 6:23 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 6:23 AM

Post #8 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

You beauty.. thats giving me a load more info now :D

Quote:

D:\uploads\new_scripts>perl distribute.pl > log.txt
DBI::db=HASH(0x21222c8) trace level set to 3 in DBI 1.37-ithread
Note: perl is running without the recommended perl -w option
-> prepare for DBD::mysql::db (DBI::db=HASH(0x2122d08)~0x21222c8 'INSERT INT
O lsql_Links (ID, Title, LinkOwner, Add_Date, Mod_Date, Description, upload_id,
isPremium,PremiumDescription) VALUES (``, `The relationship between height and w
eight`, 'new_admin', CURDATE(), CURDATE(), `<html>

<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<title>At a Mayfield High School...') thr#0183F340
dbih_setup_handle(DBI::st=HASH(0x211fef0)=>DBI::st=HASH(0x2121724), DBD::mys
ql::st, 211fefc, Null!)
dbih_make_com(DBI::db=HASH(0x21222c8), 02120E4C, DBD::mysql::st, 208, 000000
00) thr#0183F340
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0x211fef0) at distribute.pl line 209 via distribute
.pl line 106
-> execute for DBD::mysql::st (DBI::st=HASH(0x211fef0)~0x2121724) thr#0183F3
40
-> dbd_st_execute for 02122d2c
MySQL server has gone away error 2006 recorded: MySQL server has gone away
<- dbd_st_execute -2 rows
!! ERROR: 2006 'MySQL server has gone away'
<- execute= undef at distribute.pl line 210 via distribute.pl line 106
[Thu Mar 24 14:19:31 2005] distribute.pl: DBD::mysql::st execute failed: MySQL s
erver has gone away at distribute.pl line 210.
DBD::mysql::st execute failed: MySQL server has gone away at distribute.pl line
210.
-> DESTROY for DBD::mysql::st (DBI::st=HASH(0x2121724)~INNER) thr#0183F340
error: 2006 'MySQL server has gone away'
<- DESTROY= undef
-> DESTROY for DBD::mysql::db (DBI::db=HASH(0x21222c8)~INNER) thr#0183F340
[Thu Mar 24 14:19:31 2005] distribute.pl: Issuing rollback() for database handle
being DESTROY'd without explicit disconnect().
MySQL server has gone away error 2006 recorded: MySQL server has gone away
&imp_dbh->mysql: 2120ea4
error: 2006 'MySQL server has gone away'
<- DESTROY= undef

D:\uploads\new_scripts>

Quote:

Do you also have access to the MySQL logs to see what might be going on?

Sure do. I've got a virtual terminal connection to the NT server. Wouldn't really know where the log file is though :( I've found it before on UNIX servers, but thats a different story Unsure

TIA

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 6:33 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 6:33 AM

Post #9 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

Ok, now this is interesting;

Code:
	my $sql = qq|INSERT INTO lsql_Links (Title, LinkOwner, Add_Date, Mod_Date, upload_id, isPremium,PremiumDescription) VALUES (`$hit->{Title}`, 'new_admin', CURDATE(), CURDATE(),`$hit->{ID}`, `$hit->{isPremium}`, ?)|; 
              my $sth = $dbh->prepare($sql) || die "\n\nSQL: ".$sql."\n\n $DBI::errstr"; 
              $sth->execute($hit->{PremiumDescription}) || die "\n\nSQL: ".$sql."\n\n $DBI::errstr";

The error being given;

Quote:

error: 1054 'Unknown column 'The relationship between height and weight'
in 'field list''
<- DESTROY= undef

...but "The relationship between height and weight" is the title! (from $hit->{Title}) ... so I've no idea why its giving that error :|

TIA

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 6:40 AM

Chaz

Enthusiast (819 posts)

Mar 24, 2005, 6:40 AM

Post #10 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

Try replacing $article with " " in your insert and see what happens. Does it alway seem to fail when inserting the same article?

~Charlie

[edit]Do this using your original insert code.[/edit]

Last edited by:

Chaz: Mar 24, 2005, 6:45 AM

Mar 24, 2005, 6:44 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 6:44 AM

Post #11 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

Yeah, I've tried removing $article (which gets put in "Description") ... so that shouldn't be the problem (still gives the same error). I'm just confused why its thinks that $hit->{Title} is the field name, when its clearly in the VALUES () part :/

TIA

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 6:46 AM

Chaz

Enthusiast (819 posts)

Mar 24, 2005, 6:46 AM

Post #12 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

What happens if you run that qury via phpMyAdmin or via mysql on the command line?

~Charlie

Mar 24, 2005, 7:05 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 7:05 AM

Post #13 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

Query;

Quote:

INSERT INTO lsql_Links (Title,LinkOwner,Add_Date,Mod_Date,upload_id,isPremium,PremiumDescription) VALUES ('The relationship between height and weight', 'new_admin', CURDATE(), CURDATE(),'404199', '0','')

..gives;

Quote:

rows affected: 1

:/

Cheers

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 7:13 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 7:13 AM

Post #14 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

Ok, its *definatly* something to do with that record. I put in a bit of code, to skip that record;

Code:
          if ($hit->{ID} == 404199) { next; }

..and that added ok!

NB: Probably should have told you this earlier, but we have a seperate MySQL server from our main machine (so we can share the DB's between our 4 servers). Maybe thats the problem?

Cheers

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 7:21 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 7:21 AM

Post #15 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

Ah, this is interesting... the value passed into $article, is saved in a table called "Articles". They link in based on LinkID = xxx.

I just tried using phpMyAdmin to edit this entry, and it gave me this error;

Quote:

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 3632506 bytes) in /var/www/html/sites/domain.com/mysql/tbl_change.php on line 368

intersting :/ Could it be that the machine is running out of available memory? (its a pretty loaded server, in terms of processing power and RAM).

TIA

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 7:33 AM

Chaz

Enthusiast (819 posts)

Mar 24, 2005, 7:33 AM

Post #16 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

Hmm, maybe you have a memory leak somewhere in your code. Are you freeing up those resurces after you insert them? I'm still not really sure thats the problem though. I'm betting that there is something in that article that is causing the problem. Before skipping that article, can you print it to your log and post it here?

~Charlie

Mar 24, 2005, 7:36 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 7:36 AM

Post #17 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

Hi,

I'll PM the article :) (its got a little bit of sensative details in it, which I can't post publically :(). At least, I will once I can extract the whole thing <G>

TIA

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 7:58 AM

Chaz

Enthusiast (819 posts)

Mar 24, 2005, 7:58 AM

Post #18 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

That will work. I'll try to insert it and see what happens. If you want, you can e-mail it to piper at dev-null.net.

~Charlie

Mar 24, 2005, 8:08 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 8:08 AM

Post #19 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

Hi,

Nice one.. I've just emailed it to you :)

Thanks again.

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 8:34 AM

Chaz

Enthusiast (819 posts)

Mar 24, 2005, 8:34 AM

Post #20 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

What is the column type on the column that you're trying to stuff $article into? (I think it's Description?) I think the problem is that you hit the maximum size limit for that column type. That article is around 1.5 MB.

~Charlie

Mar 24, 2005, 8:40 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 8:40 AM

Post #21 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

Hi,

Its a TEXT field I believe. Maybe try VARCHAR?

Cheers

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 8:45 AM

Chaz

Enthusiast (819 posts)

Mar 24, 2005, 8:45 AM

Post #22 of 28

Shortcut

Re: [Andy] "MySQL Server has gone away" error... driving me mad! In reply to

Try MEDIUMTEXT or LONGTEXT and see if that helps. There still might be a limit somewhere else that you are hitting.

http://dev.mysql.com/...g-type-overview.html

~Charlie

Mar 24, 2005, 8:55 AM

Chaz

Enthusiast (819 posts)

Mar 24, 2005, 8:55 AM

Post #23 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

There must be something going on somewhere else. It should still insert but it will truncate at the max length. I tried inserting it into a text field with this:

Code:
LOAD DATA INFILE '/tmp/phpD8D.tmp' 
INTO TABLE `test1` 
FIELDS TERMINATED BY '' 
ESCAPED BY '\\'  
LINES TERMINATED BY '\r\n'  
(`html`)

and it worked. It just truncated at the max value.

Mar 24, 2005, 8:55 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 8:55 AM

Post #24 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

Mmm.. its already set to LONGTEXT :/

I'l try stripping out rubbish code (i.e all the <html> tags, as they take up a LOT of the space).

Cheers

Andy (mod)
andy@ultranerds.co.uk

Mar 24, 2005, 8:56 AM

Andy

Veteran / Moderator (17297 posts)

Mar 24, 2005, 8:56 AM

Post #25 of 28

Shortcut

Re: [Chaz] "MySQL Server has gone away" error... driving me mad! In reply to

In Reply To:

There must be something going on somewhere else. It should still insert but it will truncate at the max length. I tried inserting it into a text field with this:

Code:
LOAD DATA INFILE '/tmp/phpD8D.tmp' 
INTO TABLE `test1` 
FIELDS TERMINATED BY '' 
ESCAPED BY '\\'  
LINES TERMINATED BY '\r\n'  
(`html`)

and it worked. It just truncated at the max value.

Mmm.. interesting :/

Is there a mysql_escape() function in Perl? (could just use regex, but I'm sure I'll miss at least one charachter that screws it up :().

TIA

Andy (mod)
andy@ultranerds.co.uk

"MySQL Server has gone away" error... driving me mad!

(Page 1 of 2)

Last edited by:

Last edited by: