mysql.server script does not honor datadir settings

[tlack]

>Description:

Change datadir in /etc/my.cnf or elsewhere. Start server (i.e.,
/usr/local/share/mysql/mysql.server start) and then try to stop it
(/usr/local/share/mysql/mysql.server stop)

>How-To-Repeat:

# joe /etc/my.cnf
Change some settings, especially datadir
# /usr/local/share/mysql/mysql.server start
wait a few seconds..
# /usr/local/share/mysql/mysql.server stop
No mysqld pid file found. Looked for /var/db/mysql/farmer.pid.

>Fix:

Workaround: edit mysql.server script. Fix: have it parse datadir

>Submitter-Id:  
>Originator:tlack
>Organization:
 
>MySQL support: none
>Synopsis:  mysql.server script does not honor settings /etc/my.cnf
>Severity:  non-critical
>Priority:  low
>Category:  mysql
>Class: sw-bug
>Release:   mysql-3.23.49 (FreeBSD port: mysql-server-3.23.49)

>Environment:

System: FreeBSD farmer.terespondo.com 4.5-STABLE FreeBSD 4.5-STABLE #2: Fri Jun  7 
17:58:48 CDT 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/FARMER  i386


Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.3 20010315 (release) [FreeBSD]
Compilation info: CC='cc'  CFLAGS='-O -pipe '  CXX='cc'  CXXFLAGS='-O -pipe  
-felide-constructors -fno-rtti -fno-exceptions'  LDFLAGS=''
LIBC: 
-r--r--r--  1 root  wheel  1208228 Feb 20 11:13 /usr/lib/libc.a
lrwxr-xr-x  1 root  wheel  9 Feb 20 11:13 /usr/lib/libc.so -> libc.so.4
-r--r--r--  1 root  wheel  573888 Feb 20 11:13 /usr/lib/libc.so.4
Configure command: ./configure  --localstatedir=/var/db/mysql --without-perl 
--without-debug --without-readline --without-bench --with-mit-threads=no 
--with-libwrap --with-low-memory '--with-comment=FreeBSD port: mysql-server-3.23.49' 
--enable-assembler --with-berkeley-db --with-innodb --prefix=/usr/local 
i386-portbld-freebsd4.5
Perl: This is perl, version 5.005_03 built for i386-freebsd

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Plagued by Error 127

[Thomas Lackner]

First, thanks for your response.

I don't have the error message in front of me, but it's something like:

# myisamchk -r table
Data rows: 56,402
Attempting to fix records:   0/0 something something
Data rows: 0
#

Next time it occurs I'll save the detailed error message.

MySQL doesn't crash.. if it did, that might be better! Instead it just 
returns 127 errors and I have to take it down, fix table, bring it back up.

I'm going to change the code to do delete * from table where 1=1 instead 
of truncate. I really hope that will solve my problem!

- Tom

Dan Nelson wrote:
> In the last episode (Jun 07), Thomas Lackner said:
> 
>>A client of mine runs MySQL in a pretty high volume environment, and for 
>>the past couple months we've had a lot of trouble with corrupt tables 
>>(error 127). We've changed the MySQL version (presently to version 
>>3.23.49), FreeBSD version (presently to 4.5-STABLE), hardware (from IDE 
>>to SCSI) and of course the amount of data involved. We aren't using 
> 
> ... 
> 
>>I can always use "myisamchk", but usually this ends up deleting every 
>>record in the table. (Luckily for us this is an okay situation based on 
>>our application) Running myisamchk on a scheduled basis is a bit too 
>>much of a hack for me to explain to my client -- and my conscience.
> 
> 
> If myisamchk ends up deleting every record, that sounds really bad. 
> Does it print any diagnostics as it runs through them, or does it run
> silently and you end up with an empty table?  All I can think is maybe
> the truncate table code is acting up.  Maybe try swapping in "delete *
> from table where 1=1", or "drop table / create table"?
> 
> Does mysql itself crash (and the table is corrupt on restart), or just
> out of the blue start returning 127's?
> 


-- 
[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: inserting values w/ relationships

[Dan Nelson]

In the last episode (Jun 07), Jule Slootbeek said:
> Hey gang,
>   I have two tables:
> 
> user
> ++--+--+-+-+---+
> | Field  | Type | Null | Key | Default | Extra |
> ++--+--+-+-+---+
> | user_id| int(10) unsigned |  | PRI | NULL| auto_increment|
> | first_name | varchar(10)  |  | | |   |
> | last_name  | varchar(20)  |  | | |   |
> | email  | varchar(100) |  | | |   |
> | username   | varchar(16)  |  | | |   |
> | password   | varchar(32)  |  | | |   |
> ++--+--+-+-+---+
> 
> and
> 
> quiz
> +-+--+--+-+-++
> | Field   | Type | Null | Key | Default | Extra  |
> +-+--+--+-+-++
> | quiz_id | int(10) unsigned |  | PRI | NULL| auto_increment |
> | user_id | int(10) unsigned |  | | 0   ||
> | title   | varchar(255) |  | | ||
> +-+--+--+-+-++
> 
> now user_id and is a relationship between user and quiz...
> How do i make sure that when i insert a new row into quiz that user_id
> gets taken from quiz, without me having to look it up in user and
> entering it manually?
> any ideas?

INSERT INTO users VALUES (NULL,'myfname','mylname','myemail','username','pass');
INSERT INTO quiz VALUES (NULL,LAST_INSERT_ID(), 'title goes here');

-- 
Dan Nelson
[EMAIL PROTECTED]

$#$@! list filter. sql, query. there. happy?

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: Plagued by Error 127

[Dan Nelson]

In the last episode (Jun 07), Thomas Lackner said:
> A client of mine runs MySQL in a pretty high volume environment, and for 
> the past couple months we've had a lot of trouble with corrupt tables 
> (error 127). We've changed the MySQL version (presently to version 
> 3.23.49), FreeBSD version (presently to 4.5-STABLE), hardware (from IDE 
> to SCSI) and of course the amount of data involved. We aren't using 
... 
> I can always use "myisamchk", but usually this ends up deleting every 
> record in the table. (Luckily for us this is an okay situation based on 
> our application) Running myisamchk on a scheduled basis is a bit too 
> much of a hack for me to explain to my client -- and my conscience.

If myisamchk ends up deleting every record, that sounds really bad. 
Does it print any diagnostics as it runs through them, or does it run
silently and you end up with an empty table?  All I can think is maybe
the truncate table code is acting up.  Maybe try swapping in "delete *
from table where 1=1", or "drop table / create table"?

Does mysql itself crash (and the table is corrupt on restart), or just
out of the blue start returning 127's?

-- 
Dan Nelson
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: newbie ? Load Data in MySQL

[bvyas3]

No, there is no way to trigger an insert automatically when you are doing a
LOAD DATA (bulk load). You will need another flat file with the appropriate
info and load that in as well...just as you have thought of.
- Original Message -
From: "Dion Wickander" <[EMAIL PROTECTED]>
To: "MySQL Help" <[EMAIL PROTECTED]>
Sent: Friday, June 07, 2002 8:12 AM
Subject: newbie ? Load Data in MySQL


> I am working on a project were the customer info will be uploaded to a
MySQL
> database from multiple sources with a customer ID already assigned to them
> and this data needs to be related to other tables with related info. OK I
> have an auto increment column set up as the primary key to keep track of
the
> customer records that are being created and using it to relate the data to
> my other tables.
>
> here is the question
>
> is their a built in or automated way to load data in bulk to the customer
> table and with each record added to that table create a record in the
other
> related tables with the correct primary key? or would this need to be a
> record by record individual insert to create the corresponding records.
>
> - my thinking is that I may have to load data in bulk, setting a flag for
> the newly uploaded data. Then do a select that finds the flags and then
> creating the new records in the related tables based on the found set.
then
> of course removing the flags after all other tables are updated.
>
> I was just wondering if their is a built in mechanism for something like
> this?
>
> thanks in advance.
>
>
> MySQL 3.23.39
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> 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: Not using indexes???

[Jon Frisby]

> * Mysql, after reading the query, decides wether using an index would be
> better than just a table row scan. Hence,   it's MySql's decision

MySQL is making the wrong decision.  As stated below, it's doing a table
scan when it only needs to look at some 2,800 rows out of 970,000 rows.  In
addition, the "real" query I've been trying to make work (a join) gets
nonsense output from EXPLAIN -- some 50k rows from the left table are
involved in the join, yet MySQL estimates that 1 row will be!  (The query
produces correct results, it just takes a very very long time to do so .)


> * You can force MySql to use indexes using the 'using index'
> option with the
> select query.(MySql 'might' still reject the force, not sure of the
> circumstances).

I have never managed to get MySQL to obey the "using index" option.  It
produces no effect here.


> * The first 2 queries pretty much won't use index because it is not a
> specific value that you are asking MySql to search for but just a
> NOT NULL,
> very different for '=' or even ranges.

Using sequence_log_id > 0 produces the same result.  Using any particular
value in place of 0 produces the same effect.  Using sequence_log_id > foo
AND sequence_log_id < bar does seem to use the index...

explain SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE
sequence_log_id > 10 and sequence_log_id < 40;
+---+---+-+-+-+--+--
++
| table | type  | possible_keys   | key | key_len | ref  | rows
| Extra  |
+---+---+-+-+-+--+--
++
| click | range | sequence_log_id | sequence_log_id |   5 | NULL |   10
| where used |
+---+---+-+-+-+--+--
++


> * The final query is a Group By and hence it will need to use the index to
> group by the Values that it is going to show you.

However it seems to be doing a table scan across the actual *data*.  It does
not seem to be using the index to eliminate rows.


> That should use indexes since you are searching for something
> specific. You
> can also try ranges and it should still use indexes ( a rule of thumb is
> that if the return result is more than 30% of the total number of records,
> it's faster to do just a full table scan so it still might not
> use it if you
> specify a very big range)

