problems with foreign keys revisited
Hi there, I am having some issues between two tables and foreign keys. Here is the two tables I have setup. | complaints |CREATE TABLE `complaints` ( `complaintID` int(11) NOT NULL auto_increment, `ticket_number` varchar(20) NOT NULL default '', `complainant_name` varchar(100) NOT NULL default '', `program` varchar(100) NOT NULL default '', `date_added` datetime NOT NULL default '-00-00 00:00:00', `date_received` date NOT NULL default '-00-00', `date_response_due` date NOT NULL default '-00-00', `date_response_sent` date NOT NULL default '-00-00', `breach_complaint` tinyint(1) NOT NULL default '0', `nuisance_complaint` tinyint(1) NOT NULL default '0', `complaint_typeID` int(11) NOT NULL default '0', `refererID` int(11) NOT NULL default '0', `centreID` tinyint(4) NOT NULL default '0', `divisionID` tinyint(4) NOT NULL default '0', `complaint_statusID` tinyint(4) NOT NULL default '0', `userID` int(11) NOT NULL default '0', PRIMARY KEY (`complaintID`), UNIQUE KEY `complaintID` (`complaintID`), FOREIGN KEY (`complaintID`) REFERENCES `complaint_threads` (`complaintID`) ON DELETE CASCADE ) TYPE=InnoDB | | complaint_threads | CREATE TABLE `complaint_threads` ( `threadID` int(11) NOT NULL auto_increment, `message` text NOT NULL, `complaintID` int(11) NOT NULL default '0', `date_added` datetime NOT NULL default '-00-00 00:00:00', `userID` int(11) NOT NULL default '0', `parent_threadID` int(11) NOT NULL default '0', PRIMARY KEY (`threadID`), KEY `complaintID` (`complaintID`) ) TYPE=InnoDB | The complaints table I want to join to the complaint_threads table using complaintID. I dont know if I have set this up right, but i'm getting foreign key constraint errors :\ How can i set this up properly, so when I delete an entry in complaints it will cascade delete in complaint_threads . Please let me know. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stupid Newbie Query Help with AND and OR logical operators
Here's my question. I wrote this query: Select * FROM name WHERE last LIKE d AND choice1=2 OR choice2=2 OR choice3=2; What I'm looking for are records that satisfy the LIKE d condition But then, Only one of the three other conditions need be true: choice1=2 choice2=2 choice3=2 I want to cover the possibilities, e.g., choice1=2 choice2=1 choice3=1 choice1=1 choice2=2 choice3=whatever Or choice1=1 choice2=1 choice3=2 Does this make sense? The query I've written doesn't seem quite right. Because of the AND following the LIKE d condition, it seems like all the records will have to have choice2 equaling 2. Some help clarifying this issue would be appreciated. Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL and OpenMosix
On Thu, May 06, 2004 at 11:29:18AM -0600, Alfredo Cole wrote: El Jue 06 May 2004 11:05, escribió: On Thu, May 06, 2004 at 06:55:38AM -0600, Alfredo Cole wrote: At the time I wrote Chapter 8 of High Performance MySQL, I tried to discuss the available options: http://www.oreilly.com/catalog/hpmysql/toc.html However, some of the commercial information was hard to come by, so if you're looking at those, you may need to discuss with the vendors too. Jeremy: I have ordered your book from Amazon.com. Great, thanks. I hope it's helpful. But I am not planning to use a commercial solution. I want to use OpenMosix, which is released under the GPL. Any suggestions would be welcome. My knowledge of OpenMosix is extremely limited. I've not heard of anyone successfully using MySQL with OpenMosix for fail-over. That doesn't mean it hasn't been done, but it'd be news to me. I assume you've also asked on the relevant OpenMosix list(s). One would hope they'd know. But maybe not. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Features for Evaluation
Joseph, Have a look at http://dev.mysql.com/tech-resources/features.html. It lets you compare MySQL to various other popular databases. Warning: I'd take this information with some caution. I don't mean to suggest that it is false, just that the emphasis seems to be on making MySQL look good and glossing over its limitations at times. For example, when comparing MySQL 4.0.13 with DB2 Version 8.1, they note that DB2 8.1 doesn't have a CURRENT_USER function; I assume that's true (I'm running DB2 7.2 and haven't upgraded yet) but it may lead you to believe that there is no way to determine the identify of the current user in DB2. In fact, the 'user' keyword will do exactly that, e.g. 'select user from sysibm.sysdummy' *will* return the user id of the current user. With regards to glossing over limitations, one example is the 'subqueries' comparison: they admit that MySQL doesn't have them and that DB2 does but the fact that this is a single entry in a rather large table suggests that this is of minimal concern and is no more concern than the 'current_user' vs. 'user' point I mentioned. In fact, the absence of subqueries in MySQL is a big issue for a great many MySQL users since subqueries are such a fundamental part of most SQL. MySQL will be providing subqueries in MySQL 4.1.x but some products, like DB2, have had them from Day 1. Rhino - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 06, 2004 11:09 PM Subject: Features for Evaluation Hi, I am setting up a lab to evaluate main features of MySQL so that provide advise for other departments to consider for their choice of database. Do you have a list of such features? Or do you have similar evaluation reports for my reference? Thanks, Joseph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow login
Hi, I'm using MySQL 3.23.53 on W2000 and I have a following problem: when I restart the database server then the first attempt to connect from any client program last cca 30 seconds (too long!!), each next attempt to connect lasts less than one second. The traffic is usually very small, so it can't be caused by it. I tried several client applications and all behave in the same way, so I guess the problem is inside the database. Can anybody help me? Thanks Jiri Matejka, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best design in an conditional relationship
Hi, I would like to hear from you what would be the best way to design tables with a conditional relationship between them. I am callin this a conditional relationship, because the external link can be to one table or to another, but not for both at the same time, in the same record. Here is the example. Table people has data about a person, such as name and birthday. Table business has data about a business (office, company). Both have an (int) id field. Table customer is what really matters. It has some fields about the client's internal data. This table will have a type field which points to either people or business. Depending on what kinf of client this is. This is the problem. An customer can be either a person (from people table) or a business (from business table). So here is some ideas to tie the tables: 1 - A field client which is the id of the client in either the table people or busisness. In this situation both people and business tables have an id with auto_increment. 2 - Almost like 1, but making the id field from both table being unique between them. So if there is an id '12' in people, there will be no '12' in business's id. One way to do it would be to have an special table with an auto_increment field that could be generated in order to use as the id in the table people or business. So, before inserting a new recording in those tables, you insert a row in this special table, just to get the auto_incremented number. This number would be used as the id in those tables and this would avoid to have duplicated ids between the tables (and no need for locking). 3 - Instead of having a field client like those examples, it would have two fields: people and business. Each one is a external link to its corresponding table. The caveat in this situation is that only one of this fields should have valid information. This would be specified by the field type. The first example is the simplest to implement if I am doing the programing (in Perl for example). However, I am not sure how I would do this with a 3rd party application such as access. The second example is the first with a way to avoid repeated 'id's, so that if for some reason it points to the wrong table, there will bo no key with the id it is looking for. Probably a silly solution. The third solution is probably more easy to implement the relationship, however I would still have to say which one should get the data based of customer.type field. So, for this situation, which solution is the best one, or what type of solution is most commonly used? Sure I could choose whatever I feel like when I am programing the interface with perl, php, or whatever I use. However, I am looking for a design that would also work on 3rd party SQL applications like Access, OpenOffice and Crystal Reports without much trouble. So, what are your thoughts? - Raul Dias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext searching
I beleive this is the built-in word list file you were looking for. And the
word beyond was in the list. It would probably be helpful if there were
a SHOW command that listed the active built-in stopwords.
Ed
-
#include ftdefs.h
ulong ft_min_word_len=4;
ulong ft_max_word_len=HA_FT_MAXCHARLEN;
ulong ft_query_expansion_limit=5;
char ft_boolean_syntax[]=+ -()~*:\\|;
const HA_KEYSEG ft_keysegs[FT_SEGS]={
{
HA_KEYTYPE_VARTEXT, /* type */
63, /* language (will be overwritten) */
0, 0, 0, /* null_bit, bit_start, bit_end */
HA_VAR_LENGTH | HA_PACK_KEY, /* flag */
HA_FT_MAXBYTELEN, /* length */
HA_FT_WLEN, /* start */
0,/* null_pos */
NULL /* charset */
},
{
/*
Note, this (and the last HA_KEYTYPE_END) segment should NOT
be packed in any way, otherwise w_search() won't be able to
update key entry 'in vivo'
*/
HA_FT_WTYPE, 63, 0, 0, 0, HA_NO_SORT, HA_FT_WLEN, 0, 0, NULL
}
};
const struct _ft_vft _ft_vft_nlq = {
ft_nlq_read_next, ft_nlq_find_relevance, ft_nlq_close_search,
ft_nlq_get_relevance, ft_nlq_reinit_search
};
const struct _ft_vft _ft_vft_boolean = {
ft_boolean_read_next, ft_boolean_find_relevance, ft_boolean_close_search,
ft_boolean_get_relevance, ft_boolean_reinit_search
};
FT_INFO *ft_init_search(uint flags, void *info, uint keynr,
byte *query, uint query_len, byte *record)
{
FT_INFO *res;
if (flags FT_BOOL)
res= ft_init_boolean_search((MI_INFO *)info, keynr, query, query_len);
else
res= ft_init_nlq_search((MI_INFO *)info, keynr, query, query_len, flags,
record);
return res;
}
const char *ft_stopword_file = 0;
const char *ft_precompiled_stopwords[] = {
#ifdef COMPILE_STOPWORDS_IN
/* This particular stopword list was taken from SMART distribution
ftp://ftp.cs.cornell.edu/pub/smart/smart.11.0.tar.Z
it was slightly modified to my taste, though
*/
a's,
able,
about,
above,
according,
accordingly,
across,
actually,
after,
afterwards,
again,
against,
ain't,
all,
allow,
allows,
almost,
alone,
along,
already,
also,
although,
always,
am,
among,
amongst,
an,
and,
another,
any,
anybody,
anyhow,
anyone,
anything,
anyway,
anyways,
anywhere,
apart,
appear,
appreciate,
appropriate,
are,
aren't,
around,
as,
aside,
ask,
asking,
associated,
at,
available,
away,
awfully,
be,
became,
because,
become,
becomes,
becoming,
been,
before,
beforehand,
behind,
being,
believe,
below,
beside,
besides,
best,
better,
between,
beyond,
both,
brief,
but,
by,
c'mon,
c's,
came,
can,
can't,
cannot,
cant,
cause,
causes,
certain,
certainly,
changes,
clearly,
co,
com,
come,
comes,
concerning,
consequently,
consider,
considering,
contain,
containing,
contains,
corresponding,
could,
couldn't,
course,
currently,
definitely,
described,
despite,
did,
didn't,
different,
do,
does,
doesn't,
doing,
don't,
done,
down,
downwards,
during,
each,
edu,
eg,
eight,
either,
else,
elsewhere,
enough,
entirely,
especially,
et,
etc,
even,
ever,
every,
everybody,
everyone,
everything,
everywhere,
ex,
exactly,
example,
except,
far,
few,
fifth,
first,
five,
followed,
following,
follows,
for,
former,
formerly,
forth,
four,
from,
further,
furthermore,
get,
gets,
getting,
given,
gives,
go,
goes,
going,
gone,
got,
gotten,
greetings,
had,
hadn't,
happens,
hardly,
has,
hasn't,
have,
haven't,
having,
he,
he's,
hello,
help,
hence,
her,
here,
here's,
hereafter,
hereby,
herein,
hereupon,
hers,
herself,
hi,
him,
himself,
his,
hither,
hopefully,
how,
howbeit,
however,
i'd,
i'll,
i'm,
i've,
ie,
if,
ignored,
immediate,
in,
inasmuch,
inc,
indeed,
indicate,
indicated,
indicates,
inner,
insofar,
instead,
into,
inward,
is,
isn't,
it,
it'd,
it'll,
it's,
its,
itself,
just,
keep,
keeps,
kept,
know,
knows,
known,
last,
lately,
later,
latter,
latterly,
least,
less,
lest,
let,
let's,
like,
liked,
likely,
little,
look,
looking,
looks,
ltd,
mainly,
many,
may,
maybe,
me,
mean,
meanwhile,
merely,
might,
more,
moreover,
most,
mostly,
much,
must,
my,
myself,
name,
namely,
nd,
near,
nearly,
necessary,
need,
needs,
neither,
never,
nevertheless,
new,
next,
nine,
no,
nobody,
non,
none,
noone,
nor,
normally,
not,
nothing,
novel,
now,
nowhere,
obviously,
of,
off,
often,
Finding Empty Records
I am trying to find all my empty records. I am looking for NULLs and Empty strings but is there a simple way to just as this? I thought MySql may have something built in. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hash Index
My grasp of computing theory isn't terribly strong or theoretical so I hope others will correct me if anything I say here is inaccurate I got curious to see if I could find a good basic description of hashing online so I did a Google search on hash index. I was surprised how many hits I had to rule out because they weren't terribly clear; most assumed too much prior knowledge to be clear to somone without the basic concepts behind them. 1. I eventually found one at http://ciips.ee.uwa.edu.au/~morris/Year2/PLDS210/hash_tables.html that may still be a bit theoretical for some tastes but it has a nifty animation that illustrates hashing functions at work which I think may clarify matters somewhat. The link for the animation is near the bottom of the page. By the way, this page does not use the term 'hash index' very much but I think it would be safe to think of a 'hash index' as just being a 'hash table' that points to a data table in a relational database. Again, I'm counting on the people with more theory background to jump in if that is not reasonable. 2. I found another explanation at http://bo.majewski.name/bluear/gnu/GLib/ch03.html which includes some C language code, which may be of interest if you program in C. If these explanations doesn't answer your questions, you could try some web searches of your own and/or consult a good computer science textbook that explains standard programming algorithms. I don't happen to have such a book so I can't recommend one in particular but others may be able to suggest a good one. Rhino - Original Message - From: Lou Olsten [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 06, 2004 6:01 PM Subject: Hash Index This one is more curiosity than a problem. I have read the docs about HASH indexes and how they are used, but I'm just wholly unfamiliar with WHAT a HASH index is. I'm only familiar with the term 'hash' as it relates to encryption. What exactly IS a hash index? Just curious, Lou -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Two problems with prepared statements in ver 5.0.0
Hello, I have been working with the C API in version 4.1.1. I encountered a problem which I thought might be resolved in version 5.0.0, so I have loaded the RPMs for version 5.0.0 onto my development RedHat Linux system. - Problem 1 - can't link: According to documentation, many of the names of prepared statement functions have changed in version 5.0.0. I changed my C code to use the new names, but when I try to link, all of the new names are not found in the libraries. Here are some sample error message: sql.o: In function `MYSQL_ADD_PREP': sql.o(.text+0x1ac3): undefined reference to `mysql_stmt_init' sql.o(.text+0x1b2e): undefined reference to `mysql_stmt_prepare' sql.o(.text+0x1b7a): undefined reference to `mysql_stmt_result_metadata' sql.o(.text+0x1db6): undefined reference to `mysql_stmt_init' sql.o(.text+0x1e22): undefined reference to `mysql_stmt_prepare' sql.o(.text+0x1e6e): undefined reference to `mysql_stmt_param_count' sql.o(.text+0x2127): undefined reference to `mysql_stmt_bind_param' I had all of this working with version 4.1.1, except that I had a problem trying to use two different prepared statements alternately. - Problem 2 - mysql_stmt_result_metadata() returns int. According to documentation, this function should return a pointer to MYSQL_RES. In version 4.1.1, the previous function - named mysql_get_metadata - did work that way. But now I get a warning when I compile saying: coresql.c:183: warning: assignment makes pointer from integer without a cast - Is there something I am missing, or is this version not well tested for these features? Is anyone using this? Thanks, Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stupid Newbie Query Help with AND and OR logical operators
You need parenthesis around cond1 OR cond2 OR cond3 otherwise effectively you are going to get all records matching the following: last d AND choice1=2 + choice2=2 + choice3=2 Therefore, you select statement should be written: SELECT * FROM name WHERE last LIKE d AND (choice1=2 OR choice2=2 OR choice3=2); Bernard On Friday 07 May 2004 13:51, Bob Cohen wrote: Here's my question. I wrote this query: Select * FROM name WHERE last LIKE d AND choice1=2 OR choice2=2 OR choice3=2; What I'm looking for are records that satisfy the LIKE d condition But then, Only one of the three other conditions need be true: choice1=2 choice2=2 choice3=2 I want to cover the possibilities, e.g., choice1=2 choice2=1 choice3=1 choice1=1 choice2=2 choice3=whatever Or choice1=1 choice2=1 choice3=2 Does this make sense? The query I've written doesn't seem quite right. Because of the AND following the LIKE d condition, it seems like all the records will have to have choice2 equaling 2. Some help clarifying this issue would be appreciated. Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error: Too many open files
Hello, this maybe a more related issue with DBI API but I thought I try first posting here to see why we would be getting this error (24) - Too many open files Error Message: DBD::mysql::st execute failed: File './mysql/user.MYD' not found (Errcode: 24) What would be causing this type of error too occur Thx's -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hash Index
Hope this helps
Very much. Thanks to all who responded.
http://ciips.ee.uwa.edu.au/~morris/Year2/PLDS210/hash_tables.html was
helpful as well.
Lou
- Original Message -
From: Andy Ford [EMAIL PROTECTED]
To: Lou Olsten [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, May 05, 2004 6:33 PM
Subject: Re: Hash Index
A hash is a key value pair
i.e. if you want a value for a unique key (in perl anyway) you can do
this...
%status = (
1 = true,
0 = false
);
If I have a variable that hold an integer such as
$test = 1 I can do the following.
if($status{$test} eq true) {
do something
}
I have passed the variable $test as a key to the hash %status, and it
has returned a value for that unique key.
It's very similar in mySQL
I come from a C background and now working extensively in Perl - this is
a very powerful feature.
Hope this helps
Andy
On Thu, 2004-05-06 at 23:01, Lou Olsten wrote:
This one is more curiosity than a problem.
I have read the docs about HASH indexes and how they are used, but I'm
just wholly unfamiliar with WHAT a HASH index is. I'm only familiar with
the term 'hash' as it relates to encryption. What exactly IS a hash
index?
Just curious,
Lou
--
perl -e 'print qq^;@) [###]^^qq^z\.MY{eLQ9^'
in:control developer, Telindus, RG27 9HY
DDI: +44 1256 709211, GSM: +44 7810 636652
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
test disregard
test disregard -- = - Linux rocks!!! http://www.dedserius.com/ = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL query problem in conjunction with PHP
Hi I found an instruction on the net concerning Date Arithmetic and which seems to be exactly what I need to hel me get going..however there seems to be either a mistake on my behalf or on the writers behalf. Can anyone say whats wrong with this query $query2=UPDATE KK_Fatalie SET datum = DATE_ADD(date,INTERVAL intervall' ''.$typeArray[$row-type].)WHERE kk_ID=.$row-kk_ID; The query is suppose to result in the following; do an UPDATE on table KK_Fatalie and set the column datum equal to todays date increased with the interval stated in the column intervall where the kolumn kk_ID equals the kk_ID in my earlier php query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't find host.MYD
I know this is a thorny old problem, but I'm having trouble resolving it. Here's the set-up. I'm running Linux. The MySQL data directory (as configured in my.cnf) is /share/mysql/data/ because I was sharing the data between Linux and Windows on a dual-boot system. This set-up has been working fine for months. Some time ago I stopped using and removed Windows. So today I decided that the /share partition should no longer be FAT32. I backup-up everything on it (just simply copied the files to another partition) reformatted as ext3 and copied everything back. Now mysqld won't keep running - ie, it starts up then immediately shuts down again - and I found a message in the mysqld.log file saying: 040508 11:10:24 mysqld started 040508 11:10:24 /usr/sbin/mysqld: Can't find file: 'host.MYD' (errno: 2) 040508 11:10:24 mysqld ended But, the file host.MYD is definitely in the /share/mysql/data/mysql/ directory, although it appears to be 0 bytes (?). I figured this might be a file permissions error, so I've tried changing the owner for all the mysql files to 'mysql' (group is currently 'root') and permissions are 755. This hasn't solved the problem. Any ideas would be most welcome. @+ Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Conference Presentations
Hi Reed, Unfortuntaely I am unable to answer your question, but my presentation Using MySQL in a Japanese environment ... and avoiding common pitfalls is online at http://www.be-known-online.com/mysql/ Hope that helps. On Friday 07 May 2004 12:02, Ed Reed wrote: Where's the presentations? They were supposed to be on the website at the end of last week. Thanks -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit in sub-query - when can we expect it?
Also interested in answer to this one. Terry Riley --Original Message- Hi List, When can we expect limits in sub-queries? I am currently on 4.1.0. 1235 - This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Query: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Directory Permissions on files
Yes, the file permissions do affect whether the table can be updated. The files need to be writable (in the filesystem sense) by the user running mysqld. By default MySQL creates directories umask 0700 and files 0660 unless the UMASK env variable is set differently when mysqld is started. If you chown to the user running mysql and chmod the files to an appropriate umask you should see this issue go away. Robert Reed wrote: Greetings, I've recently inherited a FreeBSD server running MySQL 3.23.54. It's good and stable. I have a second server that runs as a slave to the first. Everything goes smoothly until I make changes to a certain table on my master. This will kill the slave with the error that this table is read-only. These are all MyISAM tables. I noticed recently that the various directories have different permissions and access levels on them and wondered what the correct levels should be. And...does this even have an effect on whether the table can be written to? Thanks in Advance = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stupid Newbie Query Help with AND and OR logical operators
Thanks to Bernard, Sunmaia, and Ingo! I knew the query wasn't quite right. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL and OpenMosix
El Vie 07 May 2004 00:20, escribió: (...) My knowledge of OpenMosix is extremely limited. I've not heard of anyone successfully using MySQL with OpenMosix for fail-over. That doesn't mean it hasn't been done, but it'd be news to me. I assume you've also asked on the relevant OpenMosix list(s). One would hope they'd know. But maybe not. Jeremy This is the reply I got from Moshe Bar (OpenMosix developer): *** Even if threads of an application can't migrate, other processes (which eat up resources away from the application in question) can migrate away and therefore speed up the application. I can't stress enough that it is not the migration that speeds up the applications, it's the load balancing that increases throughput. Moshe *** So there may be hope. I will setup two computers with OM next week and see what I can do with them. Thank you and regards. -- Alfredo J. Cole http://www.acyc.com http://www.clshonduras.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA from memory?
Hi MySQL Gurus, I am pretty green at SQL DB anything, so please bear with me. I would like to input new/pertinent data into my main table as fast/efficiently as possible. From reading Paul DuBois books, the Reference Manual and a few other sources I am convinced that LOAD DATA will be faster than the multitude of individual INSERTS that I would end up doing. So, assuming that doing something from memory is always faster than doing that something from a file - any filesystem caching aspects being a caveat - is it possible, (and if so how) to use LOAD DATA INFILE on something I already have in memory? (like my made-to-order array?) I am using perl - so maybe I can do all this inside an open filehandle and use the FH as my INFILE? Maybe access my array that way?? I apologize if this has been asked/beaten to death. Searching these mail lists yielded nothing. I looked, honestly. My environment: MySQL 4.0.18 perl 5.6 FreeBSD 5.1 Thanks for any help, Ray Spence __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stupid Newbie Query Help with AND and OR logical operators
Bob Cohen wrote: Here's my question. I wrote this query: Select * FROM name WHERE last LIKE d AND choice1=2 OR choice2=2 OR choice3=2; To predict the outcome of this query requires knowledge of the precedence of AND vs. OR in mysql. I can't find it documented anywhere, but I think AND has higher precedence than OR. Hence, your WHERE clause evaluates as WHERE (last LIKE d AND choice1=2) OR choice2=2 OR choice3=2 which is not what you want. My advice is to never rely on precedence. Instead, I suggest you *always* use parentheses to make it clear what you want. From your description, I think you need: WHERE last LIKE d AND (choice1=2 OR choice2=2 OR choice3=2) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I borrow a working model (database/PHP system)?
Is it possible to copy a working MySQL database and a page driven by that database to another website? If so, it would a great learning resource - a working model. I jumped into the world of PHP and MySQL a few weeks ago. I'm now working with PHP includes, but I've barely scratched the surface of MySQL. I have made MySQL databases, but I haven't yet learned how to use them to manipulate pages, nor have I navigated the username/password maze and got a database online. Rather than ask a zillion questions on forums, I'd like to get my hands on a working model, which would probably fill in many of the blanks. Could I give someone my temporary password and ask them to copy a simple database and matching page to one of my websites, or is that even feasible? The second option would be to solicit the code for a webpage driven by a database, recreate it and publish it online, then get some specs regarding the database, create it, and publish it online. Below is a brief synopsis of the project I'm working on. * * * * * * * * * * I'm working on a series of websites focusing on the world's nations and the 50 states. I'm using PHP includes to designate the name and linking code for every jurisdiction. Thus, $myname = 'Alaska' transforms the title element to Alaska on a particular page, and identifies it as a member of the United States and North America, with linking codes us and na. This forms a bread crumbs type link sequence at the top of each page: Home World North America U.S. Alaska I also use nation and state codes to identify css elements. For example, the page's title might be: div class=title id=title? php echo ak? echo myname/div Thus, the div's id translates into id=titleak for Alaska, with the word Alaska replacing echo myname. Many people have told me to use a database, but it's so complex. Rather than spend another two weeks figuring it out, I'd like to get my hands on a working model so I can understand some of the concepts discussed in MySQL's tutorials. Thanks! __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help getting started
Hello, I have a number of databases, and associated web page files written for the SmithMicro product WebDNA. Basically it is a shopping cart program, but I am using it for database driven dynamically produced web pages. I need to convert my files to MySQL and use php web pages as the front end for page display, adding editing database content. I have textbooks and various internet resources to help me with the language and syntax of these two products, but I need to have an example of some working files that I can begin with. Can anyone send me a db and associated files so that I can begin to see how it all works? Thank you very much for any assistance, Steve Braun [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: Last inserted ID]
On Wed, 2004-05-05 at 22:49, Paul DuBois wrote: At 22:39 -0400 5/5/04, Nathan Jones wrote: Hi there, I seem to be having a problem retrieving the last inserted ID for a table. The query I am using is as follows: It's far easier than anyone else has mentioned, as of my writing. Just use the PHP function 'mysql_insert_id()' after your insert query. This function has been in the PHP MySQL module since at least sometime during the version 3 cycle. Here's the address to the online PHP documentation for the MySQL module: http://www.php.net/manual/en/ref.mysql.php This is true, but it's possible that the underlying cause of the problem that results in the query not working on PHP may also affect whether or not mysql_insert_id() returns the desired value. For example, if the SELECT LAST_INSERT_ID() query is not being issued on the same connection as the query that generates the ID, calling mysql_insert_id() might not work, either. mysql_insert_id() takes as an optional parameter, like all MySQL functions in PHP, referencing the connection to check. To garauntee that you are using the correct connection, always pass the resource for the connection to the mysql_* functions. mysql_insert_id() must be called as the next query on the connection of the INSERT query, otherwise you get back 0. By using this function, you simply reduce the chances for error in the id retrieval. I know that by default many of us don't RTFM, but in the case of any language, I've found that reading the manuals to be very helpful. In fact, I keep copies of all the manuals locally in order to save the time required to retrieve from the web. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tables locking order
Hi, Can someone clarify the following. When I use statement LOCK TABLES t1 WRITE, t2 READ, ...; Am I garanteed that locking will be performed in the order t1, t2, etc.? If not, use of this on multiple threads will produce locks. Manual says to use this statement, but it does not say if the order of locking is preserved. Thank you in advance for the help. Mikalai. __ Post your free ad now! http://personals.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Directory Permissions on files
Robert Reed wrote: Greetings, I've recently inherited a FreeBSD server running MySQL 3.23.54. It's good and stable. I have a second server that runs as a slave to the first. Everything goes smoothly until I make changes to a certain table on my master. This will kill the slave with the error that this table is read-only. These are all MyISAM tables. I noticed recently that the various directories have different permissions and access levels on them and wondered what the correct levels should be. And...does this even have an effect on whether the table can be written to? The user that mysqld runs as must be able to read from and write to the table files. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with very special characters
Hello everybody, I'm back! (been a subscriber here before, a while ago .. was quite an active one at that time .. ;)) .. and I'm back for a reason, having a very wicked problem. The setup is one RedHat linux 9 machine (called s007), previously in use as both our web and database server. As traffic increased dramatically earlier this week, we had to grab another server, running RedHat Fedora. We'll call her s006, and use to serve the webpages. All happened very quickly and smooth for most of it. Only currently still existing problem has (appearantly) to do with transferring the very special characters over the lines between s007 and s006, displaying them on the screen and entering them in the database through webforms. Normal special chars like é, ê, à and so on seem to be displayed ok (after calling htmlentities in php: PHP 4.3.3 (cgi) (built: Oct 21 2003 09:51:55) on s006 and PHP 4.3.4 (cli) (built: Jan 24 2004 22:34:14) on s007), but the more exotic ones (~ and ^ signs on and under Z, S .. and stuff like that) still cause problems on the s006. Same script, requesting data from the same rows of the same database on the s007 works as it is supposed to. MySQL version of both servers is mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386), for both machines, /etc/sysconfig/i18n looks like: 1 LANG=en_US 2 SUPPORTED=nl_NL:nl_NL:nl:en_US:en 3 SYSFONT=lat0-sun16 4 SYSFONTACM=iso15 (numbers are line numbers). To compare the pages: http://esctoday.s007.interlize.net/annual/2004/participants.php http://esctoday.s006.interlize.net/annual/2004/participants.php
Problems with very special characters
Hello everybody, I'm back! (been a subscriber here before, a while ago .. was quite an active one at that time .. ;)) .. and I'm back for a reason, having a very wicked problem. The setup is one RedHat linux 9 machine (called s007), previously in use as both our web and database server. As traffic increased dramatically earlier this week, we had to grab another server, running RedHat Fedora. We'll call her s006, and use to serve the webpages. All happened very quickly and smooth for most of it. Only currently still existing problem has (appearantly) to do with transferring the very special characters over the lines between s007 and s006, displaying them on the screen and entering them in the database through webforms. Normal special chars like é, ê, à and so on seem to be displayed ok (after calling htmlentities in php: PHP 4.3.3 (cgi) (built: Oct 21 2003 09:51:55) on s006 and PHP 4.3.4 (cli) (built: Jan 24 2004 22:34:14) on s007), but the more exotic ones (~ and ^ signs on and under Z, S .. and stuff like that) still cause problems on the s006. Same script, requesting data from the same rows of the same database on the s007 works as it is supposed to. MySQL version of both servers is mysql Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386), for both machines, /etc/sysconfig/i18n looks like: 1 LANG=en_US 2 SUPPORTED=nl_NL:nl_NL:nl:en_US:en 3 SYSFONT=lat0-sun16 4 SYSFONTACM=iso15 (numbers are line numbers). To compare the pages: http://esctoday.s007.interlize.net/annual/2004/participants.php http://esctoday.s006.interlize.net/annual/2004/participants.php http://esctoday.s006.interlize.net/annual/2004/participants.php I'm stuck here with my hands in my hair, and would very much appriciate any clue to a solution, Wouter van Vliet (ps. I tried to post this message some days before too, but failed back then. If it comes in twice, I apologize)
Re: Hash Index
A hash is a key value pair
i.e. if you want a value for a unique key (in perl anyway) you can do
this...
%status = (
1 = true,
0 = false
);
If I have a variable that hold an integer such as
$test = 1 I can do the following.
if($status{$test} eq true) {
do something
}
I have passed the variable $test as a key to the hash %status, and it
has returned a value for that unique key.
It's very similar in mySQL
I come from a C background and now working extensively in Perl - this is
a very powerful feature.
Hope this helps
Andy
On Thu, 2004-05-06 at 23:01, Lou Olsten wrote:
This one is more curiosity than a problem.
I have read the docs about HASH indexes and how they are used, but I'm
just wholly unfamiliar with WHAT a HASH index is. I'm only familiar with
the term 'hash' as it relates to encryption. What exactly IS a hash
index?
Just curious,
Lou
--
perl -e 'print qq^;@) [###]^^qq^z\.MY{eLQ9^'
in:control developer, Telindus, RG27 9HY
DDI: +44 1256 709211, GSM: +44 7810 636652
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
STDDEV already in 4.0.x
Hi there, I have a question regarding the STD()/STDDEV() function. In the manual it says, that STD() is the square root of VARIANCE() and for VARIANCE it says it is first implemented in 4.1. But when I look at the 4.1. changes it only says: Added new VARIANCE(expr) function returns the variance of expr. It doesn't mention STD()/STDDEV() at all. so here is my question: is STD()/STDDEV() already implemented in 4.0.x or only in 4.1? best regards Matthias _ Matthias Eireiner email: [EMAIL PROTECTED] www.bvcapital.com _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STDDEV already in 4.0.x
At 16:02 -0700 5/7/04, Matthias Eireiner wrote: Hi there, I have a question regarding the STD()/STDDEV() function. In the manual it says, that STD() is the square root of VARIANCE() and for VARIANCE it says it is first implemented in 4.1. But when I look at the 4.1. changes it only says: Added new VARIANCE(expr) function returns the variance of expr. It doesn't mention STD()/STDDEV() at all. so here is my question: is STD()/STDDEV() already implemented in 4.0.x or only in 4.1? STD()/STDDEV() have been around for a long time. VARIANCE() was added later. Nevertheless, that doesn't change their mathematical relationship. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL does not automatically start at BOOT time??
Hello List: I have been learning MySQL. I have installed tested MySQL server 3-4 times without problem. This time I used the an alpha-nightly snapshot and have run into problems. My current Installation: -- LINUX RH9 Source Installaion: mysql-5.0.1-alpha-nightly-20040504.tar.gz DUAL Pentium 1GHz CPU 1.5 GB Memory Installation Procedure: -- I follow the installation procedure is as described in MySQL documentation a doze of consulting MySQL by Paul DuBois book: % cd /usr/local % gunzip -dc mysql-5.0.1-alpha-nightly-20040504.tar.gz | tar xvf - % cd mysql-5.0.1-alpha-nightly-20040504 % ./configure -prefix=/usr/local/mysql % make % make install % scripts/mysql_install_db % chown -R mysqladm.mysqlgrp/usr/local/mysql/var % chown -R mysqladm.mysqlgrp/rad/var % chmod -R go-rwx /usr/local/mysql/var * It is a good idea to first see if the MySQL can be started: % /usr/local/mysql/bin/mysqld_safe -user=mysqladm % ps -aux | grep mysql There should be 4-5 lines showing how MySQL is running. Wonderful, MySQL is running. % reboot Setup to start MySQL automatically % cp /usr/local/mysql Version No/mysql.server /etc/init.d % cd /etc/init.d % chmod 500 mysql.server % chkconfig - -add mysql.server % reboot Check if MySQL is running: % ps -aux | grep mysql Problem: - Well, MySQL does not start automatically. -- As I said, I have used a similar installation script before without proble. This time, the only modifications made to the instalation script are: (1) Few hints from Paul DuBois book (2) Different version of MySQL. Can somebody point me to the problem? Is in the script I am using is missing something? Kirti TIB P. O. Box 49 Mountain City, TN 37683 Tel: (423) 727-30001 Fax: (423) 727-3002 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL does not automatically start at BOOT time??
At 14:08 -0400 5/8/04, Kirti S. Bajwa wrote: Hello List: I have been learning MySQL. I have installed tested MySQL server 3-4 times without problem. This time I used the an alpha-nightly snapshot and have run into problems. My current Installation: -- LINUX RH9 Source Installaion: mysql-5.0.1-alpha-nightly-20040504.tar.gz DUAL Pentium 1GHz CPU 1.5 GB Memory Installation Procedure: -- I follow the installation procedure is as described in MySQL documentation a doze of consulting MySQL by Paul DuBois book: % cd /usr/local % gunzip -dc mysql-5.0.1-alpha-nightly-20040504.tar.gz | tar xvf - % cd mysql-5.0.1-alpha-nightly-20040504 % ./configure -prefix=/usr/local/mysql % make % make install % scripts/mysql_install_db % chown -R mysqladm.mysqlgrp/usr/local/mysql/var % chown -R mysqladm.mysqlgrp/rad/var % chmod -R go-rwx /usr/local/mysql/var * It is a good idea to first see if the MySQL can be started: % /usr/local/mysql/bin/mysqld_safe -user=mysqladm % ps -aux | grep mysql There should be 4-5 lines showing how MySQL is running. Wonderful, MySQL is running. % reboot Setup to start MySQL automatically % cp /usr/local/mysql Version No/mysql.server /etc/init.d % cd /etc/init.d % chmod 500 mysql.server % chkconfig - -add mysql.server Try adding: chkconfig mysql.server on Then run this to see if it's been enabled for the appropriate runlevels: chkconfig --list mysql.server % reboot Check if MySQL is running: % ps -aux | grep mysql Problem: - Well, MySQL does not start automatically. -- As I said, I have used a similar installation script before without proble. This time, the only modifications made to the instalation script are: (1) Few hints from Paul DuBois book (2) Different version of MySQL. Can somebody point me to the problem? Is in the script I am using is missing something? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STDDEV already in 4.0.x
Paul DuBois wrote: At 16:02 -0700 5/7/04, Matthias Eireiner wrote: Hi there, I have a question regarding the STD()/STDDEV() function. In the manual it says, that STD() is the square root of VARIANCE() and for VARIANCE it says it is first implemented in 4.1. But when I look at the 4.1. changes it only says: Added new VARIANCE(expr) function returns the variance of expr. It doesn't mention STD()/STDDEV() at all. so here is my question: is STD()/STDDEV() already implemented in 4.0.x or only in 4.1? STD()/STDDEV() have been around for a long time. VARIANCE() was added later. Nevertheless, that doesn't change their mathematical relationship. :-) Yes, but I'd have to agree that the manual is possibly misleading on this. It doesn't say that STD() and STDDEV() return the square root of the (mathematical/statistical) variance. It says, Returns the standard deviation of expr (the square root of VARIANCE()). That is, it explicitly references the VARIANCE() function, which in turn is first implemented in 4.1. I think STD() and STDDEV() first implemented in 4.1 is a *reasonable* (though incorrect) reading of the manual, as you can't return the square root of an undefined function. The manual also fails to mention whether STD() and STDDEV return the population or sample standard deviation. How about something like: STD(expr) STDDEV(expr) Returns the standard deviation (the square root of the variance) of expr (considering rows as the whole population, not as a sample; so it has the number of rows as denominator). The STDDEV() form of this function is provided for Oracle compatibility. If you want to reference the new VARIANCE() function in the description of STD(), you could add something like, Starting with MySQL 4.1, you can also calculate the variance directly with the VARIANCE() function at the end. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Object oriented databases
I've heard somwhere that developpers of mySQL 5 are working on implementing an object oriented database of mySQL is that true? in this case can i have help on how to use the object side of the database? thanks _ MSN Search, le moteur de recherche qui pense comme vous ! http://search.msn.fr/worldwide.asp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stupid Newbie Query Help with AND and OR logical operators
hi, you should set parentheses on it Select * FROM name WHERE last LIKE d AND (choice1=2 OR choice2=2 OR choice3=2); without it interprets it as like d and choice1=2 or choice2=2 or choice3=2 Regards Ingo Thierack --On Freitag, 7. Mai 2004 13:51 -0400 Bob Cohen [EMAIL PROTECTED] wrote: Here's my question. I wrote this query: Select * FROM name WHERE last LIKE d AND choice1=2 OR choice2=2 OR choice3=2; What I'm looking for are records that satisfy the LIKE d condition But then, Only one of the three other conditions need be true: choice1=2 choice2=2 choice3=2 I want to cover the possibilities, e.g., choice1=2 choice2=1 choice3=1 choice1=1 choice2=2 choice3=whatever Or choice1=1 choice2=1 choice3=2 Does this make sense? The query I've written doesn't seem quite right. Because of the AND following the LIKE d condition, it seems like all the records will have to have choice2 equaling 2. Some help clarifying this issue would be appreciated. Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Speed Problem
hello. i have a speed problem ;). table: title_id bigint(50),title char(200), cdate datetime query: select title, max(cdate) as mdt, count(title_id) as num from entry where (date_format(cdate, '%Y-%m-%d %H:%i:%s') between '2004-05-07 00:00:01' AND '2004-05-08 23:59:59') and (on = 'Y') group by title order by mdt desc limit 0,25 (have 40+ records in table, server result: 3.0547 second) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL query problem in conjunction with PHP
Thomas Nyman wrote: I found an instruction on the net concerning Date Arithmetic and which seems to be exactly what I need to hel me get going..however there seems to be either a mistake on my behalf or on the writers behalf. Can anyone say whats wrong with this query $query2=UPDATE KK_Fatalie SET datum = DATE_ADD(date,INTERVAL intervall' ''.$typeArray[$row-type].)WHERE kk_ID=.$row-kk_ID; The query is suppose to result in the following; do an UPDATE on table KK_Fatalie and set the column datum equal to todays date increased with the interval stated in the column intervall where the kolumn kk_ID equals the kk_ID in my earlier php query. You appear to have some syntax errors in your PHP (too many quote marks, and they're mismatched in some places). You also don't tell us the exact MySQL error message, and it's not obvious based on your $query2 line what all problems are without more knowledge about your table structure. Try printing out your $query2 string to the web page where you're running this query. Then enter the query using the interactive mysql program and seeing what error message MySQL produces. You could also edit your mysql_query() call to do something like: $rid2 = mysql_query($query2); if(! $rid2) echo Error in query! Error: .mysql_errno().: .htmlspecialchars(mysql_error()).br\n; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL does not automatically start at BOOT time??
Paul: Did what you said. Here is the output: % chkconfig --list mysql.server mysql.server 0:off 1:off 2:on 3:on 4:on 5:on 6:off What next? Kirti -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, May 08, 2004 2:24 PM To: Kirti S. Bajwa; [EMAIL PROTECTED] Subject: Re: MySQL does not automatically start at BOOT time?? At 14:08 -0400 5/8/04, Kirti S. Bajwa wrote: Hello List: I have been learning MySQL. I have installed tested MySQL server 3-4 times without problem. This time I used the an alpha-nightly snapshot and have run into problems. My current Installation: -- LINUX RH9 Source Installaion: mysql-5.0.1-alpha-nightly-20040504.tar.gz DUAL Pentium 1GHz CPU 1.5 GB Memory Installation Procedure: -- I follow the installation procedure is as described in MySQL documentation a doze of consulting MySQL by Paul DuBois book: % cd /usr/local % gunzip -dc mysql-5.0.1-alpha-nightly-20040504.tar.gz | tar xvf - % cd mysql-5.0.1-alpha-nightly-20040504 % ./configure -prefix=/usr/local/mysql % make % make install % scripts/mysql_install_db % chown -R mysqladm.mysqlgrp/usr/local/mysql/var % chown -R mysqladm.mysqlgrp/rad/var % chmod -R go-rwx /usr/local/mysql/var * It is a good idea to first see if the MySQL can be started: % /usr/local/mysql/bin/mysqld_safe -user=mysqladm % ps -aux | grep mysql There should be 4-5 lines showing how MySQL is running. Wonderful, MySQL is running. % reboot Setup to start MySQL automatically % cp /usr/local/mysql Version No/mysql.server /etc/init.d % cd /etc/init.d % chmod 500 mysql.server % chkconfig - -add mysql.server Try adding: chkconfig mysql.server on Then run this to see if it's been enabled for the appropriate runlevels: chkconfig --list mysql.server % reboot Check if MySQL is running: % ps -aux | grep mysql Problem: - Well, MySQL does not start automatically. --- - -- As I said, I have used a similar installation script before without proble. This time, the only modifications made to the instalation script are: (1) Few hints from Paul DuBois book (2) Different version of MySQL. Can somebody point me to the problem? Is in the script I am using is missing something? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL does not automatically start at BOOT time??
Paul: I did not realize that I sent the response directly to you instead of the list. In any case, it did not work now I re-start with a clean RH9 install. I thought why to wait until Monday. I need to get it done. Thanks again. You gave me lots of new instructions to read about. Kirt -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, May 08, 2004 6:44 PM To: Kirti S. Bajwa Subject: RE: MySQL does not automatically start at BOOT time?? Paul: Did what you said. Here is the response: % chkconfig --list mysql.server mysql.server 0:off 1:off 2:on 3:on 4:on 5:on 6:off That looks correct. If you did chkconfig mysql.server on, *and then restarted*, and the server still doesn't start automatically, check the MySQL error log to see if it has a clue. If it doesn't, I dunno. What next? Kirti -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, May 08, 2004 2:24 PM To: Kirti S. Bajwa; [EMAIL PROTECTED] Subject: Re: MySQL does not automatically start at BOOT time?? At 14:08 -0400 5/8/04, Kirti S. Bajwa wrote: Hello List: I have been learning MySQL. I have installed tested MySQL server 3-4 times without problem. This time I used the an alpha-nightly snapshot and have run into problems. My current Installation: -- LINUX RH9 Source Installaion: mysql-5.0.1-alpha-nightly-20040504.tar.gz DUAL Pentium 1GHz CPU 1.5 GB Memory Installation Procedure: -- I follow the installation procedure is as described in MySQL documentation a doze of consulting MySQL by Paul DuBois book: % cd /usr/local % gunzip -dc mysql-5.0.1-alpha-nightly-20040504.tar.gz | tar xvf - % cd mysql-5.0.1-alpha-nightly-20040504 % ./configure -prefix=/usr/local/mysql % make % make install % scripts/mysql_install_db % chown -R mysqladm.mysqlgrp/usr/local/mysql/var % chown -R mysqladm.mysqlgrp/rad/var % chmod -R go-rwx /usr/local/mysql/var * It is a good idea to first see if the MySQL can be started: % /usr/local/mysql/bin/mysqld_safe -user=mysqladm % ps -aux | grep mysql There should be 4-5 lines showing how MySQL is running. Wonderful, MySQL is running. % reboot Setup to start MySQL automatically % cp /usr/local/mysql Version No/mysql.server /etc/init.d % cd /etc/init.d % chmod 500 mysql.server % chkconfig - -add mysql.server Try adding: chkconfig mysql.server on Then run this to see if it's been enabled for the appropriate runlevels: chkconfig --list mysql.server % reboot Check if MySQL is running: % ps -aux | grep mysql Problem: - Well, MySQL does not start automatically. -- - - -- As I said, I have used a similar installation script before without proble. This time, the only modifications made to the instalation script are: (1) Few hints from Paul DuBois book (2) Different version of MySQL. Can somebody point me to the problem? Is in the script I am using is missing something? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed Problem
i have a speed problem ;). table: title_id bigint(50),title char(200), cdate datetime Next time, please provide information about existing indices and provide output of EXPLAIN of the query... query: select title, max(cdate) as mdt, count(title_id) as num from entry where (date_format(cdate, '%Y-%m-%d %H:%i:%s') between '2004-05-07 00:00:01' AND '2004-05-08 23:59:59') Here MySQL will calculate the date format for each row that it has to check! Rewrite this with the column name not used as parameter for a function: WHERE cdate BETWEEN xx AND x This way MySQL can compare the column to constant values, which is much faster! ( WHERE column constant + INTERVAL 1 MONTH proved to be 100 times (!) faster than WHERE column - INTERVAL 1 MONTH constant in one case I came across) and (on = 'Y') What is on? There is no column in the table with that name! group by title If you have a combined index on cdate, on and title (or at least part of title) it will probably be faster too. order by mdt desc limit 0,25 DESC is usually slower than ASC, but in this case there is no alternative IMHO. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another Stupid Newbie Question ^.^
I connect to my data base...
$dbh=mysql_connect (localhost, kipples_women, ) or die ('I cannot connect to
the database because: ' . mysql_error());
mysql_select_db (kipples_hotwomen);
and then I issue a query..
$query=SELECT *
FROM women
WHERE index = $id;
$result=mysql_query($query) or die(Query failed : . mysql_error());
now here's where it gets funky, and where I can't figure out what is going wrong...
I now have it display the information from the row that has the field index and
where index is equal to $id (the number from the url). Now, say that $id was equal to
21... I get an error message that says..
Query failed : You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near 'index = 21'
at line 3
Now, first of all, it isnt on line3, its like line 128 or something... second.. I
can't see anything wrong with that syntax... My SQL server version is 4.0.18-standard
and I can't find documentation for it. I also tried to use LIMIT $id Oo and it gets
even weirder. I can get a result with LIMIT $id but it pulls the wrong row! if $id
equals 24 for example, it will get the row with a value of 7 in the field for
index I don't get it.. Even had a friend look at it and he said it all looked
good..
I'd really appreciate any help anyone could throw at me... I'm rather new to all this!
Thanks,
Kip Gordon
C for MySQL on MacOSX
Hello, Where do you get c headers/libraries for mysql on macosx ? I don't seem to find them by running the mysql installer .pkg. Thanx in advance. Best, hAj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another Stupid Newbie Question ^.^
Kip Gordon wrote:
I connect to my data base...
$dbh=mysql_connect (localhost, kipples_women, ) or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db (kipples_hotwomen);
and then I issue a query..
$query=SELECT *
FROM women
WHERE index = $id;
$result=mysql_query($query) or die(Query failed : . mysql_error());
now here's where it gets funky, and where I can't figure out what is going wrong...
I now have it display the information from the row that has the field
index and where index is equal to $id (the number from the url). Now, say
that $id was equal to 21... I get an error message that says..
Query failed : You have an error in your SQL syntax. Check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'index = 21' at line 3
Now, first of all, it isnt on line3, its like line 128 or something...
The error message is coming from mysql with regard to your 3-line query.
The mysql server has no way of knowing which line of your script sent the query.
The problem is that index is a reserved word. Your best bet would be to use
a different name for the column, id perhaps. If you wish to use a column
named index, you must enclose it in backticks, like this:
$query=SELECT *
FROM women
WHERE `index` = $id;
But I'd recommend
ALTER TABLE women CHANGE `index` id INT;
to rename the index column to id. (Replace INT with the same column type
you used to define index.) Then your query becomes
$query=SELECT *
FROM women
WHERE id = $id;
No more need for backticks, and WHERE id = $id is intuitive.
second.. I can't see anything wrong with that syntax... My SQL server
version is 4.0.18-standard and I can't find documentation for it. I also
See the online manual http://dev.mysql.com/doc/mysql/en/.
tried to use LIMIT $id Oo and it gets even weirder. I can get a result with
LIMIT $id but it pulls the wrong row! if $id equals 24 for example, it
will get the row with a value of 7 in the field for index I don't get
it.. Even had a friend look at it and he said it all looked good..
I'm not sure what you mean by LIMIT $id Oo, but no, it doesn't pull the
wrong row. LIMIT has nothing to do with which rows are selected. That's
the job of the WHERE clause. Nor does it have anything to do with how the
returned rows are sorted. That's the job of the ORDER BY clause. LIMIT is
used to determine how many rows are returned if you don't want them all.
Thus, LIMIT 24 means you want the first 24 rows that match your WHERE
clause. See the manual http://dev.mysql.com/doc/mysql/en/SELECT.html for
the complete SELECT syntax.
I'd really appreciate any help anyone could throw at me... I'm rather new to all this!
Thanks,
Kip Gordon
We were all new once. Hope this helps.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL query problem in conjunction with PHP
Thomas Nyman wrote: Hi I found an instruction on the net concerning Date Arithmetic and which seems to be exactly what I need to hel me get going..however there seems to be either a mistake on my behalf or on the writers behalf. Can anyone say whats wrong with this query $query2=UPDATE KK_Fatalie SET datum = DATE_ADD(date,INTERVAL intervall' ''.$typeArray[$row-type].)WHERE kk_ID=.$row-kk_ID; The query is suppose to result in the following; do an UPDATE on table KK_Fatalie and set the column datum equal to todays date increased with the interval stated in the column intervall where the kolumn kk_ID equals the kk_ID in my earlier php query. You say you want to add an interval to today's date, but your query is apparently adding to a column named date. To get today's date, use the CURDATE() function. $query2=UPDATE KK_Fatalie SET datum = CURDATE() + INTERVAL intervall .$typeArray[$row-type] . WHERE kk_ID=.$row-kk_ID; I'm assuming inteval1 holds an integer and $typeArray[$row-type] holds an appropriate INTERVAL type (DAY or YEAR or...) On the other hand, if you really do have a column named date you wish to use, then you need to realize that date is a reserved word, so it has to be enclosed in backticks to use it as a name. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find host.MYD
Steve Mansfield wrote: I know this is a thorny old problem, but I'm having trouble resolving it. Here's the set-up. I'm running Linux. The MySQL data directory (as configured in my.cnf) is /share/mysql/data/ because I was sharing the data between Linux and Windows on a dual-boot system. This set-up has been working fine for months. Some time ago I stopped using and removed Windows. So today I decided that the /share partition should no longer be FAT32. I backup-up everything on it (just simply copied the files to another partition) reformatted as ext3 and copied everything back. Now mysqld won't keep running - ie, it starts up then immediately shuts down again - and I found a message in the mysqld.log file saying: 040508 11:10:24 mysqld started 040508 11:10:24 /usr/sbin/mysqld: Can't find file: 'host.MYD' (errno: 2) 040508 11:10:24 mysqld ended But, the file host.MYD is definitely in the /share/mysql/data/mysql/ directory, although it appears to be 0 bytes (?). I figured this might be a file permissions error, so I've tried changing the owner for all the mysql files to 'mysql' (group is currently 'root') and permissions are 755. This hasn't solved the problem. Any ideas would be most welcome. @+ Steve Steve: Check to see if mysqld is correctly reading your my.cnf, and that no other --datadir option gets passed on the command line before my.cnf is read. The easiest way to do it is to add debugging messages to mysqld_safe script (safe_mysqld in the older versions). -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stupid Newbie Query Help with AND and OR logical operators
Bob Cohen wrote: Here's my question. I wrote this query: Select * FROM name WHERE last LIKE d AND choice1=2 OR choice2=2 OR choice3=2; What I'm looking for are records that satisfy the LIKE d condition But then, Only one of the three other conditions need be true: choice1=2 choice2=2 choice3=2 I want to cover the possibilities, e.g., choice1=2 choice2=1 choice3=1 choice1=1 choice2=2 choice3=whatever Or choice1=1 choice2=1 choice3=2 Does this make sense? The query I've written doesn't seem quite right. Because of the AND following the LIKE d condition, it seems like all the records will have to have choice2 equaling 2. Some help clarifying this issue would be appreciated. Thanks. Bob Cohen b.p.e.Creative http://www.bpecreative.com Design and production services for the web Put creative minds to work for you Bob, You should have used some paranthese to separate the clauses u want for or. You may be getting errors because of the operator precedence. 'And' is having higher precedence than 'or'. Further like should be used with wildcard chars '%' or '_' .I fu re-write ur qry like Select * FROM name WHERE last LIKE %d% AND (choice1=2 OR choice2=2 OR choice3=2); //This gives all recs where last is having char d anywhere in the string. // 'd%' - starting with 'd' // '%d' - ending with 'd' ur next reqrmnet. If u want to cover all possibilities in singlr querry u should use 'IN' operator like (choice1 IN (2,1) or choice2 IN (1,2)). Else u may have to use separate qrys. reg, Eldo Skaria. -- #---# # Viva OpenSource # #---# -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
