InnoDB weird thing!!
Hi, I wanna ask about InnoDB... I've been trying InnoDB for sometimes and I found out one thing weird that is : When I make an InnoDB tables and filled it with some records ..it took about 2 Mb of file's capacities.. but when I delete all the records... the file's capabilities just still 2 Mb... If I'm not wrong, Micro Access has such problem too but we can use compact database to refresh it... So how to compact innoDB databases??? By regard; Nicholas Kho
Re: MSSQL to MYSQL
Matt and David, thanks for your help. Cheers from Argentina, Rodrigo Matt Chatterley wrote: Yep. Theres no reason at all why this sort of thing won't work for MSSQL too. Use SQL Enterprise Manager to generate a create script for all objects in the database, and also tell it to script referential integrity (FKs, etc). Then add anything MySQL specific, such as Type=InnoDB (which you will need for transactions/FKs, although I believe BDB works as well?). The only possible problem you will run into is with any code that is embedded into the database - stored procedures shouldn't be too bad, as the syntax in MySQL is fairly similar, you'll just end up changing some function names and tweaking (unless you have very complicated MS SPs). Remember that there are no table variables in MySQL, and that the syntax to create a temporary table is 'create temporary table xyz' not 'create #xyz'. Views of course, are a different matter. In terms of the database structure itself, without embedded code though, it should work perfectly... Cheers, Matt -Original Message- From: David Carlos Brunstein [mailto:[EMAIL PROTECTED] Sent: 11 April 2004 05:23 To: 'Rodrigo Galindez' Cc: [EMAIL PROTECTED] Subject: RE: MSSQL to MYSQL Hi Rodrigo. I'm facing a similar task but from Informix to MySQL. What I do is: 1. Obtain a SQL script to create the logical database (an Informix tool give it). You can use Erwin, with reverse engineer and the save the script. 2. Add the Type=INNODB clause for each CREATE sentence. 3. Divide the scritp into tow: one for the tables creation (with its primary key) (CreateDB.sql) and another one for the alter tables to create the foreing keys (AlterDB.sql). 4. Create a script for loading data from TXT files. First you have to save every table data from SQL Server into TXT files, then load them into MySQL tables (LoadDB.sql). 5. Run the AlterDB.sql script (step 3). It works fine to me. Regards, David. == David Carlos Brunstein System Analyst / Software Developer Buenos Aires, Argentina Mail to: David _ Brunstein @ Yahoo . Com . ar IM: DavidBrunstein @ Hotmail . Com -Original Message- From: Rodrigo Galindez [mailto:[EMAIL PROTECTED] Sent: Saturday, April 10, 2004 3:38 AM To: [EMAIL PROTECTED] Subject: MSSQL to MYSQL Hello list, I have to move a database in MSSQL to MYSQL, with the table structures and all the respective data. I tested one product to do this, SQLyog, and it works fine, except for some little problems with indexes and primary/secondary keys. I want to know if anyone have been dealing with the same problem to recommend me some suggestions/tips/tricks. Do you know another program/script/ways to do this migration ? I want to migrate everything from the original MSSQL database, like indexes, relationships, and so on. Can you guys recommend me some actions or tips to take ? Thanks in advance, -- Rodrigo Galindez Information Management Assistant Center for Human Rights and Environment (CEDHA) Gral Paz 186 10 A 5000 - Cordoba - Argentina Tel/fax 54-351-4256278 [EMAIL PROTECTED] www.cedha.org.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling mysql with intel icc
Problem description: CFLAGS=-O3 -unroll2 -ip -mp -no-gcc -restrict CC=icc CXX=icc CXXFLAGS=-O3 -unroll2 -ip -mp -no-gcc -restrict ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysqladm --without-debug --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --disable-shared --localstatedir=/home/mysqladm/data --enable-assembler The fun stops right away with following message: checking for gcc... icc checking for C compiler default output... configure: error: C compiler cannot create executables See `config.log' for more details. What did config.log have in it? This is what config log say: ## - ## ## Output files. ## ## - ## AVAILABLE_LANGUAGES_ERRORS_RULES='./ac_available_languages_fragment' ## --- ## ## confdefs.h. ## ## --- ## #define DOT_FRM_VERSION 6 #define MACHINE_TYPE i686 #define PACKAGE mysql #define PACKAGE_BUGREPORT #define PACKAGE_NAME #define PACKAGE_STRING #define PACKAGE_TARNAME #define PACKAGE_VERSION #define PROTOCOL_VERSION 10 #define SYSTEM_TYPE pc-linux #define VERSION 4.0.18 configure: exit 77 andy ideas? Andy _ Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P! Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a problem
I have a webserver on Mandrake9 and Mysql3.23.56,php4.3.x,apache1.3.x is running on it. i wanted to transfer this server another server(FreeBSD4.9) completely. To backup of mysql database i used mysqldump as following; mysqldump -u user -p -all mydatabasename mydatabasename.backup no problem After that, On new server I restored with mysql command mysql -u user -p mydatabasename mydatabasename.backup successfully I reach many pages based database well But I got error message some pages based database as below; Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource . What shall i do ? Thanks
Re: Mac Installation
check the permission of /tmp folder. I think mysql user can not access that dir: chmod 777 /tmp Santino At 22:00 + 11-04-2004, Mustafa Hakim wrote: I am trying to install MySql on MAC OS X. After the pkg is installed I am not able to start the MySql server due to the Socket error. ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Now, when I check the /tmp folder I see that there is no file by that name 'mysql.sock'. Has anyone faced a similar problem? Thanks, Mustafa. _ Contact brides grooms FREE! http://www.shaadi.com/ptnr.php?ptnr=hmltag Only on www.shaadi.com. Register now! -- 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: Set Password [SOLVED]
The man page says /usr/bin/mysqladmin -u user -p somepassword Not here it doesn't. My man page says: mysqladmin [-#|--debug= logfile] [-f|--force] [-?|--help] [--character-sets-dir=directory] [-C|--compress] [-h|--host=[#]] [-p[pwd]] [--password=[pwd]] [-P|--port= pnum] [-i|--sleep= sec] [-E|--vertical] [-s|--silent] [-S|--socket= #] [-r|--relative] [-t|--timeout= #] [-u|--user= uname][-v|--verbose][-V|--version] [-w|--wait[=retries]] Which means you would user either: /usr/bin/mysqladmin -uuser -psomepassword or /usr/bin/mysqladmin --user=user --pass=somepassword Both of which work from my command line. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Set Password [SOLVED]
On Mon, Apr 12, 2004 at 11:19:50AM +0100, Russell Horn wrote the following: The man page says /usr/bin/mysqladmin -u user -p somepassword Not here it doesn't. My man page says: mysqladmin [-#|--debug= logfile] [-f|--force] [-?|--help] [--character-sets-dir=directory] [-C|--compress] [-h|--host=[#]] [-p[pwd]] [--password=[pwd]] [-P|--port= pnum] [-i|--sleep= sec] [-E|--vertical] [-s|--silent] [-S|--socket= #] [-r|--relative] [-t|--timeout= #] [-u|--user= uname][-v|--verbose][-V|--version] [-w|--wait[=retries]] Which means you would user either: /usr/bin/mysqladmin -uuser -psomepassword or /usr/bin/mysqladmin --user=user --pass=somepassword Both of which work from my command line. Neither of the above worked for me with the possible exception of the user field as I had not had any problems with it. That's okay though as I'm now having problems with the mysqlaccess command. -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Re: InnoDB weird thing!!
starofframe [EMAIL PROTECTED] wrote: Hi, I wanna ask about InnoDB... I've been trying InnoDB for sometimes and I found out one thing weird that is : When I make an InnoDB tables and filled it with some records ..it took about 2 Mb of file's capacities.. but when I delete all the records... the file's capabilities just still 2 Mb... If I'm not wrong, Micro Access has such problem too but we can use compact database to refresh it... So how to compact innoDB databases??? There is no command in MySQL that shrinks size of the InnoDB data file. You can dump your tables, recreate InnoDB table space and then restore tables. -- 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: Getting mysql to work in Panther
Daniel Lahey [EMAIL PROTECTED] wrote: I have been trying for months to set up mysql on my Mac (Panther) with no success. I can't run mysqladmin as root. I get an access denied error message. If I try to use SHELLmysqladmin -u root password 'password' I get access denied as either root or the regular user. I can't run mysql as root. 'access denied' I can't create databases. I can run mysql as the regular user and mess with the test db, and that's about all I can do. I've followed every instruction I could find online at the command line. I've tried CocoaMySQL. I have practically resorted to prayer. Are there step-by-step instructions anywhere? I would be eternally grateful for any help. What exactly error message did you receive? -- 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: upgrade which client?
Duke, Brian [EMAIL PROTECTED] wrote: I needed to do some subqueries. I had mysql-3.23. Somehow I thought mysql4 included subqueries. Loaded mysql-4.0. Converted everything and got it working except it doesn't do subqueries. Therefore we installed mysql-4.1.1 6 hours of figuring out the permissions finally we got the databases all fixed and now I can actually query the tables I see. Now there is 1 error left that we're a little confused about. We logged into the server locally via: mysql -u root -p It verifies the password and lets us in. We create the users for all the php scripts via: Mysqlgrant all on scratch.* to 'fred'@'localhost' identified by 'password'; And then when we run our php scripts the mysql-server says; Error MySQL said: #1250 - Client does not support authentication protocol requested by server; consider upgrading MySQL client So what client is mysql-server talking about? Is it the php-mysql rpm or the MySQL-client rpm. The server was installed as a standard binary gunzip. We didn't see a mysql-client binary gunzip just the rpm. Our box currently has the following services: MySQL-client-4.1.1-0 php-mysql-4.3.4-2 mysql-standard-4.1.1-alpha-pc-linux-i686 on a redhat9 os running a 2.4.20-30.9 kernel to be fair we installed the mysql-client after we got the first error message. Before that we had no mysql-client and added users via mysqladmin. I don't think I can upgrade the php-mysql any higher yet. Can someone let us know what we need to upgrade? You should compile PHP with MySQL 4.1 client library or run MySQL server with --old-passwords option: http://dev.mysql.com/doc/mysql/en/Password_hashing.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: syntax error on create
warwick mayson [EMAIL PROTECTED] wrote: I am new to mysql and have a create script that when sourced throws a syntax error. The script : CREATE TABLE employee ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, PRIMARY KEY(id) ) TYPE=InnoDB; CREATE TABLE position ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, position_type INTEGER UNSIGNED NULL, PRIMARY KEY(id) ) TYPE=InnoDB; CREATE TABLE company ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NULL, PRIMARY KEY(id) ) TYPE=InnoDB; CREATE TABLE company_position ( company_id INTEGER UNSIGNED NOT NULL, position_id INTEGER UNSIGNED NOT NULL, PRIMARY KEY(company_id, position_id), INDEX company_id_FKIndex1(company_id), INDEX position_id_FKIndex2(position_id), FOREIGN KEY(company_id) REFERENCES company(id) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY(position_id) REFERENCES position(id) ON DELETE NO ACTION ON UPDATE NO ACTION ) TYPE=InnoDB; returns : ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'position(id) ON DELETE NO ACTION ON UPDATE NO ACT Can anyone explain why this is happening ??? MySQL has a function with name POSITION. You should quote table name with backticks (i.e. REFERENCES `position`(`id`) ..) or add space between table name and bracket (i.e. REFERENCES position (id) .. ) -- 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]
sql-bench - wrong socket error
Hi there, I am trying to benchmark a mysql installation. After running: /usr/local/mysql/sql-bench # ./run-all-tests it throws the error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' Thats ok, since the socket is /tmp/mysql.sock as configured in my.cnf I did restart the server before running the test. Has anybody an idea on how to solve this? Thanx, Andy _ Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P! Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems building readline on Solaris 7
Hi- I have not been able to build readline on Solaris 7. No matter what I change in the configuration, it still gives me the same error. Any ideas would be greatly appreciated. Thank you all in advance for the help. Regards, Erek System Configuration: gcc: 3.3.2 gnu make: 3.80 sparc-sun-solaris2.7 ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --libexecdir=/usr/local/mysql/bin --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --disable-shared --with-innodb Here's the error: if gcc -DUNDEF_THREADS_HACK -DHAVE_CONFIG_H -DNO_KILL_INTR -I. -I. -I.. -I./../include -I./.. -I..-O3 -DDBUG_OFF -O3 -fno-omit-frame-pointer -DHAVE_CURSES_H -I/opt/home2/erek/src/mysql-4.0.18/include -DHAVE_RWLOCK_T -MT tilde.o -MD -MP -MF .deps/tilde.Tpo \ -c -o tilde.o `test -f 'tilde.c' || echo './'`tilde.c; \ then mv -f .deps/tilde.Tpo .deps/tilde.Po; \ else rm -f .deps/tilde.Tpo; exit 1; \ fi rm -f libreadline.a false cru libreadline.a readline.o funmap.o keymaps.o vi_mode.o parens.o rltty.o complete.o bind.o isearch.o display.o signals.o util.o kill.o undo.o macro.o input.o callback.o terminal.o xmalloc.o history.o histsearch.o histexpand.o histfile.o nls.o search.o shell.o tilde.o make[2]: *** [libreadline.a] Error 1 make[2]: Leaving directory `/opt/home2/erek/src/mysql-4.0.18/readline' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/opt/home2/erek/src/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: sql-bench - wrong socket error
Hi- ./run-all-tests --socket='/tmp/mysql.sock' from ./run-all-tests --help... Regards, Erek On Mon, Apr 12, 2004 at 02:09:28PM +0200, Walter Andreas wrote: Hi there, I am trying to benchmark a mysql installation. After running: /usr/local/mysql/sql-bench # ./run-all-tests it throws the error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' Thats ok, since the socket is /tmp/mysql.sock as configured in my.cnf I did restart the server before running the test. Has anybody an idea on how to solve this? Thanx, Andy _ Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P! Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157 -- 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: mysql error file
I changed mysql_safe to mysqld_safe and it is still sending the error output to $HOSTNAME.err -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 11:17 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: mysql error file At 10:10 -0400 4/7/04, Luc Foisy wrote: I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? You might want to use [mysqld_safe] rather than [mysql_safe]. -- 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 error file
I hade it misconfigured (wrong param name prior to MySQL 4.0) Doc Quote http://dev.mysql.com/doc/mysql/en/Error_log.html Beginning with MySQL 4.0.10, you can specify where mysqld stores the error log file with the option --log-error[=file_name]. If no file_name value is given, mysqld uses the name `'hostname'.err' and writes the file in the data directory. (Prior to MySQL 4.0.10, the Windows error log name is `mysql.err'.) If you execute FLUSH LOGS the error log will be renamed with a suffix of -old and mysqld creates a new empty log file. In older MySQL versions on Unix, error log handling was done by mysqld_safe which redirected the error file to 'hostname'.err. You could change this filename by specifying a --err-log=filename option to mysqld_safe. End Doc Quote I changed it to what it should be according to the documentation and again it writes to $HOSTNAME.err [mysqld_safe] --err-log=/usr/data/mysql/mysql.err I am restaring MySQL with: /etc/rc.d/init.d/mysql stop /etc/rc.d/init.d/mysql start mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686) RedHat 9.0 What I am seeing with ps -axw 14683 pts/0S 0:00 /bin/sh /usr/bin/safe_mysqld --datadir=/usr/data/mysql --pid-file=/usr/data/mysql/mysql.pid 14712 pts/0S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/usr/data/mysql --user=mysql --pid-file=/usr/data/mysql/mysql.pid -Original Message- From: Luc Foisy Sent: Monday, April 12, 2004 9:12 AM To: Paul DuBois; MYSQL-List (E-mail) Subject: RE: mysql error file I changed mysql_safe to mysqld_safe and it is still sending the error output to $HOSTNAME.err -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 11:17 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: mysql error file At 10:10 -0400 4/7/04, Luc Foisy wrote: I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? You might want to use [mysqld_safe] rather than [mysql_safe]. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Queries per second average
The site that I am working on is experiencing MySQL freeze ups any time after the 'Queries per second average'; seen on the STATUS output; is at 48-50 in value. When the site owner asked the hosting service about this they told him that the MySQL cannot go above that limit. He still has lots of CPU and RAM resources so it makes me wonder why? Is there a limitation to MySQL in this regard or is the host just trying to save on badwidth? ;) Thanks for any insights, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries per second average
I've done hundreds if not thousands of queries per second... I do not see how the server can be an issue unless it's configuration is bare.. And I don't know how much that should affect it if it's a decent server :-/ If there are configuration constraints, it could be disk that's mussing it up. I just don't think 50/sec is a lot... P Dan Johnson [EMAIL PROTECTED] 04/12/2004 01:16 PM To: [EMAIL PROTECTED] cc: Subject:Queries per second average The site that I am working on is experiencing MySQL freeze ups any time after the 'Queries per second average'; seen on the STATUS output; is at 48-50 in value. When the site owner asked the hosting service about this they told him that the MySQL cannot go above that limit. He still has lots of CPU and RAM resources so it makes me wonder why? Is there a limitation to MySQL in this regard or is the host just trying to save on badwidth? ;) Thanks for any insights, 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]
Re: install: config root user fails
I have a similar setup at home, and got the same error. See explanations and fixes below. Douglas Dickinson wrote: warning: complete db server newbie! (I've done plenty of SQL JDBC coding, but never had to care about the server side until now ;-) Installed the binary distribution packages for Mac OS X: mysql-max-4.0.l8.pkg MySQLStartupItem.pkg no problem. But then when trying to configure the root user for the db, cut and paste straight from Chapter 2 of the manual fails: mysql_install_db created users [EMAIL PROTECTED] and [EMAIL PROTECTED] without paswords. The following two mysqladmin commands are meant to set the passwords: $ mysqladmin -u root password asdf Here you connect via the unix socket as [EMAIL PROTECTED] and set a password. Good. $ mysqladmin -u root -h `hostname` password asdf mysqladmin: connect to server at 'quickbeam.local' failed error: 'Host '192.168.1.100' is not allowed to connect to this MySQL server' Here you try to connect via tcp as [EMAIL PROTECTED] to set a password, but it doesn't work. Note that mysql says Host '192.168.1.100' is not allowed to connect It didn't say Host 'quickbeam.local' is not allowed to connect That's the problem -- mysql sees the connection attempt as coming from 192.168.1.100 rather than quickbeam.local. This is because your DNS does not equate that hostname with that IP number, which is to be expected with your setup (router and cable/DSL modem). So, [EMAIL PROTECTED] still has no password, but cannot connect either. Fortunately, you don't really need this user. Please help point me in the right direction, Connect as [EMAIL PROTECTED] with mysql -u root -p You'll be prompted for the [EMAIL PROTECTED] password that you set. At the mysql prompt, enter DELETE FROM mysql.User WHERE Host 'localhost'; FLUSH PRIVILEGES; The first line gets rid of any tcp users, such as [EMAIL PROTECTED] The second line makes the change take effect. Some additional suggestions: Enter GRANT ALL ON test.* to [EMAIL PROTECTED] IDENTIFIED BY 'some_password'; at the mysql prompt to create yourself as a mysql user with full access to the test db. (I'm assuming your Mac username is douglasdd. Change the command as necessary.) If at some point you find that you need root to be able to connect from quickbeam.local via TCP, you can enter GRANT ALL ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'some_password'; at the mysql prompt, replacing some_password with an appropriate password. You should probably read about the mysql privilege system in the manual: http://dev.mysql.com/doc/mysql/en/Privilege_system.html http://dev.mysql.com/doc/mysql/en/User_Account_Management.html DouglasDD Hope that helps. Michael PS - I'm sure that this has come up before, but searching on list.mysql.com is currently broken (DP failed for all queries). TONS OF DETAILS: Mac OS X 10.3.3 (not server edition) on a PowerPC G5 dual 2GHz $ uname -a Darwin quickbeam.local 7.3.0 Darwin Kernel Version 7.3.0: Fri Mar 5 14:22:55 PST 2004; root:xnu/xnu-517.3.15.obj~4/RELEASE_PPC Power Macintosh powerpc $ mysqladmin version mysqladmin Ver 8.40 Distrib 4.0.18, for apple-darwin6.8 on powerpc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.18-max Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 40 min 30 sec Threads: 1 Questions: 5 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 1 Queries per second avg: 0.002 Network: the mac is 192.168.1.100 (behind a LinkSys from the cable modem) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries per second average
I agree with Peter, 50 queries per second is not a MySQL limit. Have you checked the slow query log or the *.err log file to see if anything is being logged or if `bad-performing` queries are causing this bottleneck? Have you checked your variables to see what your `max-connections` variable is? -Original Message- From: Dan Johnson To: [EMAIL PROTECTED] Sent: 4/12/04 8:16 AM Subject: Queries per second average The site that I am working on is experiencing MySQL freeze ups any time after the 'Queries per second average'; seen on the STATUS output; is at 48-50 in value. When the site owner asked the hosting service about this they told him that the MySQL cannot go above that limit. He still has lots of CPU and RAM resources so it makes me wonder why? Is there a limitation to MySQL in this regard or is the host just trying to save on badwidth? ;) Thanks for any insights, 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]
Reinstall Windows.
I need to re-install windows, so I need to re-install mysql. How can I backup my databases so when I reinstall mysql put them to work again?, Thx in advance, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reinstall Windows.
I need to re-install windows, so I need to re-install mysql. How can I backup my databases so when I reinstall mysql put them to work again?, Thx in advance, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reinstall Windows.
You can use mysqldump, then zip up the files. Do not forget to backup your innodb directories if you are using InnoDB tables and grab the my.ini/my.cnf file. Once you have re-installed windows just place the directories/files on the new file system and adjust your my.ini/my.cnf if necessary. Victor -Original Message- From: Alejandro C. Garrammone To: MySQL Mailing List Sent: 4/12/04 9:10 AM Subject: Reinstall Windows. I need to re-install windows, so I need to re-install mysql. How can I backup my databases so when I reinstall mysql put them to work again?, Thx in advance, Alex -- 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: Queries per second average
At 08:16 AM 4/12/2004, you wrote: The site that I am working on is experiencing MySQL freeze ups any time after the 'Queries per second average'; seen on the STATUS output; is at 48-50 in value. When the site owner asked the hosting service about this they told him that the MySQL cannot go above that limit. He still has lots of CPU and RAM resources so it makes me wonder why? Is there a limitation to MySQL in this regard or is the host just trying to save on badwidth? ;) Thanks for any insights, Dan Dan, Is your web application running on a virtual server? If so, the ISP is likely limiting you to 50 queries/second so that you or someone else sharing this MySQL server won't hog the MySQL resources. For example, your ISP may have 10 users sharing the same MySQL database and if each web application gets 50 queries per second, that amounts to 10x50=500 queries per second in total. But if you're paying for a dedicated database server, then your ISP may be illicitly sharing your MySQL server with others because you should be able to get a few hundred queries per second at minimum. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries per second average
Dan Johnson wrote: The site that I am working on is experiencing MySQL freeze ups any time after the 'Queries per second average'; seen on the STATUS output; is at 48-50 in value. When the site owner asked the hosting service about this they told him that the MySQL cannot go above that limit. He still has lots of CPU and RAM resources so it makes me wonder why? Is there a limitation to MySQL in this regard or is the host just trying to save on badwidth? ;) Thanks for any insights, Is it possible that you or your provider has set the MAX_QUERIES_PER_HOUR setting? Check: http://dev.mysql.com/doc/mysql/en/GRANT.html Hope this helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries per second average
Victor Pendleton wrote: I agree with Peter, 50 queries per second is not a MySQL limit. Have you checked the slow query log or the *.err log file to see if anything is being logged or if `bad-performing` queries are causing this bottleneck? Have you checked your variables to see what your `max-connections` variable is? -Original Message- From: Dan Johnson To: [EMAIL PROTECTED] Sent: 4/12/04 8:16 AM Subject: Queries per second average The site that I am working on is experiencing MySQL freeze ups any time after the 'Queries per second average'; seen on the STATUS output; is at 48-50 in value. When the site owner asked the hosting service about this they told him that the MySQL cannot go above that limit. He still has lots of CPU and RAM resources so it makes me wonder why? Is there a limitation to MySQL in this regard or is the host just trying to save on badwidth? ;) Thanks for any insights, Dan Hi Victor and Peter! The connections limit was the first hurdle that I came up against. It had been set to 50 connections. It did not take long for connection errors to appear in the logs. The /etc/my.cnf file now looks as follows: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock set-variable=max_connections=1200 set-variable=table_cache=2400 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Slow queries do not seem to be a problem in relation to the amount of total queries. The database has been running for an hour now with no slow queries. When it is approaching 50 queries per second average it seems the value could have been as high as 2000+; yet the amount of queries would be very large at that point. After inspecting the logs in more detail it seems MySQL thinks that key_buffer_size variable should be increased dramatically. That has been done and will let you know if it is successfull to fiz this limitation. - Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries per second average
Awesome. Hope it works out. P Dan Johnson [EMAIL PROTECTED] 04/12/2004 02:16 PM To: [EMAIL PROTECTED] cc: Subject:Re: Queries per second average Victor Pendleton wrote: I agree with Peter, 50 queries per second is not a MySQL limit. Have you checked the slow query log or the *.err log file to see if anything is being logged or if `bad-performing` queries are causing this bottleneck? Have you checked your variables to see what your `max-connections` variable is? -Original Message- From: Dan Johnson To: [EMAIL PROTECTED] Sent: 4/12/04 8:16 AM Subject: Queries per second average The site that I am working on is experiencing MySQL freeze ups any time after the 'Queries per second average'; seen on the STATUS output; is at 48-50 in value. When the site owner asked the hosting service about this they told him that the MySQL cannot go above that limit. He still has lots of CPU and RAM resources so it makes me wonder why? Is there a limitation to MySQL in this regard or is the host just trying to save on badwidth? ;) Thanks for any insights, Dan Hi Victor and Peter! The connections limit was the first hurdle that I came up against. It had been set to 50 connections. It did not take long for connection errors to appear in the logs. The /etc/my.cnf file now looks as follows: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock set-variable=max_connections=1200 set-variable=table_cache=2400 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Slow queries do not seem to be a problem in relation to the amount of total queries. The database has been running for an hour now with no slow queries. When it is approaching 50 queries per second average it seems the value could have been as high as 2000+; yet the amount of queries would be very large at that point. After inspecting the logs in more detail it seems MySQL thinks that key_buffer_size variable should be increased dramatically. That has been done and will let you know if it is successfull to fiz this limitation. - 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]
RE: Queries per second average
Use caution when modifying your values The following formula should not be greater than your total amount of memory. key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections ... Also allow memory for the Operating system and other applications that may be running on the same machine. Victor -Original Message- From: Dan Johnson To: [EMAIL PROTECTED] Sent: 4/12/04 9:16 AM Subject: Re: Queries per second average Victor Pendleton wrote: I agree with Peter, 50 queries per second is not a MySQL limit. Have you checked the slow query log or the *.err log file to see if anything is being logged or if `bad-performing` queries are causing this bottleneck? Have you checked your variables to see what your `max-connections` variable is? -Original Message- From: Dan Johnson To: [EMAIL PROTECTED] Sent: 4/12/04 8:16 AM Subject: Queries per second average The site that I am working on is experiencing MySQL freeze ups any time after the 'Queries per second average'; seen on the STATUS output; is at 48-50 in value. When the site owner asked the hosting service about this they told him that the MySQL cannot go above that limit. He still has lots of CPU and RAM resources so it makes me wonder why? Is there a limitation to MySQL in this regard or is the host just trying to save on badwidth? ;) Thanks for any insights, Dan Hi Victor and Peter! The connections limit was the first hurdle that I came up against. It had been set to 50 connections. It did not take long for connection errors to appear in the logs. The /etc/my.cnf file now looks as follows: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock set-variable=max_connections=1200 set-variable=table_cache=2400 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Slow queries do not seem to be a problem in relation to the amount of total queries. The database has been running for an hour now with no slow queries. When it is approaching 50 queries per second average it seems the value could have been as high as 2000+; yet the amount of queries would be very large at that point. After inspecting the logs in more detail it seems MySQL thinks that key_buffer_size variable should be increased dramatically. That has been done and will let you know if it is successfull to fiz this limitation. - 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]
Problem
Im havin problem running Mysql through DOS. I´m running or trying to, mysql-4.1.1a-alpha-win, I had no problem running this program in my WinXP computer, but in this one with Win98, after typing almost all commands e.g. mysql -u root nothing happens after, it doesn´t freeze my pc, nothing happens. Thanks in advance Andre
Multiple SELECTs in one query
Hello, all! I am porting my Visual Basic app over from MSDE to MySQL, and things so far are going quite well. I've found most of the gotcha differences in how I need to structure my queries, but I am having trouble with one in particular. In my original code, I could use one query to get a total count of records, a count of records meeing a criteria (Status = Complete), and an average on another field for the records meeting that criteria. It looked like this in code: SQLStr = SELECT DoneCount=(SELECT Count(*) FROM flist WHERE Status = 'Complete'), _ TotalCount=(SELECT Count(*) FROM flist ), _ AvgRenderTime=(SELECT Avg(renderminutes) FROM flist WHERE Status = 'Complete') The resulting SQL query would look something like this: SQLStr = SELECT DoneCount=(SELECT Count(*) FROM tableFLIST WHERE Status = 'Complete'), TotalCount=(SELECT Count(*) FROM tableFLIST), AvgRenderTime=(SELECT Avg(renderminutes) FROM tableFLIST WHERE Status = 'Complete') Now, in MySQL, I get syntax errors in the query - most of them around TotalCount= in this example. In my investigation, I found that I could break the one query apart and execute three calls to get the information I needed, like this: SQLStr = SELECT count(*) as TotalCount FROM flist rs.Open SQLStr totalFrames = rs!totalcount rs.Close SQLStr = SELECT Count(*) AS DoneCount FROM flist WHERE Status = 'Complete' rs.Open SQLStr doneframes = rs!donecount rs.Close SQLStr = SELECT Avg(renderminutes) as AvgRenderTime FROM flist WHERE Status = 'Complete' rs.Open SQLStr rs.Close So now that I've made a long story even longer, my question is simply this - is there a way to execute all three selects within the same query, as I was able to do when my database was MSDE? It seems that it would be more efficient than making three hits on the database when one would suffice. Many thanks for any help you can provide! Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql error file
Note (see ps output) that you are using safe_mysqld, rather than mysqld_safe, as you have mysql 3.23.58. Hence, you need to change [mysqld_safe] to [safe_mysqld] in your my.cnf. See http://dev.mysql.com/doc/mysql/en/mysqld_safe.html. Michael Luc Foisy wrote: I hade it misconfigured (wrong param name prior to MySQL 4.0) Doc Quote http://dev.mysql.com/doc/mysql/en/Error_log.html Beginning with MySQL 4.0.10, you can specify where mysqld stores the error log file with the option --log-error[=file_name]. If no file_name value is given, mysqld uses the name `'hostname'.err' and writes the file in the data directory. (Prior to MySQL 4.0.10, the Windows error log name is `mysql.err'.) If you execute FLUSH LOGS the error log will be renamed with a suffix of -old and mysqld creates a new empty log file. In older MySQL versions on Unix, error log handling was done by mysqld_safe which redirected the error file to 'hostname'.err. You could change this filename by specifying a --err-log=filename option to mysqld_safe. End Doc Quote I changed it to what it should be according to the documentation and again it writes to $HOSTNAME.err [mysqld_safe] --err-log=/usr/data/mysql/mysql.err I am restaring MySQL with: /etc/rc.d/init.d/mysql stop /etc/rc.d/init.d/mysql start mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686) RedHat 9.0 What I am seeing with ps -axw 14683 pts/0S 0:00 /bin/sh /usr/bin/safe_mysqld --datadir=/usr/data/mysql --pid-file=/usr/data/mysql/mysql.pid 14712 pts/0S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/usr/data/mysql --user=mysql --pid-file=/usr/data/mysql/mysql.pid -Original Message- From: Luc Foisy Sent: Monday, April 12, 2004 9:12 AM To: Paul DuBois; MYSQL-List (E-mail) Subject: RE: mysql error file I changed mysql_safe to mysqld_safe and it is still sending the error output to $HOSTNAME.err -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 11:17 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: mysql error file At 10:10 -0400 4/7/04, Luc Foisy wrote: I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? You might want to use [mysqld_safe] rather than [mysql_safe]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup
I use snapback it does the rolling restoration... works awesome it links the files to save space, and had a transfer log, to show how much was transfered Steve Sills Platnum Computers, President http://www.platnum.com [EMAIL PROTECTED] - Original Message - From: Benjamin Arai [EMAIL PROTECTED] To: RCorbet Cc: Steve Sills [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, April 11, 2004 11:15 PM Subject: Re: backup Don't use rsync. Try rdiff-backup, its much more reliable and offers rolling restoration. On Sat, 2004-04-10 at 02:08, Matt W wrote: Hi Steve, You might want to look at FLUSH TABLES WITH READ LOCK. That's a query to run from mysql, but I'm sure you can get it to work in your shell script (you need to maintain the MySQL connection while doing the backup). I don't know much about that, though. I think you just run UNLOCK TABLES when you're finished. Matt - Original Message - From: Steve Sills Sent: Tuesday, April 06, 2004 8:17 PM Subject: backup I want to use rsync to backup my db server, how do i lock all the tables for all the db's to read only so i cando my backup, then unlock them again. It needs to be done from the command line, not the mysql program. Anyone have any ideas? I have looked and couldn't find the answer i was looking before. Its running from a shell script, from my backup machine. Its currently setup to shut down the server, however i don't want to have to do this. Thanks in advance. Steve Sills Platnum Computers, President http://www.platnum.com [EMAIL PROTECTED] Benjamin Arai Araisoft Email: [EMAIL PROTECTED] Website: http://www.araisoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mac Installation
Santino wrote: check the permission of /tmp folder. I think mysql user can not access that dir: I agree. At least a couple of Mac OS X updates improperly restrict permissions on /tmp. chmod 777 /tmp Don't do that. It's not safe. You need the sticky bit set so one user can't clobber another user's files. Instead, run sudo chmod 1777 /tmp Michael Santino At 22:00 + 11-04-2004, Mustafa Hakim wrote: I am trying to install MySql on MAC OS X. After the pkg is installed I am not able to start the MySql server due to the Socket error. ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Now, when I check the /tmp folder I see that there is no file by that name 'mysql.sock'. Has anyone faced a similar problem? Thanks, Mustafa. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple SELECTs in one query
You will need to be using MySQL 4.1.x in order to perform sub-selects. -Original Message- From: Steve Pugh To: [EMAIL PROTECTED] Sent: 4/12/04 11:01 AM Subject: Multiple SELECTs in one query Hello, all! I am porting my Visual Basic app over from MSDE to MySQL, and things so far are going quite well. I've found most of the gotcha differences in how I need to structure my queries, but I am having trouble with one in particular. In my original code, I could use one query to get a total count of records, a count of records meeing a criteria (Status = Complete), and an average on another field for the records meeting that criteria. It looked like this in code: SQLStr = SELECT DoneCount=(SELECT Count(*) FROM flist WHERE Status = 'Complete'), _ TotalCount=(SELECT Count(*) FROM flist ), _ AvgRenderTime=(SELECT Avg(renderminutes) FROM flist WHERE Status = 'Complete') The resulting SQL query would look something like this: SQLStr = SELECT DoneCount=(SELECT Count(*) FROM tableFLIST WHERE Status = 'Complete'), TotalCount=(SELECT Count(*) FROM tableFLIST), AvgRenderTime=(SELECT Avg(renderminutes) FROM tableFLIST WHERE Status = 'Complete') Now, in MySQL, I get syntax errors in the query - most of them around TotalCount= in this example. In my investigation, I found that I could break the one query apart and execute three calls to get the information I needed, like this: SQLStr = SELECT count(*) as TotalCount FROM flist rs.Open SQLStr totalFrames = rs!totalcount rs.Close SQLStr = SELECT Count(*) AS DoneCount FROM flist WHERE Status = 'Complete' rs.Open SQLStr doneframes = rs!donecount rs.Close SQLStr = SELECT Avg(renderminutes) as AvgRenderTime FROM flist WHERE Status = 'Complete' rs.Open SQLStr rs.Close So now that I've made a long story even longer, my question is simply this - is there a way to execute all three selects within the same query, as I was able to do when my database was MSDE? It seems that it would be more efficient than making three hits on the database when one would suffice. Many thanks for any help you can provide! Steve -- 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]: backup
I did get soimething working mysql command setup to lock tables with read lock command.. works very well. Steve Sills Platnum Computers, President http://www.platnum.com [EMAIL PROTECTED] - Original Message - From: Carsten R. Dreesbach [EMAIL PROTECTED] To: RCorbet Cc: Steve Sills [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, April 11, 2004 11:30 PM Subject: Re[2]: backup Hi Matt, if Steve can accept the limitations, mysqlhotcopy might work for him: http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html If not, mysqldump with --add-locks could do it fairly easily, I should think... http://dev.mysql.com/doc/mysql/en/mysqldump.html Saturday, April 10, 2004, 5:08:43 AM, you wrote: MW Hi Steve, MW You might want to look at FLUSH TABLES WITH READ LOCK. That's a query MW to run from mysql, but I'm sure you can get it to work in your shell MW script (you need to maintain the MySQL connection while doing the MW backup). I don't know much about that, though. I think you just run MW UNLOCK TABLES when you're finished. MW Matt MW - Original Message - MW From: Steve Sills MW Sent: Tuesday, April 06, 2004 8:17 PM MW Subject: backup MW I want to use rsync to backup my db server, how do i lock all the tables MW for all the db's to read only so i cando my backup, then unlock them MW again. It needs to be done from the command line, not the mysql MW program. Anyone have any ideas? I have looked and couldn't find the MW answer i was looking before. Its running from a shell script, from my MW backup machine. Its currently setup to shut down the server, however i MW don't want to have to do this. Thanks in advance. MW Steve Sills MW Platnum Computers, President MW http://www.platnum.com MW [EMAIL PROTECTED] -- Best regards, Carsten R. Dreesbach mailto:[EMAIL PROTECTED] Senior Consultant Systar, Inc. 8000 Westpark Dr Suite 450 McLean, VA 22102 USA Tel: (703) 556-8436 Fax: (703) 556-8430 Cel: (571) 213-7904 -- 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: mysql error file
Actually, that didn't change anything. Still writing to $HOSTNAME.err -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, April 12, 2004 12:03 PM To: Luc Foisy Cc: Paul DuBois; MYSQL-List (E-mail) Subject: Re: mysql error file Note (see ps output) that you are using safe_mysqld, rather than mysqld_safe, as you have mysql 3.23.58. Hence, you need to change [mysqld_safe] to [safe_mysqld] in your my.cnf. See http://dev.mysql.com/doc/mysql/en/mysqld_safe.html. Michael Luc Foisy wrote: I hade it misconfigured (wrong param name prior to MySQL 4.0) Doc Quote http://dev.mysql.com/doc/mysql/en/Error_log.html Beginning with MySQL 4.0.10, you can specify where mysqld stores the error log file with the option --log-error[=file_name]. If no file_name value is given, mysqld uses the name `'hostname'.err' and writes the file in the data directory. (Prior to MySQL 4.0.10, the Windows error log name is `mysql.err'.) If you execute FLUSH LOGS the error log will be renamed with a suffix of -old and mysqld creates a new empty log file. In older MySQL versions on Unix, error log handling was done by mysqld_safe which redirected the error file to 'hostname'.err. You could change this filename by specifying a --err-log=filename option to mysqld_safe. End Doc Quote I changed it to what it should be according to the documentation and again it writes to $HOSTNAME.err [mysqld_safe] --err-log=/usr/data/mysql/mysql.err I am restaring MySQL with: /etc/rc.d/init.d/mysql stop /etc/rc.d/init.d/mysql start mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686) RedHat 9.0 What I am seeing with ps -axw 14683 pts/0S 0:00 /bin/sh /usr/bin/safe_mysqld --datadir=/usr/data/mysql --pid-file=/usr/data/mysql/mysql.pid 14712 pts/0S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/usr/data/mysql --user=mysql --pid-file=/usr/data/mysql/mysql.pid -Original Message- From: Luc Foisy Sent: Monday, April 12, 2004 9:12 AM To: Paul DuBois; MYSQL-List (E-mail) Subject: RE: mysql error file I changed mysql_safe to mysqld_safe and it is still sending the error output to $HOSTNAME.err -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 11:17 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: mysql error file At 10:10 -0400 4/7/04, Luc Foisy wrote: I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? You might want to use [mysqld_safe] rather than [mysql_safe]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I avoid filesort with BETWEEN and ORDER BY
Donny, Thanks for your answer. I tried your example after adding the necessary index and it works as long as I define a single number not a range (i.e. p_cat.lft = 4). Will keep working on the range part. What I am more interested in is the workarounds that can be made to improve the ORDER BY DESC problems as I can do all my queries perfectly withour this order by but as soon as it is added my site gives up. As the site is displaying classified ads they need to be displayed from the newest to the oldest. Any more help will be greatlty appreciated. Cheers Steve.. Donny Simonton wrote: Steven, In your case, you query doesn't even use an index. And you are using an order by DESC. Now what I would recommend is something like this, change your query just to test this out. SELECT p.* FROM p_cat c, p_ad p WHERE p.cat = c.id AND c.lft BETWEEN 4 AND 5 ORDER BY p.date DESC LIMIT 0,30; Select * from p_cat inner join p_ad on p_cat.id = p_ad.cat and p_cat.lft = 4 Order by p_ad.date ASC limit 0,30. Also add an index on id + lft on the p_cat table. And you also don't have an index on p_ad.date which is what you are trying to order by. Sorry, I had to rewrite the query because aliases drive me insane. Now in this case, you will see that with lft I have it set to do an exact match, not a range which is what between will give you. If you only were doing between two numbers like 4,5 or 100,101, I would personally recommend using IN. But that's my preference. Now with your order by, if you do the order by ASC, you won't have many if any problems. But you as a lot of people need to order by DESC, which mysql doesn't support very well, at least if you use explain. But there are work arounds to solve the problem, if you are like me and want to have 0 slow queries. Donny -Original Message- From: Steven Ducat [mailto:[EMAIL PROTECTED] Sent: Sunday, April 11, 2004 5:32 PM To: [EMAIL PROTECTED] Subject: How can I avoid filesort with BETWEEN and ORDER BY I am trying to optimize a query using both BETWEEN and ORDER BY but after months of reading and research I still can not get the hang of this. Details are as follows: 2 Tables CREATE TABLE `p_ad` ( `id` int(11) NOT NULL auto_increment, `cat` mediumint(9) NOT NULL default '0', `title` varchar(50) default NULL, `description` text, `location` varchar(50) default NULL, `pcode` varchar(8) default NULL, `pcode_id` smallint(4) default NULL, `ph` varchar(50) default NULL, `email` varchar(50) default NULL, `user_id` int(11) NOT NULL default '0', `date` timestamp(14) NOT NULL, `price` decimal(10,2) default NULL, `email_priv` tinyint(1) default '0', PRIMARY KEY (`id`), KEY `cat_pc_date` (`cat`,`pcode_id`,`date`), KEY `c_p_d` (`cat`,`pcode`,`date`), KEY `user` (`user_id`), KEY `cat_date` (`cat`,`date`) ) TYPE=MyISAM; CREATE TABLE `p_cat` ( `id` mediumint(9) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `parent` mediumint(11) default '0', `lft` mediumint(11) NOT NULL default '0', `rgt` mediumint(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `LFT` (`lft`), KEY `PARENT` (`parent`) ) TYPE=MyISAM; Query as follows: EXPLAIN SELECT p.* FROM p_cat c, p_ad p WHERE p.cat = c.id AND c.lft BETWEEN 4 AND 5 ORDER BY p.date DESC LIMIT 0,30; +---+---++--+-+--+ ---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---++--+-+--+ ---+-+ | p | ALL | cat_pc_date,c_p_d,cat_date | NULL |NULL | NULL 60002 | Using temporary; Using filesort | | c | range | PRIMARY,LFT | LFT| 3 | NULL | 1 | Using where | +---+---++--+-+--+ ---+-+ Is there any way I can get a query like this to avoid using a temporary table and filesort. ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple SELECTs in one query
Aha, that would explain it! I guess my next question would have to be...is the the appropriate list to inquire as to the stability of the current Alpha builds? My app is pretty timid in its use of SQL, mostly SELECTS and a smattering of UPDATES and INSERTS on single tables. But on the other hand, I have enough to do with the *rest* of my port without worrying about stability. Many thanks for the quick response! Steve Victor Pendleton wrote: You will need to be using MySQL 4.1.x in order to perform sub-selects. Hello, all! I am porting my Visual Basic app over from MSDE to MySQL, and things so far are going quite well. I've found most of the gotcha differences in how I need to structure my queries, but I am having trouble with one in particular. [Lengthy rambling code snippets] So now that I've made a long story even longer, my question is simply this - is there a way to execute all three selects within the same query, as I was able to do when my database was MSDE? It seems that it would be more efficient than making three hits on the database when one would suffice. Many thanks for any help you can provide! Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Q] moving database to server in new timezone
I'm, using mysqldump to dump a complete database on a server in The Netherlands and want to load the generated SQL onto a server in the USA to make an exact copy of the database. However, I have Timestamp(14) fields that are dumped as e.g. 2004101015 local time and is read as local time too on the USA server. Ie, the time is then off by 10 odd hours. How can I dump the database to generate SQL timestamps in GMT and also make it being read as GMT on the other side? Thanks __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th 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 mysql with intel icc
Walter Andreas wrote: #define SYSTEM_TYPE pc-linux #define VERSION 4.0.18 configure: exit 77 andy ideas? That's singularly sparse; not even a line number. All I can suggest is to find where configure tests for an executable output and see what it's doing. It may be misinterpreting icc for another compiler. -- jimoe at sohnen-moe dot com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
security reason for not using load data infile local?
Hello, MySQL gurus, Sometimes the 'local' option of 'load data infile' is disabled for security reasons (that is what I got from web). What could be the security problem? I have another question, is there any way to change a variable of mysql server without shutting it down? Thank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: security reason for not using load data infile local?
Ginger, can't speak to the log file issue but check out this link for the dynamic server variables: http://dev.mysql.com/doc/mysql/en/Dynamic_System_Variables.html Best O'luck, Tripp --- Ginger Cheng [EMAIL PROTECTED] wrote: Hello, MySQL gurus, Sometimes the 'local' option of 'load data infile' is disabled for security reasons (that is what I got from web). What could be the security problem? I have another question, is there any way to change a variable of mysql server without shutting it down? Thank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th 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: security reason for not using load data infile local?
It depends on the variable. Can you give an example of the variable you are trying to set? As far as the load data infile, I believe it depends on how your database will be accessed. If you have a need for remote administration or are working with geographically separated databases then the ability to load data from a different may be useful. While if you are only working from the local machine and you want to further lock down the system then disabling this may be a good decision. -Original Message- From: Ginger Cheng To: [EMAIL PROTECTED] Sent: 4/12/04 12:49 PM Subject: security reason for not using load data infile local? Hello, MySQL gurus, Sometimes the 'local' option of 'load data infile' is disabled for security reasons (that is what I got from web). What could be the security problem? I have another question, is there any way to change a variable of mysql server without shutting it down? Thank you for help ginger -- 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: Problems building readline on Solaris 7
Hi Erek, This seems familiar to me, are use still using Solaris make? If yes, try using GNU (gmake) it looks like a make problem. Hope this helps, Ken - Original Message - From: Erek Dyskant [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 12, 2004 8:44 AM Subject: Problems building readline on Solaris 7 Hi- I have not been able to build readline on Solaris 7. No matter what I change in the configuration, it still gives me the same error. Any ideas would be greatly appreciated. Thank you all in advance for the help. Regards, Erek System Configuration: gcc: 3.3.2 gnu make: 3.80 sparc-sun-solaris2.7 ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --libexecdir=/usr/local/mysql/ bin --with-extra-charsets=complex --enable-thread-safe-client --enable- local-infile --disable-shared --with-innodb Here's the error: if gcc -DUNDEF_THREADS_HACK -DHAVE_CONFIG_H -DNO_KILL_INTR -I. -I. -I.. -I./../include -I./.. -I..-O3 -DDBUG_OFF -O3 -fno-omit-frame-poi nter -DHAVE_CURSES_H -I/opt/home2/erek/src/mysql-4.0.18/include -DHAVE_RWLOCK_T -MT tilde.o -MD -MP -MF .deps/tilde.Tpo \ -c -o tilde.o `test -f 'tilde.c' || echo './'`tilde.c; \ then mv -f .deps/tilde.Tpo .deps/tilde.Po; \ else rm -f .deps/tilde.Tpo; exit 1; \ fi rm -f libreadline.a false cru libreadline.a readline.o funmap.o keymaps.o vi_mode.o parens.o rltty.o complete.o bind.o isearch.o display.o signals.o util.o kill.o undo.o macro.o input.o callback.o terminal.o xmalloc.o history.o histsearch.o histexpand.o histfile.o nls.o search.o shell.o tilde.o make[2]: *** [libreadline.a] Error 1 make[2]: Leaving directory `/opt/home2/erek/src/mysql-4.0.18/readline' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/opt/home2/erek/src/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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] moving database to server in new timezone
On 12-Apr-2004 Riaan Oberholzer wrote: I'm, using mysqldump to dump a complete database on a server in The Netherlands and want to load the generated SQL onto a server in the USA to make an exact copy of the database. However, I have Timestamp(14) fields that are dumped as e.g. 2004101015 local time and is read as local time too on the USA server. Ie, the time is then off by 10 odd hours. How can I dump the database to generate SQL timestamps in GMT and also make it being read as GMT on the other side? A. run both servers on the same time-zone. B. load the data and run the query: UPDATE tbl SET ts= DATE_SUB(ts, INTERVAL 10 HOUR) WHERE ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql error file
Hmm... Have you looked in /etc/rc.d/init.d/mysql to see what, exactly, it does when you tell it to start? Michael Luc Foisy wrote: Actually, that didn't change anything. Still writing to $HOSTNAME.err -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, April 12, 2004 12:03 PM To: Luc Foisy Cc: Paul DuBois; MYSQL-List (E-mail) Subject: Re: mysql error file Note (see ps output) that you are using safe_mysqld, rather than mysqld_safe, as you have mysql 3.23.58. Hence, you need to change [mysqld_safe] to [safe_mysqld] in your my.cnf. See http://dev.mysql.com/doc/mysql/en/mysqld_safe.html. Michael Luc Foisy wrote: I hade it misconfigured (wrong param name prior to MySQL 4.0) Doc Quote http://dev.mysql.com/doc/mysql/en/Error_log.html Beginning with MySQL 4.0.10, you can specify where mysqld stores the error log file with the option --log-error[=file_name]. If no file_name value is given, mysqld uses the name `'hostname'.err' and writes the file in the data directory. (Prior to MySQL 4.0.10, the Windows error log name is `mysql.err'.) If you execute FLUSH LOGS the error log will be renamed with a suffix of -old and mysqld creates a new empty log file. In older MySQL versions on Unix, error log handling was done by mysqld_safe which redirected the error file to 'hostname'.err. You could change this filename by specifying a --err-log=filename option to mysqld_safe. End Doc Quote I changed it to what it should be according to the documentation and again it writes to $HOSTNAME.err [mysqld_safe] --err-log=/usr/data/mysql/mysql.err I am restaring MySQL with: /etc/rc.d/init.d/mysql stop /etc/rc.d/init.d/mysql start mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686) RedHat 9.0 What I am seeing with ps -axw 14683 pts/0S 0:00 /bin/sh /usr/bin/safe_mysqld --datadir=/usr/data/mysql --pid-file=/usr/data/mysql/mysql.pid 14712 pts/0S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/usr/data/mysql --user=mysql --pid-file=/usr/data/mysql/mysql.pid -Original Message- From: Luc Foisy Sent: Monday, April 12, 2004 9:12 AM To: Paul DuBois; MYSQL-List (E-mail) Subject: RE: mysql error file I changed mysql_safe to mysqld_safe and it is still sending the error output to $HOSTNAME.err -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 11:17 AM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: mysql error file At 10:10 -0400 4/7/04, Luc Foisy wrote: I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? You might want to use [mysqld_safe] rather than [mysql_safe]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FBSD 5.2.CURRENT-p4 and mysqld problems
Are you able to run show process list? What is the status of your query? What date was your freeBSD 5-current built on? Please send output of uname -a, so that we know exactly which version on which date because -current changes every day. Also are you using libmap.conf? If yes, what are it's contents. What does top and ps show when this query is locked up, can you include output of this? Why do you feel you MUST use freebsd5 (I am just curious)? Have you tried a GDB backtrace (please use debug version of mysql!)? I know you already know this, but you shouldn't be using 5-current on a production server and even the releases such as 5.2.1 can have issues that you need to be aware of. Also, one final recommendation, if you are using current and have not updated in the last 30 to 60 days I would recommend that you update again as many thing have been changed and fixed. Hope this helps, Ken - Original Message - From: Ganbold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, April 11, 2004 11:35 PM Subject: FBSD 5.2.CURRENT-p4 and mysqld problems Hi, I found some problematic queries which locks mysql server any further processing. I'm using mysql-4.0.18 from FreeBSD ports collection. I'm using FreeBSD-5.2-CURRENT. Some of the queries like : select sum(total_amount) pbc from customers.payment where contract_id=0 and contract_id=4999 and from_unixtime(time_stamp,'%Y-%m')='2004-03'; Above query is not using any index. It locks mysql server and all other following queries go into queue until I kill locked mysqld thread. After killing locked mysqld thread everything goes back to function normally. I made composite index on contract_id,time_stamp,total_amount and let's see what will happen. In the meantime, can somebody explain me what the problem is? I already sent email to freebsd-current and freebsd-ports mailing list about this problem. Does somebody have this problem before? Let me know if there is any solution. I know I should use FreeBSD 4.9 stable since it doesn't have any problem with this issue. However I need to solve this problem on FreeBSD-5.2-CURRENT. thanks in advance, Ganbold At 01:36 PM 05.04.2004, you wrote: yes, we resolved the issue it looks like. I wanted to hold off on announcing this until i was sure, but it's been running for about 24 hours now without a lockup. options MAXDSIZ=(1024*1024*1024) # change max from 512M to 1G add that to your kernel config. basically whats happening ( and i'm probably wrong with the technicals on this, but bear with me :D ) is that mysqld would aquire a lock (on a table more then likely) and then try to allocate above the FBSD 5.2 max default memory size (512M). when it did this, it crashed and left the lock on the table. from there it just froze. making that change seems to have fixed it. it was never evident before because we had a slower webserver that wasn't loading the sql server all that much. now it is, and we started developing problems. :). as you can tell with this link: http://sql.tribalwar.com/before-ps.txt the mysqld proc wen't above 512M. check your sql.err log, if you're getting a malloc error, then this is more then likely your problem. Thanks to everyone for the help in tracking this down. Let me know if this fixes the problem for you. Also, are you running -CURRENT in production? or testing? I had given some thought to upgrading the box to -CURRENT after the commit for the network stack settled. But I first need to get serial console enabled on it :). Daryl --- Ganbold [EMAIL PROTECTED] wrote: Hi Daryl, I have exactly same problem as you. I have FreeBSD 5.2-CURRENT (did cvsup on March 23) with mysql-4.0.18 from ports collection. It is compiled with linuxthreads. Mysql is working fine, except it sometimes freezes, sometimes one or two tables get corrupted. Usually freezes once per day. Just freezes and I had to kill mysql process and start. Did you solve your problem? I also downloaded latest snapshot from mysql.com web site and installed, but nothing changes. Let me know if you find something. TIA, Ganbold At 12:48 PM 03.04.2004, you wrote: ah! thanks :) It just happened again and I was able to collect the data again. Before I did mysqladmin shutdown/killall -11 mysqld http://sql.tribalwar.com/before-ps.txt a few times the 3 giant processes would show as -, but then it got to the point where they weren't changing one bit (staying at Giant) after I did mysqladmin shutdown/killall -11 mysqld http://sql.tribalwar.com/after-ps.txt this eventually cleared up after a minute or so. I've given some serious thought to upgrading to -CURRENT, but with the network stack commit comming soon (or happening now), I don't want to get caught in the middle of it and have severe problems.
How to protect MySQL server from intruders ?
Hello Group I would like to know if there is some thing I can do during the configuration of MySQL server so that I could restrict only one user from accessing the database and all others are restricted from the access of the databas? I mean I do not want anybody else to access the database from the outside world, except one particular application (with one username and password) which I would like to give access to , that would be running in the same system as the database Bottomline restrict the outside world traffic for the database !!! = Sam _o /\ __o - \_/ \ \, __(_)/_(_)/\___(_) /_(_)__ __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th 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: Getting mysql to work in Panther
It sounds like you are able to login as a guest, but not as 'root'. Please note that 'root' for MySQL is not the same as root user for the OS, they are totally unrelated. I typically change the account name for MySQL root to 'mysqlroot', so my login would be mysql -u mysqlroot -p. There is no regular user after the initial install of MySQL, there is only MySQL root. As long as you followed the directions and did nothing else after you installed and started MySQL, the mysqladmin command to set the initial password should have worked. I'm guessing you might have tried some other things first. When in a total jam, you can startup mysqld without having it check access rights. You can then login as a guest and set the root password manual. You can kill the mysql process and start it backup up with out the grant tables. mysqld --skip-grant-tables But if you don't know how to set the password manually (you need to encrypt it), you should probably reinstall MySQL and see if you can set the root password. Download the install from the MySQL website. I've done about a dozen installs of MySQL on Macs over the past two years without a problem, so it does work. On Apr 12, 2004, at 1:26 AM, Daniel Lahey wrote: I have been trying for months to set up mysql on my Mac (Panther) with no success. I can't run mysqladmin as root. I get an access denied error message. If I try to use SHELLmysqladmin -u root password 'password' I get access denied as either root or the regular user. I can't run mysql as root. 'access denied' I can't create databases. I can run mysql as the regular user and mess with the test db, and that's about all I can do. I've followed every instruction I could find online at the command line. I've tried CocoaMySQL. I have practically resorted to prayer. Are there step-by-step instructions anywhere? I would be eternally grateful for any help. -- 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: mysql error file
Michael wrote:
mm... Have you looked in /etc/rc.d/init.d/mysql to see what, exactly, it
oes when you tell it to start?
Michael
I briefly looked in there. This is the same script it uses to start MySQL, and was
installed with the rpm of MySQL.
I would think it should come with all available options already...
To me, it doesn't look like it even reads that option from the my.cnf file. It doesn't
look like its reading any option from mysqld_safe group.
Pasting the whole thing, so I don't have to work with file attaching.
#!/bin/sh
# Copyright Abandoned 1996 TCX DataKonsult AB Monty Program KB Detron HB
# This file is public domain and comes with NO WARRANTY of any kind
# MySQL daemon start/stop script.
# Usually this is put in /etc/init.d (at least on machines SYSV R4 based
# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
# When this is done the mysql server will be started when the machine is
# started and shut down when the systems goes down.
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 90 90
# description: A very fast and reliable SQL database engine.
# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $local_fs $network $remote_fs
# Required-Stop: $local_fs $network $remote_fs
# Default-Start: 3 5
# Default-Stop: 3 5
# Short-Description: start and stop MySQL
# Description: MySQL is a very fast and reliable SQL database engine.
### END INIT INFO
# If you install MySQL on some other places than /, then you
# have to do one of the following things for this script to work:
#
# - Run this script from within the MySQL installation directory
# - Create a /etc/my.cnf file with the following information:
# [mysqld]
# basedir=path-to-mysql-installation-directory
# - Add the above to any other configuration file (for example ~/.my.ini)
# and copy my_print_defaults to /usr/bin
# - Add the path to the mysql-installation-directory to the basedir variable
# below.
#
# If you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.
basedir=
# The following variables are only set for letting mysql.server find things.
# Set some defaults
datadir=/var/lib/mysql
pid_file=
if test -z $basedir
then
basedir=/
bindir=/usr/bin
else
bindir=$basedir/bin
fi
PATH=/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin
export PATH
if test -z $pid_file
then
pid_file=$datadir/`/bin/hostname`.pid
else
case $pid_file in
/* ) ;;
* ) pid_file=$datadir/$pid_file ;;
esac
fi
mode=$1# start or stop
parse_arguments() {
for arg do
case $arg in
--basedir=*) basedir=`echo $arg | sed -e 's/^[^=]*=//'` ;;
--datadir=*) datadir=`echo $arg | sed -e 's/^[^=]*=//'` ;;
--pid-file=*) pid_file=`echo $arg | sed -e 's/^[^=]*=//'` ;;
esac
done
}
# Get arguments from the my.cnf file,
# groups [mysqld] [mysql_server] and [mysql.server]
if test -x ./bin/my_print_defaults
then
print_defaults=./bin/my_print_defaults
elif test -x $bindir/my_print_defaults
then
print_defaults=$bindir/my_print_defaults
elif test -x $bindir/mysql_print_defaults
then
print_defaults=$bindir/mysql_print_defaults
else
# Try to find basedir in /etc/my.cnf
conf=/etc/my.cnf
print_defaults=
if test -r $conf
then
subpat='^[^=]*basedir[^=]*=\(.*\)$'
dirs=`sed -e /$subpat/!d -e 's//\1/' $conf`
for d in $dirs
do
d=`echo $d | sed -e 's/[ ]//g'`
if test -x $d/bin/my_print_defaults
then
print_defaults=$d/bin/my_print_defaults
break
fi
if test -x $d/bin/mysql_print_defaults
then
print_defaults=$d/bin/mysql_print_defaults
break
fi
done
fi
# Hope it's in the PATH ... but I doubt it
test -z $print_defaults print_defaults=my_print_defaults
fi
parse_arguments `$print_defaults mysqld mysql_server mysql.server`
# Safeguard (relative paths, core dumps..)
cd $basedir
case $mode in
'start')
# Start daemon
if test -x $bindir/safe_mysqld
then
# Give extra arguments to mysqld with the my.cnf file. This script may
# be overwritten at next upgrade.
$bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file
# Make lock for RedHat / SuSE
if test -w /var/lock/subsys
then
touch /var/lock/subsys/mysql
fi
else
echo Can't execute $bindir/safe_mysqld from dir $basedir
fi
;;
'stop')
# Stop daemon. We use a signal here to avoid having to know the
# root password.
if test -s $pid_file
then
mysqld_pid=`cat $pid_file`
echo Killing mysqld with pid $mysqld_pid
kill $mysqld_pid
# mysqld should remove the pid_file when it exits, so wait for it.
sleep 1
while [ -s $pid_file -a $flags != aa ]
do
[ -z $flags ] echo Wait for mysqld to exit\c || echo .\c
flags=a$flags
Re: Multiple SELECTs in one query
You might consider a whole different approach which is more efficient, because regardless of VB or MySQL - in your current setup you are issueing 3 distinct SQL statements against the same table and you might be able to convert it to only issueing one. Basically, use functions to create 1's or 0's for every row of the table - depending on the 3 conditions and then sum/average them up. For the count(*) just put a 1. For the complete say if complete then 1 else 0. For the 3rd select minutes or 0 depending on complete. The SQL will look something like this (I am writing in pseudocode): SELECT sum (1) as total_count, sum (if status = complete then 1 else 0) as count_complete, avg (if status = complete then minutes else 0) as avg_of_complete FROM tableFLIST You'll get all 3 results in one pass. You will need to be using MySQL 4.1.x in order to perform sub-selects. -Original Message- From: Steve Pugh To: [EMAIL PROTECTED] Sent: 4/12/04 11:01 AM Subject: Multiple SELECTs in one query Hello, all! I am porting my Visual Basic app over from MSDE to MySQL, and things so far are going quite well. I've found most of the gotcha differences in how I need to structure my queries, but I am having trouble with one in particular. In my original code, I could use one query to get a total count of records, a count of records meeing a criteria (Status = Complete), and an average on another field for the records meeting that criteria. It looked like this in code: SQLStr = SELECT DoneCount=(SELECT Count(*) FROM flist WHERE Status = 'Complete'), _ TotalCount=(SELECT Count(*) FROM flist ), _ AvgRenderTime=(SELECT Avg(renderminutes) FROM flist WHERE Status = 'Complete') The resulting SQL query would look something like this: SQLStr = SELECT DoneCount=(SELECT Count(*) FROM tableFLIST WHERE Status = 'Complete'), TotalCount=(SELECT Count(*) FROM tableFLIST), AvgRenderTime=(SELECT Avg(renderminutes) FROM tableFLIST WHERE Status = 'Complete') Now, in MySQL, I get syntax errors in the query - most of them around TotalCount= in this example. In my investigation, I found that I could break the one query apart and execute three calls to get the information I needed, like this: SQLStr = SELECT count(*) as TotalCount FROM flist rs.Open SQLStr totalFrames = rs!totalcount rs.Close SQLStr = SELECT Count(*) AS DoneCount FROM flist WHERE Status = 'Complete' rs.Open SQLStr doneframes = rs!donecount rs.Close SQLStr = SELECT Avg(renderminutes) as AvgRenderTime FROM flist WHERE Status = 'Complete' rs.Open SQLStr rs.Close So now that I've made a long story even longer, my question is simply this - is there a way to execute all three selects within the same query, as I was able to do when my database was MSDE? It seems that it would be more efficient than making three hits on the database when one would suffice. Many thanks for any help you can provide! Steve -- 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 protect MySQL server from intruders ?
Hi Sam, There are many methods and you should alsways employ more than one. The first is to use the grant system inside of mysql http://dev.mysql.com/doc/mysql/en/GRANT.html If you want no network access at all use --skip-networking Don't listen for TCP/IP connections at all. All interaction with mysqld must be made via named pipes (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are allowed. http://dev.mysql.com/doc/mysql/en/Server_options.html Also you could use a firewall between your system and the outside world. If you need to allow outside access use a VPN. If that is not an option some systems employ wrappers look at /etc/hosts.allow or hosts_access. You could also install a software firewall on your system. But this is outside the scope of this mailing list. Hope this helps, Ken - Original Message - From: Sam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 12, 2004 3:31 PM Subject: How to protect MySQL server from intruders ? Hello Group I would like to know if there is some thing I can do during the configuration of MySQL server so that I could restrict only one user from accessing the database and all others are restricted from the access of the databas? I mean I do not want anybody else to access the database from the outside world, except one particular application (with one username and password) which I would like to give access to , that would be running in the same system as the database Bottomline restrict the outside world traffic for the database !!! = Sam _o /\ __o - \_/ \ \, __(_)/_(_)/\___(_) /_(_)__ __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling mysql with intel icc
are you sure that the icc binary is in your path? have you sourced the script in the icc directory which sets the environment variables for you? yonah Walter Andreas wrote: Hi there, I am trying to compile mysql 4.x with intel compiler for maximum performance. On my research I have found that following line will squese more performance out of mysql: CFLAGS=-O3 -unroll2 -ip -mp -no-gcc -restrict CC=icc CXX=icc CXXFLAGS=-O3 -unroll2 -ip -mp -no-gcc -restrict ./configure --prefix=/usr/local/mysql--with-mysqld-user=mysqladm --without-debug --with-client-ldflags=-all-static--with-mysqld-ldflags=-all-static --disable-shared--localstatedir=/home/mysqladm/data --enable-assembler The fun stops right away with following message: checking for gcc... icc checking for C compiler default output... configure: error: C compiler cannot create executables See `config.log' for more details. Whats wrong? I did not tell mysql to drop the c compiler?! Did I? Thanx for any help, Andy _ Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P! Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
raid configure option?
Can anyone explain what the raid configure option does? Is this for use when storing mysql on hardware raid? if so, what type(s)- ie. striping, mirroring? Thanks yonah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: raid configure option?
In the last episode (Apr 13), Yonah Russ said: Can anyone explain what the raid configure option does? Is this for use when storing mysql on hardware raid? if so, what type(s)- ie. striping, mirroring? It's mainly to support tables over 2gb on old Linux kernels that can't do large files. You can also do a poor-man's RAID with it by creating a RAID table, moving the files to separate disks, and creating symlinks that point to the new locations. Only striping is supported. If you have it, use hardware RAID and regular mysql tables instead. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table lock problem on INSERT with FULLTEXT index?
FWIW, I'm still having this problem. I've completely dropped the table and re-built it from the ground up. It's a bizarre problem... The table is totally simple. A primary key, and then three varchar fields. The FULLTEXT index spans the 3 varchar fields. There are only 6500+ rows, so it's pretty tiny. A mysqldump of the table is only 442K. Has no-one else seen anything like this? I can't imagine I'm the only one. Thanks, Don Don MacAskill wrote: Hi there, I've got a bizarre problem I can't seem to solve. I have a small MyISAM table (6533 rows) with a small FULLTEXT index (3 columns per row, an average of 1 word per column). When I do an INSERT on the table, many times the thread gets stuck perpetually in Query | update. Future reads from other threads, of course, stay Locked. When I try to kill the thread using mysqladmin, the thread sticks around for thousands of seconds in Killed | update until I finally just have to kill mysqld manually and let it restart. I've tried REPAIR, ANALYZE, and OPTIMIZE on the table, both using mysql and myisamchk. Tried all the options, such as extended and force and the like. I've even tried wrapping the INSERT with LOCK TABLE table WRITE and UNLOCK TABLES. Still no dice, the INSERT still hangs sometimes. I haven't bothered trying INSERT DELAYED since LOCK TABLE seems more drastic anyway and it didn't work. This happens both on 4.0.17 and 4.0.18. This is a RHEL3 WS dual AMD64 box w/8GB of RAM. Strangely enough, I have another MyISAM table with 1,285,742 rows and a larger FULLTEXT index, and it never locks this way. I have a third MyISAM table with 61,834 rows and a larger FULLTEXT index that locked on me once like this last night, but then I ran through and updated nearly every row overnight, and inserted a few hundred new ones, and it didn't lock at all. FWIW, it only seems to hang on INSERT, not on UPDATE. I haven't tried DELETE yet. I'm stumped. Any help would be greatly appreciated! Thanks, Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Process in waiting for table state after an ALTER TABLE
Hi, I'm using MySQL 4.0.18-max (same problem with 4.0.18-standard) on one server, and I encounter a strange problem : When I make some change on a table (adding index for exemple), at the end of the ALTER process all the queries that must write on this table switch in the Waiting for table state forever. A flush table doesn't solve the problem and I have to kill mysql and restart the server... Last time this problem occurs, I was converting a table to InnoDB, and the Waiting for table problems occured when the ALTER TABLE switched to renaming table state, at this end of the ALTER process... When I restarted mysql, the InnoDB table was OK. Any idea of what could happen on this server, since it's the first time I see this kind of problem with MySQL ? Thanks, Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB and searchs
Hi, We have a FULLTEXT search in our site. The is after we migrate the tables from MyISAM to InnoDB, this search stop working. Looking at the MySQL Documentation (http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html) I´ve seen that it´s support for MyISAM tables online. So, how can I make a search for a word in a LONGTEXT column? Will it be fast? Any help would be appreciated, Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Get MySQL to ignore the backslashes?
Is there a 'global' way to tell MySQL to not interpret the backslashes ('\'s) in the
submitted SQL as escape characters? In other words, I'd like for them to always be
treated as if they themselves were already escaped with a backslash (i.e. '\\'). I'm
using 3.23.52, accessing it with an older (2.x?) MM JDBC driver. I know of a couple
of code-level solutions that will require a number of changes in the existing code,
but was hoping to find a more 'global' solution, via either a global call or a
configuration setting. Any ideas are much appreciated.
Thanks in advance,
Alex Zeltser
__
Introducing the New Netscape Internet Service.
Only $9.95 a month -- Sign up today at http://isp.netscape.com/register
Netscape. Just the Net You Need.
New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get MySQL to ignore the backslashes?
Hi Alex. This question was discussed last week. Search the archives
for 'backslash'. The short answer is that there is no global option to
do what you want. You'll have to do it in code.
On Mon, 2004-04-12 at 17:10, [EMAIL PROTECTED] wrote:
Is there a 'global' way to tell MySQL to not interpret the backslashes ('\'s) in the
submitted SQL as escape characters? In other words, I'd like for them to always be
treated as if they themselves were already escaped with a backslash (i.e. '\\').
I'm using 3.23.52, accessing it with an older (2.x?) MM JDBC driver. I know of a
couple of code-level solutions that will require a number of changes in the existing
code, but was hoping to find a more 'global' solution, via either a global call or a
configuration setting. Any ideas are much appreciated.
Thanks in advance,
Alex Zeltser
__
Introducing the New Netscape Internet Service.
Only $9.95 a month -- Sign up today at http://isp.netscape.com/register
Netscape. Just the Net You Need.
New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp
--
|- Garth Webb -|
|- [EMAIL PROTECTED] -|
signature.asc
Description: This is a digitally signed message part
backup table/restore table question
hi... i have a server where there are 5 tables inside a database. i was wondering if i did the following command from inside a script: backup table dbname.tablename to /home/users/my_dir/ then with a different script gzip/tar them, after gzipped ftp them to my test server into say c:/db_backup/db_name, connect to my local test server and then type: restore db_name.tablename from c:/backup/db_name/ and have it work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I determine the row number or key when table has no key fields
Andy Ford wrote: I thought LIMIT limited you to N number of CONCURRENT record. ie. limit 10 or limit 20 I believe Ross would like to select select 1000 records and then do a sub select of records 1-20 and then 21-40 on this record set LIMIT also allows you to specify a starting record, i.e. LIMIT 50, 100 so Ross could change the first parameter to accomplish this. Example: First query: LIMIT 0, 20 Second: LIMIT 20, 20 Third: LIMIT 40, 20 etc Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext index
I am trying to add a second fulltext index to an already existing table
(named articles).
The first index is for field(column name) text of type longtext
I want to add a second index that will be for this field and
field(column name) title of type varchar
I used this query: alter table articles add fulltext (title,text);
But after doing that, and getting a query OK result that indicated
all existing rows had been affected, the fulltext index (title,text)
does not work, because the rows supposedly indexed from the title
column are not bringing up any results (for known-item searching).
The query against this index was: SELECT * FROM articles WHERE MATCH
(title,text) AGAINST ('search_term');
Am I missing something? Is the query I wrote above incorrect?
Any help would be super appreciated.
Thanks,
Nathan
--
Nathan Mealey
Director of Operations
Cycle-Smart, Inc.
P.O. Box 1482
Northampton, MA
01061-1482
[EMAIL PROTECTED]
(413) 587-3133
(413) 210-7984 Mobile
(512) 681-7043 Fax
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FBSD 5.2.CURRENT-p4 and mysqld problems
Ken, Thank you very much for reply. I'm having this problem since March 13th, and having big trouble. I compiled mysql with linuxthreads-2.2.3-15. At 04:17 AM 13.04.2004, you wrote: Are you able to run show process list? Sometimes I can't run show processlist. It hangs. What is the status of your query? What date was your freeBSD 5-current built on? Please send output of uname -a, so that we know exactly which version on which date because -current changes every day. I did cvsup recently. uname -an shows: backend2# uname -an FreeBSD backend2.ub.mng.net 5.2-CURRENT FreeBSD 5.2-CURRENT #4: Sun Apr 11 17:23:09 ULAT 2004 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/BACKEND2 i386 Somehow on my another 5.2-CURRENT machine built on March 12 mysql-4.0.18 runs without any problem. Probably there was some change in FreeBSD kernel source and this might cause mysqld with linuxthreads lock and hang. But it is just my guess. Also are you using libmap.conf? If yes, what are it's contents. I think I'm not using libmap.conf, since there is no /etc/libmap.conf file. What does top and ps show when this query is locked up, can you include output of this? When mysql hangs output of the ps axlwww | grep mysql shows following: backend2# ps axlwww | grep mysql 0 12544 12536 0 8 0 1564 988 wait S ??0:00.01 sh -c /usr/local/bin/mysqldump --all-databases --complete-insert --user=backupman --password=xxx all_db_backend2_2004-04-13.sql 0 12545 12544 0 4 0 2692 2016 sbwait S ??7:25.74 /usr/local/bin/mysqldump --all-databases --complete-insert --user=backupman --password=xxx 0 19239 18683 0 8 0 1564 1004 wait S ??0:00.00 sh -c /bin/sh /tmp/logrot0AWE3J /var/db/mysql/general.log 0 19240 19239 0 8 0 1572 1008 wait S ??0:00.00 /bin/sh /tmp/logrot0AWE3J /var/db/mysql/general.log 0 19244 19240 0 4 0 1708 972 sbwait S ??0:00.01 /usr/local/bin/mysqladmin --password=xxx flush-logs 0 14377 1 0 8 0 1584 1032 wait S p00:00.04 /bin/sh /usr/local/bin/mysqld_safe --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/backend2.pid --log-slow-queries=slow.log --log=general.log --log-update=update.log --default-character-set=latin1 88 15081 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 15082 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 16118 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 16119 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 16120 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 16386 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 17143 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 17647 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 17648 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 18669 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 18670 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 18671 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 19245 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 19763 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 19764 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 20268 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 20269 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 20270 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 20271 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 20790 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 22329 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 22330 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 22332 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 23352 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 23858 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 23859 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 24376 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 25397 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 25901 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 25902 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 26406 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 26422 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 26926 49061 0 20 12 472736 445900 pause SNp00:00.00 (mysqld) 88 26927 49061 0 20 12 472736 445900 pause
Re: Fulltext index
I find doing this helps to reinitialize the indexes
ALTER TABLE shotlist TYPE=MyISAM;
REPAIR TABLE shotlist QUICK;
I am trying to add a second fulltext index to an already existing table
(named articles).
The first index is for field(column name) text of type longtext
I want to add a second index that will be for this field and
field(column name) title of type varchar
I used this query: alter table articles add fulltext (title,text);
But after doing that, and getting a query OK result that indicated
all existing rows had been affected, the fulltext index (title,text)
does not work, because the rows supposedly indexed from the title
column are not bringing up any results (for known-item searching).
The query against this index was: SELECT * FROM articles WHERE MATCH
(title,text) AGAINST ('search_term');
Am I missing something? Is the query I wrote above incorrect?
Any help would be super appreciated.
Thanks,
Nathan
--
Nathan Mealey
Director of Operations
Cycle-Smart, Inc.
P.O. Box 1482
Northampton, MA
01061-1482
[EMAIL PROTECTED]
(413) 587-3133
(413) 210-7984 Mobile
(512) 681-7043 Fax
--
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: backup table/restore table question
At 20:23 -0400 4/12/04, Andy B wrote: hi... i have a server where there are 5 tables inside a database. i was wondering if i did the following command from inside a script: backup table dbname.tablename to /home/users/my_dir/ then with a different script gzip/tar them, after gzipped ftp them to my test server into say c:/db_backup/db_name, connect to my local test server and then type: restore db_name.tablename from c:/backup/db_name/ and have it work 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]
OS X 10.3 install step by step
For some reason I get a lot of email from users on Mac OS X that can not install MySql. I have written a step by step in hopes it will help. I really did little more than copy the notes on the MySql site, but alas, it seems some people are not following those correctly. Hopefully, people can point others to this link http://newgeo.com/mysql/ -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compiling mysql on a pentium
Hi there, how to compile mysql 4.0.18 on a pentium for best performance? I searched the net now for 2 days and found lots of hints on compiling with icc and pgcc, but it looks to me that icc is not working with mysql 4.0.18 and pgcc is out of date (maybe gcc already catched up with pgcc?). Setting compiler flags is also a miraqle for me. This is going to be a production server, so it should be really stable and not the trade for performance. Can anybody advice a configure command with compiler settings for a p4 machine with 1G ram? Thank you for your advice, Andy _ Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P! Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling mysql on a pentium
Walter Andreas wrote: Hi there, how to compile mysql 4.0.18 on a pentium for best performance? I searched the net now for 2 days and found lots of hints on compiling with icc and pgcc, but it looks to me that icc is not working with mysql 4.0.18 and pgcc is out of date (maybe gcc already catched up with pgcc?). Setting compiler flags is also a miraqle for me. This is going to be a production server, so it should be really stable and not the trade for performance. Can anybody advice a configure command with compiler settings for a p4 machine with 1G ram? Thank you for your advice, Andy Check the INSTALL-SOURCE file in your source distribution. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling mysql on a pentium
how to compile mysql 4.0.18 on a pentium for best performance? Check the INSTALL-SOURCE file in your source distribution. I found this: CFLAGS=-O2 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysqladm --without-debug --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --disable-shared --localstatedir=/home/mysqladm/data --enable-assembler but it says for x86 which could also be amd?! What about pgcc and how about -march=pentium4 which I found somewhere else on the net? Andy Daniel Kasak [EMAIL PROTECTED] schrieb am 13.04.04 05:31:35: Walter Andreas wrote: Hi there, how to compile mysql 4.0.18 on a pentium for best performance? I searched the net now for 2 days and found lots of hints on compiling with icc and pgcc, but it looks to me that icc is not working with mysql 4.0.18 and pgcc is out of date (maybe gcc already catched up with pgcc?). Setting compiler flags is also a miraqle for me. This is going to be a production server, so it should be really stable and not the trade for performance. Can anybody advice a configure command with compiler settings for a p4 machine with 1G ram? Thank you for your advice, Andy Check the INSTALL-SOURCE file in your source distribution. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au hr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Sober.A-F! Kostenfrei fuer FreeMail Nutzer. http://f.web.de/?mc=021158 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too many server instances
Each one of those is a thread, not a server instance. I assume you're using Linux, since linux shows separate threads as processes. j- k- On Saturday 10 April 2004 06:48 pm, Emmett Bishop said something like: Howdy all, I am having trouble configuring my server parameters with my.cnf because there seems to be several mysql server instances (mysqld processes) running on my linux box. What I would like to do is bump up the innodb_buffer_pool_size on the server to 512M (the box has 3GB of RAM). The problem is that there seem to be several instances of mysql server running concurrently on the box. Each one of them allocates 512M for the buffer pool and the box grinds to a halt, completely out of RAM to do anything! I've included a snippet of the top command display (I hope that you can read it easily). -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling mysql on a pentium
Walter Andreas wrote: how to compile mysql 4.0.18 on a pentium for best performance? Check the INSTALL-SOURCE file in your source distribution. I found this: CFLAGS=-O2 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysqladm --without-debug --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --disable-shared --localstatedir=/home/mysqladm/data --enable-assembler but it says for x86 which could also be amd?! What about pgcc and how about -march=pentium4 which I found somewhere else on the net? You haven't actually told us what processor ( 'pentium' is a marketing term, not a processor type ... there are many different types of pentiums ) you have, but you should be able to use -march=pentium4 or whatever matches your processor. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OS X 10.3 install step by step
On 4/12/04 8:50 PM, Scott Haneda [EMAIL PROTECTED] wrote: For some reason I get a lot of email from users on Mac OS X that can not install MySql. I have written a step by step in hopes it will help. I really did little more than copy the notes on the MySql site, but alas, it seems some people are not following those correctly. Hopefully, people can point others to this link http://newgeo.com/mysql/ I've received much helpful info from this site as well. http://www.entropy.ch/software/macosx/mysql/#install The guy who runs it used to create binary installers for MySQL. Now that MySQL offers them, he simply provides installation and update instructions. I've used his site many times in my various updates/installs, and it's been an enormous help. Just thought I'd throw it out there for people to see. James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
