Recursion

[Rob]

Hi all,

I need some help with recursion in mySql. I have the following table:

ID | Name | ParentID

1  | Bob  | 0
2  | John | 1
3  | Elm  | 1

etc.

For a given ID, I need to recurse up the tree and get all the parents.  I've
already read
about Joe Celko's nested set approach, but it's not a good solution as
apparently updates are
a real pain and this table will be modified heavily.  Does anyone have any
good suggestions??
Maybe store procs (although, by all accounts store proc functionality
doesn't come standard with
mySql)??

Thanks


---
Rob

**
Rob Cherry
mailto:[EMAIL PROTECTED]
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.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

Recursion

[Rob]

Hi all,

I need some help with recursion in mySql. I have the following table:

ID | Name | ParentID

1  | Bob  | 0
2  | John | 1
3  | Elm  | 1

etc.

For a given ID, I need to recurse up the tree and get all the parents.  I've
already read
about Joe Celko's nested set approach, but it's not a good solution as
apparently updates are
a real pain and this table will be modified heavily.  Does anyone have any
good suggestions??
Maybe store procs (although, by all accounts store proc functionality
doesn't come standard with
mySql)??

Thanks


---
Rob

**
Rob Cherry
mailto:[EMAIL PROTECTED]
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.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: Needed full table scan over 1,2 GB is toooooo slow

[Jeremy Zawodny]

On Wed, Feb 12, 2003 at 08:06:03AM +0100, Nico Rehmann wrote:
> Hi,
> 
> I have a big problem.
> I NEED to do a full table scan every month on a table.
> I really need all of the data in this table
> This table has about 1,2 GB of data.
> It takes mysql about 45 minutes to send the data.
> 
> I configured mysql with the my-huge.cnf and 
> I also tried "record_buffer=50M" 
> but nothing really changed
> 
> Server:
> 2 GB RAM,
> 4 IDE Harddisks and IBM RAID Controller
> only mysql is running on this server
> 
> What do i have to configure to get these huge amount of data faster?
> Or is mysql not the right database for such things?

Is the scanning disk or I/O bound?  Does it max out the CPU?  Or is
there idle time yet?
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.8: up 8 days, processed 283,232,781 queries (371/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

Needed full table scan over 1,2 GB is toooooo slow

[Nico Rehmann]

Hi,

I have a big problem.
I NEED to do a full table scan every month on a table.
I really need all of the data in this table
This table has about 1,2 GB of data.
It takes mysql about 45 minutes to send the data.

I configured mysql with the my-huge.cnf and 
I also tried "record_buffer=50M" 
but nothing really changed

Server:
2 GB RAM,
4 IDE Harddisks and IBM RAID Controller
only mysql is running on this server

What do i have to configure to get these huge amount of data faster?
Or is mysql not the right database for such things?


Thanks in advance

Nico


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

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

mysqladmin password has no effect in 4.0.10 gamma max-nt.

[Dan Ertman]

Hello,
The documentation says that in order to change the root user's password, you
should run
C:\mysql\bin\mysqladmin -u root password your_password
I have tried this multiple times, on Windows 2000 and Windows XP, running
mysql as a service (mysql-max-nt), and it has had no effect on the root
user's password.
After running this command, with or without "mysqadmin -u root reload", I am
still able to:
1) Connect using "mysql mysql -u root"
2) Shut down using "mysqladmin -u root shutdown"

Fix:
I fixed the problem using
>mysql mysql -u root
>update user set password=PASSWORD('your_password') where user='root';
>flush privileges;
However, this does not change the fact that mysqladmin appears to be bugged.

Release: MySQL 4.0.10.
Executable: mysql-max-nt running as a service
Environment: 1) Toshiba Laptop, 1.1 GHz PIII, 256 RAM, 30 GBHD.
 2) Dell PowerEdge Server, dual 1.2 GHz P Xeon, 2 GB Ram, 200 GBHD
System: 1) Windows XP Home
  2) Windows 2000 Server
Compiler: Binary distribution.

Thanks!
Dan Ertman


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

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: Insert & Replication issues...

[Jeremy Zawodny]

On Wed, Feb 12, 2003 at 10:59:00AM +0800, Trevor Phillips wrote:
> I have a setup of 3 servers, with MySQL replication in a round-robin fashion 
> between all 3. So far, any writes to a particular table are done only on one 
> machine at any time. The replication is used as a hot backup, not a 
> load-balancing thing.
> 
> I now have an instance where I'd like to do inserts on several of
> the machines at once. The inserts are just logged info, so there's
> no unique key, no auto-increment, etc...
> 
> Is it safe to do this, in this configuration?

Yes.

> If simultaneous updates hit 2 or more of the servers at the same
> time, will it still replicate all updates properly (even if they may
> chronologically occur at different times)?

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

MySQL 4.0.8: up 8 days, processed 282,721,681 queries (371/sec. avg)

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

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

Re: [mysql] Counting rows

[Ahmed S K Anis]

show table status:

check out the columns names you will get as idea as to how info it
gives.


>>> olinux <[EMAIL PROTECTED]> Wednesday, February 12, 2003 12:21:16 PM
>>>
SELECT count(*) FROM table WHERE whatever

olinux


--- Tim Johnson <[EMAIL PROTECTED]> wrote:
> Hello All:
> Let's suppose I want to count the rows in set
> for a match
> to a value in a column. I don't need to read the
> selection
> set, I just need to know the number of matches.
> 
> What is the quickest, most efficient way to do this
> in mysql?
> TIA
> -- 
> Tim Johnson <[EMAIL PROTECTED]>
>   http://www.alaska-internet-solutions.com 
>   http://www.johnsons-web.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 
> 


__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com 

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

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



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

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

Re: [mysql] Counting rows

[olinux]

SELECT count(*) FROM table WHERE whatever

olinux


--- Tim Johnson <[EMAIL PROTECTED]> wrote:
> Hello All:
> Let's suppose I want to count the rows in set
> for a match
> to a value in a column. I don't need to read the
> selection
> set, I just need to know the number of matches.
> 
> What is the quickest, most efficient way to do this
> in mysql?
> TIA
> -- 
> Tim Johnson <[EMAIL PROTECTED]>
>   http://www.alaska-internet-solutions.com
>   http://www.johnsons-web.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
> 


__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

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

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

RE: RAND is mysql 3.22

[Daniel Rossi]

hi guys i just tried this on my works old mysql server and it doesnt seem to work , is 
it possible ? :|


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

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

Insert & Replication issues...

[Trevor Phillips]

I have a setup of 3 servers, with MySQL replication in a round-robin fashion 
between all 3. So far, any writes to a particular table are done only on one 
machine at any time. The replication is used as a hot backup, not a 
load-balancing thing.

I now have an instance where I'd like to do inserts on several of the machines 
at once. The inserts are just logged info, so there's no unique key, no 
auto-increment, etc... 

Is it safe to do this, in this configuration?

If simultaneous updates hit 2 or more of the servers at the same time, will it 
still replicate all updates properly (even if they may chronologically occur 
at different times)?

(I am currently using MySQL 3.23.49 from Debian packages...)

-- 
. Trevor Phillips -   http://jurai.murdoch.edu.au/ . 
: Web Technical Administrator -  [EMAIL PROTECTED] : 
| IT Services-  Murdoch University | 
 ><
| On nights such as this, evil deeds are done. And good deeds, of /
| course. But mostly evil, on the whole. /
 \  -- (Terry Pratchett, Wyrd Sisters)  /


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

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 there a SQL/diff program out there?

[Lance Lovette]

I've been happy with mysqldiff.

http://adamspiers.org/computing/mysqldiff/

Lance


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

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

Re: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Steff]

Hi Mark,
Thanks for taking the time to reply.

 I like the idea of changing our counter model to reduce the 
contention around the one file. I will talk to the other developers 
about this.

 I re-read the reference you mentioned 
"http://www.innodb.com/ibman.html#InnoDB_restrictions";  and do 
not see the problem regarding using table level locks on the 
counter table, while we use transaction control on all the rest of the 
tables.  I can see the potential for problems if we mix table locks 
and transaction control on the same table, but in our case the 
counter table is never referenced except by one function and that 
function uses the table level locks, and always uses its own 
connection. 

By re-asking my question I am not trying to say you are wrong, 
I am simply trying to understand things to make sure we can 
remove any other  potential problems we have lurking in our code. 

 Thanks again for taking the time to reply. This problem has 
been haunting us for two months now and yours is the first contact 
we have had with someone who works with MySql in a production 
environment. 

Thanks
Steff

On 11 Feb 2003 at 19:09, Mark Matthews wrote:

