-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: update causing synchronize exception only with replication
PostPosted: Wed Nov 24, 2004 7:19 am 
Senior
Senior

Joined: Fri Jun 18, 2004 10:17 am
Posts: 140
We are having an issue with Hibernate and SQL Server replication (i.e synchronizes table data between 2 databases, 1 internal, 1 external). We have tested Hibernate with our application without errors when replication is not enabled on the Hibernate tables. In order for replication SQL Server adds a new column to the Hibernate tables called rowguid. We have tested adding a column of the same name and type without replication to ensure the error is not caused by adding a new column to a Hibernate table and this was fine. We are convinced that the single use case for causing this issue is turning replication on, and all that happens to the tables is that IDENTITY columns get a NOT FOR REPLICATION flag.

You can see the stack trace below. It occurs when an update is made to a table via Hibernate. All params are bound and then the error occurs.

Oddly, if I copy the update statement from the logging, and then use the parameters in the logging also, this works fine directly in SQL Server.

The update is done on a new session also, so there are no previous objects in the Hibernate session to clash with the update.

Hibernate version:

2.1.4

Mapping documents:

On request :)

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

SEVERE: Could not synchronize database state with session
net.sf.hibernate.HibernateException: SQL insert, update or delete failed (row not found)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:25)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:689)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:642)
at net.sf.hibernate.impl.ScheduledUpdate.execute(ScheduledUpdate.java:52)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2414)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2368)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2236)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
at com.qas.newmedia.internet.renewals.logic.RenewalsLogic.update(RenewalsLogic.java:52)

Name and version of the database you are using:

SQL Server

The generated SQL (show_sql=true):

Hibernate: update dbOnlineRenewals.dbo.tblRenewalAccounts set account_ref=?, status=?, currency_code=?, correspondance_type=?, parent_account_contact_id=?, renewal_contact_id=?, updated_renewal_contact_id=?, date_contact_left=?, payment_id=?, login_id=?, date_first_logged_in=?, date_last_logged_in=?, login_count=? where renewal_account_id=?

Debug level Hibernate log excerpt:

24-Nov-2004 10:45:47 net.sf.hibernate.impl.BatcherImpl getPreparedStatement
FINEST: preparing statement
24-Nov-2004 10:45:47 net.sf.hibernate.persister.EntityPersister dehydrate
FINEST: Dehydrating entity: [com.qas.newmedia.internet.renewals.dto.RenewalAccount#3]
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding '1-14H3L' to parameter: 1
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding null to parameter: 2
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding 'GBP' to parameter: 3
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding null to parameter: 4
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding '6' to parameter: 5
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding '5' to parameter: 6
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding null to parameter: 7
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding null to parameter: 8
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding null to parameter: 9
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding '5790FF0B-9795-4C70-B1C4-15283D6AA3EC' to parameter: 10
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding '24 November 2004 10:45:47' to parameter: 11
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding '24 November 2004 10:45:47' to parameter: 12
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding '1' to parameter: 13
24-Nov-2004 10:45:47 net.sf.hibernate.type.NullableType nullSafeSet
FINEST: binding '3' to parameter: 14
24-Nov-2004 10:45:47 net.sf.hibernate.impl.SessionImpl execute
SEVERE: Could not synchronize database state with session
net.sf.hibernate.HibernateException: SQL insert, update or delete failed (row not found)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:25)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:689)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:642)
at net.sf.hibernate.impl.ScheduledUpdate.execute(ScheduledUpdate.java:52)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2414)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2368)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2236)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
at com.qas.newmedia.internet.renewals.logic.RenewalsLogic.update(RenewalsLogic.java:52)


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 24, 2004 11:34 am 
Senior
Senior

Joined: Fri Jun 18, 2004 10:17 am
Posts: 140
we have found that the replication setting places triggers onto the tables and this is disrupting whatever hibernate wants back from the update execute.

is there anything we can do about this?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 24, 2004 12:00 pm 
jTDS Developer
jTDS Developer

Joined: Tue Feb 24, 2004 5:36 pm
Posts: 70
Location: Bucharest, Romania
On SQL Server triggers may return update counts, just like update/insert queries (usually because they do update/insert queries themselves). The only way around it is if the driver you are using has an option to only return the last update count (that's the actual insert/update). What driver are you using? (The MS driver doesn't have this feature.)

Alin.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 24, 2004 12:10 pm 
Senior
Senior

