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/archive%40mail-archive.com This email sent to [email protected]
