Help My Sql
Please Send me a link from where i can download MySql Database for windows and MySqlGui for windows i have been trying to download from the site (MySql.com) but was unable to do so please send me at earliest Awaiting for ur mail Asif __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql 4.0.11-gamma: TRUNCATE table bug
Hi, I am a new user (hardly a month old) to mysql. My configuration: P4 with Windows 2000 prof, apache, PHP4.3, Mysql 4.0.11-gamma, phpmyadmin 2.4.0. Now my problem is, a user with no privilege is able to run SQL command TRUNCATE table to empty any table! First I reported it to phpmyadmin team and after lengthy discussion, I was advised to report to you. Since it is a serious security hole, I would like to get rid of it at the earliest. If any fix is already available, please let me know. Thanks -rajaraman __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.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
[PATCH]: proper usage of errno in mysql 4.0.11a
Hi,
This patch fixes errno usage. Often in mysql there is assumption
that errno is simple int variable while it is not in glibc (=2.3.1).
It's a macro.
See
http://lists.debian.org/debian-glibc/2002/debian-glibc-200212/msg00364.html
Patch by Artur Frysiak [EMAIL PROTECTED] that fixes that (and thus allows
compiling on glibc = 2.3.1). There is simpler patch doing the same
thing here
http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:133487:200302:ioffpbhpdaoihkbckdio
Also these patches were ignored while they are fixing real bugs:
http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:132400:200302:fhigljdnohbmleilklpi
--- mysql-4.0.11a-gamma/mysys/mf_cache.c.wiget Tue Mar 4 16:03:44 2003
+++ mysql-4.0.11a-gamma/mysys/mf_cache.cTue Mar 4 16:04:19 2003
-20,6 +20,7
#include m_string.h
#include my_static.h
#include mysys_err.h
+#include errno.h
/*
Remove an open tempfile so that it doesn't survive
--- mysql-4.0.11a-gamma/mysys/my_copy.c.wiget Tue Mar 4 16:06:02 2003
+++ mysql-4.0.11a-gamma/mysys/my_copy.c Tue Mar 4 16:06:20 2003
-29,6 +29,7
time_t modtime;
};
#endif
+#include errno.h
/*
--- mysql-4.0.11a-gamma/mysys/my_chsize.c.wiget Tue Mar 4 16:04:38 2003
+++ mysql-4.0.11a-gamma/mysys/my_chsize.c Tue Mar 4 16:05:47 2003
-17,6 +17,7
#include mysys_priv.h
#include mysys_err.h
#include m_string.h
+#include errno.h
/*
Change size of file.
--- mysql-4.0.11a-gamma/mysys/my_error.c.wiget Tue Mar 4 16:06:58 2003
+++ mysql-4.0.11a-gamma/mysys/my_error.cTue Mar 4 16:07:11 2003
-19,6 +19,7
#include m_string.h
#include stdarg.h
#include m_ctype.h
+#include errno.h
/* Define some external variables for error handling */
--- mysql-4.0.11a-gamma/mysys/my_delete.c.wiget Tue Mar 4 16:06:34 2003
+++ mysql-4.0.11a-gamma/mysys/my_delete.c Tue Mar 4 16:06:45 2003
-16,6 +16,7
#include mysys_priv.h
#include mysys_err.h
+#include errno.h
int my_delete(const char *name, myf MyFlags)
{
--- mysql-4.0.11a-gamma/mysys/my_getwd.c.wiget Tue Mar 4 16:08:45 2003
+++ mysql-4.0.11a-gamma/mysys/my_getwd.cTue Mar 4 16:08:59 2003
-30,6 +30,7
#if defined(OS2)
#include direct.h
#endif
+#include errno.h
#ifdef __EMX__
/* chdir2 support also drive change */
--- mysql-4.0.11a-gamma/mysys/my_gethostbyname.c.wiget Tue Mar 4 16:07:30 2003
+++ mysql-4.0.11a-gamma/mysys/my_gethostbyname.cTue Mar 4 16:08:13 2003
-23,6 +23,7
#include netdb.h
#endif
#include my_net.h
+#include errno.h
/* This file is not needed if my_gethostbyname_r is a macro */
#if !defined(my_gethostbyname_r)
--- mysql-4.0.11a-gamma/mysys/my_lib.c.wigetTue Mar 4 16:09:10 2003
+++ mysql-4.0.11a-gamma/mysys/my_lib.c Tue Mar 4 16:09:24 2003
-51,6 +51,7
#include iodef.h
#include descrip.h
#endif
+#include errno.h
#ifdef OS2
#include my_os2dirsrch.h
--- mysql-4.0.11a-gamma/mysys/my_lockmem.c.wigetTue Mar 4 16:09:36 2003
+++ mysql-4.0.11a-gamma/mysys/my_lockmem.c Tue Mar 4 16:09:54 2003
-19,6 +19,7
#include mysys_priv.h
#include mysys_err.h
#include my_list.h
+#include errno.h
#ifdef HAVE_MLOCK
#include sys/mman.h
--- mysql-4.0.11a-gamma/mysys/my_lread.c.wiget Tue Mar 4 16:10:06 2003
+++ mysql-4.0.11a-gamma/mysys/my_lread.cTue Mar 4 16:10:17 2003
-16,6 +16,7
#include mysys_priv.h
#include mysys_err.h
+#include errno.h
/* Read a chunk of bytes from a file */
--- mysql-4.0.11a-gamma/mysys/my_mkdir.c.wiget Tue Mar 4 16:11:03 2003
+++ mysql-4.0.11a-gamma/mysys/my_mkdir.cTue Mar 4 16:11:14 2003
-21,6 +21,7
#ifdef __WIN__
#include direct.h
#endif
+#include errno.h
int my_mkdir(const char *dir, int Flags, myf MyFlags)
{
--- mysql-4.0.11a-gamma/mysys/my_lwrite.c.wiget Tue Mar 4 16:10:26 2003
+++ mysql-4.0.11a-gamma/mysys/my_lwrite.c Tue Mar 4 16:10:36 2003
-16,6 +16,7
#include mysys_priv.h
#include mysys_err.h
+#include errno.h
/* Write a chunk of bytes to a file */
--- mysql-4.0.11a-gamma/mysys/my_malloc.c.wiget Tue Mar 4 16:10:45 2003
+++ mysql-4.0.11a-gamma/mysys/my_malloc.c Tue Mar 4 16:10:55 2003
-21,6 +21,7
#include mysys_priv.h
#include mysys_err.h
#include m_string.h
+#include errno.h
/* My memory allocator */
--- mysql-4.0.11a-gamma/mysys/my_once.c.wiget Tue Mar 4 16:11:25 2003
+++ mysql-4.0.11a-gamma/mysys/my_once.c Tue Mar 4 16:11:34 2003
-23,6 +23,7
#include mysys_priv.h
#include my_static.h
#include mysys_err.h
+#include errno.h
/*
Alloc for things we don't nead to free
--- mysql-4.0.11a-gamma/mysys/my_os2file64.c.wiget Tue Mar 4 16:11:55 2003
+++ mysql-4.0.11a-gamma/mysys/my_os2file64.cTue Mar 4 16:12:11 2003
-18,6 +18,8
Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
MA 02111-1307, USA */
+#include errno.h
+
void _OS2errno( APIRET rc);
longlong_lseek64( int fd, longlong offset, int seektype);
int_lock64( int fd, int locktype,
Re: Peculiar Problem During Transactions
Raghu, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, March 05, 2003 7:03 AM Subject: Peculiar Problem During Transactions Placed At : MAATDLN Hi, I have a database with a table that has 3 columns - name, age and status and this table has 10 rows. This table is an Innodb table with dyname table type (incase this is important) and so I can conduct transactions on this table. I using a client open a connection to this database and start a transaction, the first of my statement being a)delete from table-name where name='xxx'; Now I dont either commit or rollback my transaction and on a second window using a client again connect to the database and give a statement - the statement being b)update table-name set status='Y' where status='N'; please see section 8.4 at http://www.innodb.com/ibman.html#InnoDB_transaction_model: UPDATE ... SET ... WHERE ... : sets an exclusive lock on every record the search encounters. The search in your UPDATE is probably not using any index but scanning the whole table. The need to lock every record we look at in an UPDATE stems from MySQL's binlogging. Binlogging requires that data-modifying SQL statements are performed at a SERIALIZABLE isolation level with respect to each other. That in turn is needed because we must be sure a replication slave performs the SQL statement in the exact same way as the master. Now this query hangs and it returns stating lock wait timeout exceeded. I go one step further and give another query c)update table-name set status='Y' where name='bbb' and this works like a dream (as expected). This uses an index and does not scan the whole table. Normally one would have expected the second one also to have worked because Innodb has row level locking on not table level locking but I was quite surprised that it didnt work. Now I know if it had been table leve locking then my third statement would not have worked but it worked...hence it almost confirms that innodb is indeed using row level locking, but is there any way I can get my second statement to work too without me having to wait until I complete the transaction that I started on my first window. Is this condition normal cos this works on both Oracle and Post gres. Regards Raghu Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
myisamchk vs repair and optimize
Hello everyone I have a question. In a MySQL 3.23 server running on Linux (RH 7.x), can I symlink the data files and index files of MyISAM tables to different disks for better performance? After RTFM, it seems that commands alter, repair and optimize will migrate the files back to the same disk automatically? However, if I'm willing to run myisamchk from the command line, will that still happen? best regards, Jens-Petter Salvesen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: [PATCH]: proper usage of errno in mysql 4.0.11a
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wed, 5 Mar 2003, Arkadiusz Miskiewicz wrote: This patch fixes errno usage. Often in mysql there is assumption that errno is simple int variable while it is not in glibc (=2.3.1). It's a macro. Thanks! We've fixed this differently in the meanwhile: [SNIP] # This is a BitKeeper generated patch for the following project: # Project Name: MySQL - fast and reliable SQL database # This patch format is intended for GNU patch command version 2.5 or higher. # This patch includes the following deltas: # ChangeSet1.676.1.665 - 1.676.1.666 # include/global.h1.39.8.1 - 1.39.9.1 # # The following is the BitKeeper ChangeSet Log # # 03/02/28 [EMAIL PROTECTED] 1.676.1.666 # Always include errno.h # # diff -Nru a/include/global.h b/include/global.h - --- a/include/global.h Wed Mar 5 11:46:02 2003 +++ b/include/global.h Wed Mar 5 11:46:02 2003 @@ -223,6 +223,7 @@ #define CONFIG_SMP #include asm/atomic.h #endif +#include errno.h /* Recommended by debian */ /* Go around some bugs in different OS and compilers */ #if defined(_HPUX_SOURCE) defined(HAVE_SYS_STREAM_H) [SNIP] 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.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+ZdWnSVDhKrJykfIRAhNVAJ4tdhGirZ8kLxfchruDqu6nYY0NNgCfQXzC mM1yUv2DY+7gMw5dgKPD3Do= =oAG7 -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
related tables result collected in one field
Hi! I'm having this main table with references to files in it: kod: +-+-+ | id | filnamn | +-+-+ |168 |v008-12.jpg| +-+-+ Then I have this table to relate one file to one or several categories: +-++--+ |id | fromid | toid | +-++--+ |4| 1 | 2 | | 257 | 2 | 2 | +-++--+ Which lies in this table for categories: ++--+ | id | kategorinamn | ++--+ | 5 | demonstrations | | 6 |people | ++--+ The following sql: SELECT DISTINCT bilder.id, bilder.filnamn, kategorier.kategorinamn FROM bilder LEFT JOIN relateratabell ON bilder.id = relateratabell.fromid LEFT JOIN kategorier ON relateratabell.toid = kategorier.id WHERE (relateratabell.fromid IS NULL OR relateratabell.fromid IS NOT NULL) AND bilder.stickord LIKE '%basta%' //for example ORDER BY filnamn; Gives the following result: +---+---+--+ | id | filnamn | kategorinamn| +---+---+--+ | 166| v007-86.jpg | demonstrations | | 166| v007-86.jpg | people | | 167| v008-03.jpg | demonstrations | | 167| v008-03.jpg | people | +---+---+--+ This is what I expected it to, but I'ld rather get a result as this one: +---+---+--+ | id | filnamn | kategorinamn| +---+---+--+ | 166| v007-86.jpg | demonstrations, people| | 167| v008-03.jpg | demonstrations, people| +---+---+--+ Where the categories have been collected together into the same field, so that I don't get multiple rows for the same file Is this possible to achieve? Many many thanks to the one who can give me some input! Sincerely Victor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Need help about SQL sintax
Hello mysql, I have 2 tables : Table1: col1 col2 col3 DataSource Table2: col1 col2 col3 DataDestination How to update Table2.DataDestination with value of Table1.DataSourse where table1.col1=table2.col2 and table1.col2=table2.col2 and table1.col3=table2.col3 -- Best regards, PandaCoop-Krasimir_Slaveykov mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql 4.0.11-gamma: TRUNCATE table bug
Hi! On Mar 05, Dr. R. Rajaraman wrote: Hi, I am a new user (hardly a month old) to mysql. My configuration: P4 with Windows 2000 prof, apache, PHP4.3, Mysql 4.0.11-gamma, phpmyadmin 2.4.0. Now my problem is, a user with no privilege is able to run SQL command TRUNCATE table to empty any table! First I reported it to phpmyadmin team and after lengthy discussion, I was advised to report to you. Yes, you are right. To have bug fixed it's usually a good idea to report it to vendor :) Still proper places for reporting bugs are [EMAIL PROTECTED] - for security-related bugs [EMAIL PROTECTED] - for other bugs Since it is a serious security hole, I would like to get rid of it at the earliest. I wasn't able to repeat it. === mysql select current_user(); ++ | current_user() | ++ | @localhost | ++ 1 row in set (0.00 sec) mysql show grants for ''@'localhost'; +--+ | Grants for @localhost| +--+ | GRANT USAGE ON *.* TO ''@'localhost' | +--+ 1 row in set (0.00 sec) mysql truncate table test.a; ERROR 1044: Access denied for user: '@localhost' to database 'test' === Please, provide more information about this bug. At least, show the output from the command sequence as above. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Ãâ·ÑËÍ800Ôª£¬ÌáÃûÒ»ÈËËÍ200Ôª£¡¿ìÀ´°¡£¡£¡
http://www.richmails.com/cgi-bin/[EMAIL PROTECTED] 2 ,1002520500 http://www.richmails.com/cgi-bin/[EMAIL PROTECTED] 1002561101025534435261 $0.05-$0.204 http://www.richmails.com/cgi-bin/[EMAIL PROTECTED] SIGN-UPCONTINUE Firstname Last Name Street Address 3No.3,ZhongShan Road,Guangzhou,GuangDong !!! City (If this is N/A please enter NA) State Zip Code (If this is N/A, please enter 1) Country Password Confirm Password Click here to indicate that you have read and agree to the Terms and Conditions.CONTINUE OK Your Current Earnings 2 http://www.richmails.com/cgi-bin/[EMAIL PROTECTED] = http://www.chinamysql.com 100M1010M20318/ http://3long.sayba.com 5881800 http://shop.sayba.com http://www.lovexin.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
query re: Innodb Table Locks
to whom it may concern, I need some help regarding transactions using innodb tables. I am running MySQL 3.23.51-Max as database server and developing a web front-end using ASP. Many of the tables used are of innodb format since it is imperative for me to use transactions in most of my scripts. In 95% of the cases the scripts work fine but at times this error pops up 'Lock Wait Timeout Try Restarting Transaction' This leaves certain tables locked for a quite a number of hours and the only possibilty to recover the table is to restart mysql. Now my software is still at the testing stage and so i'm the only user. But when going live, 15 people will be using it concurrently and restarting my sql each time to avoid the table lock will not be possible. How can the deadlock be avoided? I have set innodb_lock_wait as 60 in my.cnf file, is this enough? as for the transaction syntax i am using the following Begin; on error resume next sql statements If err then rollback; else commit; end if on error goto 0 am i doing something wrong? will set autocommit=0; be useful? or is there any way to recover from the table lock? i would really appreciate a reply Best Regards Erik DeBattista Systems Developer Webcraft Ltd. --CONTACT DETAILS--- www.webcraft.com.mt Email: [EMAIL PROTECTED] Tel: +356 21421540 Fax: +356 21419300 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
External clients with MySQl 3.23 on Redhat 8.0
Hi I am having problems setting up an ODBC client on Windows XP for a Redhat server running MySQL 3.23-54. I have seen some references to other people having this problem. Does anyone know about this, and a fix? I don't want to rebuild the server with Debian/Suse (where I have done this before many times), but if I must I will. Many thanx Simon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: [PATCH]: proper usage of errno in mysql 4.0.11a
Hi! On Mar 05, Arkadiusz Miskiewicz wrote: Hi, This patch fixes errno usage. Often in mysql there is assumption that errno is simple int variable while it is not in glibc (=2.3.1). It's a macro. Thank you. It was fixed since 4.0.11 (though, somehwhat simplified way - #include errno.h was added to my_global.h, so all files get it automatically). Also these patches were ignored while they are fixing real bugs: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:132400:200302:fhigljdnohbmleilklpi Applying... Sorry for this - mysql general list is high-traffic list and sometimes mail can slip unnoticed. For patches it's better use internals@ or bugs@ (if it's a bugfix) lists that have much lower traffic. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load Balanced Mysql Cluster
Hi, We had a similar problem and we have taken a different approach: Instead of having dedicated slave servers, we configure a slave mysql server on each webserver. Each webserver connects only to 'localhost' slave mysql server. Each slave server only deals with local connections (200 in your case) and it doesn't increase when we add more webservers. With this setup, you only deal with load-balancing at HTTP level which is much easier than SQL load-balancing. Hope this helps Joseph Bueno Jared Saul wrote: I have a database heavy site that utilizes a master mysql server which processes all updates and replicates to 4 slave servers that handle the queries. Right now I have a simple perl randomized selection that determines which of the four database slaves will receive any given query. This works but is very limited and not fault tolerant at all. In particular I have periodic (and unexplained) server load spikes and continued requests to a spiking server results in a rapid site wide downward spiral of hung connections another topic for another post. But even a simple hardware failure would not be tolerated in the current scheme. Anyway, my hope was to utilize one of our Alteon Ace Director 3 load balancers (AD3) to perform intelligent database load balancing. I have found that this doesn't work in an obvious manner with the following complications: 1) There are no suitable health checks and tcp health checks result in many half-open connections (seem to hang on authentication) that ultimately max out the connections on the DB servers. ICMP work better but also causes sporadic hung connections and other bizzarre behaviors. I have considered scripted http health checks against a web page that ran a script that monitored the database but that seems convoluted. 2) The use of persistent database connections (via. mod_perl) becomes a nightmare with each server receiving (total_apache_processess)x(number apache servers) persistant connections. Such that, with 3 apache servers running 100-200 processes each all of the 4 mysql servers maintains 300-600 persistant connections. This quickly gets out of control. Note, this issue exists regardless of load balancing methodology. Well, I've contemplated this situation for months and it's really driving me rather nuts. Any help is much appreciated. Thanks- Jared - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need help about SQL sintax
This should do what you want: UPDATE Table2, Table1 SET Table2.DataDestination=Table1.DataSource WHERE Table1.col1=Table2.col1 AND Table1.col2=Table2.col2 AND Table1.col3=Table2.col3; --- PandaCoop-Krasimir_Slaveykov [EMAIL PROTECTED] wrote: Hello mysql, I have 2 tables : Table1: col1 col2 col3 DataSource Table2: col1 col2 col3 DataDestination How to update Table2.DataDestination with value of Table1.DataSourse where table1.col1=table2.col2 and table1.col2=table2.col2 and table1.col3=table2.col3 -- Best regards, PandaCoop-Krasimir_Slaveykov mailto:[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 __ Post your free ad now! http://personals.yahoo.ca - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: latin1_de with index
On Tue, 4 Mar 2003, Fabian Schmidt wrote:
Description:
A select returns uncomplete results, when querying an indexed
varchar row with latin1_de character set and strings containing umlauts
or 'ß'.
How-To-Repeat:
start MySQL 4.0 with character set latin1_de.
create table test (word varchar(255) not null default '', index(word));
insert into test values ('ss'),('ß'),('ä'),('ae');
select * from test where word='ss';
+--+
| ss |
| ß|
+--+
- looks fine.
select * from test where word='ß';
+--+
| ss |
+--+
I would expect that it returns 'ß' as a result as well.
select * from test where word='ä';
+--+
| ae |
+--+
- is what you least expect.
I can reproduce this on 4.0.10-gamma-debug-log
To get even more confused, the result is changing:
select * from test where word like 'ae';
-- ae
select * from test where word='ä';
-- ae, ä
select * from test where word='ae';
-- ae, ä
select * from test where word='ä';
-- ae
I could not reproduce this behaviour on 4.0.10; got both results;
however, when ä and ae are equivalent, is this really a problem? Some
texts do substitute ae for ä and I want to have a hit searching for 'ä'.
but look at this:
mysql select * from test where word like 'ae';
Empty set (0.00 sec)
Some words like Tetraeder have the combination 'ae' and should be found
looking for LIKE '%ae%'; surprisingly this works:
mysql insert into test values ('Tetraeder');
Query OK, 1 row affected (0.00 sec)
mysql select * from test where word like '%ae%';
*** 1. row ***
word: ae
*** 2. row ***
word: Tetraeder
2 rows in set (0.00 sec)
Whenever there is a joker it works:
mysql select * from test where word like '%ae';
*** 1. row ***
word: ae
1 row in set (0.00 sec)
mysql select * from test where word like 'ae%';
*** 1. row ***
word: ae
1 row in set (0.00 sec)
but this does not:
mysql select * from test where word like 'ae';
Empty set (0.00 sec)
Regards,
Thomas Spahni
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fwd: how to handle a 10GB index-file?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, ...I have a quite critical problem and didn't find anything on mysql.com or via google :-( Maybe someone here has some hints for me? The data file .MYD of a certain table has a size of 7.3 Gig and the corresponding index file .MYI has a size of 10Gig. When deleting records from this table - will the index file automaticly be stripped down? IF not - is there a way to trim the index-file to use less space?!? BTW: how is the maximum size of a index file determined? yesterday, one of our databases refused to insert more data into the table, because there was no space left in the index-file (17GB) - but the filesystem had plenty of space left (SOLARS 8). Is the any Documentation regarding this issue? thanks, Andy - -- [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+ZgGpRrny/uOBVy4RAsPZAJ9XI/OrOuhfwMGEnBpbvBkKHyTtngCghoqJ KRQc0/zSIAnQQkAG+V0zOns= =oi3/ -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
Can't use more than one alias with temp-tables
sorry for only half-using mysqlbug, Martin Description: When using more than one alias on a temporary table I get the error message: ERROR 1137: Can't reopen table: 'first_alias_name' The only thing I found about this error is a fix in version 3.23.36 which seems to cope with the topic... The same error seems to occur in older versions too... I verified it with '4.0.3-beta'. How-To-Repeat: CREATE TEMPORARY TABLE tmp ( id INT NOT NULL ); SELECT T1.id FROM tmp T1, tmp T2; - ERROR 1137: Can't reopen table: 'T1' Fix: ??? Submitter-Id: Originator: Martin Correll Organization: MySQL support: none Synopsis: Can't use more than one alias with temp-tables Severity: non-critical Priority: medium Category: mysql Class: sw-bug or doc-bug Release: mysql-4.0.11-gamma-max (Official MySQL-max binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: debian linux -- +++ GMX - Mail, Messaging more http://www.gmx.net +++ Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Double Select?!
Hiya, Any help would be appreciated! Due to various hardware requirements, on our exiting server space is a problem not speed. I store a lot of details of members, and have worked out that I will save space by storing lastname's, firstname's etc in a seperate table. e.g. lastname table is id and lastname field. firstname table is id and firstname field My members table then has a field called lastnameid, firstnameid etc. So I can do: SELECT firstname, lastname FROM members, firstname, lastname WHERE firstnameid=firstname.id AND lastnameid=lastname.id So as an extension I am whating to include middle names, so in the members table I have a collum called middlenameid, how do I do this with a SELECT? Thanks, Steve SQL cos I have to include it! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to tell when mysql has frozen
Is your temp directory running out of disk space? Seth Brundle wrote: I attempted to optimize a table last night, which usually takes about 5 minutes but I expected to take longer as I had done a slew of deletes. Well, it took over an hour, and from the last modified dates on the table and temp files, it looked like they hadnt been updated in 10-15 minutes. Eventually I gave up and disconnected, as I was on a laptop and had to disconnect from ssh. Today I found the tables corrupted, and am attempting a repair table, but I am in the same situation - the command is still running after 20 minutes and the table files have not been modified for a while (th eindex file's modified date is being updated but the filesize has not changed). mysqld is at 85% CPU in top. How can you tell when mysql is just taking a lot of time or has croaked/frozen/is stuck in a bad loop? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Table Design
Band members should probably be in a separate table. One person may be a member of several bands, and the membership in a band changes over time. This will make modeling difficult. [EMAIL PROTECTED] wrote: Hello, I`m doing a database in MySQL to catalog cds, and i`m not sure if my table structure is the best way to do it: Artist Table Artist_Id int unsigned not null auto_increment primary key Name char(120) // Artist or Band Name Country char(30) // Artist Or Band Country Members char(255) // Band Members Biography text // Artist or Band History CDS Table CD_Id int unsigned not null auto_increment primary key Artist_Id int unsigned not null Title char(150) // CD Title Style char(150) // CD Style Year year(4) // CD Release Year Song Table CD_Id int unsigned not null Position tinyint // Song position on cd Name char ( 255 ) // Song Name length char(10) // Song Length lyric text // Song Lyric Is it the best structure and relationship for my database? Some exemples If i try to find the a artist name i can search for artist name in artist table, but if i want to know what cds a artist got? SELECT * from CDS WHERE Artist_Id = SOMENUMBER but if i don`t know the artist id then i need to do a search for the id first? So is not better use artist name to my primary key? And in same way use CD title as primary key in cd table and in song table? But it's not a waste of space?? I`m a bit new in database so sometimes i get a bit confude about structure and my frontend program will be a java gui to serch and insert things in database so i`m trying to make easy as possible. Thank You. -- Use o melhor sistema de busca da Internet Radar UOL - http://www.radaruol.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: replace string
I want to replace part of a string with something else. I have a table called favorites. This can be done by someone who logs in on the browser with email and password. I'm using php in a web page. Do I use the following syntax: $query_del = UPDATE favorites REPLACE(favorites,'BitOfStringToReplace','NewBitOfString') WHERE email='$email' AND password = '$pw'; It's the use of UPDATE ... REPLACE together that I'm not sure of. UPDATE normally goes with SET. Thanks, John http://www.cantor.com CONFIDENTIAL: This e-mail, including its contents and attachments, if any, are confidential. If you are not the named recipient please notify the sender and immediately delete it. You may not disseminate, distribute, or forward this e-mail message or disclose its contents to anybody else. Copyright and any other intellectual property rights in its contents are the sole property of Cantor Fitzgerald. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Although we routinely screen for viruses, addressees should check this e-mail and any attachments for viruses. We make no representation or warranty as to the absence of viruses in this e-mail or any attachments. Please note that to ensure regulatory compliance and for the protection of our customers and business, we may monitor and read e-mails sent to and from our server(s). For further important information, please read the Important Legal Information and Legal Statement at http://www.cantor.com/legal_information.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: replace string
i would use following $query_del = UPDATE favorites set favorites='.REPLACE(favorites,'BitOfStringToReplace','NewBitOfString').' WHERE email='$email' AND password = '$pw'; eva -Original Message- From: Dillon, John [mailto:[EMAIL PROTECTED] Sent: 5. marca 2003 15:53 To: '[EMAIL PROTECTED]' Subject: RE: replace string I want to replace part of a string with something else. I have a table called favorites. This can be done by someone who logs in on the browser with email and password. I'm using php in a web page. Do I use the following syntax: $query_del = UPDATE favorites REPLACE(favorites,'BitOfStringToReplace','NewBitOfString') WHERE email='$email' AND password = '$pw'; It's the use of UPDATE ... REPLACE together that I'm not sure of. UPDATE normally goes with SET. Thanks, John http://www.cantor.com CONFIDENTIAL: This e-mail, including its contents and attachments, if any, are confidential. If you are not the named recipient please notify the sender and immediately delete it. You may not disseminate, distribute, or forward this e-mail message or disclose its contents to anybody else. Copyright and any other intellectual property rights in its contents are the sole property of Cantor Fitzgerald. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Although we routinely screen for viruses, addressees should check this e-mail and any attachments for viruses. We make no representation or warranty as to the absence of viruses in this e-mail or any attachments. Please note that to ensure regulatory compliance and for the protection of our customers and business, we may monitor and read e-mails sent to and from our server(s). For further important information, please read the Important Legal Information and Legal Statement at http://www.cantor.com/legal_information.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Fwd: how to handle a 10GB index-file?
Two solutions to your problem: table full errors: check the 'AVG_ROW_LENGTH' and 'MAX_ROWS' option for create and alter table. You can change these on the fly using 'alter table', but it will take quite a time for a table your size (few hours-1 day depending on machine). The exact values for these parameters are not all that important. just make it large enough. Usually, you run into this limit around 4 Gig Byte, but maybe you already set these parameters and need to increase them. Check 'show table status' to verify the current value. To reduce the size of you index file: try a 'repair table'. The 'alter table' will take care of it too. You can also drop and recreate this index. Side note: look at merge tables... they look like a pain at first, but once you get used to it they are quite handy for tables your size. The data file .MYD of a certain table has a size of 7.3 Gig and the corresponding index file .MYI has a size of 10Gig. When deleting records from this table - will the index file automaticly be stripped down? IF not - is there a way to trim the index-file to use less space?!? BTW: how is the maximum size of a index file determined? yesterday, one of our databases refused to insert more data into the table, because there was no space left in the index-file (17GB) - but the filesystem had plenty of space left (SOLARS 8). Is the any Documentation regarding this issue? thanks, Andy - -- [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+ZgGpRrny/uOBVy4RAsPZAJ9XI/OrOuhfwMGEnBpbvBkKHyTtngCghoqJ KRQc0/zSIAnQQkAG+V0zOns= =oi3/ -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 -- [EMAIL PROTECTED] Collaborative Intrusion Detection join http://www.dshield.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
too many open files, error 24, but max-files is large
on mysql 4.0.10-gamma (rpm install, Redhat advanced server), I am running into 'too many open files' issues ( error 24 ). I am using a rather large merge table (30 distinct tables merged), which is likely the culprit. The error shows up as I have about a dozen of connections. I did increase the number of file handles substantionally (16384) and it looks like this limit is not reached: cat /proc/sys/fs/file-nr 5328475816384 This is a 2.4 kernel, so inodes are allocated automatically and there is no inode-max setting. 'open_files_limit' is set to 0. 'table_cache' is set to 1024 cmd line used to run mysqld: /bin/sh /usr/bin/mysqld_safe --log-slow-queries --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/x440.pid ... /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql--pid-file=/var/lib/mysql/x440.pid --skip-locking--log-slow-queries in other words: I checked ulimit... What am I missing? hardware: dual Xeon system, 8 GByte RAM -- [EMAIL PROTECTED] Collaborative Intrusion Detection join http://www.dshield.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fwd: data-retrieving udf
hi everybody, i'm trying to add a udf to mysql that itself accesses the database. anybody ever tried that? i want a function to dynamically create a database, symbolic links and tables. i thought it could be as easy as just mix up a normal udf with my table-creating thing. but when i try to create the function mysql says: errno: 2 /usr/lib/myso.so: undefined symbol _Z10mysql_initP8st_mysql (works fine if i pack it into a main-function and compile/link without the -shared option) or is it the wrong library? ( -L'/usr/lib/mysql' -lmysqlclient) or is it just evil to load a shared client into the server? :-) ...btw. does anyone have an example of a working udf written in c++? i had to write everything in c :-| nobab northern baltic blue gmbh [EMAIL PROTECTED] www.nobab.com Stammhaus: Maklerstraße 6 - 24159 Kiel Telefon 0431 33 983 0 Niederlassung West: Siemensring 27 - 47877 Willich Telefon 02154 48129 00 Büro Haiger (im Hause Pracht): Hansastr. 10 - 35708 Haiger Niederlassung Südwest (im Hause DPD): Carl-Benz-Straße 15 - 71634 Ludwigsburg Telefon 07141 300371 980 Büro Freising bei München (im Hause trans-o-flex): Adolf-Kolping-Str. 10 - 85356 Freising Zentralfax 0431 33983 20 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Count (*) function results needed in echo statement
Just wanted to let y'all know this worked awesome! Thanks so much! Renee :) -Original Message- From: Sqlcoders.com Programming Dept [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 04, 2003 11:20 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Count (*) function results needed in echo statement -Original Message- From: Stitchin' [mailto:[EMAIL PROTECTED] Subject: Count (*) function results needed in echo statement I've got the following code in my php file: /* Select all categories from designcatagories table */ $query = SELECT designcatagories.catcode, designcatagories.catdesc, count(*) FROM designs,designcatagories where designs.catcode = designcatagories.catcode GROUP BY catcode; $result = mysql_query($query) or die (Couldn't execute query.); Hi Renee, The easiest way to reference the output of mysql's functions in a meaningful way inside php would be to use 'as field_name', so for example if you changed your query to.. $query = SELECT designcatagories.catcode, designcatagories.catdesc, count(*) as thiscount FROM designs,designcatagories where designs.catcode = designcatagories.catcode GROUP BY catcode; ..you could then simply reference the result of count() as thiscount in the same way as you print catdesc, catcode et al currently. HTH, William. I tested it in phpMyAdmin and I'm getting the correct results here's my dilemma ... I want to spit out a list of my catcodes, # designs found, catdesc and I'm not sure how to reference the count(*) object??? What I have below doesn't work, it just prints the () and I can't find any reference to this in my books??? HELP!!! echo b$catcode/b/font /td td$count()/td td$catdesc/td /tr; Renee Toth Stitchin' Up A Storm www.stitchinupastorm.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fwd: data-retrieving udf
hi, There is an example (udf_example.cc) in 'sql' sub-directory of mysql sources. You can code your functions in C++ but you must declare your UDF entry points as 'extern C' Regards, Joseph Bueno Maurizio Oristanio wrote: hi everybody, i'm trying to add a udf to mysql that itself accesses the database. anybody ever tried that? i want a function to dynamically create a database, symbolic links and tables. i thought it could be as easy as just mix up a normal udf with my table-creating thing. but when i try to create the function mysql says: errno: 2 /usr/lib/myso.so: undefined symbol _Z10mysql_initP8st_mysql (works fine if i pack it into a main-function and compile/link without the -shared option) or is it the wrong library? ( -L'/usr/lib/mysql' -lmysqlclient) or is it just evil to load a shared client into the server? :-) ...btw. does anyone have an example of a working udf written in c++? i had to write everything in c :-| nobab northern baltic blue gmbh [EMAIL PROTECTED] www.nobab.com Stammhaus: Maklerstraße 6 - 24159 Kiel Telefon 0431 33 983 0 Niederlassung West: Siemensring 27 - 47877 Willich Telefon 02154 48129 00 Büro Haiger (im Hause Pracht): Hansastr. 10 - 35708 Haiger Niederlassung Südwest (im Hause DPD): Carl-Benz-Straße 15 - 71634 Ludwigsburg Telefon 07141 300371 980 Büro Freising bei München (im Hause trans-o-flex): Adolf-Kolping-Str. 10 - 85356 Freising Zentralfax 0431 33983 20 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Can't use more than one alias with temp-tables
On Wednesday 05 March 2003 16:08, correll at gmx dot de wrote: Description: When using more than one alias on a temporary table I get the error message: ERROR 1137: Can't reopen table: 'first_alias_name' The only thing I found about this error is a fix in version 3.23.36 which seems to cope with the topic... The same error seems to occur in older versions too... I verified it with '4.0.3-beta'. How-To-Repeat: CREATE TEMPORARY TABLE tmp ( id INT NOT NULL ); SELECT T1.id FROM tmp T1, tmp T2; - ERROR 1137: Can't reopen table: 'T1' It's not a bug. You can't use temporary table twice in the same query and it's described in the manual at: http://www.mysql.com/doc/en/Temporary_table_problems.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: activate Innodb on mysql 3.23.49-3 (linux) ?
On Tuesday 04 March 2003 22:38, François Roussel wrote: I want to add transaction support to an existing database running on mysql 3.23.49-3 distributed with Red Hat 7.3. After browsing through the Mysql and the Innodb documentation, it appears that I have the following options: 1) Configure mysql 3.23.49-3 to support innodb tables. Since the have_innodb variable displays as disabled, not no, it should be possible in theory. After some experimenting, I did succeed in creating and using an innodb table but at the expense of the server sometimes refusing to stop. I had just added to my.cnf: innodb_data_file_path=ibdata1:30M. If you use 3.23.xx you should also install -MAX package or configure with --with-innodb option. 2) Install the server from the Max package and configure to activate Innodb tables. This appears to be the recommended option in the documentation. Since the Max RPM for 3.23.49-3 is nowhere to be found, this implies installing the latest binaries (currently 3.23.55). I experimented with this option and after the installation, I had to create a my.cnf file which 3.23.55 does not install by default. I succeeded in creating a innodb table. So far so good. my.cnf is not created by default, you should create it by yourself. 3.23.49 is a bit outdated and usage 3.23.55 is more recommended. 3)Upgrade to 4.011 which has innodb in the standard binaries. This is even easier as the default configuration enables innodb tables, but this version is still gamma. Can option 1) work ? nope. If not, should I go with option 2) or option 3) ? Up to you :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: re: Simple subtraction problem
On Tuesday 04 March 2003 19:35, Jamie Jennings wrote: Yes that must be it. Both columns are unsigned. Is there any way to accomplish this with one query with unsigned columns? Take a look at cast functions: http://www.mysql.com/doc/en/Cast_Functions.html Otherwise I will have to fetch each column seperately (two queries) and then do the subtraction outside MySQL (in my C code). -- 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
HAVING vs. WHERE
In the MySQL reference, it warns against using HAVING for items that should be in a WHERE clause. I'm not sure what items should be in a WHERE clause. My exact problem is I want to select some records that have a dotted IP address as one of the fields. And I want to filter out the multicast addresses, which are the addresses that begin with the numbers in the range of 224. thru 239. This does it: SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,.)) 224 OR left(inetAdr,instr(inetAdr,.)) 239 and this works as well: SELECT *,left(inetAdr,instr(inetAdr,.)) as ia FROM Client HAVING ia 224 OR ia 239 and it is a little cleaner, although as I'm going to be doing this in a PHP script, cleanliness isn't all that important. So I guess I have 2 questions: 1] Which should I use? 2] Is this the easiest way to check for the multicast address? -- Jonathan Arnold (mailto:[EMAIL PROTECTED]) Amazing Developments http://www.buddydog.org It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so. Mark Twain - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HAVING vs. WHERE
go to
http://www.mysql.com/doc/en/Miscellaneous_functions.html
and take a look at
inet_ntoa and inet_aton
you may store IP addresses as integers and the query will be
SELECT inet_aton(inetAdr), * FROM Client WHERE inetAdr
inet_aton('240.0.0.0') OR inetAdrinet_aton('239.255.255.255')
the inet_aton will return the IP dotted.
As well, you do not need to use HAVING here.
Hope this helps.
==
Stanimir Dzharkalov
Developer
Internet Division
MobilTel EAD
email: [EMAIL PROTECTED]
==
- Original Message -
From: Jonathan Arnold [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, March 05, 2003 6:17 PM
Subject: HAVING vs. WHERE
In the MySQL reference, it warns against using HAVING for items
that should be in a WHERE clause. I'm not sure what items
should be in a WHERE clause.
My exact problem is I want to select some records that have a
dotted IP address as one of the fields. And I want to filter out
the multicast addresses, which are the addresses that begin with
the numbers in the range of 224. thru 239. This does it:
SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,.)) 224
OR left(inetAdr,instr(inetAdr,.)) 239
and this works as well:
SELECT *,left(inetAdr,instr(inetAdr,.)) as ia FROM Client HAVING ia
224
OR ia 239
and it is a little cleaner, although as I'm going to be doing this in a
PHP script, cleanliness isn't all that important.
So I guess I have 2 questions:
1] Which should I use?
2] Is this the easiest way to check for the multicast address?
--
Jonathan Arnold (mailto:[EMAIL PROTECTED])
Amazing Developments http://www.buddydog.org
It ain't what you don't know that gets you into trouble.
It's what you know for sure that just ain't so. Mark Twain
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HAVING vs. WHERE
At 11:17 -0500 3/5/03, Jonathan Arnold wrote: In the MySQL reference, it warns against using HAVING for items that should be in a WHERE clause. I'm not sure what items should be in a WHERE clause. If a test can be in either clause, put it in the WHERE clause. The principle is that the sooner you can eliminate rows from consideration for further processing, the better. WHERE is evaluated to determine which rows to select, HAVING is applied to the selected rows after that. This mostly makes a difference when using HAVING with GROUP BY. If you can use WHERE to prevent certain rows even from entering into the grouping calculations, it saves work compared to eliminating them later in the HAVING. My exact problem is I want to select some records that have a dotted IP address as one of the fields. And I want to filter out the multicast addresses, which are the addresses that begin with the numbers in the range of 224. thru 239. This does it: SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,.)) 224 OR left(inetAdr,instr(inetAdr,.)) 239 and this works as well: SELECT *,left(inetAdr,instr(inetAdr,.)) as ia FROM Client HAVING ia 224 OR ia 239 and it is a little cleaner, although as I'm going to be doing this in a PHP script, cleanliness isn't all that important. So I guess I have 2 questions: 1] Which should I use? 2] Is this the easiest way to check for the multicast address? -- Jonathan Arnold (mailto:[EMAIL PROTECTED]) Amazing Developments http://www.buddydog.org It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so. Mark Twain - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HAVING vs. WHERE
HAVING is for qualifying result rows based on the value of aggregate functions, WHERE is for qualifying result rows based on individual (column) values. So in you case you should use WHERE. Although useful in the right situation, HAVING is used much less than a WHERE clause. One example of how to use HAVING would be to show values that have duplicate entries in the database: SELECT FirstName, LastName, Count(*) FROM MyTable GROUP BY FirstName, LastName HAVING Count(*) 1 HTH, Tore. - Original Message - From: Jonathan Arnold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 11:17 AM Subject: HAVING vs. WHERE In the MySQL reference, it warns against using HAVING for items that should be in a WHERE clause. I'm not sure what items should be in a WHERE clause. My exact problem is I want to select some records that have a dotted IP address as one of the fields. And I want to filter out the multicast addresses, which are the addresses that begin with the numbers in the range of 224. thru 239. This does it: SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,.)) 224 OR left(inetAdr,instr(inetAdr,.)) 239 and this works as well: SELECT *,left(inetAdr,instr(inetAdr,.)) as ia FROM Client HAVING ia 224 OR ia 239 and it is a little cleaner, although as I'm going to be doing this in a PHP script, cleanliness isn't all that important. So I guess I have 2 questions: 1] Which should I use? 2] Is this the easiest way to check for the multicast address? -- Jonathan Arnold (mailto:[EMAIL PROTECTED]) Amazing Developments http://www.buddydog.org It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so. Mark Twain - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HAVING vs. WHERE
On Wed 2003-03-05 at 11:17:37 -0500, [EMAIL PROTECTED] wrote: In the MySQL reference, it warns against using HAVING for items that should be in a WHERE clause. I'm not sure what items should be in a WHERE clause. Everything except stuff that only works when it's in the HAVING clause. The HAVING clause is applied only after all rows matching the WHERE clause have been fetched, i.e. if you put a condition from the WHERE clause into the HAVING clause, you take away all possibilities for optimizing. [...] This does it: SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,.)) 224 OR left(inetAdr,instr(inetAdr,.)) 239 and this works as well: SELECT *,left(inetAdr,instr(inetAdr,.)) as ia FROM Client HAVING ia 224 OR ia 239 In this case you won't notice a big difference, because the first query uses an expression on inetAdr and therefore cannot use indexes either. Try hard to have a pure column on one side of the operator, like this: SELECT * FROM Client WHERE inetAdr NOT BETWEEN 224. AND 239. which will happily use an index on inetAdr. and it is a little cleaner, although as I'm going to be doing this in a PHP script, cleanliness isn't all that important. So I guess I have 2 questions: 1] Which should I use? My version. ;-) 2] Is this the easiest way to check for the multicast address? You may want to have a look at the functions INET_NTOA() and INET_ATON(). HTH, Benjamin. -- [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: HAVING vs. WHERE
In the last episode (Mar 05), Jonathan Arnold said: In the MySQL reference, it warns against using HAVING for items that should be in a WHERE clause. I'm not sure what items should be in a WHERE clause. The WHERE clause is used to restrict records, and is also used by the query optimizer to determine which indexes and tables to use. HAVING is a filter on the final resultset, and is applied after ORDER BY and GROUP BY, so mysql cannot use it to optimize the query. My exact problem is I want to select some records that have a dotted IP address as one of the fields. And I want to filter out the multicast addresses, which are the addresses that begin with the numbers in the range of 224. thru 239. This does it: SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,.)) 224 OR left(inetAdr,instr(inetAdr,.)) 239 and this works as well: SELECT *,left(inetAdr,instr(inetAdr,.)) as ia FROM Client HAVING ia 224 OR ia 239 and it is a little cleaner, although as I'm going to be doing this in a PHP script, cleanliness isn't all that important. So I guess I have 2 questions: 1] Which should I use? The first way will be slightly faster, but not by much since you won't be able to use an index (due to your left/instr functions). 2] Is this the easiest way to check for the multicast address? The fastest way would be to store the IP as an unsigned integer, put an index on inetAddr, and SELECT * from Client WHERE inetAddr NOT BETWEEN INET_ATON(224.0.0.0) AND INET_ATON(239.255.255.255) Also saves you 12 bytes per record: 16 bytes to store a CHAR(15) vs 4 bytes for an INT. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie Install Questions: RH Linux 7.1
I'm having trouble getting MySQL running on a RedHat Linux 7.1 machine. The log file complains that host.MYD is missing; I don't see mention of this file in the documentation, and it's not on my computer anywhere. Also, the mysql.sock file is not present, and I don't know how to create it -- I assume it'll take more than a 'touch'! Bruce Feist - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
viewing uncommited transactions (InnoDB)
Does anyone know how to view uncommited transactions for a session? This is more of a problem when using MySQL Manager when it asks whether or not to commit the transaction list. Is there a definitive way to know what queries have yet to be committed/rolled back? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 hangs on multi table update on innodb table.
Hi, I havent received any response on this so i'll send this again. I did check again with 4.0.11-gamma and the problem is still there. Regards, Scott Version: Mysql 4.0.10-gamma Description: Mysql hangs and possibly can not recover from a query on an innodb table. How to Repeat : drop table if exists parent; drop table if exists child; CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT PRIMARY KEY, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=INNODB; insert into parent set id=1; insert into child set id=1, parent_id=1; update parent,child set parent.id=parent.id+1, child.parent_id=parent.id+1; Thank you for your time. Scott Wong, Meiko America, INC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Simple SELECT question
I need a little help querying 2 tables in MySQL; Table User User_Id // Id = 2 User_Name // John Table History User_Id // 2 User_History // John has born in 58 SELECT User_History FROM History WHERE User_Id = 2; // return John has born in 58 But what my SELECT should look if i don't know the user id and want search the User_History by the User_Name? SELECT User_History FROM History WHERE User_Id = ( SOMEWAY TO DATABASE FIND JOHN ID BY JOHN NAME ) -- Use o melhor sistema de busca da Internet Radar UOL - http://www.radaruol.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Count (*) function results needed in echo statement
Renne I did a similar thing with asp, my query was like SELECT COUNT(*) AS countresult, uniqueref FROM global WHERE PROPOSEDRELEASE = 2002 and SURNAME = ' globsurname ' group by uniqueref Im my asp code I referenced countresult Hope this helps John B -Original Message- From: Stitchin' [mailto:[EMAIL PROTECTED] Sent: 05 March 2003 04:08 To: [EMAIL PROTECTED] Subject: Count (*) function results needed in echo statement I've got the following code in my php file: /* Select all categories from designcatagories table */ $query = SELECT designcatagories.catcode, designcatagories.catdesc, count(*) FROM designs,designcatagories where designs.catcode = designcatagories.catcode GROUP BY catcode; $result = mysql_query($query) or die (Couldn't execute query.); I tested it in phpMyAdmin and I'm getting the correct results here's my dilemma ... I want to spit out a list of my catcodes, # designs found, catdesc and I'm not sure how to reference the count(*) object??? What I have below doesn't work, it just prints the () and I can't find any reference to this in my books??? HELP!!! echo b$catcode/b/font /td td$count()/td td$catdesc/td /tr; Renee Toth Stitchin' Up A Storm www.stitchinupastorm.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql hangs on multi table update on innodb table.
Scott Wong writes: Hi, I havent received any response on this so i'll send this again. I did check again with 4.0.11-gamma and the problem is still there. Regards, Scott Hi! This will require a major re-write in multi table update's and will not come up very soon. But should come up with the end of this month. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com Join MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Simple SELECT question
Ok, lessee... SELECT user.user_id, user_name, user_history FROM user, history WHERE user_history LIKE %58% AND user.user_id = history.user_id; That should do what you need, lemme know. Mike Hillyer http://www.dynamergy.com/mike -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 10:41 AM To: [EMAIL PROTECTED] Subject: Simple SELECT question I need a little help querying 2 tables in MySQL; Table User User_Id // Id = 2 User_Name // John Table History User_Id // 2 User_History // John has born in 58 SELECT User_History FROM History WHERE User_Id = 2; // return John has born in 58 But what my SELECT should look if i don't know the user id and want search the User_History by the User_Name? SELECT User_History FROM History WHERE User_Id = ( SOMEWAY TO DATABASE FIND JOHN ID BY JOHN NAME ) -- Use o melhor sistema de busca da Internet Radar UOL - http://www.radaruol.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Simple SELECT question
[EMAIL PROTECTED] wrote: I need a little help querying 2 tables in MySQL; Table User User_Id // Id = 2 User_Name // John Table History User_Id // 2 User_History // John has born in 58 SELECT User_History FROM History WHERE User_Id = 2; // return John has born in 58 But what my SELECT should look if i don't know the user id and want search the User_History by the User_Name? SELECT User_History FROM History WHERE User_Id = ( SOMEWAY TO DATABASE FIND JOHN ID BY JOHN NAME ) SELECT User_History FROM User u INNER JOIN Table_History h ON u.User_ID = h.User_ID WHERE u.User_Name = 'John' ought to do the trick for you. Bruce Feist, also born in 58 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Simple SELECT question
--- [EMAIL PROTECTED] wrote: I need a little help querying 2 tables in MySQL; Table User User_Id // Id = 2 User_Name // John Table History User_Id // 2 User_History // John has born in 58 SELECT User_History FROM History WHERE User_Id = 2; // return John has born in 58 But what my SELECT should look if i don't know the user id and want search the User_History by the User_Name? SELECT User_History FROM History WHERE User_Id = ( SOMEWAY TO DATABASE FIND JOHN ID BY JOHN NAME ) I am no MySQL expert by any means, but I believe that a sub select would handle it. However, last I knew MySQL didn't support them. So I believe the other solution would be to do a JOIN, I thought I remembered people here saying that JOINs are the sub-select work around. Seeing as how I do not know them at all I can't offer an example... Sorry If this is incorrect someone please correct me. Thanx, Ryan __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Get warning info from mysql_info()
I'm trying to use a INSERT INTO table1 (column1, column2, column3) SELECT 'value', 'value2', table2.value3 FROM table2 WHERE table2.value4='name'; This query fails from C, but if I copy and paste it exactly to the mysql client, it works. If I run mysql_info(), it tells me: Records: 0 Duplicates: 0 Warnings: 1 But I have no idea what the warning is. Is there any way to find out? mysql_error returns '\0' so it's no help. Thanks! __ Post your free ad now! http://personals.yahoo.ca - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: OS X codewarrior libraries
Hi Lenz - thanks for your response. Please define not compatible. What exactly happens when you try to use them? Is there an error? Which version of MySQL/Code Warrior do you use? I am using the most up-to-date version of CodeWarrior, v8.3, and I have tried with several different versions of MySQL - 3.23.55, standard 4.0.9, and standard 4.0.10. CodeWarrior will not let me import the library files into my project. In the compiler's documentation I read that on Mac OS a library has a file type of 'MPLF' and a creator of 'CWIE'. The distributed libraries have no creator or file type, but this makes sense because they are not Mac OS files. If I manually munge the file's type and creator code to these values CodeWarrior will successfully import the file into the project, but compilation results in a Illegal object file data error. So I am assuming that there is some deeper level of incompatibility between the format of the library files and what CodeWarrior expects to use. So what are my options? If nobody has a CodeWarrior version of the header files available, I am assuming I can attempt to compile one myself. I downloaded a 4.0.10 gamma distribution which appears to have all the necessary source code. I am a relative newbie to the world of unix-based programming - I've never even used the make utility before. Am I setting myself up for a world of pain? thanks again for your help. Ben - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Linux Filesystem Type and Performance
If you have that many files in a directory, I would try a filesystem that indexes directories. Reiserfs does, and there's a patch somewhere for ext2 (and probably for ext3). I don't know about the other filesystems. Otherwise the application has to do a linear search through the directory every time it operates on a filename. As to why you're hitting a wall, perhaps you're blowing through a cache in MySQL or the kernel. Check number of allowed open files (ulimit -n), and whatever tweaks there are in the kernel (in /proc probably) for size of directory caches (can't help you there but I'm sure someone can). --Pete On Tue, Mar 04, 2003 at 09:04:20PM -0500, Gary Huntress wrote: Greetings, I've been running a public MySQL server for a couple of years that now supports several thousand users. I used to run this on a lowly PII-350 and it ran quite fine with CPU loads usually hanging well under 0.20. The only hitch was the time necessary to connect seemed to jump up to a few seconds when the number of databases exceeded 2000 or so. It appeared to not be linear, performance was good below 2000 databases and the connection time went to a couple of seconds (as measured by a trivial php page that makes a connection). I suspected that the connection speed was an ext2 filesystem bottleneck when it had to deal with the large number of files in the mysql data directory. I modernised a few months ago to an athlon 1.3GHz with the same ram and a faster hard drive. I also upgraded to Linux 7.3 using an ext3 filesystem. I thought that ext3 might handle a large number of files better, and I also thought that the faster box and newer kernel would allow me to have more databases on one server without suffering the same connection speed penalty. However, I seem to have hit this same wall at roughly the same number of DBs (closer to 3000 this time) To summarize, it appears to me that there is a reasonable upper bound on the number of databases without suffering connection performance, probably limited by the filesystem. My questions are 1) Are these observations expected? (I was a bit disappointed that the new box didn't perform better...the cpu load is usually idle) 2) Is there a way to mitigate this at the linux admin level? (kernel parameters? ext3 parameters? change to reiserfs? 3) Finally, are there MySQL parameters that can be tuned to enhance performance for a large number of databases (note, not concurrent users...that is a small number) Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Replication REQUIRE SSL
Hi, I'm running 4.0.11a-gamma. I have replication working and I have SSL working. I can't seem to get them to work together. Based on mysql.com's documentation, it seems that this version of MySQL does in fact support secure replication. Unfortunately, when I add REQUIRE SSL to the slave user's GRANT string, the slave can no longer connect. Any idea why this is? I have master-ssl as an option in my.cnf, and I have master-ssl-cert and master-ssl-key setup properly. Thanks in advance for any help, Russell - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Replication REQUIRE SSL
At 15:48 -0500 3/5/03, Russell D. Weiss wrote: Hi, I'm running 4.0.11a-gamma. I have replication working and I have SSL working. I can't seem to get them to work together. Based on mysql.com's documentation, it seems that this version of MySQL does in fact support secure replication. What documentation is that? The --master-ssl-xxx options are there, but they're no-ops. Replication won't work over secure connections. Unfortunately, when I add REQUIRE SSL to the slave user's GRANT string, the slave can no longer connect. Any idea why this is? I have master-ssl as an option in my.cnf, and I have master-ssl-cert and master-ssl-key setup properly. Thanks in advance for any help, Russell - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Temporary Tables
Thanks! Mamatha From: Paul DuBois [EMAIL PROTECTED] To: Mamatha Balasubramanian [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: Re: Temporary Tables Date: Tue, 4 Mar 2003 17:31:36 -0600 At 23:19 + 3/4/03, Mamatha Balasubramanian wrote: Thank you once again. I have a web-interface that does search on a given text and I would have a script that creates a temporary table. So according to you, in my script, I just to need create a temporary table and not have to worry about another client using the same web interface (and thereby using the same program). Can you please elaborate a little more on this? Sure. You are incorrect. :-) That is, you're making an assumption that cannot necessarily be made. If you can guarantee that the web script will establish a new connection, and the connection will terminate when the script ends, you can indeed do what you describe above. But you *cannot* do that if you're running your script in an environment that uses persistent connections that may be used by successive instances of the script. PHP persistent connections fall into this class, for example. Several requests might be served by the same instance of the web server process, and you don't know that one request won't be getting the connection used by a previous request. In that case, the connection won't have closed, and the TEMPORARY table won't have disappeared. You can guard against this by issuing this query before creating the TEMPORARY table: DROP TABLE IF EXISTS tbl_name Thanks, Mamatha From: Paul DuBois [EMAIL PROTECTED] To: Mamatha Balasubramanian [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: Re: Temporary Tables Date: Tue, 4 Mar 2003 17:06:30 -0600 At 23:00 + 3/4/03, Mamatha Balasubramanian wrote: Hi, I would like to know how MySQL handles multiple temporary tables? 1. Can multiple temporary tables be created at the same time? 2. If so, how does MySQL differentiate them - do we need to explicitly give them different names inorder to identify them or does MySQL provide a timestamp (or use some other means) to identify the tables? You can create multiple temporary tables, but they must have different names. A TEMPORARY table can have the same name as a non-TEMPORARY table. The non-TEMPORARY table is hidden to the client that creates the TEMPORARY table as long as the TEMPORARY table exists. A second TEMPORARY table with the same name cannot be created. This is on a connection-specific basis. Two clients each can create a TEMPORARY table with the same name. Only the table created by a given client is visible to that client. I use MySQL 4.0.7 on Red Hat. Thanks, Mamatha _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Replication REQUIRE SSL
Oops, I guess I assumed that support for it was there since options are documented, and it even says: Available after 4.0.0. Turn SSL on for replication. Be warned that is this is a relatively new feature. Example: master-ssl Instead, I guess it should say be warned, this is just a stub and doesn't even work yet! I guess I'll be doing replication over an SSH tunnel. Anyone seen any caveats with doing it that way? Thanks, Russ - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Russell D. Weiss [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 4:34 PM Subject: Re: Replication REQUIRE SSL At 15:48 -0500 3/5/03, Russell D. Weiss wrote: Hi, I'm running 4.0.11a-gamma. I have replication working and I have SSL working. I can't seem to get them to work together. Based on mysql.com's documentation, it seems that this version of MySQL does in fact support secure replication. What documentation is that? The --master-ssl-xxx options are there, but they're no-ops. Replication won't work over secure connections. Unfortunately, when I add REQUIRE SSL to the slave user's GRANT string, the slave can no longer connect. Any idea why this is? I have master-ssl as an option in my.cnf, and I have master-ssl-cert and master-ssl-key setup properly. Thanks in advance for any help, Russell - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Replication REQUIRE SSL
Oops, I guess I assumed that support for it was there since options are documented, and it even says: Available after 4.0.0. Turn SSL on for replication. Be warned that is this is a relatively new feature. Example: master-ssl Instead, I guess it should say be warned, this is just a stub and doesn't even work yet! I guess I'll be doing replication over an SSH tunnel. Anyone seen any caveats with doing it that way? I believe that should work. Thanks, Russ 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
mysql scripts
Is there a webpage or something I can read about creating a script that can be ran from mysql prompt with the ./ . My script doesn't seem to work, even though it looks like legal sql to me. I am running this under win98. Terry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 scripts
At 16:16 -0600 3/5/03, Terry Cheryl Haimann wrote: Is there a webpage or something I can read about creating a script that can be ran from mysql prompt with the ./ . My script doesn't seem to work, even though it looks like legal sql to me. I am running this under win98. The command is \. and not ./ -- perhaps that's the problem. It'd help if you indicated exactly what you type, and what error messages (if any) result. Terry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Replication REQUIRE SSL
On Wed, Mar 05, 2003 at 05:12:13PM -0500, Russell D. Weiss wrote: I guess I'll be doing replication over an SSH tunnel. Anyone seen any caveats with doing it that way? That will work just fine. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 30 days, processed 938,997,078 queries (356/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
Syntax confusion
Hi, perform the following query
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
and get
'TSV12345-1','2003-02-25 00:00:00'
'TSV12345-2','2003-02-25 00:00:00'
'TSV14818-3','2003-02-19 00:00:00'
'TSV14836-00015','2003-02-27 00:00:00'
'TSV14841-9','2003-02-28 00:00:00'
which is OK. When I only want data from 2/25/2003 and query with
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
AND BA_DATE = '2003-25-2'
I get 0 rows returned
The book hinted at using DATE_FORMAT which I did
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
AND BA_DATE = DATE_FORMAT('2003-25-2', '%y-%d-%m')
But still 0 rows... D'OH... What am I missing here? Thanks for any
help... I would hate to have to tell the boss we're going back to
VB-ADO-MSAccess ;-)
Jeff
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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 Using SELECT INTO OUTFILE
Description: I'm using Mysql 4.0.10-gamma with query caching: set-variable = query_cache_limit=10M set-variable = query_cache_size=10M set-variable = query_cache_startup_type=1 The Command: Select * INTO OUTFILE YourFileName FIELDS TERMINATED BY ',' ENCLOSED BY '' From AnyTable; Note: rm -f YourFilename is executed before the Select. The first execution of the command writes the expected data to YourFileName. Any subsequent executions do not. This is because of query caching. If the SELECT Statement is changed to: Select * SQL_NO_CACHE INTO OUTFILE YourFileName FIELDS TERMINATED BY ',' ENCLOSED BY '' From AnyTable; or: Select * From AnyTable; Subsequent Calls Work Fine. How-To-Repeat: Execute the same command more than once. Fix: Use SQL_NO_CACHE as temporary fix Shouldn't have to.. Submitter-Id: submitter ID Originator:Nick Pasich Organization: WebSite: http://www.users.cts.com/sd/n/npasich _ _ _ __ _ __ __ _ ___(_) ___| |__ | '_ \| '_ \ / _` / __| |/ __| '_ \ | | | | |_) | (_| \__ \ | (__| | | | |_| |_| .__/ \__,_|___/_|\___|_| |_|@sd.cts.com |_| MySQL support: email support Synopsis: Problems Using SELECT INTO OUTFILE Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.10-gamma (Source distribution) Server: /usr/local/MYSQL/bin/mysqladmin Ver 8.40 Distrib 4.0.10-gamma, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.10-gamma-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /usr/local/MYSQL/mysql.sock Uptime: 24 days 3 hours 4 min 47 sec Threads: 1 Questions: 72731 Slow queries: 1213 Opens: 6681 Flush tables: 605 Open tables: 6 Queries per second avg: 0.035 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux NICK 2.4.20-pre10-ac2 #2 Sun Oct 13 12:48:34 PDT 2002 i586 unknown Architecture: i586 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/GCC/gcc-2.95.3/lib/gcc-lib/i586-pc-linux-gnulibc1/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='-O3 -march=i586' CXX='gcc' CXXFLAGS='-O3 -march=i586 -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 12 06:52 /lib/libc.so.4 - libc.so.4.7.6 -rwxr-xr-x1 root root 623620 Feb 26 1996 /lib/libc.so.4.5.26 -rwxr-xr-x1 bin bin634880 Aug 13 1996 /lib/libc.so.4.7.5 -rwxr-xr-x1 root wheel 634880 Apr 29 1996 /lib/libc.so.4.7.6 lrwxrwxrwx1 root root 14 Oct 12 06:52 /lib/libc.so.5 - libc.so.5.4.46 -rwxr-xr-x1 bin bin562683 Aug 12 1996 /lib/libc.so.5.0.9 -rwxr-xr-x1 bin bin558615 Apr 12 1997 /lib/libc.so.5.2.18 -rwxr-xr-x1 bin bin 1816182 Dec 6 1996 /lib/libc.so.5.4.17 -rwxr-xr-x1 bin bin 1760718 Aug 13 1996 /lib/libc.so.5.4.2 -rwxr-xr-x1 bin bin 1861963 Apr 15 1997 /lib/libc.so.5.4.23 -rwxr-xr-x1 bin bin 1868689 May 30 1997 /lib/libc.so.5.4.33 -rwxr-xr-x1 bin bin 1760174 Aug 18 1996 /lib/libc.so.5.4.4 -rwxr-xr-x1 bin bin 1874736 Feb 1 1998 /lib/libc.so.5.4.44 -rwxr-xr-x1 bin bin 1427975 Jun 20 1998 /lib/libc.so.5.4.46 -rw-r--r--1 bin bin 1131866 Jun 20 1998 /usr/lib/libc.a lrwxrwxrwx1 bin bin19 Sep 26 08:18 /usr/lib/libc.so - /lib/libc.so.5.4.46 Configure command: ./configure '--enable-shared' '--enable-assembler' '--without-debug' '--prefix=/usr/local/MYSQL' '--localstatedir=/usr/local/MYSQL/DB' '--with-unix-socket-path=/usr/local/MYSQL/mysql.sock' '--with-extra-charsets=none' '--without-innodb' 'CFLAGS=-O3 -march=i586' 'CXXFLAGS=-O3 -march=i586 -felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax confusion
At 8:40 +1000 3/6/03, Jeff wrote:
Hi, perform the following query
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
and get
'TSV12345-1','2003-02-25 00:00:00'
'TSV12345-2','2003-02-25 00:00:00'
'TSV14818-3','2003-02-19 00:00:00'
'TSV14836-00015','2003-02-27 00:00:00'
'TSV14841-9','2003-02-28 00:00:00'
which is OK. When I only want data from 2/25/2003 and query with
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
AND BA_DATE = '2003-25-2'
I get 0 rows returned
Not surprising, since there is no month 25! :-)
Do you mean '2003-02-25'?
By the way, do all your BA_DATE values have 00:00:00 in the time
part? If so, you may want to convert the column to DATE. If not,
then if you want to match values that occur anywhere is a given
date, a comparison like you're doing won't work.
The book hinted at using DATE_FORMAT which I did
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
AND BA_DATE = DATE_FORMAT('2003-25-2', '%y-%d-%m')
But still 0 rows... D'OH... What am I missing here? Thanks for any
help... I would hate to have to tell the boss we're going back to
VB-ADO-MSAccess ;-)
Jeff
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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 Using SELECT INTO OUTFILE
Description: I'm using Mysql 4.0.10-gamma with query caching: set-variable = query_cache_limit=10M set-variable = query_cache_size=10M set-variable = query_cache_startup_type=1 The Command: Select * INTO OUTFILE YourFileName FIELDS TERMINATED BY ',' ENCLOSED BY '' From AnyTable; Note: rm -f YourFilename is executed before the Select. The first execution of the command writes the expected data to YourFileName. Any subsequent executions do not. This is because of query caching. If the SELECT Statement is changed to: Select * SQL_NO_CACHE INTO OUTFILE YourFileName FIELDS TERMINATED BY ',' ENCLOSED BY '' From AnyTable; or: Select * From AnyTable; Subsequent Calls Work Fine. How-To-Repeat: Execute the same command more than once. Fix: Use SQL_NO_CACHE as temporary fix Shouldn't have to.. Submitter-Id: submitter ID Originator:Nick Pasich Organization: WebSite: http://www.users.cts.com/sd/n/npasich _ _ _ __ _ __ __ _ ___(_) ___| |__ | '_ \| '_ \ / _` / __| |/ __| '_ \ | | | | |_) | (_| \__ \ | (__| | | | |_| |_| .__/ \__,_|___/_|\___|_| |_|@sd.cts.com |_| MySQL support: email support Synopsis: Problems Using SELECT INTO OUTFILE Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.10-gamma (Source distribution) Server: /usr/local/MYSQL/bin/mysqladmin Ver 8.40 Distrib 4.0.10-gamma, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.10-gamma-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /usr/local/MYSQL/mysql.sock Uptime: 24 days 3 hours 4 min 47 sec Threads: 1 Questions: 72731 Slow queries: 1213 Opens: 6681 Flush tables: 605 Open tables: 6 Queries per second avg: 0.035 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux NICK 2.4.20-pre10-ac2 #2 Sun Oct 13 12:48:34 PDT 2002 i586 unknown Architecture: i586 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/GCC/gcc-2.95.3/lib/gcc-lib/i586-pc-linux-gnulibc1/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='-O3 -march=i586' CXX='gcc' CXXFLAGS='-O3 -march=i586 -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 12 06:52 /lib/libc.so.4 - libc.so.4.7.6 -rwxr-xr-x1 root root 623620 Feb 26 1996 /lib/libc.so.4.5.26 -rwxr-xr-x1 bin bin634880 Aug 13 1996 /lib/libc.so.4.7.5 -rwxr-xr-x1 root wheel 634880 Apr 29 1996 /lib/libc.so.4.7.6 lrwxrwxrwx1 root root 14 Oct 12 06:52 /lib/libc.so.5 - libc.so.5.4.46 -rwxr-xr-x1 bin bin562683 Aug 12 1996 /lib/libc.so.5.0.9 -rwxr-xr-x1 bin bin558615 Apr 12 1997 /lib/libc.so.5.2.18 -rwxr-xr-x1 bin bin 1816182 Dec 6 1996 /lib/libc.so.5.4.17 -rwxr-xr-x1 bin bin 1760718 Aug 13 1996 /lib/libc.so.5.4.2 -rwxr-xr-x1 bin bin 1861963 Apr 15 1997 /lib/libc.so.5.4.23 -rwxr-xr-x1 bin bin 1868689 May 30 1997 /lib/libc.so.5.4.33 -rwxr-xr-x1 bin bin 1760174 Aug 18 1996 /lib/libc.so.5.4.4 -rwxr-xr-x1 bin bin 1874736 Feb 1 1998 /lib/libc.so.5.4.44 -rwxr-xr-x1 bin bin 1427975 Jun 20 1998 /lib/libc.so.5.4.46 -rw-r--r--1 bin bin 1131866 Jun 20 1998 /usr/lib/libc.a lrwxrwxrwx1 bin bin19 Sep 26 08:18 /usr/lib/libc.so - /lib/libc.so.5.4.46 Configure command: ./configure '--enable-shared' '--enable-assembler' '--without-debug' '--prefix=/usr/local/MYSQL' '--localstatedir=/usr/local/MYSQL/DB' '--with-unix-socket-path=/usr/local/MYSQL/mysql.sock' '--with-extra-charsets=none' '--without-innodb' 'CFLAGS=-O3 -march=i586' 'CXXFLAGS=-O3 -march=i586 -felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Insert/UpDate dates
Working on a routine to export a Filemaker data to mySQL for php application. Using scripts I build a calculated field below: INSERT INTO abctable (JobNumber, JobTitle, AE, Client, StartDate) VALUES (9997, ' ABC ' , 'jrt' ,1234, '') or UPDATE abctable SET JobTitle='ABC',AE='jrt',Client=1234,SomethingDate='' WHERE JobNumber = 9997 and send this to mySQl via ODBC Problem. When the Filemaker date is blank the calculated field is produces SomethingDate=''. After the Insert or update the mySQL field is -00-00. And in the php form shows up as 00/00/. How can I get the date to be blank in mySQL. thanks jrt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Syntax confusion
Yes Paul, that's what I meant.
See, even confusing myself now ;-)
The field is datetime as the time is used in other functions...
Would I be better using 2 separate fields?
Jeff
- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: Jeff [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED]
Sent: Thursday, March 06, 2003 8:51 AM
Subject: Re: Syntax confusion
At 8:40 +1000 3/6/03, Jeff wrote:
Hi, perform the following query
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
and get
'TSV12345-1','2003-02-25 00:00:00'
'TSV12345-2','2003-02-25 00:00:00'
'TSV14818-3','2003-02-19 00:00:00'
'TSV14836-00015','2003-02-27 00:00:00'
'TSV14841-9','2003-02-28 00:00:00'
which is OK. When I only want data from 2/25/2003 and query with
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
AND BA_DATE = '2003-25-2'
I get 0 rows returned
Not surprising, since there is no month 25! :-)
Do you mean '2003-02-25'?
By the way, do all your BA_DATE values have 00:00:00 in the time
part? If so, you may want to convert the column to DATE. If not,
then if you want to match values that occur anywhere is a given
date, a comparison like you're doing won't work.
The book hinted at using DATE_FORMAT which I did
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
AND BA_DATE = DATE_FORMAT('2003-25-2', '%y-%d-%m')
But still 0 rows... D'OH... What am I missing here? Thanks for any
help... I would hate to have to tell the boss we're going back to
VB-ADO-MSAccess ;-)
Jeff
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: Syntax confusion
At 9:05 +1000 3/6/03, Jeff wrote:
Yes Paul, that's what I meant.
See, even confusing myself now ;-)
The field is datetime as the time is used in other functions...
Would I be better using 2 separate fields?
Not necessarily, but if you want to match all times within a given
date, you'll need to do something like:
BA_DATE = '2003-02-25 00:00:00' AND BA_DATE = '2003-02-25 23:59:59'
or
TO_DAYS(FROM_DAYS(BA_DATE)) = '2003-02-25'
The TO_DAYS(FROM_DAYS()) transform chops off the time part.
Jeff
- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: Jeff [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED]
Sent: Thursday, March 06, 2003 8:51 AM
Subject: Re: Syntax confusion
At 8:40 +1000 3/6/03, Jeff wrote:
Hi, perform the following query
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
and get
'TSV12345-1','2003-02-25 00:00:00'
'TSV12345-2','2003-02-25 00:00:00'
'TSV14818-3','2003-02-19 00:00:00'
'TSV14836-00015','2003-02-27 00:00:00'
'TSV14841-9','2003-02-28 00:00:00'
which is OK. When I only want data from 2/25/2003 and query with
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
AND BA_DATE = '2003-25-2'
I get 0 rows returned
Not surprising, since there is no month 25! :-)
Do you mean '2003-02-25'?
By the way, do all your BA_DATE values have 00:00:00 in the time
part? If so, you may want to convert the column to DATE. If not,
then if you want to match values that occur anywhere is a given
date, a comparison like you're doing won't work.
The book hinted at using DATE_FORMAT which I did
SELECT BA_ID, BA_DATE
FROM BA
WHERE SUBSTRING(BA_ID,1,3) = 'TSV'
AND BA_DATE = DATE_FORMAT('2003-25-2', '%y-%d-%m')
But still 0 rows... D'OH... What am I missing here? Thanks for any
help... I would hate to have to tell the boss we're going back to
VB-ADO-MSAccess ;-)
Jeff
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: Syntax confusion
On Wed 2003-03-05 at 17:12:23 -0600, [EMAIL PROTECTED] wrote: [...] BA_DATE = '2003-02-25 00:00:00' AND BA_DATE = '2003-02-25 23:59:59' or TO_DAYS(FROM_DAYS(BA_DATE)) = '2003-02-25' Hm. You mean FROM_DAYS(TO_DAYS(BA_DATE)), don't you? BA_DATE is a timestamp, not a number of days. Aside from that, it prevents use of indexes, which is always bad. :-) How about BA_DATE LIKE '2003-02-25%' instead? HTH, Benjamin. -- [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: Insert/UpDate dates
Well, if this is a one-time batch transfer then a simple UPDATE abctable SET SomethingDate IS NULL when SomethingDate = '-00-00'; should null all the fields in question. Of course, you have to have a NULLable date field. Regards, Mike Hillyer http://www.dynamergy.com/mike -Original Message- From: John Thorne [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 3:51 PM To: [EMAIL PROTECTED] Subject: Insert/UpDate dates Working on a routine to export a Filemaker data to mySQL for php application. Using scripts I build a calculated field below: INSERT INTO abctable (JobNumber, JobTitle, AE, Client, StartDate) VALUES (9997, ' ABC ' , 'jrt' ,1234, '') or UPDATE abctable SET JobTitle='ABC',AE='jrt',Client=1234,SomethingDate='' WHERE JobNumber = 9997 and send this to mySQl via ODBC Problem. When the Filemaker date is blank the calculated field is produces SomethingDate=''. After the Insert or update the mySQL field is -00-00. And in the php form shows up as 00/00/. How can I get the date to be blank in mySQL. thanks jrt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Syntax confusion
At 0:23 +0100 3/6/03, Benjamin Pflugmann wrote: On Wed 2003-03-05 at 17:12:23 -0600, [EMAIL PROTECTED] wrote: [...] BA_DATE = '2003-02-25 00:00:00' AND BA_DATE = '2003-02-25 23:59:59' or TO_DAYS(FROM_DAYS(BA_DATE)) = '2003-02-25' Hm. You mean FROM_DAYS(TO_DAYS(BA_DATE)), don't you? BA_DATE is a timestamp, not a number of days. Of course. Thanks. Aside from that, it prevents use of indexes, which is always bad. :-) How about BA_DATE LIKE '2003-02-25%' instead? That'd work, too. HTH, Benjamin. -- [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: Syntax confusion
Thanks guys, Appreciate the help. Kinda makes sense now ;-) Jeff - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Benjamin Pflugmann [EMAIL PROTECTED]; Jeff [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Thursday, March 06, 2003 9:32 AM Subject: Re: Syntax confusion At 0:23 +0100 3/6/03, Benjamin Pflugmann wrote: On Wed 2003-03-05 at 17:12:23 -0600, [EMAIL PROTECTED] wrote: [...] BA_DATE = '2003-02-25 00:00:00' AND BA_DATE = '2003-02-25 23:59:59' or TO_DAYS(FROM_DAYS(BA_DATE)) = '2003-02-25' Hm. You mean FROM_DAYS(TO_DAYS(BA_DATE)), don't you? BA_DATE is a timestamp, not a number of days. Of course. Thanks. Aside from that, it prevents use of indexes, which is always bad. :-) How about BA_DATE LIKE '2003-02-25%' instead? That'd work, too. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can this subquery be expressed as a join?
Hi All; I recieved an e-mail as part of a PHP user group mailing list, does anyone know the answer? cut here Ok, I know that you can do a ( ) in a query for working stuff but can you do another query there? ie select * from Activity join ( select License Number, max(Activity Date) from Activity where Agency = 'OGC' and Posted = '2003-01-01' and ( Activity = 'RIG RELEASED' or Activity = 'RESUMED' or Activity = 'SUSPENDED' or Activity = 'CANCELLED' or Activity = 'DRILLING' or Activity = 'PREP TO SPUD' ) group by License Number having max(Activity Date) = '2003-01-01' ) AS T1 ON Activity.License Number = T1.License Number and Activity.Activity Date = T1.Activity Date cut here Any help would be appreciated. Thanks, Mike Hillyer http://www.dynamergy.com/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 [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
some gamma-gatcha sqlbench stats
I just built a P4 3.06 1GB DDR 333 15k U160 SCSI MySQL Linux server. I was dissapointed because I couldnt get the dual-channel DDR 400 MB I tried first to run stable :( dual-channel memory controllers definitely made a slight difference on the benchmarks with the same ram. alter-table 2 ATIS 13 big-tables 11 insert 736 select 424 wisconsin 3 I havent really tuned the server yet. BTW I have a 1.4GB table with a 648MB fulltext index. If anyone has server tuning settings for me, I'd like to hear em. I have the key_buffer at 500MB, bout all I've done so far. Next I am going to try compiling MySQL and DBI/DBD with the Intel 7.0 compiler. This compiler produced a 21% faster Perl for me then gcc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Feature request: using CURRENT_DATE as DEFAULT value
http://www.mysql.com/doc/en/CREATE_TABLE.html states Default values must be constants. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. Is this ever going to be fixed? I often find myself using this when creating new records, and it'd be just one less thing to worry about putting in the INSERT statement? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Foreign keys and being FIRST index
http://www.mysql.com/doc/en/SEC457.html states that there must be an index where the foreign key and the referenced key are listed as the FIRST columns. Will this restriction be lifted soon? It is incredibly frustrating. I don't see why they have to be indexes, and more importantly, I don't see why they have to be FIRST! Ugh. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
very slow mysql server, help please
Hi All: I have three web server (DUAL 1.4 G MHZ, 1G RAM) connect to one mysql database server (DUAL 2.2 G MHZ, 2 G RAM), SSL accelerator and a load balancer. All those servers are FreeBSD. For some reason, mysql server can be easily max out without many users. Also there are many processes in sleep (some of them has been put in sleep for over 3000 seconds.) My database has nearly 140 tables, but there are less than 1 recorders. Database file size is about 3M. One of the table has a BLOB field (Hold some images for my web server.). I am using mysql_pconnect() (Of cause, I tried mysql_connect(), but it's does not make big different.). Question 1: Does sleeping processes slow down database server? Question 2: Any ideas, why is the database so slow? Any suggestions? Thanks in advance and for Reading my message. William _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Foreign keys and being FIRST index
In the last episode (Mar 05), Daevid Vincent said: http://www.mysql.com/doc/en/SEC457.html states that there must be an index where the foreign key and the referenced key are listed as the FIRST columns. Will this restriction be lifted soon? It is incredibly frustrating. I don't see why they have to be indexes, and more importantly, I don't see why they have to be FIRST! Ugh. I'm not sure that sentence means what you think it does. What they're saying is you need to index both fields, and if you decide to make that index a compound one with multiple keyparts, the foreign/referenced field must be the first. They don't have to be the first fields in the table or anything. As for why you want them indexed. Have you ever tried deleting a lot of records from a table with a foreign key constraint on another table with no index? Each delete of your first table requires a full table scan of the second table, to make sure you're not violating the constraint. A co-worker forgot to index a constraint in Oracle once and his table updates took 2 hours instead of 2 minutes. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
very slow mysql server, help please
Hi All: I have three web server (DUAL 1.4 G MHZ, 1G RAM) connect to one mysql database server (DUAL 2.2 G MHZ, 2 G RAM), SSL accelerator and a load balancer. All those servers are FreeBSD. For some reason, mysql server can be easily max out without many users. Also there are many processes in sleep (some of them has been put in sleep for over 3000 seconds.) My database has nearly 140 tables, but there are less than 1 recorders. Database file size is about 3M. One of the table has a BLOB field (Hold some images for my web server.). I am using mysql_pconnect() (Of cause, I tried mysql_connect(), but it's does not make big different.). Question 1: Does sleeping processes slow down database server? Question 2: Any ideas, why is the database so slow? Any suggestions? Thanks in advance and for Reading my message. William - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Foreign keys and being FIRST index
In the last episode (Mar 05), Daevid Vincent said: http://www.mysql.com/doc/en/SEC457.html states that there must be an index where the foreign key and the referenced key are listed as the FIRST columns. Will this restriction be lifted soon? It is incredibly frustrating. I don't see why they have to be indexes, and more importantly, I don't see why they have to be FIRST! Ugh. I'm not sure that sentence means what you think it does. What they're saying is you need to index both fields, and if you decide to make that index a compound one with multiple keyparts, the foreign/referenced field must be the first. They don't have to be the first fields in the table or anything. Right, but sometimes it isn't, or you already used up that 'first' spot for a different foreign key reference in another table. Maybe I am still not understanding something elementary, but I've tried to create some tables, and certain ones work, while others don't, and they're always related to that foreign key issue. In any event, it shouldn't matter if it's first or not! I should be able to use any/all fields in a any number of databases as foreign keys reguardless of their position in the schema or index. As for why you want them indexed. Have you ever tried deleting a lot of records from a table with a foreign key constraint on another table with no index? Each delete of your first table requires a full table scan of the second table, to make sure you're not violating the constraint. A co-worker forgot to index a constraint in Oracle once and his table updates took 2 hours instead of 2 minutes. I don't dispute the benefit of indexes, I just don't think it should be a REQUIREMENT. And to be honest, sometimes a table isn't made of millions of records, maybe it's only a few, but you still want ref integrity. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 report: MySQL Embedded Server v4.0.10
Bug report -- MySQL Embedded Server v4.0.10, binary distribution for Windows Machine specs: Compaq Presario desktop Windows XP Professional SP1 Problem description: There are two bugs: The SHOW PROCESSLIST and KILL statements do not function as documented when using the MySQL Embedded Server. The SHOW PROCESSLIST statement returns an empty set. The KILL statement causes an error 1094. - To reproduce: - Run the mysql-server.exe included with the MySQL binary distribution. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.10-gamma-embedded mysql SHOW PROCESSLIST; Empty set (0.00 sec) mysql KILL 1; ERROR 1094: Unknown thread id: 1 --- My contact information: --- Matt Solnit [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
Database Questions
Hi All, Can MySQL give some indication of how many questions are being asked to each database? Using the show variables, I can get the total questions, but was wondering if it's possible to get it broken down on a per database view? Rgds Terence query sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Database Questions
At 11:59 +0800 3/6/03, Terence wrote: Hi All, Can MySQL give some indication of how many questions are being asked to each database? Using the show variables, I can get the total questions, but was wondering if it's possible to get it broken down on a per database view? No. Rgds Terence query sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Feature request: using CURRENT_DATE as DEFAULT value
At 17:56 -0800 3/5/03, Daevid Vincent wrote: http://www.mysql.com/doc/en/CREATE_TABLE.html states Default values must be constants. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. Is this ever going to be fixed? Yes. When? Dunno. I often find myself using this when creating new records, and it'd be just one less thing to worry about putting in the INSERT statement? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
No Reply from your side!! Waiting for your reply!!!
Dear Mr. Venu Tech Group, We are facing few problem in mysql database. Take an eg. we have table called bb_usersession0 user A is trying to delete a record which is having the billingstatus B . User A delete from bb_usersession0 where ui_sessionid='cs_venkatesh0006A46C' AND us_billingstaus='B' User B update bb_usersession0 set us_billingstatus='Y' where us_billingstatus='N'; In the above case mYSQL IS LOCKING THE whole table so the USER B is not able to update the status.In user session B we are getting the followin message ERROR 1205: Lock wait timeout exceeded; Try restarting transaction Kindly give me solution for the above problem. Awaiting your reply Regards velu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
OpenBSD
I hope I can get some help here, I'm installing mySQL on OpenBSD and i cannot find the mysql_install_db file, all the documentation that I've read is saying its the first step after doing make and make install from the ports tree. FYI: Its not in /usr/local/bin, and there was no /usr/local/mysql dir, and of course no /usr/local/share/doc/mysql dir either so far this has been a small nightmare for me. Has others had this problem with OpenBSD? And if so can you point me in the right direction to get it running! Thanks! Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Foreign keys and being FIRST index
In the last episode (Mar 05), Daevid Vincent said: I'm not sure that sentence means what you think it does. What they're saying is you need to index both fields, and if you decide to make that index a compound one with multiple keyparts, the foreign/referenced field must be the first. They don't have to be the first fields in the table or anything. Right, but sometimes it isn't, or you already used up that 'first' spot for a different foreign key reference in another table. Maybe I If it isn't, then create another index, on just that field. If you have multiple foreign keys, create a separate index, one for each foreign key. You can have multiple indexes on one table with no problems. Maybe MySQL should be modified so that the required indexes are silently created when a FOREIGN KEY clause is processed. am still not understanding something elementary, but I've tried to create some tables, and certain ones work, while others don't, and they're always related to that foreign key issue. In any event, it shouldn't matter if it's first or not! I should be able to use any/all fields in a any number of databases as foreign keys reguardless of their position in the schema or index. As for why you want them indexed. Have you ever tried deleting a lot of records from a table with a foreign key constraint on another table with no index? Each delete of your first table requires a full table scan of the second table, to make sure you're not violating the constraint. A co-worker forgot to index a constraint in Oracle once and his table updates took 2 hours instead of 2 minutes. I don't dispute the benefit of indexes, I just don't think it should be a REQUIREMENT. And to be honest, sometimes a table isn't made of millions of records, maybe it's only a few, but you still want ref integrity. If it's only a few records then the index won't take up that much space :) I'm pretty sure the requirement was done to make the InnoDB coders' lives easier. I know there are low-level functions for look up a keyvalue in an index. I don't know if there is one for do a full table scan and search for a value in one field without having to generate a small query and execute it (i.e. subquery, which mysql does not yet support). -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
×ÏÖñÑûÇëÄúÀ´¹²´´ÎÄѧµÄÊÀ¼ÍÍøÂç
= http://www.chinamysql.com 100M1010M20318/ http://3long.sayba.com 5881800 http://shop.sayba.com http://www.lovexin.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 and its OLEDB provider error
Hello, mysqladmin reports 'unknown settiing --default-char-set' if i set client default charset in my.cnf on suse linux. If I comment this line, there are no errors, even if if server has win1250 as default charset. But myOLEDB quit working, if i use charset other than latin1. Can this be fixed? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Indexes
At 4:41 + 3/6/03, Donna Robinson wrote: Filter-fodder: mysql, query Hi, Is this a very hard question or an absurdly simple one? I posted it days ago, and no-one has responded! Please can someone help? How can I avoid Using temporary, Using filesort when creating indices when my selects MUST be ordered? Donna Maybe none of understand what your question means? I know I don't. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 and its OLEDB provider error
At 7:21 +0100 3/6/03, marjan mrak wrote: Hello, mysqladmin reports 'unknown settiing --default-char-set' if i set client default charset in my.cnf on suse linux. If I comment this line, there are no errors, even if if server has win1250 as default charset. There is no such option. I assume you mean --default-character-set. But that option is not understood by all clients (for example, it's not understood by mysqladmin), so you can't put it in the [client] group in an option file. But myOLEDB quit working, if i use charset other than latin1. Can this be fixed? You'll have to use some option group other than [client] that myOLEDB uses. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
