Re: About safe_mysqld - another problem
Hi All, This problem is predominant and is being experienced by all who are instaling mysql. please could someone be of help and help us newbies. Thanks Ajay --- peter_tkchoi [EMAIL PROTECTED] wrote: Hi, I'm a beginner of MySql. I've just installed the lastest ver. MySql under RH7.1 Linux. First, I do have runned the mysql_install_db script. When I tried to up the MySql server by running safe_mysqld script, it also failed and now I've got an error message logged as follows: 010720 22:39:06 mysqld started 010720 22:39:06 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010720 22:39:06 mysqld ended I want to ask if anybody help me: 1. What should I do in order to resolve this problem??? Pls advise. Thank you for your attention. Regards. Peter Choi == ·s®ö§K¶O¹q¤l«H½c http://sinamail.sina.com.hk - 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 __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ - 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: mysql.org
John Birrell wrote: On Fri, Jul 20, 2001 at 12:29:52PM -0400, Britt Johnston wrote: MySQL AB and NuSphere had a meeting over the phone, we exchanged information and opinions and NuSphere will propose times for the next meeting. Since these issues are between two private companies, please take your discussions off this list. It is none of our business. By all means, tell us the result. With all due respect, John, I for one absolutely disagree with you on this. I want to be kept as informed as MySQL AB and NuSphere are willing to keep me. I'd be willing to bet there are a sufficient number of participants here who agree with me. Tom Keller mysql? query? database? huh? - 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
rand()
Hi, I am trying the below sql statment: mysql select c.city, t.periodo, t.vitofferta from travel t, city c where t.cityid=c.id and t.periodo2001-07-20 and month(t.periodo)=month(2001-07-20) and YEAR(t.periodo)=YEAR(2001-07-20) and rand() limit 7; The result: +---+++ | city | periodo| vitofferta | +---+++ | Mauritius | 2001-07-21 | 2199 | | Napoli| 2001-07-28 | 645| | London| 2001-07-31 | 345| | London| 2001-07-31 | 375| | London| 2001-07-31 | 375| | London| 2001-07-31 | 385| | London| 2001-07-31 | 385| +---+++ Perfect. But when I try again I want it to display another sent of 7 records but it just keeps displaying the same. +---+ | version() | +---+ | 3.23.36 | +---+ How do I do this. Adrian - 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
MySQL 3.23.40 released
Hi! New version of MySQL is available, here is the changelog: Changes in release 3.23.40 -- * Fixed problem with --low-priority-updates and INSERT's. * Fixed bug in slave thread when under some rare circumstances it could get 22 bytes ahead on the offset in the master * Added slave_wait_timeout for replication. * Fixed problem with UPDATE and BDB tables. * Fixed hard bug in BDB tables when using key parts. * Fixed problem when using the GRANT FILE ON database.* ...; Previously we added the DROP privilege for the database. * Fixed that DELETE FROM table_name ... LIMIT 0 and UPDATE FROM table_name ... LIMIT 0 doesn't delete/update anything. * CHECK TABLE now checks if an auto_increment column contains the value 0. * Sending a SIGHUP to mysqld will now only flush the logs, not reset the replication. * Fixed parser to allow floats of type 1.0e1 (no sign after e). * Option --force to myisamchk now also updates states. * Added option --warnings to mysqld. Now mysqld only prints the error Aborted connection if this option is used. * Fixed problem with SHOW CREATE TABLE when you didn't have a PRIMARY KEY. * Fixed properly the rename of innodb_unix_file_flush_method to innodb_flush_method. * Fixed bug when converting UNSIGNED BIGINT to DOUBLE. This caused a problem when doing comparisons with BIGINT's outside of the signed range. * Fixed bug in BDB tables when querying empty tables. * Fixed a bug when using COUNT(DISTINCT) with LEFT JOIN and there wasn't any matching rows. * Removed all documentation referring to the GEMINI table type. GEMINI is not released under an Open Source license. Regards, - Jani For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Jani Tolonen [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - 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: Questions about extremely large database support
Hi! On Jul 20, Jeremy Zawodny wrote: On Thu, Jul 19, 2001 at 03:15:02PM -0500, Tom Wheeler wrote: Our two most important requirements for the database engine are speed and scalability. We will be inserting probably 5,000,000 records per day into our database Sounds doable--on reasonable hardware. Well, on my tests I was able to add ~4,000,000 rows in 17 hrs with MySQL 3.23, and in 4 hrs in MySQL 4.0 Table had 4 indexes. Removing those increased the speed by several orders of magnitude (!!!). Hadware was moderate - Athlon 1GHz, 640M RAM. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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
How do I do this?
Hi, I am trying the below sql statment: mysql select c.city, t.periodo, t.vitofferta from travel t, city c where t.cityid=c.id and t.periodo2001-07-20 and month(t.periodo)=month(2001-07-20) and YEAR(t.periodo)=YEAR(2001-07-20) and rand() limit 7; The result: +---+++ | city | periodo| vitofferta | +---+++ | Mauritius | 2001-07-21 | 2199 | | Napoli| 2001-07-28 | 645| | London| 2001-07-31 | 345| | London| 2001-07-31 | 375| | London| 2001-07-31 | 375| | London| 2001-07-31 | 385| | London| 2001-07-31 | 385| +---+++ Perfect. But when I try again I want it to display another sent of 7 records but it just keeps displaying the same. +---+ | version() | +---+ | 3.23.36 | +---+ How do I do this. Adrian - 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: About safe_mysqld - another problem
you may find that the permissions for the mysql tables are set to root... I had to chown mysql:mysql the tables in mysqlbasedir/data/mysql/* before I could start mysqld check the permissions -Original Message- From: Ajay Nagarkar [mailto:[EMAIL PROTECTED]] Sent: 21 July 2001 08:05 To: [EMAIL PROTECTED] Subject: Re: About safe_mysqld - another problem Hi All, This problem is predominant and is being experienced by all who are instaling mysql. please could someone be of help and help us newbies. Thanks Ajay --- peter_tkchoi [EMAIL PROTECTED] wrote: Hi, I'm a beginner of MySql. I've just installed the lastest ver. MySql under RH7.1 Linux. First, I do have runned the mysql_install_db script. When I tried to up the MySql server by running safe_mysqld script, it also failed and now I've got an error message logged as follows: 010720 22:39:06 mysqld started 010720 22:39:06 /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 010720 22:39:06 mysqld ended I want to ask if anybody help me: 1. What should I do in order to resolve this problem??? Pls advise. Thank you for your attention. Regards. Peter Choi == 7s.v'K6O9q$l+H=c http://sinamail.sina.com.hk - 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 __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ - 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: Replication Load InFile
On Wed, Jul 18, 2001 at 07:38:10PM +0200, Warren van der Merwe wrote: Hi If you use the load infile to insert images into a blob field, and then the path does not exist when the replication takes place, will there be an error, or is it clever enough to get the blob data from the db? It is not that clever... Work is happening in 4.0 to remedy that, however. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 35 days, processed 269,896,575 queries (89/sec. avg) - 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: gemini tables in mysql
On Fri, Jul 20, 2001 at 01:19:57PM +0300, Sinisa Milivojevic wrote: Yes, in 3.23.41 source tarball only. We will not put Gemini tables in our binaries due to several reasons of which stability is just one. Does this mean they'll re-appear in the manual as well? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 35 days, processed 269,908,904 queries (89/sec. avg) - 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: Transaction for MySQL
On Wed, Jul 18, 2001 at 02:06:03PM -0700, Michael Tam wrote: Hi all, I am new using MySQL. I found out that the default table type used in the binary isn't support transaction and would like to make MySQL uses another table type which supports transaction. The following are my questions regarding the issue: 1) which one should I use InnoDB or BDB? what kind of advantages given by each type? BDB tables have been around in MySQL longer. They provide page-level locking. InnoDB tables are newer but have row-level locking and are faster than BDB tables. 2) currently, I am using the default type - MyISAM, where I am running MySqld-nt. If I would like to switch to another table type. Do I just uninstall the service of MySqld-nt from win2000 and install MySqld-Max service?? Hard to know. I've never installed or uninstalled MySQL on Windows. Does the uninstall remove the data files? 3 what would happen to my existing MyISAM type tables which already exists in MySQL if I do the switch over? and would I able to use/create MyISAM type table uder MySqld-Max?? You can convert any MyISAM tables to InnoDB that you'd like. It's just a matter of: ALTER TABLE mytable TYPE = InnoDB; for each one of them. Or you can use the mysql_convert_table_format script that comes with MySQL. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 35 days, processed 269,914,242 queries (89/sec. avg) - 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: will new databases be reflected in database replication.
On Fri, Jul 20, 2001 at 12:23:11PM +0800, Russell Teo Wee Liang wrote: Hi all, I am running mysql 3.23.33 on a Sun Ultra-250 and is running slave databases on 2 other machines. Everything was running fine until someone created a new database and it wasn't updated in the slave databases. Is the slave set to update EVERYTHING from the master, or just selected databases/tables? Can you post the relvant my.cnf bits? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 35 days, processed 269,929,634 queries (89/sec. avg) - 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: Transaction for MySQL
Hi Jeremy, Thank you for your valuable info. However, may be you can fill me with some more detail here In question 2), what I meant is not uninstall MySql from Windows 2000 but the service of it by calling mysqld-nt --remove. That will take off the service of the mysql-nt from being the default mysql daemon and then I'd call mysqld-max --install to establish the max version of sqld which supports the BDB and InnoDB. This, then, leads to question 3), if I switch the daemon as mentioned in 2) then would the tables created under mysqld-nt will remain intact as mysql-max takes over?? Thank you very much. Regards, Michael - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Michael Tam [EMAIL PROTECTED] Cc: mysql [EMAIL PROTECTED] Sent: Saturday, July 21, 2001 2:57 AM Subject: Re: Transaction for MySQL On Wed, Jul 18, 2001 at 02:06:03PM -0700, Michael Tam wrote: Hi all, I am new using MySQL. I found out that the default table type used in the binary isn't support transaction and would like to make MySQL uses another table type which supports transaction. The following are my questions regarding the issue: 1) which one should I use InnoDB or BDB? what kind of advantages given by each type? BDB tables have been around in MySQL longer. They provide page-level locking. InnoDB tables are newer but have row-level locking and are faster than BDB tables. 2) currently, I am using the default type - MyISAM, where I am running MySqld-nt. If I would like to switch to another table type. Do I just uninstall the service of MySqld-nt from win2000 and install MySqld-Max service?? Hard to know. I've never installed or uninstalled MySQL on Windows. Does the uninstall remove the data files? 3 what would happen to my existing MyISAM type tables which already exists in MySQL if I do the switch over? and would I able to use/create MyISAM type table uder MySqld-Max?? You can convert any MyISAM tables to InnoDB that you'd like. It's just a matter of: ALTER TABLE mytable TYPE = InnoDB; for each one of them. Or you can use the mysql_convert_table_format script that comes with MySQL. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 35 days, processed 269,914,242 queries (89/sec. avg) - 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: gemini tables in mysql
Jeremy Zawodny writes: On Fri, Jul 20, 2001 at 01:19:57PM +0300, Sinisa Milivojevic wrote: Does this mean they'll re-appear in the manual as well? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 35 days, processed 269,908,904 queries (89/sec. avg) I truly do not know. This entire issue is still quite open. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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
How to create table in msql DB through cgi script
Hello, I have a database set up on my web site at my ISP server. Connection to the DB can only be made from the localhost. So I have to do everything through CGI. They have the perl DBI and php3 that I can use to access the database. They provided a script to access the DBI which I modified to create 'TABLES' to the DB, however it did not work. The script and modified lines (indicated by a '#' at the end of the lines) are provided below. If anyone would tell me what is amiss in the coding, and how to correct it, I'll appreciate it. Thanks. -- Joe -- Script == ___ #!/usr/local/bin/perl ### # # mysql_connect.pl # # uses the perl DBI module # # replace database name user name and password with the correct values # ### # # set output to unbuffered and print the correct content-type # $|=1; print Content-Type: text/html\n\n; # # we need to use the DBI module # use DBI; # # replace database name user name and password with the correct values # set up the variables to connect to the database # $user='alchemy'; $passwd=''; $dbname='URW'; $dsn = DBI:mysql:$dbname; # # this establishes the connection to the database and lets us know if it worked # print Attempting to connect to the database...BR; $dbh = DBI-connect($dsn, $user, $passwd); if ( !defined $dbh) { print Fatal Error!BRCould not connect to database.BR; print Database: $dbnameBR; print User: $userBRHR; exit 1; } else { print Successful connection to database: B$dsn/BBR\n; } # # generate our SQL commmand # any SQL command can go here (select, create, alter, etc) # replace table with correct value # $command =CREATE TABLE test_0 (ID INTEGER(6), name VARCHAR(20), address1 VARCHAR(40), address2 VARCHAR(40), city VARCHAR(25), state VARCHAR(16), postalcode VARCHAR(12), country VARCHAR(30); # print Command: $commandp\n; # $command =CREATE TABLE test_1 (ID INTEGER(6), sex CHAR(1), birth date, weight SMALLINT(3), remarks VARCHAR(255); # print Command: $commandp\n; # $command =SHOW TABLES; # print Command: $commandp\n; # $command =DESCRIBE test_0; # print Command: $commandp\n; # $command =select * from test_1; # print Command: $commandp\n; # # # create a statement handle and prepare to execute the SQL statement # $sth = $dbh-prepare($command); if ( !defined $sth ) { print Can't create Statement Handle Object $dbh-errstrBR\n; exit 1; } # # execute the command # $rv = $sth-execute; print Return Code: B$rv/B.$dbh-errstr. BRHR\n; exit; --- Returned message in Netscape Attempting to connect to the database... Successful connection to database: DBI:mysql:URW Command: CREATE TABLE test_0 (ID INTEGER(6), name VARCHAR(20), address1 VARCHAR(40), address2 VARCHAR(40), city VARCHAR(25), state VARCHAR(16), postalcode VARCHAR(12), country VARCHAR(30) Command: CREATE TABLE test_1 (ID INTEGER(6), sex CHAR(1), birth date, weight SMALLINT(3), remarks VARCHAR(255) Command: SHOW TABLES Command: DESCRIBE test_0 Command: select * from test_1 Return Code: Table 'URW'.test_1' doesn't exist
Re: Transaction for MySQL
On Sat, Jul 21, 2001 at 03:46:58AM -0700, Michael Tam wrote: Hi Jeremy, In question 2), what I meant is not uninstall MySql from Windows 2000 but the service of it by calling mysqld-nt --remove. That will take off the service of the mysql-nt from being the default mysql daemon and then I'd call mysqld-max --install to establish the max version of sqld which supports the BDB and InnoDB. This, then, leads to question 3), if I switch the daemon as mentioned in 2) then would the tables created under mysqld-nt will remain intact as mysql-max takes over?? I believe so, yes. But it'd be safest to first back them up. It can't hurt to be safe. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 35 days, processed 270,286,701 queries (89/sec. avg) - 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: Problem installing/compiling 3.23.39 on solaris 8
Hello, concerning problem with installing/compiling mysql 3.23.39 on solaris 8 using gcc 3.0 Solution: compile with gcc 2.95.3 I would still like to use gcc 3.0 thou :-) peter gad - 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: How to create table in msql DB through cgi script
You need to prepare and execute all of the SQL statements. You're just assigning SQL strings to a variable and printing them out -- you need to actually execute them. Only the last statement, select * from test_1, is actually being run. Read through the perl DBI manual: http://search.cpan.org/doc/TIMB/DBI-1.19/DBI.pm Consider setting the RaiseError attribute so you can skip the error checking code. On Sat, Jul 21, 2001 at 03:58:25AM -0700, Joseph Maxwell wrote: Hello, I have a database set up on my web site at my ISP server. Connection to the DB can only be made from the localhost. So I have to do everything through CGI. They have the perl DBI and php3 that I can use to access the database. They provided a script to access the DBI which I modified to create 'TABLES' to the DB, however it did not work. The script and modified lines (indicated by a '#' at the end of the lines) are provided below. If anyone would tell me what is amiss in the coding, and how to correct it, I'll appreciate it. Thanks. -- Joe -- Script == ___ #!/usr/local/bin/perl ### # # mysql_connect.pl # # uses the perl DBI module # # replace database name user name and password with the correct values # ### # # set output to unbuffered and print the correct content-type # $|=1; print Content-Type: text/html\n\n; # # we need to use the DBI module # use DBI; # # replace database name user name and password with the correct values # set up the variables to connect to the database # $user='alchemy'; $passwd=''; $dbname='URW'; $dsn = DBI:mysql:$dbname; # # this establishes the connection to the database and lets us know if it worked # print Attempting to connect to the database...BR; $dbh = DBI-connect($dsn, $user, $passwd); if ( !defined $dbh) { print Fatal Error!BRCould not connect to database.BR; print Database: $dbnameBR; print User: $userBRHR; exit 1; } else { print Successful connection to database: B$dsn/BBR\n; } # # generate our SQL commmand # any SQL command can go here (select, create, alter, etc) # replace table with correct value # $command =CREATE TABLE test_0 (ID INTEGER(6), name VARCHAR(20), address1 VARCHAR(40), address2 VARCHAR(40), city VARCHAR(25), state VARCHAR(16), postalcode VARCHAR(12), country VARCHAR(30); # print Command: $commandp\n; # $command =CREATE TABLE test_1 (ID INTEGER(6), sex CHAR(1), birth date, weight SMALLINT(3), remarks VARCHAR(255); # print Command: $commandp\n; # $command =SHOW TABLES; # print Command: $commandp\n; # $command =DESCRIBE test_0; # print Command: $commandp\n; # $command =select * from test_1; # print Command: $commandp\n; # # # create a statement handle and prepare to execute the SQL statement # $sth = $dbh-prepare($command); if ( !defined $sth ) { print Can't create Statement Handle Object $dbh-errstrBR\n; exit 1; } # # execute the command # $rv = $sth-execute; print Return Code: B$rv/B.$dbh-errstr. BRHR\n; exit; --- Returned message in Netscape Attempting to connect to the database... Successful connection to database: DBI:mysql:URW Command: CREATE TABLE test_0 (ID INTEGER(6), name VARCHAR(20), address1 VARCHAR(40), address2 VARCHAR(40), city VARCHAR(25), state VARCHAR(16), postalcode VARCHAR(12), country VARCHAR(30) Command: CREATE TABLE test_1 (ID INTEGER(6), sex CHAR(1), birth date, weight SMALLINT(3), remarks VARCHAR(255) Command: SHOW TABLES Command: DESCRIBE test_0 Command: select * from test_1 Return Code: Table 'URW'.test_1' doesn't exist -- Maurice Aubrey [EMAIL PROTECTED] MySQL 3.22.32: up 2.1 days, processed 147,097,454 queries (801/sec. avg) - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql.org
Since these issues are between two private companies, please take your discussions off this list. It is none of our business. By all means, tell us the result. This discussion most certainly is our business. I would appeal to both MySQL AB and NuShphere to keep discussions as public as is reasonably possible. Open source development in general is in a phase where business interests, legitimate and otherwise, are directly affecting many pieces of software that are critical to the overall health of the Internet. The more we, the users of MySQL and the like, are squeezed out of the debate the less 'open' open-source will become. The less we show an interest in the debate the less will be the perceived need to keep the source truly open. Monty and co obviously felt there was a need for the mysql list to be informed. I strongly applaud this. It's crucial that we encourage folks like Monty to stick to the principle that open source software is for an open community, even if it does occasionally mean you have to wash your laundry in public. Lists like this are trivial to filter at the recipient's end, so for those who really object please do that rather than encourage that the debate be taken offline. Mark Mark Tiramani FREDO Internet Services [EMAIL PROTECTED] - 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: mysql.org
On Sat, Jul 21, 2001 at 01:13:15PM +0100, Mark Tiramani wrote: Since these issues are between two private companies, please take your discussions off this list. It is none of our business. By all means, tell us the result. This discussion most certainly is our business. No it's not. MySQL is owned by MySQL AB. The fact that you get to use their code under GPL has no bearing on their commercial discussions. All these posts to this list only serve to inflame the situation. -- John Birrell - [EMAIL PROTECTED]; [EMAIL PROTECTED] - 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
best version
Im running FBSD ver 2.2.8 and need to know what version of mysql installs ans runs best on this platform. At this time upgrading in not an option. Thanks - 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: mysql.org
John Birrell wrote: On Sat, Jul 21, 2001 at 01:13:15PM +0100, Mark Tiramani wrote: Since these issues are between two private companies, please take your discussions off this list. It is none of our business. By all means, tell us the result. This discussion most certainly is our business. No it's not. MySQL is owned by MySQL AB. The fact that you get to use their code under GPL has no bearing on their commercial discussions. All these posts to this list only serve to inflame the situation. John: I beg to differ. This discussion is very relevant to all of our planning, if we use MySQL as a critical component in our Open Source application(s). Use a filter. Regards, Van -- = Linux rocks!!! http://www.dedserius.com/ = - 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: Left Join very sloooowwww..
Hi Benjamin Then I got my hands on examples which used a left join examples.. I have never tried a normal join (sigh..), but thinking of it, what is the difference between an left join and a normal join..?? With a normal join (I don't know the technical term), I mean something like (both variants are semantically equivalent, i.e. do the same) SELECT * FROM orders, productorders WHERE productorders.orderid = orders.id SELECT * FROM orders JOIN productorders WHERE productorders.orderid = orders.id Yes, I see, this is basically the apples/pears join I know and also use a lot.. Indeed you are right that you get another result because records which can't be joined will also be displayed in the result with the the 'left table' join en be left away in the normal join.. In my query this can be a problem because it is possible to add a record to productorders table with for example extra discount.. The 'ordernr' of this discount article can't be linked to the articles table.. (Though I must say that we have never done this in the past..) (But it is possible..) - eg, hard to explain, but the pco table allows us to add articles which can't be linked to the articles database.. Therefore a LEFT JOIN allways has to inspect all rows, whereas a normal join can optimize which rows to look at. I see And you are very right about this..!!! I ran your new query on the database and it was done in little more than 15 seconds!!! My old query goes on for more than a minute (more than 4 times as long..) The total query takes even longer because I also make a second query to get the total sum of all turnovers.. I need this to calculate the percentage per brand of the total turnover made... Because the query is so hard and I don't see a easy way to get the total amout of all turnover (on all brands) easily I can solve this by making a small loop in PHP I use to retrieve all records in an array and then quickly manipulate this array to count up total... (Another option I saw in the MySQL manual was to insert the results into a temporary table and then do a (quick) query on this table to get the totals.. (This has the advantage that the sum of all the turnovers will always match the total turnover you get this way in stead of doing a seperare query and having a chance that someone inserts a new order in the mean while which will influence this number..) Please note, that the key id is redundant, because the PRIMARY KEY is already a key on the column id (the same is true for the next table). What do you mean by redundant..? I think that you mean that it hasn't any effect because this column already is a primary key..? An index on perfect_customer_productorders.date could also help, if the kind of query like the one above is not too unusual for you. Yes, this makes sence (date index), but I don't really ever use these queries... It's also not really a problem that this query takes so long, but it keeps me very busy that MySQL is blazing fast on all my queries and when I do some linking the speed drops.. (But I have learned that 90% has to do with my left joining (and I know now why..) and probably I also bet that one table (pco) which contains a varchar column to link with tables with will drop the speed.. B.T.W. Whats the side effects on using a index.. What I see in the mysql manual inserts and updates get slower because the index needs to be updated... But what other reasons are there to not use indexes...? I use PHPmyAdmin a lot to place indexes and I also noticed that you can place two indexes on the same column..?? Also does it have any use to place an index when a column is unique..? (eg: I make all my auto-increment colums unique..) PS: Your query rewritten to use normal joins (and with use of table aliases to make it shorter). --- snip snip snip The query is great, I am used that MySQL takes more than 1 minute to do an query.. But if you thing what he is doing it's really stiill is very fast... Just to end of this email I have also made a query which (almost) gets the same results in about 4 seconds.. :-) SELECT cpo.brandname, Sum((cpo.quantity)*(cpo.price)) AS omzet FROMperfect_customer_productorders AS cpo, perfect_customer_orders AS co WHERE co.id = cpo.orderid AND cpo.date = DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 30 DAY ) , '%Y-%m-%d' ) AND cpo.date = DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 0 DAY ) , '%Y-%m-%d' ) AND co.status != 2 GROUP BY cpo.brandname ORDER BY omzet asc Basically I drop the linking to my articles table, products table and brand table.. All the linking to articles and then to products and then to brand is basically done for 1 thing... And that is to have the option to group by brandname!!... How can I do this..??? Well, in the productorders database I *also* store the brandname of the articles + some other values like the productname/articlename..) Why do I do this..?
Re: Is last_insert_id reliable?
Thanks to you and all the others who helped me out with this. I *am* using MyISAM tables, and everything is working the way I want it to. This is every bit as fast as the last_insert_id, with the added benefit that it is accurate no matter which thread I hit 8-). Yes, I am using pconnects, and my apache has spawned about 7 children, so there's that angle working against last_insert as well. I'm so impressed with the speed of MySQL. And my memory problems seem to have evaporated since I tightened up my SELECT statements and got rid of the table.* requests. Unbelievable performance on a PowerMac 9500 (180 MHz 604e). Walter On 7/20/01 9:24 PM, Joshua J. Kugler [EMAIL PROTECTED] wrote: Yes, you are correct in assuming that, but ONLY if you use MyISAM tables. BDB and ISAM tables reuse number, kind of. Ex: 1 2 3 4 5 If you delete 5, the next record you insert will have an ID of 5. If you delete 4 instead of 5, the next one inserted will have an ID of 6. So, BDB and ISAM tables do kind of a MAX(id)+1, whereas MyISAM tables actually keep track of the last used auto increment number. Hope that helps! j- k- On Friday 20 July 2001 17:11, you wrote: That I'm trying to do here is get the VALUE of hit_id from the last row of hits. It is an auto-increment number, but it is much higher than the count of the rows, because people go through and delete their hits from time to time. Am I correct in assuming that auto-numbers are never re-used? Walter On 7/20/01 7:06 PM, Joshua J. Kugler [EMAIL PROTECTED] wrote: Read the manual again on last_insert_id(). That function only gives you the unique id of the last insert *for that thread* If you connect, don't do any inserts, and select last_insert_id, there is no guarentee what you will get. If you are trying to find the number of rows, it would be much easier to do this: SELECT COUNT(*) AS pixels FROM hits Try that. From the looks of what you are doing, that will be accurate every time. COUNT(*) knows how many rows there are in a table. last_insert_id() has nothing to do with how many rows are in a table. If you are going to delete old rows one day, then you might want to do this: SELECT MAX(id) as pixels FROM hits Hope that helps. On Friday 20 July 2001 13:31, Walter Lee Davis wrote: Currently, I have been counting the rows in PHP in order to get the value of the last hit_id in the database like this: $sql4 = SELECT hits.hit_id from hits; $result = mysql_query ($sql4, $connection) or die(error # . mysql_errno() . : . mysql_error()); $last = mysql_num_rows($result) - 1; $go = mysql_data_seek($result, $last); $row = mysql_fetch_object($result); $pixels = number_format($row-hit_id); Which is really expensive on my very tall hits table. I hit upon doing it in MySQL, which is much, much faster: $sql4 = SELECT hits.hit_id as pixels from hits WHERE hit_id=LAST_INSERT_ID();; $result = mysql_query ($sql4, $connection) or die(error # . mysql_errno() . : . mysql_error()); $row = mysql_fetch_object($result); $pixels = number_format($row-pixels); It's staggering the difference in speed. But if I sit there and hit refresh on the browser, I get wildly different values for $pixels. It jumps around the actual number by +-10 or 12. The first method is precisely the same every time (unless a hit is recorded in the interim). Am I doing something wrong here? Can I not count on MySQL to know how many rows it has recorded in a table? Any idea why that select statement would select a different row each time? - 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
AW: How to create table in msql DB through cgi script
What about installing and using phpMyAdmin?? http://phpmyadmin.sourceforge.net/ -Ursprungliche Nachricht- Von: Joseph Maxwell [mailto:[EMAIL PROTECTED]] Gesendet: Samstag, 21. Juli 2001 12:58 An: [EMAIL PROTECTED] Betreff: How to create table in msql DB through cgi script Hello, I have a database set up on my web site at my ISP server. Connection to the DB can only be made from the localhost. So I have to do everything through CGI. They have the perl DBI and php3 that I can use to access the database. They provided a script to access the DBI which I modified to create 'TABLES' to the DB, however it did not work. The script and modified lines (indicated by a '#' at the end of the lines) are provided below. If anyone would tell me what is amiss in the coding, and how to correct it, I'll appreciate it. Thanks. -- Joe -- Script == ___ #!/usr/local/bin/perl ## # # # mysql_connect.pl # # uses the perl DBI module # # replace database name user name and password with the correct values # ## # # # set output to unbuffered and print the correct content-type # $|=1; print Content-Type: text/html\n\n; # # we need to use the DBI module # use DBI; # # replace database name user name and password with the correct values # set up the variables to connect to the database # $user='alchemy'; $passwd=''; $dbname='URW'; $dsn = DBI:mysql:$dbname; # # this establishes the connection to the database and lets us know if it worked # print Attempting to connect to the database...BR; $dbh = DBI-connect($dsn, $user, $passwd); if ( !defined $dbh) { print Fatal Error!BRCould not connect to database.BR; print Database: $dbnameBR; print User: $userBRHR; exit 1; } else { print Successful connection to database: B$dsn/BBR\n; } # # generate our SQL commmand # any SQL command can go here (select, create, alter, etc) # replace table with correct value # $command =CREATE TABLE test_0 (ID INTEGER(6), name VARCHAR(20), address1 VARCHAR(40), address2 VARCHAR(40), city VARCHAR(25), state VARCHAR(16), postalcode VARCHAR(12), country VARCHAR(30); # print Command: $commandp\n; # $command =CREATE TABLE test_1 (ID INTEGER(6), sex CHAR(1), birth date, weight SMALLINT(3), remarks VARCHAR(255); # print Command: $commandp\n; # $command =SHOW TABLES; # print Command: $commandp\n; # $command =DESCRIBE test_0; # print Command: $commandp\n; # $command =select * from test_1; # print Command: $commandp\n; # # # create a statement handle and prepare to execute the SQL statement # $sth = $dbh-prepare($command); if ( !defined $sth ) { print Can't create Statement Handle Object $dbh-errstrBR\n; exit 1; } # # execute the command # $rv = $sth-execute; print Return Code: B$rv/B.$dbh-errstr. BRHR\n; exit; -- - Returned message in Netscape Attempting to connect to the database... Successful connection to database: DBI:mysql:URW Command: CREATE TABLE test_0 (ID INTEGER(6), name VARCHAR(20), address1 VARCHAR(40), address2 VARCHAR(40), city VARCHAR(25), state VARCHAR(16), postalcode VARCHAR(12), country VARCHAR(30) Command: CREATE TABLE test_1 (ID INTEGER(6), sex CHAR(1), birth date, weight SMALLINT(3), remarks VARCHAR(255) Command: SHOW TABLES Command: DESCRIBE test_0 Command: select * from test_1 Return Code: Table 'URW'.test_1' doesn't exist - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HELP How do I get SQL server 7.0+ mysql interface
Please help 1. I have a table called as bookmaster in sql server 7.0 (NT) with vb as the frontend situated locally ( DELHI ). Any updations ( insert mostly ) on this table should have a corresponding change incorporated in mysql (Linux) which is in US. This is required since we do not want to input data again into bookmaster in mysql. We are using mysql for accessing data via a portal site 2. If there is not direct interface then can you suggest any utility that is available or that can be developed which would do a bulk copy at the end of the day. I.e I do a bcp of bookmaster from sql7.0 and then transfer the same into mysql via some utility, basis your suggestion Balaji
Re: Using InnoDb table type - Urgent help required
At 16:27 Uhr -0700 20.7.2001, Rashmi Mittal wrote: innodb_data_file_path=ibdata1:200M innodb_data_home_dir=f:/mysql/innodb/ibdata innodb_log_group_home_dir=f:/mysql/innodb/iblogs The directories specified in the above variables exist. However when I try to run the service I get the error Could not start the MySQL service on Local Computer... Error 1067: The process terminated unexpectedly. No error is logged in the mysql.err file in the data directory. It seems you are missing a innodb_log_arch_dir directive. I remember innodb complaining about this not having the same value as innodb_log_group_home_dir. Anyway, you could probably try to start the daemon from the dos prompt directly, so you see error messages - at least that's what I do on linux. (Currently I start it through a perl script with the following options (strip the unix_file_flush stuff of course): exec qw{ /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/pflanze.pid --skip-locking --innodb_data_home_dir=/usr/local/mysql/ --innodb_data_file_path=ibdata/ibdata1:1000M --set-variable=innodb_mirrored_log_groups=1 --innodb_log_group_home_dir=/usr/local/mysql/iblog/ --set-variable=innodb_log_files_in_group=3 --set-variable=innodb_log_file_size=50M --set-variable=innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=1 --innodb_log_arch_dir=/usr/local/mysql/iblog/ --innodb_log_archive=0 --set-variable=innodb_buffer_pool_size=90M --set-variable=innodb_additional_mem_pool_size=5M --set-variable=innodb_file_io_threads=4 --set-variable=innodb_lock_wait_timeout=50 --innodb_unix_file_flush_method=O_DSYNC }; ) christian. - 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
Redhat rpm of latest mysql server?
All, It looks to me like the latest rpm available from Redhat is 3.23.36 (mysql-server-3.23.36-1). I have it installed on my RedHat 7.1 system but it appears to have a replication bug of some sort. This bug is reportedly fixed in later versions. A rpm with a more recent version is available from MySQL (MySQL-3.23.40-1) but the difference in the names causes the package manager grief with it starts checking dependencies. Is there some way to get this to work? TIA, Emery - 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
syntax problem
Hi I have compiled a report using Mascon only to find I can't export it to any other software I have! Great report but not much good to me as it is :-( (I'm not well versed in php yet, otherwise this might be an issue) However, I did get the sql query script and thought I could run it in Mascon itself and export the data table to excel which would do for now. However, this syntax appears to produce exactly the query I want in the report tool but doesn't appear to work when cut and past into the sql query option in mascon. Can you tell me where the query is wrong please? Im only just learning sql and it looks ok to me! *** select handicap_results.date, handicap_results.victor, handicaps.icq_nick, handicaps.provisional, handicaps.handicaps, from handicap_results handicap_results, inner join handicaps handicaps on (handicaps.icq_nick = handicap_results.victor), group by handicap_results.victor, handicaps.handicap, handicaps.provisional, handicap_results.date, Thanks very much in anticipation! Sara
Re: Left Join very sloooowwww..
Hi David! On Sat, Jul 21, 2001 at 04:08:51PM +0200, [EMAIL PROTECTED] wrote: [...] SELECT * FROM orders, productorders WHERE productorders.orderid = orders.id SELECT * FROM orders JOIN productorders WHERE productorders.orderid = orders.id Yes, I see, this is basically the apples/pears join I know and also use a lot.. Yes. [...] In my query this can be a problem because it is possible to add a record to productorders table with for example extra discount.. The 'ordernr' of this discount article can't be linked to the articles table.. (Though I must say that we have never done this in the past..) Well, either add LEFT JOINs (but only after the join with the article tables)... (But it is possible..) - eg, hard to explain, but the pco table allows us to add articles which can't be linked to the articles database.. ... or maybe you can just insert an appropriate dummy article? [...] I can solve this by making a small loop in PHP I use to retrieve all records in an array and then quickly manipulate this array to count up total... If speed is a concern, this sounds like the best solution to me. [...] Please note, that the key id is redundant, because the PRIMARY KEY is already a key on the column id (the same is true for the next table). What do you mean by redundant..? superfluous, unnecessary I think that you mean that it hasn't any effect because this column already is a primary key..? Yes, it will be never used. [...] I also bet that one table (pco) which contains a varchar column to link with tables with will drop the speed.. Correct. B.T.W. Whats the side effects on using a index.. What I see in the mysql manual inserts and updates get slower because the index needs to be updated... But what other reasons are there to not use indexes...? Filesize and insertion/update speed are the main reasons. Another is to only use what you really need, i.e. creating indexes which you are not sure about using at all, should be avoided. I use PHPmyAdmin a lot to place indexes and I also noticed that you can place two indexes on the same column..?? To place two keys on the same column may be possible but doesn't help anything. There is one exception, though, there are compound keys, which will be of use. So, INDEX(id), INDEX(id) makes no sense, but IDNEX(id), INDEX(id,ordernr) may make sense. Btw, INDEX(id) and KEY(id) are synonyms within MySQL and are the common keys. A value may appear several times. UNIQUE(id) or UNIQUE KEY(id) are like INDEX(id) with an additional constraint, nameley that each value may only appear once. A PRIMARY KEY is like UNIQUE with the additional constraint, that it may not contain NULL values and that there may be only one PRIMARY KEY per table. (Additionally, primary keys have a special meaning in database design). Also does it have any use to place an index when a column is unique..? (eg: I make all my auto-increment colums unique..) It depends. UNIQUE(id) is a mean to assure that 'id' has no duplicates, if you need the database to ensure this constraint. If you want a key on 'id' and you know that each value may only appear once, I know of no reason not to use UNIQUE(id) instead of KEY(id). If both does not apply, i.e. you don't need a key on that column and you don't need the database to enforce the uniqueness, it makes no sense to create a unique key on the column. [...] Just to end of this email I have also made a query which (almost) gets the same results in about 4 seconds.. :-) Could you post an EXPLAIN for it? I am curious to see it. SELECT cpo.brandname, Sum((cpo.quantity)*(cpo.price)) AS omzet FROMperfect_customer_productorders AS cpo, perfect_customer_orders AS co WHERE co.id = cpo.orderid AND cpo.date = DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 30 DAY ) , '%Y-%m-%d' ) AND cpo.date = DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 0 DAY ) , '%Y-%m-%d' ) AND co.status != 2 GROUP BY cpo.brandname ORDER BY omzet asc [...] Why do I do this..? Well, when I ever need to change a brandname because the company changes his name, or maybe I need to change the name of a product for some strange reason I always want to be able to reconstruct the original bill made for the order.. I can only do this by storing the brandname etc when the order is made.. The probably are other ways to do this... Your solution looks fine. One could consider to only store an id to a table with brandnames, else, I would do it the same way. Bye, Benjamin. - 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: Using InnoDb table type - Urgent help required
Thanks a lot for your help Christian. I tried running the server from the DOS prompt and it complained about the innodb_log_arch_dir variable. It is running fine now. Rashmi -Original Message- From: Christian Jaeger [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 21, 2001 11:32 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Using InnoDb table type - Urgent help required At 16:27 Uhr -0700 20.7.2001, Rashmi Mittal wrote: innodb_data_file_path=ibdata1:200M innodb_data_home_dir=f:/mysql/innodb/ibdata innodb_log_group_home_dir=f:/mysql/innodb/iblogs The directories specified in the above variables exist. However when I try to run the service I get the error Could not start the MySQL service on Local Computer... Error 1067: The process terminated unexpectedly. No error is logged in the mysql.err file in the data directory. It seems you are missing a innodb_log_arch_dir directive. I remember innodb complaining about this not having the same value as innodb_log_group_home_dir. Anyway, you could probably try to start the daemon from the dos prompt directly, so you see error messages - at least that's what I do on linux. (Currently I start it through a perl script with the following options (strip the unix_file_flush stuff of course): exec qw{ /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/pflanze.pid --skip-locking --innodb_data_home_dir=/usr/local/mysql/ --innodb_data_file_path=ibdata/ibdata1:1000M --set-variable=innodb_mirrored_log_groups=1 --innodb_log_group_home_dir=/usr/local/mysql/iblog/ --set-variable=innodb_log_files_in_group=3 --set-variable=innodb_log_file_size=50M --set-variable=innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=1 --innodb_log_arch_dir=/usr/local/mysql/iblog/ --innodb_log_archive=0 --set-variable=innodb_buffer_pool_size=90M --set-variable=innodb_additional_mem_pool_size=5M --set-variable=innodb_file_io_threads=4 --set-variable=innodb_lock_wait_timeout=50 --innodb_unix_file_flush_method=O_DSYNC }; ) christian. - 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: Another Newbie Question
Bob Rea schrieb am Samstag, 21. Juli 2001, 19:14:34: On Friday 20 July 2001 10:46 pm, you wrote: You may want to understand what distinct and avg means. The combination doesn't make sense. I took the example in question out of a book that I am using to teach myself SQL. So I don't quite understand what it is after. It says, The following example uses the AVG() function to return the average product price offered by a specific vendorhere the DISTINCT argument is used so that the average only takes into account unique prices Does this make sense at all? in MySQL? Well, it came to me after my reply that this might be the meaning of the query. Actually, I don't know if this is standard SLQ and MySQL will support it or not. The error messages indicates it does not, but I may be wrong. Anyway, according to the example, it is clear that the average of all prices may differ substantially from the average of all distinct prices. If the latter does make sense economically is another question. The usual prescription for things not implemented is to look for a work around. In this case, it would be very easy to create a temporary table (could be a memory table which is very fast but has some restrictions), write all distinct values to that table and take an average from those. Thanks for helping a learner. You are welcome. Bob Rea schrieb am Samstag, 21. Juli 2001, 02:36:11: The SQL book I am using has this: mysql select avg(distinct prod_price) as avg_price - from Products - where vend_id = 'dll01' - ; and responds thus: ERROR 1064: You have an error in your SQL syntax near 'distinct prod_price) as avg_price from Products where vend_id = 'dll01'' at line 1 How do I do this, if I can? -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - 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: Left Join very sloooowwww..
On 21-Jul-2001 Benjamin Pflugmann wrote: Hi David! snip good discussion on optimizing joins Filesize and insertion/update speed are the main reasons. Another is to only use what you really need, i.e. creating indexes which you are not sure about using at all, should be avoided. I use PHPmyAdmin a lot to place indexes and I also noticed that you can place two indexes on the same column..?? To place two keys on the same column may be possible but doesn't help anything. There is one exception, though, there are compound keys, which will be of use. So, INDEX(id), INDEX(id) makes no sense, but IDNEX(id), INDEX(id,ordernr) may make sense. Benjamin, I'll pick a nit here. A query on id only can use either index, a query on id ordernr could also use both indexes but the second one would be more useful. A query on ordernr only can't use either, and will result in a full scan. ITYM: INDEX(id), INDEX(ordernr,id) Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - 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: mysql.org
On Fri, Jul 20, 2001 at 12:29:52PM -0400, Britt Johnston wrote: MySQL AB and NuSphere had a meeting over the phone, we exchanged information and opinions and NuSphere will propose times for the next meeting. Britt... Britt, thanks for the update. The neutral tone and lack of result make it sound like you had a frank exchange of views. Since MySQL AB initiated posting events here as they occur, and few people complained about it, I see no reason why either side shouldn't continue. -- Bob Hall mysql list incantation: sql table database query - 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: mysql.org
On Fri, Jul 20, 2001 at 12:29:52PM -0400, Britt Johnston wrote: MySQL AB and NuSphere had a meeting over the phone, we exchanged information and opinions and NuSphere will propose times for the next meeting. Britt... Britt, thanks for the update. The neutral tone and lack of substantial result make it sound like you had a frank exchange of views. Since MySQL AB initiated posting events here as they occur, and few people complained about it, I see no reason why either side shouldn't continue, unless we plan to ban all discussion of the matter. -- Bob Hall mysql list incantation: sql table database query - 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: Upgrade MySQL
I have one other question, does it make a difference where you put the tar.gz file on the server before you untar it for the install?? Does this have a bearing on the installation, when you using the binary install?? On Thu, 19 Jul 2001 15:50:13 -0700, Jeremy Zawodny [EMAIL PROTECTED] wrote: The only databases currently are the default 'test' and the 'mysql' db's no other db's created. The current variables show: basedir/ datadir /var/lib/mysql My question is, when upgrading and to replace the current version, I assume I would need to install using the paths,..correct?? You can. Or you can install anywehere else and just copy the data files from /var/lib/mysql to the new data dir when your're done. Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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
Win2k
I am using Win2k Advanced Server and Professional. Both systems have a problem with the current version of MySQL GUI when you successfully type in the correct password, of erroring and exiting. If you type in the incorrect password, it lets you into the application and just shows the access denied to database error at the bottom. If you hit connect again, and type in the correct password,it errors out. Steve
Re: Upgrade MySQL
On Sat, Jul 21, 2001 at 04:21:35PM -0500, MikemickaloBlezien wrote: I have one other question, does it make a difference where you put the tar.gz file on the server before you untar it for the install?? Does this have a bearing on the installation, when you using the binary install?? It doesn't matter, no. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 35 days, processed 271,979,567 queries (88/sec. avg) - 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: Upgrade MySQL
? - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: MikemickaloBlezien [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, July 22, 2001 12:31 AM Subject: Re: Upgrade MySQL On Sat, Jul 21, 2001 at 04:21:35PM -0500, MikemickaloBlezien wrote: I have one other question, does it make a difference where you put the tar.gz file on the server before you untar it for the install?? Does this have a bearing on the installation, when you using the binary install?? It doesn't matter, no. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 35 days, processed 271,979,567 queries (88/sec. avg) - 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: Left Join very sloooowwww..
Hi Don. On Sat, Jul 21, 2001 at 03:28:45PM -0500, [EMAIL PROTECTED] wrote: [...] There is one exception, though, there are compound keys, which will be of use. So, INDEX(id), INDEX(id) makes no sense, but IDNEX(id), INDEX(id,ordernr) may make sense. Benjamin, I'll pick a nit here. A query on id only can use either index, a query on id ordernr could also use both indexes but the second one would be more useful. A query on ordernr only can't use either, and will result in a full scan. ITYM: INDEX(id), INDEX(ordernr,id) Of course, you are right. I was sloppy in my example. Best regards, Benjamin. - 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: Left Join very sloooowwww..
Hi Benjamin, Just a quick gif picture of the explain of the other query I made.. (It's quite late now and I will read your email tomorrow again..) Could you post an EXPLAIN for it? I am curious to see it. SELECT cpo.brandname, Sum((cpo.quantity)*(cpo.price)) AS omzet FROMperfect_customer_productorders AS cpo, perfect_customer_orders AS co WHERE co.id = cpo.orderid AND cpo.date = DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 30 DAY ) , '%Y-%m-%d' ) AND cpo.date = DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 0 DAY ) , '%Y-%m-%d' ) AND co.status != 2 GROUP BY cpo.brandname ORDER BY omzet asc This is the explain: table type possible_keys key key_len ref rows Extra Edit Delete co ALL PRIMARY,id2596 where used Edit Delete cpo ALL 4431 where used Bye Bye David - Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: David Bouw [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, July 21, 2001 9:37 PM Subject: Re: Left Join very sl.. Hi David! On Sat, Jul 21, 2001 at 04:08:51PM +0200, [EMAIL PROTECTED] wrote: [...] SELECT * FROM orders, productorders WHERE productorders.orderid = orders.id SELECT * FROM orders JOIN productorders WHERE productorders.orderid = orders.id Yes, I see, this is basically the apples/pears join I know and also use a lot.. Yes. [...] In my query this can be a problem because it is possible to add a record to productorders table with for example extra discount.. The 'ordernr' of this discount article can't be linked to the articles table.. (Though I must say that we have never done this in the past..) Well, either add LEFT JOINs (but only after the join with the article tables)... (But it is possible..) - eg, hard to explain, but the pco table allows us to add articles which can't be linked to the articles database.. ... or maybe you can just insert an appropriate dummy article? [...] I can solve this by making a small loop in PHP I use to retrieve all records in an array and then quickly manipulate this array to count up total... If speed is a concern, this sounds like the best solution to me. [...] Please note, that the key id is redundant, because the PRIMARY KEY is already a key on the column id (the same is true for the next table). What do you mean by redundant..? superfluous, unnecessary I think that you mean that it hasn't any effect because this column already is a primary key..? Yes, it will be never used. [...] I also bet that one table (pco) which contains a varchar column to link with tables with will drop the speed.. Correct. B.T.W. Whats the side effects on using a index.. What I see in the mysql manual inserts and updates get slower because the index needs to be updated... But what other reasons are there to not use indexes...? Filesize and insertion/update speed are the main reasons. Another is to only use what you really need, i.e. creating indexes which you are not sure about using at all, should be avoided. I use PHPmyAdmin a lot to place indexes and I also noticed that you can place two indexes on the same column..?? To place two keys on the same column may be possible but doesn't help anything. There is one exception, though, there are compound keys, which will be of use. So, INDEX(id), INDEX(id) makes no sense, but IDNEX(id), INDEX(id,ordernr) may make sense. Btw, INDEX(id) and KEY(id) are synonyms within MySQL and are the common keys. A value may appear several times. UNIQUE(id) or UNIQUE KEY(id) are like INDEX(id) with an additional constraint, nameley that each value may only appear once. A PRIMARY KEY is like UNIQUE with the additional constraint, that it may not contain NULL values and that there may be only one PRIMARY KEY per table. (Additionally, primary keys have a special meaning in database design). Also does it have any use to place an index when a column is unique..? (eg: I make all my auto-increment colums unique..) It depends. UNIQUE(id) is a mean to assure that 'id' has no duplicates, if you need the database to ensure this constraint. If you want a key on 'id' and you know that each value may only appear once, I know of no reason not to use UNIQUE(id) instead of KEY(id). If both does not apply, i.e. you don't need a key on that column and you don't need the database to enforce the uniqueness, it makes no sense to create a unique key on the column. [...] Just to end of this email I have also made a query which (almost) gets the same results in about 4 seconds.. :-) Could you post an EXPLAIN for it? I am curious to see it. SELECT cpo.brandname, Sum((cpo.quantity)*(cpo.price)) AS omzet FROMperfect_customer_productorders AS cpo, perfect_customer_orders AS co WHERE
FW: MySQL JDBC
-Original Message- From: shane [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 21, 2001 10:49 AM To: [EMAIL PROTECTED] Subject: RE: MySQL JDBC yes. thks actually all i did was to do a: GRANT ALL ON DB.* TO user@localhost IDENTIFIED BY pwd -Original Message- From: Venu Anuganti [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 21, 2001 12:39 AM To: shane Subject: RE: MySQL JDBC does this solved your problem ? :-Venu -Original Message- From: shane [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 19, 2001 11:16 PM To: [EMAIL PROTECTED] Subject: RE: MySQL JDBC thks a million :) shane -Original Message- From: Venu Anuganti [mailto:[EMAIL PROTECTED]] Sent: Friday, July 20, 2001 2:07 PM To: shane; Ricardo Striquer Soares Cc: [EMAIL PROTECTED] Subject: RE: MySQL JDBC Here is the one...it looks lists.mysql.com is filtering the attachments. -- import java.sql.*; public class TestMySQL { public static void main(String[] Args) { try { // The newInstance() call is a work around for some // broken Java implementations Class.forName(org.gjt.mm.mysql.Driver).newInstance(); } catch (Exception E) { System.err.println(Unable to load driver.); E.printStackTrace(); } try { /* Connection Management */ //Here test is the database name //vanuganti--UID //venu--PWD Connection conn = DriverManager.getConnection(jdbc:mysql://localhost:3306/test,va nuganti, venu); conn.setAutoCommit(true); /* Create stmt and process some basic SQL statements */ Statement stmt = conn.createStatement(); stmt.execute(drop table test_mysql); stmt.execute(create table test_mysql(col1 int, col2 varchar(25))); stmt.execute(insert into test_mysql values(100,'mysql')); stmt.execute(insert into test_mysql values(200,'myjdbc')); /* Result set */ ResultSet rs = stmt.executeQuery(SELECT * FROM test_mysql); while (rs.next()) { System.out.println(COL1:+rs.getInt(1)); System.out.println(COL2:+rs.getString(2)); } // Clean up after ourselves rs.close(); stmt.close(); conn.close(); } catch (SQLException E) { System.out.println(SQLException: + E.getMessage()); System.out.println(SQLState: + E.getSQLState()); System.out.println(VendorError: + E.getErrorCode()); } } } -- -Original Message- From: shane [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 19, 2001 10:15 PM To: Venu Anuganti; Ricardo Striquer Soares; shane Cc: [EMAIL PROTECTED] Subject: RE: MySQL JDBC think the attachment is wrong. the only thing i get is a text file containing 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 could you pls send us the samples again, thks shane -Original Message- From: Venu Anuganti [mailto:[EMAIL PROTECTED]] Sent: Friday, July 20, 2001 11:45 AM To: Ricardo Striquer Soares; shane Cc: [EMAIL PROTECTED] Subject: Re: MySQL JDBC Hi Ricardo / Shane If you are using mmmysql JDBC Driver, please find the attaches simple JDBC sample that can be used with this driver to connect to MySQL server and do some basic operations. Before compiling the sample, make sure you set the CLASSPATH variable to mmmysql JDBC driver installed/achieved directory. Hope this helps you. If you have any questions or feedback let us know. Thank you Venu Anuganti - 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: mysql.org
Too bad it's not your list to make that decision, but Monteys Ruben mysql - the database written and owned by MYSQL AB On Sat, Jul 21, 2001 at 01:13:15PM +0100, Mark Tiramani wrote: Since these issues are between two private companies, please take your discussions off this list. It is none of our business. By all means, tell us the result. This discussion most certainly is our business. No it's not. MySQL is owned by MySQL AB. The fact that you get to use their code under GPL has no bearing on their commercial discussions. All these posts to this list only serve to inflame the situation. -- John Birrell - [EMAIL PROTECTED]; [EMAIL PROTECTED] - 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