Re[2]: Bug or feature fulltext search?
Hello Sergei, Tuesday, July 23, 2002, 10:17:06 PM, you wrote: SG Hi! SG On Jul 23, Artem V. Ryabov wrote: Hello mysql, see this 2 queries: why they not equal? why first skip many records? mysql select left(ftText,20) from Txt_index where match(ftText) against('+(wùd200206*) +wù12p' in boolean mode); 3 rows in set (0.36 sec) mysql select left(ftText,20) from Txt_index where match(ftText) against('+wùd200206* +wù12p' in boolean mode); 42 rows in set (8.00 sec) mysql show variables like 'version'; +---+-+ | Variable_name | Value | +---+-+ | version | 4.0.2-alpha-log | +---+-+ 1 row in set (0.00 sec) SG Obviously, it's a bug. SG Could you provide a test case ? I tried to use 'REPAIR TABLE Txt_index USE_FRM', this query work _very_ slow: +--++--++ | Table| Op | Msg_type | Msg_text | +--++--++ | NewWeb.Txt_index | repair | warning | Number of rows changed from 0 to 43390 | | NewWeb.Txt_index | repair | status | OK | +--++--++ 2 rows in set (2 days 11 hours 21 min 7.35 se2 rows in set (2 But noting change: mysql select left(ftText,17) from Txt_index where match(ftText) against('+(wùd200206*) +wù12p' in boolean mode); 3 rows in set (0.35 sec) mysql select left(ftText,17) from Txt_index where match(ftText) against('+wùd200206* +wù12p' in boolean mode); 42 rows in set (4.52 sec) mysql SG Regards, SG Sergei -- Best regards, Artemmailto:[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
problem on open a table
HI, I do not manage to open a table it puts me Can' t open takes off: ' pkf_as_events_log. MYD '. (Errno: 145) is what anybody has already had this error on mysql? Cordially Florian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Installing MySQL 3.23.51 on AIX 4.3.3
Hi Scott, you have to set the PATH variable. PATH=/usr/local/bin:$PATH in your profile. Then the mysql_install_db runs. -Ursprüngliche Nachricht- Von: Scott Pippin [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 31. Juli 2002 21:17 An: [EMAIL PROTECTED] Betreff: Installing MySQL 3.23.51 on AIX 4.3.3 I get the following error when installing the MySql Binary in AIX: exec(): 0509-036 Cannot load program mysql because of the following errors: 0509-130 Symbol resolution failed for mysql because: 0509-136 Symbol stpcpy (number 125) is not exported from dependent module /usr/lib/libc.a(shr.o). 0509-136 Symbol strnlen (number 126) is not exported from dependent module /usr/lib/libc.a(shr.o). 0509-192 Examine .loader section symbols with the 'dump -Tv' command. I get this error when running mysql_install_db and when trying to start the database. Note: I am using AIX 4.3.3 and MySQL 3.23.51. - 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: Answer: how to get 64 indexes
On Mon, Jul 29, 2002 at 12:15:50PM -0700, [EMAIL PROTECTED] wrote: After many headaches, rantings, and emails, i have FINALLY figured out how to get 64 indexes out of MySQL tables. [snip] I think its a sad situation that i could find numerous references to using 64 keys, none more specific than: you need to change some things in the source code or you need to change some things in myisam.h and unireg.h, and some other things Then do others a favor and submit a documentation patch! Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 63 days, processed 1,324,311,510 queries (241/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: assertion failed invalid signature in file details.php
- Original Message - From: Bart Burkhardt [EMAIL PROTECTED] i get this error in mysql 3.23.51 with snortreport (http://www.circuitsmaximus.com/download.html) is this an mysql error or snortreport error ? problem solved, i had to enable global environment=on in php.ini so, no mysql bug.. mysql query bart - 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
Large File support on linux 2.2
Mysql, SQL, etc. Hi, Does anyone have experience of compiling in large file support for Linux ? I just want to know what is the scope of the change: do I have to recompile the kernel and every single library and application ? thanks, Martin - 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
synopsis of the problem (one line)
Description: How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:Thomas Raabe Organization: Thomas Raabe E-Mail: [EMAIL PROTECTED] UNI-Kassel - HRZ-ABT II Tel: 0561-804-2285 Moenchebergstr. 11 - 34125 Kassel Fax: 0561-804-2297 MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-3.22.32 (Source distribution) Environment: System: Linux hrz-pc73 2.2.16 #1 Wed Aug 2 20:22:26 GMT 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' Configure command: ./configure --enable-shared --enable-assembler --prefix=/usr --libdir=/usr/lib --libexecdir=/usr/sbin --sysconfdir=/etc --localstatedir=/var/mysql --infodir=/usr/share/info --mandir=/usr/share/man --without-debug --with-mysqld-user=mysql Perl: This is perl, version 5.005_03 built for i586-linux - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
From Saravanan, REC, INDIA
Hi Lists, I recently downloaded mysql-3.23.51-pc-linux-gnu-i686 from mysql.com for linux platform. I did the following. Created group mysql Created user mysql Extracted the zip using gunzip to /usr/local/mysql-VERSION-OS/ cd /usr/local/mysql-VERSION-OS/ /configre --prefix=/usr/loca/mysql [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# Starting mysqld daemon with datab ases from /usr/local/mysql-3.23.51-pc-linux-gnu-i686/data 020801 16:23:20 mysqld ended [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# make make: *** No targets specified and no makefile found. Stop. [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# make install make: *** No rule to make target `install'. Stop. What is the problem, I am following the instructions provided in the manual.txt. I am new to this. Regards, Saravanan * C. Saravanan Phone : 500 281 *2241 (Off.) Programmer, Dept. of Comp.Sc. Eng. 341 311 (Res.) Regional Engineering College, Trichy, Tamilnadu, INDIAFax : 500 188 * What is a friend? A single soul dwelling in two bodies. - This message was sent from RECTMAIL. http://www.rect.edu/ or http://rangoli.rect.ernet.in/ - 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
Linux Installation (make) problem
To: [EMAIL PROTECTED] From: Saravanan C [EMAIL PROTECTED] Subject: From Saravanan, REC, INDIA Date: Thu, 1 Aug 2002 13:05:17 GMT - Hi Lists, I recently downloaded mysql-3.23.51-pc-linux-gnu-i686 from mysql.com for linux platform. I did the following. Created group mysql Created user mysql Extracted the zip using gunzip to /usr/local/mysql-VERSION-OS/ cd /usr/local/mysql-VERSION-OS/ ./configre --prefix=/usr/loca/mysql . . [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# Starting mysqld daemon with datab ases from /usr/local/mysql-3.23.51-pc-linux-gnu-i686/data 020801 16:23:20 mysqld ended [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# make make: *** No targets specified and no makefile found. Stop. [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# make install make: *** No rule to make target `install'. Stop. What is the problem, I am following the instructions provided in the manual.txt. I am new to this. Regards, Saravanan * C. Saravanan Phone : 500 281 *2241 (Off.) Programmer, Dept. of Comp.Sc. Eng. 341 311 (Res.) Regional Engineering College, Trichy, Tamilnadu, INDIAFax : 500 188 * What is a friend? A single soul dwelling in two bodies. - This message was sent from RECTMAIL. http://www.rect.edu/ or http://rangoli.rect.ernet.in/ - This message was sent from RECTMAIL. http://www.rect.edu/ or http://rangoli.rect.ernet.in/ - 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
enable-local-infile=1 or where can I found older version?
I installed mysql version 3.23.51 on redhat 7.2 (but I had the same problem with version 3.23.49a) I am not able to load data form text file neather with the load command or with mysqlimport The error message is The used command is not allowed with this Mysql version I found some mail telling the problem is in the way I configured mysql whene I installed it (I didn't enable local-infile=1) but with the two release I tried to install (.tar.gz) you use a particulare configure wich start ./scripts/mysql_install_db In the online documentation they say to give command wich are different from the one you find in the file INSTALL-BiNARY you got with the distribution. Please can you give some suggestions? where can I found old realease? Thank you for help Lana __ Scarica il nuovo Yahoo! Messenger: con webcam, nuove faccine e tante altre novità. http://it.yahoo.com/mail_it/foot/?http://it.messenger.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
MySQL bug? - problems with character-set
Hi! I tested MySQL 3.23.51 under Windows and 3.23.49 under Linux RedHat 7.3 with such SQL: select lcase('HERE I TIPED IN SPECIFIC MY LANGUAGE CHARACTERS') and recieved different results (of course, I used same config file with case-sensitive configuration). Result under Linux looks better, under window - wrong, because result contain lower-case and upper-case symbols together It's loks like this:'HErE I tIPED MY LaNGUaGE CHaraCtERS' Now I feel confused, hov I can test, is my conf file. Andis Grasis - 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: dos trouble
What O/S are you using ... Win2k, WinXP, etc.? - Original Message - From: Shion D. Deysarkar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, July 31, 2002 11:15 PM Subject: dos trouble I'm trying to start up mysql through dos prompt. When I enter a command like c:\mysql\bin\mysqld or anything similar (any command in the bin directory), it just waits there. I can't Ctrl-C out of it. I can end the process to get out. What's happening? -- Shion Deysarkar http://www.contrib.andrew.cmu.edu/~sdd Be water, my friend. -- Bruce Lee - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - 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 3.23.51 compile problems with SuSE 8.0
Blast! I _did_ ask Yast to install the file - for some reason it didnt go on during the installation. Ah well, you learn something new every day... Many thanks for your time in helping me solve this one. Regards Andy On Thu, 1 Aug 2002, Lenz Grimmer wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 01 August 2002 12:24, A.J.Dawson wrote: I get a slightly different result in my config.h file: $ grep GETHOSTBY config.h /* #undef HAVE_GETHOSTBYNAME_R_GLIBC2_STYLE */ /* #undef HAVE_GETHOSTBYNAME_R_RETURN_INT */ #define HAVE_GETHOSTBYADDR_R 1 #define HAVE_GETHOSTBYNAME_R 1 Sorry to say that I have no real idea what this means! I have had a rummage through the config.log file (available at http://mosquito.mech.brad.ac.uk/~andy/mysql/config.log.gz) to find that it is throwing errors up to do with 'cclplus'. Could this be the cause of the problem? Have I done something really daft like not install something that is required? Yes, looks like that is the reason. Please make sure to install package gpp, which is the GNU C++ compiler - it includes the missing cc1plus and g++ files. If you could decipher the config.log file and let me know what is going on, that would be great. Please install this package and try it again. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE9SRC3SVDhKrJykfIRAprKAJ9dVD3W6iNov/zqcO5ZkmyCOtdTRwCfT7c2 4/QrvnkHhwA6nsAjW5hphrI= =N3Jw -END PGP SIGNATURE- Dr. Andy Dawson [EMAIL PROTECTED] http://www.mossie.org http://www.museum-explorer.org.uk Never attribute to malice that which is adequately explained by stupidity. - 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 crashes (buffer overrun?)
Colin, what version of MySQL you are running and on what OS? The assertion means there is an attempt to reference a pointer which does not point to the buffer pool. It might be memory corruption or table corruption. If you have updated to new disk drives in Linux, best to check if there are newer drivers. Please send me the complete error log so that I can resolve the stack traces if possible. Best regards, Heikki Innobase Oy ... We just brought a database back up after bringing it down for a hardware upgrade (added hard drives) and after we brought it back up it started crashing a lot. The queries that crash it vary. I realize it might be simple case of database corruption (the data files are now on a different physical drive) but I thought I'd post it here in case the logged messages ring any bells. Relevant info: InnoDB: Assertion failure in thread 24601 in file ../include/buf0buf.ic line 263 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 agaist 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=268431360 record_buffer=1044480 sort_buffer=1048568 max_used_connections=19 max_connections=200 threads_connected=20 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 670938 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Any ideas? - Colin - 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
Please suggest some
Very sorry for my childish question, I'm just starting to learn about MySQL Following is my procedure in fixing the problem of installation on Intel PC, and Linux RH7.3. 1. RPM installation 2. When execute mysql, there is a message ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/msql.sock' (111) 3. Follow the Binary installation 4. At ./scripts/mysql_install_db, there is a message ./bin/mysqld: Shutdown Complete 5. At ./bin/safe_mysqld --user=mysql , there is a message mysqld ended 6. The mysql command still cannot be executed successfully with the message in the 2nd step. In /var/log/mysql.log shows an error message /usr/local/mysql/bin/mysqld: can't find file: ./mysql/host.frm (errno: 13), then mysqld ended. There is a comment suggesting that this is about 'permittion denied', but the above process is run under user 'root@localhost'. Please suggest me how can I start mysql.. Chai --- Chai Wutiwiwatchai Furui Laboratory, Department of Computer Science, Tokyo Institute of Technology --- _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - 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: Linux Installation (make) problem
Hi, I recomend you to use the pre-compiles binary distribution. You won't have to build it and it worked for me. This is the link for the Intel platform: http://www.mysql.com/downloads/download.php?file=Downloads/MySQL-3.23/mysql-3.23.51-pc-linux-gnu-i686.tar.gz Bye and Good Luck. --- Saravanan C [EMAIL PROTECTED] wrote: To: [EMAIL PROTECTED] From: Saravanan C [EMAIL PROTECTED] Subject: From Saravanan, REC, INDIA Date: Thu, 1 Aug 2002 13:05:17 GMT - Hi Lists, I recently downloaded mysql-3.23.51-pc-linux-gnu-i686 from mysql.com for linux platform. I did the following. Created group mysql Created user mysql Extracted the zip using gunzip to /usr/local/mysql-VERSION-OS/ cd /usr/local/mysql-VERSION-OS/ ./configre --prefix=/usr/loca/mysql . . [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# Starting mysqld daemon with datab ases from /usr/local/mysql-3.23.51-pc-linux-gnu-i686/data 020801 16:23:20 mysqld ended [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# make make: *** No targets specified and no makefile found. Stop. [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# make install make: *** No rule to make target `install'. Stop. What is the problem, I am following the instructions provided in the manual.txt. I am new to this. Regards, Saravanan * C. Saravanan Phone : 500 281 *2241 (Off.) Programmer, Dept. of Comp.Sc. Eng. 341 311 (Res.) Regional Engineering College, Trichy, Tamilnadu, INDIAFax : 500 188 * What is a friend? A single soul dwelling in two bodies. - This message was sent from RECTMAIL. http://www.rect.edu/ or http://rangoli.rect.ernet.in/ - This message was sent from RECTMAIL. http://www.rect.edu/ or http://rangoli.rect.ernet.in/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: THIS IS NOT AN ADVERTISEMENT: Please suggest me some
well for one i think you need to make sure after doing mysql_install_db , you check the ownership of your diretory , is should be owned by mysql.deamon, after that try and start mysql i think it should work after, such errors are ususally due to ownership. hope this helps :) ma 2cents cheers David Ziggy Lubowa Systems Administrator One2net (U) 7th Floor Workers Hse Tel: +256 41 345466 Mob: +256 77 769064 Fax: +256 41 345468 Web: www.one2net.co.ug --- A Network Of People And Technology | --- On Thu, 1 Aug 2002, Chai Wutiwiwatchai wrote: Original-Message-ID: [EMAIL PROTECTED] Original-Sender: Chai Wutiwiwatchai [EMAIL PROTECTED] Original-Subject: Please suggest some Original-Date: Thu, 01 Aug 2002 19:52:59 +0700 - Very sorry for my childish question, I'm just starting to learn about MySQL Following is my procedure in fixing the problem of installation on Intel PC, and Linux RH7.3. 1. RPM installation 2. When execute mysql, there is a message ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/msql.sock' (111) 3. Follow the Binary installation 4. At ./scripts/mysql_install_db, there is a message ./bin/mysqld: Shutdown Complete 5. At ./bin/safe_mysqld --user=mysql , there is a message mysqld ended 6. The mysql command still cannot be executed successfully with the message in the 2nd step. In /var/log/mysql.log shows an error message /usr/local/mysql/bin/mysqld: can't find file: ./mysql/host.frm (errno: 13), then mysqld ended. There is a comment suggesting that this is about 'permittion denied', but the above process is run under user 'root@localhost'. Please suggest me how can I start mysql.. Chai _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A field named change can't be created
A field named change can't be created. Any other name is OK. CREATE TABLE test ( change char(1) default NULL ) SQL-query : CREATE TABLE test ( change char(1) default NULL ) MySQL said: You have an error in your SQL syntax near 'change char(1) default NULL )' at line 1 -- Taras V. Plakhotnichenko e-mail: [EMAIL PROTECTED] -- I have four arrows under my belt, One is gone, it well sped - 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
Problems When Upgrading to MySQL 4.02
Hi, I am trying to upgrade to 4.02 from 3.23.47 on FreeBSD 4.6. The mysql database does not want to work for some reason, when I move my old mysql database over the new one, it does not start up, giving an error about permissions even though the permissions of that directory are 770 mysql.mysql. Is this is a common bug? I had it running once, but it was not displaying my webpage correctly with broken images everywhere instead of the pictures that are grabbed from the db. - 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
THIS IS NOT AN ADVERTISEMENT: Please suggest me some
Original-Message-ID: [EMAIL PROTECTED] Original-Sender: Chai Wutiwiwatchai [EMAIL PROTECTED] Original-Subject: Please suggest some Original-Date: Thu, 01 Aug 2002 19:52:59 +0700 - Very sorry for my childish question, I'm just starting to learn about MySQL Following is my procedure in fixing the problem of installation on Intel PC, and Linux RH7.3. 1. RPM installation 2. When execute mysql, there is a message ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/msql.sock' (111) 3. Follow the Binary installation 4. At ./scripts/mysql_install_db, there is a message ./bin/mysqld: Shutdown Complete 5. At ./bin/safe_mysqld --user=mysql , there is a message mysqld ended 6. The mysql command still cannot be executed successfully with the message in the 2nd step. In /var/log/mysql.log shows an error message /usr/local/mysql/bin/mysqld: can't find file: ./mysql/host.frm (errno: 13), then mysqld ended. There is a comment suggesting that this is about 'permittion denied', but the above process is run under user 'root@localhost'. Please suggest me how can I start mysql.. Chai _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: maximum number of columns max row size
A rule of thumb I always follow is to design the DB so that you don't have to add columns (or modify the structure) unless something unforseen comes up (like a new feature). In your case, you are expecting to add variables in the future, so I would deign accordingly. Use rows instead of columns. Using rows also allows you to index all variable values and names, since there are only two columns. You couldn't index all your columns if you expanded horizontally instead of vertically. Besides, it's a heck of a lot easier to add a row than a column. On Thursday, August 1, 2002, at 01:09 AM, Michael Zelina wrote: Can someone provide insight into what a good maximum number of columns is in a MySQL database? I have an extensive list of variables for an engineering application which number in the 700+ range with more additions possible down the road. My question is: should I break up the variables into several tables for storage, or can I use a single table with this many columns. I cannot find any good rules of thumb for how much the number of columns effects performance. In trying to consolidate my existing data from Access (which limits columns to 255 per table) into a single table, I received the following error: mysql create table tblsessions select * from tblsessionfiles, tblsessionfiles2, tblsessionfiles3 WHERE tblsessionfiles2.sessionid_2 = tblsessionfiles.id38 AND tblsessionfiles3.sessionid_3 = tblsessionfiles.id38; ERROR 1118: Too big row size. The maximum row size, not counting BLOBs, is 65535. You have to change some fields to BLOB I assumed that the maximum row size is the total bytes for the entire row. My questions are: (1) can I change this without recompling MySQL and (2) should I really do this or just stick with three or more separate tables for performance reasons. FYI, I am running this on a well-equipped server and will probably only have a maximum # of concurrent users doing DELETE and INSERT in the range of 150 or so. Thanks, Mike - 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 -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - 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: Problems When Upgrading to MySQL 4.02
Check the files in the directory. I bet they are not owned by mysql. Cliff wrote: Hi, I am trying to upgrade to 4.02 from 3.23.47 on FreeBSD 4.6. The mysql database does not want to work for some reason, when I move my old mysql database over the new one, it does not start up, giving an error about permissions even though the permissions of that directory are 770 mysql.mysql. Is this is a common bug? I had it running once, but it was not displaying my webpage correctly with broken images everywhere instead of the pictures that are grabbed from the db. - 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: Problems When Upgrading to MySQL 4.02
all of the files are chown -R mysql in that directory. Any other ideas? - Original Message - From: Gerald Clark [EMAIL PROTECTED] To: Cliff [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, August 01, 2002 7:05 AM Subject: Re: Problems When Upgrading to MySQL 4.02 Check the files in the directory. I bet they are not owned by mysql. Cliff wrote: Hi, I am trying to upgrade to 4.02 from 3.23.47 on FreeBSD 4.6. The mysql database does not want to work for some reason, when I move my old mysql database over the new one, it does not start up, giving an error about permissions even though the permissions of that directory are 770 mysql.mysql. Is this is a common bug? I had it running once, but it was not displaying my webpage correctly with broken images everywhere instead of the pictures that are grabbed from the db. - 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 on open a table
Florian, Thursday, August 01, 2002, 10:44:40 AM, you wrote: FL I do not manage to open a table it puts me Can' t open takes off: ' FL pkf_as_events_log. MYD '. (Errno: 145) is what anybody has already had FL this error on mysql? Your table is corrupted. You shoulf repair it: http://www.mysql.com/doc/R/e/Repair.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: A field named change can't be created
you could always try dropping a 'd' on the end of 'change'... as in making the field 'changed' if it's a flag to see if the data has changed. Gerald Clark wrote: It is not a good idea to use reserved words for field names. If you must, then use back ticks ( below the ~ on my keyboard) . CREATE TABLE test (`change` char(1) default NULL ) Taras Plakhotnichenko wrote: A field named change can't be created. Any other name is OK. CREATE TABLE test ( change char(1) default NULL ) SQL-query : CREATE TABLE test ( change char(1) default NULL ) MySQL said: You have an error in your SQL syntax near 'change char(1) default NULL )' at line 1 - 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
Selectable results
Hello, In my web page I have MySQL retrieve information from a database, and display it in a table. I would like to make the results of the search selectable, as in, be able to click on a field in my table and have it preform a function. Here is the code for my table. I would like $myrow[FileName] to be selectable, with an onClick or onMouseover function. if ($myrow = mysql_fetch_array($result)){ echo table border=6 ; echo trtdh3File Name/h3/td/tr ; do{ printf(trtd%s/td/tr, $myrow[FileName]); } while ($myrow = mysql_fetch_array($result)); echo /table ; } I would appreciate some help on the syntax. Thanks, B __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.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
permissions questions...
mysql show grants for nouveau@localhost; +--- + | Grants for nouveau@localhost| +--- + | GRANT USAGE ON *.* TO 'nouveau'@'localhost' IDENTIFIED BY PASSWORD '4be6641910d7bc1b' | +--- + 1 row in set (0.00 sec) mysql revoke usage on *.* from nouveau@localhost; Query OK, 0 rows affected (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.01 sec) mysql show grants for nouveau@localhost; +--- + | Grants for nouveau@localhost| +--- + | GRANT USAGE ON *.* TO 'nouveau'@'localhost' IDENTIFIED BY PASSWORD '4be6641910d7bc1b' | +--- + 1 row in set (0.00 sec) Is there a way to get rid of that? Is there a way to create a user by giving access to only one database, without giving him the permission to mysqlshow --user=[thisuser] --password=[thispassword] and retrieve all database names? He should see only the name of his database...? Thanks! - Christian Caron - 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: dos trouble
I'm trying to start up mysql through dos prompt. When I enter a command like c:\mysql\bin\mysqld or anything similar (any command in the bin directory), it just waits there. I can't Ctrl-C out of it. I can end the process to get out. What's happening? You just started the server. If you want to run it as a service (I assume you have Windows NT or 2000), use the --install option. Now, you could open another command prompt window and start mysql with, for example: mysql --user=someguy --password=secret Hope this helps. = - Benoit St-Jean [EMAIL PROTECTED] Yahoo! Messenger: bstjean http://cactus.swiki.net - __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to speed up slow query from OR
Hello! James MacLean wrote: Hi Folks, Have only known enough SQL to do what we needed. Today on mysql-3.23.51 we found out that : select something from table where index1 = 'value' or index2 = 'value'; Runs considerably slower than: select something from table where index1 = 'value'; or select something from table where index2 = 'value'; Does it have a compund index, created like this: CREATE INDEX myindex ON table (index1,index2); ??? Greetings Ralf -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Connecting to MySQL using OLEDB
Tell me what you do ADODB - OLEDB driver for ODBC - MyODBC - MySQL ADODB - MyOLEDB - MySQL or anything else? first case: check don't promt on connect Did you create a data source? (First thing you don't need to do...) But whatever you do with VB, MS ADODB and MySQL - you will need time. (Many time ;-) ) Chris - Original Message - From: Darrell A. Sullivan, II [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 31, 2002 9:04 PM Subject: Connecting to MySQL using OLEDB Can anyone tell me if it is possible to connect to a MySQL server running on Linux from a Windows Visual Basic application without setting up a DSN on the Windows computer? I know this can be done with MS SQL Server and I have heard it can be done with Oracle so I am wondering if there is a connection string that will work with MySQL. Currently everything I try brings up a DSN setup screen and this is something that I definitely do not want. Thanks for any help. Darrell - 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
various questions about my.ini /or my.cnf
As a newcomer to MySql, I have struggled through the install and am working my way through the tutorial. My environment: Win2000 MySql version 3.23.51-nt My.ini: [mysqld] # set basedir to installation path, e.g., c:/mysql basedir=c:/mysql # set datadir to location of data directory, # e.g., c:/mysql/data or d:/mydata/data datadir=c:/mysql/data # turn load data local option on local-infile=1 I am attempting to load table = menagerie from a text file name pet.txt. To make this work I have had to do the following: 1. Include local-infile=1 option on the login to mysql. Why is this not picked from My.ini? C:\mysql\binmysql -u jarkema -p --local-infile=1 2. Fully qualify the input file on the load data command. Why is the data path not being picked up from My.ini? mysql load data local infile c:/mysql/data/pet.txt into table pet; If I don't fully qualify the input file name I get: mysql load data local infile pet.txt into table pet; ERROR: File 'pet.txt' not found (Errcode: 2) It doesn't appear that my.ini is being used by mysql. Can anyone explain why? Thanks, Jerry - 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: Linux Installation (make) problem
Hello! Saravanan C wrote: To: [EMAIL PROTECTED] From: Saravanan C [EMAIL PROTECTED] Subject: From Saravanan, REC, INDIA Date: Thu, 1 Aug 2002 13:05:17 GMT - Hi Lists, I recently downloaded mysql-3.23.51-pc-linux-gnu-i686 from mysql.com for linux platform. I did the following. Created group mysql Created user mysql Extracted the zip using gunzip to /usr/local/mysql-VERSION-OS/ cd /usr/local/mysql-VERSION-OS/ ./configre --prefix=/usr/loca/mysql . . You changed the ownership of the files and directories to mysql.mysql? You cared for the /var/run/mysql drectory? [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# Starting mysqld daemon with datab ases from /usr/local/mysql-3.23.51-pc-linux-gnu-i686/data 020801 16:23:20 mysqld ended [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# make make: *** No targets specified and no makefile found. Stop. [root@neelsl mysql-3.23.51-pc-linux-gnu-i686]# make install make: *** No rule to make target `install'. Stop. What is the problem, I am following the instructions provided in the manual.txt. I am new to this. Greetings Ralf -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: dos trouble
Home or Pro? I have no experience with WinXP Home, but with WinXP Pro you need to installed MySQL as a service, then start the service. After that, the console will start with c:\mysql\bin\mysql Instructions for installing as a service are in the MySQL Manual. -Original Message- From: Shion D. Deysarkar [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 01, 2002 9:32 AM To: Gerald R. Jensen Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: dos trouble Win XP On Thu, 1 Aug 2002, Gerald R. Jensen wrote: What O/S are you using ... Win2k, WinXP, etc.? - Original Message - From: Shion D. Deysarkar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, July 31, 2002 11:15 PM Subject: dos trouble I'm trying to start up mysql through dos prompt. When I enter a command like c:\mysql\bin\mysqld or anything similar (any command in the bin directory), it just waits there. I can't Ctrl-C out of it. I can end the process to get out. What's happening? -- Shion Deysarkar http://www.contrib.andrew.cmu.edu/~sdd Be water, my friend. -- Bruce Lee - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. -- Shion Deysarkar http://www.contrib.andrew.cmu.edu/~sdd Ah! That is clearly a metaphysical speculation, and like most metaphysical speculations has very little reference at all to the actual facts of real life, as we know them. -- Gwendolen - 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 speed up slow query from OR
On Thu, 1 Aug 2002, Ralf Narozny wrote: Hello! James MacLean wrote: Hi Folks, Have only known enough SQL to do what we needed. Today on mysql-3.23.51 we found out that : select something from table where index1 = 'value' or index2 = 'value'; Runs considerably slower than: select something from table where index1 = 'value'; or select something from table where index2 = 'value'; Does it have a compund index, created like this: CREATE INDEX myindex ON table (index1,index2); ??? Greetings Ralf Just tried that and it almost doubled my time again (to over 1sec) :(. Thanks for the idea though :), welcome any more :), JES -- James B. MacLean[EMAIL PROTECTED] Department of Education Nova Scotia, Canada B3M 4B2 - 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
Error in using source command with PHP
When in MySQL in a terminal, if I enter the command source scriptfile.sql, MySQL correctly executes the source file. However, if using PHP, I execute the following commands in a PHP file: ?php // data base is connected... $query = source scriptfile.sql; $result = mysql_query($query); I get a MySQL Query Error - You have an error in your SQL syntax near 'source scriptfile.sql' at line 1. I have tried numerous combinations of parentheses, single quotes and relative and absolute file paths. Can someone please tell me what I am doing wrong? Stephen Wardlaw Yale labs - 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
NumberFormatException at ResultSet.getTimestamp()!
Hello: I am getting NumberFormatException when getting Timestamp using ResultSet class. This occurs only if I run simultaneous threads. This doesn't happen if the same select query is not fired several times. The application is currently using older version of JDBC driver (mysql_both_comp.jar). I downloaded 2.0.14 version and tried on that. I didn't face the problem again. Anyone know if this is the bug with the old version of JDBC driver? It will be very helpful if I can get a good response on this. - thanks Ram Myneni TransLogic Systems 5th Floor Amogh Plaza, Greenlands, Hyderabad, AP 500 016, India Tel: +91-40-3401795 x 25 Fax: +91-40-3407943 http://www.translogicsys.com Logic that 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
RPM Installation Question
I was hoping to use the RPM distribution to install MySQL on my Linux machine. However, I notice that it sets the data directory to /var/lib/mysql ... This is unacceptable for my purposes... I know I can probably use a link to fix this, but I'd rather do it the right way... How do I tell mySQL where the data directory is? Is this a compiled option? Thanks, --- Jason H. Frisvold Senior ATM Engineer Engineering Dept. Penteledata RedHat Engineer - RHCE # 807302349405893 Cisco Certified - CCNA # CSCO10151622 [EMAIL PROTECTED] --- Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world. -- Albert Einstein [1879-1955] - 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: a newserver would be better than this mailing list , 1 moretime
Personally, I think one simple change would work wonders for the list: adding a [mysql] ident at the beginning of each subject line. An EASY change that would allow for MUCH easier sorting of the list into a different folder. From there, you can thread it with (almost) any modern mailreader.. David Wolf p.s. PLEASE can you put a [mysql] tag on the list??? - 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: Please suggest some
Chai, Thursday, August 01, 2002, 3:52:59 PM, you wrote: CW Very sorry for my childish question, I'm just starting to learn about MySQL CW Following is my procedure in fixing the problem of installation on Intel PC, CW and Linux RH7.3. CW 1. RPM installation CW 2. When execute mysql, there is a message ERROR 2002: Can't connect to CW local MySQL server through socket '/var/lib/mysql/msql.sock' (111) CW 3. Follow the Binary installation CW 4. At ./scripts/mysql_install_db, there is a message ./bin/mysqld: Shutdown CW Complete CW 5. At ./bin/safe_mysqld --user=mysql , there is a message mysqld ended CW 6. The mysql command still cannot be executed successfully with the message CW in the 2nd step. CW In /var/log/mysql.log shows an error message /usr/local/mysql/bin/mysqld: CW can't find file: ./mysql/host.frm (errno: 13), then mysqld ended. There CW is CW a comment suggesting that this is about 'permittion denied', but the above CW process is run under user 'root@localhost'. Please suggest me how can I CW start CW mysql.. MySQL must be the owner of the data dir. chown -R mysql:mysql /var/lib/mysql if /var/lib/mysql is the data dir. -- 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: permissions questions...
Caron, Thursday, August 01, 2002, 5:34:16 PM, you wrote: C mysql show grants for nouveau@localhost; C +--- C + C | Grants for nouveau@localhost| C +--- C + C | GRANT USAGE ON *.* TO 'nouveau'@'localhost' IDENTIFIED BY PASSWORD C '4be6641910d7bc1b' | C +--- C + C 1 row in set (0.00 sec) C mysql revoke usage on *.* from nouveau@localhost; C Query OK, 0 rows affected (0.00 sec) C Is there a way to get rid of that? :-) Yes, give to user 'nouveau' any privileges except USAGE. USAGE is a synonim for 'no privileges'. C Is there a way to create a user by giving access to only one database, C without giving him the permission to mysqlshow --user=[thisuser] C --password=[thispassword] and retrieve all database names? He should see C only the name of his database...? Yes, start mysqld with --safe-show-database option. And since 4.0.2 there is a SHOW DATABASES privilege. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
INSERT Question
Hello All, First, Let me start by saying this question has most likely been asked before, but I did a search in the archive and was unable to come up with any results. I think this is because I am not quite sure what this is called. So, I do apologize, but hopefully this can be answered quickly and will really help me out. This is more of a General SQL question, regarding sytanx. I have 3 table I am going to be working on. contact, address and state. state contains 51 states (Washington D.C is listed as a state) each with their unique ID. Contact contains a persons first and last name, as well as their own ID. Address contains their street name, the city, state and a column called contactid for the id of the person who lives there. State is a foreign key for the state table and contactid is a foreign key for the contact's id. Now here is what I want to do: I want to insert a person into the contact database and then insert their address into the address table, but I want to somehow put the ID of the state, and the id of the contact into the fields without actually having to know the values. Like rather then having to look up New York and then insert 033, can I just somehow put New York? Is this possible to do? Many thanks -Steven mysql, sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Running an SQL file from the command line
mysqlsms.sql Also if you are inside the application mysqlsource sms.sql Bye and Good Luck! --- Scott Pippin [EMAIL PROTECTED] wrote: I need to run a file called sms.sql from the command line. Is this possible and what is the syntax? Thanks in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.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
various questions about my.ini /or my.cnf
As a newcomer to MySql, I have struggled through the install and am working my way through the tutorial. My environment: Win2000 MySql version 3.23.51-nt My.ini: [mysqld] # set basedir to installation path, e.g., c:/mysql basedir=c:/mysql # set datadir to location of data directory, # e.g., c:/mysql/data or d:/mydata/data datadir=c:/mysql/data # turn load data local option on local-infile=1 I am attempting to load table = menagerie from a text file name pet.txt. To make this work I have had to do the following: 1. Include local-infile=1 option on the login to mysql. Why is this not picked from My.ini? C:\mysql\binmysql -u jarkema -p --local-infile=1 2. Fully qualify the input file on the load data command. Why is the data path not being picked up from My.ini? mysql load data local infile c:/mysql/data/pet.txt into table pet; If I don't fully qualify the input file name I get: mysql load data local infile pet.txt into table pet; ERROR: File 'pet.txt' not found (Errcode: 2) It doesn't appear that my.ini is being used by mysql. Can anyone explain why? Thanks, Jerry - 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 speed up slow query from OR
James MacLean wrote: On Thu, 1 Aug 2002, Ralf Narozny wrote: Hello! James MacLean wrote: Hi Folks, Have only known enough SQL to do what we needed. Today on mysql-3.23.51 we found out that : select something from table where index1 = 'value' or index2 = 'value'; Runs considerably slower than: select something from table where index1 = 'value'; or select something from table where index2 = 'value'; Does it have a compund index, created like this: CREATE INDEX myindex ON table (index1,index2); ??? Greetings Ralf Just tried that and it almost doubled my time again (to over 1sec) :(. Thanks for the idea though :), welcome any more :), JES Hello, According to MySQL documentation, indexes are not used with OR: http://www.mysql.com/doc/M/y/MySQL_indexes.html You can't speed up your query by adding indexes. Running 2 separate queries is the best you can do if you want to go fast. Regards -- Joseph Bueno NetClub/Trader.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: permissions questions...
Yes, but when I create a user with any other grants, it generates automatically this grant usage on *.*: mysql use Liste_MP3; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql grant select on Liste_MP3.* to usernew@localhost identified by password; Query OK, 0 rows affected (0.01 sec) mysql show grants for usernew@localhost; +--- + | Grants for usernew@localhost | +--- + | GRANT USAGE ON *.* TO 'usernew'@'localhost' IDENTIFIED BY PASSWORD '0c2993b965 2e3c06' | | GRANT SELECT ON Liste_MP3.* TO 'usernew'@'localhost' | +--- + 2 rows in set (0.00 sec) It's a new user I never entered before, and still, it generates this usage on *.*... Thanks! -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: 1 août, 2002 11:32 To: [EMAIL PROTECTED] Subject: Re: permissions questions... Caron, Thursday, August 01, 2002, 5:34:16 PM, you wrote: C mysql show grants for nouveau@localhost; C +--- C + C | Grants for nouveau@localhost| C +--- C + C | GRANT USAGE ON *.* TO 'nouveau'@'localhost' IDENTIFIED BY PASSWORD C '4be6641910d7bc1b' | C +--- C + C 1 row in set (0.00 sec) C mysql revoke usage on *.* from nouveau@localhost; C Query OK, 0 rows affected (0.00 sec) C Is there a way to get rid of that? :-) Yes, give to user 'nouveau' any privileges except USAGE. USAGE is a synonim for 'no privileges'. C Is there a way to create a user by giving access to only one database, C without giving him the permission to mysqlshow --user=[thisuser] C --password=[thispassword] and retrieve all database names? He should see C only the name of his database...? Yes, start mysqld with --safe-show-database option. And since 4.0.2 there is a SHOW DATABASES privilege. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem under high load
Hi all-- I'm running a PHP/Apache/MySQL web app on a server. Specs are: Slackware Linux v8.0 Kernel 2.4.16 Dual Athlon MP 1.2 GHz processors on a Tyan S2460 Mobo 1 GB registered ecc ram I'm running MySQL 3.23.51-log, I believe, and it's the binary version for x86 supplied by MySQL. The problem I'm having is that under high load, (i.e. 250 connections to mysql server), it seems to just stop executing queries. Up to that point, it handles about 80 queries/sec. The mysql memory footprint grows to about 50 M, according to top. Then, for no apparent reason, it stops handling queries. vmstat shows that there are about 290 processes waiting for run time, although disk activity remains low. I have swap space set up on an independent drive (IDE, with UDMA2), and it's not using much of that anyhow. I've set my key_buffer to 196M, open_tables to 1000 (and /proc/sys/fs/file-max is at 4096), and reasonable defaults (from my-large.cnf) for pretty much everything else. I've analyzed all of my queries, and none of them seem to be slow. Some have using temporary and a few have using filesort, which i know is awful, but there's no reason it's going, going, going, dead. Any help would be greatly appreciated. Matthew Kahn ZipStream Computing, LLC - 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 in using source command with PHP
That is because source is a mysql client command, not as sql command. It gets executed by the client 'mysql'. The 'mysql' client reads it line by line, and passes the lines to the server. The server never sees the scriptfile.sql. You will have to write a PHP modules that reads the file, and passes it lin-by-line to the server, or have PHP execute the mysql client, passing it all options and parameters. Stephen Wardlaw wrote: When in MySQL in a terminal, if I enter the command source scriptfile.sql, MySQL correctly executes the source file. However, if using PHP, I execute the following commands in a PHP file: ?php // data base is connected... $query = source scriptfile.sql; $result = mysql_query($query); I get a MySQL Query Error - You have an error in your SQL syntax near 'source scriptfile.sql' at line 1. I have tried numerous combinations of parentheses, single quotes and relative and absolute file paths. Can someone please tell me what I am doing wrong? Stephen Wardlaw Yale labs - 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: maximum number of columns max row size
Hi Brent, Thanks for the response. Currently, I have the variables listed out in a table with one variable per row... something similar to the following: ++--+- | ID | DimensionName| ++--+- | 1 | units_length | | 2 | units_temp | | 3 | dim_positionX| | 4 | dim_width| | 5 | dim_length | ++--+- So, I can easily add dimensions or remove them as needed. The problem I have concern about is storing the dimension values a user inputs for a given session. For this I now have a table defined as follows that maps to the ID of the variable list table: +--+--+--+--+--+ | SESSIONID| ID1 | ID2 | ID3 | ... | +--+--+--+--+--+ | session_abcd | in | F| 5.5 | | | tesfile1 | in | F| 6.0 | | | large_design | in | F| 7.25 | | | small_design | in | F| 8.0 | | +--+--+--+--+--+ 4 rows in set (0.04 sec) This way, I can have the values stored as a single row and easily delete and add session file values from the database. Using the ID value as the column provides some abstraction so I can rename dimensions and the sessions are still valid. Any thoughts on how better to store the session data? Since I am coming over from Access, I currently have three tables with 255 columns each. Performance-wise it seems OK... and I'm wondering if I should consolidate the tables into one or leave them separated. Or if even with a 255 column table I might run into performance problems. Thanks again for the help, Mike Brent Baisley wrote: A rule of thumb I always follow is to design the DB so that you don't have to add columns (or modify the structure) unless something unforseen comes up (like a new feature). In your case, you are expecting to add variables in the future, so I would deign accordingly. Use rows instead of columns. Using rows also allows you to index all variable values and names, since there are only two columns. You couldn't index all your columns if you expanded horizontally instead of vertically. Besides, it's a heck of a lot easier to add a row than a column. On Thursday, August 1, 2002, at 01:09 AM, Michael Zelina wrote: Can someone provide insight into what a good maximum number of columns is in a MySQL database? I have an extensive list of variables for an engineering application which number in the 700+ range with more additions possible down the road. My question is: should I break up the variables into several tables for storage, or can I use a single table with this many columns. I cannot find any good rules of thumb for how much the number of columns effects performance. In trying to consolidate my existing data from Access (which limits columns to 255 per table) into a single table, I received the following error: mysql create table tblsessions select * from tblsessionfiles, tblsessionfiles2, tblsessionfiles3 WHERE tblsessionfiles2.sessionid_2 = tblsessionfiles.id38 AND tblsessionfiles3.sessionid_3 = tblsessionfiles.id38; ERROR 1118: Too big row size. The maximum row size, not counting BLOBs, is 65535. You have to change some fields to BLOB I assumed that the maximum row size is the total bytes for the entire row. My questions are: (1) can I change this without recompling MySQL and (2) should I really do this or just stick with three or more separate tables for performance reasons. FYI, I am running this on a well-equipped server and will probably only have a maximum # of concurrent users doing DELETE and INSERT in the range of 150 or so. Thanks, Mike - 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 -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - 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: a newserver would be better than this mailing list , 1 more time
p.s. PLEASE can you put a [mysql] tag on the list??? Please not, it wastes space on the screen. not a lot of space - and does have the great advantage that we can then filter out the mysql stuff into a separate mailbox whihc (on standard UNIX mail) isnt possible without it. -pcf. - 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: permissions questions...
on 8/1/02 11:32 AM, Victoria Reznichenko at [EMAIL PROTECTED] wrote: C Is there a way to create a user by giving access to only one database, C without giving him the permission to mysqlshow --user=[thisuser] C --password=[thispassword] and retrieve all database names? He should see C only the name of his database...? Yes, start mysqld with --safe-show-database option. And since 4.0.2 there is a SHOW DATABASES privilege. Following up on this... (as a MySQL newbie)! Is this option somehow specified in the my.conf file? What is the example syntax? Or if not, where would this be specified (so when MySQL starts as a service on its own, this option is included). Thanks for any pointers! -- Bill Leonard [EMAIL PROTECTED] www.machinemen.com407.464.0147 - 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: maximum number of columns max row size
I thought you were coming from some other database. I haven't heard dimension since my FoxPro (on the Mac) days. Since session data is temporary, you should use temporary tables to store you session variables. Just use the real table to hold you default values. You want to do something like this at the start of a session: create table SessionVars SELECT * from SessionDefaultsTable Then you just read or update the SessionVars table as needed. MySQL will handle the creation and management of the table and that temp table will be unique to each user, so you don't need to worry about table name conflicts, MySQL handles it. Also, MySQL handles dropping the table when the user disappears, so you don't have to worry about cleanup. Read up on temporary tables, I think they would help you considerably in this situation. On Thursday, August 1, 2002, at 01:39 PM, Michael Zelina wrote: Hi Brent, Thanks for the response. Currently, I have the variables listed out in a table with one variable per row... something similar to the following: ++--+- | ID | DimensionName| ++--+- | 1 | units_length | | 2 | units_temp | | 3 | dim_positionX| | 4 | dim_width| | 5 | dim_length | ++--+- So, I can easily add dimensions or remove them as needed. The problem I have concern about is storing the dimension values a user inputs for a given session. For this I now have a table defined as follows that maps to the ID of the variable list table: +--+--+--+--+--+ | SESSIONID| ID1 | ID2 | ID3 | ... | +--+--+--+--+--+ | session_abcd | in | F| 5.5 | | | tesfile1 | in | F| 6.0 | | | large_design | in | F| 7.25 | | | small_design | in | F| 8.0 | | +--+--+--+--+--+ 4 rows in set (0.04 sec) This way, I can have the values stored as a single row and easily delete and add session file values from the database. Using the ID value as the column provides some abstraction so I can rename dimensions and the sessions are still valid. Any thoughts on how better to store the session data? Since I am coming over from Access, I currently have three tables with 255 columns each. Performance-wise it seems OK... and I'm wondering if I should consolidate the tables into one or leave them separated. Or if even with a 255 column table I might run into performance problems. Thanks again for the help, Mike Brent Baisley wrote: A rule of thumb I always follow is to design the DB so that you don't have to add columns (or modify the structure) unless something unforseen comes up (like a new feature). In your case, you are expecting to add variables in the future, so I would deign accordingly. Use rows instead of columns. Using rows also allows you to index all variable values and names, since there are only two columns. You couldn't index all your columns if you expanded horizontally instead of vertically. Besides, it's a heck of a lot easier to add a row than a column. On Thursday, August 1, 2002, at 01:09 AM, Michael Zelina wrote: Can someone provide insight into what a good maximum number of columns is in a MySQL database? I have an extensive list of variables for an engineering application which number in the 700+ range with more additions possible down the road. My question is: should I break up the variables into several tables for storage, or can I use a single table with this many columns. I cannot find any good rules of thumb for how much the number of columns effects performance. In trying to consolidate my existing data from Access (which limits columns to 255 per table) into a single table, I received the following error: mysql create table tblsessions select * from tblsessionfiles, tblsessionfiles2, tblsessionfiles3 WHERE tblsessionfiles2.sessionid_2 = tblsessionfiles.id38 AND tblsessionfiles3.sessionid_3 = tblsessionfiles.id38; ERROR 1118: Too big row size. The maximum row size, not counting BLOBs, is 65535. You have to change some fields to BLOB I assumed that the maximum row size is the total bytes for the entire row. My questions are: (1) can I change this without recompling MySQL and (2) should I really do this or just stick with three or more separate tables for performance reasons. FYI, I am running this on a well-equipped server and will probably only have a maximum # of concurrent users doing DELETE and INSERT in the range of 150 or so. Thanks, Mike - 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-
Re: How to speed up slow query from OR
If running two queries is running faster than perhaps the best way to do it is select the first query into a temporary table and then insert select the second to append to the temp table. Then you can select and sort however you want from the temporary table. create table tempname SELECT something from table... insert into tempname (something columns) SELECT something from table... select something from tempname order by column Three steps instead of one, but if it runs faster then it helps. On Thursday, August 1, 2002, at 11:11 AM, James MacLean wrote: On Thu, 1 Aug 2002, Ralf Narozny wrote: Hello! James MacLean wrote: Hi Folks, Have only known enough SQL to do what we needed. Today on mysql-3.23.51 we found out that : select something from table where index1 = 'value' or index2 = 'value'; Runs considerably slower than: select something from table where index1 = 'value'; or select something from table where index2 = 'value'; Does it have a compund index, created like this: CREATE INDEX myindex ON table (index1,index2); ??? Greetings Ralf Just tried that and it almost doubled my time again (to over 1sec) :(. Thanks for the idea though :), welcome any more :), JES -- James B. MacLean[EMAIL PROTECTED] Department of Education Nova Scotia, Canada B3M 4B2 - 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 -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - 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: max_connect_errors wait_timeout problem?
On Wed, 2002-07-31 at 19:09, Troy Hakala wrote: I decreased wait_timeout (from 28,800 to 300) to kill off sleeping threads. This morning, the server stopped accepting connections and gave me this error: ERROR 1129: Host 'hostname' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts' flush-hosts worked and I increased the max_connect_errors from 10 to 1000 to kind of prevent this in the future. But what causes this error? If you read the comments on http://www.mysql.com/doc/B/l/Blocked_host.html it seems that decreasing wait_timeout does cause this. Is there a bug in mysql where it thinks that killing off sleeping threads is really a connect error? I'm using 3.23.45, btw. I'm not shure, but i think it is not a bug. I think a connection is only successfull when it is closed by the same entity who opened it. If it was mysql closing the connection, then it should increment its counter of connection_errors. It tells that someone opened a connection and did not close it. It's only an opinion. -- Diana Soares - 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: maximum number of columns max row size
The way you store session values, as you said, can be troublesome when new dimensions are added, or existing ones removed. Also it might be constrained by some column limit. I'd try something like this, +--++-+---+ | SESSIONID| UserID | DimensionID | Value | +--++-+---+ | session_abcd | 100| 1 | 1 | | session_abcd | 100| 2 | 1 | | large_design | 200| 1 | 7.25 | | small_design | 100| 1 | 8.0 | | ... | ...| ... | ... | +--++-+---+ This might help you expand/shrink dimensions a little easier. One obvious drawback with this approach is that we are fixing all dimension values to a single type, say double. In order for this to work, you'll need to define IDs for each possible character values, which might or might not work for your situation. Hope it helps. James -Original Message- From: Michael Zelina [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 01, 2002 10:40 AM To: Brent Baisley Cc: [EMAIL PROTECTED] Subject: Re: maximum number of columns max row size Hi Brent, Thanks for the response. Currently, I have the variables listed out in a table with one variable per row... something similar to the following: ++--+- | ID | DimensionName| ++--+- | 1 | units_length | | 2 | units_temp | | 3 | dim_positionX| | 4 | dim_width| | 5 | dim_length | ++--+- So, I can easily add dimensions or remove them as needed. The problem I have concern about is storing the dimension values a user inputs for a given session. For this I now have a table defined as follows that maps to the ID of the variable list table: +--+--+--+--+--+ | SESSIONID| ID1 | ID2 | ID3 | ... | +--+--+--+--+--+ | session_abcd | in | F| 5.5 | | | tesfile1 | in | F| 6.0 | | | large_design | in | F| 7.25 | | | small_design | in | F| 8.0 | | +--+--+--+--+--+ 4 rows in set (0.04 sec) This way, I can have the values stored as a single row and easily delete and add session file values from the database. Using the ID value as the column provides some abstraction so I can rename dimensions and the sessions are still valid. Any thoughts on how better to store the session data? Since I am coming over from Access, I currently have three tables with 255 columns each. Performance-wise it seems OK... and I'm wondering if I should consolidate the tables into one or leave them separated. Or if even with a 255 column table I might run into performance problems. Thanks again for the help, Mike Brent Baisley wrote: A rule of thumb I always follow is to design the DB so that you don't have to add columns (or modify the structure) unless something unforseen comes up (like a new feature). In your case, you are expecting to add variables in the future, so I would deign accordingly. Use rows instead of columns. Using rows also allows you to index all variable values and names, since there are only two columns. You couldn't index all your columns if you expanded horizontally instead of vertically. Besides, it's a heck of a lot easier to add a row than a column. On Thursday, August 1, 2002, at 01:09 AM, Michael Zelina wrote: Can someone provide insight into what a good maximum number of columns is in a MySQL database? I have an extensive list of variables for an engineering application which number in the 700+ range with more additions possible down the road. My question is: should I break up the variables into several tables for storage, or can I use a single table with this many columns. I cannot find any good rules of thumb for how much the number of columns effects performance. In trying to consolidate my existing data from Access (which limits columns to 255 per table) into a single table, I received the following error: mysql create table tblsessions select * from tblsessionfiles, tblsessionfiles2, tblsessionfiles3 WHERE tblsessionfiles2.sessionid_2 = tblsessionfiles.id38 AND tblsessionfiles3.sessionid_3 = tblsessionfiles.id38; ERROR 1118: Too big row size. The maximum row size, not counting BLOBs, is 65535. You have to change some fields to BLOB I assumed that the maximum row size is the total bytes for the entire row. My questions are: (1) can I change this without recompling MySQL and (2) should I really do this or just stick with three or more separate tables for performance reasons. FYI, I am running this on a well-equipped server and will probably only have a maximum # of concurrent users doing DELETE and INSERT in the range of 150 or so. Thanks, Mike - Before
Re: maximum number of columns max row size
It's funny how terminology can be so confusing! When I say dimension, I mean engineering-wise as in the distance from a to b. Also, my use of session probably wasn't a good word in this context. Our sessions are actually permanently stored, since they are associated with a particular engineering job. The user may want to recall a past job to review the inputs and/or recreate the design. That said, the temporary table information is useful... I may need that to manage the system once we change the user interface from a single page to a step-by-step approach. Thanks for all of the help! Mike Zelina Brent Baisley wrote: I thought you were coming from some other database. I haven't heard dimension since my FoxPro (on the Mac) days. Since session data is temporary, you should use temporary tables to store you session variables. Just use the real table to hold you default values. You want to do something like this at the start of a session: create table SessionVars SELECT * from SessionDefaultsTable Then you just read or update the SessionVars table as needed. MySQL will handle the creation and management of the table and that temp table will be unique to each user, so you don't need to worry about table name conflicts, MySQL handles it. Also, MySQL handles dropping the table when the user disappears, so you don't have to worry about cleanup. Read up on temporary tables, I think they would help you considerably in this situation. On Thursday, August 1, 2002, at 01:39 PM, Michael Zelina wrote: Hi Brent, Thanks for the response. Currently, I have the variables listed out in a table with one variable per row... something similar to the following: ++--+- | ID | DimensionName| ++--+- | 1 | units_length | | 2 | units_temp | | 3 | dim_positionX| | 4 | dim_width| | 5 | dim_length | ++--+- So, I can easily add dimensions or remove them as needed. The problem I have concern about is storing the dimension values a user inputs for a given session. For this I now have a table defined as follows that maps to the ID of the variable list table: +--+--+--+--+--+ | SESSIONID| ID1 | ID2 | ID3 | ... | +--+--+--+--+--+ | session_abcd | in | F| 5.5 | | | tesfile1 | in | F| 6.0 | | | large_design | in | F| 7.25 | | | small_design | in | F| 8.0 | | +--+--+--+--+--+ 4 rows in set (0.04 sec) This way, I can have the values stored as a single row and easily delete and add session file values from the database. Using the ID value as the column provides some abstraction so I can rename dimensions and the sessions are still valid. Any thoughts on how better to store the session data? Since I am coming over from Access, I currently have three tables with 255 columns each. Performance-wise it seems OK... and I'm wondering if I should consolidate the tables into one or leave them separated. Or if even with a 255 column table I might run into performance problems. Thanks again for the help, Mike Brent Baisley wrote: A rule of thumb I always follow is to design the DB so that you don't have to add columns (or modify the structure) unless something unforseen comes up (like a new feature). In your case, you are expecting to add variables in the future, so I would deign accordingly. Use rows instead of columns. Using rows also allows you to index all variable values and names, since there are only two columns. You couldn't index all your columns if you expanded horizontally instead of vertically. Besides, it's a heck of a lot easier to add a row than a column. On Thursday, August 1, 2002, at 01:09 AM, Michael Zelina wrote: Can someone provide insight into what a good maximum number of columns is in a MySQL database? I have an extensive list of variables for an engineering application which number in the 700+ range with more additions possible down the road. My question is: should I break up the variables into several tables for storage, or can I use a single table with this many columns. I cannot find any good rules of thumb for how much the number of columns effects performance. In trying to consolidate my existing data from Access (which limits columns to 255 per table) into a single table, I received the following error: mysql create table tblsessions select * from tblsessionfiles, tblsessionfiles2, tblsessionfiles3 WHERE tblsessionfiles2.sessionid_2 = tblsessionfiles.id38 AND tblsessionfiles3.sessionid_3 = tblsessionfiles.id38; ERROR 1118: Too big row size. The maximum row size, not counting BLOBs, is 65535. You have to change some fields to BLOB I assumed that the maximum row size is the total bytes for the entire row. My questions are:
FreeBSD/MySQL 4.0.2 and UNION buggy?
Is anyone else seeing odd problems with heavy use of UNIONs on a MySQL 4.0.x server on FreeBSD? I seem to be able to induce the problem on FreeBSD but not Linux. I have a lot more testing to do yet, but if someone else is seeing it that would really help to narrow the bug down. Thanks, Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 63 days, processed 1,333,225,384 queries (241/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: How to speed up slow query from OR
On Thu, Aug 01, 2002 at 06:15:16PM +0200, Joseph Bueno wrote: According to MySQL documentation, indexes are not used with OR: http://www.mysql.com/doc/M/y/MySQL_indexes.html You can't speed up your query by adding indexes. Running 2 separate queries is the best you can do if you want to go fast. In MySQL 4.x you could use a UNION to do it. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 63 days, processed 1,334,527,253 queries (241/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: maximum number of columns max row size
Michael Zelina wrote: This is an interesting idea... I thought about doing this, but the variety of data types prohibited me from doing so. Initially, I was going to make everything VARCHAR(80)... but that seemed to be horrible for data storage purposes and would prevent me from being able to use SQL functions for numeric items without converting the items first. The data conversions are automatic in the server. abcd + 0 is 0 1234 + 1 is 1235 Use the data type field to select the correct input validation routine, but store everything as a varchar. You can then make up your own datatypes, like state or city, or phone. Perl is typeless anyway. If you do math it is a number. If you concat two variables, then they are strings. This works very well. - 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
Installation frustration....
Not sure if this is the correct list to be sending to but I am having some very frustrating problems with mysql-3.23.51 on redhat 7.2 My problem is the db starts just fine using bin/safe_mysqld --user=root although the crt seems to lock when I issue the command. After I hit ctl+c and get back to a prompt, the db is running in ps -aux listing. I checked it from the administrative site that I have setup on the server and the tables are there along with all the data a default install should have. Problem is, when I attempt to start it via mysqld I get 020801 14:27:49 /usr/local/mysql-3.23.51-pc-linux-gnu-i686/bin/mysqld: Table 'mysql.host' doesn't exist Does anyone know what this might be a symptm of? I cant seem to find much about this error specifically that is applicable to my setup. Im just trying to get the db so that it will start via mysqldThanks in advance! Best Regards, Ben Turner == DMX direct, Inc Director of Web Applications http://www.dmx-direct.com 303.333.0999 x15 == - 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
InnoDB: Looong pause when log file is full?
Hi, I've read the performance tuning tips for InnoDB (http://www.innodb.com/ibman.html#InnoDB_tuning), but am getting bit when the log files are full and the buffer pool is checkpointed. By 'geting bit', I mean for several minutes the db server basically stops, and our website stops serving pages. Does anyone have advice about what we can do to alleviate this? Instead of having three 150mb log files, would we be better off with 30 15mb log files? Our log files are on the same raid array as the data, but would it really make that much difference to move them to a separate disk? Alternately, is there a way to trigger this action at night, so we can avoid it happening during the day? It shut us down for about five minutes today. Details follow. Many thanks, --Pete Harlan [EMAIL PROTECTED] Possibly relevant details: 1. Binary mysql-max-3.23.51-pc-linux-gnu-i686.tar.gz. 2. Linux 2.4.19-rc3, though has happened with other kernels. Debian woody. 3. Dual Intel 933mhz, 2gb ram, 15krpm scsi raid. Here's the /etc/my.cnf file we use. Except for this pausing, performance is generally great, and we're not disk bound or running out of ram (500mb free (used for caching) while machine is pausing): /etc/my.cnf: [mysqld] log-slow-queries skip-locking innodb_data_home_dir = /usr/local/mysql/data/innodb/data innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:50M:autoextend set-variable = innodb_buffer_pool_size = 1200M set-variable = innodb_additional_mem_pool_size = 20M innodb_log_group_home_dir = /usr/local/mysql/data/innodb/logs innodb_log_arch_dir = /usr/local/mysql/data/innodb/logs innodb_log_archive = 0 set-variable = innodb_log_files_in_group = 3 set-variable = innodb_log_file_size = 150M set-variable = innodb_log_buffer_size = 4M innodb_flush_log_at_trx_commit = 0 set-variable = innodb_file_io_threads = 4 set-variable = innodb_lock_wait_timeout = 50 set-variable = record_buffer = 1M set-variable = sort_buffer = 2M set-variable = key_buffer=150M set-variable = tmp_table_size=4M set-variable = table_cache=500 set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = thread_concurrency=4 default-table-type=InnoDB set-variable = long_query_time=5 log-bin server-id=1 - 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
InnoDB enum and set datatypes
I was wondering about the Enum and Set column types in InnoDB tables. I was able to create an InnoDB table with an enum column, but i get errors when trying to insert values into that column. I'm thinking that InnoDB doesnt support those data types, but the MySQL interface to InnoDB may have some not quite consistent behavior. no biggie, just wondering if anyone has encountered this. sean peters [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
[Bug in UNION clause]
Description: The UNION clause displays results 2 times even when not using the ALL keyword with it. example: (SELECT allusa.id,line,name,city,country,listcity,state,liststate,url,line,bookable,cost_range_low,cost_range_high,showlink,desc1,child,pets,wc,spec.id,spec.historic,spec.skiing,spec.romance,spec.antiques,spec.family_fun,spec.spas,spec.luxury_,spec.historic FROM bb.allusa LEFT JOIN bb.spec on allusa.id=spec.id WHERE city = 'san francisco' AND line=1 order by state,city,name) UNION (SELECT allusa.id,line,name,city,country,listcity,state,liststate,url,line,bookable,cost_range_low,cost_range_high,showlink,desc1,child,pets,wc,spec.id,spec.historic,spec.skiing,spec.romance,spec.antiques,spec.family_fun,spec.spas,spec.luxury_,spec.historic FROM bb.allusa LEFT JOIN bb.spec on allusa.id=spec.id WHERE (line=1) AND (city LIKE '%san%' OR listcity LIKE '%san%' OR state='san' OR statename LIKE '%san%' OR country LIKE '%san%' OR name LIKE '%san%') AND (city LIKE '%francisco%' OR listcity LIKE '%francisco%' OR state='francisco' OR statename LIKE '%francisco%' OR country LIKE '%francisco%' OR name LIKE '%francisco%')ORDER BY state,city,name ) LIMIT 15,15 produces the city San Francisco twice in the listing. How-To-Repeat: http://www.lanierbb.com/search/search.php?pt=bbq=san+franciscostart=15numr=29 Fix: Could not find a fix Submitter-Id: submitter ID Originator:Cliff Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.0.2-alpha (Official MySQL binary) Server: /usr/local/bin/mysqladmin Ver 8.35 Distrib 4.0.2-alpha, for unknown-freebsdelf4.6 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.2-alpha Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 4 hours 38 min 52 sec Threads: 34 Questions: 478382 Slow queries: 2 Opens: 1924 Flush tables: 1 Open tables: 64 Queries per second avg: 28.591 Environment: System: FreeBSD lists.travelguides.com 4.6-STABLE FreeBSD 4.6-STABLE #0: Sat Jul 13 13:04:51 CDT 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/TRAVELGUIDES i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 132 Jun 21 07:31 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Jun 21 07:31 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 579412 Jun 21 07:31 /usr/lib/libc.so.4 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --enable-assembler --with-named-z-libs=not-used --disable-shared 'CFLAGS=-O3 -fno-omit-frame-pointer' CXX=gcc CC=gcc 'CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ORDER BY question
I am performing a query along the lines of the following: SELECT DISTINCT property.Internal_ID FROM property, owner_names WHERE property.Internal_ID = owner_names.Internal_ID AND [ other conditions ] ORDER BY owner_names.Name Without the order by clause this is a pretty quick query, but with it, things slow down considerably. The query then takes 5-10 times as long. :( the property table has 1,000,000+ records and there are an average of about 1.4 owner_names records for each property record I'm not exactly sure what happens with the ORDER BY clause, because if a property record has more than one owner_name record associated with it, how does MySQL decide which to use for sorting? I'd guess that if there are more than one, it will take the first one in sort order. The owner_names table also has a field named Display_Order, which determines in what order to display the names for mailing labels, etc. I'd really like the ORDER BY to only consider records with Display_Order = 1, but the SELECT to consider all owner_name records. I know you can put some types of expressions in an order by clause, but i don't know how to express the above concept in SQL. thanks sean peters [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: InnoDB: Looong pause when log file is full?
On Thu, Aug 01, 2002 at 04:37:05PM -0500, Pete Harlan wrote: Hi, I've read the performance tuning tips for InnoDB (http://www.innodb.com/ibman.html#InnoDB_tuning), but am getting bit when the log files are full and the buffer pool is checkpointed. By 'geting bit', I mean for several minutes the db server basically stops, and our website stops serving pages. Does anyone have advice about what we can do to alleviate this? Instead of having three 150mb log files, would we be better off with 30 15mb log files? It shouldn't matter how many files you have. InnoDB sees them as one striped file anyway. Our log files are on the same raid array as the data, but would it really make that much difference to move them to a separate disk? It can if things are I/O bound, and they likely are. Alternately, is there a way to trigger this action at night, so we can avoid it happening during the day? It shut us down for about five minutes today. You could increase the size of your logs. That'll increase recovery time if there's ever a crash, but it should give InnoDB more breathing room. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 63 days, processed 1,335,360,828 queries (241/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: Re: InnoDB: Looong pause when log file is full?
Thanks for your feedback (and your general untiring devotion to the cause...) On Thu, Aug 01, 2002 at 04:30:10PM -0700, Jeremy Zawodny wrote: about what we can do to alleviate this? Instead of having three 150mb log files, would we be better off with 30 15mb log files? It shouldn't matter how many files you have. InnoDB sees them as one striped file anyway. That's good to know. You could increase the size of your logs. That'll increase recovery time if there's ever a crash, but it should give InnoDB more breathing room. Won't that only make for fewer long pauses, rather than schedule or shorten them? It won't fly with the powers that be if our system shuts down in the middle of the day and my response is Oh yeah, our server does that every three weeks and there's nothing we can do about it. Someone else suggested setting innodb_flush_log_at_trx_commit to 1, which should make for many frequent flushes rather than one monster occasional flush. Would that not take care of it? (I set it to 0 because the manual suggested that as a big performance improvement at the expense of a small measure of recoverability in the unlikely event of a crash.) [Is there a way to set that variable with 3.23.51 without shutting down the server? I couldn't find a way.] Thanks, --Pete sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ORDER BY question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: | I am performing a query along the lines of the following: | | SELECT DISTINCT property.Internal_ID | FROM property, owner_names | WHERE property.Internal_ID = owner_names.Internal_ID | AND [ other conditions ] | ORDER BY owner_names.Name | | Without the order by clause this is a pretty quick query, but with it, | things | slow down considerably. The query then takes 5-10 times as long. :( | the property table has 1,000,000+ records | and there are an average of about 1.4 owner_names records for each property | record | | I'm not exactly sure what happens with the ORDER BY clause, because | if a property record has more than one owner_name record associated with | it, how does MySQL decide which to use for sorting? | | I'd guess that if there are more than one, it will take the first one in | sort order. | | The owner_names table also has a field named Display_Order, which determines | in what order to display the names for mailing labels, etc. | I'd really like the ORDER BY to only consider records with Display_Order = | 1, | but the SELECT to consider all owner_name records. | | I know you can put some types of expressions in an order by clause, but i | don't | know how to express the above concept in SQL. | | thanks | sean peters | [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 | Is owner_names.Name indexed? If not, MySQL is probably going to need to do a filesort after it has select all of the rows, which will take a very long time...Use EXPLAIN [your query] to see what MySQL is really going to do. -Mark -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6-2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE9ScwdlyjUJM+7nP4RAghsAKCHTB+pKISrX9AP2s2UHuiD0/elTwCffQNv ZwdnPDarmO8MPfz85NRSP2Y= =N7g9 -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
Accessing new columns within the WHERE condition
Okay, I thought I'd done this before but maybe not. I can't figure it out now. What I want to do is: SELECT column AS something FROM table WHERE something 5 Basically I'm creating a new column called something and I want to use it to compare with in the WHERE condition. Is this possible? Ultimately I want to have MySQL calculate the age of people and also put a condition on it. I am using a very old fashioned web language which doesn't have a lot of power. I could do this in PHP if I could only use it. But I need MySQL to do it. Any thoughts? Also what is the name of this that I am doing, creating a new column. Is it a dynamic column or something? - 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
Failed on getting reply's on:: How to download a mssql db to mysql once a day
Hello everybody! I just found out that something strange happend when I tried to subscribe to this list. I was able to send my question but something wrong happend when trying to subscribe. Therefor I was unable to see if I got any answers to my question. I would really appreciate if the people who answered to this question could post it again since I'm in quite a hurry to solve this problem. Thanks again ps: I have subscribed correctly this time:) Sincerely Lars Erik Petersen Original message: Hi! Does anyone know how to copy/download a mssql db running on winnt to a mysql db once a day. I have had a mssql db for some while and want to continue using it. Now I am about to create a website (using php/mysql) that will pull data from the mysql db. Therefor I need the mysql db to either copy the entire mssql db once a day or just pull the changes. I don't wish to work directly on the mssql db - due to security, safety and network traffic. Will myodbc be able to connect to a mssql db and download the data correctly into a mysql db? If not, is there any other way I can get a mssql into fully working mysql db by copy once a day, replicating? In advance, thank you! Sincerely Lars Erik Petersen [EMAIL PROTECTED] - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - 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 4.0 on Debian
Hello all, Has anyone successfully gotten MySQL 4.02 running on Debian? I'm running into problems if I use alien to convert the RPMs. I could install from source, but I want to avoid cruftiness and make it easy to upgrade. Basically, I run into conflicts with the package libmysqlclient10, but if I try to remove this package, I also remove PHP4-mysql, etc. If anyone has gotten this to work, what steps have they taken? I appreciate debian's update system for Apache and PHP, so I don't want to install those from source if it can be avoided. If there's a smarter upgrade path, please let me know. I'm willing to try out different ways of doing this. Using: Debian 3.0 (stable) on i386, apache-ssl 3.26, php4 4.1.2. thanks, Quinten - 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
Basic MySQL setup question
Dear All, My web host provider says that he cannot allow me to have a MySQL account on the server. However, he asks me to set up my tables offline and ftp them across and asses them using Php. Can this be done? Thank you. Kindest regards, Kenneth Gomez, Engineering Analyst, Compumod Sdn Bhd (Malaysia Office of MSC.Software Corporation), Suite 35C, 35th Floor, Empire Tower, 182, Jalan Tun Razak, 50400 Kuala Lumpur, Malaysia. Tel : +603-7490 1022 Fax: +603-7490 1033 http://www.mscsoftware.com.my Stock : http://www.nyse.com/marketinfo/marketinfo.html?sym=mnsx=10y=7 ** This email and any files transmitted with it may contain privileged and confidential information intended solely for the use of the individual to which they are addressed. If you are not the intended recipient, do not read, disseminate, distribute or copy this message or attached files. If you have received this message in error please notify the sender immediately and destroy this email. *** - 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: Basic MySQL setup question
Yes, Install mysql on a local box (same OS as your provider and preferably the same MySql version), create and populate your tables and then FTP the files that reside in the mysql data directory. There will be 3 files associated with each table .MYD (Data file) .MYI (Index file) .frm (schema/description file). Regards, Bhavin. - Original Message - From: Kenneth Gomez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 01, 2002 7:41 PM Subject: Basic MySQL setup question Dear All, My web host provider says that he cannot allow me to have a MySQL account on the server. However, he asks me to set up my tables offline and ftp them across and asses them using Php. Can this be done? Thank you. Kindest regards, Kenneth Gomez, Engineering Analyst, Compumod Sdn Bhd (Malaysia Office of MSC.Software Corporation), Suite 35C, 35th Floor, Empire Tower, 182, Jalan Tun Razak, 50400 Kuala Lumpur, Malaysia. Tel : +603-7490 1022 Fax: +603-7490 1033 http://www.mscsoftware.com.my Stock : http://www.nyse.com/marketinfo/marketinfo.html?sym=mnsx=10y=7 ** This email and any files transmitted with it may contain privileged and confidential information intended solely for the use of the individual to which they are addressed. If you are not the intended recipient, do not read, disseminate, distribute or copy this message or attached files. If you have received this message in error please notify the sender immediately and destroy this email. *** - 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: Basic MySQL setup question
On Fri, Aug 02, 2002 at 10:41:12AM +0800, Kenneth Gomez wrote: Dear All, My web host provider says that he cannot allow me to have a MySQL account on the server. However, he asks me to set up my tables offline and ftp them across and asses them using Php. Can this be done? Yes. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 64 days, processed 1,338,515,981 queries (241/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 4.0 on Debian
On Thu, Aug 01, 2002 at 09:50:33PM -0400, Quinten Steenhuis wrote: Hello all, Has anyone successfully gotten MySQL 4.02 running on Debian? I'm running into problems if I use alien to convert the RPMs. I could install from source, but I want to avoid cruftiness and make it easy to upgrade. Basically, I run into conflicts with the package libmysqlclient10, but if I try to remove this package, I also remove PHP4-mysql, etc. If anyone has gotten this to work, what steps have they taken? I appreciate debian's update system for Apache and PHP, so I don't want to install those from source if it can be avoided. If there's a smarter upgrade path, please let me know. I'm willing to try out different ways of doing this. My only suggestion is that you build from source. What about it do you find crufty? I know it's not the debian way but there's no .deb available... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 64 days, processed 1,338,527,626 queries (241/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
Setting the safe_show_database variable in my.cnf
Newbie alert here...! Making my first attempt at changing a variable... I am trying to add this line: set-variable= safe_show_database=ON To the my.cnf file... But when I do that and then attempt to restart MySQL, it doesn't... Give the can't connect to the mysql.sock error. This is on RedHat 7.3, MySQL 3.23.51. I am using the /etc/my.cnf if that matters. Any help would be GREATLY appreciated... ! -- Bill Leonard [EMAIL PROTECTED] www.machinemen.com407.464.0147 - 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
problems when a combination of join, select distinct and order by are used together.
Description: When we join two or more tables, select distinct and order by on a column, we get problems. How-To-Repeat: mysql select * from test; +--+--+ | a| b| +--+--+ |1 |1 | |2 |2 | |1 |3 | +--+--+ 3 rows in set (0.00 sec) mysql select * from test as a1, test as a2; +--+--+--+--+ | a| b| a| b| +--+--+--+--+ |1 |1 |1 |1 | |2 |2 |1 |1 | |1 |3 |1 |1 | |1 |1 |2 |2 | |2 |2 |2 |2 | |1 |3 |2 |2 | |1 |1 |1 |3 | |2 |2 |1 |3 | |1 |3 |1 |3 | +--+--+--+--+ 9 rows in set (0.00 sec) mysql select * from test as a1, test as a2 order by a2.b; +--+--+--+--+ | a| b| a| b| +--+--+--+--+ |1 |1 |1 |1 | |2 |2 |1 |1 | |1 |3 |1 |1 | |1 |1 |2 |2 | |2 |2 |2 |2 | |1 |3 |2 |2 | |1 |1 |1 |3 | |2 |2 |1 |3 | |1 |3 |1 |3 | +--+--+--+--+ 9 rows in set (0.00 sec) This is the problem: mysql select distinct a1.a from test as a1, test as a2 order by a2.b; +--+ | a| +--+ |1 | |2 | |1 | |2 | |1 | |2 | +--+ 6 rows in set (0.00 sec) mysql select distinct a1.a from test as a1, test as a2 order by a1.b; +--+ | a| +--+ |1 | |2 | |1 | +--+ 3 rows in set (0.01 sec) Interestingly, the query without the order by works... :) mysql select distinct a1.a from test as a1, test as a2; +--+ | a| +--+ |1 | |2 | +--+ 2 rows in set (0.00 sec) Fix: Err... avoid putting order by or distinct together? If you need both ... err... bad luck. Submitter-Id: submitter ID Originator: Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: error on distinct when used with joined table and order by Severity: serious Priority: medium Category: mysql Class: sw-bug Release:mysql-3.23.49 (Source distribution) Environment: System: Linux t1.trellian.com 2.4.18 #1 Wed Jun 5 14:20:33 EST 2002 i686 unknown 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/2.95.4/specs gcc version 2.95.4 20011002 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jul 22 21:23 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1153784 Jul 18 21:53 /lib/libc-2.2.5.so -rw-r--r--1 root root 2390922 Jul 18 21:53 /usr/lib/libc.a -rw-r--r--1 root root 178 Jul 18 21:53 /usr/lib/libc.so -rw-r--r--1 root root 726660 Mar 24 15:56 /usr/lib/libc-client.so.2001 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 --with-libwrap --enable-assembler --with-berkeley-db --with-innodb --enable-static --enable-shared --enable-local-infile --with-raid --enable-thread-safe-client --without-readline --with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql --without-bench --with-client-ldflags=-lstdc++ --with-extra-charsets=all - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Exporting Multi-DataType Excel Worksheet to CSV for Import into MySQL
I have done a lot of looking and search through archives of mailing lists and newsgroups and I guess I haven't looked enough because I still haven't found what I'm looking for. What I have is an Excel worksheet that had 26 fields and several hundred rows of data. The data types will be are INT, TINYTEXT, and TEXT when they finally make it into MySQL. The TEXT and TINYTEXT fields contain commas, double quotes, single quotes, ampersands, dollarsigns, periods, and HTML tags. When I export it to CSV some of the TEXT and TINYTEXT fields only contain alpha characters and so they don't get quoted. My problem: I need all of the TEXT and TINYTEXT fields to be quoted and all of the INT fields to not be quoted (of course). Currently when I export to CSV and then LOAD DATA INTO MySql I get the error: ERROR 1062: Duplicate entry '1' for key 1 My import query is: LOAD DATA INFILE 'filename.csv' INTO TABLE tablename FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' LINES TERMINATED BY '\n'; I have check out the Text Write macro program and it doesn't take my need to have some fields quoted but not others into accout. I am sure someone else out there has done this. I am wondering what the best way to go about this is. Any help is greatly appreciated. Thank you, Jonathan Duncan - 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 bug? - problems with character-set
Hi! It's just a simple example, because I don't want send latvian-specific symbols. This problem occurs with latvian-specific symbols only. Configuration file I cheked some 4-5 times. I'm confused now. Regards from Latvia! Andis Grasis - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 01, 2002 4:41 PM Subject: Re: MySQL bug? - problems with character-set Andis, Thursday, August 01, 2002, 2:28:12 PM, you wrote: AG I tested MySQL 3.23.51 under Windows and 3.23.49 under Linux RedHat 7.3 with AG such SQL: AG select lcase('HERE I TIPED IN SPECIFIC MY LANGUAGE CHARACTERS') AG and recieved different results (of course, I used same config file with AG case-sensitive configuration). AG Result under Linux looks better, under window - wrong, because result AG contain lower-case and upper-case symbols together AG It's loks like this:'HErE I tIPED MY LaNGUaGE CHaraCtERS' AG Now I feel confused, hov I can test, is my conf file. Strange .. on my w2k and RedHat 7.1 box I got correct results: mysql select lcase('HERE I TIPED IN SPECIFIC MY LANGUAGE CHARACTERS'); +--+ | lcase('HERE I TIPED IN SPECIFIC MY LANGUAGE CHARACTERS') | +--+ | here i tiped in specific my language characters | +--+ 1 row in set (0.00 sec) What Windows do you use? How did you install MySQL from binary or from source? -- 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 - 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