You are viewing a plain text version of this content. The canonical link for it is here.

Posted to ojb-user@db.apache.org by shivaken <sh...@e-can.ne.jp> on 2003/07/31 05:43:31 UTC

Problem: autoincrement can broke 1:1 relation

Hi, I posted a mail about this problem before.

But I want to let you know and evaluate my patch.

To use db naive autoincrement, we use SequenceManagerNativeImpl and 
SequenceManagerMySQLImpl with access="readonly".

But, in this way, auto incremented value can be modified when you do
'store, delete, store'.

This is caused like below
------------------------------------------------------
// a.getId() == 0;  
broker.store(a);

// a.getId() is setted;
int aId = a.getId()

// check restore value.
a.equals(A.restoreByID(aId));

broker.delete(a);

broker.store(a);
// a.getId() != aId;  other value, autoincremented one;
------------------------------------------------------

This effect make treating foreign key harder wrong.
1:1 relation can be broken.

This is not usual stuation. But, This is not the same behavior as 
SequenceManagerHighLowImpl.

To fix this, SequenceManage has to update auto increment field only when
auto incremented by DB. the value must be changed when inserted.

Please try my patch for MySQLImpl. Though that implementation is ad-hoc.
It behaves like HighLowImpl.
 
-- shivaken
antshell: Ant command line front end
http://www.antshell.org

Re: Problem: autoincrement can break 1:1 relation

Posted by Armin Waibel <ar...@code-au-lait.de>.

Hi again,

> > sorry I'm not a "DB-guy", so have patience with me ;-)
> > do you mean
> > "SELECT LAST_INSERT_ID() "+tablename+" LIMIT 1"
> > ??
>
> Yes, it is. That will work with no problem and improve performance.
>

ok, I will patch PlatformMySQLImpl

> Sorry, I don't have exact answer now. Please wait.
>
> # Did you remove "access=readonly" ?
> # Patched version seems to needs that to work correctly.
>
test case fails with "access=readonly"  and without

regards,
Armin


----- Original Message -----
From: "shivaken" <sh...@e-can.ne.jp>
To: "OJB Users List" <oj...@db.apache.org>
Sent: Tuesday, August 05, 2003 10:46 AM
Subject: Re: Problem: autoincrement can break 1:1 relation


> Hi,
>
> > thanks for the patch!
> > Sorry for the late reply!
>
> Thanks.
> I had to explain about getUniqueLong.
> In my environment (MySQL and ojb-cvs), patched
SequenceManagerNativeImpl needs
> to change getUniqueLong to return 0 to work correctory.
> But I didn't try ojb's junit-test. That was like a quick hack.
>
> > I got problems running the patched version of
> > SequenceManagerNativeImpl against the
> > NativeIdentifierTest junit-test. "Old" version pass
> > the test without problems, integrating your patch
> > one test case fails.
> >
> > Does the actual version of NativeIdentifierTest pass with your
> > modified SequenceManagerNativeImpl (test only works with
> > hsql and mysql, I only run against hsql)?
>
> Ok, I will check out cvs and test and research the cause.
>
> > > I want SequenceManagerNativeImpl to support init property to
define
> > > this value. That can be 0 or -1. And it must be depend on DB type.
> >
> > we can use a custom attribute to make this configurable.
> > But when I set 0 as return value most test cases fail
> > (NativeIdentifierTest)
>
> Then, It seems that MySQL needs 0, and others need -1.
> proper value seems to depend on DB.
> But NativeIdentifierTest dosen't allow to use 0.
>
> I don't comprehend PersistenceBroker completely and haven't read
> NativeIdentifier yet. I will try junit-test for NativeIdentifier and
read
> source file.
>
> > > Yesterday, I found another bug in SequenceManagerMySQLImpl.
> > > MySQL returns long array SELECT LAST_INSERT_ID() query,
> > > so getting new id takes long time when table has many records.
> > > Just adding ' LIMIT 1 '  at the end fixes this performance
problem.
> >
> > sorry I'm not a "DB-guy", so have patience with me ;-)
> > do you mean
> > "SELECT LAST_INSERT_ID() "+tablename+" LIMIT 1"
> > ??
>
> Yes, it is. That will work with no problem and improve performance.
>
> Sorry, I don't have exact answer now. Please wait.
>
> # Did you remove "access=readonly" ?
> # Patched version seems to needs that to work correctly.
>
> regards,
>
> -- shivaken
> antshell: Ant command line front end
> http://www.antshell.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>



