date:20030121

Steve,

 SQL joins are damn slow! I have a simple database of about 37,000
 records in [Court Cases] and 2,000,000 records in [Defendants] (soon
to
 be more).

Please post the structures of your 2 tables (at least, the relevant
parts), and the join query that gets so slow. Otherwise, we can only
guess.

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

- Original Message -
From: Steve Quezadas [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, January 21, 2003 10:30 AM
Subject: Joins are damn slow. . .


 Maybe I'm a bit naive in saying this but. . .

 SQL joins are damn slow! I have a simple database of about 37,000
 records in [Court Cases] and 2,000,000 records in [Defendants] (soon
to
 be more). When I do a search on [Defendants] (the big table) alone, a
 saerch can go in like .7 seconds. But if I do a search on [Defendants]
 joined to [Cases], the search jumps to about 5 seconds.  (yes, I
indexed
 the joining fields and the search terms).

 This bites. . .

 However. I notice that if I do two separate searches it goes quicker
 (about 2.5 seconds combined). I can do a criteria search on defendants
 and then put all the resulting case numbers in a temporary table. Then
 do a join of that temporary table to the much smaller Cases table and
do
 a search on that. I get the same results, and the query time is
halved.

 Ummm, is there any reason why I shouldn't do this? (Other than the
 inelegance of running two queries instaed of one) Do people do stuff
 like this for performance reasons?

 - Steve



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

 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

Mandatory server downtime?

2003-01-21 Thread Clemente

Hi there,
My hosting company tells me that they need to take down the MySQL server
every day for 30 to 60 minutes to make a backup. This is the quote:
Please note that mysql is shutdown daily sometime between 3am and 9 am
US eastern for 30 minutes to an hour. This is get a good backup of all
mysql databases on the server during the daily backup process.

Is this normal? This implies that my dynamic generated web site is down
during a 2.1% to a 4.2% of the time (not counting any other server down
time). I hear that normal down times for web sites are more like 1% or
less.
Is this really necessary? Does MySQL requires to be shutdown to generate
a good backup?
Any comments are welcome. Thanks a lot.

--

