PDA

View Full Version : Do I need InnoDB... or can I fake it?



westmunc
02-08-2004, 02:33 AM
I seem to have learnt the hard way that normal mySQL tables aren't a true relational database -- I've configured a set of tables that was supposed to go something like this: information stored respectively in tables X and Y were referenced in table Z. Table Z was referenced in Table A; and ideally when Table A was to be displayed, it would show all the information from tables X and Y. However, phpMyAdmin's relational helper doesn't seem to support this two-generation sort of construct, and no amount of fiddling about in phpMyEdit can make it display what I want. Does anyone have suggestions?

I have heard that mySQL can make true relational databases with InnoDB, but InnoDB support is turned off by default, and I don't know if I am able to activate it.

jalal
02-08-2004, 03:09 AM
MySQL is a true relational database. It maybe that PhpMyAdmin can't create what you want with its interface, but you shoulc be able to do anything you need using raw SQL.

InnoDB, if I remember correctly is used for ACID transactions and is disabled because it adds a fair bit of overhead and is rarely, if ever, needed on a web site.

HTH

codingscape
06-09-2004, 04:15 PM
You would have to recompile mysql in order to activeate InnoDB. I don't know how WestHost feels about you doing that on your account, though it is possible.

I would reccomend you ditch phpMyAdmin and use a the dbdesigner tool from www.fabforce.net, it's free and it lets you graphically design your databases and then remotely connect to your server to run SQL statements directly against your server to create and update the tables.

wlanni
07-19-2006, 03:48 PM
Actually, all you have to do is open /etc/my.cnf and remove the skip-innodb line, and innodb will work fine. The version of MySQL on WestHost's vsd's has innodb compiled with it. And in order to create databases that use Foreign Keys, I am pretty sure you need InnoDB for it to work.

However, you will probably run into a problem I'm having, where when you type in mysqld in the shell, you get a warning "can't create test file /usr/local/mysql/data/[filename]
InnoDB: Operating system error number 13 in a file operation
InnoDB: the error means mysqld does not have the access rights to the directory... etc.

so now it's time to google to figure out how to give the mysqld access rights.

blender
07-19-2006, 04:48 PM
I believe that the previous entry mentioning that a recompile was needed was probably referring to the MySQL 3.x that WH offers but since then they have offered 4.x which does indeed have InnoDB included as you mentioned. Since /usr/local/mysql/data is root owned I wouldn't waste too much time trying to figure it out. Most likely what you need is the --datadir flag to point mysqld to the data directory that WH uses (--datadir=/var/lib/mysql )

I did a "ps -auxwww" in my account and here are the flags that mysqld is currently running with:

/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --user=blender --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock

Hope that helps.

Barb54
08-01-2006, 04:46 PM
MySQL IS a true relational database and when you combine it with PHP it's dynamite! I have no problems rendering data such as you are talking about using php scripts. There are many tools out there to help render the data in the way you want to see it without knowing any php so do a Google or MSN search for PHP/mySQL database tools.

HTH,
Barb