Re: Duplicate entry '4183884' for key 'PRIMARY'
I agree.
I started working with WebObjects and databases in 2001 about and I know very
little about databases. I used MyISAM and after a while was a mess.
I moved then to OpenBase and everything fine. Later I moved to FrontBase that
was become free and I totally love it. It is fully transactional and zero
issues.
For a few projects i use MySQL with InnoDB. Italian government requires that
for government stuff that the DB engine must be Open Source (that guarantees
future readability of data). The app can be closed source (WO is ok).
Amedeo
Sent from my iPhone
On 18/mag/2013, at 00:26, Kieran Kelleher kelleh...@gmail.com wrote:
InnoDB is all or nothing . don't waste your time mixing InnoDB and MyISAM
in transactions - makes the results even more confusing.
Convert them all. evidently, InnoDB is faster nowadays anyway. innoDB can
cache everything in memory. MyISAM IIRC only can cache PKs in memory and must
always get rows from disk. There is no benefit to staying on MyISAM format.
On May 17, 2013, at 4:36 PM, Mark Gowdy go...@mac.com wrote:
Thanks Paul, it looks like you are correct.
Two 'Kieran Kelleher' email stated:
There is also no row locking on EO_PK_TABLE with myisam, so you will get
primary key clashes and subsequent failed inserts if the db is sufficiently
contended.
and
(1) Never use MyISAM - no transactional support means a failed
editingContext.saveChanges() will not result in a rollback and semi-saved
changes to the database. data integrity goes out the window. Use InnoDB
don't just convert live DB though, dump/import to a standby server,
configure InnoDB first completely and actually do a little bit of studying
the MySQL docs to understand how. Also, get a head start here:
https://github.com/kierankelleher/gic-mysql-tools
(2) Don't create real foreign key constraints because MySQL does not
support deferred constraints (which is the biggest negative of using
MySQL). Instead just manually create indexes on every foreign key field.
Otherwise your performance will deteriorate as tables sizes get larger.
Seems like sound advise.
I now have some careful un-picking to do.
Also, incase anyone is interested, I eventually found the primary key
generation code in JDBCPlugIn.class : newPrimaryKeys(int count, EOEntity
entity, JDBCChannel channel)
Other DB plugins do their own thing, but the MySQL one appears to fall back
on JDBCPlugIn.
Thanks again,
Mark
On 17 May 2013, at 21:18, Paul Yu wrote:
Well you certainly should not use MyIsam system. Innodb is the right
answer. See Kieran's post on this subject.
But I'm not sure that will be the end of your issues.
Sent from my iPad
On May 17, 2013, at 4:03 PM, Mark Gowdy go...@mac.com wrote:
Hi,
We have started getting more of these errors recently, now that the system
is under more load.
EvaluateExpression failed:
com.webobjects.jdbcadaptor._MySQLPlugIn$MySQLExpression: INSERT INTO
queue_item(ITEM_DATE, ... etc)
Duplicate entry '4183884' for key 'PRIMARY'
I believe the problem is due to the fact that there is more than one
instance trying to write to the 'queue_item' table, and the EO_PK_TABLE
mechanism is handing out the same ID more than once.
Probably something to do with locking.
Our Database is MySQL 5, and the EO_PK_TABLE is of type MyISAM.
I tried to find the mechanism that allocate the primary key, but I keep
bumping up against:
EOAdaptorChannel:
@Deprecated
public NSDictionaryString, Object
primaryKeyForNewRowWithEntity(EOEntity entity){
return null;
}
Any advise would be appreciated,
Thanks,
Mark
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/pyu%40mac.com
This email sent to p...@mac.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/kelleherk%40gmail.com
This email sent to kelleh...@gmail.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/amedeomantica%40me.com
This email sent to amedeomant...@me.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
Re: Duplicate entry '4183884' for key 'PRIMARY'
Using SequalPro, it lets me change the table type (in the Table Info tab).
This issues the statement:
ALTER TABLE `EO_PK_TABLE` ENGINE = InnoDB;
Is there any likely downside to this, compared with recreating the schema from
scratch?
Mark
On 17 May 2013, at 23:26, Kieran Kelleher wrote:
InnoDB is all or nothing . don't waste your time mixing InnoDB and MyISAM
in transactions - makes the results even more confusing.
Convert them all. evidently, InnoDB is faster nowadays anyway. innoDB can
cache everything in memory. MyISAM IIRC only can cache PKs in memory and must
always get rows from disk. There is no benefit to staying on MyISAM format.
On May 17, 2013, at 4:36 PM, Mark Gowdy go...@mac.com wrote:
Thanks Paul, it looks like you are correct.
Two 'Kieran Kelleher' email stated:
There is also no row locking on EO_PK_TABLE with myisam, so you will get
primary key clashes and subsequent failed inserts if the db is sufficiently
contended.
and
(1) Never use MyISAM - no transactional support means a failed
editingContext.saveChanges() will not result in a rollback and semi-saved
changes to the database. data integrity goes out the window. Use InnoDB
don't just convert live DB though, dump/import to a standby server,
configure InnoDB first completely and actually do a little bit of studying
the MySQL docs to understand how. Also, get a head start here:
https://github.com/kierankelleher/gic-mysql-tools
(2) Don't create real foreign key constraints because MySQL does not
support deferred constraints (which is the biggest negative of using
MySQL). Instead just manually create indexes on every foreign key field.
Otherwise your performance will deteriorate as tables sizes get larger.
Seems like sound advise.
I now have some careful un-picking to do.
Also, incase anyone is interested, I eventually found the primary key
generation code in JDBCPlugIn.class : newPrimaryKeys(int count, EOEntity
entity, JDBCChannel channel)
Other DB plugins do their own thing, but the MySQL one appears to fall back
on JDBCPlugIn.
Thanks again,
Mark
On 17 May 2013, at 21:18, Paul Yu wrote:
Well you certainly should not use MyIsam system. Innodb is the right
answer. See Kieran's post on this subject.
But I'm not sure that will be the end of your issues.
Sent from my iPad
On May 17, 2013, at 4:03 PM, Mark Gowdy go...@mac.com wrote:
Hi,
We have started getting more of these errors recently, now that the system
is under more load.
EvaluateExpression failed:
com.webobjects.jdbcadaptor._MySQLPlugIn$MySQLExpression: INSERT INTO
queue_item(ITEM_DATE, ... etc)
Duplicate entry '4183884' for key 'PRIMARY'
I believe the problem is due to the fact that there is more than one
instance trying to write to the 'queue_item' table, and the EO_PK_TABLE
mechanism is handing out the same ID more than once.
Probably something to do with locking.
Our Database is MySQL 5, and the EO_PK_TABLE is of type MyISAM.
I tried to find the mechanism that allocate the primary key, but I keep
bumping up against:
EOAdaptorChannel:
@Deprecated
public NSDictionaryString, Object
primaryKeyForNewRowWithEntity(EOEntity entity){
return null;
}
Any advise would be appreciated,
Thanks,
Mark
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/pyu%40mac.com
This email sent to p...@mac.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/kelleherk%40gmail.com
This email sent to kelleh...@gmail.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Duplicate entry '4183884' for key 'PRIMARY'
Sometimes I did that change too. Seems to work but really don't know it is the
best way. Kieran was suggesting a dump and restore.
Amedeo
Sent from my iPhone
On 18/mag/2013, at 13:10, Mark Gowdy go...@mac.com wrote:
Using SequalPro, it lets me change the table type (in the Table Info tab).
This issues the statement:
ALTER TABLE `EO_PK_TABLE` ENGINE = InnoDB;
Is there any likely downside to this, compared with recreating the schema
from scratch?
Mark
On 17 May 2013, at 23:26, Kieran Kelleher wrote:
InnoDB is all or nothing . don't waste your time mixing InnoDB and
MyISAM in transactions - makes the results even more confusing.
Convert them all. evidently, InnoDB is faster nowadays anyway. innoDB
can cache everything in memory. MyISAM IIRC only can cache PKs in memory and
must always get rows from disk. There is no benefit to staying on MyISAM
format.
On May 17, 2013, at 4:36 PM, Mark Gowdy go...@mac.com wrote:
Thanks Paul, it looks like you are correct.
Two 'Kieran Kelleher' email stated:
There is also no row locking on EO_PK_TABLE with myisam, so you will get
primary key clashes and subsequent failed inserts if the db is
sufficiently contended.
and
(1) Never use MyISAM - no transactional support means a failed
editingContext.saveChanges() will not result in a rollback and semi-saved
changes to the database. data integrity goes out the window. Use
InnoDB don't just convert live DB though, dump/import to a standby
server, configure InnoDB first completely and actually do a little bit of
studying the MySQL docs to understand how. Also, get a head start here:
https://github.com/kierankelleher/gic-mysql-tools
(2) Don't create real foreign key constraints because MySQL does not
support deferred constraints (which is the biggest negative of using
MySQL). Instead just manually create indexes on every foreign key field.
Otherwise your performance will deteriorate as tables sizes get larger.
Seems like sound advise.
I now have some careful un-picking to do.
Also, incase anyone is interested, I eventually found the primary key
generation code in JDBCPlugIn.class : newPrimaryKeys(int count, EOEntity
entity, JDBCChannel channel)
Other DB plugins do their own thing, but the MySQL one appears to fall back
on JDBCPlugIn.
Thanks again,
Mark
On 17 May 2013, at 21:18, Paul Yu wrote:
Well you certainly should not use MyIsam system. Innodb is the right
answer. See Kieran's post on this subject.
But I'm not sure that will be the end of your issues.
Sent from my iPad
On May 17, 2013, at 4:03 PM, Mark Gowdy go...@mac.com wrote:
Hi,
We have started getting more of these errors recently, now that the
system is under more load.
EvaluateExpression failed:
com.webobjects.jdbcadaptor._MySQLPlugIn$MySQLExpression: INSERT INTO
queue_item(ITEM_DATE, ... etc)
Duplicate entry '4183884' for key 'PRIMARY'
I believe the problem is due to the fact that there is more than one
instance trying to write to the 'queue_item' table, and the EO_PK_TABLE
mechanism is handing out the same ID more than once.
Probably something to do with locking.
Our Database is MySQL 5, and the EO_PK_TABLE is of type MyISAM.
I tried to find the mechanism that allocate the primary key, but I keep
bumping up against:
EOAdaptorChannel:
@Deprecated
public NSDictionaryString, Object
primaryKeyForNewRowWithEntity(EOEntity entity){
return null;
}
Any advise would be appreciated,
Thanks,
Mark
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/pyu%40mac.com
This email sent to p...@mac.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/kelleherk%40gmail.com
This email sent to kelleh...@gmail.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/amedeomantica%40me.com
This email sent to amedeomant...@me.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Duplicate entry '4183884' for key 'PRIMARY'
I recommend configuring the InnoDB settings before you start conversion. See
the example configs on github project. The reason is that some settings are
harder to change after you have starting using InnoDB with default and probably
suboptimal settings.
And yeah, that command will do the conversion properly. Simple to convert.
Study the example configs. They are documented.
Regards, Kieran.
(Sent from my iPhone)
On May 18, 2013, at 7:10 AM, Mark Gowdy go...@mac.com wrote:
Using SequalPro, it lets me change the table type (in the Table Info tab).
This issues the statement:
ALTER TABLE `EO_PK_TABLE` ENGINE = InnoDB;
Is there any likely downside to this, compared with recreating the schema
from scratch?
Mark
On 17 May 2013, at 23:26, Kieran Kelleher wrote:
InnoDB is all or nothing . don't waste your time mixing InnoDB and
MyISAM in transactions - makes the results even more confusing.
Convert them all. evidently, InnoDB is faster nowadays anyway. innoDB
can cache everything in memory. MyISAM IIRC only can cache PKs in memory and
must always get rows from disk. There is no benefit to staying on MyISAM
format.
On May 17, 2013, at 4:36 PM, Mark Gowdy go...@mac.com wrote:
Thanks Paul, it looks like you are correct.
Two 'Kieran Kelleher' email stated:
There is also no row locking on EO_PK_TABLE with myisam, so you will get
primary key clashes and subsequent failed inserts if the db is
sufficiently contended.
and
(1) Never use MyISAM - no transactional support means a failed
editingContext.saveChanges() will not result in a rollback and semi-saved
changes to the database. data integrity goes out the window. Use
InnoDB don't just convert live DB though, dump/import to a standby
server, configure InnoDB first completely and actually do a little bit of
studying the MySQL docs to understand how. Also, get a head start here:
https://github.com/kierankelleher/gic-mysql-tools
(2) Don't create real foreign key constraints because MySQL does not
support deferred constraints (which is the biggest negative of using
MySQL). Instead just manually create indexes on every foreign key field.
Otherwise your performance will deteriorate as tables sizes get larger.
Seems like sound advise.
I now have some careful un-picking to do.
Also, incase anyone is interested, I eventually found the primary key
generation code in JDBCPlugIn.class : newPrimaryKeys(int count, EOEntity
entity, JDBCChannel channel)
Other DB plugins do their own thing, but the MySQL one appears to fall back
on JDBCPlugIn.
Thanks again,
Mark
On 17 May 2013, at 21:18, Paul Yu wrote:
Well you certainly should not use MyIsam system. Innodb is the right
answer. See Kieran's post on this subject.
But I'm not sure that will be the end of your issues.
Sent from my iPad
On May 17, 2013, at 4:03 PM, Mark Gowdy go...@mac.com wrote:
Hi,
We have started getting more of these errors recently, now that the
system is under more load.
EvaluateExpression failed:
com.webobjects.jdbcadaptor._MySQLPlugIn$MySQLExpression: INSERT INTO
queue_item(ITEM_DATE, ... etc)
Duplicate entry '4183884' for key 'PRIMARY'
I believe the problem is due to the fact that there is more than one
instance trying to write to the 'queue_item' table, and the EO_PK_TABLE
mechanism is handing out the same ID more than once.
Probably something to do with locking.
Our Database is MySQL 5, and the EO_PK_TABLE is of type MyISAM.
I tried to find the mechanism that allocate the primary key, but I keep
bumping up against:
EOAdaptorChannel:
@Deprecated
public NSDictionaryString, Object
primaryKeyForNewRowWithEntity(EOEntity entity){
return null;
}
Any advise would be appreciated,
Thanks,
Mark
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/pyu%40mac.com
This email sent to p...@mac.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/kelleherk%40gmail.com
This email sent to kelleh...@gmail.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Duplicate entry '4183884' for key 'PRIMARY'
Actually that second statement I made about dump and restore to change table-space location of a table's data might be wrong ... maybe a simple ALTER TABLE table ENGINE = InnoDB on an existing InnoDB table might be enough to recreate the table in its own file if you changed the table-space settings. I have not tried it. In any case, it is not related to the topic of discussion moving on. The conversion to InnoDB is simple. I added a trivial use at your own risk script, that I have used many times without problems, to the repo on github: https://github.com/kierankelleher/gic-mysql-tools/blob/master/UtilityScripts/AlterTablesToInnodbEngine.sh I strongly advise you to configure InnoDB settings in /etc/my.cnf before you convert though: https://github.com/kierankelleher/gic-mysql-tools/blob/master/README.markdown After conversion, then you can hunt for and clean up data integrity issues since conversion to InnoDB will only prevent future issues related to absence of transaction support - it will not fix the data problems created in the past in your database caused by your use of MyISAM. You must find those data problems and manually fix them in SQL to restore the integrity of your database(s) after the conversion. On May 18, 2013, at 11:53 AM, Kieran Kelleher kelleh...@gmail.com wrote: No need to dump and restore. That is only useful to change all InnoDB tables from a single table-space file to a file per table for example. Regards, Kieran. (Sent from my iPhone) On May 18, 2013, at 7:45 AM, Amedeo Mantica amedeomant...@me.com wrote: Sometimes I did that change too. Seems to work but really don't know it is the best way. Kieran was suggesting a dump and restore. Amedeo Sent from my iPhone On 18/mag/2013, at 13:10, Mark Gowdy go...@mac.com wrote: Using SequalPro, it lets me change the table type (in the Table Info tab). This issues the statement: ALTER TABLE `EO_PK_TABLE` ENGINE = InnoDB; Is there any likely downside to this, compared with recreating the schema from scratch? Mark On 17 May 2013, at 23:26, Kieran Kelleher wrote: InnoDB is all or nothing . don't waste your time mixing InnoDB and MyISAM in transactions - makes the results even more confusing. Convert them all. evidently, InnoDB is faster nowadays anyway. innoDB can cache everything in memory. MyISAM IIRC only can cache PKs in memory and must always get rows from disk. There is no benefit to staying on MyISAM format. On May 17, 2013, at 4:36 PM, Mark Gowdy go...@mac.com wrote: Thanks Paul, it looks like you are correct. Two 'Kieran Kelleher' email stated: There is also no row locking on EO_PK_TABLE with myisam, so you will get primary key clashes and subsequent failed inserts if the db is sufficiently contended. and (1) Never use MyISAM - no transactional support means a failed editingContext.saveChanges() will not result in a rollback and semi-saved changes to the database. data integrity goes out the window. Use InnoDB don't just convert live DB though, dump/import to a standby server, configure InnoDB first completely and actually do a little bit of studying the MySQL docs to understand how. Also, get a head start here: https://github.com/kierankelleher/gic-mysql-tools (2) Don't create real foreign key constraints because MySQL does not support deferred constraints (which is the biggest negative of using MySQL). Instead just manually create indexes on every foreign key field. Otherwise your performance will deteriorate as tables sizes get larger. Seems like sound advise. I now have some careful un-picking to do. Also, incase anyone is interested, I eventually found the primary key generation code in JDBCPlugIn.class : newPrimaryKeys(int count, EOEntity entity, JDBCChannel channel) Other DB plugins do their own thing, but the MySQL one appears to fall back on JDBCPlugIn. Thanks again, Mark On 17 May 2013, at 21:18, Paul Yu wrote: Well you certainly should not use MyIsam system. Innodb is the right answer. See Kieran's post on this subject. But I'm not sure that will be the end of your issues. Sent from my iPad On May 17, 2013, at 4:03 PM, Mark Gowdy go...@mac.com wrote: Hi, We have started getting more of these errors recently, now that the system is under more load. EvaluateExpression failed: com.webobjects.jdbcadaptor._MySQLPlugIn$MySQLExpression: INSERT INTO queue_item(ITEM_DATE, ... etc) Duplicate entry '4183884' for key 'PRIMARY' I believe the problem is due to the fact that there is more than one instance trying to write to the 'queue_item' table, and the EO_PK_TABLE mechanism is handing out the same ID more than once. Probably something to do with locking. Our Database is MySQL 5, and the EO_PK_TABLE is of type MyISAM. I tried to find the mechanism that allocate the primary key, but I keep bumping up
