Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am creating integration into eshop script, shop is using MyISAM tables and I need to implement something like transactions in InnoDB, what I actually need is full recovery of data after modifying or adding some rows.

Scenario would be like: some PHP script fills several tables with information, there will be about 10 tables affected. I'm thinking about creating temporary tables and filling them. After whole fill process succeeded, it will put actual rows to permanent tables and if not it will clean temporary tables.

I am wondering if there is easier way to have full recovery on fail? I cannot edit existing table's structure, or change table's engine. Thanks in advance for your time.

Edit: I have made very basic table of reference for simple understading of what I am dealing with. http://creately.com/diagram/h7yqs7qg2/UP3fno4Rzw3DTW3IdDa5uVDZQHE%3D

Real case scenario would be: I am creating order for user, but I cannot be sure that user creation didn't failed on behalf of creation(let's say created row only on main user table) then when I'll check if user exists for order it will return true. So when I'll start order creation an I'll need it's address but it won't exist. Thus order creation will also fail. That's why there has to be rollback if failed any of data. Data cannot be simply deleted by row id because row could be modified(Invoicing order or change of order status).

Sorry for my bad Englist.

share|improve this question

migrated from stackoverflow.com Oct 6 '12 at 15:46

This question came from our site for professional and enthusiast programmers.

    
Sorry, I am new at stackoverflow, is there is way to move it automatically without reposting? –  user1597483 Oct 6 '12 at 12:56
1  
Are you using any PHP framework? This can be done through transactions. If any error arises during a transaction, all changes will be rolled back to its previous state. –  suke Oct 6 '12 at 12:57
    
I've flagged it for moderator attention with the comment that it might best be moved. Welcome to Stack Overflow by the way :-) Wrapping your inserts in a transaction would gurantee that if the insert failed at some point, all changes on that connection would be rolled back. However, I think MyISAM is not transaction safe (it's the InnoDB engine that is) so this option isn't available to you and why a DBA might be able to help you more :-) –  dash Oct 6 '12 at 12:58
    
I am using Joomla 2.5 it has methods for transactions but it works only when table engine is InnoDB. –  user1597483 Oct 6 '12 at 13:03
add comment

1 Answer 1

I have BAD NEWS / GOOD NEWS for you

BAD NEWS

MyISAM is simply not suitable for you because...

  • MyISAM is not a transactional storage engine
  • Writes to a MyISAM cannot be rolled back
  • MyISAM tables that are open crash very easily if the mysqld process or DB server crashes

GOOD NEWS

Using InnoDB is absolutely in your best interests. You will have to do some work for this to become a reality.

STEP 01 : Create a Script that will Convert all tables to InnoDB

Conversion of all tables to InnoDB is incredibly straightforward.

MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM'"
SQL="${SQL} AND table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > /root/ConvertMyISAMToInnoDB.sql

STEP 02 : Configure InnoDB Settings for Multithreading and Caching

If the DB Server has 8GB RAM, use the following settings in /etc/my.cnf

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table
innodb_buffer_pool_size=4G
innodb_log_file_size=1G
innodb_log_buffer_size=32M
innodb_open_files=16384
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=5000
key_buffer_size=8M

STEP 03 : Create new InnoDB Logs

cd /var/lib/mysql
service mysql stop
rm -f ibdata1 ib_logfile0 ib_logfile1
service mysql start

STEP 04 : Perform InnoDB Conversion

mysql ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql

STEP 05 : Make changes in your code for transactional support

You will have to employ the use of three commands : 1) START TRANSACTION; 2) COMMIT; 3) ROLLBACK;

Before you starting writing new data, precede it once with

START TRANSACTION;

START TRANSACTION; will create an initial checkpoint in the event you want to

  • commit all changes to the database (via COMMIT)
  • abandon all subsequent changes (via ROLLBACK)

Thus, in your PHP code, you must strategically place COMMIT; commands in places your know you want to fully submit all INSERTs, UPDATEs, and DELETEs of all InnoDB tables. In like fashion, you must also place ROLLBACK; commands in places where you wish to abandoned compiled changes to InnoDB tables.

CAVEAT

If you still want to the data as MyISAM, I have an additional suggestion.

Get another DB Server. Load all data into the second server as follows:

STEP 01 : service mysql stop

STEP 02 : Add this to /etc/my.cnf

[mysqld]
skip-innodb

STEP 03 : service mysql start

STEP 04 : Load the data into the second server

mysql -hIPofSecondServer ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql

STEP 05 : Setup replication from InnoDB Server to MyISAM server

That way, you can change back to MyISAM by just failing over to the server that's MyISAM

share|improve this answer
    
Thank you for very clear and informative answer. I am new here so I can not upvote your answer. Problem is that this process where I need rollback would execute 2-3 times a day, it syncronizes eshop with accounting program on local computer. I don't think that customer(who will buy my sync tool) would like also to buy better hosting plan because InnoDB has higher requirements for MySQL server. I am thinking to implement this programmatically by saving every row which is UPDATED and DELETED before sending actual query. –  user1597483 Oct 8 '12 at 9:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.