MySQL+OpenVPN

2006-08-05 Thread Asif Lodhi 

Hi,

Can anyone on this list tell me _how_ i can connect to my MySQL-5.0.22
server running on Windows-XP-SP2 using OpenVPN on Windows?  It looks
like a good VPN/SSL implementation.

--
Thanks in advance,

Asif

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Limiting a Join

2006-08-05 Thread Michael Caplan 

Hi there,

I'm following up on a thread I started yesterday with a new thread, 
cause I'm now looking at  a different problem: limiting the result of a 
join.  For example:


SELECT
  *
FROM
  a
JOIN
  b
ON
  a.id = b.id

If I wanted all records from a and only the first record from b, how 
would I integrate a LIMIT statement in this, or some other statement 
that would achieve the same end?  Appending LIMIT to the end of the 
query will limit the entire result set, which is not the desired effect.


Any ideas?

Thanks,

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

stuck on localhost authentication

2006-08-05 Thread Jeff Drew 

I'm apologize for posting an apparently simple question, but I'm stuck. I'm
trying to get my java application to connect to mysql on the same server.
The server is redhat with mysql 5.1.11. I'd done this with mysql 4.x in the
distant past.

I add a user with the command:

   insert into user (user,host) values ('jeff','localhost');
   flush privileges;
   grant all on databaseName.* to jeff;

select user,host from user; returns
 jeff  localhost

In Java, I use:
DriverManager.getConnection( /localhost/databaseName , 'jeff',null );

when the application tries to connect, DriverManager.getConnection() gets a
bad handshake error.

So I set the password in mysql with:
  set password for jeff = password('xyz');

now select user,host, password from user; returns 2 rows
 jeff localhost
 jeff %  *4232432323

I think this is the problem - the following getConnection() method is
directed to the 2nd entry because it has a password, but it's not localhost
so my localhost-based Java app is denied.

In Java, I use:
 DriverManager.getConnection( /localhost/tm , 'jeff','xyz' );

Then I get an authentication failed error.

I've also tried:
create user 'jeff'; but that created a   % host entry,
not localhost

What should the user table look like for a localhost user? Which commands
get me there?

Thanks

Re: Limiting a Join

2006-08-05 Thread Peter Brawley 

Michael,

If I wanted all records from a and only the first record from b,
how would I integrate a LIMIT statement in this, or some other
statement that would achieve the same end?  Appending LIMIT
to the end of the query will limit the entire result set, which is not
the desired effect.

Assuming first means the smallest joined value on orderingcol ...

select a.*,b.*
from a inner join b on a.id=b.id
left join c on b.id=c.id and b.orderingcol  c.orderingcol
where c.orderingcol is null;

PB

-

Michael Caplan wrote:

Hi there,

I'm following up on a thread I started yesterday with a new thread, 
cause I'm now looking at  a different problem: limiting the result of 
a join.  For example:


SELECT
  *
FROM
  a
JOIN
  b
ON
  a.id = b.id

If I wanted all records from a and only the first record from b, 
how would I integrate a LIMIT statement in this, or some other 
statement that would achieve the same end?  Appending LIMIT to the end 
of the query will limit the entire result set, which is not the 
desired effect.


Any ideas?

Thanks,

Michael




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL 5.0.24 has been released

2006-08-05 Thread Joerg Bruehe 

Hi,


MySQL 5.0.24, a new version of the popular Open Source Database
Management System, has been released. The Community Edition is now
available in source and binary form for a number of platforms from our
download pages at
   http://dev.mysql.com/downloads/ and mirror sites.

Note that not all mirror sites may be up to date at this point in time -
if you can't find this version on some mirror, please try again later or
choose another download site.

This is a bugfix release for the current production release family.

This section documents all changes and bug fixes that have been
applied since the last official MySQL release. If you would like
to receive more fine-grained and personalized update alerts about
fixes that are relevant to the version and features you use,
please consider subscribing to MySQL Network (a commercial MySQL
offering). For more details please see
http://www.mysql.com/network/advisors.html.

Please refer to our bug database at http://bugs.mysql.com/ for more
details about the individual bugs fixed in this version.

We welcome and appreciate your feedback!


Changes in release 5.0.24

Bugs fixed:
  * Security fix: If a user has access to MyISAM table t, that
