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]
