Locked out of mySQL.
Hello, I'm trying to learn mysql PHP. I recently set up the mysql server (with the file: mysql-3.23.32-pc-linux-gnu-i686.tar.gz) My problem is this: I either forgot the password, or neglected to set up any user accounts. The documentation says to do this: ./bin/mysqladmin -u root -p password 'new-password' ./bin/mysqladmin -u root -h twistedlogic -p password 'new-password' which returns the following: Enter password: ./bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' I also tried renaming the mysql directory (in /usr/local/) and re-installing it. no. maybe I did something wrong there, because that seems to me it should have worked, being a binary package... and the mysql table being held in mysql/data/mysql. anyway, if anyone knows how I can get back in tell me! :) thanks, AJ. - 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: Locked out of mySQL.
In the manual First off, have you tried the following ... 6.15 Causes of Access denied Errors If you encounter Access denied errors when you try to connect to the MySQL server, the list below indicates some courses of action you can take to correct the problem: After installing MySQL, did you run the mysql_install_db script to set up the initial grant table contents? If not, do so. See section 6.12 Setting Up the Initial MySQL Privileges. Test the initial privileges by executing this command: shell mysql -u root test The server should let you connect without error. You should also make sure you have a file `user.MYD' in the MySQL database directory. Ordinarily, this is `PATH/var/mysql/user.MYD', where PATH is the pathname to the MySQL installation root. After a fresh installation, you should connect to the server and set up your users and their access permissions: shell mysql -u root mysql The server should let you connect because the MySQL root user has no password initially. That is also a security risk, so setting the root password is something you should do while you're setting up your other MySQL users. If you try to connect as root and get this error: Access denied for user: '@unknown' to database mysql this means that you don't have an entry in the user table with a User column value of 'root' and that mysqld cannot resolve the hostname for your client. In this case, you must restart the server with the --skip-grant-tables option and edit your `/etc/hosts' or `\windows\hosts' file to add an entry for your host. If so, then do this ... 20.11 How to reset a forgotten password. If you have forgotten the root user password for MySQL, you can restore it with the following procedure. Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a .pid file which is normally in the MySQL database directory: kill `cat /mysql-data-directory/hostname.pid` You must be either the Unix root user or the same user the server runs as to do this. Restart mysqld with the --skip-grant-tables option. Connect to the mysqld server with mysql -h hostname mysql and change the password with a GRANT command. See section 7.34 GRANT and REVOKE syntax. You can also do this with mysqladmin -h hostname -u user password 'new password' Load the privilege tables with: mysqladmin -h hostname flush-privileges or with the SQL command FLUSH PRIVILEGES. Note that after you started mysqld with --skip-grant-tables, any usage of GRANT commands will give you an Unknown command error until you have executed FLUSH PRIVILEGES. -Original Message- From: AJ Hawks [mailto:[EMAIL PROTECTED]] Sent: Saturday, 27 January 2001 19:01 To: mySQL Subject: Locked out of mySQL. Hello, I'm trying to learn mysql PHP. I recently set up the mysql server (with the file: mysql-3.23.32-pc-linux-gnu-i686.tar.gz) My problem is this: I either forgot the password, or neglected to set up any user accounts. The documentation says to do this: ./bin/mysqladmin -u root -p password 'new-password' ./bin/mysqladmin -u root -h twistedlogic -p password 'new-password' which returns the following: Enter password: ./bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' I also tried renaming the mysql directory (in /usr/local/) and re-installing it. no. maybe I did something wrong there, because that seems to me it should have worked, being a binary package... and the mysql table being held in mysql/data/mysql. anyway, if anyone knows how I can get back in tell me! :) thanks, AJ. - 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: 3.23.32 + FreeBSD 4.2 /w Lastest Snapshots - Still CoreDump
Artem Koutchine writes: Could you point me in the direction where i coudl find that "Ken Manzel's info on FreeBSD 4.* patches." I am having the same problem with 3.23.32 on 4.2-stable (dies sporadically with sig 11). Regards, Artem You can look at the archives of [EMAIL PROTECTED] list and search for Ken Menzel messages. He has sent several mails on the subject quite recently. Regards, Sinisa __ _ _ ___ == MySQL AB /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| /*/^^^\*\^^^ /*/ \*\Developers Team - 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
disk free space
I really miss a function to retrieve the free disk space on a MySQL server. Previously this was not a problem since a ran PHP and MySQL on the same server, but for performance reasons i split them. However, there seems no function to get the free disk space automatically via MySQL, so a 'intelligent' application or script cannot decide wether or not to store data depending on free file space (note: this may be huge amounts of data so it is quite sensefull). Only workaround I though of is installing PHP on the mysql server as well :((( Anybody knows a nice workaround?
Re: 3.23.31 gives FULLTEXT problems on sparc64 Linux
Hi! Sorry for delay :-( On Jan 24, Tibor Simko wrote: Hello I wrote: After upgrading to 3.23.31, FULLTEXT selects give often "error -1 from table handler" for me. [...] Sergei Golubchik [EMAIL PROTECTED] wrote: Thanks for a bug report! This would be fixed in a nex release. Thanks. In 3.23.32 the test case I submitted apparently works better, but bad things still happen, like the following table corruption: DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id mediumint unsigned NOT NULL auto_increment, tag char(6) NOT NULL default '', value text NOT NULL default '', PRIMARY KEY (id), KEY kt(tag), KEY kv(value(15)), FULLTEXT KEY kvf(value) ) TYPE=MyISAM; INSERT INTO t1 (tag,value) VALUES ('foobar','baz'); CHECK TABLE t1; The error says: "Can't read indexpage from filepos: -1". Moreover, myisachk isn't able to repair it! (neither -r nor -o). Tested on both sparc64-linux (sun4u) and sparc-solaris2.6 (sun4m). cheers -- TS Thank you for reporting this. This will be fixed in a next release. But it was really not a big problem - the table was not really corrupted. myisamchk thought it was because there was empty index and non-empty table - something that should never happen with normal index. The bug was fixed by making myisamchk to treat such a situation as normal. 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
Re: ftp driver for mysql,
[EMAIL PROTECTED] writes: I am a customer to a web designer that I have since cut relations with.He left me high and dry and I am left to my own devices to ftp my web site to another hosting co. Is there a driver exclusively for ftp work that you can sell me; with step by step instructions of how to use it. or is there an alternative thing that you can do to help me with my delima.Needless to say every one uses the html coding and I cannot find good help. also I do not think I should hve to redesign or recode my site. You can see the mysql program that was used as a data base if you try to punch up ladykight.com lost in california USA Hi! The explanation of your problem is vary vague. Can you explain technically what exactly is your problem. Regards, Sinisa __ _ _ ___ == MySQL AB /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| /*/^^^\*\^^^ /*/ \*\Developers Team - 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: Was FULLTEXT bug fixed?
Hi! On Jan 26, Steven Roussey wrote: Hi, There was a bug in 3.23.31 (I think) with fultext indexes. Was this fixed in 3.23.32? Sincerely, Steven Roussey Network54.com http://network54.com/?pp=e Probably, yes. I do remember some bugfixing between 3.23.31 and 3.23.32 and there were no known bugs in FULLTEXT by 3.23.32 release date. Sorry for not having an appropriate line in ChangeLog. :-( 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
FreeBSD 4.2-stable + MySQL 3.23.32 sig 11 problem
Hi! I am having this weird problem that MySQL just dies with signal 11 several time per day when loads get just a bit hight (the load is very low, about 1 q/s and on some export ops it gets a bit high two time per 24 hours). Any way, FreeBSD 4.2 is not RELASE but STABLE which is cvsupped pretty often (last time was on Jan 20th). So all those problem with thread which Ken Manzel mentions are gone as well as broken compiler. I do not use port for MySQL and build it from the tar ball source like: gmake gmake install (i use gmake for a historical reason, bsd make could not build it some time ago). I just looked at the ports and saw a couple of compiler options: -felide-constructors -fno-exceptions -fno-rtti And it is said that w/o then MySQL could crash under high loads. Well, I tried to make an experiment: sh CXXFLAGS='-felide-constructors -fno-exceptions -fno-rtti' ./configure --prefix=/usr/local/mysql --with-charset=win1251 --localstatedir=/usr/local/mysql/data --with-mit-threads=no --without-debug --without-perl --without-bench gmake gmake install Now I am going to see if the problem goes away. Artem - 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
(+) Help needed
Ok I need help with this.. I have to values like $dat1="2001-01-11"; $dat2="2001-02-12"; so and i have Mysql table with IP | Size | Date| -- 213.32.44.11 | 3|2001-01-11-12| -- 213.32.44.11 | 3|2001-01-11-13| -- 213.32.44.12 | 3|2001-01-12-12| -- 213.32.44.14 | 3|2001-01-13-17| -- 213.32.44.16 | 3|2001-01-14-11| -- 213.32.44.111 | 3|2001-02-15-10| -- 213.32.44.121 | 3|2001-01-13-10| -- 213.32.44.131 | 3|2001-01-15-10| -- So i need SELECT from table only record between values $dat1 and $dat2 how to do this ?! Thx _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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
select * from table where float(3,1)=number; if number has decimals, it doesn't work.
Description: if a column is defined as float(3,1) and your sql query uses where in that particular column and the value has decimals on it then you got empty set. How-To-Repeat: ($:~) mysql stuff Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35 to server version: 3.23.32 Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql select max(tiesaailma) from tiesaa; +-+ | max(tiesaailma) | +-+ | 5.3 | +-+ 1 row in set (3.92 sec) mysql select tiesaailma,tiesaacity from tiesaa where tiesaailma=5.3; Empty set (1.13 sec) mysql select tiesaailma,tiesaacity from tiesaa where tiesaailma=3; ++---+ | tiesaailma | tiesaacity| ++---+ |3.0 | Tie 8; Pattijoki | |3.0 | Tie 8; Närpiö, Bäckliden | |3.0 | Tie 8; Närpiö, Bäckliden | |3.0 | Tie 8; Pattijoki | |3.0 | Tie 2; Pori, Kähkönen | |3.0 | Tie 8; Kristiinankaup, Lapväärtti | |3.0 | Tie 83; Ylitornio, Raanujärvi | ++---+ 7 rows in set (1.12 sec) mysql Fix: not known. Submitter-Id: submitter ID Originator:Jani Ollikainen Organization: - bestis [EMAIL PROTECTED] Jani Ollikainen http://come.to/bestis - MySQL support: none Synopsis: some implementations of getting data with max() don't work. Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.32 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.14 Distrib 3.23.32, for redhat-linux-gnu on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.32 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 3 min 21 sec Threads: 1 Questions: 60 Slow queries: 0 Opens: 23 Flush tables: 1 Open tables: 17 Queries per second avg: 0.299 Environment: System: Linux ls.sicom.fi 2.4.0 #5 Mon Jan 22 16:10:56 EET 2001 i586 unknown Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.0) Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686' CXX='c++' CXXFLAGS='-O2 -march=i386 -mcpu=i686' LDFLAGS='' LIBC: lrwxrwxrwx1 root root9 May 18 1999 /lib/libc.so.5 - libc.so.6 lrwxrwxrwx1 root root 11 Dec 9 12:45 /lib/libc.so.6 - libc-2.2.so -rwxr-xr-x1 root root 5072386 Jan 11 01:37 /lib/libc-2.2.so -rw-r--r--1 root root 24498288 Jan 11 01:35 /usr/lib/libc.a -rw-r--r--1 root root 178 Jan 11 01:35 /usr/lib/libc.so Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/usr/lib --libexecdir=/usr/libexec --localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man --infodir=/usr/share/info --without-debug --without-readline --enable-shared --with-extra-charsets=complex --without-bench --localstatedir=/var/lib/mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql --with-extra-charsets=all - 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: (+) Help needed
Don't you think this has nothing specific to MySQL? Maybe you should read some books on SQL and the manual for MySQL? SELECT * FROM tablename WHERE datefield'2001-01-11' AND datefied'2001-02-12' Don;t ask how to correctly call the query in perl, read perldoc DBI perldoc DBD::mysql Does ANYBODY read books manuals these days? Bye Artem - Original Message - From: "Alexei Sh." [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 27, 2001 4:04 PM Subject: (+) Help needed Ok I need help with this.. I have to values like $dat1="2001-01-11"; $dat2="2001-02-12"; so and i have Mysql table with IP | Size | Date| -- 213.32.44.11 | 3|2001-01-11-12| -- 213.32.44.11 | 3|2001-01-11-13| -- 213.32.44.12 | 3|2001-01-12-12| -- 213.32.44.14 | 3|2001-01-13-17| -- 213.32.44.16 | 3|2001-01-14-11| -- 213.32.44.111 | 3|2001-02-15-10| -- 213.32.44.121 | 3|2001-01-13-10| -- 213.32.44.131 | 3|2001-01-15-10| -- So i need SELECT from table only record between values $dat1 and $dat2 how to do this ?! Thx __ ___ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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
disk free space
I really miss a function to retrieve the free disk space on a MySQL server. Previously this was not a problem since a ran PHP and MySQL on the same server, but for performance reasons i split them. However, there seems no function to get the free disk space automatically via MySQL, so a 'intelligent' application or script cannot decide wether or not to store data depending on free file space (note: this may be huge amounts of data so it is quite sensefull). Only workaround I though of is installing PHP on the mysql server as well :((( Anybody knows a nice workaround?
using libmysqlclient
Thanks for your help, but i needed a litle more help, I tried to do that and it almost worked bu i still got this error gcc -O2 -g -o mysql1 mysql.c -L/usr/local/mysql/lib -lmysqlcl ient -lnsl -lgthreads -lz -lcrypt -lsock et -lm Undefined first referenced symbol in file main /usr/ccs/lib/crt1.o mysql1: fatal error: Symbol referencing errors. No output written to mysql1 collect2: ld returned 1 exit status - 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: AW: Strange behavior of MySQL 3.23.xx with GROUP BY ...
I think that is the wrong answer ... Standard-SQL and ANSI-SQL require that a column that is displayed and has no grouping function applied to it MUST also appear in the GROUP BY-clause!!! Sir, every dialect of SQL has it's non-ANSI quirks. I've never heard of Standard-SQL, but ANSI-SQL is a standard established by a standards organization and used by vendors as a basis for their own dialects. There has never been a commercial dialect of SQL that fully matched its contemporary ANSI standard. They have all deviated from it in some way. In this case, MySQL extends the standard by allowing you to include an extra column in the SELECT clause. The result: fldNAME fldMONAT sum(fldSALARY) boris 1 10200 stephan 1 6000 itself makes no sense ... none earned as much money in January (1) ... ! You didn't group by month. You grouped by name. Since you didn't use an aggregate function on fldMONAT, MySQL returns the value found in that field in the first row in the group. Since you did use an aggregate function on fldSALARY, MySQL returns the aggregate value for that group. Postgres gives an error, Oracle gives an error, SQL-Server 7 gives an error even M$-Access 2000 gives an error why doesn't MySQL? There are parts of the Oracle SQL dialect that will raise errors in other RDBMSs, but not in Oracle. There are parts of Transact-SQL that will raise errors in other RDBMSs, but not in SQL Server. There are parts of the MySQL SQL dialect that will raise errors in other RDBMSs, but not in MySQL. Literature: http://www.newi.ac.uk/walshea/db1/sql04/index.htm there: http://www.newi.ac.uk/walshea/db1/sql04/sld015.htm http://www.nku.edu/~raghavan/gby.html http://info-it.umsystem.edu/oracle/svslr/svslr.2.1089.html http://www.sk.postgresql.org/docs/user/sql-select.htm and so on ... I think ANSI-Specs should even tell you the same ... ! And don't forget http://www.mysql.com/doc/E/x/Extensions_to_ANSI.html -Ursprungliche Nachricht- Von: Bob Hall [mailto:[EMAIL PROTECTED]] Gesendet: Freitag, 26. Januar 2001 12:18 An: [EMAIL PROTECTED] Betreff: Re: Strange behavior of MySQL 3.23.xx with GROUP BY ... I found a very strange behaviour of MySQL 3.23.xx with GROUP BY clause ... I have a Table: CREATE TABLE groupbyTEST ( fldNAME varchar(20) NOT NULL, fldMONTH tinyint(4) DEFAULT '0' NOT NULL, fldSALARY decimal(10,0) DEFAULT '0' NOT NULL, fldDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (fldNAME, fldMONTH) ); with following records: INSERT INTO groupbyTEST VALUES ( 'stephan', '1', '1000', '2001-01-25 23:12:29'); INSERT INTO groupbyTEST VALUES ( 'boris', '1', '2000', '2001-01-25 23:12:45'); INSERT INTO groupbyTEST VALUES ( 'stephan', '2', '1200', '2001-01-25 23:12:56'); INSERT INTO groupbyTEST VALUES ( 'stephan', '3', '1000', '2001-01-25 23:13:08'); INSERT INTO groupbyTEST VALUES ( 'stephan', '4', '900', '2001-01-25 23:14:50'); INSERT INTO groupbyTEST VALUES ( 'boris', '2', '1200', '2001-01-25 23:15:01'); INSERT INTO groupbyTEST VALUES ( 'stephan', '5', '1900', '2001-01-25 23:15:16'); INSERT INTO groupbyTEST VALUES ( 'boris', '3', '2400', '2001-01-25 23:15:39'); INSERT INTO groupbyTEST VALUES ( 'boris', '4', '2100', '2001-01-25 23:15:50'); INSERT INTO groupbyTEST VALUES ( 'boris', '5', '2300', '2001-01-25 23:16:01'); INSERT INTO groupbyTEST VALUES ( 'boris', '6', '200', '2001-01-25 23:16:11'); A statement like: SELECT fldNAME, fldMONTH, sum(fldSALARY) FROM groupbyTEST GROUP BY fldNAME HAVING sum(fldSALARY)200 should cause an error because of the missing fldMONTH in the GROUP BY clause, but it is executed without an error and results: fldNAME fldMONAT sum(fldGEHALT) boris 1 10200 stephan 1 6000 ... makes not very much sense ... does it?? Sir, why do you think it should cause an error? The statement is valid and the result is correct. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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]
Re: Locked out of mySQL.
Hi- I think you are getting tripped up by the same thing as I was when I tried to install MySQL. When you are prompted "Enter password:", you do NOT type the new password. You simply hit enter. --Quentin Subject: Locked out of mySQL. From: "AJ Hawks" [EMAIL PROTECTED] Date: Sat, 27 Jan 2001 01:30:30 -0700 To: "mySQL" [EMAIL PROTECTED] Hello, I'm trying to learn mysql PHP. I recently set up the mysql server (with the file: mysql-3.23.32-pc-linux-gnu-i686.tar.gz) My problem is this: I either forgot the password, or neglected to set up any user accounts. The documentation says to do this: ../bin/mysqladmin -u root -p password 'new-password' ../bin/mysqladmin -u root -h twistedlogic -p password 'new-password' which returns the following: Enter password: ../bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost mailto:root@localhost' (Using password: YES)' I also tried renaming the mysql directory (in //usr/local//) and re-installing it. no. maybe I did something wrong there, because that seems to me it should have worked, being a binary package... and the mysql table being held in mysql/data/mysql. anyway, if anyone knows how I can get back in tell me! [:)] thanks, AJ.
how to delete records older than 14 days
Hello, I'm trying to figure out the correct syntax for requesting the database to remove all records older than 14 days. The mysql query that I am using (from within php4) is: //---update news so only last two weeks of news are available---// $sql2 = "delete from news where newsdate DATE_SUB(NOW(), INTERVAL 14 DAY)"; $result2 = mysql_db_query($dbname,$sql); But it doesn't seem to be working. Here is the the definition of the "news" table: mysql describe news; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(3) | | PRI | 0 | auto_increment | | hub | char(3) | YES | | NULL || | newsdate | date | YES | | NULL || | headline | varchar(100) | YES | | NULL || | article | longtext | YES | | NULL || +--+--+--+-+-++ 5 rows in set (0.05 sec) Basically this is for a news page, and items get pushed down the "list" (what the viewer sees) and then eventually are supposed to be deleted. Any suggestions on how to do this more efficiently would be welcome as well. Please email me directly (hit reply to the message). Thanks- Thomas - 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
C api functions blocks the computer
I'm resending the e-mail again so it may be clearest... First of all, before I forget it... My MySQL is: mysql Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686) I have a problem with mysql_fetch_row... When I use it the system goes to 100% CPU usage and nothing else, I get not rows... The problem seems to be inside the mysql_fetch_row function but I don't know I have just upgraded mysql to 3.23 but in the server I'm working there is an older one so I have to work with it... * THIS IS C LANGUAGE *** int main (int argc,char **argv) { MYSQL *MySQLConnect=NULL; MYSQL_RES *result;// Resultado de las operaciones . . . Row = mysql_fetch_row(result); Here is the problem j=0; while(Row!=NULL); { printf("\n\t%u %4s",j,Row); Row=mysql_fetch_row(result); And Here j++; } . . . } END C * I know result contains a valid address before calling the function, because I use it to show how many rows are available some lines before, (42 rows) but the system blocks anyway until I press Ctrl-c The program waits before j=0 and stops there; Any points on this...? Thank you Answer to my e-mail too, thanks... -- Gonzalo Aguilar. Madrid, Espaa (Spain) | Reymad Studios | [EMAIL PROTECTED] | Privado| [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: (+) Help needed
Slight correction, for some reason the query below does NOT work on my sample DB unless I quote the date values. This should work now: SELECT IP, Size, Date FROM your_table WHERE Date BETWEEN '2001-02-01' AND '2001-05-01'; so and i have Mysql table with IP | Size | Date| -- 213.32.44.11 | 3|2001-01-11-12| -- 213.32.44.11 | 3|2001-01-11-13| -- 213.32.44.12 | 3|2001-01-12-12| -- 213.32.44.14 | 3|2001-01-13-17| -- 213.32.44.16 | 3|2001-01-14-11| -- 213.32.44.111 | 3|2001-02-15-10| -- 213.32.44.121 | 3|2001-01-13-10| -- 213.32.44.131 | 3|2001-01-15-10| -- So i need SELECT from table only record between values $dat1 and $dat2 how to do this ?! Thx _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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
strip
Can someone tell me how i can get(or can someone send me) the strip command for SCO Open Server 5.0.5? - 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 Bug ? (Can't Connect to Mysql Server on 192.9.200.2 (10055))
From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Mysql Bug ? (Can't Connect to Mysql Server on 192.9.200.2 (10055)) Description: I am connecting from Visual Basic 6 to Mysql via ODBC DSN. When i made 94 sleeped connection after 94 can't connect to a server. It says : [TCX][MYODBC]Can't Connect to Mysql Server on 192.9.200.2 (10055) Is it normal. If there a maximum connection limit for process ? I think it is abnormal cause everyone is talking about 10.000 Web Hit. Is it cause from sleeped process list ? I increased maximum connection to 200 but it not works. I think it is not related with maximum connection . It is related with maximum process number. How-To-Repeat: Run this code on Microsoft Visual Basic 6 ENT Edition. For i = 1 To 200 Set Temp_Bag(i) = New ADODB.Connection Temp_Bag(i).Open "DSN=Avukatpro;UID=avukatpro;PWD=avukatpro" Next i It hangs 94 on Winnt. It hangs 60 on Win 95. It hangs 50 on Win98 SE. Fix: Any offer? What means 10055 ? I check-out documents. Can't see any recomend or definition for this error number. Additional Information : Host: 3.23.30-gamma ClientInfo: 3.23.6-alpha HostInfo: 192.9.200.2 via TCP/IP ProtoInfo: 10 I stopped on 93 process and got process list. It is attached. Synopsis: synopsis of the problem (one line) Submitter-Id: Yusuf Incekara Originator: Yusuf Incekara Organization: Avukatpro Limited Sirketi MySQL support: None Severity: Critical Priority: High Category: mysqld, ODBC Class: sw-bug,ODBC-bug Release: mysql-3.23.26-beta Exectutable: mysqld-nt and mysqld-opt Environment: PIII 700 MHZ Intel,256 MB RAM with WinNT4 , Same Problem on PIII MHZ Intel with 128 MB Ram System:WinNT4 with Service Pack 6a, Internet Explorer 5 Same problem on Windows 95 Osr 2 Same Problem on Windows 98 SE Compiler: ? Architecture: i686 +-+---+---+---+-+--+---+--+ | Id | User | Host | db| Command | Time | State | Info | +-+---+---+---+-+--+---+--+ | 1 | root | INCEKARA2 | mysql | Sleep | 1114 | | | | 189 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 190 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 191 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 192 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 193 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 194 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 195 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 196 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 197 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 198 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 199 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 200 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 201 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 202 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 203 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 204 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 205 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 206 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 207 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 208 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 209 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 210 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 211 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 212 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 213 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 214 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 215 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 216 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 217 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 218 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 219 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 220 | avukatpro |
Mysql Bug ? (Can't Connect to Mysql Server on 192.9.200.2 (10055))
From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Mysql Bug ? (Can't Connect to Mysql Server on 192.9.200.2 (10055)) Description: I am connecting from Visual Basic 6 to Mysql via ODBC DSN. When i made 94 sleeped connection after 94 can't connect to a server. It says : [TCX][MYODBC]Can't Connect to Mysql Server on 192.9.200.2 (10055) Is it normal. If there a maximum connection limit for process ? I think it is abnormal cause everyone is talking about 10.000 Web Hit. Is it cause from sleeped process list ? I increased maximum connection to 200 but it not works. I think it is not related with maximum connection . It is related with maximum process number. How-To-Repeat: Run this code on Microsoft Visual Basic 6 ENT Edition. For i = 1 To 200 Set Temp_Bag(i) = New ADODB.Connection Temp_Bag(i).Open "DSN=Avukatpro;UID=avukatpro;PWD=avukatpro" Next i It hangs 94 on Winnt. It hangs 60 on Win 95. It hangs 50 on Win98 SE. Fix: Any offer? What means 10055 ? I check-out documents. Can't see any recomend or definition for this error number. Additional Information : Host: 3.23.30-gamma ClientInfo: 3.23.6-alpha HostInfo: 192.9.200.2 via TCP/IP ProtoInfo: 10 I stopped on 93 process and got process list. It is attached. Synopsis: synopsis of the problem (one line) Submitter-Id: Yusuf Incekara Originator: Yusuf Incekara Organization: Avukatpro Limited Sirketi MySQL support: None Severity: Critical Priority: High Category: mysqld, ODBC Class: sw-bug,ODBC-bug Release: mysql-3.23.26-beta Exectutable: mysqld-nt and mysqld-opt Environment: PIII 700 MHZ Intel,256 MB RAM with WinNT4 , Same Problem on PIII MHZ Intel with 128 MB Ram System:WinNT4 with Service Pack 6a, Internet Explorer 5 Same problem on Windows 95 Osr 2 Same Problem on Windows 98 SE Compiler: ? Architecture: i686 +-+---+---+---+-+--+---+--+ | Id | User | Host | db| Command | Time | State | Info | +-+---+---+---+-+--+---+--+ | 1 | root | INCEKARA2 | mysql | Sleep | 1114 | | | | 189 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 190 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 191 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 192 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 193 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 194 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 195 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 196 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 197 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 198 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 199 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 200 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 201 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 202 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 203 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 204 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 205 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 206 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 207 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 208 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 209 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 210 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 211 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 212 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 213 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 214 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 215 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 216 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 217 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 218 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 219 | avukatpro | INCEKARA2 | avukatpro | Sleep | 16 | | | | 220 | avukatpro |
(+)Help
I have two mysql server with same tables how can i synch. this tables i'm mean if one table is updated second table is updated too !? _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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: Possible to load only index MYI file into RAM?
Thanks for the help Kent and the rest of the list, Would be grateful for some help interpreting some mysql system variables. Just to recap on what I am trying to achieve, I want to load as much of a very large key file (224704512 mykeyfile.MYI) into RAM to improve selects. I did a "show status;" and the mysql variables for the keys were as follows :- | Key_blocks_used | 7793 | | Key_read_requests| 107315735 | | Key_reads| 219450 | | Key_write_requests | 0 | | Key_writes | 0 | Variable key_buffer_size is set to 8388600. I understand that the key_buffer_size is in bytes so I have a 8MB key buffer size. So I can increase this and that should improve select speeds. That's great. My QUESTION is : what do the "Key_read_requests" and "Key_reads" figures tell me. Are they in bytes? I'm not quite sure how to use them and interpret them to determine if select speeds are improving? Thanks everyone. This is such a great list! Michael Thomas -Original Message- From: Kent Hoover [mailto:[EMAIL PROTECTED]] Sent: Friday, January 26, 2001 2:39 PM To: [EMAIL PROTECTED] Subject: Possible to load only index MYI file into RAM? You can tell MySQL to use (lots) more RAM to cache Index Keys by setting an explicit value for its key_buffer variable. You can use the SHOW STATUS query to determine the cache hit rate by comparing these two values: Key_reads versus Key_read_requests. Key_reads divided by Key_read_requests would give you the cache "miss rate". Cheers, Kent Hoover - 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: (+)Help
RTFM on Replication or do develop own methods! SEARCH THE LIST (start from www.mysql.com Documention/Mail lists) - Original Message - From: "Alexei Sh." [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 27, 2001 8:20 PM Subject: (+)Help I have two mysql server with same tables how can i synch. this tables i'm mean if one table is updated second table is updated too !? __ ___ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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
Problem with replication in 3.23.32
Greetings- I have been experiencing an unusual issue with replication in 3.23.32. We are using a dual-master setup on two FreeBSD4.2-STABLE machines. I am able to initially set up the replication, and issue simple updates from one or the other machine, but in certain circumstances we receive errors like this, although I've not changed any settings: 010126 23:31:14 Slave thread: error connecting to master:Can't connect to MySQL server on '86' (65)(22), retry in 3306 sec mysql show slave status; +-+--+-+---+ --+-+---+-+ -+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | +-+--+-+---+ --+-+---+-+ -+ | 86 | 209.95.64.253 | 0 | 3306 | test1-bin.001 | 0 | Yes | | | +-+--+-+---+ --+-+---+-+ -+ 1 row in set (0.01 sec) This has happened under a number of circumstances, the most recent being after resynching the two database, successfully performing a couple of updates, and then issuing "reset master" and "reset slave" on both machines. I then shutdown one machine's MySQL server, and inserted 100,000 very simple rows into the other server. Upon restarting the first server, it begins to replicate, all the way to about 93,000 records, and then quits with a message like this: 010126 22:27:30 Error reading packet from server: Could not find first log (read_errno 0,server_errno=65535) At this point, show slave status looks normal, but all replication has ceased. However, the next time that the slave is restarted, I see an anomaly such as the show slave status above, and also this in the master.info file (which was previously normal): test2-bin.001 0 73 test2.circle.n replicator 0 3306 ! 3306 10 As well as the following in the error log (timestamp equivalent to MySQL server startup): 010126 22:39:56 Warning: setrlimit returned ok, but didn't change limits. Max open files is 1064 010126 22:39:56 Warning: Changed limits: max_connections: 250 table_cache: 402 010126 22:40:41 Slave thread: error connecting to master:Can't connect to MySQL server on '86' (65)(65), retry in 3306 sec I have verified that the binary logs do exist and are readable by the mysql user. I have tried clearing all logs, resetting both master and slave on both servers, resynching and then restarting, and am still encountering this issue. A similar error also occurs when both servers are running - the replication stops at a certain point and the slave table gets corrupted as above. my.cnf reads as follows on both machines (with the master-host and server-id different): [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=512K set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M set-variable= max_connections=250 set-variable= connect_timeout=60 log-update master-host=test2.circle.net master-user=x master-password=x master-connect-retry=10 log-bin server-id=2 Does anyone have any suggestions or ideas about this? Any help would be most appreciated. I would be glad to provide any more information that might be necessary. Please cc me off the list with any responses. Thanks for everyone's time- Brendan Sherar Server Operations Circle Net, Inc. http://www.circle.net/ - 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: 3 million+ records problems.
Have you tried adding an index on id? -Original Message- From: taree [mailto:taree]On Behalf Of Manuel Capinha Sent: January 27, 2001 12:10 PM To: [EMAIL PROTECTED] Subject: 3 million+ records problems. Hi! I've got a table with 3 million+ records. This table has only to columns: an int and a varchar(80). I need to periodically execute a statement like: "SELECT id,count(*) FROM names GROUP bY id", which takes very long time to end. I'm using perl (DBI) to access this table. When I do a select by id (SELECT name FROM names WHERE id=1) it hangs forever for very large results (1million). I'm running mysql 3.22.29 on FreeBSD 3.4. Would an upgrade to the latest 3.23 help ? Would an upgrade to FreeBSD 4 or maybe Linux help ? The id column is indexed, but i believe this is A Good Thing(tm). I've checked past messages of this mailing list and i couldn't find anything that seemed relevant to my case. Can anyone give me some suggestions to speed things up ? Thanks in advance. Manuel Capinha, who as just seen his weekend ruined. - 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: 3 million+ records problems.
Yes, that was the first thing i tried. The id column is indexed. Scott Gerhardt wrote: Have you tried adding an index on id? -Original Message- From: taree [mailto:taree]On Behalf Of Manuel Capinha Sent: January 27, 2001 12:10 PM To: [EMAIL PROTECTED] Subject: 3 million+ records problems. Hi! I've got a table with 3 million+ records. This table has only to columns: an int and a varchar(80). I need to periodically execute a statement like: "SELECT id,count(*) FROM names GROUP bY id", which takes very long time to end. I'm using perl (DBI) to access this table. When I do a select by id (SELECT name FROM names WHERE id=1) it hangs forever for very large results (1million). I'm running mysql 3.22.29 on FreeBSD 3.4. Would an upgrade to the latest 3.23 help ? Would an upgrade to FreeBSD 4 or maybe Linux help ? The id column is indexed, but i believe this is A Good Thing(tm). I've checked past messages of this mailing list and i couldn't find anything that seemed relevant to my case. Can anyone give me some suggestions to speed things up ? Thanks in advance. Manuel Capinha, who as just seen his weekend ruined. - 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: 3 million+ records problems.
From a mysqldump: # # Table structure for table 'names' # CREATE TABLE names ( id int(10) unsigned DEFAULT '0' NOT NULL, name varchar(80) DEFAULT '' NOT NULL, KEY id (id) ); The machine is an Intel Celeron 533Mhz, with 64 mb of RAM, with dual 10 GB EIDE hard drives (doing RAID), running FreeBSD 3.4-Release. Artem Koutchine wrote: Well then, be so kind and tell us your index configuration and your hardware configuration. - Original Message - From: "Manuel Capinha" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 27, 2001 9:10 PM Subject: 3 million+ records problems. Hi! I've got a table with 3 million+ records. This table has only to columns: an int and a varchar(80). I need to periodically execute a statement like: "SELECT id,count(*) FROM names GROUP bY id", which takes very long time to end. I'm using perl (DBI) to access this table. When I do a select by id (SELECT name FROM names WHERE id=1) it hangs forever for very large results (1million). I'm running mysql 3.22.29 on FreeBSD 3.4. Would an upgrade to the latest 3.23 help ? Would an upgrade to FreeBSD 4 or maybe Linux help ? The id column is indexed, but i believe this is A Good Thing(tm). I've checked past messages of this mailing list and i couldn't find anything that seemed relevant to my case. Can anyone give me some suggestions to speed things up ? Thanks in advance. Manuel Capinha, who as just seen his weekend ruined. - 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: RAW Device support
-Original Message- From: Nazeem Y [mailto:[EMAIL PROTECTED]] Sent: 27 January 2001 19:19 To: [EMAIL PROTECTED] Subject: RAW Device support Does MySQl syupport RAW devices ? Thanks You mean devices that can Read And Write? Sure. If you perhaps mean that it can address harddisks directly instead of going through an fs, then I guess the answer would be 'no'. - 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: RAW Device support
That would be more like DB2 or Oracle which would be more than happy to setup their own table space on a raw drive. They build their own file system and tables on the drive. Its very efficient and why a lot of large databases reside on DB2 or Oracle. Curtis On Sat, 27 Jan 2001, Sander Pilon wrote: -Original Message- From: Nazeem Y [mailto:[EMAIL PROTECTED]] Sent: 27 January 2001 19:19 To: [EMAIL PROTECTED] Subject: RAW Device support Does MySQl syupport RAW devices ? Thanks You mean devices that can Read And Write? Sure. If you perhaps mean that it can address harddisks directly instead of going through an fs, then I guess the answer would be 'no'. - 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: 3 million+ records problems.
Adding another 192megs of ram or so would really boost performance. When I increased the RAM on my PIII 450Mhz from 128 to 256Mb, performance almost doubled for most queries on a 72k record table. From a mysqldump: # # Table structure for table 'names' # CREATE TABLE names ( id int(10) unsigned DEFAULT '0' NOT NULL, name varchar(80) DEFAULT '' NOT NULL, KEY id (id) ); The machine is an Intel Celeron 533Mhz, with 64 mb of RAM, with dual 10 GB EIDE hard drives (doing RAID), running FreeBSD 3.4-Release. Artem Koutchine wrote: Well then, be so kind and tell us your index configuration and your hardware configuration. - Original Message - From: "Manuel Capinha" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 27, 2001 9:10 PM Subject: 3 million+ records problems. Hi! I've got a table with 3 million+ records. This table has only to columns: an int and a varchar(80). I need to periodically execute a statement like: "SELECT id,count(*) FROM names GROUP bY id", which takes very long time to end. I'm using perl (DBI) to access this table. When I do a select by id (SELECT name FROM names WHERE id=1) it hangs forever for very large results (1million). I'm running mysql 3.22.29 on FreeBSD 3.4. Would an upgrade to the latest 3.23 help ? Would an upgrade to FreeBSD 4 or maybe Linux help ? The id column is indexed, but i believe this is A Good Thing(tm). I've checked past messages of this mailing list and i couldn't find anything that seemed relevant to my case. Can anyone give me some suggestions to speed things up ? Thanks in advance. Manuel Capinha, who as just seen his weekend ruined. - 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 - 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
FS setup for MySQL (Was : RAW Device support)
Interesting, how will MySQL benefit from using -async -noatime options for filesystem it is keeping its data on or from using SoftUpdates (for FreeBSD in particular). Anybody experimented? Artem - Original Message - From: "Dan Nelson" [EMAIL PROTECTED] To: "Nazeem Y" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, January 27, 2001 9:34 PM Subject: Re: RAW Device support In the last episode (Jan 27), Nazeem Y said: Does MySQl syupport RAW devices ? No, and since MySQL relies on the operating system to cache table data, you would get horrible performance anyway (the OS does not cache raw devices). -- Dan Nelson [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
MySQL connecting to MSSQL7
Hi, It may sound strange, but the fact is that I'm needing to share data between one M$SQL7 and the MySQL. It's one business envoirnment that is migrating from one to the other, and in the meantime (until the new system does have all the features developed) I may still need to query the data in different structure. Did anyone has any information about putting MySQL either as a master or as a slave in a automatic replication configuration with M$SQL7 server ? I'm not talking about simultaneous updates, but only updates at MySQL to be replicated at M$SQL7 server, obviously in different machines, one running RedHat and other running WinNT. 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
3.23.32 not showing any DB's in mysql util
I just built from a src.rpm and installed 3.23.32 on an RedHat 7.0 box. It installs with no errors and runs, but using the command line utility "mysql" it does not show any of the databases (test, mysql, etc). The databases are in the /var/lib/mysql directory, and I can create new databases, they just don't display in mysql, or phpMyAdmin for that matter. Sorry if this is a known issue, I've not been able to find an answer in the list archives. -- Brett Jones [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
can not make mysql
Hi, I failed to install mysql-3.23.30-gamma on bsdi4.2. this is the err mesg I got: Making all in myisam make: don't know how to make mi_test_all. Stop *** Error code 1 Pls help 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: how to delete records older than 14 days
Sir, your DELETE statement runs fine on my computer. The problem appears to be in your PHP code. Post the problem to a PHP group with all the relevant lines, not just the two shown below. Hello, I'm trying to figure out the correct syntax for requesting the database to remove all records older than 14 days. The mysql query that I am using (from within php4) is: //---update news so only last two weeks of news are available---// $sql2 = "delete from news where newsdate DATE_SUB(NOW(), INTERVAL 14 DAY)"; $result2 = mysql_db_query($dbname,$sql); But it doesn't seem to be working. Here is the the definition of the "news" table: mysql describe news; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(3) | | PRI | 0 | auto_increment | | hub | char(3) | YES | | NULL || | newsdate | date | YES | | NULL || | headline | varchar(100) | YES | | NULL || | article | longtext | YES | | NULL || +--+--+--+-+-++ 5 rows in set (0.05 sec) Basically this is for a news page, and items get pushed down the "list" (what the viewer sees) and then eventually are supposed to be deleted. Any suggestions on how to do this more efficiently would be welcome as well. Please email me directly (hit reply to the message). Thanks- Thomas - 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 Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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 delete records older than 14 days
On Sat, Jan 27, 2001 at 09:58:23AM -0600, Thomas Katsampes wrote: Hello, I'm trying to figure out the correct syntax for requesting the database to remove all records older than 14 days. The mysql query that I am using (from within php4) is: //---update news so only last two weeks of news are available---// $sql2 = "delete from news where newsdate DATE_SUB(NOW(), INTERVAL 14 DAY)"; $result2 = mysql_db_query($dbname,$sql); Uh.. is this an *exact* quote of your PHP script? Why are you defining $sql2, and using $sql in the mysql_db_query()? G'luck, Peter -- If I had finished this sentence, - 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
password mysql
we are just getting started using mysql and want to develop an admin password access section for our site using cold fusion mysql. Is thier a site with examples of this type of thing ? Thanks, Byron - 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: FS setup for MySQL (Was : RAW Device support)
In the last episode (Jan 27), Artem Koutchine said: Interesting, how will MySQL benefit from using -async -noatime options for filesystem it is keeping its data on or from using SoftUpdates (for FreeBSD in particular). Not much. Since you're constantly accessing the same small amount of files over and over, there just isn't enough metadata being changed for any of those three options to make any difference (all three options affect metadata only; file data itself is written async by defaut). I'd enable softupdates just on general principles, though. -- Dan Nelson [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 manual
NuSphere offers a MySQL, PHP, Perl, Apache distribution that includes the manual (and 3 O'Reilly books) in a printed format. For $79 you can't go wrong - Original Message - From: "Pak Tse" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 23, 2001 8:10 PM Subject: MySQL manual I cannot download the PDF format of the manual from the website. Any help ? - 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
ORDER BY problem and possibly others..
Hello, I just installed the newest MySQL (3.23.32) with PHP 4.0.4pl1 (shared module) on an Apache 1.3.12 (Linux). Some SQL syntaxes that worked before don't anymore. E.g.: $query = "SELECT * FROM $userstable ORDER BY when DESC LIMIT 0, 3"; doesn't work: Warning: Supplied argument is not a valid MySQL result resource in /home/FV/aal/public_html/frame1.php3 on line 168 empty set It seems it needs to be: $query = "SELECT * FROM $userstable ORDER BY 'when' DESC LIMIT 0, 3"; Also, phpMyAdmin is completely O3 (Out Of Order), the left pane usually showing the databases is only showing blanks Any comments? J.M. Roth J.M. Roth I N T E L L I G E N T - I P S . A . - Internet Services more PHONE: (+352) 26/4363-53FAX: (+352) 26/4363-73 OFFICES: 7, rue Pletzer Centre Helfent L-8080 Bertrange www.iip.lu - [EMAIL PROTECTED]MOBILE: (+352) 091/436353
Re: ORDER BY problem and possibly others..
* J.M. Roth [EMAIL PROTECTED] wrote on 28.01.01 02:05: I just installed the newest MySQL (3.23.32) with PHP 4.0.4pl1 (shared module) on an Apache 1.3.12 (Linux). ... $query = "SELECT * FROM $userstable ORDER BY when DESC LIMIT 0, 3"; doesn't work: From your query I think you upgraded from a 3.22.x version? When was not a reserved word in 3.22, but is so in 3.23. There have been a few hints how to both circumvent and fix this in 3.23, just search the archives. The thread was called something like "3.22 databases in 3.23 cause problems with fields named 'when'". -- Tomi Junnila [EMAIL PROTECTED] http://www.badzilla.net/~topeju/ Electronics and Information Technology, University of Turku, Finland - 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: ORDER BY problem and possibly others..
"J.M. Roth" wrote: Hello, I just installed the newest MySQL (3.23.32) with PHP 4.0.4pl1 (shared module) on an Apache 1.3.12 (Linux). Some SQL syntaxes that worked before don't anymore. E.g.: $query = "SELECT * FROM $userstable ORDER BY when DESC LIMIT 0, 3"; doesn't work: Warning: Supplied argument is not a valid MySQL result resource in /home/FV/aal/public_html/frame1.php3 on line 168 empty set http://www.mysql.com/doc/R/e/Reserved_words.html http://www.mysql.com/doc/N/e/News-3.23.2.html ... sorry, somtimes adding new features ads reserved words - 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: can not make mysql
Why gamma??? Go to www.mysql.com and download a stable version of mysql-3.23.31. It's official now! On Saturday 27 January 2001 16:11, CheongMeng wrote: = Hi, = I failed to install mysql-3.23.30-gamma on bsdi4.2. = this is the err mesg I got: = Making all in myisam = make: don't know how to make mi_test_all. Stop = *** Error code 1 = = Pls help = 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 -- Bolek, URL: http://www.bolek.com URL: http://slash.bolek.com e-mail: [EMAIL PROTECTED] ICQ: 4086197, Address: 402905326 - 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
Temporary Table problem
I'm using version 3.23.25, and am having trouble setting up a temporary table. I have the following permanent table already set up: bill_pay (pbid int (8) default '0' not null auto_increment, billdesc varchar(20), bpamount int(8), pbdate date, budgetid int(8), primary key (pbid), unique id (pbid)) I have tried to set up a temporary table: CREATE TEMPORARY TABLE tempbills ( billdesc varchar(20), bpamount int(8) ) SELECT billdesc, bpamount FROM bill_pay When I do this I get an error message saying "Duplicate column name "billdesc"". And if I change the column names on the temp table it still won't work - the table doesn't get created. If someone could help me out I'd be grateful! Matt _ Get your FREE download of MSN Explorer at http://explorer.msn.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: Bug ?? combining NOT and like in where clause
Tor-Erik, If 'I' was a language syntax interpreter, i'd see "where not field like pattern" as where (not(field) like pattern and "where field not like pattern" as where (field !like pattern) notice that not(field) probably returns 'False' so your first query is: where False like pattern with, of course, a empty result set Use brackets anyhow because "where not (field like pattern)" makes sense! rene - Original Message - From: "Tor-Erik Hagen" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: "Tor-Erik Hagen" [EMAIL PROTECTED] Sent: Sunday, January 28, 2001 12:56 AM Subject: Bug ?? combining NOT and like in where clause Tested on the following versions of mysql: 3.22.32, 3.23.31 on Linux Description: "where not field like pattern" gives empty set, while "where field not like pattern" return what i expect How-To-Repeat: create table testtabell(test varchar(10)); insert into testtabell values('test.com') insert into testtabell values('test.no') insert into testtabell values('test2.com') insert into testtabell values('test2.no') select * from testtabell where not test like '%.no'; This returns an empty set select * from testtabell where test not like '%.no'; This one returns: +---+ | test | +---+ | test.com | | test2.com | +---+ 2 rows in set (0.00 sec) That is right. I don't know if the first one is legal syntax, but it should then return an parse error on the sql, not an empty set. MySQL support: none Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.31 (Source distribution) Environment: machine, os, target, libraries (multiple lines) System: Linux zalo 2.2.16 #73 Fri Jun 16 18:48:22 PDT 2000 i586 unknown Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' Configure command: ./configure --without-bench - 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: Temporary tables
How about CREATE TEMPORARY TABLE tempbills SELECT billdesc, bpamount FROM bill_pay Thanks, James NEBIS Tech. http://www.nebis.com -Original Message- From: Matt Bailey [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 27, 2001 4:49 PM To: [EMAIL PROTECTED] Subject: Temporary tables I'm using version 3.23.25, and am having trouble setting up a temporary table. I have the following permanent table already set up: bill_pay (pbid int (8) default '0' not null auto_increment, billdesc varchar(20), bpamount int(8), pbdate date, budgetid int(8), primary key (pbid), unique id (pbid)) I have tried to set up a temporary table: CREATE TEMPORARY TABLE tempbills ( billdesc varchar(20), bpamount int(8) ) SELECT billdesc, bpamount FROM bill_pay When I do this I get an error message saying "Duplicate column name "billdesc"". And if I change the column names on the temp table it still won't work - the table doesn't get created. If someone could help me out I'd be grateful! Matt _ Get your FREE download of MSN Explorer at http://explorer.msn.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: how to get the union of the values in a SET column ?
Hi again Bob, I know it's not 'proper' database design and I should have normalized the one-to-many relationships to multiple tables. However, it was fast and gave me a result which worked quickly (and up against a mad deadline). I think you've just identified the most common use of the SET datatype. In your original posting, you said that a procedural solution wouldn't scale well. If you database will never be more than 5,000 records, why are you concerned about scalability? Are you planning on processing a hundred concurrent queries? I meant scalability in terms of the number of items in the SET (not the number of rows in the table). For example, consider a SET field with 26 items - eg. set_col SET ('A','B','C'. 'Z') If I wish to find out which values of the SET have been used at least once, I have to either: a) Query for each member of the SET and find out which gives a result: SELECT * FROM my_table WHERE set_col LIKE ('%A%') SELECT * FROM my_table WHERE set_col LIKE ('%B%') etc. b) Select DISTINCT(set_col) and then parse the results for for each member of the set .. which is a worse method. I was hoping that since each member of the SET really represents a number, that there would be some bitwise operation that would painlessly produce all the members of the SET that appear at least once. If not, no worries - I'll redo the database properly when I get some time. Thanks again, chas Bob wrote: Sir, the basic problem is that you've violated first normal form, which states columns should only contain atomic (indivisible) data. Since you plan to divide up the data in your columns, it's obviously not indivisible. My recommendation is to brush up on the first three normal forms and redesign your tables. There may be a way to do what you want with MySQL functions, but that will require a lot of processing and slow your queries down. You'll be back where you started, with a solution that doesn't scale well. Chas wrote: I have a column that is defined as a SET. For simplicity's sake, let's say the column is defined as: set_col SET ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'Z') After entering a few hundred rows, I know that some but not all values of the SET have been used at least once. How can one work out which values have been used at least once ? Doing a SELECT DISTINCT(set_col) on the table might provide something like this: A,B,D A,C,G,N,M,P C,E,F,H,I,R,S C,E,G,I D,F G,H,L,M,O etc But it then requires processing to work out which values have been used at least once. It's not very scalable. Is there a method to retrieve a list of all the values that have been used at least once ? - 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 Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - 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
threads stuck waiting for table in 3.23.32 linux
DescripWell, for some reason after a table is being automatically repaired by mysql , some thread will be a suspended state. Mysqladmin will report a "waiting for table" in the state column of those threads. A look at the .err file showed me a "Warning: Checking table:" message. I waited a while and eventually I got my usual "Aborted connection" but the threads were still there. The oldest one had a times value of 95510. precise description of the problem (multiple lines) How-To-Repeat: run your server with myisam-recover and wait. Fix: mysqladmin flush-tables got rid of the offending threads Submitter-Id: submitter ID Originator:[EMAIL PROTECTED] Organization: MySQL support: none Synopsis: stuck threads Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release:mysql-3.23.32 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.14 Distrib 3.23.32, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.32-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /my/mysql.sock Uptime: 1 day 2 hours 46 min 40 sec Threads: 3 Questions: 4344271 Slow queries: 0 Opens: 3479 Flush tables: 2 Open tables: 62 Queries per second avg: 45.065 Environment: System: Linux medusa.hitlist.com 2.2.14-5.0smp #1 SMP Tue Mar 7 21:01:40 EST 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 19 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 4101324 Feb 29 2000 /lib/libc-2.1.3.so -rw-r--r--1 root root 20272704 Feb 29 2000 /usr/lib/libc.a -rw-r--r--1 root root 178 Feb 29 2000 /usr/lib/libc.so Configure command: ./configure --with-unix-socket-path=/my/mysql.sock --with-berkely-db=/usr/local/BerkeleyDB.3.2 Perl: This is perl, version 5.005_03 built for i386-linux - 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
simple problem with mysql install
I recently upgraded to linux 7.0 which comes with mysql. Everytime that i try and start the mysql server it starts up then stops. when I try the mysql -user command it gives me a cant connect to mysql.sock error. any idea what is going wrong? Anthony Ortega (512)444-6238 6700 B. Deatonhill Austin Tx, 78745 Find the best deals on the web at AltaVista Shopping! http://www.shopping.altavista.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
simple problem with mysql install
I recently upgraded to linux 7.0 which comes with mysql. Everytime that i try and start the mysql server it starts up then stops. when I try the mysql -user command it gives me a cant connect to mysql.sock error. any idea what is going wrong? Anthony Ortega (512)444-6238 6700 B. Deatonhill Austin Tx, 78745 Find the best deals on the web at AltaVista Shopping! http://www.shopping.altavista.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: OK, I'll bite
NuSphere announced Gemini a few months ago and there is a press release on our website if you want to see the official details at: http://www.nusphere.com/releases/103000.htm Gemini is a project that NuSphere is doing to add a new table type to MySQL that will provide ACID transactions, row-level locking, and automatic crash recovery. The underlying code was part of the kernel of a successful commercial database and has the reputation for being one of the most reliable and easy to use databases available. Gemini brings mature enterprise-class database technology to MySQL in areas that have not been the focus of the popular MyISAM table type. Gemini and MyISAM table types are extremely complementary and I believe the combination will result in an expanding number of applications that will use MySQL as part of their core IT infrastucture not only for front end web functions, but also backend ecommerce functions. In November we met with members of the core MySQL development team in our offices to finalize design issues for the integration effort and since then have been hard at work building the gemini table handler. If you are interested watch this space or our website at www.nusphere.com for more information. Britt... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Vinny Sent: Sunday, January 28, 2001 12:32 AM To: Mysql List Subject: OK, I'll bite What the heck is gemini and innobase? and how will they be used in mysql of the future? -- Vincent [EMAIL PROTECTED] Xaymaca Studios http://www.blendermania.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: ArabiaSoft and NuSphere
NuSphere is cheaper, comes with a printed MySQL manual, and IMHO, installs easier. - Original Message - From: "Frank J. Schmuck" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 24, 2001 6:45 AM Subject: ArabiaSoft and NuSphere Any comments on the packages from ArabiaSoft and NuSphere? Thanks Frank - 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
indexed text fields aren't handled correctly
Description: When creating an index with multiple text fields, `where' clause isn't handled correctly. If no index is present, "a = 'foo' and b = 'bar'" does work well. However, if an index is present, "a = 'foo'" and "a = 'foo' and b != 'bar'" work correctly, but "a = 'foo' and b = 'bar'" doesn't. Surprisingly enough, "a = 'foo' and (b = 'bar') = 1" still works! How-To-Repeat: create table test (a text, b text); insert test values ('foo', 'bar'); insert test values ('foo', 'foo'); /* This works. */ select * from test where a = 'foo' and b = 'bar'; create index test_index on test (a(3), b(3)); /* This doesn't work. */ select * from test where a = 'foo' and b = 'bar'; /* But this works. */ select * from test where a = 'foo' and (b = 'bar') = 1; Fix: Use STRCMP. Submitter-Id: submitter ID Originator: Organization: MySQL support: none Synopsis: indexed text fields are mishandled. Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.32 (Source distribution) Server: mysqladmin Ver 8.14 Distrib 3.23.32, for sun-solaris2.8 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.32 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 2 hours 59 min 50 sec Threads: 2 Questions: 190 Slow queries: 7 Opens: 47 Flush tables: 1 Open tables: 8 Queries per second avg: 0.018 Environment: System: SunOS space 5.8 Generic_108528-04 sun4u sparc SUNW,Ultra-80 Architecture: sun4 Some paths: /bin/perl /usr/local/bin/make /opt/sfw/bin/gmake /opt/sfw/bin/gcc GCC: Reading specs from /opt/sfw/lib/gcc-lib/sparc-sun-solaris2.8/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='-O6 -I/usr/local/include' CXX='gcc' CXXFLAGS='-O6 -I/usr/local/include -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1731920 1·î 6Æü 2000ǯ /lib/libc.a lrwxrwxrwx 1 root root 11 1·î 23Æü 10:02 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1113260 1·î 6Æü 2000ǯ /lib/libc.so.1 -rw-r--r-- 1 root bin 1731920 1·î 6Æü 2000ǯ /usr/lib/libc.a lrwxrwxrwx 1 root root 11 1·î 23Æü 10:02 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1113260 1·î 6Æü 2000ǯ /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql --with-low-memory --enable-assembler Perl: This is perl, version 5.005_03 built for sun4-solaris - 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