Converting MyISAM to InnoDB
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.
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.
-
- Joomla! Apprentice
- Posts: 33
- Joined: Mon Jun 08, 2009 2:46 pm
- Location: NJ, USA
- Contact:
Converting MyISAM to InnoDB
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!
- seoirserob
- Joomla! Apprentice
- Posts: 12
- Joined: Wed May 18, 2011 11:16 am
- Location: Ireland
- Contact:
Re: Converting MyISAM to InnoDB
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.
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.
-
- Joomla! Apprentice
- Posts: 33
- Joined: Mon Jun 08, 2009 2:46 pm
- Location: NJ, USA
- Contact:
Re: Converting MyISAM to InnoDB
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.
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.
- localizador1709
- Joomla! Apprentice
- Posts: 16
- Joined: Thu Apr 09, 2009 10:03 pm
- Location: São Paulo - Brasil
- Contact:
Re: Converting MyISAM to InnoDB
Someone, please tell me know: how to convert the DB to InnoDB?
- Per Yngve Berg
- Joomla! Master
- Posts: 30930
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: Converting MyISAM to InnoDB
You can change the db engine of the table in phpmyadmin (Operations tab).
- localizador1709
- Joomla! Apprentice
- Posts: 16
- Joined: Thu Apr 09, 2009 10:03 pm
- Location: São Paulo - Brasil
- Contact:
Re: Converting MyISAM to InnoDB
Thanks for the reply. So I have to change the tables one by one. Is there a command to change all at once?
- Per Yngve Berg
- Joomla! Master
- Posts: 30930
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: Converting MyISAM to InnoDB
ALTER TABLE table_name ENGINE=innodb
-
- Joomla! Enthusiast
- Posts: 221
- Joined: Sat May 31, 2008 12:15 am
- Location: Yokohama, Japan
- Contact:
Re: Converting MyISAM to InnoDB
When you say table_name, there are a lot in DB. Please tell me how to convert all the table_name into innodb.Per Yngve Berg wrote:ALTER TABLE table_name ENGINE=innodb
- Per Yngve Berg
- Joomla! Master
- Posts: 30930
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: Converting MyISAM to InnoDB
I don't think there is a wild card available. Put the commands into a file and execute the file.
- cbahiana
- Joomla! Enthusiast
- Posts: 186
- Joined: Wed Aug 24, 2005 2:03 pm
- Location: Rio de Janeiro
- Contact:
Re: Converting MyISAM to InnoDB
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:
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):
6) Use the "Search and Replace" to replace (remember to use your prefix, this one is mine)
by
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
Note that there's an empty space before ENGINE. You need it!
9) Paste
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!
*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
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
Code: Select all
mxlb_
Code: Select all
ALTER TABLE mxlb_
8) Replace the line change code by
Code: Select all
ENGINE = INNODB;\n
9) Paste
Code: Select all
ENGINE = INNODB
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...
You can't always get what you want, but if you try sometimes...
-
- Joomla! Intern
- Posts: 77
- Joined: Wed Jan 02, 2013 10:09 am
Re: Converting MyISAM to InnoDB
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
Failing to do this, if i remember correctly could slow down your data retrieval
- cbahiana
- Joomla! Enthusiast
- Posts: 186
- Joined: Wed Aug 24, 2005 2:03 pm
- Location: Rio de Janeiro
- Contact:
Re: Converting MyISAM to InnoDB
You are absolutely right, and here is the reference:
http://dev.mysql.com/doc/refman/5.5/en/ ... nnodb.html
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...
You can't always get what you want, but if you try sometimes...
-
- Joomla! Intern
- Posts: 77
- Joined: Wed Jan 02, 2013 10:09 am
Re: Converting MyISAM to InnoDB
brilliant. Cbahiana thank you! :-)
- BreathWork
- Joomla! Intern
- Posts: 87
- Joined: Mon Aug 29, 2005 8:03 am
- Location: Copenhagen, Denmark
- Contact:
Re: Converting MyISAM to InnoDB
Or you could simply run this:
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.
Code: Select all
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'mydatabase'
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