You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Christian Mittendorf <ch...@freenet.de> on 2006/07/12 10:28:37 UTC
Duplicate Key Problem
Hello!
We are running a MySQL 5.0 server using InnoDB tables for our
applications. We have multiple web applications accessing this same
database and the system is running smooth and without problems (almost).
But yesterday we were experiencing some strange errors. During the
afternoon some CayenneRuntimeExceptions appeared in the log file,
which were caused by:
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '4353880' for key 1"
A bit later other CayenneRuntimeException appeared. For those
exceptions the cause was:
Caused by: java.sql.SQLException: Deadlock found when trying to get
lock; Try restarting transaction, message from server: "Lock wait
timeout exceeded; try restarting transaction"
I'm now wondering, are both errors related to each other?
There are, from my point of view, two possible causes for the
"Duplicate entry..." message:
- two Insert statements on the same object, which might be possible
if the application is clustered, which our applications are not, or
- dual use of the same key in different objects
Has anybody else experienced something like that? Are there any hints
what I might do to avoid such situations?
Christian
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
Hmm... we had experienced some OutOfMemoryExceptions during the last
weeks. That
would be an explanation, wouldn't it? However, I can't remember one
on that day. I'll have
a closer look at the log file tomorrow.
Christian
Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
> course if the connection goes down or something else equally bad
> happens during unlock, then you can end up locking the table. I'll
> be curious to know what exactly happened, as we may improve the
> unlock reliability as a result.
>
> Andrus
>
>
> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
>
>> Yes, all of our applications on this database are cayenne based.
>> I will have a look at the logs tomorrow and see if I can find some
>> other exceptions...
>>
>> Thanks,
>> Christian
>>
>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
>>
>>> These could be related, then. I know the MySQL adapter has to
>>> lock the
>>> auto_pk_support table to generate keys and perhaps something
>>> happened to
>>> leave the table locked. Did you see any other exceptions?
>>>
>>> Also, you said you have multiple applications hitting the same
>>> database.
>>> Are all of these applications Cayenne-based?
>>>
>>> Thanks,
>>>
>>> /dev/mrg
>>>
>>>
>>> -----Original Message-----
>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>> Sent: Wednesday, July 12, 2006 1:50 PM
>>> To: cayenne-user@incubator.apache.org
>>> Subject: Re: Duplicate Key Problem
>>>
>>>
>>> We don't use the autoincrement from MySQL.
>>>
>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>>>
>>>> Are you using MySQL's autoincrement feature for the PKs or the
>>>> auto_pk_support table from Cayenne?
>>>>
>>>> -----Original Message-----
>>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>>> Sent: Wednesday, July 12, 2006 4:29 AM
>>>> To: cayenne-user@incubator.apache.org
>>>> Subject: Duplicate Key Problem
>>>>
>>>>
>>>> Hello!
>>>>
>>>> We are running a MySQL 5.0 server using InnoDB tables for our
>>>> applications. We have multiple web applications accessing this same
>>>> database and the system is running smooth and without problems
>>>> (almost).
>>>>
>>>> But yesterday we were experiencing some strange errors. During the
>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>>>> which were caused by:
>>>>
>>>> Caused by: java.sql.SQLException: null, message from server:
>>>> "Duplicate entry '4353880' for key 1"
>>>>
>>>> A bit later other CayenneRuntimeException appeared. For those
>>>> exceptions the cause was:
>>>>
>>>> Caused by: java.sql.SQLException: Deadlock found when trying to get
>>>> lock; Try restarting transaction, message from server: "Lock wait
>>>> timeout exceeded; try restarting transaction"
>>>>
>>>> I'm now wondering, are both errors related to each other?
>>>>
>>>> There are, from my point of view, two possible causes for the
>>>> "Duplicate entry..." message:
>>>>
>>>> - two Insert statements on the same object, which might be possible
>>>> if the application is clustered, which our applications are not, or
>>>> - dual use of the same key in different objects
>>>>
>>>> Has anybody else experienced something like that? Are there any
>>>> hints
>>>> what I might do to avoid such situations?
>>>>
>>>> Christian
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>
RE: Re: Duplicate Key Problem
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
It is good to know some progress has been made there.
Thanks!
/dev/mrg
-----Original Message-----
From: Neil Pierson [mailto:neil.pierson@gmail.com]
Sent: Friday, July 14, 2006 11:22 AM
To: cayenne-user@incubator.apache.org
Subject: Re: Re: Duplicate Key Problem
As an aside, MySQL has changed much since a few years ago.
When asked to store a value in a numeric column that is outside the data
type's allowable range, MySQL's behavior depends on the SQL mode in
effect
at the time. For example, if no restrictive modes are enabled, MySQL
clips
the value to the appropriate endpoint of the range and stores the
resulting
value instead. However, if the mode is set to TRADITIONAL, MySQL rejects
a
value that is out of range with an error, and the insert fails, in
accordance with the SQL standard.
In non-strict mode, when an out-of-range value is assigned to an integer
column, MySQL stores the value representing the corresponding endpoint
of
the column data type range.
If you assign a value to a CHAR or VARCHAR column that exceeds the
column's
maximum length, the value is truncated to fit. If the truncated
characters
are not spaces, a warning is generated. For truncation of non-space
characters, you can cause an error to occur (rather than a warning) and
suppress insertion of the value by using strict SQL mode -- if MySQL is
running in strict mode, values that exceed the column length are *not
stored
*, and an error results.
As of 5.0.2, the server requires that month and day values be legal, and
not
merely in the range 1 to 12 and 1 to 31, respectively. With strict mode
disabled, invalid dates such as '2004-04-31' are converted to
'0000-00-00'and a warning is generated. With strict mode enabled,
invalid dates generate
an error.
The current release of MySQL is 5.0.22; 5.1 is in beta.
On 7/14/06, Michael Gentry <bl...@gmail.com> wrote:
>
> I stopped using MySQL for my local stuff a few years ago, especially
> after I found this little jewel:
>
> mysql> create table test (
> -> numberField numeric(4,2),
> -> stringField varchar(4),
> -> notNullField varchar(4) not null,
> -> dateField date);
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> insert into test (numberField, stringField, dateField)
> -> values (10000000, 'hello', '2/31/2006');
> Query OK, 1 row affected (0.02 sec)
>
> mysql> select * from test;
> +-------------+-------------+--------------+------------+
> | numberField | stringField | notNullField | dateField |
> +-------------+-------------+--------------+------------+
> | 999.99 | hell | | 0000-00-00 |
> +-------------+-------------+--------------+------------+
> 1 row in set (0.00 sec)
>
> So, MySQL will a) alter numbers for you (no error), b) alter strings
> for you (no error), c) allow nulls for NOT NULL columns (no error),
> and d) zero out invalid dates (no error). I still think MySQL can be
> great for read-mostly information due to it's speed, but I want
> something more robust for anything more important (especially
> user-entered data).
>
> I'm thinking Craig's suggestion might be OK, even though it just
> doesn't feel right. Maybe give it 3 chances to obtain a primary key
> before aborting? (Make it configurable, of course, but 3 as a
> default.) Toss a few delays in, too?
>
> Thanks,
>
> /dev/mrg
>
>
> On 7/13/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > Our current more deterministic strategy worked ok, (I guess partly
> > because most database adapters are sequence-based, and don't have to
> > deal with locking). But I am certainly not a fan of pessimistic
> > locking and considered a strategy similar to what Craig outlined
some
> > time ago as well.
> >
> > I guess we can make it an option for MySQLAdapter (the only one that
> > uses explicit locking) and use it with a hard limit on a number of
> > conflicts.
> >
> > Andrus
> >
> >
> > On Jul 13, 2006, at 4:54 PM, Craig L Russell wrote:
> > > Hi,
> > >
> > > This might be a really random suggestion, but couldn't you use the
> > > following strategy, even with autocommit=true?
> > >
> > > public long getNextKey() {
> > > boolean conflict = true;
> > > while (conflict) {
> > > key = SELECT current_key_value from auto_pk_table
> > > next_key = key + increment;
> > > UPDATE auto_pk_table SET current_key_value = next_key_value
WHERE
> > > current_key_value = key
> > > conflict = 0 rows updated
> > > }
> > > return next_key;
> > > }
> > >
> > > All you worry about here is making sure that your increment is
> > > large enough to avoid frequent conflicts.
> > >
> > > Craig
> > >
> > > On Jul 13, 2006, at 6:17 AM, Gentry, Michael (Contractor) wrote:
> > >
> > >> The basic strategy to refresh the primary key cache in MySQL is:
> > >>
> > >> * lock table
> > >> * select next key values from auto_pk_support
> > >> * update auto_pk_support with a new next key (20 keys by default)
> > >> * unlock table
> > >>
> > >> I was just looking at the MySQL docs for "LOCK TABLES" and it
> > >> appears to
> > >> work differently with InnoDB. Which DB storage type are you
using?
> > >> Also, Andrus' suggestion that there could've been a failure with
the
> > >> connection going down could be correct. It would've happened
between
> > >> select and update above. An application got 20 PKs, but the
> > >> connection
> > >> was broken before it could update the PK table. Another
application
> > >> comes along and gets the same 20 PKs.
> > >>
> > >> Any chance you can use PostgreSQL? It uses sequences which are
> > >> atomic
> > >> operations.
> > >>
> > >> Thanks,
> > >>
> > >> /dev/mrg
> > >>
> > >> PS. Andrus: If a connection is broken and then Cayenne auto-
> > >> reconnects,
> > >> does it discard the PK cache? (I'm guessing not, which could
explain
> > >> this.)
> > >>
> > >>
> > >> -----Original Message-----
> > >> From: Christian Mittendorf
[mailto:christian.mittendorf@freenet.de]
> > >> Sent: Thursday, July 13, 2006 6:23 AM
> > >> To: cayenne-user@incubator.apache.org
> > >> Subject: Re: Duplicate Key Problem
> > >>
> > >>
> > >> I haven't found any hint to something abnormal in my log files
yet.
> > >> However, we experienced the duplicate key problem some minutes
ago
> > >> and I was able to track down the problem a bit.
> > >>
> > >> Affected is only one single WebApp and these "Duplicat entry"
errors
> > >> occured since yesterday, about 15:30. If I search for this
exception
> > >> I can find this ID list:
> > >>
> > >> $ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048820' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048821' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048822' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048823' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048824' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048825' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048826' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048827' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048828' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048829' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048830' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048831' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048832' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048833' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048834' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048835' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048836' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048837' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048836' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048838' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048839' for key 1"
> > >>
> > >> It seems as if one package of PKs (17048820 - 17048839 ) was
double
> > >> used.
> > >> How does the PK magic of Cayenne work? Does each DataContext get
its
> > >> own set of IDs?
> > >>
> > >>
> > >> Christian
> > >>
> > >>
> > >>
> > >> Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
> > >>
> > >>> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
> > >>> course if the connection goes down or something else equally bad
> > >>> happens during unlock, then you can end up locking the table.
I'll
> > >>> be curious to know what exactly happened, as we may improve the
> > >>> unlock reliability as a result.
> > >>>
> > >>> Andrus
> > >>>
> > >>>
> > >>> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
> > >>>
> > >>>> Yes, all of our applications on this database are cayenne
based.
> > >>>> I will have a look at the logs tomorrow and see if I can find
some
> > >>>> other exceptions...
> > >>>>
> > >>>> Thanks,
> > >>>> Christian
> > >>>>
> > >>>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
> > >>>>
> > >>>>> These could be related, then. I know the MySQL adapter has to
> > >>>>> lock the
> > >>>>> auto_pk_support table to generate keys and perhaps something
> > >>>>> happened to
> > >>>>> leave the table locked. Did you see any other exceptions?
> > >>>>>
> > >>>>> Also, you said you have multiple applications hitting the same
> > >>>>> database.
> > >>>>> Are all of these applications Cayenne-based?
> > >>>>>
> > >>>>> Thanks,
> > >>>>>
> > >>>>> /dev/mrg
> > >>>>>
> > >>>>>
> > >>>>> -----Original Message-----
> > >>>>> From: Christian Mittendorf
> > >>>>> [mailto:christian.mittendorf@freenet.de]
> > >>>>> Sent: Wednesday, July 12, 2006 1:50 PM
> > >>>>> To: cayenne-user@incubator.apache.org
> > >>>>> Subject: Re: Duplicate Key Problem
> > >>>>>
> > >>>>>
> > >>>>> We don't use the autoincrement from MySQL.
> > >>>>>
> > >>>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
> > >>>>>
> > >>>>>> Are you using MySQL's autoincrement feature for the PKs or
the
> > >>>>>> auto_pk_support table from Cayenne?
> > >>>>>>
> > >>>>>> -----Original Message-----
> > >>>>>> From: Christian Mittendorf
> > >>>>>> [mailto:christian.mittendorf@freenet.de]
> > >>>>>> Sent: Wednesday, July 12, 2006 4:29 AM
> > >>>>>> To: cayenne-user@incubator.apache.org
> > >>>>>> Subject: Duplicate Key Problem
> > >>>>>>
> > >>>>>>
> > >>>>>> Hello!
> > >>>>>>
> > >>>>>> We are running a MySQL 5.0 server using InnoDB tables for our
> > >>>>>> applications. We have multiple web applications accessing
this
> > >>>>>> same
> > >>>>>> database and the system is running smooth and without
problems
> > >>>>>> (almost).
> > >>>>>>
> > >>>>>> But yesterday we were experiencing some strange errors.
During
> > >>>>>> the
> > >>>>>> afternoon some CayenneRuntimeExceptions appeared in the log
file,
> > >>>>>> which were caused by:
> > >>>>>>
> > >>>>>> Caused by: java.sql.SQLException: null, message from server:
> > >>>>>> "Duplicate entry '4353880' for key 1"
> > >>>>>>
> > >>>>>> A bit later other CayenneRuntimeException appeared. For those
> > >>>>>> exceptions the cause was:
> > >>>>>>
> > >>>>>> Caused by: java.sql.SQLException: Deadlock found when trying
> > >>>>>> to get
> > >>>>>> lock; Try restarting transaction, message from server: "Lock
> > >>>>>> wait
> > >>>>>> timeout exceeded; try restarting transaction"
> > >>>>>>
> > >>>>>> I'm now wondering, are both errors related to each other?
> > >>>>>>
> > >>>>>> There are, from my point of view, two possible causes for the
> > >>>>>> "Duplicate entry..." message:
> > >>>>>>
> > >>>>>> - two Insert statements on the same object, which might be
> > >>>>>> possible
> > >>>>>> if the application is clustered, which our applications are
> > >>>>>> not, or
> > >>>>>> - dual use of the same key in different objects
> > >>>>>>
> > >>>>>> Has anybody else experienced something like that? Are there
any
> > >>>>>> hints
> > >>>>>> what I might do to avoid such situations?
> > >>>>>>
> > >>>>>> Christian
> > >>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>>
> > >>>>
> > >>>>
> > >>>
> > >>
> > >
> > > Craig Russell
> > > Architect, Sun Java Enterprise System
http://java.sun.com/products/jdo
> > > 408 276-5638 mailto:Craig.Russell@sun.com
> > > P.S. A good JDO? O, Gasp!
> > >
> >
> >
>
Re: Re: Duplicate Key Problem
Posted by Neil Pierson <ne...@gmail.com>.
As an aside, MySQL has changed much since a few years ago.
When asked to store a value in a numeric column that is outside the data
type's allowable range, MySQL's behavior depends on the SQL mode in effect
at the time. For example, if no restrictive modes are enabled, MySQL clips
the value to the appropriate endpoint of the range and stores the resulting
value instead. However, if the mode is set to TRADITIONAL, MySQL rejects a
value that is out of range with an error, and the insert fails, in
accordance with the SQL standard.
In non-strict mode, when an out-of-range value is assigned to an integer
column, MySQL stores the value representing the corresponding endpoint of
the column data type range.
If you assign a value to a CHAR or VARCHAR column that exceeds the column's
maximum length, the value is truncated to fit. If the truncated characters
are not spaces, a warning is generated. For truncation of non-space
characters, you can cause an error to occur (rather than a warning) and
suppress insertion of the value by using strict SQL mode -- if MySQL is
running in strict mode, values that exceed the column length are *not stored
*, and an error results.
As of 5.0.2, the server requires that month and day values be legal, and not
merely in the range 1 to 12 and 1 to 31, respectively. With strict mode
disabled, invalid dates such as '2004-04-31' are converted to
'0000-00-00'and a warning is generated. With strict mode enabled,
invalid dates generate
an error.
The current release of MySQL is 5.0.22; 5.1 is in beta.
On 7/14/06, Michael Gentry <bl...@gmail.com> wrote:
>
> I stopped using MySQL for my local stuff a few years ago, especially
> after I found this little jewel:
>
> mysql> create table test (
> -> numberField numeric(4,2),
> -> stringField varchar(4),
> -> notNullField varchar(4) not null,
> -> dateField date);
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> insert into test (numberField, stringField, dateField)
> -> values (10000000, 'hello', '2/31/2006');
> Query OK, 1 row affected (0.02 sec)
>
> mysql> select * from test;
> +-------------+-------------+--------------+------------+
> | numberField | stringField | notNullField | dateField |
> +-------------+-------------+--------------+------------+
> | 999.99 | hell | | 0000-00-00 |
> +-------------+-------------+--------------+------------+
> 1 row in set (0.00 sec)
>
> So, MySQL will a) alter numbers for you (no error), b) alter strings
> for you (no error), c) allow nulls for NOT NULL columns (no error),
> and d) zero out invalid dates (no error). I still think MySQL can be
> great for read-mostly information due to it's speed, but I want
> something more robust for anything more important (especially
> user-entered data).
>
> I'm thinking Craig's suggestion might be OK, even though it just
> doesn't feel right. Maybe give it 3 chances to obtain a primary key
> before aborting? (Make it configurable, of course, but 3 as a
> default.) Toss a few delays in, too?
>
> Thanks,
>
> /dev/mrg
>
>
> On 7/13/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > Our current more deterministic strategy worked ok, (I guess partly
> > because most database adapters are sequence-based, and don't have to
> > deal with locking). But I am certainly not a fan of pessimistic
> > locking and considered a strategy similar to what Craig outlined some
> > time ago as well.
> >
> > I guess we can make it an option for MySQLAdapter (the only one that
> > uses explicit locking) and use it with a hard limit on a number of
> > conflicts.
> >
> > Andrus
> >
> >
> > On Jul 13, 2006, at 4:54 PM, Craig L Russell wrote:
> > > Hi,
> > >
> > > This might be a really random suggestion, but couldn't you use the
> > > following strategy, even with autocommit=true?
> > >
> > > public long getNextKey() {
> > > boolean conflict = true;
> > > while (conflict) {
> > > key = SELECT current_key_value from auto_pk_table
> > > next_key = key + increment;
> > > UPDATE auto_pk_table SET current_key_value = next_key_value WHERE
> > > current_key_value = key
> > > conflict = 0 rows updated
> > > }
> > > return next_key;
> > > }
> > >
> > > All you worry about here is making sure that your increment is
> > > large enough to avoid frequent conflicts.
> > >
> > > Craig
> > >
> > > On Jul 13, 2006, at 6:17 AM, Gentry, Michael (Contractor) wrote:
> > >
> > >> The basic strategy to refresh the primary key cache in MySQL is:
> > >>
> > >> * lock table
> > >> * select next key values from auto_pk_support
> > >> * update auto_pk_support with a new next key (20 keys by default)
> > >> * unlock table
> > >>
> > >> I was just looking at the MySQL docs for "LOCK TABLES" and it
> > >> appears to
> > >> work differently with InnoDB. Which DB storage type are you using?
> > >> Also, Andrus' suggestion that there could've been a failure with the
> > >> connection going down could be correct. It would've happened between
> > >> select and update above. An application got 20 PKs, but the
> > >> connection
> > >> was broken before it could update the PK table. Another application
> > >> comes along and gets the same 20 PKs.
> > >>
> > >> Any chance you can use PostgreSQL? It uses sequences which are
> > >> atomic
> > >> operations.
> > >>
> > >> Thanks,
> > >>
> > >> /dev/mrg
> > >>
> > >> PS. Andrus: If a connection is broken and then Cayenne auto-
> > >> reconnects,
> > >> does it discard the PK cache? (I'm guessing not, which could explain
> > >> this.)
> > >>
> > >>
> > >> -----Original Message-----
> > >> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> > >> Sent: Thursday, July 13, 2006 6:23 AM
> > >> To: cayenne-user@incubator.apache.org
> > >> Subject: Re: Duplicate Key Problem
> > >>
> > >>
> > >> I haven't found any hint to something abnormal in my log files yet.
> > >> However, we experienced the duplicate key problem some minutes ago
> > >> and I was able to track down the problem a bit.
> > >>
> > >> Affected is only one single WebApp and these "Duplicat entry" errors
> > >> occured since yesterday, about 15:30. If I search for this exception
> > >> I can find this ID list:
> > >>
> > >> $ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048820' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048821' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048822' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048823' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048824' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048825' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048826' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048827' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048828' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048829' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048830' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048831' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048832' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048833' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048834' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048835' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048836' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048837' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048836' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048838' for key 1"
> > >> Caused by: java.sql.SQLException: null, message from server:
> > >> "Duplicate entry '17048839' for key 1"
> > >>
> > >> It seems as if one package of PKs (17048820 - 17048839 ) was double
> > >> used.
> > >> How does the PK magic of Cayenne work? Does each DataContext get its
> > >> own set of IDs?
> > >>
> > >>
> > >> Christian
> > >>
> > >>
> > >>
> > >> Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
> > >>
> > >>> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
> > >>> course if the connection goes down or something else equally bad
> > >>> happens during unlock, then you can end up locking the table. I'll
> > >>> be curious to know what exactly happened, as we may improve the
> > >>> unlock reliability as a result.
> > >>>
> > >>> Andrus
> > >>>
> > >>>
> > >>> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
> > >>>
> > >>>> Yes, all of our applications on this database are cayenne based.
> > >>>> I will have a look at the logs tomorrow and see if I can find some
> > >>>> other exceptions...
> > >>>>
> > >>>> Thanks,
> > >>>> Christian
> > >>>>
> > >>>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
> > >>>>
> > >>>>> These could be related, then. I know the MySQL adapter has to
> > >>>>> lock the
> > >>>>> auto_pk_support table to generate keys and perhaps something
> > >>>>> happened to
> > >>>>> leave the table locked. Did you see any other exceptions?
> > >>>>>
> > >>>>> Also, you said you have multiple applications hitting the same
> > >>>>> database.
> > >>>>> Are all of these applications Cayenne-based?
> > >>>>>
> > >>>>> Thanks,
> > >>>>>
> > >>>>> /dev/mrg
> > >>>>>
> > >>>>>
> > >>>>> -----Original Message-----
> > >>>>> From: Christian Mittendorf
> > >>>>> [mailto:christian.mittendorf@freenet.de]
> > >>>>> Sent: Wednesday, July 12, 2006 1:50 PM
> > >>>>> To: cayenne-user@incubator.apache.org
> > >>>>> Subject: Re: Duplicate Key Problem
> > >>>>>
> > >>>>>
> > >>>>> We don't use the autoincrement from MySQL.
> > >>>>>
> > >>>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
> > >>>>>
> > >>>>>> Are you using MySQL's autoincrement feature for the PKs or the
> > >>>>>> auto_pk_support table from Cayenne?
> > >>>>>>
> > >>>>>> -----Original Message-----
> > >>>>>> From: Christian Mittendorf
> > >>>>>> [mailto:christian.mittendorf@freenet.de]
> > >>>>>> Sent: Wednesday, July 12, 2006 4:29 AM
> > >>>>>> To: cayenne-user@incubator.apache.org
> > >>>>>> Subject: Duplicate Key Problem
> > >>>>>>
> > >>>>>>
> > >>>>>> Hello!
> > >>>>>>
> > >>>>>> We are running a MySQL 5.0 server using InnoDB tables for our
> > >>>>>> applications. We have multiple web applications accessing this
> > >>>>>> same
> > >>>>>> database and the system is running smooth and without problems
> > >>>>>> (almost).
> > >>>>>>
> > >>>>>> But yesterday we were experiencing some strange errors. During
> > >>>>>> the
> > >>>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
> > >>>>>> which were caused by:
> > >>>>>>
> > >>>>>> Caused by: java.sql.SQLException: null, message from server:
> > >>>>>> "Duplicate entry '4353880' for key 1"
> > >>>>>>
> > >>>>>> A bit later other CayenneRuntimeException appeared. For those
> > >>>>>> exceptions the cause was:
> > >>>>>>
> > >>>>>> Caused by: java.sql.SQLException: Deadlock found when trying
> > >>>>>> to get
> > >>>>>> lock; Try restarting transaction, message from server: "Lock
> > >>>>>> wait
> > >>>>>> timeout exceeded; try restarting transaction"
> > >>>>>>
> > >>>>>> I'm now wondering, are both errors related to each other?
> > >>>>>>
> > >>>>>> There are, from my point of view, two possible causes for the
> > >>>>>> "Duplicate entry..." message:
> > >>>>>>
> > >>>>>> - two Insert statements on the same object, which might be
> > >>>>>> possible
> > >>>>>> if the application is clustered, which our applications are
> > >>>>>> not, or
> > >>>>>> - dual use of the same key in different objects
> > >>>>>>
> > >>>>>> Has anybody else experienced something like that? Are there any
> > >>>>>> hints
> > >>>>>> what I might do to avoid such situations?
> > >>>>>>
> > >>>>>> Christian
> > >>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>>
> > >>>>
> > >>>>
> > >>>
> > >>
> > >
> > > Craig Russell
> > > Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> > > 408 276-5638 mailto:Craig.Russell@sun.com
> > > P.S. A good JDO? O, Gasp!
> > >
> >
> >
>
Re: Re: Duplicate Key Problem
Posted by Michael Gentry <bl...@gmail.com>.
I stopped using MySQL for my local stuff a few years ago, especially
after I found this little jewel:
mysql> create table test (
-> numberField numeric(4,2),
-> stringField varchar(4),
-> notNullField varchar(4) not null,
-> dateField date);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test (numberField, stringField, dateField)
-> values (10000000, 'hello', '2/31/2006');
Query OK, 1 row affected (0.02 sec)
mysql> select * from test;
+-------------+-------------+--------------+------------+
| numberField | stringField | notNullField | dateField |
+-------------+-------------+--------------+------------+
| 999.99 | hell | | 0000-00-00 |
+-------------+-------------+--------------+------------+
1 row in set (0.00 sec)
So, MySQL will a) alter numbers for you (no error), b) alter strings
for you (no error), c) allow nulls for NOT NULL columns (no error),
and d) zero out invalid dates (no error). I still think MySQL can be
great for read-mostly information due to it's speed, but I want
something more robust for anything more important (especially
user-entered data).
I'm thinking Craig's suggestion might be OK, even though it just
doesn't feel right. Maybe give it 3 chances to obtain a primary key
before aborting? (Make it configurable, of course, but 3 as a
default.) Toss a few delays in, too?
Thanks,
/dev/mrg
On 7/13/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> Our current more deterministic strategy worked ok, (I guess partly
> because most database adapters are sequence-based, and don't have to
> deal with locking). But I am certainly not a fan of pessimistic
> locking and considered a strategy similar to what Craig outlined some
> time ago as well.
>
> I guess we can make it an option for MySQLAdapter (the only one that
> uses explicit locking) and use it with a hard limit on a number of
> conflicts.
>
> Andrus
>
>
> On Jul 13, 2006, at 4:54 PM, Craig L Russell wrote:
> > Hi,
> >
> > This might be a really random suggestion, but couldn't you use the
> > following strategy, even with autocommit=true?
> >
> > public long getNextKey() {
> > boolean conflict = true;
> > while (conflict) {
> > key = SELECT current_key_value from auto_pk_table
> > next_key = key + increment;
> > UPDATE auto_pk_table SET current_key_value = next_key_value WHERE
> > current_key_value = key
> > conflict = 0 rows updated
> > }
> > return next_key;
> > }
> >
> > All you worry about here is making sure that your increment is
> > large enough to avoid frequent conflicts.
> >
> > Craig
> >
> > On Jul 13, 2006, at 6:17 AM, Gentry, Michael (Contractor) wrote:
> >
> >> The basic strategy to refresh the primary key cache in MySQL is:
> >>
> >> * lock table
> >> * select next key values from auto_pk_support
> >> * update auto_pk_support with a new next key (20 keys by default)
> >> * unlock table
> >>
> >> I was just looking at the MySQL docs for "LOCK TABLES" and it
> >> appears to
> >> work differently with InnoDB. Which DB storage type are you using?
> >> Also, Andrus' suggestion that there could've been a failure with the
> >> connection going down could be correct. It would've happened between
> >> select and update above. An application got 20 PKs, but the
> >> connection
> >> was broken before it could update the PK table. Another application
> >> comes along and gets the same 20 PKs.
> >>
> >> Any chance you can use PostgreSQL? It uses sequences which are
> >> atomic
> >> operations.
> >>
> >> Thanks,
> >>
> >> /dev/mrg
> >>
> >> PS. Andrus: If a connection is broken and then Cayenne auto-
> >> reconnects,
> >> does it discard the PK cache? (I'm guessing not, which could explain
> >> this.)
> >>
> >>
> >> -----Original Message-----
> >> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> >> Sent: Thursday, July 13, 2006 6:23 AM
> >> To: cayenne-user@incubator.apache.org
> >> Subject: Re: Duplicate Key Problem
> >>
> >>
> >> I haven't found any hint to something abnormal in my log files yet.
> >> However, we experienced the duplicate key problem some minutes ago
> >> and I was able to track down the problem a bit.
> >>
> >> Affected is only one single WebApp and these "Duplicat entry" errors
> >> occured since yesterday, about 15:30. If I search for this exception
> >> I can find this ID list:
> >>
> >> $ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048820' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048821' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048822' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048823' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048824' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048825' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048826' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048827' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048828' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048829' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048830' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048831' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048832' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048833' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048834' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048835' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048836' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048837' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048836' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048838' for key 1"
> >> Caused by: java.sql.SQLException: null, message from server:
> >> "Duplicate entry '17048839' for key 1"
> >>
> >> It seems as if one package of PKs (17048820 - 17048839 ) was double
> >> used.
> >> How does the PK magic of Cayenne work? Does each DataContext get its
> >> own set of IDs?
> >>
> >>
> >> Christian
> >>
> >>
> >>
> >> Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
> >>
> >>> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
> >>> course if the connection goes down or something else equally bad
> >>> happens during unlock, then you can end up locking the table. I'll
> >>> be curious to know what exactly happened, as we may improve the
> >>> unlock reliability as a result.
> >>>
> >>> Andrus
> >>>
> >>>
> >>> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
> >>>
> >>>> Yes, all of our applications on this database are cayenne based.
> >>>> I will have a look at the logs tomorrow and see if I can find some
> >>>> other exceptions...
> >>>>
> >>>> Thanks,
> >>>> Christian
> >>>>
> >>>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
> >>>>
> >>>>> These could be related, then. I know the MySQL adapter has to
> >>>>> lock the
> >>>>> auto_pk_support table to generate keys and perhaps something
> >>>>> happened to
> >>>>> leave the table locked. Did you see any other exceptions?
> >>>>>
> >>>>> Also, you said you have multiple applications hitting the same
> >>>>> database.
> >>>>> Are all of these applications Cayenne-based?
> >>>>>
> >>>>> Thanks,
> >>>>>
> >>>>> /dev/mrg
> >>>>>
> >>>>>
> >>>>> -----Original Message-----
> >>>>> From: Christian Mittendorf
> >>>>> [mailto:christian.mittendorf@freenet.de]
> >>>>> Sent: Wednesday, July 12, 2006 1:50 PM
> >>>>> To: cayenne-user@incubator.apache.org
> >>>>> Subject: Re: Duplicate Key Problem
> >>>>>
> >>>>>
> >>>>> We don't use the autoincrement from MySQL.
> >>>>>
> >>>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
> >>>>>
> >>>>>> Are you using MySQL's autoincrement feature for the PKs or the
> >>>>>> auto_pk_support table from Cayenne?
> >>>>>>
> >>>>>> -----Original Message-----
> >>>>>> From: Christian Mittendorf
> >>>>>> [mailto:christian.mittendorf@freenet.de]
> >>>>>> Sent: Wednesday, July 12, 2006 4:29 AM
> >>>>>> To: cayenne-user@incubator.apache.org
> >>>>>> Subject: Duplicate Key Problem
> >>>>>>
> >>>>>>
> >>>>>> Hello!
> >>>>>>
> >>>>>> We are running a MySQL 5.0 server using InnoDB tables for our
> >>>>>> applications. We have multiple web applications accessing this
> >>>>>> same
> >>>>>> database and the system is running smooth and without problems
> >>>>>> (almost).
> >>>>>>
> >>>>>> But yesterday we were experiencing some strange errors. During
> >>>>>> the
> >>>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
> >>>>>> which were caused by:
> >>>>>>
> >>>>>> Caused by: java.sql.SQLException: null, message from server:
> >>>>>> "Duplicate entry '4353880' for key 1"
> >>>>>>
> >>>>>> A bit later other CayenneRuntimeException appeared. For those
> >>>>>> exceptions the cause was:
> >>>>>>
> >>>>>> Caused by: java.sql.SQLException: Deadlock found when trying
> >>>>>> to get
> >>>>>> lock; Try restarting transaction, message from server: "Lock
> >>>>>> wait
> >>>>>> timeout exceeded; try restarting transaction"
> >>>>>>
> >>>>>> I'm now wondering, are both errors related to each other?
> >>>>>>
> >>>>>> There are, from my point of view, two possible causes for the
> >>>>>> "Duplicate entry..." message:
> >>>>>>
> >>>>>> - two Insert statements on the same object, which might be
> >>>>>> possible
> >>>>>> if the application is clustered, which our applications are
> >>>>>> not, or
> >>>>>> - dual use of the same key in different objects
> >>>>>>
> >>>>>> Has anybody else experienced something like that? Are there any
> >>>>>> hints
> >>>>>> what I might do to avoid such situations?
> >>>>>>
> >>>>>> Christian
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>
> >>>>
> >>>>
> >>>
> >>
> >
> > Craig Russell
> > Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> > 408 276-5638 mailto:Craig.Russell@sun.com
> > P.S. A good JDO? O, Gasp!
> >
>
>
Re: Duplicate Key Problem
Posted by Andrus Adamchik <an...@objectstyle.org>.
Our current more deterministic strategy worked ok, (I guess partly
because most database adapters are sequence-based, and don't have to
deal with locking). But I am certainly not a fan of pessimistic
locking and considered a strategy similar to what Craig outlined some
time ago as well.
I guess we can make it an option for MySQLAdapter (the only one that
uses explicit locking) and use it with a hard limit on a number of
conflicts.
Andrus
On Jul 13, 2006, at 4:54 PM, Craig L Russell wrote:
> Hi,
>
> This might be a really random suggestion, but couldn't you use the
> following strategy, even with autocommit=true?
>
> public long getNextKey() {
> boolean conflict = true;
> while (conflict) {
> key = SELECT current_key_value from auto_pk_table
> next_key = key + increment;
> UPDATE auto_pk_table SET current_key_value = next_key_value WHERE
> current_key_value = key
> conflict = 0 rows updated
> }
> return next_key;
> }
>
> All you worry about here is making sure that your increment is
> large enough to avoid frequent conflicts.
>
> Craig
>
> On Jul 13, 2006, at 6:17 AM, Gentry, Michael (Contractor) wrote:
>
>> The basic strategy to refresh the primary key cache in MySQL is:
>>
>> * lock table
>> * select next key values from auto_pk_support
>> * update auto_pk_support with a new next key (20 keys by default)
>> * unlock table
>>
>> I was just looking at the MySQL docs for "LOCK TABLES" and it
>> appears to
>> work differently with InnoDB. Which DB storage type are you using?
>> Also, Andrus' suggestion that there could've been a failure with the
>> connection going down could be correct. It would've happened between
>> select and update above. An application got 20 PKs, but the
>> connection
>> was broken before it could update the PK table. Another application
>> comes along and gets the same 20 PKs.
>>
>> Any chance you can use PostgreSQL? It uses sequences which are
>> atomic
>> operations.
>>
>> Thanks,
>>
>> /dev/mrg
>>
>> PS. Andrus: If a connection is broken and then Cayenne auto-
>> reconnects,
>> does it discard the PK cache? (I'm guessing not, which could explain
>> this.)
>>
>>
>> -----Original Message-----
>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>> Sent: Thursday, July 13, 2006 6:23 AM
>> To: cayenne-user@incubator.apache.org
>> Subject: Re: Duplicate Key Problem
>>
>>
>> I haven't found any hint to something abnormal in my log files yet.
>> However, we experienced the duplicate key problem some minutes ago
>> and I was able to track down the problem a bit.
>>
>> Affected is only one single WebApp and these "Duplicat entry" errors
>> occured since yesterday, about 15:30. If I search for this exception
>> I can find this ID list:
>>
>> $ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048820' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048821' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048822' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048823' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048824' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048825' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048826' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048827' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048828' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048829' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048830' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048831' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048832' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048833' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048834' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048835' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048836' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048837' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048836' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048838' for key 1"
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '17048839' for key 1"
>>
>> It seems as if one package of PKs (17048820 - 17048839 ) was double
>> used.
>> How does the PK magic of Cayenne work? Does each DataContext get its
>> own set of IDs?
>>
>>
>> Christian
>>
>>
>>
>> Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
>>
>>> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
>>> course if the connection goes down or something else equally bad
>>> happens during unlock, then you can end up locking the table. I'll
>>> be curious to know what exactly happened, as we may improve the
>>> unlock reliability as a result.
>>>
>>> Andrus
>>>
>>>
>>> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
>>>
>>>> Yes, all of our applications on this database are cayenne based.
>>>> I will have a look at the logs tomorrow and see if I can find some
>>>> other exceptions...
>>>>
>>>> Thanks,
>>>> Christian
>>>>
>>>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
>>>>
>>>>> These could be related, then. I know the MySQL adapter has to
>>>>> lock the
>>>>> auto_pk_support table to generate keys and perhaps something
>>>>> happened to
>>>>> leave the table locked. Did you see any other exceptions?
>>>>>
>>>>> Also, you said you have multiple applications hitting the same
>>>>> database.
>>>>> Are all of these applications Cayenne-based?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> /dev/mrg
>>>>>
>>>>>
>>>>> -----Original Message-----
>>>>> From: Christian Mittendorf
>>>>> [mailto:christian.mittendorf@freenet.de]
>>>>> Sent: Wednesday, July 12, 2006 1:50 PM
>>>>> To: cayenne-user@incubator.apache.org
>>>>> Subject: Re: Duplicate Key Problem
>>>>>
>>>>>
>>>>> We don't use the autoincrement from MySQL.
>>>>>
>>>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>>>>>
>>>>>> Are you using MySQL's autoincrement feature for the PKs or the
>>>>>> auto_pk_support table from Cayenne?
>>>>>>
>>>>>> -----Original Message-----
>>>>>> From: Christian Mittendorf
>>>>>> [mailto:christian.mittendorf@freenet.de]
>>>>>> Sent: Wednesday, July 12, 2006 4:29 AM
>>>>>> To: cayenne-user@incubator.apache.org
>>>>>> Subject: Duplicate Key Problem
>>>>>>
>>>>>>
>>>>>> Hello!
>>>>>>
>>>>>> We are running a MySQL 5.0 server using InnoDB tables for our
>>>>>> applications. We have multiple web applications accessing this
>>>>>> same
>>>>>> database and the system is running smooth and without problems
>>>>>> (almost).
>>>>>>
>>>>>> But yesterday we were experiencing some strange errors. During
>>>>>> the
>>>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>>>>>> which were caused by:
>>>>>>
>>>>>> Caused by: java.sql.SQLException: null, message from server:
>>>>>> "Duplicate entry '4353880' for key 1"
>>>>>>
>>>>>> A bit later other CayenneRuntimeException appeared. For those
>>>>>> exceptions the cause was:
>>>>>>
>>>>>> Caused by: java.sql.SQLException: Deadlock found when trying
>>>>>> to get
>>>>>> lock; Try restarting transaction, message from server: "Lock
>>>>>> wait
>>>>>> timeout exceeded; try restarting transaction"
>>>>>>
>>>>>> I'm now wondering, are both errors related to each other?
>>>>>>
>>>>>> There are, from my point of view, two possible causes for the
>>>>>> "Duplicate entry..." message:
>>>>>>
>>>>>> - two Insert statements on the same object, which might be
>>>>>> possible
>>>>>> if the application is clustered, which our applications are
>>>>>> not, or
>>>>>> - dual use of the same key in different objects
>>>>>>
>>>>>> Has anybody else experienced something like that? Are there any
>>>>>> hints
>>>>>> what I might do to avoid such situations?
>>>>>>
>>>>>> Christian
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
RE: Duplicate Key Problem
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
Yes, the drawback is if you keep getting conflicts (which hopefully
would be minimal).
-----Original Message-----
From: Craig.Russell@Sun.COM [mailto:Craig.Russell@Sun.COM]
Sent: Thursday, July 13, 2006 4:54 PM
To: cayenne-user@incubator.apache.org
Subject: Re: Duplicate Key Problem
Hi,
This might be a really random suggestion, but couldn't you use the
following strategy, even with autocommit=true?
public long getNextKey() {
boolean conflict = true;
while (conflict) {
key = SELECT current_key_value from auto_pk_table
next_key = key + increment;
UPDATE auto_pk_table SET current_key_value = next_key_value WHERE
current_key_value = key
conflict = 0 rows updated
}
return next_key;
}
All you worry about here is making sure that your increment is large
enough to avoid frequent conflicts.
Craig
On Jul 13, 2006, at 6:17 AM, Gentry, Michael (Contractor) wrote:
> The basic strategy to refresh the primary key cache in MySQL is:
>
> * lock table
> * select next key values from auto_pk_support
> * update auto_pk_support with a new next key (20 keys by default)
> * unlock table
>
> I was just looking at the MySQL docs for "LOCK TABLES" and it
> appears to
> work differently with InnoDB. Which DB storage type are you using?
> Also, Andrus' suggestion that there could've been a failure with the
> connection going down could be correct. It would've happened between
> select and update above. An application got 20 PKs, but the
> connection
> was broken before it could update the PK table. Another application
> comes along and gets the same 20 PKs.
>
> Any chance you can use PostgreSQL? It uses sequences which are atomic
> operations.
>
> Thanks,
>
> /dev/mrg
>
> PS. Andrus: If a connection is broken and then Cayenne auto-
> reconnects,
> does it discard the PK cache? (I'm guessing not, which could explain
> this.)
>
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Thursday, July 13, 2006 6:23 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
> I haven't found any hint to something abnormal in my log files yet.
> However, we experienced the duplicate key problem some minutes ago
> and I was able to track down the problem a bit.
>
> Affected is only one single WebApp and these "Duplicat entry" errors
> occured since yesterday, about 15:30. If I search for this exception
> I can find this ID list:
>
> $ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048820' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048821' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048822' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048823' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048824' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048825' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048826' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048827' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048828' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048829' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048830' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048831' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048832' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048833' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048834' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048835' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048836' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048837' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048836' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048838' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048839' for key 1"
>
> It seems as if one package of PKs (17048820 - 17048839 ) was double
> used.
> How does the PK magic of Cayenne work? Does each DataContext get its
> own set of IDs?
>
>
> Christian
>
>
>
> Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
>
>> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
>> course if the connection goes down or something else equally bad
>> happens during unlock, then you can end up locking the table. I'll
>> be curious to know what exactly happened, as we may improve the
>> unlock reliability as a result.
>>
>> Andrus
>>
>>
>> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
>>
>>> Yes, all of our applications on this database are cayenne based.
>>> I will have a look at the logs tomorrow and see if I can find some
>>> other exceptions...
>>>
>>> Thanks,
>>> Christian
>>>
>>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
>>>
>>>> These could be related, then. I know the MySQL adapter has to
>>>> lock the
>>>> auto_pk_support table to generate keys and perhaps something
>>>> happened to
>>>> leave the table locked. Did you see any other exceptions?
>>>>
>>>> Also, you said you have multiple applications hitting the same
>>>> database.
>>>> Are all of these applications Cayenne-based?
>>>>
>>>> Thanks,
>>>>
>>>> /dev/mrg
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>>> Sent: Wednesday, July 12, 2006 1:50 PM
>>>> To: cayenne-user@incubator.apache.org
>>>> Subject: Re: Duplicate Key Problem
>>>>
>>>>
>>>> We don't use the autoincrement from MySQL.
>>>>
>>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>>>>
>>>>> Are you using MySQL's autoincrement feature for the PKs or the
>>>>> auto_pk_support table from Cayenne?
>>>>>
>>>>> -----Original Message-----
>>>>> From: Christian Mittendorf
>>>>> [mailto:christian.mittendorf@freenet.de]
>>>>> Sent: Wednesday, July 12, 2006 4:29 AM
>>>>> To: cayenne-user@incubator.apache.org
>>>>> Subject: Duplicate Key Problem
>>>>>
>>>>>
>>>>> Hello!
>>>>>
>>>>> We are running a MySQL 5.0 server using InnoDB tables for our
>>>>> applications. We have multiple web applications accessing this
>>>>> same
>>>>> database and the system is running smooth and without problems
>>>>> (almost).
>>>>>
>>>>> But yesterday we were experiencing some strange errors. During the
>>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>>>>> which were caused by:
>>>>>
>>>>> Caused by: java.sql.SQLException: null, message from server:
>>>>> "Duplicate entry '4353880' for key 1"
>>>>>
>>>>> A bit later other CayenneRuntimeException appeared. For those
>>>>> exceptions the cause was:
>>>>>
>>>>> Caused by: java.sql.SQLException: Deadlock found when trying to
>>>>> get
>>>>> lock; Try restarting transaction, message from server: "Lock wait
>>>>> timeout exceeded; try restarting transaction"
>>>>>
>>>>> I'm now wondering, are both errors related to each other?
>>>>>
>>>>> There are, from my point of view, two possible causes for the
>>>>> "Duplicate entry..." message:
>>>>>
>>>>> - two Insert statements on the same object, which might be
>>>>> possible
>>>>> if the application is clustered, which our applications are
>>>>> not, or
>>>>> - dual use of the same key in different objects
>>>>>
>>>>> Has anybody else experienced something like that? Are there any
>>>>> hints
>>>>> what I might do to avoid such situations?
>>>>>
>>>>> Christian
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Duplicate Key Problem
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi,
This might be a really random suggestion, but couldn't you use the
following strategy, even with autocommit=true?
public long getNextKey() {
boolean conflict = true;
while (conflict) {
key = SELECT current_key_value from auto_pk_table
next_key = key + increment;
UPDATE auto_pk_table SET current_key_value = next_key_value WHERE
current_key_value = key
conflict = 0 rows updated
}
return next_key;
}
All you worry about here is making sure that your increment is large
enough to avoid frequent conflicts.
Craig
On Jul 13, 2006, at 6:17 AM, Gentry, Michael (Contractor) wrote:
> The basic strategy to refresh the primary key cache in MySQL is:
>
> * lock table
> * select next key values from auto_pk_support
> * update auto_pk_support with a new next key (20 keys by default)
> * unlock table
>
> I was just looking at the MySQL docs for "LOCK TABLES" and it
> appears to
> work differently with InnoDB. Which DB storage type are you using?
> Also, Andrus' suggestion that there could've been a failure with the
> connection going down could be correct. It would've happened between
> select and update above. An application got 20 PKs, but the
> connection
> was broken before it could update the PK table. Another application
> comes along and gets the same 20 PKs.
>
> Any chance you can use PostgreSQL? It uses sequences which are atomic
> operations.
>
> Thanks,
>
> /dev/mrg
>
> PS. Andrus: If a connection is broken and then Cayenne auto-
> reconnects,
> does it discard the PK cache? (I'm guessing not, which could explain
> this.)
>
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Thursday, July 13, 2006 6:23 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
> I haven't found any hint to something abnormal in my log files yet.
> However, we experienced the duplicate key problem some minutes ago
> and I was able to track down the problem a bit.
>
> Affected is only one single WebApp and these "Duplicat entry" errors
> occured since yesterday, about 15:30. If I search for this exception
> I can find this ID list:
>
> $ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048820' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048821' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048822' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048823' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048824' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048825' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048826' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048827' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048828' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048829' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048830' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048831' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048832' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048833' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048834' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048835' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048836' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048837' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048836' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048838' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048839' for key 1"
>
> It seems as if one package of PKs (17048820 - 17048839 ) was double
> used.
> How does the PK magic of Cayenne work? Does each DataContext get its
> own set of IDs?
>
>
> Christian
>
>
>
> Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
>
>> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
>> course if the connection goes down or something else equally bad
>> happens during unlock, then you can end up locking the table. I'll
>> be curious to know what exactly happened, as we may improve the
>> unlock reliability as a result.
>>
>> Andrus
>>
>>
>> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
>>
>>> Yes, all of our applications on this database are cayenne based.
>>> I will have a look at the logs tomorrow and see if I can find some
>>> other exceptions...
>>>
>>> Thanks,
>>> Christian
>>>
>>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
>>>
>>>> These could be related, then. I know the MySQL adapter has to
>>>> lock the
>>>> auto_pk_support table to generate keys and perhaps something
>>>> happened to
>>>> leave the table locked. Did you see any other exceptions?
>>>>
>>>> Also, you said you have multiple applications hitting the same
>>>> database.
>>>> Are all of these applications Cayenne-based?
>>>>
>>>> Thanks,
>>>>
>>>> /dev/mrg
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>>> Sent: Wednesday, July 12, 2006 1:50 PM
>>>> To: cayenne-user@incubator.apache.org
>>>> Subject: Re: Duplicate Key Problem
>>>>
>>>>
>>>> We don't use the autoincrement from MySQL.
>>>>
>>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>>>>
>>>>> Are you using MySQL's autoincrement feature for the PKs or the
>>>>> auto_pk_support table from Cayenne?
>>>>>
>>>>> -----Original Message-----
>>>>> From: Christian Mittendorf
>>>>> [mailto:christian.mittendorf@freenet.de]
>>>>> Sent: Wednesday, July 12, 2006 4:29 AM
>>>>> To: cayenne-user@incubator.apache.org
>>>>> Subject: Duplicate Key Problem
>>>>>
>>>>>
>>>>> Hello!
>>>>>
>>>>> We are running a MySQL 5.0 server using InnoDB tables for our
>>>>> applications. We have multiple web applications accessing this
>>>>> same
>>>>> database and the system is running smooth and without problems
>>>>> (almost).
>>>>>
>>>>> But yesterday we were experiencing some strange errors. During the
>>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>>>>> which were caused by:
>>>>>
>>>>> Caused by: java.sql.SQLException: null, message from server:
>>>>> "Duplicate entry '4353880' for key 1"
>>>>>
>>>>> A bit later other CayenneRuntimeException appeared. For those
>>>>> exceptions the cause was:
>>>>>
>>>>> Caused by: java.sql.SQLException: Deadlock found when trying to
>>>>> get
>>>>> lock; Try restarting transaction, message from server: "Lock wait
>>>>> timeout exceeded; try restarting transaction"
>>>>>
>>>>> I'm now wondering, are both errors related to each other?
>>>>>
>>>>> There are, from my point of view, two possible causes for the
>>>>> "Duplicate entry..." message:
>>>>>
>>>>> - two Insert statements on the same object, which might be
>>>>> possible
>>>>> if the application is clustered, which our applications are
>>>>> not, or
>>>>> - dual use of the same key in different objects
>>>>>
>>>>> Has anybody else experienced something like that? Are there any
>>>>> hints
>>>>> what I might do to avoid such situations?
>>>>>
>>>>> Christian
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
Am 21.07.2006 um 17:16 schrieb Gentry, Michael ((Contractor)):
> Glad it is working right now. Are you using the new MySQL adapter
> Andrus put together for you?
No, not yet. We thought we will give it a try next week.
> Yes, Cayenne will catch the exception for the lost connection and
> try to
> automatically reconnect. That might be better than the JDBC driver
> auto-reconnecting, since Cayenne will know something "bad"
> happened. I
> don't know that this feature is documented anywhere. I discovered
> it by
> accident. I was testing my application on my laptop and had left it
> running inside Eclipse. When I went home, I put the laptop in standby
> mode totally forgetting about the application. Came back the next
> morning and tried using the application again and saw an exception
> with
> Cayenne handling it and automatically reconnecting. This also works
> well in deployment, since it will auto-reconnect when the DB server is
> rebooted.
That behaviour does fit perfectly to the picture we experienced. It
seems as if
the mysql connection is lost from time to time and that the mysql jdbc
driver did reconnect "in the background" automatically. Giving
Cayenne no
chance to recognize the lost. Cayenne would in that case continue to
work
and in the meantime some other process grabbed the same package of
primary keys
and our problems started.
Besides, I still haven't seen any "Duplicate key" messages from our
systems!
Thanks to all for this helpful discussion!
Christian
> /dev/mrg
>
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Friday, July 21, 2006 11:09 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
>
> Am 21.07.2006 um 16:14 schrieb Gentry, Michael ((Contractor)):
>
>> 4.0.12 of MySQL. I think autoReconnect=false is a good thing to try,
>> since Cayenne will catch connection exceptions and try to
>> reconnect on
>> its own.
>
> From what I can see so far, after we updated all apps some hours
> ago, it
> seems to be working fine.
>
> How would Cayenne handle a lost connection? Would that result into an
> Exception or would Cayenne take care, try to reconnect and try to
> work as
> expected?
>
> Is there some documentation available or do I have to play "Read the
> source, Luke", as Obi-Wan liked to say ;-)
>
> Thanks and have a nice weekend!
>
> Christian
>
>
>
>
>>
>> Thanks,
>>
>> /dev/mrg
>>
>>
>>
>> -----Original Message-----
>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>> Sent: Friday, July 21, 2006 9:56 AM
>> To: cayenne-user@incubator.apache.org
>> Subject: Re: Duplicate Key Problem
>>
>>
>> Am 13.07.2006 um 20:35 schrieb Andrus Adamchik:
>>
>>> BTW, I tried to reproduce PK generator getting an incorrect PK
>>> range on MySQL by emulating some load via JMeter. It never happens
>>> (at least on a single VM instance). I wrote a test case that throws
>>> an exception randomly on committing the user transaction. Still the
>>> application was able to recover from failed transactions and carry
>>> on processing other requests.
>>
>> May it be that the version of MySQL has some affects on what we
>> experience? We are currently using 5.0.18.
>>
>>
>>> I am curious what Michael finds in his tests.
>>
>> Me too, are there any results available?
>>
>>
>>> Anyways, I went ahead and added an explicit commit to the PK
>>> generator (that code, although I couldn't make it fail, still
>>> looked suspect) and posted new jars here:
>>>
>>> http://dev.objectstyle.org/~andrus/cayenne-07132006/
>>>
>>> Christian, I would appreciate if you could try this in your
>>> application and see if you still get those errors.
>>
>> We will update some applications in the next week, we haven't found
>> the time to do so this week.
>>
>> But there was some other idea we had discussed recently. At the
>> moment all of our apps use the "autoReconnect=true" feature of the
>> jdbc driver and after some discussions with colleagues we updated the
>> config to use "autoReconnect=false". If our problem is related to
>> MySQL loosing the connection I would expect to get some kind of "Lost
>> connection" errors instead of duplicate key messages.
>>
>>
>> Christian
>>
>>
>
RE: Duplicate Key Problem
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
Glad it is working right now. Are you using the new MySQL adapter
Andrus put together for you?
Yes, Cayenne will catch the exception for the lost connection and try to
automatically reconnect. That might be better than the JDBC driver
auto-reconnecting, since Cayenne will know something "bad" happened. I
don't know that this feature is documented anywhere. I discovered it by
accident. I was testing my application on my laptop and had left it
running inside Eclipse. When I went home, I put the laptop in standby
mode totally forgetting about the application. Came back the next
morning and tried using the application again and saw an exception with
Cayenne handling it and automatically reconnecting. This also works
well in deployment, since it will auto-reconnect when the DB server is
rebooted.
/dev/mrg
-----Original Message-----
From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
Sent: Friday, July 21, 2006 11:09 AM
To: cayenne-user@incubator.apache.org
Subject: Re: Duplicate Key Problem
Am 21.07.2006 um 16:14 schrieb Gentry, Michael ((Contractor)):
> 4.0.12 of MySQL. I think autoReconnect=false is a good thing to try,
> since Cayenne will catch connection exceptions and try to reconnect on
> its own.
From what I can see so far, after we updated all apps some hours
ago, it
seems to be working fine.
How would Cayenne handle a lost connection? Would that result into an
Exception or would Cayenne take care, try to reconnect and try to
work as
expected?
Is there some documentation available or do I have to play "Read the
source, Luke", as Obi-Wan liked to say ;-)
Thanks and have a nice weekend!
Christian
>
> Thanks,
>
> /dev/mrg
>
>
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Friday, July 21, 2006 9:56 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
> Am 13.07.2006 um 20:35 schrieb Andrus Adamchik:
>
>> BTW, I tried to reproduce PK generator getting an incorrect PK
>> range on MySQL by emulating some load via JMeter. It never happens
>> (at least on a single VM instance). I wrote a test case that throws
>> an exception randomly on committing the user transaction. Still the
>> application was able to recover from failed transactions and carry
>> on processing other requests.
>
> May it be that the version of MySQL has some affects on what we
> experience? We are currently using 5.0.18.
>
>
>> I am curious what Michael finds in his tests.
>
> Me too, are there any results available?
>
>
>> Anyways, I went ahead and added an explicit commit to the PK
>> generator (that code, although I couldn't make it fail, still
>> looked suspect) and posted new jars here:
>>
>> http://dev.objectstyle.org/~andrus/cayenne-07132006/
>>
>> Christian, I would appreciate if you could try this in your
>> application and see if you still get those errors.
>
> We will update some applications in the next week, we haven't found
> the time to do so this week.
>
> But there was some other idea we had discussed recently. At the
> moment all of our apps use the "autoReconnect=true" feature of the
> jdbc driver and after some discussions with colleagues we updated the
> config to use "autoReconnect=false". If our problem is related to
> MySQL loosing the connection I would expect to get some kind of "Lost
> connection" errors instead of duplicate key messages.
>
>
> Christian
>
>
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
Am 21.07.2006 um 16:14 schrieb Gentry, Michael ((Contractor)):
> 4.0.12 of MySQL. I think autoReconnect=false is a good thing to try,
> since Cayenne will catch connection exceptions and try to reconnect on
> its own.
From what I can see so far, after we updated all apps some hours
ago, it
seems to be working fine.
How would Cayenne handle a lost connection? Would that result into an
Exception or would Cayenne take care, try to reconnect and try to
work as
expected?
Is there some documentation available or do I have to play "Read the
source, Luke", as Obi-Wan liked to say ;-)
Thanks and have a nice weekend!
Christian
>
> Thanks,
>
> /dev/mrg
>
>
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Friday, July 21, 2006 9:56 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
> Am 13.07.2006 um 20:35 schrieb Andrus Adamchik:
>
>> BTW, I tried to reproduce PK generator getting an incorrect PK
>> range on MySQL by emulating some load via JMeter. It never happens
>> (at least on a single VM instance). I wrote a test case that throws
>> an exception randomly on committing the user transaction. Still the
>> application was able to recover from failed transactions and carry
>> on processing other requests.
>
> May it be that the version of MySQL has some affects on what we
> experience? We are currently using 5.0.18.
>
>
>> I am curious what Michael finds in his tests.
>
> Me too, are there any results available?
>
>
>> Anyways, I went ahead and added an explicit commit to the PK
>> generator (that code, although I couldn't make it fail, still
>> looked suspect) and posted new jars here:
>>
>> http://dev.objectstyle.org/~andrus/cayenne-07132006/
>>
>> Christian, I would appreciate if you could try this in your
>> application and see if you still get those errors.
>
> We will update some applications in the next week, we haven't found
> the time to do so this week.
>
> But there was some other idea we had discussed recently. At the
> moment all of our apps use the "autoReconnect=true" feature of the
> jdbc driver and after some discussions with colleagues we updated the
> config to use "autoReconnect=false". If our problem is related to
> MySQL loosing the connection I would expect to get some kind of "Lost
> connection" errors instead of duplicate key messages.
>
>
> Christian
>
>
RE: Duplicate Key Problem
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
I was never able to reproduce the problem, either. I have version
4.0.12 of MySQL. I think autoReconnect=false is a good thing to try,
since Cayenne will catch connection exceptions and try to reconnect on
its own.
Thanks,
/dev/mrg
-----Original Message-----
From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
Sent: Friday, July 21, 2006 9:56 AM
To: cayenne-user@incubator.apache.org
Subject: Re: Duplicate Key Problem
Am 13.07.2006 um 20:35 schrieb Andrus Adamchik:
> BTW, I tried to reproduce PK generator getting an incorrect PK
> range on MySQL by emulating some load via JMeter. It never happens
> (at least on a single VM instance). I wrote a test case that throws
> an exception randomly on committing the user transaction. Still the
> application was able to recover from failed transactions and carry
> on processing other requests.
May it be that the version of MySQL has some affects on what we
experience? We are currently using 5.0.18.
> I am curious what Michael finds in his tests.
Me too, are there any results available?
> Anyways, I went ahead and added an explicit commit to the PK
> generator (that code, although I couldn't make it fail, still
> looked suspect) and posted new jars here:
>
> http://dev.objectstyle.org/~andrus/cayenne-07132006/
>
> Christian, I would appreciate if you could try this in your
> application and see if you still get those errors.
We will update some applications in the next week, we haven't found
the time to do so this week.
But there was some other idea we had discussed recently. At the
moment all of our apps use the "autoReconnect=true" feature of the
jdbc driver and after some discussions with colleagues we updated the
config to use "autoReconnect=false". If our problem is related to
MySQL loosing the connection I would expect to get some kind of "Lost
connection" errors instead of duplicate key messages.
Christian
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
Am 13.07.2006 um 20:35 schrieb Andrus Adamchik:
> BTW, I tried to reproduce PK generator getting an incorrect PK
> range on MySQL by emulating some load via JMeter. It never happens
> (at least on a single VM instance). I wrote a test case that throws
> an exception randomly on committing the user transaction. Still the
> application was able to recover from failed transactions and carry
> on processing other requests.
May it be that the version of MySQL has some affects on what we
experience? We are currently using 5.0.18.
> I am curious what Michael finds in his tests.
Me too, are there any results available?
> Anyways, I went ahead and added an explicit commit to the PK
> generator (that code, although I couldn't make it fail, still
> looked suspect) and posted new jars here:
>
> http://dev.objectstyle.org/~andrus/cayenne-07132006/
>
> Christian, I would appreciate if you could try this in your
> application and see if you still get those errors.
We will update some applications in the next week, we haven't found
the time to do so this week.
But there was some other idea we had discussed recently. At the
moment all of our apps use the "autoReconnect=true" feature of the
jdbc driver and after some discussions with colleagues we updated the
config to use "autoReconnect=false". If our problem is related to
MySQL loosing the connection I would expect to get some kind of "Lost
connection" errors instead of duplicate key messages.
Christian
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
Am 13.07.2006 um 20:35 schrieb Andrus Adamchik:
> Anyways, I went ahead and added an explicit commit to the PK
> generator (that code, although I couldn't make it fail, still
> looked suspect) and posted new jars here:
>
> http://dev.objectstyle.org/~andrus/cayenne-07132006/
>
> Christian, I would appreciate if you could try this in your
> application and see if you still get those errors.
I will give those jars a try next week and test it in one or two
applications. But it's difficult to predict when this error happens...
Christian
Re: Duplicate Key Problem
Posted by Andrus Adamchik <an...@objectstyle.org>.
Besides Cayenne normally uses connection pool, so once the pool is
loaded, the connections are reused for the lifetime of the app. There
is something interesting going on there...
Andrus
On Jul 13, 2006, at 2:51 PM, Gentry, Michael ((Contractor)) wrote:
> I've tried many times so far and haven't been able to get duplicate
> keys
> across two applications (different VMs in this case). This comment
> from
> Christian sounded a bit extreme to me, though:
>
> "The db has collected 3893813 connection attempts and 534 aborted
> clients
> since we restarted the db two days ago."
>
> A normal Cayenne application would generate very few connection
> attempts. 3.8 million in 2 days? That's 2700 connections/minute.
> That
> is a LOT. (My Tapestry-based application probably does 1-2
> connections/database per week, depending on load/etc.) I wonder if
> MySQL could be stuttering or having a few issues under that kind of
> load?
>
> /dev/mrg
>
>
> -----Original Message-----
> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Sent: Thursday, July 13, 2006 2:36 PM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
> BTW, I tried to reproduce PK generator getting an incorrect PK range
> on MySQL by emulating some load via JMeter. It never happens (at
> least on a single VM instance). I wrote a test case that throws an
> exception randomly on committing the user transaction. Still the
> application was able to recover from failed transactions and carry on
> processing other requests.
>
> I am curious what Michael finds in his tests.
>
> Anyways, I went ahead and added an explicit commit to the PK
> generator (that code, although I couldn't make it fail, still looked
> suspect) and posted new jars here:
>
> http://dev.objectstyle.org/~andrus/cayenne-07132006/
>
> Christian, I would appreciate if you could try this in your
> application and see if you still get those errors.
>
> Andrus
>
>
> On Jul 13, 2006, at 11:53 AM, Andrus Adamchik wrote:
>
>> Cool, I'll wait for the results.
>>
>> Andrus
>>
>>
>> On Jul 13, 2006, at 11:44 AM, Gentry, Michael ((Contractor)) wrote:
>>
>>> The quickest way to test that I can think of is to be stepping
>>> through
>>> the PK generation code in the debugger and after you lock/select the
>>> PKs, use "mysqladmin kill" to kill your connection before the
>>> update/unlock. You can then try to access the auto_pk_support table
>>> from another app (or the mysql prompt) and see if it is still
>>> locked/etc. I might could use my CayenneExample (with a few
>>> tweaks) to
>>> test this in a bit.
>>>
>>> /dev/mrg
>>>
>>>
>>> -----Original Message-----
>>> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
>>> Sent: Thursday, July 13, 2006 11:23 AM
>>> To: cayenne-user@incubator.apache.org
>>> Subject: Re: Duplicate Key Problem
>>>
>>>
>>>
>>> On Jul 13, 2006, at 10:57 AM, Gentry, Michael ((Contractor)) wrote:
>>>
>>>> Is the PK cache per VM or per DataNode? I was thinking per
>>>> DataNode
>>>> (obviously within the same VM, of course).
>>>
>>> True, more accurately it is one per DataNode, and is shared by all
>>> DataContexts that sit on top of a given DataDomain.
>>>
>>>
>>>> Another thing that could be tricky is that the MySQL JDBC connector
>>>> (Connector/J) has an autoReconnect=true option, which would catch a
>>>> disconnection before Cayenne could see it and reconnect. Not
>>>> sure at
>>>> all what would happen to an in-progress transaction if that were
>>>> the
>>>> case.
>>>
>>> Good point. But I am more concerned about runtime exceptions in the
>>> code that theoretically can cause a PK range to become invalid. One
>>> straightforward way to fix that is to apply the same approach we did
>>> for Sybase PK generator per CAY-588 (i.e. ensure that PK is
>>> generated
>>> outside of the main transaction. I guess that's what we'll have to
>>> do, but I want to have a way to reproduce the problem first, if only
>>> to know that our fix actually fixes it.
>>>
>>> Andrus
>>>
>>
>>
>
>
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
Am 13.07.2006 um 20:51 schrieb Gentry, Michael ((Contractor)):
> "The db has collected 3893813 connection attempts and 534 aborted
> clients
> since we restarted the db two days ago."
>
> A normal Cayenne application would generate very few connection
> attempts. 3.8 million in 2 days? That's 2700 connections/minute.
> That
> is a LOT. (My Tapestry-based application probably does 1-2
> connections/database per week, depending on load/etc.) I wonder if
> MySQL could be stuttering or having a few issues under that kind of
> load?
There are some other tasks running on that db that are not based on
Cayenne but instead use PHP. Just forget these numbers, I don't think
that they have something to do with the described problem. And the
uptime was a of course a wild guess by me ;-)
Christian
RE: Duplicate Key Problem
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
I've tried many times so far and haven't been able to get duplicate keys
across two applications (different VMs in this case). This comment from
Christian sounded a bit extreme to me, though:
"The db has collected 3893813 connection attempts and 534 aborted
clients
since we restarted the db two days ago."
A normal Cayenne application would generate very few connection
attempts. 3.8 million in 2 days? That's 2700 connections/minute. That
is a LOT. (My Tapestry-based application probably does 1-2
connections/database per week, depending on load/etc.) I wonder if
MySQL could be stuttering or having a few issues under that kind of
load?
/dev/mrg
-----Original Message-----
From: Andrus Adamchik [mailto:andrus@objectstyle.org]
Sent: Thursday, July 13, 2006 2:36 PM
To: cayenne-user@incubator.apache.org
Subject: Re: Duplicate Key Problem
BTW, I tried to reproduce PK generator getting an incorrect PK range
on MySQL by emulating some load via JMeter. It never happens (at
least on a single VM instance). I wrote a test case that throws an
exception randomly on committing the user transaction. Still the
application was able to recover from failed transactions and carry on
processing other requests.
I am curious what Michael finds in his tests.
Anyways, I went ahead and added an explicit commit to the PK
generator (that code, although I couldn't make it fail, still looked
suspect) and posted new jars here:
http://dev.objectstyle.org/~andrus/cayenne-07132006/
Christian, I would appreciate if you could try this in your
application and see if you still get those errors.
Andrus
On Jul 13, 2006, at 11:53 AM, Andrus Adamchik wrote:
> Cool, I'll wait for the results.
>
> Andrus
>
>
> On Jul 13, 2006, at 11:44 AM, Gentry, Michael ((Contractor)) wrote:
>
>> The quickest way to test that I can think of is to be stepping
>> through
>> the PK generation code in the debugger and after you lock/select the
>> PKs, use "mysqladmin kill" to kill your connection before the
>> update/unlock. You can then try to access the auto_pk_support table
>> from another app (or the mysql prompt) and see if it is still
>> locked/etc. I might could use my CayenneExample (with a few
>> tweaks) to
>> test this in a bit.
>>
>> /dev/mrg
>>
>>
>> -----Original Message-----
>> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
>> Sent: Thursday, July 13, 2006 11:23 AM
>> To: cayenne-user@incubator.apache.org
>> Subject: Re: Duplicate Key Problem
>>
>>
>>
>> On Jul 13, 2006, at 10:57 AM, Gentry, Michael ((Contractor)) wrote:
>>
>>> Is the PK cache per VM or per DataNode? I was thinking per DataNode
>>> (obviously within the same VM, of course).
>>
>> True, more accurately it is one per DataNode, and is shared by all
>> DataContexts that sit on top of a given DataDomain.
>>
>>
>>> Another thing that could be tricky is that the MySQL JDBC connector
>>> (Connector/J) has an autoReconnect=true option, which would catch a
>>> disconnection before Cayenne could see it and reconnect. Not
>>> sure at
>>> all what would happen to an in-progress transaction if that were the
>>> case.
>>
>> Good point. But I am more concerned about runtime exceptions in the
>> code that theoretically can cause a PK range to become invalid. One
>> straightforward way to fix that is to apply the same approach we did
>> for Sybase PK generator per CAY-588 (i.e. ensure that PK is generated
>> outside of the main transaction. I guess that's what we'll have to
>> do, but I want to have a way to reproduce the problem first, if only
>> to know that our fix actually fixes it.
>>
>> Andrus
>>
>
>
Re: Duplicate Key Problem
Posted by Andrus Adamchik <an...@objectstyle.org>.
BTW, I tried to reproduce PK generator getting an incorrect PK range
on MySQL by emulating some load via JMeter. It never happens (at
least on a single VM instance). I wrote a test case that throws an
exception randomly on committing the user transaction. Still the
application was able to recover from failed transactions and carry on
processing other requests.
I am curious what Michael finds in his tests.
Anyways, I went ahead and added an explicit commit to the PK
generator (that code, although I couldn't make it fail, still looked
suspect) and posted new jars here:
http://dev.objectstyle.org/~andrus/cayenne-07132006/
Christian, I would appreciate if you could try this in your
application and see if you still get those errors.
Andrus
On Jul 13, 2006, at 11:53 AM, Andrus Adamchik wrote:
> Cool, I'll wait for the results.
>
> Andrus
>
>
> On Jul 13, 2006, at 11:44 AM, Gentry, Michael ((Contractor)) wrote:
>
>> The quickest way to test that I can think of is to be stepping
>> through
>> the PK generation code in the debugger and after you lock/select the
>> PKs, use "mysqladmin kill" to kill your connection before the
>> update/unlock. You can then try to access the auto_pk_support table
>> from another app (or the mysql prompt) and see if it is still
>> locked/etc. I might could use my CayenneExample (with a few
>> tweaks) to
>> test this in a bit.
>>
>> /dev/mrg
>>
>>
>> -----Original Message-----
>> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
>> Sent: Thursday, July 13, 2006 11:23 AM
>> To: cayenne-user@incubator.apache.org
>> Subject: Re: Duplicate Key Problem
>>
>>
>>
>> On Jul 13, 2006, at 10:57 AM, Gentry, Michael ((Contractor)) wrote:
>>
>>> Is the PK cache per VM or per DataNode? I was thinking per DataNode
>>> (obviously within the same VM, of course).
>>
>> True, more accurately it is one per DataNode, and is shared by all
>> DataContexts that sit on top of a given DataDomain.
>>
>>
>>> Another thing that could be tricky is that the MySQL JDBC connector
>>> (Connector/J) has an autoReconnect=true option, which would catch a
>>> disconnection before Cayenne could see it and reconnect. Not
>>> sure at
>>> all what would happen to an in-progress transaction if that were the
>>> case.
>>
>> Good point. But I am more concerned about runtime exceptions in the
>> code that theoretically can cause a PK range to become invalid. One
>> straightforward way to fix that is to apply the same approach we did
>> for Sybase PK generator per CAY-588 (i.e. ensure that PK is generated
>> outside of the main transaction. I guess that's what we'll have to
>> do, but I want to have a way to reproduce the problem first, if only
>> to know that our fix actually fixes it.
>>
>> Andrus
>>
>
>
Re: Duplicate Key Problem
Posted by Andrus Adamchik <an...@objectstyle.org>.
Cool, I'll wait for the results.
Andrus
On Jul 13, 2006, at 11:44 AM, Gentry, Michael ((Contractor)) wrote:
> The quickest way to test that I can think of is to be stepping through
> the PK generation code in the debugger and after you lock/select the
> PKs, use "mysqladmin kill" to kill your connection before the
> update/unlock. You can then try to access the auto_pk_support table
> from another app (or the mysql prompt) and see if it is still
> locked/etc. I might could use my CayenneExample (with a few
> tweaks) to
> test this in a bit.
>
> /dev/mrg
>
>
> -----Original Message-----
> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Sent: Thursday, July 13, 2006 11:23 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
>
> On Jul 13, 2006, at 10:57 AM, Gentry, Michael ((Contractor)) wrote:
>
>> Is the PK cache per VM or per DataNode? I was thinking per DataNode
>> (obviously within the same VM, of course).
>
> True, more accurately it is one per DataNode, and is shared by all
> DataContexts that sit on top of a given DataDomain.
>
>
>> Another thing that could be tricky is that the MySQL JDBC connector
>> (Connector/J) has an autoReconnect=true option, which would catch a
>> disconnection before Cayenne could see it and reconnect. Not sure at
>> all what would happen to an in-progress transaction if that were the
>> case.
>
> Good point. But I am more concerned about runtime exceptions in the
> code that theoretically can cause a PK range to become invalid. One
> straightforward way to fix that is to apply the same approach we did
> for Sybase PK generator per CAY-588 (i.e. ensure that PK is generated
> outside of the main transaction. I guess that's what we'll have to
> do, but I want to have a way to reproduce the problem first, if only
> to know that our fix actually fixes it.
>
> Andrus
>
RE: Duplicate Key Problem
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
The quickest way to test that I can think of is to be stepping through
the PK generation code in the debugger and after you lock/select the
PKs, use "mysqladmin kill" to kill your connection before the
update/unlock. You can then try to access the auto_pk_support table
from another app (or the mysql prompt) and see if it is still
locked/etc. I might could use my CayenneExample (with a few tweaks) to
test this in a bit.
/dev/mrg
-----Original Message-----
From: Andrus Adamchik [mailto:andrus@objectstyle.org]
Sent: Thursday, July 13, 2006 11:23 AM
To: cayenne-user@incubator.apache.org
Subject: Re: Duplicate Key Problem
On Jul 13, 2006, at 10:57 AM, Gentry, Michael ((Contractor)) wrote:
> Is the PK cache per VM or per DataNode? I was thinking per DataNode
> (obviously within the same VM, of course).
True, more accurately it is one per DataNode, and is shared by all
DataContexts that sit on top of a given DataDomain.
> Another thing that could be tricky is that the MySQL JDBC connector
> (Connector/J) has an autoReconnect=true option, which would catch a
> disconnection before Cayenne could see it and reconnect. Not sure at
> all what would happen to an in-progress transaction if that were the
> case.
Good point. But I am more concerned about runtime exceptions in the
code that theoretically can cause a PK range to become invalid. One
straightforward way to fix that is to apply the same approach we did
for Sybase PK generator per CAY-588 (i.e. ensure that PK is generated
outside of the main transaction. I guess that's what we'll have to
do, but I want to have a way to reproduce the problem first, if only
to know that our fix actually fixes it.
Andrus
Re: Duplicate Key Problem
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jul 13, 2006, at 10:57 AM, Gentry, Michael ((Contractor)) wrote:
> Is the PK cache per VM or per DataNode? I was thinking per DataNode
> (obviously within the same VM, of course).
True, more accurately it is one per DataNode, and is shared by all
DataContexts that sit on top of a given DataDomain.
> Another thing that could be tricky is that the MySQL JDBC connector
> (Connector/J) has an autoReconnect=true option, which would catch a
> disconnection before Cayenne could see it and reconnect. Not sure at
> all what would happen to an in-progress transaction if that were the
> case.
Good point. But I am more concerned about runtime exceptions in the
code that theoretically can cause a PK range to become invalid. One
straightforward way to fix that is to apply the same approach we did
for Sybase PK generator per CAY-588 (i.e. ensure that PK is generated
outside of the main transaction. I guess that's what we'll have to
do, but I want to have a way to reproduce the problem first, if only
to know that our fix actually fixes it.
Andrus
RE: Duplicate Key Problem
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
Is the PK cache per VM or per DataNode? I was thinking per DataNode
(obviously within the same VM, of course).
Another thing that could be tricky is that the MySQL JDBC connector
(Connector/J) has an autoReconnect=true option, which would catch a
disconnection before Cayenne could see it and reconnect. Not sure at
all what would happen to an in-progress transaction if that were the
case.
/dev/mrg
-----Original Message-----
From: Andrus Adamchik [mailto:andrus@objectstyle.org]
Sent: Thursday, July 13, 2006 10:51 AM
To: cayenne-user@incubator.apache.org
Subject: Re: Duplicate Key Problem
On Jul 13, 2006, at 6:23 AM, Christian Mittendorf wrote:
> How does the PK magic of Cayenne work? Does each DataContext get its
> own set of IDs?
No, the id pool is shared per VM. I am fairly certain the
synchronization within single VM works ok though.
On Jul 13, 2006, at 9:17 AM, Gentry, Michael ((Contractor)) wrote:
> PS. Andrus: If a connection is broken and then Cayenne auto-
> reconnects,
> does it discard the PK cache? (I'm guessing not, which could explain
> this.)
No it doesn't. In fact I believe Cayenne may be to blame here, not
MySQL (or maybe both, if locking turns out to be broken :-)). A few
weeks ago I was investigating a possibility of pk range clashes due
to runtime exceptions in PK generator and/or user transaction that
called the generator. I could not reproduce it though, but what
Christian is seeing looks very much like it.
Let me try it again with JMeter.
Andrus
On Jul 13, 2006, at 9:17 AM, Gentry, Michael ((Contractor)) wrote:
> The basic strategy to refresh the primary key cache in MySQL is:
>
> * lock table
> * select next key values from auto_pk_support
> * update auto_pk_support with a new next key (20 keys by default)
> * unlock table
>
> I was just looking at the MySQL docs for "LOCK TABLES" and it
> appears to
> work differently with InnoDB. Which DB storage type are you using?
> Also, Andrus' suggestion that there could've been a failure with the
> connection going down could be correct. It would've happened between
> select and update above. An application got 20 PKs, but the
> connection
> was broken before it could update the PK table. Another application
> comes along and gets the same 20 PKs.
>
> Any chance you can use PostgreSQL? It uses sequences which are atomic
> operations.
>
> Thanks,
>
> /dev/mrg
>
> PS. Andrus: If a connection is broken and then Cayenne auto-
> reconnects,
> does it discard the PK cache? (I'm guessing not, which could explain
> this.)
>
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Thursday, July 13, 2006 6:23 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
> I haven't found any hint to something abnormal in my log files yet.
> However, we experienced the duplicate key problem some minutes ago
> and I was able to track down the problem a bit.
>
> Affected is only one single WebApp and these "Duplicat entry" errors
> occured since yesterday, about 15:30. If I search for this exception
> I can find this ID list:
>
> $ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048820' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048821' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048822' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048823' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048824' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048825' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048826' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048827' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048828' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048829' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048830' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048831' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048832' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048833' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048834' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048835' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048836' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048837' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048836' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048838' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048839' for key 1"
>
> It seems as if one package of PKs (17048820 - 17048839 ) was double
> used.
> How does the PK magic of Cayenne work? Does each DataContext get its
> own set of IDs?
>
>
> Christian
>
>
>
> Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
>
>> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
>> course if the connection goes down or something else equally bad
>> happens during unlock, then you can end up locking the table. I'll
>> be curious to know what exactly happened, as we may improve the
>> unlock reliability as a result.
>>
>> Andrus
>>
>>
>> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
>>
>>> Yes, all of our applications on this database are cayenne based.
>>> I will have a look at the logs tomorrow and see if I can find some
>>> other exceptions...
>>>
>>> Thanks,
>>> Christian
>>>
>>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
>>>
>>>> These could be related, then. I know the MySQL adapter has to
>>>> lock the
>>>> auto_pk_support table to generate keys and perhaps something
>>>> happened to
>>>> leave the table locked. Did you see any other exceptions?
>>>>
>>>> Also, you said you have multiple applications hitting the same
>>>> database.
>>>> Are all of these applications Cayenne-based?
>>>>
>>>> Thanks,
>>>>
>>>> /dev/mrg
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>>> Sent: Wednesday, July 12, 2006 1:50 PM
>>>> To: cayenne-user@incubator.apache.org
>>>> Subject: Re: Duplicate Key Problem
>>>>
>>>>
>>>> We don't use the autoincrement from MySQL.
>>>>
>>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>>>>
>>>>> Are you using MySQL's autoincrement feature for the PKs or the
>>>>> auto_pk_support table from Cayenne?
>>>>>
>>>>> -----Original Message-----
>>>>> From: Christian Mittendorf
>>>>> [mailto:christian.mittendorf@freenet.de]
>>>>> Sent: Wednesday, July 12, 2006 4:29 AM
>>>>> To: cayenne-user@incubator.apache.org
>>>>> Subject: Duplicate Key Problem
>>>>>
>>>>>
>>>>> Hello!
>>>>>
>>>>> We are running a MySQL 5.0 server using InnoDB tables for our
>>>>> applications. We have multiple web applications accessing this
>>>>> same
>>>>> database and the system is running smooth and without problems
>>>>> (almost).
>>>>>
>>>>> But yesterday we were experiencing some strange errors. During the
>>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>>>>> which were caused by:
>>>>>
>>>>> Caused by: java.sql.SQLException: null, message from server:
>>>>> "Duplicate entry '4353880' for key 1"
>>>>>
>>>>> A bit later other CayenneRuntimeException appeared. For those
>>>>> exceptions the cause was:
>>>>>
>>>>> Caused by: java.sql.SQLException: Deadlock found when trying to
>>>>> get
>>>>> lock; Try restarting transaction, message from server: "Lock wait
>>>>> timeout exceeded; try restarting transaction"
>>>>>
>>>>> I'm now wondering, are both errors related to each other?
>>>>>
>>>>> There are, from my point of view, two possible causes for the
>>>>> "Duplicate entry..." message:
>>>>>
>>>>> - two Insert statements on the same object, which might be
>>>>> possible
>>>>> if the application is clustered, which our applications are
>>>>> not, or
>>>>> - dual use of the same key in different objects
>>>>>
>>>>> Has anybody else experienced something like that? Are there any
>>>>> hints
>>>>> what I might do to avoid such situations?
>>>>>
>>>>> Christian
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
Re: Duplicate Key Problem
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jul 13, 2006, at 6:23 AM, Christian Mittendorf wrote:
> How does the PK magic of Cayenne work? Does each DataContext get its
> own set of IDs?
No, the id pool is shared per VM. I am fairly certain the
synchronization within single VM works ok though.
On Jul 13, 2006, at 9:17 AM, Gentry, Michael ((Contractor)) wrote:
> PS. Andrus: If a connection is broken and then Cayenne auto-
> reconnects,
> does it discard the PK cache? (I'm guessing not, which could explain
> this.)
No it doesn't. In fact I believe Cayenne may be to blame here, not
MySQL (or maybe both, if locking turns out to be broken :-)). A few
weeks ago I was investigating a possibility of pk range clashes due
to runtime exceptions in PK generator and/or user transaction that
called the generator. I could not reproduce it though, but what
Christian is seeing looks very much like it.
Let me try it again with JMeter.
Andrus
On Jul 13, 2006, at 9:17 AM, Gentry, Michael ((Contractor)) wrote:
> The basic strategy to refresh the primary key cache in MySQL is:
>
> * lock table
> * select next key values from auto_pk_support
> * update auto_pk_support with a new next key (20 keys by default)
> * unlock table
>
> I was just looking at the MySQL docs for "LOCK TABLES" and it
> appears to
> work differently with InnoDB. Which DB storage type are you using?
> Also, Andrus' suggestion that there could've been a failure with the
> connection going down could be correct. It would've happened between
> select and update above. An application got 20 PKs, but the
> connection
> was broken before it could update the PK table. Another application
> comes along and gets the same 20 PKs.
>
> Any chance you can use PostgreSQL? It uses sequences which are atomic
> operations.
>
> Thanks,
>
> /dev/mrg
>
> PS. Andrus: If a connection is broken and then Cayenne auto-
> reconnects,
> does it discard the PK cache? (I'm guessing not, which could explain
> this.)
>
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Thursday, July 13, 2006 6:23 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
> I haven't found any hint to something abnormal in my log files yet.
> However, we experienced the duplicate key problem some minutes ago
> and I was able to track down the problem a bit.
>
> Affected is only one single WebApp and these "Duplicat entry" errors
> occured since yesterday, about 15:30. If I search for this exception
> I can find this ID list:
>
> $ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048820' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048821' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048822' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048823' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048824' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048825' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048826' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048827' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048828' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048829' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048830' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048831' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048832' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048833' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048834' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048835' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048836' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048837' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048836' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048838' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048839' for key 1"
>
> It seems as if one package of PKs (17048820 - 17048839 ) was double
> used.
> How does the PK magic of Cayenne work? Does each DataContext get its
> own set of IDs?
>
>
> Christian
>
>
>
> Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
>
>> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
>> course if the connection goes down or something else equally bad
>> happens during unlock, then you can end up locking the table. I'll
>> be curious to know what exactly happened, as we may improve the
>> unlock reliability as a result.
>>
>> Andrus
>>
>>
>> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
>>
>>> Yes, all of our applications on this database are cayenne based.
>>> I will have a look at the logs tomorrow and see if I can find some
>>> other exceptions...
>>>
>>> Thanks,
>>> Christian
>>>
>>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
>>>
>>>> These could be related, then. I know the MySQL adapter has to
>>>> lock the
>>>> auto_pk_support table to generate keys and perhaps something
>>>> happened to
>>>> leave the table locked. Did you see any other exceptions?
>>>>
>>>> Also, you said you have multiple applications hitting the same
>>>> database.
>>>> Are all of these applications Cayenne-based?
>>>>
>>>> Thanks,
>>>>
>>>> /dev/mrg
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>>> Sent: Wednesday, July 12, 2006 1:50 PM
>>>> To: cayenne-user@incubator.apache.org
>>>> Subject: Re: Duplicate Key Problem
>>>>
>>>>
>>>> We don't use the autoincrement from MySQL.
>>>>
>>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>>>>
>>>>> Are you using MySQL's autoincrement feature for the PKs or the
>>>>> auto_pk_support table from Cayenne?
>>>>>
>>>>> -----Original Message-----
>>>>> From: Christian Mittendorf
>>>>> [mailto:christian.mittendorf@freenet.de]
>>>>> Sent: Wednesday, July 12, 2006 4:29 AM
>>>>> To: cayenne-user@incubator.apache.org
>>>>> Subject: Duplicate Key Problem
>>>>>
>>>>>
>>>>> Hello!
>>>>>
>>>>> We are running a MySQL 5.0 server using InnoDB tables for our
>>>>> applications. We have multiple web applications accessing this
>>>>> same
>>>>> database and the system is running smooth and without problems
>>>>> (almost).
>>>>>
>>>>> But yesterday we were experiencing some strange errors. During the
>>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>>>>> which were caused by:
>>>>>
>>>>> Caused by: java.sql.SQLException: null, message from server:
>>>>> "Duplicate entry '4353880' for key 1"
>>>>>
>>>>> A bit later other CayenneRuntimeException appeared. For those
>>>>> exceptions the cause was:
>>>>>
>>>>> Caused by: java.sql.SQLException: Deadlock found when trying to
>>>>> get
>>>>> lock; Try restarting transaction, message from server: "Lock wait
>>>>> timeout exceeded; try restarting transaction"
>>>>>
>>>>> I'm now wondering, are both errors related to each other?
>>>>>
>>>>> There are, from my point of view, two possible causes for the
>>>>> "Duplicate entry..." message:
>>>>>
>>>>> - two Insert statements on the same object, which might be
>>>>> possible
>>>>> if the application is clustered, which our applications are
>>>>> not, or
>>>>> - dual use of the same key in different objects
>>>>>
>>>>> Has anybody else experienced something like that? Are there any
>>>>> hints
>>>>> what I might do to avoid such situations?
>>>>>
>>>>> Christian
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
Puhhh... good to know ;-)
Am 13.07.2006 um 19:15 schrieb Andrus Adamchik:
> Actually Cayenne transaction resets all connections to
> "autocommit=false" internally, so this shouldn't be an issue.
>
> Andrus
>
>
> On Jul 13, 2006, at 12:50 PM, Christian Mittendorf wrote:
>
>> Yes, it seems to behave differently and I think that our DB is
>> running with AUTOCOMMIT=1, which is the default setting:
>>
>> "The correct way to use LOCK TABLES with transactional tables,
>> such as InnoDB tables, is to set AUTOCOMMIT = 0 and not to call
>> UNLOCK TABLES until you commit the transaction explicitly."
>
Re: Duplicate Key Problem
Posted by Andrus Adamchik <an...@objectstyle.org>.
Actually Cayenne transaction resets all connections to
"autocommit=false" internally, so this shouldn't be an issue.
Andrus
On Jul 13, 2006, at 12:50 PM, Christian Mittendorf wrote:
> Yes, it seems to behave differently and I think that our DB is
> running with AUTOCOMMIT=1, which is the default setting:
>
> "The correct way to use LOCK TABLES with transactional tables,
> such as InnoDB tables, is to set AUTOCOMMIT = 0 and not to call
> UNLOCK TABLES until you commit the transaction explicitly."
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
Am 13.07.2006 um 15:17 schrieb Gentry, Michael ((Contractor)):
> I was just looking at the MySQL docs for "LOCK TABLES" and it
> appears to
> work differently with InnoDB.
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
Yes, it seems to behave differently and I think that our DB is
running with AUTOCOMMIT=1, which is the default setting:
"The correct way to use LOCK TABLES with transactional tables,
such as InnoDB tables, is to set AUTOCOMMIT = 0 and not to call
UNLOCK TABLES until you commit the transaction explicitly."
> Which DB storage type are you using?
We are using InnoDB...
> Also, Andrus' suggestion that there could've been a failure with the
> connection going down could be correct. It would've happened between
> select and update above. An application got 20 PKs, but the
> connection
> was broken before it could update the PK table. Another application
> comes along and gets the same 20 PKs.
The db has collected 3893813 connection attempts and 534 aborted clients
since we restarted the db two days ago. However, there are also other
databases that are hosted on that mysql server that are accessed with
perl
or php clients.
> Any chance you can use PostgreSQL? It uses sequences which are atomic
> operations.
Well.. migrating to PostgreSQL is no solution right now because
knowledge
is primarily focussed on MySQL yet. However, there are some colleages
where PostgreSQL is in use.
Christian
>
> Thanks,
>
> /dev/mrg
>
> PS. Andrus: If a connection is broken and then Cayenne auto-
> reconnects,
> does it discard the PK cache? (I'm guessing not, which could explain
> this.)
>
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Thursday, July 13, 2006 6:23 AM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
> I haven't found any hint to something abnormal in my log files yet.
> However, we experienced the duplicate key problem some minutes ago
> and I was able to track down the problem a bit.
>
> Affected is only one single WebApp and these "Duplicat entry" errors
> occured since yesterday, about 15:30. If I search for this exception
> I can find this ID list:
>
> $ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048820' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048821' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048822' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048823' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048824' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048825' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048826' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048827' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048828' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048829' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048830' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048831' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048832' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048833' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048834' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048835' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048836' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048837' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048836' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048838' for key 1"
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '17048839' for key 1"
>
> It seems as if one package of PKs (17048820 - 17048839 ) was double
> used.
> How does the PK magic of Cayenne work? Does each DataContext get its
> own set of IDs?
>
>
> Christian
>
>
>
> Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
>
>> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
>> course if the connection goes down or something else equally bad
>> happens during unlock, then you can end up locking the table. I'll
>> be curious to know what exactly happened, as we may improve the
>> unlock reliability as a result.
>>
>> Andrus
>>
>>
>> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
>>
>>> Yes, all of our applications on this database are cayenne based.
>>> I will have a look at the logs tomorrow and see if I can find some
>>> other exceptions...
>>>
>>> Thanks,
>>> Christian
>>>
>>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
>>>
>>>> These could be related, then. I know the MySQL adapter has to
>>>> lock the
>>>> auto_pk_support table to generate keys and perhaps something
>>>> happened to
>>>> leave the table locked. Did you see any other exceptions?
>>>>
>>>> Also, you said you have multiple applications hitting the same
>>>> database.
>>>> Are all of these applications Cayenne-based?
>>>>
>>>> Thanks,
>>>>
>>>> /dev/mrg
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>>> Sent: Wednesday, July 12, 2006 1:50 PM
>>>> To: cayenne-user@incubator.apache.org
>>>> Subject: Re: Duplicate Key Problem
>>>>
>>>>
>>>> We don't use the autoincrement from MySQL.
>>>>
>>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>>>>
>>>>> Are you using MySQL's autoincrement feature for the PKs or the
>>>>> auto_pk_support table from Cayenne?
>>>>>
>>>>> -----Original Message-----
>>>>> From: Christian Mittendorf
>>>>> [mailto:christian.mittendorf@freenet.de]
>>>>> Sent: Wednesday, July 12, 2006 4:29 AM
>>>>> To: cayenne-user@incubator.apache.org
>>>>> Subject: Duplicate Key Problem
>>>>>
>>>>>
>>>>> Hello!
>>>>>
>>>>> We are running a MySQL 5.0 server using InnoDB tables for our
>>>>> applications. We have multiple web applications accessing this
>>>>> same
>>>>> database and the system is running smooth and without problems
>>>>> (almost).
>>>>>
>>>>> But yesterday we were experiencing some strange errors. During the
>>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>>>>> which were caused by:
>>>>>
>>>>> Caused by: java.sql.SQLException: null, message from server:
>>>>> "Duplicate entry '4353880' for key 1"
>>>>>
>>>>> A bit later other CayenneRuntimeException appeared. For those
>>>>> exceptions the cause was:
>>>>>
>>>>> Caused by: java.sql.SQLException: Deadlock found when trying to
>>>>> get
>>>>> lock; Try restarting transaction, message from server: "Lock wait
>>>>> timeout exceeded; try restarting transaction"
>>>>>
>>>>> I'm now wondering, are both errors related to each other?
>>>>>
>>>>> There are, from my point of view, two possible causes for the
>>>>> "Duplicate entry..." message:
>>>>>
>>>>> - two Insert statements on the same object, which might be
>>>>> possible
>>>>> if the application is clustered, which our applications are
>>>>> not, or
>>>>> - dual use of the same key in different objects
>>>>>
>>>>> Has anybody else experienced something like that? Are there any
>>>>> hints
>>>>> what I might do to avoid such situations?
>>>>>
>>>>> Christian
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
RE: Duplicate Key Problem
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
The basic strategy to refresh the primary key cache in MySQL is:
* lock table
* select next key values from auto_pk_support
* update auto_pk_support with a new next key (20 keys by default)
* unlock table
I was just looking at the MySQL docs for "LOCK TABLES" and it appears to
work differently with InnoDB. Which DB storage type are you using?
Also, Andrus' suggestion that there could've been a failure with the
connection going down could be correct. It would've happened between
select and update above. An application got 20 PKs, but the connection
was broken before it could update the PK table. Another application
comes along and gets the same 20 PKs.
Any chance you can use PostgreSQL? It uses sequences which are atomic
operations.
Thanks,
/dev/mrg
PS. Andrus: If a connection is broken and then Cayenne auto-reconnects,
does it discard the PK cache? (I'm guessing not, which could explain
this.)
-----Original Message-----
From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
Sent: Thursday, July 13, 2006 6:23 AM
To: cayenne-user@incubator.apache.org
Subject: Re: Duplicate Key Problem
I haven't found any hint to something abnormal in my log files yet.
However, we experienced the duplicate key problem some minutes ago
and I was able to track down the problem a bit.
Affected is only one single WebApp and these "Duplicat entry" errors
occured since yesterday, about 15:30. If I search for this exception
I can find this ID list:
$ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048820' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048821' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048822' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048823' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048824' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048825' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048826' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048827' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048828' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048829' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048830' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048831' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048832' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048833' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048834' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048835' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048836' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048837' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048836' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048838' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048839' for key 1"
It seems as if one package of PKs (17048820 - 17048839 ) was double
used.
How does the PK magic of Cayenne work? Does each DataContext get its
own set of IDs?
Christian
Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
> course if the connection goes down or something else equally bad
> happens during unlock, then you can end up locking the table. I'll
> be curious to know what exactly happened, as we may improve the
> unlock reliability as a result.
>
> Andrus
>
>
> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
>
>> Yes, all of our applications on this database are cayenne based.
>> I will have a look at the logs tomorrow and see if I can find some
>> other exceptions...
>>
>> Thanks,
>> Christian
>>
>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
>>
>>> These could be related, then. I know the MySQL adapter has to
>>> lock the
>>> auto_pk_support table to generate keys and perhaps something
>>> happened to
>>> leave the table locked. Did you see any other exceptions?
>>>
>>> Also, you said you have multiple applications hitting the same
>>> database.
>>> Are all of these applications Cayenne-based?
>>>
>>> Thanks,
>>>
>>> /dev/mrg
>>>
>>>
>>> -----Original Message-----
>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>> Sent: Wednesday, July 12, 2006 1:50 PM
>>> To: cayenne-user@incubator.apache.org
>>> Subject: Re: Duplicate Key Problem
>>>
>>>
>>> We don't use the autoincrement from MySQL.
>>>
>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>>>
>>>> Are you using MySQL's autoincrement feature for the PKs or the
>>>> auto_pk_support table from Cayenne?
>>>>
>>>> -----Original Message-----
>>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>>> Sent: Wednesday, July 12, 2006 4:29 AM
>>>> To: cayenne-user@incubator.apache.org
>>>> Subject: Duplicate Key Problem
>>>>
>>>>
>>>> Hello!
>>>>
>>>> We are running a MySQL 5.0 server using InnoDB tables for our
>>>> applications. We have multiple web applications accessing this same
>>>> database and the system is running smooth and without problems
>>>> (almost).
>>>>
>>>> But yesterday we were experiencing some strange errors. During the
>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>>>> which were caused by:
>>>>
>>>> Caused by: java.sql.SQLException: null, message from server:
>>>> "Duplicate entry '4353880' for key 1"
>>>>
>>>> A bit later other CayenneRuntimeException appeared. For those
>>>> exceptions the cause was:
>>>>
>>>> Caused by: java.sql.SQLException: Deadlock found when trying to get
>>>> lock; Try restarting transaction, message from server: "Lock wait
>>>> timeout exceeded; try restarting transaction"
>>>>
>>>> I'm now wondering, are both errors related to each other?
>>>>
>>>> There are, from my point of view, two possible causes for the
>>>> "Duplicate entry..." message:
>>>>
>>>> - two Insert statements on the same object, which might be possible
>>>> if the application is clustered, which our applications are not, or
>>>> - dual use of the same key in different objects
>>>>
>>>> Has anybody else experienced something like that? Are there any
>>>> hints
>>>> what I might do to avoid such situations?
>>>>
>>>> Christian
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
I haven't found any hint to something abnormal in my log files yet.
However, we experienced the duplicate key problem some minutes ago
and I was able to track down the problem a bit.
Affected is only one single WebApp and these "Duplicat entry" errors
occured since yesterday, about 15:30. If I search for this exception
I can find this ID list:
$ cat x.log.2006-07-12 x.log | grep 'message from server'|uniq
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048820' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048821' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048822' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048823' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048824' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048825' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048826' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048827' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048828' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048829' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048830' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048831' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048832' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048833' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048834' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048835' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048836' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048837' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048836' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048838' for key 1"
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '17048839' for key 1"
It seems as if one package of PKs (17048820 - 17048839 ) was double
used.
How does the PK magic of Cayenne work? Does each DataContext get its
own set of IDs?
Christian
Am 12.07.2006 um 23:40 schrieb Andrus Adamchik:
> Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of
> course if the connection goes down or something else equally bad
> happens during unlock, then you can end up locking the table. I'll
> be curious to know what exactly happened, as we may improve the
> unlock reliability as a result.
>
> Andrus
>
>
> On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
>
>> Yes, all of our applications on this database are cayenne based.
>> I will have a look at the logs tomorrow and see if I can find some
>> other exceptions...
>>
>> Thanks,
>> Christian
>>
>> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
>>
>>> These could be related, then. I know the MySQL adapter has to
>>> lock the
>>> auto_pk_support table to generate keys and perhaps something
>>> happened to
>>> leave the table locked. Did you see any other exceptions?
>>>
>>> Also, you said you have multiple applications hitting the same
>>> database.
>>> Are all of these applications Cayenne-based?
>>>
>>> Thanks,
>>>
>>> /dev/mrg
>>>
>>>
>>> -----Original Message-----
>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>> Sent: Wednesday, July 12, 2006 1:50 PM
>>> To: cayenne-user@incubator.apache.org
>>> Subject: Re: Duplicate Key Problem
>>>
>>>
>>> We don't use the autoincrement from MySQL.
>>>
>>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>>>
>>>> Are you using MySQL's autoincrement feature for the PKs or the
>>>> auto_pk_support table from Cayenne?
>>>>
>>>> -----Original Message-----
>>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>>> Sent: Wednesday, July 12, 2006 4:29 AM
>>>> To: cayenne-user@incubator.apache.org
>>>> Subject: Duplicate Key Problem
>>>>
>>>>
>>>> Hello!
>>>>
>>>> We are running a MySQL 5.0 server using InnoDB tables for our
>>>> applications. We have multiple web applications accessing this same
>>>> database and the system is running smooth and without problems
>>>> (almost).
>>>>
>>>> But yesterday we were experiencing some strange errors. During the
>>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>>>> which were caused by:
>>>>
>>>> Caused by: java.sql.SQLException: null, message from server:
>>>> "Duplicate entry '4353880' for key 1"
>>>>
>>>> A bit later other CayenneRuntimeException appeared. For those
>>>> exceptions the cause was:
>>>>
>>>> Caused by: java.sql.SQLException: Deadlock found when trying to get
>>>> lock; Try restarting transaction, message from server: "Lock wait
>>>> timeout exceeded; try restarting transaction"
>>>>
>>>> I'm now wondering, are both errors related to each other?
>>>>
>>>> There are, from my point of view, two possible causes for the
>>>> "Duplicate entry..." message:
>>>>
>>>> - two Insert statements on the same object, which might be possible
>>>> if the application is clustered, which our applications are not, or
>>>> - dual use of the same key in different objects
>>>>
>>>> Has anybody else experienced something like that? Are there any
>>>> hints
>>>> what I might do to avoid such situations?
>>>>
>>>> Christian
>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>
Re: Duplicate Key Problem
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hmm.. MySQLPkGenerator runs "UNLOCK" in the finally block. Of course
if the connection goes down or something else equally bad happens
during unlock, then you can end up locking the table. I'll be curious
to know what exactly happened, as we may improve the unlock
reliability as a result.
Andrus
On Jul 12, 2006, at 5:29 PM, Christian Mittendorf wrote:
> Yes, all of our applications on this database are cayenne based.
> I will have a look at the logs tomorrow and see if I can find some
> other exceptions...
>
> Thanks,
> Christian
>
> Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
>
>> These could be related, then. I know the MySQL adapter has to
>> lock the
>> auto_pk_support table to generate keys and perhaps something
>> happened to
>> leave the table locked. Did you see any other exceptions?
>>
>> Also, you said you have multiple applications hitting the same
>> database.
>> Are all of these applications Cayenne-based?
>>
>> Thanks,
>>
>> /dev/mrg
>>
>>
>> -----Original Message-----
>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>> Sent: Wednesday, July 12, 2006 1:50 PM
>> To: cayenne-user@incubator.apache.org
>> Subject: Re: Duplicate Key Problem
>>
>>
>> We don't use the autoincrement from MySQL.
>>
>> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>>
>>> Are you using MySQL's autoincrement feature for the PKs or the
>>> auto_pk_support table from Cayenne?
>>>
>>> -----Original Message-----
>>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>>> Sent: Wednesday, July 12, 2006 4:29 AM
>>> To: cayenne-user@incubator.apache.org
>>> Subject: Duplicate Key Problem
>>>
>>>
>>> Hello!
>>>
>>> We are running a MySQL 5.0 server using InnoDB tables for our
>>> applications. We have multiple web applications accessing this same
>>> database and the system is running smooth and without problems
>>> (almost).
>>>
>>> But yesterday we were experiencing some strange errors. During the
>>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>>> which were caused by:
>>>
>>> Caused by: java.sql.SQLException: null, message from server:
>>> "Duplicate entry '4353880' for key 1"
>>>
>>> A bit later other CayenneRuntimeException appeared. For those
>>> exceptions the cause was:
>>>
>>> Caused by: java.sql.SQLException: Deadlock found when trying to get
>>> lock; Try restarting transaction, message from server: "Lock wait
>>> timeout exceeded; try restarting transaction"
>>>
>>> I'm now wondering, are both errors related to each other?
>>>
>>> There are, from my point of view, two possible causes for the
>>> "Duplicate entry..." message:
>>>
>>> - two Insert statements on the same object, which might be possible
>>> if the application is clustered, which our applications are not, or
>>> - dual use of the same key in different objects
>>>
>>> Has anybody else experienced something like that? Are there any
>>> hints
>>> what I might do to avoid such situations?
>>>
>>> Christian
>>>
>>>
>>>
>>>
>>
>
>
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
Yes, all of our applications on this database are cayenne based.
I will have a look at the logs tomorrow and see if I can find some
other exceptions...
Thanks,
Christian
Am 12.07.2006 um 19:56 schrieb Gentry, Michael ((Contractor)):
> These could be related, then. I know the MySQL adapter has to lock
> the
> auto_pk_support table to generate keys and perhaps something
> happened to
> leave the table locked. Did you see any other exceptions?
>
> Also, you said you have multiple applications hitting the same
> database.
> Are all of these applications Cayenne-based?
>
> Thanks,
>
> /dev/mrg
>
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Wednesday, July 12, 2006 1:50 PM
> To: cayenne-user@incubator.apache.org
> Subject: Re: Duplicate Key Problem
>
>
> We don't use the autoincrement from MySQL.
>
> Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
>
>> Are you using MySQL's autoincrement feature for the PKs or the
>> auto_pk_support table from Cayenne?
>>
>> -----Original Message-----
>> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
>> Sent: Wednesday, July 12, 2006 4:29 AM
>> To: cayenne-user@incubator.apache.org
>> Subject: Duplicate Key Problem
>>
>>
>> Hello!
>>
>> We are running a MySQL 5.0 server using InnoDB tables for our
>> applications. We have multiple web applications accessing this same
>> database and the system is running smooth and without problems
>> (almost).
>>
>> But yesterday we were experiencing some strange errors. During the
>> afternoon some CayenneRuntimeExceptions appeared in the log file,
>> which were caused by:
>>
>> Caused by: java.sql.SQLException: null, message from server:
>> "Duplicate entry '4353880' for key 1"
>>
>> A bit later other CayenneRuntimeException appeared. For those
>> exceptions the cause was:
>>
>> Caused by: java.sql.SQLException: Deadlock found when trying to get
>> lock; Try restarting transaction, message from server: "Lock wait
>> timeout exceeded; try restarting transaction"
>>
>> I'm now wondering, are both errors related to each other?
>>
>> There are, from my point of view, two possible causes for the
>> "Duplicate entry..." message:
>>
>> - two Insert statements on the same object, which might be possible
>> if the application is clustered, which our applications are not, or
>> - dual use of the same key in different objects
>>
>> Has anybody else experienced something like that? Are there any hints
>> what I might do to avoid such situations?
>>
>> Christian
>>
>>
>>
>>
>
RE: Duplicate Key Problem
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
These could be related, then. I know the MySQL adapter has to lock the
auto_pk_support table to generate keys and perhaps something happened to
leave the table locked. Did you see any other exceptions?
Also, you said you have multiple applications hitting the same database.
Are all of these applications Cayenne-based?
Thanks,
/dev/mrg
-----Original Message-----
From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
Sent: Wednesday, July 12, 2006 1:50 PM
To: cayenne-user@incubator.apache.org
Subject: Re: Duplicate Key Problem
We don't use the autoincrement from MySQL.
Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
> Are you using MySQL's autoincrement feature for the PKs or the
> auto_pk_support table from Cayenne?
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Wednesday, July 12, 2006 4:29 AM
> To: cayenne-user@incubator.apache.org
> Subject: Duplicate Key Problem
>
>
> Hello!
>
> We are running a MySQL 5.0 server using InnoDB tables for our
> applications. We have multiple web applications accessing this same
> database and the system is running smooth and without problems
> (almost).
>
> But yesterday we were experiencing some strange errors. During the
> afternoon some CayenneRuntimeExceptions appeared in the log file,
> which were caused by:
>
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '4353880' for key 1"
>
> A bit later other CayenneRuntimeException appeared. For those
> exceptions the cause was:
>
> Caused by: java.sql.SQLException: Deadlock found when trying to get
> lock; Try restarting transaction, message from server: "Lock wait
> timeout exceeded; try restarting transaction"
>
> I'm now wondering, are both errors related to each other?
>
> There are, from my point of view, two possible causes for the
> "Duplicate entry..." message:
>
> - two Insert statements on the same object, which might be possible
> if the application is clustered, which our applications are not, or
> - dual use of the same key in different objects
>
> Has anybody else experienced something like that? Are there any hints
> what I might do to avoid such situations?
>
> Christian
>
>
>
>
Re: Duplicate Key Problem
Posted by Christian Mittendorf <ch...@freenet.de>.
We don't use the autoincrement from MySQL.
Am 12.07.2006 um 16:35 schrieb Gentry, Michael ((Contractor)):
> Are you using MySQL's autoincrement feature for the PKs or the
> auto_pk_support table from Cayenne?
>
> -----Original Message-----
> From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
> Sent: Wednesday, July 12, 2006 4:29 AM
> To: cayenne-user@incubator.apache.org
> Subject: Duplicate Key Problem
>
>
> Hello!
>
> We are running a MySQL 5.0 server using InnoDB tables for our
> applications. We have multiple web applications accessing this same
> database and the system is running smooth and without problems
> (almost).
>
> But yesterday we were experiencing some strange errors. During the
> afternoon some CayenneRuntimeExceptions appeared in the log file,
> which were caused by:
>
> Caused by: java.sql.SQLException: null, message from server:
> "Duplicate entry '4353880' for key 1"
>
> A bit later other CayenneRuntimeException appeared. For those
> exceptions the cause was:
>
> Caused by: java.sql.SQLException: Deadlock found when trying to get
> lock; Try restarting transaction, message from server: "Lock wait
> timeout exceeded; try restarting transaction"
>
> I'm now wondering, are both errors related to each other?
>
> There are, from my point of view, two possible causes for the
> "Duplicate entry..." message:
>
> - two Insert statements on the same object, which might be possible
> if the application is clustered, which our applications are not, or
> - dual use of the same key in different objects
>
> Has anybody else experienced something like that? Are there any hints
> what I might do to avoid such situations?
>
> Christian
>
>
>
>
RE: Duplicate Key Problem
Posted by "Gentry, Michael (Contractor)" <mi...@fanniemae.com>.
Are you using MySQL's autoincrement feature for the PKs or the
auto_pk_support table from Cayenne?
-----Original Message-----
From: Christian Mittendorf [mailto:christian.mittendorf@freenet.de]
Sent: Wednesday, July 12, 2006 4:29 AM
To: cayenne-user@incubator.apache.org
Subject: Duplicate Key Problem
Hello!
We are running a MySQL 5.0 server using InnoDB tables for our
applications. We have multiple web applications accessing this same
database and the system is running smooth and without problems (almost).
But yesterday we were experiencing some strange errors. During the
afternoon some CayenneRuntimeExceptions appeared in the log file,
which were caused by:
Caused by: java.sql.SQLException: null, message from server:
"Duplicate entry '4353880' for key 1"
A bit later other CayenneRuntimeException appeared. For those
exceptions the cause was:
Caused by: java.sql.SQLException: Deadlock found when trying to get
lock; Try restarting transaction, message from server: "Lock wait
timeout exceeded; try restarting transaction"
I'm now wondering, are both errors related to each other?
There are, from my point of view, two possible causes for the
"Duplicate entry..." message:
- two Insert statements on the same object, which might be possible
if the application is clustered, which our applications are not, or
- dual use of the same key in different objects
Has anybody else experienced something like that? Are there any hints
what I might do to avoid such situations?
Christian