On Wednesday, January 4, 2006 1313, Chander Ganesan <[EMAIL PROTECTED]> wrote: >John Hoover wrote: > >>I need some advice re my choice of a storage engine for >transaction-safe processing and including tables that are not >transaction-safe within transactions. >> >>The problem: We need to insert related records into several >different tables and be sure that all the insertions succeeded. >It seems that transactions are the recommended way of achieving >this and I was thinking of using InnoDB tables. However, I'm >not sure if that is the best engine to use - can anyone give me >reasons for selecting a specific transaction-safe engine?
>You can use BDB or InnoDB - InnoDB provides row level locking, BDB >provides page level locking. I'd say InnoDB is the way to go >(usually). I was planning to use InnoDB, but wanted to find out if anybody had good reasons to use BDB instead. Guess I'll stick to my original plan. >>Also, some of my insertions will involve the mySQL tables >(creation of a new user and granting privileges, for example). >According to the manuals, those tables use the myISAM engine >and can not be changed to any other engine. What is the best >way to handle insertion errors on myISAM tables? I had planned >to test for an error after each operation and, if one occurred, >manually undo whatever previous operations had already >succeeded. That's a lot of work if the operation involves >multiple tables and I'd like to know if there is a better >alternative. Finally, if I do handle errors manually, what >should I do if there is an error in the error handler? For >example, suppose I've inserted one record and then an error >prevents insertion of the related record so that I have to >delete the previously inserted record. Is it possible for the >delete to fail? If so, I'll have a partial transaction that >can't be completed and can't be undone - what should I do to clean up? >What are your insertion operations? Typically, you would use GRANT >statements to add users the these tables - and those statements (if they >fail) won't do any GRANTing. I wouldn't grant access using insert >statements - you'll be flushing your privilege tables >regularly. The most common operation will probably be adding new users. I expect that changing privileges for existing users will be done very infrequently. Sounds like GRANT will handle both requirements nicely. >Unless you are using the Host table, I'd recommend you do the >following: > >1. Prior to modifying a user, use the 'show grants' statement to find >out what access the user has - store that. >2. Perform all your GRANT operations. >3. If a single operation fails, remove the user and execute the stored >access (from step 1) for the user to restore his/her access. - if the >user didn't already exist, just remove all their access. For the most common case, there will be no previous user so, if anything fails, I can just delete the user and I'll be back to the starting point. However, for the case where I'm modifying an existing user, I'll use your suggestion for getting and storing the original privileges. >For users that won't connect to the database directly, you probably >don't want to create individual accounts - as if the user connects >directly they can perform operations outside the bounds of your >application (where you may implement business logic). You're the second person to mention users who won't connect directly to the database and I'm not sure that I understand what you mean. I THINK you mean "connect via the command line tools and bypass my application." I suppose that I could have "hidden" accounts (and passwords) that my application uses behind the scenes. In this scenario, my users would "login" with their name and password, but the connection to the DB would use some secret account name and password. After the connection was made, the application would query some table (private to my application) to find out the actual privileges for that person. The application would then enforce those privileges. Nobody could bypass the application because they wouldn't know the hidden usernames or passwords. My concern with this scheme is that the "secret" usernames and passwords would either be set in a preferences file, in which case they wouldn't really be secret, or they'd be coded in the application, in which case I couldn't change them (we're supposed to change our passwords at least once a year - more often for some systems). >Assuming you stick with grant statements, it shouldn't be too difficult >to maintain integrity when you want to do your >"pseudo-transactions". > >Use InnoDB everywhere else. I think that's what I'll do. Thanks for the suggestions. >-- >Chander Ganesan >Open Technology Group, Inc. >One Copley Parkway, Suite 210 >Morrisville, NC 27560 >Phone: 877-258-8987/919-463-0999 >http://www.otg-nc.com > -- John Hoover [EMAIL PROTECTED] 301-890-6932 (H) 202-767-2335 (W) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