---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Problem: autoincrement can break 1:1 relation

Posted by shivaken <sh...@e-can.ne.jp>.

Hi,

> thanks for the patch!
> Sorry for the late reply!

Thanks.
I had to explain about getUniqueLong.
In my environment (MySQL and ojb-cvs), patched SequenceManagerNativeImpl needs 
to change getUniqueLong to return 0 to work correctory.
But I didn't try ojb's junit-test. That was like a quick hack.

> I got problems running the patched version of
> SequenceManagerNativeImpl against the
> NativeIdentifierTest junit-test. "Old" version pass
> the test without problems, integrating your patch
> one test case fails.
>
> Does the actual version of NativeIdentifierTest pass with your
> modified SequenceManagerNativeImpl (test only works with
> hsql and mysql, I only run against hsql)?

Ok, I will check out cvs and test and research the cause.

> > I want SequenceManagerNativeImpl to support init property to define
> > this value. That can be 0 or -1. And it must be depend on DB type.
>
> we can use a custom attribute to make this configurable.
> But when I set 0 as return value most test cases fail
> (NativeIdentifierTest)

Then, It seems that MySQL needs 0, and others need -1.
proper value seems to depend on DB.
But NativeIdentifierTest dosen't allow to use 0.

I don't comprehend PersistenceBroker completely and haven't read 
NativeIdentifier yet. I will try junit-test for NativeIdentifier and read 
source file.

> > Yesterday, I found another bug in SequenceManagerMySQLImpl.
> > MySQL returns long array SELECT LAST_INSERT_ID() query,
> > so getting new id takes long time when table has many records.
> > Just adding ' LIMIT 1 '  at the end fixes this performance problem.
>
> sorry I'm not a "DB-guy", so have patience with me ;-)
> do you mean
> "SELECT LAST_INSERT_ID() "+tablename+" LIMIT 1"
> ??

Yes, it is. That will work with no problem and improve performance.

Sorry, I don't have exact answer now. Please wait.

# Did you remove "access=readonly" ? 
# Patched version seems to needs that to work correctly.

regards,
 
-- shivaken
antshell: Ant command line front end
http://www.antshell.org


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Problem: autoincrement can break 1:1 relation

Posted by Armin Waibel <ar...@code-au-lait.de>.

Hi,

thanks for the patch!
Sorry for the late reply!

I got problems running the patched version of
SequenceManagerNativeImpl against the
NativeIdentifierTest junit-test. "Old" version pass
the test without problems, integrating your patch
one test case fails.

I checked in new tests. Please get latest from CVS

src/test/org/apache/ojb/broker/sequence/NativeIdentifierTest.java

src/java/org/apache/ojb/broker/util/sequence/SequenceManagerNativeImpl.j
ava

Does the actual version of NativeIdentifierTest pass with your
modified SequenceManagerNativeImpl (test only works with
hsql and mysql, I only run against hsql)?

>
> Though I'm very suprised about you don't want to support
> SequenceManagerMySQLImpl.
> I don't like SequenceManagerNativeImpl because
> SequenceManagerNativeImpl.getUniqueLong returns -1.
> I really don't like that. And MySQL dosen't work properly with -1.
>
> I want SequenceManagerNativeImpl to support init property to define
> this value. That can be 0 or -1. And it must be depend on DB type.
>

we can use a custom attribute to make this configurable.
But when I set 0 as return value most test cases fail
(NativeIdentifierTest)