The return result is about 0.3% -- that is a far cry from 30%.  Using ranges
may work (I'll just use 0 and maxint), but this is definitely not desirable
behavior from the MySQL optimizer if I have to resort to such a trick.

Using *a* specific value is somewhat inapplicable here because my ultimate
goal is to use this table in a *join*.  It's not a good thing when the left
table *must* do a table scan across 50k rows, and MySQL acts brain dead when
scanning the right table and looks at all 970k rows even though it should be
getting 1..10 rows out of click for each row from the left table...

-JF


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Error in shared libraries

[bvyas3]

Yes, regarding the $PATH my bad... putting it in /usr/lib should have
resolved it(since it's generally in LD_LIBRARY_PATH). Besides that, as Ritu
suggested, it should be in LD_LIBRARY_PATH variable in the environment & not
$PATH. Not familiar with /etc/ld.conf, that might work too.
Bhavin.
- Original Message -
From: "Chris Knipe" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "Charitha" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Thursday, June 06, 2002 9:23 PM
Subject: Re: Error in shared libraries


> Doesn't this need to be in ld's path?  /etc/ld.conf ?
>
> I'm not to sure Not on *nix at the moment, but I believe the proper
way
> to add it would be through /etc/ld.conf and running ldconfig to update the
> paths.
>
> Kind Regards,
>
> Chris Knipe
> MegaLAN Corporate Networking Services
> Tel: +27 21 854 7064
> Cell: +27 72 434 7582
>
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: "Charitha" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Friday, June 07, 2002 9:14 AM
> Subject: Re: Error in shared libraries
>
>
> > or aalternatively, u can also do a echo $PATH and put the library in one
> of
> > those path. eg: /usr/lib or someplave OR
> > do PATH=$PATH:/usr/lib/mysql, if it work, put that command in your
> > '.profile' so that it loads every time you log in.
> > - Original Message -
> > From: "Chris Knipe" <[EMAIL PROTECTED]>
> > To: "Charitha" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > Sent: Thursday, June 06, 2002 9:00 PM
> > Subject: Re: Error in shared libraries
> >
> >
> > > It may be looking for the library in a different location, such as
> > > /usr/local/lib/mysql
> > >
> > > ldd 
> > >
> > > Should show you where exactly it is looking for the library, then you
> can
> > > try to symlink the library to that location.  Alternatively,
recompiling
> > the
> > > application may also help.
> > >
> > > Kind Regards,
> > >
> > > Chris Knipe
> > > MegaLAN Corporate Networking Services
> > > Tel: +27 21 854 7064
> > > Cell: +27 72 434 7582
> > >
> > > - Original Message -
> > > From: "Charitha" <[EMAIL PROTECTED]>
> > > To: <[EMAIL PROTECTED]>
> > > Sent: Friday, June 07, 2002 5:46 AM
> > > Subject: Error in shared libraries
> > >
> > >
> > > >
> > > >
> > > > Hello all,
> > > >
> > > > I am having one application in which i am using mysql as database.
> > > > When i run my application it gives following error.
> > > >
> > > > "error:loading shared libraries libmysqlclient.so.10 (No such file
or
> > > > directory)"
> > > >
> > > > I am having that library in /usr/lib/mysql/
> > > > Still it gives this error.Why is it so..?
> > > > How to solve this problem?
> > > >
> > > >
> > > > Thanks and regards,
> > > > Charitha C.
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > >
> > > >
> > > >
> > >
> -
> > > > Before posting, please check:
> > > >http://www.mysql.com/manual.php   (the manual)
> > > >http://lists.mysql.com/   (the list archive)
> > > >
> > > > 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
> >
> >
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: Not using indexes???

[bvyas3]

* Mysql, after reading the query, decides wether using an index would be
better than just a table row scan. Hence,   it's MySql's decision
* You can force MySql to use indexes using the 'using index' option with the
select query.(MySql 'might' still reject the force, not sure of the
circumstances).
* The first 2 queries pretty much won't use index because it is not a
specific value that you are asking MySql to search for but just a NOT NULL,
very different for '=' or even ranges.
* The final query is a Group By and hence it will need to use the index to
group by the Values that it is going to show you.
* The third query is a bit confusing. I don't think it should be using
indexes, just as the second one but it is due to some reason, can't answer
that one but instead I would also suggest that you try this:
explain Select sum(actual_revenue) From Click where sequence_log_id =
"specifysomevalue";
That should use indexes since you are searching for something specific. You
can also try ranges and it should still use indexes ( a rule of thumb is
that if the return result is more than 30% of the total number of records,
it's faster to do just a full table scan so it still might not use it if you
specify a very big range)


Regards,
Bhavin Vyas.


- Original Message -
From: "Jon Frisby" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 07, 2002 6:07 PM
Subject: Not using indexes???


> Please excuse me if this is something blindingly obvious, but having now
> encountered this in several circumstances, but I have been unable to find
a
> resolution in the docs (perhaps I just missed it?).
>
> In actuality we encountered the problem when doing some nasty joins, but
the
> problem seems really to be more fundamental as we've reproduced the
problem
> without a join...
>
> We're using MySQL 4.0.1 on Linux (from the RPM).  We have an InnoDB table,
> 'click' with 6 or so indexes on it.  Notably:
> -`id` is the PRIMARY KEY.
> -`sequence_log_id` has a non-unique index.
>
> The problem is when we do queries that involve columns *not* available
> directly from the index, MySQL absolutely refuses to use an index for
> looking up rows.  See the output from EXPLAIN for each of these queries:
>
> SELECT COUNT(*) FROM click WHERE sequence_log_id IS NOT NULL;
>
+---+---+-+-+-+--+--
> --+-+
> | table | type  | possible_keys   | key | key_len | ref  |
rows
> | Extra   |
>
+---+---+-+-+-+--+--
> --+-+
> | click | index | sequence_log_id | sequence_log_id |   5 | NULL |
> 939470 | where used; Using index |
>
+---+---+-+-+-+--+--
> --+-+
>
>
> SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS
NOT
> NULL;
>
+---+--+-+--+-+--++-
> ---+
> | table | type | possible_keys   | key  | key_len | ref  | rows   | Extra
> |
>
+---+--+-+--+-+--++-
> ---+
> | click | ALL  | sequence_log_id | NULL |NULL | NULL | 939470 | where
> used |
>
+---+--+-+--+-+--++-
> ---+
> (`actual_revenue` isn't part of the index.
>
>
> SELECT COUNT(*), SUM(id) FROM click WHERE sequence_log_id IS NOT NULL;
>
+---+---+-+-+-+--+--
> --+-+
> | table | type  | possible_keys   | key | key_len | ref  |
rows
> | Extra   |
>
+---+---+-+-+-+--+--
> --+-+
> | click | index | sequence_log_id | sequence_log_id |   5 | NULL |
> 939470 | where used; Using index |
>
+---+---+-+-+-+--+--
> --+-+
> (Since it's an InnoDB table, a secondary index uses the PK as a reference
> for the index.)
>
>
> SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS
NOT
> NULL GROUP BY sequence_log_id;
>
+---+---+-+-+-+--+--
> --++
> | table | type  | possible_keys   | key | key_len | ref  |
rows
> | Extra  |
>
+---+---+-+-+-+--+--
> --++
> | click | index | sequence_log_id | sequence_log_id |   5 | NULL |
> 939469 | where used |
>
+---+---+-+-+-+--+--
> --++
> (The key isn't actually *used* here, as demonstrated by the query taking a
> long time to run...  Of the ~970k rows in click, about 2800 have
> sequence_log_id IS NOT NULL.)
>
>
>
> My question is:  Is this a bug?  A feature?  What can I do to get MySQL to
> use the s

Re: Not using indexes???

[Erv Young]

Jon,

Have you tried phrasing your WHERE clause as
 WHERE sequence_log_id >= 0 ?
If there is a determinate lower bound other than zero, then substitute it 
for 0 as the second comparand.

Let us know how it comes out.

(And however it comes out, don't ask me why.  The real experts can sort 
that out for both of us when they get to their desks, in about 4 hours' time.)

--Erv

At 06:07 PM 6/7/2002 -0700, Jon Frisby wrote:
>Please excuse me if this is something blindingly obvious, but having now
>encountered this in several circumstances, but I have been unable to find a
>resolution in the docs (perhaps I just missed it?).
>
>In actuality we encountered the problem when doing some nasty joins, but the
>problem seems really to be more fundamental as we've reproduced the problem
>without a join...
>
>We're using MySQL 4.0.1 on Linux (from the RPM).  We have an InnoDB table,
>'click' with 6 or so indexes on it.  Notably:
>-`id` is the PRIMARY KEY.
>-`sequence_log_id` has a non-unique index.
>
>The problem is when we do queries that involve columns *not* available
>directly from the index, MySQL absolutely refuses to use an index for
>looking up rows.  See the output from EXPLAIN for each of these queries:
>
>SELECT COUNT(*) FROM click WHERE sequence_log_id IS NOT NULL;
>+---+---+-+-+-+--+--
>--+-+
>| table | type  | possible_keys   | key | key_len | ref  | rows
>| Extra   |
>+---+---+-+-+-+--+--
>--+-+
>| click | index | sequence_log_id | sequence_log_id |   5 | NULL |
>939470 | where used; Using index |
>+---+---+-+-+-+--+--
>--+-+
>
>
>SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT
>NULL;
>+---+--+-+--+-+--++-
>---+
>| table | type | possible_keys   | key  | key_len | ref  | rows   | Extra
>|
>+---+--+-+--+-+--++-
>---+
>| click | ALL  | sequence_log_id | NULL |NULL | NULL | 939470 | where
>used |
>+---+--+-+--+-+--++-
>---+
>(`actual_revenue` isn't part of the index.
>
>
>SELECT COUNT(*), SUM(id) FROM click WHERE sequence_log_id IS NOT NULL;
>+---+---+-+-+-+--+--
>--+-+
>| table | type  | possible_keys   | key | key_len | ref  | rows
>| Extra   |
>+---+---+-+-+-+--+--
>--+-+
>| click | index | sequence_log_id | sequence_log_id |   5 | NULL |
>939470 | where used; Using index |
>+---+---+-+-+-+--+--
>--+-+
>(Since it's an InnoDB table, a secondary index uses the PK as a reference
>for the index.)
>
>
>SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT
>NULL GROUP BY sequence_log_id;
>+---+---+-+-+-+--+--
>--++
>| table | type  | possible_keys   | key | key_len | ref  | rows
>| Extra  |
>+---+---+-+-+-+--+--
>--++
>| click | index | sequence_log_id | sequence_log_id |   5 | NULL |
>939469 | where used |
>+---+---+-+-+-+--+--
>--++
>(The key isn't actually *used* here, as demonstrated by the query taking a
>long time to run...  Of the ~970k rows in click, about 2800 have
>sequence_log_id IS NOT NULL.)
>
>
>
>My question is:  Is this a bug?  A feature?  What can I do to get MySQL to
>use the sequence_log_id index?
>
>-JF
>
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
>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

Not using indexes???

[Jon Frisby]

Please excuse me if this is something blindingly obvious, but having now
encountered this in several circumstances, but I have been unable to find a
resolution in the docs (perhaps I just missed it?).

In actuality we encountered the problem when doing some nasty joins, but the
problem seems really to be more fundamental as we've reproduced the problem
without a join...

We're using MySQL 4.0.1 on Linux (from the RPM).  We have an InnoDB table,
'click' with 6 or so indexes on it.  Notably:
-`id` is the PRIMARY KEY.
-`sequence_log_id` has a non-unique index.

The problem is when we do queries that involve columns *not* available
directly from the index, MySQL absolutely refuses to use an index for
looking up rows.  See the output from EXPLAIN for each of these queries:

SELECT COUNT(*) FROM click WHERE sequence_log_id IS NOT NULL;
+---+---+-+-+-+--+--
--+-+
| table | type  | possible_keys   | key | key_len | ref  | rows
| Extra   |
+---+---+-+-+-+--+--
--+-+
| click | index | sequence_log_id | sequence_log_id |   5 | NULL |
939470 | where used; Using index |
+---+---+-+-+-+--+--
--+-+


SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT
NULL;
+---+--+-+--+-+--++-
---+
| table | type | possible_keys   | key  | key_len | ref  | rows   | Extra
|
+---+--+-+--+-+--++-
---+
| click | ALL  | sequence_log_id | NULL |NULL | NULL | 939470 | where
used |
+---+--+-+--+-+--++-
---+
(`actual_revenue` isn't part of the index.


SELECT COUNT(*), SUM(id) FROM click WHERE sequence_log_id IS NOT NULL;
+---+---+-+-+-+--+--
--+-+
| table | type  | possible_keys   | key | key_len | ref  | rows
| Extra   |
+---+---+-+-+-+--+--
--+-+
| click | index | sequence_log_id | sequence_log_id |   5 | NULL |
939470 | where used; Using index |
+---+---+-+-+-+--+--
--+-+
(Since it's an InnoDB table, a secondary index uses the PK as a reference
for the index.)


SELECT COUNT(*), SUM(actual_revenue) FROM click WHERE sequence_log_id IS NOT
NULL GROUP BY sequence_log_id;
+---+---+-+-+-+--+--
--++
| table | type  | possible_keys   | key | key_len | ref  | rows
| Extra  |
+---+---+-+-+-+--+--
--++
| click | index | sequence_log_id | sequence_log_id |   5 | NULL |
939469 | where used |
+---+---+-+-+-+--+--
--++
(The key isn't actually *used* here, as demonstrated by the query taking a
long time to run...  Of the ~970k rows in click, about 2800 have
sequence_log_id IS NOT NULL.)



My question is:  Is this a bug?  A feature?  What can I do to get MySQL to
use the sequence_log_id index?

-JF


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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 Replication

[Michael Brunson]

On Fri, 7 Jun 2002 17:40:01 -0600 , "Orr, Steve"
<[EMAIL PROTECTED]> wrote:

| What's the best way to lock down a slave to ensure that only select SQL
| queries are executed and that all command line SQL inserts, updates, and
| deletes are performed only on the master?
| 
| TIA

Only put in select privileges in the permission tables.



Thanks,
Michael
--
Michael Brunson  504.473.6643
[EMAIL PROTECTED] ICQ: 83163789
  ---   Intercosmos Media Group, Inc.  ---
  www.intercosmos.comwww.directnic.com 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Maximum JOINED tables

[Roger Baklund]

* Benjamin Pflugmann
> On Fri 2002-06-07 at 11:43:38 +0200, [EMAIL PROTECTED] wrote:
> [...]
> > However, I think my question is valid... I don't think it is a
> good idea to
> > limit the number of joins based on the processor architecture, and this
> > makes me wonder what other 'strange' limitations I might find
> in mysql...
> > some questions to Victoria/MySQL AB:
>
> Well, of course, they could limit the join to 31 tables on all
> platforms.
>
> But then people would start to ask, why this limit is artificially low
> on 64 bit platforms.

Well... :)

I would agree to this if any 64 bit based functionality was limited to 64
bit platforms, but that is not the case. For instance, we can use BIGINT on
all platforms. I don't think 'people' would think there is a connection
between the processor word width and the number of joins in a single
select... but of course, I could be wrong.

I do not suggest that the limit should be lowered to 31 on all platforms,
but I find it hard to believe that it is impossible to implement joins in a
different way, so that the max number of joins would be the same on all
platforms. Monty & company have of course worked hard to prevent platform
dependency, and it seems as this is the only part of mysql directly
dependant on the processor architecture. I find it strange, I don't say it
is not true.

> > What (other) features of mysql are dependant on the processor
> > architecture?
>
> file size (indirectly... it is dependend on the OS, but the OS is
> often dependend on the architecture).
>
> I can not think of any other, currently.

If file size qualifies, then the table name letter casing issue and the
differences in the floating point functions also qualifies. But all of these
are OS dependant, not directly processor architecture dependant. And I think
the number of joins in a query is a _feature_ on a different level.

> [...]
> > Is this limitation "by design", and if so, why? (I suspect it is by
> > accident, and that's ok. That would mean it will probably be
> fixed in the
> > future.)
>
> I can only guess, but I think it is still there, because increasing it
> would be fruitless as long as finding the optimal join is in O(2^N).

You are probably right.

> Additionally, people seem to seldom need so many tables in a single
> join, and that's probably, too, why the join order optimizer is still
> the way it is.

Yes... and seldom = sometimes... :)

I don't know the limits for any of the other rdbms, but crashme reports +64
for IBM, Microsoft, Oracle and PostgreSQL.

> > The join order optimization is done before the query is executed, so
> > I guess you are not talking about the _query_ beeing to slow, but
> > the _preparation_ of the query... right?
>
> I am not sure what your point is.

Just making sure we are talking about the same thing. :)

> The client will have to wait too long, no matter where the time is spent.

The nature of my application allows for a relatively slow query preparation,
because I have few simultaneous queries. I guess up to 0.2 sec could have
been acceptable. (I want the answer within 0.5 sec.)

Query execution time will always increase for each join you add to a select,
but that was of course _not_ what you was talking about. I should have known
better. :)

> > In our current implementation of this system, we have about 20
> tables in the
> > main select statement, and we have never seen this problem. Will it be a
> > problem with 24 tables? 28 tables? You probably don't know the answer to
> > these questions, but maybe you can tell me more about why you suspect 30
> > tables would be too slow...?
>
> Because people had problems with that in the past and asked on the
> list for the reason and the answer (by Monty, I think) was that the
> join order optimization takes the additional time (in comparison to a
> STRAIGHT_JOIN). The delay was in the order of seconds, IIRC.

Ah. Thanks.

> You'd have to try out. Since the time is doubling with each table,
> let's say 31 tables would need 100 secs, with 20 tables you would
> hardly notice it, as it would be only 1/2048 of that, i.e. less than
> 0.05 secs.

Yes, I see. (31-20=11, 2048 = 11 bits)

> You see why allowing 64 tables futile?

Well... apparently it _is_ allowed on 64 bit platforms... :)

But I understand why implementing it for 32 bit platform is not a
prioritized task for the developers.

> Presuming processing one order
> would be as simply as one plain instruction, the join order optimizer
> would need on a Pentium III 500 (225.4 MIPS):
>
> #tables
>  10  0.045 secs
>  20  0.0047 secs
>  30  4.76 secs
>  40  1.35 hours
>  50 57.81 days
>  60162.20 years
>
> Of course, real scaling will differ as pure MIPS don't show real
> speed. But you get the idea.

Yupp. Exponential growth.

> For your purpose, you have to try out, if speed decrease with 30
> tables matters to you. Please post any result you find.

Yes, I will do some tests during the next couple of week

MySQL Replication

[Orr, Steve]

What's the best way to lock down a slave to ensure that only select SQL
queries are executed and that all command line SQL inserts, updates, and
deletes are performed only on the master?

TIA

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

inserting values w/ relationships

[Jule Slootbeek]

Hey gang,
I have two tables:

user
++--+--+-+-+---+
| Field  | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| user_id| int(10) unsigned |  | PRI | NULL| auto_increment|
| first_name | varchar(10)  |  | | |   |
| last_name  | varchar(20)  |  | | |   |
| email  | varchar(100) |  | | |   |
| username   | varchar(16)  |  | | |   |
| password   | varchar(32)  |  | | |   |
++--+--+-+-+---+

and

quiz
+-+--+--+-+-++
| Field   | Type | Null | Key | Default | Extra  |
+-+--+--+-+-++
| quiz_id | int(10) unsigned |  | PRI | NULL| auto_increment |
| user_id | int(10) unsigned |  | | 0   ||
| title   | varchar(255) |  | | ||
+-+--+--+-+-++

now user_id and is a relationship between user and quiz...
How do i make sure that when i insert a new row into quiz that user_id
gets taken from quiz, without me having to look it up in user and
entering it manually?
any ideas?

Jule
--
Jule Slootbeek  
[EMAIL PROTECTED]

http://blindtheory.cjb.net

( 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

Plagued by Error 127

[Thomas Lackner]

A client of mine runs MySQL in a pretty high volume environment, and for 
the past couple months we've had a lot of trouble with corrupt tables 
(error 127). We've changed the MySQL version (presently to version 
3.23.49), FreeBSD version (presently to 4.5-STABLE), hardware (from IDE 
to SCSI) and of course the amount of data involved. We aren't using 
LinuxThreads, soft updates, async filesystems, or any other edgey 
performance options. The system was built by Rackspace so I can assume 
decent quality components, and besides the problem has followed us after 
a server change.

The error occurs almost at random. Usually once or twice a week, always 
on the same table, and not necessarily at high load times.

The table involved (so far it has only happened on one table) is 
frequently truncated, and it has an almost 1:1 ratio of selects and 
inserts.

I can always use "myisamchk", but usually this ends up deleting every 
record in the table. (Luckily for us this is an okay situation based on 
our application) Running myisamchk on a scheduled basis is a bit too 
much of a hack for me to explain to my client -- and my conscience.

