RE: Mysql.Sock problem

[A.T.Z.]

Is mysql running (ps -axu)

if not:
/path/to/safe_mysqld --user=mysql &

This is explained in the docs

-Oorspronkelijk bericht-
Van: Jason Steig [mailto:[EMAIL PROTECTED]]
Verzonden: dinsdag 31 december 2002 9:08
Aan: [EMAIL PROTECTED]
Onderwerp: Mysql.Sock problem




I' am relatively new to MySQL and I 'am having a problem with starting
the ./mysql program I always get the mysql.sock error.  I know this
mysql.sock file is used when the local machine is the mysql server.
Likewise I can't start mysql.server or mysqld.  I' am trying to start it
as root.  I know this is not a good practice.  The first think I have to
do is (./mysql -u (User) password).  This is what allows me to create a
user to run mysqld? Correct?




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

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: Sanity on restore

[Patrick Nelson]

Patrick Nelson wrote:
-
Moving a server (RH6.2 w/ 3.23.43) to a temporary system (RH7.3 w/
3.23.49-3) and I have a mysql dump file created using:

  mysqldump -A my-move.sql

Just installed the server rpm on the temp system and any attempt to connect
gives error because of a password, guessing that's because I've got a blank
database.  The sanity check is:  User auth is done by ldap so the users
entered in the mysql database should be ok.  Anyone having done a restore,
could you please tell me the process you used.  The database is very large
and I don't want to find out I frigged up hours later.
-

I ultimately just did a cold directory copy of "/var/lib/mysql".  I was then
able to login using:

  mysql -h localhost -u root -p mysql

and restarted the mysqld.  Then I changed the specific system names from the
old system to new within mysql client with:

  UPDATE user SET host='' WHERE host='';
  FLUSH PRIVILEGES;

then on the DNS I changed the CNAME of where mydb pointed to and reloaded
the zone.  Then tested on a few clients... everything worked great.  No one
even noticed the change.  Easier than restoring?  I'm not sure, but this was
very easy.

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

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

Embedded MySQL Server

[·N¬Ã ÃC]

Hi,
   Since libmysqld does not use sockets or TCP/IP, what's the mechanism it uses when 
the
client and server communicate with each other?
I mean how embedded MySQL Server works as a
server. Thanks for reply in advance.

Regards.
-- 
___
Get your free email from http://mymail.mailasia.com

Powered by Outblaze

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

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

Mysql.Sock problem

[Jason Steig]

I' am relatively new to MySQL and I 'am having a problem with starting
the ./mysql program I always get the mysql.sock error.  I know this
mysql.sock file is used when the local machine is the mysql server.
Likewise I can't start mysql.server or mysqld.  I' am trying to start it
as root.  I know this is not a good practice.  The first think I have to
do is (./mysql -u (User) password).  This is what allows me to create a
user to run mysqld? Correct?



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

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