Clemente Zamora
GlobalReservas
(http://www.clementezamora.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

Replication.

2003-01-21 Thread Simon Green

Hi
I have two questions about replication on MySQL.

One: How far behind can a SLAVE get on reading the BIN log before not being
able to catch up?
My SLAVE was off for 4 days and it just seams as if it can not catch up.

Two: On SHOW SLAVE STATUS one of the fields in called Skip_conunter. Due to
an error I ran SET SQL_SLAVE_SKIP_COUNTER=n. The number in the Skip_conunter
is about the same number as I set skip to, but every time I do a SHOW SALVE
STATUS it shows a different number. 
What dose Skip_counter mean and so why is the number changing.
Thanks Simon





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

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: Mandatory server downtime?

2003-01-21 Thread Simon Green

There are two ways I use to dynamic backup.
One lock table can copy it. Use some think like hotcopy. The only down side
can be that if the table is very big the lock can queue two may questions. 
The other way it to use replication can make the backup from the slave. 
If it is taking up to an hour are they doing a full systems backup?
Simon

-Original Message-
From: Clemente [mailto:[EMAIL PROTECTED]]
Sent: 21 January 2003 10:44
To: [EMAIL PROTECTED]
Subject: Mandatory server downtime?


Hi there,
My hosting company tells me that they need to take down the MySQL server
every day for 30 to 60 minutes to make a backup. This is the quote:
Please note that mysql is shutdown daily sometime between 3am and 9 am
US eastern for 30 minutes to an hour. This is get a good backup of all
mysql databases on the server during the daily backup process.

Is this normal? This implies that my dynamic generated web site is down
during a 2.1% to a 4.2% of the time (not counting any other server down
time). I hear that normal down times for web sites are more like 1% or
less.
Is this really necessary? Does MySQL requires to be shutdown to generate
a good backup?
Any comments are welcome. Thanks a lot.

--

Clemente Zamora
GlobalReservas
(http://www.clementezamora.com)



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

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

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

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

Re: building 4.1 from source

2003-01-21 Thread Rafal Jank

On Mon, 20 Jan 2003 13:01:55 -0700
Zak Greant [EMAIL PROTECTED] wrote:

 On Mon, Jan 20, 2003 at 01:14:53PM +0100, Rafal Jank wrote:
  Hi,
  
  I've just downloaded version 4.1 from bk and have problem with building
  it. What version of automake is needed? Because after
   aclocal
  I get:
  aclocal: configure.in: 459: macro `AM_PROG_AS' not found in library
 
   Youw will need version 1.5 of automake (see
   http://www.mysql.com/doc/en/Installing_source_tree.html) for
   additional details on the tools required.
 
Thank you - that halped. But now I have another problem - make fails with
following error:

gcc -shared  libmysql.lo password.lo manager.lo get_password.lo errmsg.lo
my_init.lo my_static.lo my_malloc.lo my_realloc.lo my_create.lo my_delete.lo
mf_tempfile.lo my_open.lo my_read.lo my_write.lo errors.lo my_error.lo
my_getwd.lo my_div.lo mf_pack.lo my_messnc.lo mf_dirname.lo mf_fn_ext.lo
mf_wcomp.lo typelib.lo safemalloc.lo my_alloc.lo mf_format.lo mf_path.lo
mf_unixpath.lo my_fopen.lo my_symlink.lo my_fstream.lo mf_loadpath.lo
my_pthread.lo my_thr_init.lo thr_mutex.lo mulalloc.lo string.lo default.lo
my_compress.lo array.lo my_once.lo list.lo my_net.lo charset.lo hash.lo
mf_iocache.lo mf_iocache2.lo my_seek.lo my_pread.lo mf_cache.lo md5.lo sha1.lo
my_getopt.lo my_gethostbyname.lo my_port.lo my_lib.lo strmov.lo strxmov.lo
strxnmov.lo strnmov.lo strmake.lo strend.lo strnlen.lo strfill.lo is_prefix.lo
int2str.lo str2int.lo strinstr.lo strcont.lo strcend.lo bcmp.lo bchange.lo
bmove.lo bmove_upp.lo longlong2str.lo strtoull.lo strtoll.lo llstr.lo
my_vsnprintf.lo ctype.lo ctype-simple.lo ctype-bin.lo ctype-mb.lo
ctype-big5.lo ctype-czech.lo ctype-euc_kr.lo ctype-win1250ch.lo ctype-utf8.lo
ctype-gb2312.lo ctype-gbk.lo ctype-latin1_de.lo ctype-sjis.lo ctype-tis620.lo
ctype-ujis.lo xml.lo dbug.lo vio.lo viosocket.lo viossl.lo viosslfactories.lo
net.lo   -Wl,-soname -Wl, -o .libs/
/usr/bin/ld: cannot open output file .libs/: Is a directory
collect2: ld returned 1 exit status

-- 
_/_/  _/_/_/  - Rafa Jank [EMAIL PROTECTED] -
 _/  _/  _/  _/   _/ Wirtualna Polska SA   http://www.wp.pl 
  _/_/_/_/  _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625
   _/  _/  _/ ==*  http://szukaj.wp.pl *==--

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

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: Is rename table to another database safe for INNODB tables

2003-01-21 Thread Heikki Tuuri

Jannie,

- Original Message -
From: Jannie Qu [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Tuesday, January 21, 2003 1:30 AM
Subject: Is rename table to another database safe for INNODB tables

 sql, query,

 Hi, all,

 As you may knew, mysql will implement rename database in version 4.1
 Right now, I need to rename a database, what I plan to do is rename each
 table (INNODB type, version 3.23.53 on MAC OS) to another new database.

 RENAME TABLE current_db.tbl_name TO new_db.tbl_name;

 Do you think it's safe to (1) do the rename? or I'd better do a (2) import
 all to the new database? Which way is better?

RENAME is safe.

 Thank you,
 Jannie

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

mysql 5

2003-01-21 Thread Dan Rossi

i know this may be an impatient ask , when will mysql 5 maybe release ? , i
was hoping to check out the stored procedures features, php 5 is on dev and
has started to use OO handling which is good to start coding for the future
rollover.


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

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

Re: Maximum SQL size?

* Steve Quezadas 
 Is there  a maximum size to a SQL statement in mySQL? Just wondering.

It's 16M in version 3.23.x, unlimited in 4.0.x, according to:

URL: http://www.mysql.com/doc/en/Packet_too_large.html 

-- 
Roger

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

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

Re: Re: indexing a full text

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Victoria, et al --

...and then Victoria Reznichenko said...
% 
% On Monday 20 January 2003 16:16, David T-G wrote:
%  create table pieces
%(
...
%  words text (4095) ,
%  fulltext index (words(4095))
%) ;
% 
% David, words text (4095) is incorrect definition of column. TEXT is a 
% variable-length column type with the maximum size 64K:
%   http://www.mysql.com/doc/en/Storage_requirements.html

Ahhh...  I get it.  I took from the docs that you could specify how much
you wanted to assign for a TEXT.  I know that I won't need the full 64k
for the text of the piece, though tinytext is too small, and so I wanted
to save space that I knew would be wasted.

Thanks for the pointer!


% 
% You should use
% [skip]
% ...
% words TEXT,
% FULLTEXT(words)

And because it's fulltext rather than just index I don't have to specify
the length of what I want to index, right?


Thanks  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://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

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

iD8DBQE+LTn2Gb7uCXufRwARAhweAKDJUay4sCeepIqR7UfB833HO8JvXACgxw+i
lzW/PZ1lbq2qimebhG8wK0I=
=A6JK
-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

Copy database question

2003-01-21 Thread Tom Nickels

Hi everybody,

I am new on mysql. I use phpadmin for adminstration.

My problem:

I have Database 1, table userdata, field username

I need to copy the above mentioned data to:

Database2, Table chat_data, field nick

I have no clue how to do it! Can anybody tell me the string? Thanks for
help!

Tom


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

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

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thomas, et al --

...and then Thomas Spahni said...
% 
% On Mon, 20 Jan 2003, David T-G wrote:
% 
...
%create table pieces
%(
%  # ID number
%  id smallint unsigned not null default 0 auto_increment primary key ,
%  title char(50) ,
%  index (title) ,
%  words text (4095) ,
%  fulltext index (words(4095))
%) ;
% 
% David,
% 
% try the following:
% 
% create table pieces (
%   id smallint unsigned not null auto_increment primary key,
%   title varchar(50),
%   words text,
%   key (title(16)),

It took me a minute to figure out this one...  Only well down in the
'create table syntax' section does it state that key is a synonym for
index :-)


%   fulltext (words)
% ) TYPE=MYISAM;
% 
% Are you sure that smallint is enough for your index? Do you really need to

I've thought about that a bit, but don't know if there is a more 'right'
answer that comes with experience...  In this case and other cases I've
had so far, it is; that will give me 64k.  Perhaps it's not worth thinking
about the few bytes a smallint will save me; I already use smallint even
where tinyint will do just fine for the sake of consistency (all of my
ID columns are smallint, so foreign key references can automatically be
smallint).  Do you suggest that perhaps I just go with int or bigint for
my IDs, again for consistency but now across all of my work rather than
just guaranteed within a database?


% index the full title?

I think so...  If the title is Let Not the Wise Man Glory in his Wisdom
and I want to be able to use 'wisdom' as a keyword for my search, it
needs to be indexed, right?


% 
% Thomas Spahni
% -- 
% filter: mysql, query


Thanks  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://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

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

iD8DBQE+LTunGb7uCXufRwARAqpIAJ95Fhu5f+HH3tcicF+yUku0pfvjawCbBx1P
uM4AIpHzJnuT8T7ZU/mhP+8=
=dZi/
-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: How2unsubscribe?

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Sam --

...and then [EMAIL PROTECTED] said...
% 
% Hi list,

Hi!


% 
% I have migrated to SQLServer, so I wonder if some one can direct me how to 
unsubscribe please.

No problem.  Take a left out of your office, go to the end of the hall,
turn right, and keep going.  You'll merge into the main corridor, where
you'll probably find other users.  Ask one of them to explain how to read
the full body of an email and then come back and finish this note.


% 
% Thanks.

No problem.  Don't let it become a habit, though, unless you purchas an
UnSubscribe Support Services Contract.


% 
% Sam


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://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

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

iD8DBQE+LT0NGb7uCXufRwARAkIGAKCIZPwYU7pvxdz+GycSlUkynu6RugCfX0/L
Hu6UG8JfiXFPzBO4Ox/Qw00=
=PNJS
-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: Mandatory server downtime?

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Clemente --

...and then Clemente said...
% 
% Hi there,

Hi!


% My hosting company tells me that they need to take down the MySQL server
% every day for 30 to 60 minutes to make a backup. This is the quote:
% Please note that mysql is shutdown daily sometime between 3am and 9 am
% US eastern for 30 minutes to an hour. This is get a good backup of all
% mysql databases on the server during the daily backup process.

Hmmm...  Speaking as a SysAdmin, that looks like a cover-all-bases
general announcement.  At the very least you should be able to predict
exactly when your host will be unavailable; a 6-hour window is too wide.
The same may be the case for the mysql downtime; you may in fact only be
down for a few minutes if at all.

I would check with your support and get specifics on how, when, for how
long, and why any part of your server will be unavailable.


% 
% Is this normal? This implies that my dynamic generated web site is down
% during a 2.1% to a 4.2% of the time (not counting any other server down
% time). I hear that normal down times for web sites are more like 1% or
% less.

Indeed.  If your provider is going to take down your web site on a daily
basis, you may need to find another provider.  While I applaud their
dedication to detail and commitment to stability, it doesn't seem to
serve the customer too well overall!

shameless_plugWe do hot backups of mysql with no down time.  Drop me a
note if you'd like more info on how to switch to us./shameless_plug


% Is this really necessary? Does MySQL requires to be shutdown to generate
% a good backup?

No, it doesn't; one can use mysqlhotcopy, a filesystem copy, a slave
server, or backup software smart enough to quickly lock things as it
reads a table -- just to name a few of the *many* possibilities and
permutations thereof.


% Any comments are welcome. Thanks a lot.
% 
% --
% 
% Clemente Zamora
% GlobalReservas
% (http://www.clementezamora.com)


HTH  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://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

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

iD8DBQE+LT8LGb7uCXufRwARAuC6AJ4wpkMKki61v7fwEJQNnXoZASuTCwCeL3Yp
k7rAV5aARcqv1VbDV6oCloc=
=ZxSV
-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

Copy database question

2003-01-21 Thread Tom Nickels

Hi everybody,

I am new on mysql. I use phpadmin for adminstration.

My problem:

I have Database 1, table userdata, field username

I need to copy the above mentioned data to:

Database2, Table chat_data, field nick

I have no clue how to do it! Can anybody tell me the string? Thanks for
help!

Tom


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

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: Financial packages

2003-01-21 Thread Doug Thompson

You might find something at
http://www.linuxapps.com/

Doug


On Tue, 21 Jan 2003 00:06:32 -0800, Ed Reed wrote:


Does anyone have a list of manufactures of financial packages that will run with 
MySQL?
We need to move from Intuit Quickbooks to something that can potentially scale to as 
many as 200 users. Greatplains and Solomon appear to only run on SQLServer. 

Any suggestions or past upgrade experiences would be great.



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

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: Copy database question

Tom,

 I have Database 1, table userdata, field username
 I need to copy the above mentioned data to:
 Database2, Table chat_data, field nick

in phpMyAdmin 2.3.3pl1 you can accomplish this as follows:

1. Choose Database 1 in the left frame.
2. Click table userdata in the left frame.
3. Click the Operations tab in the right frame.
4. Choose Copy table to (database.table) in the right frame.
5. Choose Database 2 (must exist), fill in chat_data, choose
Structure and data. Klick Go.
6. Choose Database 2 and then chat_data in the left frame.
7. Click the Structure tab in the right frame.
8. For column username, click Change, fill in nick, click Save.

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

- Original Message -
From: Tom Nickels [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, January 21, 2003 1:23 PM
Subject: Copy database question


 Hi everybody,

 I am new on mysql. I use phpadmin for adminstration.

 My problem:

 I have Database 1, table userdata, field username

 I need to copy the above mentioned data to:

 Database2, Table chat_data, field nick

 I have no clue how to do it! Can anybody tell me the string? Thanks
for
 help!

 Tom


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

 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: Copy database question

* Tom Nickels
 I have Database 1, table userdata, field username

 I need to copy the above mentioned data to:

 Database2, Table chat_data, field nick

 I have no clue how to do it! Can anybody tell me the string?

If the destination table already exist, something like this:

INSERT INTO Database2.chat_data SELECT username AS nick FROM `Database
1`.userdata

URL: http://www.mysql.com/doc/en/INSERT_SELECT.html 

Note the backticks used for the database name containing a space:

URL: http://www.mysql.com/doc/en/Legal_names.html 

--
Roger


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

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 between MySql and MS Sql Server

2003-01-21 Thread John Lodge

Hello all,

I wonder if anyone has set up a MySql database to mirror one running on MS
Sql Server. Although this sounds like an odd requirement, I have
a project where this would be extremely useful. Can anyone give me any
suggestions as to where to find out more information on this subject.
I have done some searching on Google and in the FAQ, and have not come up
with anything that I have found useful.

Any comments greatly appreciated,

Thanks,

John Lodge

 John Lodge
 Software Engineer
 Redwood Technologies Limited
 T +[44] (0)1344 304 344
 F +[44] (0)1344 304 345
 M +[44] (0)794 122 1422
 E [EMAIL PROTECTED]
 W www.redwoodtech.com
 
Email Disclaimer

The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else
is unauthorised. If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to be taken in reliance
on it is prohibited and may be unlawful. When addressed to our clients any
opinions or advice contained in this email are subject to the limitations of
Redwood Technologies Limited's standard terms and conditions of contract.




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

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.54a can be crased with a exploit for 3.23.53

2003-01-21 Thread Dennis Kruyt

Hi,

When I try the hoagie_mysql exploit from http://void.at/releases.html
on a 3.23.54a MySQL server (witch sould be safe) then i can crash the
database with this.

How did I do it?

I start hoagie_mysql with a valid db user (not root). Then press ctrl-c
(abort) and start the tool again. Now the tool has reported that the
attack has failed. But the MySQL db is restarted if i look in the error
log and some normal connectie to the database then will fail. I have
tried it on several server with success.

###

packages:/opt/pkgs# ./hoagie_mysql -u qwerty -p ytrewq
connecting to [localhost] as [qwerty] ... ok
sending one byte requests with user [root] ...

[CTRL-C]

packages:/opt/pkgs# ./hoagie_mysql -u qwerty -p ytrewq
connecting to [localhost] as [qwerty] ... ok
sending one byte requests with user [root] ...
attack failed

### Mysql.err log:

030121 12:36:16  mysqld restarted
030121 12:36:17  InnoDB: Started
/opt/zx/mysql/libexec/mysqld: ready for connections
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail

key_buffer_size=16773120
record_buffer=131072
sort_buffer=524280
max_used_connections=0
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 80379
K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Stack range sanity check OK, backtrace follows:
0x80c46b4
0x40022f54
0x4014847a
0x40148074
0x829039e
0x829086d
0x80af85d
0x80c9c26
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at (nil)  is invalid pointer
thd-thread_id=2

Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 2 did to cause the crash.  In some cases of
really
bad corruption, the values shown above may be invalid

The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash

Number of processes running now: 0
030121 12:37:56  mysqld restarted
030121 12:37:57  InnoDB: Started
/opt/zx/mysql/libexec/mysqld: ready for connections


packages:~# mysqld --version
mysqld  Ver 3.23.54 for pc-linux on i686


mysql select * from db;
+--+++-+-+--
---+-+-+---++---
--+++
| Host | Db | User   | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv |
References_priv | Index_priv | Alter_priv |
+--+++-+-+--
---+-+-+---++---
--+++
| 192.168.1.76 | qwerty | qwerty | Y   | Y   | Y
| Y   | Y   | Y | N  | N   |
Y  | Y  |
| localhost| qwerty | qwerty | Y   | Y   | Y
| Y   | Y   | Y | N  | N   |
Y  | Y  |
| packages | qwerty | qwerty | Y   | Y   | Y
| Y   | Y   | Y | N  | N   |
Y  | Y  |
+--+++-+-+--
---+-+-+---++---
--+++
3 rows in set (0.00 sec)

mysql select * from user;
+--++--+-+-+
-+-+-+---+-+
---+--+---++-+--
--++
| Host | User   | Password | Select_priv | Insert_priv |
Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv |
Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv
| Index_priv | Alter_priv |
+--++--+-+-+
-+-+-+---+-+
---+--+---++-+--
--++
|

mysql: field type of double without scientific notation

2003-01-21 Thread Philipp Sutter


I have a Mysql-table with a field of type double. when i retrieve very small
numbers from this table I get them allways in a scientific notation:
i.e. 8.34e-05. what i have to do, that i get the same number in normal
notation: 0.834?

thank you for any hints in advance!

philipp






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

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

Re: mysql: field type of double without scientific notation

* Philipp Sutter 
 I have a Mysql-table with a field of type double. when i retrieve 
 very small
 numbers from this table I get them allways in a scientific notation:
 i.e. 8.34e-05. what i have to do, that i get the same number in normal
 notation: 0.834?

See the FORMAT() function:

URL: http://www.mysql.com/doc/en/Miscellaneous_functions.html 

-- 
Roger

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

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 won't run

2003-01-21 Thread FlashGuy


Hi,

I had MYsql running on a nt4 server and w2k server. I uninstalled it a few weeks ago 
and just recently tried installing it again. The install went fine but when I start 
the service I 
keep getting an error no maater what I try. Using localhost and administrator.

Whats going on? If I install it on a fresh pc (never had it installed before) it 
works OK.


---
Colonel Nathan R. Jessop
Commanding Officer
Marine Ground Forces
Guatanamo Bay, Cuba
---




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

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

Always need to change to root user when I start the server

2003-01-21 Thread Alejandro C. Garrammone

The problem is this. Always when I start the server, when I try to show the
table mysql, I get an error message that saids Access Denied to user 
but when I put the same command with the --user=root it works. the question
is: How can I start the server whit root priviledges?

thxs.

Alex.


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

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: foreign key problem

2003-01-21 Thread Victoria Reznichenko

On Tuesday 21 January 2003 09:56, Saju Pappachen wrote:
 In my MySQL I have 3 tables like this and I have a pblm. in setting the
 foreign key.Pls. help

[skip]

 create table employee_skill_details(EMP_ID int(10) not null references
 employee_details(EMP_ID), SKILL_ID varchar(5) not null references
 skill_master(SKILL_ID),primary key(EMP_ID,SKILL_ID));


 Now based on this I should not be able to add an element to
 employee_skill_details table if the curresponding EMP_ID is not present in
 employee_details and SKILL_ID is not present in skill_master, because I
 have set references for it.i.e. foreign key. But still I am able to add
 data in employee_skill_details directly eventhough those data is not
 present in the other two tables.

 What is the problem?

FOREIGN KEY is supported only on InnoDB tables:
http://www.mysql.com/doc/en/SEC449.html

In your case REFERENCES do nothing, so MySQL doesn't check if corresponding 
rows are existing in the parent table:
http://www.mysql.com/doc/en/example-Foreign_keys.html



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





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

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

Re: FreeBSD 5.0 Thread Work...

2003-01-21 Thread Ken Menzel

Hi Dan,
   Thanks for that information,  does that mean (for us simple SQL
guys) that even though I am running 5.0RC3 and can compile and install
MySQL just fine (from ports or with my own script) it is using the
threads library from 4-STABLE branch not the new threads?

Also does this have soemthing to do with the word GIANT0 that keeps
appearing in the 'top' process list?

observations
I was hoping for great things from the 5.0, even with kernel profiling
off (invariants?) the /usr/local/sql-bench runs slower on 5.0.  It is
not a show stopper, but it is noticable and measureable.  I am going
to continue to run 5.0 on my test servers with the hopes of great
improvements.  I guess it will be a few more months before trying in
production to be able to see an improvement.  I don't mind being an
early adopter (with the problems that comes with it),  but it will be
good to see things works better.  It does appear not to creash at all!
(for me).
\observations

Guess it just a little early, (Darn)!

A freebsd fan
Ken


- Original Message -
From: Dan Nelson [EMAIL PROTECTED]
To: Maximo Migliari [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, January 20, 2003 4:54 PM
Subject: Re: FreeBSD 5.0 Thread Work...


 In the last episode (Jan 20), Maximo Migliari said:
  YES, pretty please, with sugar on top: from now on also make
  available for us binary distributions of MySQL optimized for
FreeBSD
  5.0's new threading implementation.
 
  There is a very large FreeBSD user base that must not be ignored
:)

 FreeBSD 5.0 has the ability for userland processes to create
multiple
 kernel threads.  It does not yet have a POSIX interface to those
 threads, so don't go asking people for kernel threads support just
yet :)

 5.0 has a lot of new features, but some of them are not complete
 (kernel threads), or not very well tested (sparc64/ia64 ports, MAC,
 ufs2).  The hope is that lots of people will install 5.0 and report
 problems so that 5.2 can be marked -STABLE.

 http://www.freebsd.org/releases/5.0R/early-adopter.html

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




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

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:Need Help. Trying to selve comma delimeted set problem for 2 months.

2003-01-21 Thread Rog11228

Hello,

Here is a set based solution.
Using v4.0.6 gamma on w2k pro.

CREATE TABLE broad (name varchar(10) not null,
groups varchar(20) not null,
primary key (name));

INSERT INTO broad values(DOG,1,3,4,2),
   (CAT,2,4,5),
   (BIRD,3,1,2),  
  
   (SHEEP,3,9,11,1,17,2);   

-- Create table of digits from 1-N where N is = to the length of 
-- largest groups string (number of characters in string).

CREATE TABLE digits 
(digit tinyint unsigned not null primary key);

INSERT INTO digits 
values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);

-- Parse out each number in the groups string into column number.
-- @s holds the delimiter used in the string (groups) to be parsed (ie. 
comma).

SET @s:=',';  
SELECT name,
  1*(LTRIM(RTRIM(SUBSTRING(CONCAT(@s,groups,@s),digit+1,
   LOCATE(@s,CONCAT(@s,groups,@s),digit+1)-
   (LOCATE(@s,CONCAT(@s,groups,@s),digit)+1) as number
 from broad,digits
 where  digit between LOCATE(@s,CONCAT(@s,groups,@s),digit) and 
LENGTH(CONCAT(@s ,groups)) -1
-- ORDER BY name,number
ORDER BY number,name; 
   
MS Server guy very interested in MySql.

www.rac4sql.net

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

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

Re: MYsql won't run

2003-01-21 Thread miguel solórzano

At 09:05 21/01/2003 -0500, FlashGuy wrote:
Hi,


Hi,

I had MYsql running on a nt4 server and w2k server. I uninstalled it a few 
weeks ago and just recently tried installing it again. The install went 
fine but when I start the service I
keep getting an error no maater what I try. Using localhost and administrator.

Whats going on? If I install it on a fresh pc (never had it installed 
before) it works OK.

Remove first the older service with:

mysqld-nt.exe --remove

and install again:

mysqld-nt.exe --install

if you are installed in another directory than the default
c:\mysql, then you need to use the basedir and datadir
sets in the my.ini file.


--
Regards,
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Fulltime Developer
/_/  /_/\_, /___/\___\_\___/   São Paulo - Brazil
   ___/   www.mysql.com 


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

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

Re: Always need to change to root user when I start the server

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Alex --

...and then Alejandro C. Garrammone said...
% 
% The problem is this. Always when I start the server, when I try to show the
% table mysql, I get an error message that saids Access Denied to user 
% but when I put the same command with the --user=root it works. the question
% is: How can I start the server whit root priviledges?

You need to create a database user (eg agarrammone) with use and select
(and perhaps other) privileges.  Just because you have an account on the
computer doesn't mean you have an account in the database -- and vice versa.


% 
% thxs.

HTH  HAND


% 
% Alex.


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://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

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

iD8DBQE+LWKGGb7uCXufRwARAmwXAJ4s/hAhMwpBfBBw5rLD4ynskdWXDQCg31TT
mLEre9Yw3tSkubvwbQll3lg=
=l1dj
-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: Mandatory server downtime?

2003-01-21 Thread Michael T. Babcock

David T-G wrote:

Hmmm... Speaking as a SysAdmin, that looks like a cover-all-bases
general announcement.

I was going to say the same thing; its always good (legally) to say you
might be down, instead of doing it and having people sue you.

Indeed. If your provider is going to take down your web site on a daily
basis, you may need to find another provider [...] We do hot backups of mysql with no down time. Drop me a note if you'd like more info on how to switch to us.

Ditto ;-)

For what its worth, sites like http://www.findsp.com will help you
search for ISPs that support various features like MySQL. Just talk to
the techs responsible for MySQL at your ISP (they have at least one,
right?) and ask if they pay for support (helps if they have a problem;
but I'm in a glass house here -- we don't yet) and how they do the backups.

No, it doesn't; one can use mysqlhotcopy, a filesystem copy, a slave
server, or backup software smart enough to quickly lock things as it
reads a table -- just to name a few of the *many* possibilities and
permutations thereof.

Depending on server load, it may be more reasonable to shut down MySQL
or at least lock it solid with table locks before doing a backup to get
it over with, but backing up from a replication client is much smarter,
imho.

--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock

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

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

- in colum names

2003-01-21 Thread Jonas Eriksson


Hi

We are upgrading our mysqlserver to  3.23.54 from a old one.
But we can't get mysqldump to import all the tables to the new
server becouse some of the table names have - sign in it. Like ip-number

Is it any way to come around this error or do we have to contact our
customers
to make them change their tablenames?
 


Med vänliga hälsningar
Jonas Eriksson


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

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: Joins are damn slow. . .

2003-01-21 Thread Michael T. Babcock

Steve Quezadas wrote:


However. I notice that if I do two separate searches it goes quicker 
(about 2.5 seconds combined). I can do a criteria search on defendants 
and then put all the resulting case numbers in a temporary table. Then 
do a join of that temporary table to the much smaller Cases table and 
do a search on that. I get the same results, and the query time is 
halved.


Do an explain on all those queries, post the output here and the time it 
took to run the queries.

--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock



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

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

Re: mysql: field type of double without scientific notation

2003-01-21 Thread Philipp Sutter

Roger Baklund:
 * Philipp Sutter 
  I have a Mysql-table with a field of type double. when i retrieve 
  very small
  numbers from this table I get them allways in a scientific notation:
  i.e. 8.34e-05. what i have to do, that i get the same number in normal
  notation: 0.834?
 
 See the FORMAT() function:
 
 URL: http://www.mysql.com/doc/en/Miscellaneous_functions.html 
 
 -- 
 Roger

format() has two disadvantages:
1) format will round the number. i can't round the numbers.
2) format(number,13) will get i.e. 0.83400. i do not want the
ending zeros.

is there an other way to get only the number i.e. 0.834.

philipp


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

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 update data in a table - getting an error

2003-01-21 Thread Don

Hi,

I want to change a field in a table.  Currently the data is in the
format -- 4818.50.00
I want to remove the decimal points so that it is like -- 4818.50.00
I tried the sql code below but am getting a syntax error.  Can someone
please
point out the error of my ways?

UPDATE DocComms SET ItemHSCode = concat(substr( ItemHSCode, 1, 4 ),
substr( ItemHSCode, 6, 2 ), substr( ItemHSCode, 9, 2 ))

Thanks,
Don


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003


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

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 update data in a table - getting an error

2003-01-21 Thread Don

Hi,
RESENT DFUE TO TYPO

I want to change a field in a table.  Currently the data is in theformat --
4818.50.00
I want to remove the decimal points so that it is like -- 48185000
I tried the sql code below but am getting a syntax error.  Can someone
please point out the error of my ways?

UPDATE DocComms SET ItemHSCode = concat(substr( ItemHSCode, 1, 4 ),
substr( ItemHSCode, 6, 2 ), substr( ItemHSCode, 9, 2 ))

Thanks,
Don


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003


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

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: Mandatory server downtime?

2003-01-21 Thread Clemente

Hi,
Thanks a lot for all those replies! It seems like my hosting company is using the 
cheapest and/or easiest way to make a full, consistent backup. And I guess the
ideal method from those mentioned is to have a slave server you can take down for 
backup.
The question is then whether such backup is necessary on a daily basis (I get a daily 
mysqldump for my records anyway without sysadmin intervention), and also how to
find this kind of detailed technical information from hosting companies to be able to 
choose one with a good quality/price ratio.
Any opinions on either?
Thanks.

Michael T. Babcock ha escrito:

 David T-G wrote:

 Hmmm...  Speaking as a SysAdmin, that looks like a cover-all-bases
 general announcement.
 

 I was going to say the same thing; its always good (legally) to say you
 might be down, instead of doing it and having people sue you.

 Indeed.  If your provider is going to take down your web site on a daily
 basis, you may need to find another provider [...] We do hot backups of mysql with 
no down time.  Drop me a note if you'd like more info on how to switch to us.
 

 Ditto ;-)

 For what its worth, sites like http://www.findsp.com will help you
 search for ISPs that support various features like MySQL.  Just talk to
 the techs responsible for MySQL at your ISP (they have at least one,
 right?) and ask if they pay for support (helps if they have a problem;
 but I'm in a glass house here -- we don't yet) and how they do the backups.

 No, it doesn't; one can use mysqlhotcopy, a filesystem copy, a slave
 server, or backup software smart enough to quickly lock things as it
 reads a table -- just to name a few of the *many* possibilities and
 permutations thereof.
 

 Depending on server load, it may be more reasonable to shut down MySQL
 or at least lock it solid with table locks before doing a backup to get
 it over with, but backing up from a replication client is much smarter,
 imho.

 --
 Michael T. Babcock
 C.T.O., FibreSpeed Ltd.
 http://www.fibrespeed.net/~mbabcock

--

Clemente Zamora
GlobalReservas
(http://www.clementezamora.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: HELP !! -- mySQL on TCP/IP on Mac OS X

2003-01-21 Thread Brent Baisley

Read up on the GRANT command in MySQL. This sets up the security level 
for the tables, hosts and even columns. MySQL defaults to only allowing 
the localhost to connect ot the database, which is fine if you are 
running the webserver and something like PHP on that computer. Once you 
separate them, you need to GRANT the other machine access to MySQL.
For example:
GRANT ALL ON db_name.* TO @192.168.1.%
Will grant all privileges on database db_name to every user on the 
192.168.1 network. You wouldn't want to actually grant that much access 
(i.e. dropping tables) to all users, but it's an example.

This is a very important part of MySQL database security that you should 
become familiar with. This has nothing to do with what platform you are 
running MySQL on (I'm on a Mac too). There are a number of good books 
that cover this well. I use MySQL by Paul DuBois (who you see on this 
list occasionally).


On Monday, January 20, 2003, at 02:26 AM, Ram Kumar wrote:

greetings nug

how can we access mySQL installation (from entropy.ch, Marc Liyanage)
through TCP/IP on a network

we are able to access mySQL on localhost, but tcp fails

regards, ram

--

 
+--+
   Mac Solutions
   http://www.macsolutionsindia.com +91 98102 70848
ram @ macsolutionsindia.com
   MSN: [EMAIL PROTECTED]   support @ macsolutionsindia.com
 
+--+
 Simplifying solutions for you...  Macintosh, Windows, Client/Server, 
Web



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

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


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


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

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: FreeBSD 5.0 Thread Work...

2003-01-21 Thread Dan Nelson

In the last episode (Jan 21), Ken Menzel said:
Thanks for that information,  does that mean (for us simple SQL
 guys) that even though I am running 5.0RC3 and can compile and
 install MySQL just fine (from ports or with my own script) it is
 using the threads library from 4-STABLE branch not the new threads?

It is still using userland threads, yes.  You're limited to one CPU and
threads will have to wait for each other's disk I/O.
 
 Also does this have soemthing to do with the word GIANT0 that keeps
 appearing in the 'top' process list?

Where in 4.* all system calls implicitly grabbed a kernel lock to keep
multiple processes from entering the kernel at the same time, about
half of 5.*'s syscalls are multi-processor safe.  The unsafe ones still
have to grab a lock, but in 5.* it's called Giant and it shows up in
top.
 
 observations
 I was hoping for great things from the 5.0, even with kernel profiling
 off (invariants?) the /usr/local/sql-bench runs slower on 5.0.  It is

WITNESS is the kernel option that's most likely slowing you down here. 
It forces each lock attempt to prove that it won't cause a deadlock.
INVARIANTS doesn't hurt too much.  Also try disabling the J malloc
debug flag by running ln -s 'j' /etc/malloc.conf.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

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

Re: Mandatory server downtime?

2003-01-21 Thread Michael T. Babcock

Clemente wrote:


The question is then whether such backup is necessary on a daily basis



I run incremental tapes hourly at some client locations.  You may only 
need weekly backups.  If the server room filled with water tomorrow and 
you had to return to your last backup, when do you want that backup to 
be from?

find this kind of detailed technical information from hosting companies to be able to choose one with a good quality/price ratio.
 


Call them, say you've got a problem with MySQL you need to talk to a 
tech person about and talk to whoever you get about what's been 
discussed here.  Ask if they're on the list :).

--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock



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

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

re: - in colum names

2003-01-21 Thread Egor Egorov

On Tuesday 21 January 2003 17:11, Jonas Eriksson wrote:

 We are upgrading our mysqlserver to  3.23.54 from a old one.
 But we can't get mysqldump to import all the tables to the new
 server becouse some of the table names have - sign in it. Like ip-number

 Is it any way to come around this error or do we have to contact our
 customers
 to make them change their tablenames?

Use --quote-names option of mysqldump:
http://www.mysql.com/doc/en/mysqldump.html



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




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

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

re: Always need to change to root user when I start the server

2003-01-21 Thread Egor Egorov

On Tuesday 21 January 2003 16:06, Alejandro C. Garrammone wrote:

 The problem is this. Always when I start the server, when I try to show the
 table mysql, I get an error message that saids Access Denied to user 
 but when I put the same command with the --user=root it works.
 the question is: How can I start the server whit root priviledges?

You mean how to _connect_ to the server as a 'root'? 

Just specify --user=root as you wrote above ;)




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




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

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

Re: mysql: field type of double without scientific notation

* Philipp Sutter
 Am Die, 2003-01-21 um 15.03 schrieb Roger Baklund:
  * Philipp Sutter
   I have a Mysql-table with a field of type double. when i retrieve
   very small
   numbers from this table I get them allways in a scientific notation:
   i.e. 8.34e-05. what i have to do, that i get the same number in normal
   notation: 0.834?
 
  See the FORMAT() function:
 
  URL: http://www.mysql.com/doc/en/Miscellaneous_functions.html 
 
  --
  Roger

 format() has two disadvantages for me:
 1) I do not want, that number will be rounded.

hm... then you may already have a problem, because float/double are
approximate number types. You do not control the exact value:

mysql create table floattest (f float);
Query OK, 0 rows affected (0.01 sec)

mysql insert floattest set f=0.01;
Query OK, 1 row affected (0.00 sec)

mysql select f,format(f,6),format(f,14),format(f,15) from floattest;
++-+--+---+
| f  | format(f,6) | format(f,14) | format(f,15)  |
++-+--+---+
| 1e-006 | 0.01| 0.01 | 0.007 |
++-+--+---+
1 row in set (0.00 sec)

These results may vary between different computers, depending on the
underlaying C library used for floating point calculations.

 2) when I try: format(number,13), I will get i.e. 0.83400; and I
 do not want the ending zeros.

 I there an other way to get only i.e. 0.834?

You could try using DECIMAL, which is an exact data type, but many of the
same problems apply. For instance, arithmetics and expressions like  SET f1
= 1.05-f2 or WHERE field = 1.234 or similar can not be safely used,
because approximate numbers are used for any constant expressions, even if
the column type is DECIMAL (or NUMERIC).

I never use FLOAT/REAL/NUMERIC/DECIMAL myself, mainly to avoid these
'approximity-problems'. If I need a column with for instance monetary data,
I just multiply by 100 when I store the value, and divide by hundred when I
retrieve:

mysql create table prices (item int,price int);
Query OK, 0 rows affected (0.05 sec)

mysql INSERT prices SET item=123,price=14550; # 145.50 * 100
Query OK, 1 row affected (0.00 sec)

mysql SELECT item,price/100 price FROM prices;
+--++
| item | price  |
+--++
|  123 | 145.50 |
+--++
1 row in set (0.00 sec)

A totally different approach may be to strip the trailing zeroes from the
returned value, using whatever scripting language you are using for your
application. That is, if about 14 digits is exact enough for your needs.

I also tried some hacks, just for the fun of it, but it did not work out in
my 3.23.30. Maybe it will work in a newer version:

mysql select f,right(f,3)+0 from floattest;
++--+
| f  | right(f,3)+0 |
++--+
| 1e-006 |6 |
++--+
1 row in set (0.00 sec)

mysql select f,right(f,3)+0,format(f,right(f,3)+0) from floattest;
ERROR 1064: You have an error in your SQL syntax near 'right(f,3)+0) from
floattest' at line 1

mysql select f,@a:=right(f,3)+0,@a from floattest;
++--+--+
| f  | @a:=right(f,3)+0 | @a   |
++--+--+
| 1e-006 |6 | 6|
++--+--+
1 row in set (0.00 sec)

mysql select f,@a:=right(f,3)+0,format(f,@a) from floattest;
ERROR 1064: You have an error in your SQL syntax near '@a) from floattest'
at line 1

(It seems as the FORMAT() function is picky about the second parameter, I
guess it must be a constant.)

HTH,

--
Roger


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

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: Mandatory server downtime?

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Clemente --

...and then Clemente said...
% 
% Hi,

Hi!


% Thanks a lot for all those replies! It seems like my hosting company is using the 
cheapest and/or easiest way to make a full, consistent backup. And I guess the

Happy to help.  Yes, it sounds like they're taking the easy way out.


% ideal method from those mentioned is to have a slave server you can take down for 
backup.

I'd say that's the best way to go, too.


% The question is then whether such backup is necessary on a daily basis (I get a 
daily mysqldump for my records anyway without sysadmin intervention), and also how to

You know, if you have a mysqldump on a disk that's backed up anyway then
you have a backup and shouldn't have to worry about any downtime :-)


% find this kind of detailed technical information from hosting companies to be able 
to choose one with a good quality/price ratio.

I don't know of a formula, but I'd ask things like

  - how is my host backed up?
  - how is my database backed up?
  - will there be any regular downtime?
  - what is your uptime guarantee?
  - how is that uptime calculated?

as well as some questions close to my heart like

  - do you have redundant networking to my machine, both to handle a
failed switch and a backhoe accident?
  - do you store your tapes in a disaster-proof facility?
  - what is your expected restore time for a file or db table on my
machine?
  - what is your expected recovery time for a complete disaster on my
machine?

and go from there; if any of the answers don't sound right, then ask more
questions about that topic.


% Any opinions on either?

I have the best quality/price ratio, of course.  [Oops; I did it again.]


% Thanks.


HTH  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://justpickone.org/davidtg/  Shpx gur Pbzzhavpngvbaf Qrprapl Npg!

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

iD8DBQE+LXQWGb7uCXufRwARAp/oAKCwXfluT1CAH7UyvEdiNWp9i7SougCfekTe
/wfHIghrg/pBHqG0ob3cbqM=
=wtjn
-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: - in colum names

* Jonas Eriksson
 We are upgrading our mysqlserver to  3.23.54 from a old one.
 But we can't get mysqldump to import all the tables to the new
 server becouse some of the table names have - sign in it. Like ip-number

 Is it any way to come around this error or do we have to contact our
 customers to make them change their tablenames?

If the old version was 3.23.6 or newer, use the -Q or --quote-names option
of mysqldump:

URL: http://www.mysql.com/doc/en/mysqldump.html 

--
Roger



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

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 update data in a table - getting an error

* Don
 I want to change a field in a table.  Currently the data is in
 theformat --
 4818.50.00
 I want to remove the decimal points so that it is like -- 48185000
 I tried the sql code below but am getting a syntax error.  Can someone
 please point out the error of my ways?

 UPDATE DocComms SET ItemHSCode = concat(substr( ItemHSCode, 1, 4 ),
 substr( ItemHSCode, 6, 2 ), substr( ItemHSCode, 9, 2 ))

The name of the substring function in mysql is SUBSTRING() or MID(), not
SUBSTR().

It would be easier to use the REPLACE() function:

UPDATE DocComms SET ItemHSCode = REPLACE(ItemHSCode,'.','')

URL: http://www.mysql.com/doc/en/String_functions.html 

HTH,

--
Roger


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

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

opposite of this join


I have three tables, people, events and epeople. epeople includes a person
id and an event id and works as a list of people attending events in the
database.

The following query returns a list of all the people participating in a
particular event
given an event id.

select people.id as pid, concat(lastname, , , firstname) as name from
events
  left join epeople on events.id=epeople.eid
  left join people on epeople.pid=people.id
where events.id=2;

How could I get a list of all the people not participating?
  thought I would add to the where clause with:
and people.id is null;

but that returns an empty set.


tia,
jb




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

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

Hi and why

Hi, i'm new at this list and to the mysql world. I'm a delphi/firebird
developer and have to acomplish some tasks using a mysql based database. So
i'm looking about how things work in mysql.

My question... i created a table TEST, with two columns, ID_TEST int(11) and
NAME varchar(50).. and  added three records. This way:

1, ppl1
2, ppl2
3, null

ok.. why when i execute this sql, mysql return the two first rows, instead
of raising an error?

select * from TEST where NAME = 2??

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: Trying to update data in a table - getting an error

2003-01-21 Thread Diana Soares

Hi, 

substr() doesn't exist in MySQL. 
Use substring().

UPDATE DocComms SET ItemHSCode = concat(substring( ItemHSCode, 1, 4 ),
substring( ItemHSCode, 6, 2 ), substring( ItemHSCode, 9, 2 ))


On Tue, 2003-01-21 at 15:34, Don wrote:
 Hi,
 RESENT DFUE TO TYPO
 
 I want to change a field in a table.  Currently the data is in theformat --
 4818.50.00
 I want to remove the decimal points so that it is like -- 48185000
 I tried the sql code below but am getting a syntax error.  Can someone
 please point out the error of my ways?
 
 UPDATE DocComms SET ItemHSCode = concat(substr( ItemHSCode, 1, 4 ),
 substr( ItemHSCode, 6, 2 ), substr( ItemHSCode, 9, 2 ))
 
 Thanks,
 Don
-- 
Diana Soares


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

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

Whats between a varchar text ??

2003-01-21 Thread Craig melia

Hi

I am building my 1st app in php with Mysql and I hope you can help.

I want to have users give a description in a form and varchar(255) wont give
me enough space so I thought I would use text(650) but it seems the 'text'
type wont take the limit of (650), it simply offers the user (65535): way
too much.

Is there anything I can do to set a column type to recieve just 650
characters of text, or something similar?

Craig


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

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: Upgrading Red Hat rpms

2003-01-21 Thread Nuño Iglesias

EL día  Mon, 20 Jan 2003 16:22:55 -0500
Walter [EMAIL PROTECTED] escribió:

--I have a Red Hat 7.3 box running MySQL 3.23.49 (installed with the Red Hat 
--rpms).  I am about to upgrade to 4.0.9 and would prefer using the RPMs from 
--MySQL.  My plan was to just un-install the Red Hat rpms and then install 
--the new rpms (after backing everything up first, of course).  Is there 
--anything else I should do or look out for?

What I usually do whith RPMS...is to upgrade them...with -U option.
rpm -U package.version.rpm

--
--Thanks in advance 
--
--Walter
--
--
---
--Before posting, please check:
--   http://www.mysql.com/manual.php   (the manual)
--   http://lists.mysql.com/   (the list archive)
--
--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: Help with a select where an ID isn't in another table, but is avalid row.

2003-01-21 Thread Diana Soares

Hi, 
I didn't test this.
Assuming that Swordfish.scanner.scanner_id   0 for all rows (and
that's why it doesn't appear in the results list from the first query):

SELECT InteractV2.Job_Queue.TargetRange AS TargetIP, 
  IFNULL(INET_NTOA(Swordfish.scanner.ipaddr),'ANY SCANNER') AS ScannerIP
FROM InteractV2.Job_Queue 
LEFT JOIN Swordfish.scanner
  ON Swordfish.scanner.scanner_id = InteractV2.Job_Queue.ScannerID
WHERE InteractV2.Job_Queue.CompanyID = '123';

Note the LEFT JOIN and the IFNULL.
Hope it works!


On Tue, 2003-01-21 at 08:14, Daevid Vincent wrote:
 Having a bit of a sticky wicket here... I'm sure the answer is simple
 but I'm just not seeing it.
 
 Basically I want to return all the TargetRanges and ScannerIDs. You
 would think it's simple, here's the snag. See how ScannerID has 0 in
 some rows. Well, we used 0 to mean any scanner in our PHP code. So, I
 want a select query that will return me BOTH the rows for CompanyID =
 123. As for the 0 ScannerID row, well leave the column blank, or
 populate it with ANY SCANNER or something, that would be awesome.
 Currently I only get the one row that has a ScannerID = 5. The second
 query below doesn't work as it gives me multiple permutations (of
 course). Any of you gurus know how to resolve this?
 
 So the output should ideally look like:
 +--++
 | TargetIP | ScannerIP  |
 +--++
 | www.interactnetworks.com | 66.150.172.129 |
 | 192.168.30.1 | ANY SCANNER|
 +--++
 
 OR even this is cool
 
 +--++
 | TargetIP | ScannerIP  |
 +--++
 | www.interactnetworks.com | 66.150.172.129 |
 | 192.168.30.1 ||
 +--++
 
 mysql select * from InteractV2.Job_Queue;
 +-+---+--+---+
 | QueueID | ScannerID | TargetRange  | CompanyID |
 +-+---+--+---+
 |   3 | 0 | 65.121.191.46|89 |
 |   8 | 0 | 12.228.139.218   |99 |
 |  14 | 0 | 12.228.90.64 |   121 |
 |  10 | 0 | 206.253.218.122  |   117 |
 |  11 | 5 | 206.253.218.123  |   117 |
 |  15 | 5 | www.interactnetworks.com |   123 | --
 |  19 | 0 | 192.168.30.1 |   123 | --
 |  17 | 0 | 207.13.196.235   |   125 |
 +-+---+--+---+
 
 mysql SELECT InteractV2.Job_Queue.TargetRange AS TargetIP, 
 -INET_NTOA(Swordfish.scanner.ipaddr) AS ScannerIP
 - FROM InteractV2.Job_Queue, Swordfish.scanner
 - WHERE Swordfish.scanner.scanner_id =
 InteractV2.Job_Queue.ScannerID
 - AND InteractV2.Job_Queue.CompanyID = '123';
 +--++
 | TargetIP | ScannerIP  |
 +--++
 | www.interactnetworks.com | 66.150.172.129 |
 +--++
 
 mysql SELECT InteractV2.Job_Queue.TargetRange AS TargetIP, 
 -INET_NTOA(Swordfish.scanner.ipaddr) AS ScannerIP
 - FROM InteractV2.Job_Queue, Swordfish.scanner
 - WHERE Swordfish.scanner.scanner_id =
 InteractV2.Job_Queue.ScannerID
 - OR InteractV2.Job_Queue.ScannerID = 0
 - AND Swordfish.scanner.scanner_id 
 InteractV2.Job_Queue.ScannerID
 - AND InteractV2.Job_Queue.CompanyID = '123';
 +--+-+
 | TargetIP | ScannerIP   |
 +--+-+
 | 206.253.218.123  | 66.150.172.129  |
 | www.interactnetworks.com | 66.150.172.129  |
 | 192.168.30.1 | 66.150.172.129  |
  [snipped for brevity sake]
 | 192.168.30.1 | 192.168.10.70   |
 | 192.168.30.1 | 192.168.10.80   |
 | 192.168.30.1 | 192.168.25.4|
 +--+-+
 29 rows in set (0.00 sec)
 
 Other useful information:
 
 select scanner_id, ipaddr, inet_ntoa(ipaddr) as scanner_ip from
 Swordfish.scanner order by scanner_id;
 +++-+
 | scanner_id | ipaddr | scanner_ip  |
 +++-+
 |  5 | 1117170817 | 66.150.172.129  |
  [snipped for brevity sake]
 | 23 | 3232238150 | 192.168.10.70   |
 | 25 | 3232238160 | 192.168.10.80   |
 | 26 | 3232241924 | 192.168.25.4|
 +++-+
 27 rows in set (0.00 sec)
 
-- 
Diana Soares


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

opposite of this join? -- simplified

I would like to get the difference of the results from query 2 and query 1,
or the people not attending event [id 2] (referenced by epeople.eid).
Thought the answer would be query three, but as you see, I get an empty
set. Any advice would be appreciated.
jb


Here is everyone attending the event [id 2]


1) mysql select people.id as pid, concat(lastname, , , firstname) as
name from
- epeople
-   left join people on epeople.pid=people.id
-  where epeople.eid=2;
+--+--+
| pid  | name |
+--+--+
|5 | Piovesan, Ron|
|6 | Mossberg, Walt   |
|7 | Reporter, Jon|
|8 | Analyst, Jon |
|   12 | Media, Karen |
|   15 | Galvin, Tom  |
|   16 | Ogilvy, Tom  |
|   17 | Machines, Industrial |
|2 | Tenderich, Burghardt |
+--+--+
9 rows in set (0.01 sec)




Here are all the people records:

2) mysql select people.id as pid, concat(lastname, , , firstname) as
name from
- people;
+-+--+
| pid | name |
+-+--+
|   1 | Collins, Stacey  |
|   2 | Tenderich, Burghardt |
|   3 | Executive, Bea   |
|   4 | Bea, Joe |
|   5 | Piovesan, Ron|
|   6 | Mossberg, Walt   |
|   7 | Reporter, Jon|
|   8 | Analyst, Jon |
|   9 | Smith, Susan |
|  10 | Jane, Sarah  |
|  12 | Media, Karen |
|  13 | PR, Bea  |
|  14 | Relations, Public|
|  15 | Galvin, Tom  |
|  16 | Ogilvy, Tom  |
|  17 | Machines, Industrial |
+-+--+
16 rows in set (0.00 sec)


3) mysql select people.id as pid, concat(lastname, , , firstname) as
name from
- epeople
-   left join people on epeople.pid=people.id
-  where epeople.eid=2
-  and people.id is 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

Install 2 Servers of different versions

2003-01-21 Thread Chris Faust

Hey All,

I want to install the mySql 4.0.9 to try out some features but I want to
make sure my 3.23.54 release stays 100% as is.
I'd like to have both servers running, using different data dirs etc. etc.
In short I don't want anything at all shared between the two.

I've read a bunch of docs on the site as well as the multi install section
the MySql book and what I've come up with so far is.

Download the source install and configure with the following options.
--prefix=/usr/mysql_409 (new dir for the 4.0 install)
--with-unix-socket-path=/usr/local/mysql4/mysql.sock
--localstatedir=/usr/mysql_409/db
--with-tcp-port=4006

Now I'm at the point where I have a bunch of questions I can't seem to find
a positive answer for..

1. Are those the only options needed? Will this make sure that /etc/my.cf is
only used for the 3.x install and the 4.x will allow me to have a my.cnf in
the /usr/mysql_409 dir?

2. This is dumb, but do I pass each option one at a time into ./configure?
Meaning I tried to do it all at once, for example
./configure --prefix=/usr/mysql_409 \ --with-unix ...), but I got an error
of it not seeing the second param as a valid setting, yet if I do each one,
one at a time it works fine.

3. Is port 4406 OK for second install?

4. How do I get a local mysql prompt, do I just start using -P 4406
whenever I want to connect on the command line?

Its only my dev machine so I'm not worried about too much, I just really
want to make sure I don't mess with the 3.x install.

Thanks in Advance!!
-Chris




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

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

new mysql is not working properly with JDBC

2003-01-21 Thread Snistle

Hey all,

I hope you can help with this issue, as it seems to be something wrong with my MySQL 
installation: I recently upgraded my RedHat install, and in doing so upgraded Java and 
MySQL. I had JDBC code which worked perfectly prior to the upgrade, but now cant 
connect. It continuously tells me Communication Link Failure. However, this happens 
with both local code and code on other servers in my workspace which were unaffected 
by the upgrade, other than the fact that they are connecting to the newer version of 
MySQL now. have any of you had this problem? Any ideas what the solution is? I'd 
appreciate help. 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: Whats between a varchar text ??

2003-01-21 Thread Chris Faust

If you want to limit the amount of text coming in or going out, wouldn't it
be easier to it either in your php script or constrain the html textarea?

Just a thought.

-Chris


 -Original Message-
 From: Craig melia [mailto:[EMAIL PROTECTED]]
 Sent: Tuesday, January 21, 2003 12:28 PM
 To: [EMAIL PROTECTED]
 Subject: Whats between a varchar text ??


 Hi

 I am building my 1st app in php with Mysql and I hope you can help.

 I want to have users give a description in a form and
 varchar(255) wont give
 me enough space so I thought I would use text(650) but it seems
 the 'text'
 type wont take the limit of (650), it simply offers the user (65535): way
 too much.

 Is there anything I can do to set a column type to recieve just 650
 characters of text, or something similar?

 Craig


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

 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: Joins are damn slow. . .

2003-01-21 Thread Jennifer Goodie

Your joins are probably not using indexes and the tables could be joining in
an inefficient order.  Try running an explain on your query to see how it is
being done.

-Original Message-
From: Steve Quezadas [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 21, 2003 1:31 AM
To: [EMAIL PROTECTED]
Subject: Joins are damn slow. . .

Maybe I'm a bit naive in saying this but. . .

SQL joins are damn slow!

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

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: Whats between a varchar text ??

At 17:27 + 1/21/03, Craig melia wrote:

Hi

I am building my 1st app in php with Mysql and I hope you can help.

I want to have users give a description in a form and varchar(255) wont give
me enough space so I thought I would use text(650) but it seems the 'text'
type wont take the limit of (650), it simply offers the user (65535): way
too much.


Why does this matter?  MySQL doesn't store 65535 characters per value,
just the characters that are actually in your string.  The column uses
only as much space as is necessary.

You can easily enforce a limit of 650 characters in your application.



Is there anything I can do to set a column type to recieve just 650
characters of text, or something similar?

Craig



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

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: Hi and why

At 15:02 -0200 1/21/03, Valdir Stiebe Junior wrote:

Hi, i'm new at this list and to the mysql world. I'm a delphi/firebird
developer and have to acomplish some tasks using a mysql based database. So
i'm looking about how things work in mysql.

My question... i created a table TEST, with two columns, ID_TEST int(11) and
NAME varchar(50).. and  added three records. This way:

1, ppl1
2, ppl2
3, null

ok.. why when i execute this sql, mysql return the two first rows, instead
of raising an error?

select * from TEST where NAME = 2??


I don't know why it returns the first two rows, but (assuming you didn't
really end your query with two question marks) what error are you expecting
to occur?



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: Mysql and Database Link

2003-01-21 Thread MySQL

Yep Jermey are right what he says - Anyone know about using Database link in Mysql???

Regards
Frank

- Original Message - 
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Egor Egorov [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, January 20, 2003 4:35 PM
Subject: Re: Mysql and Database Link

 On Mon, Jan 20, 2003 at 03:49:00PM +0200, Egor Egorov wrote:
  On Monday 20 January 2003 06:36, MySQL wrote:

   I'm woundering if Mysql can use Database link, like Oracle can???

   Eg. If i have some tables at my labtop, and want to make the same table(s)
   at my server with this command:

   create table test as select * from test@database_link (This is how you do
   it on Oracle), but how do I do this in Mysql

  You can use CREATE .. SELECT statement:
  http://www.mysql.com/doc/en/CREATE_TABLE.html

  but you must create indexes manually.

 I think you misunderstand.  Those are not really the same.  Links in
 Oracle (as described to me) allow you to connect multiple servers
 together--so you can join between tables on different servers, for
 example.

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

 MySQL 4.0.8: up 1 days, processed 46,297,176 queries (320/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: Whats between a varchar text ??

2003-01-21 Thread Keith C. Ivey

On 21 Jan 2003, at 17:27, Craig melia wrote:

 I want to have users give a description in a form and varchar(255)
 wont give me enough space so I thought I would use text(650) but it
 seems the 'text' type wont take the limit of (650), it simply offers
 the user (65535): way too much.

Use a TEXT column and limit the length of the input in your 
application, which you probably should be doing anyway (since just 
having MySQL truncate it without notifying the user wouldn't be very 
friendly).

[Filter fodder: SQL]

-- 
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: Hi and why

 I don't know why it returns the first two rows, but (assuming you didn't
 really end your query with two question marks) what error are you
expecting
 to occur?

I didn't end my query with the two question marks. :)
And if i put 0 instead of (2 or any different of zero) after the equal sign
the sql return nothing. (better than returning garbage)
I was expecting something like 'Column NAME isn't of type integer'.



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

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: opposite of this join

2003-01-21 Thread Bob Hall

On Tue, Jan 21, 2003 at 08:43:29AM -0800, Josh L Bernardini wrote:
 
 I have three tables, people, events and epeople. epeople includes a person
 id and an event id and works as a list of people attending events in the
 database.
 
 The following query returns a list of all the people participating in a
 particular event
 given an event id.
 
 select people.id as pid, concat(lastname, , , firstname) as name from
 events
   left join epeople on events.id=epeople.eid
   left join people on epeople.pid=people.id
 where events.id=2;
 
 How could I get a list of all the people not participating?
   thought I would add to the where clause with:
 and people.id is null;
 
 but that returns an empty set.

Right. Presumably, there's no entry in your join table to link a 
person to an event that they're not attending.

I've got a entry in the MySQL SQL section of my website called 
Whether a row on one side of a many-to-many join is linked to 
a given row on the other side that has a solution.
http://users.starpower.net/rjhalljr/Serve

Bob Hall

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

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

Fwd: MYsql won't run

2003-01-21 Thread FlashGuy

Well...I uninstalled mysql and installed it again and I still can't get the service to 
run!!!???

==BEGIN FORWARDED MESSAGE==
 From: FlashGuy [EMAIL PROTECTED]
 To: FlashMX [EMAIL PROTECTED]
 Date: Tue, 21 Jan 2003 09:05:52 -0500
 Reply-To: FlashGuy [EMAIL PROTECTED]
 Priority: Normal
 X-Mailer: PMMail 2000 Standard (2.20.2502) For Windows 2000 (5.1.2600;1)
 MIME-Version: 1.0
 Content-Type: text/plain; charset=iso-8859-1
 Content-Transfer-Encoding: 7bit
 Subject: MYsql won't run

Hi,

I had MYsql running on a nt4 server and w2k server. I uninstalled it a few weeks ago 
and just recently tried installing it again. The install went fine but when I start 
the 
service I 
keep getting an error no maater what I try. Using localhost and administrator.

Whats going on? If I install it on a fresh pc (never had it installed before) it 
works OK.

---
Colonel Nathan R. Jessop
Commanding Officer
Marine Ground Forces
Guatanamo Bay, Cuba
---

===END FORWARDED MESSAGE===

---
Colonel Nathan R. Jessop
Commanding Officer
Marine Ground Forces
Guatanamo Bay, Cuba
---

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

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: opposite of this join

2003-01-21 Thread Brent Baisley

You are looking for a list of people rather than a list of events and 
the people attending them. So you want to start your select from People, 
not from Events. Get a list of all the people, then filter out those 
attending event id 2. You don't want to start you select from epeople 
because then you are excluding those people that are not attending any 
event.

select people.id as pid,concat(lastname,, ,firstname) as name
from people
left join epeople on epeople.pid=people.id
left join events on events.id=epeople.eid
where events.id!=2

I think will do it, although something feels wrong. Hmmm, let me know.


On Tuesday, January 21, 2003, at 11:43 AM, Josh L Bernardini wrote:

The following query returns a list of all the people participating in a
particular event
given an event id.

select people.id as pid, concat(lastname, , , firstname) as name from
events
  left join epeople on events.id=epeople.eid
  left join people on epeople.pid=people.id
where events.id=2;

How could I get a list of all the people not participating?
  thought I would add to the where clause with:
and people.id is null;


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


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

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: Whats between a varchar text ??

Craig,

 I want to have users give a description in a form and varchar(255)
wont give
 me enough space so I thought I would use text(650) but it seems the
'text'
 type wont take the limit of (650), it simply offers the user (65535):
way
 too much.

In MySQL, you have TINYTEXT (2**8 = 256, like (VAR)CHAR), TEXT (2**16 =
65536), MEDIUMTEXT (2**24) and LONGTEXT (2**32). Unlike CHAR/VARCHAR,
you cannot restrict the length of TEXT column types.

Now for the good news: All TEXT types will store data dynamically, i.e.
let's say you store 10 bytes in a TEXT column, then this will require 12
bytes (2 bytes to store the actual length), not 65536 bytes.

If you want to restrict the amount of text your users can enter, you
will have to do this in your application. Just to point out what I mean,
here's a rather radical method using PHP:

$user_description = substr($user_description,0,650);
mysql_query(INSERT INTO userdata (description) VALUES
('$user_description'));

Alternatively, you could use:

mysql_query(INSERT INTO userdata (description) VALUES
(LEFT('$user_description',650)));

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

- Original Message -
From: Craig melia [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, January 21, 2003 6:27 PM
Subject: Whats between a varchar text ??


 Hi

 I am building my 1st app in php with Mysql and I hope you can help.

 I want to have users give a description in a form and varchar(255)
wont give
 me enough space so I thought I would use text(650) but it seems the
'text'
 type wont take the limit of (650), it simply offers the user (65535):
way
 too much.

 Is there anything I can do to set a column type to recieve just 650
 characters of text, or something similar?

 Craig


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

 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: Hi and why

Sorry, correcting my last email, the sql result the two rows when i use
'NAME = 0'.

- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: Valdir Stiebe Junior [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, January 21, 2003 4:33 PM
Subject: Re: Hi and why

 At 15:02 -0200 1/21/03, Valdir Stiebe Junior wrote:
 Hi, i'm new at this list and to the mysql world. I'm a delphi/firebird
 developer and have to acomplish some tasks using a mysql based database.
So
 i'm looking about how things work in mysql.

 My question... i created a table TEST, with two columns, ID_TEST int(11)
and
 NAME varchar(50).. and  added three records. This way:

 1, ppl1
 2, ppl2
 3, null

 ok.. why when i execute this sql, mysql return the two first rows,
instead
 of raising an error?

 select * from TEST where NAME = 2??

 I don't know why it returns the first two rows, but (assuming you didn't
 really end your query with two question marks) what error are you
expecting
 to occur?

 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: Mysql and Database Link

Frank,

 Yep Jermey are right what he says - Anyone know about using Database
link in Mysql???

This is not built in. You will have to do this within your application.

You can also use some of the MySQL helper apps for this, like mysqldump
in combination with mysql (the command line tool), like this:

laptop mysqldump myDB | mysql -h myserver myDB

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

- Original Message -
From: MySQL [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, January 21, 2003 7:36 PM
Subject: Re: Mysql and Database Link


Yep Jermey are right what he says - Anyone know about using Database
link in Mysql???

Regards
Frank

- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Egor Egorov [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, January 20, 2003 4:35 PM
Subject: Re: Mysql and Database Link


 On Mon, Jan 20, 2003 at 03:49:00PM +0200, Egor Egorov wrote:
  On Monday 20 January 2003 06:36, MySQL wrote:
 
   I'm woundering if Mysql can use Database link, like Oracle can???
  
   Eg. If i have some tables at my labtop, and want to make the same
table(s)
   at my server with this command:
  
   create table test as select * from test@database_link (This is how
you do
   it on Oracle), but how do I do this in Mysql
 
  You can use CREATE .. SELECT statement:
  http://www.mysql.com/doc/en/CREATE_TABLE.html
 
  but you must create indexes manually.

 I think you misunderstand.  Those are not really the same.  Links in
 Oracle (as described to me) allow you to connect multiple servers
 together--so you can join between tables on different servers, for
 example.

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

 MySQL 4.0.8: up 1 days, processed 46,297,176 queries (320/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



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

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

join from this subselect

I can't come up with the join syntax to mimic this subselect query to list
people not attending an event (*epeople.eid == event.id):

select people.id as pid, concat(lastname, , , firstname) as name from
people where people.id not in
(select epeople.pid from
epeople
  left join people on epeople.pid=people.id
where epeople.eid=2);

Thought it would be:

select epeople.pid, concat(lastname, , , firstname) as name from epeople
  left join people on epeople.pid=people.id
where epeople.eid=2
  and people.id is null;

but I get an empty set.

The following lists people attending an event:

select epeople.pid, concat(lastname, , , firstname) as name from epeople
  left join people on epeople.pid=people.id
where epeople.eid=2;

tia,
jb

PS is 4.1 available anywhere so I could just use the subselect. Under 4.1,
are subselects or joins more efficient?



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

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: Hi and why

At 16:47 -0200 1/21/03, Valdir Stiebe Junior wrote:

  I don't know why it returns the first two rows, but (assuming you didn't

 really end your query with two question marks) what error are you

expecting

 to occur?


I didn't end my query with the two question marks. :)
And if i put 0 instead of (2 or any different of zero) after the equal sign
the sql return nothing. (better than returning garbage)
I was expecting something like 'Column NAME isn't of type integer'.


That won't happen. MySQL performs extensive type conversion of values,
attempting to execute the query in the most sensible way given what you
provide.

I'm stlll surprised that NAME = 2 returns anything.  When I tried your
example, it returned no rows.  I get two rows with NAME = 0, nothing
with NAME = 2.  Are you sure you're not mixing up your test results?

NAME = 0 is a string-to-number conversion.  MySQL converts the string
to a number and performs a numeric comparison.  Of your three values,
'ppl1' and 'ppl2' will be converted to 0 (which compares the same as 0),
but NULL is still NULL (which does not compare the same as 0).

If you're getting two rows with NAME = 2 and nothing with NAME = 0,
I'm at a loss to explain it.

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

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: Hi and why

 I'm stlll surprised that NAME = 2 returns anything.  When I tried your
 example, it returned no rows.  I get two rows with NAME = 0, nothing
 with NAME = 2.  Are you sure you're not mixing up your test results?

Sorry, my mistake.

 NAME = 0 is a string-to-number conversion.  MySQL converts the string
 to a number and performs a numeric comparison.  Of your three values,
 'ppl1' and 'ppl2' will be converted to 0 (which compares the same as 0),
 but NULL is still NULL (which does not compare the same as 0).

Ok, i understand now. This is useful when you have things like '10' or
'342'. But when you have 'ppl1' isn't deterministic wich number it
represent. But thanks. I'm studying to understand how MySql works.

I have another question, will open another thread to it.

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: Hi and why

At 18:11 -0200 1/21/03, Valdir Stiebe Junior wrote:

  I'm stlll surprised that NAME = 2 returns anything.  When I tried your

 example, it returned no rows.  I get two rows with NAME = 0, nothing
 with NAME = 2.  Are you sure you're not mixing up your test results?


Sorry, my mistake.


 NAME = 0 is a string-to-number conversion.  MySQL converts the string
 to a number and performs a numeric comparison.  Of your three values,
 'ppl1' and 'ppl2' will be converted to 0 (which compares the same as 0),
 but NULL is still NULL (which does not compare the same as 0).


Ok, i understand now. This is useful when you have things like '10' or
'342'. But when you have 'ppl1' isn't deterministic wich number it
represent.


Yes, it is.  'ppl1' doesn't begin with digits, so it converts to zero
in numeric context.


 But thanks. I'm studying to understand how MySql works.

I have another question, will open another thread to it.

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

Problem with MySQL Control Center

2003-01-21 Thread Edgard Dapás/CCPI


Hi !!!Anybody can help me with this problem?

Thanks

I've  just installed Control Center

Version:
  0.8.9-beta Linux (Intel glibc 2.2 systems, static) (3.1M)

Operating System RedHat 7.1

When I try to execute the program it have the following error

[root@LINUX1 mysqlcc]# ./mysqlcc
Segmentation fault

In the memory there are th followings programs
[root@LINUX1 mysqlcc]# ipcs

 Segmentos memoria compartida 
key   shmid propietarioperms bytes nattchestado
0x 0 root  600   1056768   9 dest
0x 32769 root  600   5324809 dest
0x 65538 apache600   46084 9 dest
0x 360451root  777   1966082 dest
0x 524292root  777   1966082 dest
0x 1146885   root  777   1966082 dest
0x 1277958   root  777   1966082 dest
0x 1409031   root  777   1966082 dest
0x 10453000  root  777   1966082 dest
0x 10485769  root  777   1966082 dest
0x 10584074  root  777   1966082 dest

-- Matrices semáforo ---
key   semid propietarioperms nsems estado
0x 0 apache600   1

-- Colas de mensajes -
key   msqid propietarioperms bytes utilizadosmensajes




La   información  contenida  en  este  correo  es  para  uso exclusivo de
los destinatarios  del  mismo. Está prohibido a  las personas o entidades
que no sean los destinatarios de este correo, realizar cualquier tipo de
modificación, copia o distribución del mismo.
Si  Ud. recibe este correo por error, tenga a  bien notificar al emisor y
eliminarlo.

This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author  and do not necessarily represent those of
Personal. If you are not the intended recipient, be advised that you have
received this email in error and that any use, dissemination, forwarding,
printing, or copying of this email is strictly prohibited. If you have
received this email in error please contact the sender.
(Embedded image moved to file: pic24199.pcx)


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

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: Hi and why

2003-01-21 Thread Dean Harding

I just tried it myself and I get an empty set as expected.

What do you get when you do a 'select * from test'?

 -Original Message-
 From: Valdir Stiebe Junior [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, 22 January 2003 5:48 am
 To: [EMAIL PROTECTED]
 Subject: Re: Hi and why

  I don't know why it returns the first two rows, but (assuming you
didn't
  really end your query with two question marks) what error are you
 expecting
  to occur?

 I didn't end my query with the two question marks. :)
 And if i put 0 instead of (2 or any different of zero) after the equal
 sign
 the sql return nothing. (better than returning garbage)
 I was expecting something like 'Column NAME isn't of type integer'.

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

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

2003-01-21 Thread gerald_clark

ppl1 converted to numeric is 0.
You are compareing to a number.

Valdir Stiebe Junior wrote:


Sorry, correcting my last email, the sql result the two rows when i use
'NAME = 0'.

- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: Valdir Stiebe Junior [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, January 21, 2003 4:33 PM
Subject: Re: Hi and why


 

At 15:02 -0200 1/21/03, Valdir Stiebe Junior wrote:
   

Hi, i'm new at this list and to the mysql world. I'm a delphi/firebird
developer and have to acomplish some tasks using a mysql based database.
 

So
 

i'm looking about how things work in mysql.

My question... i created a table TEST, with two columns, ID_TEST int(11)
 

and
 

NAME varchar(50).. and  added three records. This way:

1, ppl1
2, ppl2
3, null

ok.. why when i execute this sql, mysql return the two first rows,
 

instead
 

of raising an error?

select * from TEST where NAME = 2??
 

I don't know why it returns the first two rows, but (assuming you didn't
really end your query with two question marks) what error are you
   

expecting
 

to occur?

   

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


 




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

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

simple windows client for mysql

2003-01-21 Thread David Driscoll

I have a fairly basic mysql database running on unix. Now I need to make this 
data available to other people.  These would be various engineers and 
administrators using every version of windows from 95 (japanese version) to 
XP all using laptops connected to our internal network.  I am looking for an 
easy to use, easy to install mysql client which would enable users to query 
my database.  It seems that installing mysql server for windows on each 
machine would do this but it seems like using a shovel to swat a fly. 
Suggestions?  Cheap is good too.
Thanks
Dave Driscoll

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

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: Hi and why

2003-01-21 Thread Gelu Gogancea

Hi,
Indeed...this happend in both main version of MySQL (ver. 3.x and 4.0.x)
However ,if you use :

select * from test_table where NAME='2';

you will have the correct result.

Regards,
Gelu
___
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message -
From: Valdir Stiebe Junior [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, January 21, 2003 8:47 PM
Subject: Re: Hi and why


  I don't know why it returns the first two rows, but (assuming you didn't
  really end your query with two question marks) what error are you
 expecting
  to occur?

 I didn't end my query with the two question marks. :)
 And if i put 0 instead of (2 or any different of zero) after the equal
sign
 the sql return nothing. (better than returning garbage)
 I was expecting something like 'Column NAME isn't of type integer'.



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

 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

Communication Link Failed?

2003-01-21 Thread Snistle

Hi,

I'm running Connector/J 2.0.14 with J2SDK1.4.1_01. Prior to these upgrades, all my 
code ran fine. However, I upgraded all my packages (MySQL, J2SDK, and to Connector/J, 
as already stated) and now I keep getting an IOException. Why is this happening? I've 
thought about the timeout scenario but there arent any links at all, and adding 
autorefresh doesnt help since it never gets the connection to begin with. The stack 
trace finds it errors in the Connector/J init attempts. Anyone have any thoughts?

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

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: opposite of this join


Bob,
First of all, thank you. I never would have gotten here on my own. Only I
am not quite there.
Using your example, I have managed to list all the events a person is
attending and not attending. Wondering if you might provide some further
clues as to how to restrict the query to those events a person is not
attending, or in you example, those items a person doesn't own.

This step is towards the goal of returning all the people not attending an
event - or not owning a desk.

Also what is the significance of the value returned in the attends column?

Here's what I've got:

mysql SELECT events.event,
- people.lastname,
- Sum(epeople.eid = people.id) AS attends
- FROM (events, people) LEFT JOIN epeople
- ON events.id = epeople.eid
- WHERE people.id=1
- Group by events.id;
+--+--+-+
| event| lastname | attends |
+--+--+-+
| BEA World, San Francisco | Collins  |   9 |
| The Storage Consortium   | Collins  |   0 |
| BEA II   | Collins  |   0 |
+--+--+-+
3 rows in set (0.01 sec)

From your many-many example, here are those events this individual is
attending:

mysql SELECT event, lastname
- FROM people, epeople, events
- WHERE people.id = epeople.pid
- AND epeople.eid = events.id
- AND people.id=1;
+--+--+
| event| lastname |
+--+--+
| BEA World, San Francisco | Collins  |
| BEA II   | Collins  |
+--+--+
2 rows in set (0.00 sec)

thanks again,
jb


|-+
| |   Bob Hall   |
| |   rjhalljr@starpow|
| |   er.net  |
| ||
| |   01/21/2003 10:49 |
| |   AM   |
| ||
|-+
  
--|
  |
  |
  |   To:   [EMAIL PROTECTED]  
  |
  |   cc:  
  |
  |   Subject:  Re: opposite of this join  
  |
  
--|




On Tue, Jan 21, 2003 at 08:43:29AM -0800, Josh L Bernardini wrote:

 I have three tables, people, events and epeople. epeople includes a
person
 id and an event id and works as a list of people attending events in the
 database.

 The following query returns a list of all the people participating in a
 particular event
 given an event id.

 select people.id as pid, concat(lastname, , , firstname) as name from
 events
   left join epeople on events.id=epeople.eid
   left join people on epeople.pid=people.id
 where events.id=2;

 How could I get a list of all the people not participating?
   thought I would add to the where clause with:
 and people.id is null;

 but that returns an empty set.

Right. Presumably, there's no entry in your join table to link a
person to an event that they're not attending.

I've got a entry in the MySQL SQL section of my website called
Whether a row on one side of a many-to-many join is linked to
a given row on the other side that has a solution.
http://users.starpower.net/rjhalljr/Serve

Bob Hall

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

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

Connetion times out - OT

2003-01-21 Thread Mike(mickako)Blezien

Hello all,

sorry if this maybe a bit OT, but we've seem to be getting this error 110: 
Connection timed out.. from our Perl scripts connecting to our MySQL database... 
is this perl or the mysql server related issue ?

thx's
--
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel:  1(985)902-8484
MSN: [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

Problems: Configuring from source.

2003-01-21 Thread Froin Laven

Solaris 8 (sparc)
gcc 3.2
Configuring from source version mysql-3.23.54a

2 problems:

1. I get a checksum error when untar'ing the tgz file found on any of the
download servers.
2. config.status: error: cannot find input file: readline/Makefile.in

The directory exists, but there are no files.

Any help would be appreciated.


-- 
Well, he didn't know what to do, so he decided to look at the government,
to see what they did, and scale it down and run his life that way.
-- Laurie Anderson

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

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

Re: MySQL 3.23.54a can be crased with a exploit for 3.23.53

2003-01-21 Thread Sergei Golubchik

Hi!

On Jan 21, Dennis Kruyt wrote:
 Hi,
 
 When I try the hoagie_mysql exploit from http://void.at/releases.html
 on a 3.23.54a MySQL server (witch sould be safe) then i can crash the
 database with this.
 
 How did I do it?
 
 I start hoagie_mysql with a valid db user (not root). Then press ctrl-c
 (abort) and start the tool again. Now the tool has reported that the
 attack has failed. But the MySQL db is restarted if i look in the error
 log and some normal connectie to the database then will fail. I have
 tried it on several server with success.

You should've contacted us (using [EMAIL PROTECTED]) first
so we'd be able to release fixed version :(

Anyway, this is fixed. 3.23.55 will be released soon.
For impatients, there's our bk tree, available publicaly

Thanks for bugreport.

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: FreeBSD 5.0 Thread Work...

2003-01-21 Thread Jeremy Zawodny

On Mon, Jan 20, 2003 at 02:20:10PM -0500, Grigor, Peter wrote:
 Alrighty then!
 
 Now FreeBSD 5.0 has been released, what are the plans for mysql to evaluate
 the new (really new :) thread implementation.

What's wrong with LinuxThreads on FreeBSD?  It works great with
FreeBSD 4.x.

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

MySQL 4.0.8: up 2 days, processed 83,092,818 queries (328/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 problems with InnoDB tables...?

2003-01-21 Thread Heikki Tuuri

Kris,

- Original Message -
From: Krzysztof Karski [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Tuesday, January 21, 2003 11:35 AM
Subject: replication problems with InnoDB tables...?

 So I have been reading a lot lately about replication but I can't find a
 good summary/explanation of MySQL's replication features regarding InnoDB
 tables and how their transactional properties may cause problems in
 replication.

transactions should not cause problems in replication. However, a relevant
bug fix is this:

MySQL/InnoDB-4.0.6, December 19, 2002
...
Fixed a bug: in replication issuing SLAVE STOP in the middle of a
multi-statement transaction could cause that SLAVE START would only perform
part of the transaction. A similar error could occur if the slave crashed
and was restarted.

 I have seen suggestions that InnoDB tables should be converted to MyISAM
 tables before being loaded and then converted back to InnoDB tables.

LOAD TABLE FROM MASTER only works for MyISAM tables.

To set up a new replication slave you should use a cold backup, or a hot
backup made with the non-free InnoDB tool.

 I have also seen many others here confused as to what problems there are
 between InnoDB and replication.

 So, could some friendly MySQL guru please explain to me, and many others,
 what the replication issues with InnoDB tables really are...please? The
docs
 state that InnoDB is replication safe yet there seem to be so many
problems
 with them when updates fail, roll back or are partially executed because
 some constraint conflict that causes replication to stop.

A relevant bug fix:

MySQL/InnoDB-4.0.6, December 19, 2002
...

Fixed two bugs introduced in 4.0.4: in AUTO_INCREMENT, REPLACE could cause
the counter to be left 1 too low. A deadlock or a lock wait timeout could
cause the same problem.

MySQL does not write to the binlog SQL statements which fail. It does not
write to the binlog any SQL statements from a transaction which is rolled
back. Thus, these are never replicated, and in the slave there should not be
any constraint violation errors.

 Sincerely
 Kris Karski

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, hot backup, 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

Re: simple windows client for mysql

2003-01-21 Thread Scott Pippin

I am looking for an 
easy to use, easy to install mysql client which would enable users to
query 
my database.  It seems that installing mysql server for windows on
each 
machine would do this but it seems like using a shovel to swat a fly.

Suggestions?  Cheap is good too.

Try SQlyog
http://www.webyog.com/

or

MySQL-Front - very good but no longer being developed
http://mysqlfront.sstienemann.de/

Both of these are free.

Hope that helps.

Scott Pippin
[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: simple windows client for mysql

2003-01-21 Thread Tam, Michael

Have you check out MyCC from mysql.com ??  It can be used as a front-end of
MySQL.

Cheers,
Michael

-Original Message-
From: David Driscoll [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 21, 2003 3:53 PM
To: [EMAIL PROTECTED]
Subject: simple windows client for mysql

I have a fairly basic mysql database running on unix. Now I need to make
this 
data available to other people.  These would be various engineers and 
administrators using every version of windows from 95 (japanese version) to 
XP all using laptops connected to our internal network.  I am looking for an

easy to use, easy to install mysql client which would enable users to query 
my database.  It seems that installing mysql server for windows on each 
machine would do this but it seems like using a shovel to swat a fly. 
Suggestions?  Cheap is good too.
Thanks
Dave Driscoll

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

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: FreeBSD 5.0 Thread Work...

2003-01-21 Thread Grigor, Peter

Nothing's wrong with Linuxthreads :)

But eventually the SA stuff that FreeBSD has chosen to implement will give
better performance than kernel threads, so I was asking when the Mysql team
was thinking of tackling getting a binary out for 5.0.

Peter
^_^

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 21, 2003 4:45 PM
To: Grigor, Peter
Cc: '[EMAIL PROTECTED]'
Subject: Re: FreeBSD 5.0 Thread Work...


On Mon, Jan 20, 2003 at 02:20:10PM -0500, Grigor, Peter wrote:
 Alrighty then!
 
 Now FreeBSD 5.0 has been released, what are the plans for mysql to
evaluate
 the new (really new :) thread implementation.

What's wrong with LinuxThreads on FreeBSD?  It works great with
FreeBSD 4.x.

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

MySQL 4.0.8: up 2 days, processed 83,092,818 queries (328/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: Communication Link Failed?

2003-01-21 Thread Mark Matthews

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[EMAIL PROTECTED] wrote:

Hi,

I'm running Connector/J 2.0.14 with J2SDK1.4.1_01. Prior to these upgrades, all my code ran fine. However, I upgraded all my packages (MySQL, J2SDK, and to Connector/J, as already stated) and now I keep getting an IOException. Why is this happening? I've thought about the timeout scenario but there arent any links at all, and adding autorefresh doesnt help since it never gets the connection to begin with. The stack trace finds it errors in the Connector/J init attempts. Anyone have any thoughts?



Are you running Linux? Various versions of Linux (RedHat, mostly) have 
issues with TCP/IP connections being made to various versions of MySQL 
server. You should ensure that you are using MySQL-4.0.9 or newer from 
MySQL-AB binaries (not RedHat), and that connecting using TCP/IP from 
the mysql command-line client works.

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

	-Mark

- -- 
MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /  Mark Matthews [EMAIL PROTECTED]
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
 /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
___/ www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+LchktvXNTca6JD8RAo4zAKC3Fqu+cQSxK9ghtWRbfxaq/9/ibQCgjnCM
GLpbi1mBVePa6jvY2gTKVHE=
=9Ssx
-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: simple windows client for mysql

Dave,

 I am looking for an
 easy to use, easy to install mysql client which would enable users to
query
 my database.

What about a web browser? It's most probably installed on all laptops.

You could use some simple PHP script (not as mighty as phpMyAdmin, I
would suggest) to convert SQL queries into HTML replies.

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

- Original Message -
From: David Driscoll [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, January 22, 2003 12:53 AM
Subject: simple windows client for mysql


I have a fairly basic mysql database running on unix. Now I need to make
this
data available to other people.  These would be various engineers and
administrators using every version of windows from 95 (japanese version)
to
XP all using laptops connected to our internal network.  I am looking
for an
easy to use, easy to install mysql client which would enable users to
query
my database.  It seems that installing mysql server for windows on each
machine would do this but it seems like using a shovel to swat a fly.
Suggestions?  Cheap is good too.
Thanks
Dave Driscoll

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

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

Detecting Mysql servers on the network

2003-01-21 Thread Defryn, Guy



Is it possible to detect mysql servers on the network without knowing the hostname?

cheers

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

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: opposite of this join

2003-01-21 Thread Bob Hall

On Tue, Jan 21, 2003 at 01:38:42PM -0800, Josh L Bernardini wrote:
 
 Bob,
 First of all, thank you. I never would have gotten here on my own. Only I
 am not quite there.
 Using your example, I have managed to list all the events a person is
 attending and not attending. Wondering if you might provide some further
 clues as to how to restrict the query to those events a person is not
 attending, or in you example, those items a person doesn't own.
 
 This step is towards the goal of returning all the people not attending an
 event - or not owning a desk.
 
 Also what is the significance of the value returned in the attends column?
 
 Here's what I've got:
 
 mysql SELECT events.event,
 - people.lastname,
 - Sum(epeople.eid = people.id) AS attends
 - FROM (events, people) LEFT JOIN epeople
 - ON events.id = epeople.eid
 - WHERE people.id=1
 - Group by events.id;

It's been a while since I worked with this query, so I'm not sure that 
this answer is correct. However, after quickly going over the article 
again, I believe you can treat the attends column as a boolean type. 
Add AND attends = FALSE to the WHERE clause to get the events they 
will not attend. If that works, it answers both of your questions.

(You can also use attends = 0, but attends = FALSE is more 
self-documenting.)


HTH
Bob Hall

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

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

create mysql user?? how

2003-01-21 Thread M A



hi,

would someone please tell me how to create mysql user? and how to set the 
mysql root password?


Thanks

_
The new MSN 8: advanced junk mail protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail


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

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: checking permissions and users in mysql

2003-01-21 Thread Jon Miller

On Tue, 2003-01-21 at 23:12, [EMAIL PROTECTED] wrote:
 Your message cannot be posted because it appears to be either spam or
 simply off topic to our filter. To bypass the filter you must include
 one of the following words in your message:
 
 sql,query,queries,smallint
 
 If you just reply to this message, and include the entire text of it in the
 reply, your reply will go through. However, you should
 first review the text of the message to make sure it has something to do
 with MySQL. Just typing the word MySQL once will be sufficient, for example.
 
 You have written the following:
 
 Is there a way to check or view in mysql what permissions users have to certain
 databases?
-- 
Jon Miller [EMAIL PROTECTED]
MMT Networks Pty Ltd



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

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

Rank Amateur Back for More

2003-01-21 Thread Stephen Tiano

Okay, I found User Comments at the end of the section of the user manual 
online that contained the info I needed to get the option of loading a 
textfile into a table.

The startup line I use for that is:

   /usr/local/bin/mysql -u root --local-infile -p menagerie

where 'menagerie' is the database that has the table into which I want 
to load data.

Observation: I guess there's no way to just give myself a 'free pass' to 
be able to load data into any database. It appears I have to name a 
particular database right at the start.

Anyway ... Then, of course, I do a

   USE menagerie

Now I want toL in the textfile called pet.txt, which I downloaded from 
the MySQL website at 
http:.//www.mysql.com/Downloads/Contrib/Examples/menagerie.zip, into the 
table pet which now resides in my Sites folder. So I issue the command

   LOAD DATA LOCAL INFILE pet.txt INTO TABLE pet;

and, for the first time, I am not denied LOADing DATA privileges. I do, 
however, get to read:

   ERROR:
   File 'pet.txt' not found (Errcode:2)

What's up with that? I checked the folder 'menagerie' in the Sites 
folder. pet.txt is most definitely there, and it contains comma 
delimited rows to go with the tutorial table I'm working at.

But it'd sure help top know what's going on now with trying to load data 
from a textfile.

Steve Tiano


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

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: Detecting Mysql servers on the network