What can I do to diagnose and resolve this problem?
-- 
[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: new to mysql, what did I type wrong?

[justin cunningham]

Try 'mysqlaccess root localhost' and see if there is a password set in
the output.  

Try man mysqladmin and set the password there.

If you've hosed your perms then stop the damon and restart it with
--skip-grant-tables and start again.  

Remember to do mysqladmin reload or flush privileges from the mysql
client.

When In doubt try man mysql... 

Also, once you've got the password working you can save it in
/root/.my.cnf so you don't have to type it all the time.  

Read the man pages carefully since setting the password from the command
prompt & mysql client syntax varies.

Regards, Justin 

-Original Message-
From: castopia [mailto:[EMAIL PROTECTED]] 
Sent: Friday, June 07, 2002 2:11 PM
To: [EMAIL PROTECTED]
Subject: new to mysql, what did I type wrong?

Hi,

I try to change the root password after the first time
connecting to mysql server. I followed the tutorial
at http://www.mysql.com/articles/mysql_intro.html.

Somehow, I got the error mesage, what did I type
wrong?

Please help. Thank you.


~ # mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.49

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');
ERROR 1133: Can't find any matching row in the user table

mysql> set password for 'root'@'localhost' = password('mypass');
ERROR 133 : Can't find any matching row in the user table
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

new to mysql, what did I type wrong?

[castopia]

Hi,

I try to change the root password after the first time
connecting to mysql server. I followed the tutorial
at http://www.mysql.com/articles/mysql_intro.html.

Somehow, I got the error mesage, what did I type
wrong?

Please help. Thank you.


~ # mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.49

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');
ERROR 1133: Can't find any matching row in the user table

mysql> set password for 'root'@'localhost' = password('mypass');
ERROR 133 : Can't find any matching row in the user table
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

Monitoring locks

[Candice M Bostwick]

We are  using  MySQL 3.23.42  and having some difficulties with locks.
I was wondering if anyone out there has written any neat monitoring tools
or knows of
one that will alarm and send email when locks are excessive/persistent.

Thanks,  in advance
Candy Bostwick

Candice Bostwick ([EMAIL PROTECTED])
Database Specialist (http://waterdata.usgs.gov/nwis)
USGS - National Center Reston, VA  20192
(703) 648-4878


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mySQL and eVC++ app

[Ruben Roberts]

Hello,

I created an eVC++ app for Pocket PC that uses libmySQL.lib, but it 
won't compile.  It compiles
when I comment out any references to any mySQL functions from the 
libmySQL dll.  I initially used
mysql++ but found out that it uses STL, which of course eVC++ doesn't 
support, so I was hoping
libmySQL.lib would solve the problem.  I get "error LNK2019: unresolved 
external symbol ", and
then the respective symbols I reference from libmySQL.dll.  I created a 
VC++ app and pasted in all
the mySQL portions from the eVC++ app, and of course, it compiles, and 
when I run the app it
inserts and pulls the data from mySQL.  When I change the project 
settings to print progress
messages when linking, in my eVC++, I see where it's searching 
libmySQL.lib, but it never loads
libmySQL.dll.  Is it possible to remedy this, or is it just not going 
to work?  Thanks for your
time.

RRoberts

__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.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: Innodb "autoextended"

[Weaver, Walt]

I believe the autoextend functionality won't be available until 4.0.2.

--Walt Weaver
  Bozeman, Montana

-Original Message-
From: vlady [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 07, 2002 2:19 PM
To: [EMAIL PROTECTED]
Subject: Innodb "autoextended"


Hi all,
I am using mysql-4.0.1. I am trying to set up a second innodb datafile. 
Following the instructions in the manual I added :

"innodb_data_file_path=ibdata1:64M;ibdata2:100M:autoextend"

in my my.cnf file, but when I restart the mysql I get the error:

"InnoDB: syntax error in innodb_data_file_path"

I found that the reason for that is the last field "autoextend".
Can some one has had the same problem or somthing is wrong with my 
instalation?

Vlady


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: Installing MySQL++

[Richard Fox]

Minor points:

Why didn't you add -I/usr/include/mysql to compile options instead of
changing  to ?

Try posting to [EMAIL PROTECTED] instead, it's low traffic but the
correct list for mysql++

I upgraded to GCC 3.0.4 for other reasons and built mysql++ with that, and
never ran into the errors you are getting (but I had to apply a patch to the
mysql++ src, provided on the mysql++ download page)

Rich


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Innodb "autoextended"

[vlady]

Hi all,
I am using mysql-4.0.1. I am trying to set up a second innodb datafile. 
Following the instructions in the manual I added :

"innodb_data_file_path=ibdata1:64M;ibdata2:100M:autoextend"

in my my.cnf file, but when I restart the mysql I get the error:

"InnoDB: syntax error in innodb_data_file_path"

I found that the reason for that is the last field "autoextend".
Can some one has had the same problem or somthing is wrong with my 
instalation?

Vlady


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Maximum JOINED tables

[Benjamin Pflugmann]

Hi.

On Fri 2002-06-07 at 11:43:38 +0200, [EMAIL PROTECTED] wrote:
[...]
> However, I think my question is valid... I don't think it is a good idea to
> limit the number of joins based on the processor architecture, and this
> makes me wonder what other 'strange' limitations I might find in mysql...
> some questions to Victoria/MySQL AB:

Well, of course, they could limit the join to 31 tables on all
platforms.

But then people would start to ask, why this limit is artificially low
on 64 bit platforms.

> What (other) features of mysql are dependant on the processor
> architecture?

file size (indirectly... it is dependend on the OS, but the OS is
often dependend on the architecture).

I can not think of any other, currently.

[...]
> Is this limitation "by design", and if so, why? (I suspect it is by
> accident, and that's ok. That would mean it will probably be fixed in the
> future.)

I can only guess, but I think it is still there, because increasing it
would be fruitless as long as finding the optimal join is in O(2^N).

Additionally, people seem to seldom need so many tables in a single
join, and that's probably, too, why the join order optimizer is still
the way it is.

> The join order optimization is done before the query is executed, so
> I guess you are not talking about the _query_ beeing to slow, but
> the _preparation_ of the query... right?

I am not sure what your point is.

The client will have to wait too long, no matter where the time is spent.

> In our current implementation of this system, we have about 20 tables in the
> main select statement, and we have never seen this problem. Will it be a
> problem with 24 tables? 28 tables? You probably don't know the answer to
> these questions, but maybe you can tell me more about why you suspect 30
> tables would be too slow...?

Because people had problems with that in the past and asked on the
list for the reason and the answer (by Monty, I think) was that the
join order optimization takes the additional time (in comparison to a
STRAIGHT_JOIN). The delay was in the order of seconds, IIRC.

You'd have to try out. Since the time is doubling with each table,
let's say 31 tables would need 100 secs, with 20 tables you would
hardly notice it, as it would be only 1/2048 of that, i.e. less than
0.05 secs.

You see why allowing 64 tables futile? Presuming processing one order
would be as simply as one plain instruction, the join order optimizer
would need on a Pentium III 500 (225.4 MIPS):

#tables
 10  0.045 secs
 20  0.0047 secs
 30  4.76 secs
 40  1.35 hours
 50 57.81 days
 60162.20 years

Of course, real scaling will differ as pure MIPS don't show real
speed. But you get the idea.

For your purpose, you have to try out, if speed decrease with 30
tables matters to you. Please post any result you find.

Bye,

Benjamin.

-- 
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Re: insert

[Chris Knipe]

> Escape the \
> 
> like so:
> 
> 'c:\\temp\\calc.exe'
> 
> It's all in the manuals.
> 
> Kind Regards,
> 
> Chris Knipe
> MegaLAN Corporate Networking Services
> Tel: +27 21 854 7064
> Cell: +27 72 434 7582
> 
> - Original Message - 
> From: Silmara Cristina Basso 
> To: [EMAIL PROTECTED] 
> Sent: Friday, June 07, 2002 10:19 PM
> Subject: insert
> 
> 
> I think you don't undestand...
> 
> My problem is when i run the select the result is the folling
> 
> c:temcalc.exe); not c:\temp\calc.exe





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: newbie

[Chris Knipe]

INSERT INTO  (column) VALUES ('path');

I'd recommend you goto www.mysql.com and download the manual - then read it.


Kind Regards,

Chris Knipe
MegaLAN Corporate Networking Services
Tel: +27 21 854 7064
Cell: +27 72 434 7582

- Original Message -
From: "Silmara Cristina Basso" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 07, 2002 9:44 PM
Subject: newbie


> I'm newbie MySql
> I have one field path-char-250.
>
> How can i insert the following value ('c:\temp\calc.exe')?
>
>
>
>
>
>
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> 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

mySQL and eVC++ app

[Ruben Roberts]

Hello,

I created an eVC++ app for Pocket PC that uses libmySQL.lib, but it 
won't compile.  It compiles
when I comment out any references to any mySQL functions from the 
libmySQL dll.  I initially used
mysql++ but found out that it uses STL, which of course eVC++ doesn't 
support, so I was hoping
libmySQL.lib would solve the problem.  I get "error LNK2019: unresolved 
external symbol ", and
then the respective symbols I reference from libmySQL.dll.  I created a 
VC++ app and pasted in all
the mySQL portions from the eVC++ app, and of course, it compiles, and 
when I run the app it
inserts and pulls the data from mySQL.  When I change the project 
settings to print progress
messages when linking, in my eVC++, I see where it's searching 
libmySQL.lib, but it never loads
libmySQL.dll.  Is it possible to remedy this, or is it just not going 
to work?  Thanks for your
time.

RRoberts


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.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

newbie

[Silmara Cristina Basso]

I'm newbie MySql
I have one field path-char-250.

How can i insert the following value ('c:\temp\calc.exe')?





 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Permissions for mysqladmin

[Gerald Clark]

Cindy Nelson wrote:

> Hi,
>
> I am using SuSE 7.0 and MySQL 4.0.1. I had quite a time
> getting the daemon started on Linux but finally succeeded.
> I can start the daemon logged on as su and I can connect
> to the database as user=mysql and modify the existing DB
> called test.
>
> I set the permissions as recommended in INSTALL-BINARY.
> Logged on as "su" I did the following:
>  chown -R root /usr/local/mysql 

that should be mysql, not root
chown -R mysql:mysql  /usr/local/mysql

>
>  chown -R mysql /usr/local/mysql/data 

not necessary - see above.

>
>  chgrp -R  /usr/local/mysql 

You forgot the group, but not necessary - see above.

>
>  chown -R /usr/root/local/mysql/bin

You forgot the user, but this should be ok from the chown -R 
 mysql:mysql /usr/local/mysql above

>
>
> Apparently what should happen is the system administrator should
> be able to start and stop the daemon and control the binary files and
> anybody in the group that I created "mysql" should be able to manipulate
> data in the DBs to which they have permissions. 

Only the mysqld  manipulates the data.

>
>
> Well.. I have a problem when I try to issue the
> command to create a new database. 

try:
mysql -u root

That should get you in.
You need to read the manual section on privileges.
You have to set up privileges for other users that want to connect to 
the server.

>
>
>   mysqladmin create 
>
> If I am logged on as root or a user in the group mysql, I get the message
> Access denied for @localhost.  I tried doing a chmod on 
> several files
> in mysql but to no avail. Also I notice there is no file in etc called
> my.cfg as the documentation suggests. It seems that the permissions are
> stored in a table in /usr/local/mysql/data/mysql but I cannot get at 
> the table to
> change anything.
>
> It would seem that there is someway to issue some kind of external 
> command
> that would change the permissions for the superuser and user mysql 
> without
> manipulating that table directly.
>
> I am really not concerned about security right now. This is a stand 
> alone Linux
> server at my house that is rarely connected to anything. I just want 
> to practice
> my SQL skills.
>
> Any ideas.
>
>
> **
> Cindy Nelson(404) 213-9498 cell
> 202 Jimmie Nelson Rd SW(770) 382-4474 home
> Kingston, Ga 30145
>
>  e-mail: [EMAIL PROTECTED]
>
> http://home.earthlink.net/~cindynelson
>
> **




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: sub-queries

[Benjamin Pflugmann]

Hello.

On Fri 2002-06-07 at 05:15:51 +0200, [EMAIL PROTECTED] wrote:
[...]
> > No, it's probably not difficult at all. It simply seems as if
> > people do not understand exactly what you want. A communication
> > problem, IMHO.
> 
> Kevin was also kind enough to point this out to me, and I have to
> agree with both of you.  As I did explain to Kevin in private
> however, I do have very

Never mind. I was just trying to explain why nobody came up with the
right solution at once.

> If anyone feel like taking 10 minutes of their time, and explaining

Well, probably more than 10 minutes. ;-) 

> exactly what the query does, I'll appreciate it allot.  It may help
> me understand the basic logic behind the structure of the query, and
> aid me in the future when I may need to execute such queries again
> (although, I'm honestly hoping that when such a time comes, MySQL
> will support sub-queries).

Okay. Let's see the query again, a bit reformatted:

SELECT mh.HostID, mh.HostDescription
FROM   monitorhosts mh
   LEFT JOIN monitorhostgroupdetails mhgd ON
mh.HostID = mhgd.HostID AND
mhgd.HostGroupID = 2
WHERE  mhgd.HostID IS NULL AND
   mh.CompanyID = 1;


First, I assume that it is known that a normal JOIN (written with ',')
builds a cross product of the two tables, i.e. build pairs of each
record from the first table with each record of the second table.
Then, you normally have something like

  WHERE mh.HostID = mhgd.HostID

which only chooses those pairs, which have matching HostIDs. A LEFT
JOIN does the same, but for all records of the left (=first) table,
which have no match in the right table, it will insert NULL for the
right table values. I.e. if you have (from above)

  LEFT JOIN ... ON mh.HostID = mhgd.HostID

you will get the result from a normal JOIN (all pairs for which
mh.HostID = mhgd.HostID is true) and all remaining records from
monitorhosts (all for whose HostID was no record in
monitorhostgroupdetails) paired with NULL values for the columns of
monitorhostgroupdetails.

Another way to see this is to take all records from the left table and
pair them with either the matching records from the right table or
with NULL values if record matched.

An additional "mhgd.HostGroupID = 2" in the ON clause will only
consider a pair valid, if "mhgd.HostGroupID = 2" (as in a normal join)
and for all non-fitted records of the left table it pairs them with
NULL values again. This means, we now get NULL values for all (former)
pairs which have "mhgd.HostGroupID != 2".

Now the WHERE clause can be applied. "mhgd.HostID IS NULL" now chooses
all records, which have NULL values for the right table, i.e. all
pairs, which had no match on the condition "mh.HostID = mhgd.HostID
AND mhgd.HostGroupID = 2", this means all records of the left table,
for which there was no matching HostID in mhgd which also was in
mhgd.HostGroupID = 2. The latter is a different wording for

... mh.HostID NOT IN ( SELECT mhgd.HostID
   FROM monitorhostgroupdetails mhgd
   WHERE mhgd.HostGroupID = 2 )

Which should look familiar to you. ;-)

"mh.CompanyID = 1" restricts the result to only the company in
question, of course.

Of course, the RDBMS (here MySQL) optimizes how it retrieves the pairs
you want. But the above is the underlying logic of how it works.

Hope that helped,

 Benjamin.


-- 
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Permissions for mysqladmin

[Cindy Nelson]

Hi,

I am using SuSE 7.0 and MySQL 4.0.1. I had quite a time
getting the daemon started on Linux but finally succeeded.
I can start the daemon logged on as su and I can connect
to the database as user=mysql and modify the existing DB
called test.

I set the permissions as recommended in INSTALL-BINARY.
Logged on as "su" I did the following:
  chown -R root /usr/local/mysql
  chown -R mysql /usr/local/mysql/data
  chgrp -R  /usr/local/mysql
  chown -R /usr/root/local/mysql/bin

Apparently what should happen is the system administrator should
be able to start and stop the daemon and control the binary files and
anybody in the group that I created "mysql" should be able to manipulate
data in the DBs to which they have permissions.

Well.. I have a problem when I try to issue the
command to create a new database.

   mysqladmin create 

If I am logged on as root or a user in the group mysql, I get the message
Access denied for @localhost.  I tried doing a chmod on several files
in mysql but to no avail. Also I notice there is no file in etc called
my.cfg as the documentation suggests. It seems that the permissions are
stored in a table in /usr/local/mysql/data/mysql but I cannot get at the 
table to
change anything.

It would seem that there is someway to issue some kind of external command
that would change the permissions for the superuser and user mysql without
manipulating that table directly.

I am really not concerned about security right now. This is a stand alone Linux
server at my house that is rarely connected to anything. I just want to 
practice
my SQL skills.

Any ideas.


**
Cindy Nelson(404) 213-9498 cell
202 Jimmie Nelson Rd SW(770) 382-4474 home
Kingston, Ga 30145

  e-mail: [EMAIL PROTECTED]

http://home.earthlink.net/~cindynelson

**


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mySQL and eVC++

[Ruben Roberts]

Hello,

I created an eVC++ app for Pocket PC that uses libmySQL.lib, but it 
won't compile.  It compiles
when I comment out any references to any mySQL functions from the 
libmySQL dll.  I initially used
mysql++ but found out that it uses STL, which of course eVC++ doesn't 
support, so I was hoping
libmySQL.lib would solve the problem.  I get "error LNK2019: unresolved 
external symbol ", and
then the respective symbols I reference from libmySQL.dll.  I created a 
VC++ app and pasted in all
the mySQL portions from the eVC++ app, and of course, it compiles, and 
when I run the app it
inserts and pulls the data from mySQL.  When I change the project 
settings to print progress
messages when linking, in my eVC++, I see where it's searching 
libmySQL.lib, but it never loads
libmySQL.dll.  Is it possible to remedy this, or is it just not going 
to work?  Thanks for your
time.

RRoberts


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.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