user can create a MERGE table m that accesses t. However, if
the user's privileges on t are subsequently revoked, the user
can continue to access t by doing so through m. If this
behavior is undesirable, you can start the server with the new
--skip-merge option to disable the MERGE storage engine.
(Bug#15195: http://bugs.mysql.com/15195)
  * NDB Cluster: The repeated creating and dropping of a table
would eventually lead to NDB Error 826, Too many tables and
attributes ... Insufficient space.
(Bug#20847: http://bugs.mysql.com/20847)
  * With the auto_increment_increment system variable set larger
than 1, if the next generate AUTO_INCREMENT value would be
larger than the column's maximum value, the value would be
clipped down to that maximum value and inserted, even if the
resulting value would not be in the generated sequence. This
could cause problems for master-master replication. Now the
server clips the value down to the previous value in the
sequence, which correctly produces a duplicate-key error if
that value already exists in the column.
(Bug#20524: http://bugs.mysql.com/20524)
  * If a table on a slave server had a higher AUTO_INCREMENT
counter than the corresponding master table (even though all
rows of the two tables were identical), in some cases REPLACE
or INSERT ... ON DUPLICATE KEY UPDATE would not replicate
properly using statement-based logging. (Different values
would be inserted on the master and slave.)
(Bug#20188: http://bugs.mysql.com/20188)
  * Under heavy load (executing more than 1024 simultaneous
complex queries), a problem in the code that handles internal
temporary tables could lead to writing beyond allocated space
and memory corruption. Use of more than 1024 simultaneous
cursors server wide also could lead to memory corruption.
(This applies both to stored procedure and C API cursors.)
(Bug#21206: http://bugs.mysql.com/21206)
  * A race condition during slave server shutdown caused an assert
failure. (Bug#20850: http://bugs.mysql.com/20850)
  * mysqldump produced a malformed dump file when dumping multiple
databases that contained views.
(Bug#20221: http://bugs.mysql.com/20221)
  * SELECT @@INSERT_ID displayed a value unrelated to a preceding
SET INSERT_ID. (It was returning LAST_INSERT_ID instead.)
(Bug#20392: http://bugs.mysql.com/20392)
  * Updating a column of a FEDERATED table to NULL sometimes
failed. (Bug#16494: http://bugs.mysql.com/16494)
  * Performing INSERT ... SELECT ... JOIN ... USING without
qualifying the column names caused ERROR 1052 column 'x' in
field list is ambiguous even in cases where the column
references were unambiguous.
(Bug#18080: http://bugs.mysql.com/18080)
  * Bug#10952: http://bugs.mysql.com/10952 may cause inadvertent data
loss. A fix for this bug had been contained in MySQL 5.0.23,
but the approach used causes a loss of intended functionality.
Because of this, that fix has been reverted in MySQL 5.0.24.
As a consequence, the risk of inadvertent data loss still exists,
see Bug#10952: http://bugs.mysql.com/10952
  * A SELECT that used a subquery in the FROM clause that did not
select from a table failed when the subquery was used in a
join. (Bug#21002: http://bugs.mysql.com/21002)
  * REPLACE ... SELECT for a view required the INSERT privilege
for tables other than the table being modified.
(Bug#21135: http://bugs.mysql.com/21135)
  * Failure to account for a NULL table pointer on big-endian
machines could cause a server crash during type conversion.
(Bug#21135: http://bugs.mysql.com/21135)
  * mysqldump sometimes did not select the correct database before

Re: Limiting a Join

2006-08-05 Thread Michael Caplan 

Peter,

Thanks for the reply.  I was not able to get your query working as 
illustrated.  I also realize that my example query was flawed, as I made 
no reference to an ordering column (as you point out).  What I was able 
to get working is the following:



SELECT
 *
FROM
 a
JOIN
 b
ON
 a.id = b.a_id
WHERE
b.id =
(
   SELECT
  MAX(C.ID)
   FROM
  B as C
   WHERE
  C.a_id = a.id
)


I'm wondering if you have any thoughts on this approach.

Best,

Michael


Peter Brawley wrote:

Michael,

If I wanted all records from a and only the first record from b,
how would I integrate a LIMIT statement in this, or some other
statement that would achieve the same end?  Appending LIMIT
to the end of the query will limit the entire result set, which is not
the desired effect.

Assuming first means the smallest joined value on orderingcol ...

select a.*,b.*
from a inner join b on a.id=b.id
left join c on b.id=c.id and b.orderingcol  c.orderingcol
where c.orderingcol is null;

PB

-

Michael Caplan wrote:

Hi there,

I'm following up on a thread I started yesterday with a new thread, 
cause I'm now looking at  a different problem: limiting the result of 
a join.  For example:


SELECT
  *
FROM
  a
JOIN
  b
ON
  a.id = b.id

If I wanted all records from a and only the first record from b, 
how would I integrate a LIMIT statement in this, or some other 
statement that would achieve the same end?  Appending LIMIT to the 
end of the query will limit the entire result set, which is not the 
desired effect.


Any ideas?

Thanks,

Michael







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: stuck on localhost authentication

2006-08-05 Thread Brad Jahnke 
   insert into user (user,host) values ('jeff','localhost');
   flush privileges;
   grant all on databaseName.* to jeff;

jeff = [EMAIL PROTECTED]

not [EMAIL PROTECTED]

So in your statement you should have used...

grant all on databaseName.* to [EMAIL PROTECTED];

select user,host from user; returns
 jeff  localhost
 
 In Java, I use:
DriverManager.getConnection( /localhost/databaseName , 'jeff',null );
 
 when the application tries to connect, DriverManager.getConnection() gets a
 bad handshake error.
 
 So I set the password in mysql with:
  set password for jeff = password('xyz');

Similarly... you need to use [EMAIL PROTECTED]

 now select user,host, password from user; returns 2 rows
 jeff localhost
 jeff %  *4232432323
 
 I think this is the problem - the following getConnection() method is
 directed to the 2nd entry because it has a password, but it's not localhost
 so my localhost-based Java app is denied.

 In Java, I use:
 DriverManager.getConnection( /localhost/tm , 'jeff','xyz' );
 
 Then I get an authentication failed error.

Connector/J on 'nix types, only works via TCP/IP.  Your app may be trying to
connect via unix socket.  If the user/password fix does not work specify
127.0.0.1 in your connection setting (or even omit host since 127.0.0.1 is
default host value).  Also, make sure your MySQL server is listening via
TCP/IP.  If it is not, be sure to take the necessary security precautions
before doing so, and/or just enable TCP/IP on 127.0.0.1 (the  user
[EMAIL PROTECTED] is valid for connections on 127.0.0.1).





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Limiting a Join

2006-08-05 Thread Peter Brawley 

Michael

The subquery version of that logic

SELECT * FROM a
JOIN  b ON  a.id = b.a_id
WHERE b.id = (
 SELECT MAX(c.id) 
 FROM b as c

 WHERE c.a_id = a.id
);

likely runs slower. If the sorting column is also the joining column, 
you can still write it as a join:


SELECT a.*, b.*
FROM a INNER JOIN b ON a.id=b.id
LEFT JOIN b AS c ON a.id=c.id and b.id  c.id
WHERE c.id IS NULL;

PB

-

if the sorting column (id) is also the joining column (id).

PB


Michael Caplan wrote:

Peter,

Thanks for the reply.  I was not able to get your query working as 
illustrated.  I also realize that my example query was flawed, as I 
made no reference to an ordering column (as you point out).  What I 
was able to get working is the following:



SELECT
 *
FROM
 a
JOIN
 b
ON
 a.id = b.a_id
WHERE
b.id =
(
   SELECT
  MAX(C.ID)
   FROM
  B as C
   WHERE
  C.a_id = a.id
)


I'm wondering if you have any thoughts on this approach.

Best,

Michael


Peter Brawley wrote:

Michael,

If I wanted all records from a and only the first record from b,
how would I integrate a LIMIT statement in this, or some other
statement that would achieve the same end?  Appending LIMIT
to the end of the query will limit the entire result set, which is not
the desired effect.

Assuming first means the smallest joined value on orderingcol ...

select a.*,b.*
from a inner join b on a.id=b.id
left join c on b.id=c.id and b.orderingcol  c.orderingcol
where c.orderingcol is null;

PB

-

Michael Caplan wrote:

Hi there,

I'm following up on a thread I started yesterday with a new thread, 
cause I'm now looking at  a different problem: limiting the result 
of a join.  For example:


SELECT
  *
FROM
  a
JOIN
  b
ON
  a.id = b.id

If I wanted all records from a and only the first record from b, 
how would I integrate a LIMIT statement in this, or some other 
statement that would achieve the same end?  Appending LIMIT to the 
end of the query will limit the entire result set, which is not the 
desired effect.


Any ideas?

Thanks,

Michael










--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.7/409 - Release Date: 8/4/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SELECT with Result Ordered by Minimum of Fields

2006-08-05 Thread David T. Ashley 

Hi,

I have a table and I'd like to do a SELECT query with the result ordered by
the minimum of two fields.  I might also like to query by the minimum (but
that is an easier problem, as I can just rephrase it as an OR).

Can I just write something like:

SELECT * FROM mytable WHERE fieldofinterestvalue ORDER BY MIN(field1,
field2) ASC;

or is it more complicated or impossible?

Thanks, Dave.