Joined: Fri Jun 18, 2004 10:17 am
Posts: 140
we are using jTDS and funnily enough we also just found the parameter lastUpdateCount=true! However, it says this is on by default in jTDS and yet we get this error. We will try to manually set this...


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 24, 2004 12:27 pm 
jTDS Developer
jTDS Developer

Joined: Tue Feb 24, 2004 5:36 pm
Posts: 70
Location: Bucharest, Romania
Very interesting. I'm a jTDS developer and I would really like to know if this is an actual issue with jTDS. So please post an update if you find out anything.

Alin.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 24, 2004 12:30 pm 
Senior
Senior

Joined: Fri Jun 18, 2004 10:17 am
Posts: 140
the replication trigger looks like this if it helps.

create trigger upd_7737C91A637746318D62051D5D1B8A21 on [dbo].[tblRenewalAccounts] FOR UPDATE AS
if sessionproperty('replication_agent') = 1 and (select trigger_nestlevel()) = 1 -- and master.dbo.fn_isreplmergeagent() = 1
return
/* Declare variables */

declare @article_rows_updated int
select @article_rows_updated = count(*) from inserted
declare @contents_rows_updated int, @updateerror int
declare @bm varbinary(500), @missingbm varbinary(500), @lineage varbinary(255), @cv varbinary(2048)
declare @tablenick int, @nick int, @ccols int, @partchange int, @joinchange int, @partgen int, @joingen int, @newgen int
declare @partchangebm varbinary(500), @joinchangebm varbinary(500)

set nocount on
set @tablenick = 30049000
select @newgen = gen_cur from sysmergearticles where nickname = @tablenick
if @newgen is null
set @newgen = 0
/* Use intrinsic funtion to set bits for updated columns */
set @bm = columns_updated()

/* only do the map down when needed */
set @missingbm = 0x00
set @partchangebm = 0x00
set @joinchangebm = 0x00
if update([rowguid])
begin
if @@trancount > 0
rollback tran

RAISERROR (20062, 16, -1)
end


/* See if the partition might have changed */
if @partchangebm = 0x0
set @partchange = 0
else
set @partchange= { fn INTERSECTBITMAPS (@bm, @partchangebm) }

/* See if a column used in a join filter changed */
if @joinchangebm = 0x0
set @joinchange = 0
else
set @joinchange= { fn INTERSECTBITMAPS (@bm, @joinchangebm) }
execute master..xp_mapdown_bitmap 0x0040, @bm output

exec dbo.sp_MSgetreplnick @nickname = @nick output
select @ccols = 15

set @lineage = { fn UPDATELINEAGE(0x0, @nick) }
set @lineage = { fn UPDATELINEAGE(@lineage, @nick) }
set @cv = { fn INITCOLVS(@ccols, @nick) }
if (@@error <> 0)
begin
goto FAILURE
end
set @cv = { fn UPDATECOLVBM(@cv, @nick, @bm, @missingbm) }

if @joinchange = 1
begin

set @joingen = @newgen
set @partgen = @newgen
end
else if @partchange = 1
begin
set @partgen = @newgen
set @joingen = NULL
end
else
begin
set @partgen = NULL
set @joingen = NULL
end

update ctsv_7737C91A637746318D62051D5D1B8A21
set lineage = { fn UPDATELINEAGE(lineage, @nick) },
generation = @newgen,
joinchangegen = case when (@joinchange = 1) then @newgen else joinchangegen end,
partchangegen = case when (@partchange = 1) then @newgen else partchangegen end,

colv1 = { fn UPDATECOLVBM(colv1, @nick, @bm, @missingbm) }
FROM inserted as I JOIN ctsv_7737C91A637746318D62051D5D1B8A21 as V
ON (I.rowguidcol=V.rowguid)
and tablenick = @tablenick

select @updateerror = @@error, @contents_rows_updated = @@rowcount



if @article_rows_updated <> @contents_rows_updated
begin
insert into ctsv_7737C91A637746318D62051D5D1B8A21 (tablenick, rowguid, lineage, colv1, generation, partchangegen, joinchangegen)
select @tablenick, rowguidcol, @lineage, @cv, @newgen, @partgen, @joingen from inserted where
rowguidcol not in (select rowguid from ctsv_7737C91A637746318D62051D5D1B8A21 where tablenick = @tablenick)

if @@error <> 0
GOTO FAILURE
end

return
FAILURE:
if @@trancount > 0
rollback tran
raiserror (20041, 16, -1)
return


Top
 Profile  
 
 Post subject:
PostPosted: Wed Nov 24, 2004 12:49 pm 
Senior
Senior

Joined: Fri Jun 18, 2004 10:17 am
Posts: 140
ok, 0.8 fixes this.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.