myodbc & odbc@localhost

[Ray]

how much access does odbc@localhost need?

i don't have any dsn that use the user, i don't have any php scripts that use 
it as a log in into the database; however if i disable the user, ALL of the 
pages that use a mysql odbc datasource break with the error that 
odbc@localhost can't log in. (all of the affected pages are writen in  
coldfusion)

this only started happening the day after phpMyAdmin was installed, and 
everything on the machine was fine for about 8 hrs after that (sorry, don't 
know the exact time, but poo hit the fan in the night)  everything was 
working for about 5 days before that, and the only other changes we made were 
some syntax fixes on coldfusion pages.  nothing that should affect the 
datasource logins.  

[no, i'm not trying to blame phpMyAdmin. i know it has nothing to do with it, 
but its the only changes that we know of before it broke.]
[All = 4 sites, each with their own mysql login, the machine is a newly setup 
(maybe about 2 weeks now), and the first one we have here that uses MySQL]

system info:
Win2k (w sp2)
Apache 1.3.24
ColdFusion 5
Php 4.2.1
MyODBC 2.50.39-nt
MySQL 3.23.49-nt

each virtual domain that uses mysql database has their own logins

and the only thing keeping me from editing odbc@localhost and hunting for the 
breaking point is that this has happened on the production machine.

-- 
mysql, sql, query

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysql@lists.mysql.com

[root]

>Description:

>How-To-Repeat:

>Fix:


>Submitter-Id:  
>Originator:
>Organization:
 
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:  
>Severity:  
>Priority:  
>Category:  mysql
>Class: 
>Release:   mysql-3.23.39 (Source distribution)

>Environment:

System: Linux test2 2.2.19 #22 Wed Jun 20 18:12:16 PDT 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-slackware-linux/2.95.3/specs
gcc version 2.95.3 20010315 (release)
Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Jun  7 01:15 /lib/libc.so.6 -> libc-2.2.3.so
-rwxr-xr-x1 root root  4783716 May 26  2001 /lib/libc-2.2.3.so
-rw-r--r--1 root root 24721042 May 26  2001 /usr/lib/libc.a
-rw-r--r--1 root root  178 May 26  2001 /usr/lib/libc.so
Configure command: ./configure  --prefix=/usr --with-mysqld-user=mysql 
--with-unix-socket-path=/var/run/mysql/mysql.sock --localstatedir=/var/lib/mysql 
--with-pthread --enable-thread-safe-client --enable-assembler --with-raid 
--with-libwrap --without-bench i386-slackware-linux


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysql@lists.mysql.com

[root]

>Description:

>How-To-Repeat:

>Fix:


>Submitter-Id:  
>Originator:
>Organization:
 
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:  
>Severity:  
>Priority:  
>Category:  mysql
>Class: 
>Release:   mysql-3.23.39 (Source distribution)

>Environment:

System: Linux test2 2.2.19 #22 Wed Jun 20 18:12:16 PDT 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-slackware-linux/2.95.3/specs
gcc version 2.95.3 20010315 (release)
Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Jun  7 01:15 /lib/libc.so.6 -> libc-2.2.3.so
-rwxr-xr-x1 root root  4783716 May 26  2001 /lib/libc-2.2.3.so
-rw-r--r--1 root root 24721042 May 26  2001 /usr/lib/libc.a
-rw-r--r--1 root root  178 May 26  2001 /usr/lib/libc.so
Configure command: ./configure  --prefix=/usr --with-mysqld-user=mysql 
--with-unix-socket-path=/var/run/mysql/mysql.sock --localstatedir=/var/lib/mysql 
--with-pthread --enable-thread-safe-client --enable-assembler --with-raid 
--with-libwrap --without-bench i386-slackware-linux


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Counting distinct elements

[David M. Peak]

Depends.  If this is a correlation table and the combined columns are a
primary key,

SELECT ObjId,
   COUNT(ElemId)
FROMElems
GROUP BY ElemId;

if the combination is not unique, then:

SELECT ObjId,
   COUNT(DISTINCT ElemId)
FROMElems
GROUP BY ElemId;

- Original Message -
From: "Csaba Gabor" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 07, 2002 10:20 AM
Subject: Counting distinct elements


> Seems like this should be an easy question, but I just don't get it:
>
> If I have CREATE TABLE Elems (ObjId INT, ElemId INT);
> Is there a nice query to find the Number of Distinct ElemId's for each
ObjId?
>
> Motivation: Later I will want to add a WHERE ObjId IN (...) and join the
Obj describing table to
> the results of this query.
>
> Thanks,
> Csaba Gabor from New York
>
> __
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.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
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mySQL and eVC++

[Ruben Roberts]

Hello,

I created an eVC++ app for Pocket PC that uses libmySQL.lib, but it won't compile.  It 
compiles
when I comment out any references to any mySQL functions from the libmySQL dll.  I 
initially used
mysql++ but found out that it uses STL, which of course eVC++ doesn't support, so I 
was hoping
libmySQL.lib would solve the problem.  I get "error LNK2019: unresolved external 
symbol ", and
then the respective symbols I reference from libmySQL.dll.  I created a VC++ app and 
pasted in all
the mySQL portions from the eVC++ app, and of course, it compiles, and when I run the 
app it
inserts and pulls the data from mySQL.  When I change the project settings to print 
progress
messages when linking, in my eVC++, I see where it's searching libmySQL.lib, but it 
never loads
libmySQL.dll.  Is it possible to remedy this, or is it just not going to work?  Thanks 
for your
time.

RRoberts


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.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: Counting distinct elements

[Gerald Clark]

You mean

SELECT ObjId,ElemId,count( distinct Objid,ElemId ) from Elems group by 
ObjId;

?


Csaba Gabor wrote:

>Seems like this should be an easy question, but I just don't get it:
>
>If I have CREATE TABLE Elems (ObjId INT, ElemId INT);
>Is there a nice query to find the Number of Distinct ElemId's for each ObjId?
>
>Motivation: Later I will want to add a WHERE ObjId IN (...) and join the Obj 
>describing table to
>the results of this query.
>
>Thanks,
>Csaba Gabor from New York
>
>__
>Do You Yahoo!?
>Yahoo! - Official partner of 2002 FIFA World Cup
>http://fifaworldcup.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
>
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Counting distinct elements

[Csaba Gabor]

Seems like this should be an easy question, but I just don't get it:

If I have CREATE TABLE Elems (ObjId INT, ElemId INT);
Is there a nice query to find the Number of Distinct ElemId's for each ObjId?

Motivation: Later I will want to add a WHERE ObjId IN (...) and join the Obj 
describing table to
the results of this query.

Thanks,
Csaba Gabor from New York

__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.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

unable to install mysql 3.23.49 on solaris-sparc

[Pushkar Pradhan]

Hi,
I'm new to this stuff. I first tried to install the binary distribution
but it failed because of not finding libz.so,
then I am trying to install with the source code,
./configure  --prefix=/rstc/user1/erc/pushkar/mysql-3.23.49
make was okay, mesgs. were given about not finding libz.so but said it was
okay and it could be linked later.
but make install fails:

 /usr/local/gnu/bin/install -c  test-ATIS