> Yesterday, I found another bug in SequenceManagerMySQLImpl.
> MySQL returns long array SELECT LAST_INSERT_ID() query,
> so getting new id takes long time when table has many records.
> Just adding ' LIMIT 1 '  at the end fixes this performance problem.
>

sorry I'm not a "DB-guy", so have patience with me ;-)
do you mean
"SELECT LAST_INSERT_ID() "+tablename+" LIMIT 1"
??

regards,
Armin

> regards,
>
> -- shivaken
> antshell: Ant command line front end
> http://www.antshell.org
>


------------------------------------------------------------------------
--------


> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org





---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

Re: Problem: autoincrement can break 1:1 relation

Posted by shivaken <sh...@e-can.ne.jp>.

Hi Armin, thanks for your response.

> thanks for the patch. Please, can you send me a patch for
> SequenceManagerNativeImpl (see CVS) too. Because
> I don't want to support SequenceManagerMySQLImpl
> any longer (to check your patch you can find a simple test case
> called NativeIdentifierTest). thanks.

Ok. I attached the patch for SequenceManagerNativeImpl.

Though I'm very suprised about you don't want to support 
SequenceManagerMySQLImpl.
I don't like SequenceManagerNativeImpl because 
SequenceManagerNativeImpl.getUniqueLong returns -1.
I really don't like that. And MySQL dosen't work properly with -1.

I want SequenceManagerNativeImpl to support init property to define
this value. That can be 0 or -1. And it must be depend on DB type.

Yesterday, I found another bug in SequenceManagerMySQLImpl.
MySQL returns long array SELECT LAST_INSERT_ID() query,
so getting new id takes long time when table has many records.
Just adding ' LIMIT 1 '  at the end fixes this performance problem.

regards,

-- shivaken
antshell: Ant command line front end
http://www.antshell.org

Re: Problem: autoincrement can broke 1:1 relation

Posted by Armin Waibel <ar...@code-au-lait.de>.

Hi,
----- Original Message -----
From: "shivaken" <sh...@e-can.ne.jp>
To: "'OJB Users List'" <oj...@db.apache.org>
Sent: Thursday, July 31, 2003 5:43 AM
Subject: Problem: autoincrement can broke 1:1 relation


> Hi, I posted a mail about this problem before.
>
> But I want to let you know and evaluate my patch.
>

thanks for the patch. Please, can you send me a patch for
SequenceManagerNativeImpl (see CVS) too. Because
I don't want to support SequenceManagerMySQLImpl
any longer (to check your patch you can find a simple test case
called NativeIdentifierTest). thanks.

> To use db naive autoincrement, we use SequenceManagerNativeImpl and
> SequenceManagerMySQLImpl with access="readonly".
>
> But, in this way, auto incremented value can be modified when you do
> 'store, delete, store'.
>
exotic! ;-)

> This is caused like below
> ------------------------------------------------------
> // a.getId() == 0;
> broker.store(a);
>
> // a.getId() is setted;
> int aId = a.getId()
>
> // check restore value.
> a.equals(A.restoreByID(aId));
>
> broker.delete(a);
>
> broker.store(a);
> // a.getId() != aId;  other value, autoincremented one;
> ------------------------------------------------------
>
> This effect make treating foreign key harder wrong.
> 1:1 relation can be broken.
>
> This is not usual stuation. But, This is not the same behavior as
> SequenceManagerHighLowImpl.
>

hmm, your are right, this is different from using other sequence manager
implementations

> To fix this, SequenceManage has to update auto increment field only
when
> auto incremented by DB. the value must be changed when inserted.
>
> Please try my patch for MySQLImpl. Though that implementation is
ad-hoc.
> It behaves like HighLowImpl.
>
await eagerly your patch for SequenceManagerNativeImpl ;-)

regards,
Armin

> -- shivaken
> antshell: Ant command line front end
> http://www.antshell.org
>


------------------------------------------------------------------------
--------


> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org