mysql problems
Hello guys Here is the problem. You may find it trivial, but it is a serious obstacle for me. I make a database, a table in mysql and it goes fine. Then I try to view my table with PHP. I am pretty sure that I can't connect to the database. In particular $db = mysql_connect(localhost, root, ) or die; statement doesn't seem to be valid. Should I use another username, password or both? Are there any suggestions? Trying to solve the previous problem I face a new one: My new username is not anymore root, but rootlocalhost. This happened during a phonecall assistant by a friend. How did this happen? How can I generally change my username? If the previous explanation is not clear all I mean is: when I try to access mysql the: mysql -u root command is not valid. Instead I get this message: access denied for user: 'rootlocalhost' (Using password: NO). Thanks for the cooperation
Can't run mysql after installation
Dear All, I met with this problem when I tried to run mysql: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I have already installed server, client, devel, and need to install bench. Thank you for suggestions! Jack Jack Chen, Stein Lab, Cold Spring Harbor Labs 1 Bungtown Road, Cold Spring Harbor, NY, 11724 Tel: 1 516 3676904; e-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
safe_mysqld fails. Can't read .err log
Hi, I am having a problem with Mysql starting. It has been fine for 2 years. I am invoking safe_mysqld - This is issuing the start of the databases in the appropriate directory but I then receive the message that the mysql daemone ends. I have tried to read the .err log which exists in the database directory but am unable to read it. Is there a trick to opening the error log .err file. Appreciate help. yes i do. Thanks, Melissa -- __ Download the FREE Opera browser at www.opera.com/download/ Free OperaMail at http://www.operamail.com/ Powered by Outblaze - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: The request properties can not be supported by this ODBC Driver.
SQL ASP I can't seem to find any examples of saving text area form inputs to TEXT type fields. They all seem to be text inputs or strings being saved to varchar fields... Can someone point me to an example of saving large text quantities to a TEXT Type field in ASP? Thanks Alan McDonald http://www.meta.com.au - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Same syntax on MySQL and Microsoft SQL Server
A few months ago I tried to use MySQL as an alternative database backend for our software product. Currently we are using MS Access and MS SQL Server. We encountered that MySQL (or MyODBC) uses different quoting characters for legal names and strings. At present we are using square brackets for names (like [my table], [my column] or [my table].[my column]) and inverted commas for strings (like mytxt = 'text'). Is there an option for MySQL (or MyODBC) which makes basic SQL of these databases compatible? Thanks Christian __ Gesendet von Yahoo! Mail - http://mail.yahoo.de Möchten Sie mit einem Gruß antworten? http://grusskarten.yahoo.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: using mysql successfully for 2 years-now getting connection error
Hi Melissa, /tmp/mysql/sock ? Are you sure you do not mean: /tmp/mysql.sock ? At any rate, it is a UNIX domain socket, and is supposed to be empty (it is basically a socket in the filesystem). If you do a ls -la /tmp/mysql.sock, it will give you something like this: srwxrwxrwx 1 mysql wheel0 Oct 8 23:12 mysql.sock But maybe you really just made a type in the socket dir. :) - Mark System Administrator Asarian-host.org --- If you were supposed to understand it, we wouldn't call it code. - FedEx - Original Message - From: melissa allman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 21, 2002 6:01 PM Subject: using mysql successfully for 2 years-now getting connection error Hi, I have been running MYSQL without a problem for 2 years. Today I received a message as follows: ERROR 2002: Can't connect to local MYSQL server through socket /tmp/mysql/sock(111) Did some preliminary search on message on MYSQL.COM site. This file does exist on server but appears to be empty. I tried to find documentation on what mysql.sock should contain. Can anyone direct me to what mysql.sock's config should be. I do have backup and that wil be my next step. I think somehow this got corrupted but am only trying to get the server back up at this point. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What is relay-bin log?
Hi! Chung == Chung Ha-nyung [EMAIL PROTECTED] writes: Chung It is seems that RESET SLAVE query sholud not be done while Chung replication is working. Slave forget its replication position and cannot Chung continue replication since it tries to do the queries already executed. Chung Do I need to do SLAVE STOP before RESET SLAVE? Or any Chung other means to delete replication logs during replication? Which log files is it that you want to delete ? (Please give an example for this) If you have a problem that old relay logs are not deleted, try to upgrade to MySQL 4.0.4 ; We recently fixed a bug in this code. If you want to delete all old binary log files, then the command is RESET MASTER. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: why so late with Mac OS X versions?
From: Ray Kiddy [EMAIL PROTECTED] Can someone tell me what the holdup is in getting binaries built for Mac OS X? Uhm, volunteer labor? Marc Liyanage has been doing MacOS X binaries in Switzerland. When I lived there, they took month-long vacations, two-hour lunches, and frowned on weekend work. Maybe he has a life outside of MySQL? :-) (According to an article I saw in a recent issue of Scientific American, the Swiss are also measurably happier than Americans. Wonder why? :-) Have you tried compiling it yourself? That would be the UNIX way of doing things... SQL SQL SQL SQL SQL SQL SQL SQL -- : Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Newsletters now on-line at http://www.Bytesmiths.com/Newsletter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Same syntax on MySQL and Microsoft SQL Server
We encountered that MySQL (or MyODBC) uses different quoting characters for legal names and strings. At present we are using square brackets for names (like [my table], [my column] or [my table].[my column]) and inverted commas for strings (like mytxt = 'text'). Is there an option for MySQL (or MyODBC) which makes basic SQL of these databases compatible? BSJ Well, let's put it this way : tables names between BSJ square brackets only exists in Microsoft! Oracle, BSJ Informix, Sybase, DB2 and all others aren't using BSJ square brackets to specify columns and table names! BSJ So SQL is pretty standard here! That might be right, but this is how Microsoft works. I think it would be a big advantage if it is possible to use mySQL instead of MS SQL Server not only at the startup of a new project. It's so nice to have instruments like MyODBC, why break here? Quoting like Micrsofot should not become standard, but an option. And I think, I am not the only one who will like it. Christian __ Gesendet von Yahoo! Mail - http://mail.yahoo.de Möchten Sie mit einem Gruß antworten? http://grusskarten.yahoo.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SHOW question
Greetings... In the show tablesif there is no database selected in the Server...it will give an error that no database is selected. In the second query...it will always return a resultsetmaybe an empty resultset if there are no fields in the table. hope it helps. Insane SQLyog - The Definitive Win32 GUI for MySQL http://www.sqlyog.com - Original Message - From: Fabio Checconi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 21, 2002 8:36 PM Subject: SHOW question I'm writing a program using the C API. When I do a query SHOW TABLES am I sure that on every installation it will return a result set of one column containing the tables name, or there are systems where that's not true ? Am I sure that from a SHOW FIELDS FROM table i'll get a result set with the column name in the first field and the type in the second (with other info on other fields) ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SHOW question
I'm writing a program using the C API. When I do a query SHOW TABLES am I sure that on every installation it will return a result set of one column containing the tables name, or there are systems where that's not true ? Am I sure that from a SHOW FIELDS FROM table i'll get a result set with the column name in the first field and the type in the second (with other info on other fields) ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how do you define a relationship?
Jan Steinman wrote: My motto: Generalize for the norm; specialize for the exception. My motto: Do it right, then optimize it later if it sucks. Keep a single director field, with a second NULL FULLTEXT field for additional directors. The NULL test for the normal case is much cheaper than what you have to go through by assuming that ALL movies have multiple directors! I don't know that that's true; I'd be fairly confident that a double-JOIN to get to the director would be almost equally fast to the single-JOIN in fact, since as you point out there will only usually be one entry in the MovieDirector table's index for each movie. This is how books are handled in large databases. Each has a primary author, and may contain secondary authors. There is no assumption that large numbers of books have multiple, equal authors. That may be true in some cases, however, the primary author may simply have to be the one first in alphabetical order, since I own quite a few books written by multiple, equal parties. This way, you can easily and conveniently list multiples as Speilberg (et. al.) without doing joins. An interested browser can then go further to find out who the others are. In the other case, each request for a director requires a JOIN. In one case, they require a single join, in the other, a double-join. However, in the other case, searching by director is easier (since choosing the non-primary director will still return the correct results from a single query, although if you're willing to completely sideline this option, doing a LIKE query against the additional field is almost acceptable). it's just a general-purpose movies database, why bog the whole thing down just to suit a few exceptional cases? I hate to claim that something 'bogs something down' without proof ... I'm going to go off and do some timings ... :) -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Strange behavior of MySQL 3.23.51
Hello.
On Mon 2002-10-21 at 18:42:05 +0200, [EMAIL PROTECTED] wrote:
[...]
I think it's an error in MySQL. Why else this different
behaviour with and without unique index?
IMHO it's different issue.
IMHO, it's the main issue. Adding or removing an index, even an unique
one may not change the result of a SELECT. So he found a malfunction
with his setting.
To ponder about GROUP BY or not, while he has a malfunctioning mysqld,
only takes focus from real the issue. When the malfunction is
rectified, one can look into the query (which IMHO should work as it
is).
Stefan, I think the problem is that the optimizer makes a wrong guess,
related to UNIQUE index. Could you please try with the newest 3.23.x
version and if it is still reproducable, report it to
[EMAIL PROTECTED] (or even better, use mysqlbug).
Regards,
Benjamin.
PS: People, please trim the quotes in your replies. It really does not
make any sense to quote the list footer several times.
[...]
- Original Message -
From: [EMAIL PROTECTED]
To: [MYSQL] [EMAIL PROTECTED]
Sent: Monday, October 21, 2002 5:46 PM
Subject: Strange behavior of MySQL 3.23.51
I have a database structure as follows (simplyfied) :
CREATE TABLE masterTABLE (
pid int(11) unsigned NOT NULL default '0',
c1id int(11) unsigned default NULL,
c2id int(11) unsigned default NULL,
value int(11) unsigned NOT NULL default '0',
UNIQUE KEY pid2 (pid,c1id,c2id),
UNIQUE KEY pid (pid,value)
) TYPE=MyISAM;
INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
CREATE TABLE childTABLE1 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE1 VALUES (1, 'Yes');
INSERT INTO childTABLE1 VALUES (2, 'No');
INSERT INTO childTABLE1 VALUES (4, 'Yes');
INSERT INTO childTABLE1 VALUES (5, 'No');
CREATE TABLE childTABLE2 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE2 VALUES (3, 'Yes');
If I do this query:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
the Result will be 5 which is probably wrong.
The expected Result ist 4.
The correct Result will be returned if you remove
both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
[...]
--
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 3.23.52 source code .... where?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 21 October 2002 20:55, Wim van den Berge wrote: Can anyone tell me where I can find the source code for 3.23.52? I looked on source forge and there is a section for 3.23.52, but there is no source code for 3.23.52 in it (No Files). The latest it seems to have is 3.23.51. Try http://www.mysql.com/Downloads/MySQL-3.23/mysql-3.23.52.tar.gz or even better http://www.mysql.com/Downloads/MySQL-3.23/mysql-3.23.53.tar.gz If you want to be on the bleeding edge, see http://www.mysql.com/doc/en/Installing_source_tree.html for instructions on how to obtain the most current source tree. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) iD8DBQE9tFhqSVDhKrJykfIRApZAAJ43rmLcphoJR1vve0NnwN4uAco7NACfYfUi SMdGpetN+3os3sdg4qx5gvE= =Utqz -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
using mysql successfully for 2 years-now getting connection error
Hi, I have been running MYSQL without a problem for 2 years. Today I received a message as follows: ERROR 2002: Can't connect to local MYSQL server through socket /tmp/mysql/sock(111) Did some preliminary search on message on MYSQL.COM site. This file does exist on server but appears to be empty. I tried to find documentation on what mysql.sock should contain. Can anyone direct me to what mysql.sock's config should be. I do have backup and that will be my next step. I think somehow this got corrupted but am only trying to get the server back up at this point. Can't get the daemon to run-Starting MYSQL but it keeps ending. Appreciate whatever you have. Thanks, Melissa -- __ Download the FREE Opera browser at www.opera.com/download/ Free OperaMail at http://www.operamail.com/ Powered by Outblaze - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Static linking
Hi, I tryed to link mysqld static but its give me some strange reports: its say that i have no enogth memory! mysqld is version 3.23.52 Other problem is when i use --prefix in configure script basedir is set to //va/ - thats very strange. TNX Dishko Georgiev - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem upgrading
I believe you need to move the socket which defaults to /tmp. If you move it to /var/run or /var/tmp, you should be good to go. I think you can either set the location at compile time or in the config files. Thanks for the response! I have /tmp symlinked to /var/tmp, and /var is mounted writable and it still doesn't work. Hmm. I got out truss and it seems that mysql is trying to do access(/, 0x02) which is failing. There's no need for this program to write to root so why is it trying to do so, and how do I make it stop? Thanks, Lance Jerry - Original Message - From: Lance Uyehara [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 18, 2002 7:30 PM Subject: problem upgrading I was running mysql 3.23.26 on freebsd 4.6 without a problem. Then I had a runaway high load cpu event and figured I should at least get later code before complaining. So I grabbed 3.23.52 (the latest freebsd package). My databases are in /var/db/mysql. My root partition is mounted readonly. When I run: /usr/local/bin/safe_mysqld --user=mysql --skip-networking /dev/null 21 I get the following error: Fatal error: Please read Security section of the manual to find out how to run mysqld as root! When I do mount -u / everything runs just fine. So my question is what do I need to do to run mysqld with the / partition mounted read only? Thanks for the help, Lance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Automatic rounding in INTERVAL calculations
Description: When attempting to select rows periodically (as in scheduling) the INTERVAL function will round the parts of an expression before doing the calculation of the expression. For example: now() - INTERVAL 0.1*60 MINUTE == now() The correct result would be a difference of six minutes! While this can be corrected if manually entering values, if the 0.1 in the above example comes from a table column you cannot. Yes, the system could have been designed to operate in non-fractional minutes but hours were more convenient. How-To-Repeat: select now(), now() - interval (0.1*60) minute; Fix: Perhaps change the order of operations within the INTERVAL processing so that a fractional formula is calculated BEFORE rounding the values in the formula. Submitter-Id: n/a Originator:Andrew Smith Organization: Technotopia MySQL support: none Synopsis: interval calculation problem Severity: serious Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.38 (Source distribution) Server: /esp/mysql/bin/mysqladmin Ver 8.20 Distrib 3.23.38, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.38-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 9 hours 42 min 40 sec Threads: 5 Questions: 443957 Slow queries: 0 Opens: 101 Flush tables: 1 Open tables: 64 Queries per second avg: 12.699 Environment: System: Linux bugs.esponsive.com 2.4.3-6smp #1 SMP Wed May 16 04:29:16 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-85) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 May 9 15:12 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 5734740 Apr 2 2002 /lib/libc-2.2.4.so -rw-r--r--1 root root 27332668 Apr 2 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 2 2002 /usr/lib/libc.so Configure command: ./configure --prefix=/esp/mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how do you define a relationship?
Unfortunately this goes well beyond the scope of my original question. Although the inputs that you all have provided thus far have been a very good read, I still don't grasp the concept of defining a relationship. Is it simply referring to a primary key in a field other than the one where the key was defined? Or is there some other way to define a key that I don't see? All I'm looking for is a simple example, syntax excerpt, etc...that shows me (only the slow learner level) exactly how to define a relationship. -Original Message- From: Jan Steinman [mailto:Jan;Bytesmiths.com] Sent: Monday, October 21, 2002 12:07 PM To: [EMAIL PROTECTED] Subject: RE: how do you define a relationship? From: Arthur Fuller [EMAIL PROTECTED] Unfortunately that oversimplifies the situation. A least a few movies have more than one director. My motto: Generalize for the norm; specialize for the exception. The vast majority of movies have but one director. And I suspect that movies with multiple directors have but one who is primary. Keep a single director field, with a second NULL FULLTEXT field for additional directors. The NULL test for the normal case is much cheaper than what you have to go through by assuming that ALL movies have multiple directors! This is how books are handled in large databases. Each has a primary author, and may contain secondary authors. There is no assumption that large numbers of books have multiple, equal authors. This way, you can easily and conveniently list multiples as Speilberg (et. al.) without doing joins. An interested browser can then go further to find out who the others are. In the other case, each request for a director requires a JOIN. Of course, your particular application may be director-centric, like if you're building a special database to support research on directors. But if it's just a general-purpose movies database, why bog the whole thing down just to suit a few exceptional cases? SQL SQL SQL SQL SQL SQL SQL SQL -- : Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Newsletters now on-line at http://www.Bytesmiths.com/Newsletter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Test for table lock
On 21-Oct-2002 Mailing Lists wrote: Hi, Using MySQL, perl and DBI, is there a way to test to see if a table is locked (as in LOCK table...). We batch process our updates every 3 hours or so (it's not a set schedule though, it depends when a certain level of new updates is reached), and our main tables are locked during this time for around 10 minutes or so. I'd like to be able to modify our search and display scripts to inform the users to come back later while the tables are locked. Take a look at user level locking: GET_LOCK() and RELEASE_LOCK() Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What is relay-bin log?
Hi! Chung == Chung Ha-nyung [EMAIL PROTECTED] writes: Chung If all works correctly, can I delete replication logs by RESET SLAVE Chung safely? Chung I'd like to configure two servers with two-master scheme, each is the Chung master of Chung another. relay logs should automaticly be deleted and you should never have to do RESET SLAVE to delete them. RESET SLAVE should only be used when you want the slave to not be a part of the replication anymore. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: using mysql successfully for 2 years-now getting connection error
Hi, In /etc/my.cnf you must put the right path to this file. E.g. [mysqld] datadir=/var/lib/mysql socket=/tmp/mysql.sock . Or if you don't want to make changes int my.cnf file you can connect using : mysql -S /tmp/mysql.sock Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: melissa allman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 21, 2002 6:01 PM Subject: using mysql successfully for 2 years-now getting connection error Hi, I have been running MYSQL without a problem for 2 years. Today I received a message as follows: ERROR 2002: Can't connect to local MYSQL server through socket /tmp/mysql/sock(111) Did some preliminary search on message on MYSQL.COM site. This file does exist on server but appears to be empty. I tried to find documentation on what mysql.sock should contain. Can anyone direct me to what mysql.sock's config should be. I do have backup and that will be my next step. I think somehow this got corrupted but am only trying to get the server back up at this point. Can't get the daemon to run-Starting MYSQL but it keeps ending. Appreciate whatever you have. Thanks, Melissa -- __ Download the FREE Opera browser at www.opera.com/download/ Free OperaMail at http://www.operamail.com/ Powered by Outblaze - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp field in the InnoDB table
Devi, As I understand you need to update timestamp field in some tables. And you have only 4-5 milliseconds for it? Is it correct? What is the problem? Best regards, Mikhail. - Original Message - From: Devi Annisetty [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 3:08 PM Subject: RE: Timestamp field in the InnoDB table Hi, This is almost my requirement.I have to update the exact timestamps(most critical for me) and I will have only 4- 5 mts available to update different tables. So how can I do that with out much time difference. I am new to MYSQL. Any help would be greatly appreciated. Thanks Devi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Compiling mysql with correct libraries
On Mon, 21 Oct 2002 09:51:41 +0200 Iago Sineiro [EMAIL PROTECTED] wrote: Hi all. I want to compile MySQL statically with my own UDF and InnoDB support in one box and with make_binary_distribution install it in a RedHat 8.0 box. Which are the correct versions for gcc and glibc? And automake, autoconfig, and any another posible package needed for compile MySQL? Which is the best version of RedHat for compiling MySQL? Also which are the options used in compilation when is created MySQL-3.23.x-MAX.rpm? Thanks in advance. Iago. HI! This is a difficult question, as we learned recently that there are some problems with MySQL and glibc supplied with RH 8.0. You can use gcc 3.2, automake 1.5 and autoconf 2.54. The options used in creating our 3.23 RPM are described in our manusl. We currently build our RPM's on updated RH 6.2. -- Regards, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange problem with MySql IIS ASP
Hi All! I've Select like this: SELECT ExpDepDate, ActArrDate FROM oceandata It works beautifully But When I modify it like this SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata I'll get nothing, just a empty screen with a browser! I'm using Ultradev to create this and when I test it in Ultradev it works good, same in MySQL CC Admin. But not on IIS? ASP look like this: %@LANGUAGE=VBSCRIPT% !--#include file=../../Connections/connMyIFDData.asp -- % set Recordset1 = Server.CreateObject(ADODB.Recordset) Recordset1.ActiveConnection = MM_connMyIFDData_STRING Recordset1.Source = SELECT ExpDepDate, ActArrDate, Count(Ref) as NoOfRefs FROM oceandata Recordset1.CursorType = 0 Recordset1.CursorLocation = 2 Recordset1.LockType = 3 Recordset1.Open() Recordset1_numRows = 0 % % Dim Repeat1__numRows Repeat1__numRows = 10 Dim Repeat1__index Repeat1__index = 0 Recordset1_numRows = Recordset1_numRows + Repeat1__numRows % html head titleUntitled Document/title meta http-equiv=Content-Type content=text/html; charset=iso-8859-1 /head body bgcolor=#FF text=#00 table width=100% border=0 % While ((Repeat1__numRows 0) AND (NOT Recordset1.EOF)) % tr td%=(Recordset1.Fields.Item(ExpDepDate).Value)%/td td%=(Recordset1.Fields.Item(ActArrDate).Value)%/td td%=(Recordset1.Fields.Item(Act_Est).Value)%/td /tr % Repeat1__index=Repeat1__index+1 Repeat1__numRows=Repeat1__numRows-1 Recordset1.MoveNext() Wend % /table /body /html % Recordset1.Close() % Any help will be helpfull Regards Juha Mörsky MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: why so late with Mac OS X versions?
Ed Carp wrote: Marc Liyanage has been doing MacOS X binaries in Switzerland. When I lived there, they took month-long vacations, two-hour lunches, and frowned on weekend work. Maybe he has a life outside of MySQL? :-) How do I get a job in Switzerland? ;) Funny, that's exactly what I was thinking. Oh, and like you said: SQL, Query. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ENABLE KEYS
mysqlalter table a_log ENABLE KEYS; ERROR 1064: You have an error in your SQL syntax near 'ENABLE KEYS' at line 1 What version are you running, I think I read ENABLE/DISABLE KEYS just work in 4.0. STIBS - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: normalization question
Hi! I don't understand problem. Car can belong to only one Dealer, and one Dealer can have many Cars, therefore DealerID should be in Car table. Also Car can be only one Model, but there are many Cars the same Model. It looks quite normal to put ModelID into Car table, and I don't see any possible problems. Of course if there are any special requirements for the database, they have to be incorporated into the model. Am I missing your point? Artem -Original Message- From: [EMAIL PROTECTED] [mailto:speters;metromls.com] Sent: Monday, October 21, 2002 5:43 PM To: [EMAIL PROTECTED] Subject: normalization question I'm working on a design for a database that is leaving me with some normalization issues that I havent had to deal with before. For example, if im trying to store information about car dealerships I end up with the following situation: I want to store the cars in stock at each physical dealership, with certain attributes. So, I have a table Dealership as follows: Dealer_ID Name Address Etc. Then there is a Car_Make table (for things like Chevy, Hyundai): Car_Make_ID Make_Name ... And a Model table (for things like Elantra, Neon, etc): Model: Model_ID Car_Make_ID Model_Name ... And a table to describe the cars themselves: (one row per car) Car: VIN_number(vehicle identification number, what the DMV uses) Model_ID other attributes ... Now, to associate a specific car with a dealer, I want to put Dealer_ID in the Cars table, but then the Cars table has multiple parent tables, specifically Model Dealership I'm sure that this is far from normal, and will lead to programming problems, but i dont see what to do about it. Any advice would be greatly appreciated. thanks, sean peters [EMAIL PROTECTED] sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ERD, Scripts, and Reverse Engineering
I work with DeZign as well and chose it for basically the same reason as you. I particularly love the fact that it supports domains. I have been communicating with the author and encouraging him to add specific InnoDB table types. I also suggested another feature to him that he really liked; we just have to come up with the right name for it. It's like a Domain except that it contains multiple columns, each defined like a Domain; for the moment I call it a meta-domain. For example, a meta-domain called CreditCardInfo would contain columns for CardNumber, CardHolder, ExpiryDate, DVV number and so on. Then you could add these columns to a table simply by adding the CreditCardInfo meta-domain to a table. Rick is considering it for inclusion in his next release. One other product I use is called dbScripter, from dkgAdvancedSolutions.com. It generates create and drop scripts as well as populate scripts for Oracle, Access and MySQL databases, using syntax profiles. I wrote a profile for MySQL that will be included in the next release. It costs around $95 US. An important feature that competitors do not have is the ability to create index scripts. Arthur -Original Message- From: Andy Blackshaw [mailto:support;getme.co.uk] Sent: Monday, October 21, 2002 8:16 AM To: [EMAIL PROTECTED] Subject: Re: ERD, Scripts, and Reverse Engineering I did the same seach and evaluation a couple of weeks back. Ended up buying Dezign for Databases, partly on cost (compared to Case Studio which seems the most comprehensive product), but also because the evaluation version allowed enough tables/releationships to do a realistic eval + good documentation. Reverse engineering is possible if you buy the additional Importerscripts, or ImporterMysql, and you can define your own logical types (called domains) or additional Mysql types if the set is not complete. As far as I can see there is no way to define the table type, but the rest is covered. So far I'm finding it stable and very easy to work with. Andy On 20 Oct 2002 at 19:07, Randy Garland wrote: Hello, all: I'm looking for a well-designed Windows-based database design tool that: 1) (Most importantly) Supports MySQL 4.x: a) supports all of its datatypes, including MEDIUMINT, ENUM, BLOB, AND TEXT b) lets the user decide between table types (ISAM v. InnoDB v BDB) 2) Creates ERDs (Entity Relationship Diagrams) with standard 'crow's feet' notation 3) From the ERDs, generates 'load' and 'drop' sql scripts 4) Can reverse engineer an existing MySQL database, creating an ERD from the database 5) Is, preferably, free or cheap I've looked and looked. I'm currently evaluating three products: 1) Dezign for Databases (out of the Netherlands) 2) CASE Studio version 2.10.1, by CHARONWARE (www.casestudio.com) 3) Database Design Studio, by chillisource (www.chillisource.com) All of them are commercial, and I have reservations about each one. None is MySQL-specific (they cover many db's), and as such, don't really make it easy for the user to easily define MySQL-specific data types. Also, only CASE includes Reverse Engineering. CASE is big bucks ($325), and the others are in the $100 range. I just don't want to have to create my ERDs in Visio, document the details in Excel, manually create scripts, and then have to update all three when the db needs change. Is there one, good, unified tool for MySQL that can help? Thanks in advance, Randy Garland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Compiling mysql with correct libraries
builds with these compilers have been tested extensively. We would suggest, however, that you stay away from gcc 3.0 series until we have had some time to do some more extensive testing. Speaking of which, how /is/ testing of gcc 3.x going? Is this an active issue for the new MySQL major releases? -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
normalization question
I'm working on a design for a database that is leaving me with some normalization issues that I havent had to deal with before. For example, if im trying to store information about car dealerships I end up with the following situation: I want to store the cars in stock at each physical dealership, with certain attributes. So, I have a table Dealership as follows: Dealer_ID Name Address Etc. Then there is a Car_Make table (for things like Chevy, Hyundai): Car_Make_ID Make_Name ... And a Model table (for things like Elantra, Neon, etc): Model: Model_ID Car_Make_ID Model_Name ... And a table to describe the cars themselves: (one row per car) Car: VIN_number(vehicle identification number, what the DMV uses) Model_ID other attributes ... Now, to associate a specific car with a dealer, I want to put Dealer_ID in the Cars table, but then the Cars table has multiple parent tables, specifically Model Dealership I'm sure that this is far from normal, and will lead to programming problems, but i dont see what to do about it. Any advice would be greatly appreciated. thanks, sean peters [EMAIL PROTECTED] sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
3.23.52 source code .... where?
Can anyone tell me where I can find the source code for 3.23.52? I looked on source forge and there is a section for 3.23.52, but there is no source code for 3.23.52 in it (No Files). The latest it seems to have is 3.23.51. Thanks, Wim sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RAM needed for a mysql server
Hi. I asked this question last week and nobody answered. Please someone give me a hint. I need to buy a new server that must handle 500 concurrent connections to a mysql server and I don't know how many RAM should have. I've been looking to the process list but I'm clueless about how much memory is shared. Any advice ? Here is the output of VMonitor: ## PID UIDSize Share VSize Rss TTY St Command 1 23902 mysql 21M 2.2M 71M 21M S mysqld 2 23904 mysql 21M 2.2M 71M 21M S mysqld 3 23905 mysql 21M 2.2M 71M 21M S mysqld 4 23906 mysql 21M 2.2M 71M 21M S mysqld Does this mean every connection uses 21M of memory, 2.2 MB shared with the rest of mysql clients ? Thank you very much. -- frankie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL - ERROR 2003: Can't connect
Clive Grigg wrote: Hi. I am new to this and can't get started because I receive the error message: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061). o/s : WindowsXP. Any ideas? Thanks, Clive. Hi, I don't use MySQL on Windows so I can't really help you; however, have you checked the manual ? This page: http://www.mysql.com/doc/en/Can_not_connect_to_server.html may be useful, there are some comments on error 2003 from users at the bottom of this page. Hope this helps -- Joseph Bueno - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql socket problem in win98 (thanks for the help!)
First of all, thanks to everyone for no reply and no help! If someone is interested, I've found that i Win9x systems, if there are persistent connections open, the system instead of used the same opened connection for the same session, it opens a new connection for each script call, and, because it can't close persistent connections, (the port has always estabilished connection), it ever opens new port connection to the 3306 (Mysql server), while it reachs the open port limit. So, it's impossible to make new connections with servers. Ciao __ Mio Yahoo!: personalizza Yahoo! come piace a te http://it.yahoo.com/mail_it/foot/?http://it.my.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: safe_mysqld fails. Can't read .err log
Hi, Go to in the /etc/init.d and just type : mysqld start It should be no problem to open the err.log file. Try: more err.log Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: melissa allman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 21, 2002 7:41 PM Subject: safe_mysqld fails. Can't read .err log Hi, I am having a problem with Mysql starting. It has been fine for 2 years. I am invoking safe_mysqld - This is issuing the start of the databases in the appropriate directory but I then receive the message that the mysql daemone ends. I have tried to read the .err log which exists in the database directory but am unable to read it. Is there a trick to opening the error log .err file. Appreciate help. yes i do. Thanks, Melissa -- __ Download the FREE Opera browser at www.opera.com/download/ Free OperaMail at http://www.operamail.com/ Powered by Outblaze - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using auto-increment like sequence
Mike Hillyer wrote: two rows simultaneously was nil. So, that may work for you as long as each user is logged in separately, and you can accept putting a field in your row referring to the row creator. FWIW, you could always grab the current PID of your process and make your primary key 'id, pid'. Then you just have to change any related _SQL_ :-) tables to also have 'foreign_id, foreign_pid'. If, in fact, the userid won't get used twice simultaneously, then your solution is also workable. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Strange behavior of MySQL 3.23.51
And what about the WHERE-Clause??
It is not used in Statements with Aggregat functions? ...
cool answer ... but I really can't think so ...
-Ursprüngliche Nachricht-
Von: Mikhail Entaltsev [mailto:mike_lynx;smtp.ru]
Gesendet: Montag, 21. Oktober 2002 18:42
An: [EMAIL PROTECTED]; [MYSQL]
Cc: [EMAIL PROTECTED]
Betreff: Re: Strange behavior of MySQL 3.23.51
http://www.mysql.com/doc/en/Group_by_functions.html
...If you use a group function in a statement containing no GROUP BY
clause, it is equivalent to grouping on all rows...
So when you are asking about SELECT MAX( value ) FROM
masterTABLE AS m ...
without GROUP BY clause,
MySQL is looking MAX(value) through whole table.
It explains also why there is no syntax error.
I think it's an error in MySQL. Why else this different
behaviour with and without unique index?
IMHO it's different issue.
Best regards,
Mikhail.
- Original Message -
From: [EMAIL PROTECTED]
To: [MYSQL] [EMAIL PROTECTED]
Sent: Monday, October 21, 2002 6:21 PM
Subject: AW: Strange behavior of MySQL 3.23.51
Hmmmnn ... I don't think so ... COUNT() is also an
aggregate function and a SELECT COUNT(*) FROM table
should work on every database without grouping.
I think it's an error in MySQL. Why else this different
behaviour with and without unique index?
-Ursprüngliche Nachricht-
Von: Mikhail Entaltsev [mailto:mike_lynx;smtp.ru]
Gesendet: Montag, 21. Oktober 2002 18:01
An: [EMAIL PROTECTED]; [MYSQL]
Betreff: Re: Strange behavior of MySQL 3.23.51
Stephan,
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
you are using aggregate function. So you need to use group
by clause.
Just add at the end group by m.pid:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
GROUP BY m.pid
Best regards,
Mikhail.
P.S. But actually it is strange that MySQl didn't report syntax
error there.
:(
- Original Message -
From: [EMAIL PROTECTED]
To: [MYSQL] [EMAIL PROTECTED]
Sent: Monday, October 21, 2002 5:46 PM
Subject: Strange behavior of MySQL 3.23.51
I have a database structure as follows (simplyfied) :
CREATE TABLE masterTABLE (
pid int(11) unsigned NOT NULL default '0',
c1id int(11) unsigned default NULL,
c2id int(11) unsigned default NULL,
value int(11) unsigned NOT NULL default '0',
UNIQUE KEY pid2 (pid,c1id,c2id),
UNIQUE KEY pid (pid,value)
) TYPE=MyISAM;
INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
CREATE TABLE childTABLE1 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE1 VALUES (1, 'Yes');
INSERT INTO childTABLE1 VALUES (2, 'No');
INSERT INTO childTABLE1 VALUES (4, 'Yes');
INSERT INTO childTABLE1 VALUES (5, 'No');
CREATE TABLE childTABLE2 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE2 VALUES (3, 'Yes');
If I do this query:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
the Result will be 5 which is probably wrong.
The expected Result ist 4.
The correct Result will be returned if you remove
both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
--
Stephan Skusa mailto:stephan.skusa;lippe-net.de
Lippe-Net Online-Service http://www.lippe-net.de
Herforder Strasse 309 tel.: +49 (0)521 - 977 998 - 0
33609 Bielefeld - Germany fax: +49 (0)521 - 977 998 - 9
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
Fw: Which files to download/install - problems running MySQL
My email below was rejected for some reason, so I am trying again to send it. Regards, Nikki - Original Message - From: Nikki Mason [EMAIL PROTECTED] To: Miguel Angel Solórzano [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 20, 2002 8:41 AM Subject: Re: Which files to download/install - problems running MySQL Miquel, I tried what you suggested, but still it didn't work properly. For the server: C:\cd mysql\bin C:\mysql\binmysqld-opt --standalone --console 021020 8:20:30 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. The cursor did not return to the prompt to allow anymore commands to be typed. (I haven't created a c:\my.cnf or c:\windows\my.ini) For the client: C:\mysql\binmysql -uroot -p Enter password: Again, nothing more was displayed on the screen and the cursor did not return to the prompt to allow anymore commands to be typed. Regards, Nikki Mason - Original Message - From: Miguel Angel Solórzano [EMAIL PROTECTED] To: Nikki Mason [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, October 19, 2002 9:29 PM Subject: Re: Which files to download/install - problems running MySQL At 15:16 19/10/2002 +0100, Nikki Mason wrote: Hi, Hi, I wish to learn SQL, and as some of my friends already use MySQL, they recommended that I too use it. Please can you tell me what I need to download and install to get me going. I have: Pentium III 450Mhz, 256Mb Ram Windows 98 I don't have a C or C++ compiler installed. My PC is not connected to a network - it is standalone. I had earlier downloaded just the zip file for version 4.0.4-beta (Windows). I unzipped it and ran the 'setup' program. Once setup had completed I tried the test on the server - at no point had I run the Admin program or created the my.cnf or my.ini. I opened a dos window and from the directory c:\mysql\bin entered: mysqld --standalone No other text was shown in the dos window and it appeared to hang. The whole computer also seemed to 'hang'. I had to use ctrl C to kill the application and get back normal operation to my PC. Why you had killed the mysqld server ?. The mysqld.exe is a console application that when started with --standalone option it works in the background. If you want to see the console screen then issue: C:\mysql\binmysqld-opt --standalone --console 021019 17:25:31 InnoDB: Started mysqld-opt: ready for connections and for to work with the mysql client: C:\c:\mysql\bin\mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.53-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql after a fresh installation left the root password blank. for to shutdown the server: C:\c:\mysql\bin\mysqladmin shutdown 021019 17:27:27 mysqld-opt: Normal shutdown 021019 17:27:27 InnoDB: Starting shutdown... 021019 17:27:29 InnoDB: Shutdown completed 021019 17:27:29 mysqld-opt: Shutdown Complete I then tried manually creating the file c:\my.cnf and populated with the information given in the installation instructions. The server still hung. I then tried using the admin program with the similar results - the green traffic light was showing, but the dos window that was displayed appeared 'hung' and again the PC itself seemed to have hung - it operated extremely slowly. The task manager showed the admin tool as 'not responding'. What have I/am I doing wrong? -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ São Paulo - Brazil ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Which files to download/install - problems running MySQL
Hello, This is the my.cnf that I use in my syste. It is located in C:\. You can use it as guide. Start ## #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] basedir=D:/MySql datadir=D:/MySql/data log-bin=D:/mysql/Data/mysqllogs/binarylog log-bin-index=D:/mysql/Data/mysqllogs/binaryindex binlog-do-db=GGData local-infile=1 #InnoDB Settings innodb_data_home_dir = D:/MySql/GGData innodb_data_file_path = ggdata1:20M;ggdata2:20M;ggdata3:20M set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = D:/MySql/GGData/logs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=3M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = D:/MySql/GGData/logs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=5M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=60 ### End Make sure that the InnoDB directories exists in your computer, both the data_home_dir and the log_group_home_dir. Also make sure to delete the my.ini that was probably created in your windows directory. After you create the directories and the my.cnf file just open a dos windows and from the bin directory run mysqld or mysqld-max. Hope this helps JFernando *** sql *** -Original Message- From: Nikki Mason [mailto:nikki_mason;btopenworld.com] Sent: October 21, 2002 15:22 To: Miguel Angel Solórzano Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Fw: Which files to download/install - problems running MySQL My email below was rejected for some reason, so I am trying again to send it. Regards, Nikki - Original Message - From: Nikki Mason [EMAIL PROTECTED] To: Miguel Angel Solórzano [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 20, 2002 8:41 AM Subject: Re: Which files to download/install - problems running MySQL Miquel, I tried what you suggested, but still it didn't work properly. For the server: C:\cd mysql\bin C:\mysql\binmysqld-opt --standalone --console 021020 8:20:30 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. The cursor did not return to the prompt to allow anymore commands to be typed. (I haven't created a c:\my.cnf or c:\windows\my.ini) For the client: C:\mysql\binmysql -uroot -p Enter password: Again, nothing more was displayed on the screen and the cursor did not return to the prompt to allow anymore commands to be typed. Regards, Nikki Mason - Original Message - From: Miguel Angel Solórzano [EMAIL PROTECTED] To: Nikki Mason [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, October 19, 2002 9:29 PM Subject: Re: Which files to download/install - problems running MySQL At 15:16 19/10/2002 +0100, Nikki Mason wrote: Hi, Hi, I wish to learn SQL, and as some of my friends already use MySQL, they recommended that I too use it. Please can you tell me what I need to download and install to get me going. I have: Pentium III 450Mhz, 256Mb Ram Windows 98 I don't have a C or C++ compiler installed. My PC is not connected to a network - it is standalone. I had earlier downloaded just the zip file for version 4.0.4-beta (Windows). I unzipped it and ran the 'setup' program. Once setup had completed I tried the test on the server - at no point had I run the Admin program or created the my.cnf or my.ini. I opened a dos window and from the directory c:\mysql\bin entered: mysqld --standalone No other text was shown in the dos window and it appeared to hang. The whole computer also seemed to 'hang'. I had to use ctrl C to kill the application and get back normal operation to my PC. Why you had killed the mysqld server ?. The mysqld.exe is a console application that when started with --standalone option it works in the background. If you want to see the console screen then issue: C:\mysql\binmysqld-opt --standalone --console 021019 17:25:31 InnoDB: Started mysqld-opt: ready for connections and for to work with the mysql client: C:\c:\mysql\bin\mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.53-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql after a fresh installation left the root password blank. for to shutdown the server: C:\c:\mysql\bin\mysqladmin shutdown 021019 17:27:27 mysqld-opt: Normal shutdown 021019 17:27:27 InnoDB: Starting shutdown... 021019 17:27:29 InnoDB: Shutdown completed 021019 17:27:29 mysqld-opt:
logging into mysql on mac os x
Hi everyone, I'm a newbie to mysql and i'm trying to log into the mysql monitor from the terminal in mac os x (10.2.1). I was able to do it when i installed the system but for the life of me i can not figure out how to go back into it. Whenever i type the commands the articles i read say it never opens. I look in other books, type what they say and doesn't work. Any ideas? what is the commands to log in? Please help! and thank you in advance! Davinder Davinder [EMAIL PROTECTED] http://www.mahal.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ANN: EMS MySQL Manager 1.92 released
EMS HiTech company is announcing the next version (1.92) of MySQL Manager -- A Powerful MySQL Administration and Development Tool for Windows95/98/ME/NT/2000/XP. You can download the latest version from http://www.mysqlmanager.com/download.phtml What's new in version 1.92? 1. Grant Manager: Extract User's Grants and Extract All Users' Grants functions was added. Now you can extract user's grants as statements to a script by one click. 2. A possibility of duplicating tables from one database to another was added. Now you can create a table based on structure of the existing one between different databases. 3. Now it is possible to run more than one instances of MySQL Manager at once. To disable this feature check the Options-Environment Options-Preferences-Disable Multiple Instances option. 4. Database Registration Info dialog: Interactive Mode option was added. 5. Extract Metadata Wizard: Fixed bug in with generating NULL values. 6. Fixed bug with Access Violation appearance after switching between Print Data and Grid View modes in the Data View. (*) 7. Fixed bugs in Analyze, Check, Repair and Optimize Tables wizards. Now you can process tables which are named as MySQL keyword (e.g. `order`) using those wizards. 8. Fixed bug with choosing TAB or SPACE as CSV file delimiter the Import Data Wizard. (*) 9. Fixed some bugs in the Visual Query Builder. (*) 10. Some small improvements and minor bugfixes. (*) - Professional Edition only What is the EMS MySQL Manager? EMS MySQL Manager provides you powerful and effective tools for MySQL Server administration and objects management. Its Graphical User Interface (GUI) allows you to create/edit all MySQL database objects in a most easy and simple way, run SQL scripts, manage users and administrate user privileges, visually build SQL queries, extract or print metadata, export/import data, view/edit BLOBs and many more services that will make your work with the MySQL server as easy as it can be... We hope you'll enjoy working with our software. Thank you for your attention. Best regards, EMS HiTech development team. http://www.ems-hitech.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Seeking the minimum value of DateTime column type in MySQL
Dear friends, I am developing a database application for our saleses with PHP and MySQL. Our sales must call the customers on time. They may not be late to call the customers. The may not miss the appointment to meet the customer on the telephone line. But I have a problem to implement the SQL query on MySQL DB Engine. Suppose I have this simple table (APPOINTMENT Table) : CUSTID Cont_Pers APPOINTMENT_TIME 1 Albert 2002-10-23 13:00:00 2 Sarah 2002-10-24 14:00:00 3 Sandra 2002-10-23 08:00:00 ... ... ... ... ... ... I want to display one record which contain the appointment which has closest time between the current time ( sysdate() ) and the APPOINTMENT_TIME. So each the sales query one record (select) the record from this table with application than they will finf the record which has the earliest appointment time. I tried this SQL Query but it doesn't work : SELECT * FROM Appointment WHERE MIN(sysdate()-appointment_time) GROUP BY CustID Please tell me to determine the proper SQL Statement. Thank you very much in advance. __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Please help: MySQL automatic reconnect
Hi, I have read the MySQL manual that the automatic reconnect is implemented since version 3.21.18. Also according to the C API manual, the mysql_real_connect will set the automatic reconnect flag in MYSQL structure. However, I cannot find any instruction to switch off this automatic reconnect feature. The reason for doing this is because I am using the mysql_real_connect with an IP address that is established over a PPP link. Once if the connection over the phone line is broken, there is no way to reconnect it. My application hangs when I try to unconnect the PPP link. It seems that the MYSQL connection using blocking I/O. Is that correct? Can I use mysql_connect instead to avoid automatic reconnect? Thanks Joe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql grant ?
I want to give read access to all users on the localhost for a specific mysql table, I have tried the following: grant select on database.table to %@localhost; grant select on database.table to %@localhost; grant select on database.table to @localhost; None seem to work, what is the correct format? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi-join timings
*horribly unrealistic test warning* movies table: 1004 entries (id, directorid, name) directors table: 1765 entries (id, name) moviedirectors table: 1004 entries (movieid, directorid) (duplication of data with directorid is purposeful, for the sake of testing two query types with the same data). select movies.name, directors.name from movies left join moviedirectors on movieid = movies.id left join directors on moviedirectors.directorid = directors.id order by directors.name; 1004 rows, 0.052 secs (average, 10 runs) select movies.name, directors.name from movies left join directors on directorid = directors.id order by directors.name; 1004 rows, 0.035 secs (average, 10 runs) These of course do not represent anything close to real queries. Doing a select for the list of movies by a given director is 0.02 secs with the single join, 0.05 secs with the double join. Getting the director name for a movie by title is 0.00 secs, even using LIKE and with 66 row result sets: +++---+-+-+ | table | type | possible_keys | key | key_len | +++---+-+-+ +--++ | rows | Extra | | movies | ALL| NULL | NULL|NULL | | 1004 | where used; Using filesort | | moviedirectors | ref| PRIMARY | PRIMARY | 4 | | 10 | Using index| | directors | eq_ref | PRIMARY | PRIMARY | 4 | |1 || -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange behaviour of MySQL 3.23.53
Description:
Hello sirs,
Mr. Benjamin Pflugmann told me to send this problem to
[EMAIL PROTECTED]
I have a database structure as follows (simplyfied) :
CREATE TABLE masterTABLE (
pid int(11) unsigned NOT NULL default '0',
c1id int(11) unsigned default NULL,
c2id int(11) unsigned default NULL,
value int(11) unsigned NOT NULL default '0',
UNIQUE KEY pid2 (pid,c1id,c2id),
UNIQUE KEY pid (pid,value)
) TYPE=MyISAM;
INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
CREATE TABLE childTABLE1 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE1 VALUES (1, 'Yes');
INSERT INTO childTABLE1 VALUES (2, 'No');
INSERT INTO childTABLE1 VALUES (4, 'Yes');
INSERT INTO childTABLE1 VALUES (5, 'No');
CREATE TABLE childTABLE2 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE2 VALUES (3, 'Yes');
If I do this query:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
the Result will be 5 which is probably wrong.
The expected Result ist 4.
The correct Result will be returned if you remove
both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
This behaviour ist tested on MySQL 3.23.51 and now even
on MySQL 3.23.53.
TIA
How-To-Repeat:
Code Sample above
Fix:
??
Submitter-Id: submitter ID
Originator: Stephan Skusa
Organization: Lippe-Net Online-Service
MySQL support: none
Synopsis: Strange behaviour of MAX() in combination with UNIQUE KEY's
Severity: non-critical
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-3.23.53 (Source distribution)
Environment:
System: Linux akademos 2.2.18 #1 Fri Jan 19 22:10:35 GMT 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='-O6' CXX='gcc' CXXFLAGS='-O6' LDFLAGS=''
LIBC:
-rwxr-xr-x1 root root 1382179 Jan 19 2001 /lib/libc.so.6
-rw-r--r--1 root root 2585872 Jan 19 2001 /usr/lib/libc.a
-rw-r--r--1 root root 178 Jan 19 2001 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql-3.23.53
--with-charset=german1 --with-innodb CC=gcc CFLAGS=-O6 CXXFLAGS=-O6 CXX=gcc
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tips for LARGE system
On Sun, Oct 20, 2002 at 10:52:22PM -0700, Steven Roussey wrote: Other people can tell you more about replication and how it is different from clustering or load balancing. And you can use it for backup in a way too. It depends on your requirements though. Yeah. And you can use replication to enable load-balancing. They work quite well together. If things are partitionable, then some thinking about that upfront will do you a world of good later. Depend on your application though. Indeed. Partitioning before you NEED to can save a ton of hassle. 7) I'd also appreciate any input from people who have used official mysql support before. We have used their support and it was excellent. Heck, their free support is great. The paid stuff just gurantees it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 75 days, processed 1,588,578,807 queries (242/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL with InnoDb Tables - A Big Confusion
change your my.cnf to use the following variables:(I left our info in them for reference) innodb_data_file_path = ibdata1:400M innodb_data_home_dir = c:\ibdata innodb_log_group_home_dir = c:\iblogs innodb_log_arch_dir = c:\iblogs also make sure the MySQL my.cnf is place in the /etc directory in UNIX or the Windows-system-directory in Windows. The INNodb from what I understand should be set up to run as a global option and not a server-specific option, that is why you should put it in one of the directories above. Scott Pippin Shomal Bafna [EMAIL PROTECTED] 10/21/02 08:46AM Sorry, I know this question would best suit for [EMAIL PROTECTED] but there is over limit problem. E-mail Account: lists-mysql is over the limit of 31457280 bytes. I am running a Mandrake Linux 8.1, with an rpm installed ver 3.23.41. Unfortunately configuring the MySQL for InnoDb for me turned into an intriguing problem. I installed the MySQL-Max 3.23.41 version. And tried to follow instructions but mysql would start if i create an my.cnf (by default this file is missing from installation) [mysqld] innodb_data_home_dir = innodb_data_file_path = /innodb-data/ But on starting the deamon it wouldn't start. Perplexed and trying to trouble shoot where the problem i remarked the entries and mysqld ran. But 'show variables like have_%' displayed BDB=YES and InnoDB=DISABLED. How to turn around this problem? Waiting for suggestions. Shomal = shomal __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ - Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail java-unsubscribe-##L=##[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how do you define a relationship?
Unfortunately that oversimplifies the situation. A least a few movies have more than one director. AI, for example: first half by Kubrick, second (lousy) half by Speilberg. Another example is The Twilight Zone, which had four directors. This calls for a junction table between Movies and Directors, called MovieDirectors and having MovieID and DirectorID. Perhaps this isn't refined enough either. Consider Clint Eastwood, who stars, directs and produces. Woody Allen, who does all that and also writes. A fuller (heh heh) resolution of the problem might look like this: Create a new table called Roles, which contains rows for Actor, Director, Producer, D.O.P. and whatever else is of interest. Combine the existing Directors and Actors tables into a single table called Talent. Create a junction table called TalentRoles, with FKs referencing Talent and Roles. Clint would have rows for Writer, Director and Actor. Now create a table called MovieRoles with foreign keys into TalentRoles and Movies. Then you add as many people to a movie as you want, nw whatever roles are legitimate for those people. I.e. you couldn't add Clint as a writer. hth, Arthur Fuller -Original Message- From: bwarehouse [mailto:bwarehouse;yahoo.com] Sent: Sunday, October 20, 2002 5:38 PM To: Randy Hammons; [EMAIL PROTECTED] Subject: RE: how do you define a relationship? You are on the right track.. but kill your relationships as you have them currently defined... think of it this way... (one to many).. you have movies and you have directors.. it should be one director to many movies seeing that a movie normally only has one director.. so take you primary key from directors and place that foreign key in the movies table. Remember: one director has many movies, not one movie has many directors.. BOTTOM LINE. Make a field in your movies table exactly the same as the primary key in the directors table, but don't autonumber it if that is what is in the primary key of the directors table, just make it the same type( ie. Number, text, date, etc). and make sure it has the exact same specs as the directors primary key field except the autonumber type; and make sure the perimeter is allow duplicate key is set to yes, if you index the field in the movies table), but you don't have to index the foreign key in the movie table if the primary key of the directors table is already indexed. Just make the foreign key, allow duplicates 'YES' in the movie table.. Hope this helps.. Later b.ware -Original Message- From: Randy Hammons [mailto:rathlon1;cox.net] Sent: Sunday, October 20, 2002 8:43 AM To: [EMAIL PROTECTED] Subject: how do you define a relationship? I think I've firmed my theoritical understanding of relationships enough to be dangerous. Now for the logical parthow do you actually define a relationship in mysql? For instance... table movies columns (movie id(pk) , movie title , year filmed) table directors columns (director id(pk), movie id(fk), total movies) Here I have a primary key in each table which I have defined in my table structure. I also have a foreign key in the directors table, but where did I define that to the database? Is this a sort of relationship? Like a one to many? I've noticed in some reading that certain keys are refered to by their respective names ( column - whatever ) and the relatioship column is defined as ( column - relwhatever ), is this the proper way to define a relationship? Is the primary/foreignkey, relationship concept something that is used by the database to replicate data or are they design tools used to develope an independent, non repetitive data structure? Someone please help...I'm so confused. Thanks so much - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql problems
On 21-Oct-2002 porcupine wrote: Hello guys Here is the problem. You may find it trivial, but it is a serious obstacle for me. I make a database, a table in mysql and it goes fine. Then I try to view my table with PHP. I am pretty sure that I can't connect to the database. In particular $db = mysql_connect(localhost, root, ) or die; statement doesn't seem to be valid. Should I use another username, password or both? Are there any suggestions? Why isn't 'root' password-ed ? And it's usually a 'bad idea' to run php/Perl scripts as root. Anyhoo; to see what's going on use: $db = mysql_connect(localhost, root, ) or die(mysql_error() .BR\n); Trying to solve the previous problem I face a new one: My new username is not anymore root, but root@localhost. This happened during a phonecall assistant by a friend. How did this happen? How can I generally change my username? If the previous explanation is not clear all I mean is: when I try to access mysql the: mysql -u root command is not valid. Instead I get this message: access denied for user: 'root@localhost' (Using password: NO). mysql -u root -p[password] --and type in the password at the prompt. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem upgrading
I believe you need to move the socket which defaults to /tmp. If you move it to /var/run or /var/tmp, you should be good to go. I think you can either set the location at compile time or in the config files. Thanks for the response! I have /tmp symlinked to /var/tmp, and /var is mounted writable and it still doesn't work. -Lance Jerry - Original Message - From: Lance Uyehara [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 18, 2002 7:30 PM Subject: problem upgrading I was running mysql 3.23.26 on freebsd 4.6 without a problem. Then I had a runaway high load cpu event and figured I should at least get later code before complaining. So I grabbed 3.23.52 (the latest freebsd package). My databases are in /var/db/mysql. My root partition is mounted readonly. When I run: /usr/local/bin/safe_mysqld --user=mysql --skip-networking /dev/null 21 I get the following error: Fatal error: Please read Security section of the manual to find out how to run mysqld as root! When I do mount -u / everything runs just fine. So my question is what do I need to do to run mysqld with the / partition mounted read only? Thanks for the help, Lance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how do you define a relationship?
From: Arthur Fuller [EMAIL PROTECTED] Unfortunately that oversimplifies the situation. A least a few movies have more than one director. My motto: Generalize for the norm; specialize for the exception. The vast majority of movies have but one director. And I suspect that movies with multiple directors have but one who is primary. Keep a single director field, with a second NULL FULLTEXT field for additional directors. The NULL test for the normal case is much cheaper than what you have to go through by assuming that ALL movies have multiple directors! This is how books are handled in large databases. Each has a primary author, and may contain secondary authors. There is no assumption that large numbers of books have multiple, equal authors. This way, you can easily and conveniently list multiples as Speilberg (et. al.) without doing joins. An interested browser can then go further to find out who the others are. In the other case, each request for a director requires a JOIN. Of course, your particular application may be director-centric, like if you're building a special database to support research on directors. But if it's just a general-purpose movies database, why bog the whole thing down just to suit a few exceptional cases? SQL SQL SQL SQL SQL SQL SQL SQL -- : Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Newsletters now on-line at http://www.Bytesmiths.com/Newsletter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange behaviour of MySQL-3.23.51
I have a database structure as follows (simplyfied) :
CREATE TABLE masterTABLE (
pid int(11) unsigned NOT NULL default '0',
c1id int(11) unsigned default NULL,
c2id int(11) unsigned default NULL,
value int(11) unsigned NOT NULL default '0',
UNIQUE KEY pid2 (pid,c1id,c2id),
UNIQUE KEY pid (pid,value)
) TYPE=MyISAM;
INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
CREATE TABLE childTABLE1 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE1 VALUES (1, 'Yes');
INSERT INTO childTABLE1 VALUES (2, 'No');
INSERT INTO childTABLE1 VALUES (4, 'Yes');
INSERT INTO childTABLE1 VALUES (5, 'No');
CREATE TABLE childTABLE2 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE2 VALUES (3, 'Yes');
If I do this query:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
the Result will be 5 which is probably wrong.
The expected Result ist 4.
The correct Result will be returned if you remove
both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
--
Stephan Skusa mailto:stephan.skusa;lippe-net.de
Lippe-Net Online-Service http://www.lippe-net.de
Herforder Strasse 309 tel.: +49 (0)521 - 977 998 - 0
33609 Bielefeld - Germany fax: +49 (0)521 - 977 998 - 9
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: why so late with Mac OS X versions?
Marc Liyanage has been doing MacOS X binaries in Switzerland. When I lived there, they took month-long vacations, two-hour lunches, and frowned on weekend work. Maybe he has a life outside of MySQL? :-) How do I get a job in Switzerland? ;) sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how do you define a relationship?
Arthur Fuller wrote: Create a new table called Roles, which contains rows for Actor, Director, Producer, D.O.P. and whatever else is of interest. For what its worth, I would consider it more appropriate to simply do: Movies: ID, etc. People: ID, Name, etc. MoviePeople: MoviesID, PeopleID, Role SELECT COUNT(*) FROM Movies LEFT JOIN MoviePeople ON Movies.ID = MovieID LEFT JOIN People ON PeopleID = People.ID WHERE People.Name = Clint Eastwood AND MoviePeople.Role = Writer; ... to check if Clint Eastwood is a writer or not; although it limits you to the movies you know about, but in a situation like IMDB.com, that's pretty much everything. PS, I'd actually make Role an ID to a Roles table, but that would've added a JOIN to the query above and unnecessarily complicated my comment. Good suggestion though! -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Need help installing PHP (Linux) with MySQL and Apache
I can't get PHP-4.2.3 compiled to work with MySQL and Apache, despite months of trying. Apache compiled ok, and so did MySQL -both work, on my Debian Linux box. I've tried various combos of installing the Deb packages, compiling from source, and various combinations thereto. I started out using http://hotwired.lycos.com/webmonkey/00/44/index4a_page4.html?tw=programming as a guide, in efforts to create a way of accessing MySQL via PHP Gui of some sort. I've been trying for months, on and off, using Redhat, Mandrake, and now Debian3.0. I've tried getting Escapade installed, but pages won't load unless I change a line in httpd.conf, and then Apache won't run because it complains of the changed line in httpd.conf, which was supposedly necessary to get Escapade to run. Typing ./httpd -l in my Apache bin directory shows that mod_so.c is an enabled module. My attempt to compile PHP-4.2.3 by typing ./configure --with-mysql=/usr/bin/ resulted in the following error message configure: error: Cannot find header files under /usr/bin/ I've tried changing the path to MySQL headers, but still get same error msg. I really don't know how to find these MySQL headers since I wouldn't know one if I saw it. Can someone help with a pointer/link? I'd very greatly appreciate it.. Chip [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ENABLE KEYS
Please help me! mysqlalter table a_log ENABLE KEYS; ERROR 1064: You have an error in your SQL syntax near 'ENABLE KEYS' at line 1 Thank's - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp field in the InnoDB table
Heikki, thank you for your response. Is this a big problem? Well, actually it is not very big problem for me now, because I already know about that feature. ;) But I would prefer to have timestamp that means the end of the transaction. Anyway it would be very useful to have some notices in the documentation. Thank you. Best regards, Mikhail. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:20 PM Subject: Re: Timestamp field in the InnoDB table Mikhail, - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:11 PM Subject: Timestamp field in the InnoDB table Hi, I have found one unclear place for me regarding to the timestamp field in the InnoDB table. Please, explain me am I correct or not. Let's say we have a table Test with 'timestamp' field: CREATE TABLE `Test` ( `id` int(3) NOT NULL auto_increment, `UpdateDate` timestamp(14) NOT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; and I try to update one row in this table: update Test set UpdateDate = NULL where id = 1; Let's say I started 'update' statement at 15:00:00. But the row with id = 1 is blocked by another transaction, so 'update' statement needs to wait till the end of the transaction. After 10 sec the block on the record with id = 1 is released. So my 'update' finished. select UpdateDate from Test where id = 1 gives me 2002102115, but I would expect 20021021150010. So what do you think about it? looks like the MySQL interpreter assigns the clock time value to the timestamp field before calling the InnoDB backend. Is this a big problem? Thanks in advance. Best regards, Mikhail. Regards, Heikki sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
adUseClient
SQL The docs say that recordset1.RecordCount will return the correct value if adUseClient is used as the cursorlocation setting. I find that this is not correct - is there another setting which needs to be mae to make this return correctly - my return matching rows setting is also set. Alan McDonald http://www.meta.com.au - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Setup Query to search smallest subset on a Conditional And?
Hi, In previous e-mail I asked how to improve a word search and used some of the suggestions. Now I'm doing a 2 word search and need a little advice. Background: There are 2 tables: word and content. content consists of 2 fields: rec_id int(11) unsigned not null, description varchar(200) not null word consists of 2 fields: word int(11) unsigned not null, rec_id int(11) unsigned not null word is created from content. Each actual word from the field content.description is created as a record in the table word with its rec_id. The field word.word is the CRC of the actual word hence the integer type. I've setup a query as such for the words: new york There are ~70,000 records with the word new, and ~10,000 with the word new. Query is: select description from content, word w1, word w2 where w1.word=1810056261 and w1.rec_id=content.rec_id and w2.word=3012632499 and w2.rec_id=content.rec_id ORDER BY w1.word, w2.word; When I do an explain it shows that its checking over ~80,000 records +-+++-+-+--- +---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+++-+-+--- +---+-+ | w2 | ref| word_index1| word_index1 | 4 | const | 10982 | where used | | content | eq_ref | PRIMARY,content_index1 | PRIMARY | 4 | w2.rec_id | 1 | Using index | | w1 | ref| word_index1| word_index1 | 4 | const | 84725 | where used | +-+++-+-+--- +---+-+ My question: Is it possible to setup the query to tell mysql to search the smallest subset only when you have a conditional AND in this kind of join. Thanks in advance. regards David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Obscure FULLTEXT search problems
Hi there, I have some trouble understanding some results I'm getting when executing a SELECT statement in an FULLTEXT indexed table in my MySql db. In scenario 1 I'm using this SQL-statement: SELECT * FROM tArticles WHERE ( Title LIKE '%HYDRO%' OR Body LIKE '%HYDRO%' ) In scenario 2 I'm using this SQL-statement: SELECT * FROM tArticles WHERE ( MATCH( Title, Body ) AGAINST( 'HYDRO' ) ) My problem is that if I'm searching for a word (For instance HYDRO) that are found in a lot of my articles, then scenario 1 is much faster than scenario 2. But if the search word is in just a few of the articles then scenario 2 is _much_ faster than scenario 1. Can someone explain this to me? What do I do? I'm running MySql 3.23.46 on Solaris 8. My table creation statement looks like this: CREATE TABLE tArticles ( ArticleId int(11) NOT NULL auto_increment, Title varchar(255) NOT NULL default '', Author varchar(255) NOT NULL default '', Version int(3) NOT NULL default '0', TickerCodes varchar(255) NOT NULL default '', SubjectCodes varchar(255) NOT NULL default '', MessageNum varchar(20) NOT NULL default '', Timestamp datetime NOT NULL default '-00-00 00:00:00', Body text NOT NULL, Footer varchar(255) NOT NULL default '', PRIMARY KEY (ArticleId), KEY Timestamp (Timestamp), FULLTEXT KEY Title (Title,Body) ) TYPE=MyISAM; -- Jakob Vad Nielsen [EMAIL PROTECTED] NHST - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RedHat 8.0 and MySQL
Luc, I just installed RH 8.0 (i86) and also installed the MySQL (4.0.4) rpm's from the MySQL site (didn't use the ones on the RH 8.0 CD's -- they were 3.x.x). My machine is standalone so there are no other users accessing MySQL. MySQL worked fine except that it wouldn't launch when the system booted up and I had to su to start the MySQL daemon. Looking at the online MySQL documentation, I saw that I could put a command in the /etc/rc.local file: ?/mysql.server start, where the ? is the directory path for the mysql.server script. This worked OK but rc.local is read after Linux goes through all its other initializations. I also have RH 7.3 running on a different machine (also has MySQL 4.0.4) and the MySQL daemon starts up normally when the system boots up. I checked all the /etc/rcX.d (where X = 0, 1...6) directories and found that there were two differences between my RH 7.3 machine and my RH8.0 machine. After I changed my RH8.0 machine to have the same symlinks as the RH 7.3 machine, MySQL now boots up automatically with no problems. I probably should of checked the mysql script (found in /etc/init.d) between the two distributions, but I didn't see any indication of changes in the script (i.e, change dates, etc). Anyway, here are the symlink changes I made (all the mysql symlinks point to ../init.d/mysql: rc3.d --- change K90mysql to S90mysql. rc5.d --- change K90mysql to S90mysql. Good luck! -- Paul A. Valente The MITRE Corp. 202 Burlington Rd, M/S 1618A Bedford, MA 01730-1420 Office: 781.377.7753 Fax: 781.377.8652 or 6712 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERD, Scripts, and Reverse Engineering
I did the same seach and evaluation a couple of weeks back. Ended up buying Dezign for Databases, partly on cost (compared to Case Studio which seems the most comprehensive product), but also because the evaluation version allowed enough tables/releationships to do a realistic eval + good documentation. Reverse engineering is possible if you buy the additional Importerscripts, or ImporterMysql, and you can define your own logical types (called domains) or additional Mysql types if the set is not complete. As far as I can see there is no way to define the table type, but the rest is covered. So far I'm finding it stable and very easy to work with. Andy On 20 Oct 2002 at 19:07, Randy Garland wrote: Hello, all: I'm looking for a well-designed Windows-based database design tool that: 1) (Most importantly) Supports MySQL 4.x: a) supports all of its datatypes, including MEDIUMINT, ENUM, BLOB, AND TEXT b) lets the user decide between table types (ISAM v. InnoDB v BDB) 2) Creates ERDs (Entity Relationship Diagrams) with standard 'crow's feet' notation 3) From the ERDs, generates 'load' and 'drop' sql scripts 4) Can reverse engineer an existing MySQL database, creating an ERD from the database 5) Is, preferably, free or cheap I've looked and looked. I'm currently evaluating three products: 1) Dezign for Databases (out of the Netherlands) 2) CASE Studio version 2.10.1, by CHARONWARE (www.casestudio.com) 3) Database Design Studio, by chillisource (www.chillisource.com) All of them are commercial, and I have reservations about each one. None is MySQL-specific (they cover many db's), and as such, don't really make it easy for the user to easily define MySQL-specific data types. Also, only CASE includes Reverse Engineering. CASE is big bucks ($325), and the others are in the $100 range. I just don't want to have to create my ERDs in Visio, document the details in Excel, manually create scripts, and then have to update all three when the db needs change. Is there one, good, unified tool for MySQL that can help? Thanks in advance, Randy Garland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: normalization question
Except in the case that cars aren't held at the dealers and any one car can be purchased from a range of dealers. In which case you will need a seperate table like, Dealer_Car_Map (dealer_id,car_id). Ric. - Original Message - From: Artem Koltsov [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 9:17 PM Subject: RE: normalization question Hi! I don't understand problem. Car can belong to only one Dealer, and one Dealer can have many Cars, therefore DealerID should be in Car table. Also Car can be only one Model, but there are many Cars the same Model. It looks quite normal to put ModelID into Car table, and I don't see any possible problems. Of course if there are any special requirements for the database, they have to be incorporated into the model. Am I missing your point? Artem -Original Message- From: [EMAIL PROTECTED] [mailto:speters;metromls.com] Sent: Monday, October 21, 2002 5:43 PM To: [EMAIL PROTECTED] Subject: normalization question I'm working on a design for a database that is leaving me with some normalization issues that I havent had to deal with before. For example, if im trying to store information about car dealerships I end up with the following situation: I want to store the cars in stock at each physical dealership, with certain attributes. So, I have a table Dealership as follows: Dealer_ID Name Address Etc. Then there is a Car_Make table (for things like Chevy, Hyundai): Car_Make_ID Make_Name ... And a Model table (for things like Elantra, Neon, etc): Model: Model_ID Car_Make_ID Model_Name ... And a table to describe the cars themselves: (one row per car) Car: VIN_number(vehicle identification number, what the DMV uses) Model_ID other attributes ... Now, to associate a specific car with a dealer, I want to put Dealer_ID in the Cars table, but then the Cars table has multiple parent tables, specifically Model Dealership I'm sure that this is far from normal, and will lead to programming problems, but i dont see what to do about it. Any advice would be greatly appreciated. thanks, sean peters [EMAIL PROTECTED] sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp field in the InnoDB table
Mikhail, - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 2:34 PM Subject: Re: Timestamp field in the InnoDB table Heikki, thank you for your response. Is this a big problem? Well, actually it is not very big problem for me now, because I already know about that feature. ;) But I would prefer to have timestamp that means the end of the transaction. that is difficult, because then we would need a completion procedure at the end of a transaction which would go to set the timestamps to the transaction commit timepoint. Anyway it would be very useful to have some notices in the documentation. Thank you. Best regards, Mikhail. Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:20 PM Subject: Re: Timestamp field in the InnoDB table Mikhail, - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:11 PM Subject: Timestamp field in the InnoDB table Hi, I have found one unclear place for me regarding to the timestamp field in the InnoDB table. Please, explain me am I correct or not. Let's say we have a table Test with 'timestamp' field: CREATE TABLE `Test` ( `id` int(3) NOT NULL auto_increment, `UpdateDate` timestamp(14) NOT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; and I try to update one row in this table: update Test set UpdateDate = NULL where id = 1; Let's say I started 'update' statement at 15:00:00. But the row with id = 1 is blocked by another transaction, so 'update' statement needs to wait till the end of the transaction. After 10 sec the block on the record with id = 1 is released. So my 'update' finished. select UpdateDate from Test where id = 1 gives me 2002102115, but I would expect 20021021150010. So what do you think about it? looks like the MySQL interpreter assigns the clock time value to the timestamp field before calling the InnoDB backend. Is this a big problem? Thanks in advance. Best regards, Mikhail. Regards, Heikki sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL - ERROR 2003: Can't connect
Hi. I am new to this and can't get started because I receive the error message: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061). o/s : WindowsXP. Any ideas? Thanks, Clive. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Normalization sql
John Chang wrote: What is the Title.ID and Actor.ID? Will this work if someone is doing a text search on a webpage? The results will go to another page. Does the below go to the first page or the results page? You need a good SQL book and a good CGI/web programming book. The .ID's are usually auto-incrementing unique ID's for the sake of fast referencing. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Strange behavior of MySQL 3.23.51
Hmmmnn ... I don't think so ... COUNT() is also an
aggregate function and a SELECT COUNT(*) FROM table
should work on every database without grouping.
I think it's an error in MySQL. Why else this different
behaviour with and without unique index?
-Ursprüngliche Nachricht-
Von: Mikhail Entaltsev [mailto:mike_lynx;smtp.ru]
Gesendet: Montag, 21. Oktober 2002 18:01
An: [EMAIL PROTECTED]; [MYSQL]
Betreff: Re: Strange behavior of MySQL 3.23.51
Stephan,
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
you are using aggregate function. So you need to use group by clause.
Just add at the end group by m.pid:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
GROUP BY m.pid
Best regards,
Mikhail.
P.S. But actually it is strange that MySQl didn't report syntax
error there.
:(
- Original Message -
From: [EMAIL PROTECTED]
To: [MYSQL] [EMAIL PROTECTED]
Sent: Monday, October 21, 2002 5:46 PM
Subject: Strange behavior of MySQL 3.23.51
I have a database structure as follows (simplyfied) :
CREATE TABLE masterTABLE (
pid int(11) unsigned NOT NULL default '0',
c1id int(11) unsigned default NULL,
c2id int(11) unsigned default NULL,
value int(11) unsigned NOT NULL default '0',
UNIQUE KEY pid2 (pid,c1id,c2id),
UNIQUE KEY pid (pid,value)
) TYPE=MyISAM;
INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
CREATE TABLE childTABLE1 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE1 VALUES (1, 'Yes');
INSERT INTO childTABLE1 VALUES (2, 'No');
INSERT INTO childTABLE1 VALUES (4, 'Yes');
INSERT INTO childTABLE1 VALUES (5, 'No');
CREATE TABLE childTABLE2 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE2 VALUES (3, 'Yes');
If I do this query:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
the Result will be 5 which is probably wrong.
The expected Result ist 4.
The correct Result will be returned if you remove
both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
--
Stephan Skusa mailto:stephan.skusa;lippe-net.de
Lippe-Net Online-Service http://www.lippe-net.de
Herforder Strasse 309 tel.: +49 (0)521 - 977 998 - 0
33609 Bielefeld - Germany fax: +49 (0)521 - 977 998 - 9
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RAM needed for a mysql server
On 21-Oct-2002 Francesc Guasch wrote: Hi. I asked this question last week and nobody answered. Please someone give me a hint. I need to buy a new server that must handle 500 concurrent connections to a mysql server and I don't know how many RAM should have. I've a server that does 200 average up to 600 (max_connections) Dual Pentium III 850Mhz w/ 1 Gig of memory. Load is normal at 0.6, and peaks at 4 processes. I've been looking to the process list but I'm clueless about how much memory is shared. Any advice ? Here is the output of VMonitor: ## PID UIDSize Share VSize Rss TTY St Command 1 23902 mysql 21M 2.2M 71M 21M S mysqld 2 23904 mysql 21M 2.2M 71M 21M S mysqld 3 23905 mysql 21M 2.2M 71M 21M S mysqld 4 23906 mysql 21M 2.2M 71M 21M S mysqld Does this mean every connection uses 21M of memory, 2.2 MB shared with the rest of mysql clients ? No. The base footprint of the daemon is 24-40M. Each additional thread will eat another 0.5 - 1.5M, depending on configuration. YMMV. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Compiling mysql with correct libraries
-Mensaje original- De: Sinisa Milivojevic [mailto:sinisa;mysql.com] Enviado el: lunes, 21 de octubre de 2002 14:28 Para: Iago Sineiro CC: [EMAIL PROTECTED]; [EMAIL PROTECTED] Asunto: Re: Compiling mysql with correct libraries On Mon, 21 Oct 2002 09:51:41 +0200 Iago Sineiro [EMAIL PROTECTED] wrote: Hi all. I want to compile MySQL statically with my own UDF and InnoDB support in one box and with make_binary_distribution install it in a RedHat 8.0 box. Which are the correct versions for gcc and glibc? And automake, autoconfig, and any another posible package needed for compile MySQL? Which is the best version of RedHat for compiling MySQL? Also which are the options used in compilation when is created MySQL-3.23.x-MAX.rpm? Thanks in advance. Iago. HI! This is a difficult question, as we learned recently that there are some problems with MySQL and glibc supplied with RH 8.0. You can use gcc 3.2, automake 1.5 and autoconf 2.54. But at www.mysql.com download page there is this compiler advisory: Compiler Advisory: Several of our users have reported random crashes and table corruption with MySQL binaries compiled with gcc 2.96 on the x86 Linux platform. Although we were unable to duplicate the problems ourselves or understand their exact cause, we suspect with a great degree of confidence that the problem was compiler related. Replacing the faulty binary with our binary always eliminated the problem. We recommend that MySQL be compiled with gcc 2.95 if you have to compile your own binary at all. It is also acceptable to compile it with gcc 2.91 - builds with these compilers have been tested extensively. We would suggest, however, that you stay away from gcc 3.0 series until we have had some time to do some more extensive testing. ... Is gcc 3.2 really tested or I should compile mysql with gcc 2.96? The options used in creating our 3.23 RPM are described in our manusl. Yes, I was looking in the manual when I see it. Sorry. We currently build our RPM's on updated RH 6.2. -- How updated is your RH 6.2? Iago. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What is relay-bin log?
It is seems that RESET SLAVE query sholud not be done while replication is working. Slave forget its replication position and cannot continue replication since it tries to do the queries already executed. Do I need to do SLAVE STOP before RESET SLAVE? Or any other means to delete replication logs during replication? -- Chung Ha-nyung alita@[neowiz.com|kldp.org] Sayclub http://www.sayclub.com NeoWiz http://www.neowiz.com -Original Message- From: Chung Ha-nyung [mailto:alita;neowiz.com] Sent: Monday, October 21, 2002 2:22 PM To: 'Victoria Reznichenko'; [EMAIL PROTECTED] Subject: RE: What is relay-bin log? If all works correctly, can I delete replication logs by RESET SLAVE safely? I'd like to configure two servers with two-master scheme, each is the master of another. -- Chung Ha-nyung alita@[neowiz.com|kldp.org] Sayclub http://www.sayclub.com NeoWiz http://www.neowiz.com -Original Message- From: Victoria Reznichenko [mailto:victoria.reznichenko;ensita.net] Sent: Friday, October 18, 2002 8:01 PM To: [EMAIL PROTECTED] Subject: re: What is relay-bin log? Chung, Thursday, October 17, 2002, 9:48:15 AM, you wrote: CHn At the slave server of replication there are log files like CHn environment hostname-relay-bin.xxx. Yes, these logs are used for replication: http://www.mysql.com/doc/en/Replication_SQL.html CHn For what these logs and how can I know which one I can delete safely? RESET SLAVE; -- For technical support contracts, goto https://order.mysql.com/?ref=ma02-010c This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timestamp field in the InnoDB table
Mikhail, - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 21, 2002 1:11 PM Subject: Timestamp field in the InnoDB table Hi, I have found one unclear place for me regarding to the timestamp field in the InnoDB table. Please, explain me am I correct or not. Let's say we have a table Test with 'timestamp' field: CREATE TABLE `Test` ( `id` int(3) NOT NULL auto_increment, `UpdateDate` timestamp(14) NOT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; and I try to update one row in this table: update Test set UpdateDate = NULL where id = 1; Let's say I started 'update' statement at 15:00:00. But the row with id = 1 is blocked by another transaction, so 'update' statement needs to wait till the end of the transaction. After 10 sec the block on the record with id = 1 is released. So my 'update' finished. select UpdateDate from Test where id = 1 gives me 2002102115, but I would expect 20021021150010. So what do you think about it? looks like the MySQL interpreter assigns the clock time value to the timestamp field before calling the InnoDB backend. Is this a big problem? Thanks in advance. Best regards, Mikhail. Regards, Heikki sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Connection pool
Hi, Does anyone has a connection pool built for MySQL using JAva (the application run on Tomcat).. I have one but its not running satisfactorily and leaves some processes suspended.. thnx in adv, sands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table gets wiped out!
Still need help with this, guys. :) I read that was a problem with the list being over some limit (?). So, in case you did not receive this, I hereby resent it. Otherwise, I am sorry for posting it twice. Thanks, - Mark Ok, rebuillding a table, I did (in Perl): $sth = $dbh - prepare (LOCK TABLES $table WRITE); die $dbh-errstr if (not defined ($sth - execute)); $sth = $dbh - prepare (DELETE FROM $table); die $dbh-errstr if (not defined ($sth - execute)); This truncated the table. Then I filled the table anew, and, at the end (before the UNLOCK), issued: $sth = $dbh - prepare (OPTIMIZE TABLE $table); die $dbh-errstr if (not defined ($sth - execute)); Then... the ENTIRE table is gone after the optimize! If I do not run the optimize, the table is fully restored. But once I optimize it, it is flat gone! I am not so new as to cry bug the moment something escapes my grasp; but I find this very odd, to say the least. I know OPTIMIZE reclaims losts space and such. But what seems to have happened, is that the newly filled rows (that went over the old onces that were deleted) now also get deleted by the OPTIMIZE. If not a bug, then that behavior, pardon my French, is downright crazy. If RE-USED old rows get wiped after an OPTIMIZE, then there is something rotten in the state of Denmark. :) I mean, re-used rows are in use again! This seems related to the LOCK. If I do not LOCK the table first, everything is ok, even after the OPTIMIZE. But with the table still LOCKED (as I am, obviously, still writing to it), all re-used rows in the table just get wiped out after the OPTIMIZE! Yes, I am the guy that decided to set up shop at home too, on my Windows XP machine, to test out a few things before using them on my production server. Good thing I did. :) I tried this on both my FreeBSD box, MySQL 3.32.49 and my home editions, version 3.23.52; the result is the same in both cases. Please, any suggestions would be helpful, Thanks, - Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Test for table lock
Hi, Using MySQL, perl and DBI, is there a way to test to see if a table is locked (as in LOCK table...). We batch process our updates every 3 hours or so (it's not a set schedule though, it depends when a certain level of new updates is reached), and our main tables are locked during this time for around 10 minutes or so. I'd like to be able to modify our search and display scripts to inform the users to come back later while the tables are locked. Thanks, Martyn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Seeking the minimum value of DateTime column type in MySQL
On 21-Oct-2002 Prabu Subroto wrote: Dear friends, I am developing a database application for our saleses with PHP and MySQL. Our sales must call the customers on time. They may not be late to call the customers. The may not miss the appointment to meet the customer on the telephone line. But I have a problem to implement the SQL query on MySQL DB Engine. Suppose I have this simple table (APPOINTMENT Table) : CUSTID Cont_Pers APPOINTMENT_TIME 1 Albert 2002-10-23 13:00:00 2 Sarah 2002-10-24 14:00:00 3 Sandra 2002-10-23 08:00:00 ... ... ... ... ... ... I want to display one record which contain the appointment which has closest time between the current time ( sysdate() ) and the APPOINTMENT_TIME. So each the sales query one record (select) the record from this table with application than they will finf the record which has the earliest appointment time. I tried this SQL Query but it doesn't work : SELECT * FROM Appointment WHERE MIN(sysdate()-appointment_time) GROUP BY CustID Please tell me to determine the proper SQL Statement. SELECT * FROM Appointment WHERE NOW appointment_time ORDER BY appointment_time LIMIT 1; (I don't understand why you GROUP BY. The SalesRep can only talk to one at a time, right ?) Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Foreign keys in InnoDB tables
Hello I am a brand new user of MySql, and have some questions about using InnoDB tables. I noticed in the manual that you can only specify - ON DELETE SET NULL Or - ON DELETE CASCADE What is the default behaviour if nothing is specified? Also, does anyone know if there are any plans to support additional options? Finally, when you have many tables with a foreign key referencing a table, how does this affect performance of updates on the table being referenced? Thanks. Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Compiling mysql with correct libraries
Hi all. I want to compile MySQL statically with my own UDF and InnoDB support in one box and with make_binary_distribution install it in a RedHat 8.0 box. Which are the correct versions for gcc and glibc? And automake, autoconfig, and any another posible package needed for compile MySQL? Which is the best version of RedHat for compiling MySQL? Also which are the options used in compilation when is created MySQL-3.23.x-MAX.rpm? Thanks in advance. Iago. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Timestamp field in the InnoDB table
Hi, I have found one unclear place for me regarding to the timestamp field in the InnoDB table. Please, explain me am I correct or not. Let's say we have a table Test with 'timestamp' field: CREATE TABLE `Test` ( `id` int(3) NOT NULL auto_increment, `UpdateDate` timestamp(14) NOT NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB; and I try to update one row in this table: update Test set UpdateDate = NULL where id = 1; Let's say I started 'update' statement at 15:00:00. But the row with id = 1 is blocked by another transaction, so 'update' statement needs to wait till the end of the transaction. After 10 sec the block on the record with id = 1 is released. So my 'update' finished. select UpdateDate from Test where id = 1 gives me 2002102115, but I would expect 20021021150010. So what do you think about it? Thanks in advance. Best regards, Mikhail. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Tips for LARGE system
handling from 3M to 20M records/day with potentially a lot of processing, live inserts/ updates etc. How many records do you forsee? (There are some things to look up in the manual to give MySQL a hint at the total size of the table so it can start using longer internal pointers.) How big are the records? What kind of processing? Are you separating your transactional system from the reporting system? What is the pattern of use of the records? Unfortunately, your information is not enough to go on. For example, we do 200,000,000+ queries a day and barely break a load of 1.00 on Linux. But we have had a lot of time to optimize things. 3) Are there any guidelines in estimating more presicely what hardware I will need? Will your application we processor or disk based? I'd guess disk based, so get lots of RAM (fast RAM -- like DDR or whatever), and as many fast disks as you can afford and stripe them. Personally, I built our machine from parts at Frys Electronics. I think you could put something together for a reasonable amount of money. Do note, it has been my experience that hardware costs are inversely exponentially proportional to the optimization of the database structure and queries. Fully normalizing and then selectively denormalizing will huge differences in throughput. As will optimizing queries. (Specifically to the database in use helps tons too.) 4) I cannot foresee all the possible growth, nor will the initial budget be huge. Is it then woth planning for building out / clustering for some redunacy and some load balancing upfront Other people can tell you more about replication and how it is different from clustering or load balancing. And you can use it for backup in a way too. It depends on your requirements though. If things are partitionable, then some thinking about that upfront will do you a world of good later. Depend on your application though. 7) I'd also appreciate any input from people who have used official mysql support before. We have used their support and it was excellent. Sincerely, Steven Roussey http://Network54.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error 1034: 136 when fixing table from Create Index on long table
I am running 3.23.52-Max under Linux. I now have a table with 54M rows: mysql select count(*) from targetTsObj; +--+ | count(*) | +--+ | 54549046 | +--+ 1 row in set (0.05 sec) Creating an index on this takes 1 hour 10 minutes, with this error: mysql create index targetTsObjobjId on targetTsObj (objId); ERROR 1034: 136 when fixing table The describe command shows that no index has been built. Previously, it had trouble building this same index, and complained about not being able to open a file in /tmp I suspect that /tmp was not large enough, so we changed the tmpdir variable in my.cnf to point to a file system with *plenty* of roomw and restarted the server. myisamchk seems to have no complaints about this table: bash-2.04$ myisamchk targetTsObj Checking MyISAM file: targetTsObj Data records: 54549046 Deleted blocks: 0 - check file-size - check key delete-chain - check record delete-chain - check index reference bash-2.04$ myisamchk -d targetTsObj MyISAM file: targetTsObj Record format: Fixed length Character set: latin1 (8) Data records: 54549046 Deleted blocks: 0 Recordlength: 2643 table description: Key Start Len Index Type bash-2.04$ Please let me know what this error means, how to get around it, or what additional information you need. Thanks! Here is the output of mysqladmin variable +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | basedir | / | | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | bdb_home| /export/data/dp20.a/data/mysql/ | | bdb_max_lock| 1 | | bdb_logdir | | | bdb_shared_data | OFF | | bdb_tmpdir | /export/data/dp20.a/tmp/ | | bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (August 14, 2002) | | binlog_cache_size | 32768 | | character_set | latin1
Re: Error 1034: 136 when fixing table from Create Index on long table
Hi, [root@forum] /usr/local/mysql/var perror 136 Error code 136: Unknown error 136 136 = No more room in index file Are you sure your file system can handle the size of your index file ? Regards, Jocelyn - Original Message - From: Chris Stoughton [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 22, 2002 12:26 AM Subject: Error 1034: 136 when fixing table from Create Index on long table I am running 3.23.52-Max under Linux. I now have a table with 54M rows: mysql select count(*) from targetTsObj; +--+ | count(*) | +--+ | 54549046 | +--+ 1 row in set (0.05 sec) Creating an index on this takes 1 hour 10 minutes, with this error: mysql create index targetTsObjobjId on targetTsObj (objId); ERROR 1034: 136 when fixing table The describe command shows that no index has been built. Previously, it had trouble building this same index, and complained about not being able to open a file in /tmp I suspect that /tmp was not large enough, so we changed the tmpdir variable in my.cnf to point to a file system with *plenty* of roomw and restarted the server. myisamchk seems to have no complaints about this table: bash-2.04$ myisamchk targetTsObj Checking MyISAM file: targetTsObj Data records: 54549046 Deleted blocks: 0 - check file-size - check key delete-chain - check record delete-chain - check index reference bash-2.04$ myisamchk -d targetTsObj MyISAM file: targetTsObj Record format: Fixed length Character set: latin1 (8) Data records: 54549046 Deleted blocks: 0 Recordlength: 2643 table description: Key Start Len Index Type bash-2.04$ Please let me know what this error means, how to get around it, or what additional information you need. Thanks! Here is the output of mysqladmin variable +-+- + | Variable_name | Value | +-+- + | back_log| 50 | | basedir | / | | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | bdb_home| /export/data/dp20.a/data/mysql/ | | bdb_max_lock| 1 | | bdb_logdir | | | bdb_shared_data | OFF | | bdb_tmpdir | /export/data/dp20.a/tmp/ | | bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (August 14, 2002) | | binlog_cache_size | 32768 | | character_set | latin1 | | character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /export/data/dp20.a/data/mysql/ | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 0 | | have_bdb| YES | | have_gemini | NO | | have_innodb | DISABLED | | have_isam | YES | | have_raid | NO | | have_openssl| NO | | init_file | | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | | | innodb_data_home_dir | | | innodb_file_io_threads | 4 | | innodb_force_recovery | 0 | | innodb_thread_concurrency | 8 | | innodb_flush_log_at_trx_commit | 16777216 | | innodb_fast_shutdown| ON | | innodb_flush_method | | | innodb_lock_wait_timeout| 50 | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | | | innodb_mirrored_log_groups | 1 | | interactive_timeout | 28800 | | join_buffer_size| 131072 | | key_buffer_size | 536866816 | | language| /usr/share/mysql/english/ | | large_files_support
Re: Re: Bug report
Hello Douglas, Saturday, October 19, 2002, 2:47:06 PM, you wrote: D cd /home/mysql/mysql/data D touch ibdata1 D How do I control that size? It means that you already have file ibdata1 and this file has another size that you are specifying in my.cnf ... D - Original Message - D From: Egor Egorov [EMAIL PROTECTED] D To: [EMAIL PROTECTED] D Sent: Saturday, October 19, 2002 6:35 AM D Subject: re: Bug report Douglas, Saturday, October 19, 2002, 1:08:14 AM, you wrote: D INNODB: Error:datafile /home/mysql/mysql/data/ibdata1 is of a different D size D INNODB: than specified in the my.cnf file! D INNODB:Assertion failure in thread 138207232 in file os0file.c D send bug report to [EMAIL PROTECTED] D mysqld got signal 11 D key_buffer_size = 16773120 D read_buffer_size = 131072 D sort_buffer_size = 0 D max_used_connections = 0 D threads_connected = 0 D It is possible that mysqld could use up to D key_buffer_size + (read_buffersize + sort_buffer_size) * D max_connections = 29180 bytes of memory D Hope that's OK; if not, decrease some variables in the equation D 021018 17:50:31 mysqld ended Douglas, did you carefully read error message? INNODB: Error:datafile /home/mysql/mysql/data/ibdata1 is of a different D size INNODB: than specified in the my.cnf file! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: show processlist ?
On Mon, Oct 21, 2002 at 02:51:14PM +0200, cristian ditoiu wrote: Hi . Using Mysql3.23.49 on RH 7.3 + PHP + Apache . When issuing 'show processlist' i get a lot of these : What exactly means 'sleep' ? The thread (connection) is idle. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 76 days, processed 1,603,281,982 queries (242/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Japanese, Korean and English in same mysql db
Hi, I have a need to store many languages (Japanese, Korean, Spanish, English..) in the same mysql db. I also need to search for these strings to see if they are already in the db for updates. Of course, this is a problem since the charset setting drives the logic for searching. I've browsed over posts about problems with sorting/searching mixed Japanese/English/other languages. Is the only option available to me is to preprocess these strings into something (Unicode ?) before storing and searching for them in the DB. Thanks. Jimmy Vo [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Japanese, Korean and English in same mysql db
At 16:04 -0700 10/21/02, Jimmy Vo wrote: Hi, I have a need to store many languages (Japanese, Korean, Spanish, English..) in the same mysql db. I also need to search for these strings to see if they are already in the db for updates. Of course, this is a problem since the charset setting drives the logic for searching. I've browsed over posts about problems with sorting/searching mixed Japanese/English/other languages. Is the only option available to me is to preprocess these strings into something (Unicode ?) before storing and searching for them in the DB. Thanks. Jimmy Vo [EMAIL PROTECTED] If you're talking about storing different languages in different columns, you'll be able to do this easily in MySQL 4.1. If you're talking about storing values from different languages in the same column, then you won't be able to do that unless you convert the values to a common encoding first. Unicode would be an option for this. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql grant ?
At 15:29 -0500 10/21/02, Terry Cheryl Haimann wrote: I want to give read access to all users on the localhost for a specific mysql table, I have tried the following: grant select on database.table to %@localhost; grant select on database.table to %@localhost; grant select on database.table to @localhost; None seem to work, what is the correct format? There isn't one. Wildcards aren't allowed in the user part of account names. You could grant access to the anonymous user for the database, perhaps, but the syntax for that is @localhost, not @localhost. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: Same syntax on MySQL and Microsoft SQL Server
Hi Jan, We encountered that MySQL (or MyODBC) uses different quoting characters for legal names and strings. BSJ Well, let's put it this way : tables names between BSJ square brackets only exists in Microsoft! That might be right, but this is how Microsoft works. JS flameI'm sorry, but I am VEHEMENTLY opposed to such arguments! JS As revealed in court documents, Microsoft's official strategy JS toward standards is embrace, embellish, extinguish. That same JS court found Microsoft guilty of illegal monopolistic actions -- a JS finding that that has withstood appeal. JS That sort of attitude is an invitation to let Microsoft take over JS standards. And when they accomplish that, they will change them JS every now and then, just as a way to kill off effective JS third-party resistance, like MySQL! Just look what they tried to JS do to Java, until a court injunction stopped them! JS So, if you VALUE alternatives such as MySQL, then do what is right JS and port your non-standard code on your own time! MySQL did not JS make you put the non-standard square brackets in your code, so JS why should they be responsible for fixing your broken code?/flame You've a very radical position. Maybe this is the wrong place for such a discussion and maybe my english isn't well enough, but I'll try it. We've thousands lines of code and hundreds of users. I can gurantee that some users insist on a MS solution. Not all, but some. If we break MS support we will loose customers (and a lot of time) and it's harder to get new. I think, if we can offer both, there's a high probability, that many customers will turn to mySQL because of the costs. MySQL s a great thing but I cant't recommend it, if we loose MS support Quoting like Micrsofot should not become standard, but an option. JS That sounds like an excellent job for a third-party tool, NOT JS something to be built-in to MySQL! Why don't you get started JS building it, Christian? Sounds like a few lines of Perl code. I'm JS sure the MySQL team would be willing to make it available to JS others after you build it. JS That's the best of all worlds: people can use your tool if they JS need it, MySQL will not be encumbered with maintaining even more JS #ifdefs, and you will be a hero. Get working on it! In my opinion ths tool is called MyODBC. Everyone who wants to migrate slightly must use ODBC (or ADO). We're already using classes for SQL translating, but only for reporting and other third party tools. It's hell! I think a runtime option for MySQL (not compiler option) would be adequate. Perl won't do it. And I think, I am not the only one who will like it. JS But please understand, there are those of us who will DISLIKE it! Why? It must be a great pleasure to convince MS useres. MySQL has the power. And it must be VERY open to get a lot of more developers. And you must remember, developers don't pay much. The end user pays. So you must get more developers! And it's much more easy to say you've an alternative. JS Competition is good. Standards are good. Microsoft: Just Say No. That's too easy. I think they made some great stuff (their office is unbeatable), and I don't like their proceeding as well (e.g. I'm using 'The Bat!' and LaTex), but the end user isn't interested in it. In the end, only 5% of our customers would accept pure Linux solutions, because other needed software is only available for Windows. What the hell we can do. Only coexist! And get more and more of their portion. Heiko __ Gesendet von Yahoo! Mail - http://mail.yahoo.de Möchten Sie mit einem Gruß antworten? http://grusskarten.yahoo.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What is relay-bin log?
Well... Then when is the time for replication logs to be deleted? Can I change the conditions for it? I'm not able to find out related section in the official manual. No mention about auto deletion, either. -- Chung Ha-nyung alita@[neowiz.com|kldp.org] Sayclub http://www.sayclub.com NeoWiz http://www.neowiz.com -Original Message- From: Michael Widenius [mailto:monty;mysql.com] Sent: Monday, October 21, 2002 9:22 PM To: Á¤ Çϳç Cc: 'Victoria Reznichenko'; [EMAIL PROTECTED] Subject: RE: What is relay-bin log? Hi! Chung == Chung Ha-nyung [EMAIL PROTECTED] writes: Chung If all works correctly, can I delete replication logs by RESET SLAVE Chung safely? Chung I'd like to configure two servers with two-master scheme, each is the Chung master of Chung another. relay logs should automaticly be deleted and you should never have to do RESET SLAVE to delete them. RESET SLAVE should only be used when you want the slave to not be a part of the replication anymore. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PRI key
In MYSQL I'm using MYISAM. Should the 2 fields in each of the Stars TitleGenres table be Primary keys since they are 'tied' to other fields? Studios (StudioName, StudioID) Genres (GenreName, GenreID) Titles (VideoTitle, Details, StudioID, GenreID, BitRateID, TitleID) Actors (F_Name, L_Name, ActorID) Stars (TitleID, ActorID) TitleGenres (TitleID, GenreID) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how do you define a relationship?
Unfortunately this goes well beyond the scope of my original question. Although the inputs that you all have provided thus far have been a very good read, I still don't grasp the concept of defining a relationship. Is it simply referring to a primary key in a field other than the one where the key was defined? Or is there some other way to define a key that I don't see? All I'm looking for is a simple example, syntax excerpt, etc...that shows me (only the slow learner level) exactly how to define a relationship. Unfortunately this goes well beyond the scope of my original question. Although the inputs that you all have provided thus far have been a very good read, I still don't grasp the concept of defining a relationship. Is it simply referring to a primary key in a field other than the one where the key was defined? Or is there some other way to define a key that I don't see? All I'm looking for is a simple example, syntax excerpt, etc...that shows me (only the slow learner level) exactly how to define a relationship. CREATE TABLE parent ( parent_id INT UNSIGNED NOT NULL PRIMARY KEY ) TYPE=innodb; CREATE TABLE child ( child_id INT UNSIGNED NOT NULL PRIMARY KEY, parent_id INT UNSIGNED NOT NULL, INDEX ( parent_id ), FOREIGN KEY ( parent_id ) REFERENCES parent( parent_id ) ) TYPE=innodb; CREATE TABLE grandchild ( grandchild_id INT UNSIGNED NOT NULL PRIMARY KEY, child_id INT UNSIGNED NOT NULL, INDEX ( child_id ), FOREIGN KEY ( child_id ) REFERENCES child( child_id ) ) TYPE=innodb; To see the relationships at work, try to insert into grandchild a child_id that doesn't exist in child, or try to insert into child a parent_id that doesn't exist in parent. hth PB - filter fodder: mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Where is the mysqladmin?
Hi there, I have just installed the mySQL 3.23 and don't know nothing about it yet. When I installed it there was a message telling me to change the mySQl root user's password with the command mysqladmin, but I don't have this file on my computer. Do I need to install something else? I have installed the mySQL rpm file on a computer runing Linux. Thanks? __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
where is the mysqladmin?
Hi there, I'm have just installed mySQL and I'm completelly new at it. When it was installed it showed a message telling me to change the mySQL root user's password using the mysqladmin, but I don't have this file. I installed the mySQL 3.23 using the rpm file on a computer runing Linux. So, do I need to install something else to have this executable available? Thanks __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using AUTO_INCREMENT like SEQUENCE - Resolved
At 16:57 +0200 10/21/02, Joseph Bueno wrote: Hi all, Sorry to jump in the middle of this thread but there is a much simpler way to generate sequence numbers: You create an auxiliary table with a one row: CREATE TABLE sequence ( code int(11) DEFAULT '0' NOT NULL ); INSERT INTO sequence VALUES (0); And, each time you need a new sequence number: UPDATE sequence SET code=LAST_INSERT_ID(code+1); SELECT LAST_INSERT_ID(); We use this method in our applications and it works well. (and I didn't invent it, it is described in Paul DuBois'book ;)) And I didn't invent it, either, it's described in the MySQL manual! :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SHOW question
At 17:06 +0200 10/21/02, Fabio Checconi wrote: I'm writing a program using the C API. When I do a query SHOW TABLES am I sure that on every installation it will return a result set of one column containing the tables name, or there are systems where that's not true ? Am I sure that from a SHOW FIELDS FROM table i'll get a result set with the column name in the first field and the type in the second (with other info on other fields) ? It sounds like you're not sure at all. :-) But to answer what I suspect you're asking: - The output of SHOW TABLES always contains a single column. - The output of SHOW FIELDS always contain columns names and types in the first two columns. Note the that output may consist of no *rows*, but that's what the columns are used for. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fw: Which files to download/install - problems running MySQL
At 20:22 21/10/2002 +0100, Nikki Mason wrote: Hi, My email below was rejected for some reason, so I am trying again to send it. No problems, but I need to clarify you that behavior: 1- When you start the server with: name_server --standalone --console. The DOS prompt screen is owned by the MySQL server process, in another words the behavior you saw is correct. You minimized this screen and open another DOS prompt session for to work with the mysql.exe client. ---server prompt screen--- C:\mysql\binmysqld-opt --console --standalone 021022 1:23:47 InnoDB: Started mysqld-opt: ready for connections -- ---client prompt screen--- C:\mysql\binmysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.53-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql -- 2- If you don't want to see the screen used to start the server, only issue the command: name_server. In this case you should see the MySQL server process in the ALT+CTRL+ DEL screen and also open another DOS prompt for to work with the mysql.exe client. 3- For to shutdown the server, you use the mysqladmin.exe tool: mysqladmin shutdown. Hope now is clarify for you: Regards, Nikki - Original Message - From: Nikki Mason [EMAIL PROTECTED] To: Miguel Angel Solórzano [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 20, 2002 8:41 AM Subject: Re: Which files to download/install - problems running MySQL Miquel, I tried what you suggested, but still it didn't work properly. For the server: C:\cd mysql\bin C:\mysql\binmysqld-opt --standalone --console 021020 8:20:30 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. The cursor did not return to the prompt to allow anymore commands to be typed. (I haven't created a c:\my.cnf or c:\windows\my.ini) For the client: C:\mysql\binmysql -uroot -p Enter password: Again, nothing more was displayed on the screen and the cursor did not return to the prompt to allow anymore commands to be typed. Regards, Nikki Mason - Original Message - From: Miguel Angel Solórzano [EMAIL PROTECTED] To: Nikki Mason [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, October 19, 2002 9:29 PM Subject: Re: Which files to download/install - problems running MySQL At 15:16 19/10/2002 +0100, Nikki Mason wrote: Hi, Hi, I wish to learn SQL, and as some of my friends already use MySQL, they recommended that I too use it. Please can you tell me what I need to download and install to get me going. I have: Pentium III 450Mhz, 256Mb Ram Windows 98 I don't have a C or C++ compiler installed. My PC is not connected to a network - it is standalone. I had earlier downloaded just the zip file for version 4.0.4-beta (Windows). I unzipped it and ran the 'setup' program. Once setup had completed I tried the test on the server - at no point had I run the Admin program or created the my.cnf or my.ini. I opened a dos window and from the directory c:\mysql\bin entered: mysqld --standalone No other text was shown in the dos window and it appeared to hang. The whole computer also seemed to 'hang'. I had to use ctrl C to kill the application and get back normal operation to my PC. Why you had killed the mysqld server ?. The mysqld.exe is a console application that when started with --standalone option it works in the background. If you want to see the console screen then issue: C:\mysql\binmysqld-opt --standalone --console 021019 17:25:31 InnoDB: Started mysqld-opt: ready for connections and for to work with the mysql client: C:\c:\mysql\bin\mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.53-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql after a fresh installation left the root password blank. for to shutdown the server: C:\c:\mysql\bin\mysqladmin shutdown 021019 17:27:27 mysqld-opt: Normal shutdown 021019 17:27:27 InnoDB: Starting shutdown... 021019 17:27:29 InnoDB: Shutdown completed 021019 17:27:29 mysqld-opt: Shutdown Complete I then tried manually creating the file c:\my.cnf and populated with the information given in the installation instructions. The server still hung. I then tried using the admin program with the similar results - the green traffic light was showing, but the dos window that was displayed appeared 'hung' and
Question about state of tables
Hello; newbie to this list. I wonder if there is a clear method of obtaining a user's permissions on a table. The reason I ask is that I am attempting to setup a catalog solution for a user I am hosting, and I believe I have provided access to the d/b but I need to confirm. I thought I might get this using mysqladmin, but it does not seem available. Is there a log somewhere of transactions on the table? Mark == Mark Jaffe | (408) 972-9638 (home) Chief Wizard| (408) 807-1530 (cell/page/voicemail) Computer Wizards| (425) 795-6421 (FAX) [EMAIL PROTECTED] | http://www.wizdev.net/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sun Solaris 2.7 Sparc static binary of MySQLGUI 1.6
When I tried to execute Sun Solaris 2.7 Sparc static binary of MySQLGUI 1.6, I get the error that it can't find, libstdc++.so.2.10.0. I thought this was suppose to be a statically linked executable? I can fine the .a file but not the .so file. - Bob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems with char field
Hi MySQL-team!
I develop WIN32 software that uses MySQL database. My software uses MySQL
ODBC driver 3.51 and ADO interface. MDAC 2.7 is installed. My problem is
that while I'm trying to update MySQL record contained char(15) field, I get
an error Microsoft OLE DB Provider for ODBC Drivers Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available. No
work was done. I don't have such problem with numeric fields. The numeric
fields are updating normally with the same code.
Here is a sample of my code:
try
{
_RecordsetPtr pRecordset;
HRESULT hr;
_bstr_t btmp;
hr = pRecordset-Open(vTable, vConnection,adOpenForwardOnly,
adLockOptimistic, adCmdTableDirect);
hr = pRecordset-AddNew();
...
pRecordset-Fields-GetItem(Lid)-Value=btmp;
...
pRecordset-Fields-GetItem(Lproto)-Value=btmp;
...
pRecordset-Fields-GetItem(Lsrc_ip)-Value=btmp;
}
The fields id and proto are numeric, the field src_ip is char. And I
get the above error while updating the src_ip field. Do you have any
ideas? Can you help me?
Yours sincerely,
Alex Bash
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