/rstc/user1/erc/pushkar/mysql-3.23.49/sql-bench/test-ATIS
/usr/local/gnu/bin/install: `test-ATIS' and
`/rstc/user1/erc/pushkar/mysql-3.23.49/sql-bench/test-ATIS' are the same
file
*** Error code 1
make: Fatal error: Command failed for target `install-benchSCRIPTS'
Current working directory /rstc/user1/erc/pushkar/mysql-3.23.49/sql-bench
*** Error code 1
make: Fatal error: Command failed for target `install-am'
Current working directory /rstc/user1/erc/pushkar/mysql-3.23.49/sql-bench
*** Error code 1
make: Fatal error: Command failed for target `install-recursive'

I dont' have admin priveleges so I'm installing on my own partition,
thus I can't even do:
shell> groupadd mysql
shell> useradd -g mysql mysql

Is that the problem? Thanks,


-Pushkar S. Pradhan


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Maximum JOINED tables

[Roger Baklund]

* Victoria Reznichenko
> RB> mysql is considered to be platform independant, and I suppose
> RB> MySQL AB is trying to keep it that way...?
>
> Roger, MySQL is platform independent.

I did not say it was not... :)

> It works on any platform with
> the same data, but if the architecture is 64 bit, you can use it for more
> power than on 32 bit.

If the feature set is different on different platforms, the product is not
truly platform independant. The number of joins in a query is a part of the
feature set, IMHO.

It is fully possible that right now someone is developing an application on
a 64bit platform, not knowing that the system will not work on 32bit
platforms simply because they have too many joins.

It is not the 'lack of power' on 32bit platforms that will prevent it from
working, it's the _platform dependant_ limitation in the number of joins.

Ok, _now_ I am saying that mysql is not platform independant, but of course,
this 'incompatibility' is not something most users will run into, so I guess
we could say it is 99% true that mysql is platform independant, or that
mysql is 99% platform independant... :)

(There are also issues with letter casing in table names and different
implementations of the floating point methods.)

> You can change it (set 63) in 32 bit CPU by hack in compilation, but
> making join map 64 bit on 32 bit box makes the work of MySQL slower.

And in this case 'slower' means...? (If we are talking about milliseconds
per query, I don't see the problem. If we are talking about milliseconds per
traversed row, there would be a problem.)

Has anyone ever done this hack, and if so, was the result usefull?

Thank you for the info, Victoria! :)

...and sorry to those who got fooled by me pointing at crashme telling them
the limit was 64 in 3.23.x...

--
Roger
sql


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL queries, dynamic???

[Luc Foisy]

Is there any way to dynamically create columns non programatically? As in pure sql 
queries?

This is what I am doing now:

SELECT SQL_BUFFER_RESULT 
ORDERHEADER.ID_CUSTOMER AS 'Acct #',
CUSTOMER.Company AS 'Company',
SALESLEVEL.Name AS 'Client Type',
DATE_FORMAT(ORDERHEADER.AvailableAt, '%b') AS 'Month',
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 1,1,0)) AS '1',
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 2,1,0)) AS '2',
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 3,1,0)) AS '3',
etc
SUM(IF(DAYOFMONTH(ORDERHEADER.AvailableAt) = 31,1,0)) AS '31',
COUNT(ORDERHEADER.ID) AS 'Total' 
FROM ORDERHEADER 
LEFT JOIN CUSTOMER ON ORDERHEADER.ID_CUSTOMER = CUSTOMER.ID 
LEFT JOIN SALESLEVEL ON CUSTOMER.ID_SALESLEVEL = SALESLEVEL.ID 
WHERE (ORDERHEADER.AvailableAt BETWEEN '[param:StartDate]' AND 
DATE_FORMAT('[param:EndDate]', '%Y%m%d235959')) 
GROUP BY ORDERHEADER.ID_CUSTOMER, DATE_FORMAT(ORDERHEADER.AvailableAt, '%m') 
ORDER BY DATE_FORMAT(ORDERHEADER.AvailableAt, '%b'), Total DESC

(NOTE: [param:EndDate] and [param:StartDate] is filled in through a parser, these can 
range from 1 day to months apart)

Sample output is as follows:

(these are days)
Acct#   Company Client Type Month   1   2   3   ... 31  Total

3212Joe's Bike Shop Weekly  DEC 5   7   3   ... 4  
 72
3423Sam's Deli  Daily   DEC 6   3   3   ... 2  
 65
3243Jerry's Bi-Weekly   DEC 4   5   2   ... 5  
 69
3212Joe's Bike Shop Weekly  JAN 0   4   3   ... 7  
 57
3423Sam's Deli  Daily   JAN 0   2   7   ... 8  
 64
3243Jerry's Bi-Weekly   JAN 0   4   3   ... 3  
 57


Desired output:

Acct#   Company Client Type 12/112/212/3... 12/31   12/Total   
 1/1 1/2 1/3 ... 1/311/Total Grand Total
3212Joe's Bike Shop Weekly  5   7   3   ... 4   72 
 0   4   3   ... 3   57  129
3423Sam's Deli  Daily   6   3   3   ... 2   65 
 0   2   7   ... 8   64  129
3243Jerry's Bi-Weekly   4   5   2   ... 5   69 
 0   4   3   ... 3   57  126

12/1, 12/2, 12/3 etc being month/day


The report original report has the day columns hard coded, can they by dynamic based 
on the dates chosen, as in the number of days per month and the actual date numbers 
themselves
Is this possible in any way? it doesnt matter if temp tables need to be made or not to 
do this, whatever means sql queries can manage it

I have a progromatic (JAVA) way to do this with a while loop cycling through the date 
ranges which generates the sql then runs that query, but don't really want to program 
everything when a new report like this would be needed


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: Thread based optimization?

[Matt Rowe]

Hi All

At 11:17 PM 6/6/2002 -0700, Jeremy wrote:
> > However, there is the occasional "select" query that requires a good
> > amount of work on the database.
>
>Why?  Can it be optimized?

The query is needs about 10 left joins, and requires data from about 12 
different tables (some tables have > 50,000 rows).  I believe everything is 
indexed properly, because the query is very fast when it is run without the 
"order by," and "group by" clauses...  We are using LIMIT 50, but MySQL 
still has to sort through many (>50,000) rows.


> > Is there a smart way to "limit system resources per thread"?
>
>Nope.

I saw section 4.6.3 of the maual ("Limiting user resources"), but was 
hoping for some kind of undocumented tweak.

> > I'd much rather have the long-query person wait an extra few seconds
> > than have several short-query people wait.  I've tried tuning all
> > the typical parameters, but I don't think the logjam is memory-based
> > (the hard disk doesn't move).
>
>Hmm...  Have you tried making the slow query a LOW_PRIORITY one?

I don't think a "select" statements have a "low_priority" flag??  Is there 
an alternative?  I'd rather not go back and change all the "fast" queries 
to "high_priority," and I don't even think that will have much of an 
affect, since MySQL only queue's "high_priority" "selects" in front of 
"inserts" and "updates."

Thanks Jeremy,
Matt



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: newbie SOUNDEX question

[Dave Callaghan]

Thanks you for your prompt reply!

Luck of the Irish, I guess. This is another reason 
to test your systems on your client's platform, not 
your own. My client's target the Latin American 
population, so let's try this again with a more 
representative data sample.

Note that I had an error in my sql syntax in 
addition to my error in basic understanding of 
soundex.

INSERT INTO sound (name) VALUES ('Gonzales');

select id from sound where soundex(name) = 
soundex('Gonzalez');
returns 1 records

select id from sound where soundex(name) = 
soundex('Gonsallez');
returns 1 records

select id from sound where soundex(name) = 
soundex('Gimsallez');
returns 1 records

And, of course, 

select id from sound where soundex(name) = 
soundex('Calighim');
returns 1 records

select id from sound where soundex(name) = 
soundex('Callahan');
returns 0 records

Also, soundex has a known problem with first 
characters:
select id from sound where soundex(name) = 
soundex('Kallaghan');
returns 0 records

> I hate to be the one to break this to you, but
> soundex('Callaghan') != soundex('Callahan')
> 
> It's just the sad truth: the algorithm thinks they 
are pronounced
> differently, so it doesn't consider them to match.
> 
> soundex() is certainly better than nothing, but in 
many cases not much
> better. I'm not sure whether even MetaPhone (a 
more robust soundex variant)
> would get this case right, and all the 
mis-spelling systems I've ever
> implemented have been suplemented with a set of 
heuristics (like searching
> for all single-letter drops, adds, and 
transpositions) and often a
> mis-spelling database in an attempt to find 
suggested alternatives.
> 
> -rob
> 
> On 7/6/02 at 11:39 pm, Dave Callaghan 
<[EMAIL PROTECTED]> wrote:
> 
> > I want to use SOUNDEX for name queries, but I 
don't 
> > seem to be doing it correctly. 
> > 
> > As a test, I made a small table and popluated it 
> > with my name. My goal is to be able to find my 
name 
> > 'Callaghan', with its most common misspelling 
> > 'Callahan'. 
> > 
> > I've read the MySQL doc, so I know how to 
actually 
> > invoke soundex at the mysql prompt. I read an 
> > article that said soundex queries move more 
quickly 
> > if you add a column for the soundex.
> > 
> > Thus:
> > 
> > CREATE TABLE sound (
> >   id tinyint(3) NOT NULL auto_increment,
> >   name varchar(100) default NULL,
> >   sound varchar(100) default NULL,
> >   PRIMARY KEY  (id)
> > ) TYPE=MyISAM;
> > 
> > select soundex('Callaghan');
> > result = C425
> > 
> > INSERT INTO sound VALUES (1, 'Callaghan', 
'C425');
> > 
> > Simple enough. Now, I expected all three of the 
> > following queries to return a value. 
> > 
> > select id from sound where sound = 
> > soundex('Callaghan');
> > returns 1 records
> > 
> > select id from sound where sound = 
> > soundex('Callahan');
> > returns 0 records
> > 
> > select id from sound where name = 
> > soundex('Callaghan');
> > returns 0 records
> > 
> > I'm missing something obvious here, but I've 
read 
> > the doc and I'm not sure what's wrong.
> > 
> > -- 
> > Get your free email from www.linuxmail.org 
> > 
> > 
> > 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
> > 
> > 
> 
> 


-- 
Get your free email from www.linuxmail.org 


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

RE: InnoDB Hot Backups... ALL OR NOTHING ???

[Orr, Steve]

Thanks for your response Daniel,

1. I appreciate that InnoDB is more robust than ISAM- passes the "ACID"
test. 

2. > Just convert InnoDB tables to MyISAM and backup the MyISAM version.
I did this on a large table- it took a while and generated lots of I/O.
Multiply this by 200 databases and 1000 tables on a single server- it's a
production support issue. We still need a hot backup solution that's more
capable than "all or nothing." Using hot-swappable RAID with journaling file
systems doesn't obviate the need for online database backups. Another option
is full database replication with duplicate servers and disks. $igh...

3. > you can have many tablespaces...
I don't think so. According to the docs it's one tablespace with many files
and the data is comingled. On a 100GB database with 50 2GB files, what
happens when one file is lost? Restore the entire system while all the
databases are down? InnoDB is great but I'd like to be able to recover a
single database from backups while the other databases are up and running.


Enhancement Request:
1. The ability to associate a database with named tablespaces/files. 
2. Given 1, the ability to backup and recover a single database, tablespace,
or file set without impacting on the online availability of other databases,
tablespaces, and/or files.

Without this capability, the loss of one data file effectively equates to
the loss of the entire database server and the mean time to recovery (MTTR)
is unecessarily long. 


IMHO :-)




-Original Message-
From: Kiss Dániel [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 07, 2002 12:04 AM
To: Orr, Steve; [EMAIL PROTECTED]
Subject: Re: InnoDB Hot Backups... ALL OR NOTHING ???


First of all, there are many aspects of your problem.

1. The InnoDB uses transaction safe table types, and uses the log files to 
restore if anything goes wrong during the tsanasction. So it is almost 
impossible to have a permanent database error, that cannot be repaired by 
InnoDB itself. If MySQL crashes, you just have to restart it, and InnoDB 
repaires automatically all the tables containing errors.

2. In spite of the first section, its a good idea to create backups of your 
InnoDB tablespace, because it can happen that the hard disk you have your 
tablespace files fails and in a case like this you don't have anything 
else, just your backup files.
It's a little bit difficult to save all the InnoDB tablespaces onto another 
backup disk, because they can be very big, although they are compressable 
very well, because the empty spaces inside the tablespace contain zeros.
A simple solution is not to backup directly the InnoDB tablespaces. Just 
convert the InnoDB tables into MyISAM and backup the MyISAM version of them.

3. The third aspect is that you can have many tablespaces, not only one big.
For example instead of an InnoDB initialization command in my.cnf like this
  innodb_data_file_path = ibdata1:2000M
you can use this
  innodb_data_file_path = 
ibdata1:200M;ibdata2:200M;ibdata3:200M;..ibdata10:200M

Good luck,
 Daniel

At 15:21 2002.06.06. -0600, you wrote:
>Can you backup/restore just one MySQL database with InnoDB hot backup? From
>what I gather it's an all or nothing proposition. As I understand it,
>there's only one "tablespace" (with any number of data files) and all
>database tables of type 'InnoDB' are comingled in the one tablespace.
>Therefore, if a single datafile becomes corrupt, all the databases with
>InnoDB type tables are down and you have to restore everything. Is that
>right? If so are there any plans to have multiple named tablespaces?
>
>We have a single server with 150+ databases (one for each hosted customer).
>If one customer database goes down then we can restore the MYISAM type
>tables without affecting the 24X7 availability for the other 149 customers.
>However, if we convert tables to type InnoDB and a data file is corrupted
or
>lost, then all databases are down and we have to restore EVERYTHING. Is
this
>correct?
>
>
>Sincere thanks in advance...
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: newbie SOUNDEX question

[Roger Baklund]

* Dave Callaghan
> I want to use SOUNDEX for name queries, but I don't
> seem to be doing it correctly.
>
> As a test, I made a small table and popluated it
> with my name. My goal is to be able to find my name
> 'Callaghan', with its most common misspelling
> 'Callahan'.

That will be difficult using soundex(), because the two names have different
soundex values:

mysql> select soundex('Callaghan'),soundex('Callahan');
+--+-+
| soundex('Callaghan') | soundex('Callahan') |
+--+-+
| C425 | C450|
+--+-+
1 row in set (0.00 sec)

> I've read the MySQL doc, so I know how to actually
> invoke soundex at the mysql prompt. I read an
> article that said soundex queries move more quickly
> if you add a column for the soundex.

That makes sense, yes.

> Thus:
>
> CREATE TABLE sound (
>   id tinyint(3) NOT NULL auto_increment,
>   name varchar(100) default NULL,
>   sound varchar(100) default NULL,
>   PRIMARY KEY  (id)
> ) TYPE=MyISAM;
>
> select soundex('Callaghan');
> result = C425
>
> INSERT INTO sound VALUES (1, 'Callaghan', 'C425');
>
> Simple enough. Now, I expected all three of the
> following queries to return a value.
>
> select id from sound where sound =
> soundex('Callaghan');
> returns 1 records
>
> select id from sound where sound =
> soundex('Callahan');
> returns 0 records

You are looking for a row with sound="C450", which does not exist.

> select id from sound where name =
> soundex('Callaghan');
> returns 0 records

You are looking for a row with name="C425", which does not exist.

> I'm missing something obvious here, but I've read
> the doc and I'm not sure what's wrong.

You expected soundex() to catch the common misspelling for your name, but
soundex() is far from perfect. Sometimes it does not find a name that is
very similar, and sometimes it will return names that are very far from
similar...

All these should find your row:

select id from sound where sound = soundex('Calaghan');
select id from sound where sound = soundex('Callagan');
select id from sound where sound = soundex('Clgn');
select id from sound where sound = soundex('Clxm');
select id from sound where sound = soundex('Celikan');
select id from sound where sound = soundex('Cilligen');

Note that a soundex() code is always 4 characters or more in mysql. Zeroes
are appended for the missing characters:

mysql> select soundex('C');
+--+
| soundex('C') |
+--+
| C000 |
+--+
1 row in set (0.00 sec)

Also, when there are very many letters in a word, the soundex code can
become rather long...:

mysql> select soundex('Circumstantial');
+---+
| soundex('Circumstantial') |
+---+
| C62523534 |
+---+
1 row in set (0.00 sec)

Some soundex() implementations I have seen cut such a code, so that you end
up with just C625 or C6252, but with mysql you have to do this cutting
yourself, if you want it.

--
Roger
sql


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: sporadic very large temp files (temp tables)

[Egor Egorov]

Greg,
Thursday, June 06, 2002, 6:42:09 PM, you wrote:

GT> help please.  I am seeing very disturbing, very large, and very sporadic
GT> temporary files appear in the MySQL $tmpdir location.  Here's a brief listing:
GT>   > -rw-rw1 mysqlmysql6488514560 Jun  6 14:14 #sql4f3_1f8aa_2.MYD
GT>   > -rw-rw1 mysqlmysql1024 Jun  6 11:12 #sql4f3_1f8aa_2.MYI

GT> Note that first file is 6.5 GB!!!  MySQL is not crashing but in writing this
GT> huge file, it is filling the entire remaining contents of the hard drive.
GT> I used to have tmpdir unset (default is /tmp) but that filled the root
GT> partition of my drive.  I switched the tmpdir to a second disk that had more
GT> space but it ended up filling every available byte.  The entire contents of
GT> my DB is only 1.4 GB so how in the world is a 6 GB temporary table being
GT> created?

MySQL creates temporary tables for some kind of queries. As to size of
the file, it's not so strange. Most likely you query is producing 
a cartesian product of something, i.e. if you have 1000 rows in one
table and 1000 in another the result of full Cartesian product of these tables will
be a billion of rows ...

GT> The problem has been recurring about once per week for months and I have no
GT> idea at this point how to debug.  Any direction would be greatly appreciated.

GT> System info:  Red Hat 7.1; Mysql v3.23.36





-- 
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: Aggregate functions

[Egor Egorov]

Kris,
Friday, June 07, 2002, 1:21:48 PM, you wrote:

KS> Sorry to bother the Guru's here, but I've recently started working on
KS> getting a mySQL database up and running and I noticed that some
KS> aggregate functions like MAX and MIN don't have 'DISTINCT' as an
KS> optional arguement. Are these not supported or is this just missing
KS> from the documentation?

How can usage of DISTINCT influence on the result of the MIN() or MAX()?
F.e. if you have values: 3,4,1,2,1,7 the result of MIN() will be 1. It
doesn't matter how many times this value is present in the table ...

KS> Kris Simonis
KS> OGD Software





-- 
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: Re: Maximum JOINED tables

[Victoria Reznichenko]

Roger,
Friday, June 07, 2002, 12:43:38 PM, you wrote:

RB> * Benjamin Pflugmann
>> On Thu 2002-06-06 at 19:17:10 +0200, [EMAIL PROTECTED] wrote:
>> [...]
>>
>> > But... is this the only feature of mysql which is dependant on the
>> > processor architecture?
>>
>> See it the other way: You are not garantueed more than 31 tables in a
>> join. If you happen to have a 64Bit architecture, you can use more.

RB> Yes... a positive attitude makes life easier. :)

RB> However, I think my question is valid... I don't think it is a good idea to
RB> limit the number of joins based on the processor architecture, and this
RB> makes me wonder what other 'strange' limitations I might find in mysql...
RB> some questions to Victoria/MySQL AB:

RB> mysql is considered to be platform independant, and I suppose MySQL AB is
RB> trying to keep it that way...?

Roger, MySQL is platform independent. It works on any platform with
the same data, but if the architecture is 64 bit, you can use it for more
power than on 32 bit.

RB> Will the max number of joined tables (on 32-bit platform) stay at 31 in
RB> version 4.x, or is it planned to increase this limit?

I don't know.

RB> Is this limitation "by design", and if so, why? (I suspect it is by
RB> accident, and that's ok. That would mean it will probably be fixed in the
RB> future.)

You can change it (set 63) in 32 bit CPU by hack in compilation, but
making join map 64 bit on 32 bit box makes the work of MySQL slower.




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How to sort on multiple columns?

[Victoria Reznichenko]

Tim,
Friday, June 07, 2002, 7:05:40 AM, you wrote:

TJ> I am using
TJ> mysql  Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu
TJ> on RH 7.2

TJ> I would like to sort on multiple columns, but Mysql
TJ> tells my that the specified key would be too long
TJ> (error 1071).
TJ> If this is the case, what might be an alternative 
TJ> strategy?

TJ> Specifically, here is an example table with description:

TJ> describe CL0420;
TJ> +--+-+--+-+-+---+
TJ> | Field| Type| Null | Key | Default | Extra |
TJ> +--+-+--+-+-+---+
TJ> | ID   | int(11) | YES  | | 0   |   |
TJ> | Producer | char(255)   | YES  | MUL | NULL|   |
TJ> | Varietal | char(255)   | YES  | MUL | NULL|   |
TJ> | Vineyard | char(255)   | YES  | MUL | NULL|   |
TJ> | Year | char(50)| YES  | | NULL|   |
TJ> | Size | char(255)   | YES  | | NULL|   |
TJ> | Average  | int(11) | YES  | | 0   |   |
TJ> | Sold | smallint(6) | YES  | | NULL|   |
TJ> +--+-+--+-+-+---+

TJ> I'd like to sort on Producer, Varietal, Vineyard
TJ>   (in that order)
TJ> I would also appreciate being pointed towards appropriate
TJ> documentation as well.

Hm.. It's a very strange..
I can't reproduce it on my box. You gave incomplete info about your
indexes. Can you show me the output of CREATE TABLE and show your
SELECT statement?

TJ> Best Regards




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: newbie SOUNDEX question

[Richard Morton]

Hi,

You are doing everything fine, however, soundex is not a perfect system and does not 
always work. It does simplify some types of queries. But in the vast majority of cases 
I don't think that it is worth the bother. 

It doesn't actually link all the same sounding sirnames, if there were a system like 
that life would be nice and simple, however (and correct me if I am wrong) there isn't 
one. Soundex was an attempt and it doesn't really work. It is used by the US 
government but it just means you have to search under a couple of different soundex 
codes rather than searching under 8 or 9 different spellings.

See this example.

mysql> select soundex('morton'), soundex('moreton'), soundex('morten');
+---++---+
| soundex('morton') | soundex('moreton') | soundex('morten') |
+---++---+
| M635  | M635   | M635  |
+---++---+
1 row in set (0.00 sec)

mysql> select soundex('callaghan'), soundex('callahan'), soundex('calahan');
+--+-++
| soundex('callaghan') | soundex('callahan') | soundex('calahan') |
+--+-++
| C425 | C450| C450   |
+--+-++
1 row in set (0.00 sec)
mysql>


I hope this helps.

Richard


 >>  I want to use SOUNDEX for name queries, but I don't 
 >>  seem to be doing it correctly. 

 >>  As a test, I made a small table and popluated it 
 >>  with my name. My goal is to be able to find my name 
 >>  'Callaghan', with its most common misspelling 
 >>  'Callahan'. 

 >>  I've read the MySQL doc, so I know how to actually 
 >>  invoke soundex at the mysql prompt. I read an 
 >>  article that said soundex queries move more quickly 
 >>  if you add a column for the soundex.

 >>  Thus:

 >>  CREATE TABLE sound (
 >>  id tinyint(3) NOT NULL auto_increment,
 >>  name varchar(100) default NULL,
 >>  sound varchar(100) default NULL,
 >>  PRIMARY KEY  (id)
 >>  ) TYPE=MyISAM;

 >>  select soundex('Callaghan');
 >>  result = C425

 >>  INSERT INTO sound VALUES (1, 'Callaghan', 'C425');

 >>  Simple enough. Now, I expected all three of the 
 >>  following queries to return a value. 

 >>  select id from sound where sound = 
 >>  soundex('Callaghan');
 >>  returns 1 records

 >>  select id from sound where sound = 
 >>  soundex('Callahan');
 >>  returns 0 records

 >>  select id from sound where name = 
 >>  soundex('Callaghan');
 >>  returns 0 records

 >>  I'm missing something obvious here, but I've read 
 >>  the doc and I'm not sure what's wrong.

 >>  -- 
 >>  Get your free email from www.linuxmail.org 




 >>  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: newbie SOUNDEX question

[Rob]

I hate to be the one to break this to you, but
soundex('Callaghan') != soundex('Callahan')

It's just the sad truth: the algorithm thinks they are pronounced
differently, so it doesn't consider them to match.

soundex() is certainly better than nothing, but in many cases not much
better. I'm not sure whether even MetaPhone (a more robust soundex variant)
would get this case right, and all the mis-spelling systems I've ever
implemented have been suplemented with a set of heuristics (like searching
for all single-letter drops, adds, and transpositions) and often a
mis-spelling database in an attempt to find suggested alternatives.

-rob

On 7/6/02 at 11:39 pm, Dave Callaghan <[EMAIL PROTECTED]> wrote:

> I want to use SOUNDEX for name queries, but I don't 
> seem to be doing it correctly. 
> 
> As a test, I made a small table and popluated it 
> with my name. My goal is to be able to find my name 
> 'Callaghan', with its most common misspelling 
> 'Callahan'. 
> 
> I've read the MySQL doc, so I know how to actually 
> invoke soundex at the mysql prompt. I read an 
> article that said soundex queries move more quickly 
> if you add a column for the soundex.
> 
> Thus:
> 
> CREATE TABLE sound (
>   id tinyint(3) NOT NULL auto_increment,
>   name varchar(100) default NULL,
>   sound varchar(100) default NULL,
>   PRIMARY KEY  (id)
> ) TYPE=MyISAM;
> 
> select soundex('Callaghan');
> result = C425
> 
> INSERT INTO sound VALUES (1, 'Callaghan', 'C425');
> 
> Simple enough. Now, I expected all three of the 
> following queries to return a value. 
> 
> select id from sound where sound = 
> soundex('Callaghan');
> returns 1 records
> 
> select id from sound where sound = 
> soundex('Callahan');
> returns 0 records
> 
> select id from sound where name = 
> soundex('Callaghan');
> returns 0 records
> 
> I'm missing something obvious here, but I've read 
> the doc and I'm not sure what's wrong.
> 
> -- 
> Get your free email from www.linuxmail.org 
> 
> 
> 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

newbie SOUNDEX question

[Dave Callaghan]

I want to use SOUNDEX for name queries, but I don't 
seem to be doing it correctly. 

As a test, I made a small table and popluated it 
with my name. My goal is to be able to find my name 
'Callaghan', with its most common misspelling 
'Callahan'. 

I've read the MySQL doc, so I know how to actually 
invoke soundex at the mysql prompt. I read an 
article that said soundex queries move more quickly 
if you add a column for the soundex.

Thus:

CREATE TABLE sound (
  id tinyint(3) NOT NULL auto_increment,
  name varchar(100) default NULL,
  sound varchar(100) default NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

select soundex('Callaghan');
result = C425

INSERT INTO sound VALUES (1, 'Callaghan', 'C425');

Simple enough. Now, I expected all three of the 
following queries to return a value. 

select id from sound where sound = 
soundex('Callaghan');
returns 1 records

select id from sound where sound = 
soundex('Callahan');
returns 0 records

select id from sound where name = 
soundex('Callaghan');
returns 0 records

I'm missing something obvious here, but I've read 
the doc and I'm not sure what's wrong.

-- 
Get your free email from www.linuxmail.org 


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

Re: MySQL and DBI remote access

[Paul DuBois]

At 9:59 -0500 6/7/02, Shawn wrote:
>Geeze...  This message was sent (according to my sentbox) on 5/24/02 
>and just appeared on the list on 6/7/02 (I had given up on it).  I 
>don't know whats up with that...
>
>Next, what I asked for was a way to read the datasources,  NOT 
>connect to mysql.  I want to be able to see all the drivers(Oracle, 
>MSSQL, etc.) in addition to the MySQL, as well as all ther databases 
>(NOT a single one I connect to).  I just used that as an example (my 
>bad). Then I guess I could connect to them in the normal manner 
>(which I don't have an issue with since I CAN pass the username, 
>password, and host). So, the question still stands, is there a way 
>to read the data sources remotely?
>
>I am able to do it fine if I turn off all security, but as I 
>mentioned, there seems to be no way to pass the user name and 
>password, let alone the host.

Right.  data_sources() is essentially pretty useless unless anyone can
connect to your (local) server.  As for MySQL, you do need to connect to the
server in order to get a list of data sources (databases).  It won't just
hand out the list to anyone that asks for it.

>
>TIA,
>Shawn
>
>- Original Message -
>From: "Colin Faber" <[EMAIL PROTECTED]>
>To: "Shawn" <[EMAIL PROTECTED]>
>Cc: "MySql List" <[EMAIL PROTECTED]>
>Sent: Friday, June 07, 2002 4:14 AM
>Subject: Re: MySQL and DBI remote access
>
>
>>  Hi,
>>
>>  Right from the "connect" section in the DBD::mysql manual:
>>
>> $dsn =
>>  "DBI:mysql:database=$database;host=$hostname;port=$port";
>>
>> $dbh = DBI->connect($dsn, $user, $password);
>>
>>
>>
>>  Shawn wrote:
>>  >
>>  > Hello all,
>>  >   Does anyone know if there is a way to remotely call the 
>>data_source [DBI->data_source('mysql')] property to pull all 
>>available databases/drivers?
>>  >
>>  >   I can't seem to do it since you are not allowed to specify a 
>>host name, user name, or password with it.  The docs mention 
>>nothing about it using the call remotely, but I can't believe that 
>>I am the first to have an issue with it...  Any pointers would be 
>>greatly appreciated?
>>  >
>>  > TIA,
>>  > Shawn
>>  >
>>  > -
>>  > Before posting, please check:
>>  >http://www.mysql.com/manual.php   (the manual)
>>  >http://lists.mysql.com/   (the list archive)
>>  >
>>  > To request this thread, e-mail <[EMAIL PROTECTED]>
>>  > To unsubscribe, e-mail <[EMAIL PROTECTED]>
>>  > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>
>>  --
>>  Colin Faber
>>  (303) 736-5160
>>  fpsn.net, Inc.
>>
>>
>
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

newbie ? Load Data in MySQL

[Dion Wickander]

I am working on a project were the customer info will be uploaded to a MySQL
database from multiple sources with a customer ID already assigned to them
and this data needs to be related to other tables with related info. OK I
have an auto increment column set up as the primary key to keep track of the
customer records that are being created and using it to relate the data to
my other tables.

here is the question

is their a built in or automated way to load data in bulk to the customer
table and with each record added to that table create a record in the other
related tables with the correct primary key? or would this need to be a
record by record individual insert to create the corresponding records.

- my thinking is that I may have to load data in bulk, setting a flag for
the newly uploaded data. Then do a select that finds the flags and then
creating the new records in the related tables based on the found set. then
of course removing the flags after all other tables are updated.

I was just wondering if their is a built in mechanism for something like
this?

thanks in advance. 


MySQL 3.23.39 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Please help if you can...

[Rob]

Either I'm misunderstanding what you're trying to do or that Oracle query is
a really complicated way of doing something simple.

create table Customer ( name text, id int auto_increment primary key );
create table Skill ( description text, id int auto_increment primary key );
create table CustomerSkills ( customer int NOT NULL, skill int NOT NULL );

insert into Customer (name) values
('Steve'),('Harry'),('Rachel'),('Iggy'),('Harold'),('Saori');

select * from Customer;

+++
| name   | id |
+++
| Steve  |  1 |
| Harry  |  2 |
| Rachel |  3 |
| Iggy   |  4 |
| Harold |  5 |
| Saori  |  6 |
+++
6 rows in set (0.00 sec)

insert into Skill (description) values
('Typing'),('Dancing'),('SQL'),('Modelling'),('Judo'),('Acrobatics');

select * from Skill;

+-++
| description | id |
+-++
| Typing  |  1 |
| Dancing |  2 |
| SQL |  3 |
| Modelling   |  4 |
| Judo|  5 |
| Acrobatics  |  6 |
+-++
6 rows in set (0.00 sec)

insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Steve' and Skill.description in ('Typing','SQL');

insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Harry' and Skill.description in
('Typing','SQL','Judo');

insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Rachel' and Skill.description in ('Dancing');

insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Iggy' and Skill.description in ('Dancing','Judo');

insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Harold' and Skill.description in ('SQL','Modelling');

insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Saori' and Skill.description in
('Typing','Dancing','SQL','Modelling','Judo','Acrobatics');



select Customer.name, Skill.description from Customer,Skill,CustomerSkills
where Customer.id = CustomerSkills.customer
and Skill.id = CustomerSkills.skill;

++-+
| name   | description |
++-+
| Steve  | Typing  |
| Steve  | SQL |
| Harry  | Typing  |
| Harry  | SQL |
| Harry  | Judo|
| Rachel | Dancing |
| Iggy   | Dancing |
| Iggy   | Judo|
| Harold | SQL |
| Harold | Modelling   |
| Saori  | Typing  |
| Saori  | Dancing |
| Saori  | SQL |
| Saori  | Modelling   |
| Saori  | Judo|
| Saori  | Acrobatics  |
++-+
16 rows in set (0.00 sec)

select Customer.name, Skill.description from Customer,Skill,CustomerSkills
where Customer.id = CustomerSkills.customer
and Skill.id = CustomerSkills.skill 
and Skill.description in ('SQL');

++-+
| name   | description |
++-+
| Steve  | SQL |
| Harry  | SQL |
| Harold | SQL |
| Saori  | SQL |
++-+
4 rows in set (0.01 sec)

select Customer.name, Skill.description from Customer,Skill,CustomerSkills
where Customer.id = CustomerSkills.customer
and Skill.id = CustomerSkills.skill
and Skill.description in ('SQL','Dancing');

++-+
| name   | description |
++-+
| Steve  | SQL |
| Harry  | SQL |
| Rachel | Dancing |
| Iggy   | Dancing |
| Harold | SQL |
| Saori  | Dancing |
| Saori  | SQL |
++-+
7 rows in set (0.01 sec)

Of course, you'll probably want to use Skill.id instead of skill.description
for your WHERE...

Were you trying to do something more complex than this?

-rob

On 7/6/02 at 2:52 pm, Hekuran Vokshi <[EMAIL PROTECTED]> wrote:

> Hi everybody,
> 
> I'm new to MySQL and have got the following code that works with Oracle
> SQL... After looking at the documentation I realised that DECODE seems to
be
> reserved for encryption... any idea of how this would work with MySQL?
Here
> is the code:
> 
> The tables are as follows
> tabletable
> table
> CUSTOMER   CUSTOMER_SKILLS SKILL
> CUSTOMER_ID (p)   CUSTOMER_ID(f)   SKILL_ID(p)
> CUSTOMER_NAME SKILL_ID  (f)   SKILL_DESC
> 
> (p) Primary Key
> (f) Foreign Key
> 
> The idea is to select the customer(s) that have 'n' skill(s) and return
the
> customer name and skill(s) description. The skills are know from the user
> input.
> 
> SELECT
> DECODE (A.SKILLS_ID, B.SKILLS_ID, D.CUSTOMER_NAME, NULL) "Customer
> Name", A.SKILL_DESC "Skill Title"
>  FROM   CUSTOMER_DETAILS D, CUSTOMER_SKILLS C, SKILLS B, SKILLS A
> 
>  WHERE C.SKILLS_ID  = A.SKILLS_ID
>  AND D.CUSTOMER_ID  = C.CUSTOMER_ID
>  AND A.SKILLS_ID IN (1,3,5)
> 
>  ANDB.SKILLS_ID= (SELECT MIN(A.

Re: MySQL and DBI remote access

[Shawn]

Geeze...  This message was sent (according to my sentbox) on 5/24/02 and just appeared 
on the list on 6/7/02 (I had given up on it).  I don't know whats up with that...

Next, what I asked for was a way to read the datasources,  NOT connect to mysql.  I 
want to be able to see all the drivers(Oracle, MSSQL, etc.) in addition to the MySQL, 
as well as all ther databases (NOT a single one I connect to).  I just used that as an 
example (my bad). Then I guess I could connect to them in the normal manner (which I 
don't have an issue with since I CAN pass the username, password, and host). So, the 
question still stands, is there a way to read the data sources remotely?

I am able to do it fine if I turn off all security, but as I mentioned, there seems to 
be no way to pass the user name and password, let alone the host.

TIA,
Shawn

- Original Message - 
From: "Colin Faber" <[EMAIL PROTECTED]>
To: "Shawn" <[EMAIL PROTECTED]>
Cc: "MySql List" <[EMAIL PROTECTED]>
Sent: Friday, June 07, 2002 4:14 AM
Subject: Re: MySQL and DBI remote access


> Hi,
> 
> Right from the "connect" section in the DBD::mysql manual:
> 
>$dsn =
> "DBI:mysql:database=$database;host=$hostname;port=$port";
> 
>$dbh = DBI->connect($dsn, $user, $password);
> 
> 
> 
> Shawn wrote:
> > 
> > Hello all,
> >   Does anyone know if there is a way to remotely call the data_source 
>[DBI->data_source('mysql')] property to pull all available databases/drivers?
> > 
> >   I can't seem to do it since you are not allowed to specify a host name, user 
>name, or password with it.  The docs mention nothing about it using the call 
>remotely, but I can't believe that I am the first to have an issue with it...  Any 
>pointers would be greatly appreciated?
> > 
> > TIA,
> > Shawn
> > 
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> > 
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> -- 
> Colin Faber
> (303) 736-5160
> fpsn.net, Inc.
> 
> 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: If a table in a query is empty?

[Jay Blanchard]

[snip]
This happens because a 'normal' join is like a multiplicative product
between all the rows matched in all the tables:
result_rows = a_rows * b_rows * c_rows * d_rows
so if any of the factors are zero, the result is zero.
Your WHERE clause will cut down on the number of rows you get back, and you
expect that the optimizer will prevent this obscene number of rows from ever
really being generated, but this is how it logically works.

If you want different behavior, you can use a LEFT JOIN, which effectively
rounds all the factors up to one by adding a row to each table with all NULL
fields.
[/snip]

Thanks RobI should've stayed in bed this morning! Some days I feel like
such a Homer Simpson, "d'oh!"! I completely forgot blanked on doing a LOJ.
OTOH this is just proof of why developers should not work in a vacuum as we
all suffer from brain paralysis from time-to-time.

Have a great weekend!

Jay
mysql, sql, query

"It's hip to snip!"






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: If a table in a query is empty?

[Rob]

This happens because a 'normal' join is like a multiplicative product
between all the rows matched in all the tables:
result_rows = a_rows * b_rows * c_rows * d_rows
so if any of the factors are zero, the result is zero.
Your WHERE clause will cut down on the number of rows you get back, and you
expect that the optimizer will prevent this obscene number of rows from ever
really being generated, but this is how it logically works.

If you want different behavior, you can use a LEFT JOIN, which effectively
rounds all the factors up to one by adding a row to each table with all NULL
fields.

On 7/6/02 at 8:47 am, Jay Blanchard <[EMAIL PROTECTED]>
wrote:

> I located this behavior this morning and was wondering how to prevent it;
> 
> I have 3 (or more tables) where I perform a query like this;
> 
> SELECT a.btn, b.dateFlag, c.dateFlag, d.dateFlag
> FROM tblA a, tblB b, tblC c, tblD d
> WHERE a.btn = b.btn
> 
> tblA and tblB have information in them and btn is a key column in all
> tables. tblC and tblD are currently empty. When I perform the query I get
> "Empty set (0.00 sec)". If I remove c.dateFlag and d.dateFlag like this;
> 
> SELECT a.btn, b.dateFlag
> FROM tblA a, tblB b
> WHERE a.btn = b.btn
> 
> I do get results back. Any mention of a table that is empty returns
nothing.
> I don't ever remember running into this before, is this standard?
> 
> Jay
> mysql, sql, query
> 
> "It's hip to snip!"
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> 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

Please help if you can...

[Hekuran Vokshi]

Hi everybody,

I'm new to MySQL and have got the following code that works with Oracle
SQL... After looking at the documentation I realised that DECODE seems to be
reserved for encryption... any idea of how this would work with MySQL? Here
is the code:

The tables are as follows
tabletable
table
CUSTOMER   CUSTOMER_SKILLS SKILL
CUSTOMER_ID (p)   CUSTOMER_ID(f)   SKILL_ID(p)
CUSTOMER_NAME SKILL_ID  (f)   SKILL_DESC

(p) Primary Key
(f) Foreign Key

The idea is to select the customer(s) that have 'n' skill(s) and return the
customer name and skill(s) description. The skills are know from the user
input.

SELECT
DECODE (A.SKILLS_ID, B.SKILLS_ID, D.CUSTOMER_NAME, NULL) "Customer
Name", A.SKILL_DESC "Skill Title"
 FROM   CUSTOMER_DETAILS D, CUSTOMER_SKILLS C, SKILLS B, SKILLS A

 WHERE C.SKILLS_ID  = A.SKILLS_ID
 AND D.CUSTOMER_ID  = C.CUSTOMER_ID
 AND A.SKILLS_ID IN (1,3,5)

 ANDB.SKILLS_ID= (SELECT MIN(A.SKILLS_ID)
 FROM   CUSTOMER_SKILLS A
 WHERE  A.CUSTOMER_ID =
D.CUSTOMER_ID
 AND A.SKILLS_ID IN  (1,3,5) )
 ORDER BY D.CUSTOMER_NAME, 2;

> Thanks in advance
> Heki
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> 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

If a table in a query is empty?

[Jay Blanchard]

I located this behavior this morning and was wondering how to prevent it;

I have 3 (or more tables) where I perform a query like this;

SELECT a.btn, b.dateFlag, c.dateFlag, d.dateFlag
FROM tblA a, tblB b, tblC c, tblD d
WHERE a.btn = b.btn

tblA and tblB have information in them and btn is a key column in all
tables. tblC and tblD are currently empty. When I perform the query I get
"Empty set (0.00 sec)". If I remove c.dateFlag and d.dateFlag like this;

SELECT a.btn, b.dateFlag
FROM tblA a, tblB b
WHERE a.btn = b.btn

I do get results back. Any mention of a table that is empty returns nothing.
I don't ever remember running into this before, is this standard?

Jay
mysql, sql, query

"It's hip to snip!"



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: Stupid Q

[Gerald Clark]

Are you shure you don't really want ENUM('y','n')

SET('y','n') will allow the field to be both 'y' and 'n' at the same time.

Chris Knipe wrote:

>I'm talking under correction here, but
>
>SET('y'.'n') default 'n' NOT NULL - should work.  I normally just do all
>this stuff via phpmyadmin, it works really great...
>
>--
>me
>
>
>- Original Message -
>From: "Jason Soza" <[EMAIL PROTECTED]>
>To: "MySQL Mailing List" <[EMAIL PROTECTED]>
>Sent: Friday, June 07, 2002 5:06 AM
>Subject: Stupid Q
>
>
>>Hoping someone can help me here:
>>
>>I've made a column using SET("y","n") NOT NULL - I want the default value
>>
>in
>
>>that column to be "n", so if no data is entered for that column, it'd be
>>
>"n"
>
>>automatically.
>>
>>How do I do that? Not sure what to search for in the manual.
>>
>>Jason Soza
>>
>>filter fodder: 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

Table design (innodb) question

[Sander Pilon]

When you have a table with both numeric and variable-length text data,
and you need to update the numeric part a lot, it made sense in MyISAM
to split the numeric from the textpart. (Because working on fixed-length
tables is so much faster.) Say...

Original:

Table 1:  id1 int, id2 int, articletext text

MyISAM optimized:

Table 1:  id1, id2
Table 2:  id1, articletext

(primary key on id1)

Does the same go for InnoDB tables? Or would the original table be just
as fast? (Or perhaps faster)

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: Release Schedule

[Heikki Tuuri]

Frederick,

4.0.1 is beta quality, or even stable, if you do not use the multi-table
delete or replication.

For 3.23.51 my guess is it will be is released June 14, 2002. For 4.0.2 my
guess is July 15, 2002.

For 3.23.52 I guess July 7, 2002. InnoDB-3.23.52 is already completed. When
the build master gets .51 out he immediately has to start building .52.

Let us see how these predictions fare :).

Regards,

Heikki
Innobase Oy

- Original Message -
From: "Frederick L. Steinkopf" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, June 07, 2002 2:31 PM
Subject: Re: Release Schedule


> A release schedule would be a big help.  Can anyone provide a date as to
> when 4.xx will go beta?
> Fred Steinkopf
>
>
> > > > Can someone *please* pick up the dropped ball on the
> > > > release schedule and run with it ? It's not such
> > > > a big deal for ISAM table users but it's a very big
> > > > deal for InnoDB tables...
> > > >
> > >
> > > MySQL AB: The least you could do is say: OK, we have this X problem
and
> > > still working out Y... but we anticipate being finished next week.
Then
> > > we'll spend about a week testing the build, and it will be live.
> > >
> > > Instead we get Monty talking about posting 3.23.50 in a week about 2
> > months
> > > ago.  Then nothing... dead caller at the other end!
>
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Help - foreign keys in MySQL-Max-4.0.1

[victoria papazoi]

Hi,
I use MySQL-Max-4.0.1 and i am wantering if
the information about the foreign keys are stored at
comment properties of table with maximum length 60 characters?
I need to use innodb tables with many f.keys.What I have to do?

Thank you.





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: mysqlshow incompatibility (3.23.39 --> 4.0.1-alpha) ?

[Thomas Spahni]

Gang,

I'm answering my own question here because I was able to solve (part of)
the problem in the meantime and want to give you some feedback. Perhaps
there is a better or more general solution for this and any further
comments from the listmembers are still appreciated.

Problem:

Older (pre 4.0.0) clients report an error when accessing a new server
running with charset latin1_de enabled.

Analysis:

latin1_de was introduced with version 4.0.0. However, latin1_de is not a
"real charset" but rather a special sort order. This should not affect the
client in most (all?) cases.

Fix:

(This is applied to fix the client host running 3.23.39)

1) added to the [client] section of '.my.cnf' on client host:
   character-sets-dir= /usr/share/mysql/charsets

2) added one line to /usr/share/mysql/charsets/Index on client host:
   latin1_de 31

3) created a "fake" configuration file latin1_de.conf :
   cd /usr/share/mysql/charsets
   ln -s latin1.conf latin1_de.conf

Works like a charm but I consider this a hack. It would mean to hack each
and every client machine to make it work. Patching the server daemon would
be easier.

Developers:

Is there a more general way to make old clients happy with new servers
running charset latin1_de ??? I think of patching the server to make
a fake report to the client pretending latin1 is in use.

Question:

How does latin1_de affect FULLTEXT matching? No clues to this in
the very fine manual (I looked at:)
http://www.mysql.com/documentation/mysql/bychapter/\
manual_MySQL_Database_Administration.html#German_character_set


Thomas Spahni


On Fri, 7 Jun 2002, Thomas Spahni wrote:
> ok folks,
> 
> I was digging into my own problem and that's what I found:
> 
> host 'hegel' running 3.23.39 is using character_set latin1
> host 'fidel' running 4.0.1   is using character_set latin1_de
> 
> latin1_de is character set '#31' mentioned in the error message below.
> Character set latin_de was introduced with release 4.0.0. From the very
> fine manual:
> 
> Changes in release 4.0.0
> * New character set `latin_de' which provides correct German sorting.
> 
> Does this mean that clients before ver 4.0.0 can't access new servers with
> correct German sorting???
> 
> Thomas
> 
> 
> On Thu, 6 Jun 2002, Thomas Spahni wrote:
> 
> > Dear listmembers,
> > 
> > I've got 2 hosts running Linux: hostnames are 'hegel' and 'fidel'
> > 
> > me@hegel:~>mysqladmin version
> > mysqladmin  Ver 8.21 Distrib 3.23.39, for pc-linux-gnu on i586
> > 
> > me@fidel:~>mysqladmin version
> > mysqladmin  Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686
> > 
> > When logged in to 'hegel' I can't mysqlshow the databases on 'fidel'. It
> > works, but gives an error.
> > 
> > me@fidel:~> mysqlshow -u me -h fidel
> > +---+
> > | Databases |
> > +---+
> > | bge   |
> > | mysql |
> > | something |
> > | test  |
> > +---+
> > 
> > me@hegel:~> mysqlshow -u me -h fidel
> > mysqlshow: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
> > mysqlshow: Character set '#31' is not a compiled character set and is not
> > specified in the '/usr/share/mysql/charsets/Index' file
> > +---+
> > | Databases |
> > +---+
> > | bge   |
> > | mysql |
> > | something |
> > | test  |
> > +---+
> > 
> > However, it runs fine the other way round (4.0.1 client, 3.23.39 server):
> > 
> > me@fidel:~> mysqlshow -u me -h hegel
> > +--+
> > |  Databases   |
> > +--+
> > | casino   |
> > | mitteilungen |
> > | mysql|
> > | ssl  |
> > | test |
> > +--+
> > 
> > What happens? Any insights what to look for?
> > Thomas Spahni


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Release Schedule

