Revenue Source

Welcome to the Revenue Source affiliate marketing forums.

You are viewing our internet marketing and SEO forums as a guest which gives you limited access to most of our discussions.  By joining our free community, you will have access to post affiliate marketing topics, communicate privately with other members (PM), exchange SEO strategies, and access many other special features.  Registration is fast, simple and absolutely free so please, join our community today!

If you have any problems, please don't hesitate to contact us.

Go Back   Revenue Source > Site Design & Development > Databases
Reload this Page Pitfalls of converting to InnoDB
Tags: , ,

Reply
 
LinkBack Thread Tools Search this Thread
Old
  (#1 (permalink))
Affiliate Blogs is Offline
Revenue Source Veteran
Affiliate Blogs has a brilliant future here!
 
Affiliate Blogs's Avatar
 
Join Date: Oct 2005
Posts: 9,077
Jack of All Trades
CyberSpace United States
   
Pitfalls of converting to InnoDB - 02-25-2007

We often recommend to our clients to convert their current database from MyISAM tables to InnoDB.
The transfer by itself in most cases is almost plain, however the application can be broken by new unexpected errors
1205 (ER_LOCK_WAIT_TIMEOUT)
Lock wait timeout expired. Transaction was rolled back.
1213 (ER_LOCK_DEADLOCK)
Transaction deadlock. You should rerun the transaction.
It is not hard to handle these errors, but you should be aware of.
This is some thing we do in our PHP applications:
PLAIN TEXT
CODE:
  1. [FONT='Courier New', Courier, monospace]class mysqlx extends mysqli {[/font]
  2. [FONT='Courier New', Courier, monospace] [/font]
  3. [FONT='Courier New', Courier, monospace]...[/font]
  4. [FONT='Courier New', Courier, monospace] [/font]
  5. [FONT='Courier New', Courier, monospace] function deadlock_query($query) {[/font]
  6. [FONT='Courier New', Courier, monospace] $MAX_ATTEMPS = 100;[/font]
  7. [FONT='Courier New', Courier, monospace] $current = 0;[/font]
  8. [FONT='Courier New', Courier, monospace] while ($current++ query($query);[/font]
  9. [FONT='Courier New', Courier, monospace] [/font]
  10. [FONT='Courier New', Courier, monospace] if(!$res && ( $this->errno== '1205' || $this->errno == '1213' ) )[/font]
  11. [FONT='Courier New', Courier, monospace] continue;[/font]
  12. [FONT='Courier New', Courier, monospace] else [/font]
  13. [FONT='Courier New', Courier, monospace] break;[/font]
  14. [FONT='Courier New', Courier, monospace] }[/font]
  15. [FONT='Courier New', Courier, monospace] } [/font]
  16. [FONT='Courier New', Courier, monospace]...[/font]
  17. [FONT='Courier New', Courier, monospace]} [/font]



You may want to handle ER_LOCK_WAIT_TIMEOUT in different way, especially for web applications where long waiting is not good, you get the idea.
Also there is script we are using for converting databases with many tables, maybe it will be useful for you
PLAIN TEXT
CODE:
  1. [FONT='Courier New', Courier, monospace]DBNAME="dbname"[/font]
  2. [FONT='Courier New', Courier, monospace]DBUSER="user"[/font]
  3. [FONT='Courier New', Courier, monospace]DBPWD="password"[/font]
  4. [FONT='Courier New', Courier, monospace]for t in $(mysql -u$DBUSER -p$DBPWD --batch --column-names=false -e "show tables" $DBNAME);[/font]
  5. [FONT='Courier New', Courier, monospace]do[/font]
  6. [FONT='Courier New', Courier, monospace]echo "Converting table $t"[/font]
  7. [FONT='Courier New', Courier, monospace]mysql -u$DBUSER -p$DBPWD -e "alter table $t type=InnoDB" $DBNAME;[/font]
  8. [FONT='Courier New', Courier, monospace]done [/font]



There is the standard script mysql_convert_table_format in the MySQL distribution, but it requires Perl and DBI package and sometimes they are absent on used server.


Pitfalls of converting to InnoDB - Read More...
  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads for: Pitfalls of converting to InnoDB
Thread Thread Starter Forum Replies Last Post
Converting deb files in Ubuntu SEO Blogs SEO / SEM 0 01-04-2007 03:26 AM
Affiliate Marketing Program Pitfalls 308 Affiliate Blogs Affiliate Marketing 0 11-20-2006 06:30 AM
Affiliate Marketing Program Pitfalls 308 Affiliate Blogs Affiliate Marketing 0 11-20-2006 12:04 AM
Avoiding the pitfalls of Customer Reciprocation Affiliate Blogs Affiliate Marketing 0 11-17-2006 04:32 AM
Traffic - Driving It, Keeping It, Converting It Affiliate Marketing News Commission Junction 0 10-17-2006 07:48 PM



© 2004-6 RevenueSource.com.  All rights reserved.  Do not duplicate or redistribute in any form.
This website and its logos/design are property of RevenueSource.com.  All rights reserved. vBSEO 3.2.0 RC7


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34