Glad this thread happened. Made me take a look at my other databases too. Glad for the thread, glad for these links. Time to clean database house a bit.
Ramsay's disdain for mySQL is starting to gain a bit of traction with me recently. Though, conversely, I haven't had much trouble with mysql. I will maybe take the plunge on my next "transactional" application. On May 19, 2013, at 11:55 AM, Kieran Kelleher <[email protected]> wrote: > Configure the defaults....... which are properties in the /etc/my.cnf > properties file just like we use for configuring our WebObjects apps. > Documented examples here: > > https://github.com/kierankelleher/gic-mysql-tools > > A typical config file I use has about 4 dozen config properties and quarter > of those are innodb specific. > > This property dictates the default engine: > > default-storage-engine=innodb > > On May 19, 2013, at 9:07 AM, James Cicenia <[email protected]> wrote: > >> >> Curious. >> >> I am working on a new app and am using migrations. I totally forgot about >> myisam vs innnodb and noticed all my migrations created myisam files. Is >> there a way to indicate what kind of table in the migrations? >> >> thanks >> >> On May 18, 2013, at 12:23 PM, Kieran Kelleher <[email protected]> wrote: >> >>> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 NSDictionary<String, 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 ([email protected]) >>>>>>>>>> Help/Unsubscribe/Update your Subscription: >>>>>>>>>> https://lists.apple.com/mailman/options/webobjects-dev/pyu%40mac.com >>>>>>>>>> >>>>>>>>>> This email sent to [email protected] >>>>>>>> >>>>>>>> _______________________________________________ >>>>>>>> Do not post admin requests to the list. They will be ignored. >>>>>>>> Webobjects-dev mailing list ([email protected]) >>>>>>>> Help/Unsubscribe/Update your Subscription: >>>>>>>> https://lists.apple.com/mailman/options/webobjects-dev/kelleherk%40gmail.com >>>>>>>> >>>>>>>> This email sent to [email protected] >>>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> Do not post admin requests to the list. They will be ignored. >>>>>> Webobjects-dev mailing list ([email protected]) >>>>>> Help/Unsubscribe/Update your Subscription: >>>>>> https://lists.apple.com/mailman/options/webobjects-dev/amedeomantica%40me.com >>>>>> >>>>>> This email sent to [email protected] >>> >>> _______________________________________________ >>> Do not post admin requests to the list. They will be ignored. >>> Webobjects-dev mailing list ([email protected]) >>> Help/Unsubscribe/Update your Subscription: >>> https://lists.apple.com/mailman/options/webobjects-dev/james%40jimijon.com >>> >>> This email sent to [email protected] >> >> _______________________________________________ >> Do not post admin requests to the list. They will be ignored. >> Webobjects-dev mailing list ([email protected]) >> Help/Unsubscribe/Update your Subscription: >> https://lists.apple.com/mailman/options/webobjects-dev/kelleherk%40gmail.com >> >> This email sent to [email protected] >
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to [email protected]
