Mysql 4.1.3-beta stalls when flush privileges
I know that I should use mysqlbug to report bugs, but my mysqlbug prints mysqlbug: problem report sent Description: When there are rows in mysql.columns_priv table query flush privileges stalls when query repeatly. How-To-Repeat: Insert some rows in mysql.columns_priv table. Try to query flush privileges repeatly. Fix: Have no idea. Submitter-Id: [EMAIL PROTECTED] Originator:User Organization: Ymir Entertainment MySQL support: none Synopsis: stall when flush privileges Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.1.3-beta (FreeBSD port: mysql-client-4.1.3) C compiler:cc (GCC) 3.3.3 [FreeBSD] 20031106 C++ compiler: c++ (GCC) 3.3.3 [FreeBSD] 20031106 Environment: machine, os, target, libraries (multiple lines) @ /tmp/mysqlbug-25983/mysqlbug 53 lines, 3406 characters written Environment: machine, os, target, libraries (multiple lines) System: FreeBSD SQL3.metin2.co.kr 5.2.1-RELEASE FreeBSD 5.2.1-RELEASE #0: Mon Feb 23 20:45:55 GMT 2004 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/GENERIC i386 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Configured with: FreeBSD/i386 system compiler Thread model: posix gcc version 3.3.3 [FreeBSD] 20031106 Compilation info: CC='cc' CFLAGS='-O -pipe -mcpu=pentiumpro -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer -fno-gcse' CXX='c++' CXXFLAGS='-O -pipe -mcpu=pentiumpro -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer -fno-gcse -O -pipe -mcpu=pentiumpro -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer -fno-gcse -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 892348 Feb 24 04:41 /lib/libc.so.5 -r--r--r-- 1 root wheel 1730766 Feb 24 04:41 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 14 May 2 09:43 /usr/lib/libc.so - /lib/libc.so.5 Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-libedit' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-ndbcluster' '--with-comment=FreeBSD port: mysql-client-4.1.3' '--enable-thread-safe-client' '--with-charset=euckr' '--with-extra-charsets=all' '--with-mysqld-ldflags=-all-static' '--enable-assembler' '--with-berkeley-db' '--with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -L/usr/local/lib -llthread -llgcc_r -llstdc++ -llsupc++' '--without-server' '--prefix=/usr/local' '--build=i386-portbld-freebsd5.2.1' 'CFLAGS=-O -pipe -mcpu=pentiumpro -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer -fno-gcse' 'CXX=c++' 'build_alias=i386-portbld-freebsd5.2.1' 'CC=cc' 'CXXFLAGS=-O -pipe -mcpu=pentiumpro -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer -fno-gcse -O -pipe -mcpu=pentiumpro -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer -fno-gcse -felide-constructors -fno-rtti -fno-exceptions' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: oscommerce online offline replication
I think you are looking for a backup solution. This script is awesome for automatic daily/weekly/monthly backups (and compresses them) http://members.lycos.co.uk/wipe_out/automysqlbackup/ http://sourceforge.net/projects/automysqlbackup/ To recreate the database from a backup: copy the backup to the *other* server and do the following shell mysqladmin drop db_name shell mysqladmin create db_name shell mysql db_name backup-file.sql see also: http://dev.mysql.com/doc/mysql/en/mysqldump.html olinux --- Business A2Z [EMAIL PROTECTED] wrote: Hi All This may be a longshot or it could just be the place where the answer is. I have asked something similiar before regarding this but never could a clear view of the way to proceed. Anyway, here it is. Simply I have an offline mirror of oscommerce what I woould like to do is keep the online version updated with the offline recordset (products) is there a way to do this without overwriting the tables? Ideally there are other fields from the online version I would like to keep updated offline in which case there is always an exact copy on and offline? If there is a way to do this I would appreciate any clues, links, books, and methods and programming techniques to achieve this. Andrew __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Crash
Hello, This morning I got a crash from two mysql servers (one master, one slave). The stack trace is the following on both : 0x8071f44 handle_segfault + 420 0x82a0e38 pthread_sighandler + 184 0x82f09c8 _dl_relocate_object + 1208 0x82d1e4f dl_open_worker + 879 0x82d137a _dl_catch_error + 154 0x82d20d2 _dl_open + 114 0x8290d26 dlopen_doit + 38 0x82d137a _dl_catch_error + 154 0x8291026 _dlerror_run + 246 0x8290d55 dlopen + 37 0x80e1ce3 udf_init__Fv + 595 0x807302b main + 2491 0x82a4864 __libc_start_main + 212 0x8048101 _start + 33 As far as I understand this, is that I've got something wrong with the User Definable Function. Am I right ? We added the UDF on friday, on everything seems going well, but not this morning. Any ideas where I should investigate ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Can't start server
Hi, have you got a file : /var/lib/mysql/mysql.sock if yes and no mysql is running, remove it ? [EMAIL PROTECTED] wrote: Hmm...can someone help me on this? Many thanks. - Forwarded by Joseph S CHUNG/OGCIO/HKSARG on 2004-07-12 09:40 - Joseph S CHUNG To: [EMAIL PROTECTED] 2004-07-09 15:10 cc: Subject: Can't start server || | [ ] Urgent | || || | [ ] Return Receipt | || I install mysql 4.0 with RPM on Fedora2, which comes with mysql 3.23. When I try to start mysql server, the /var/log/mysqld.log file record the following error message: mysqld started Warning : Asked for 196608 thread stack, but got 126976 Can't start server : Bind on unix socket : Permission denied Do you already have another mysqld server running on socket : /var/lib/mysql/mysql.sock ? Aborting /usr/sbin/mysqld: Shutdown Complete mysqld ended There is no mysqld running. Any idea? Thanks, Joseph -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start server
Problem solved. Thank you very much. juhui [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] cc: 2004-07-12 09:48 Subject: Re: Can't start server || | [ ] Urgent | || || | [ ] Return Receipt | || http://forums.devshed.com/archive/t-154426 On Fri, 9 Jul 2004 15:10:43 +0800, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I install mysql 4.0 with RPM on Fedora2, which comes with mysql 3.23. When I try to start mysql server, the /var/log/mysqld.log file record the following error message: mysqld started Warning : Asked for 196608 thread stack, but got 126976 Can't start server : Bind on unix socket : Permission denied Do you already have another mysqld server running on socket : /var/lib/mysql/mysql.sock ? Aborting /usr/sbin/mysqld: Shutdown Complete mysqld ended There is no mysqld running. Any idea? Thanks, Joseph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- LB can FlY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sql Query Issue
Hi All, I migrated the data from Oracle to MySQL.The following query works fine with Oracle whereas in MySql its hanging. SELECT distinct caty.name, caty.c_id, caty.notes, count(distinct segs.in_id) as num FROM segs, caty, st_mbers, t_mbers, p_mbrs, pr_mbers where segs.c_id = caty.c_id and caty.c_id=st_mbers.c_id and st_mbers.st_id = t_mbers.st_id and t_mbers.t_id = p_mbers.t_id and p_mbers.p_id = pr_mbers.p_id and pr_mbers.p_id = 1 group by st_caty.c_id, st_caty.name, st_caty.notes order by st_caty.name Following are the table structures with Row count in each table. CREATE TABLE segs( s_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, c_id INT(12) NULL , text VARCHAR(255) NULL, lookup VARCHAR(255) NULL, in_idINT(12) NULL, prr_dVARCHAR(12) NULL, nxt_dVARCHAR(12) NULL, descnVARCHAR(255) NULL, notesVARCHAR(255) NULL, s_st_id INT(12) NULL, versnFLOAT(10,4) NULL, mesg_typeVARCHAR(50) NULL ); Row count 34700 CREATE TABLE caty( c_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NULL, notesVARCHAR(255) NULL, version FLOAT(10,4) ); Row count 281 CREATE TABLE st_mbers( st_idINT(12) NULL, c_id INT(12) NULL, version FLOAT(10,4) NULL, st_mber_id INT(12) NOT NULL ); Row count 1362 CREATE TABLE t_mbers( t_id INT(12) NOT NULL, st_idINT(12) NULL, seq_nbr INT(12) NULL, version FLOAT(10,4) NULL, t_mber_idINT(12) NOT NULL ); Row count 1260 CREATE TABLE p_mbers ( p_idINT(12) NOT NULL , t_idINT(12) NULL , seq_nbr INT(12) NULL, notes TEXT NULL, version FLOAT(10,4) NULL, p_mber_id INT(12) NOT NULL ); Row Count 2198 CREATE TABLE pr_mbers( pr_idINT(12) NULL, p_id INT(12) NULL, seq_nbr INT(12) NULL, pr_mbr_id INT(12) NOT NULL ); Row Count 294 Help me in solving this hanging issue. I tried the same with both Linux and Windows XP but without any success. I tried with one record in each table mentioned above and its working fine. Kindly guide me in this regard. Thanks in advance. regards msjeyabalan ** CONFIDENTIAL INFORMATION ** This e-mail transmission and any attachments may contain confidential information belonging to the sender. The information is intended solely for the use of the individual(s) or entities addressed. If you are not the intended recipient, you are hereby notified that any copying, disclosing, distributing, or use of this e-mail and/or attachment is strictly prohibited. If you received this transmission in error please notify the sender immediately and delete the message and all attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concurrency Question
Hi, It suggests below to bundle transactions into one commit, at what point does this become unecessary ? For example I have 2 threads each doing 12,000 inserts in 1 commit each. Would I really gain any performance if I did these 24,000 inserts in 1 commit only ? Additionally, what performance should I be able to acheive with innodb ? My server is currently Linux RH, 2 CPU's 3.2Ghz, 4 Gig of ram and SCSI drives. I think my queires are optimised, I've had as much as 8000-9000 inserts per second but this is when I start to run into machine load issues. Am I asking to much of the machine ? Marvin. -Original Message- From: Josh Chamas [mailto:[EMAIL PROTECTED] Sent: 05 July 2004 20:23 To: Javier Diaz Cc: [EMAIL PROTECTED] Subject: Re: Concurrency Question Javier Diaz wrote: Hi We have changed all our tables to InnoDB and now the server is not able to handle the load, even when we are not running the SELECTs statements against these tables yet. As I mentioned in my email we make a lots of INSERTS and UPDATES in these tables (more than 3000 per second). So far using MyISAM everything was OK, but now when we moved the tables to InnoDB (to be able to make Read/Write operations in these tables) the performance was down completely and the server can not handle it. Does anyone have a rough idea when you change from MyISAM to InnoDB how the performance is affected? I would appreciate any ideas you can have, we really need this ASAP. I would suggest load/capacity testing things very carefully in a test environment before moving your system to InnoDB. InnoDB has very different locking / disk i/o behavior than MyISAM as you have discovered. It also seems to use about 2x the disk space for my tables as it has something like a 19 byte overhead per record in the table. Like others suggested, make sure you bundle as many transactions as possible in one commit. Each commit will end up doing a disk write, so using an auto-commit mode ( without BEGIN WORK ) will result in one disk write per transaction. If you bundle many SQL statements in one transaction, you can get a relative performance improvement, say if you can get an average of 3 insert/updates per transaction, then you have just increased your performance by a factor of 3 if your operations were disk bound in the first place which is likely at 3000 inserts/updates per second. That said, I also found that InnoDB can do some non-intuitive row level locking that can result in dead locks, so when moving to many SQL operations per transaction, you also have to test this carefully under load to make sure that your code does not result in any dead locks. Regards, Josh Josh Chamas, Founder| NodeWorks - http://www.nodeworks.com Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com http://www.chamas.com | Apache::ASP - http://www.apache-asp.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql client/server model
i am running mysql 4.0 in fedora core2. It is working ok. Now i want to connect other clients to the same server. How to this? i read the docs but couldn't make it work. thanks for any pointers. -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. --- Linus Torvalds -- SK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Can't start server
Hi, since I installed mysql server 4.0 i cannot start mysqld daemon. I go to /usr/bin and execute mysqld, but i get the following message: [EMAIL PROTECTED] bin]# safe_mysqld Starting mysqld daemon with databases from /var/lib/mysql 040712 11:33:07 mysqld ended How can i know what's the problem? Thanks On Mon, 2004-07-12 at 09:38, Philippe Poelvoorde wrote: Hi, have you got a file : /var/lib/mysql/mysql.sock if yes and no mysql is running, remove it ? [EMAIL PROTECTED] wrote: Hmm...can someone help me on this? Many thanks. - Forwarded by Joseph S CHUNG/OGCIO/HKSARG on 2004-07-12 09:40 - Joseph S CHUNG To: [EMAIL PROTECTED] 2004-07-09 15:10 cc: Subject: Can't start server || | [ ] Urgent | || || | [ ] Return Receipt | || I install mysql 4.0 with RPM on Fedora2, which comes with mysql 3.23. When I try to start mysql server, the /var/log/mysqld.log file record the following error message: mysqld started Warning : Asked for 196608 thread stack, but got 126976 Can't start server : Bind on unix socket : Permission denied Do you already have another mysqld server running on socket : /var/lib/mysql/mysql.sock ? Aborting /usr/sbin/mysqld: Shutdown Complete mysqld ended There is no mysqld running. Any idea? Thanks, Joseph -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade to mysql 4.0 in Fedora Core 2
After installing FC2 i realized that the mysql server version installed was 3.x. I wanted to install version 4.0, so i got the rpm package from mysql website and i installed it on my system. Now it seems like mysql server is not running. I get no output for the command ps -A | grep mysql. Also, when i try to connect to mysql server using mysql client, i get the following error message: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) It seems like mysql server does not automatically start when i boot up my computer... how can i start it manually? Where in the filesystem does mysql server get installed after installing the rpm? Though, when i run the Gnome Service config. utility, i can see mysql service on the list, with a checkmark, and it appears to be running... i'm so confused about this. Please help. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql Query Issue
This is an index problem. Your tables don't contain any indices except on PKs. This can't work, given the number of joins and table sizes. Read the doc about indices. Stefan Am Monday 12 July 2004 09:55 schrieb Jeyabalan Murugesan Sankarasubramanian: Hi All, I migrated the data from Oracle to MySQL.The following query works fine with Oracle whereas in MySql its hanging. SELECT distinct caty.name, caty.c_id, caty.notes, count(distinct segs.in_id) as num FROM segs, caty, st_mbers, t_mbers, p_mbrs, pr_mbers where segs.c_id = caty.c_id and caty.c_id=st_mbers.c_id and st_mbers.st_id = t_mbers.st_id and t_mbers.t_id = p_mbers.t_id and p_mbers.p_id = pr_mbers.p_id and pr_mbers.p_id = 1 group by st_caty.c_id, st_caty.name, st_caty.notes order by st_caty.name Following are the table structures with Row count in each table. CREATE TABLE segs( s_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, c_id INT(12) NULL , text VARCHAR(255) NULL, lookup VARCHAR(255) NULL, in_id INT(12) NULL, prr_d VARCHAR(12) NULL, nxt_d VARCHAR(12) NULL, descn VARCHAR(255) NULL, notes VARCHAR(255) NULL, s_st_idINT(12) NULL, versn FLOAT(10,4) NULL, mesg_type VARCHAR(50) NULL ); Row count 34700 CREATE TABLE caty( c_id INT(12) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NULL, notes VARCHAR(255) NULL, versionFLOAT(10,4) ); Row count 281 CREATE TABLE st_mbers( st_id INT(12) NULL, c_id INT(12) NULL, version FLOAT(10,4) NULL, st_mber_id INT(12) NOT NULL ); Row count 1362 CREATE TABLE t_mbers( t_id INT(12) NOT NULL, st_id INT(12) NULL, seq_nbrINT(12) NULL, versionFLOAT(10,4) NULL, t_mber_id INT(12) NOT NULL ); Row count 1260 CREATE TABLE p_mbers ( p_id INT(12) NOT NULL , t_id INT(12) NULL , seq_nbr INT(12) NULL, notes TEXT NULL, version FLOAT(10,4) NULL, p_mber_id INT(12) NOT NULL ); Row Count 2198 CREATE TABLE pr_mbers( pr_id INT(12) NULL, p_id INT(12) NULL, seq_nbrINT(12) NULL, pr_mbr_id INT(12) NOT NULL ); Row Count 294 Help me in solving this hanging issue. I tried the same with both Linux and Windows XP but without any success. I tried with one record in each table mentioned above and its working fine. Kindly guide me in this regard. Thanks in advance. regards msjeyabalan ** CONFIDENTIAL INFORMATION ** This e-mail transmission and any attachments may contain confidential information belonging to the sender. The information is intended solely for the use of the individual(s) or entities addressed. If you are not the intended recipient, you are hereby notified that any copying, disclosing, distributing, or use of this e-mail and/or attachment is strictly prohibited. If you received this transmission in error please notify the sender immediately and delete the message and all attachments. -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Crash
Hi,
After downgrading to the 4.0.15 provided by Mandrake, we found a more
explicit message : cannot found xxx function, which was one of our UDF.
we recompile the UDF with the extern C (not needed on our dev. server,
which is strange...), and everything is fine now.
When one query :
INSERT INTO func VALUES('myfunc_xxx',1,'libudf_cos.so','function');
are the .so tested and the function tested/loaded ? or do we have the
restart the server ?
Philippe Poelvoorde wrote:
Hello,
This morning I got a crash from two mysql servers (one master, one
slave). The stack trace is the following on both :
0x8071f44 handle_segfault + 420
0x82a0e38 pthread_sighandler + 184
0x82f09c8 _dl_relocate_object + 1208
0x82d1e4f dl_open_worker + 879
0x82d137a _dl_catch_error + 154
0x82d20d2 _dl_open + 114
0x8290d26 dlopen_doit + 38
0x82d137a _dl_catch_error + 154
0x8291026 _dlerror_run + 246
0x8290d55 dlopen + 37
0x80e1ce3 udf_init__Fv + 595
0x807302b main + 2491
0x82a4864 __libc_start_main + 212
0x8048101 _start + 33
As far as I understand this, is that I've got something wrong with the
User Definable Function. Am I right ?
We added the UDF on friday, on everything seems going well, but not this
morning. Any ideas where I should investigate ?
--
Philippe Poelvoorde
COS Trading Ltd.
+44.(0)20.7376.2401
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cost of joins?
I know the boat has probably sailed by now on this thread, but as far as I saw nobody has thrown in what I was going to say. A simple blanket statement like Design for understanding, logic and maintenance, not performance. is a little too glossy. You can't put all database usage into the one basket like that. Some applications rely on a database purely as a backend, thus, a convoluted but extremely high performance design may be favoured over a logical but average performance one. Some applications will rely on logic and user understanding to ensure that data is preserved and stored correctly, therefore a more logical layout will be favoured over a confusing one that may perform somewhat better. In the end it's up to the database designer to make the right decision as to what's most appropriate for the application. If you need more performance, throw more hardware at it You will find that in many situations throwing more hardware is _still_ not going to help. Optimizing your queries, application, tuning your server and database design is where it's going to count most. With optimizations in these areas only you can cut a query that took many hours previously down to sub second, adding more hardware in this situation is still going to leave you with an undesirably high execution time. Of course, more hardware is after optimizing queries, indices, cache etc :-) While I understand where you're going with your comments, I think it's important to make sure people know these things. Sure is. Of course, I also had performance problems once (with an Oracle application) and we had to resort to different ways of doing things, by I always/still stand by my statement that you should not design for performance, but logically and for maintenance and understanding etc... With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Authentication Scheme Bypass
Hello, Happen to come accross this articale http://www.securiteam.com/unixfocus/5BP0420DFQ.html and was wondering, using 4.0.20, if we need to make some changes or has this been addressed by MySQL staff ?? TIA, -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE_FORMAT DISTINCT
Using 4.1.3 beta (InnoDB) on XP, via ColdFusion MX I have a table containing (among other things) a list of dates for soccer matches to be played. In order to list them correctly, the SQL has been: SELECT DISTINCT fixturedate, MONTH(fixturedate) AS CalMonth FROM Fixtures ORDER BY fixturedate which works fine, and I'm able to output the result of the SQL without problem. If that query is changed to: SELECT DISTINCT fixturedate, MONTH(fixturedate) AS CalMonth, DATE_FORMAT(fixturedate, '%d a%') AS testing FROM Fixtures ORDER BY fixturedate Coldfusion tells me that it can't convert a ByteArray into a string when it tries to output #testing# from the query (the other two fields are output with no error). However, outputting this query to a text file shows the #testing# field as a string. If the 'DISTINCT' is removed: SELECT fixturedate, MONTH(fixturedate) AS CalMonth, DATE_FORMAT(fixturedate, '%d a%') AS testing FROM Fixtures ORDER BY fixturedate Then the #testing# string outputs without a problem. Is there something in the documentation I've missed, or is this a bug (perhaps in CFMX)? Can't see why the DISTINCT clause should change a string to a ByteArray Any help would be appreciated. Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Crash
Hi,
We try to upgrade to 4.0.20, but we still have a crash on startup. Is
there any binaries compatibilities regarding the binairies provided by
MySQL ?
Philippe Poelvoorde wrote:
Hi,
After downgrading to the 4.0.15 provided by Mandrake, we found a more
explicit message : cannot found xxx function, which was one of our UDF.
we recompile the UDF with the extern C (not needed on our dev. server,
which is strange...), and everything is fine now.
When one query :
INSERT INTO func VALUES('myfunc_xxx',1,'libudf_cos.so','function');
are the .so tested and the function tested/loaded ? or do we have the
restart the server ?
Philippe Poelvoorde wrote:
Hello,
This morning I got a crash from two mysql servers (one master, one
slave). The stack trace is the following on both :
0x8071f44 handle_segfault + 420
0x82a0e38 pthread_sighandler + 184
0x82f09c8 _dl_relocate_object + 1208
0x82d1e4f dl_open_worker + 879
0x82d137a _dl_catch_error + 154
0x82d20d2 _dl_open + 114
0x8290d26 dlopen_doit + 38
0x82d137a _dl_catch_error + 154
0x8291026 _dlerror_run + 246
0x8290d55 dlopen + 37
0x80e1ce3 udf_init__Fv + 595
0x807302b main + 2491
0x82a4864 __libc_start_main + 212
0x8048101 _start + 33
As far as I understand this, is that I've got something wrong with the
User Definable Function. Am I right ?
We added the UDF on friday, on everything seems going well, but not
this morning. Any ideas where I should investigate ?
--
Philippe Poelvoorde
COS Trading Ltd.
+44.(0)20.7376.2401
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Control Center font script configuration problem
Hello, I am a programmer in Korea. I have a problem with MySQL Control Center font script configuration. In options - fonts - application font, I selected options like below : Font : gulim (Korean font) Script : hangul (Korean) and I clicked 'OK' button. But when I open 'application font' window again, the script configuration always goes back to 'latin'. Even though I configured 'script' exactly 'hangul', why does this problem happen? Also I tested another script language like 'greek', 'runic', 'hiragana' and etc. But always come back to 'latin'. Closing and running the program again does not make it better. How can I get my script language configuration correctly? _ , ... http://www.msn.co.kr/money/interlotto/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
When is mysql 4.1.x , production?
when is mysql 4.1.x going to be released for production? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can you help me optimizing this query?
Hi all, I have an audio tracks info table, let's call it Tracks; every Track can have one or more ' Character' ( it is not a genre, it is something like 'Italian' or 'International' or '80's' or 'evergreen' ) so a track can be 'International' and 'Evergreen', or 'Italian' and '70's') I have three tables to handle this: Track Character Track_Character ( the intermediate table to handle the Track-Characters connection) Now, if I want to search all tracks, let's say by title, and have for every track its data and its characters codes, I'm doing something like: select track.*, track_character.id_character from track left join track_character on track.track_id = track_character.track id where track_title = '%something%' order by track_title this seems to work, but it is VERY VERY slow, and I'm working with only 4000-5000 track records, most of them having only one track_character record. It can last up to 120 seconds if I leave the title blank, resulting on all tracks listed. Any Idea on how could I optimize this? Thanx in advance, Giulio Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you help me optimizing this query?
What about having a column for each genre, so tracks would look like: id, title, artist, gItalian, gInternational, g80, g70 the genres are of type int so 1 for yes 0 for no. then just do SELECT * FROM tracks WHERE g80 = 1 AND gItalian = 1 Not the most dynamic of solutions, but as your genre list wont change that often? should be ok. -- Dave - Original Message - From: Giulio [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, July 12, 2004 1:50 PM Subject: can you help me optimizing this query? Hi all, I have an audio tracks info table, let's call it Tracks; every Track can have one or more ' Character' ( it is not a genre, it is something like 'Italian' or 'International' or '80's' or 'evergreen' ) so a track can be 'International' and 'Evergreen', or 'Italian' and '70's') I have three tables to handle this: Track Character Track_Character ( the intermediate table to handle the Track-Characters connection) Now, if I want to search all tracks, let's say by title, and have for every track its data and its characters codes, I'm doing something like: select track.*, track_character.id_character from track left join track_character on track.track_id = track_character.track id where track_title = '%something%' order by track_title this seems to work, but it is VERY VERY slow, and I'm working with only 4000-5000 track records, most of them having only one track_character record. It can last up to 120 seconds if I leave the title blank, resulting on all tracks listed. Any Idea on how could I optimize this? Thanx in advance, Giulio Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication stopping
Hi, often my slave suddenly stops, reporting these the logs: 040712 12:19:00 Slave I/O thread exiting, read up to log 'db-bin.3323', position 197564621 040712 12:19:10 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.3323' at position 197564621 040712 13:48:22 Slave I/O thread exiting, read up to log 'db-bin.3323', position 208931388 040712 13:48:25 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.3323' at position 208931388 this is the configuration of the slave: [mysqld] log-bin server-id=20 master-host=master master-port=3306 master-user=replica master-password=*** replicate-ignore-db=mysql replicate-wild-do-table=db1.prc replicate-wild-do-table=db2.provincia_rc replicate-wild-do-table=db3.tc replicate-wild-do-table=prc.% master-connect-retry=60 slave-skip-errors=all How can I understand exactly what is stopping the replication? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: When is mysql 4.1.x , production?
On Mon, 12 Jul 2004 12:05:53 + Ben David, Tomer [EMAIL PROTECTED] wrote: when is mysql 4.1.x going to be released for production? When it is ready I'd guess. :) If history is a predictor though I would expect 4.1.x to go though 5-7 more beta releases though. Help the developers out by testing it in your enviornment. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT + ORDER BY confusion
If what you mean by most recent are the products with the latest 'post_date', try this: SELECT ID, title, max(s.post_date) as post_date FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 GROUP BY ID,title order by post_date desc LIMIT 10 You will have an extra column of data but you should not be required to display every column, are you? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Jon Drukman | | | [EMAIL PROTECTED]| | | m | | | Sent by: news| | | [EMAIL PROTECTED]| | | rg | | || | || | | 07/09/2004 04:08 | | | PM | | || |-+ | | | | To: [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: SELECT DISTINCT + ORDER BY confusion | | I've got a product story setup where there can be multiple stories of a given type for any product. I want to find the names of the products with the most-recently-posted stories of a certain type. This query works well: SELECT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 +++ | id | title | +++ | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 | The Lord of the Rings, The Battle for Middle-earth | | 914811 | The Sims 2 | | 919973 | RollerCoaster Tycoon 3 | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | | 915040 | Soldiers: Heroes of World War II | +++ however since there are multiple stories of the correct type for some of those products, i would like to dedupe the results and just get a unique list of products. however, if i use SELECT DISTINCT it applies that BEFORE it does the sort, so i don't get only the most recent products. what i actually get seems to be pretty random. SELECT DISTINCT p.id,p.title FROM product p join e_prod ep on ep.product=p.id join story s on s.id = ep.story and s.status = 9 and s.type = 14 where p.platform_id = 5 and p.genre_id = 23282 order by s.post_date desc limit 10 ++---+ | id | title | ++---+ | 917958 | Port Royale 2 | | 920457 | Cuban Missile Crisis | | 915000 | Axis Allies | | 919602 | Blitzkrieg: Burning Horizon | | 914594 | SuperPower 2 | | 914911 | Kohan II: Kings of War| | 915017 | Sid Meier's Pirates! | | 918842 | Warlords Battlecry III| | 919973 | RollerCoaster Tycoon 3| | 920314 | Immortal Cities: Children of the Nile | ++---+ that's pretty messed up. really what i'd like is: +++ | id | title | +++ | 917958 | Port Royale 2 | | 919355 | Warhammer 40,000: Dawn of War | | 918989 |
Re: How can I count() on multiple tables in a single query?
It sounds as though you don't want to see how many rows are returned for the users column but rather how many distinct names are in that column; you need a COUNT(DISTINCT). Your GROUP BY clause is also off as it does not list all of the non-aggregated columns in your SELECT statement. See if this works: SELECT Departments.DeptID , DeptName , COUNT(DISTINCT UserDept.CoreID) AS users , COUNT(IP_Addr) as devices FROM Departments LEFT JOIN IP_Dept ON Departments.DeptID = IP_Dept.DeptID LEFT JOIN UserDept ON Departments.DeptID = UserDept.DeptID GROUP BY Departments.DeptID, Departments.DeptName ORDER BY DeptName DESC; Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Daevid Vincent | | | [EMAIL PROTECTED]| | | m | | || | | 07/09/2004 07:22 | | | PM | | || |-+ | | | | To: [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: How can I count() on multiple tables in a single query? | | Using mysql v4.0.x on linux. Given three tables... CREATE TABLE Departments ( DeptID int(10) unsigned NOT NULL auto_increment, DeptName char(30) default NULL, PRIMARY KEY (DeptID) ) CREATE TABLE UserDept ( CoreID int(10) unsigned NOT NULL default '0', DeptID int(10) unsigned NOT NULL default '0', DeptAdmin char(1) default NULL, DeptEmail char(1) default NULL, DeptContact char(1) default NULL, KEY DeptID (DeptID), KEY CoreID (CoreID) ) CREATE TABLE IP_Dept ( IP_Addr int(10) unsigned NOT NULL default '0', DeptID int(10) unsigned NOT NULL default '0', UNIQUE KEY DeptIP (IP_Addr,DeptID) ) What I want is a listing of all the department names, and a tally of how many users in each, and another column with the tally of how many IPs in each I've tried various combinations of this, changing the COUNT() and GROUP BY values: SELECT Departments.DeptID, DeptName, COUNT(UserDept.CoreID) AS users, COUNT(IP_Addr) as devices FROM Departments LEFT JOIN IP_Dept on Departments.DeptID = IP_Dept.DeptID LEFT JOIN UserDept ON Departments.DeptID = UserDept.DeptID GROUP BY UserDept.CoreID, IP_Dept.IP_Addr ORDER BY DeptName DESC; But nothing is working right. Mostly what happens is both 'users' and 'devices' is the same value. Is this possible? I can do it for the first COUNT(). And then I could do a second query, but I'm trying to do this in a single query if possible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
Harald Fuchs wrote: In article [EMAIL PROTECTED], gerald_clark [EMAIL PROTECTED] writes: Asif Iqbal wrote: Jack Coxen wrote: If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. My databases (mysql,rt3,test) are taking only about 2k. No. The ibdata* files ARE your database. You need more disk space, and you need it now. Not necessarily. ibdata files can grow if they are declared with autoextend, but they don't shrink, even if you're deleting all rows. And without disk space, he can't dump and reload unless he dumps to tape. Better verify that tape dump before deleting those innodb files! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication stopping
To debug the problem. Make SHOW SLAVE STATUS in the slave and check for the error number.. Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.gittigidiyor.com http://www.dalar.net - Original Message - From: Nico Sabbi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 12, 2004 6:08 PM Subject: Replication stopping Hi, often my slave suddenly stops, reporting these the logs: 040712 12:19:00 Slave I/O thread exiting, read up to log 'db-bin.3323', position 197564621 040712 12:19:10 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.3323' at position 197564621 040712 13:48:22 Slave I/O thread exiting, read up to log 'db-bin.3323', position 208931388 040712 13:48:25 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'db-bin.3323' at position 208931388 this is the configuration of the slave: [mysqld] log-bin server-id=20 master-host=master master-port=3306 master-user=replica master-password=*** replicate-ignore-db=mysql replicate-wild-do-table=db1.prc replicate-wild-do-table=db2.provincia_rc replicate-wild-do-table=db3.tc replicate-wild-do-table=prc.% master-connect-retry=60 slave-skip-errors=all How can I understand exactly what is stopping the replication? Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT] Email addresses shown in archive at lists.mysql.com
Dear list, After posting a question last week from an email address I only use for this list, I have already started to receive spam and virus reports. Looking at the archives at lists.mysql.com I notice that the from address is not shown, just the name. But... If someone else quotes your message in a reply ( which is considered best practice in all mailing list I have ever been a member of ) email addresses are displayed. Is there anyway that these addresses can be removed/ blanked from the archives? I understand that my address could have ( and is likely to have ) been harvested by a list member infected with a virus, but with the growing increase in spam shouldn't the archives be clean by default? Thoughts? I will be unsubscribing from the list and re-subsubscrbing with a new address shortly, so don't be surprised if direct replies are bounced... Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] Email addresses shown in archive at lists.mysql.com
Ian, note that the MySQL mailing list is also forwarded to the mailing.database.myodbc Usenet group, as well as to several mailing list archiving websites. Spam robots and viruses have plenty of places from which to harvest your email address if you write to this list. I personally receive some 2000 viruses and 300 spam emails per day to my email address. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html . List: mysql Subject:[OT] Email addresses shown in archive at lists.mysql.com From: Ian Gibbons mysql1 () fishnet ! co ! uk Date: 2004-07-12 14:18:49 Message-ID: 40F2ABD9.17993.2491A560 () localhost [Download message RAW] Dear list, After posting a question last week from an email address I only use for this list, I have already started to receive spam and virus reports. Looking at the archives at lists.mysql.com I notice that the from address is not shown, just the name. But... If someone else quotes your message in a reply ( which is considered best practice in all mailing list I have ever been a member of ) email addresses are displayed. Is there anyway that these addresses can be removed/ blanked from the archives? I understand that my address could have ( and is likely to have ) been harvested by a list member infected with a virus, but with the growing increase in spam shouldn't the archives be clean by default? Thoughts? I will be unsubscribing from the list and re-subsubscrbing with a new address shortly, so don't be surprised if direct replies are bounced... Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Weeding out duplicates
Robert, No problem. I actually considered the PHP option, and usually do since I use it quite a bit. However, what I was trying to accomplish was only a one time thing so I just decided to do it all with queries. Thank you though for your helpfulness, and everyone else too. What did I ever do without the Internet and the amazing resource it makes everyone! Regards, Jonathan Duncan Robert A. Rosenberg [EMAIL PROTECTED] 07/10 8:09 pm At 17:25 -0600 on 07/09/2004, Jonathan Duncan wrote about Re: Weeding out duplicates: For the information of someone who may need it in the future. I used Jeffrey's idea for determining duplicates. Then I created a temporary table, and used insert...select to put the id's of the duplicates in the temporary table. Then it was a simple delete from table where temp.id=table.id. Thanks for the help. Jonathan Duncan I responded to your query earlier with a PHP/MySQL solution that was equivalent to this. I only saw your reply after sending my suggestion. Sorry for giving you an answer you had already discovered. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a SELECT Query
Hi, I use the following query to extract information about a practice in my database. However if the practice system id hasnt been set then the query won't work. SELECT P.*, S.System_Name FROM Practices P, Systems S WHERE P.Practice_ID = '.$_SESSION['ses_practice_id'].' AND S.System_ID = P.System_ID Is there a way to allow the query to extract the practuce information even if the system hasnt been set? Thanks for your help. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a Date Query Please!
If I understand you correctly, you need a query that will return for 1 user's available hours for the next 10 days. (Since you didn't use any of the new datetime functions that were added recently to MySQL, I assume you are using a version 4.x) SELECT User_ID , DATE_FORMAT(Booking_End_Date, %Y-%m-%d) as AvailDate , 8.5 - ( SUM( (0.0 + UNIX_TIMESTAMP(Booking_End_Date)) - (0.0 + UNIX_TIMESTAMP(Booking_Start_Date)) ) /3600 ) AS Available_Hours FROM Bookings b WHERE b.User_ID = '610' AND Booking_Start_Date = '2004-07-08' AND Booking_End_Date '2004-07-19' GROUP BY User_ID, DATE_FORMAT(Booking_End_Date, %Y-%m-%d) NOTES: The 0.0+ math converts the UNIX_TIMESTAMP() results to a signed floating point value. Divide by 3600 to convert total seconds to hours. I changed the WHERE clause to select those bookings that start anytime on or after your first day and end sometime during the last day (that's why I said the 11th day). Be aware that if you have any bookings that start on one day and end on another, this calculation will fail miserably. Hopefully, though it helps you to see how many different ways there are of looking at the issue. This query will only return rows for dates where a booking entry exists. If in that 10 day span, you have a completely open day, no row will exist that will trigger a calculation for that day so no available hours will be returned. You can work around this by making either a dummy booking (a booking for 0 elapsed time sometime during that day) or by INNER JOINING a temporary table of Dates that cover the range you are curious in or by specifically testing for a list of specific dates in a more complex WHERE clause. If you use the dummy booking method of tagging days as bookable, you could also change the query to look like this: SELECT User_ID , DATE_FORMAT(Booking_End_Date, %Y-%m-%d) as AvailDate , 8.5 - ( SUM( (0.0 + UNIX_TIMESTAMP(Booking_End_Date)) - (0.0 + UNIX_TIMESTAMP(Booking_Start_Date)) ) /3600 ) AS Available_Hours FROM Bookings b WHERE b.User_ID = '610' AND Booking_Start_Date = '2004-07-08' GROUP BY User_ID, DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ORDER BY DATE_FORMAT(Booking_End_Date, %Y-%m-%d) LIMIT 10 Which will return the users available hours during the next 10 available booking dates (on or after 2004-07-08) regardless of their calendar sequence. That way if a user is NOT available for bookings (because they were on vacation...) the absence of a dummy entry would not list those days as available in the results. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+- | | shaun thornburgh| | | [EMAIL PROTECTED]| | | otmail.com | | | | | | 07/09/2004 04:00 | | | PM| | | | |-+- | | | | To: [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: Help with a Date Query Please! | | Hi, I have a table called Bookings which holds start times and end times for appointments, these are held in Booking_Start_Date and Booking_End_Date. I have a page on my site that runs a query to produce a grid to show availiability per day for the next ten days for each user of the system. Users work 8.5 hours a day and the query shows how many hours available the user has on that day: SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM Bookings B WHERE B.User_ID = '610' AND NOT ( '2004-07-08' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2004-07-08' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) The problem here is I have to do this query to produce a result for each cell and then for each user so 10 users = 100 queries to load the page!
Re: [OT] Email addresses shown in archive at lists.mysql.com
On Mon, Jul 12, 2004 at 05:28:04PM +0300, Heikki Tuuri wrote the following: Ian, note that the MySQL mailing list is also forwarded to the mailing.database.myodbc Usenet group, as well as to several mailing list archiving websites. Spam robots and viruses have plenty of places from which to harvest your email address if you write to this list. I personally receive some 2000 viruses and 300 spam emails per day to my email address. I'm subscribed to at least 2 dozen mailing lists (I'm active on about a 1/4 of them, the others are informational for programs or interests I have) and I don't get nearly that many viruses and spam, even in a week. -- AIM: pres CTHULHU | ICQ: 18115568 | Yahoo: pagan_prince Jabber: DarkKnightRadick@(jabber.org|amessage.at) | Libertarian @ Large PGP: 0x642F7BDA | http://groups.yahoo.com/group/tennesseans-for-badnarik/ http://mc-luug.homelinux.org/mailman/listinfo/mc-luug pgpwqh3VJzRap.pgp Description: PGP signature
Re: [OT] Email addresses shown in archive at lists.mysql.com
Ian Gibbons wrote: If someone else quotes your message in a reply ( which is considered best practice in all mailing list I have ever been a member of ) email addresses are displayed. I quote you. Now where is an email address displayed? I understand that my address could have ( and is likely to have ) been harvested by a list member infected with a virus, but with the growing increase in spam shouldn't the archives be clean by default? The only way list archives will be clean is if people send clean emails to lists. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query gets count wrong
I am an anal single query-oholic. I know I could do this in 2 queries I have a query involving several related tables and I have attempted to reduce it down to what causes not what I want results. I am attempting to fill a summary table. For each main item in this table I want to count the number of child items in a related table that point to it. This is fine. I have a third table called ratings which rates individual child items and it is the problem. The table qxe contains e_id which is a foreign key to the main table and q_id which is a foreign key to the actual child items. I had to set things up this way because a q might belong to more than one e. Right now I am not even interested in anything about the q's data I just want to count them. The ratings table is the problem because any q can have an arbitrary number of ratings. so a rating has q_id and e_id as well as another key that combines to form a multipart key. instead of the number of q_ids in the qxe table that have e_id equal to the the current e_id, I am getting as e_count the total number of ratings for that e_id which is a huge humber. I know I need another constraint but I cant figure out what it is. What constraint can I add to make this query do what I want while still being able to average the ratings. SELECT e.e_id, e.e_code, COUNT(qxe.q_id) as e_count, avg(ratings.r_quality) as avqual FROM e, qxe, ratings WHERE e.e_id = qxe.e_id AND ratings.e_id = e.e_id AND ratings.q_id = qxe.q_id AND ratings.e_id = qxe.e_id GROUP BY e.e_id; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql restarts when creating UDF.
hi, i have a very serious problem, when i try to reinitialise the database and start my application. By reinitilisation i would remove the database i created along with the UDF functions i created. Then when i try to start my application, which creates the database tables and the UDf, MySQL Restarts . The following error was logged in the error log : Number of processes running now: 0 040712 18:26:33 mysqld restarted 040712 18:26:33 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 44378 InnoDB: Doing recovery: scanned up to log sequence number 0 44378 040712 18:26:33 InnoDB: Flushing modified pages from the buffer pool... 040712 18:26:33 InnoDB: Started /abc/MySQL4.0.15/mysql-standard-4.0.15-pc-linux-i686/bin/mysqld: ready for connections. Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 040712 20:13:51 mysqld started /advent/MySQL4.0.15/mysql-standard-4.0.15-pc-linux-i686/bin/mysqld: ready for connections. Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=2 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x84a6aa0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbff3ef48, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8070640 0x8288108 0x82c2a56 0x82e3cab 0x82c50df 0x82c460a 0x82c5362 0x8283fd6 0x82c460a 0x82842d6 0x8284005 0x80ddf44 0x807d5ad 0x807e895 0x8079e03 0x807985d 0x807904f 0x82858bc 0x82bb07a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x84af4f8 = create function getData returns integer soname sharedObject.so thd-thread_id=19 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. When the application is started for first time, mysql restarts and the database and UDF's are not created, but however when it was started for the second time, the database and the UDF's are getin created. please help me . this is found to appear only on mysql 4.0.* , while it works fine in 3.23.* ___ALL-NEW Yahoo! Messenger - so many all-new ways to express yourself http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data corruption on deletes
This sequence causes the same errors: ALTER TABLE postsearch DISABLE KEYS; delete from postsearch where postId=65031 limit 1; ALTER TABLE postsearch ENABLE KEYS; #1034 - Incorrect key file for table: 'postsearch'. Try to repair it Could this be a bug, as hinted at here by people using 4.1: http://bugs.mysql.com/bug.php?id=3822 http://bugs.mysql.com/bug.php?id=3808 ? Jim Jim wrote: gerald_clark wrote: Hardware? Celeron 1.3Ghz, IDE drive, 512Mb RAM OS and version? GNU/Linux, 2.4.20-8 kernel MySql version? 4.0.17 Size of data file? Size of index file? postsearch.frm 8.7K postsearch.MYD 3.5G postsearch.MYI 1.0G postsearch.TMD 3.5G Filesystem type? ext3 Sorry 'bout that! Also, in the time since posting the question below, I tried the operation again (after repairing things), but I did a ALTER TABLE ___ DISABLE KEYS before and an ENABLE KEYS after. This did not work, as although the DB seemed to be working afterwards, mysql describe postsearch; ERROR 1016: Can't open file: 'postsearch.MYI'. (errno: 144) So now I am repairing again. Thanks for your interest, Jim I have a table with several keys. When I try to delete anything from this table, I get data corruption and have to repair it with myisamchk. Selects, updates work fine. Here's the create table statement: CREATE TABLE `postsearch` ( `postId` int(11) NOT NULL default '0', `weblogId` int(11) NOT NULL default '0', `url` varchar(200) NOT NULL default '', `plink` varchar(200) NOT NULL default '', `image` varchar(100) default NULL, `language` varchar(100) default NULL, `title` varchar(100) default NULL, `weblogTitle` varchar(100) default NULL, `dateCreated` datetime NOT NULL default '-00-00 00:00:00', `post` text, `excerpt` text, `parserVersion` varchar(255) default NULL, PRIMARY KEY (`postId`), KEY `weblog_key` (`weblogId`,`dateCreated`), KEY `url_key` (`url`), KEY `plink_key` (`plink`), FULLTEXT KEY `excerpt` (`excerpt`) ) TYPE=MyISAM I think I have to somehow disable the keys, but am not sure quite how. Here's what happens when I try to delete: mysql select postId from postsearch where dateCreated NOW() - INTERVAL 14 DAY limit 1; ++ | postId | ++ | 65031 | ++ 1 row in set (0.10 sec) mysql delete from postsearch where postId=65031 limit 1; ERROR 1034: Incorrect key file for table: 'postsearch'. Try to repair it Anybody have any idea? Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql restarts when creating UDF.
Hi, I've got approx. the same pb. Try the MySQL package provided by you favorite distro, recompile, and see if it works. I have no clue where the problem comes from. Prem Soman wrote: hi, i have a very serious problem, when i try to reinitialise the database and start my application. By reinitilisation i would remove the database i created along with the UDF functions i created. Then when i try to start my application, which creates the database tables and the UDf, MySQL Restarts . The following error was logged in the error log : Number of processes running now: 0 040712 18:26:33 mysqld restarted 040712 18:26:33 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 44378 InnoDB: Doing recovery: scanned up to log sequence number 0 44378 040712 18:26:33 InnoDB: Flushing modified pages from the buffer pool... 040712 18:26:33 InnoDB: Started /abc/MySQL4.0.15/mysql-standard-4.0.15-pc-linux-i686/bin/mysqld: ready for connections. Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 040712 20:13:51 mysqld started /advent/MySQL4.0.15/mysql-standard-4.0.15-pc-linux-i686/bin/mysqld: ready for connections. Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=2 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x84a6aa0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbff3ef48, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8070640 0x8288108 0x82c2a56 0x82e3cab 0x82c50df 0x82c460a 0x82c5362 0x8283fd6 0x82c460a 0x82842d6 0x8284005 0x80ddf44 0x807d5ad 0x807e895 0x8079e03 0x807985d 0x807904f 0x82858bc 0x82bb07a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x84af4f8 = create function getData returns integer soname sharedObject.so thd-thread_id=19 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. When the application is started for first time, mysql restarts and the database and UDF's are not created, but however when it was started for the second time, the database and the UDF's are getin created. please help me . this is found to appear only on mysql 4.0.* , while it works fine in 3.23.* ___ALL-NEW Yahoo! Messenger - so many all-new ways to express yourself http://uk.messenger.yahoo.com -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you help me optimizing this query?
I've found a solution so I'm answering to myself, for who can be interested and for asking if I've done something that could affect on some way my DB performances. I've added an index to both the columns that I use for the ON clause of my left join query, and now it runs at the speed of thought! can I be happy with this result, or there's something I have not considered? Giulio Il giorno 12/lug/04, alle 14:50, Giulio ha scritto: Hi all, I have an audio tracks info table, let's call it Tracks; every Track can have one or more ' Character' ( it is not a genre, it is something like 'Italian' or 'International' or '80's' or 'evergreen' ) so a track can be 'International' and 'Evergreen', or 'Italian' and '70's') I have three tables to handle this: Track Character Track_Character ( the intermediate table to handle the Track-Characters connection) Now, if I want to search all tracks, let's say by title, and have for every track its data and its characters codes, I'm doing something like: select track.*, track_character.id_character from track left join track_character on track.track_id = track_character.track id where track_title = '%something%' order by track_title this seems to work, but it is VERY VERY slow, and I'm working with only 4000-5000 track records, most of them having only one track_character record. It can last up to 120 seconds if I leave the title blank, resulting on all tracks listed. Any Idea on how could I optimize this? Thanx in advance, Giulio Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Speed this Query Up?
Hi Doug, I looked at it again and noticed a relationship that I missed before. You *do* have loc_countries_lang associated to the other tables through the loc_countries table. Sorry I missed it last time. I also noticed that you wrote this from the bottom up you started with the most detailed element, veg and added information from its parents and some of its children to complete your query (Wow! what a good night's sleep can do to help you think, eh?) I am going to try to diagram the dependency tree of the JOINS in this query. Problem is I have never done this without graphics so it may look silly. veg - loc_states - loc_districts - loc_countries - loc_countries_lang - users - users_intros - veg_titles - tech_equip Since the objects seem related in a geographic hierarchy, let me see what it looks like if I flip the tree around like: loc_countries - loc_countries_lang - loc_states - loc_districts - veg - users - users_intros - veg_titles - tech_equip The top two branches of this tree seems to be driven by the fact that lang_ID = 0. The bottom branch is looking for particular veg records. We could narrow part of our search list (improving our join performance) if we start with a query like: CREATE TEMPORARY TABLE tmpStateList SELECT s.state_id, ctrl.country_name , ctr.nice_country_name FROM loc_countries ctr INNER JOIN loc_countries_lang ctrl ON ctr.country_id = ctrl.country_id AND ctrl.lang_id =0 INNER JOIN loc_districts d on d.district_id = s.district_id and d.lang_id = 0 INNER JOIN loc_states AS s ON s.district_id = d.district_ID AND s.lang_ID = 0 This gives us a list of all states that speak language 0 along with their country_name and nice_country_name. Now we need to get at the veg-based information in order to get the rest of the data asked for in the original query. Optionally, you can add an index to the temp table to speed up the next stage's query (I usually do). ALTER TABLE tmpStateList add key(state_ID); SELECT v.veg_name , v.veg_id , u.user_id , u.user_name , IFNULL( t.title_name, 'Untitled' ) AS title_name , tsl.country_name , tsl.nice_country_name , te.equip_name , CONCAT( ui.first_name, ' ',ui.last_name ) AS full_name FROM tmpStateList tsl INNER JOIN veg AS v tsl.state_id = v.state_id INNER JOIN users AS u ON u.user_id = v.user_id AND u.acct_status = 'Enabled' INNER JOIN user_intros AS ui ON ui.user_id = u.user_id AND ui.lang_id =0 LEFT JOIN veg_titles AS t ON t.veg_id = v.veg_id AND t.lang_id =0 LEFT JOIN tech_equip AS te ON te.equip_id = v.equip_id WHERE v.latest_version = 'Y' AND v.cur_status = 'Active' ORDER BY v.date_submitted DESC LIMIT 0 , 10 You could flip the deconstruction/reconstruction process I just thought through. Create a temp table that contains the state_id and the other veg-based columns (where state.lang_id=0) then JOIN back to it the district, country, and country language tables to fill in the rest of the query. Sometimes deconstructing a complex, multi-stage join like this one and analyzing each branch of the join separately, it is possible to further minimize the number of rows at each stage of the join. It just requires a little more work as you are, in essence, forcing yourself to think like the query optimizer. Each stage has the potential to be optimized (like the joins between veg and the user_xx tables, perhaps?). By breaking a single larger statement down into smaller, more manageable joins, you can get significant performance enhancement at the expense of having to hand-code the additional steps that the optimizer would normally *try* to do for you automatically. Personally, I have improved the performance of a rather complex join from about 20 minutes to just about 2 seconds by using this technique so I know it works if done correctly. If you could, would you please let me know how the split-up query operates for you? Thanks in advance. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Doug V | | | [EMAIL PROTECTED]| | || | | 07/09/2004 04:31 | | | PM | | || |-+ | | | | To: [EMAIL PROTECTED] | | cc:
Re: can you help me optimizing this query?
Do you have all of the right indexes on your tables to assist MySQL in creating your JOIN? Show us the results of SHOW CREATE TABLE for your 3 tables, please... Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Giulio | | | [EMAIL PROTECTED]| | | n.it| | || | | 07/12/2004 08:50 | | | AM | | || |-+ | | | | To: MySQL List [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: can you help me optimizing this query? | | Hi all, I have an audio tracks info table, let's call it Tracks; every Track can have one or more ' Character' ( it is not a genre, it is something like 'Italian' or 'International' or '80's' or 'evergreen' ) so a track can be 'International' and 'Evergreen', or 'Italian' and '70's') I have three tables to handle this: Track Character Track_Character ( the intermediate table to handle the Track-Characters connection) Now, if I want to search all tracks, let's say by title, and have for every track its data and its characters codes, I'm doing something like: select track.*, track_character.id_character from track left join track_character on track.track_id = track_character.track id where track_title = '%something%' order by track_title this seems to work, but it is VERY VERY slow, and I'm working with only 4000-5000 track records, most of them having only one track_character record. It can last up to 120 seconds if I leave the title blank, resulting on all tracks listed. Any Idea on how could I optimize this? Thanx in advance, Giulio Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you help me optimizing this query?
David, I *really* do not like to be critical. However, for the sake of the newer DBAs out there I feel the need to discourage your suggestion. Your idea would work but I would not recommend it under most circumstances. Breaking normalization should only be used as a tool of last resort when you are trying to optimize query response time. As you said yourself, it is not the most flexible idea as implementing it would require design changes to both the database and the application in order to deal with what should be a data-only problem, adding a new character type to your list of track characters. Sorry. :-( Most respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | David Scott| | | [EMAIL PROTECTED]| | | o.uk| | || | | 07/12/2004 09:01 | | | AM | | || |-+ | | | | To: MySQL List [EMAIL PROTECTED], Giulio [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: Re: can you help me optimizing this query? | | What about having a column for each genre, so tracks would look like: id, title, artist, gItalian, gInternational, g80, g70 the genres are of type int so 1 for yes 0 for no. then just do SELECT * FROM tracks WHERE g80 = 1 AND gItalian = 1 Not the most dynamic of solutions, but as your genre list wont change that often? should be ok. -- Dave - Original Message - From: Giulio [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, July 12, 2004 1:50 PM Subject: can you help me optimizing this query? Hi all, I have an audio tracks info table, let's call it Tracks; every Track can have one or more ' Character' ( it is not a genre, it is something like 'Italian' or 'International' or '80's' or 'evergreen' ) so a track can be 'International' and 'Evergreen', or 'Italian' and '70's') I have three tables to handle this: Track Character Track_Character ( the intermediate table to handle the Track-Characters connection) Now, if I want to search all tracks, let's say by title, and have for every track its data and its characters codes, I'm doing something like: select track.*, track_character.id_character from track left join track_character on track.track_id = track_character.track id where track_title = '%something%' order by track_title this seems to work, but it is VERY VERY slow, and I'm working with only 4000-5000 track records, most of them having only one track_character record. It can last up to 120 seconds if I leave the title blank, resulting on all tracks listed. Any Idea on how could I optimize this? Thanx in advance, Giulio Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql@lists.mysql.com
Da: [EMAIL PROTECTED] Oggetto:Re: can you help me optimizing this query? Data: 12 luglio 2004 19:10:36 CET A:[EMAIL PROTECTED] Hi Shawn, this is the last version of my intermediate table. with this version the query is absolute speedy. I have added an index to both the columns I use for the ON clause of the left join, and I'm absolutely satisyed with the performance now. is this the correct way to optimize performance on with left join? CREATE TABLE `brani_caratteri` ( `bc_id` int(11) NOT NULL auto_increment, `bc_id_brano` int(11) NOT NULL default '0', `bc_id_carattere` int(11) NOT NULL default '0', KEY `id_genere` (`bc_id`), KEY `bc_id_brano` (`bc_id_brano`), KEY `bc_id_carattere` (`bc_id_carattere`) ) TYPE=MyISAM legend: brani means track; carattere means character; Il giorno 12/lug/04, alle 18:59, [EMAIL PROTECTED] ha scritto: Do you have all of the right indexes on your tables to assist MySQL in creating your JOIN? Show us the results of SHOW CREATE TABLE for your 3 tables, please... Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine |-+ | | Giulio | | | [EMAIL PROTECTED]| | | n.it| | || | | 07/12/2004 08:50 | | | AM | | || |-+ -- --| | | | To: MySQL List [EMAIL PROTECTED] | | cc: | | Fax to: | | Subject: can you help me optimizing this query? | -- --| Hi all, I have an audio tracks info table, let's call it Tracks; every Track can have one or more ' Character' ( it is not a genre, it is something like 'Italian' or 'International' or '80's' or 'evergreen' ) so a track can be 'International' and 'Evergreen', or 'Italian' and '70's') I have three tables to handle this: Track Character Track_Character ( the intermediate table to handle the Track-Characters connection) Now, if I want to search all tracks, let's say by title, and have for every track its data and its characters codes, I'm doing something like: select track.*, track_character.id_character from track left join track_character on track.track_id = track_character.track id where track_title = '%something%' order by track_title this seems to work, but it is VERY VERY slow, and I'm working with only 4000-5000 track records, most of them having only one track_character record. It can last up to 120 seconds if I leave the title blank, resulting on all tracks listed. Any Idea on how could I optimize this? Thanx in advance, Giulio Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052
Implementing full text searching on an existing, production database.
Hi All,
I'm currently engaged in a project to implement full text searching
of our product database. The current implementation was written for an
older version of MySQL and doesn't implement BOOLEAN MODE.
Currently, the full text search is against a de-normalised table from
fields icluding the products ID, title description, in a simple
table :
CREATE TABLE text_search
{
product_uid int(10) NOT NULL,
rank int(10) NOT NULL DEFAULT 0,
txt TEXT
}
with the full text index set up against txt.
There are several problems with this implementation, firstly the
de-normalised txt field doesn't include all information on a
product, so I would like to implement the search against the full
product table. There are approx 65,000+ products in the table, and the
index would be on 2 or 3 fields in that table. Has anyone retro-fitted
a full text index to a production database? Is there an established
strategy for doing this?
Because of the nature of our business we sell a lot of products where
the keyword is 3 letters, DVD, USB, DDR etc etc. The manual mentions
that while you can reduce the minimum number of letters, it's not a
good idea Modifying the default behavior will, in most cases, make
the search results worse. it says. Is there a better way to ensure
these search terms are included in the index?
Finally, we also carry parimetric data on our products, it would be
good to include this information in the full text search. The only way
I can think of is to create a seperate full text index on the table
storing the parimetric data, and then run the query against both
tables, mergeing the results in code.
--
-S
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trying to upload an excel file into table
Hello, I am a newbie to mysql and have one major task to accomplish in order perform my duties. I have a few excel files that have a few hundred to a thousand columns, with each column consisting of a title/category, and thousands of bits of information (decimals) under each category. What i want to do is have each column heading become a field, and all the info under that heading become information in that field. Is there a way to set up my tables so I do not have to manually type in each field name? Otherwise, it is too long and tedious to type each field name by hand. Thanks Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: trying to upload an excel file into table
You can use a programming language to read the first line of the files and create a DDL statement for you. But MySQL will not do this natively. I am not certain but I think you may be able to use Visual Basic and the excel DOM to perform the entire process. Or you can just export the data into a tab or csv or some other limited format and import using MySQL load data or mysqlimport. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 7/12/04 12:47 PM Subject: trying to upload an excel file into table Hello, I am a newbie to mysql and have one major task to accomplish in order perform my duties. I have a few excel files that have a few hundred to a thousand columns, with each column consisting of a title/category, and thousands of bits of information (decimals) under each category. What i want to do is have each column heading become a field, and all the info under that heading become information in that field. Is there a way to set up my tables so I do not have to manually type in each field name? Otherwise, it is too long and tedious to type each field name by hand. Thanks Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade to mysql 4.0 in Fedora Core 2
brpm -qa|grep mysql/b will show you what mysql packages you have installed. You probably have both 3.x and 4.x packages installed and assuming you don't have a 3.x database you want to preserve, I would suggest uninstalling the 3.x package with brpm --erase iname_of_3.x_package/i/b To determine where the files in a package are installed you can query the package via rpm. To do so simply do brpm -ql iname_of_package/i/b You can use rpm -ql to find where the 4.0 rpm installed its binaries and execute them from there. --- A. Lanza [EMAIL PROTECTED] wrote: After installing FC2 i realized that the mysql server version installed was 3.x. I wanted to install version 4.0, so i got the rpm package from mysql website and i installed it on my system. Now it seems like mysql server is not running. I get no output for the command ps -A | grep mysql. Also, when i try to connect to mysql server using mysql client, i get the following error message: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) It seems like mysql server does not automatically start when i boot up my computer... how can i start it manually? Where in the filesystem does mysql server get installed after installing the rpm? Though, when i run the Gnome Service config. utility, i can see mysql service on the list, with a checkmark, and it appears to be running... i'm so confused about this. Please help. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
difference in result using the function sum()
Hello , I hope you could help me. I have a small difference in te result of this query QUERY SELECT `an0300_polizas_desgloce`.cuenta, `an0200_polizas`.fecha, sum(`an0300_polizas_desgloce`.debito), sum(`an0300_polizas_desgloce`.credito), (sum(`an0300_polizas_desgloce`.debito)-sum(`an0300_polizas_desgloce`.credito)), an0100_cuentas.nombre FROM `an0100_cuentas` INNER JOIN `an0300_polizas_desgloce` ON (`an0100_cuentas`.cuenta = `an0300_polizas_desgloce`.cuenta)INNER JOIN `an0200_polizas` ON (`an0300_polizas_desgloce`.id_poliza = `an0200_polizas`.id_poliza) WHERE ( (`an0300_polizas_desgloce`.cuenta = '110201000') and (`an0200_polizas`.fecha like '2004-01-%') ) GROUP BY `an0300_polizas_desgloce`.cuenta ORDER BY `an0300_polizas_desgloce`.id_poliza RESULT cuenta |fecha|sum_debito | sum_credito |total | nombre 110201000 |04/01/23 |410336.6| 413758.07 |-3421.46 | HSBC As you could see, if you substract 410336.6 to 413758.07 the result is -3421.47 Why the result that mysql show me in the total column is different? - Do You Yahoo!? Yahoo! Net: La mejor conexión a internet y 25MB extra a tu correo por $100 al mes.
Re: Implementing full text searching on an existing, production database.
Keep in mind that if you create an index on multiple
fields, then all of those fields must be searched at
once. You can't index product_name, product_desc and
product_category for instance, then only search on the
product_name field using MATCHES.
If you want to bypass this (and many other
limitations, including stoplists, short words, etc)
then I would suggest indexing your data with a
seperate text indexing system like Jakarta Lucene
(http://jakarta.apache.org/lucene/docs/index.html).
Using a product like Lucene will also allow you to
implement your parametric searching MUCH easier. You
can either define additional parametric fields in you
lucene index or you could create a second one and with
the API very easily merge the searches between the
indexes.
Keep in mind that a major limitation of the mysql
fulltext engine is that it can't index more than 500
characters which could be a major drawback for your
parametric data.
The following assumes you will stick with mysql
fulltext indexes...
In order to index 3 letter words, you will need to set
min_ft_word_len in your mysql.cnf file. You probably
also want to create your own list of stopwords and use
ft_stopword_file.
If your table is large then the biggest problem you
are going to have when creating the index is that the
table will be locked while the index is being created.
If that is a problem then I would suggest that you
create a seperate table with create table as. You
will need some way of keeping track of any additions
to the base table at this point, perhaps using the
highest product_id, or if you have a modification
timestamp on your table use that.
Create the text index on the new table and test it
out. When you are satisfied that everything is
working ok then rename the new table to the old table,
and insert/update the records that have been
added/modified since you created the copy.
If you can suffer the downtime then simply create the
index on the table and wait it out.
There is very little danger in adding the text index
to a production table. Just back up your database
before you make the modifications and you should have
no problems whatsoever.
--- Stuart Grimshaw [EMAIL PROTECTED] wrote:
Hi All,
I'm currently engaged in a project to implement
full text searching
of our product database. The current implementation
was written for an
older version of MySQL and doesn't implement BOOLEAN
MODE.
Currently, the full text search is against a
de-normalised table from
fields icluding the products ID, title
description, in a simple
table :
CREATE TABLE text_search
{
product_uid int(10) NOT NULL,
rank int(10) NOT NULL DEFAULT 0,
txt TEXT
}
with the full text index set up against txt.
There are several problems with this implementation,
firstly the
de-normalised txt field doesn't include all
information on a
product, so I would like to implement the search
against the full
product table. There are approx 65,000+ products in
the table, and the
index would be on 2 or 3 fields in that table. Has
anyone retro-fitted
a full text index to a production database? Is there
an established
strategy for doing this?
Because of the nature of our business we sell a lot
of products where
the keyword is 3 letters, DVD, USB, DDR etc etc. The
manual mentions
that while you can reduce the minimum number of
letters, it's not a
good idea Modifying the default behavior will, in
most cases, make
the search results worse. it says. Is there a
better way to ensure
these search terms are included in the index?
Finally, we also carry parimetric data on our
products, it would be
good to include this information in the full text
search. The only way
I can think of is to create a seperate full text
index on the table
storing the parimetric data, and then run the query
against both
tables, mergeing the results in code.
--
-S
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ RE:Re: When is mysql 4.1.x , production? ]
5-7 more beta releases How much time is 1 beta release taking (approxiamtly) Thanks :) Original Message: From: Josh Trutwin [EMAIL PROTECTED] To: CC: [EMAIL PROTECTED] Subject: Re: When is mysql 4.1.x , production? Date: Mon Jul 12 16:41:32 GMT 2004 On Mon, 12 Jul 2004 12:05:53 + Ben David, Tomer [EMAIL PROTECTED] wrote: when is mysql 4.1.x going to be released for production? When it is ready I'd guess. :) If history is a predictor though I would expect 4.1.x to go though 5-7 more beta releases though. Help the developers out by testing it in your enviornment. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trying to upload an excel file into table
David, This sounds vaguely like a normalization issue as well. If your data consists of one or more identifying columns and the rest the columns exist data as explanatory data then you should probably think about scripting an import routine. Do you see repetitive blocks of data in the (probably) leftmost columns of your data? If you do then you should definitely consider normalizing. A table with several hundred (let alone a thousand) columns, while possible, will be rather clumsy to deal with. If I can be curious: What was the source of this data? Do you already have this data in any other format? What environment are you working in? What version of MySQL are you running? How good are your scripting skills? How much Excel experience do you have? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] rutgers.edu To: [EMAIL PROTECTED] cc: 07/12/2004 01:47 Fax to: PM Subject: trying to upload an excel file into table Hello, I am a newbie to mysql and have one major task to accomplish in order perform my duties. I have a few excel files that have a few hundred to a thousand columns, with each column consisting of a title/category, and thousands of bits of information (decimals) under each category. What i want to do is have each column heading become a field, and all the info under that heading become information in that field. Is there a way to set up my tables so I do not have to manually type in each field name? Otherwise, it is too long and tedious to type each field name by hand. Thanks Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there an easy way to find duplicate records in a table?
This query will show you a list of all Vendor, ID combinations, and how many times they appear if they appear more than once in your data: SELECT Vendor, ID, Count(1) as dupes FROM name_of_your_table_goes_here GROUP BY Vendor, ID HAVING Count(1) 1 Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Gannaway [EMAIL PROTECTED]To: [EMAIL PROTECTED] hics.comcc: Fax to: 07/10/2004 05:10 Subject: Is there an easy way to find duplicate records in a table? PM I have a table that our distributor sent us. The table doesn't have any keys. It does, however, have 7,782 duplicate records. I found this out when I tried to have MySQL make a unique product ID by combining 2 fields of each record. Here's what I need to know... Is there a MySQL command that will reveal duplicate entries in a table, and find them by looking at just 2 fields??? I'm trying to create a primary key in the new table by combining 2 fields together (Vendor and ID). Sample data format: +==+ | ImportTable | +==+ | Vendor | ID | Price | ++-+---+ | AAD| 1 | $9.98 | | AAD| 1 | $3.98 | | AAD| 52 | $9.98 | | BCD| 2 | $8.98 | | BCD| 5 | $8.98 | | CSX| 44 | $7.98 | ++-+---+ I'd like something that will identify 'AAD 1' as a duplicate, even though their Prices are different. Thanks!! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: oscommerce online offline replication
Thanks for the response well i guess a backup is just copying the DB records from one pace to another and is a solution however I need something more precise, could someone point to a good reference on mapping tables and a good programming language to execute an updating process please Andrew -Original Message- From: olinux [mailto:[EMAIL PROTECTED] Sent: 12 July 2004 08:07 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: oscommerce online offline replication I think you are looking for a backup solution. This script is awesome for automatic daily/weekly/monthly backups (and compresses them) http://members.lycos.co.uk/wipe_out/automysqlbackup/ http://sourceforge.net/projects/automysqlbackup/ To recreate the database from a backup: copy the backup to the *other* server and do the following shell mysqladmin drop db_name shell mysqladmin create db_name shell mysql db_name backup-file.sql see also: http://dev.mysql.com/doc/mysql/en/mysqldump.html olinux --- Business A2Z [EMAIL PROTECTED] wrote: Hi All This may be a longshot or it could just be the place where the answer is. I have asked something similiar before regarding this but never could a clear view of the way to proceed. Anyway, here it is. Simply I have an offline mirror of oscommerce what I woould like to do is keep the online version updated with the offline recordset (products) is there a way to do this without overwriting the tables? Ideally there are other fields from the online version I would like to keep updated offline in which case there is always an exact copy on and offline? If there is a way to do this I would appreciate any clues, links, books, and methods and programming techniques to achieve this. Andrew __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.718 / Virus Database: 474 - Release Date: 09/07/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication - multiple masters
I have 4 servers in my environment: DEPOT - master server WWW1 - web server #1 WWW2 - web server #2 WWW3 - web server #3 The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. Is configuration as simple as the correct entries in my.cnf? That is, can muliple entries for master-host, master-user... exist? Any caveats with this configuration? Question #2 - A small databases exists on the DEPOT which I would like replicated to all web servers. Is there any reason why this would not work with the above situation [DEPOT acting as a slave for multiple masters]? Many thanks, I hope I have omitted any relevant information... -- ../mk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - multiple masters
On Mon, Jul 12, 2004 at 03:49:33PM -0400, Marc Knoop wrote: I have 4 servers in my environment: DEPOT - master server WWW1 - web server #1 WWW2 - web server #2 WWW3 - web server #3 The web servers record web metrics to local mysql databases. I would like those local databases to be consolidated onto the DEPOT [as three separate DBs]. Is configuration as simple as the correct entries in my.cnf? That is, can muliple entries for master-host, master-user... exist? Any caveats with this configuration? You cannot do that. http://dev.mysql.com/books/hpmysql-excerpts/ch07.html See figure 7-2. You'd need to run 3 instances of MySQL on DEPOT, one for each WWW server you'd like to mirror. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unicode help
Could someone who has experience with handling the Unicode character set in a MySQL database please write to me? In particular, I am trying to discover how in an ASCII-limited environment one can specify non-ASCII characters; I do know their Unicode encodings, just not how to write an INSERT command in SQL that uses them. THANKS! Lisa N. Michaud, Assistant Professor of Computer Science Department of Mathematics and Computer Science, Wheaton College [EMAIL PROTECTED] http://cs.wheatoncollege.edu/lmichaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which records are not contained in 2 different tables?
You need to use 2 LEFT JOINS: SELECT a.* FROM tablea a LEFT JOIN table b ON a.id = b.a_id LEFT JOIN table c ON a.id = c.a_id WHERE b.id is null AND c.id is null and check for both joined tables to return null values. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Gannaway [EMAIL PROTECTED]To: [EMAIL PROTECTED] hics.comcc: Fax to: 07/10/2004 06:00 Subject: Which records are not contained in 2 different tables? PM I've got one huge table (table a), and two smaller tables (tables b and c) I need to find which records in 'table a' are not in 'table b' nor are they in 'table c'. The Primary Key for all 3 tables is 'ProductID'. I looked at the LEFT JOIN command in the docs, but it looks like you can only compare 1 table to 1 table. How do I do this? -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem getting innodb enabled ...
Running MySQL 4.0.15 on SuSE 9 Professional. MySQL works fine until I try to allow innodb by uncommenting the lines in the my.cnf file. Once I do this MySQLwill not start, the error message in the log is: 040712 15:25:39 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/lib/mysql/' 040712 15:25:39 mysqld ended I found one thread in the SuSE listserv where someone fixed the problem by deleting all the files and directories in /var/lib/mysql except mysql and test, but that didn't work for me. The file permissions on /var/lib/mysql are: drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 mysql and drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 . drwxr-xr-x 41 root root 1056 2004-07-12 15:12 .. drwx--2 mysqldaemon576 2004-07-12 11:12 CPIAInventory drwx--2 mysqldaemon 17976 2004-02-26 08:50 egroupware drwx--2 mysqldaemon336 2004-06-28 16:21 EmployeeLog drwxr-xr-x2 mysqldaemon 48 2004-07-12 15:22 innodb drwx--2 mysqldaemon528 2003-11-12 18:13 mysql -rw-rw1 mysqldaemon 2877 2004-07-12 15:58 mysqld.log -rw-rw1 mysqldaemon220 2004-06-29 15:56 mysqld.log-20040630.gz -rw-rw1 mysqldaemon220 2004-06-30 18:20 mysqld.log-20040701.gz -rw-rw1 mysqldaemon284 2004-07-07 18:04 mysqld.log-20040708.gz -rw-rw1 mysqldaemon223 2004-07-10 08:56 mysqld.log-20040710.gz -rw-rw1 mysqldaemon237 2004-07-11 11:55 mysqld.log-20040712.gz -rw-rw1 mysqldaemon 5 2004-07-12 15:58 mysqld.pid srwxrwxrwx1 mysqldaemon 0 2004-07-12 15:58 mysql.sock drwx--2 mysqldaemon816 2004-06-28 15:29 phpmyadmin drwx--2 mysqldaemon 48 2003-11-12 18:13 test Any ideas? (I'm also posting this to the SuSE list.) -- Hugh mailto: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query gets count wrong
You need DISTINCT to make the COUNT() function ignore duplicate values. SELECT e.e_id , e.e_code , COUNT(DISTINCT qxe.q_id) as e_count , avg(ratings.r_quality) as avqual FROM e, qxe, ratings WHERE e.e_id = qxe.e_id AND ratings.e_id = e.e_id AND ratings.q_id = qxe.q_id AND ratings.e_id = qxe.e_id GROUP BY e.e_id, e.e_code; That way you see how many different q_id's you have and not how many rows were used in the ratings calculations. It is also good form to always list all un-aggregated columns in your GROUP BY clauses. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gerald Taylor [EMAIL PROTECTED]To: t cc: [EMAIL PROTECTED] Fax to: 07/12/2004 09:42 Subject: Re: query gets count wrong AM Please respond to platypus I am an anal single query-oholic. I know I could do this in 2 queries I have a query involving several related tables and I have attempted to reduce it down to what causes not what I want results. I am attempting to fill a summary table. For each main item in this table I want to count the number of child items in a related table that point to it. This is fine. I have a third table called ratings which rates individual child items and it is the problem. The table qxe contains e_id which is a foreign key to the main table and q_id which is a foreign key to the actual child items. I had to set things up this way because a q might belong to more than one e. Right now I am not even interested in anything about the q's data I just want to count them. The ratings table is the problem because any q can have an arbitrary number of ratings. so a rating has q_id and e_id as well as another key that combines to form a multipart key. instead of the number of q_ids in the qxe table that have e_id equal to the the current e_id, I am getting as e_count the total number of ratings for that e_id which is a huge humber. I know I need another constraint but I cant figure out what it is. What constraint can I add to make this query do what I want while still being able to average the ratings. SELECT e.e_id, e.e_code, COUNT(qxe.q_id) as e_count, avg(ratings.r_quality) as avqual FROM e, qxe, ratings WHERE e.e_id = qxe.e_id AND ratings.e_id = e.e_id AND ratings.q_id = qxe.q_id AND ratings.e_id = qxe.e_id GROUP BY e.e_id; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Concurrency Question
Quoting Marvin Wright [EMAIL PROTECTED]: Hi, It suggests below to bundle transactions into one commit, at what point does this become unecessary ? For example I have 2 threads each doing 12,000 inserts in 1 commit each. Would I really gain any performance if I did these 24,000 inserts in 1 commit only ? I would not think this would make a difference, in fact you should test this, as it may be that in fact bundling 24,000 at a time is slower than 12,000 at a time. I know that things can slow down when doing too much in one transaction in Oracle for example, and InnoDB/MySQL might be similar. With regards to performance gains by bundling, this is more about not committing after one insert but committing after 10 or 100, as there will be certain performance gains here. Additionally, what performance should I be able to acheive with innodb ? My server is currently Linux RH, 2 CPU's 3.2Ghz, 4 Gig of ram and SCSI drives. I think my queires are optimised, I've had as much as 8000-9000 inserts per second but this is when I start to run into machine load issues. Am I asking to much of the machine ? Each platform/hardware/OS/etc. will have different performance than another, but I would think that you are doing pretty well at 8000-9000 inserts per second. With a dual CPU system, you could also try doing the inserts in parallel for further speed, for example in a 2nd forked or threaded job, so as to make sure the system is taking full advantage of that 2nd processor, otherwise a process that is executing serially/sequentially will just use up one CPU. When it comes to benchmarking performance tuning, make sure to establish a level as which performance is good enough ahead of time, otherwise you can spend all your time doing these things with only marginal gains. Regards, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: When is mysql 4.1.x , production?
A beta takes as long as a beta takes. That is really the nature of beta testing. As for an approximate timeline, I've heard various quotes, but most people seem to think somewhere late third quarter that the release will be marked stable. 4.1.3 is really quite stable and you should have very few problems with it. If you are developing a new product and you need features that are available only in the 4.1 release, then you are highly encouraged to test the release. By doing so you help to move the beta forward because in the unlikely event that you do find any problems you can report them and they will get resolved. --- Ben David, Tomer [EMAIL PROTECTED] wrote: 5-7 more beta releases How much time is 1 beta release taking (approxiamtly) Thanks :) Original Message: From: Josh Trutwin [EMAIL PROTECTED] To: CC: [EMAIL PROTECTED] Subject: Re: When is mysql 4.1.x , production? Date: Mon Jul 12 16:41:32 GMT 2004 On Mon, 12 Jul 2004 12:05:53 + Ben David, Tomer [EMAIL PROTECTED] wrote: when is mysql 4.1.x going to be released for production? When it is ready I'd guess. :) If history is a predictor though I would expect 4.1.x to go though 5-7 more beta releases though. Help the developers out by testing it in your enviornment. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem getting innodb enabled ...
Can you post the exact syntax from your my.cnf file? -Original Message- From: Hugh Taylor To: [EMAIL PROTECTED] Sent: 7/12/04 3:17 PM Subject: Problem getting innodb enabled ... Running MySQL 4.0.15 on SuSE 9 Professional. MySQL works fine until I try to allow innodb by uncommenting the lines in the my.cnf file. Once I do this MySQLwill not start, the error message in the log is: 040712 15:25:39 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/lib/mysql/' 040712 15:25:39 mysqld ended I found one thread in the SuSE listserv where someone fixed the problem by deleting all the files and directories in /var/lib/mysql except mysql and test, but that didn't work for me. The file permissions on /var/lib/mysql are: drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 mysql and drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 . drwxr-xr-x 41 root root 1056 2004-07-12 15:12 .. drwx--2 mysqldaemon576 2004-07-12 11:12 CPIAInventory drwx--2 mysqldaemon 17976 2004-02-26 08:50 egroupware drwx--2 mysqldaemon336 2004-06-28 16:21 EmployeeLog drwxr-xr-x2 mysqldaemon 48 2004-07-12 15:22 innodb drwx--2 mysqldaemon528 2003-11-12 18:13 mysql -rw-rw1 mysqldaemon 2877 2004-07-12 15:58 mysqld.log -rw-rw1 mysqldaemon220 2004-06-29 15:56 mysqld.log-20040630.gz -rw-rw1 mysqldaemon220 2004-06-30 18:20 mysqld.log-20040701.gz -rw-rw1 mysqldaemon284 2004-07-07 18:04 mysqld.log-20040708.gz -rw-rw1 mysqldaemon223 2004-07-10 08:56 mysqld.log-20040710.gz -rw-rw1 mysqldaemon237 2004-07-11 11:55 mysqld.log-20040712.gz -rw-rw1 mysqldaemon 5 2004-07-12 15:58 mysqld.pid srwxrwxrwx1 mysqldaemon 0 2004-07-12 15:58 mysql.sock drwx--2 mysqldaemon816 2004-06-28 15:29 phpmyadmin drwx--2 mysqldaemon 48 2003-11-12 18:13 test Any ideas? (I'm also posting this to the SuSE list.) -- Hugh mailto: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I count() on multiple tables in a single query? SOLVED
Thanks for the reply Shawn. When I ran your version, I noticed that my 'devices' column was off by a multiplier of 3 (that is, it said 9 rather than 3, or 15 rather than 5, etc.) Turns out, it just needed another DISTINCT in there. So, for those playing along at home, here is the final working query: SELECT Departments.DeptID, DeptName, COUNT(DISTINCT UserDept.CoreID) AS users, COUNT(DISTINCT IP_Addr) as devices FROM Departments LEFT JOIN IP_Dept ON Departments.DeptID = IP_Dept.DeptID LEFT JOIN UserDept ON Departments.DeptID = UserDept.DeptID GROUP BY Departments.DeptID, Departments.DeptName ORDER BY DeptName DESC; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 12, 2004 6:39 AM To: Daevid Vincent Cc: [EMAIL PROTECTED] Subject: Re: How can I count() on multiple tables in a single query? It sounds as though you don't want to see how many rows are returned for the users column but rather how many distinct names are in that column; you need a COUNT(DISTINCT). Your GROUP BY clause is also off as it does not list all of the non-aggregated columns in your SELECT statement. See if this works: SELECT Departments.DeptID , DeptName , COUNT(DISTINCT UserDept.CoreID) AS users , COUNT(IP_Addr) as devices FROM Departments LEFT JOIN IP_Dept ON Departments.DeptID = IP_Dept.DeptID LEFT JOIN UserDept ON Departments.DeptID = UserDept.DeptID GROUP BY Departments.DeptID, Departments.DeptName ORDER BY DeptName DESC; Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Using mysql v4.0.x on linux. Given three tables... CREATE TABLE Departments ( DeptID int(10) unsigned NOT NULL auto_increment, DeptName char(30) default NULL, PRIMARY KEY (DeptID) ) CREATE TABLE UserDept ( CoreID int(10) unsigned NOT NULL default '0', DeptID int(10) unsigned NOT NULL default '0', DeptAdmin char(1) default NULL, DeptEmail char(1) default NULL, DeptContact char(1) default NULL, KEY DeptID (DeptID), KEY CoreID (CoreID) ) CREATE TABLE IP_Dept ( IP_Addr int(10) unsigned NOT NULL default '0', DeptID int(10) unsigned NOT NULL default '0', UNIQUE KEY DeptIP (IP_Addr,DeptID) ) What I want is a listing of all the department names, and a tally of how many users in each, and another column with the tally of how many IPs in each I've tried various combinations of this, changing the COUNT() and GROUP BY values: SELECT Departments.DeptID, DeptName, COUNT(UserDept.CoreID) AS users, COUNT(IP_Addr) as devices FROM Departments LEFT JOIN IP_Dept on Departments.DeptID = IP_Dept.DeptID LEFT JOIN UserDept ON Departments.DeptID = UserDept.DeptID GROUP BY UserDept.CoreID, IP_Dept.IP_Addr ORDER BY DeptName DESC; But nothing is working right. Mostly what happens is both 'users' and 'devices' is the same value. Is this possible? I can do it for the first COUNT(). And then I could do a second query, but I'm trying to do this in a single query if possible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with my LEFT JOIN query...
It sounds like you are missing indexes. Please post the results of - SHOW CREATE TABLE ImportLiebermansStep3Add; - and - SHOW CREATE TABLE ProductsOld; - and we can tell you if you have enough indexes or not. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Gannaway [EMAIL PROTECTED]To: [EMAIL PROTECTED] hics.comcc: Fax to: 07/10/2004 08:18 Subject: Need help with my LEFT JOIN query... PM I'm trying to SELECT a field (ProductID) from 'Table a' WHERE two corresponding fields are equal (a.PUBLISHER = b.Vendor AND a.NUMBER = b.VIN) Table 'a' (approximately 100,000 records): ++ | ImportLiebermansStep3Add | ++ | ProductID | PUBLISHER | NUMBER | +---+---++ | ACC_4076 | ACC | 4076 | | BCD_300 | BCD | 300| | DEC_R50 | DEC | R50| | WIN_220 | WIN | 220| +---+---++ Table 'b' (approximately 20,000 records): ++ | ProductsOLD| ++ | ProductID | Vendor| VIN| +---+---++ | | ACC | 4076 | | | BCD | | | | DEC | R50| +---+---++ Here's my Query SELECT a.ProductID FROM ImportLiebermansStep3Add a LEFT JOIN ProductsOLD b ON a.PUBLISHER=b.Vendor AND a.NUMBER=b.VIN WHERE b.ProductID IS NULL; But this query runs, and runs, and runs, and runs, and runs without ever giving results. What am I missing??? Thanks! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1 performance
I've been comparing the performance of 4.1 with the MySQL 3.23.58 that came with our Rocks cluster software. I'm finding that 4.1 is running approximately 1/3 slower (e.g. a 0.075 second query goes to 0.100 seconds) than 3.23.58. Size of buffers, etc. seems to have little effect. The database is fairly large with about 3 gig spread over a half-dozen tables. The largest table has 62 million rows. Is this other's experience? If so, should we expect that after a production version of 4.1 is released? --Thomas Hickey, Chief Scientist, OCLC --614.764.6000 --mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] --http://errol.oclc.org/laf/n82-54463.html http://errol.oclc.org/laf/n82-54463.html
Re: Implementing full text searching - more questions
Keep in mind that a major limitation of the mysql fulltext engine is that it can't index more than 500 characters which could be a major drawback for your parametric data. Just some genral qiuestions, Does that mean the max. string that can be indexed and therefore searched on is 500 chars? What exactly is this limitation? The following assumes you will stick with mysql fulltext indexes... In order to index 3 letter words, you will need to set min_ft_word_len in your mysql.cnf file. You probably also want to create your own list of stopwords and use ft_stopword_file. Can I just add words or append words to the existing default stopword file? After I add words do I have to reindex the fields or restart anything? (I'm using win32) If I insert/add a record to a fulltext table do I have to redo(reindex) anything? Assuming I'm using a recent ver. of MYSQL. Thanks, just genral questions...thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solaris Performance Issue
On Mon, Jul 12, 2004 at 10:14:09AM +0800, Linda wrote: Hi Jeremy, I didn't find any resouce limit but the performace is very bad. Do you have any suggestion how to tune the solaris to provide the better performance for Solaris? Well, MySQL doesn't just randomly slow down. There's either a software or hardware cause. If you're not maxing out your memory, CPU, or disk bandwidth it's probably a software problem. And, not being a Solaris expert, I don't know where to start if you think the OS is the culprit. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Implementing full text searching - more questions
Does that mean the max. string that can be indexed and therefore searched on is 500 chars? What exactly is this limitation? I may have been wrong on this limit. I know I read about it somewhere, but I can't seem to find out where at the moment. Since the fulltext index is maintained as a seperate b-tree with each word from the record and its local weight, I am nearly certain I was wrong in making the 500 char limit assertion (though a single word is limited to that length). Can I just add words or append words to the existing default stopword file? After I add words do I have to reindex the fields or restart anything? (I'm using win32) You can't modify the default stoplist, but you can create your own using ft_stopword_file (just include the words on the default stoplist). If you change the stopwords you must rebuild your index. If I insert/add a record to a fulltext table do I have to redo(reindex) anything? Assuming I'm using a recent ver. of MYSQL. Fulltext indexes are maintained just as normal indexes when you insert/update/delete rows. They will (sometimes greatly) affect the speed of your DML operations, but no special synching is necessary to keep them up to date with your table data. Thanks, just genral questions...thanks. You are quite welcome. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto Generation Of Passwords
I was wondering if it's possible to create a field in a MySQL Databse that automatically creates unique field values for use as a password for example Michael Mason Business Support Services Arras People Tel: 01706 342310 Mobile: 07793 782287 Fax: 01706 642754 Member of the Recruitment Employment Confederation (00052055) The views expressed in this mail are entirely those of the sender, and do not necessarily represent the views or position of Arras Services Ltd. The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in relation to the contents of this information is strictly prohibited and may be unlawful. Neither the sender nor the represented institution is liable for the correct and complete transmission of the contents of this e-mail, or for its timely receipt.
Implementing full text searching - need recommendations
Please let me show you one (TEXT data-type) item example. You will see Title_[ ... Author[ ...Resp_Org__[... Obviously, I don't want those strings searchable, they are headings, but I do want the content to the right of them indexed and searchable. So looking at it - what should I put in my stop words list...eg. Author[ ? This would be a typical text field indexed by fulltext. I have never used it before - any recomendations appreciated - it's formatted below for readability. I have the actual field item below this formatted item complete with '\r\n'...it's text afterall. Thanks for the help. Title_[ Balloon Capabilities and Futures] Author[ Thomas W. Kelly Resp_Org__[ Air Force Cambridge Research Labs. FundingOrg[ Date__[ Dec 1963 Report_No_[ Found in: AFCRL-TR-63-919, AFSG No. 154 Reposit_No[ Found in: AD-614 065 ContractNo[ Descript__[ Conference Paper, 25 p Notes_[ This symposium was held in Boston, MA on 25 to 27 September 1963. Twenty papers were presented, of which 16 were published. This paper is found on pp 3-27.] Subj_Terms[ Balloon technology, tethered balloons, payload orientation, hot air balloons, balloon design, manned balloons, instrumentation] Content___[ The papers covered present load and altitude capabilities of many types of plastic balloons. Objectives of current research in balloon technology are described, including extension of present capabilities, increased reliability, and longer duration.] ElecAccess[ http://library/Databases/Balloon/Data/BT1034.02.pdf Avail_From[ WFF/BTL Title_[ Balloon Capabilities and Futures]\r\nAuthor[ Thomas W. Kelly\r\nResp_Org__[ Air Force Cambridge Research Labs.\r\nFundingOrg[\r\nDate__[ Dec 1963\r\nReport_No_[ Found in: AFCRL-TR-63-919, AFSG No. 154\r\nReposit_No[ Found in: AD-614 065\r\nContractNo[\r\nDescript__[ Conference Paper, 25 p\r\nNotes_[ This symposium was held in Boston, MA on 25 to 27 September 1963. Twenty papers were presented, of which 16 were published. This paper is found on pp 3-27.]\r\nSubj_Terms[ Balloon technology, tethered balloons, payload orientation, hot air balloons, balloon design, manned balloons, instrumentation]\r\nContent___[ The papers covered present load and altitude capabilities of many types of plastic balloons. Objectives of current research in balloon technology are described, including extension of present capabilities, increased reliability, and longer duration.]\r\nElecAccess[ http://library/Databases/Balloon/Data/BT1034.02.pdf\r\nAvail_From[ WFF/BTL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Unicode help
Could someone who has experience with handling the Unicode character set in a MySQL database please write to me? In particular, I am trying to discover how in an ASCII-limited environment one can specify non-ASCII characters; I do know their Unicode encodings, just not how to write an INSERT command in SQL that uses them. When using the mysql command line client I insert utf8 data like this: INSERT INTO your_table VALUES (CONVERT(_ucs2 0x0061 USING utf8)); if you prefer ucs2 then you don't need to convert: INSERT INTO your_table VALUES (_ucs2 0x0061); 0061 is the unicode codepoint for lowercase a. Just replace this with the codepoint(s) of the string you want to insert. You can also, of course, insert utf8 directly: _utf8 0x61, but who knows the utf8 codes beyond the basic latin characters?! If this doesn't fully answer your question just let me know, and I'll try to help more. Jeremy THANKS! Lisa N. Michaud, Assistant Professor of Computer Science Department of Mathematics and Computer Science, Wheaton College [EMAIL PROTECTED] http://cs.wheatoncollege.edu/lmichaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Can't start server
If you start if from root, please make sure to use option --user=mysql. Please check mysqld.log file and see if there is any tips. Good luck. A. Lanza [EMAIL PROTECTED] To: Philippe Poelvoorde [EMAIL PROTECTED] cc: [EMAIL PROTECTED] 2004-07-12 17:34 [EMAIL PROTECTED] Subject: Re: Fw: Can't start server || | [ ] Urgent | || || | [ ] Return Receipt | || Hi, since I installed mysql server 4.0 i cannot start mysqld daemon. I go to /usr/bin and execute mysqld, but i get the following message: [EMAIL PROTECTED] bin]# safe_mysqld Starting mysqld daemon with databases from /var/lib/mysql 040712 11:33:07 mysqld ended How can i know what's the problem? Thanks On Mon, 2004-07-12 at 09:38, Philippe Poelvoorde wrote: Hi, have you got a file : /var/lib/mysql/mysql.sock if yes and no mysql is running, remove it ? [EMAIL PROTECTED] wrote: Hmm...can someone help me on this? Many thanks. - Forwarded by Joseph S CHUNG/OGCIO/HKSARG on 2004-07-12 09:40 - Joseph S CHUNG To: [EMAIL PROTECTED] 2004-07-09 15:10 cc: Subject: Can't start server || | [ ] Urgent | || || | [ ] Return Receipt | || I install mysql 4.0 with RPM on Fedora2, which comes with mysql 3.23. When I try to start mysql server, the /var/log/mysqld.log file record the following error message: mysqld started Warning : Asked for 196608 thread stack, but got 126976 Can't start server : Bind on unix socket : Permission denied Do you already have another mysqld server running on socket : /var/lib/mysql/mysql.sock ? Aborting /usr/sbin/mysqld: Shutdown Complete mysqld ended There is no mysqld running. Any idea? Thanks, Joseph -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: oscommerce online offline replication
Check out the manual section about replication. http://dev.mysql.com/doc/mysql/en/Replication.html Also read the user comments, sometimes you will find useful code snippets and URLs. PHP or perl would be my language of choice. A PHP solution is mentioned in the user comments of the above URL. olinux --- Business A2Z [EMAIL PROTECTED] wrote: Thanks for the response well i guess a backup is just copying the DB records from one pace to another and is a solution however I need something more precise, could someone point to a good reference on mapping tables and a good programming language to execute an updating process please Andrew -Original Message- From: olinux [mailto:[EMAIL PROTECTED] Sent: 12 July 2004 08:07 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: oscommerce online offline replication I think you are looking for a backup solution. This script is awesome for automatic daily/weekly/monthly backups (and compresses them) http://members.lycos.co.uk/wipe_out/automysqlbackup/ http://sourceforge.net/projects/automysqlbackup/ To recreate the database from a backup: copy the backup to the *other* server and do the following shell mysqladmin drop db_name shell mysqladmin create db_name shell mysql db_name backup-file.sql see also: http://dev.mysql.com/doc/mysql/en/mysqldump.html olinux --- Business A2Z [EMAIL PROTECTED] wrote: Hi All This may be a longshot or it could just be the place where the answer is. I have asked something similiar before regarding this but never could a clear view of the way to proceed. Anyway, here it is. Simply I have an offline mirror of oscommerce what I woould like to do is keep the online version updated with the offline recordset (products) is there a way to do this without overwriting the tables? Ideally there are other fields from the online version I would like to keep updated offline in which case there is always an exact copy on and offline? If there is a way to do this I would appreciate any clues, links, books, and methods and programming techniques to achieve this. Andrew __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure Backup
Hi, How do we take the backup of Stored Procedures in MySQL? And, how can we restore them back? I saw that proc.myd and proc.myi are created within the MySQL Database. Copying them could be one option. Is there any built-in way to achieve the same? Thanks, Nawal Lodha
