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 <blacknex..mail.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 <andru..bjectstyle.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.mittendor..reenet.de]
> > >> Sent: Thursday, July 13, 2006 6:23 AM
> > >> To: cayenne-use..ncubator.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.mittendor..reenet.de]
> > >>>>> Sent: Wednesday, July 12, 2006 1:50 PM
> > >>>>> To: cayenne-use..ncubator.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.mittendor..reenet.de]
> > >>>>>> Sent: Wednesday, July 12, 2006 4:29 AM
> > >>>>>> To: cayenne-use..ncubator.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.Russel..un.com
> > > P.S. A good JDO? O, Gasp!
> > >
> >
> >
>
This archive was generated by hypermail 2.0.0 : Fri Jul 14 2006 - 11:22:57 EDT