[mysql] how to use transaction-table with non-transaction table?

[Xuefer]

i have 2 type of tables in 1 database
a. innodb
b. myisam
i can create them, and use them separately, but how if i use them together?

i have a "bill" table in innodb, which record the money a user have
and a "services" table in myisam, which record the service a user buyed

when a user buy a service, his money is taken from `bill` table, and his service is set to "enable" in `services` table

table `services is read every page to check if a user have buyed the servcice. i think myisam is faster.
should i change `services` table to innodb? or any other solution?

[ergophobe]

There's an article on exactly that at O'Reilly

Pitfalls of Transactions with PHP [onlamp.com]

In the author's example, the "customers" "order_details" and other tables are MyISAM and only the "orders" table is transaction safe.

Tom

[ergophobe]

PS, you may also be interested in

binlogging bugs when doing INSERT with mixed InnoDB / MyISAM [bugs.mysql.com]

Note that Michael Widenius (aka Monty) says

"This is one of the non fixable things when mixing transactional and non
transactional tables. I have update the manual to reflect this."

[Xuefer]

many thanks, i'm looking into it.

[Xuefer]

seems all his table is altered into innodb

what if when i update `services`+`bill` table and failed in the half?

a. update services ..
b. update bill..
or b then a
there might be an "server shuting down in progress" or other error in the 2nd query

i'm planning to add a log table in innodb
insert log set ... (innodb)
commit
update servces (myisam)
update bill (innodb)
update log set done='1' where logid=$logid
commit

any idea?

[ergophobe]

Are they? Pretty sure she uses

- MyISAM for 'customers' and 'mp3'
- InnoDB for 'orders'
- Memory for temp table that holds info.

Then she uses transactions to put the mission-critical stuff in the InnoDB tables and if that's good, puts the other stuff in the MyISAM and then throws away the temp table. I only looked through it quickly, so I could be wrong.

Tom