Re: creating related tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter, et al -- ...and then Peter Brawley said... % % > I can't find a my.cnf file anywhere on this % > FreeBSD 4.5 system! % > % > I know [also from your other reply :-] that I can set various options in % > my personal .my.cnf file... Does that include innodb file paths and the % > like, or is that only in the system-level file? % % IMO it's best to have just one my.cnf.ini. I imagine so, but I can't change the system file. Of course, I haven't found it yet, either! % % > % skip-innodb % > % is not commented out, do so. If other innodb vars are commented out, % > % uncomment them. % > % > I'll leave the rest of these checks until I find the file... Meanwhile, % > here are some selections from a 'show variables;' that make me wonder % > about our innodb status... ... % % See the manual about how to set up InnoDB. You will have to create the % InnoDB directory yourself, then set % innodb_data_file_path = : % innodb_data_home_dir = % innodb_log_arch_dir = http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE969okGb7uCXufRwARAuBaAKCctm6gNxYboMPLJ/k44M3gqGyBrgCgxvKQ N53k36P1JzNdxBIBjoSgRgo= =DSoI -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating related tables
> I can't find a my.cnf file anywhere on this > FreeBSD 4.5 system! > > I know [also from your other reply :-] that I can set various options in > my personal .my.cnf file... Does that include innodb file paths and the > like, or is that only in the system-level file? IMO it's best to have just one my.cnf.ini. > % skip-innodb > % is not commented out, do so. If other innodb vars are commented out, > % uncomment them. > > I'll leave the rest of these checks until I find the file... Meanwhile, > here are some selections from a 'show variables;' that make me wonder > about our innodb status... > > Variable_name Value > > bdb_home /var/db/mysql/ > datadir /var/db/mysql/ > have_bdb YES > have_innodb DISABLED > have_isam YES > innodb_additional_mem_pool_size 1048576 > innodb_buffer_pool_size 8388608 > innodb_data_file_path > innodb_data_home_dir > innodb_file_io_threads 4 > innodb_force_recovery 0 > innodb_thread_concurrency 8 > innodb_flush_log_at_trx_commit 16777216 > innodb_fast_shutdown ON > innodb_flush_method > innodb_lock_wait_timeout 50 > innodb_log_arch_dir > innodb_log_archive OFF > innodb_log_buffer_size 1048576 > innodb_log_file_size 5242880 > innodb_log_files_in_group 2 > innodb_log_group_home_dir > innodb_mirrored_log_groups 1 > transaction_isolation READ-COMMITTED > tmp_table_size 33554432 > tmpdir /var/tmp/ > version 3.23.52 See the manual about how to set up InnoDB. You will have to create the InnoDB directory yourself, then set innodb_data_file_path = : innodb_data_home_dir = innodb_log_arch_dir = http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating related tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter, et al -- ...and then Peter Brawley said... % % David, % % >From any client... % SHOW VARIABLES LIKE 'version'; % or % SELECT @@version; % will show the version. Ahhh... Thanks! Well, it looks like we're at 3.23.52. Not all is lost, but I wonder why the truncate worked... % % Search your my.cnf/ini for 'innodb'. If This is another one... I can't find a my.cnf file anywhere on this FreeBSD 4.5 system! I know [also from your other reply :-] that I can set various options in my personal .my.cnf file... Does that include innodb file paths and the like, or is that only in the system-level file? % skip-innodb % is not commented out, do so. If other innodb vars are commented out, % uncomment them. I'll leave the rest of these checks until I find the file... Meanwhile, here are some selections from a 'show variables;' that make me wonder about our innodb status... Variable_name Value bdb_home /var/db/mysql/ datadir /var/db/mysql/ have_bdb YES have_innodb DISABLED have_isam YES innodb_additional_mem_pool_size 1048576 innodb_buffer_pool_size 8388608 innodb_data_file_path innodb_data_home_dir innodb_file_io_threads4 innodb_force_recovery 0 innodb_thread_concurrency 8 innodb_flush_log_at_trx_commit16777216 innodb_fast_shutdown ON innodb_flush_method innodb_lock_wait_timeout 50 innodb_log_arch_dir innodb_log_archiveOFF innodb_log_buffer_size1048576 innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir innodb_mirrored_log_groups1 transaction_isolation READ-COMMITTED tmp_table_size33554432 tmpdir/var/tmp/ version 3.23.52 How does it look, Doc? Will I ever play the violin? % % PB Thanks & HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE96pQ4Gb7uCXufRwARAsdwAJ0aiPa4zkQ/rHuB88TCOCDsfNb/agCgyyZk cClg0FJ5roYSnM03ZYVo1Yc= =Xdn6 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating related tables
David, The server and 8 command-line utilities (mysqladmin, mysqldump, mysqlimport, mysqlshow, mysqlcheck, myisamchk, mysqlhotcopy and myisampack) read MySQL option files. Under Linux, they read options from /etc/my.cnf, and under Windows they read from c:\windows\my.ini and c:\my.cnf, in that order. The server reads from the [mysqld] section of the file. Utilities and all clients read from the [client] section of these files. Utilities also read from an options file section named after themselves, so for example mysqladmin reads from a [mysqladmin] section, if present. You can use command line arguments to tell the server and these utilities which configuration file(s) to read, or to read no configuration file at all: --defaults-file=fileName This tells the utility to read options from fileName. --defaults-extra-file=fileName This tells the utility to read fileName after reading the global option file(s). --no-defaults This tells the utility to read no option file. PB - > Peter, et al -- > > ...and then Peter Brawley said... > % > % Did you touch up my.cnf/ini to enable InnoDB? > > Oh, yeah -- can I do that on a per-user basis or is that for the whole > database system? > > > % > % PB > > > Thanks again & HAND > > mysql query, > :-D > - -- > David T-G * There is too much animal courage in > (play) [EMAIL PROTECTED] * society and not sufficient moral courage. > (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" > http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.0.7 (FreeBSD) > > iD4DBQE96moiGb7uCXufRwARAl9CAJdOfMmiPhTmLpCRHBHNVpW+0kqPAKCcw3Ox > WZBnKmovtOaD8pSIV62IgQ== > =TKqK > -END PGP SIGNATURE- > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating related tables
David, >From any client... SHOW VARIABLES LIKE 'version'; or SELECT @@version; will show the version. Search your my.cnf/ini for 'innodb'. If skip-innodb is not commented out, do so. If other innodb vars are commented out, uncomment them. PB - - Original Message - From: "David T-G" <[EMAIL PROTECTED]> To: "mysql users" <[EMAIL PROTECTED]> Cc: "Peter Brawley" <[EMAIL PROTECTED]> Sent: Sunday, December 01, 2002 11:58 AM Subject: Re: creating related tables > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Peter, et al -- > > ...and then Peter Brawley said... > % > % Did you touch up my.cnf/ini to enable InnoDB? > > No, I didn't; I'm just a user here. I have yet to see details on whether > or not we really have InnoDB support. Is there a way I can tell from the > mysql client, or does it depend on going to find the person who installed > it to ask what he did at setup time? > > I just don't know how to get into the code to see how it's set up, how > it's installed, and so on... There's no > > SHOW VERSION ; > > command that I can find :-) > > > % > % PB > > > Thanks & HAND > > mysql query, > :-D > - -- > David T-G * There is too much animal courage in > (play) [EMAIL PROTECTED] * society and not sufficient moral courage. > (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" > http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.0.7 (FreeBSD) > > iD8DBQE96mn3Gb7uCXufRwARAthzAKDBf6bTrH3opaAeaoWXGtYr7fBRAgCfRalq > qlqdVzVCkoNegvKdqc2wF7E= > =Ry+u > -END PGP SIGNATURE- > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating related tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter, et al -- ...and then Peter Brawley said... % % Did you touch up my.cnf/ini to enable InnoDB? Oh, yeah -- can I do that on a per-user basis or is that for the whole database system? % % PB Thanks again & HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD4DBQE96moiGb7uCXufRwARAl9CAJdOfMmiPhTmLpCRHBHNVpW+0kqPAKCcw3Ox WZBnKmovtOaD8pSIV62IgQ== =TKqK -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating related tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter, et al -- ...and then Peter Brawley said... % % Did you touch up my.cnf/ini to enable InnoDB? No, I didn't; I'm just a user here. I have yet to see details on whether or not we really have InnoDB support. Is there a way I can tell from the mysql client, or does it depend on going to find the person who installed it to ask what he did at setup time? I just don't know how to get into the code to see how it's set up, how it's installed, and so on... There's no SHOW VERSION ; command that I can find :-) % % PB Thanks & HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE96mn3Gb7uCXufRwARAthzAKDBf6bTrH3opaAeaoWXGtYr7fBRAgCfRalq qlqdVzVCkoNegvKdqc2wF7E= =Ry+u -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating related tables
Did you touch up my.cnf/ini to enable InnoDB? PB - - Original Message - From: "David T-G" <[EMAIL PROTECTED]> To: "mysql users" <[EMAIL PROTECTED]> Sent: Sunday, December 01, 2002 6:32 AM Subject: Re: creating related tables > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hello again -- > > ...and then David T-G said... > % > % I would like to create a couple of related tables (just for starters) but > % I can't figure out how to do it; there are lots of CREATE TABLE examples > % in the mysql manual but nothing that shows related columns. > > Aha! I get it! I *have* to have the InnoDB add-on in order to support > foreign keys and fully relational tables, right? Boy, that took a lot of > digging. > > So now I know that I can > > CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; > CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), > FOREIGN KEY (parent_id) REFERENCES parent(id) > ON DELETE SET NULL) TYPE=INNODB; > > to create relational tables, just like the manual says. > > Now I need to have innodb included, then. The manual says that it's > included in 4.0.x, so if I have 4.0 then I should have innodb. Not only > does the SQL above not give me any errors, but I also successfully ran > > TRUNCATE child ; > TRUNCATE parent ; > > and since the manual says that TRUNCATE is new with 4.0 I must have a 4.x > rather than 3.x mysqld. Good so far. > > If that's the case, then why do I get > > mysql> show create table child ; > +---+-+ > | Table | Create Table > | > +---+-+ > | child | CREATE TABLE `child` ( > `id` int(11) default NULL, > `parent_id` int(11) default NULL, > KEY `par_ind` (`parent_id`) > ) TYPE=MyISAM | > +---+-+ > > mysql> show create table parent ; > +++ > | Table | Create Table > | > +++ > | parent | CREATE TABLE `parent` ( > `id` int(11) NOT NULL default '0', > PRIMARY KEY (`id`) > ) TYPE=MyISAM | > +++ > > on my tables? TYPE=MyISAM doesn't look very innodb-like... > > > TIA & HAND > > mysql query, > :-D > - -- > David T-G * There is too much animal courage in > (play) [EMAIL PROTECTED] * society and not sufficient moral courage. > (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" > http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.0.7 (FreeBSD) > > iD8DBQE96h2CGb7uCXufRwARAgtQAKCaQ9s/Yw6f/KP8U8kKHgbZjysb7gCfSMoW > 2PJU8VEIpkNknj5Ii1ZiC2I= > =xrei > -END PGP SIGNATURE- > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating related tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello again -- ...and then David T-G said... % % I would like to create a couple of related tables (just for starters) but % I can't figure out how to do it; there are lots of CREATE TABLE examples % in the mysql manual but nothing that shows related columns. Aha! I get it! I *have* to have the InnoDB add-on in order to support foreign keys and fully relational tables, right? Boy, that took a lot of digging. So now I know that I can CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) TYPE=INNODB; to create relational tables, just like the manual says. Now I need to have innodb included, then. The manual says that it's included in 4.0.x, so if I have 4.0 then I should have innodb. Not only does the SQL above not give me any errors, but I also successfully ran TRUNCATE child ; TRUNCATE parent ; and since the manual says that TRUNCATE is new with 4.0 I must have a 4.x rather than 3.x mysqld. Good so far. If that's the case, then why do I get mysql> show create table child ; +---+-+ | Table | Create Table | +---+-+ | child | CREATE TABLE `child` ( `id` int(11) default NULL, `parent_id` int(11) default NULL, KEY `par_ind` (`parent_id`) ) TYPE=MyISAM | +---+-+ mysql> show create table parent ; +++ | Table | Create Table | +++ | parent | CREATE TABLE `parent` ( `id` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM | +++ on my tables? TYPE=MyISAM doesn't look very innodb-like... TIA & HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE96h2CGb7uCXufRwARAgtQAKCaQ9s/Yw6f/KP8U8kKHgbZjysb7gCfSMoW 2PJU8VEIpkNknj5Ii1ZiC2I= =xrei -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
creating related tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I would like to create a couple of related tables (just for starters) but I can't figure out how to do it; there are lots of CREATE TABLE examples in the mysql manual but nothing that shows related columns. For instance, I'd have a client table with name, address, etcetc, and client ID columns. Then I'd also have a room table with room listings, capacities, locations, etcetc, and room ID columns. Add to that a classes table showing info about classes offerred and, of course, a class ID column. Finally maybe an instructor table with the same sort of columns. Now I want a schedule table that will instantiate a course and track what clients are in it, using the client ID from the client table and the class ID from the class table and the room and the instructor and such. How do I specify a foreign key or a related column or a whatever when I create this table? TIA & HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE96XHRGb7uCXufRwARAiB2AJ49zRrJWsTF/eMwwoC8I4Ao3wk9SQCfdDXY AVkqYPa71Hvj+ZPYsNyCJ5g= =zryv -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
