Re: MySQL: Moving to InnoDB
Re: MySQL: Moving to InnoDB
- Subject: Re: MySQL: Moving to InnoDB
- From: Kieran Kelleher <email@hidden>
- Date: Wed, 13 Jan 2010 22:08:32 -0500
Hi Timo,
If you mix MyISAM and InnoDB, then when a transaction fails, IIRC, just he InnoDB tables will rollback, the MyISAM tables will not. If important,to you, then you could potentially write some logic that if an ec save fails, then do some logic to delete the rows (EOs) inserted into the MyISAM tables. Also, perhaps some schema changes might allow the FULLTEXT search columns to be broken out into separate related tables in a similar fashion to how one might break out a BLOB image attribute into a separate table from an entity's table to prevent it unecessarily being hydrated from a fault into a real EO. This is just an idea ... I have not had to deal with your situation, but this is a possible approach:
BEFORE
Entity1 (maps to MyISAM table)
attr1
attr2
fulltextattr
AFTER
Entity1 (maps to innodb table)
attr1
attr2
FK <<---> Entity2 (maps to myisam table)
PK
fulltextattr
The java logic of Entity can easily be changed so that the get and set pull the fulltextattr from the related eo.
The advantage is that if a transaction fails halfway, you end up with a Entity2 in the DB and no corresponding Entity1. You can delete these orphans if an ec fails to save and the PHP logic can ignore search results where Entity2 has no related Entity1's.
HTH, Kieran
On Jan 13, 2010, at 4:01 PM, David LeBer wrote:
>
> On 2010-01-13, at 3:52 PM, Timo Hoepfner wrote:
>
>> Hi list, especially Kieran. :)
>>
>> I have a project that evolved over the years. Basically it's a product and image database, which also provides the content for the customer's home page. The product and image database and CMS administration is done in WO, the customer's home page was coded in PHP by someone else (other company), accessing the same DB (read only). Using MySQL was a customer requirement when the project started.
>>
>> The PHP stuff depends on MySQL's FULLTEXT search (with a custom stopwords configuration), which in turns requires the use of MYISAM. I'd really like to move the whole DB to InnoDB tables, but I have no clue how to keep the FULLTEXT stuff of the PHP part functional.
>>
>> It would be no problem to change the WO or PHP code (for which I now also have the honor to maintain) as long the stuff continues to work as expected.
>>
>> Any ideas?
>
> Timo,
>
> I know it's ugly, but you can mix InnoDB and MyISAM tables in the same database.
>
> If the FULL TEXT indexes are only on a single/few tables/columns that may be an option.
>
> Keep MyISAM for the FT tables, move everything else to InnoDB.
>
> ;david
>
> --
> David LeBer
> Codeferous Software
> 'co-def-er-ous' adj. Literally 'code-bearing'
> site: http://codeferous.com
> blog: http://davidleber.net
> profile: http://www.linkedin.com/in/davidleber
> twitter: http://twitter.com/rebeld
> --
> Toronto Area Cocoa / WebObjects developers group:
> http://tacow.org
>
>
>
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden