Bug #37069 for CGI-Session: Bug with the mysql driver and using a foreign key relationship with the Sessions table

Tue Jun 24 18:21:53 2008 skirkup [...] jupiterimages.com - Ticket created

Subject:	Bug with the mysql driver and using a foreign key relationship with the Sessions table
Date:	Tue, 24 Jun 2008 10:19:40 -0700
To:	bug-CGI-Session [...] rt.cpan.org
From:	Steve Kirkup <skirkup [...] jupiterimages.com>

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

Wed Jun 25 09:05:30 2008 MARKSTOS [...] cpan.org - Taken

Wed Jun 25 09:08:14 2008 mark [...] summersault.com - Correspondence added

Subject:	Re: [rt.cpan.org #37069] Bug with the mysql driver and using a foreign key relationship with the Sessions table
Date:	Wed, 25 Jun 2008 09:07:33 -0400
To:	bug-CGI-Session [...] rt.cpan.org
From:	Mark Stosberg <mark [...] summersault.com>

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

Wed Jun 25 09:08:17 2008 The RT System itself - Status changed from 'new' to 'open'

Sat Jun 28 22:03:27 2008 ron [...] savage.net.au - Correspondence added

Subject:	Re: [rt.cpan.org #37069] Bug with the mysql driver and using a foreign key relationship with the Sessions table
Date:	Sun, 29 Jun 2008 12:01:58 +1000
To:	bug-CGI-Session [...] rt.cpan.org
From:	Ron Savage <ron [...] savage.net.au>

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

Tue Jul 01 11:05:46 2008 skirkup [...] jupiterimages.com - Correspondence added

Subject:	Re: [rt.cpan.org #37069] Bug with the mysql driver and using a foreign key relationship with the Sessions table
Date:	Tue, 01 Jul 2008 08:06:36 -0700
To:	bug-CGI-Session [...] rt.cpan.org
From:	Steve Kirkup <skirkup [...] jupiterimages.com>

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

Fri Oct 31 23:23:35 2008 MARKSTOS [...] cpan.org - Correspondence added

This was apparently released as a fix in 4.33.

Fri Oct 31 23:23:37 2008 MARKSTOS [...] cpan.org - Status changed from 'open' to 'resolved'