Re: ERROR 1045: Access denied for user:
Did not see a reference to reloading tables in the docs. This is just weird enough for me to vpn in and try again :-) Thanks! -- Steve (egrep) On Fri, 7 Mar 2003, Ben Balbo wrote: Hi Steve, Really silly question, but you are reloading the tables once you change the grants, aren't you :-) mysqladmin -p reload B. In response to your mail sent on Friday, March 07, 2003 at 10:48:06 AM. Mysql version 3.23.55 Slackware linux 8.0 Mysql version 3.23.55 OpenBSD I have tried 3 versions of mysql on linux and on OpenBSD. I have even tried compiling on linux from the source tarball. Without the GRANT privs to allow network access: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'192.58.197.0/255.255.255.0'; (example), I would get a you are not allowed to connect error. Once I applied the grant to the user: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'%' the users table shows: | Host| User | --- | % | user | If I coonect to the database without a host, or with host set to localhost, I can connect fine. If I connect from another machine, or just use the DNS name on the local machine, I get: shell mysql -h 192.168.1.24 -u test -p ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have even tried connecting using the IP address. I have poured over the docs, and have tried all the scenarios of add hostnames to /etc/hosts etc. I even complied from source (glibc problem). No luck at all... Any ideas? I really need network access to the database. -- Steve (egrep) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1045: Access denied for user:
Tried this... no go... The wierd part, is that localhost works fine, but I cannot connect locally if I use the hostname. Thanks for the help! (maybe I should go back to an old version of mysql :-) -- Steve (egrep) On Fri, 7 Mar 2003, Ben Balbo wrote: Hi Steve, Really silly question, but you are reloading the tables once you change the grants, aren't you :-) mysqladmin -p reload B. In response to your mail sent on Friday, March 07, 2003 at 10:48:06 AM. Mysql version 3.23.55 Slackware linux 8.0 Mysql version 3.23.55 OpenBSD I have tried 3 versions of mysql on linux and on OpenBSD. I have even tried compiling on linux from the source tarball. Without the GRANT privs to allow network access: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'192.58.197.0/255.255.255.0'; (example), I would get a you are not allowed to connect error. Once I applied the grant to the user: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'%' the users table shows: | Host| User | --- | % | user | If I coonect to the database without a host, or with host set to localhost, I can connect fine. If I connect from another machine, or just use the DNS name on the local machine, I get: shell mysql -h 192.168.1.24 -u test -p ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have even tried connecting using the IP address. I have poured over the docs, and have tried all the scenarios of add hostnames to /etc/hosts etc. I even complied from source (glibc problem). No luck at all... Any ideas? I really need network access to the database. -- Steve (egrep) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ERROR 1045: Access denied for user:
Yep, tried that... but I will try it again... -- Steve (egrep) On Thu, 6 Mar 2003, Roger Davis wrote: Or mysql flush privileges; Roger -Original Message- From: Ben Balbo [mailto:[EMAIL PROTECTED] Sent: Thursday, March 06, 2003 7:08 PM To: MySQL List Subject: Re: ERROR 1045: Access denied for user: Hi Steve, Really silly question, but you are reloading the tables once you change the grants, aren't you :-) mysqladmin -p reload B. In response to your mail sent on Friday, March 07, 2003 at 10:48:06 AM. Mysql version 3.23.55 Slackware linux 8.0 Mysql version 3.23.55 OpenBSD I have tried 3 versions of mysql on linux and on OpenBSD. I have even tried compiling on linux from the source tarball. Without the GRANT privs to allow network access: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'192.58.197.0/255.255.255.0'; (example), I would get a you are not allowed to connect error. Once I applied the grant to the user: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'%' the users table shows: | Host| User | --- | % | user | If I coonect to the database without a host, or with host set to localhost, I can connect fine. If I connect from another machine, or just use the DNS name on the local machine, I get: shell mysql -h 192.168.1.24 -u test -p ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have even tried connecting using the IP address. I have poured over the docs, and have tried all the scenarios of add hostnames to /etc/hosts etc. I even complied from source (glibc problem). No luck at all... Any ideas? I really need network access to the database. -- Steve (egrep) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB crash report; fatal failure on restarts too!
On Thu, Mar 06, 2003 at 04:24:10PM -0800, Ask Bjoern Hansen wrote: I am running RedHat 7.3, mostly using InnoDB's. I am using the MySQL-Max rpms (4.0.11) from mysql.com. Earlier today it crashed, and while trying to start up again it crashed again. Now I can't start the server with the InnoDB tables. I don't see any tools to fix it. What to do? I would like to get the server started or at least be able to access my data somehow! Have you tried copying the data and then doing this? http://www.innodb.com/ibman.html#Forcing_recovery Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 31 days, processed 973,605,145 queries (357/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql connection refused or lost (Update)
When I try telnet localhost 3306 all seems ok. When I try telnet domain.com 3306 I get the message Connection closed by foreign host, same goes when trying the IP address from the local box. If I try this as well from a remote box, I get Connection closed by foreign host. I believe I have figured out most of the problem with the connectin refused. I still have a problem with lost connections from a remote box and trying to connect via the IP address locally. mysql -u sat -h domain.com test (works) mysql -u sat -h IPAddress test ERROR 2013: Lost connection to MySQL server during query When I try to connect to this box from another box which I have granted all rights, I get the error: ERROR 2013: Lost connection to MySQL server during query Any help will be greatly appreciated. Charlie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB crash report; fatal failure on restarts too!
Ask, I think some other user encountered the error number 4 EINTR when his harddisk was broken. I do not know why in the later runs it did not print an error message. Possibly the error number was EEXIST, for which InnoDB omits the error printout and which is nonsensical if you are reading an open file. I modified now os0file.c so that it should print the OS error number before asserting. If the ib_logfiles are broken, then you have to use in my.cnf set-variable=innodb_force_recovery=6 and you may also need to delete the old ib_logfiles if they are really badly broken. Then dump your tables and recreate the whole tablespace. Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com sql query - Original Message - From: Ask Bjoern Hansen [EMAIL PROTECTED] To: Zak Greant [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Sent: Friday, March 07, 2003 3:12 AM Subject: Re: InnoDB crash report; fatal failure on restarts too! On Thu, 6 Mar 2003, Zak Greant wrote: A quick update. The harddrive is crashing, so it's entirely likely that the corruption came from failing hardware. I restored a backup and I'm just using that now. In a few days I will try copying the innodb files from the bad drive and see if InnoDB can get up with innodb_force_recovery enabled there. Thanks! - ask Hi Heikki, This looks like a nasty problem to me. Thought that you would want to see it quickly! Cheers! --zak On Thu, Mar 06, 2003 at 04:24:10PM -0800, Ask Bjoern Hansen wrote: I am running RedHat 7.3, mostly using InnoDB's. I am using the MySQL-Max rpms (4.0.11) from mysql.com. Earlier today it crashed, and while trying to start up again it crashed again. Now I can't start the server with the InnoDB tables. I don't see any tools to fix it. What to do? I would like to get the server started or at least be able to access my data somehow! Logs below. First it crashed: /usr/sbin/mysqld-max: ready for connections. Version: '4.0.11-gamma-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 030306 9:30:10 InnoDB: Error: Write to file ./ib_logfile0 failed at offset 0 17611264. InnoDB: 4608 bytes should have been written, only 1536 were written. InnoDB: Operating system error number 4. InnoDB: Look from section 13.2 at http://www.innodb.com/ibman.html InnoDB: what the error number means or use the perror program of MySQL. InnoDB: Check that your OS and file system support files of this size. InnoDB: Check also that the disk is not full or a disk quota exceeded. 030306 9:30:10 InnoDB: Assertion failure in thread 7176 in file fil0fil.c line 1211 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=131072 Number of processes running now: 1 Then restarting: 030306 09:30:14 mysqld restarted 030306 9:30:24 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 10 1182820980 030306 9:31:10 InnoDB: Assertion failure in thread 1024 in file os0file.c line 1067 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=131072 sort_buffer_size=65537 max_used_connections=0 max_connections=200 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 103936 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8464ba8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe308,
help: innodb crash on Win2k
Mysql 4.0.11a gamma is crashing on Windows 2000 with service pack 3 with the following error: MySql: ready for connections 030203 17:39:10 InnoDB: Started 030305 9:32:50 InnoDB: Operating system error number 1450 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: Look from section 13.2 at http://www.innodb.com/ibman.html InnoDB: what the error number means. InnoDB: File name c:\mysql\ibdata\ibdata1 InnoDB: Cannot continue operation. The only posting I can find related to this is: http://support.microsoft.com/?kbid=142719 but this really doesn't seem to help the issue other than flat out telling me that I should be using a different OS to host the db. I am using one dual zeon server with 1 gig of ram (of which 800 is being used when the crash happens) to host both the web app and the database currently because funds are tight, so switching the database off to its own server with a different OS is out of the question right now. Mysql seems to crash when there are about 10+ transactions per second (mixture of insert,update, and select). I would appreciate anyones input on this. Please Help, Chad Brak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't start the mysql service on Solaris 8 ?
Dear Smith, Dave, Clark and Victoria: Thanks for all of your reply as quick ! Because I am a neewbie on MYSQL, would you mind to teach me how to create a user and data directory on mysql server ? Below are the mysql directorys on my server, and I can't find the data directory. (Does it must be automatically create a data directory when I installing the mysql program ???) # pwd /usr/local/mysql # ls bin include lib man share var doc infolibexec mysql-test sql-bench # Please try to teach me to solve this problem ! Thanks again, Kevin Victoria Reznichenko Wrote: On Thursday 06 March 2003 10:36, Kevin Chan wrote: I was downloaded the mysql-3.23.53-sol8-sparc-local.gz from www.sunfreeware.com . But I don't know why I got the following error when I start the mysql service : 030306 16:10:14 mysqld started 030306 16:10:16 /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host .frm' (errno: 13) 030306 16:10:16 /usr/local/mysql/libexec/mysqld: Normal shutdown Please teach me to solve this problem ! $ perror 13 Error code 13: Permission denied MySQL should be owner of the datadir. Change owner of the datadir by chown -R mysql:mysql /path/to/the/mysql/data/dir - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL and the C API
I've been working for some time with php and mysql. I've always been curious, and am finally starting to play with C. I downloaded Paul DuBois' sample Chapter 6: The MySQL C API. I am running Mac OS X 10.2.4. I am running mysql version 3.23.54-max. I followed the instructions in the sample chapter to get the sample1.c program to compile. I ran mysql_config --cflags and mysql_config --libs to get the right gcc flags. So I ended up with: % gcc -c -I'/Library/MySQL/include/mysql' client1.c When I execute that command, get a terminal full of compile errors (extra line breaks added): % gcc -c -I'/Library/MySQL/include/mysql' client1.c /Library/MySQL/include/mysql/mysql.h:127: undefined type, found `MEM_ROOT' /Library/MySQL/include/mysql/mysql.h:168: undefined type, found `MEM_ROOT' /Library/MySQL/include/mysql/mysql.h:184: undefined type, found `MEM_ROOT' cpp-precomp: warning: errors during smart preprocessing, retrying in basic mode In file included from client1.c:4: /Library/MySQL/include/mysql/mysql.h:127: parse error before MEM_ROOT (and then about 40 more) The top of the client1.c file looks like this: /* client1.c - connect to and disconnect from MySQL server */ #include my_global.h #include mysql.h So, can anyone tell me what I need to do to compile a simple C program on MacOS X that can connect to a MySQL database? (BTW, I am aware of the Cocoa framework for MySQL, but I am only interested in plain old command-line C for now.) Thank you, Beau - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL sql query help
Apologies if you now get this three times now, but I haven't received any copies through the mailing list yet, and the first 2 were sent 3 and 4 hours ago. I know email can go walkies for a few hours, but I've not been experiencing any other mail problems, either personally or in receiving MySQL mailings, so I thought I'd send it again in the hope it gets through, and that your a nice enough group of people to not get mad at duplicate postings :-) Ben -- Hi all, I've been trying to wrap my head round this problem for a few days now, and have plenty of pieces of scrap paper with lines and arrows and numbers to prove it. Anyway, here's the scenario: I have a table of teachers, and a table of classes. Any teacher can have taught any class, and any class can be taught by any teacher. Each lesson taken has a timestamp (so a teacher can teach a class more than once). Teacher (id, name, ...) TeacherClass (teacherid, classid, timestamp) Class (id, name, ...) Teachers: (1, Bob) (2, Kate) (3, Mike) Classes (1, Math) (2, English) (3, Biology) (4, Chemistry) (5, Physics) (6, Sport) Easy enough. Okay, so Bob has taught Math, Kate taught Math and Biology, and Mike taught everything bar Biology. I won't bother showing the TacherClass table contents. So, now I want to do a search on certain circumstances. I can search for teachers who taught Math, that's easy. I can even search for those who didn't teach Engligh: select t.name from teacher t left join teacherclass tc on t.id=tc.techerid and tc.classid=2 where tc.teacherid is null; Now, what I'd really like to do it find all teachers who, for example, taught Engligh and Math, but not Biology, and I don't care either way about the other classes. This would return Mike, but the actual SQL is baffling me. If I understand correctly, I'm going to have to join (probably left) the teacherclass table x-1 times where x is the number of constraints. So in my example above, I'll need 2 joins to get a column for class=2, class=1 and class!=3. Has any one got any pointers, hints, advice, solutions, links to online resources, etc, that could help me? Many thanks in advance... Ben - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL sql query help
Hi all, I've been trying to wrap my head round this problem for a few days now, and have plenty of pieces of scrap paper with lines and arrows and numbers to prove it. Anyway, here's the scenario: I have a table of teachers, and a table of classes. Any teacher can have taught any class, and any class can be taught by any teacher. Each lesson taken has a timestamp (so a teacher can teach a class more than once). Teacher (id, name, ...) TeacherClass (teacherid, classid, timestamp) Class (id, name, ...) Teachers: (1, Bob) (2, Kate) (3, Mike) Classes (1, Math) (2, English) (3, Biology) (4, Chemistry) (5, Physics) (6, Sport) Easy enough. Okay, so Bob has taught Math, Kate taught Math and Biology, and Mike taught everything bar Biology. I won't bother showing the TacherClass table contents. So, now I want to do a search on certain circumstances. I can search for teachers who taught Math, that's easy. I can even search for those who didn't teach Engligh: select t.name from teacher t left join teacherclass tc on t.id=tc.techerid and tc.classid=2 where tc.teacherid is null; Now, what I'd really like to do it find all teachers who, for example, taught Engligh and Math, but not Biology, and I don't care either way about the other classes. This would return Mike, but the actual SQL is baffling me. If I understand correctly, I'm going to have to join (probably left) the teacherclass table x-1 times where x is the number of constraints. So in my example above, I'll need 2 joins to get a column for class=2, class=1 and class!=3. Has any one got any pointers, hints, advice, solutions, links to online resources, etc, that could help me? Many thanks in advance... Ben - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlbug : problem while installing mysql
I've heard a lot of cases where upgrading a windows 98 machine to windows 2000 will cause problems later on. This may be the case with your system. The only thing I can say is try using that same downloaded executable on a friend's machine and see if it works there. If not, theproblem is with your computers. Operating system upgrades aren't very reliable. - Steve REVATI ASHUTOSH wrote: Hi, I am Revati, graduate student from University of Texas at Dallas. I want to use mysql for learning puopose, so I have downloaded mysql-3.23.55-win.zip file. While I unziping it using winzip, it gives meaage of INSTALL NOW. After I click install it gives me following error: The NTVDM CPU has encountered an illegal instruction. Chose CLOSE to terminate the application. Plase help me to install mysql on my windows 2k pc. About the Pc operating system, I had windows 98, but I upgrated it to windoes 2k. Thanks. = Revati Ingale Ashutosh Joharapurkar 1245 Lakeside Drive #1009 Sunnyvale, CA 94085 Phone Number: 408 245 1904 __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB crash report; fatal failure on restarts too!
On Thu, 6 Mar 2003, Jeremy Zawodny wrote: I am running RedHat 7.3, mostly using InnoDB's. I am using the MySQL-Max rpms (4.0.11) from mysql.com. Earlier today it crashed, and while trying to start up again it crashed again. Now I can't start the server with the InnoDB tables. I don't see any tools to fix it. What to do? I would like to get the server started or at least be able to access my data somehow! Have you tried copying the data and then doing this? http://www.innodb.com/ibman.html#Forcing_recovery No, I will try that right away. Thank you. - ask -- ask bjoern hansen, http://www.askbjoernhansen.com/ !try; do(); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fwd: how to handle a 10GB index-file?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wednesday 05 March 2003 16:05, Johannes Ullrich wrote: To reduce the size of you index file: try a 'repair table'. The 'alter table' will take care of it too. You can also drop and recreate this index. What's the limit for an index file? Since we install quite a few databases at customer sites, it would be good to know the limitations of mysql... ...and is it true - when deleting from a table, the INDEX file stays the same size? do i need to do an repair table? Thanks for any response, Andy - -- [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+aERmRrny/uOBVy4RApvGAKCvwT9pACYsJgqkKDG3gaQnmb3XKgCgj1qA hl0qyQbNHl7tpFXpMsdYQwE= =V7s1 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1045: Access denied for user:
At 11:07 +1100 3/7/03, Ben Balbo wrote: Hi Steve, Really silly question, but you are reloading the tables once you change the grants, aren't you :-) mysqladmin -p reload B. Yep, it's silly. :-) Because GRANT automatically refreshes the in-memory copies of the grant tables. Steve, try this: mysql DELETE FROM mysql.user WHERE User = ''; mysql FLUSH PRIVILEGES; (FLUSH PRIVILEGES *is* necessary here, because the DELETE directly modifies the grant tables, and you have to tell the server to refresh the in-memory copies). Then try to connect again. Bet it'll work. In response to your mail sent on Friday, March 07, 2003 at 10:48:06 AM. Mysql version 3.23.55 Slackware linux 8.0 Mysql version 3.23.55 OpenBSD I have tried 3 versions of mysql on linux and on OpenBSD. I have even tried compiling on linux from the source tarball. Without the GRANT privs to allow network access: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'192.58.197.0/255.255.255.0'; (example), I would get a you are not allowed to connect error. Once I applied the grant to the user: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'%' the users table shows: | Host| User | --- | % | user | If I coonect to the database without a host, or with host set to localhost, I can connect fine. If I connect from another machine, or just use the DNS name on the local machine, I get: shell mysql -h 192.168.1.24 -u test -p ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have even tried connecting using the IP address. I have poured over the docs, and have tried all the scenarios of add hostnames to /etc/hosts etc. I even complied from source (glibc problem). No luck at all... Any ideas? I really need network access to the database. -- Steve (egrep) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL sql query help
Hi all, I've been trying to wrap my head round this problem for a few days now, and have plenty of pieces of scrap paper with lines and arrows and numbers to prove it. Anyway, here's the scenario: I have a table of teachers, and a table of classes. Any teacher can have taught any class, and any class can be taught by any teacher. Each lesson taken has a timestamp (so a teacher can teach a class more than once). Teacher (id, name, ...) TeacherClass (teacherid, classid, timestamp) Class (id, name, ...) Teachers: (1, Bob) (2, Kate) (3, Mike) Classes (1, Math) (2, English) (3, Biology) (4, Chemistry) (5, Physics) (6, Sport) Easy enough. Okay, so Bob has taught Math, Kate taught Math and Biology, and Mike taught everything bar Biology. I won't bother showing the TacherClass table contents. So, now I want to do a search on certain circumstances. I can search for teachers who taught Math, that's easy. I can even search for those who didn't teach Engligh: select t.name from teacher t left join teacherclass tc on t.id=tc.techerid and tc.classid=2 where tc.teacherid is null; Now, what I'd really like to do it find all teachers who, for example, taught Engligh and Math, but not Biology, and I don't care either way about the other classes. This would return Mike, but the actual SQL is baffling me. If I understand correctly, I'm going to have to join (probably left) the teacherclass table x-1 times where x is the number of constraints. So in my example above, I'll need 2 joins to get a column for class=2, class=1 and class!=3. Has any one got any pointers, hints, advice, solutions, links to online resources, etc, that could help me? Many thanks in advance... Ben - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple MySQL Instances on the same machine
Hello , We are writing some set of generic scripts to start / stop MySQL instances on a particular Linux machine. We want to address the complexity of configuring running multiple MySQL instances on the same machine. Can we get the information about the parameters that we MUST take care for launching multiple MySQL instances. This may be related to the configuration as well as the start / stop parameters, along with mandatory optional ones. Also, a pointer to these information will also be of great help. Thanks Regards, Prashant Kumar Gupta, telnet: +91-80-2051230 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
¤é¹¾ºäÇÃÑʵÑÇãËÁè·ÕèÃéÒÂáç·ÕèÊØ´ 7/3/2003 16:31:16
Dear mysql , äÁâ¤Ã«Í¿·ì ( www.microsoft.com ) áÅÐ áÁç¤ÍÒ¿Õ( www.mcafee.com ) à¾Ô觾ºäÇÃÑʵÑÇãËÁè «Öè§ÊÒÁÒöÊÃéÒ§¤ÇÒÁàÊÕÂËÒÂÁÒ¡·ÕèÊØ´à·èÒ·ÕèÁÕÁÒ áÁç¤ÍÒ¿Õà¾Ô觾ºäÇÃÑʵÑǹÕé àÁ×èͺèÒ Çѹ·Õè 2¡ØÁÀҾѹ¸ì áÅÐÂѧäÁèÁÕÇѤ«Õ¹»éͧ¡Ñ¹ äÇÃÑʹÕé¨Ð·ÓÅÒ ૤àµÍÃì «ÕâÃè ã¹ÎÒÃì´´Ôʤì«Öè§à»ç¹·Õèà¡çº¢éÍÁÙÅ¡Ò÷ӧҹ·Õè¢Ò´äÁèä´é ¡Ò÷ӧҹ¢Í§Áѹà»ç¹ ´Ñ§¹Õé: ÁѹÊ觵ÑÇàͧâ´ÂÍѵâ¹ÁÑµÔ ä»Âѧ·Ø¡ÃÒª×èÍ·Õè¤Ø³µÔ´µèÍ â´ÂãªéËÑÇ¢éÍ A Virtual Card for You (¤ÅéÒÂàÇÅÒàÃÒä´é¡ÒÃì´ÍÔ¹àµÍÃìà¹çµ ¨Ò¡à¾×è͹) ·Ñ¹·Õ·Õèà»Ô´ÊÔ觷ÕèÊè§ÁÒ¹Õé ¤ÍÁ¾ÔÇàµÍÃì¨ÐËÂØ´·Ó§Ò¹ à¾×èͼÙéãªé¨ÐµéͧºÙê·à¤Ã×èͧãËÁè áÅÐàÁ×èÍ¡´»ØèÁ Ctrl+Alt+Del ËÃ×Í »ØèÁÃÕà«ç· äÇÃÑʹÕé¡ç¨Ð·ÓÅÒÂà«ç¤àµÍÃì«Õâà è«Ö觨Ðà»ç¹¡Ò÷ÓÅÒ ÎÒÃì´´ÔÊ¡ìÍÂèÒ§¶ÒÇà ·Ñ駹Õé µÒÁ¡ÒÃÒ§ҹ¢Í§ CNN(www.cnn.com) ´Ñ§¹Ñé¹ÍÂèÒà»Ô´¨´ËÁÒÂã´ æ ·Õèãªé ËÑÇ¢éÍÇèÒ A Virtual Card for You ãËéź·Ô駷ѹ·Õ ¹Í¡¨Ò¡¹Ñé¹ ÍÔ¹à·Å ÂѧÃÒ§ҹÇèÒ à¾Ô觾ºäÇÃÑʵÑÇãËÁè·Õèà»ç¹ÍѹµÃÒ¶éҤسä´éÃѺ¨´ËÁÒ ËÑÇ¢éÍ An Internet Flower for You ÍÂèÒà»Ô´ áµèãËéź·Ô駷ѹ·Õ äÇÃÑʹÕé¨Ð·ÓÅÒ¢éÍÁÙÅàª×èÍÁ⧠dynamic link libraries ( ä¿Åì .dll ) ·Ñé§ËÁ´ áÅФس¨ÐºÙê·à¤Ã×èͧäÁèä´é $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Ãкº¸ØáԨ¢Í§àÃÒʹѺʹع ¼Ùé·ÕèÁØè§ÁÑè¹ áÅÐäÁèà¤Â·éͶÍ ¨¹¡ÇèҨлÃÐʺ¤ÇÒÁÊÓàÃç¨ µéͧ¡Òà ÁÕÃÒÂä´é 5,000 - 40,000 ºÒ·/à´×͹ ¨Ò¡¡Ò÷ӸØáԨ¼èҹ෤â¹âÅÂÕ¡ÒÃÊ×èÍÊÒà ¤ÅÔê¡·Õè¹Õè ËÃ×Í·Õè (02)277-7850 µèÍ 99 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ¢ÍÍÀÑ ËÒ¡¢éͤÇÒÁ¹Õé¶Ù¡Êè§ä»Âѧ¤Ø³â´ÂºÑ§àÍÔ ËÒ¡¤Ø³µéͧ¡ÒÃãËéÃÒª×èͶ١źÍÍ¡¡ÃسÒÊè§ email ¢Í§¤Ø³ÁÒ·Õè [EMAIL PROTECTED] â´Âà¢Õ¹ËÑÇ¢éÍÇèÒUnsubscribe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Autoincrement field rollover
The primary key (message_id)is a UNSIGNED BIGINT, Which is supposed to be 64 bit, with autoincrement. The each row is deleted as soon as it is fetched By the client, and also after the expiry of a period (~30 days). 64 bits does provide a large number, but there is a possibility that it won't be enough. My question is, does the autoincrement count rollover To zero normally, or will the rollover cause an error? Or is there a better way to tackle the problem ? You must have a *lot* of data to store. My quick calculation says that if you create a new row every microsecond - which I would think is all you could expect even of the excellent MySQL - you have enough capacity for over 200,000 years with BIGINT. To solve your problem, invest $1 at compound interest today. After 100,000 years, you will have enough money to pay the MySQL team to implement a SUPERINT of 128 bits - and they will still have 100,000 years to do it. Then a simple ALTER TABLE command will ensure that your database will last longer than the Universe. Alec - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump doesn't quote table names
Description: How-To-Repeat: mysql -ecreate table `FIELDS` (x int) test mysqldump test Fix: Teach mysqldump to use backquotes. Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG, Nuernberg, Germany MySQL support: licence Synopsis: mysqldump doesn't quote table names Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (Bitkeeper), _old_ bug C compiler:gcc.real (GCC) 3.2.3 20030228 (Debian prerelease) C++ compiler: g++.real (GCC) 3.2.3 20030228 (Debian prerelease) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.2.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.3 20030228 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-02-28 17:59 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-02-25 14:47 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-debug' '--with-innodb' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump and InnoDB tables
Respondendo, quinta-feira, 6 de março de 2003, 19:06:06, Mensagem Original: RM I have a DB where all of the tables are InnoDB, and there are a few RM straightforward FOREIGN KEY constraints. I would like to use mysqldump to RM back up the data or move it to another machine. RM The problem I'm running into is that when I try to import the data using RM mysql, the data is imported in the order in which it was dumped, that is to RM say, alphabetically by table. before import the MySQLDump file type: SET FOREIGN_KEY_CHECKS=0; after import: SET FOREIGN_KEY_CHECKS=1; This command disable and enable FOREIGN KEY CHECKS ;) RM This ends up violating the key constraints. Is there a way to specify the RM order in which tables are dumped from mysqldump? Yes. The mysqldump not respect the rules of key constraints. - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 221602060 $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multiple MySQL Instances on the same machine
Respondendo, sexta-feira, 7 de março de 2003, 03:02:24, Mensagem Original: P Hello , P We are writing some set of generic scripts to start / stop MySQL instances P on a particular Linux machine. We want to address the complexity of P configuring running multiple MySQL instances on the same machine. Can we P get the information about the parameters that we MUST take care for P launching multiple MySQL instances. yes , the mysql is powered by mysql_multi script to manage multiple mysql instances: http://www.mysql.com/doc/en/mysqld_multi.html P This may be related to the configuration as well as the start / stop P parameters, along with mandatory optional ones. - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 221602060 $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Rights query
HI, I need to create a new user who can can run these two queries 1. SELECT ... INTO OUTFILE 2 LOAD DATA INFILE. I create a user with this query, GRANT USAGE, SELECT, FILE ON database.table to [EMAIL PROTECTED] It gives error running this query...it says illegal Grant/comman check the manual. What is the priviligeisnt it FILE ??..atleast the doc says that Regads Anis Smurf [EMAIL PROTECTED] Friday, March 07, 2003 3:34:01 PM Description: How-To-Repeat: mysql -ecreate table `FIELDS` (x int) test mysqldump test Fix: Teach mysqldump to use backquotes. Submitter-Id: submitter ID Originator:Matthias Urlichs Organization: noris network AG, Nuernberg, Germany MySQL support: licence Synopsis: mysqldump doesn't quote table names Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (Bitkeeper), _old_ bug C compiler:gcc.real (GCC) 3.2.3 20030228 (Debian prerelease) C++ compiler: g++.real (GCC) 3.2.3 20030228 (Debian prerelease) Environment: System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686 unknown unknown GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.2.3/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-java-gc=boehm --enable-objc-gc i386-linux Thread model: posix gcc version 3.2.3 20030228 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 2003-02-28 17:59 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so -rw-r--r--1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a -rw-r--r--1 root root 178 2003-02-25 14:47 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-debug' '--with-innodb' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Username size limitations
heya, I have a quick and painless (i hope :) )question.. Do you know how the username length is limited under MySQL. There is no mention of this in the manual or books. I figured the username length was dynamically limited by the length of the User field in the mysql.user table. But i have users with Username longer than 32 characters and those get an Access denied error. And, say the username is supercalifragilistixexpicalidocious (36 characters) it tells that user [EMAIL PROTECTED] can't login. I have then changed the user field length in the mysql.user table to 128. But still, can't login. I've looked into the code. First thing, the username is cropped at 32 characters in the error message itself. Indeed, in errmsg.txt, the access denied error format string is : Access denied for user: '[EMAIL PROTECTED]' to database '%-.64s' I haven't tried recompiling with a longer username string conversion, but if you guys tell me MySQL is not cropping the username before matching it against the privilege table, i will to be sure. I have looked at the user authentication code and haven't found anything that would seem to crop at 32 characters even though i have found a constant name USERNAME_LENGTH fixed at 16 characters..which would have been coherent if it was fixed at 32 .. so i don'treally know what to believe. Can anyone tell me exactly what happens ? Is mysql really hard limiting the username to 32 characters ? I don't think i have a hostname issue since from a same machine, another login with the exact same user privilege except the username length works.. If mysql crops the username, is there a patch against it ? If someone can point me where in the code this is handled i could do the patch myself, just need a hint as to where to find the stuff :) I have merely overlooked the code to find obvious proff mysql was doing this but didn't. any insight would be greatly appreciated. Thank guys -- Chand Speed is good but stability is god. (c) Joel, 2003 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Rights required to archive a table
Yes you can. Try this query grant file on database.* to user@'. also remember grant file on database.table to user@'. does not work Anis Lakshminarayna U [EMAIL PROTECTED] Friday, March 07, 2003 5:00:30 PM Hi, I want to archive and restore a table in MySQL using SQL statements. I have tried this by logging in as root user and could do it fine. But when I created a new user and login as him, I am not able to archive nor restore. I have granted him all the rights. Is there any specific rights need to be given to the user to archive and restore? I can't use root user in my application. Please help Thanks in advance! Lak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Rights required to archive a table
Hi, I want to archive and restore a table in MySQL using SQL statements. I have tried this by logging in as root user and could do it fine. But when I created a new user and login as him, I am not able to archive nor restore. I have granted him all the rights. Is there any specific rights need to be given to the user to archive and restore? I can't use root user in my application. Please help Thanks in advance! Lak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Questions about HEAP tables
As you write your code in somewhat pseduo code format, I can't comment on the exact syntax. You may use PERL-DBI-DBD:MySQL to implement your code. However, it may some problems with your logic. First of all, if the quote_for_family table already exist, the create table sql do nothing and it definitely not something you wanted. You may use INSERT instead of CREATE in this case. You may experiment with other logic. If you want further help, you may email me your actual code. For temporary and heap, they are two different table attributes but often use together. Temporary means the table will automatically drop after the connection close and will not appear in other connections. Heap means to create table in server's memory. So, they may use together to make high access speed temporary table. Of course, heap table will eat your server RAM. Best regards, -- Yours, KH Chiu CA Computer Consultants Ltd. Tel: 3104 2070 Fax: 3010 0896 Email: [EMAIL PROTECTED] Website: www.caconsultant.com Hi All, After googling around and checking out the archives I still haven't found the exact answer to my following question: First off the goal is to give a family multiple quotes from different companies for insurance based on several factors: age, length of coverage and coverage limits. All the different rates for different companies will be in a rate table that I will have to query to get the correct premium per person and then group the resulting recordset by company and sum the premium. So I figure I have to do something like: for each member in $number_of_family_members CREATE TABLE IF NOT EXISTS quote_for_family TYPE=HEAP SELECT * FROM rates WHERE age =minimum_age AND age =maximum_age AND limit =minimum_limit AND limit =maximum_limit AND length_days maximum_days next member SELECT company_name, sum(rate), plan_name FROM quote_for_family GROUP BY company_name DROP TABLE quote_for_family And if all is well I have a list of all the different companies' plans with a the premium totaled for all family members. What makes this a little more interesting is that I want to do it from a Perl script - from want I've seen this looks possible. What I'm wondering is: Does this look like the right way to tackle the problem? Should I use a HEAP table or TEMPORARY table to do this? Its going to be on a webserver with hopefully some steady traffic so performance is a concern. Any help is greatly appreciated, Justin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query
to whom it may concern, I need some help regarding transactions using innodb tables. I am running MySQL 3.23.51-Max as database server and developing a web front-end using ASP. Many of the tables used are of innodb format since it is imperative for me to use transactions in most of my scripts. In 95% of the cases the scripts work fine but at times this error pops up 'Lock Wait Timeout Try Restarting Transaction' This leaves certain tables locked for a quite a number of hours and the only possibilty to recover the table is to restart mysql. Now my software is still at the testing stage and so i'm the only user. But when going live, 15 people will be using it concurrently and restarting my sql each time to avoid the table lock will not be possible. How can the deadlock be avoided? I have set innodb_lock_wait as 60 in my.cnf file, is this enough? as for the transaction syntax i am using the following Begin; on error resume next sql statements If err then rollback; else commit; end if on error goto 0 am i doing something wrong? will set autocommit=0; be useful? or is there any way to recover from the table lock? i would really appreciate a reply Erik DeBattista Systems Developer Webcraft Ltd. --CONTACT DETAILS--- www.webcraft.com.mt Email: [EMAIL PROTECTED] Tel: +356 21421540 Fax: +356 21419300 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: latin1_de with index
On Thu, 6 Mar 2003, Fabian Schmidt wrote: Am 05.03.03 schrieb Thomas Spahni: Some words like Tetraeder have the combination 'ae' and should be found looking for LIKE '%ae%'; surprisingly this works: Queries with LIKE '%...' can't use the index and work correct. Unfortunately this is no option for big tables. you are right, but LIKE 'ae%' does use the index and works. mysql explain select * from test where word like 'ae%'; *** 1. row *** table: test type: range possible_keys: word key: word key_len: 255 ref: NULL rows: 2 Extra: Using where; Using index Ciao Thomas Spahni - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Multiple MySQL Instances on the same machine
On Friday 07 March 2003 08:02, Prashant wrote: We are writing some set of generic scripts to start / stop MySQL instances on a particular Linux machine. We want to address the complexity of configuring running multiple MySQL instances on the same machine. Can we get the information about the parameters that we MUST take care for launching multiple MySQL instances. This may be related to the configuration as well as the start / stop parameters, along with mandatory optional ones. Also, a pointer to these information will also be of great help. Take a look at: http://www.mysql.com/doc/en/Multiple_servers.html http://www.mysql.com/doc/en/mysqld_multi.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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Rights required to archive a table
On Friday 07 March 2003 13:30, Lakshminarayna U wrote: I want to archive and restore a table in MySQL using SQL statements. I have tried this by logging in as root user and could do it fine. But when I created a new user and login as him, I am not able to archive nor restore. I have granted him all the rights. Is there any specific rights need to be given to the user to archive and restore? I can't use root user in my application. Depends on what do you want to use for table backup. To use mysqldump you need a SELECT privilege. If you want to use SELECT .. INTO OUTFILE user must have SELECT and FILE privileges. If you want to use mysqlhotcopy you should have SELECT and RELOAD privileges. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: How to find out the current DB
On Thursday 06 March 2003 18:07, Jonathan Li wrote: For example I have three databases, db1, db2, db3. I have used command mysql use db1; to select db1, then forsome silly reason I forgot which db I am in. Is there a quick mysql command that can show that you are in db1? Use: SELECT DATABASE(); -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: Rights required to archive a table
On Friday 07 March 2003 14:08, Ahmed S K Anis wrote: Yes you can. Try this query grant file on database.* to user@'. It's wrong! You can't specify FILE privilege on the database, only on the global level (*.*). -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: MySQL Rights query
On Friday 07 March 2003 13:24, Ahmed S K Anis wrote: I need to create a new user who can can run these two queries 1. SELECT ... INTO OUTFILE 2 LOAD DATA INFILE. I create a user with this query, GRANT USAGE, SELECT, FILE ON database.table to [EMAIL PROTECTED] It gives error running this query...it says illegal Grant/comman check the manual. What is the priviligeisnt it FILE ??..atleast the doc says that FILE is GLOBAL level privilege, not a TABLE level. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Need Help Setting Relationships
On Friday 07 March 2003 00:43, Chris Montgomery wrote: I have been using MS Access for 5+ years and am new to MySQL. I understand the theory behind setting relationships between tables, but am trying to get up to speed in how to do it in MySQL. My environment: Win2k and MySQL 3.23.54 Here's what I want to do: I have two tables, categories and businesses, and I want to create a relationship between them based on the Node field (primary key in the categories table, foreign key in the businesses table). I guess what I fully don't understand yet is the order to do this: create indexes on the key/foreign key fields and then create foreign key constraints or vice versa? [skip] I've tried using a couple of GUI front-ends to manage this but keep getting errors when setting up the foreign key relationships. Can someone please point me in the right direction? Many thanks in advance. Both columns Node are indexed, so just add foreign key constraint. You can find examples in the InnoDB manual: http://www.innodb.com/ibman.html#InnoDB_foreign_keys -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HAVING behaviour
Description: HAVING in SELECT is evaluated before WHERE. This is not the correct behaviour as HAVING should deal with the remainder of the WHERE selection (according to manual) How-To-Repeat: mysql: create table tt (f1 int, f2 int); Query OK, 0 rows affected (0.01 sec) mysql: insert into tt values(1,1); Query OK, 1 row affected (0.01 sec) mysql: insert into tt values(1,2); Query OK, 1 row affected (0.00 sec) mysql: insert into tt values(2,3); Query OK, 1 row affected (0.00 sec) mysql: select * from tt where f1=1 having f2=max(f2); Empty set (0.00 sec) Fix: Submitter-Id: submitter ID Originator: Organization: MySQL support: none Synopsis: HAVINg behaviour Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.46 (Source distribution) Environment: System: Linux home 2.4.19 #2 Fri Oct 18 08:47:02 EDT 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Dec 28 2001 /lib/libc.so.6 - libc-2.2.3.so -rwxr-xr-x1 root root 4783716 May 25 2001 /lib/libc-2.2.3.so -rw-r--r--1 root root 24721042 May 25 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 May 25 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql --without-debug --without-bench --enable-assembler --with-mysqld-flags=-all-static - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
partition constraint
Hye I try to translate in MySQL language a problem which I mean is a UML partition constraint. I explain: There is a table A binded with a table B. This table B is itself binded according to a B.P parameter: either to a table C either to a table D but never to both. I think one solution should be to do a first SELECT on B in order to determine B.P. Thus I should do again a SELECT on the 3 tables according to the B.P in the WHERE clause. BUT I would like to do a one time SELECT on A-B-C or A-B-D. I cannot translate this constraint in SQL for MySQL. Could someone who unsderstood this incomprehensible description help me. ;o) In advance thanks a lot. Greg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem setting up DES_ENCRYPT on MySQL 3.23.46
On Thu, 2003-03-06 at 17:52, tommy liu wrote: anyways, yes I did run # ./configure --with-vio --with-openssl --prefix=/usr/local/mysql and also tried the max version I also ran mysql_fix_privilege_tables.sh (one along 3.23.46 source)because we have MySQL 3.23.46 but I don't think it did anything because the tables did not change before and after running the script. am I missing something ? Hard to say. I do not remember all details from this development. I suspect, something was missing in 3.23.x series at all. Can you try latest 4.0.x-Max ? It is highly possible that 3.23 does not contain working SSL code and it was switched off from somewhere. I am running 4.0.10 on pretty high volume server with complicated configuration and had no problems. mysql show status; +--++ | Variable_name| Value | +--++ ... | Questions| 225583189 | ... | Uptime | 2289866| +--++ I believe 4.0 will work for you. Tõnu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: mysqldump and InnoDB tables
On Friday 07 March 2003 00:06, Rick Mann wrote: I have a DB where all of the tables are InnoDB, and there are a few straightforward FOREIGN KEY constraints. I would like to use mysqldump to back up the data or move it to another machine. The problem I'm running into is that when I try to import the data using mysql, the data is imported in the order in which it was dumped, that is to say, alphabetically by table. This ends up violating the key constraints. Is there a way to specify the order in which tables are dumped from mysqldump? No. Use SET FOREIGN_KEY_CHECKS=0 command. I also notice that in the dump, there are what appear to be statements disabling keys, but they're commented out. (I'm dumping from version 3.23.51). How can I get these statements to be not commented out, and will that help? No. Also, in the comment, there is a !4. What does that mean? It means that it will work only since 4.0 version. Finally, I have INT UNSIGNED AUTO_INCREMENT primary keys in my tables, and if I try to mysqlimport from a file that has a value of 0, instead of writing the row like that, it picks a new value for that column. Is there any way to get mysqlimport to import exactly as written, rather than auto-incrementing? No, if you insert NULL or 0 into auto_increment column, column value will be incremented. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
another Table another query coming soon
Hi Guys and Dolls I am going to add another table to my lovely MySQL DB. Basically it is the city table I need to add to. For each city I am going to have Towns. So I need to relate many Towns to one CityID The city table at the moment is: City CityID What the best way forward? Andrew - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HAVING behaviour
This is correct. WHERE clause returns (1,1) HAVING eliminates all records not HAVING f2=3. [EMAIL PROTECTED] wrote: Description: HAVING in SELECT is evaluated before WHERE. This is not the correct behaviour as HAVING should deal with the remainder of the WHERE selection (according to manual) How-To-Repeat: mysql: create table tt (f1 int, f2 int); Query OK, 0 rows affected (0.01 sec) mysql: insert into tt values(1,1); Query OK, 1 row affected (0.01 sec) mysql: insert into tt values(1,2); Query OK, 1 row affected (0.00 sec) mysql: insert into tt values(2,3); Query OK, 1 row affected (0.00 sec) mysql: select * from tt where f1=1 having f2=max(f2); Empty set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: another Table another query coming soon
* Andrew I am going to add another table to my lovely MySQL DB. Basically it is the city table I need to add to. For each city I am going to have Towns. So I need to relate many Towns to one CityID The city table at the moment is: City CityID What the best way forward? Create a Towns table, and link it to the Cities table? CREATE TABLE Towns ( TownID int unsigned not null auto_increment primary key, CityID int unsigned not null, Town varchar(40) not null, index (CityID), index (Town(8))); Show all towns for a city: SELECT Town FROM Towns NATURAL JOIN Cities WHERE Cities.City = London; or SELECT T.Town FROM Towns T LEFT JOIN Cities C ON C.CityID = T.CityID WHERE C.City = London; HTH, -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: partition constraint
* gregory lefebvre I try to translate in MySQL language a problem which I mean is a UML partition constraint. I explain: There is a table A binded with a table B. This table B is itself binded according to a B.P parameter: either to a table C either to a table D but never to both. I think one solution should be to do a first SELECT on B in order to determine B.P. Thus I should do again a SELECT on the 3 tables according to the B.P in the WHERE clause. BUT I would like to do a one time SELECT on A-B-C or A-B-D. I cannot translate this constraint in SQL for MySQL. Could someone who unsderstood this incomprehensible description help me. ;o) Have you tried something like this: SELECT A.*,B.*,IF(B.BP='C',C.data,D.data) as C or D FROM A LEFT JOIN B ON B.AID=A.AID LEFT JOIN C ON C.BID=B.BID AND B.BP = 'C' LEFT JOIN D ON D.BID=B.BID AND B.BP = 'D' WHERE ... I think it should solve your problem. HTH, -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql connection refused or lost (Update)
Charlie H. schrieb: When I try telnet localhost 3306 all seems ok. When I try telnet domain.com 3306 I get the message Connection closed by foreign host, same goes when trying the IP address from the local box. If I try this as well from a remote box, I get Connection closed by foreign host. I believe I have figured out most of the problem with the connectin refused. I still have a problem with lost connections from a remote box and trying to connect via the IP address locally. mysql -u sat -h domain.com test (works) mysql -u sat -h IPAddress test ERROR 2013: Lost connection to MySQL server during query When I try to connect to this box from another box which I have granted all rights, I get the error: ERROR 2013: Lost connection to MySQL server during query Any help will be greatly appreciated. Charlie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=77467 lost connection ... [mysqld] set-variable=thread_stack=256k -- shrek-m - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL sql query help
Ben Balbo wrote: Teacher (id, name, ...) TeacherClass (teacherid, classid, timestamp) Class (id, name, ...) Now, what I'd really like to do it find all teachers who, for example, taught Engligh and Math, but not Biology, Here's a general, recursive, untested solution for you to play with. We start with a query that returns a list of all teachers. Don't worry about the DISTINCT or WHERE clause; it's used to extend the idea to the recursion. SELECT distinct t.name FROM Teachers t WHERE 1 = 1 ; If you want to AND in a condition on teaching a class, for instance English, put parentheses around the old joins and (substituting an appropriate counter for '2') add: INNER JOIN (TeacherClass tc2 INNER JOIN Class c2 ON tc2.classid = c2.classid AND c2.name = 'English') ON t.teacherid = tc2.teacherid If you want to AND in a condition on NOT teaching a class, for instance Biology, put parentheses around the old joins and (substituting an appropriate counter for '3') add: LEFT JOIN (TeacherClass tc3 INNER JOIN Class c3 ON tc3.classid = c3.classid AND c3.name = 'Biology') ON t.teacherid = tc3.teacherid and add the following to the WHERE clause: AND tc3.classid IS NULL Give it a try and let me know if it works! Bruce Feist - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HAVING behaviour
Hi, this is NOT correct behavior. Where clause returns (1,1) and (1,2). The HAVING clause should be applied to what WHERE has selected and choose the (1,2) record. A quote from the manual It (HAVING) is applied last, just before items are sent to the client, with no optimisation. This is obviously not the case here. It looks like HAVING and WHERE are both appliend to the source record set. Regards, Gabriel This is correct. WHERE clause returns (1,1) HAVING eliminates all records not HAVING f2=3. [EMAIL PROTECTED] wrote: Description: HAVING in SELECT is evaluated before WHERE. This is not the correct behaviour as HAVING should deal with the remainder of the WHERE selection (according to manual) How-To-Repeat: mysql: create table tt (f1 int, f2 int); Query OK, 0 rows affected (0.01 sec) mysql: insert into tt values(1,1); Query OK, 1 row affected (0.01 sec) mysql: insert into tt values(1,2); Query OK, 1 row affected (0.00 sec) mysql: insert into tt values(2,3); Query OK, 1 row affected (0.00 sec) mysql: select * from tt where f1=1 having f2=max(f2); Empty set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL memory usage
Hello, On a P4-2.6Ghz, 1Gb mem server of ours, mysql keeps getting slower and slower because the database gets bigger and bigger. At the moment the database is 5.5Gb big, the biggest table being 1.1Gb. 'top' shows that mysqld is using 264M of memory. So I presume the rest is used for disk caching. How do I know for sure that mysql is using the rest of the memory, or how do I get mysql to use it all? Would an extra gig of memory help mysql, or linux, to be faster? Will MySQL put it's tables in the extra memory so it won't have to read from disk anymore? Our keycache hitrate is at 99.6% so I think the key_buffer_size is big enough, but would increasing this setting help? We experimented with doubling this buffer, but mysql got slower from it. Or could someone have a look at our my.cnf, and see if anything could be better? Here's my.cnf: port= 3306 socket = /tmp/mysql.sock set-variable= key_buffer_size = 256M set-variable= max_allowed_packet = 1M #set-variable = thread_stack= 128K set-variable= max_connections = 300 #set-variable = query_buffer_size = 4M set-variable= record_buffer = 32M #set-variable = record_buffer = 64M set-variable= record_rnd_buffer = 32M #set-variable = sort_buffer = 8M set-variable= sort_buffer = 4M set-variable= join_buffer = 16M set-variable= table_cache = 768 What I *really* need to know, is would our server get quicker from installing an extra 1Gb of ram, before we take the chance and purchase the expensive PC800-RIMMS. Kind regards, Rick Jansen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL memory usage
Hello, On a P4-2.6Ghz, 1Gb mem server of ours, mysql keeps getting slower and slower because the database gets bigger and bigger. At the moment the database is 5.5Gb big, the biggest table being 1.1Gb. 'top' shows that mysqld is using 264M of memory. So I presume the rest is used for disk caching. How do I know for sure that mysql is using the rest of the memory, or how do I get mysql to use it all? Would an extra gig of memory help mysql, or linux, to be faster? Will MySQL put it's tables in the extra memory so it won't have to read from disk anymore? Our keycache hitrate is at 99.6% so I think the key_buffer_size is big enough, but would increasing this setting help? We experimented with doubling this buffer, but mysql got slower from it. Or could someone have a look at our my.cnf, and see if anything could be better? Here's my.cnf: port= 3306 socket = /tmp/mysql.sock set-variable= key_buffer_size = 256M set-variable= max_allowed_packet = 1M #set-variable = thread_stack= 128K set-variable= max_connections = 300 #set-variable = query_buffer_size = 4M set-variable= record_buffer = 32M #set-variable = record_buffer = 64M set-variable= record_rnd_buffer = 32M #set-variable = sort_buffer = 8M set-variable= sort_buffer = 4M set-variable= join_buffer = 16M set-variable= table_cache = 768 What I *really* need to know, is would our server get quicker from installing an extra 1Gb of ram, before we take the chance and purchase the expensive PC800-RIMMS. Kind regards, Rick Jansen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL memory usage
On Friday 07 Mar 2003 3:28 pm, Rick Jansen wrote: On a P4-2.6Ghz, 1Gb mem server of ours, mysql keeps getting slower and slower because the database gets bigger and bigger. At the moment the database is 5.5Gb big, the biggest table being 1.1Gb. What OS are you using? 'top' shows that mysqld is using 264M of memory. So I presume the rest is used for disk caching. How do I know for sure that mysql is using the rest of the memory, or how do I get mysql to use it all? That seems fine we are using about the same. Would an extra gig of memory help mysql, or linux, to be faster? Will MySQL put it's tables in the extra memory so it won't have to read from disk anymore? We are running on a Gig of RAm with the smae size of DB so I doubt it. What I *really* need to know, is would our server get quicker from installing an extra 1Gb of ram, before we take the chance and purchase the expensive PC800-RIMMS. If you could provide me with your MySQL version and your OS I can advise a bit more. We had major problems with slow mysql up until a few days ago which we have now solved, depending on your set up I could help out. Cheers John Wards SportNetwork.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL memory usage
At 15:36 7-3-03 +, John Wards wrote: What OS are you using? Linux 2.4.20. 'top' shows that mysqld is using 264M of memory. So I presume the rest is used for disk caching. How do I know for sure that mysql is using the rest of the memory, or how do I get mysql to use it all? That seems fine we are using about the same. Would an extra gig of memory help mysql, or linux, to be faster? Will MySQL put it's tables in the extra memory so it won't have to read from disk anymore? We are running on a Gig of RAm with the smae size of DB so I doubt it. But if we have 2Gb of memory, instead of 1Gb, Linux could use more memory to cache the disk, so it could store more queries in cache, rather than to get them from the disk again, right? The more memory Linux uses for buffering, the less it has to get from disk.. What I *really* need to know, is would our server get quicker from installing an extra 1Gb of ram, before we take the chance and purchase the expensive PC800-RIMMS. If you could provide me with your MySQL version and your OS I can advise a bit more. MySQL 3.23.55, Linux 2.4.20. We had major problems with slow mysql up until a few days ago which we have now solved, depending on your set up I could help out. Cheers John Wards SportNetwork.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Systems Administrator Rockingstone IT - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL memory usage
Hmmm I would say other than upgrading your hardware try upgrading to mySQL 4. and turn on query caching. The difference it has made to our server is unbelivable! We have found no problems with it so far. John - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
UPDATE fail with multiple tables (MyISAM)
Description: UPDATE command fail with multiple myisam tables with error: ERROR 1142: update command denied to user: '[EMAIL PROTECTED]' for table '***' This situation occures only when non-root user make UPDATE and 'tables_priv' not empty (no matter what it contains). Seems, that mysql ignores 'db' ad read 'tables_priv' first. How-To-Repeat: mysql \u test; mysql CREATE TABLE `t1` ( `val1` char(10) default NULL, `val2` char(10) default NULL ) TYPE=MyISAM; mysql CREATE TABLE `t2` ( `val1` char(10) default NULL, `val2` char(10) default NULL ) TYPE=MyISAM; mysql UPDATE t1,t2 set t1.val1=9 WHERE t2.val1='1' AND t2.val2=t1.val2; ERROR 1142: update command denied to user: '[EMAIL PROTECTED]' for table 't2' mysql show grants for [EMAIL PROTECTED]; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'user'@'localhost' | | GRANT ALL PRIVILEGES ON `test`.* TO 'user'@'localhost' | ++ 2 rows in set (0.01 sec) Fix: Submitter-Id: n/a Originator:[EMAIL PROTECTED] Organization: n/a MySQL support: none Synopsis: UPDATE fail with multiple tables (MyISAM) Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-4.0.11a-gamma (Source distribution) Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.11a-gamma, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.11a-gamma Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 4 days 1 hour 58 min 37 sec Threads: 20 Questions: 43015649 Slow queries: 712 Opens: 12223 Flush tables: 1 Open tables: 512 Queries per second avg: 121.955 C compiler:gcc (GCC) 3.2.2 C++ compiler: gcc (GCC) 3.2.2 Environment: System: Linux db.lamport.msk.ru 2.4.20-rc1aa1 #1 þÔ× îÏÑ 14 22:31:09 MSK 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.2 2.96-112.7.1) Compilation info: CC='/usr/local/gcc/bin/gcc' CFLAGS='-O4 -march=pentium3 -mcpu=pentium3 -msse -pipe' CXX='/usr/local/gcc/bin/gcc' CXXFLAGS='-O4 -march=pentium3 -mcpu=pentium3 -msse -pipe -felide-constructors -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 îÏÑ 10 19:47 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1285884 ïËÔ 10 21:19 /lib/libc-2.2.4.so -rw-r--r--1 root root 27338282 ïËÔ 10 20:48 /usr/lib/libc.a -rw-r--r--1 root root 178 ïËÔ 10 20:48 /usr/lib/libc.so lrwxrwxrwx1 root root 10 íÁÊ 26 2002 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--build=i686-pc-linux-gnu' '--prefix=/usr' '--libexecdir=/usr/sbin' '--localstatedir=/var/lib/mysql' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--enable-assembler' '--with-mysqld-ldflags=-all-static' '--with-mysql-user=mysql' '--with-innodb' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-extra-charsets=latin1,koi8_ru,cp1251' '--enable-thread-safe-client' 'CC=/usr/local/gcc/bin/gcc' 'CFLAGS=-O4 -march=pentium3 -mcpu=pentium3 -msse -pipe' 'CXXFLAGS=-O4 -march=pentium3 -mcpu=pentium3 -msse -pipe -felide-constructors -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW' 'CXX=/usr/local/gcc/bin/gcc' 'build_alias=i686-pc-linux-gnu' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.x and SSL replication thoughts.
On Fri, 2003-03-07 at 10:54, [EMAIL PROTECTED] wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I read the thread by R. Weiss, P. DuBios, and J. Zawodny regarding the incomplete implementation of replication over SSL. I had been fighting the same problem for about a week, thinking it was something I was doing wrong. Didn't think to look for un-implemented stubs... If I understand the process, the act of replication is a simple connect via a dedicated and persistent thread on the slave which which registers to listen for updates to particular dbs/tables and which is fed updates as they come in. Since the c-client and commandline SSL functions seem to be working just fine (after the inclusion of the CA-neuter-patch in 4.0.12), why is replication implementation any different? Would it not be trivial to complete this section of the code so people don't have to use stunnel and native SSL on the same box? Perhaps there's a particular issue involved, or perhaps developers just don't have the time to dedicate to the feature. I'm just wondering so I can adjust my plans accordingly. Thanks. Dan -- Dan Geist | [EMAIL PROTECTED] | (404)269-6822 Network Security Engineer | Cox Communications Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Memory usage of MySQL-Max on Rh 8.0 with InnnoDB
Hello, I am running MySQL-Max on RH8.0 on a Dell 1650, 2 proc with 512MB ram. I am only using InnoDB for databases (40gb) other than the mysql.*. I have the following relevant memory settings: set-variable= sort_buffer=2M set-variable= record_buffer=2M ## For redhat 8.0 set-variable= thread_stack=192k set-variable= myisam_sort_buffer_size=2M ... set-variable = innodb_log_buffer_size=30M set-variable = innodb_buffer_pool_size=396M set-variable = innodb_additional_mem_pool_size=2M I don't have anything other than the basic daemons running on this machine. Top reports: 71 processes: 69 sleeping, 2 running, 0 zombie, 0 stopped CPU0 states: 6.0% user, 1.0% system, 0.0% nice, 91.0% idle CPU1 states: 55.1% user, 11.1% system, 0.0% nice, 32.0% idle Mem: 513296K av, 506796K used,6500K free, 0K shrd,7468K buff Swap: 1048568K av, 241628K used, 806940K free 159928K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 2772 mysql.pr 15 0 480M 302M 53104 S63.8 60.3 1:15 mysqld-max Vmstat reports: [EMAIL PROTECTED] prod]# vmstat 1 procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 0 0 376168 6580 3176 105724 2 31025 25910 0 0 99 0 1 0 376168 7160 3212 104216 1064 0 1064 5823 846 631 30 3 67 1 0 0 376168 6592 3192 101156 1688 0 1700 5820 988 929 30 4 67 1 0 0 376168 6516 2680 102644 848 16 2924 6312 769 627 29 8 63 This doesn't seem right. Why so much swap usage? Why am I swapping? The other processes on the box don't even come close to accounting for the swap usage, let alone the swapping. Any help here is greatly appreciated. Thanks Richard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: 4.0.x and SSL replication thoughts.
sql,query,queries,smallint At 10:56 -0500 3/7/03, Dan Geist wrote: I read the thread by R. Weiss, P. DuBios, and J. Zawodny regarding the incomplete implementation of replication over SSL. I had been fighting the same problem for about a week, thinking it was something I was doing wrong. Didn't think to look for un-implemented stubs... If I understand the process, the act of replication is a simple connect via a dedicated and persistent thread on the slave which which registers to listen for updates to particular dbs/tables and which is fed updates as they come in. Since the c-client and commandline SSL functions seem to be working just fine (after the inclusion of the CA-neuter-patch in 4.0.12), why is replication implementation any different? Would it not be trivial to complete this section of the code so people don't have to use stunnel and native SSL on the same box? It's always easy when someone else is doing the work. :-) It's not necessarily so trivial. Replication connections don't use the same client library that regular client programs do. It was not an easy thing to get SSL integrated into the regular client library and working properly; I would expect that might be true for replication connections as well. There's also the time issue, and you note below. I have no authority to speak on the matter of timetable, but if you want my best guess: don't hold your breath. Continue to tunnel. Perhaps there's a particular issue involved, or perhaps developers just don't have the time to dedicate to the feature. I'm just wondering so I can adjust my plans accordingly. Thanks. Dan -- Dan Geist | [EMAIL PROTECTED] | (404)269-6822 Network Security Engineer | Cox Communications Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
CASE statement within SUM()
Hello listers, In mysql, is it possible imbed a conditional CASE statement within a SUM() function to effect how the SUM() returns the final value for a set of rows? For example, if I have a table foo with column score, and score has the rows with the values 1 to 10, can I write the SUM() in such a way to double the value of the number 7 for example to the final sum? Ie when the SUM() function encounters the number 7, add (score*2) instead of just score to the final total. Something like: SUM(CASE WHEN score = 7 THEN score*2 ELSE score END) AS final_sum (Of course this doesn't seem to work, but it's the best I can approximate) Any help, much much appreciated. Best, Peter D Bethke - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CASE statement within SUM()
Peter, I think something like this will work: mysql select sum( if(score=7, score * 2, score)) from table_name; David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.x and SSL replication thoughts.
On Fri, 2003-03-07 at 17:56, Dan Geist wrote: If I understand the process, the act of replication is a simple connect via a dedicated and persistent thread on the slave which which registers No, it is not simple. It is not very complicated also but bit unusual. Usually SSL layer stuff uses different port for SSL (remind HTTP port 443 instead of port 80). For MySQL it was requirement to stay compatible with all current stuff - port number and fallback to non-SSL connection. This is difference no 1 - MySQL client connects to server as every usual client with SSL mode off. Client sends handshake packet with special few bytes named capabilities. If client capabilities have HAVE_SSL flag, server responds with own flags including HAVE_SSL. After that without loosing connection all handshake is made again but now with SSL turned on. This is not very common way to do things. There are tricks around ACL-s. Some places must check if cipher and certificates are good enough for some operation. Replication uses separate code and all things need to be incorporated there. MySQL server will act as client and even has special MySQL client library code (which is not equal to common libmysqlclient code) in mini_client.c file. If someone will hack it, replication over SSL becomes possible. Sorry, I do not remember all details about MySQL code. I can take a look and implement missing functionality within decent time if some interested party will pay for this job. Perhaps there's a particular issue involved, or perhaps developers just don't have the time to dedicate to the feature. I'm just wondering so I can adjust my plans accordingly. I do not know current plans of MySQL AB, but you can influence on them if you buy support. Other option to support community is to provide good quality patch to MySQL AB. Tõnu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: repeated, many-character write queries hang the server
MySQL folks, I posted a full bug report yesterday, but I'm afraid that my problem might get lost in the details. It also had the wrong reply email address. Here's a summary: Under a variety of circumstances, creates and inserts of a table with many columns, 2304 to be exact, hang the server. The symptoms are that the mysqld thread gets a signal 11, and the client never completes. This behavior is seen on different servers, and from the perl and C API's and from the MySQL command line. The create query is 74 kB long and the insert query is 39 kB. The hangs seem to be history dependent. If many inserts are done rapidly, the server is more likely to hang. The tables use an auto-incremented primary key. Any help would be appreciated. Getting sort of desparate here. How-To-Repeat: The following commands: mysql test create.sql mysql test insert.sql mysql test insert.sql mysql test insert.sql . . . executed rapidly from the shell seems to reliably hang the server. Here create.sql and insert.sql can be found at http://www.jlab.org/~marki/mysql_bug/create.sql http://www.jlab.org/~marki/mysql_bug/insert.sql Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for redhat-linux-gnu on i386 System: Linux claspc14.jlab.org 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 i686 unknown Architecture: i686 -- Mark -- Mark M. Ito, Thomas Jefferson National Accelerator Facility 12000 Jefferson Ave., Mail Stop 12H, Newport News, VA 23606 Email: [EMAIL PROTECTED], Phone: (757)269-5295 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: very slow mysql server, help please
Thanks All for reply my message. After I reconfigured mysql server, I did load test. When mysql server reached 99.7%, but CUP resource is only at 50%. Since this server is mysql database only. Is there a way I can change some mysql system variables to increase CUP resource usage to 80%-90% when mysql max out. When I did load test, There only one CUP is in use, but they switch to each other. Is this normal, or I did configure wrong for my FreeBSD box? Thanks again guys. William -Original Message- From: John Wards [mailto:[EMAIL PROTECTED] Sent: Thursday, March 06, 2003 6:31 AM To: William Wang; [EMAIL PROTECTED] Subject: Re: very slow mysql server, help please On Thursday 06 Mar 2003 3:11 am, William Wang wrote: Question 1: Does sleeping processes slow down database server? Nope, I have had many a discussion whats best to use, pcconect or normall connect but have yet to conclude what is better. We feel that pconnect works best for us. Question 2: Any ideas, why is the database so slow? We have been having huge problems with mySQL for months now running on freebsd so we decided to try using linuxthreads and also upgrade to mysql 4. This has done the trick and its actually serving the pages quicker because we have turned query caching on. I would say try installing linuxthreads then recompile your current version of mySQL to have linuxthread support. If that doesn't work try upgrading to 4. HTH Cheers John Wards SportNetwork.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Configure fails
I am using the the following command: #./configure --enable-thread-safe-client --prefix=/usr/local/mysql This is what happens: checking named thread libs:... -lpthread -lthread checking for strtok_r in -lpthread... yes checking for strtok_r... yes checking for dlopen in -ldl... yes checking for unistd.h... (cached) yes checking for restartable system calls... no checking need of special linking flags... none checking for gcc option to accept ANSI C... none needed checking for an ANSI C-conforming const... yes checking for inline... inline checking for off_t... yes checking for struct stat.st_rdev... yes checking whether time.h and sys/time.h may both be included... yes checking whether struct tm is in sys/time.h or time.h... time.h checking for char... yes checking size of char... configure: error: No size for char type. A likely cause for this could be that there isn't any static libraries installed. You can verify this by checking if you have libm.a in /lib, /usr/lib or some other standard place. If this is the problem, install the static libraries and try again. If this isn't the problem, examine config.log for possible errors. If you want to report this, use 'scripts/mysqlbug' and include at least the last 20 rows from config.log! From config.log: (I have verified verified these libraries) configure:9291: result: configure:9299: error: No size for char type. A likely cause for this could be that there isn't any static libraries installed. You can verify this by checking if you have libm.a in /lib, /usr/lib or some other standard place. If this is the problem, install the static libraries and try again. If this isn't the problem, examine config.log for possible errors. If you want to report this, use 'scripts/mysqlbug' and include at least the last 20 rows from config.log! Please help if you can and thanks in advance! Judith Buff Computer System Manager/WebCT Admin Texas AM University at Galveston 200 Seawolf Parkway Galveston, Texas 77553 Ph: 409-740-4961 Fax: 409 740-4450 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CASE statement within SUM()
At 11:29 -0500 3/7/03, Peter D Bethke wrote: Hello listers, In mysql, is it possible imbed a conditional CASE statement within a SUM() function to effect how the SUM() returns the final value for a set of rows? For example, if I have a table foo with column score, and score has the rows with the values 1 to 10, can I write the SUM() in such a way to double the value of the number 7 for example to the final sum? Ie when the SUM() function encounters the number 7, add (score*2) instead of just score to the final total. Something like: SUM(CASE WHEN score = 7 THEN score*2 ELSE score END) AS final_sum (Of course this doesn't seem to work, but it's the best I can approximate) There's no reason it shouldn't work. What was the exact query you used, and what was the result? What error message, for example. Any help, much much appreciated. Best, Peter D Bethke - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump doesn't quote table names
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Smurf wrote: Description: How-To-Repeat: mysql -ecreate table `FIELDS` (x int) test mysqldump test Fix: Teach mysqldump to use backquotes. Why not mysqldump --quote-names test -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+aNkHtvXNTca6JD8RAghPAKDBHh4nqMY3rpG1TX5J14sGHIg0VgCeKTll mYvBLWv/Qy6r2hkRbyXSFd8= =dzrH -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CASE statement within SUM()
That's also right: select sum( case 1 when score=7 then score * 2 else score end) from foo; [EMAIL PROTECTED] on 03/07/2003 11:28:03 AM To: Peter D Bethke [EMAIL PROTECTED], MySQL List [EMAIL PROTECTED] cc:(bcc: David P. Giragosian/MDACC) Subject: Re: CASE statement within SUM() At 11:29 -0500 3/7/03, Peter D Bethke wrote: Hello listers, In mysql, is it possible imbed a conditional CASE statement within a SUM() function to effect how the SUM() returns the final value for a set of rows? For example, if I have a table foo with column score, and score has the rows with the values 1 to 10, can I write the SUM() in such a way to double the value of the number 7 for example to the final sum? Ie when the SUM() function encounters the number 7, add (score*2) instead of just score to the final total. Something like: SUM(CASE WHEN score = 7 THEN score*2 ELSE score END) AS final_sum (Of course this doesn't seem to work, but it's the best I can approximate) There's no reason it shouldn't work. What was the exact query you used, and what was the result? What error message, for example. Any help, much much appreciated. Best, Peter D Bethke - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BUGs on MyODBC when trying MS DTS MSSQL 2000 to MYSQL 3.23.55
How-To-Repeat: I am tried to use MS DTS to transfer a table from MSSQL 2000 to MYSQL 3.23.55, however there has an error during the transformation on one data type. The error happens when I have a text datatype on MSSQL 2000 table, and text/blob on MySQL table. The error is fail to insert or update the blob data type. The table only has one attribute MS SQL 2000- MySQL MS Table a1 MySQL Table a1 text - blob(doesn't work) MS Table b2 MySQL Table b2 text - text (dosen't work) MS Table c3 MySQL Table c3 varchar (255) - varchar (255) (works) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Connectivity to a newly created DB problem
i installed
1. MySQL 3.23---Production release(recommende),
2. Windows 95/98/NT/2000/XP 3.23.55 13.co3M,
3. Windows Source(zip) 3.23.55 13.2M
4. Control Center,
in my PC.
I created a testDB from Control Center, Added it and message shows as 'Connection
added successfully' but
its property profile shows;
Connected: No, Host name:local host, user name:root,
Why Connected is No instead of Yes in :profile? Is it really connected?
Question 1.
--
I then coded in Notepad to connect the testDB ;
HTML
HEAD
TITLECreating a testDB/TITLE
BODY
?php
//Set the variables for the database access:
$Host = localhost;
$User = root;
$Password = ;
$DBName = testDB;
$Link = mysql_connect ($host, $user, $password);
if (mysql_create_db ($DBName, $Link))
{print (sucessBR\n);}
else
{print (no successBR\n);}I got this. Question 2.
mysql_close ($Link);
?
/BODY
/HTML
-
I was unable o connect to testDB. I got 'no success'. why am I unable to connect?
Please asnwer question 1 and 2.
Thanks.
ITA Network Management
Phone: (213) 485-0125
E-mail: [EMAIL PROTECTED]
ITA Network Management
Phone: (213) 485-0125
E-mail: [EMAIL PROTECTED]
Victor Rivera [EMAIL PROTECTED] 02/27/03 03:32PM
Perfect, is it a dedicated server or do you share?
Victor Rivera
Rackspace Managed Hosting
800.961.2888 ext 1428
we have a test server and apache.
ITA Network Management
Phone: (213) 485-0125
E-mail: [EMAIL PROTECTED]
[EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
02/27/03 12:09PM
Do you need a dedicated server?
Victor Rivera
Rackspace Managed Hosting
Account Manager- Team C
800.961.2888 ext. 1428
Package : 2-tier
Budget: 200
First Name: Fran
Last Name : Kobayashi
Company :
Phone : 213.485.0125
Email : [EMAIL PROTECTED]
Comments:
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql connection refused or lost (Update)
When I try to connect to this box from another box which I have granted all rights, I get the error: ERROR 2013: Lost connection to MySQL server during query https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=77467 lost connection ... [mysqld] set-variable=thread_stack=256k Thanks! Life is good again :) Best Wishes, Charlie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1045: Access denied for user:
That was a great suggestion (getting rid of the blank user), but alas, it did not work. Here are the three examples: (first one fail because the blank user was removed) [EMAIL PROTECTED]:~$ mysql ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) [EMAIL PROTECTED]:~$ mysql -u depot -h localhost -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5711 to server version: 3.23.55 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED]:~$ mysql -u depot -h depotmon -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- Steve (egrep) On Thu, 6 Mar 2003, Paul DuBois wrote: At 11:07 +1100 3/7/03, Ben Balbo wrote: Hi Steve, Really silly question, but you are reloading the tables once you change the grants, aren't you :-) mysqladmin -p reload B. Yep, it's silly. :-) Because GRANT automatically refreshes the in-memory copies of the grant tables. Steve, try this: mysql DELETE FROM mysql.user WHERE User = ''; mysql FLUSH PRIVILEGES; (FLUSH PRIVILEGES *is* necessary here, because the DELETE directly modifies the grant tables, and you have to tell the server to refresh the in-memory copies). Then try to connect again. Bet it'll work. In response to your mail sent on Friday, March 07, 2003 at 10:48:06 AM. Mysql version 3.23.55 Slackware linux 8.0 Mysql version 3.23.55 OpenBSD I have tried 3 versions of mysql on linux and on OpenBSD. I have even tried compiling on linux from the source tarball. Without the GRANT privs to allow network access: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'192.58.197.0/255.255.255.0'; (example), I would get a you are not allowed to connect error. Once I applied the grant to the user: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'%' the users table shows: | Host| User | --- | % | user | If I coonect to the database without a host, or with host set to localhost, I can connect fine. If I connect from another machine, or just use the DNS name on the local machine, I get: shell mysql -h 192.168.1.24 -u test -p ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have even tried connecting using the IP address. I have poured over the docs, and have tried all the scenarios of add hostnames to /etc/hosts etc. I even complied from source (glibc problem). No luck at all... Any ideas? I really need network access to the database. -- Steve (egrep) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: another Table another query coming soon
Actually thinking about this in a bit more depth! I also have the infamous items table where individual company records are being stored. So is the best way to do this: i) add another field that will coreespond to the items.ItemsID or ii) create another table town_ref CREATE TABLE town_ref ( town_id int(11) DEFAULT '' NOT NULL auto_increment, town varchar(255), PRIMARY KEY (town_id) ); and then another table: CREATE TABLE item_town ( id int(11) DEFAULT '' NOT NULL auto_increment, item_id int(11), town_id int(11), PRIMARY KEY (id) ); which will then involve adding another column to the items table :( Andrew -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: 07 March 2003 14:51 To: MySQL-Lista Cc: [EMAIL PROTECTED] Subject: Re: another Table another query coming soon * Andrew I am going to add another table to my lovely MySQL DB. Basically it is the city table I need to add to. For each city I am going to have Towns. So I need to relate many Towns to one CityID The city table at the moment is: City CityID What the best way forward? Create a Towns table, and link it to the Cities table? CREATE TABLE Towns ( TownID int unsigned not null auto_increment primary key, CityID int unsigned not null, Town varchar(40) not null, index (CityID), index (Town(8))); Show all towns for a city: SELECT Town FROM Towns NATURAL JOIN Cities WHERE Cities.City = London; or SELECT T.Town FROM Towns T LEFT JOIN Cities C ON C.CityID = T.CityID WHERE C.City = London; HTH, -- Roger --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.459 / Virus Database: 258 - Release Date: 25/02/2003 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Configure fails
On Fri, Mar 07, 2003 at 11:05:37AM -0600, Judi Buff wrote: I am using the the following command: #./configure --enable-thread-safe-client --prefix=/usr/local/mysql You realize that you've given us no details of your platform. OS, version, which version of MySQL, etc. Please help if you can and thanks in advance! Judith Buff Computer System Manager/WebCT Admin Texas AM University at Galveston 200 Seawolf Parkway Galveston, Texas 77553 Ph: 409-740-4961 Fax: 409 740-4450 -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MATCH / AGAINST bug/problem
MATCH / AGAINST problem/bug
MySQL version: 4.0.11-gamma
OS : Windows 2000 SP3
Under some circumstances this query does never terminate. The win32
service cannot be stopped either.
SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('550') AS score FROM file as f, filesys as fs WHERE f.id = fs.fileid
HAVING score 0 ORDER by score DESC
mysqladmin -proc Output
===
| 25850 | kb | localhost | kb | Query | 17497 | Copying to tmp table
| SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('2147220991') AS score FROM file |
OR
| 37522 | kb | localhost | kb | Query | 1323 | Copying to tmp table
| SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('550') AS score FROM file as f, |
Table Structure
===
#
# Table structure for table `file`
#
CREATE TABLE file (
id int(11) NOT NULL auto_increment,
title text,
version varchar(255) default NULL,
created int(11) NOT NULL default '0',
modified int(11) default NULL,
body text,
hits int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY modified (modified),
KEY hits (hits),
KEY created (created),
FULLTEXT KEY titlebody (title,body),
FULLTEXT KEY body (body)
) TYPE=MyISAM;
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Configure Fails
On Fri, Mar 07, 2003 at 01:23:27PM -0600, Judi Buff wrote: Brian, I'm sorry, I am running Solaris8 on Sparc, and trying to install MySQL 3.23.53. I get into this stuff so deep I have tunnel vision and think the whole world should know what I am talking about. On Fri, Mar 07, 2003 at 11:05:37AM -0600, Judi Buff wrote: I am using the the following command: #./configure --enable-thread-safe-client --prefix=/usr/local/mysql You realize that you've given us no details of your platform. OS, version, which version of MySQL, etc. Please help if you can and thanks in advance! Judith Buff Computer System Manager/WebCT Admin Texas AM University at Galveston 200 Seawolf Parkway Galveston, Texas 77553 Ph: 409-740-4961 Fax: 409 740-4450 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1045: Access denied for user:
I just installed the standard-4.0.11 binary and the same problem exists. Error 1130: this host is not allowed to connect. Run the grant and flush privs. Access denied... -- Steve (egrep) On Fri, 7 Mar 2003, Steve Pirk wrote: That was a great suggestion (getting rid of the blank user), but alas, it did not work. Here are the three examples: (first one fail because the blank user was removed) [EMAIL PROTECTED]:~$ mysql ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) [EMAIL PROTECTED]:~$ mysql -u depot -h localhost -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5711 to server version: 3.23.55 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED]:~$ mysql -u depot -h depotmon -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- Steve (egrep) On Thu, 6 Mar 2003, Paul DuBois wrote: At 11:07 +1100 3/7/03, Ben Balbo wrote: Hi Steve, Really silly question, but you are reloading the tables once you change the grants, aren't you :-) mysqladmin -p reload B. Yep, it's silly. :-) Because GRANT automatically refreshes the in-memory copies of the grant tables. Steve, try this: mysql DELETE FROM mysql.user WHERE User = ''; mysql FLUSH PRIVILEGES; (FLUSH PRIVILEGES *is* necessary here, because the DELETE directly modifies the grant tables, and you have to tell the server to refresh the in-memory copies). Then try to connect again. Bet it'll work. In response to your mail sent on Friday, March 07, 2003 at 10:48:06 AM. Mysql version 3.23.55 Slackware linux 8.0 Mysql version 3.23.55 OpenBSD I have tried 3 versions of mysql on linux and on OpenBSD. I have even tried compiling on linux from the source tarball. Without the GRANT privs to allow network access: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'192.58.197.0/255.255.255.0'; (example), I would get a you are not allowed to connect error. Once I applied the grant to the user: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'%' the users table shows: | Host| User | --- | % | user | If I coonect to the database without a host, or with host set to localhost, I can connect fine. If I connect from another machine, or just use the DNS name on the local machine, I get: shell mysql -h 192.168.1.24 -u test -p ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have even tried connecting using the IP address. I have poured over the docs, and have tried all the scenarios of add hostnames to /etc/hosts etc. I even complied from source (glibc problem). No luck at all... Any ideas? I really need network access to the database. -- Steve (egrep) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HAVING behaviour
Your whole query is malformed. You have an aggregate function in the having. try: select f1,max(f2) from tt where f1=1 group by f1; Gabriel Tataranu wrote: Hi, this is NOT correct behavior. Where clause returns (1,1) and (1,2). The HAVING clause should be applied to what WHERE has selected and choose the (1,2) record. A quote from the manual It (HAVING) is applied last, just before items are sent to the client, with no optimisation. This is obviously not the case here. It looks like HAVING and WHERE are both appliend to the source record set. Regards, Gabriel This is correct. WHERE clause returns (1,1) HAVING eliminates all records not HAVING f2=3. [EMAIL PROTECTED] wrote: Description: HAVING in SELECT is evaluated before WHERE. This is not the correct behaviour as HAVING should deal with the remainder of the WHERE selection (according to manual) How-To-Repeat: mysql: create table tt (f1 int, f2 int); Query OK, 0 rows affected (0.01 sec) mysql: insert into tt values(1,1); Query OK, 1 row affected (0.01 sec) mysql: insert into tt values(1,2); Query OK, 1 row affected (0.00 sec) mysql: insert into tt values(2,3); Query OK, 1 row affected (0.00 sec) mysql: select * from tt where f1=1 having f2=max(f2); Empty set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1045: Access denied for user:
You grant IP address does not match the IP address of the client failing the connection. Steve Pirk wrote: That was a great suggestion (getting rid of the blank user), but alas, it did not work. Here are the three examples: (first one fail because the blank user was removed) [EMAIL PROTECTED]:~$ mysql ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) [EMAIL PROTECTED]:~$ mysql -u depot -h localhost -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5711 to server version: 3.23.55 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED]:~$ mysql -u depot -h depotmon -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- Steve (egrep) On Thu, 6 Mar 2003, Paul DuBois wrote: At 11:07 +1100 3/7/03, Ben Balbo wrote: Hi Steve, Really silly question, but you are reloading the tables once you change the grants, aren't you :-) mysqladmin -p reload B. Yep, it's silly. :-) Because GRANT automatically refreshes the in-memory copies of the grant tables. Steve, try this: mysql DELETE FROM mysql.user WHERE User = ''; mysql FLUSH PRIVILEGES; (FLUSH PRIVILEGES *is* necessary here, because the DELETE directly modifies the grant tables, and you have to tell the server to refresh the in-memory copies). Then try to connect again. Bet it'll work. In response to your mail sent on Friday, March 07, 2003 at 10:48:06 AM. Mysql version 3.23.55 Slackware linux 8.0 Mysql version 3.23.55 OpenBSD I have tried 3 versions of mysql on linux and on OpenBSD. I have even tried compiling on linux from the source tarball. Without the GRANT privs to allow network access: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'192.58.197.0/255.255.255.0'; (example), I would get a you are not allowed to connect error. Once I applied the grant to the user: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'%' the users table shows: | Host| User | --- | % | user | If I coonect to the database without a host, or with host set to localhost, I can connect fine. If I connect from another machine, or just use the DNS name on the local machine, I get: shell mysql -h 192.168.1.24 -u test -p ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have even tried connecting using the IP address. I have poured over the docs, and have tried all the scenarios of add hostnames to /etc/hosts etc. I even complied from source (glibc problem). No luck at all... Any ideas? I really need network access to the database. -- Steve (egrep) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MATCH / AGAINST bug/problem
Hi ..
Free disk space: 32GB
Thats not the problem I guess.
-Mat
-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED]
Sent: Freitag, 7. März 2003 22:03
To: Mathias Berchtold
Cc: [EMAIL PROTECTED]
Subject: Re: MATCH / AGAINST bug/problem
I you run low on temp filesystem space, mysqld will wait until disk
space becomes available.
Are you getting low when it appears to hang?
Mathias Berchtold wrote:
MATCH / AGAINST problem/bug
MySQL version: 4.0.11-gamma
OS : Windows 2000 SP3
Under some circumstances this query does never terminate. The win32
service cannot be stopped either.
SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('550') AS score FROM file as f, filesys as fs WHERE f.id = fs.fileid
HAVING score 0 ORDER by score DESC
mysqladmin -proc Output
===
| 25850 | kb | localhost | kb | Query | 17497 | Copying to tmp table
| SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('2147220991') AS score FROM file |
OR
| 37522 | kb | localhost | kb | Query | 1323 | Copying to tmp table
| SELECT f.id as id, fs.name as name, MATCH (f.title,f.body) AGAINST
('550') AS score FROM file as f, |
Table Structure
===
#
# Table structure for table `file`
#
CREATE TABLE file (
id int(11) NOT NULL auto_increment,
title text,
version varchar(255) default NULL,
created int(11) NOT NULL default '0',
modified int(11) default NULL,
body text,
hits int(11) NOT NULL default '0',
PRIMARY KEY (id),
KEY modified (modified),
KEY hits (hits),
KEY created (created),
FULLTEXT KEY titlebody (title,body),
FULLTEXT KEY body (body)
) TYPE=MyISAM;
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem setting up DES_ENCRYPT on MySQL 3.23.46
thank you very much !!! I will try 4.0 then tommy From: Tonu Samuel [EMAIL PROTECTED] To: tommy liu [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: problem setting up DES_ENCRYPT on MySQL 3.23.46 Date: 07 Mar 2003 16:04:19 +0200 On Thu, 2003-03-06 at 17:52, tommy liu wrote: anyways, yes I did run # ./configure --with-vio --with-openssl --prefix=/usr/local/mysql and also tried the max version I also ran mysql_fix_privilege_tables.sh (one along 3.23.46 source)because we have MySQL 3.23.46 but I don't think it did anything because the tables did not change before and after running the script. am I missing something ? Hard to say. I do not remember all details from this development. I suspect, something was missing in 3.23.x series at all. Can you try latest 4.0.x-Max ? It is highly possible that 3.23 does not contain working SSL code and it was switched off from somewhere. I am running 4.0.10 on pretty high volume server with complicated configuration and had no problems. mysql show status; +--++ | Variable_name| Value | +--++ ... | Questions| 225583189 | ... | Uptime | 2289866| +--++ I believe 4.0 will work for you. Tõnu _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HAVING behaviour
Hi, Your whole query is malformed. You have an aggregate function in the having. It's absolutely legal query. In fact I don't get any error messages that say otherwise. I'm not SQL guru but I haven't heard that agregate functions have to be excuded from HAVING clause. To quote a SQL web tutorial HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING... it would be impossible to test for result conditions. try: select f1,max(f2) from tt where f1=1 group by f1; I thank you for your help but this query will not produce the same result as the original query (I know, on this particular data set it will , but I can show you a slighly more complex data set that shows that). BTW, I've already recoded the aplication to bypass this problem. But all that is beside the point: HAVING clause seems to be evaluated on original data set and not at the end of query execution as it should. Flawed as it may be, the query on the original post demonstrated a bug of mysql in handling HAVING clause. Regards, Gabriel How-To-Repeat: mysql: create table tt (f1 int, f2 int); Query OK, 0 rows affected (0.01 sec) mysql: insert into tt values(1,1); Query OK, 1 row affected (0.01 sec) mysql: insert into tt values(1,2); Query OK, 1 row affected (0.00 sec) mysql: insert into tt values(2,3); Query OK, 1 row affected (0.00 sec) mysql: select * from tt where f1=1 having f2=max(f2); Empty set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
cannot open master log from slave
030308 8:09:29 Error reading packet from server: Binary log is not open (server_ errno=1236) 030308 8:09:29 Got fatal error 1236: 'Binary log is not open' from master when r eading data from binary log 030308 8:09:29 Slave I/O thread exiting, read up to log 'FIRST', position 4 030308 8:11:18 Slave I/O thread: connected to master '[EMAIL PROTECTED]:330 6', replication started in log 'FIRST' at position 4 030308 8:11:18 Error reading packet from server: Binary log is not open (server_ errno=1236) 030308 8:11:18 Got fatal error 1236: 'Binary log is not open' from master when r eading data from binary log can anyone help me here i'm stumped ?? sql :| - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1045: Access denied for user:
Both ip's are in the user table along with the % wildcard. -- Steve (egrep) On Fri, 7 Mar 2003, gerald_clark wrote: You grant IP address does not match the IP address of the client failing the connection. Steve Pirk wrote: That was a great suggestion (getting rid of the blank user), but alas, it did not work. Here are the three examples: (first one fail because the blank user was removed) [EMAIL PROTECTED]:~$ mysql ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) [EMAIL PROTECTED]:~$ mysql -u depot -h localhost -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5711 to server version: 3.23.55 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED]:~$ mysql -u depot -h depotmon -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -- Steve (egrep) On Thu, 6 Mar 2003, Paul DuBois wrote: At 11:07 +1100 3/7/03, Ben Balbo wrote: Hi Steve, Really silly question, but you are reloading the tables once you change the grants, aren't you :-) mysqladmin -p reload B. Yep, it's silly. :-) Because GRANT automatically refreshes the in-memory copies of the grant tables. Steve, try this: mysql DELETE FROM mysql.user WHERE User = ''; mysql FLUSH PRIVILEGES; (FLUSH PRIVILEGES *is* necessary here, because the DELETE directly modifies the grant tables, and you have to tell the server to refresh the in-memory copies). Then try to connect again. Bet it'll work. In response to your mail sent on Friday, March 07, 2003 at 10:48:06 AM. Mysql version 3.23.55 Slackware linux 8.0 Mysql version 3.23.55 OpenBSD I have tried 3 versions of mysql on linux and on OpenBSD. I have even tried compiling on linux from the source tarball. Without the GRANT privs to allow network access: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'192.58.197.0/255.255.255.0'; (example), I would get a you are not allowed to connect error. Once I applied the grant to the user: mysql GRANT ALL PRIVILEGES ON db.* - TO user@'%' the users table shows: | Host| User | --- | % | user | If I coonect to the database without a host, or with host set to localhost, I can connect fine. If I connect from another machine, or just use the DNS name on the local machine, I get: shell mysql -h 192.168.1.24 -u test -p ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have even tried connecting using the IP address. I have poured over the docs, and have tried all the scenarios of add hostnames to /etc/hosts etc. I even complied from source (glibc problem). No luck at all... Any ideas? I really need network access to the database. -- Steve (egrep) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need Help Setting Relationships
Howdy Egor, Friday, March 7, 2003, 7:36:00 AM, Egor Egorov wrote: Both columns Node are indexed, so just add foreign key constraint. You can find examples in the InnoDB manual: http://www.innodb.com/ibman.html#InnoDB_foreign_keys I've looked at the examples, but one thing it doesn't mention is whether both indexes need to be unique. In the primary table they are, but does the index in the table with the foreign key need to be a unique index also? Seems that no matter what I try I can't set the foreign keys. I've tried doing this at the cmd line and using GUI's (DBManager, SQLYog, etc.). TIA. -- Chris Montgomery - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
clustering/scalability question
Hello, We're a web hosting company currently hosting nearly 2000 MySQL databases (3.23.54) at around 200-300 queries/second. Amazing performance, really. But we're growing fast and planning for the next step. Our wishlist is short: We want to allow all our customers to have one set of instructions that says connect to db.modwest.com (as opposed to 'read from here, write to there', or 'connect to db2 if your domain name starts with M-Z'). Of course on the invisible backend, multiple servers would be servicing these requests. I'd appreciate any bright ideas or pertinent links. Thanks! -- John Masterson Modwest, Inc. Powerful, Affordable Web Hosting http://www.modwest.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: clustering/scalability question
On Fri, Mar 07, 2003 at 04:56:54PM -0700, John Masterson wrote: Hello, We're a web hosting company currently hosting nearly 2000 MySQL databases (3.23.54) at around 200-300 queries/second. Amazing performance, really. But we're growing fast and planning for the next step. Our wishlist is short: We want to allow all our customers to have one set of instructions that says connect to db.modwest.com (as opposed to 'read from here, write to there', or 'connect to db2 if your domain name starts with M-Z'). Of course on the invisible backend, multiple servers would be servicing these requests. Tell them to connect to db.$their_domain.com and setup CNAMEs in DNS that point to their real server. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 32 days, processed 1,005,162,161 queries (357/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
interesting topics including stuff on text boolean search using match
http://www.databasejournal.com/features/mysql/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple foreign keys?
Can I have multiple foreign keys in a table? Like this... I ask because I can't seem to get it to work. Errno: 150. CREATE TABLE `dept_table` ( `dept_id` mediumint(8) unsigned NOT NULL auto_increment, `dept_timestamp` timestamp(14) NOT NULL, `dept_company_table_id` mediumint(8) unsigned NOT NULL default '0' REFERENCES company_table(company_id), `dept_rep_table_id` smallint(8) unsigned default NULL REFERENCES rep_table(rep_id), `dept_name` varchar(255) NOT NULL default '', `dept_SCM_10` date NOT NULL default '-00-00', `dept_SCM_20` date NOT NULL default '-00-00', `dept_SCM_40` date NOT NULL default '-00-00', `dept_SCM_60` date NOT NULL default '-00-00', `dept_SCM_80` date NOT NULL default '-00-00', `dept_SCM_90` date NOT NULL default '-00-00', `dept_SCM_100` date NOT NULL default '-00-00', `dept_notes` text NOT NULL, PRIMARY KEY (`dept_id`), KEY `dept_company_table_id` (`dept_company_table_id`), KEY `dept_rep_table_id` (`dept_rep_table_id`), FOREIGN KEY (dept_company_table_id) REFERENCES company_table(company_id) ON DELETE CASCADE, FOREIGN KEY (dept_rep_table_id) REFERENCES rep_table(rep_id) ON DELETE SET NULL ) TYPE=InnoDB; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Need client side suggestions
I'm investigating the feasibility of converting an MS Access database to a MySQL based system and could use some advice. The server side will probably be run on a Linux box and is not a problem since I can deal with that easily enough. The clients will all be win98 based (possibility of upgrades to winxp in the near future - bleh). I need a good system for data entry and reports, opensource is preferred. Basically, I'm looking for something that will duplicate the functionality of the MS Access macros, menus, forms and reports design tools. Features desired: 1. GUI design tool to create data entry forms and reports for printing. 2. Macros (or something similar) to tie together queries, forms and/or reports. 3. Some scripting ability to massage data, if possible. 4. Ability to lock form and report styles so they can't be modified by users. 5. Ability to limit who can access certain forms and reports. 6. Some data validation capability before sending to mysql would be desirable (e.g. field can't be blank or must be a number). 7. Either opensource or very inexpensive (and definitely no recurring license fees!). My budget is almost non-existent at the moment. I've searched all over for some kind of package that could do this, but the opensource items didn't seem to be very complete or mature, and the commercial packages were outrageously expensive. If nothing exists that fullfills my needs, I may go with a web based interface for data entry (using perl or php), but would still need something to handle printed reports. BTW, consider my wishlist as a good starting point for a new opensource project if anyone needs something to sink their teeth into. Rossz P.S. If this is the wrong list to ask this question, please direct me to the correct one. Thank you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need Help Setting Relationships
Disregard my previous msg. It looks like I have it figured out. Friday, March 7, 2003, 5:31:38 PM, Chris Montgomery wrote: I've looked at the examples, but one thing it doesn't mention is whether both indexes need to be unique. In the primary table they are, but does the index in the table with the foreign key need to be a unique index also? Seems that no matter what I try I can't set the foreign keys. I've tried doing this at the cmd line and using GUI's (DBManager, SQLYog, etc.). Cheers. -- Chris Montgomery - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
'mysqldump' doesn't preserve REFERENCES
If I create a table that uses REFERENCES in it, then mysqldump it, the
REFERENCES isn't there
Notice the `contact_dept_table_id` field in both of the below outputs:
CREATE TABLE `contact_table` (
`contact_id` mediumint(8) unsigned NOT NULL auto_increment,
`contact_timestamp` timestamp(14) NOT NULL,
`contact_dept_table_id` mediumint(8) unsigned NOT NULL default '0'
REFERENCES dept_table(dept_id),
`contact_fname` varchar(255) NOT NULL default '',
`contact_lname` varchar(255) NOT NULL default '',
`contact_title` varchar(50) NOT NULL default '',
`contact_email` varchar(255) NOT NULL default '',
`contact_incept` date NOT NULL default '-00-00',
`contact_phone` varchar(20) NOT NULL default '',
`contact_address1` varchar(70) NOT NULL default '',
`contact_address2` varchar(70) NOT NULL default '',
`contact_city` varchar(50) NOT NULL default '',
`contact_state` varchar(50) NOT NULL default '',
`contact_zip` varchar(50) NOT NULL default '',
`contact_country` varchar(50) NOT NULL default '',
`contact_notes` longtext NOT NULL,
PRIMARY KEY (`contact_id`),
KEY `contact_dept_table_id` (`contact_dept_table_id`),
KEY `contact_lname` (`contact_lname`),
KEY `contact_fname` (`contact_fname`),
FOREIGN KEY (contact_dept_table_id) REFERENCES dept_table(dept_id) ON
DELETE CASCADE
) TYPE=InnoDB;
Then execute:
[daevid=pts/1]5:01pm:{/home/daevid} mysqldump -a crm
-- MySQL dump 8.22
--
-- Host: localhostDatabase: crm
-
-- Server version 3.23.55-Max
--
-- Table structure for table 'contact_table'
--
CREATE TABLE contact_table (
contact_id mediumint(8) unsigned NOT NULL auto_increment,
contact_timestamp timestamp(14) NOT NULL,
contact_dept_table_id mediumint(8) unsigned NOT NULL default '0', /*
shouldn't there be a REFERENCES here? */
contact_fname varchar(255) NOT NULL default '',
contact_lname varchar(255) NOT NULL default '',
contact_title varchar(50) NOT NULL default '',
contact_email varchar(255) NOT NULL default '',
contact_incept date NOT NULL default '-00-00',
contact_phone varchar(20) NOT NULL default '',
contact_address1 varchar(70) NOT NULL default '',
contact_address2 varchar(70) NOT NULL default '',
contact_city varchar(50) NOT NULL default '',
contact_state varchar(50) NOT NULL default '',
contact_zip varchar(50) NOT NULL default '',
contact_country varchar(50) NOT NULL default '',
contact_notes longtext NOT NULL,
PRIMARY KEY (contact_id),
KEY contact_dept_table_id (contact_dept_table_id),
KEY contact_lname (contact_lname),
KEY contact_fname (contact_fname),
FOREIGN KEY (`contact_dept_table_id`) REFERENCES `crm.dept_table`
(`dept_id`) ON DELETE CASCADE
) TYPE=InnoDB;
--
-- Dumping data for table 'contact_table'
--
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Install issue
Description: Message indicates to use /usr/bin/mysqladmin to set root password at end of installation, but this is not included in the distribution How-To-Repeat: RedHat 8.0 - rpm --install -p MySQL-3.23.55-1.i386.rpm Fix: Submitter-Id: Paul Higgs Originator:root Organization: Ericsson Inc MySQL support: none Synopsis: Incorrect instruction at install time Severity: serious Priority: medium Category: mysql Class: doc-bug Release: mysql-3.23.55 (Official MySQL RPM) Environment: Intel, RedHat 8.0 System: Linux bogz.pl.us.am.ericsson.se 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Mar 7 06:04 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x2 root root 1235468 Sep 5 2002 /lib/libc-2.2.93.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--without-innodb' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using ssh tunnel and mysql
Hi. I wanted to connect from a client machine to a MySQL server using ssh. I execute the following on the local machine (the server is foo.bar.com): % ssh -n -N -L 3307:foo.bar.com:3306 foo.bar.com I then try to connect from the local machine with: % mysql -h localhost -p 3307 --user=me --password But after prompting me for my password, MySQL denies me access. I've successfully used this technique with Perforce to have encrypted Perforce communication, and I can ssh into the remote machine and use mysql without problems. Anyone have any ideas why this isn't working for me? Thanks! -- Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using ssh tunnel and mysql
On Fri, Mar 07, 2003 at 05:37:38PM -0800, LZ Orders wrote: Hi. I wanted to connect from a client machine to a MySQL server using ssh. I execute the following on the local machine (the server is foo.bar.com): % ssh -n -N -L 3307:foo.bar.com:3306 foo.bar.com I then try to connect from the local machine with: % mysql -h localhost -p 3307 --user=me --password But after prompting me for my password, MySQL denies me access. What if you use 127.0.0.1 instead of localhost? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 32 days, processed 1,006,604,456 queries (357/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL compiling problem - (a classic)
Hello, This is my first post to [EMAIL PROTECTED] I'm about to compile MySQL (3.23.55) för Red Hat 7.2 that I begin with; ./configure --prefix=/usr/local/mysql I get this error message after a while of machine-chewing; checking for tgetent in -lncurses... no checking for tgetent in -lcurses... no checking for tgetent in -ltermcap... no checking for termcap functions library... configure: error: No curses/termcap library found I know that termcap, ncurses and is on the system (I downloaded the rpm and tried to install/upgrade it, with the answer it is already on the system), and after doing this I wonder what can be wrong... Can anyone please help me?! Have a nice weekend! / H - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL compiling problem - (a classic)
At 03:35 2003-03-08, Henrik Leghissa wrote: I'm about to compile MySQL (3.23.55) för Red Hat 7.2 that I begin with; ./configure --prefix=/usr/local/mysql I get this error message after a while of machine-chewing; checking for tgetent in -lncurses... no checking for tgetent in -lcurses... no checking for tgetent in -ltermcap... no checking for termcap functions library... configure: error: No curses/termcap library found I know that termcap, ncurses and is on the system (I downloaded the rpm and tried to install/upgrade it, with the answer it is already on the system), and after doing this I wonder what can be wrong... To add to my own post; I tried to re-install termcap, ncurses and libtermcap by using rpm -ivh --force packet.rpm and tried to do the ./configure again - still no luck! :-( / H - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Issue Solved (Re: MySQL compiling problem - (a classic))
At 03:51 2003-03-08, Henrik Leghissa wrote: I'm about to compile MySQL (3.23.55) för Red Hat 7.2 that I begin with; ./configure --prefix=/usr/local/mysql I get this error message after a while of machine-chewing; checking for tgetent in -lncurses... no checking for tgetent in -lcurses... no checking for tgetent in -ltermcap... no checking for termcap functions library... configure: error: No curses/termcap library found I know that termcap, ncurses and is on the system (I downloaded the rpm and tried to install/upgrade it, with the answer it is already on the system), and after doing this I wonder what can be wrong... To add to my own post; I tried to re-install termcap, ncurses and libtermcap by using rpm -ivh --force packet.rpm and tried to do the ./configure again - still no luck! :-( I searched a bit online, thought a bit too (it's good yes) and downloaded libtermcap-devel*, installed it on the system and VOILÁ! ;-) Sorry for taking up you guys time!!! Cheers! / H - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
But now new problems :-(
I managed to get passed the ./configure-phase, but now I encountered more trouble; To compile mysql I write make... and get these error-messages after a while; SNIP gcc -DDEFAULT_CHARSET_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DUNDEF_THREADS_HACK -DDONT_USE_RAID -I. -I. -I.. -I./../include -I../include -I./.. -I.. -I.. -O3 -DDBUG_OFF -c libmysql.c -MT libmysql.lo -MD -MP -MF .deps/libmysql.TPlo -fPIC -DPIC -o .libs/libmysql.lo libmysql.c: In function `mysql_real_connect': libmysql.c:1348: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type libmysql.c:1348: too few arguments to function `gethostbyname_r' libmysql.c:1348: warning: assignment makes pointer from integer without a cast make[2]: *** [libmysql.lo] Error 1 make[2]: Leaving directory `/home/henrik/linux/MySQL/mysql-3.23.55/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/home/henrik/linux/MySQL/mysql-3.23.55' make: *** [all] Error 2 ...and the compilation stops Anyone with knowledge how to remedy this problem? Thank you in advance. / H - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using ssh tunnel and mysql
What does it really matter is whether you have create mySQL user that allow access the database from the SSH server. Please note that localhost, 127.0.0.1 or the machine ip address will be treated as different entries by mySQL. -- Yours, KH Chiu CA Computer Consultants Ltd. Tel: 3104 2070 Fax: 3010 0896 Email: [EMAIL PROTECTED] Website: www.caconsultant.com On Fri, Mar 07, 2003 at 05:37:38PM -0800, LZ Orders wrote: Hi. I wanted to connect from a client machine to a MySQL server using ssh. I execute the following on the local machine (the server is foo.bar.com): % ssh -n -N -L 3307:foo.bar.com:3306 foo.bar.com I then try to connect from the local machine with: % mysql -h localhost -p 3307 --user=me --password But after prompting me for my password, MySQL denies me access. What if you use 127.0.0.1 instead of localhost? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 32 days, processed 1,006,604,456 queries (357/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
update a value in a field.
Hello everyone, I'm trying to write a query that would update a field in a table. Here is the catch. Table 1 has two fields - field A and Field B Table 2 has two fields - field A and Field B Each table has many more fields but these are the only two I'm concerned with right now. Field B in table 2 is empty. Field B in table 1 is filled. Field A is filled in both tables. What I would like to do is set field B in table 2 to the value of field B in table 1 where field A in table 2 is equal to field A in table 1. Any help would be appreciated. Chris Burger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: update a value in a field.
At 17:10 -0500 3/7/03, Christopher M Burger wrote: Hello everyone, I'm trying to write a query that would update a field in a table. Here is the catch. Table 1 has two fields - field A and Field B Table 2 has two fields - field A and Field B Each table has many more fields but these are the only two I'm concerned with right now. Field B in table 2 is empty. Field B in table 1 is filled. Field A is filled in both tables. What I would like to do is set field B in table 2 to the value of field B in table 1 where field A in table 2 is equal to field A in table 1. Any help would be appreciated. Chris Burger UPDATE t1, t2 SET t2.B = t1.B WHERE t2.A = t1.A; Requires MySQL 4.0.2. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: HAVING behaviour
Hi, I had some private e-mail suggesting that MAX functions should apply to the full table - as it is the maximum value of the field- and this is why HAVING clause behaved in that manner. I'm sure this is not the case since MAX does obey (as it should) WHERE clause in the query. To prove my point I've decided to use another data set as example: create table tt (f1 int, f2 int); insert into tt values(1,1); insert into tt values(1,2); insert into tt values(2,3); insert into tt values(1,4); select f2 from tt where f1=1 having f2=max(f2); Empty set (0.00 sec) You draw the conclusions. Regards, Gabriel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
charsets
I have mysql 3.23.55 for Windows where charset win1250ch is included. Now I download the same version for Linux (rpm) but this charset is not included here. Why? Thank you for your quick reply. Petr Vileta, Czech Republic http://www.practisoft.cz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