[Frederick L. Steinkopf]

A release schedule would be a big help.  Can anyone provide a date as to
when 4.xx will go beta?
Fred Steinkopf


> > > Can someone *please* pick up the dropped ball on the
> > > release schedule and run with it ? It's not such
> > > a big deal for ISAM table users but it's a very big
> > > deal for InnoDB tables...
> > >
> >
> > MySQL AB: The least you could do is say: OK, we have this X problem and
> > still working out Y... but we anticipate being finished next week.  Then
> > we'll spend about a week testing the build, and it will be live.
> >
> > Instead we get Monty talking about posting 3.23.50 in a week about 2
> months
> > ago.  Then nothing... dead caller at the other end!




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: MyODBC DSN Configuration Dialog Hidden

[mySQL list]

Hi Venu,

> >
> > The problem I have only occurs (as far as I can tell) on
> > Windows XP, not
> > Windows 2000: If I try and connect to a host where mysql is
> > not running, the
> > driver pops up an  error message box,
> > saying [MySQL][ODBC 3.51 Driver] Can't connect to MySQL
> > server on 'hostname'
> > (10061), then after clicking ok, the DSN Configuration Dialog
> > is opened, but
> > hidden behind the application window. In contrast, on Windows
> > 2000, no error
> > message box or dsn dialog appears at all (which I prefer).
>
>  You can disable the re-connect prompt in ado by using
>  DriverNoPrompt option from your conn.open method.

