PublishedEvent error with MySQL InnoDB tables

[Erik de Hair <er...@pocos.nl>]

Hi,

It looks like using PublishedEvent with a table IsisPublishedEvent with engine InnoDB gives some trouble.

I'm persisting 2 objects of the same entity with @Published-annotation in one transaction. Isis tries to insert 2 corresponding rows in an EMPTY IsisPublishedEvent-table. The first with sequence 0, the second with sequence 1. This gives a duplicate key exception for the primary key, I think because MySQL inserts the first entry with a sequence = 1 (0 is not allowed in InnoDB). The second insert will fail because sequence 1 already exists.

The same action will succeed when going back to the objectform and doing the same again :-$ (still with an empty table) This gives exactly the same insert-statements (with sequence 0 and 1) but this time all goes well. Entries will be inserted with sequence 1 and 2.

I can work around this by inserting a row in IsisPublishedEvent with sequence 1.

Is this a bug or am I doing something wrong?

Erik

RE: PublishedEvent error with MySQL InnoDB tables

[Erik de Hair <er...@pocos.nl>]

I already did a

SHOW TABLE STATUS FROM `database` LIKE 'IsisPublishedEvent' ;

The auto_increment field had a value of 1 when the problem occurred. I manually tried to alter this value to 0 (before I learned this isn't allowed in InnoDB). The second time I triggered the action it still had a value of 1. So it seems nothing changed in the database after running the action.

It's not easy for me to let Isis create the tables. After that I can't log in and trigger the action because our application needs a lot of data to work correctly. I did let Isis create the tables in memory and I used the create table statement of Isis to create the MySQL tables (with the necessary modifications for field types). This was all before I 'got into trouble'.

Erik

________________________________________
From: Dan Haywood [dan@haywood-associates.co.uk]
Sent: Friday, July 18, 2014 11:32 AM
To: users
Subject: Re: PublishedEvent error with MySQL InnoDB tables

It sounds like a bug, but I'm not sure that it's a bug in Isis itself.


Bit of googling around says that once can do a command such as:

  ALTER TABLE table_name AUTO_INCREMENT = 1;

 Perhaps you could do an experiment: let Isis build the empty database,
then manually run this command outside of Isis.  Then, see if all works ok.

If so, then maybe a workaround might be to provide a hook in Isis to run
such commands to fix up the schema.

(I've been thinking it might be useful to have such a hook anyway ... the
use case I have in mind is being able to replace the tables that Isis
gnerates with views/instead-of triggers of the same "shape", with those
views mapping to an existing legacy schema).

Anyway, let me know

Dan




On 18 July 2014 10:23, Erik de Hair <er...@pocos.nl> wrote:

> Hi,
>
> It looks like using PublishedEvent with a table IsisPublishedEvent with
> engine InnoDB gives some trouble.
>
> I'm persisting 2 objects of the same entity with @Published-annotation in
> one transaction. Isis tries to insert 2 corresponding rows in an EMPTY
> IsisPublishedEvent-table. The first with sequence 0, the second with
> sequence 1. This gives a duplicate key exception for the primary key, I
> think because MySQL inserts the first entry with a sequence = 1 (0 is not
> allowed in InnoDB). The second insert will fail because sequence 1 already
> exists.
>
> The same action will succeed when going back to the objectform and doing
> the same again :-$ (still with an empty table) This gives exactly the same
> insert-statements (with sequence 0 and 1) but this time all goes well.
> Entries will be inserted with sequence 1 and 2.
>
> I can work around this by inserting a row in IsisPublishedEvent with
> sequence 1.
>
> Is this a bug or am I doing something wrong?
>
> Erik
>

Re: PublishedEvent error with MySQL InnoDB tables

[Dan Haywood <da...@haywood-associates.co.uk>]

It sounds like a bug, but I'm not sure that it's a bug in Isis itself.


Bit of googling around says that once can do a command such as:

  ALTER TABLE table_name AUTO_INCREMENT = 1;

 Perhaps you could do an experiment: let Isis build the empty database,
then manually run this command outside of Isis.  Then, see if all works ok.

If so, then maybe a workaround might be to provide a hook in Isis to run
such commands to fix up the schema.

(I've been thinking it might be useful to have such a hook anyway ... the
use case I have in mind is being able to replace the tables that Isis
gnerates with views/instead-of triggers of the same "shape", with those
views mapping to an existing legacy schema).

Anyway, let me know

Dan




On 18 July 2014 10:23, Erik de Hair <er...@pocos.nl> wrote:

> Hi,
>
> It looks like using PublishedEvent with a table IsisPublishedEvent with
> engine InnoDB gives some trouble.
>
> I'm persisting 2 objects of the same entity with @Published-annotation in
> one transaction. Isis tries to insert 2 corresponding rows in an EMPTY
> IsisPublishedEvent-table. The first with sequence 0, the second with
> sequence 1. This gives a duplicate key exception for the primary key, I
> think because MySQL inserts the first entry with a sequence = 1 (0 is not
> allowed in InnoDB). The second insert will fail because sequence 1 already
> exists.
>
> The same action will succeed when going back to the objectform and doing
> the same again :-$ (still with an empty table) This gives exactly the same
> insert-statements (with sequence 0 and 1) but this time all goes well.
> Entries will be inserted with sequence 1 and 2.
>
> I can work around this by inserting a row in IsisPublishedEvent with
> sequence 1.
>
> Is this a bug or am I doing something wrong?
>
> Erik
>