Skip Menu |
 

This queue is for tickets about the CGI-Session CPAN distribution.

Report information
The Basics
Id: 37069
Status: resolved
Priority: 0/
Queue: CGI-Session

People
Owner: MARKSTOS [...] cpan.org
Requestors: skirkup [...] jupiterimages.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Received: from x1.develooper.com (x1.develooper.com [63.251.223.170]) by diesel.bestpractical.com (Postfix) with SMTP id 9E5B94D8076 for <bug-CGI-Session [...] rt.cpan.org>; Tue, 24 Jun 2008 18:21:07 -0400 (EDT)
Received: (qmail 4537 invoked from network); 24 Jun 2008 22:21:06 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 24 Jun 2008 22:21:06 -0000
Received: from nfw1.iworld.com (HELO nfw1.iworld.com) (63.236.18.142) by 16.mx.develooper.com (qpsmtpd/0.43rc1) with ESMTP; Tue, 24 Jun 2008 15:17:57 -0700
Received: (from uucp [...] localhost) by nfw1.iworld.com (8.12.6/8.12.6) id m5OHInpO024923 for <bug-CGI-Session [...] rt.cpan.org>; Tue, 24 Jun 2008 13:18:49 -0400 (EDT)
Received: from nodnsquery(10.10.1.52) by nfw1.iworld.com via csmap (V6.0) id srcAAAsNaqRW; Tue, 24 Jun 08 13:18:49 -0400
Received: from [10.1.14.53] by mailsrv.iworld.com (iPlanet Messaging Server 5.2 HotFix 0.9 (built Jul 29 2002)) with ESMTPS id <0K2Z004679FCNH [...] mailsrv.iworld.com> for bug-CGI-Session [...] rt.cpan.org; Tue, 24 Jun 2008 13:18:49 -0400 (EDT)
Delivered-To: cpan-bug+CGI-Session [...] diesel.bestpractical.com
User-Agent: Thunderbird 2.0.0.14 (X11/20080502)
MIME-Version: 1.0
Subject: Bug with the mysql driver and using a foreign key relationship with the Sessions table
X-Authentication-Warning: nfw1.iworld.com: uucp set sender to <skirkup [...] jupiterimages.com> using -f
Return-Path: <skirkup [...] jupiterimages.com>
X-Original-To: bug-CGI-Session [...] rt.cpan.org
Date: Tue, 24 Jun 2008 10:19:40 -0700
Message-Id: <48612CAC.4060308 [...] jupiterimages.com>
content-type: text/plain; charset="utf-8"; format="flowed"
To: bug-CGI-Session [...] rt.cpan.org
Content-Transfer-Encoding: 7BIT
From: Steve Kirkup <skirkup [...] jupiterimages.com>
X-RT-Original-Encoding: ISO-8859-1
Content-Length: 1419
Download (untitled) / with headers
text/plain 1.3k
Howdy, I was implementing something to help 'Customer Service' do administration with a customers session. To do this I created this table, CREATE TABLE Customer ( customer_id CHAR(10) PRIMARY KEY, session_id CHAR(32) NOT NULL, FOREIGN KEY (session_id) REFERENCES sessions(id) ) ENGINE = INNODB; So I am using the 'sessions' table for CGI::Session to store stuff. When I go to flush() the CGI::Session I get this error: Cannot delete or update a parent row: a foreign key constraint fails (`White_Label/Customer`, CONSTRAINT `Customer_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`)) at /usr/local/share/perl/5.8.8/CGI/Session/Driver/mysql.pm line 50. Looking at line 50 of the mysql.pm file is this, $dbh->do("REPLACE INTO " . $self->table_name . " ($self->{IdColName}, $self->{DataColName}) VALUES(?, ?)", undef, $sid, $datastr) or return $self->set_error( "store(): \$dbh->do failed " . $dbh->errstr ); So what the replace is supposed to is delete the original row, if there is one, and then insert a new one. Which is causing my problem. Could I recommend instead that you use the |INSERT ... ON DUPLICATE KEY UPDATE syntax instead? This will not delete a row, which keeps consistency with FOREIGN KEYS. |http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Steve Kirkup -- Steve Kirkup Sr Web Developer 520-881-8101 ext 119
MIME-Version: 1.0
X-Spam-Status: No, hits=-2.6 required=8.0 tests=BAYES_00,SPF_PASS
In-Reply-To: <rt-3.6.HEAD-20362-1214346114-1165.37069-4-0 [...] rt.cpan.org>
X-Mailer: Evolution 2.22.2
References: <RT-Ticket-37069 [...] rt.cpan.org> <48612CAC.4060308 [...] jupiterimages.com> <rt-3.6.HEAD-20362-1214346114-1165.37069-4-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
X-RT-Original-Encoding: utf-8
Received: from x1.develooper.com (x1.develooper.com [63.251.223.170]) by diesel.bestpractical.com (Postfix) with SMTP id 136684D81B2 for <bug-CGI-Session [...] rt.cpan.org>; Wed, 25 Jun 2008 09:07:52 -0400 (EDT)
Received: (qmail 27349 invoked from network); 25 Jun 2008 13:07:52 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 25 Jun 2008 13:07:52 -0000
Received: from tanagra.summersault.com (HELO tanagra.summersault.com) (12.161.105.149) by 16.mx.develooper.com (qpsmtpd/0.43rc1) with SMTP; Wed, 25 Jun 2008 06:07:37 -0700
Received: (qmail 6682 invoked from network); 25 Jun 2008 09:07:33 -0400
Received: from simba.summersault.com (HELO ?192.168.97.182?) (192.168.97.182) by tanagra.summersault.com with SMTP; 25 Jun 2008 09:07:33 -0400
Delivered-To: cpan-bug+CGI-Session [...] diesel.bestpractical.com
Subject: Re: [rt.cpan.org #37069] Bug with the mysql driver and using a foreign key relationship with the Sessions table
Return-Path: <mark [...] summersault.com>
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: bug-CGI-Session [...] rt.cpan.org
Date: Wed, 25 Jun 2008 09:07:33 -0400
X-Spam-Level: *
Message-Id: <1214399253.30599.31.camel [...] localhost>
To: bug-CGI-Session [...] rt.cpan.org
Content-Transfer-Encoding: 7bit
From: Mark Stosberg <mark [...] summersault.com>
RT-Message-ID: <rt-3.6.HEAD-20377-1214399294-230.37069-0-0 [...] rt.cpan.org>
Content-Length: 1048
Show quoted text
> I was implementing something to help 'Customer Service' do > administration with a customers session. To do this I created this table, > > CREATE TABLE Customer ( > customer_id CHAR(10) PRIMARY KEY, > session_id CHAR(32) NOT NULL, > FOREIGN KEY (session_id) REFERENCES sessions(id) > ) ENGINE = INNODB; > > So I am using the 'sessions' table for CGI::Session to store stuff. When > I go to flush() the CGI::Session I get this error: > > Cannot delete or update a parent row: a foreign key constraint fails > (`White_Label/Customer`, CONSTRAINT `Customer_ibfk_1` FOREIGN KEY > (`session_id`) REFERENCES `sessions` (`id`)) at > /usr/local/share/perl/5.8.8/CGI/Session/Driver/mysql.pm line 50. >
I recommend addressing this in your code, using SQL standard features for foreign key definitions like "ON DELETE SET NULL" and "ON UPDATE CASCADE". You can read about the options here: http://www.postgresql.org/docs/8.2/static/ddl-constraints.html#DDL-CONSTRAINTS-FK (for PostgreSQL, I realize). Mark
MIME-Version: 1.0
X-Spam-Status: No, hits=-2.6 required=8.0 tests=BAYES_00
In-Reply-To: <rt-3.6.HEAD-20362-1214346114-1165.37069-4-0 [...] rt.cpan.org>
X-Mailer: Evolution 2.22.1
References: <RT-Ticket-37069 [...] rt.cpan.org> <48612CAC.4060308 [...] jupiterimages.com> <rt-3.6.HEAD-20362-1214346114-1165.37069-4-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
X-RT-Original-Encoding: utf-8
Received: from x1.develooper.com (x1.develooper.com [63.251.223.170]) by diesel.bestpractical.com (Postfix) with SMTP id 5E5A54D8048 for <bug-CGI-Session [...] rt.cpan.org>; Sat, 28 Jun 2008 22:03:19 -0400 (EDT)
Received: (qmail 21468 invoked from network); 29 Jun 2008 02:03:19 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 29 Jun 2008 02:03:19 -0000
Received: from hosted03.westnet.com.au (HELO hosted03.westnet.com.au) (203.10.1.215) by 16.mx.develooper.com (qpsmtpd/0.43rc1) with ESMTP; Sat, 28 Jun 2008 19:03:15 -0700
Received: from hosted03.westnet.com.au (hosted03.westnet.com.au [127.0.0.1]) by hosted03.westnet.com.au (Postfix) with SMTP id 7E3962BB41B for <bug-CGI-Session [...] rt.cpan.org>; Sun, 29 Jun 2008 10:03:09 +0800 (WST)
Received: from [192.168.100.4] (unknown [58.6.188.135]) by hosted03.westnet.com.au (Postfix) with ESMTP id 145D32BB216 for <bug-CGI-Session [...] rt.cpan.org>; Sun, 29 Jun 2008 10:03:05 +0800 (WST)
Delivered-To: cpan-bug+CGI-Session [...] diesel.bestpractical.com
Subject: Re: [rt.cpan.org #37069] Bug with the mysql driver and using a foreign key relationship with the Sessions table
X-PMX-Branch: TNG-Outgoing
Return-Path: <ron [...] savage.net.au>
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: bug-CGI-Session [...] rt.cpan.org
Date: Sun, 29 Jun 2008 12:01:58 +1000
X-Spam-Level: *
Message-Id: <1214704918.3575.113.camel [...] zoe.savage.net.au>
To: bug-CGI-Session [...] rt.cpan.org
Content-Transfer-Encoding: 7bit
From: Ron Savage <ron [...] savage.net.au>
RT-Message-ID: <rt-3.6.HEAD-23245-1214705007-1197.37069-0-0 [...] rt.cpan.org>
Content-Length: 482
Download (untitled) / with headers
text/plain 482b
Hi Steve I've checked into SVN a patch as you suggested. $many x $thanx. It will become V 4.32. All tests pass, but we would /greatly/ appreciate you checking the new code to ensure it works as expected. I had to install MySQL to test this. It's V 5.0.51a. Testing revealed a problem with setting the global variable in t/mysql.t, $CGI::Session::MySQL::TABLE_NAME, so I fixed that too. See Changes for details. -- Ron Savage ron@savage.net.au http://savage.net.au/index.html
MIME-Version: 1.0
X-Spam-Status: No, hits=-2.6 required=8.0 tests=BAYES_00
X-Authentication-Warning: nfw1.iworld.com: uucp set sender to <skirkup [...] jupiterimages.com> using -f
In-Reply-To: <rt-3.6.HEAD-23245-1214705007-1197.37069-6-0 [...] rt.cpan.org>
References: <RT-Ticket-37069 [...] rt.cpan.org> <48612CAC.4060308 [...] jupiterimages.com> <rt-3.6.HEAD-20362-1214346114-1165.37069-4-0 [...] rt.cpan.org> <1214704918.3575.113.camel [...] zoe.savage.net.au> <rt-3.6.HEAD-23245-1214705007-1197.37069-6-0 [...] rt.cpan.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
X-RT-Original-Encoding: utf-8
Received: from x1.develooper.com (x1.develooper.com [63.251.223.170]) by diesel.bestpractical.com (Postfix) with SMTP id 837FB4D819E for <bug-CGI-Session [...] rt.cpan.org>; Tue, 1 Jul 2008 11:05:31 -0400 (EDT)
Received: (qmail 26408 invoked from network); 1 Jul 2008 15:05:30 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 1 Jul 2008 15:05:30 -0000
Received: from nfw1.iworld.com (HELO nfw1.iworld.com) (63.236.18.142) by 16.mx.develooper.com (qpsmtpd/0.43rc1) with ESMTP; Tue, 01 Jul 2008 08:05:27 -0700
Received: (from uucp [...] localhost) by nfw1.iworld.com (8.12.6/8.12.6) id m61F5NwY027905 for <bug-CGI-Session [...] rt.cpan.org>; Tue, 1 Jul 2008 11:05:23 -0400 (EDT)
Received: from nodnsquery(10.10.1.52) by nfw1.iworld.com via csmap (V6.0) id srcAAABraaG2; Tue, 1 Jul 08 11:05:23 -0400
Received: from [10.1.14.53] by mailsrv.iworld.com (iPlanet Messaging Server 5.2 HotFix 0.9 (built Jul 29 2002)) with ESMTPS id <0K3C00G851WWW5 [...] mailsrv.iworld.com> for bug-CGI-Session [...] rt.cpan.org; Tue, 01 Jul 2008 11:05:23 -0400 (EDT)
Delivered-To: cpan-bug+CGI-Session [...] diesel.bestpractical.com
User-Agent: Thunderbird 2.0.0.14 (X11/20080502)
Subject: Re: [rt.cpan.org #37069] Bug with the mysql driver and using a foreign key relationship with the Sessions table
Return-Path: <skirkup [...] jupiterimages.com>
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: bug-CGI-Session [...] rt.cpan.org
Date: Tue, 01 Jul 2008 08:06:36 -0700
X-Spam-Level: *
Message-Id: <486A47FC.8010406 [...] jupiterimages.com>
To: bug-CGI-Session [...] rt.cpan.org
Content-Transfer-Encoding: 7BIT
From: Steve Kirkup <skirkup [...] jupiterimages.com>
RT-Message-ID: <rt-3.6.HEAD-8137-1214924746-1644.37069-0-0 [...] rt.cpan.org>
Content-Length: 695
Download (untitled) / with headers
text/plain 695b
Ron, Excellent, I had meant to get around to following up your last email, but I was very busy last week. I shall try to get look at it today. Steve Kirkup ron@savage.net.au via RT wrote: Show quoted text
> <URL: http://rt.cpan.org/Ticket/Display.html?id=37069 > > > Hi Steve > > I've checked into SVN a patch as you suggested. $many x $thanx. It will > become V 4.32. > > All tests pass, but we would /greatly/ appreciate you checking the new > code to ensure it works as expected. > > I had to install MySQL to test this. It's V 5.0.51a. Testing revealed a > problem with setting the global variable in t/mysql.t, > $CGI::Session::MySQL::TABLE_NAME, so I fixed that too. > > See Changes for details. >
MIME-Version: 1.0
In-Reply-To: <48612CAC.4060308 [...] jupiterimages.com>
X-Mailer: MIME-tools 5.426 (Entity 5.426)
Content-Disposition: inline
Charset: utf8
References: <48612CAC.4060308 [...] jupiterimages.com>
Message-Id: <rt-3.6.HEAD-11878-1225509815-1468.37069-0-0 [...] rt.cpan.org>
Content-Type: text/plain
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 46
This was apparently released as a fix in 4.33.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.