Date sent:  Tue, 11 Feb 2003 19:09:16 -0600
From:   Mark Matthews <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Copies to:  [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject:Re: Transaction problems using InnoDB, "not 
locked with LOCKTABLES"

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> [EMAIL PROTECTED] wrote:
> > Hello,
> > We are experiencing severe problems when running MySql with 
> > INNODB in a  production environment.  Applications which work 
> > fine under light load fail when under production load.
> > 
> > Our MySql environment is as follows:
> > OS Platform: Windows 2000 Service Pack 2
> > Machine description:
> > Compiler   : VC++ 6.0
> > Architecture   : i686
> > Total Memory   : 2097151 KB RAM
> > Server Info3.23.54-max-nt-log
> > have_innodbYES
> > innodb_additional_mem_pool_size104857600
> > innodb_buffer_pool_size1048576000
> > innodb_data_file_path  ibdata1
> > innodb_data_home_dir   
> > innodb_file_io_threads 4
> > innodb_force_recovery  0
> > innodb_thread_concurrency  8
> > innodb_flush_log_at_trx_commit 1
> > innodb_fast_shutdown   ON
> > innodb_flush_method
> > innodb_lock_wait_timeout   50
> > innodb_log_arch_dir
> > innodb_log_archive OFF
> > innodb_log_buffer_size 1048576
> > innodb_log_file_size   5242880
> > innodb_log_files_in_group  2
> > innodb_log_group_home_dir  .\
> > innodb_mirrored_log_groups 1
> > 
> > 
> > We have a large block of SQL statements (about 50) which we 
> > would like to treat as  one transaction.  The software works fine
> > when running in a limited environment,  but issues intermittent
> > errors when running in production.
> > 
> > All the errors follow the pattern of "java.sql.SQLException: General
> > error: Table 'productsprovided'  was not locked with LOCK TABLES". 
> > 
> > The table name will be different at various times (even though the
> > code being executed is always the same), but the error is always
> > talking about a failure to lock the tables.
> > 
> >  We are using  two different connections within this one processing 
> > loop. The one connection shows up as number 7 below, while
> > the other is number 12. Our expectation is that these two 
> > connections will operate independently, even though the Java code is
> > working with them both within our "transaction".  Our expectation is
> > that a rollback on connection 7 will NOT effect the activities which
> > had taken place on connection 12 across this same timespan. Along
> > the same lines we would expect the lock and unlock on connection 12
> > will not affect the transaction under way on connection 7. 
> > 
> > Our SQL in this application follows the following pattern. (the
> > following is a section from the MysQL log with just one instance of
> > the application running):
> > 
> > 030125 20:24:29   7 Query   SET autocommit=0
> >   7 Query   BEGIN
> >   7 Query   Select * from accountsprovided
> >   Where
> > ExternalID='I06'AND FinServProvID = 'C33'
> >   7 Query   UPDATE accountsprovided SET
> > Enabled='Y' WHERE  AccountID = 'CKCBSBF2994309'
> >   7 Query   Select * from account Where
> >   AccountID
> > =  'CKCBSBF2994309'
> >   7 Query   UPDATE account SET 
> > PreLimit=1.00 WHERE  AccountID = 'CKCBSBF2994309'
> > 
> >  12 Query   Lock Table Control Write
> >   

Mysqld not starting as user "mysql"

[Guild N Crantz]

I have just installed mysql-max-3.23.55-pc-linux-i686.tar.gz and now
I'm finding it impossible to run mysqld without being root.  The
permisions for the mysql data tree are all set with user and group
"mysql".  However when I run mysql.server I get permission denied
errors:

Starting mysqld daemon with databases from /usr/local/mysql/data
./bin/safe_mysqld: line 236:
/usr/local/mysql/data/ophelia.menagerie.cc.err: Permission denied
rm: cannot remove `/usr/local/mysql/data/ophelia.menagerie.cc.pid':
Permission denied
./bin/safe_mysqld: line 242:
/usr/local/mysql/data/ophelia.menagerie.cc.err: Permission denied
tee: /usr/local/mysql/data/ophelia.menagerie.cc.err: Permission denied
030211 20:30:23  mysqld ended
tee: /usr/local/mysql/data/ophelia.menagerie.cc.err: Permission denied


I have setup /etc/my.cnf:

[mysqld]
basedir=/usr/local/mysql
user=mysql

I have mysql.server in /etc/rc.d/init.d/ and mysqld won't start at
boot.  I am assuming it is because of these premission errors.  Is
there some way that I can force mysqld to always run as user "mysql"? 
Is there a setting now that is overriding /etc/my.cnf?

Thank you very much,

Guildencrantz


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

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

[Announcement] for PHP and MySQL 4.1 Users/Testers

[Georg Richter]

Hi,

The new mysql extension (ext/mysqli) is now in the php-cvs-repository. Please 
note that you need MySQL Version 4.1 (both server and client library) and 
that this extension has experimental status.

Configuration:

--with-mysqli=/path-to-mysql-4.1  -without-mysql

If you want to use both ext/mysql and ext/mysqli you have to specity 

--with-mysqli=/path-to-mysql-4.1  --with-mysql=/path-to-mysql-4.1 
 

Some new features:

OO and plain interface
SSL connection support
Variable bindings
Bigint support
Replication support

For some samples check the tests subdir. I'll add documentation within the 
next days.

Regards

Georg

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

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: Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

[EMAIL PROTECTED] wrote:

Hello,
We are experiencing severe problems when running MySql with 
INNODB in a  production environment.  Applications which work 
fine under light load fail when under production load.

Our MySql environment is as follows:
OS Platform: Windows 2000 Service Pack 2
Machine description:
Compiler   : VC++ 6.0
Architecture   : i686
Total Memory   : 2097151 KB RAM
Server Info3.23.54-max-nt-log
have_innodbYES
innodb_additional_mem_pool_size104857600
innodb_buffer_pool_size1048576000
innodb_data_file_path  ibdata1
innodb_data_home_dir   
innodb_file_io_threads 4
innodb_force_recovery  0
innodb_thread_concurrency  8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown   ON
innodb_flush_method
innodb_lock_wait_timeout   50
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size   5242880
innodb_log_files_in_group  2
innodb_log_group_home_dir  .\
innodb_mirrored_log_groups 1


We have a large block of SQL statements (about 50) which we 
would like to treat as  one transaction.  The software works fine 
when running in a limited environment,  but issues intermittent 
errors when running in production.

All the errors follow the pattern of "java.sql.SQLException: General
error: Table 'productsprovided'  was not locked with LOCK 
TABLES". 

The table name will be different at various times (even though the 
code being executed is always the same), but the error is always 
talking about a failure to lock the tables.

 We are using  two different connections within this one processing 
loop. The one connection shows up as number 7 below, while
the other is number 12. Our expectation is that these two 
connections will operate independently, even though the Java code 
is working with them both within our "transaction".  Our expectation 
is that a rollback on connection 7 will NOT effect the activities 
which had taken place on connection 12 across this same
timespan. Along the same lines we would expect the lock and 
unlock on connection 12 will not affect the transaction under way on 
connection 7. 

Our SQL in this application follows the following pattern. (the 
following is a section from the MysQL log with just one instance of 
the application running):

030125 20:24:29   7 Query   SET autocommit=0
  7 Query   BEGIN
  7 Query   Select * from accountsprovided Where
ExternalID='I06'AND FinServProvID = 'C33'
  7 Query   UPDATE accountsprovided SET
Enabled='Y' WHERE  AccountID = 'CKCBSBF2994309'
  7 Query   Select * from account Where AccountID
=  'CKCBSBF2994309'
  7 Query   UPDATE account SET 
PreLimit=1.00 WHERE  AccountID = 'CKCBSBF2994309'

 12 Query   Lock Table Control Write
 12 Query   Select * From Control
 12 Query   Update Control set NextID = 6999244
 12 Query   Unlock Tables

  7 Query   INSERT INTO productsowned (
VestedValue, Quantity ) VALUES ( 7293.90, 7293.9)

...  About another 40 lines of SQL following this same general 
pattern but using different tables.

  7 Query   Update cachestatus Set UpdatedOn =
null, UpdatedBy =  'XMLWarehouseLoader' Where PrimaryID = 
'CKCBSBF2994310' AND SecondaryID = 'CKCBSBF2994311'
  7 Query   COMMIT
  7 Query   SET autocommit=1

We are unable to determine what other activities taking place on 
the server/tables are causing the conflict. Other applications are 
running against this database which are doing allot of Selects and a 
few updates, all without any explicit transaction control 
(autocommit=1). 

 Will someone please provide us with some insights into what we 
are doing wrong?

It appears that you are trying to use two different locking models, and 
not noticing the problems with doing that until you get enough 
concurrency going in your system (i.e. production load levels)

Heikki can add to this, but it's not usually the best idea in the world 
to mix 'LOCK TABLES' and InnoDB (see 
http://www.innodb.com/ibman.html#InnoDB_restrictions) unless you really 
know how it's going to interact.

From your SQL, it appears that you are attempting to use 'LOCK TABLES' 
to implement a 'sequence' generator. There are two shortcomings with 
your approach. First it won't scale well, because all transactions are 
serializing on a single row...which is probably why you're getting these 
problems. Second, you're locking for _every_ transaction, when if you 
want to use this seque

Transaction problems using InnoDB, "not locked with LOCKTABLES"

[Steff]

Hello,
We are experiencing severe problems when running MySql with 
INNODB in a  production environment.  Applications which work 
fine under light load fail when under production load.

Our MySql environment is as follows:
OS Platform: Windows 2000 Service Pack 2
Machine description:
Compiler   : VC++ 6.0
Architecture   : i686
Total Memory   : 2097151 KB RAM
Server Info3.23.54-max-nt-log
have_innodbYES
innodb_additional_mem_pool_size104857600
innodb_buffer_pool_size1048576000
innodb_data_file_path  ibdata1
innodb_data_home_dir   
innodb_file_io_threads 4
innodb_force_recovery  0
innodb_thread_concurrency  8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown   ON
innodb_flush_method
innodb_lock_wait_timeout   50
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size   5242880
innodb_log_files_in_group  2
innodb_log_group_home_dir  .\
innodb_mirrored_log_groups 1


We have a large block of SQL statements (about 50) which we 
would like to treat as  one transaction.  The software works fine 
when running in a limited environment,  but issues intermittent 
errors when running in production.

All the errors follow the pattern of "java.sql.SQLException: General
error: Table 'productsprovided'  was not locked with LOCK 
TABLES". 

The table name will be different at various times (even though the 
code being executed is always the same), but the error is always 
talking about a failure to lock the tables.

 We are using  two different connections within this one processing 
loop. The one connection shows up as number 7 below, while
the other is number 12. Our expectation is that these two 
connections will operate independently, even though the Java code 
is working with them both within our "transaction".  Our expectation 
is that a rollback on connection 7 will NOT effect the activities 
which had taken place on connection 12 across this same
timespan. Along the same lines we would expect the lock and 
unlock on connection 12 will not affect the transaction under way on 
connection 7. 

Our SQL in this application follows the following pattern. (the 
following is a section from the MysQL log with just one instance of 
the application running):

030125 20:24:29   7 Query   SET autocommit=0
  7 Query   BEGIN
  7 Query   Select * from accountsprovided Where
ExternalID='I06'AND FinServProvID = 'C33'
  7 Query   UPDATE accountsprovided SET
Enabled='Y' WHERE  AccountID = 'CKCBSBF2994309'
  7 Query   Select * from account Where AccountID
=  'CKCBSBF2994309'
  7 Query   UPDATE account SET 
PreLimit=1.00 WHERE  AccountID = 'CKCBSBF2994309'

 12 Query   Lock Table Control Write
 12 Query   Select * From Control
 12 Query   Update Control set NextID = 6999244
 12 Query   Unlock Tables

  7 Query   INSERT INTO productsowned (
VestedValue, Quantity ) VALUES ( 7293.90, 7293.9)

...  About another 40 lines of SQL following this same general 
pattern but using different tables.

  7 Query   Update cachestatus Set UpdatedOn =
null, UpdatedBy =  'XMLWarehouseLoader' Where PrimaryID = 
'CKCBSBF2994310' AND SecondaryID = 'CKCBSBF2994311'
  7 Query   COMMIT
  7 Query   SET autocommit=1

We are unable to determine what other activities taking place on 
the server/tables are causing the conflict. Other applications are 
running against this database which are doing allot of Selects and a 
few updates, all without any explicit transaction control 
(autocommit=1). 

 Will someone please provide us with some insights into what we 
are doing wrong?

Thanks

--
Steff McGonagle
Envisage Information Systems, Inc.

Phone (315) 497-9202 x16
www.EnvisageSystems.com
www.Enveritus.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

bug with MERGE tables and MAX

[spencer]

>Description:
MAX fails to return the correct value in some MERGE table situations.

Based on experimentation I am guessing that the query fails to read all
of the member tables when the query can be performed entirely by reading
the index.

I seem to be able to reproduce this every time on MySQL 3.23.51, .55, and
one of the versions in between (but I forget which).  I don't believe this
is the same as any of the known bugs listed in "MERGE table problems" in
the docs.  But I could be wrong.
>How-To-Repeat:
Here is a sequence of commands that can be source-d:

-- optional
drop table if exists whole;
drop table if exists part1;
drop table if exists part2;

-- create tables
create table part1 (id int(10) not null, value int(10), key (id, value));
create table part2 (id int(10) not null, value int(10), key (id, value));
create table whole (id int(10) not null, value int(10), key (id, value))
  type=merge union=(part1,part2);

-- insert some values
insert into part1 values (1, 100), (2, 200), (3, 300);
insert into part2 values (1, 200), (5, 500);

-- this correctly shows all 5 rows
select * from whole;

-- this correctly shows the 2 values where id = 1
select value from whole where id = 1;

-- this ought to say 200 but says 100 instead
select max(value) from whole where id = 1;

-- this also ought to say 200 but says 100 instead
select floor(max(value)) from whole where id = 1;

-- this correctly says 200
select max(floor(value)) from whole where id = 1;

-- this also correctly says 200
select id, max(value) from whole where id = 1 group by id;
>Fix:
Workaround: in the query, replace MAX(expr) with MAX(FUNC(expr)) where
FUNC is a no-op.  The original problem arose with a datetime column (I
changed it to an int for the test case above), so e.g.
FROM_UNIXTIME(MAX(UNIX_TIMESTAMP(tstamp))) works around the bug.

>Submitter-Id:  
>Originator:S. Spencer Sun
>Organization:
>MySQL support: none
>Synopsis:  MAX fails to return the correct value in some MERGE table situations
>Severity:  serious
>Priority:  high
>Category:  mysql
>Class: sw-bug
>Release:   mysql-3.23.55 (Source distribution)
>Server: /usr/local/bin/mysqladmin  Ver 8.23 Distrib 3.23.55, for pc-linux on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  3.23.55-log
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 10 min 22 sec

Threads: 1  Questions: 121  Slow queries: 0  Opens: 35  Flush tables: 1  Open tables: 
6 Queries per second avg: 0.195
>Environment:

System: Linux pratt 2.2.22-6.2.2smp #2 SMP Tue Oct 22 20:32:19 PDT 2002 i686 unknown
Architecture: i686

Some paths:  /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc'  CFLAGS='-O3 -mpentiumpro'  CXX='gcc'  CXXFLAGS='-O3 
-mpentiumpro -felide-constructors -fno-exceptions -fno-rtti'  LDFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Mar 13  2001 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x1 root root  4106572 Sep  9 09:58 /lib/libc-2.1.3.so
-rw-r--r--1 root root 20336836 Sep  9 09:58 /usr/lib/libc.a
-rw-r--r--1 root root  178 Sep  9 09:58 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local' '--localstatedir=/var/mysql' 
'--enable-assembler' '--with-mysqld-ldflags=-all-static' '--with-raid' 
'--enable-local-infile' 'CFLAGS=-O3 -mpentiumpro' 'CXXFLAGS=-O3 -mpentiumpro 
-felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc'


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

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

Re: Full text search

[MySQL server]

Hi

Try this.

select * from table where upper(text) like upper('%dog%')

Then all words with 'dog' in it, will appears in your query.

Hope this helps

/Frank

>
> I'm working on a full text search that will search through subject
> lines.  The search word is not necessarily a discreet word but could be
> a fragment  like ...
>
>   dog where dog could appear as hotdog, doggone, dognapper etc.
>
> My question is, is there a way of wildcarding the search request to look
> for  word fragments.
>
> I can see in the MySql manual that a search parameter like dog* will
> look  for any word ending in dog.. but that is not sufficient for this
> case.
>
> Anyone know to do this, or if it is possible?
>
> Thanks
> Gord
>
>
>
>
> _
> Protect your PC - get McAfee.com VirusScan Online
> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> 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

wrong Polish translation in mysql 3.23.52 (and previous)

[misiek]

>Description:
Wrong translation of YES/NO for Polish language. They are swapped.
Same bug in mysql 4.0.10.
>How-To-Repeat:
Look into sql/share/polish/errmsg.txt
>Fix:
Here is patch that fixes problem:

diff -urN mysql-3.23.55.org/sql/share/polish/errmsg.txt 
mysql-3.23.55/sql/share/polish/errmsg.txt
--- mysql-3.23.55.org/sql/share/polish/errmsg.txt   Wed Feb 12 00:40:17 2003
+++ mysql-3.23.55/sql/share/polish/errmsg.txt   Wed Feb 12 00:40:46 2003
@@ -7,8 +7,8 @@
 
 "hashchk",
 "isamchk",
-"TAK",
 "NIE",
+"TAK",
 "Nie mo¿na stworzyæ pliku '%-.64s' (Kod b³êdu: %d)",
 "Nie mo¿na stworzyæ tabeli '%-.64s' (Kod b³êdu: %d)",
 "Nie mo¿na stworzyæ bazy danych '%-.64s'. B³?d %d",


>Submitter-Id:  
>Originator:Arkadiusz Miskiewicz
>Organization:
  Arkadiusz Mi¶kiewiczCS at FoE, Wroclaw University of Technology
  [EMAIL PROTECTED]   AM2-6BONE, 1024/3DB19BBD, arekm(at)ircnet, PLD/Linux
>
>MySQL support: none
>Synopsis:  Bug in Polish translation.
>Severity:  non-critical
>Priority:  low
>Category:  mysql
>Class: sw-bug
>Release:   mysql-3.23.52 (PLD Linux Distribution MySQL RPM)

>Environment:
System: Linux arm.t19.ds.pwr.wroc.pl 2.4.20 #1 Tue Jan 21 19:42:35 CET 2003 i686 
AMD_Duron(tm)_processor unknown PLD Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/athlon-pld-linux/3.2.2/specs
Configured with: ../configure --prefix=/usr --infodir=/usr/share/info 
--mandir=/usr/share/man --enable-shared --enable-threads=posix --enable-__cxa_atexit 
--enable-languages=c,c++,f77,gcov,objc,ksi,ada,java --enable-c99 --enable-long-long 
--enable-multilib --enable-nls --with-gnu-as --with-gnu-ld --with-system-zlib 
--with-slibdir=/lib --without-x athlon-pld-linux
Thread model: posix
gcc version 3.2.2 (PLD Linux)
Compilation info: CC='i686-pld-linux-gcc'  CFLAGS='-O2 -march=i686 
-fomit-frame-pointer'  CXX='i686-pld-linux-gcc'  CXXFLAGS='-O2 -march=i686 -fno-rtti 
-fno-exceptions -fomit-frame-pointer'  LDFLAGS='-s'
LIBC: 
lrwxrwxrwx1 root root   13 2002-12-18 10:52 /lib/libc.so.6 -> 
libc-2.3.1.so
-rwxr-xr-x1 root root  1219260 2002-12-11 21:50 /lib/libc-2.3.1.so
-rw-r--r--1 root root  2307406 2002-12-11 21:50 /usr/lib/libc.a
-rwxr-xr-x1 root root  178 2002-12-11 21:37 /usr/lib/libc.so
lrwxr-xr-x1 root root   22 2002-10-16 00:41 /usr/lib/libc-client.so -> 
libc-client.so.2001a.0
lrwxr-xr-x1 root root   22 2002-10-16 00:41 
/usr/lib/libc-client.so.2001a -> libc-client.so.2001a.0
-rwxr-xr-x1 root root   724944 2002-10-05 08:59 
/usr/lib/libc-client.so.2001a.0
Configure command: ./configure 'LDFLAGS=-s' 'CFLAGS=-O2 -march=i686 
-fomit-frame-pointer' 'CXXFLAGS=-O2 -march=i686 -fno-rtti -fno-exceptions 
-fomit-frame-pointer' 'FFLAGS=-O2 -march=i686' 'CPPFLAGS=' 'CC=i686-pld-linux-gcc' 
'CXX=i686-pld-linux-gcc' '--build=i686-pld-linux' '--prefix=/usr' '--exec-prefix=/usr' 
'--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' 
'--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/sbin' 
'--localstatedir=/var/lib/mysql' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' 
'--infodir=/usr/share/info' '-C' '--without-debug' '--enable-shared' '--enable-static' 
'--enable-assembler' '--with-pthread' '--with-named-curses-libs=-lncurses' 
'--with-raid' '--with-extra-charsets=all' '--with-mysqld-user=mysql' 
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--without-readline' 
'--without-docs' '--with-low-memory' '--with-comment=PLD Linux Distribution MySQL RPM' 
'--enable-thread-safe-client' 'build_alias=i686-pld-linux'


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

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

Why no console window?

[Don Barton]

I am new to mysql, but am learning fast.
I have recently downloaded several front-ends to Mysql.  I used some,
deleted others.
In the process of this, I lost the ability to use the default Mysql console
window for inputting data.
The front ends are fine, but I would like to still learn the syntax
necessary for the console window,
that is not always necessary for the front-end products.  When I click on
it, I get a "beep".  Any ideas?
Thanks,

Don



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

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

Full text search

[Gord Muir]

I'm working on a full text search that will search through subject lines. 
The search word is not necessarily a discreet word but could be a fragment 
like ...

	dog where dog could appear as hotdog, doggone, dognapper etc.

My question is, is there a way of wildcarding the search request to look for 
word fragments.

I can see in the MySql manual that a search parameter like dog* will look 
for any word ending in dog.. but that is not sufficient for this case.

Anyone know to do this, or if it is possible?

Thanks
Gord




_
Protect your PC - get McAfee.com VirusScan Online  
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963


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

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

Strange sorting in table

[Viktor Vasiliou]

Hi!

I have a couple of tables in a MySQL database. In one of the tables, PhpMyAdmin sorts 
very strange. ID 1002 is places between ID 21 and ID 22. Any idea why?


Best Regards,
Viktor Vasiliou

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

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: Slow Fulltext Index Creation

[Sergei Golubchik]

Hi!

On Feb 12, Peter Bryant wrote:
> Hi.  I'm adding a fulltext index to two fields in a large (2300MB) table.
> 
> The index created the first 200MB or so of data pretty quickly (in a couple 
> of hours), but progress from there has been very slow.
> 
> After 48 hours the temporary table is only 640MB / 2300MB original table 
> size.

MySQL 3.23, right ?

> I've changed myisam_sort_buffer to 128M in the hopes that that may have 
> sped things up, but to no avail.

It does not apply here.
 
> Does anyone have a) an explanation for why the index creation seems to go 
> slower and slower the more rows there are or b) any ideas on how to sped up 
> the index creation.

a)

Because as btree becomes deeper more time is required to get to the leaf
and to maintain it.

b)

Use MySQL 4.0, increase myisam_sort_buffer (I said "does not apply in
3.23", didn't I ? :). Be sure you have enough disk space for temporary
files.

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: Maximum of 16 indexes per table

[Seth Price]

I have a table of between 90-100 columns and currently 70k rows. I
don't care how long it takes to enter the data, but I would like to
select it as fast as possible. Each column should be theoretically
queried as much as any other. Right now I am using static MyISAM
tables, which seem good enough for now, but in the future I may have
approx. 3,500k rows, so I am a little more worried about my SELECT
performance then.
~Seth


On Tuesday, February 11, 2003, at 12:54 PM, Peter Grigor wrote:


- Original Message -
From: "Seth Price" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 1:43 PM
Subject: Re: Maximum of 16 indexes per table



Has the maximum number of keys in a MyISAM table been tested more
since
this post? Is it possible to get above 64 keys? (I'm going for 90,
btw)
~Seth


Dood!!!

90??? on one table???

Peter
<^_^>
-
Peter Grigor
Hoobly Classified Ads
http://www.hoobly.com



sql


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

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

RE: Safe way to allow users to create databases?

[Sherzod Ruzmetov]

: Is there a safe way to allow ordinary users, who should 
: not have MySQL root privileges, to create databases using 
: any name they choose? It seems to me that I would need to 
: use "GRANT ALL ON *.* TO user WITH GRANT OPTION". This 
: would be dangerous because of the other user's databases 
: and the mysql database itself. All the reference books I 
: have talk about the GRANT CREATE privilege with regard to 
: tables, not databases.

Instead, allow users to create database through a gateway, such
as CGI or some PHP application. Thus you will not have to 
do "GRANT ALL ON *.*". CGI aplication will connect to mysql server
with a specific account priveleged only to CREATE accounts and will
create requested database and grants permissions accordingly.


sherzod



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

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: RAND() Problems in mysql version 3.23.54 and 3.23.55 (linux)

[Sergei Golubchik]

Hi!

On Feb 05, Tue Tønning wrote:
> Hey,
> 
> I have discovered a strange error in the mysql versions mentioned in subj.
> 
> Example.
> A table with 10 entries ...i want to draw 3 randomly
> => "select fieldName from tableName order by RAND() limit 3"
> 
> this have worked for the last year or so.but after upgrading our redhat
> 7.2 with mysql version 3.23.54 it stopped to work. It always chooses the
> same 3 entires.

try simply

 mysql -BNe 'select rand(), rand(), rand(), rand()'
 mysql -BNe 'select rand(), rand(), rand(), rand()'
 mysql -BNe 'select rand(), rand(), rand(), rand()'

to see the problem.
RAND() initialization for new connection isn't very random,
so first few rand() values differ only slightly.

It was fixed in 4.0. I will backport the fix to 3.23.
You can either upgrade to MySQL 4.0, or wait till next 3.23 release,
or use a simple workaround: run

  do benchmark(10,rand());

just after establishing connection - before first SELECT.
 
> Ohh, yeah - we code in PHP..and i have talked today with 3 other
> webdevelopment companies that have been struggeling with the same error
> after upgrading.

Easy - no need to struggling, just submit a bugreport - the bug will be
fixed at once :)
 
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: Safe way to allow users to create databases?

[Stefan Hinz]

Kevin,

> Is there a safe way to allow ordinary users, who should not have MySQL root 
>privileges, to create databases using any name they choose? It seems to me that I 
>would need to use "GRANT ALL ON *.* TO
> user WITH GRANT OPTION". This would be dangerous because of the other user's 
>databases and the mysql database itself. All the reference books I have talk about 
>the GRANT CREATE privilege with
> regard to tables, not databases.

mysql> GRANT create ON *.* TO 'creator'@'localhost';

'creator' can CREATE databases and tables within existing (and
self-created) databases, nothing else. I don't know if that's what you
really want.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3


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

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

re: newbie sql statement help

[The Mindflayer]

This might help.

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

I had the same problem.  Before I found the webpage above, I went with a less 
efficient but simpler way.  I wanted the highest revision ("revision") for each news 
article ("id").  

$SQL = "CREATE TEMPORARY TABLE news_temp SELECT * FROM news ORDER BY revision DESC;";
$db->Execute($SQL);

// at this point, the results are in the temporary table,
// ordered JUST the way we like them :)  Now to pick 
// out the top one for each news id.

$SQL = "SELECT * FROM news_temp GROUP BY id ORDER BY id DESC;";
$result = $db->Execute($SQL);

Something like this might be what you're looking for.  I don't understand exactly how 
you want to order/choose students, but you might just need order by firm rank first, 
then time rank.

I hope that's of use.

-The Mindflayer
-- 
___
http://www.operamail.com
Now with OperaMail Premium for only US$29.99/yr

Powered by Outblaze

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

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

Safe way to allow users to create databases?

[KEVIN ZEMBOWER]

Is there a safe way to allow ordinary users, who should not have MySQL root 
privileges, to create databases using any name they choose? It seems to me that I 
would need to use "GRANT ALL ON *.* TO user WITH GRANT OPTION". This would be 
dangerous because of the other user's databases and the mysql database itself. All the 
reference books I have talk about the GRANT CREATE privilege with regard to tables, 
not databases.

Any suggestions?

Thank you.

-Kevin Zembower

-
E. Kevin Zembower
Unix Administrator
Johns Hopkins University/Center for Communications Programs
111 Market Place, Suite 310
Baltimore, MD  21202
410-659-6139


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

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: client side RAD

[Kim Kohen]

G'day Charles

> I am desperately searching for a GUI RAD environment to develop database
> applications for MySQL.

Try www.servoy.com and you might find what you're looking for

cheers

kim


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

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: IP Addresses -- How to Store

[Michael T. Babcock]

On Tue, Feb 11, 2003 at 03:34:52PM -0500, Peter Grigor wrote:
> Dood, he's not gonna be very happy storing an IP as 16 bits :)
 
Excuse my long day; 32 bits would be much more useful.
-- 
Michael T. Babcock
CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, SQL)
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: need 4.1 binaries

[Ian Li]

  I am waiting for Mysql's UTF8 support as well.
NOt sure if the binaries can solve your issue,
according what I know, we need to specify the charset
information in configuration/compilation time inorder
to enable UTF8.

  I actually tried to build mysql 4.1 out of the
source tree(my system is RH 8.0); the
configuration/compilation/installation went through 
fine, but the server never starts up due to a
complaining about charset not properly compiled.

I wish you can overcome those issues, 
and please let me know.


-- ian
 


--- Jon Hancock <[EMAIL PROTECTED]> wrote:
> We are in a critical state with our app.  We would
> like to dump Oracle 9i
> and go to MySQL.  We have to do the due diligence on
> the port over the next
> few weeks.
> However, we must have full native support for UTF8. 
> Its my understanding
> this is only in MySQL 4.1.
> Our dev and test platform is Win2000.  We have tried
> very unsuccessfully to
> do a Windows build of the 4.1 tree.
> Has anyone tried this?  Can anyone give an estimate
> of when mysql.com will
> publish 4.1 binaries?
> thanks, Jon
> 
> 
>
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list
> archive)
> 
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
>
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> 


__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

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

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

Re: Migrating Microsoft Access to MySQL

[Insanely Great]

there is a tool called SQLyog at http://www.webyog.com/sqlyog/download.html

It has a very good ODBC import tool which will import your Access data to
MySQL without any fuss and its FREE!!!

Insane
- Original Message -
From: "Edward Kim" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, February 12, 2003 1:24 AM
Subject: Migrating Microsoft Access to MySQL


> I am with a small business in Southern California that would like to
migrate
> our MS Access databases to MySQL server.  Are their any computer service
> companies who can help me?
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> 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: socket error

[gerald_clark]

chown -R  mysql.mysql/var/lib/mysql
Substitute the actual location where mysql stores its data files.

J. A. Tovey wrote:


ok :)

all killed now, 

(not quite there yet though :))

I am now getting the error:

030211 16:26:51  mysqld started
030211 16:26:51  /usr/local/mysql/bin/mysqld:
Can't find file: './mysql/host.frm' (errno: 13)
030211 16:26:51  mysqld ended

?


--- Peter Grigor <[EMAIL PROTECTED]> wrote:
 

i'm sorry - could you explain that for a
simpleton :-)
i have no idea what you mean :-/

--- Peter Grigor <[EMAIL PROTECTED]> wrote:
 

- Original Message -
From: "J. A. Tovey" <[EMAIL PROTECTED]>
To: "Peter Grigor" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 9:34 AM
Subject: Re: socket error


   

Type

ps ax | grep mysql

at your shell...this will give a list of
processes that are associated with
mysql.

Then type

kill 1234 1235

where 1234 and 1235 are the process numbers of
the processes returned by ps.

Peter
<^_^>
-
Peter Grigor
Hoobly Classified Ads
http://www.hoobly.com


























   



__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

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

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


 




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

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

Re: IP Addresses -- How to Store

[Peter Grigor]

- Original Message - 
From: "Michael T. Babcock" <[EMAIL PROTECTED]>
To: "Aaron Conaway" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 3:25 PM
Subject: Re: IP Addresses -- How to Store


> On Tue, Feb 11, 2003 at 01:15:01PM -0500, Aaron Conaway wrote:
> > I'm looking to develop a database of IP addresses for the company and
> > have, of course, chosen mySQL as the backend.  I want the app to add
> > (remove, etc.) a host, giving its hostname and segment.  The app will
> > add the next available address to the database and, looking at the
> > segment, provide the subnet mask and default gateway for said new host.
> > I've got the db structure written out, but I'm having issues on how to
> > store the data like address, subnet mask, default gateway.
> 
> Consider familiarizing yourself with bit-level network masking
> math and then store your IP addresses as 16 bit long integer
> values (until you're storing ipv6 addresses, of course).  Store
> your network mask as either the 16 bit value of the mask or if
> you care about space, a tinyint of how many bits are in the mask.


Dood, he's not gonna be very happy storing an IP as 16 bits :)
-
Peter Grigor
Hoobly Classified Ads
http://www.hoobly.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

Slow Fulltext Index Creation

[Peter Bryant]

Hi.  I'm adding a fulltext index to two fields in a large (2300MB) table.

The index created the first 200MB or so of data pretty quickly (in a couple 
of hours), but progress from there has been very slow.

After 48 hours the temporary table is only 640MB / 2300MB original table 
size.

I've changed myisam_sort_buffer to 128M in the hopes that that may have sped 
things up, but to no avail.

Does anyone have a) an explanation for why the index creation seems to go 
slower and slower the more rows there are or b) any ideas on how to sped up 
the index creation.

Regards, Peter Bryant

(sql)

_
The new MSN 8: smart spam 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

Compiling on Solaris 2.6

[Jim Sobeck - comcast]

I have tried multiple times to build a version of 3.23.55 with support for
innodb under Solaris 2.6.  I can not upgrade the version of the OS.

Here is the error:

source='mysql.cc' object='mysql.o' libtool=no \
depfile='.deps/mysql.Po' tmpdepfile='.deps/mysql.TPo' \
depmode=gcc3 /bin/ksh ../depcomp \
gcc -DUNDEF_THREADS_HACK -I. -I. -I.. -I./../include -I../include -I./.. -I.
. -I
..-O3 -DDBUG_OFF -O6  -fno-implicit-templates -fno-exceptions -fno-rtti 
-DUS
E_MYSYS_NEW -DDEFINE_CXA_PURE_VIRTUAL -DHAVE_RWLOCK_T -c -o mysql.o `test -f
mys
ql.cc || echo './'`mysql.cc
mysql.cc: In function `int sql_connect(char*, char*, char*, char*, unsigned
   int)':
mysql.cc:2171: `sleep' undeclared (first use this function)
mysql.cc:2171: (Each undeclared identifier is reported only once for each
   function it appears in.)
make[2]: *** [mysql.o] Error 1

Any ideas or does anyone no where I can get a version that supports innodb
for Solaris 2.6?



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

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: IP Addresses -- How to Store

[Michael T. Babcock]

On Tue, Feb 11, 2003 at 01:15:01PM -0500, Aaron Conaway wrote:
> I'm looking to develop a database of IP addresses for the company and
> have, of course, chosen mySQL as the backend.  I want the app to add
> (remove, etc.) a host, giving its hostname and segment.  The app will
> add the next available address to the database and, looking at the
> segment, provide the subnet mask and default gateway for said new host.
> I've got the db structure written out, but I'm having issues on how to
> store the data like address, subnet mask, default gateway.

Consider familiarizing yourself with bit-level network masking
math and then store your IP addresses as 16 bit long integer
values (until you're storing ipv6 addresses, of course).  Store
your network mask as either the 16 bit value of the mask or if
you care about space, a tinyint of how many bits are in the mask.
-- 
Michael T. Babcock
CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc)
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: Robot Replies (WAS: Re: support question (win98_se) (auto))

[Michael T. Babcock]

On Sun, Feb 09, 2003 at 03:11:54PM -0700, Doug Thompson wrote:
> Am I the only one that equates these robot replies with spam?  Most of
> them are as meaningful as talking to micro$oft.

I don't know why they're sent to the list and not to the Sender address.

> >
> > * http://www.mysql.com/doc/en/Business_Services_Support.html
> > * http://www.mysql.com/doc/en/Which_OS.html
> > * http://www.mysql.com/doc/en/MySQL_licenses.html
> > * http://www.mysql.com/doc/en/Support.html
> > * http://www.mysql.com/doc/en/Languages.html
> >
> >This was an automated response to your email 'support question (win98_se)'.
-- 
Michael T. Babcock
CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc)
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 book TOC

[Michael T. Babcock]

On Sat, Feb 08, 2003 at 10:47:55PM -0800, Nasser Ossareh wrote:
> 
> SQL in the "What is SQL?" phrase threw me... I thought
> you were talking about MS SQL..

SQL is and should be known as a query language, not a product.
It should be perhaps clarified the first time it is used as
not refering to the Microsoft SQL Server product often mis-
named as 'SQL' itself (by people who don't know what SQL is).

> If I were you, I would explain relational databases
> and normalization (at list to normal form III).

Very well described by many other books; especially Oracle
certification books.

-- 
Michael T. Babcock
CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc)
http://www.fibrespeed.net/~mbabcock/

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

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

RE: Strange Updates

[Sherzod Ruzmetov]

: > select count(*) from Yellow WHERE MATCH(street_add) 
: AGAINST('+GUIDO
: > +MIGLIOLI' IN BOOLEAN MODE) AND street_short = '';
: >
: > and the result was 50 rows in set.
: >
: > But when I ran the update:
: > UPDATE yellowtest.Yellow SET street_short = 'MIGLIOLI' WHERE
: > MATCH(street_add) AGAINST('+GUIDO +MIGLIOLI' IN BOOLEAN 
: MODE) AND
: > street_short = '';
: >
: > the result was:
: >
: > Query OK, 0 rows affected (1 min 53.89 sec)
: > Rows matched: 0  Changed: 0  Warnings: 0


Most likely you're referensing different tables. Make sure
that "yellowtest.Yellow" in the second query in fact points to "Yellow" 
database meant in the first query.

Sherzod



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

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

Migrating Microsoft Access to MySQL

[Edward Kim]

I am with a small business in Southern California that would like to migrate
our MS Access databases to MySQL server.  Are their any computer service
companies who can help me?


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

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

re: problem with the load data

[Egor Egorov]

On Tuesday 11 February 2003 18:13, Edgard Dapás wrote:
> I'm trying to load a text file, I've extract from access, and Ihave the
> following error
>
>
> mysql> LOAD DATA INFILE '/home/dapasedg/provedores.txt' INTO TABLE
> proveedores -> FIELDS TERMINATED BY '|' ENCLOSED BY '"';
> ERROR 13: Can't get stat of '/home/dapasedg/provedores.txt' (Errcode: 13)

$ perror 13
Error code  13:  Permission denied

MySQL server doesn't have permission to read from this home directory.



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

[Victoria Reznichenko]

On Monday 10 February 2003 17:54, Octavian Rasnita wrote:

> Please tell me what should I do to make MySQL cache all the queries that
> can be cached.
>
> Can I do this if I am not the administrator of MySQL server? (on a session
> basis...).

Take a look at query cache:
http://www.mysql.com/doc/en/Query_Cache.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: How to modify ft_min_word_len?

[Egor Egorov]

On Monday 10 February 2003 17:55, Octavian Rasnita wrote:

> Please tell me how to modify the variable ft_min_word_len.
>
> I've tried:
>
> set ft_min_word_len=2;
> set @ft_min_word_len=2;
>
> The second creates a new variable I think and the first one tells me that
> there is no such variable.

You should define it in my.cnf file and then rebuild FULLTEXT indexes.



-- 
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: Strange Updates

[Victoria Reznichenko]

On Tuesday 11 February 2003 16:21, Daniele Lo Re wrote:

> I got weird behaviour from Mysql while doing some updates.
>
> Before doing the update I did a count, like this:
>
>
>
> select count(*) from Yellow WHERE MATCH(street_add) AGAINST('+GUIDO
> +MIGLIOLI' IN BOOLEAN MODE) AND street_short = '';
>
> and the result was 50 rows in set.
>
> But when I ran the update:
> UPDATE yellowtest.Yellow SET street_short = 'MIGLIOLI' WHERE
> MATCH(street_add) AGAINST('+GUIDO +MIGLIOLI' IN BOOLEAN MODE) AND
> street_short = '';
>
> the result was:
>
> Query OK, 0 rows affected (1 min 53.89 sec)
> Rows matched: 0  Changed: 0  Warnings: 0
>
> I don't understand what's going on, since the WHERE clause is identical
> in both queries.
>
> What do you think?

It works fine for me ..
What version of MySQL server do you use?
Could you create a repeatable test case?


-- 
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: IP Addresses -- How to Store

[Aaron Conaway]

Thanks to Peter, Ravi, and Dan.  That's exactly what I needed to know.

--
Aaron Conaway
Network Engineer III
Verisign, Inc. -- Telecom Services Division
http://www.verisign.com
Office:  912.527.4343
Fax:  912.527.4014


-Original Message-
From: Peter Hicks [mailto:[EMAIL PROTECTED]] 
Sent: Tuesday, February 11, 2003 1:30 PM
To: Dan Nelson
Cc: Aaron Conaway; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: IP Addresses -- How to Store


On Tue, 11 Feb 2003, Dan Nelson wrote:

> Store your addresses as INTs, so you would have three fields: 
> "address", "netmask", and "gateway".  You can either encode the values

> yourself, or use mysql's INET_NTOA()/INET_ATON() functions.

...and beware, INET_NTOA/ATON calls aren't compatible (as far as I can
see) with PHP's equivilent calls.  PHP uses signed integers, and MySQL
not.

Has anyone come up with a workaround to this on either the MySQL or PHP
sides?


Peter.


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

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

Re: encrypted password

[Sergei Golubchik]

Hi!

On Feb 04, Jianping Zhu wrote:
> 
> I have mysql in a redhat machine. I need to use mysql do user
> authentication to a website.
> I have a table like following.
> 
> +--+--+
> | username | passwd   |
> +--+--+
> | jianping | jian1830 |
> | chichi   | jian1830 |
> +--+--+
> 
> I want the passwd field not to be plain text but encrypted. how can i do
> that?
> 
> Thanks.

As Tonu has pointed out you should NOT use PASSWORD() function.
It's is only used to encrypt passwords used in MySQL authentification
protocol. Furthermore, it was changed in MySQL 4.1, so you application
won't be able to work with MySQL 4.1.

Use MD5() or SHA1() functions.
SHA1() was added recently, and MD5() is available for a long time
already.

Also, if you'll have Perl or PHP interface (or, actually, any other
custom written interface), it's better to calculate md5() in the
application (e.g. it's Digest::MD5 module in perl), like this:

  use Digest::MD5  qw(md5_hex);
  $sth->do('INSERT INTO table VALUES("jianping","' . md5_hex('jian1830') . '")');

instead of

  $sth->do('INSERT INTO table VALUES("jianping",md5("jian1830"))');

so that plain-text passwords won't show up in update or binary logs.
 
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

newbie sql statement help

[Bob Ramsey]

The people who run our mysql server are using version 3.23.51, so I don't 
have access to some of version 4's features like sub selectes.

I have a table with data like this:

firm	firm_rank	time	time_rank	student
A	1		10	1		jones
A	1		10	1		smith
A	1		10	1		alvin
A	2		10	1		bob
A	2		10	2		charlie
B	2		10	1		jones
B	2		10	1		smith

and so forth.  Each student can rank a firm 1-10 and an interview time 
1-10.  There are usually about 30 firms and about 200 time slots.

My goal is to pick a random student from the highest firm rank and then 
from the highest time rank for each time.  So someone who ranks the firm as 
2 and the time as 1 loses to someone who ranks the firm as 1 and the time 
as 3.  If Jones is picked for Firm A at 10, she is ineligible for any more 
Firm A slots and all  10 o'clock slots regardless of firm.

I can almost see a way to use the unique, random, and order by features to 
make one really complex select statement that just pulls everything out, 
but I can't do it.  If it can't be done, I'm willing to loop through doing 
individual firms and just selecting out a random high ranking person for 
each time, but I can't see that either.

I'm using mysql and php.  Any ideas?  I'd hate to just keep looping through 
and making lot's of small select statements, which I know I can do.  It 
seems like there's a better way.

Thanks,

bob




==
Bob Ramsey   Applications Development & Support II
ph:  1(319)335-9956  216 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==


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

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: client side RAD**

[Kevin Gale]

Charles.

Have you tried Omnis Studio (www.omnis.net). It's a 4GL RAD which
supports Mac Classic, OSX, Windows, Linux and Solaris.
I use the Mac and Windows version nearly everyday and find it an
excellent tool for creating database front-ends (mySQL access is via
ODBC).

The Omnis-Underground hosts a mailing list (www.omnis-underground.com)
which all of the main developers subscribe to.


Let me know if I can be any help.


Kev.

>--
>From:  Charles Lewis
>Sent:  Tuesday, February 11, 2003 18:13 PM
>To:[EMAIL PROTECTED]
>Subject:   client side RAD
>
>I am desperately searching for a GUI RAD environment to develop
>database
>applications for MySQL. I'm not talking about database administration
>such as provided by ControlCenter or SQLyog. I'm talking about the
>ability to build formatted forms/screens, queries and reports along the
>lines of Access. In fact, I would use Access except it's not platform
>independent. 
>
>I have scoured the internet and I can't seem to find anything that
>comes
>near to the features found in Access. Rekall comes close and has many
>attractive qualities, but it not a very mature product (yet). I'm going
>to keep playing with Rekall, but I was wondering if there is anything
>else out there that I could take a look at. I need something that is
>flexible/powerful enough to meet a variety of complex needs, such as
>you
>might find in various departments of university. At the same time, I
>definitely need to be able to develop/customize applications rapidly.
>And finally, I need the finished product (and preferably the
>development
>environment) to be GUI.
>
>Any suggestions?
>
>Charles Lewis
>[EMAIL PROTECTED]


This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk


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

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 of 16 indexes per table

[Seth Price]

Has the maximum number of keys in a MyISAM table been tested more since 
this post? Is it possible to get above 64 keys? (I'm going for 90, btw)
~Seth

to get past spam filter: MySQL sql query

Subject: Re: Maximum of 16 indexes per table
From: Michael Widenius
Date: Fri, 30 Jul 1999 23:57:19 +0300 (EEST)

> "Troy" == Troy Grady <[EMAIL PROTECTED]> writes:

>> (The maximum number of keys are 32 because MyISAM uses a bitmap in a
>> 'long' to check which keys are in use)
>>
>> Regards,
>> Monty


Troy> Section 10.17 of the manual, "Table Types", states the following 
about
Troy> MYISAM:

Troy> "Maximum number of keys/table enlarged to 32 as default. This 
can be
Troy> enlarged to 64 without having to recompile myisamchk."

Troy> Are 64 indexes per table possible?  If so, how?

Troy> Regards,

Troy> Troy

Hi!

To do this you have to change some constants in myisam.h and unireg.h
and also change the key_map typedef from long to longlong.

The main problem is that we haven't tested the code with more than 32
keys.  The main problem is that I may have have missed some
key_map variable and this is still declared as 'ulong' instead of type
'key_map'.  In theory it wouldn't be that hard to change 3.23.2 to use
64 keys...

Regards,
Monty


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

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

Re: Query Help

[Mirko]

You have one column detail - What do you mean by detail1, detail2 and so?



=


On Tue, 11 Feb 2003 13:13:31 +, Jeff Snoxell <[EMAIL PROTECTED]> 
wrote:

Hi,

How can I write a MySQL query to grab single lines of the form:

ColumnNames: EnquiryID, Name, Address, Detail1, Detail2, Detail3, Detail4

From two joined tables:

Tbl1: Enquiries
--
ID
Name
Address
Etc


Tb12: Details

ID
EnquiryID
Detail
Etc


There is an imposed maximum number of details per enquiry (say 4 for this 
example).

Many thanks,


Jeff


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

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





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

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

Re: IP Addresses -- How to Store

[Peter Hicks]

On Tue, 11 Feb 2003, Dan Nelson wrote:

> Store your addresses as INTs, so you would have three fields:
> "address", "netmask", and "gateway".  You can either encode the values
> yourself, or use mysql's INET_NTOA()/INET_ATON() functions.

...and beware, INET_NTOA/ATON calls aren't compatible (as far as I can see)
with PHP's equivilent calls.  PHP uses signed integers, and MySQL not.

Has anyone come up with a workaround to this on either the MySQL or PHP sides?


Peter.


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

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

Re: IP Addresses -- How to Store

[Dan Nelson]

In the last episode (Feb 11), Aaron Conaway said:
> I'm looking to develop a database of IP addresses for the company and
> have, of course, chosen mySQL as the backend.  I want the app to add
> (remove, etc.) a host, giving its hostname and segment.  The app will
> add the next available address to the database and, looking at the
> segment, provide the subnet mask and default gateway for said new
> host. I've got the db structure written out, but I'm having issues on
> how to store the data like address, subnet mask, default gateway.
> 
> Our network is very diverse, covering many ranges of addresses and,
> more importantly, many subnet masks.  Some are 24bit, some are 16bit,
> and some are 23bit.  What is the best way to store this data in
> mySQL?
> 
> If I do a varchar, then how do I restrict the data to between 0 and
> 255? Also, how would I manipulate any address with a classless mask? 
> I'm thinking storage as binary so I can parse out a byte of binary
> data and present it as a decimal number; this also limits the data to
> numbers between 0 and 255, based on an 8-bit byte.  The problem is
> that I have no clue how to store such.

Store your addresses as INTs, so you would have three fields:
"address", "netmask", and "gateway".  You can either encode the values
yourself, or use mysql's INET_NTOA()/INET_ATON() functions.

-- 
Dan Nelson
[EMAIL PROTECTED]
sql, query


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

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

IP Addresses -- How to Store

[Aaron Conaway]

I'm looking to develop a database of IP addresses for the company and
have, of course, chosen mySQL as the backend.  I want the app to add
(remove, etc.) a host, giving its hostname and segment.  The app will
add the next available address to the database and, looking at the
segment, provide the subnet mask and default gateway for said new host.
I've got the db structure written out, but I'm having issues on how to
store the data like address, subnet mask, default gateway.

Our network is very diverse, covering many ranges of addresses and, more
importantly, many subnet masks.  Some are 24bit, some are 16bit, and
some are 23bit.  What is the best way to store this data in mySQL?

If I do a varchar, then how do I restrict the data to between 0 and 255?
Also, how would I manipulate any address with a classless mask?  I'm
thinking storage as binary so I can parse out a byte of binary data and
present it as a decimal number; this also limits the data to numbers
between 0 and 255, based on an 8-bit byte.  The problem is that I have
no clue how to store such.

I'm running around in circles on this one.  Can some point me to a
resource that can shed some light on this type of data storage?

-- 
Aaron Conaway 
Network Engineer III 
Verisign, Inc. -- Telecom Services Division 
http://www.verisign.com 
Office:  912.527.4343 
Fax:  912.527.4014 

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

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

client side RAD

[Charles Lewis]

I am desperately searching for a GUI RAD environment to develop database
applications for MySQL. I'm not talking about database administration
such as provided by ControlCenter or SQLyog. I'm talking about the
ability to build formatted forms/screens, queries and reports along the
lines of Access. In fact, I would use Access except it's not platform
independent. 

I have scoured the internet and I can't seem to find anything that comes
near to the features found in Access. Rekall comes close and has many
attractive qualities, but it not a very mature product (yet). I'm going
to keep playing with Rekall, but I was wondering if there is anything
else out there that I could take a look at. I need something that is
flexible/powerful enough to meet a variety of complex needs, such as you
might find in various departments of university. At the same time, I
definitely need to be able to develop/customize applications rapidly.
And finally, I need the finished product (and preferably the development
environment) to be GUI.

Any suggestions?

Charles Lewis
[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

3.23.41: auto_increment bug with TRIPLE primary key? (really strange)

[The Mindflayer]

mysql -V:
mysql  Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu (i386)
(Upgrading may not be an option.)

Not subscribed to the list, so please CC me on any responses.  

I'd submit this to bugs@, but my MySQL version is way out of date and I'm not sure if 
the current version has been fixed.  A quick search on the bugs@ archives didn't net 
me anything.  I'm hoping this general discussion list can shed some light.

Please read the whole message first, because some strange things happened when I wrote 
test code.  Example SQL queries are provided at the bottom of this message.

I'm using the nifty multi-column-primary-key auto_increment feature of MySQL as 
described at
http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html

In one table I have:
|Field   |Type  |Null|Key|Default|Extra  |
|id  |bigint(20)||PRI|1  |   |
|revision|bigint(20)||PRI|NULL   |auto_increment |

(and other columns, snipped)

And it works fine.  Eg:
insert 1,NULL :  1 1
insert 1,NULL :  1 2
insert 2,NULL :  2 1
insert 2,NULL :  2 2
insert 5,NULL :  5 1

In another table I have:
|Field|Type  |Null|Key|Default|Extra |
|news_id  |bigint(20)||PRI|0  |  |
|news_revision|bigint(20)||PRI|0  |  |
|id   |bigint(20)||PRI|NULL   |auto_increment|

(and other columns, snipped)

And the "id" field doesn't behave as expected:
insert 1,1,NULL : 1 1 1
insert 1,1,NULL : 1 1 2
insert 1,2,NULL : 1 2 3  *BAD: should be 1 2 1
insert 2,1,NULL : 2 1 4  *BAD: should be 2 1 1
insert 2,2,NULL : 2 2 5  *BAD: should be 2 2 1

As you can see, the id field is behaving as an ordinary single-primary-key 
autoincrement.

Now, when I went to write test code for you all, even stranger things happened.  The 
part where I expected a failure didn't fail.  I had to actually dump my existing table 
to get code that would fail.

Here it is:

#this works, as expected
#
create temporary table double_key (id bigint not null default 1, revision bigint not 
null auto_increment, primary key (id,revision));
insert into double_key values(1,NULL);
insert into double_key values(1,NULL);
insert into double_key values(2,NULL);
insert into double_key values(2,NULL);
insert into double_key values(5,NULL);

#this ...WORKS!?  It should fail!
#
create temporary table triple_key (news_id bigint not null default 0, news_revision 
bigint not null default 0, id bigint not null auto_increment, primary key 
(news_id,news_revision,id));
insert into triple_key values(1,1,NULL);
insert into triple_key values(1,1,NULL);
insert into triple_key values(1,2,NULL);
insert into triple_key values(2,1,NULL);
insert into triple_key values(2,2,NULL);

#This is actually from my DB, more or less.
#This fails, as expected.
#I didn't want to cram all the columns and data down
#your throats, but seem to be required for it to fail.
#it's pretty much identical to the triple_key above, except
#with extra columns.
#
CREATE temporary TABLE news_images (
  news_id bigint not null default 0,
  news_revision bigint not null default 0,
  id bigint not null auto_increment,
  image_URL varchar(255) NOT NULL default '',
  caption varchar(255) default NULL,
  image_alt varchar(50) default NULL,
  creator bigint(20) default NULL,
  original varchar(255) default NULL,
  PRIMARY KEY  (id,news_revision,news_id)
) TYPE=MyISAM;
#
INSERT INTO news_images VALUES 
(4,4,NULL,'','',NULL,NULL,NULL),(4,4,NULL,'',NULL,NULL,NULL,NULL),(5,1,NULL,'','','',1,''),(5,1,NULL,'','','',1,''),(5,1,NULL,'','','',1,''),(5,1,NULL,'','','',1,''),(13,1,NULL,'','','',1,'');

Somehow the extra columns are confusing MySQL into misbehaving.

Do you know if and when (which version) this bug was fixed?
(Should I post this to the bugs mailing list?)
Do you know any reasonable workarounds?

Thanks in advance, everyone.

-The Mindflayer
-- 
___
http://www.operamail.com
Now with OperaMail Premium for only US$29.99/yr

Powered by Outblaze

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

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

mysql privs table level security

[Andrew Braithwaite]

Hi all,

Can anyone help me please?

I have a database with hundreds of tables and I want to lock down certain
tables for select only.

The order of presidence for mysql privs as far as I can tell is that if
there are database level privileges, these override the table level privs.
I don't want to have to issue a grant for each of the tables in the db - but
only wish to specify which tables need to be read only.

I could go into the datadir and chmod 440 the files in question, but this
would deny write privs to all users wheras I would like certain users to
have write access.

Any ideas?

Cheers,

Andrew

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: mysql overhead

[Stefan Hinz]

Ciprian,

> I am using PhpMyAdmin and today I got a red message saying that one
> table has overhead and I need to optimize the table. 
> This is the first time I got this message, I looked online and I
> understood why it shows. But my question is: can I do something to avoid
> it? What effects daoe the overhead have on a table?

MyISAM tables (the default table type in MySQL) become fragmented
when you delete records (this can even happen when you update records,
if your table format is dynamic). You cannot avoid this effect if
you're using MyISAM tables.

If you don't optimize fragmented MyISAM tables, they will become slow
to scan because the operating system will need more time for disk
operations.

Details: http://www.mysql.com/doc/en/OPTIMIZE_TABLE.html

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3


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

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

Re: problem with the load data

[Stefan Hinz]

Edgard,

> I'm trying to load a text file, I've extract from access, and Ihave the
> following error
mysql>> LOAD DATA INFILE '/home/dapasedg/provedores.txt' INTO TABLE proveedores
> -> FIELDS TERMINATED BY '|' ENCLOSED BY '"';
> ERROR 13: Can't get stat of '/home/dapasedg/provedores.txt' (Errcode: 13)

Error 13 means "permission denied". I sure hope your mysql server
cannot access your home directory ;)

You should move the file to a location where mysqld has read access!

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3


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

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

Re[2]: socket error

[Stefan Hinz]

J.A.,

>> > Can't find file: './mysql/host.frm' (errno: 13)
>> > 030211 16:26:51  mysqld ended

>> Did you run the mysql_install_db script??

> yes, i ran everything again

I bet my b* you didn't, or maybe something went wrong when running it.

If mysqld cannot find host.frm, this means the grant tables aren't set
up. The grant tables are in a special database called "mysql", and the
purpose of the script is to install them. Check if the database and
its tables are there, usually under /var/lib/mysql/. Then check if the
file permissions are okay, this should be something like 700 for
mysql:daemon for all subdirectories (including the mysql subdir).

If you cannot get the server to start at all, try this:

1. CD into /usr/local/mysql/bin/
2. Start mysqld this way: mysqld --skip-grant-tables

If this starts mysqld successfully, you can be sure it's a problem
with your grant tables. You should NEVER use mysqld this way in an
unsecure environment, this is strictly for test purposes. Everybody
will be able to connect to your server and perform everything on it
when mysqld has been started with this option.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3


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

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

Re: RedHat 8.0 and MySQL 3.23.55 install problem

[Csongor Fagyal]

I get this message back:
	Failed dependencies: 
	MySQL-DBI-perl-bin is needed by MySQL-bench-3.23.55-1
I know I have this dependency installed, but I think I know where the
problem is just not sure how to fix it.  Here is what I get when I do a
'rpm -qa | grep DBI-perl':
	Mysql-DBI-perl-bin-1.1825-1

I think the problem is in the name of the dependency, 'bench' is looking
for MySQL-DBI-perl-bin and I have Mysql-DBI-perl-bin installed - 'MySQL'
and 'Mysql' do not match so the dependency is failing.  AM I correct in
this?  If so how do I fix it?
 

Not very nice, but I would simply use --nodeps.

- Cs.


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

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

RedHat 8.0 and MySQL 3.23.55 install problem

[Wes Hegge]

I am having problems installing MySQL 3.23.55 on RedHat 8.0.  The
version that came with RedHat will not let me remotely login.  I found a
thread at RedHat that said that this is a known problem. One of the
suggestions was to upgrade MySQL.  That is what I am attempting to do. 
This is what happens.

I have these files:
MySQL-3.23.55-1.i386.rpm
MySQL-bench-3.23.55-1.i386.rpm
MySQL-client-3.23.55-1.i386.rpm
MySQL-devel-3.23.55-1.i386.rpm
MySQL-shared-3.23.55-1.i386.rpm
I do this command to upgrade:
rpm -Uvh MySQL*
I get this message back:
Failed dependencies: 
MySQL-DBI-perl-bin is needed by MySQL-bench-3.23.55-1
I know I have this dependency installed, but I think I know where the
problem is just not sure how to fix it.  Here is what I get when I do a
'rpm -qa | grep DBI-perl':
Mysql-DBI-perl-bin-1.1825-1

I think the problem is in the name of the dependency, 'bench' is looking
for MySQL-DBI-perl-bin and I have Mysql-DBI-perl-bin installed - 'MySQL'
and 'Mysql' do not match so the dependency is failing.  AM I correct in
this?  If so how do I fix it?

Thanks for the help,

Wes Hegge
SignalBlast.com, Inc.
p: (815) 397-1700
e: [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: optimizing left join query

[Saqib . N . Ali]

Diana,

Thanks for the suggestion. I completely forgot to add the index/primary
key. Now it works just fine :) Thanks again.

In Peace,
Saqib Ali
"I fear, if I rebel against my Lord, the retribution of an Awful Day (The
Day of Resurrection)" Al-Quran 6:15
http://docbook.sc-icc.org


|+--->
||  Diana Soares |
||  <[EMAIL PROTECTED]|
||  p.pt>|
||  No Phone Info|
||  Available|
||   |
||  02/11/2003   |
||  08:59 AM |
||   |
|+--->
  
>|
  |
|
  |   To: [EMAIL PROTECTED]  
|
  |   cc: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>  
|
  |   Subject: Re: optimizing left join query  
|
  
>|




Your user_info table is not indexed...
(user_info.uid should have an index)
Check the manual:
http://www.mysql.com/doc/en/MySQL_indexes.html


On Tue, 2003-02-11 at 16:16, [EMAIL PROTECTED] wrote:
> Hello All,
>
> I have the following 2 tables and "LEFT join" query as follows:
>
>
> mysql> describe user_lic;
> +++--+-+-+---+
> | Field  | Type   | Null | Key | Default | Extra |
> +++--+-+-+---+
> | license_id | bigint(20) |  | PRI | 0   |   |
> | user_id| bigint(20) |  | PRI | 0   |   |
> | location   | char(30)   | YES  | | NULL|   |
> +++--+-+-+---+
>
>
> mysql> describe user_info;
> +--+--+--+-+-+---+
> | Field| Type | Null | Key | Default | Extra |
> +--+--+--+-+-+---+
> | uid  | int(10) unsigned | YES  | | NULL|   |
> | location | char(30) | YES  | | NULL|   |
> | mail | char(100)| YES  | | NULL|   |
> +--+--+--+-+-+---+
>
> query = SELECT * FROM  user_lic LEFT JOIN user_info ON
> user_lic.user_id=user_info.uid WHERE user_info.uid is NULL  AND
> (user_lic.location = 'Rochester, US')
>
>
> When I run this query on a 'user_lic' table with 1000+ rows, it takes
about
> 20+ mins to complete the query. However a similar query on a Oracle DB
> takes couple of minutes.
>
> I am using a high-end Sun Server, connected to a Xiotech SAN, using Fiber
> Optics. I dont think the Hardware is the bottle neck.
>
> I am wondering how I can optimize the Query/MySQL DB to make this query
go
> faster.
>
>
>
> In Peace,
> Saqib Ali
> "I fear, if I rebel against my Lord, the retribution of an Awful Day (The
> Day of Resurrection)" Al-Quran 6:15
> http://docbook.sc-icc.org
>
--
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





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

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

Fw: Very slow request with many ORs in where parts

[Artem Koutchine]

Yes of course. Here you go:

mysql> EXPLAIN SELECT DISTINCT w0.l_id FROM   law_words as w0
->   inner join law_words as w1 on w0.l_id=w1.l_id
-> WHERE
-> w0.w_id  IN (258,282,287, 615, 1101, 1949, 1968, 3417, 3574,
3578, 3643,
-> 4345,4768, 5297, 5976,6133, 7243,7245, 9271, 9348, 11146,
11150, 11172,
->  11232,11847, 12542, 12859, 14811, 24839, 26653,27662)  AND
-> w1.w_id IN (405, 2017,2192, 2592, 2595, 2603, 2981, 4055, 4068,
4346,5755, 6480, 9384,
-> 9408, 11513, 11514, 12126, 12134, 12638, 13052, 13643, 13769,
13836, 13945, 14154, 14693, 14867,
->  14980, 15518, 15557, 17830, 19005, 19051, 19247, 20176, 20926,
22364, 22365, 22366, 22732, 24668,
-> 24793, 24956,  25286, 26242, 26665, 26847, 27144, 27348, 27815,
28494, 30910, 31878, 32161,
-> 33586,  34396);

+---+---+---+-+-+--+--+---
---+
| table | type  | possible_keys | key | key_len | ref  | rows |
Extra|
+---+---+---+-+-+--+--+---
---+
| w0| range | PRIMARY   | PRIMARY |   4 | NULL |  473 |
where used; Using index; Using temporary |
| w1| range | PRIMARY   | PRIMARY |   4 | NULL |  479 |
where used; Using index; Distinct|
+---+---+---+-+-+--+--+---
---+
2 rows in set (0.01 sec)



- Original Message -
From: "Victor Pendleton" <[EMAIL PROTECTED]>
To: "'Artem Koutchine'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 5:29 PM
Subject: RE: Very slow request with many ORs in where parts


> Can you post your explain plan?
>
> -Original Message-
> From: Artem Koutchine [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, February 11, 2003 8:23 AM
> To: [EMAIL PROTECTED]
> Subject: Very slow request with many ORs in where parts
>
>
> Hi!
>
> I have the following table:
>
> create table law_words (
> l_id int unsigned not null,
> w_id int unsigned not null,
> primary key (w_id, l_id)
> );
>
>
> The request is:
>
> SELECT DISTINCT w0.l_id FROM law_words as w0
> inner join law_words as w1 on w0.l_id=w1.l_id
> where  w0.w_id in (258,282,...  about 30 ids) and
> w1.w_id in (405, 2017, and so on about 50 ids);
>
> The basic idea is thart law_words hold index of
> words (w_id) for each law (l_id), so law can be found by words,
which
> are specified by user and the their ids are looked up in
> vocabular.
>
> Now law_words has 288000 records and that request takes
> about 1 second on a pc with 1GB of RAM and dual Pentium III XEON
> 550Mhz,
> which is TOO MUCH!
> Explain show thart mysql is
> using ' range' and primary index, and about 400 records for each
> table.
>
> For three specified words request takes about forever, so no search
is
> possible for three words. The request is using INNER JOIN to get
> the words in the 'AND' manner (laws which contain ALL specified
> words).
>
> I don't understand what i am doing wrong, since i thought it is
> a basic technology behind any search engine.
>
> Please, help, if you can.
>
> Regards, Artem
>
>
> 
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
>
> 
-
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
>


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

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

Re: socket error

[Peter Grigor]

- Original Message -
From: "J. A. Tovey" <[EMAIL PROTECTED]>
To: "Peter Grigor" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 11:52 AM
Subject: Re: socket error


>
> yes, i ran everything again
>
Well, the host.frm file is a mysql database table def...your permissions
tables are screwed...

You'll probably have to run install_db again.

Peter
<^_^>
-
Peter Grigor
Hoobly Classified Ads
http://www.hoobly.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: socket error

[J. A. Tovey]

yes, i ran everything again

--- Peter Grigor <[EMAIL PROTECTED]> wrote:
> - Original Message - 
> From: "J. A. Tovey" <[EMAIL PROTECTED]>
> To: "Peter Grigor" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Tuesday, February 11, 2003 11:33 AM
> Subject: Re: socket error
> 
> 
> > ok :)
> > 
> > all killed now, 
> > 
> > (not quite there yet though :))
> > 
> > I am now getting the error:
> > 
> > 030211 16:26:51  mysqld started
> > 030211 16:26:51  /usr/local/mysql/bin/mysqld:
> > Can't find file: './mysql/host.frm' (errno:
> 13)
> > 030211 16:26:51  mysqld ended
> > 
> > ?
> 
> Did you run the mysql_install_db script??
> 
> Peter
> <^_^>
> -
> Peter Grigor
> Hoobly Classified Ads
> http://www.hoobly.com
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>  
> 


__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

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

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

Re: optimizing left join query

[Diana Soares]

Your user_info table is not indexed... 
(user_info.uid should have an index)
Check the manual:
http://www.mysql.com/doc/en/MySQL_indexes.html


On Tue, 2003-02-11 at 16:16, [EMAIL PROTECTED] wrote:
> Hello All,
> 
> I have the following 2 tables and "LEFT join" query as follows:
> 
> 
> mysql> describe user_lic;
> +++--+-+-+---+
> | Field  | Type   | Null | Key | Default | Extra |
> +++--+-+-+---+
> | license_id | bigint(20) |  | PRI | 0   |   |
> | user_id| bigint(20) |  | PRI | 0   |   |
> | location   | char(30)   | YES  | | NULL|   |
> +++--+-+-+---+
> 
> 
> mysql> describe user_info;
> +--+--+--+-+-+---+
> | Field| Type | Null | Key | Default | Extra |
> +--+--+--+-+-+---+
> | uid  | int(10) unsigned | YES  | | NULL|   |
> | location | char(30) | YES  | | NULL|   |
> | mail | char(100)| YES  | | NULL|   |
> +--+--+--+-+-+---+
> 
> query = SELECT * FROM  user_lic LEFT JOIN user_info ON
> user_lic.user_id=user_info.uid WHERE user_info.uid is NULL  AND
> (user_lic.location = 'Rochester, US')
> 
> 
> When I run this query on a 'user_lic' table with 1000+ rows, it takes about
> 20+ mins to complete the query. However a similar query on a Oracle DB
> takes couple of minutes.
> 
> I am using a high-end Sun Server, connected to a Xiotech SAN, using Fiber
> Optics. I dont think the Hardware is the bottle neck.
> 
> I am wondering how I can optimize the Query/MySQL DB to make this query go
> faster.
> 
> 
> 
> In Peace,
> Saqib Ali
> "I fear, if I rebel against my Lord, the retribution of an Awful Day (The
> Day of Resurrection)" Al-Quran 6:15
> http://docbook.sc-icc.org
> 
-- 
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

Re: Last record

[Keith C. Ivey]

On 11 Feb 2003, at 10:30, Deependra b. Tandukar wrote:

> How do I select the very last record in a column in MySQL database?

That depends on what you mean by "last record".  What are you sorting 
by?  Once you've figured that out, do a query like this:

   SELECT * FROM table_name
   ORDER BY sort_column DESC
   LIMIT 1;

-- 
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: socket error

[Peter Grigor]

- Original Message - 
From: "J. A. Tovey" <[EMAIL PROTECTED]>
To: "Peter Grigor" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 11:33 AM
Subject: Re: socket error


> ok :)
> 
> all killed now, 
> 
> (not quite there yet though :))
> 
> I am now getting the error:
> 
> 030211 16:26:51  mysqld started
> 030211 16:26:51  /usr/local/mysql/bin/mysqld:
> Can't find file: './mysql/host.frm' (errno: 13)
> 030211 16:26:51  mysqld ended
> 
> ?

Did you run the mysql_install_db script??

Peter
<^_^>
-
Peter Grigor
Hoobly Classified Ads
http://www.hoobly.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: socket error

[J. A. Tovey]

ok :)

all killed now, 

(not quite there yet though :))

I am now getting the error:

030211 16:26:51  mysqld started
030211 16:26:51  /usr/local/mysql/bin/mysqld:
Can't find file: './mysql/host.frm' (errno: 13)
030211 16:26:51  mysqld ended

?


--- Peter Grigor <[EMAIL PROTECTED]> wrote:
> > i'm sorry - could you explain that for a
> > simpleton :-)
> > i have no idea what you mean :-/
> >
> > --- Peter Grigor <[EMAIL PROTECTED]> wrote:
> > > - Original Message -
> > > From: "J. A. Tovey" <[EMAIL PROTECTED]>
> > > To: "Peter Grigor" <[EMAIL PROTECTED]>;
> > > <[EMAIL PROTECTED]>
> > > Sent: Tuesday, February 11, 2003 9:34 AM
> > > Subject: Re: socket error
> > >
> > >
> 
> Type
> 
> ps ax | grep mysql
> 
>  at your shell...this will give a list of
> processes that are associated with
> mysql.
> 
> Then type
> 
> kill 1234 1235
> 
> where 1234 and 1235 are the process numbers of
> the processes returned by ps.
> 
> Peter
> <^_^>
> -
> Peter Grigor
> Hoobly Classified Ads
> http://www.hoobly.com
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 


__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

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

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

optimizing left join query

[Saqib . N . Ali]

Hello All,

I have the following 2 tables and "LEFT join" query as follows:


mysql> describe user_lic;
+++--+-+-+---+
| Field  | Type   | Null | Key | Default | Extra |
+++--+-+-+---+
| license_id | bigint(20) |  | PRI | 0   |   |
| user_id| bigint(20) |  | PRI | 0   |   |
| location   | char(30)   | YES  | | NULL|   |
+++--+-+-+---+


mysql> describe user_info;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| uid  | int(10) unsigned | YES  | | NULL|   |
| location | char(30) | YES  | | NULL|   |
| mail | char(100)| YES  | | NULL|   |
+--+--+--+-+-+---+

query = SELECT * FROM  user_lic LEFT JOIN user_info ON
user_lic.user_id=user_info.uid WHERE user_info.uid is NULL  AND
(user_lic.location = 'Rochester, US')


When I run this query on a 'user_lic' table with 1000+ rows, it takes about
20+ mins to complete the query. However a similar query on a Oracle DB
takes couple of minutes.

I am using a high-end Sun Server, connected to a Xiotech SAN, using Fiber
Optics. I dont think the Hardware is the bottle neck.

I am wondering how I can optimize the Query/MySQL DB to make this query go
faster.



In Peace,
Saqib Ali
"I fear, if I rebel against my Lord, the retribution of an Awful Day (The
Day of Resurrection)" Al-Quran 6:15
http://docbook.sc-icc.org


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

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

problem with the load data

[Edgard Dapás]

I'm trying to load a text file, I've extract from access, and Ihave the
following error


mysql> LOAD DATA INFILE '/home/dapasedg/provedores.txt' INTO TABLE proveedores
-> FIELDS TERMINATED BY '|' ENCLOSED BY '"';
ERROR 13: Can't get stat of '/home/dapasedg/provedores.txt' (Errcode: 13)

Anybody can help me?

Thanks

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: pic20615.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: socket error

[Peter Grigor]

> i'm sorry - could you explain that for a
> simpleton :-)
> i have no idea what you mean :-/
>
> --- Peter Grigor <[EMAIL PROTECTED]> wrote:
> > - Original Message -
> > From: "J. A. Tovey" <[EMAIL PROTECTED]>
> > To: "Peter Grigor" <[EMAIL PROTECTED]>;
> > <[EMAIL PROTECTED]>
> > Sent: Tuesday, February 11, 2003 9:34 AM
> > Subject: Re: socket error
> >
> >

Type

ps ax | grep mysql

 at your shell...this will give a list of processes that are associated with
mysql.

Then type

kill 1234 1235

where 1234 and 1235 are the process numbers of the processes returned by ps.

Peter
<^_^>
-
Peter Grigor
Hoobly Classified Ads
http://www.hoobly.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: socket error

[J. A. Tovey]

i'm sorry - could you explain that for a
simpleton :-) 
i have no idea what you mean :-/

--- Peter Grigor <[EMAIL PROTECTED]> wrote:
> - Original Message -
> From: "J. A. Tovey" <[EMAIL PROTECTED]>
> To: "Peter Grigor" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Tuesday, February 11, 2003 9:34 AM
> Subject: Re: socket error
> 
> 
> > ah ok, yes,
> >
> > the error log says
> > "
> > 030211 12:10:27  mysqld started
> > 030211 12:10:27  Can't start server: Bind on
> > TCP/IP port: Address already in use
> > 030211 12:10:27  Do you already have another
> > mysqld server running on port: 3306 ?
> > 030211 12:10:27  Aborting
> > "
> >
> > how do i get rid of this other mysql !
> > its probably because i have installed and
> > uninstalled so many times i probably have
> about
> > 10 different ones installed
> >
> > - how can i get it back to one installation
> 
> look in your processlist and kill the mysqld
> pids--there will be an entry
> for mysqld_safe, and for mysqld
> 
> Peter
> <^_^>
> -
> Peter Grigor
> Hoobly Classified Ads
> http://www.hoobly.com
> 


__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

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

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

RE: Very slow request with many ORs in where parts

[Andy Eastham]

Artem,

Have you considered using a full text index? I don't really understand
exactly what you are trying to do, but consider it if you haven't already.

Andy

> -Original Message-
> From: Artem Koutchine [mailto:[EMAIL PROTECTED]]
> Sent: 11 February 2003 14:23
> To: [EMAIL PROTECTED]
> Subject: Very slow request with many ORs in where parts
>
>
> Hi!
>
> I have the following table:
>
> create table law_words (
> l_id int unsigned not null,
> w_id int unsigned not null,
> primary key (w_id, l_id)
> );
>
>
> The request is:
>
> SELECT DISTINCT w0.l_id FROM law_words as w0
> inner join law_words as w1 on w0.l_id=w1.l_id
> where  w0.w_id in (258,282,...  about 30 ids) and
> w1.w_id in (405, 2017, and so on about 50 ids);
>
> The basic idea is thart law_words hold index of
> words (w_id) for each law (l_id), so law can be found by words, which
> are specified by user and the their ids are looked up in
> vocabular.
>
> Now law_words has 288000 records and that request takes
> about 1 second on a pc with 1GB of RAM and dual Pentium III XEON
> 550Mhz,
> which is TOO MUCH!
> Explain show thart mysql is
> using ' range' and primary index, and about 400 records for each
> table.
>
> For three specified words request takes about forever, so no search is
> possible for three words. The request is using INNER JOIN to get
> the words in the 'AND' manner (laws which contain ALL specified
> words).
>
> I don't understand what i am doing wrong, since i thought it is
> a basic technology behind any search engine.
>
> Please, help, if you can.
>
> Regards, Artem
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> 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: create temp table

[gerald_clark]

create newtable select from oldtable where 1=0;

Admin wrote:


In MySQL is it possible to create a temporary table without having to
specify all the properties?  On SQL Server (at least via query analyzer) you
can create temp tables fairly easily and the properties of each field are
inherited from the regular table.

Doesn't look like thats the case with MySQL, but it doesn't hurt to ask
first ;)

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

Re: SERIOUS PROBLEM

[Peter Grigor]

- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 9:52 AM
Subject: SERIOUS PROBLEM


> Hi,
> I am encountering a serious problem with MYSQL 3.23.48. Mine is a java
> application with the following flow.
>
>Make a connectionobject to Mysql
>
>Get a record from table1
>
>Update table2 with the table1 info.
>
> It works in 99% of the cases i am able to fetch the record from table1.
But
> in 1% cases it is not returning me a null resultset eventhough a record
> exists in the database.
> I have double checked my code and it runs perfectly runs on any other
> database.
>
> Please let me know is it a bug in Mysql or the driver i am using. ???
>
> Thanx and Regards,
> RK
>
We really can't help you much unless you post your query.

Peter
<^_^>

-
Peter Grigor
Hoobly Classified Ads
http://www.hoobly.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

create temp table

[Admin]

In MySQL is it possible to create a temporary table without having to
specify all the properties?  On SQL Server (at least via query analyzer) you
can create temp tables fairly easily and the properties of each field are
inherited from the regular table.

Doesn't look like thats the case with MySQL, but it doesn't hurt to ask
first ;)

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

SERIOUS PROBLEM

[rajup]

Hi,
I am encountering a serious problem with MYSQL 3.23.48. Mine is a java
application with the following flow.

   Make a connectionobject to Mysql

   Get a record from table1

   Update table2 with the table1 info.

It works in 99% of the cases i am able to fetch the record from table1. But
in 1% cases it is not returning me a null resultset eventhough a record
exists in the database.
I have double checked my code and it runs perfectly runs on any other
database.

Please let me know is it a bug in Mysql or the driver i am using. ???

Thanx and Regards,
RK


 * * * The information contained in this message is legally privileged and
confidential  information intended only for the use of the addressed
individual or entity indicated in  this message (or responsible for
delivery of the message to such person). It must not be  read, copied,
disclosed, distributed or used by any person other than the addressee.
Unauthorised use, disclosure or copying is strictly prohibited and may be
unlawful.  Opinions, conclusions and other information on this message that
do not relate to the  official business of any of the constituent companies
of the TATA CONSULTANCY SERVICES  shall be understood as neither given nor
endorsed by the Group. If you have received this  message in error, you
should destroy this message and kindly notify the sender by e-mail. Thank
you. * * *


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

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

Re: socket error

[Peter Grigor]

- Original Message -
From: "J. A. Tovey" <[EMAIL PROTECTED]>
To: "Peter Grigor" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 9:34 AM
Subject: Re: socket error


> ah ok, yes,
>
> the error log says
> "
> 030211 12:10:27  mysqld started
> 030211 12:10:27  Can't start server: Bind on
> TCP/IP port: Address already in use
> 030211 12:10:27  Do you already have another
> mysqld server running on port: 3306 ?
> 030211 12:10:27  Aborting
> "
>
> how do i get rid of this other mysql !
> its probably because i have installed and
> uninstalled so many times i probably have about
> 10 different ones installed
>
> - how can i get it back to one installation

look in your processlist and kill the mysqld pids--there will be an entry
for mysqld_safe, and for mysqld

Peter
<^_^>
-
Peter Grigor
Hoobly Classified Ads
http://www.hoobly.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: socket error

[J. A. Tovey]

ah ok, yes, 

the error log says
"
030211 12:10:27  mysqld started
030211 12:10:27  Can't start server: Bind on
TCP/IP port: Address already in use
030211 12:10:27  Do you already have another
mysqld server running on port: 3306 ?
030211 12:10:27  Aborting
"

how do i get rid of this other mysql !
its probably because i have installed and
uninstalled so many times i probably have about
10 different ones installed 

- how can i get it back to one installation

maybe i need to reformat again ...


--- Peter Grigor <[EMAIL PROTECTED]> wrote:
> - Original Message -
> From: "J. A. Tovey" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, February 11, 2003 9:09 AM
> Subject: socket error
> 
> 
> > hi -
> >
> > i have got to the beginning stages of
> > installation - on trying to create a root
> > password with:
> >
> > bin/mysqladmin -u root password 'password'
> >
> > i get
> >
> > bin/mysqladmin: connect to server at
> 'localhost'
> > failed
> > error: 'Can't connect to local MySQL server
> > through socket '/tmp/mysql.sock' (2)'
> > Check that mysqld is running and that the
> socket:
> > '/tmp/mysql.sock' exists!
> > [1]+  Donebin/safe_mysqld
> > --user=mysql
> >
> >
> > how can i fix this ?
> >
> > i had previously started the server with:
> >
> > [root@jt mysql]# bin/safe_mysqld --user=mysql
> &
> >
> > and it had returned:
> >
> > [1] 3698
> > [root@jt mysql]# Starting mysqld daemon with
> > databases from /usr/local/mysql/data
> > 030211 12:32:05  mysqld ended
> 
> 
> From this last line you can tell that the
> mysqld has not started properly
> ('mysql ended'). That's why you can't connect
> to it via mysqladmin.
> 
> Check the mysqld log
> (/usr/local/mysql/data/your.machine.name.err)
> for
> errors.
> 
> Peter
> <^_^>
> -
> Peter Grigor
> Hoobly Classified Ads
> http://www.hoobly.com
> 


__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

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

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

RE: Very slow request with many ORs in where parts

[Victor Pendleton]

Can you post your explain plan?

-Original Message-
From: Artem Koutchine [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 11, 2003 8:23 AM
To: [EMAIL PROTECTED]
Subject: Very slow request with many ORs in where parts


Hi!

I have the following table:

create table law_words (
l_id int unsigned not null,
w_id int unsigned not null,
primary key (w_id, l_id)
);


The request is:

SELECT DISTINCT w0.l_id FROM law_words as w0
inner join law_words as w1 on w0.l_id=w1.l_id
where  w0.w_id in (258,282,...  about 30 ids) and
w1.w_id in (405, 2017, and so on about 50 ids);

The basic idea is thart law_words hold index of
words (w_id) for each law (l_id), so law can be found by words, which
are specified by user and the their ids are looked up in
vocabular.

Now law_words has 288000 records and that request takes
about 1 second on a pc with 1GB of RAM and dual Pentium III XEON
550Mhz,
which is TOO MUCH!
Explain show thart mysql is
using ' range' and primary index, and about 400 records for each
table.

For three specified words request takes about forever, so no search is
possible for three words. The request is using INNER JOIN to get
the words in the 'AND' manner (laws which contain ALL specified
words).

I don't understand what i am doing wrong, since i thought it is
a basic technology behind any search engine.

Please, help, if you can.

Regards, Artem


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

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

Very slow request with many ORs in where parts

[Artem Koutchine]

Hi!

I have the following table:

create table law_words (
l_id int unsigned not null,
w_id int unsigned not null,
primary key (w_id, l_id)
);


The request is:

SELECT DISTINCT w0.l_id FROM law_words as w0
inner join law_words as w1 on w0.l_id=w1.l_id
where  w0.w_id in (258,282,...  about 30 ids) and
w1.w_id in (405, 2017, and so on about 50 ids);

The basic idea is thart law_words hold index of
words (w_id) for each law (l_id), so law can be found by words, which
are specified by user and the their ids are looked up in
vocabular.

Now law_words has 288000 records and that request takes
about 1 second on a pc with 1GB of RAM and dual Pentium III XEON
550Mhz,
which is TOO MUCH!
Explain show thart mysql is
using ' range' and primary index, and about 400 records for each
table.

For three specified words request takes about forever, so no search is
possible for three words. The request is using INNER JOIN to get
the words in the 'AND' manner (laws which contain ALL specified
words).

I don't understand what i am doing wrong, since i thought it is
a basic technology behind any search engine.

Please, help, if you can.

Regards, Artem


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

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 overhead

[Ciprian I. Acatrinei]

Hi,

I am using PhpMyAdmin and today I got a red message saying that one
table has overhead and I need to optimize the table. 

This is the first time I got this message, I looked online and I
understood why it shows. But my question is: can I do something to avoid
it? What effects daoe the overhead have on a table?


Thank you,
-- 
Ciprian <[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: socket error

[Peter Grigor]

- Original Message -
From: "J. A. Tovey" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 9:09 AM
Subject: socket error


> hi -
>
> i have got to the beginning stages of
> installation - on trying to create a root
> password with:
>
> bin/mysqladmin -u root password 'password'
>
> i get
>
> bin/mysqladmin: connect to server at 'localhost'
> failed
> error: 'Can't connect to local MySQL server
> through socket '/tmp/mysql.sock' (2)'
> Check that mysqld is running and that the socket:
> '/tmp/mysql.sock' exists!
> [1]+  Donebin/safe_mysqld
> --user=mysql
>
>
> how can i fix this ?
>
> i had previously started the server with:
>
> [root@jt mysql]# bin/safe_mysqld --user=mysql &
>
> and it had returned:
>
> [1] 3698
> [root@jt mysql]# Starting mysqld daemon with
> databases from /usr/local/mysql/data
> 030211 12:32:05  mysqld ended


>From this last line you can tell that the mysqld has not started properly
('mysql ended'). That's why you can't connect to it via mysqladmin.

Check the mysqld log (/usr/local/mysql/data/your.machine.name.err) for
errors.

Peter
<^_^>
-
Peter Grigor
Hoobly Classified Ads
http://www.hoobly.com


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

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

Strange Updates

[Daniele Lo Re]

Hi everybody

I got weird behaviour from Mysql while doing some updates.

Before doing the update I did a count, like this:



select count(*) from Yellow WHERE MATCH(street_add) AGAINST('+GUIDO
+MIGLIOLI' IN BOOLEAN MODE) AND street_short = '';

and the result was 50 rows in set.

But when I ran the update:
UPDATE yellowtest.Yellow SET street_short = 'MIGLIOLI' WHERE
MATCH(street_add) AGAINST('+GUIDO +MIGLIOLI' IN BOOLEAN MODE) AND
street_short = '';

the result was:

Query OK, 0 rows affected (1 min 53.89 sec)
Rows matched: 0  Changed: 0  Warnings: 0

I don't understand what's going on, since the WHERE clause is identical
in both queries.

What do you think?

Thanks

Daniele





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

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

socket error

[J. A. Tovey]

hi - 

i have got to the beginning stages of
installation - on trying to create a root
password with:

bin/mysqladmin -u root password 'password'

i get 

bin/mysqladmin: connect to server at 'localhost'
failed
error: 'Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket:
'/tmp/mysql.sock' exists!
[1]+  Donebin/safe_mysqld
--user=mysql


how can i fix this ?

i had previously started the server with:

[root@jt mysql]# bin/safe_mysqld --user=mysql &

and it had returned:

[1] 3698
[root@jt mysql]# Starting mysqld daemon with
databases from /usr/local/mysql/data
030211 12:32:05  mysqld ended



__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

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

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

Top MYSQL Sites

[Insanely Great]

Hello

Can somebody tell me what are the top sites ( except ofcourse mysql.com )
for general MySQL News, Tutorials etc.

Insane


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

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

[Peter Grigor]

Heya:

> HI
> Does mysql support function EXIST

No. It doesn't support subselects before 4.1 either.

> mysql> select * from arcdata d
> -> WHERE year='2003' AND semester='1'
> -> AND EXIST (SELECT * FROM arcdata_unitCodes u
> -> WHERE u.idnum = d.idnum AND u.semester=d.semester AND u.year=d.year
> -> AND unitcode LIKE '433%')
> -> limit 1
> -> ;
> ERROR 1064: You have an error in your SQL syntax near '(SELECT * FROM
> arcdata_unitCodes u
> WHERE u.idnum = d.idnum A' at line 3
>
> Try to select the student who are atleast doing one computer sciencs
> subject (433123, 433252...)


You'll have to rewrite your query using an inner join...not hard.

Peter
<^_^>
-
Peter Grigor
Hoobly Classified Ads
http://www.hoobly.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 IMAP Server

[Curtis Maurand]

Courier IMAP can.  www.courier-mta.com

Curtis

On Wed, 5 Feb 2003, Paul DuBois wrote:

> At 17:26 + 2/4/03, Ben Clewett wrote:
> >MySQL,
> >
> >My first post, please go easy if this is the wrong pleace!
> >
> >I'm trying to find an IMAP server which used MySQL so that my 
> >Mozilla Email client can file it's email a little faster.
> >
> >Has anybody heard of such a device?
> 
> I believe the Horde IMP server uses MySQL:
> 
> http://www.horde.org
> 
> >
> >Regards,
> >
> >Ben
> >
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> 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: encrypted password

[Curtis Maurand]

The manual suggests that the password function is really for intenal mysql 
functions.  Ideally you should use the encode or md5_encode functions.

update user set password=encode('password', 'salt') where user = 
'your_user';

Curtis


On Wed, 5 Feb 2003, Natale Babbo wrote:

> try to use the password() function.
> 
> update user set password=password('your_psw') where
> user='your_user'
> 
> bye
> 
> 
> 
> 
>  --- Jianping Zhu <[EMAIL PROTECTED]> ha scritto: > 
> > I have mysql in a redhat machine. I need to use
> > mysql do user
> > authentication to a website.
> > I have a table like following.
> > 
> > +--+--+
> > | username | passwd   |
> > +--+--+
> > | jianping | jian1830 |
> > | chichi   | jian1830 |
> > +--+--+
> > 
> > I want the passwd field not to be plain text but
> > encrypted. how can i do
> > that?
> > 
> > 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
> >  
> 
> __
> Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino
> http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> 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[2]: Robot Replies (Off topic)

[sherzodr]

Unfortunately,  good intentions don't always produce alike results.

How about if the bot replies to messages matching "newbie", "novice" and "beginner"
in the subject? They most likely tend to be from beginners, and online manual
and/or list archives are pretty much perfect solutions for their issue. I'll
also make sure the replies are NOT posted to the list, insteat sent to the
original sender.

If anyone doesn't like the idea, i guess i'll make it opt-in list then.

sherzod


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

SET field manipulation

[Jeff Snoxell]

Hi,

how can I update a set field such that all table rows get what they had in 
the set ORed with some other set value.

I.E. If I have a field called SetField and rows:

SetField
A
A,B
A,B,C
B
etc


How can I do what I'm trying to do with this mysql query?

UPDATE MyTable SET SetField = (SetField OR 'D');

Thanks,


Jeff


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

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

Query Help

[Jeff Snoxell]

Hi,

How can I write a MySQL query to grab single lines of the form:

ColumnNames: EnquiryID, Name, Address, Detail1, Detail2, Detail3, Detail4

From two joined tables:

Tbl1: Enquiries
--
ID
Name
Address
Etc


Tb12: Details

ID
EnquiryID
Detail
Etc


There is an imposed maximum number of details per enquiry (say 4 for this 
example).

Many thanks,


Jeff


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

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: Merge table limit

[Jerry]

Cheers.

The only limit I've hit so far on 365 tables, was the max number of open
files, so I've ramped that up and its working fine at the moment.

Jerry
-
Jeremy Hutchings
Apps R&D
Cable and Wireless
-


- Original Message -
From: "Diana Soares" <[EMAIL PROTECTED]>
To: "Jerry" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 12:28 PM
Subject: Re: Merge table limit


> Hi,
>
> On Sat, 2003-02-08 at 09:33, Jerry wrote:
> > Hello all.
> >
> > Does any one know, or can anyone point me in the direction of the limits
of
> > a number of tables I can put in a union of a merge table, I can figure
out
> > what the sql would be.
>
> Don't know if there is a limit..
> But you must take into account the number of file descriptors a merge
> table uses... Instead of only two per user, it would be
> number_of_tables+1 file descriptors per user.
>
> So, maybe the limit is defined by the number of file descriptors your OS
> can have. (but i'm only assuming, i'm not shure).
>
> > I have daily tables for an application that average about 2500 rows
(2.5
> > million) , I want to make a merge table for a year that will encompass
them
> > all (i.e. 9.1 billion rows).
> >
> > Has anyone had 365 tables in a merge table ? or more ?
>
> Only less than 10.. :-(
>
> > Thanks
> > Jerry
> >
> > -
> > Jeremy Hutchings
> > Apps R&D
> > Cable and Wireless
> > -
> >
> --
> 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
>


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

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: tricky regrex and case case query

[Diana Soares]

Hi, you could try:

SELECT name,
   SUBSTRING_INDEX(name,' ', 1) as first, 
   SUBSTRING_INDEX(v2, ' ', -1) as last 
FROM table

For the uppercase of the first letter, as i don't know a specific
functions to do that in MySQL (it has ucase and lcase but for all the
word), you must "play" with other functions in MySQL. See:
http://www.mysql.com/doc/en/String_functions.html

On Sat, 2003-02-08 at 23:33, Mike Blezien wrote:
> Hi all,
> 
> I think this can be done with a SQL query, but not have much luck getting it 
> right. Was hoping to get some suggestions on the best way to do this.
> 
> I need to extract the 'name' column in a mysql database table. split the name so 
> we have a "first" and "last" name value, as the name was original entered into 
> the database as their full name instead of the first and last name. My question 
> is, using SQL, can I split the 'name' column to get the two value, then do an 
> UPPER case on the first character for the first name created from the split of 
> the 'name' columnm value.
> 
> I can do this using the Perl programming I am writing, but was pretty sure I do 
> this within the SQL query, but haven't been able to get it to work properly.
> 
> thanks for any help,
> 
> 
> -- 
> MikeBlezien
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Thunder Rain Internet Publishing
> Providing Internet Solutions that work!
> http://www.thunder-rain.com
> Tel:  1(985)902-8484
> MSN: [EMAIL PROTECTED]
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
-- 
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

Re: question on "select group by a 'set' field"

[Anya]

Thanks, Fred. 
It works perfectly.

At 01:00 PM 2/11/2003 +0100, Fred van Engen wrote:
>Hello Anya,
>
>On Tue, Feb 11, 2003 at 07:51:00PM +0800, Anya wrote:
>> 
>> 
>> Dear all,
>> I have a table Kids which has the following fields:
>> 
>> kid char(20),
>> favorite_fruits set('apple','pear','orange',) 
>> 
>> favorite_fruits has choices of up to 64 items.
>> 
>> Now I want to query the count of each fruit appears in the favorite_fruits of the 
>kids in the table. Is there anyway to use 'group by' to get the count, such as: 
>select count(*) from Kids group by favorite_fruits? 
>> 
>> But if use above statement, the results will group by 'apple', 'apple, pear', 
>'apple, pear, orange' instead of by 'apple', 'pear', 'orange'
>> 
>> Or I have to do one by one such as:
>> select count(*) from Kids where favourite_fruits like '%apple%'
>> and loop against the items in the set?
>> 
>
>I never worked with sets, but you could try something like this:
>
>select
>  sum(sign(find_in_set('apple', favourite_fruits))) as apples,
>  sum(sign(find_in_set('pear', favourite_fruits))) as pears
>from Kids;
>
>This would give you all results on a single result row.
>
>
>Regards,
>
>Fred.
>
>-- 
>Fred van Engen  XB Networks B.V.
>email: [EMAIL PROTECTED]Televisieweg 2
>tel: +31 36 5462400 1322 AC  Almere
>fax: +31 36 5462424 The Netherlands
>

Anya
http://www.acmescripts.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

AW: stuck , mysql on SUSE

[Franz, Fa. PostDirekt MA]

Hi ,

if you used SUSE , why didn't you just install the
mysql-packages distibuted there with yast.
It's very easy and I never had a problem with it.
It is also possible to install foreign packages with yast later ,
if you want to udate your mysql-server later.
The problem is , that SUSE is not very clean in it's directory-structure , 
so that makefiles often don't work without beeing edited.

prosit
Klaus

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

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

error if I post a record

[Thomas Berg]

Hello everybody,

I have a problem with MySQL and an application made with Delphi. If I try to
save a modified record in a table, I get the following error: "Record can
not be locked because the record is already in use." I don't know if that is
the exact translation. The original errormessage is German: "Datensatz kann
nicht gesperrt werden, da der Datensatz von einem anderen Benutzer geändert
wurde.".

I searched in the helpfiles and in the web, but I couldn't find a solution.
This errormessage isn't explained furthermore. Of course the computer was
rebooted (several times); the table and the database were dropped and
created once more; so I think that isn't a usual problem.

My configuration: Windows 2k and Borland Delphi 5 Enterprise are used as
clientsoftware; Win 2k and MySQL 3.23.53 as serversoftware. The error is
reproducable; the errormessage appears always when I try to Post the changes
using Ttable.Post. The message appears also if I use queries; it appears if
I use MySQL 3.23.53 and the new 4.xx-Version; the message appears with
another MySQL-environment on another server; the message appears no matter
if I use MyISAM, ISAM or InnoDB-Tables; the message appears with MyODBC 2.5x
and the new 3.51-Version. I don't have problems with this record/table If I
use other db-systems like InterBase oder MS SQL-Server.

There is no problem if I use an ADO-Connection but my application uses
hundrets of tables using the BDE. So I can't change the datasets from Ttable
to ADO easily.

I am looking forward to your ideas or answers.



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