0

We have a database full of MyISAM tables. The thing is that we need transactions on just 1 table, called payments My question is, will that work? I mean by changing the engine of that table to InnoDB and using transactions on php, will that do the work? or do i have to mess around and do more than just that? Will that affect my db in anyway? The table is isolated, it doesn't have foreign keys since MyISAM doesn't support them.

Ty in advance.

Not Amused
  • 942
  • 2
  • 10
  • 28
  • Just release that changes made inside a transaction, to non-transactional tables, such as `MyISAM`, will be committed, regardless of any `ROLLBACK`s – Ross Smith II Jan 19 '13 at 19:29

1 Answers1

1

As documented under Storage Engines:

It is important to remember that you are not restricted to using the same storage engine for an entire server or schema: you can use a different storage engine for each table in your schema.

So yes, your proposal will work (provided that you only wish to attain ACID compliance on the payments table, of course).

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • So mysql/php will know how to handle that table and it'll do transactions without issues? – Not Amused Jan 19 '13 at 19:28
  • @DeusDeceit: MySQL will, yes. Of course you will, as usual, have to tell MySQL when you are beginning a transaction and when you wish for the transaction to be committed/rolled-back. MySQLi and PDO both enable you to do so from their APIs; whereas the ancient (and now deprecated) `ext/mysql`, which you shouldn't be using now anyway, does not - one would have to perform transaction operations through SQL. – eggyal Jan 19 '13 at 19:30
  • That raises another question. I wasn't part of the development team, I just came on board. The thing is that I AM using PDO in my projects, but they made that project with the old mysql API. The new question is, will it have any effects if I do my part on PHPs with `PDO` when they are doing the rest with pure `mysql` API? is that safe? – Not Amused Jan 19 '13 at 19:33
  • @DeusDeceit: It's safe, provided that one realises the respective operations will be performed over different database connections (and therefore that any `SESSION` state will not be preserved). I'd strongly encourage them to move everything on to PDO. – eggyal Jan 19 '13 at 19:36
  • The thing is that this is minor change, I agree that they should move it to PDO, but what they have already works fine for them. The changes I will add will be in separate `PHPs` from theirs, so there wont be any connection problem. I think I learned what I needed, and I can start applying it. Thumbs up, and ty. – Not Amused Jan 19 '13 at 19:39