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.
Best Answer
I have BAD NEWS / GOOD NEWS for you
BAD NEWS
MyISAM is simply not suitable for you because...
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.
STEP 02 : Configure InnoDB Settings for Multithreading and Caching
If the DB Server has 8GB RAM, use the following settings in /etc/my.cnf
STEP 03 : Create new InnoDB Logs
STEP 04 : Perform InnoDB Conversion
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;
will create an initial checkpoint in the event you want toThus, 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 placeROLLBACK;
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
STEP 03 :
service mysql start
STEP 04 : Load the data into the second server
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