I can't find any option to use "DriverNoPrompt" in ADO, only DAO, unless you
mean in the OPTION= part of the connection string. Originally I had
OPTION=35, I've tried OPTION=51 (adding 16 which should disable any
prompts), but its just the same.

>
>  You can also disable the prompt during the failed connection
>  from the DSN using the option 'Don't prompt on connect'.
>
>  And as far as hidden dialog, am not sure how does that come
>  in to picture as we use XP and 2000 for all internal
>  testing. Can you cross check whether the dialog is really
>  active by using 'alt-tab' ?

Yes it is there - alt-tab brings it to the front (and the "Don't prompt on
connect" checkbox is set when I use OPTION=51).

Regards, Ian

>
> Regards, Venu
> --
> For technical support contracts, go to https://order.mysql.com
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /   Mr. Venu <[EMAIL PROTECTED]>
>  / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Developer
> /_/  /_/\_, /___/\___\_\___/  California, USA
><___/  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

Re: strange or impossible query?

[Rob]

I'm afraid I'm not quite clear on what you're trying to do; do you want one
result per row, with one NULL column and one non-NULL column, or do you want
one row per distinct 'machine', with both a start and a stop date?

In the first case, this can be implemented with a simple IF():
IF(field_function='TT',field_date,NULL)
which will make the field NULL if field_function is not set to the correct
value.
If you want two separate fields (both date and time), then use two IFs with
the same test.

If you want one row for each machine with both start and end dates, there
are several ways to do it. Which you choose depends largely on your
integrity contraints; are you sure that you've got exactly one TT and one BT
for each machine, or do you have 'pairs', or do you want every comination?
The simplest hack is just to use the above, but wrap every query in a max()
function, and group by the field_machine:

select
max(field_machine) as fieldMachine,
max(if(field_function='TT',field_date,NULL)) as startDate,
max(if(field_function='TT',field_time,NULL)) as startTime,
max(if(field_function='BT',field_date,NULL)) as stopDate,
max(if(field_function='BT',field_time,NULL)) as stopTime
from theTable
group by fieldMachine

I consider this ugly, but it's simple to do.
If you're really trying to relate each machine to its start and end times,
then I would write the query that way: get a table of distinct machines (if
it exists already, great; if not,
create temporary table theMachines select distinct field_machine from
theTable;
) and then join that table against your start times and against your end
times:

select theMachines.field_machine,
theStart.field_date, theStart.field_time,
theEnd.field_date, theStart.field_time
from theMachines, theTable as theStart, theTable as theEnd
where theMachines.field_machine = theStart.field_machine
and theStart.field_function = 'TT'
and theMachines.field_machine = theEnd.field_machine
and theEnd.field_function = 'BT'

(Just banging this out quickly, but you get the idea.)

-rob

On 7/6/02 at 12:59 pm, Fabrizio Tivano <[EMAIL PROTECTED]> wrote:

> 
> 
> Hello dear all, 
> 
> 
> i have a table like this:
> 
> field_machine, field_date, field_time, field_function.
> 
> field_function can be =TT or +BT
> 
> well i need to perform a select query, witch display on one line
> 
> field_machine, 
> (field_date, field_time where field_function =TT) AS start, 
> (field_date, fiel_time where field_function =BT) AS stop from table;
> 
> 
> 
> 
> is possible to do that?
> and if yes, how?
> 
> thanks in advance and Regards,
> 
> fabrizio
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> 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: Normilazation and Optimization

[Jay Blanchard]

[snip]
So I figured I'd go to the list and ask the question. What are peoples
opinion's on the best book(s) covering the following topics?

1. Creation
2. Normilazation
3. Optimization
4. Managing
5. Modeling
[/snip]

A great place to start is Database Design For Mere Mortals (watch wrapped
URL)
http://shop.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=15GVH4F0T4&;
mscssid=LAFPJJM0TMRG9LSSHH92K79NF8XT268F&isbn=0201694719

HTH!

Jay
mysql, sql, query

"It's hip to snip!"






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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 or impossible query?

[Fabrizio Tivano]

Hello dear all, 


i have a table like this:

field_machine, field_date, field_time, field_function.

field_function can be =TT or +BT

well i need to perform a select query, witch display on one line

field_machine, 
(field_date, field_time where field_function =TT) AS start, 
(field_date, fiel_time where field_function =BT) AS stop from table;




is possible to do that?
...and if yes, how?

thanks in advance and Regards,

fabrizio

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysqld --flush

[ritu singla]

Hello,
(i had already posted this ques, got a reply but that
character format was unreadable..so i'm sorry for
posting it again..)


Starting mysqld with --flush, flushes tables to
database or disk log??


Pls. help!!
Thanx in advance,
Ritu Singla





Everything you always wanted to know about cars and bikes,now
 at: http://in.autos.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: Normilazation and Optimization

[Rob]

I still feel the best starting point for data modelling is the original
Flemming/von Halle book, _Handbook of Relational Database Design_. It
doesn't address any individual RDBMS implementation, which makes its lessons
general, but it carefully walks you through all the fundamentals of
modelling, all the different normal forms, etc. Iin a very simple and
systematic way. If you want a basic grounding, this is the place to go.

A really spiffy way to buy this book is via this link:


The DuBois book (http://www.amazon.com/exec/obidos/ASIN/0735709211/vcx01-20)
offers a few excellent tutorials, but I don't think it's really intended as
a data modeling guide; I always recommend combining a good book on modeling
with the best reference you can find on the implementation technology, and
the Flemming/DuBois combination implements this approach quite well.

-rob

PS: Shameless Amazon Associates linking above...but these are still the only
two books I chose to recommend.

On 7/6/02 at 12:12 am, C Mead <[EMAIL PROTECTED]> wrote:

> Hey all,
> 
> I've been searching for references on the web and even been to stores ;) 
> regarding the above subject. But I am reluctant to purchase ATM because
there 
> are so many books out there on the subject. 
> 
> So I figured I'd go to the list and ask the question. What are peoples 
> opinion's on the best book(s) covering the following topics?
> 
> 1. Creation
> 2. Normilazation 
> 3. Optimization
> 4. Managing
> 5. Modeling
> 
> 
> Paul DuBois, MySQL book was amazing one of the best refernces I've ever
gone 
> through. Now 
> I'm finding as the databases I'm building are becoming more complex,
careful 
> and concise palnning, before implementation, is needed. 
> 
> TIA,
> 
> CM 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> 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: Attn: MySQL AB: we need 3.23.5x NOW !

[Heikki Tuuri]

Mark,

3.23.51 is built and works on most major platforms, including Linux,
Solaris, Windows, and FreeBSD.

But the mysqld.sym file was forgotten from the Linux distribution. The build
master is working on that right now.

I think the release will be handed over to the web team after that, and then
it is posted to www.mysql.com. Since we have a new build procedure, I think
we must consider .51 a prerelease for a week after its posting.

Regards,

Heikki
Innobase Oy

- Original Message -
From: ""Mark Hazen"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Friday, June 07, 2002 1:06 PM
Subject: RE: Attn: MySQL AB: we need 3.23.5x NOW !


>
> > Can someone *please* pick up the dropped ball on the
> > release schedule and run with it ? It's not such
> > a big deal for ISAM table users but it's a very big
> > deal for InnoDB tables...
> >
>
> MySQL AB: The least you could do is say: OK, we have this X problem and
> still working out Y... but we anticipate being finished next week.  Then
> we'll spend about a week testing the build, and it will be live.
>
> Instead we get Monty talking about posting 3.23.50 in a week about 2
months
> ago.  Then nothing... dead caller at the other end!
>
> Please help!!! Anyone?!
> 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
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Error in shared libraries

[Ritu Singla]

Hi,
 try including this path i.e. /usr/lib/mysql/ in LD_LIBRARY_PATH in .cshrc
file...i guess, it'll do...

Ritu

On Fri, 7 Jun 2002, Charitha wrote:

> 
> 
> Hello all,
> 
> I am having one application in which i am using mysql as database.
> When i run my application it gives following error.
> 
> "error:loading shared libraries libmysqlclient.so.10 (No such file or
> directory)"
> 
> I am having that library in /usr/lib/mysql/
> Still it gives this error.Why is it so..?
> How to solve this problem?
> 
> 
> Thanks and regards,
> Charitha C.
> 
> 
> 
> 
> -- 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> 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

Aggregate functions

[Kris Simonis]

Hi,

Sorry to bother the Guru's here, but I've recently started working on
getting a mySQL database up and running and I noticed that some
aggregate functions like MAX and MIN don't have 'DISTINCT' as an
optional arguement. Are these not supported or is this just missing
from the documentation?

Kris Simonis
OGD Software

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: Attn: MySQL AB: we need 3.23.5x NOW !

[Mark Hazen]

> Can someone *please* pick up the dropped ball on the
> release schedule and run with it ? It's not such
> a big deal for ISAM table users but it's a very big
> deal for InnoDB tables...
>

MySQL AB: The least you could do is say: OK, we have this X problem and
still working out Y... but we anticipate being finished next week.  Then
we'll spend about a week testing the build, and it will be live.

Instead we get Monty talking about posting 3.23.50 in a week about 2 months
ago.  Then nothing... dead caller at the other end!

Please help!!! Anyone?!
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

RE: MyODBC DSN Configuration Dialog Hidden

[Venu]

Hi , 

> -Original Message-
> From: mySQL list [mailto:[EMAIL PROTECTED]] 
> Sent: Friday, June 07, 2002 2:35 AM
> To: [EMAIL PROTECTED]
> Subject: MyODBC DSN Configuration Dialog Hidden
> 
> 
> Hi,
> 
> I have a vb application which connects to mysql via 
> ado/myodbc 3.51.03. In
> it, users can choose to connect to databases on different machines. I
> construct a connection string from the hostname, database 
> etc. So far so
> good.
> 
> The problem I have only occurs (as far as I can tell) on 
> Windows XP, not
> Windows 2000: If I try and connect to a host where mysql is 
> not running, the
> driver pops up an  error message box,
> saying [MySQL][ODBC 3.51 Driver] Can't connect to MySQL 
> server on 'hostname'
> (10061), then after clicking ok, the DSN Configuration Dialog 
> is opened, but
> hidden behind the application window. In contrast, on Windows 
> 2000, no error
> message box or dsn dialog appears at all (which I prefer).

 You can disable the re-connect prompt in ado by using 
 DriverNoPrompt option from your conn.open method.

 You can also disable the prompt during the failed connection
 from the DSN using the option 'Don't prompt on connect'.

 And as far as hidden dialog, am not sure how does that come 
 in to picture as we use XP and 2000 for all internal 
 testing. Can you cross check whether the dialog is really 
 active by using 'alt-tab' ? 

Regards, Venu
--
For technical support contracts, go to https://order.mysql.com
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Mr. Venu <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Developer
/_/  /_/\_, /___/\___\_\___/  California, USA
   <___/  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

Fw: DECODE

[Hekuran Vokshi]

Hi everybody,

I'm new to MySQL and have got the following code that works with Oracle
SQL... After looking at the documentation I realised that DECODE seems to be
reserved for encryption... any idea of how this would work with MySQL? Here
is the code:

The tables are as follows
___   ___ _
|CUSTOMER   |   |CUSTOMER_SKILLS  |   |SKILL
|
-   ----

|CUSTOMER_ID | (p) |CUSTOMER_ID   |(f)|SKILL_ID
|(p)
|CUSTOMER_NAME |   |SKILL_ID |(f)
|SKILL_DESC  |
|:   |   
|:|
|:_ |
|__:__|

(p) Primary Key
(f) Foreign Key

The idea is to select the customer(s) that have 'n' skill(s) and display the
skills description. The skills are know from the user input.

SELECT
DECODE (A.SKILLS_ID, B.SKILLS_ID, D.CUSTOMER_NAME, NULL) "Customer
Name", A.SKILL_DESC "Skill Title"
 FROM   CUSTOMER_DETAILS D, CUSTOMER_SKILLS C, SKILLS B, SKILLS A

 WHERE C.SKILLS_ID  = A.SKILLS_ID
 AND D.CUSTOMER_ID  = C.CUSTOMER_ID
 AND A.SKILLS_ID IN (1,3,5)

 ANDB.SKILLS_ID= (SELECT MIN(A.SKILLS_ID)
 FROM   CUSTOMER_SKILLS A
 WHERE  A.CUSTOMER_ID =
D.CUSTOMER_ID
 AND A.SKILLS_ID IN  (1,3,5) )
 ORDER BY D.CUSTOMER_NAME, 2;

Thanks in advance
Heki



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Maximum JOINED tables

[Roger Baklund]

* Benjamin Pflugmann
> On Thu 2002-06-06 at 19:17:10 +0200, [EMAIL PROTECTED] wrote:
> [...]
>
> > But... is this the only feature of mysql which is dependant on the
> > processor architecture?
>
> See it the other way: You are not garantueed more than 31 tables in a
> join. If you happen to have a 64Bit architecture, you can use more.

Yes... a positive attitude makes life easier. :)