server shutdown ? lost password ? (didn't set one)

[Richard Nagle]

apparently I can connect to mySQL, as a under privilege user,
but can't created a new database, after two days, of getting this far,
Now what?

So, I did the
[MacG4a:~] rnagle% mysql -u root
ERROR 1045: Access denied for user: 'root@localhost' (Using password: 
NO)
[MacG4a:~] rnagle%
Which is suppose to be the ALL Mighty COMMAND to get in,

I even try to shutdown the server, (NO kill - 9 here !) can't
according the online manual,

Take down the mysqld server by sending a kill (not kill -9) to the 
mysqld server. The pid is stored in a `.pid' file, which is normally in 
the MySQL database directory:

shell> kill `cat /mysql-data-directory/hostname.pid`


did that, with
[MacG4a:~] rnagle% shell > kill `cat 
/usr/local/mysql-3.23.53/data/MacG4a.local.pid'
Unmatched `.
[MacG4a:~] rnagle% shell > kill `cat 
/usr/local/mysql-3.23.53/data/MacG4a.local..pid'
Unmatched `.
[MacG4a:~] rnagle%

[MacG4a:~] rnagle% shell > kill `cat 
/mysql-data-directory/MacG4a.local.pid'
Unmatched `.
[MacG4a:~] rnagle%

[MacG4a:~] rnagle% mysqladmin shutdown
mysqladmin: shutdown failed; error: 'Access denied for user: 
'@localhost' (Using password: NO)'
[MacG4a:~] rnagle%

[MacG4a:local/mysql/data] rnagle% pwd
/usr/local/mysql-3.23.53/data
[MacG4a:local/mysql/data] rnagle% ls -al
total 16
drwxr-x---   6 mysql  wheel   204 Dec 29 18:34 .
drwxr-xr-x  22 503wheel   748 Nov 18 16:00 ..
-rw-r--r--   1 mysql  wheel  1700 Dec 29 18:34 MacG4a.local..err
-rw-rw   1 mysql  wheel 3 Dec 29 18:34 MacG4a.local..pid
drwxr-x---  20 mysql  wheel   680 Nov 18 15:59 mysql
drwxr-x---   2 mysql  wheel68 Nov 18 15:59 test
[MacG4a:local/mysql/data] rnagle%

--
Somewhere over the rainbow, the has to be a way to get access.
Anyone, ( was going to do a restart, with options:Restart mysqld with 
the --skip-grant-tables option.
can't even get there.

Any Help would be appreciated, THANKS-!
Rick


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

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

Re: replication

[Jeremy Zawodny]

On Mon, Dec 30, 2002 at 10:16:07PM -0600, Dan Nelson wrote:
> Bah. sql, query, queries, smallint
> 
> In the last episode (Dec 30), Rick Faircloth said:
> > Aaaa, that's how the auto_increment id problem is solved.
> > 
> > I wonder why they can't do something like Access with its
> > ReplicationID or whatever it's called...
> 
> Access just used a random 32-bit integer, and you risked generating
> dupes.  If you go this route it might be better to create a dual-column
> primary key, with the first column being the server-id taken from
> my.cnf (already visible in the @server_id variable), and the other
> column being a standard AUTO_INCREMENT field (but with each server
> keeping track of its own max ID).

Yeah, that's another option we tossed around.  It has some not so
obvious implications for the replication code, but it's doable.  It'd
require chaning the binlog format, though.  Like AUTO-INCREMENTs
today, the slaves would need to use the server-id of the server that
wrote the binlog entry, not necessarily the one that provided the
binlog data (think of multi-level replication with a slave of a
slave).

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 15 days, processed 562,721,968 queries (407/sec. avg)

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

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

Re: replace ... select (why showing duplicates?)

[Paul DuBois]

At 10:02 -0800 12/30/02, Qunfeng Dong wrote:

Hi,

When I do "replace table2 select (cols) table1 where
clause" to update some records in the table2,
sometimes I got the something like following
indicating Duplicates records being detected.

---
Query OK, 122259 rows affected (1 min 18.28 sec)
Records: 122259  Duplicates: 122254  Warnings: 0
---

Can I safely ignore such "warnings"? The thing bothers
me is that the records selected from the table1 are
NOT duplicate with the existing records in the table2,
but still showing the "Duplicates"? They are NOT
duplicate because the count(*) with the whereclause
returns 0 records before the above "replace ...
select"
mysql> select count(*) from table2 where clause;
+--+
| count(*) |
+--+
|0 |
+--+


That's not a valid test of your assertion.  A "Duplicate" will occur
if the set of records added in the REPLACE statement contains two records
with the same unique key value.  The duplicate will be that the second
of thesee records kicks out the first one.




I am using mysql3.23.49 Linux redhat7.3

Thanks!

Qunfeng Dong



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

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

Re: replication

[Dan Nelson]

Bah. sql, query, queries, smallint

In the last episode (Dec 30), Rick Faircloth said:
> Aaaa, that's how the auto_increment id problem is solved.
> 
> I wonder why they can't do something like Access with its
> ReplicationID or whatever it's called...

Access just used a random 32-bit integer, and you risked generating
dupes.  If you go this route it might be better to create a dual-column
primary key, with the first column being the server-id taken from
my.cnf (already visible in the @server_id variable), and the other
column being a standard AUTO_INCREMENT field (but with each server
keeping track of its own max ID).

Oracle manages this by handing out "groups" of IDs to each server.  The
first server to add a record gets IDs 1-49, for eample, and the next
server gets 50-99.  When a server uses its range up, it asks for
another block (which server is in charge of handing out groups I don't
know.  Maybe the one with the highest uptime?)

http://download-west.oracle.com/docs/cd/B10501_01/rac.920/a96598/design.htm#1028013

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

Pattern Match on 3.23

[Frank Peavy]

I would like to use pattern matching as a substitute to fulltext search on 
MySQL 3.23.
Is this a good alternative?

Are there any limits, like not being able to perform a pattern match on a 
'text' field, etc., that I need to be aware of?

Thanks.


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

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 Performance Assistance

[Jamie Sullivan]

Hello,

I have noticed that my server running mysql-3.23.45 on Solaris 8/400MHz 
sparc/1GB ram system appears to be only using up swap space and not free 
memory.  During light usage this doesn't seem to be a problem but under 
heavy usage this causes my cpu to hit 90 - 100% at times.  Even with 
nothing but the OS running on my system and I start mysql it seems to only 
grab swap.  I am strictly using the my-small, my-medium & my-large 
configuration files as a reference.  Here is the output from top with 
my-large so that you can easily see the swap usage.  I have never seen this 
activity before and it has me stumped, I can only duplicate this on Solaris 
8 02/02 release, os patches are up to date.  Has anyone seen this activity 
before?

This is before mysql:

CPU states: 96.4% idle,  1.0% user,  2.6% kernel,  0.0% iowait,  0.0% swap
Memory: 1024M real, 866M free, 19M swap in use, 1787M swap free

   PID USERNAME THR PRI NICE  SIZE   RES STATETIMECPU COMMAND
 21836 root   1  500 2232K 1608K cpu  0:00  0.44% top
 21833 root   1  120 2616K 2072K sleep0:00  0.27% logit
 21844 root   1  220 1176K 1032K sleep0:00  0.04% iostat
 16686 root   1  580 3360K 2224K sleep0:07  0.03% sshd
   223 root  17  550 8624K 7960K sleep1:18  0.03% nscd
 21832 root   1  480 1032K  864K sleep0:00  0.03% sh
 1 root   1  580 2336K 1384K sleep0:44  0.02% init
 16690 root   1  480 2496K 1992K sleep0:01  0.02% bash
   210 root  12  580 4000K 2328K sleep0:03  0.00% syslogd
   313 root   1  590 3168K 1728K sleep0:01  0.00% sendmail
 16688 jds1  480 2408K 1872K sleep0:00  0.00% bash
   197 root   1  480 1784K  984K sleep0:00  0.00% inetd
 16689 root   1  480  312K  312K sleep0:00  0.00% sh
49 root   5  510 1288K  808K sleep0:00  0.00% syseventconfd
47 root   9  520 1480K 1112K sleep0:00  0.00% syseventd

This is after:

CPU states: 98.0% idle,  0.4% user,  1.4% kernel,  0.2% iowait,  0.0% swap
Memory: 1024M real, 862M free, 275M swap in use, 1530M swap free

   PID USERNAME THR PRI NICE  SIZE   RES STATETIMECPU COMMAND
 21885 root   1  500 2232K 1608K cpu  0:00  0.44% top
 21871 mysql 11  200  260M 4888K sleep0:00  0.24% mysqld
 21873 root   1  410 2616K 2072K sleep0:00  0.19% logit
 16686 root   1  580 3360K 2224K sleep0:07  0.15% sshd
 16690 root   1  480 2496K 1992K sleep0:01  0.10% bash
 21886 root   1  510 1800K 1304K sleep0:00  0.09% netstat
   223 root  17  550 8624K 7960K sleep1:18  0.06% nscd
 21872 root   1  480 1032K  864K sleep0:00  0.05% sh
 21852 root   1  200 1040K  872K sleep0:00  0.05% safe_mysqld
 1 root   1  580 2336K 1384K sleep0:44  0.02% init
   210 root  12  580 4000K 2328K sleep0:03  0.00% syslogd
   313 root   1  590 3168K 1728K sleep0:01  0.00% sendmail
 16688 jds1  480 2408K 1872K sleep0:00  0.00% bash
   197 root   1  480 1784K  984K sleep0:00  0.00% inetd
 16689 root   1  480  312K  312K sleep0:00  0.00% sh


At 09:20 AM 12/16/2002 -0800, you wrote:
On Mon, Dec 16, 2002 at 10:43:28AM -0600, Jamie Sullivan wrote:
> Hello,
>
> I am setting up a mySQL box in my development environment and have
> noticed that even under little usage mysql will consume a couple
> hundred MB's of swap space even though I have almost 1GB of apparent
> "free" physical memory.  I am using the 'my-large.cnf' configuration
> file and otherwise haven't modified anything. The server is a 400Mhz
> sparc w/ 2GB ram and 1 SCSI disk.  Can someone please provide some
> guidance as to whether this is normal or am I not configuring
> something properly.

What OS are you using?

Some versions of the Linux kernel had problems that caused it to
needlessly swap out MySQL memory when there was lots of memory still
available.

Here was some of my original discussion on the topic:

http://jeremy.zawodny.com/blog/archives/000132.html

Jeremy
--
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 1 days, processed 55,530,181 queries (426/sec. avg)

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

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




Jamie Sullivan
EOS, Inc.
717 South Grand Avenue West
Springfield, IL 62704
217-241-6000 ext.104
[EMAIL PROTECTED]


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

Re: indexing a blob

[Benjamin Pflugmann]

Hi.

On Mon 2002-12-30 at 16:53:08 -0500, [EMAIL PROTECTED] wrote:
[...]
> hashsum tinyblob not null ,   # hash of the card: have we seen this one?
> index (hashsum)   # for quick lookups
> 
> Whenever I try this with the index, I get
> 
>   ERROR 1170 at line 49: BLOB column 'hash' used in key specification
>   without a key length

>From http://www.mysql.com/doc/en/CREATE_TABLE.html:

  * With col_name(length) syntax, you can specify an index that uses
only a part of a CHAR or VARCHAR column. This can make the index
file much smaller. See section 5.4.4 Column Indexes.
  * Only the MyISAM table type supports indexing on BLOB and TEXT
columns. When putting an index on a BLOB or TEXT column you MUST
always specify the length of the index:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

> I don't know where the key length needs to be specified; I tried
> 
>   hashsum tinyblob(255) not null

That is the column definition, but it was about key length, so better
look at the key definition:

  hashsum tinyblob not null ,
  index (hashsum(255))

If you got confused by "col_name(length)" from the docu, it
references:

  index_col_name:
col_name [(length)]

on the same page.

HTH,

Benjamin.


-- 
[EMAIL PROTECTED]

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

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

Re: indexing a blob

[David T-G]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi, all --

...and then David T-G said...
% 
...
% hashsum tinyblob not null ,   # hash of the card: have we seen this one?
% index (hashsum)   # for quick lookups
...
%   ERROR 1170 at line 49: BLOB column 'hash' used in key specification
%   without a key length

Thanks so much to everyone who pointed out that I need

  hashsum tinyblob ,
  index (hashsum(NN))

where NN is the number of chars to index (quite possibly less than the
whole 255, and for me 32 -- the length of the md5 sum).  It seems obvious
now but obviously I missed it before :-)


Thanks & HAND & HH

mysql query,
:-D
- -- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, "Science and Health"
http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE+EO4FGb7uCXufRwARAqgxAKC+BvhEx/jQAWeDY24JHMR4n+F0ywCfVkGc
NrjEmbfWvkN18kIIpoKRV+Y=
=w2dv
-END PGP SIGNATURE-

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

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

Re: altering data structure

[Benjamin Pflugmann]

Hi again.

On Sun 2002-12-29 at 15:21:33 -0500, [EMAIL PROTECTED] wrote:
> ...and then Benjamin Pflugmann said...
[...]
> % Of course, that's only possible if you can live with being
> % MySQL-specific.
> 
> Hmmm...  so it's a mysql thing and not a SQL thing.  Well, I'd like to
> avoid that, even though I like mysql...

Well, setting up a database (or updating it) is always very
vendor-specific, isn't it?


Besides, I have yet to see production-level SQL that works on several
databases without change (except for stuff that is developed that
way). The problem is that the SQL standard is too unspecific in some
areas and the vendors have to fill in the details and it becomes a
PITA to work without the vendor-specific features.

IMHO, the most common example is the lack of sequence support.
AUTO_INCREMENT is MySQL-specific. To be portable you have to simulate
sequences yourself, which is possible, but you won't do except if you
*know* you will need to run on a different RDBMS later.

More info here:

  http://www.mysql.com/doc/en/Compatibility.html


> Thanks & HAND & HH
 

Hehe. I wondered about the non-abbriviation in your first mail
already. ;-)

HTH,

Benjamin.


-- 
[EMAIL PROTECTED]

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

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

Re: replication

[Jeremy Zawodny]

On Mon, Dec 30, 2002 at 05:45:07PM -0500, Rick Faircloth wrote:
> Aaaa, that's how the auto_increment id problem is solved.
> 
> I wonder why they can't do something like Access with its ReplicationID
> or whatever it's called...

They can (and probably will) but it's all a matter of development
priorities.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 15 days, processed 557,978,375 queries (408/sec. avg)

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

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

Re: ensuring an instructor, client, or room isn't double-booked

[Benjamin Pflugmann]

Hi David.

On Sun 2002-12-29 at 16:21:56 -0500, [EMAIL PROTECTED] wrote:
> ...and then Benjamin Pflugmann said...
[...]
> No; everything is one hour.  Two hours is two bookings.

Ah. Good. That makes the case easier and is exactly the case my last
answer was about.  

[...]
> % know beforehand when the class starting at x o'clock will end? And
> % saving a number for the time slot would also work?
> 
> Saving a number?  I don't know what you mean here...

Not important anymore.

But for completeness: You could do as with school classes. 9-10
o'clock is 1st, 10-11 is 2nd and so on. So you could theoretically
save "date 2003-02-10 class 1" instead of "date 2003-02-10 time
09:00:00".

Not that my suggestion would be better necessarily, it just served to
make it less ambiguous what we are talking about. Seemed it failed. ;)

[...]
> % In the case of time slots, you could create a unique index on
> % (client,time) and (instr,time) and skip the selects. Simply insert and
> % check for an duplicate key error.
> 
> Hmmm...  OK; avoiding duplicates is the end goal and so I should probably
> learn more about this apparently simple approach.

Yes, the main purpose of UNIQUE indexes is to guarantee uniqueness in
a column (or a set of columns). So that's the way to go.

> Um, what next? :-)

:-)

> This is probably in the mysql doc, so a pointer would be appreciated
> but know that I will [try to] look up multi-column indexes and see
> how they deal with duplicates.

  http://www.mysql.com/doc/en/Multiple-column_indexes.html

Although the manual covers a lot of stuff which is not strictly
MySQL-related, it is not well suited to be an introduction into the
world of relational databases.

May I suggest Paul DuBois' book? It provides a lot of insight in such
issues.

[...]
> I figured I'd have to check every record to see if the datetime and
> client (or instructor) matched what I have already, and a few years
> from now when that table is long it seems like that would take a
> while (seconds? minutes? i dunno) to query...  Maybe not at all on
> such a simple table (very short fixed-length fields, and not very
> many of them), and maybe not with the indexes (hmmm... indices?) you
> mention above.

Well, that is what indexes are all about. Searching for 1 record in
1 billion with indexes can be as fast as a full table scan for 1 in 30
records.

A more complex answer from some ealier posts of mine (still
simplified):

  http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:77486
  http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:115591
  

[...]
> % If not: Whether an index (client,time) or (time,client) is better
> % depends on your data (few or a lot of client, etc). If you don't have
> 
> I don't know exactly, but we're probably looking at a total of 3k or 4k
> clients with some .5k actively making frequent bookings, and up to a
> dozen instructors (per installation, which for starters will be a little
> PII-266 with only 64M of RAM and a 5G IDE disk but will probably settle
> on a cheapo PIII-800 with 128M and a 10G IDE disk at each studio).  Is
> that a few or a lot?

It depends on how many time slots you have. The general answer is that
you want the column with the highest cardinality first. But also the
storage size matters and so on. That's what "etc" was about. ;-)

Usually I use the trial and error method I mentioned below, myself.
But sometimes it is so obvious that you don't have to. Like if you had
only 40 time slots (only planning a week ;). The client should come
first.

> % I you are not sure, simply build all of them and look which one get
> % used. Then dropped the other ones again. The same applies for "instr".
> 
> That's a safe approach that should get the questions answered :-)

Jupp.

[...]
> I don't even know; see my newbie comment above :-)  I thought that a
> temporary table helps to speed up specific queries because the grouping
> is already done and then you just select from that, but I dunno :-)

That is correct. *If* you need grouping. And then, as you said, only
for some specific queries.

But you don't (need grouping). You are right, that you have kind of
groups in the table. But the term "grouping" is about when you want to
retrieve some fact based on groups. E.g. if you want to know the next
class each client attends, similar to this:

  http://www.mysql.com/doc/en/example-Maximum-column-group-row.html


But I think the overall answer is: Don't optimize early. You are
right, that you need a clean design to get reasonable speed. I suggest
you first care about that and think about specific optimizations (like
using temporary tables) only stumble upon a specific bottleneck. It
sounds to me as if you are worrying about non-issues currently. But,
of course, a basic understanding of what is going on never hurts.

HTH, and yes, HAND, too,

Benjamin.

-- 
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manua

RE: Binary Data and BLOB Problem

[Mike Hillyer]

Hi Kevin;

You may want to look at the online article I wrote at
http://www.dynamergy.com/mike/articles/blobaccessvb.html which covers use of
the stream object in detail. While written for VB, it should be easy enough
to convert to ASP.

Mike Hillyer




-Original Message-
From: Kevin Wixson [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 30, 2002 10:29 AM
To: [EMAIL PROTECTED]
Subject: Binary Data and BLOB Problem



Having trouble with my first MySQL database. I want to upload and install
image files to a database for online sample photo gallery for our
customers. The system I've created so far sometimes works, sometimes
doesn't. It seems to depend on the size of the file being uploaded, but
tests have been inconclusive. Having trouble diagnosing the problem since
the error is most often "Internal Server Error," which doesn't tell me
much. Otherwise I get:


Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]Lost connection to MySQL
server during query

/shooters/Insert.asp, line 148

The relevant code is:

' Checking to make sure if file was uploaded
If fileSize > 0 Then

set rsImage = Server.CreateObject("ADODB.Recordset")
rsImage.ActiveConnection = MM_connShooters_STRING
rsImage.Source = "select * from web_lab.Images limit 0"
rsImage.CursorType = adOpenKeyset
rsImage.CursorLocation = adUseClient
rsImage.LockType = adLockPessimistic
rsImage.Open()
rsImage_numRows = 0

' Adding data
rsImage.AddNew
rsImage("userID") = userID
rsImage("imageType") = contentType
rsImage("imageSize") = fileSize
rsImage("imageName") = fileName
rsImage("imageCaption") = caption
Do While lngOffset < fileSize
varChunk = LeftB(RightB(fileData, fileSize - lngOffset), conChunkSize)
rsImage("imageData").AppendChunk varChunk
lngOffset = lngOffset + conChunkSize
Loop

ERROR-->rsImage.Update

'adoImageStream.Close
rsImage.Close
Set rsImage = Nothing

Response.Write "File was successfully uploaded..."
Response.Write ""
Else
Response.Write "No file was selected for uploading"
Response.Write "..."
End If

My connection string is:

MM_connShooters_STRING = "driver={MySQL ODBC 3.51 Driver};
database=[name];uid=[id];pwd=[pass];OPTION=16427;"

The constant conChunkSize is 100. I have checked the max packet size (set
to 1550) and as you can see, set cursors to client.

Alternatively, I have also tried to use the stream object, but I haven't
had any luck there either. I'm sure I'm not doing it right.



Thank you,

Kevin Wixson
IT Manager
Norman Camera & Video

(616) 567-5552
1-800-900-6676
Fax: (616) 343-6410
e-mail: [EMAIL PROTECTED]

Visit us on the web at: http://www.normancamera.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

Sanity on restore

[Patrick Nelson]

Moving a server (RH6.2 w/ 3.23.43) to a temporary system (RH7.3 w/
3.23.49-3) and I have a mysql dump file created using:

  mysqldump -A my-move.sql

Just installed the server rpm on the temp system and any attempt to connect
gives error because of a password, guessing that's because I've got a blank
database.  The sanity check is:  User auth is done by ldap so the users
entered in the mysql database should be ok.  Anyone having done a restore,
could you please tell me the process you used.  The database is very large
and I don't want to find out I frigged up hours later.

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

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: indexing a blob

[Keith C. Ivey]

On 30 Dec 2002, at 16:53, David T-G wrote:

> I don't know where the key length needs to be specified; I tried
> 
>   hashsum tinyblob(255) not null
> 
> but that puked at the create stage :-)

The prefix length goes with the index definition, not the column 
definition.  See http://www.mysql.com/doc/en/CREATE_TABLE.html :

|   Only the MyISAM table type supports indexing on BLOB and TEXT
|   columns. When putting an index on a BLOB or TEXT column you MUST
|   always specify the length of the index: 
|
|   CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));


-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org
Phone 202-667-6653

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

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: indexing a blob

[Jennifer Goodie]

http://www.mysql.com/doc/en/CREATE_INDEX.html

you key needs a length, not your field.

index (hashsum(length)) # for quick lookups

-Original Message-
From: David T-G [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 30, 2002 1:53 PM
To: mysql users
Subject: indexing a blob


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi, all --

Having learned a bit about indexes, I have tried to practice a bit.  So
far things work for my char(20) fields, but I have a problem with a
tinyblob.  To wit:

  create table ccards
  (
# ID number
id smallint not null default 0 auto_increment primary key ,
type smallint not null ,# references ccardtypes.id
name char(40) not null ,# name as on card
number tinytext not null ,  # card number
expdate date not null , # expiration date
hashsum tinyblob not null , # hash of the card: have we seen this one?
index (hashsum) # for quick lookups
  ) ;

Whenever I try this with the index, I get

  ERROR 1170 at line 49: BLOB column 'hash' used in key specification
  without a key length

I don't know where the key length needs to be specified; I tried

  hashsum tinyblob(255) not null



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

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: indexing a blob

[Jeremy Zawodny]

On Mon, Dec 30, 2002 at 04:53:08PM -0500, David T-G wrote:
> 
> Hi, all --
> 
> Having learned a bit about indexes, I have tried to practice a bit.  So
> far things work for my char(20) fields, but I have a problem with a
> tinyblob.  To wit:
> 
>   create table ccards
>   (
> # ID number
> id smallint not null default 0 auto_increment primary key ,
> type smallint not null ,  # references ccardtypes.id
> name char(40) not null ,  # name as on card
> number tinytext not null ,# card number
> expdate date not null ,   # expiration date
> hashsum tinyblob not null ,   # hash of the card: have we seen this one?
> index (hashsum)   # for quick lookups
>   ) ;
> 
> Whenever I try this with the index, I get
> 
>   ERROR 1170 at line 49: BLOB column 'hash' used in key specification
>   without a key length
> 
> I don't know where the key length needs to be specified; I tried
> 
>   hashsum tinyblob(255) not null
> 
> but that puked at the create stage :-)

Try this:

 index (hashsum(123))   # for quick lookups

Where 123 is the number of characters/bytes to index.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 15 days, processed 557,369,159 queries (408/sec. avg)

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

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

Re: Replication priority / speed

[Dan Nelson]

In the last episode (Dec 30), Matt Sturtz said:
> Hello, Jeremy, et al--  Thanks for the reply before...  Further questions:
> >> Is it possible to set either set the priority ('nice') of the
> >> Slave thread down so it doesn't do that?
> >
> > The slave thread only?  No, not really.  You could nice MySQL when
> > you start it up.  But I'm not sure how much effect (positive or
> > negative) that'd have.
> 
> When I run "show [full] processlist", there's an "Id" column, but it
> doesn't corrospond with the Unix PID of the process (on OS's that use
> a seperate PID for each thread-- like Linux does)...  Is there any
> way to(easilly) figure out which PID is handling the slave thread, so
> that I might re-nice it after it's already been started up?

That probably won't help you, since I doubt you're CPU-bound.  Most
likely your slave thread is monopolizing the disk I/O.  You could try
manually stopping and starting the thread by sending it SIGSTOP and
SIGCONT signals.  Alternating the two every 5 seconds will give you a
50% slowdown.  I don't know how LinuxThreads is going to like having
threads messes with like that though.  You can probably also do SLAVE
STOP / SLAVE START and get the same effect.
 
-- 
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

Privileges in MySQL 4.0.7 (upgrade)

[Stefan Hinz]

Dear list,

just upgraded from MySQL 4.0.3 to 4.0.7 on my Win2K box at home. Leaving
DATADIR as it was, the 4.0.7 installation did not touch the privilege
tables in the mysql database, so I did not have the new privileges and
features available.

As there doesn't seem to be a mysql_fix_privilege_tables script for
Win2K, I wiped out my old mysql database (pressing DEL in Win Explorer),
and installed again. This gave me a new mysql database, but no new
privileges at all. My old user accounts are gone, though, so the
installation procedure definitely set up something new.

DESCRIBE host; DESCRIBE user; DESCRIBE db; shows me that features like
"Create_tmp_table_priv" and "Super_priv" are still missing. The most
"modern" feature I can find is the "References_priv".

Win Explorer tells me the mysql database is from 2002-12-30 (today), but
all the tables in there are from 2001-10-06. I took down the server when
installing the update, and I put a copy of my old DATADIR on another
disk.

Any hints are greatly appreciated.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3



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

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

Upgrade to MySQL 4.0.x

[Davy Obdam]

Hi people,

I have a few questions about whether or not i should upgrade to MySQL 
4.0.7-Gamma on my Windows XP system. I am now running MySQL 3.23.54a on 
my Laptop. Its used for developing/testing, and the applications needs 
to be transported to the final server after completion. Most of the time 
however the server is a 3.23.x and not 4.0.x. Is 4.0.x backwards 
compatible? Can i tranfer my databases without problems from my current 
MySQL 3.23.54 database to MySQL 4.0.x.? And is MySQL 4.0.7-gamma 
coonsiderd stable?

Also i would like to know how-to upgrade, can i just instal over my 
excisting MySQL installation?

Any thoughts are apreciated, thanks for your time,

Best regards,

Davy Obdam
mailto:[EMAIL PROTECTED]



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

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

duplicate entry on key 1

[Jason Eng]

Help!

I have a problem with a database which gives a  "duplicate entry on key 
1" error message when I try to re-add a record that I have just 
deleted. I have a feeling it has to do with my index..

my key column is shown like this in the create statement:

Create table 'mydb' (



'MyKeyField' varchar(30) default '',
'another_field' varchar(30) default '',
'last_field' varchar(16) default '',
UNIQUE KEY 'idx_MyKeyField' ('MyKeyField')
) Type=MyISAM

I am using MySQL 3.23.53 on MacOS X 10.2.2, with PhP3 and Lasso 5.0.1

Thanks for any help,

Jason



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

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: front ends?

[Chris Garaffa]

On Monday, December 30, 2002, at 03:42  PM, Richard Nagle wrote:

what would be the easy to use, gui front end for
mySQL 3.23.53 (for Mac OS X )


I use phpmyadmin  on Mac OS X (and OS X 
Server), with absolutely no problems.

--
Chris Garaffa
<[EMAIL PROTECTED]>
http://moondrop.heli0s.net -- coming whenever heli0s pays his bill
The true story of how I got my girlfriend into UNIX:
unzip ; strip ; touch ; finger ; mount ; fsck ; more ; yes ; umount ; 
sleep


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

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

RE: replication

[Rick Faircloth]

Aaaa, that's how the auto_increment id problem is solved.

I wonder why they can't do something like Access with its ReplicationID
or whatever it's called...

Rick

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 30, 2002 4:52 PM
To: Rick Faircloth
Cc: [EMAIL PROTECTED]
Subject: Re: replication


On Mon, Dec 30, 2002 at 12:15:49PM -0500, Rick Faircloth wrote:
> Hi, Jeremy.
> 
> What if one is the master and one is a slave, but both are used in
> production environments?

That's safe.  It avoids the problem.

> Is it possible for a slave to be used as a live production database
> or is it simply for backup?

You can use it as a live read-only server, yes.

> If they can both be used as live production databases, how can they
> be synced with replication?

The difference is that the master is read/write while the slave(s) are
read-only.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 15 days, processed 556,001,505 queries (410/sec. avg)

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

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

Re: Replication priority / speed

[Simon K. Grabowski]

>>> Or, alternatly, is there a way to limit the slave thread to only "X"
>>> bin-log transactions per second?
>>
>> There is not.
>
> Any plan to add this feature?  I would think it'd be useful...

Wouldn't it be better to *solve* your problem instead of going
around it?

> MyTOP says our key efficiency is 97.35%, with an average of 1.24 q/sec (on
> the master--  most queries are done directly on the slave, with only
> updates happening on the master).  We've optimized things as best we can. 

1.24 q/sec doesn't sound loaded at all.
How many queries/second do you see on your slave box?
How many slow queries per hour?

You say you that most queries are done directly on the slave.
Why don't you spread the SELECTs across both boxes?

> The problem is our customers are allowed to bulk-load keywords into our
> database, which causes about 4 large tables to be updated quite a bit. 
> Whenever this happens, the slaves struggle to get caught back up...

Have you tried enabling DELAY_KEY_WRITE on the 4 tables that
your bulk-loader updates?

Why not spread the bulk-load in time, so that the keywords aren't
added instantly?

Why not modify your software so that it doesn't read from the
slave while your bulk-loader runs?

Can you give some more information on your master & slave config?
(hardware, OS, MySQL show variables, show status)


Simon Grabowski
GetResponse.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: replication

[Jeremy Zawodny]

On Mon, Dec 30, 2002 at 12:15:49PM -0500, Rick Faircloth wrote:
> Hi, Jeremy.
> 
> What if one is the master and one is a slave, but both are used in
> production environments?

That's safe.  It avoids the problem.

> Is it possible for a slave to be used as a live production database
> or is it simply for backup?

You can use it as a live read-only server, yes.

> If they can both be used as live production databases, how can they
> be synced with replication?

The difference is that the master is read/write while the slave(s) are
read-only.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 15 days, processed 556,001,505 queries (410/sec. avg)

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

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

indexing a blob

[David T-G]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi, all --

Having learned a bit about indexes, I have tried to practice a bit.  So
far things work for my char(20) fields, but I have a problem with a
tinyblob.  To wit:

  create table ccards
  (
# ID number
id smallint not null default 0 auto_increment primary key ,
type smallint not null ,# references ccardtypes.id
name char(40) not null ,# name as on card
number tinytext not null ,  # card number
expdate date not null , # expiration date
hashsum tinyblob not null , # hash of the card: have we seen this one?
index (hashsum) # for quick lookups
  ) ;

Whenever I try this with the index, I get

  ERROR 1170 at line 49: BLOB column 'hash' used in key specification
  without a key length

I don't know where the key length needs to be specified; I tried

  hashsum tinyblob(255) not null

but that puked at the create stage :-)


TIA & HAND & Happy Holidays

mysql query,
:-D
- -- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, "Science and Health"
http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE+EMBEGb7uCXufRwARAr/eAJ0ffNkpN6ifCuuFehZnBKqkkR/BWACfcsAz
q7a513vlz5dO7r43GsrTwKI=
=KPvm
-END PGP SIGNATURE-

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

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

replication problems

[Shane Allen]

We have two users, lets call them test and test2.

When we log in as test from our front end web servers, we can execute queries and they 
replicate.

When we log in as test from our back end db servers (say to run a cleanup script by 
hand), the queries replicate.

When we log in as test2 from our back end db servers (as part of a periodic process), 
it replicates the queries.

When we log in from our front end web servers as test2, the queries do not replicate. 
They are never added to the binary update log. Otherwise, everything appears to work 
fine; the master db server runs the queries and updates the tables appropriately.

If necessary I can upload a copy of our grant table, but I'm hoping someone has a 
quick. easy fix. :)

Thanks

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

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

Re: Replication priority / speed

[Jeremy Zawodny]

On Mon, Dec 30, 2002 at 03:48:30PM -0600, Dan Nelson wrote:
> In the last episode (Dec 30), Matt Sturtz said:
> > Hello, Jeremy, et al--  Thanks for the reply before...  Further questions:
> > >> Is it possible to set either set the priority ('nice') of the
> > >> Slave thread down so it doesn't do that?
> > >
> > > The slave thread only?  No, not really.  You could nice MySQL when
> > > you start it up.  But I'm not sure how much effect (positive or
> > > negative) that'd have.
> > 
> > When I run "show [full] processlist", there's an "Id" column, but it
> > doesn't corrospond with the Unix PID of the process (on OS's that use
> > a seperate PID for each thread-- like Linux does)...  Is there any
> > way to(easilly) figure out which PID is handling the slave thread, so
> > that I might re-nice it after it's already been started up?
> 
> That probably won't help you, since I doubt you're CPU-bound.  Most
> likely your slave thread is monopolizing the disk I/O.  You could try
> manually stopping and starting the thread by sending it SIGSTOP and
> SIGCONT signals.  Alternating the two every 5 seconds will give you a
> 50% slowdown.  I don't know how LinuxThreads is going to like having
> threads messes with like that though.  You can probably also do SLAVE
> STOP / SLAVE START and get the same effect.

Using SLAVE {START|STOP} is much better.  Image what happens if the
slave thread gets a critical lock and then you SIGSTOP it!

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.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: style question: "drop database" in an install script

[David T-G]

Arthur, et al --

...and then Arthur Fuller said...
% 
% I don't know about anyone else, but for this sort of thing I love the
% program called DeZign, which is a data-modeling tool. You can "draw" your
% tables and their columns and set up relations and so on. DeZign will then
% write your code for you.

Thanks!

I actually broke out a Windows box to try it out so that I could see how
it would relate keys :-)  Now I have some sample syntax for foreign keys,
if nothing else.

Anyone know of a design tool which runs on Linux and FreeBSD?


% 
% Personally, I like to keep all my "drop" statements in one script, all my
% "create" scripts in another, and all my "populate" scripts in yet another.

Yeah, I think I'll do something like that.


% 
% Arthur


Thanks & HAND & Happy Holidays

mysql query,
:-D
-- 
David T-G  * There is too much animal courage in 
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, "Science and Health"
http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg!


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

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

Re: Replication priority / speed

[Jeremy Zawodny]

On Mon, Dec 30, 2002 at 01:21:49PM -0700, Matt Sturtz wrote:
> Hello, Jeremy, et al--  Thanks for the reply before...  Further questions:
> 
> 
> >> Is it possible to set either set the priority ('nice') of the Slave
> >> thread down so it doesn't do that?
> >
> > The slave thread only?  No, not really.  You could nice MySQL when you
> > start it up.  But I'm not sure how much effect (positive or negative)
> > that'd have.
> 
> When I run "show [full] processlist", there's an "Id" column, but it
> doesn't corrospond with the Unix PID of the process (on OS's that
> use a seperate PID for each thread-- like Linux does)...

Right.

> Is there any way to(easilly) figure out which PID is handling the
> slave thread, so that I might re-nice it after it's already been
> started up?

Not that I know of.  From MySQL's point of view there's no way to
know.

> >> Or, alternatly, is there a way to limit the slave thread to only "X"
> >> bin-log transactions per second?
> >
> > There is not.
> 
> Any plan to add this feature?  I would think it'd be useful...

I've not heard of any.  You can always lobby to get it on the MySQL
TODO list.

> > Are your updates already well optimized?  If you're doing enough work to
> > cause noticeable speed problems, I'd double-check that if you
> > haven't already.
> 
> MyTOP says our key efficiency is 97.35%, with an average of 1.24
> q/sec (on the master-- most queries are done directly on the slave,
> with only updates happening on the master).  We've optimized things
> as best we can.  The problem is our customers are allowed to
> bulk-load keywords into our database, which causes about 4 large
> tables to be updated quite a bit.  Whenever this happens, the slaves
> struggle to get caught back up...

Ahh, okay.  I buy that.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.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: replication

[Jeremy Zawodny]

On Mon, Dec 30, 2002 at 02:55:19PM -0500, Arthur Fuller wrote:
>
> Is there in *nix an implementation of GUIDs? If the MS math is too
> be believed, GUIDs are guaranteed unique for the next century. No
> matter how many monkeys are at the typewriters. Supposing a) that
> there is a *nix GUID implementation, what's the right column type to
> use within MyISAM and InnoDB? Assuming that anyone besides me finds
> this interesting, will MySQL ever support GUID column types?

That was one of the topics that came up this summer when I chatted
with Monty & crew about replication and auto-increment ideas.  He
seemed open to the idea but didn't commit to implementing anything.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 15 days, processed 555,361,246 queries (410/sec. avg)

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

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

Re: An Idea

[Stefan Hinz, iConnect \(Berlin\)]

Peter,

> So (taking a deep breath !) I would be prepared to lay the foundations
for a
> faq / knowledge base aimed specifically at this type of questions and
to
> manage it.

Thanks, I appreciate that. I've been waiting a moment before answering,
waiting for others to take a deep breath and say "yes", too, but it
seems you are the only one.

> [James: ] $44.99 SRP  - a low cost compared to the 'deep breath" below
:-)

I know there are good books, and I especially like Paul's books on
MySQL. There are good books by German authors on MySQL, too.

But, then again, refering to books will bring up the same sort of
answers people on this list are complaing about ("hey, stupid, go buy
book xyz and read it before asking silly questions"). With a FAQ, this
could be "You will find the answer for your question at
www.mysql.com/faq/answer_xyz.html".

> So, any thoughts? Where do we go from here?

Nowhere, I suppose :(

Maybe some of the folks at MySQL AB will read this and come up with a
database structure for the FAQ on MySQL.com and user accounts for you
and me (and maybe others, once this thing has started).

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Peter Lovatt" <[EMAIL PROTECTED]>
To: "Stefan Hinz, iConnect (Berlin)" <[EMAIL PROTECTED]>; "Cal
Evans" <[EMAIL PROTECTED]>; "Paul DuBois" <[EMAIL PROTECTED]>; "Adam
Wi´ckowski" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, December 30, 2002 1:18 AM
Subject: RE: An Idea


> Hi
>
> My first thought was that the docs on mysql.com should do the job,
but,
> although they answer most technical questions, often more down to
earth
> stuff like 'How do I store images' or 'how do I get started with SQL'
isn't
> there, or it is difficult to find.
>
> The other problem is that the documentation is often technical to
point that
> it is difficult to understand, even as a reasonably competent database
> programmer. I think newbies (and not so newbies) could feel totally
> overwhelmed by much of it.
>
> So (taking a deep breath !) I would be prepared to lay the foundations
for a
> faq / knowledge base aimed specifically at this type of questions and
to
> manage it.
>
> So, any thoughts? Where do we go from here?
>
> Peter
>
> ---
> Excellence in internet and open source software
> ---
> Sunmaia
> Birmingham
> UK
> www.sunmaia.net
> tel. 0121-242-1473
> International +44-121-242-1473
> ---
>
> -Original Message-
> From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]]
> Sent: 29 December 2002 22:01
> To: Cal Evans; Paul DuBois; Adam Wi´ckowski; [EMAIL PROTECTED]
> Subject: Re: An Idea
>
>
> Cal, Paul, dear list,
>
> thank you, Cal, for your wise words ...
>
> > "Here's the FAQ we developed so you don't have to waste our time
> asking
> > questions that have already been answered."
>
> Go to MySQL.com and type "FAQ" in the search box. This will provide 71
> results, some of them with valuable FAQ-like information, but no real
> FAQ.
>
> Instead, we have this in every list mail:
>
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
>
> I would keep this shorter, saying "Believe in God and do not sin" ;-)
>
> But seriously: Anybody here interested in setting up a FAQ on
MySQL.com
> / MySQL.de,
> - with "silly" common questions from this list,
> - and with answers in "small tutorial" format (something like
> http://www.mysql.com/articles/dotnet/index.html),
> - well organized (one person to collect / insert the "silly"
questions),
> - easy to search (only search term + search by category),
> - easy to maintain (e. g. with user comments, like the English
manual),
> - even easier to use as a referer than the MySQL manual when answering
> questions?
>
> Flame me if there _is_ a FAQ like this. At least I didn't find it at
> MySQL.com, which is most probably the first place a new MySQL user
would
> look for it.
>
> I am the German translator of the official MySQL manual, so I could
> offer to translate as much as I can from the FAQ into German.
>
> Regards,
> --
>   Stefan Hinz <[EMAIL PROTECTED]>
>   Geschäftsführer / CEO iConnect GmbH 
>   Heesestr. 6, 12169 Berlin (Germany)
>   Tel: +49 30 7970948-0  Fax: +49 30 7970948-3
>
> - Original Message -
> From: "Cal Evans" <[EMAIL PROTECTED]>
> To: "Paul DuBois" <[EMAIL PROTECTED]>; "Adam Wi´ckowski"
> <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Sunday, December 29, 2002 6:00 PM
> Subject: RE: An Idea
>
>
> > Because that's the way that (FoxPro, Access, Paradox, insert name of
> > favorite desktop RDBMS here) did it and I can't make the jump to a
> real
> > server based RDBMS!
> >
> 

Re: MySQL 4.0.7 is released

[[EMAIL PROTECTED] via news-to-mail gateway]

In article <[EMAIL PROTECTED]>, Andreas  <[EMAIL PROTECTED]> wrote:
>Hsiao Ketung Contr 61 CS/SCBN wrote:
>
>> Who would have the need to use src version of installation.
>> I imagine that src version give user more options for customizing MySql.
>
>sure ... but do you feel anythig lacking ?

I know two reasons at least (because I have done both):

1. The site uses an unconventional directory structure by choice;
   have to run configure manually to get the directory structure
   right.

2. Previous installations from RPM gave upgrade/dependency troubles;
   decided to use source to make sure that MySQL works at the site.


-- 
Ambrose Li  <[EMAIL PROTECTED]>
http://ada.dhs.org/~acli/cmcc/  http://www.cccgt.org/

DRM is theft - We are the stakeholders

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

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

front ends?

[Richard Nagle]

what would be the easy to use, gui front end for
mySQL 3.23.53 (for Mac OS X )

Thanks Again,
Richard


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

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

manual for 3.23.53 pdf?

[Richard Nagle]

looking for a pdf manual for mysql 3.23.53
anyone?

Thanks-
Richard



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

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

mysqlgui, win32, delete keypress on table row in results window hangs.

[daniel somerville]

Using: Win32 static binary of MySQLGUI 1.7.5-2 
   on win98SE

Mysql 4.04-beta serving on win2000


run MySQLgui

select * from test;

displays table correctly in mysql results window

-click-select a row in the MySQL results display
-press the delete key (intent is to delete that row)
-hang,
 CTRL-ALT-DEL, MySQL results [not responding],End Task.

Dan Somerville.




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

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

COUNT() two tables

[Alliax]

Hello,
I have been unable to get a better result than the multiplication of 2
tables where I want the addition.
I explain :
3 tables, one for genre, one for html and one for shockwave
html and shockwave have foreign key genreID

I want to count how many html and shockwave entries have the same genreID,
so I can have as a result :
genreID ! nbgenre
1   ! 6
2   ! 2
3   ! 8

where for exemple genreID 1 has 4 html and 2 shockwave entries.

Here's one query I've tested and that doesn't return the addition of html
and shockwave entries, but their multiplication !!!?? (in the exemple I
obtain for genbreID 1 => 4 * 2 = 8 instead of 6)

$query = "SELECT COUNT(*) as nbgenre, g.intitule, g.genreID
FROM genre g, html h, shockwave s
WHERE g.genreID = s.genreID
AND g.genreID = h.genreID
GROUP BY g.intitule, g.genreID
ORDER BY nbgenre DESC, g.intitule

I've tried using LEFT JOIN from exemples on the web, but I don't have enough
experience in SQL to use them correctly in my own queries, and I am not sure
it would solve the problem or if it's just another way of obtaining the same
(wrong) result.

Cheers,
Damien



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

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

Re: Replication priority / speed

[Matt Sturtz]

Hello, Jeremy, et al--  Thanks for the reply before...  Further questions:


>> Is it possible to set either set the priority ('nice') of the Slave
>> thread down so it doesn't do that?
>
> The slave thread only?  No, not really.  You could nice MySQL when you
> start it up.  But I'm not sure how much effect (positive or negative)
> that'd have.

When I run "show [full] processlist", there's an "Id" column, but it
doesn't corrospond with the Unix PID of the process (on OS's that use a
seperate PID for each thread-- like Linux does)...  Is there any way
to(easilly) figure out which PID is handling the slave thread, so that I
might re-nice it after it's already been started up?

>> Or, alternatly, is there a way to limit the slave thread to only "X"
>> bin-log transactions per second?
>
> There is not.

Any plan to add this feature?  I would think it'd be useful...

> Are your updates already well optimized?  If you're doing enough work to
> cause noticeable speed problems, I'd double-check that if you
> haven't already.

MyTOP says our key efficiency is 97.35%, with an average of 1.24 q/sec (on
the master--  most queries are done directly on the slave, with only
updates happening on the master).  We've optimized things as best we can. 
The problem is our customers are allowed to bulk-load keywords into our
database, which causes about 4 large tables to be updated quite a bit. 
Whenever this happens, the slaves struggle to get caught back up...

Thanks again,

-Matt Sturtz-



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

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: Trying to find a .zip binary install for Windows 2000 for 4.0.7 gamma

[Mark Goodge]

At 11:41 30/12/2002 -0600, Cal Evans wrote:

Is there a problem with your mail server or do you keep re-sending this
message? If the latter, please stop. Those that have read the message do not
have an answer.  Continuing to spam this overloaded list with the same
request will simply make those who may know the answer less likely to help
you.


Not to mention that the requested file is right there on the site! You'd 
have to be pretty dumb to miss it, IMO.

But, for the benefit of the dumb, here it is:

http://www.mysql.com/downloads/mysql-standard-4.0.html

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

duplicate entry on key 1

[Jason Eng]

Help!

I have a newbie question.

I have a problem with a database which gives a  "duplicate entry on key 
1" error message when I do a select on a record that I have just 
deleted.

my key column is shown like this in the create statement:

Create table 'mydb' (



'MyKeyField' varchar(30) default '',
'another_field' varchar(30) default '',
'last_field' varchar(16) default '',
UNIQUE KEY 'idx_MyKeyField' ('MyKeyField')
) Type=MyISAM

I am using MySQL 3.23.53 on MacOS X 10.2.2, with PhP and Lasso

Thanks for any help,

Jason



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

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

Re: replication

[Arthur Fuller]

Is there in *nix an implementation of GUIDs? If the MS math is too be
believed, GUIDs are guaranteed unique for the next century. No matter how
many monkeys are at the typewriters. Supposing a) that there is a *nix GUID
implementation, what's the right column type to use within MyISAM and
InnoDB? Assuming that anyone besides me finds this interesting, will MySQL
ever support GUID column types?

Arthur

- Original Message -
From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
To: "Maxime LEMAIRE" <[EMAIL PROTECTED]>
Cc: "Mysql" <[EMAIL PROTECTED]>
Sent: Monday, December 30, 2002 10:56 AM
Subject: Re: replication


> On Mon, Dec 30, 2002 at 08:37:13AM +0100, Maxime LEMAIRE wrote:
> > Hi,
> >
> > I would like to know how mySQL manage the identity (auto increment
> > counter) in a replication environment when 2 servers are both master
> > on the same database.
>
> In that situation, you really need to generate your own primary keys.
> Using auto-increment with a dual-master setup is asking for trouble.
> It'll fail sooner or later, leaving you two servers that no longer
> agree on primary keys.
>
> Jeremy
> --
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
>
> MySQL 3.23.51: up 15 days, processed 549,176,147 queries (411/sec. avg)
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


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

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: query requiring two results from one table?

[Peter Leitch]

select g.gameid, g.homeid, h.name, g.awayid, a.name, g.datetime
from Games g, Teams h, Teams a
where g.datetime > @min_dt
 and g.datetime < @max_dt
 and h.teamid = g.homeid
 and a.teamid = g.awayid


On Mon, 30 Dec 2002 11:29:45 -0600, <[EMAIL PROTECTED]> wrote:


Hello,
This is my first post to the list, so if I am asking in the wrong place, 
I apologize.

I've got some trouble putting together a query with the following tables

Games:  Teams:

gameid  teamid
homeid  name
awayid
datetime

i want to get all games within a certain timeframe, but also retrieve the 
team names for both the homeid and awayid (these are both links to the 
teams.teamid field).
Not sure how to get two results from the same table in one query.

Can anybody point me in the right direction.
thanks
gf


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

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail 
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

Embedded MySQL

[·N¬Ã ÃC]

Hi,
   Since libmysqld don't use sockets or TCP/IP,
what's the mechanism it uses when client and
server communicate with each other? Thanks for
reply in advance.

Regards.
-- 
___
Get your free email from http://mymail.mailasia.com

Powered by Outblaze

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

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

replace ... select (why showing duplicates?)

[Qunfeng Dong]

Hi, 

When I do "replace table2 select (cols) table1 where
clause" to update some records in the table2,
sometimes I got the something like following
indicating Duplicates records being detected. 

---
Query OK, 122259 rows affected (1 min 18.28 sec)
Records: 122259  Duplicates: 122254  Warnings: 0
---

Can I safely ignore such "warnings"? The thing bothers
me is that the records selected from the table1 are
NOT duplicate with the existing records in the table2,
but still showing the "Duplicates"? They are NOT
duplicate because the count(*) with the whereclause
returns 0 records before the above "replace ...
select"
mysql> select count(*) from table2 where clause;
+--+
| count(*) |
+--+
|0 |
+--+


I am using mysql3.23.49 Linux redhat7.3

Thanks!

Qunfeng Dong  

__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: NPTL and MySQL

[Jeremy Zawodny]

On Sun, Dec 29, 2002 at 01:52:34PM +0100, Richard Pijnenburg wrote:
> It seems that no one has answered yet.
> Does any one know some thing about it?

I've not seen much information about NPTL and MySQL.  There are brief
discussions now and then but nothing substantial--mostly speculation
or "someone should try that" stuff.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 15 days, processed 551,704,526 queries (411/sec. avg)

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

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

RE: Trying to find a .zip binary install for Windows 2000 for 4.0.7 gamma

[Cal Evans]

Is there a problem with your mail server or do you keep re-sending this
message? If the latter, please stop. Those that have read the message do not
have an answer.  Continuing to spam this overloaded list with the same
request will simply make those who may know the answer less likely to help
you.

=C=

*
* Cal Evans
* The Virtual CIO
* http://www.calevans.com
*


-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 30, 2002 11:12 AM
To: [EMAIL PROTECTED]
Subject: Trying to find a .zip binary install for Windows 2000 for 4.0.7
gamma


I couldn't locate a .zip binary version for Windows 2000 installation.

All I saw was the source installation:
Cygwin 1.3.9 Source Download (tar.bz2) located at
http://www.mysql.com/downloads/mysql-max-4.0.html

Did I miss it somewhere?

I would like to get a .zip file that contains the standard setup.exe file
and uses the standard Windows installation procedure.
Is there one available at this time from MySQL AB?

Thanks,

Rick

Rick Faircloth,
Prism Productions

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

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: query requiring two results from one table?

[Greg . Froese]

Thanks Matthew.
that works perfectly.

I totally forgot you can just select the table twice.
thanks for the quick response.
gf





"Matthew Smith" <[EMAIL PROTECTED]>
12/30/2002 11:34 AM
Please respond to msmith

 
To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
cc: 
Subject:RE: query requiring two results from one table?


Hi,

Your query will need to look like:

select
   G.datetime, A.name, B.name
from
   Games as G,
   Teams as A,
   Teams as B
where
   A.teamid = G.homeid and
   B.teamid = G.awayid and
   G.datetime < ? and
   G.datetime > ?
order by G.datetime

would be a good place to start


Regards


M
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 30 December 2002 17:11
To: [EMAIL PROTECTED]
Subject: query requiring two results from one table?


Hello,
This is my first post to the list, so if I am asking in the wrong place, I
apologize.

I've got some trouble putting together a query with the following tables

Games:  Teams:

gameid  teamid
homeid  name
awayid
datetime

i want to get all games within a certain timeframe, but also retrieve the
team names for both the homeid and awayid (these are both links to the
teams.teamid field).
Not sure how to get two results from the same table in one query.

Can anybody point me in the right direction.
thanks
gf

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

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: query requiring two results from one table?

[Cal Evans]

Your structure is flawed for this kind of query.

Games should be:
Games
---
gameID
===
teamID (FK to teams)
dateTime (datetime)
homeAwayFlag char(1) // either H or A
---

This way you could use something like
select g.gameid,
   g.teamID,
 g.homeAwayFlag,
 g.dateTime,
 h.teamid,
 h.name
from Games g left join Teams t on g.teamID = h.teamID
order by dateTime, homeAwayFlag

As it is (IMHO) your only choice is to use 2 SELECT statements and a UNION
(Ugly solution)

select g.gameid,
   g.teamID,
 g.homeID,
 null as awayID,
 g.dateTime,
 h.teamid,
 h.name
from Games g left join Teams t on g.teamID = h.homeID
UNION
select g.gameid,
   g.teamID,
 null as homeID,
 g.awayID,
 g.dateTime,
 h.teamid,
 h.name
from Games g left join Teams t on g.teamID = h.awayID
order by dateTime

I've not tested this but it should point you in the right direction. (But
again, changing your structure is a much better solution.)

HTH,
=C=
*
* Cal Evans
* The Virtual CIO
* http://www.calevans.com
*


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 30, 2002 11:11 AM
To: [EMAIL PROTECTED]
Subject: query requiring two results from one table?


Hello,
This is my first post to the list, so if I am asking in the wrong place, I
apologize.

I've got some trouble putting together a query with the following tables

Games:  Teams:

gameid  teamid
homeid  name
awayid
datetime

i want to get all games within a certain timeframe, but also retrieve the
team names for both the homeid and awayid (these are both links to the
teams.teamid field).
Not sure how to get two results from the same table in one query.

Can anybody point me in the right direction.
thanks
gf

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

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: query requiring two results from one table?

[Matthew Smith]

Hi,

Your query will need to look like:

select
   G.datetime, A.name, B.name
from
   Games as G,
   Teams as A,
   Teams as B
where
   A.teamid = G.homeid and
   B.teamid = G.awayid and
   G.datetime < ? and
   G.datetime > ?
order by G.datetime

would be a good place to start


Regards


M
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 30 December 2002 17:11
To: [EMAIL PROTECTED]
Subject: query requiring two results from one table?


Hello,
This is my first post to the list, so if I am asking in the wrong place, I
apologize.

I've got some trouble putting together a query with the following tables

Games:  Teams:

gameid  teamid
homeid  name
awayid
datetime

i want to get all games within a certain timeframe, but also retrieve the
team names for both the homeid and awayid (these are both links to the
teams.teamid field).
Not sure how to get two results from the same table in one query.

Can anybody point me in the right direction.
thanks
gf

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

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: nested transactions?

[Heikki Tuuri]

Thad,

- Original Message -
From: "Thad Humphries" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Monday, December 30, 2002 4:09 PM
Subject: nested transactions?


> Any word when/if MySQL might support nested transactions?  I have a large
> Oracle ESQL application (36K lines) that I wish to port to an open source
> database.  This application makes frequent use of nested
> transactions--writing to the log, updating other records based on the
> present cursor, etc.--which is not supported by MySQL or PostgreSQL.  It

I think Oracle does not have nested transactions but does have savepoints?

Transaction savepoints are already implemented internally inside InnoDB. The
MySQL support for the syntax might come around August 2003. If someone is
willing to sponsor the implementation, they may come quicker.

> is supported by Firebird (http://firebird.sourceforge.net/) but Firebird
> is a lot more work to program.
> --
> 
> Thad Humphries  "...no religious test shall ever be required
> Web Development Manager  as a qualification to any office or public
> Phone: 540/675-3015, x225trust under the United States." -Article VI

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com

sql query



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

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

query requiring two results from one table?

[Greg . Froese]

Hello,
This is my first post to the list, so if I am asking in the wrong place, I 
apologize.

I've got some trouble putting together a query with the following tables

Games:  Teams:

gameid  teamid
homeid  name
awayid
datetime

i want to get all games within a certain timeframe, but also retrieve the 
team names for both the homeid and awayid (these are both links to the 
teams.teamid field).
Not sure how to get two results from the same table in one query.

Can anybody point me in the right direction.
thanks
gf


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

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

Binary Data and BLOB Problem

[Kevin Wixson]

Having trouble with my first MySQL database. I want to upload and install 
image files to a database for online sample photo gallery for our 
customers. The system I've created so far sometimes works, sometimes 
doesn't. It seems to depend on the size of the file being uploaded, but 
tests have been inconclusive. Having trouble diagnosing the problem since 
the error is most often "Internal Server Error," which doesn't tell me 
much. Otherwise I get:


Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]Lost connection to MySQL 
server during query

/shooters/Insert.asp, line 148

The relevant code is:

' Checking to make sure if file was uploaded
If fileSize > 0 Then

set rsImage = Server.CreateObject("ADODB.Recordset")
rsImage.ActiveConnection = MM_connShooters_STRING
rsImage.Source = "select * from web_lab.Images limit 0"
rsImage.CursorType = adOpenKeyset
rsImage.CursorLocation = adUseClient
rsImage.LockType = adLockPessimistic
rsImage.Open()
rsImage_numRows = 0

' Adding data
rsImage.AddNew
rsImage("userID") = userID
rsImage("imageType") = contentType
rsImage("imageSize") = fileSize
rsImage("imageName") = fileName
rsImage("imageCaption") = caption
Do While lngOffset < fileSize
varChunk = LeftB(RightB(fileData, fileSize - lngOffset), conChunkSize)
rsImage("imageData").AppendChunk varChunk
lngOffset = lngOffset + conChunkSize
Loop

ERROR-->rsImage.Update

'adoImageStream.Close
rsImage.Close
Set rsImage = Nothing

Response.Write "File was successfully uploaded..."
Response.Write ""
Else
Response.Write "No file was selected for uploading"
Response.Write "..."
End If

My connection string is:

MM_connShooters_STRING = "driver={MySQL ODBC 3.51 Driver}; 
database=[name];uid=[id];pwd=[pass];OPTION=16427;"

The constant conChunkSize is 100. I have checked the max packet size (set 
to 1550) and as you can see, set cursors to client.

Alternatively, I have also tried to use the stream object, but I haven't 
had any luck there either. I'm sure I'm not doing it right.



Thank you,

Kevin Wixson
IT Manager
Norman Camera & Video

(616) 567-5552
1-800-900-6676
Fax: (616) 343-6410
e-mail: [EMAIL PROTECTED]

Visit us on the web at: http://www.normancamera.com  



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

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

MySQL 3.23 compile error on HPUX IPF

[Zengfa Gao]

Hi,

I am trying to compile MySQL 3.23 on HPUX 11.23 IPF.

I am using gcc 3.0. gcc 2.95 is not available on HPUX
11.23.


CC=gcc \
CXX=g++ \
CFLAGS="-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT" \
CXXFLAGS="-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT" \
CPPFLAGS="-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT" \
LDFLAGS="-L/usr/local/lib" \
./configure  \
 --prefix=/usr/local/mysql \
 --exec-prefix=/usr/local/mysql \
 --with-libwrap=/usr/local \
 --with-low-memory


gmake

gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include
-I../include  -D_POSIX_C_SOURCE=199506L -D_HPUX_SOURCE
-D__hpux__ -D_REENTRANT  -O3 -DDBUG_OFF
-D_POSIX_C_SOURCE=199506L -D_HPUX_SOURCE -D__hpux__
-D_REENTRANT  -DHAVE_BROKEN_PREAD -DDONT_USE_FINITE
-DHAVE_BROKEN_GETPASS -DNO_FCNTL_NONBLOCK
-DDO_NOT_REMOVE_THREAD_WRAPPERS
-DHAVE_BROKEN_PTHREAD_COND_TIMEDWAIT -c strxmov.c
In file included from /usr/include/pthread.h:7,
 from ../include/global.h:97,
 from strxmov.c:33:
/usr/include/sys/pthread.h:1039: parse error before
'*' token
gmake[2]: *** [strxmov.o] Error 1
gmake[2]: Leaving directory
`/var/mysql-3.23.52/strings'
gmake[1]: *** [all-recursive] Error 1
gmake[1]: Leaving directory `/var/mysql-3.23.52'
gmake: *** [all-recursive-am] Error 2


Does any one have some ideas?

Thanks!

Zengfa


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: replication

[Rick Faircloth]

Hi, Jeremy.

What if one is the master and one is a slave, but both are used in
production environments?  Is it possible for a slave to be used as a
live production database or is it simply for backup?

If they can both be used as live production databases, how can they
be synced with replication?

Rick

Rick Faircloth,
Prism Productions


-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 30, 2002 10:57 AM
To: Maxime LEMAIRE
Cc: Mysql
Subject: Re: replication


On Mon, Dec 30, 2002 at 08:37:13AM +0100, Maxime LEMAIRE wrote:
> Hi,
>
> I would like to know how mySQL manage the identity (auto increment
> counter) in a replication environment when 2 servers are both master
> on the same database.

In that situation, you really need to generate your own primary keys.
Using auto-increment with a dual-master setup is asking for trouble.
It'll fail sooner or later, leaving you two servers that no longer
agree on primary keys.

Jeremy
--
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 15 days, processed 549,176,147 queries (411/sec. avg)

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

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


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

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: Update syntax

[Cal Evans]

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#UPD
ATE

Last line in 6.4.5

=C=

*
* Cal Evans
* The Virtual CIO
* http://www.calevans.com
*


-Original Message-
From: Jim Racster [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 30, 2002 10:06 AM
To: '[EMAIL PROTECTED]'
Subject: Update syntax


Hi All,

 With a database that contains tables PetInv and Purchases:

mysql> update PetInv
-> set PetInv.Qty = PetInv.Qty + Purchases.Qty
-> where PetInv.ItemNo = Purchases.ItemNo;
ERROR 1109: Unknown table 'Purchases' in where clause

As you can see I'm trying to add purchase quantities to existing quantities,
but obviously I'm not doing it right.  Any ideas?

TIA,
Jim

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

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

Trying to find a .zip binary install for Windows 2000 for 4.0.7 gamma

[Rick Faircloth]

I couldn't locate a .zip binary version for Windows 2000 installation.

All I saw was the source installation:
Cygwin 1.3.9 Source Download (tar.bz2) located at
http://www.mysql.com/downloads/mysql-max-4.0.html

Did I miss it somewhere? 

I would like to get a .zip file that contains the standard setup.exe file
and uses the standard Windows installation procedure.
Is there one available at this time from MySQL AB?

Thanks,

Rick

Rick Faircloth,
Prism Productions

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

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

query requiring two results from one table?

[Greg . Froese]

Hello,
This is my first post to the list, so if I am asking in the wrong place, I 
apologize.

I've got some trouble putting together a query with the following tables

Games:  Teams:

gameid  teamid
homeid  name
awayid
datetime

i want to get all games within a certain timeframe, but also retrieve the 
team names for both the homeid and awayid (these are both links to the 
teams.teamid field).
Not sure how to get two results from the same table in one query.

Can anybody point me in the right direction.
thanks
gf

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

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: seagate crystal reports with mysql

[Scott Pippin]

Yes you can.  There are a few changes you have to make to the registry
to get it to work.  Let me know off list if you need any more help.  We
use mysql as our databse and Crystal Reports as our reporting engine.



Scott Pippin
[EMAIL PROTECTED]

>>> geeta varu <[EMAIL PROTECTED]> 12/28/02 03:16AM >>>
can i create reports using 
seagate crystal reports with 
mySQL as backend.
regards,
gj

__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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

Trying to locate a .zip binary installation for Windows 2000 for 4.0.7 gamma

[Rick Faircloth]

I couldn't locate a .zip binary version for Windows 2000 installation.

All I saw was the source installation:
Cygwin 1.3.9 Source Download (tar.bz2) located at
http://www.mysql.com/downloads/mysql-max-4.0.html

Did I miss it somewhere? 

I would like to get a .zip file that contains the standard setup.exe file
and uses the standard Windows installation procedure.
Is there one available at this time from MySQL AB?

Thanks,

Rick


Rick Faircloth,
Prism Productions

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

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

Converting signed and unsigned integers

[Peter Hicks]

All,

I have a table which includes IP addresses converted from dotted quads 
to signed integers using PHP's ip2long() function.

To greatly simplify some of my code, I want to convert these signed 
integers to IP addresses on my MySQL server.

The problem I'm up against is that PHP's ip2long uses signed integers, 
and inet_aton uses unsigned integers.  If all my IP addresses were below 
127.255.255.255, this would be fine :)

Documentation suggests that converting signed to unsigned (and vice 
versa) is available in MySQL 4.0.2 through the CAST() function, but I'm 
running 3.23.54 at present.

Can anybody help me?

Best wishes,


Peter.


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

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

Update syntax

[Jim Racster]

Hi All,

 With a database that contains tables PetInv and Purchases:

mysql> update PetInv
-> set PetInv.Qty = PetInv.Qty + Purchases.Qty
-> where PetInv.ItemNo = Purchases.ItemNo;
ERROR 1109: Unknown table 'Purchases' in where clause

As you can see I'm trying to add purchase quantities to existing quantities,
but obviously I'm not doing it right.  Any ideas?

TIA,
Jim

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

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

Trying to locate a zip binary Windows installation for 4.0.7 Gamma

[Rick Faircloth]

I couldn't locate a .zip binary version for Windows 2000 installation.
All I saw was the source installation:

Cygwin 1.3.9 Source Download (tar.bz2) located at

http://www.mysql.com/downloads/mysql-max-4.0.html

Did I miss it somewhere? 

I would like to get a .zip file that contains the standard setup.exe file
and uses the standard Windows installation procedure.

Is there one available at this time from MySQL AB?

Thanks,

Rick


Rick Faircloth,
Prism Productions

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

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

Re: replication

[Jeremy Zawodny]

On Mon, Dec 30, 2002 at 08:37:13AM +0100, Maxime LEMAIRE wrote:
> Hi,
> 
> I would like to know how mySQL manage the identity (auto increment
> counter) in a replication environment when 2 servers are both master
> on the same database.

In that situation, you really need to generate your own primary keys.
Using auto-increment with a dual-master setup is asking for trouble.
It'll fail sooner or later, leaving you two servers that no longer
agree on primary keys.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 15 days, processed 549,176,147 queries (411/sec. avg)

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

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

Is it a bug?

[Walter Procopio]

Hi,

I have installed mysql max 4.0.4 beta on a Suse (ver. 8.0) linux server and
I have created a database with this schema:


CREATE TABLE ecoras (
  rsnum tinyint(4) NOT NULL,
  rsori tinyint(1) NOT NULL,
  rscod int(11) NOT NULL,
  rsid int(11) NOT NULL auto_increment,
  rsdat date NOT NULL default '-00-00',
  rstda date NOT NULL default '-00-00',
  rstcd int(11) NOT NULL default '0',
  rstec int(11) default NULL,
  rstes varchar(50) NOT NULL default '',
  rstpg varchar(6) NOT NULL default '0',
  rsart varchar(250) NOT NULL default '',
  rsimm varchar(12) NOT NULL default '',
  rsimr varchar(12) default NULL,
  rsrub int(11) default NULL,
  rsrub2 int(11) default NULL,
  rsord int(11) default NULL,
  rspst int(11) default NULL,
  rsgrd char(3) default NULL,
  rsric smallint(6) default NULL,
  rsdis smallint(6) default NULL,
  rssel smallint(6) default NULL,
  rscom1 varchar(100) default NULL,
  rscom2 varchar(250) default NULL,
  rscom3 varchar(250) default NULL,
  rssot varchar(100) default NULL,
  rsoch varchar(100) default NULL,
  rsgio1 int(11) default NULL,
  rsgio2 int(11) default NULL,
  rsint1 int(11) default NULL,
  rsint2 int(11) default NULL,
  rsarg1 int(11) default NULL,
  rsarg2 int(11) default NULL,
  rsarg3 int(11) default NULL,
  rsnom1 int(11) default NULL,
  rsnom2 int(11) default NULL,
  rsnom3 int(11) default NULL,
  rstip char(1) default NULL,
  rsind tinyint(1) default NULL,
  datsys date NOT NULL default '-00-00',
  timsys time NOT NULL default '00:00:00',
  rsdel enum('Y','N') default 'N',
  PRIMARY KEY  (rsnum,rsori,rscod),
  UNIQUE KEY rsid (rsid),
  KEY rsdat (rsdat,rstda,rstes,rstpg),
  KEY rstda (rstda,rstcd,rstpg,rsart),
  KEY rstcd (rstcd,rstda,rstpg),
  KEY rstcd2 (rstcd,rsdat,rstpg),
  KEY rsimm (rsimm),
  KEY rsgio1 (rsgio1),
  KEY rsgio2 (rsgio2),
  KEY rsint1 (rsint1),
  KEY rsint2 (rsint2),
  KEY rsarg1 (rsarg1),
  KEY rsarg2 (rsarg2),
  KEY rsarg3 (rsarg3),
  KEY rsnom1 (rsnom1),
  KEY rsnom2 (rsnom2),
  KEY rsnom3 (rsnom3),
  KEY rsind (rsind,datsys,timsys)
) TYPE=MyISAM;

# 

CREATE TABLE ecorub (
  rsnum tinyint(4) NOT NULL,
  rsrub int(11) NOT NULL auto_increment,
  rsdes varchar(50) NOT NULL default '',
  rssig varchar(15) NOT NULL default '',
  rsrif int(11) default NULL,
  rsrif2 int(11) default NULL,
  rsrif3 int(11) default NULL,
  rsrif4 int(11) default NULL,
  rsrif5 int(11) default NULL,
  rsord int(11) default NULL,
  rsrms char(1) default NULL,
  rstmp int(11) default NULL,
  PRIMARY KEY  (rsnum,rsrub),
  KEY rsord (rsnum,rsord,rsdes,rsrub),
  KEY rsdes (rsnum,rsdes,rsrub),
  KEY rssig (rsnum,rssig,rsrub),
  KEY rsrif (rsnum,rsrif,rsrub),
  KEY rsrif2 (rsnum,rsrif2,rsrub),
  KEY rsrif3 (rsnum,rsrif3,rsrub),
  KEY rsrif4 (rsnum,rsrif4,rsrub),
  KEY rsrif5 (rsnum,rsrif5,rsrub),
  KEY rsrms (rsnum,rsrms,rsrub)
) TYPE=MyISAM;

# 

CREATE TABLE ecotes (
  rstcd int(11) NOT NULL auto_increment,
  rstes varchar(100) NOT NULL default '',
  rstec int(11) default NULL,
  rstmp int(11) default NULL,
  rsord int(11) default NULL,
  rsdir varchar(100) default NULL,
  rscon int(11) default NULL,
  rsweb enum('Y','N') default 'Y',
  rsdin datetime default NULL,
  rsdup datetime default NULL,
  PRIMARY KEY  (rstcd),
  KEY ecotes_2 (rstes,rstcd),
  KEY ecotes_3 (rstec,rstcd),
  KEY ecotes_4 (rsord,rstes,rstcd),
  KEY rsdir (rsdir,rstcd),
  KEY rscon (rscon,rstcd),
  KEY rsdin (rsdin,rstcd),
  KEY rsdup (rsdup,rstcd),
  KEY rstmp (rstmp,rstcd)
) TYPE=MyISAM;

# 

CREATE TABLE ecotxt (
  rsori tinyint(1) NOT NULL  default '0',
  rscod int(11) NOT NULL default '0',
  rstxt mediumtext,
  PRIMARY KEY  (rsori, rscod),
  FULLTEXT KEY rstxt (rstxt)
) TYPE=MyISAM;

# 


And I try to execute this query:


SELECTecoras.rstda, ecotes.rstes, ecoras.rsart, ecoras.rsimm,
ecorub.rsdes, ecoras.rstpg, ecoras.rsdat, ecoras.rsrub
FROM   ecoras,  ecotxt
LEFT JOIN ecotes ON ecoras.rstcd = ecotes.rstcd
LEFT JOIN ecorub ON ecoras.rsnum = ecorub.rsnum and ecoras.rsrub =
ecorub.rsrub
WHEREecoras.rsori = ecotxt.rsori
AND ecoras.rscod = ecotxt.rscod
AND (rsdat >= '2002-12-01') and (rsdat <= '2002-12-23')
AND (match(ecotxt.rstxt) against('JAVA' IN BOOLEAN MODE))
ORDER BY ecoras.rspst, ecoras.rstda, ecotes.rstes, ecoras.rstpg,
ecoras.rsart


I have no data in my resultset, but data-base contains data which match
conditions.
If I excute the following query I have a valid resultset.


SELECTecoras.rstda, ecotes.rstes, ecoras.rsart, ecoras.rsimm,
ecorub.rsdes, ecoras.rstpg, ecoras.rsdat, ecoras.rsrub
FROM   ecoras,  ecotxt
LEFT JOIN ecotes ON ecoras.rstcd = ecotes.rstcd
LEFT JOIN ecorub ON ecoras.rsnum = ecorub.rsnum and ecoras.rsrub =
ecorub.rsrub
WHEREecoras.rsnum = 0
AND ecoras.r

Trying to locate a binary install .zip file for 4.0.7 for Windows 2000 installation

[Rick Faircloth]

I couldn't locate a .zip binary version for Windows 2000 installation.
All I saw was the source installation:

Cygwin 1.3.9 Source Download (tar.bz2) located at

http://www.mysql.com/downloads/mysql-max-4.0.html

Did I miss it somewhere? 

I would like to get a .zip file that contains the standard setup.exe file
and uses the standard Windows installation procedure.

Is there one available at this time from MySQL AB?

Thanks,

Rick


Rick Faircloth,
Prism Productions


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

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: style question: "drop database" in an install script

[Arthur Fuller]

I don't know about anyone else, but for this sort of thing I love the
program called DeZign, which is a data-modeling tool. You can "draw" your
tables and their columns and set up relations and so on. DeZign will then
write your code for you.

Personally, I like to keep all my "drop" statements in one script, all my
"create" scripts in another, and all my "populate" scripts in yet another.

Arthur

-Original Message-
From: David T-G [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 27, 2002 8:15 PM
To: mysql users
Subject: style question: "drop database" in an install script


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi, all --

I'm working up the schema for my application and, as you can imagine,
starting over and reloading numerous times :-)  In fact, I intend for the
script to be an install script, run on a new installation of the software
to set things up.

The script starts out

  drop database if exist dbname ;
  create database dbname ; use dbname ;
  create table tablename
  (
...
  ) ;
  ...

to set things up and then continues

  insert into tablename (field,field,field)
( data , data , data ) ,
...
( data , data , data ) ;
  ...

to set up the predefined things (credit card types and so on).

To reload the database and try something new, all I have to do is

  mysql -udroot -p < script.sql

and what was there goes away and is rebuilt fresh.  That's mighty
convenient as I'm building up a table's design and trying it out.

Soo...  When you experienced folks write software that's meant to be
installed from a script or such, do you lead off with a 'drop database'
command, or is that just too dangerous to put in a script and you make
your users clean up any old installation by hand?  This will have to be
run by a root user, so we can figure that this user might have read the
docs and know that he's going to start [over] from scratch, but then,
again, users are users :-)

Meanwhile, is there if/then/else functionailty in SQL so that I can say

  if exist dbname exit "some error" ;
  create database dbname ;
  ...

and not try to create and populate a database on top of an existing one?
Or is there perhaps a "RENAME DATABASE" command (I saw RENAME TABLE) so
that I could rename the old one to get it out of the way but not toss it?


TIA & HAND

mysql query,
:-D
- --
David T-G  * There is too much animal courage in
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED]  -- Mary Baker Eddy, "Science and Health"
http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (FreeBSD)

iD8DBQE+DPsqGb7uCXufRwARAsp8AKC7BuVdyO7Dl5fkbvEM51o+i/BAEgCeI5iI
dI7HQb5oywLHuZIjLxXYZwY=
=QTHL
-END PGP SIGNATURE-

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

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




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

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

[Simon Windsor]

Hi

What else is running on the machine ?

What processes are using the computers resources ? Use top to find out.

What are the memory parameters used in my.cnf ?

The answers to these questions will provide some helpfull answers to your 
problem.

Simon

On Monday 30 Dec 2002 11:16 am, BPF Webmaster wrote:
> I migrated my site from a low end PC mutualized hosting to a brand new
> P3 1GHz 512M server I now manage myself. Unfortunately I found MySQL
> slower now, the time for a PHP page is now about 6 seconds and was about
> .6 before!!!
>
> I haven't done any optimization and the DB is the same, so the size
> (about 10,000 records).
>
> The only 2 weaknesses I saw is that:
> 1- I use a RedHat 7.3 RPM (haven't compiled myself)
> 2- the database folder is on another partition linked from /var/... to
> /home/... but on the same disk (a fast 10Krpm UW-SCSI).
>
> What would you advise?
>
> JM
>
> An example at http://www.labdv.com/forum/index.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

-- 
Simon Windsor
Email: [EMAIL PROTECTED]
Tel: 01454 617689
Mob: 07720 447385

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

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

Optimizing

[BPF Webmaster]

I migrated my site from a low end PC mutualized hosting to a brand new
P3 1GHz 512M server I now manage myself. Unfortunately I found MySQL
slower now, the time for a PHP page is now about 6 seconds and was about
.6 before!!!
 
I haven't done any optimization and the DB is the same, so the size
(about 10,000 records).
 
The only 2 weaknesses I saw is that:
1- I use a RedHat 7.3 RPM (haven't compiled myself)
2- the database folder is on another partition linked from /var/... to
/home/... but on the same disk (a fast 10Krpm UW-SCSI).
 
What would you advise?
 
JM
 
An example at http://www.labdv.com/forum/index.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[2]: temporary and merge tables

[Webmaster (Corin Langosch)]

Hi,

sorry, didn't look at the changelog... ;)
thanks!

corin

Monday, December 30, 2002, 9:29:12 AM, you wrote:
PD> At 9:01 +0100 12/30/02, Webmaster (Corin Langosch) wrote:
>>Hi,
>>
>>when trying to create a temporary table of type merge, mysql
>>2.23.53 fails with an error like this:
>>Can't find file: '#sql24b_776_0.MRG' (errno: 2)
>>
>>when not using the temporary keyword, everything works fine.
>>so i assume this is a missing feature (not documented that this
>>is forbidden/not supported yet) or a bug.

PD> Looks like it's documented to me:

PD> http://www.mysql.com/doc/en/News-3.23.54.html

>>
>>Corin


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

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: Fulltext search: Is there a way to disable the stop words?

[Sergei Golubchik]

Hi!

On Dec 28, John Porter Simons wrote:
> 
> Any system variables I could set, or any parameters I could add to MATCH 
> ... AGAINST query syntax, or any plans to disable stopwords for boolean 
> fulltext searches in future builds?

The answer to the last question is "yes, definitely", to others -
"sorry, but no".

Regards,
Sergei

-- 
MySQL Development Team
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/

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

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

Re: mysqld_safe

[Paul DuBois]

At 16:18 +0800 12/30/02, ecteo wrote:

when I try to start MySQL 4.0.7 in Red Hat Linux 8 by issuing

mysqld_safe

then a line of msg appear

starting mysqld deamon with database from /var/lib/mysql

then the screen stay unchange, and it does not come back to shell -> [abc]#

Does it normal or problem?


Normal.  Invoke it so that it starts in the background:

mysqld_safe &



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

Re: temporary and merge tables

[Paul DuBois]

At 9:01 +0100 12/30/02, Webmaster (Corin Langosch) wrote:

Hi,

when trying to create a temporary table of type merge, mysql
2.23.53 fails with an error like this:
Can't find file: '#sql24b_776_0.MRG' (errno: 2)

when not using the temporary keyword, everything works fine.
so i assume this is a missing feature (not documented that this
is forbidden/not supported yet) or a bug.


Looks like it's documented to me:

http://www.mysql.com/doc/en/News-3.23.54.html



Corin



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

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_safe

[ecteo]

when I try to start MySQL 4.0.7 in Red Hat Linux 8 by issuing

mysqld_safe

then a line of msg appear

starting mysqld deamon with database from /var/lib/mysql

then the screen stay unchange, and it does not come back to shell -> [abc]#

Does it normal or problem?

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

temporary and merge tables

[Webmaster (Corin Langosch)]

Hi,

when trying to create a temporary table of type merge, mysql
2.23.53 fails with an error like this:
Can't find file: '#sql24b_776_0.MRG' (errno: 2)

when not using the temporary keyword, everything works fine.
so i assume this is a missing feature (not documented that this
is forbidden/not supported yet) or a bug.

Corin


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

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