Converting MyISAM to InnoDB

Need help with the Administration of your Joomla! 2.5 site? This is the spot for you.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Locked
hanleyhansen
Joomla! Apprentice
Joomla! Apprentice
Posts: 33
Joined: Mon Jun 08, 2009 2:46 pm
Location: NJ, USA
Contact:

Converting MyISAM to InnoDB

Post by hanleyhansen » Sat Dec 10, 2011 9:50 pm

I have some custom work being done on my Joomla! site. Will it be a problem for me to convert all my Joomla! tables from MyISAM to InnoDB? Will it drastically affect anything? My biggest fear is the toll on searching since InnoDB doesn't support full-text indexing. Can someone shed more light on this? The reason why I need to do this is so that I can have foreign keys on a new database I am creating. Or can My new database just be InnoDB and have a foreign key of the user ID from the Joomla! users table? Please enlighten me. And sorry if this is in the wrong thread!

User avatar
seoirserob
Joomla! Apprentice
Joomla! Apprentice
Posts: 12
Joined: Wed May 18, 2011 11:16 am
Location: Ireland
Contact:

Re: Converting MyISAM to InnoDB

Post by seoirserob » Mon Jan 02, 2012 9:35 pm

Quite some time now since . . . but I am now faced with a similar issue. I want to integrate a POS db which is innodb and may require foreign keys between the two . . . How did this work out for you? and have you any advice?

My problem is with a virtuemart shop where I want an outlet's Point of Sale (barcode and register) system to be poulated with the names and quantities of the item in the webshop.

Slán go fóill.

Rob.

hanleyhansen
Joomla! Apprentice
Joomla! Apprentice
Posts: 33
Joined: Mon Jun 08, 2009 2:46 pm
Location: NJ, USA
Contact:

Re: Converting MyISAM to InnoDB

Post by hanleyhansen » Mon Jan 02, 2012 10:29 pm

Hello,

I ended up creating my own InnoDB table and just using Joomla!'s built-in classes for getting the user to populate my table. I stored a variable with whatever the class returned and then put that in my table. All i needed was Joomla's user table. This might not help you much but try to see if maybe VirtueMart has classes you can access that'll yield the required information. Worth a shot! If you do figure it out please share.

User avatar
localizador1709
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Thu Apr 09, 2009 10:03 pm
Location: São Paulo - Brasil
Contact:

Re: Converting MyISAM to InnoDB

Post by localizador1709 » Thu Mar 15, 2012 7:54 pm

Someone, please tell me know: how to convert the DB to InnoDB?

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30813
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: Converting MyISAM to InnoDB

Post by Per Yngve Berg » Thu Mar 15, 2012 9:17 pm

You can change the db engine of the table in phpmyadmin (Operations tab).

User avatar
localizador1709
Joomla! Apprentice
Joomla! Apprentice
Posts: 16
Joined: Thu Apr 09, 2009 10:03 pm
Location: São Paulo - Brasil
Contact:

Re: Converting MyISAM to InnoDB

Post by localizador1709 » Fri Mar 16, 2012 11:38 am

Thanks for the reply. So I have to change the tables one by one. Is there a command to change all at once?

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30813
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: Converting MyISAM to InnoDB

Post by Per Yngve Berg » Fri Mar 16, 2012 2:34 pm

ALTER TABLE table_name ENGINE=innodb

hanjimail
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 221
Joined: Sat May 31, 2008 12:15 am
Location: Yokohama, Japan
Contact:

Re: Converting MyISAM to InnoDB

Post by hanjimail » Sat Dec 29, 2012 12:05 am

Per Yngve Berg wrote:ALTER TABLE table_name ENGINE=innodb
When you say table_name, there are a lot in DB. Please tell me how to convert all the table_name into innodb.
Norito H.Yoshida, Yokohama, Japan
[email protected]
http://goyat.biz/

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30813
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: Converting MyISAM to InnoDB

Post by Per Yngve Berg » Sat Dec 29, 2012 9:32 am

I don't think there is a wild card available. Put the commands into a file and execute the file.

User avatar
cbahiana
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 186
Joined: Wed Aug 24, 2005 2:03 pm
Location: Rio de Janeiro
Contact:

Re: Converting MyISAM to InnoDB

Post by cbahiana » Thu Jun 27, 2013 12:54 pm

I don't know if anybody is still interested in this, but here is what I did:

*Using phpMyAdmin and Gedit on Ubuntu Linux. You will need a text editor that allows you to select the line change between the text lines or use a line change operator (usually \n)

1) Select the DB you want to operate;

2) Click on the "SQL" tab;

3) execute this query:

Code: Select all

SHOW TABLES
4) Copy the result to a text editor (I've used Gedit - you will need good "Search and Replace funcionality);

5) Erase the following lines (In my case the table prefixes are mxlb_. Find what is yours):

Code: Select all

Tables_in_your_table_name
mxlb_finder_tokens
mxlb_finder_tokens_aggregate
6) Use the "Search and Replace" to replace (remember to use your prefix, this one is mine)

Code: Select all

mxlb_
by

Code: Select all

ALTER TABLE mxlb_
7) Use the "Search and Replace" again, selecting from the end of the first line to the begining of the next line and choose "Search and Replace" from you text editor (this is tricky, not all text editors do this. What you want is the line change. Again, this works on Gedit);

8) Replace the line change code by

Code: Select all

 ENGINE = INNODB;\n
Note that there's an empty space before ENGINE. You need it!

9) Paste

Code: Select all

 ENGINE = INNODB
after the last table name on the last line (there wasn't a line change after it...)

10) Copy all this, go to the SQL tab of your DB on phpMyAdmin and paste it on the box, then click "Execute".

There you are!
Carlos Bahiana
You can't always get what you want, but if you try sometimes...

TheCellarRoom
Joomla! Intern
Joomla! Intern
Posts: 77
Joined: Wed Jan 02, 2013 10:09 am

Re: Converting MyISAM to InnoDB

Post by TheCellarRoom » Thu Jun 27, 2013 1:37 pm

If you have indexes set up, i think you need to create a new table as INNODB and then insert the data from the MYISAM table and recreate the indexes. I can't find the url but it was in some mysql guidance.

Failing to do this, if i remember correctly could slow down your data retrieval

User avatar
cbahiana
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 186
Joined: Wed Aug 24, 2005 2:03 pm
Location: Rio de Janeiro
Contact:

Re: Converting MyISAM to InnoDB

Post by cbahiana » Thu Jun 27, 2013 1:43 pm

You are absolutely right, and here is the reference:
http://dev.mysql.com/doc/refman/5.5/en/ ... nnodb.html
Carlos Bahiana
You can't always get what you want, but if you try sometimes...

TheCellarRoom
Joomla! Intern
Joomla! Intern
Posts: 77
Joined: Wed Jan 02, 2013 10:09 am

Re: Converting MyISAM to InnoDB

Post by TheCellarRoom » Thu Jun 27, 2013 5:18 pm

brilliant. Cbahiana thank you! :-)

User avatar
BreathWork
Joomla! Intern
Joomla! Intern
Posts: 87
Joined: Mon Aug 29, 2005 8:03 am
Location: Copenhagen, Denmark
Contact:

Re: Converting MyISAM to InnoDB

Post by BreathWork » Sat Apr 09, 2016 8:42 am

Or you could simply run this:

Code: Select all

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'mydatabase'
Change "mydatabase" to your db_name, then run the script and voila copy paste the output and run it.

No point in stuffing around in a queue, work the code, that's what it's for.
geoffreysmith.eu - Making the world a better place, one breath at a time


Locked

Return to “Administration Joomla! 2.5”