MySQL hangs after some days
Hello, since I moved to another dedicated server 1-2 months ago I'm experiencing big problems with mysql. Normally the server works fine for a website under heavy load but after a week or so it hangs in a locked table forever and thus preventig other processes to access it. Then I have to shut down the (mysql-)server, kill -9 safe_mysql and another mysql process (probably the one with the table lock) and run myisamchk -r *.MYI, then restart the server. The biggest table has currently 1160768 rows in it - don't know if this matters. The mysql server is busy most of the time doing simple SELECTS and also does many INSERTs and UPDATEs. However the server hangs on a INSERT or UPDATE query. I also had running a query log for a while (also when the server crashed) but it's hard to find useful information in there. Some information about the system: Red Hat 9 (Shrike) Linux 2.4.20-28.9 #1 Thu Dec 18 13:46:42 EST 2003 i686 athlon i386 GNU/Linux mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386) Athlon 1.5Ghz (or so), 512MB RAM HDD 28% used What should I do? I already tried to find an answer asking Google but couldn't find the solution... Thanks, Udo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert field from another table
Hello mysql, I have two tables - articles_en and articles_ru. Its fields is 'article', 'author', 'text'. The difference is - field 'text' in articles_en has english text of article and field 'text' in articles_ru has russian text of article. Fields 'article' and 'author' are identical. The tables were filled simultaneously. I want create table articles_multi which will have fields 'article', 'author', 'text_ru', 'text_en' with data from articles_en and articles_ru. How can i do it? - bye, nullevent Wednesday, March 24, 2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --replicate* question
Chua Choon Keng [EMAIL PROTECTED] wrote: What about using binlog-do-db=database_name option? In this case only updates will be logged if the current database is database_name What if I only wish to replicate a few tables of a database? Using binlog-do-db=database_name does limit to this particular database but there is no binlog-ignore-table option that I can use... Is there a workaround? Move tables that you want to replicate to the separate database and use binlog-do-db option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW_DATABASES / safe-show-database question
Robert Montgomery [EMAIL PROTECTED] wrote: I recently upgraded to mysql 4.0.18. In the past you could set the option safe-show-databases and that allowed users to list databases, but only those they have permissions to see. It seems that this 4.0.18 will only allow users to see ALL databases, or NO databases, depending on the SHOW_DATABASES field in the mysql.user table. No. Without SHOW DATABASES privilege only databases on which user has some kind of privileges will be listed in the output of SHOW DATABASES command. Is there any way to allow users to list ONLY the databases they have permissions on? Don't give user SHOW DATABASES privilege. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help with this: ERROR 1047: Unknown command
Hiep Ho [EMAIL PROTECTED] wrote: i have problem adding user to mysql. I can create a database and there is no problem however I got this ERROR 1047: Unknown command when I tried to add new user. I don't think I have this problem before. I run this command GRANT ALL ON *.* TO usename IDENTIFIED BY 'password' ; All I got is this ERROR 1047: Unknown command Execute FLUSH PRIVILEGES and then GRANT command. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior, Table Level Permission
Dear Victoria Reznichenko, Thanks for reply. Could you tell me from where I can get patch to fix this bug. or I have to use Mysql 4.0/Mysql 5.0 with regards, Udbhav Shah = Thinking is the Assets Enterprise is the Way Hard Work is the Solution - Ignited Minds __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert field from another table
nullevent [EMAIL PROTECTED] wrote: I have two tables - articles_en and articles_ru. Its fields is 'article', 'author', 'text'. The difference is - field 'text' in articles_en has english text of article and field 'text' in articles_ru has russian text of article. Fields 'article' and 'author' are identical. The tables were filled simultaneously. I want create table articles_multi which will have fields 'article', 'author', 'text_ru', 'text_en' with data from articles_en and articles_ru. How can i do it? Create new table with CREATE .. SELECT statement, then add indexes. For example: CREATE TABLE articles_multi SELECT ru.article, ru.author, ru.text_ru, en.text_en FROM articles_ru ru, articles_en en WHERE ru.article=en.article AND ru.author=en.author; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies Same problem --Plz giv the solution !!
-Original Message- From: rajesh k [mailto:[EMAIL PROTECTED] Sent: 23 March 2004 14:36 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Newbie - dependencies Same problem --Plz giv the solution !! Robb + ( ME TOO )- still in jail :-( Plz get me out of the jail !!! Thanx, bii --- Hi bii, I got the fix from Alex Greg: You have a previous version of MySQL installed, from the mysql.com RPM's. To get rid of it, do: rpm -e 'MySQL*' first, then: rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm Hope this helps, if not Alex is your man Robb - I'm free :0) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
installation de mysql
j'ai le red hat 9 et j'ai télécharger mysql-3.23.58-pc-linux-i686.tar.gz j'ai crée le repertoir /usr/local/src et j'ai fais gunzip mysql-3.23.58-pc-linux-i686.tar.gz tar xvf mysql-3.23.58-pc-linux-i686.tar.gz cd mysql-3.23.58-pc-linux-i686 ./configure --prefix=/usr/local/mysql mais ca donnée l'erreur suivante: NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databasesand start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables 040324 9:51:43 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.serverto the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h iamintrusion password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should runthe ./bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd . ; ./bin/safe_mysqld You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting the mysqld server. You can test that it is up and running with the command: ./bin/mysqladmin version [EMAIL PROTECTED] mysql-3.23.58-pc-linux-i686]# Starting mysqld daemon with databases from /usr/local/src/mysql-3.23.58-pc-linux-i686/data 040324 09:51:44 mysqld ended j'aimerais bien savoir ou se trouve l'erreur et comment je peux le régler merci d'avance _ MSN Messenger : discutez en direct avec vos amis ! http://www.msn.fr/msger/default.asp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installation de mysql
Hello, Am Mittwoch, 24. März 2004 11:01 schrieb sanaâ Aitouchen: j'ai le red hat 9 et j'ai télécharger mysql-3.23.58-pc-linux-i686.tar.gz j'ai crée le repertoir /usr/local/src et j'ai fais gunzip mysql-3.23.58-pc-linux-i686.tar.gz tar xvf mysql-3.23.58-pc-linux-i686.tar.gz cd mysql-3.23.58-pc-linux-i686 ./configure --prefix=/usr/local/mysql mais ca donnée l'erreur suivante: NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databasesand start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables 040324 9:51:43 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.serverto the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h iamintrusion password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should runthe ./bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd . ; ./bin/safe_mysqld You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting the mysqld server. You can test that it is up and running with the command: ./bin/mysqladmin version [EMAIL PROTECTED] mysql-3.23.58-pc-linux-i686]# Starting mysqld daemon with databases from /usr/local/src/mysql-3.23.58-pc-linux-i686/data 040324 09:51:44 mysqld ended j'aimerais bien savoir ou se trouve l'erreur et comment je peux le régler merci d'avance Sry i dont speak any french, but i guess ur problem is that mysql ends itself right after u tried to start it? If yes, then try to do a chown on ur localstatedir and all files inside. Ususally this will be /var/mysql/data/ but it may be different for ur installation. All db- and table- directories need to be owned by the mysql-user, which is usually mysql. If those directories and files in ur /var/mysql/data are owned by root then change it and it will work. If i misunderstood ur problem entirely then sry but maybe a good idea to try in english again then :) Bernd -- [Zufallssig 4] One OS to rule them all, one OS to find them. One OS to bring them all, and in the darkness bind them In the land of Redmond, where the shadows lie. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb_locks_unsafe_for_binlog
Hello colleagues, I knew there's an option to have a row level locking rather than a table level one, the innodb_locks_unsafe_for_binlog I knew also that this option makes the binary logs unsafe I'd like to know if the whole replication mechanism becomes unsafe in this case and then what's the mechanism to be used to replicate the DB when the innodb_locks_unsafe_for_binlog is enabled. regards Enrico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installation de mysql
Hi, j'ai le red hat 9 et j'ai télécharger mysql-3.23.58-pc-linux-i686.tar.gz j'ai crée le repertoir /usr/local/src et j'ai fais gunzip mysql-3.23.58-pc-linux-i686.tar.gz tar xvf mysql-3.23.58-pc-linux-i686.tar.gz cd mysql-3.23.58-pc-linux-i686 ./configure --prefix=/usr/local/mysql mais ca donnée l'erreur suivante: NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databasesand start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables 040324 9:51:43 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.serverto the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h iamintrusion password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should runthe ./bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd . ; ./bin/safe_mysqld You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting the mysqld server. You can test that it is up and running with the command: ./bin/mysqladmin version [EMAIL PROTECTED] mysql-3.23.58-pc-linux-i686]# Starting mysqld daemon with databases from /usr/local/src/mysql-3.23.58-pc-linux-i686/data 040324 09:51:44 mysqld ended j'aimerais bien savoir ou se trouve l'erreur et comment je peux le régler merci d'avance Please refer to the INSTALL file located into the mysql-3.23.58-pc-linux-i686 folder, all is described there. ./configure --prefix=/usr/local/mysql is not needed for binary distrib. PS : this is an english list, so talk english please or do not expect any answer ... Bye David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary keys
JOHN MEYER [EMAIL PROTECTED] wrote: Is there a way to do a SQL Select and get a record without specifying the primary key. Example. I have two tables CANDLE and VOTIVES. Can I define a select that says SELECT * FROM TABLE WHERE PRIMARY_KEY=... No, You can't. Only if PRIMARY or UNIQUE KEY consists of one integer column, you can refer to that column as _rowid: http://www.mysql.com/doc/en/CREATE_TABLE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installation de mysql
Bonjour, For the benefit of the group I will translate the French part of my text :-) Le tarball que vous avez telecharge est en fait le binaire executable de MySQL pas les sources. Donc vous n'avez pas a executer configure sur celui-ci. The tarball that you have downloaded is in fact the eexcutable binary of MySQL not the sources. Therefore, you do not have to execute configure on it. Si vous avez acces au CD de distribution de RedHat je vous recommanderais d'installer MySQL a partir du RPM. Si vous n'avez pas acces a ceux-ci telecharger le RPM pour RedHat 9. Il est en effet beaucoup plus facile d'installer MySQL a partir des RPM. If you have access to the distribution CDs of RedHat I recommend you to install MySQL from the RPM. If you do not have access to those, you should download the RPM for RedHat 9. In effect it is a lot more easier to install MySQL from the RPM. Salutations, Bernard On Wednesday 24 March 2004 05:01, sanaâ Aitouchen wrote: j'ai le red hat 9 et j'ai télécharger mysql-3.23.58-pc-linux-i686.tar.gz j'ai crée le repertoir /usr/local/src et j'ai fais gunzip mysql-3.23.58-pc-linux-i686.tar.gz tar xvf mysql-3.23.58-pc-linux-i686.tar.gz cd mysql-3.23.58-pc-linux-i686 ./configure --prefix=/usr/local/mysql mais ca donnée l'erreur suivante: NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databasesand start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables 040324 9:51:43 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.serverto the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h iamintrusion password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should runthe ./bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd . ; ./bin/safe_mysqld You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting the mysqld server. You can test that it is up and running with the command: ./bin/mysqladmin version [EMAIL PROTECTED] mysql-3.23.58-pc-linux-i686]# Starting mysqld daemon with databases from /usr/local/src/mysql-3.23.58-pc-linux-i686/data 040324 09:51:44 mysqld ended j'aimerais bien savoir ou se trouve l'erreur et comment je peux le régler merci d'avance _ MSN Messenger : discutez en direct avec vos amis ! http://www.msn.fr/msger/default.asp -- Bernard Clement Info-Electronics Systems Inc. Technical Director phone: 514-421-0767 ext: 231 fax: 514-421-0769 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary keys
So I can do something like SELECT * FROM . $tablename . WHERE _rowid= $id (I'm using PHP and the primary key is a unique integer column in each of the tables). From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Primary keys Date: Wed, 24 Mar 2004 13:28:58 +0200 JOHN MEYER [EMAIL PROTECTED] wrote: Is there a way to do a SQL Select and get a record without specifying the primary key. Example. I have two tables CANDLE and VOTIVES. Can I define a select that says SELECT * FROM TABLE WHERE PRIMARY_KEY=... No, You can't. Only if PRIMARY or UNIQUE KEY consists of one integer column, you can refer to that column as _rowid: http://www.mysql.com/doc/en/CREATE_TABLE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Find a broadband plan that fits. Great local deals on high-speed Internet access. https://broadband.msn.com/?pgmarket=en-us/go/onm00200360ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MERGE tables and concurrent inserts
Hello, Further to my MERGE table query the other day, I've now put it into action and it seems to be working fine. However I'm noticing that INSERT LOW_PRIORITY statements are blocking when there are SELECTs running. Does anyone know if concurrent inserts work with MERGE tables? Are there any criteria I need to satisfy? I know for certain that the table being used to INSERT to has *no* deleted rows in it. Do I need to ensure that all children of the MERGE table have no deleted rows? I'm wondering if specifying LOW_PRIORITY disables concurrent inserts explicitly, so I'll try removing that and see if anything improves, but in the meantime, if anyone has any advice, please share :) Thanks, -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MERGE tables and concurrent inserts
On Wed, Mar 24, 2004 at 01:23:53PM +, Chris Elsworth wrote: I'm wondering if specifying LOW_PRIORITY disables concurrent inserts explicitly, so I'll try removing that and see if anything improves, but in the meantime, if anyone has any advice, please share :) Ignore that bit. Found the answer 10 seconds after sending in the INSERT page. Note that LOW_PRIORITY should normally not be used with MyISAM tables as this disables concurrent inserts. Doh :) -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compiling with icc
Hi, I'm trying to compile mysql 4.0.18 with icc 8.0 on RH AS 3u1. I'm including my last configuration for compiling and how it died- I've tried about a million other ways- this seems to come the closest to compiling normally but dies with an internal error(looks like intel's fault not yours). Does anyone have a configuration where this compiles? What did I do wrong? fyi- there is a problem with the configure/libtool script when you don't compile -static and you use ipo... the compilation runs icc -shared to link the libraries but it needs the -ipo option as well. thanks in advance for any help! yonah here the environment variables: AR=xiar CC=icc CFLAGS='-O3 -ipo -static -axWN -march=pentium4 -mcpu=pentium4' CXX=icc CXXFLAGS='-O3 -ipo -static -axWN -march=pentium4 -mcpu=pentium4' LANG=C LD=xild PATH=/usr/local/intel/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:/root/bin:/usr/local/mysql/bin this is the configure line: ./configure --prefix=/usr/local/mysql --enable-thread-safe-client --without-innodb --without-debug --enable-assembler --with-named-curses-libs=/usr/lib/libncurses.so.5.3 it dies with this error: IPO Warning: unresolved : rl_attempted_completion_function Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : rl_completion_entry_function Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : rl_readline_name Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : popen Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : pclose Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : readline Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : add_history Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : completion_matches Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : write_history Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : read_history Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : __xstat64 Referenced in libmysqlclient.a(my_lib_ipo.o) IPO Warning: unresolved : __fxstat64 Referenced in libmysqlclient.a(my_lib_ipo.o) IPO Warning: unresolved : readdir64 Referenced in libmysqlclient.a(my_lib_ipo.o) IPO Warning: unresolved : fdopen Referenced in libmysqlclient.a(my_fopen_ipo.o) IPO Warning: unresolved : fclose Referenced in libmysqlclient.a(my_fopen_ipo.o) IPO Warning: unresolved : gethostbyname_r Referenced in libmysqlclient.a(my_gethostbyname_ipo.o) IPO Warning: unresolved : __lxstat64 Referenced in libmysqlclient.a(my_symlink_ipo.o) IPO Warning: unresolved : realpath Referenced in libmysqlclient.a(my_symlink_ipo.o) libmysql.c(1808): internal error: 0_1345 icc: error: problem during multi-file optimization compilation (code 4) make[2]: *** [mysql] Error 4 make[2]: Leaving directory `/moat-files/mysql-4.0.18/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/moat-files/mysql-4.0.18' make: *** [all] Error 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick mysql question
4.0.18 is the latest stable! On Tue, 2004-03-23 at 19:08, [EMAIL PROTECTED] wrote: Hi, A quick question, which is the most stable version of mysql to run on a very busy and commercial website(s)? 4.0 or is 4.1 stable enough? Thanks, -R.A -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ||Cel: +58-412-8859934 | ||geek by nature - linux by choice | |...|
Re: Interfaces to 5.0.0
Hi!
just add old-passwords to the mysqld section of your mysql server and
recreate the users, it should work just fine!, mysql 4 and up has a new
auth mecanism
Best Regards
On Wed, 2004-03-24 at 00:10, Lokesh wrote:
Gordon wrote:
A few weeks ago I download the windows version for 5.0.0 and installed
it on my machine. After successfully installing the new version I was
able to connect with all of my old tools including MyCC 0.9.3,
MySQLFront, SQLyog, cmd and MySQL Administrator.
Yesterday I downloaded the 5.0.0 RPM's for linux and only cmd and the
9.0.4 version of MyCC.
Is the interface different on the linux verion vs the windows version?
Is there some setting I can use to allow the old {and current MySQL
Administrator} to connect?
If you have upgraded from MySQL-3.x or MySQL 4.0; you surely need to
concern about compatibility issues. Because of change in authentication
mechanism, old clients (pre-4.1) can't talk to MySQL-5.0.0 servers.
Refer to http://www.mysql.com/doc/en/Upgrade.html, which lists the tasks
and how to plan for upgrade
--
|...|
| _ _|Victor Medina M |
|\ \ \| | _ \ / \ |Linux - Java - MySQL |
| \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA |
| / / / |___| __/ ___ \ |[EMAIL PROTECTED] |
|/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 |
||Cel: +58-412-8859934 |
||geek by nature - linux by choice |
|...|
Re: compiling with icc
Hi! On Wed, 2004-03-24 at 09:51, Yonah Russ wrote: Hi, I'm trying to compile mysql 4.0.18 with icc 8.0 on RH AS 3u1. I'm 8 GUAO! nice!, let us know how faster it is with the Intel compiler, just curious! =) fyi- there is a problem with the configure/libtool script when you don't compile -static and you use ipo... the compilation runs icc -shared to link the libraries but it needs the -ipo option as well. 8 must probably no one here uses the Intel compiler since it is paid =( it dies with this error: IPO Warning: unresolved : rl_attempted_completion_function Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : rl_completion_entry_function 8 Ncurses devel and the readline libriries must be installed for the client to compile Best Regards! -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ||Cel: +58-412-8859934 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using old frm files
Yes, that is the point, there must be a way to view the structure of .frm files, I can't believe no one has ever written a utility for this. if not, its about time! :) I can't even find any documentation as to the internal structure of .frm files, there were some vague references going back to version 3.23 or something. Yes the structure changes, but still... Dan. At 02:36 AM 3/24/2004, you wrote: you can very well use those files, though, im not sure, if you can check the table structure bafore restoring it. HTH Nitin - Original Message - From: dan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 23, 2004 9:34 PM Subject: using old frm files we had a database blown away (oops) and we still have the old .frm files, is there any way to get this structure back? is there a way to view the contents of the .frm files to see how the tables/indexes were setup? I can't find anything in the docs, faqs, etc. Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
Victor Medina wrote: Hi! On Wed, 2004-03-24 at 09:51, Yonah Russ wrote: Hi, I'm trying to compile mysql 4.0.18 with icc 8.0 on RH AS 3u1. I'm 8 GUAO! nice!, let us know how faster it is with the Intel compiler, just curious! =) An ad in one of the Linux magazines I leaf through on occassion showed some pretty noticable improvements (in the order of 20% in some cases), but I'm betting that these were crafted test cases, as we all know that MySQL's two main storage engines are extremely smart when it comes to using discs. fyi- there is a problem with the configure/libtool script when you don't compile -static and you use ipo... the compilation runs icc -shared to link the libraries but it needs the -ipo option as well. 8 must probably no one here uses the Intel compiler since it is paid =( it dies with this error: IPO Warning: unresolved : rl_attempted_completion_function Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : rl_completion_entry_function 8 Ncurses devel and the readline libriries must be installed for the client to compile Best Regards! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
Victor Medina wrote: 8 must probably no one here uses the Intel compiler since it is paid =( actually there is a free non-commercial license version here: http://www.intel.com/software/products/compilers/clin/noncom.htm which is how I happen to have it yonah it dies with this error: IPO Warning: unresolved : rl_attempted_completion_function Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : rl_completion_entry_function 8 Ncurses devel and the readline libriries must be installed for the client to compile Best Regards! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
this is what you are looking for: http://www.intel.com/software/products/global/techtopics/mysql.pdf according to intel- mysql ab provided all the performance data- much more than 20% performance boost sometimes. I'll be darned if their compiler flags work- maybe with 4.1 (apparently that's what they used) but not with 4.0.18 yonah Chris Nolan wrote: Victor Medina wrote: Hi! On Wed, 2004-03-24 at 09:51, Yonah Russ wrote: Hi, I'm trying to compile mysql 4.0.18 with icc 8.0 on RH AS 3u1. I'm 8 GUAO! nice!, let us know how faster it is with the Intel compiler, just curious! =) An ad in one of the Linux magazines I leaf through on occassion showed some pretty noticable improvements (in the order of 20% in some cases), but I'm betting that these were crafted test cases, as we all know that MySQL's two main storage engines are extremely smart when it comes to using discs. fyi- there is a problem with the configure/libtool script when you don't compile -static and you use ipo... the compilation runs icc -shared to link the libraries but it needs the -ipo option as well. 8 must probably no one here uses the Intel compiler since it is paid =( it dies with this error: IPO Warning: unresolved : rl_attempted_completion_function Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : rl_completion_entry_function 8 Ncurses devel and the readline libriries must be installed for the client to compile Best Regards! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to optimize ugly order by?
Hi all, I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype 1) ASC, (membershiptype 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with the calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) /Henrik Schröder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
It's my understanding that MySQL does not currently compile with the Intel compiler - it's too gcc-specific, and icc is not 100% gcc-compatible. I seem to remember seeing this in the on-line documentation somewhere, but I can't remember where. Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
Victor Medina wrote: it dies with this error: IPO Warning: unresolved : rl_attempted_completion_function Referenced in ipo_icc5X7oE4.o IPO Warning: unresolved : rl_completion_entry_function 8 Ncurses devel and the readline libriries must be installed for the client to compile I installed ncurses-devel and readline-devel. not much changed but then I added --without-readline to the configure options and we seem to have made progress: here is the error now: IPO Warning: unresolved : popen Referenced in ipo_icct8OVHG.o IPO Warning: unresolved : pclose Referenced in ipo_icct8OVHG.o IPO Warning: unresolved : __xstat64 Referenced in libmysqlclient.a(my_lib_ipo.o) IPO Warning: unresolved : __fxstat64 Referenced in libmysqlclient.a(my_lib_ipo.o) IPO Warning: unresolved : readdir64 Referenced in libmysqlclient.a(my_lib_ipo.o) IPO Warning: unresolved : fdopen Referenced in libmysqlclient.a(my_fopen_ipo.o) IPO Warning: unresolved : fclose Referenced in libmysqlclient.a(my_fopen_ipo.o) IPO Warning: unresolved : gethostbyname_r Referenced in libmysqlclient.a(my_gethostbyname_ipo.o) IPO Warning: unresolved : __lxstat64 Referenced in libmysqlclient.a(my_symlink_ipo.o) IPO Warning: unresolved : realpath Referenced in libmysqlclient.a(my_symlink_ipo.o) libmysql.c(1808): internal error: 0_1345 icc: error: problem during multi-file optimization compilation (code 4) make[2]: *** [mysql] Error 4 make[2]: Leaving directory `/moat-files/mysql-4.0.18/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/moat-files/mysql-4.0.18' make: *** [all] Error 2 any ideas what I'm missing? thanks yonah Best Regards! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
On 25 Mar 2004, at 01:14, Chris Nolan wrote: An ad in one of the Linux magazines I leaf through on occassion showed some pretty noticable improvements (in the order of 20% in some cases), but I'm betting that these were crafted test cases, as we all know that MySQL's two main storage engines are extremely smart when it comes to using discs. Some of our computational codes are twice the speed when they're compiled with icc compared to gcc. It can make that much difference. It's significant enough that we compile everything with icc when possible. However, there are some caveats. Some of the optimisation options available are extremely aggressive, and can break your code in all sorts of wonderful and subtle ways if you're not careful - pointer aliasing being a prime example, and we have found at least one bug in the optimiser (which Intel are working on, since we have a support contract with them). My rule of thumb is, if the code is largely compute-bound, compile it with icc if possible. Otherwise, stick with gcc. If you're IO-bound or memory-bound, choice of compiler doesn't really matter. I've compared perl scripts on perl built with icc 7.1 and gcc 3.2, and the performance difference was negligible. I wouldn't bust a gut over this, if I were you. I suspect MySQL is not CPU-bound, most of the time, and certainly not floating-point bound (which is where icc really shines) Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
Does the where clause contain the order by columns? If not, then you will have a filesort no matter what. On Wed, 24 Mar 2004, Henrik Schröder wrote: Hi all, I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype 1) ASC, (membershiptype 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with tthe calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) /Henrik Schröder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Failed to install Mysql in HP-UX 10.20
Hi, This a error to install Mysql 4.1in HP-UX 10.20 040324 12:01:50 mysqld started Warning: One can only use the --user switch if running as root InnoDB: No valid checkpoint found. InnoDB: If this error appears when you are creating an InnoDB database, InnoDB: the problem may be that during an earlier attempt you managed InnoDB: to create the InnoDB data files, but log file creation failed. InnoDB: If that is the case, please refer to section 3.1 of InnoDB: http://www.innodb.com/ibman.html 040324 12:01:53 Can't init databases 040324 12:01:53 Aborting 040324 12:01:53 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 040324 12:01:53 /var/opt/mysql/mysql-max-4.1.0-alpha-hp-hpux10.20-hppa/bin/mysqld: Shutdown Complete 040324 12:01:53 mysqld ended -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
From: Henrik Schröder [EMAIL PROTECTED] used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. One thing you could do (not very neat IMHO) is to JOIN this query with a table that contains membershiptype and a sortorder. Then you can ORDER BY the sortorder of that table... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior, Table Level Permission
Udbhav Shah [EMAIL PROTECTED] wrote: Dear Victoria Reznichenko, Thanks for reply. Could you tell me from where I can get patch to fix this bug. or I have to use Mysql 4.0/Mysql 5.0 You can install latest available version, including the bugfix, from the development source tree: http://www.mysql.com/doc/en/Installing_source_tree.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
Let me rephrase my statement. Does the index being used include the order by columns? If not, the optimizer will not be able to perform both row selection and row ordering in the same pass. On Wed, 24 Mar 2004, Victor Pendleton wrote: Does the where clause contain the order by columns? If not, then you will have a filesort no matter what. On Wed, 24 Mar 2004, Henrik Schröder wrote: Hi all, I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype 1) ASC, (membershiptype 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with tthe calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) /Henrik Schröder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --replicate* question
Egor Egorov [EMAIL PROTECTED] wrote: Chua Choon Keng [EMAIL PROTECTED] wrote: What about using binlog-do-db=database_name option? In this case only updates will be logged if the current database is database_name What if I only wish to replicate a few tables of a database? Using binlog-do-db=database_name does limit to this particular database but there is no binlog-ignore-table option that I can use... Is there a workaround? Move tables that you want to replicate to the separate database and use binlog-do-db option. Take a look also at SET SQL_BIN_LOG command: http://www.mysql.com/doc/en/SET_SQL_LOG_BIN.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
On Wed, 24 Mar 2004 15:47:46 +0100, Henrik Schröder wrote: I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0- 1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype 1) ASC, (membershiptype 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with the calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) There may be a cleaner method but could you add a field that's used for sorting so that the sorted field has a value of 0 where membership type is 1, 1 where it is 2, and 2 where it is 0? You could then ORDER by this field but use the value from membershiptype for your other purposes. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installation de mysql
i have download the rpm MySQL-3.23.58-1.i386.rpm, and i install it without any problems, but when i wont to use Snort en mode NIDS, i use output mysql in the snort .conf and with this command : snort -dev -l ./log -h @ip it gaves me this error: Error:if this build of snort was obtained as a binary distribution,then check for alternate builds that contains the necessary 'mysql' support so i want to know were is the problem and where can i find the source for mysql? please answer me as soon as possible From: Bernard Clement [EMAIL PROTECTED] To: sanaâ Aitouchen [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: Re: installation de mysql Date: Wed, 24 Mar 2004 07:03:04 -0500 Bonjour, For the benefit of the group I will translate the French part of my text :-) Le tarball que vous avez telecharge est en fait le binaire executable de MySQL pas les sources. Donc vous n'avez pas a executer configure sur celui-ci. The tarball that you have downloaded is in fact the eexcutable binary of MySQL not the sources. Therefore, you do not have to execute configure on it. Si vous avez acces au CD de distribution de RedHat je vous recommanderais d'installer MySQL a partir du RPM. Si vous n'avez pas acces a ceux-ci telecharger le RPM pour RedHat 9. Il est en effet beaucoup plus facile d'installer MySQL a partir des RPM. If you have access to the distribution CDs of RedHat I recommend you to install MySQL from the RPM. If you do not have access to those, you should download the RPM for RedHat 9. In effect it is a lot more easier to install MySQL from the RPM. Salutations, Bernard On Wednesday 24 March 2004 05:01, sanaâ Aitouchen wrote: j'ai le red hat 9 et j'ai télécharger mysql-3.23.58-pc-linux-i686.tar.gz j'ai crée le repertoir /usr/local/src et j'ai fais gunzip mysql-3.23.58-pc-linux-i686.tar.gz tar xvf mysql-3.23.58-pc-linux-i686.tar.gz cd mysql-3.23.58-pc-linux-i686 ./configure --prefix=/usr/local/mysql mais ca donnée l'erreur suivante: NOTE: This is a MySQL binary distribution. It's ready to run, you don't need to configure it! To help you a bit, I am now going to create the needed MySQL databasesand start the MySQL server for you. If you run into any trouble, please consult the MySQL manual, that you can find in the Docs directory. Installing all prepared tables 040324 9:51:43 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.serverto the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h iamintrusion password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should runthe ./bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can start the MySQL daemon with: cd . ; ./bin/safe_mysqld You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting the mysqld server. You can test that it is up and running with the command: ./bin/mysqladmin version [EMAIL PROTECTED] mysql-3.23.58-pc-linux-i686]# Starting mysqld daemon with databases from /usr/local/src/mysql-3.23.58-pc-linux-i686/data 040324 09:51:44 mysqld ended j'aimerais bien savoir ou se trouve l'erreur et comment je peux le régler merci d'avance _ MSN Messenger : discutez en direct avec vos amis ! http://www.msn.fr/msger/default.asp -- Bernard Clement Info-Electronics Systems Inc. Technical Director phone: 514-421-0767 ext: 231 fax: 514-421-0769 email: [EMAIL PROTECTED] _ MSN Messenger : discutez en direct avec vos amis ! http://www.msn.fr/msger/default.asp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
On Wed, 2004-03-24 at 10:57, Yonah Russ wrote: 8 make[2]: Leaving directory `/moat-files/mysql-4.0.18/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/moat-files/mysql-4.0.18' make: *** [all] Error 2 any ideas what I'm missing? thanks yonah ---8 Something is missing in the path, altough i am guessing here, would you mind describing your set up? where are things installled. -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ||Cel: +58-412-8859934 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
no problem- its a fresh install of RH AS 3u1 (basically the same setup as rh 9)- minimal installation with one or two rpms from the cds added afterwards the mysql source tarball is upacked in /moat-files/mysql-4.0.18/ here is my ld.so.conf if it helps /usr/kerberos/lib /usr/X11R6/lib /usr/local/mysql/lib/mysql /usr/local/lib /usr/local/intel/lib . thanks yonah Victor Medina wrote: On Wed, 2004-03-24 at 10:57, Yonah Russ wrote: 8 make[2]: Leaving directory `/moat-files/mysql-4.0.18/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/moat-files/mysql-4.0.18' make: *** [all] Error 2 any ideas what I'm missing? thanks yonah ---8 Something is missing in the path, altough i am guessing here, would you mind describing your set up? where are things installled. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to optimize ugly order by?
I don't think it does right now, but that can always be taken care of later. Assuming that the query uses one index that contains all the where-columns and the orderby-columns, having an ORDER BY that sorts on expressions is still the biggest problem, right? /Henrik -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: den 24 mars 2004 16:22 To: [EMAIL PROTECTED]; Henrik Schröder; '[EMAIL PROTECTED]' Subject: Re: How to optimize ugly order by? Let me rephrase my statement. Does the index being used include the order by columns? If not, the optimizer will not be able to perform both row selection and row ordering in the same pass. On Wed, 24 Mar 2004, Victor Pendleton wrote: Does the where clause contain the order by columns? If not, then you will have a filesort no matter what. On Wed, 24 Mar 2004, Henrik Schröder wrote: Hi all, I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype 1) ASC, (membershiptype 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with tthe calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) /Henrik Schröder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to optimize ugly order by?
Thanks, I'll have to try that to see if it's faster. Adding another column as someone else suggested is too complex in comparison because it forces a bigger code change, and I don't really want that. Changing one SELECT-statement is enough... /Henrik -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: den 24 mars 2004 16:12 To: [EMAIL PROTECTED] Subject: Re: How to optimize ugly order by? From: Henrik Schröder [EMAIL PROTECTED] used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. One thing you could do (not very neat IMHO) is to JOIN this query with a table that contains membershiptype and a sortorder. Then you can ORDER BY the sortorder of that table... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary keys
JOHN MEYER [EMAIL PROTECTED] wrote: So I can do something like SELECT * FROM . $tablename . WHERE _rowid= $id (I'm using PHP and the primary key is a unique integer column in each of the tables). Yes. For example: mysql CREATE TABLE t1(id int NOT NULL PRIMARY KEY); Query OK, 0 rows affected (0.02 sec) mysql INSERT INTO t1 VALUES(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql SELECT * FROM t1 WHERE _rowid=2; ++ | id | ++ | 2 | ++ 1 row in set (0.00 sec) From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Primary keys Date: Wed, 24 Mar 2004 13:28:58 +0200 JOHN MEYER [EMAIL PROTECTED] wrote: Is there a way to do a SQL Select and get a record without specifying the primary key. Example. I have two tables CANDLE and VOTIVES. Can I define a select that says SELECT * FROM TABLE WHERE PRIMARY_KEY=... No, You can't. Only if PRIMARY or UNIQUE KEY consists of one integer column, you can refer to that column as _rowid: http://www.mysql.com/doc/en/CREATE_TABLE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to optimize ugly order by?
You are correct. On Wed, 24 Mar 2004, Henrik Schröder wrote: I don't think it does right now, but that can always be taken care of later. Assuming that the query uses one index that contains all the where-columns and the orderby-columns, having an ORDER BY that sorts on expressions is still the biggest problem, right? /Henrik -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: den 24 mars 2004 16:22 To: [EMAIL PROTECTED]; Henrik Schröder; '[EMAIL PROTECTED]' Subject: Re: How to optimize ugly order by? Let me rephrase my statement. Does the index being used include the order by columns? If not, the optimizer will not be able to perform both row selection and row ordering in the same pass. On Wed, 24 Mar 2004, Victor Pendleton wrote: Does the where clause contain the order by columns? If not, then you will have a filesort no matter what. On Wed, 24 Mar 2004, Henrik Schröder wrote: Hi all, I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype 1) ASC, (membershiptype 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with tthe calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) /Henrik Schröder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
I assume you are just trying to make things more readable. As far as I know, MySQL does not support custom sort orders, except when a field is an enum or a set. Even then, that's not a custom order since your still sorting on the underlying values. You could do something like this for readability: SELECT ...,if(membershiptype=0,3,membershiptype) as sortby ... ORDER BY sortby This just creates a calculation column in the select that changes the value 0 to 3 and then you use the calculation column for the sort. I doubt this will speed things up, but I think it's easier to read. You may want to look into what's involved in using enum or set for the field for the future. On Mar 24, 2004, at 9:47 AM, Henrik Schröder wrote: Hi all, I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype 1) ASC, (membershiptype 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with the calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) /Henrik Schröder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert field from another table
Hi, you could try something like: INSERT INTO articles_multi(article,author,text_ru,text_en ) SELECT e.article,e.authoer,r.text,e.text FROM articles_en e, articles_ru r WHERE e.article = r.article AND e.author = r.article /Johan nullevent wrote: Hello mysql, I have two tables - articles_en and articles_ru. Its fields is 'article', 'author', 'text'. The difference is - field 'text' in articles_en has english text of article and field 'text' in articles_ru has russian text of article. Fields 'article' and 'author' are identical. The tables were filled simultaneously. I want create table articles_multi which will have fields 'article', 'author', 'text_ru', 'text_en' with data from articles_en and articles_ru. How can i do it? - bye, nullevent Wednesday, March 24, 2004 -- Johan Höök, Pythagoras Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index not functioning
I have a series of tables that were created by: CREATE TABLE ifInOctets ( id int(11) NOT NULL default '0', dtime datetime NOT NULL default '-00-00 00:00:00', counter bigint(20) NOT NULL default '0', KEY ifInOctets_idx (dtime) ); When I run a query against any of the tables, the index isn't used. The query syntax is: SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 WHERE id=2809 AND dtimeFROM_UNIXTIME(107397) AND dtime=FROM_UNIXTIME(1076734799) ORDER BY dtime; Running an EXPLAIN of that command gives: ++--+---+--+-+--+-+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+-+- + | ifInOctets_137 | ALL | dtime | NULL |NULL | NULL | 9279150 | Using where; Using filesort | ++--+---+--+-+--+-+- + 1 row in set (0.00 sec) I'm running on a Sun E250 w/RAID running Solaris 8. I'm running MySQL 4.0.16 precompiled Solaris binary. My my.cnf is essentially the my-huge.cnf file. Other people are running this application (RTG - http://rtg.sourceforge.net) on various platforms and MySQL versions and ARE NOT having this problem. I've run ANALYZE and OPTIMIZE against the tables with no effect. I've dropped the indexes and recreated them with no effect. I've done everything I can think of with no effect. I am now officially stumped. Does anyone have any suggestions on what is going on and how I can fix it? Any help would be greatly appreciated. Thanks, Jack Jack Coxen IP Network Engineer TelCove 712 North Main Street Coudersport, PA 16915 814-260-2705
Re: Index not functioning
The optimizer is informing you that `it` believes a table scan is more efficient than using an index due to the resultset size. On Wed, 24 Mar 2004, Jack Coxen wrote: I have a series of tables that were created by: CREATE TABLE ifInOctets ( id int(11) NOT NULL default '0', dtime datetime NOT NULL default '-00-00 00:00:00', counter bigint(20) NOT NULL default '0', KEY ifInOctets_idx (dtime) ); When I run a query against any of the tables, the index isn't used. The query syntax is: SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 WHERE id=2809 AND dtimeFROM_UNIXTIME(107397) AND dtime=FROM_UNIXTIME(1076734799) ORDER BY dtime; Running an EXPLAIN of that command gives: ++--+---+--+-+--+--- --+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+--- --+- + | ifInOctets_137 | ALL | dtime | NULL |NULL | NULL | 9279150 | Using where; Using filesort | ++--+---+--+-+--+--- --+- + 1 row in set (0.00 sec) I'm running on a Sun E250 w/RAID running Solaris 8. I'm running MySQL 4.0.16 precompiled Solaris binary. My my.cnf is essentially the my-huge.cnf file. Other people are running this application (RTG - http://rtg.sourceforge.net) on various platforms and MySQL versions and ARE NOT having this problem. I've run ANALYZE and OPTIMIZE against the tables with no effect. I've dropped the indexes and recreated them with no effect. I've done everything I can think of with no effect. I am now officially stumped. Does anyone have any suggestions on what is going on and how I can fix it? Any help would be greatly appreciated. Thanks, Jack Jack Coxen IP Network Engineer TelCove 712 North Main Street Coudersport, PA 16915 814-260-2705 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Building super-smack on freebsd 4.9
Not sure if this is the place to post this but...
I'm trying to build super-smack-1.2 on freebsd 4.9 w/ MySQL 4.0.18 and am
running into the following problems. Below are configure, make and gmake
output:
[EMAIL PROTECTED] /usr/local/src/build/super-smack-1.2$ ./configure --with-mysql
creating cache ./config.cache
checking for a BSD compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking whether make sets ${MAKE}... yes
checking for working aclocal... found
checking for working autoconf... found
checking for working automake... found
checking for working autoheader... found
checking for working makeinfo... found
checking for sh... /bin/sh
checking for gcc... gcc
checking whether the C compiler (gcc ) works... yes
checking whether the C compiler (gcc ) is a cross-compiler... no
checking whether we are using GNU C... yes
checking whether gcc accepts -g... yes
checking for c++... c++
checking whether the C++ compiler (c++ ) works... yes
checking whether the C++ compiler (c++ ) is a cross-compiler... no
checking whether we are using GNU C++... yes
checking whether c++ accepts -g... yes
checking how to run the C preprocessor... gcc -E
checking for a BSD compatible install... /usr/bin/install -c
checking whether ln -s works... yes
checking for flex... flex
checking for flex... (cached) flex
checking for yywrap in -lfl... yes
checking lex output file root... lex.yy
checking whether yytext is a pointer... yes
checking for bison... no
checking for byacc... byacc
checking for compress in -lz... yes
checking for crypt in -lcrypt... yes
checking for crypt... yes
checking for libmysqlclient...
checking for mysql_real_connect in -lmysqlclient... yes
checking for mysql_real_connect in -lmysqlclient... (cached) yes
checking for mysql.h...
checking for ANSI C header files... yes
checking for sys/time.h... yes
checking for unistd.h... yes
checking whether time.h and sys/time.h may both be included... yes
checking return type of signal handlers... void
checking for gettimeofday... yes
checking for strerror... yes
updating cache ./config.cache
creating ./config.status
creating Makefile
creating src/Makefile
creating config.h
Building with the following options:
MySQL Support. yes
PostgreSQL Support no
Oracle Support no
If this is not what you intended, please re-run configure.
Thanks for using super-smack!
[EMAIL PROTECTED] /usr/local/src/build/super-smack-1.2$ gmake
gmake all-recursive
gmake[1]: Entering directory `/usr/local/src/build/super-smack-1.2'
Making all in src
gmake[2]: Entering directory `/usr/local/src/build/super-smack-1.2/src'
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include -g -O2 -c super-smack.cc
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include -g -O2 -c client.cc
client.cc: In method `void Client::thread_sync()':
client.cc:347: `ERESTART' undeclared (first use this function)
client.cc:347: (Each undeclared identifier is reported only once
client.cc:347: for each function it appears in.)
gmake[2]: *** [client.o] Error 1
gmake[2]: Leaving directory `/usr/local/src/build/super-smack-1.2/src'
gmake[1]: *** [all-recursive] Error 1
gmake[1]: Leaving directory `/usr/local/src/build/super-smack-1.2'
gmake: *** [all-recursive-am] Error 2
[EMAIL PROTECTED] /usr/local/src/build/super-smack-1.2$ make
make all-recursive
Making all in src
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include-g -O2 -c client.cc
client.cc: In method `void Client::thread_sync()':
client.cc:347: `ERESTART' undeclared (first use this function)
client.cc:347: (Each undeclared identifier is reported only once
client.cc:347: for each function it appears in.)
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2/src.
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2.
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2.
Any ideas? Thanks!
Max
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
URGENT: Trying to recover MySQL DB on OSX 10.3.3
Hello, I am urgently trying to restore a mysql database that was corrupted when upgrading from OSX 10.3.2 to 10.3.3. I have tried downloading MySQL 4.0.14 and coping the original data files for the db into the appropriate data directory, but the datetime fields are still corrupted. Does anyone know if this data is recoverable? Any help would be greatly appreciated. Marc Boorshtein -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using old frm files
Dan, Try the following (0) Make sure you have the .frm files backed up (1) If you have foo.frm, delete foo.* from the data directory, then create a new MyISAM table. Doesn't matter what's in it. E.g., create table foo (x int). (2) Copy your saved .frm file back instead of the one just created. (3) If the table you lost was InnoDB, change the 4th byte of the file from hex 0C to hex 09. (This makes it look like a MyISAM .frm) (4) You should be able to run show create table foo from the MySQL client. HTH Bill Easton Lexpar Date: Tue, 23 Mar 2004 11:04:49 -0500 (EST) From: dan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: using old frm files we had a database blown away (oops) and we still have the old .frm files, is there any way to get this structure back? is there a way to view the contents of the .frm files to see how the tables/indexes were setup? I can't find anything in the docs, faqs, etc. Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
Great! Thanks for the help. Here is some more info, in case it's helpful- these are all the compile warnings from the make: my_thr_init.c(67): warning #266: function declared implicitly pthread_mutexattr_setkind_np(my_fast_mutexattr,PTHREAD_MUTEX_ADAPTIVE_NP); ^ ... icc: Command line warning: ignoring unknown option '-fno-implicit-templates' sql_string.h(20): warning #161: unrecognized #pragma #pragma interface /* gcc class implementation */ ^ icc: Command line warning: ignoring unknown option '-fno-implicit-templates' icc: Command line warning: ignoring unknown option '-fno-implicit-templates' sql_string.cc(20): warning #161: unrecognized #pragma #pragma implementation// gcc: Class implementation ^ sql_string.h(20): warning #161: unrecognized #pragma #pragma interface /* gcc class implementation */ ^ icc: Command line warning: ignoring unknown option '-fno-implicit-templates' icc: Command line warning: ignoring unknown option '-fno-implicit-templates' the rest I've posted already. thanks again yonah Victor Medina wrote: Hi! OK, i have installed a fresh RH9 (i had the cd's here, altough i use a SuSE 9 and Debian). I downloaded a fresh copy of mysql 4.0.18 and 4.1 (just to try). I installed a complete develompent environment (gcc, bison, yacc, m4, etc etc) and it compiles cleanly, again; with gcc, I will download the icc just to try =) Let's see what happens! =) Best Regards! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL statement
Is their a way of using insert into newtablename select * from oldtablename where ID=xxx But with an aditional column in newtablename V!nay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Totals
I need to get totals for the data in my database. I stumbled upon the WITH ROLLUP modifier (http://www.mysql.com/doc/en/GROUP-BY-Modifiers.html) and it appears to be exactly what I'm looking for, except for the fact that when I try to use it I get the following message: This version of MySQL doesn't yet support 'ROLLUP' The version of mySQL I have installed on this machine is 4.0.18-standard. Is there something I need to install/configure to get this modifier to work properly, or is there another way to get the totals I want. Thanks, Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index not functioning
Is there a way of changing what it believes? The resultset size is only a few thousand lines. And, btw, why does it believe that a table scan is more efficient than using an index? Jack -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 12:24 PM To: Jack Coxen; MySQL List (E-mail) Subject: Re: Index not functioning The optimizer is informing you that `it` believes a table scan is more efficient than using an index due to the resultset size. On Wed, 24 Mar 2004, Jack Coxen wrote: I have a series of tables that were created by: CREATE TABLE ifInOctets ( id int(11) NOT NULL default '0', dtime datetime NOT NULL default '-00-00 00:00:00', counter bigint(20) NOT NULL default '0', KEY ifInOctets_idx (dtime) ); When I run a query against any of the tables, the index isn't used. The query syntax is: SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 WHERE id=2809 AND dtimeFROM_UNIXTIME(107397) AND dtime=FROM_UNIXTIME(1076734799) ORDER BY dtime; Running an EXPLAIN of that command gives: ++--+---+--+-+--+--- --+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+--- --+- + | ifInOctets_137 | ALL | dtime | NULL |NULL | NULL | 9279150 | Using where; Using filesort | ++--+---+--+-+--+--- --+- + 1 row in set (0.00 sec) I'm running on a Sun E250 w/RAID running Solaris 8. I'm running MySQL 4.0.16 precompiled Solaris binary. My my.cnf is essentially the my-huge.cnf file. Other people are running this application (RTG - http://rtg.sourceforge.net) on various platforms and MySQL versions and ARE NOT having this problem. I've run ANALYZE and OPTIMIZE against the tables with no effect. I've dropped the indexes and recreated them with no effect. I've done everything I can think of with no effect. I am now officially stumped. Does anyone have any suggestions on what is going on and how I can fix it? Any help would be greatly appreciated. Thanks, Jack Jack Coxen IP Network Engineer TelCove 712 North Main Street Coudersport, PA 16915 814-260-2705
Re: Totals
At 10:19 -0800 3/24/04, Craig Gardner wrote: I need to get totals for the data in my database. I stumbled upon the WITH ROLLUP modifier (http://www.mysql.com/doc/en/GROUP-BY-Modifiers.html) and it appears to be exactly what I'm looking for, except for the fact that when I try to use it I get the following message: This version of MySQL doesn't yet support 'ROLLUP' The version of mySQL I have installed on this machine is 4.0.18-standard. Is there something I need to install/configure to get this modifier to work properly, or is there another way to get the totals I want. The first sentence on that page states: As of MySQL 4.1.1, the GROUP BY clause allows a WITH ROLLUP modifier that causes extra rows to be added to the summary output. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installed MySQL but can't connect
I've installed MySQL and started the server with: mysqld_safe I've confirmed the server is running with: ps -ef | grep mysql and mysqladmin status -- I'm trying to connect using: shell mysql -u root -p It says 'command not found' - I find the mysql command location and confirm it is executable - from that location I retry - same thing. - This may sound silly, but how can I confirm that I've installed the client? I ask because I installed from a binary tarball. It was odd how it was one file (referred to as Standard) vs. two files one being the Client and the other Server. So if anyone can help me determine if the Client is installed and/or how to connect, I would be thankful. -:Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index not functioning
Optimizers work on a thirty percent rule. If the resultset will contain thirty percent of the table then a table scan will be performed. If you want to `force` the use of an index you can use the `USE INDEX index-name` syntax. You also should look at your query. You have three criteria in your where clause id=2809 dtimeFROM_UNIXTIME(107397) dtime=FROM_UNIXTIME(1076734799) A compound index containing id and dtime should be created. Can you use a BETWEEN statement? WHERE id = 2809 AND ( dtime BETWEEN start AND stop) ... Also what is the sum of your `` and `=` resultsets? This value may be larger than you expect. On Wed, 24 Mar 2004, Jack Coxen wrote: Is there a way of changing what it believes? The resultset size is only a few thousand lines. And, btw, why does it believe that a table scan is more efficient than using an index? Jack -Original Message- From: Victor Pendleton [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Sent: Wednesday, March 24, 2004 12:24 PM To: Jack Coxen; MySQL List (E-mail) Subject: Re: Index not functioning The optimizer is informing you that `it` believes a table scan is more efficient than using an index due to the resultset size. On Wed, 24 Mar 2004, Jack Coxen wrote: I have a series of tables that were created by: CREATE TABLE ifInOctets ( id int(11) NOT NULL default '0', dtime datetime NOT NULL default '-00-00 00:00:00', counter bigint(20) NOT NULL default '0', KEY ifInOctets_idx (dtime) ); When I run a query against any of the tables, the index isn't used. The query syntax is: SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 WHERE id=2809 AND dtimeFROM_UNIXTIME(107397) AND dtime=FROM_UNIXTIME(1076734799) ORDER BY dtime; Running an EXPLAIN of that command gives: ++--+---+--+-+--+--- --+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+--- --+- + | ifInOctets_137 | ALL | dtime | NULL |NULL | NULL | 9279150 | Using where; Using filesort | ++--+---+--+-+--+--- --+- + 1 row in set (0.00 sec) I'm running on a Sun E250 w/RAID running Solaris 8. I'm running MySQL 4.0.16 precompiled Solaris binary. My my.cnf is essentially the my-huge.cnf file. Other people are running this application (RTG - http://rtg.sourceforge.net http://rtg.sourceforge.net ) on various platforms and MySQL versions and ARE NOT having this problem. I've run ANALYZE and OPTIMIZE against the tables with no effect. I've dropped the indexes and recreated them with no effect. I've done everything I can think of with no effect. I am now officially stumped. Does anyone have any suggestions on what is going on and how I can fix it? Any help would be greatly appreciated. Thanks, Jack Jack Coxen IP Network Engineer TelCove 712 North Main Street Coudersport, PA 16915 814-260-2705 Content-Type: text/html; name=unnamed Content-Transfer-Encoding: quoted-printable Content-Description: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Should there be an entry in the mysql error log?
From: Victor Pendleton [mailto:[EMAIL PROTECTED] This error should be logged. Do you have a file called hostname.err in your directory? Yes I have a file called hostname.err and that error is not in it. From: Paul DuBois [mailto:[EMAIL PROTECTED] The error log is for messages pertaining to problems with the operational state of the server. Normally there's not much in it at all. The error you show above is a problem with a query sent by a client. That type of error message is sent to the client, not logged. the query (see below) seems to be okay. the problem doesn't seem to be with the query, but with the table. UPDATE referenceEntries SET heading = 'Delete', body = '', keywords = '' , dateVerified = NULLWHERE entryID = 168 the error (see below) seems to be the first indication of a corrupt table. So wouldn't that be a server issue and be appropriate to log into the mysql error log? Incorrect key file for table: 'referenceEntries'. Try to repair it The manual makes a reference to this error in Section 14.1.3.1 Corrupted MyISAM Tables... Typical symptoms for a corrupt table is: You get the error Incorrect key file for table: '...'. Try to repair it while selecting data from the table. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installed MySQL but can't connect
At 13:24 -0500 3/24/04, Kevin Carpenter wrote: I've installed MySQL and started the server with: mysqld_safe I've confirmed the server is running with: ps -ef | grep mysql and mysqladmin status --- I'm trying to connect using: shell mysql -u root -p It says 'command not found' - I find the mysql command location and confirm it is executable - from that location I retry - same thing. Your PATH variable might not include ., the current directory. Try this instead while you're in the directory where the mysql program is located: ./mysql -u root -p If that works, then the mysql program is installed correctly, and you should just set your PATH to include the directory where mysql is located: http://www.kitebird.com/mysql-cookbook/path.pdf If that doesn't work, you probably have the mysqladmin program, but not the other clients. See below. -- This may sound silly, but how can I confirm that I've installed the client? I ask because I installed from a binary tarball. It was odd how it was one file (referred to as Standard) vs. two files one being the Client and the other Server. If you installed on Linux using RPM files, you need to install both the server RPM and the client RPM. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index not functioning
I tried using 'USE INDEX' and 'BETWEEN' but it didn't change anything. EXPLAIN SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 USE INDEX (dtime) WHERE id=2809 AND dtime BETWEEN FROM_UNIXTIME(107397) AND FROM_UNIXTIME(1076734799) ORDER BY dtime; ++--+---+--+-+--+-+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+-+- + | ifInOctets_137 | ALL | dtime | NULL |NULL | NULL | 9279150 | Using where; Using filesort | ++--+---+--+-+--+-+- + 1 row in set (0.00 sec) Is my syntax wrong? The resultset size should be around 8640 rows - 5 minute interval data for 30 days - 12 X 24 X 30 = 8640 -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 1:22 PM To: Jack Coxen; MySQL List (E-mail) Subject: RE: Index not functioning Optimizers work on a thirty percent rule. If the resultset will contain thirty percent of the table then a table scan will be performed. If you want to `force` the use of an index you can use the `USE INDEX index-name` syntax. You also should look at your query. You have three criteria in your where clause id=2809 dtimeFROM_UNIXTIME(107397) dtime=FROM_UNIXTIME(1076734799) A compound index containing id and dtime should be created. Can you use a BETWEEN statement? WHERE id = 2809 AND ( dtime BETWEEN start AND stop) ... Also what is the sum of your `` and `=` resultsets? This value may be larger than you expect. On Wed, 24 Mar 2004, Jack Coxen wrote: Is there a way of changing what it believes? The resultset size is only a few thousand lines. And, btw, why does it believe that a table scan is more efficient than using an index? Jack -Original Message- From: Victor Pendleton [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Sent: Wednesday, March 24, 2004 12:24 PM To: Jack Coxen; MySQL List (E-mail) Subject: Re: Index not functioning The optimizer is informing you that `it` believes a table scan is more efficient than using an index due to the resultset size. On Wed, 24 Mar 2004, Jack Coxen wrote: I have a series of tables that were created by: CREATE TABLE ifInOctets ( id int(11) NOT NULL default '0', dtime datetime NOT NULL default '-00-00 00:00:00', counter bigint(20) NOT NULL default '0', KEY ifInOctets_idx (dtime) ); When I run a query against any of the tables, the index isn't used. The query syntax is: SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 WHERE id=2809 AND dtimeFROM_UNIXTIME(107397) AND dtime=FROM_UNIXTIME(1076734799) ORDER BY dtime; Running an EXPLAIN of that command gives: ++--+---+--+-+--+--- --+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+--- --+- + | ifInOctets_137 | ALL | dtime | NULL |NULL | NULL | 9279150 | Using where; Using filesort | ++--+---+--+-+--+--- --+- + 1 row in set (0.00 sec) I'm running on a Sun E250 w/RAID running Solaris 8. I'm running MySQL 4.0.16 precompiled Solaris binary. My my.cnf is essentially the my-huge.cnf file. Other people are running this application (RTG - http://rtg.sourceforge.net http://rtg.sourceforge.net ) on various platforms and MySQL versions and ARE NOT having this problem. I've run ANALYZE and OPTIMIZE against the tables with no effect. I've dropped the indexes and recreated them with no effect. I've done everything I can think of with no effect. I am now officially stumped. Does anyone have any suggestions on what is going on and how I can fix it? Any help would be greatly appreciated. Thanks, Jack Jack Coxen IP Network Engineer TelCove 712 North Main Street Coudersport, PA 16915 814-260-2705 Content-Type: text/html; name=unnamed Content-Transfer-Encoding: quoted-printable Content-Description:
RE: Should there be an entry in the mysql error log?
At 12:40 -0600 3/24/04, Daniel Gaddis wrote: From: Victor Pendleton [mailto:[EMAIL PROTECTED] This error should be logged. Do you have a file called hostname.err in your directory? Yes I have a file called hostname.err and that error is not in it. From: Paul DuBois [mailto:[EMAIL PROTECTED] The error log is for messages pertaining to problems with the operational state of the server. Normally there's not much in it at all. The error you show above is a problem with a query sent by a client. That type of error message is sent to the client, not logged. the query (see below) seems to be okay. the problem doesn't seem to be with the query, but with the table. UPDATE referenceEntries SET heading = 'Delete', body = '', keywords = '' , dateVerified = NULLWHERE entryID = 168 the error (see below) seems to be the first indication of a corrupt table. So wouldn't that be a server issue and be appropriate to log into the mysql error log? I suppose you could consider it such, but on the other hand, it's not the sort of error that prevents the server from running or processing other queries. In any case, logging it to the error log isn't going to help the client know what is going on and that it should attempt to run a table repair operation. Perhaps what you are suggesting is that the message should go to both places? Incorrect key file for table: 'referenceEntries'. Try to repair it The manual makes a reference to this error in Section 14.1.3.1 Corrupted MyISAM Tables... Typical symptoms for a corrupt table is: You get the error Incorrect key file for table: '...'. Try to repair it while selecting data from the table. Thanks, Daniel -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Index not functioning
Hello Jack, Wednesday, March 24, 2004, 6:50:45 PM, you wrote: JC I tried using 'USE INDEX' and 'BETWEEN' but it didn't change anything. JC EXPLAIN SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 JC USE INDEX (dtime) WHERE id=2809 AND dtime BETWEEN FROM_UNIXTIME(107397) JC AND FROM_UNIXTIME(1076734799) ORDER BY dtime; Does your index include both id and dtime in a single index? If not, it probably ought to if the above is a typical query you need to run on this table. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL statement
Very straightforward if the new column is NULLable OR has a deterministic value, e.g. INSERT INTO newtable (col1, col2) SELECT col1, 1 FROM oldtable WHERE ID = xxx Or if NULLable: INSERT INTO newtable (col1) SELECT col1 FROM oldtable WHERE ID = xxx Equally you could populate 'col2' from an expression or similar, calculating the value from data already in oldtable. Is this what you meant? If not, what exactly are you trying to do? Thanks, Matt -Original Message- From: Vinay [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 18:09 To: [EMAIL PROTECTED] Subject: SQL statement Is their a way of using insert into newtablename select * from oldtablename where ID=xxx But with an aditional column in newtablename V!nay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re[2]: Index not functioning
The index is on dtime only. I've done some testing and dual-column indexes using id and dtime are significantly faster. However, I'm not one of the developers of this package - RTG. I have reported the speed increase to them, though. In the meantime, I'm trying to run a standard system so I'm hesitant to modify the index structure. I don't want to run into problems with future revisions. Right now, I just want my existing indexes to work. Jack -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 2:01 PM To: [EMAIL PROTECTED] Subject: Re[2]: Index not functioning Hello Jack, Wednesday, March 24, 2004, 6:50:45 PM, you wrote: JC I tried using 'USE INDEX' and 'BETWEEN' but it didn't change anything. JC EXPLAIN SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 JC USE INDEX (dtime) WHERE id=2809 AND dtime BETWEEN FROM_UNIXTIME(107397) JC AND FROM_UNIXTIME(1076734799) ORDER BY dtime; Does your index include both id and dtime in a single index? If not, it probably ought to if the above is a typical query you need to run on this table. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create_tmp_table_priv - how to grant? - solved
Hi List, Ok, I'm a moron... problem solved: Should have been: grant CREATE TEMPORARY TABLES ... and not grant Create_tmp_table_priv ... Well atleast this is now in the archives for search engines to pick up on... I swear I sat there for 10 minutes thinking Should I clutter the list with what I'm sure is some dumb mistake on my part ... hit send message, 5 seconds later I found the solution. :) Sorry! __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling with icc
Hi! OK, i have installed a fresh RH9 (i had the cd's here, altough i use a SuSE 9 and Debian). I downloaded a fresh copy of mysql 4.0.18 and 4.1 (just to try). I installed a complete develompent environment (gcc, bison, yacc, m4, etc etc) and it compiles cleanly, again; with gcc, I will download the icc just to try =) Let's see what happens! =) Best Regards! -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ||Cel: +58-412-8859934 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
Henrik Schröder wrote:
Hi all,
I have a table of users which contains a lot of information, and also a
column called membershiptype which can either be 0, 1, or 2. This table is
used a lot in various searches, and all results should be ordered by
membership type, but the order should not be 0-1-2, but instead 1-2-0.
Currently, this is achieved like this:
SELECT ... ORDER BY (membershiptype 1) ASC, (membershiptype 2) ASC,
login ASC
...which is rather ugly, and forces MySQL to create a temp table with the
calculated expressions and then re-sort the result using these. Since this
query is used a lot, it would be nice if I could get rid of this. I'm
completely stumped. Any ideas?
No, I did not choose the sort order. No, I can not change the values used.
Yes, it has to be this sort order. :-)
/Henrik Schröder
I would probably fix this by changing your membershiptype column to an ENUM,
like this:
ALTER TABLE yourtable MODIFY membershiptype ENUM('1', '2', '0');
ENUM columns return what's in the quotes in string context, but return the
position number in numeric context. So,
SELECT * FROM yourtable ORDER BY membershiptype;
would display membershiptype as 1, 2, or 0, but sort them in the order you
want (as '1' = 1, '2' = 2, '0' = 3). Also, as you would now be using the
column itself, rather than a function of the column, it is possible for an
index to help.
WARNING: Note, however, that if you do this, you will have to change any
code that sets membershiptype (INSERT or UPDATE) to add quotes, at least in
the case of 0. That is,
INSERT INTO yourtable (membershiptype) VALUES ('0');
instead of
INSERT INTO yourtable (membershiptype) VALUES (0);
This is because, if you assign a number, mysql assumes you want the value in
that position, and position 0 is the special empty string error value. (The
same will happen for 1 and 2, but they're in the right place.)
From your description, this may not apply to your case, but if the values
1, 2, and 0 code for something, you could create an ENUM column with the
descriptive text in each position, ordered as you want. For example, if 1,
2, and 0 mean 'one year', 'two year', and 'expired', then you could create a
new column with ENUM('one year', 'two year', 'expired'), set the new
column's values using membershiptype, drop membershiptype, and rename the
new column as membershiptype. The payoff would be that you could eliminate
code to translate 1, 2, and 0 into text.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on Unixware 2.1.3
Hi, I would like to know the latest version of MySQL that was qualified on Unixware 2.1.3. Thanks, Raghu Udupa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Should there be an entry in the mysql error log?
From: Paul DuBois [mailto:[EMAIL PROTECTED] I suppose you could consider it such, but on the other hand, it's not the sort of error that prevents the server from running or processing other queries. In any case, logging it to the error log isn't going to help the client know what is going on and that it should attempt to run a table repair operation. Perhaps what you are suggesting is that the message should go to both places? Yes having the message go to both places would be nice. As a sys admin type I would like to see when the problem was first noticed before the warning: checking/recovering table entries in the error log. Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
Can you do: SELECT IF(membershiptype = 0, 3, membershiptype) AS s1 FROM... ORDER BY s1 Shouldn't that work? Basically if mtype is 0 return 3 otherwise return the membership type... and sort by that field. lemme know if that works or if I'm dead wrong ;) Henrik Schröder wrote: Hi all, I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype 1) ASC, (membershiptype 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with the calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) /Henrik Schröder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Correlated Subquery Error in MySQL 4.1.1-alpha
Consider the following schema and data: CREATE TABLE person (pid INTEGER, name CHAR(5)); CREATE TABLE phone (pid INTEGER, num CHAR(10)); INSERT INTO PERSON VALUES (1, 'Bob'); INSERT INTO PERSON VALUES (2, 'Jane'); INSERT INTO PHONE VALUES (1, '12345'); INSERT INTO PHONE VALUES (1, '23456'); INSERT INTO PHONE VALUES (2, '34567'); Now consider the following query: mysql SELECT PID, (SELECT COUNT(*) FROM PHONE H WHERE H.pid = P.pid) AS C FROM person P; +--+---+ | PID | C | +--+---+ |1 | 2 | |2 | 0 | +--+---+ 2 rows in set (0.00 sec) Obviously the count for PID=2 is incorrect. If I run the same query with an ORDER BY, I get mysql SELECT PID, (SELECT COUNT(*) FROM PHONE H WHERE H.pid = P.pid) AS C FROM person P ORDER BY pid DESC; +--+---+ | PID | C | +--+---+ |2 | 1 | |1 | 0 | +--+---+ 2 rows in set (0.00 sec) Now PID=1 is wrong. It appears to always mess up the last element. What's up? I ran this on MySQL 4.1.1-alpha under both Linux (RedHat) and Windows with the same results. I start up the server as follows: bin\mysqld-max --standalone --console --ansi --default-table-type=innodb __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Should there be an entry in the mysql error log?
From: Paul DuBois [mailto:[EMAIL PROTECTED] I don't understand that second sentence, but you can submit feature requests at http://bugs.mysql.com/ For example, I would like the error log to look something like... MySQL: ready for connections. Version: '4.0.18-max-nt-log' socket: '' port: 3306 040323 8:46:41 Incorrect key file for table: 'referenceEntries'. Try to repair it attempted query was: UPDATE referenceEntriesSET heading = 'Balanced Budget - Consitution (U.S.) Amendment', body = 'Calling on Congress to convene a constitutional convention to draft a balanced budget amendment (we do not yet have an amendment to ratify) 5/92HCR 31, 65th R.S. 1977 (passed -- pp. 3257-3268, Author: Donaldson, Sponsor: Moore) HCR 40, 65th R.S. 1977 DID NOT PASS HCR 13, 65th 2nd C.S. 1978 (passed -- pp. 41-42 Author: Von Dohlen, Sponsor: Hance) HCR 9, 64th R.S. 1975 DID NOT PASS (pay as you go) HCR 72, 66th R.S. 1979 DID NOT PASS HR 113, 69th R.S. 1985 DID NOT PASS HCR 69, 70th R.S. 1987 DID NOT PASSNone in 71st R.S. 1989 -- nor the 6 special sessions None in 72nd R.S. 1991 -- nor the 3 special sessions None in 73rd R.S. 1993 None in 75th R.S. 1997See also: recent article 89.12.002 for pros and consAmending the Federal Constitution to Require a Balanced Budget (351.7202 N213A)Balanced Budget Amendment to the U.S. Constitution: Impact of Texas (Legislative Study Group, Feb. 23, 1995) L1800.9 L523i 95-03', keywords = 'defecit spending federal', dateVerified = NULL WHERE entryID = 226 040323 8:54:18 Warning: Checking table: './members/referenceentries' 040323 8:54:18 Warning: Recovering table: './members/referenceentries' I will submit a request to http://bugs.mysql.com/ Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index not functioning
Jack you must have a compound index in your table: ALTER Table ifInOctets_137 add INDEX i_id_dtime(id,dtime); In your SELECT statement, change USE INDEX (dtime) to USE INDEX (i_id_dtime) Let us know how it works. David -Original Message- From: Jack Coxen [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 11:51 AM To: '[EMAIL PROTECTED]'; MySQL List (E-mail) Subject: RE: Index not functioning I tried using 'USE INDEX' and 'BETWEEN' but it didn't change anything. EXPLAIN SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 USE INDEX (dtime) WHERE id=2809 AND dtime BETWEEN FROM_UNIXTIME(107397) AND FROM_UNIXTIME(1076734799) ORDER BY dtime; ++--+---+--+-+ --+-+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+ --+-+- + | ifInOctets_137 | ALL | dtime | NULL | NULL | NULL | 9279150 | Using where; Using filesort | ++--+---+--+-+ --+-+- + 1 row in set (0.00 sec) Is my syntax wrong? The resultset size should be around 8640 rows - 5 minute interval data for 30 days - 12 X 24 X 30 = 8640 -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 1:22 PM To: Jack Coxen; MySQL List (E-mail) Subject: RE: Index not functioning Optimizers work on a thirty percent rule. If the resultset will contain thirty percent of the table then a table scan will be performed. If you want to `force` the use of an index you can use the `USE INDEX index-name` syntax. You also should look at your query. You have three criteria in your where clause id=2809 dtimeFROM_UNIXTIME(107397) dtime=FROM_UNIXTIME(1076734799) A compound index containing id and dtime should be created. Can you use a BETWEEN statement? WHERE id = 2809 AND ( dtime BETWEEN start AND stop) ... Also what is the sum of your `` and `=` resultsets? This value may be larger than you expect. On Wed, 24 Mar 2004, Jack Coxen wrote: Is there a way of changing what it believes? The resultset size is only a few thousand lines. And, btw, why does it believe that a table scan is more efficient than using an index? Jack -Original Message- From: Victor Pendleton [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Sent: Wednesday, March 24, 2004 12:24 PM To: Jack Coxen; MySQL List (E-mail) Subject: Re: Index not functioning The optimizer is informing you that `it` believes a table scan is more efficient than using an index due to the resultset size. On Wed, 24 Mar 2004, Jack Coxen wrote: I have a series of tables that were created by: CREATE TABLE ifInOctets ( id int(11) NOT NULL default '0', dtime datetime NOT NULL default '-00-00 00:00:00', counter bigint(20) NOT NULL default '0', KEY ifInOctets_idx (dtime) ); When I run a query against any of the tables, the index isn't used. The query syntax is: SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 WHERE id=2809 AND dtimeFROM_UNIXTIME(107397) AND dtime=FROM_UNIXTIME(1076734799) ORDER BY dtime; Running an EXPLAIN of that command gives: ++--+---+--+-+ --+--- --+- + | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+ --+--- --+- + | ifInOctets_137 | ALL | dtime | NULL | NULL | NULL | 9279150 | Using where; Using filesort | ++--+---+--+-+ --+--- --+- + 1 row in set (0.00 sec) I'm running on a Sun E250 w/RAID running Solaris 8. I'm running MySQL 4.0.16 precompiled Solaris binary. My my.cnf is essentially the my-huge.cnf file. Other people are running this application (RTG - http://rtg.sourceforge.net http://rtg.sourceforge.net ) on various platforms and MySQL versions and ARE NOT having this problem. I've run ANALYZE and OPTIMIZE against the tables with no effect. I've dropped the indexes and recreated them with no effect. I've done everything I can think of with no effect. I am now officially stumped. Does anyone have any suggestions on
Re: Mystifying mysqld memory usage explosion
On 22 Mar 2004, at 18:24, Tim Cutts wrote: Some users' code is causing MySQL's memory use to explode. By the time we reach about 200 simultaneous connections, the MySQL server is using 8GB of virtual memory, and then falls over (the machine is an AlphaServer ES45 with 8 GB of physical memory, and 16 GB of swap, although processes are constrained to a total of 8GB of virtual memory in the OS) The versions of MySQL I have found this behaviour on are 4.0.14 and 4.0.17 Well, a deafening silence from the list. :-) I've got some more information now. I wanted to exclude perl, DBI and whatnot from the list of suspects, so I wrote a client in C. All the client does is connect to the database and sleep for 10 minutes. If I run 100 or so of these simultaneously, the MySQL server still explodes in memory use and crashes. So it's nothing to do with either the SQL that's being run, or the use of perl/DBI clients. It's simply a matter of connection count. Now, here's the kicker: The problem goes away completely if I start the server with --skip-innodb So my question is: are there buffers which are allocated per connection to do with accessing InnoDB tables? My reading of the documentation suggests that all the innodb_ variables refer to global buffers, logs and so on. Or have I missed something? Tim -- Dr Tim Cutts Informatics Systems Group Wellcome Trust Sanger Institute Hinxton, Cambridge, CB10 1SA, UK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Unixware 2.1.3
On Wed, 24 Mar 2004, Raghu Udupa wrote: I would like to know the latest version of MySQL that was qualified on Unixware 2.1.3. The last version I tested was MySQL-3.22.X. It would compile and run. You may be able to get recent version of MySQL to work. You will need to edit configure.in to add some things. There are library problems with UnixWare 7.0.0, 7.0.1 and 7.1.0 that have been fixed. These problems may exist with the old UnixWare 2.1.x libraries as well. There were problems with thread and threaded applications. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
I'm not a big mysql expert but I think mysql has some buffers which it keeps per connection here are some examples (from mysql website): / /If you have complex queries |sort_buffer_size| and |tmp_table_size| are likely to be very important. Values will depend on the query complexity and available resources, but 4Mb and 32Mb, respectively are recommended starting points. / Note: These are per connection values, among |read_buffer_size|, |read_rnd_buffer_size| and some others, meaning that this value might be needed for each connection. So, consider your load and available resource when setting these parameters. For example |sort_buffer_size| is allocated only if MySQL nees to do a sort. Note: be careful not to run out of memory. / I might start up mysql and watch the memory usage as you start dumb clients on at a time - see what the step in memory usage is and match it up against something in your my.cnf good luck yonah Tim Cutts wrote: On 22 Mar 2004, at 18:24, Tim Cutts wrote: Some users' code is causing MySQL's memory use to explode. By the time we reach about 200 simultaneous connections, the MySQL server is using 8GB of virtual memory, and then falls over (the machine is an AlphaServer ES45 with 8 GB of physical memory, and 16 GB of swap, although processes are constrained to a total of 8GB of virtual memory in the OS) The versions of MySQL I have found this behaviour on are 4.0.14 and 4.0.17 Well, a deafening silence from the list. :-) I've got some more information now. I wanted to exclude perl, DBI and whatnot from the list of suspects, so I wrote a client in C. All the client does is connect to the database and sleep for 10 minutes. If I run 100 or so of these simultaneously, the MySQL server still explodes in memory use and crashes. So it's nothing to do with either the SQL that's being run, or the use of perl/DBI clients. It's simply a matter of connection count. Now, here's the kicker: The problem goes away completely if I start the server with --skip-innodb So my question is: are there buffers which are allocated per connection to do with accessing InnoDB tables? My reading of the documentation suggests that all the innodb_ variables refer to global buffers, logs and so on. Or have I missed something? Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1236 corrupt binlog on master
Hello Gurus, Anyone have any ideas as to how I should handle a case of the following error? 040324 21:02:05 Slave I/O thread: connected to master mailto:'[EMAIL PROTECTED]:3306' '[EMAIL PROTECTED]:3306', replication started in log 'saturn2-bin.051' at position 442744915 040324 21:02:05 Error in Log_event::read_log_event(): 'read error', data_len=657471024,event_type=61 040324 21:02:05 Slave SQL thread: I/O error reading event(errno: -1 cur_log-error: 1723122) 040324 21:02:05 Error reading relay log event: Aborting slave SQL thread because of partial event read 040324 21:02:05 Could not parse log event entry, check the master for binlog corruption This may also be a network problem, or just a bug in the master or slave code. 040324 21:02:05 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'satur n2-bin.051' position 442744915 040324 21:02:05 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) 040324 21:02:05 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary log 040324 21:02:05 Slave I/O thread exiting, read up to log 'saturn2-bin.051', position 442744915 Replication seems to have stopped at that point and the slave keeps trying to connect and dies at with that error. I think that the binlog file 'saturn2-bin.051' on master has got courrupted. Is there any way i can start after the point of corruption in the binary file? Thanks, Arvind Arvind Gangal Senior Analyst Programmer address: 4 Buckingham Gate, London SW1E 6JP tel: +44 (0)20 7802 4306 fax: +44 (0)20 7802 9302 mob: 07734938242 email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] internet: http://www.lastminute.com/ http://www.lastminute.com do something lastminute.com This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk
Re: compiling with icc
News- I have compiled mysql 4.1.1-alpha with the icc compiler using the options intel reported in their case study pdf. specifically '-O3 -ip -unroll2' The compilation was pretty ugly and threw up a lot of warnings. I'm benchmarking now and I'll let you know what comes out of it. yonah Yonah Russ wrote: Great! Thanks for the help. Here is some more info, in case it's helpful- these are all the compile warnings from the make: my_thr_init.c(67): warning #266: function declared implicitly pthread_mutexattr_setkind_np(my_fast_mutexattr,PTHREAD_MUTEX_ADAPTIVE_NP); ^ ... icc: Command line warning: ignoring unknown option '-fno-implicit-templates' sql_string.h(20): warning #161: unrecognized #pragma #pragma interface /* gcc class implementation */ ^ icc: Command line warning: ignoring unknown option '-fno-implicit-templates' icc: Command line warning: ignoring unknown option '-fno-implicit-templates' sql_string.cc(20): warning #161: unrecognized #pragma #pragma implementation// gcc: Class implementation ^ sql_string.h(20): warning #161: unrecognized #pragma #pragma interface /* gcc class implementation */ ^ icc: Command line warning: ignoring unknown option '-fno-implicit-templates' icc: Command line warning: ignoring unknown option '-fno-implicit-templates' the rest I've posted already. thanks again yonah Victor Medina wrote: Hi! OK, i have installed a fresh RH9 (i had the cd's here, altough i use a SuSE 9 and Debian). I downloaded a fresh copy of mysql 4.0.18 and 4.1 (just to try). I installed a complete develompent environment (gcc, bison, yacc, m4, etc etc) and it compiles cleanly, again; with gcc, I will download the icc just to try =) Let's see what happens! =) Best Regards! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL on Unixware 2.1.3
Right now, we are planning to install MySQL 4.0.17-standard (latest production version) on Solaris and Linux servers. We may want to access MySQL from unixware 2.1.3 nodes. If we install mySQL 3.22.X on Unixware nodes, will I be able to access MySQL db server running 4.0.17 on Solaris/Linux servers, from Unixware node using mysql tool as well as custom applications written using MySQL api. Thanks, Raghu -Original Message- From: Boyd Lynn Gerber [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 3:57 PM To: Raghu Udupa Cc: [EMAIL PROTECTED] Subject: Re: MySQL on Unixware 2.1.3 On Wed, 24 Mar 2004, Raghu Udupa wrote: I would like to know the latest version of MySQL that was qualified on Unixware 2.1.3. The last version I tested was MySQL-3.22.X. It would compile and run. You may be able to get recent version of MySQL to work. You will need to edit configure.in to add some things. There are library problems with UnixWare 7.0.0, 7.0.1 and 7.1.0 that have been fixed. These problems may exist with the old UnixWare 2.1.x libraries as well. There were problems with thread and threaded applications. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 1042 East Fort Union #135, Midvale Utah 84047 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
out of memory error and update problem
When running mysql from the command line (Putty terminal) and trying to do some updates the updates fail. First the query I am running - mysql update warranty_temp, warranty_old set warranty_old.OwnerName=warranty_temp.OwnerName where warranty_old.WarrantyID between 75 and 100 AND warranty_temp.WarrantyID between 75 and 100; Query OK, 119 rows affected (50.97 sec) Rows matched: 14641 Changed: 119 Warnings: 0 The results of this query are that the OwnerName in record 75 is copied into the OwnerName for records 75 thru 100. That shouldn't be, there should be a differant OwnerName for each row being copied into each row of the target table. Now the table has 119 rows with the same OwnerName. I have also tried - mysql update warranty_old, warranty_temp set warranty_old.OwnerName=warranty_temp.OwnerName where warranty_old.WarrantyID = warranty_temp.WarrantyID; This copied the OwnerName in row 1 into the OwnerName of every row in the table. Seems the only way to get this to work is to update every row individually - mysql update warranty, warranty_old set warranty.OwnerName=warranty_old.OwnerName where warranty.WarrantyID='39' AND warranty_old.WarrantyID='39'; which works, but there are thousands of rows to update. Now the memory error - when trying to run a large update, it fails and the log file contains this message - 040324 14:36:22 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space I don't know what 'ulimit' is. The server has 512 megs of swap space, I opened a second Putty terminal and watched top while running the update statements in another window and MySQL uses 95% of the memory while processing but the swap never gets touched (shows only 40K used). The server has 768megs ram, 600mhz Athlon processor. I just checked the dmesg messages and found this - pid 18729 (mysqld), uid 88 inumber 32818 on /: filesystem full so I ran this - su-2.05b# df -H FilesystemSize Used Avail Capacity Mounted on /dev/ad0s1a 508M 346M 121M74%/ devfs 1.0K 1.0K 0B 100%/dev /dev/ad0s1d14G 2.9G 9.9G22%/usr File system is not full, what's it talking about? What would be the recommended remedies? -- Chip Wiegand Computer Services Simrad, Inc www.simradusa.com [EMAIL PROTECTED] There is no reason anyone would want a computer in their home. --Ken Olson, president, chairman and founder of Digital Equipment Corporation, 1977 (Then why do I have 8? Somebody help me!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query across two databases on the same server
Is there any way to have a single select statement that can do a join across two databases on the same server? Thanks
RE: Query across two databases on the same server
select db1.table.field, db2.table.field where db1.table.someotherfield = db2.table.someotherfield Peter -Original Message- From: Ed Reed [mailto:[EMAIL PROTECTED] Sent: 24 March 2004 23:45 To: [EMAIL PROTECTED] Subject: Query across two databases on the same server Is there any way to have a single select statement that can do a join across two databases on the same server? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL on Unixware 2.1.3
At 16:51 -0500 3/24/04, Raghu Udupa wrote: Right now, we are planning to install MySQL 4.0.17-standard (latest production version) on Solaris and Linux servers. We may want to access MySQL from unixware 2.1.3 nodes. Latest 4.0.x version is 4.0.18. If we install mySQL 3.22.X on Unixware nodes, will I be able to access MySQL db server running 4.0.17 on Solaris/Linux servers, from Unixware node using mysql tool as well as custom applications written using MySQL api. Yes. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL hangs after some days
[EMAIL PROTECTED] wrote: Hello, since I moved to another dedicated server 1-2 months ago I'm experiencing big problems with mysql. Normally the server works fine for a website under heavy load but after a week or so it hangs in a locked table forever and thus preventig other processes to access it. Then I have to shut down the (mysql-)server, kill -9 safe_mysql and another mysql process (probably the one with the table lock) and run myisamchk -r *.MYI, then restart the server. The biggest table has currently 1160768 rows in it - don't know if this matters. The mysql server is busy most of the time doing simple SELECTS and also does many INSERTs and UPDATEs. However the server hangs on a INSERT or UPDATE query. I also had running a query log for a while (also when the server crashed) but it's hard to find useful information in there. Some information about the system: Red Hat 9 (Shrike) Linux 2.4.20-28.9 #1 Thu Dec 18 13:46:42 EST 2003 i686 athlon i386 GNU/Linux mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386) Athlon 1.5Ghz (or so), 512MB RAM HDD 28% used What should I do? I already tried to find an answer asking Google but couldn't find the solution... Try replacing the kernel with the one from kernel.org, replacing the binary with the one from mysql.com, and adding some RAM or swap. All three are good things to do, and one of them might fix the problem. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Building super-smack on freebsd 4.9
[EMAIL PROTECTED] wrote:
Not sure if this is the place to post this but...
I'm trying to build super-smack-1.2 on freebsd 4.9 w/ MySQL 4.0.18 and am
running into the following problems. Below are configure, make and gmake
output:
[EMAIL PROTECTED] /usr/local/src/build/super-smack-1.2$ ./configure --with-mysql
creating cache ./config.cache
checking for a BSD compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking whether make sets ${MAKE}... yes
checking for working aclocal... found
checking for working autoconf... found
checking for working automake... found
checking for working autoheader... found
checking for working makeinfo... found
checking for sh... /bin/sh
checking for gcc... gcc
checking whether the C compiler (gcc ) works... yes
checking whether the C compiler (gcc ) is a cross-compiler... no
checking whether we are using GNU C... yes
checking whether gcc accepts -g... yes
checking for c++... c++
checking whether the C++ compiler (c++ ) works... yes
checking whether the C++ compiler (c++ ) is a cross-compiler... no
checking whether we are using GNU C++... yes
checking whether c++ accepts -g... yes
checking how to run the C preprocessor... gcc -E
checking for a BSD compatible install... /usr/bin/install -c
checking whether ln -s works... yes
checking for flex... flex
checking for flex... (cached) flex
checking for yywrap in -lfl... yes
checking lex output file root... lex.yy
checking whether yytext is a pointer... yes
checking for bison... no
checking for byacc... byacc
checking for compress in -lz... yes
checking for crypt in -lcrypt... yes
checking for crypt... yes
checking for libmysqlclient...
checking for mysql_real_connect in -lmysqlclient... yes
checking for mysql_real_connect in -lmysqlclient... (cached) yes
checking for mysql.h...
checking for ANSI C header files... yes
checking for sys/time.h... yes
checking for unistd.h... yes
checking whether time.h and sys/time.h may both be included... yes
checking return type of signal handlers... void
checking for gettimeofday... yes
checking for strerror... yes
updating cache ./config.cache
creating ./config.status
creating Makefile
creating src/Makefile
creating config.h
Building with the following options:
MySQL Support. yes
PostgreSQL Support no
Oracle Support no
If this is not what you intended, please re-run configure.
Thanks for using super-smack!
[EMAIL PROTECTED] /usr/local/src/build/super-smack-1.2$ gmake
gmake all-recursive
gmake[1]: Entering directory `/usr/local/src/build/super-smack-1.2'
Making all in src
gmake[2]: Entering directory `/usr/local/src/build/super-smack-1.2/src'
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include -g -O2 -c super-smack.cc
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include -g -O2 -c client.cc
client.cc: In method `void Client::thread_sync()':
client.cc:347: `ERESTART' undeclared (first use this function)
client.cc:347: (Each undeclared identifier is reported only once
client.cc:347: for each function it appears in.)
gmake[2]: *** [client.o] Error 1
gmake[2]: Leaving directory `/usr/local/src/build/super-smack-1.2/src'
gmake[1]: *** [all-recursive] Error 1
gmake[1]: Leaving directory `/usr/local/src/build/super-smack-1.2'
gmake: *** [all-recursive-am] Error 2
[EMAIL PROTECTED] /usr/local/src/build/super-smack-1.2$ make
make all-recursive
Making all in src
c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/include-g -O2 -c client.cc
client.cc: In method `void Client::thread_sync()':
client.cc:347: `ERESTART' undeclared (first use this function)
client.cc:347: (Each undeclared identifier is reported only once
client.cc:347: for each function it appears in.)
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2/src.
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2.
*** Error code 1
Stop in /usr/local/src/build/super-smack-1.2.
Hmm.. Looks like Jeremy did not quite fix everything on FreeBSD in version 1.2.
For a hack, change ERESTART to EINTR in client.cc for now until we've figured it
out.
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help query with MySQL 4.0.11
Hi I have problem with a Query. It does not recognize EXISTS. My version is MySQL 4.0.11a-gamma The query is: SELECT U.usu_id, U.usu_login FROM cva_usuario U WHERE NOT EXISTS (SELECT * FROM cva_grupo_usuario GU WHERE GU.usu_id=U usu_id Where GU.gru_id=4) Help please. Juan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Speeding up MySQL server
I have a rather heavily loaded server, which I would like to tweak a little more performance out of. It currently is binlogging although there is no slave yet. Does the process of bin logging take significant resources? It's putting out about 1GB log per day, the IO load on the disks isn't too bad. But I am curious about the internal to MySQL load of logging all that data. -- Jim Richardson http://www.eskimo.com/~warlock All true wisdom is found on T-shirts. signature.asc Description: Digital signature
Re: Help query with MySQL 4.0.11
At 21:44 -0400 3/24/04, WebMaster Informática007 wrote: Hi I have problem with a Query. It does not recognize EXISTS. My version is MySQL 4.0.11a-gamma Subqueries are supported beginning with MySQL 4.1.0. http://www.mysql.com/doc/en/Subqueries.html http://www.mysql.com/doc/en/Nutshell_4.1_features.html http://www.mysql.com/doc/en/News-4.1.x.html The query is: SELECT U.usu_id, U.usu_login FROM cva_usuario U WHERE NOT EXISTS (SELECT * FROM cva_grupo_usuario GU WHERE GU.usu_id=U usu_id Where GU.gru_id=4) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
aide urgente site d'une association que nous n'arrivons plus a reinstaller
J'ai réinstaller le site web de notre association qui a été réalisé avec SPIP 1.6 sur un serveur dédié . J'ai du changer l'emplacement et le nom de la base de donnée et je n'arrive plus a rentrer dans le site. Si je vai a l'adresse suivante : http://www.ligue-echo.org/ecrire/ Je reçois le message d'erreur suivant : Attention : un problème technique (serveur MySQL) empêche l'accès à cette partie du site. Merci de votre compréhension. 1045 Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) J'ai changer le chemin de la base dans le fichier ECRIRE/inc_connect.php3 mais rien n'a changer? Que dois-je faire? Merci pour votre aide et cordiales salutations Luc e-mail : [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speeding up MySQL server
At 17:55 -0800 3/24/04, Jim Richardson wrote: I have a rather heavily loaded server, which I would like to tweak a little more performance out of. It currently is binlogging although there is no slave yet. Does the process of bin logging take significant resources? It's putting out about 1GB log per day, the IO load on the disks isn't too bad. But I am curious about the internal to MySQL load of logging all that data. It costs you about 1 percent in performance: http://www.mysql.com/doc/en/Binary_log.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speeding up MySQL server
On Wed, Mar 24, 2004 at 08:21:15PM -0600, Paul DuBois wrote: At 17:55 -0800 3/24/04, Jim Richardson wrote: I have a rather heavily loaded server, which I would like to tweak a little more performance out of. It currently is binlogging although there is no slave yet. Does the process of bin logging take significant resources? It's putting out about 1GB log per day, the IO load on the disks isn't too bad. But I am curious about the internal to MySQL load of logging all that data. It costs you about 1 percent in performance: http://www.mysql.com/doc/en/Binary_log.html Thanks for the pointer. No big deal then, I'll have to actually do some work to improve the performance of the system :) darn, I was hoping for a quick easy victory. OK, so the system gets about 10million hits/day, and each hit, is acompanied by incrementing a counter in one of the tables. That's where the vast bulk of the writes come from, unfortunately, the table is locked with each write, and although it's quick, it still takes time. Any suggestions on where to look for info on improving this? Kind of a general question I know, but I don't need someone to do my work, just point me to someplace I can crib from :) -- Jim Richardson http://www.eskimo.com/~warlock Balance the budget. Declare politicions a game species and sell hunting stamps. signature.asc Description: Digital signature
counting rows in all tables
Not using perl or php I came up with:
mysqlshow -u user --status db_name | awk -F| '{print $2,$5}' | grep -i pattern
Is there a query to do the same thing?
_
Douglas Denault
[EMAIL PROTECTED]
Voice: 301-469-8766
Fax: 301-469-0601
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I have problem
I install (mysql control center) over the w2k server when I try connect the mysql this message is show [EMAIL PROTECTED]:3306] ERROR 1130: Host '217.219.201.2' is not allowed to connect to this MySQL server” please help me. Thank you - Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time.
Re: mysterious can't connect error message in our logs(2)
Jigal van Hemert wrote:
The continuing saga:
Can't connect to MySQL server on '192.168.13.205' (4)
Our sysadmin has been searching in the sources of the MySQL client and came
up with this:
The errormessage (4) is probably (only place where this error could be
found) raised by this fragment in libmysql.c:
if ((sock = (my_socket) socket(AF_INET,SOCK_STREAM,0)) == SOCKET_ERROR)
{
net-last_errno=CR_IPSOCK_ERROR;
sprintf(net-last_error,ER(net-last_errno),socket_errno);
goto error;
}
According to:
http://www-users.cs.umn.edu/~bentlema/unix/syscalls_and_ipc.html
this is what happens:
1) The MySQL client tries to create a new socket.
2) At that moment the thread is interupted by a signal. Control is passed to
the signal handler.
3) After the signal handler has done his work, control goes back to the
original thread. The socket() function notices that it was interupted and
returns the error-code.
If you look at the sample code in section 8.1 of the url above
for (;;) {
rmask = mask;
nfound = select(FD_SETSIZE, rmask, (fd_set *)0, (fd_set *)0,
timeout);
if (nfound 0) {
if (errno == EINTR) {
printf(interrupted system call\n);
continue;
}
/* something is very wrong! */
perror(select);
exit(1);
}
(...)
The client should try again if the EINTR error was returned.
Did we do something wrong in the configuration of the server or is this a
tiny bug?
Regards, Jigal.
It's a bug. I would recommend to patch libmysql.c for now until MySQL developers
fix it.
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another Performance query
A Z wrote: here we go: explain select * from properties where reference like '%2332' The above query cannot use the index on reference in either MyISAM or InnoDB case. However, with MyISAM it will hurt less because you are scanning a plain data file instead of a B-tree. Suggestion for a workaround - add a column rev_reference with a key on it, update t1 set rev_reference = reverse(reference), and then do where rev_reference like '2332%'; -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help query with MySQL 4.0.11
Paul DuBois wrote: At 21:44 -0400 3/24/04, WebMaster Informática007 wrote: Hi I have problem with a Query. It does not recognize EXISTS. My version is MySQL 4.0.11a-gamma Subqueries are supported beginning with MySQL 4.1.0. http://www.mysql.com/doc/en/Subqueries.html http://www.mysql.com/doc/en/Nutshell_4.1_features.html http://www.mysql.com/doc/en/News-4.1.x.html The query is: SELECT U.usu_id, U.usu_login FROM cva_usuario U WHERE NOT EXISTS (SELECT * FROM cva_grupo_usuario GU WHERE GU.usu_id=U usu_id Where GU.gru_id=4) And if you don't want to upgrade, you can rewrite your query with a join instead of a subquery. SELECT U.usu_id, U.usu_login FROM cva_usuario U LEFT JOIN cva_grupo_usuario GU ON GU.usu_id=U.usu_id AND GU.gru_id=4 WHERE GU.usu.id IS NULL See http://www.mysql.com/doc/en/Rewriting_subqueries.html Didn't you just ask this question yesterday? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication errors...
Stanton, Brian wrote: Shortly after the MySQL 4.0 line went to production, I upgraded to mysql 4.0.12. Since then my slave has been getting corrupted tables 2 to 3 times every month. I've also seen this problem in mysql 4.0.13. When I run a check table on the table in question it gives the following results: +-+---+--+-- ---+ | Table | Op| Msg_type | Msg_text | +-+---+--+-- ---+ | database.table | check | warning | Table is marked as crashed | | database.table | check | warning | 2 clients is using or hasn't closed the table properly | | database.table | check | warning | Not used space is supposed to be: 526688 but is: 522768 | | database.table | check | error| record delete-link-chain corrupted | | database.table | check | error| Corrupt | +-+---+--+-- ---+ 5 rows in set (0.01 sec) It repairs just fine and then replication continues, but I never ran into this issue in the 3.23.xx line. Has anyone else been seeing this problem? Has it been fixed in a later 4.0.x version? ERROR: 1030 Got error 127 from table handler 040207 3:44:03 Slave: error 'Got error 127 from table handler' on query ... 040207 3:44:03 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log ... I've seen it on my systems. The problem is a bug in DELETE in 4.0 that was fixed in 4.0.18. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is Frequency of Master Binlog Dump to Slave
John McCaskey wrote: The master pushes data to the slave as soon as it has executed the query itself. It is not a periodic push, but an asyncrounous push as soon as data is ready to be sent. So the gap would only be as great as the latency between your two servers. If the servers are disconnected or unable to communicate for any reason you need to be careful about your timeout values and connection retry values or they may not attempt to reconnect for a while and then there will be a datagap until they attempt to reconnect. To clear up the terminology confusion - there is no push. The slave connects and asks the master to give it a dump of its update log. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql as cluster service, failover causes broken replication
Matt Sturtz wrote: Hello-- We're using Red Hat's cluster manager (RH AS 2.1, MySQL 4.0.16 RPM). Due to a problem within the cluster software that we're working on with Red Hat, the cluster fails over from one node to the other sometimes when it shouldn't (one node will reboot, services will fail over-- at this point we think it's probably related to IO on the shared quorum partitions). When service is restored some seconds later, the slaves won't start replicating from the newly created binary-log, instead continuing to read from the previous one (IE db-bin.002 is created when MySQL is restarted, but the slaves keep reading from the old file, db-bin.001). The only fix seems to be CHANGE MASTER TO..., which seems somewhat error prone. Anybody else running MySQL in this type of environment have any words of wisdom? Thanks in advance for any info... They should keep reading from the old one until they catch up. Do they fail to roll over to the next one after finishing the old one? If yes, it would be a bug. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mystifying mysqld memory usage explosion
Tim Cutts wrote: On 22 Mar 2004, at 18:24, Tim Cutts wrote: Some users' code is causing MySQL's memory use to explode. By the time we reach about 200 simultaneous connections, the MySQL server is using 8GB of virtual memory, and then falls over (the machine is an AlphaServer ES45 with 8 GB of physical memory, and 16 GB of swap, although processes are constrained to a total of 8GB of virtual memory in the OS) The versions of MySQL I have found this behaviour on are 4.0.14 and 4.0.17 Well, a deafening silence from the list. :-) I've got some more information now. I wanted to exclude perl, DBI and whatnot from the list of suspects, so I wrote a client in C. All the client does is connect to the database and sleep for 10 minutes. If I run 100 or so of these simultaneously, the MySQL server still explodes in memory use and crashes. So it's nothing to do with either the SQL that's being run, or the use of perl/DBI clients. It's simply a matter of connection count. Now, here's the kicker: The problem goes away completely if I start the server with --skip-innodb So my question is: are there buffers which are allocated per connection to do with accessing InnoDB tables? My reading of the documentation suggests that all the innodb_ variables refer to global buffers, logs and so on. Or have I missed something? Tim: Innodb to my knowledge does not allocate very much locally per thread, and should not allocate anything at all if you are not doing any queries. Based on the test results you have reported, I would put your libc as the primary suspect, and the next one would be bad build/compiler bugs. I would suppose that --skip-innodb just changes some memory allocation patters on startup, which possibly avoid triggering the bug. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