However, I think my question is valid... I don't think it is a good idea to
limit the number of joins based on the processor architecture, and this
makes me wonder what other 'strange' limitations I might find in mysql...
some questions to Victoria/MySQL AB:

mysql is considered to be platform independant, and I suppose MySQL AB is
trying to keep it that way...?

What (other) features of mysql are dependant on the processor architecture?

Will the max number of joined tables (on 32-bit platform) stay at 31 in
version 4.x, or is it planned to increase this limit?

Is this limitation "by design", and if so, why? (I suspect it is by
accident, and that's ok. That would mean it will probably be fixed in the
future.)

> > I'm kind of nervous about this, because I'm currently working on a 'very
> > normalized' database schema, and it is possible I will need more than 31
> > tables in a single select... including multiple joins to the
> > same tables, of
> > course. It would be very bad if my design turned out to be 64-bit
> > dependant...
>
> AFAIK, if you need so many tables, you get a major problem: MySQL
> figures out the best join order by trying all combinations. This
> becomes increasingly slow as you are going to join more tables. In
> other words: Trying to use 30 tables in one select probably will be
> too slow as that it would matter whether so many tables are allowed in
> a join or not.

hm...

The join order optimization is done before the query is executed, so I guess
you are not talking about the _query_ beeing to slow, but the _preparation_
of the query... right?

How slow are we talking here...? Normally the join order is decided within
0.001 sec... (I'm only guessing) When you say slow, do you mean 0.01, or 0.1
or 1.0 sec or what?

In our current implementation of this system, we have about 20 tables in the
main select statement, and we have never seen this problem. Will it be a
problem with 24 tables? 28 tables? You probably don't know the answer to
these questions, but maybe you can tell me more about why you suspect 30
tables would be too slow...?

> Of course, you could work around it by using STRAIGHT_JOIN, but I
> assume it is to cumbersome to figure out the optimal join order for
> all SELECTs.

I was kind of hoping mysql would do that for me... but I guess I could use a
combination, using STRAIGHT_JOIN for some of the joins.

I can split my query in two (or more), but I hope I don't have to, because I
suspect this will slow down the performance, and I need to "always" have a
result within 0.5 sec...

--
Roger
sql


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MyODBC DSN Configuration Dialog Hidden

[mySQL list]

Hi,

I have a vb application which connects to mysql via ado/myodbc 3.51.03. In
it, users can choose to connect to databases on different machines. I
construct a connection string from the hostname, database etc. So far so
good.

The problem I have only occurs (as far as I can tell) on Windows XP, not
Windows 2000: If I try and connect to a host where mysql is not running, the
driver (or at least I assume its the driver), pops up an error message box,
saying [MySQL][ODBC 3.51 Driver] Can't connect to MySQL server on 'hostname'
(10061), then after clicking ok, the DSN Configuration Dialog is opened, but
hidden behind the application window. In contrast, on Windows 2000, no error
message box or dsn dialog appears at all (which I prefer).

I can't find any difference between the two machines except that the 2000
machine also has myodbc 2.5 installed, but not used.

ANY help appreciated!

ian


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL and DBI remote access

[Colin Faber]

Hi,

Right from the "connect" section in the DBD::mysql manual:

   $dsn =
"DBI:mysql:database=$database;host=$hostname;port=$port";

   $dbh = DBI->connect($dsn, $user, $password);



Shawn wrote:
> 
> Hello all,
>   Does anyone know if there is a way to remotely call the data_source 
>[DBI->data_source('mysql')] property to pull all available databases/drivers?
> 
>   I can't seem to do it since you are not allowed to specify a host name, user name, 
>or password with it.  The docs mention nothing about it using the call remotely, but 
>I can't believe that I am the first to have an issue with it...  Any pointers would 
>be greatly appreciated?
> 
> TIA,
> Shawn
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-- 
Colin Faber
(303) 736-5160
fpsn.net, Inc.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

FW: foreign keys in MySQL-Max-4.0.1

[victoria papazoi]

-Original Message-
From: victoria papazoi [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 07, 2002 11:58 AM
To: [EMAIL PROTECTED]
Subject: foreign keys in MySQL-Max-4.0.1 



Hi,
I use MySQL-Max-4.0.1 and i am wantering if
the information about the foreign keys are stored at
comment properties of table with maximum length 60 characters?
I need to use innodb tables with many f.keys.What I have to do?

Thank you.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL and DBI remote access

[Shawn]

Hello all,
  Does anyone know if there is a way to remotely call the data_source 
[DBI->data_source('mysql')] property to pull all available databases/drivers?

  I can't seem to do it since you are not allowed to specify a host name, user name, 
or password with it.  The docs mention nothing about it using the call remotely, but I 
can't believe that I am the first to have an issue with it...  Any pointers would be 
greatly appreciated?

TIA,
Shawn





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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 MySQL-Max-4.0.1

[victoria papazoi]

Hi,
I use MySQL-Max-4.0.1 and i am wantering if
the information about the foreign keys are stored at
comment properties of table with maximum length 60 characters?
I need to use innodb tables with many f.keys.What I have to do?

Thank you.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

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: mysqlshow incompatibility (3.23.39 --> 4.0.1-alpha) ?

[Thomas Spahni]

ok folks,

I was digging into my own problem and that's what I found:

host 'hegel' running 3.23.39 is using character_set latin1
host 'fidel' running 4.0.1   is using character_set latin1_de

latin1_de is character set '#31' mentioned in the error message below.
Character set latin_de was introduced with release 4.0.0. From the very
fine manual:

Changes in release 4.0.0
* New character set `latin_de' which provides correct German sorting.

Does this mean that clients before ver 4.0.0 can't access new servers with
correct German sorting???

Thomas


On Thu, 6 Jun 2002, Thomas Spahni wrote:

> Dear listmembers,
> 
> I've got 2 hosts running Linux: hostnames are 'hegel' and 'fidel'
> 
> me@hegel:~>mysqladmin version
> mysqladmin  Ver 8.21 Distrib 3.23.39, for pc-linux-gnu on i586
> 
> me@fidel:~>mysqladmin version
> mysqladmin  Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686
> 
> When logged in to 'hegel' I can't mysqlshow the databases on 'fidel'. It
> works, but gives an error.
> 
> me@fidel:~> mysqlshow -u me -h fidel
> +---+
> | Databases |
> +---+
> | bge   |
> | mysql |
> | something |
> | test  |
> +---+
> 
> me@hegel:~> mysqlshow -u me -h fidel
> mysqlshow: File '/usr/share/mysql/charsets/?.conf' not found (Errcode: 2)
> mysqlshow: Character set '#31' is not a compiled character set and is not
> specified in the '/usr/share/mysql/charsets/Index' file
> +---+
> | Databases |
> +---+
> | bge   |
> | mysql |
> | something |
> | test  |
> +---+
> 
> However, it runs fine the other way round (4.0.1 client, 3.23.39 server):
> 
> me@fidel:~> mysqlshow -u me -h hegel
> +--+
> |  Databases   |
> +--+
> | casino   |
> | mitteilungen |
> | mysql|
> | ssl  |
> | test |
> +--+
> 
> What happens? Any insights what to look for?
> Thomas Spahni


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Fw: help with quota problem, thx.

[asong]

- Original Message -
From: "asong" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 07, 2002 4:30 PM
Subject: help with quota problem, thx.


>
> - Original Message -
> From: "asong" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, June 07, 2002 4:29 PM
> Subject: Fw: mysql database quota problems.
>
>
> >
> > - Original Message -
> > From: "asong" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Friday, June 07, 2002 8:40 AM
> > Subject: mysql database quota problems.
> >
> >
> > > Hello,
> > >
> > > I want to set quota to mysql database by every database or by every
> user,
> > I
> > > can not find any issues from mysql official website, and I don't think
> the
> > > Linux or FreeBSD File System quota solution is effective, who can help
> me?
> > > Thank you in adanced.
> > >
> > > Regards,
> > > Asong.
> > >
> > >
> > >
> > > -
> > > Before posting, please check:
> > >http://www.mysql.com/manual.php   (the manual)
> > >http://lists.mysql.com/   (the list archive)
> > >
> > > 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

help with quota problem, thx.

[asong]

- Original Message -
From: "asong" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 07, 2002 4:29 PM
Subject: Fw: mysql database quota problems.


>
> - Original Message -
> From: "asong" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, June 07, 2002 8:40 AM
> Subject: mysql database quota problems.
>
>
> > Hello,
> >
> > I want to set quota to mysql database by every database or by every
user,
> I
> > can not find any issues from mysql official website, and I don't think
the
> > Linux or FreeBSD File System quota solution is effective, who can help
me?
> > Thank you in adanced.
> >
> > Regards,
> > Asong.
> >
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > 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

quota problem

[asong]

- Original Message -
From: "asong" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 07, 2002 4:29 PM
Subject: Fw: mysql database quota problems.


>
> - Original Message -
> From: "asong" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, June 07, 2002 8:40 AM
> Subject: mysql database quota problems.
>
>
> > Hello,
> >
> > I want to set quota to mysql database by every database or by every
user,
> I
> > can not find any issues from mysql official website, and I don't think
the
> > Linux or FreeBSD File System quota solution is effective, who can help
me?
> > Thank you in adanced.
> >
> > Regards,
> > Asong.
> >
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > 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

Fw: mysql database quota problems.

[asong]

- Original Message -
From: "asong" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 07, 2002 8:40 AM
Subject: mysql database quota problems.


> Hello,
>
> I want to set quota to mysql database by every database or by every user,
I
> can not find any issues from mysql official website, and I don't think the
> Linux or FreeBSD File System quota solution is effective, who can help me?
> Thank you in adanced.
>
> Regards,
> Asong.
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> 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

foreign keys in MySQL-Max-4.0.1

[victoria papazoi]

Hi,
I use MySQL-Max-4.0.1 and i am wantering if
is true that the information about the foreign keys are stored at
comment properties of table with maximum length 60 characters?
I need to use innodb tables with many f.keys.What I have to do?

Thank you.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysqld --flush

[Ritu Singla]

Hello,

Starting mysqld with --flush, flushes tables to database or disk
log??

Thanx in advance,
Ritu Singla








-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php