cancel
Showing results for 
Search instead for 
Did you mean: 

MySQL question ...InnoDB?

oliverb
Grafter
Posts: 606
Registered: 02-08-2007

MySQL question ...InnoDB?

So anyway I'm reading this book on SQL and it puts a lot of emphasis on Foriegn keys and referential integrity.
As I understand it Plusnet's MySQL service uses the MyISAM engine, which doesn't support foriegn keys.
I recall the question of installing InnoDB being raised four years ago...
Looking at the admin page it shows a list of database engines including InnoDB, and it appears possible to alter a table to use InnoDB instead of MyISAM.
Is this official?
6 REPLIES
Prod_Man
Grafter
Posts: 286
Registered: 04-08-2007

Re: MySQL question ...InnoDB?

Hello There,
You can create Tableds to be stored in various methods.
It is partof the Specification that the MySQL Server has to know to create the Table in the first Place.
But yes you can chaneg the Type of Table as you so chose.
Hell... you can even create a Temporary Table that is only stored in Memory if you really wanted to store temporary stuff.
When you create a Tabel you can specify which method it's stored by: ISAM, MyISAM, InnoDB, CSV, HEAP, Memory etc...
I assume it will convert without hitch but there is always the possibility of somethign going wrong.
Hit this in your browser: http://mysql.plus.net/ to get to PHPMyAdmin to do it.
That or better to use MySQL Administrator (Free) and Login with your MySQL Details inorder to maintain your Tables as you wish.
Hope that helps.
Jim,
oliverb
Grafter
Posts: 606
Registered: 02-08-2007

Re: MySQL question ...InnoDB?

I'm a bit confused because of this:
http://community.plus.net/forum/index.php/topic,44632.0.htmlThere were several questions in the years 2003 to 2005 and the standard answer was something along the lines of "We don't install InnoDB because bad things could happen if we do" though I'm not clear about the details.
Anyway thanks for the advice, I'm wondering what is meant by a memory or heap table, are they aliases for the same thing, and would that be a sensible way to store ... say ... session data, which is important in the short term, but discarded once the session ends. I'm presuming that a memory table would be discarded if the server was restarted.
Ben_Brown
Grafter
Posts: 2,839
Registered: 13-06-2007

Re: MySQL question ...InnoDB?

InnoDB is now supported on our mysql servers, use ENGINE=InnoDB in your creation statement. What Phil said about the data all being in one place was true until recently when we changed our mysql configuration to use a file for each InnoDB table, and imported any innodb tables to use this type of storage.
You're bang on about tables in memory being discarded if the server is restarted. Temporary tables (made with CREATE TEMPORARY TABLE ...) only last for the session, so as soon as you log out (or your script disconnects) the table is discarded.
[Moderator's note by Barry Zubel: Quote of previous post removed as per the link:rules]
Community Veteran
Posts: 3,789
Registered: 08-06-2007

Re: MySQL question ...InnoDB?

Temporary tables are great - I often use them to update a table where records have been selected from an inner join on the same table - MySQL doesn't like this but inserting the PK's into a temporary table, then using an update statement selecting the ID's back from a temporary table works perfectly.
And the temporary table is dropped as soon as the statement finishes executing.
I guess you mean the innodb_file_per_table statement Ben - one of the best changes that the InnoDB backend received IMO.
B.
Ben_Brown
Grafter
Posts: 2,839
Registered: 13-06-2007

Re: MySQL question ...InnoDB?

Yeah that's exactly it. You may have noticed a few months ago there were a couple of planned early morning outages on rumpus and humbug, that was what we were doing.
oliverb
Grafter
Posts: 606
Registered: 02-08-2007

Re: MySQL question ...InnoDB?

Thanks, in the longer term I'm unlikely to actually need foriegn key support for real, but right now I'm looking at examples in "SQL Demystified" which seems to assume foriegn key support. Anyway "Engine=InnoDB" works great once I found all the occurances of "CREATE TABLE" in the sample file.
Their sample file threw a LOT of syntax errors, turns out MySQL seems to be picky about comments, which must be two hyphens followed by a space.
I'm not even sure how much SQL I really need to learn, since presumably any CGI scripts I write will interact with the database through an interface module, and not by cranking out SQL code directly. Then again I'll need to get the hang of tables and data types.