The Joomla! Forum ™



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.



Post new topic Reply to topic  [ 13 posts ] 
Author Message
PostPosted: Sat Dec 10, 2011 9:50 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Mon Jun 08, 2009 2:46 pm
Posts: 33
Location: NJ, USA
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!


Top
 Profile  
 
PostPosted: Mon Jan 02, 2012 9:35 pm 
User avatar
Joomla! Apprentice
Joomla! Apprentice

Joined: Wed May 18, 2011 11:16 am
Posts: 11
Location: Ireland
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.

_________________
http://henderson.ie http://celtictshirts.ie/index.php


Top
 Profile  
 
PostPosted: Mon Jan 02, 2012 10:29 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Mon Jun 08, 2009 2:46 pm
Posts: 33
Location: NJ, USA
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.


Top
 Profile  
 
PostPosted: Thu Mar 15, 2012 7:54 pm 
User avatar
Joomla! Apprentice
Joomla! Apprentice

Joined: Thu Apr 09, 2009 10:03 pm
Posts: 16
Location: São Paulo - Brasil
Someone, please tell me know: how to convert the DB to InnoDB?


Top
 Profile  
 
PostPosted: Thu Mar 15, 2012 9:17 pm 
Joomla! Master
Joomla! Master

Joined: Mon Oct 27, 2008 9:27 pm
Posts: 17737
Location: Akershus, Norway
You can change the db engine of the table in phpmyadmin (Operations tab).


Top
 Profile  
 
PostPosted: Fri Mar 16, 2012 11:38 am 
User avatar
Joomla! Apprentice
Joomla! Apprentice

Joined: Thu Apr 09, 2009 10:03 pm
Posts: 16
Location: São Paulo - Brasil
Thanks for the reply. So I have to change the tables one by one. Is there a command to change all at once?


Top
 Profile  
 
PostPosted: Fri Mar 16, 2012 2:34 pm 
Joomla! Master
Joomla! Master

Joined: Mon Oct 27, 2008 9:27 pm
Posts: 17737
Location: Akershus, Norway
ALTER TABLE table_name ENGINE=innodb


Top
 Profile  
 
PostPosted: Sat Dec 29, 2012 12:05 am 
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Sat May 31, 2008 12:15 am
Posts: 206
Location: Yokohama, Japan
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
norito@gmail.com
http://kaigai.goyat.info/
goyat.jp


Top
 Profile  
 
PostPosted: Sat Dec 29, 2012 9:32 am 
Joomla! Master
Joomla! Master

Joined: Mon Oct 27, 2008 9:27 pm
Posts: 17737
Location: Akershus, Norway
I don't think there is a wild card available. Put the commands into a file and execute the file.


Top
 Profile  
 
PostPosted: Thu Jun 27, 2013 12:54 pm 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Wed Aug 24, 2005 2:03 pm
Posts: 140
Location: Rio de Janeiro
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:
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:
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:
mxlb_

by
Code:
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:
 ENGINE = INNODB;\n

Note that there's an empty space before ENGINE. You need it!

9) Paste
Code:
 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
Designer, Ergonomist, MSc Production Engineering
http://www.softdesign.eti.br


Top
 Profile  
 
PostPosted: Thu Jun 27, 2013 1:37 pm 
Joomla! Intern
Joomla! Intern

Joined: Wed Jan 02, 2013 10:09 am
Posts: 77
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


Top
 Profile  
 
PostPosted: Thu Jun 27, 2013 1:43 pm 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Wed Aug 24, 2005 2:03 pm
Posts: 140
Location: Rio de Janeiro
You are absolutely right, and here is the reference:
http://dev.mysql.com/doc/refman/5.5/en/converting-tables-to-innodb.html

_________________
Carlos Bahiana
Designer, Ergonomist, MSc Production Engineering
http://www.softdesign.eti.br


Top
 Profile  
 
PostPosted: Thu Jun 27, 2013 5:18 pm 
Joomla! Intern
Joomla! Intern

Joined: Wed Jan 02, 2013 10:09 am
Posts: 77
brilliant. Cbahiana thank you! :-)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 



Who is online

Users browsing this forum: No registered users and 31 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group