Logging in to MySQL
Roedy Green
but now for the life of me I can't get the simplest code to work.
Here is my SSCCE. I can access the database fine from the command
line with mysql -uroot -psesame
/*
* @(#)SSCCE.java
*
* Summary: Attempt to connect to an MySQL database
*
* Copyright: (c) 2009 Roedy Green, Canadian Mind Products,
http://mindprod.com
*
* Licence: This software may be copied and used freely for any purpose
but military.
* http://mindprod.com/contact/nonmil.html
*
* Requires: JDK 1.6+
*
* Created with: IntelliJ IDEA IDE.
*
* Version History:
* 1.0 2009-22 - initial version.
*/
package com.mindprod.macro;
import static java.lang.System.err;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* Attempt to connect to an MySQL database.
*
* @author Roedy Green, Canadian Mind Products
* @version 1.0 2009-22 - initial version.
* @since 2009
*/
public class SSCCE
{
/**
* Test harness
*
* @param args
*/
public static void main( String[] args )
{
try
{
final Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mindprod?user=root&password=sesame" );
}
catch ( SQLException e )
{
err.println( ">>> " + e.getMessage() + " <<<" );
e.printStackTrace( err );
}
}
}
--
Roedy Green Canadian Mind Products
http://mindprod.com
Nothing is so good as it seems beforehand.
~ George Eliot (born: 1819-11-22 died: 1880-12-22 at age: 61) (Mary Ann Evans)
Roedy Green
<see_w...@mindprod.com.invalid> wrote, quoted or indirectly quoted
someone who said :
>I seems to me I was able to login to a MySQL database some years ago,
>but now for the life of me I can't get the simplest code to work.
>
>Here is my SSCCE. I can access the database fine from the command
>line with mysql -uroot -psesame
I have installed mysql-connector-java-5.1.10-bin.jar in the ext dirs
of both the JDK and JRE.
Sabine Dinis Blochberger
Forgot to load the driver?
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
<http://java.sun.com/docs/books/tutorial/jdbc/basics/connecting.html>
Donkey Hottie
> I seems to me I was able to login to a MySQL database some years ago,
> but now for the life of me I can't get the simplest code to work.
>
> Here is my SSCCE. I can access the database fine from the command
> line with mysql -uroot -psesame
>
>
You forgot to load the driver.
public class DBDemo
{
// The JDBC Connector Class.
private static final String dbClassName = "com.mysql.jdbc.Driver";
// Connection string. emotherearth is the database the program
// is connecting to. You can include user and password after this
// by adding (say) ?user=paulr&password=paulr. Not recommended!
private static final String CONNECTION =
"jdbc:mysql://127.0.0.1/emotherearth";
public static void main(String[] args) throws
ClassNotFoundException,SQLException
{
System.out.println(dbClassName);
// Class.forName(xxx) loads the jdbc classes and
// creates a drivermanager class factory
Class.forName(dbClassName);
// Properties for user and password
Properties p = new Properties();
p.put("user","paulr");
p.put("password","paulr");
// Now try to connect
Connection c = DriverManager.getConnection(CONNECTION,p);
System.out.println("It works !");
c.close();
}
}
--
You're at the end of the road again.
Roedy Green
<see_w...@mindprod.com.invalid> wrote, quoted or indirectly quoted
someone who said :
>I seems to me I was able to login to a MySQL database some years ago,
>but now for the life of me I can't get the simplest code to work.
>
>Here is my SSCCE. I can access the database fine from the command
>line with mysql -uroot -psesame
SOLVED:
I decided to reinstall MySQL. In the process I noticed that "disable
remote TCP/IP looks as if it also disables localhost access. I
turnedb remote on, and lo and behold I connect.
Arne Vajhøj
> Roedy Green wrote:
>> I seems to me I was able to login to a MySQL database some years ago,
>> but now for the life of me I can't get the simplest code to work.
>>
>> Here is my SSCCE. I can access the database fine from the command
>> line with mysql -uroot -psesame
>> {
>> try
>> {
>> final Connection conn = DriverManager.getConnection(
>> "jdbc:mysql://localhost:3306/mindprod?user=root&password=sesame" );
>> }
>> catch ( SQLException e )
>> {
>> err.println( ">>> " + e.getMessage() + " <<<" );
>> e.printStackTrace( err );
>> }
>> }
>> }
>
> Forgot to load the driver?
> Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Just replace with the MySQL driver ....
:-)
Arne
Arne Vajhøj
Besides the code then the error message is also relevant info.
Arne
Arne Vajhøj
> On Fri, 23 Oct 2009 02:47:53 -0700, Roedy Green
> <see_w...@mindprod.com.invalid> wrote, quoted or indirectly quoted
> someone who said :
>
>> I seems to me I was able to login to a MySQL database some years ago,
>> but now for the life of me I can't get the simplest code to work.
>>
>> Here is my SSCCE. I can access the database fine from the command
>> line with mysql -uroot -psesame
>
> SOLVED:
>
> I decided to reinstall MySQL. In the process I noticed that "disable
> remote TCP/IP looks as if it also disables localhost access. I
> turnedb remote on, and lo and behold I connect.
Without loading the driver ????
Arne
Martin Gregorie
...and don't forget that SQLExceptions can be chained so you want to see
the messages out of each exception in the chain. The specs don't say what
error conditions can generate a chain, so its only sensible to assume
that any error, even during connect, can do so.
--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |
Roedy Green
<no....@here.invalid> wrote, quoted or indirectly quoted someone who
said :
>Forgot to load the driver?
>Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
According to the latest MySQL docs you don't need to do that anymore.
I tried it, though, without success.
Roedy Green
wrote, quoted or indirectly quoted someone who said :
>Without loading the driver ????
Oddly yes, but not really. See
http://dev.mysql.com/doc/refman/5.1/en/connector-j-usagenotes-basic.html#connector-j-examples-connection-drivermanager
Which suggests you don't need to load the driver. So I removed that
code, and all worked.
However, this morning it stopped working. That's because the driver
STAYED loaded, but only until a full shutdown. Both IntelliJ and
Tomcat have a persistent JVM running.
So now I have reinstated that code:
Class.forName( "com.mysql.jdbc.Driver" );
and it is behaving itself again.
Roedy Green
<mar...@address-in-sig.invalid> wrote, quoted or indirectly quoted
someone who said :
>...and don't forget that SQLExceptions can be chained so you want to see
>the messages out of each exception in the chain. The specs don't say what
>error conditions can generate a chain, so its only sensible to assume
>that any error, even during connect, can do so.
The error stack goes on for pages and pages.
Interesting phrases included "connection refused" and words to the
effect, "I sent a message to the server but it did not respond".
As I said earlier, the problem was I configured remote TCP/IP access
off which has the effect of turning off localhost access as well.
What was most frustrating is I recalled making this work for some
project before. If there were a trick to it, surely I would have
documented it.
You might ask "Why didn't MySQL give a sensible error message, like
"TCP/IP access to MySQL server blocked in the configuration?".
However, it could not say that since it wasn't even listening to a
TCP/IP port. Further, the driver had no idea why the server was not
responding.
Arne Vajhøj
> On Fri, 23 Oct 2009 10:05:52 -0400, Arne Vajh�j <ar...@vajhoej.dk>
> wrote, quoted or indirectly quoted someone who said :
>
>> Without loading the driver ????
>
> Oddly yes, but not really. See
> http://dev.mysql.com/doc/refman/5.1/en/connector-j-usagenotes-basic.html#connector-j-examples-connection-drivermanager
>
> Which suggests you don't need to load the driver. So I removed that
> code, and all worked.
It does not suggest so.
In fact it suggests Class.forName in the previous section.
Arne
Arne Vajhøj
> On Fri, 23 Oct 2009 10:55:34 +0100, Sabine Dinis Blochberger
> <no....@here.invalid> wrote, quoted or indirectly quoted someone who
> said :
>> Forgot to load the driver?
>> Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
>
> According to the latest MySQL docs you don't need to do that anymore.
> I tried it, though, without success.
You need it per JDBC spec.
Arne
Roedy Green
wrote, quoted or indirectly quoted someone who said :
>It does not suggest so.
>
>In fact it suggests Class.forName in the previous section.
The second example looks at first glance to be complete in itself.
That is misleading.
Also example 1 is labelled Connecting to MySQL Using the DriverManager
Interface
and example 2 is labelled Connector/J: Obtaining a connection from the
DriverManager
Neither piece does what is advertised. You need both.
I think it would be clearest if the code for the two examples were
combined.
Lew
who said :
>>> Forgot to load the driver?
>>> Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
>
Roedy Green wrote:
>> According to the latest MySQL docs you don't need to do that anymore.
>> I tried it, though, without success.
>
Arne Vajhøj wrote:
> You need it per JDBC spec.
>
Not necessarily. You need it to register with a 'DriverManager', but
the docs for the latter tell us,
"NOTE: The DataSource interface, new in the JDBC 2.0 API, provides
another way to connect to a data source. The use of a DataSource
object is the preferred means of connecting to a data source."
Connecting via a 'DataSource' does not involve invocation of
'Class.forName'. The docs for 'DataSource' reveal,
"A driver that is accessed via a DataSource object does not register
itself with the DriverManager."
Usually you set up a 'DataSource' through the application server's
management interface and access it via a JNDI lookup.
--
Lew
Arne Vajhøj
> On Fri, 23 Oct 2009 14:57:37 -0400, Arne Vajh�j <ar...@vajhoej.dk>
> wrote, quoted or indirectly quoted someone who said :
>> It does not suggest so.
>>
>> In fact it suggests Class.forName in the previous section.
>
> The second example looks at first glance to be complete in itself.
> That is misleading.
>
> Also example 1 is labelled Connecting to MySQL Using the DriverManager
> Interface
> and example 2 is labelled Connector/J: Obtaining a connection from the
> DriverManager
>
> Neither piece does what is advertised. You need both.
>
> I think it would be clearest if the code for the two examples were
> combined.
This stuff is part of very basic JDBC.
MySQL docs is probably intended to show people that know JDBC how to
use their JDBC driver not to teach JDBC as such.
Arne
Arne Vajhøj
> Sabine Dinis Blochberger wrote, quoted or indirectly quoted someone
> who said :
>>>> Forgot to load the driver?
>>>> Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
>
> Roedy Green wrote:
>>> According to the latest MySQL docs you don't need to do that anymore.
>>> I tried it, though, without success.
>
>> You need it per JDBC spec.
>
> Not necessarily. You need it to register with a 'DriverManager', but
> the docs for the latter tell us,
> "NOTE: The DataSource interface, new in the JDBC 2.0 API, provides
> another way to connect to a data source. The use of a DataSource
> object is the preferred means of connecting to a data source."
>
> Connecting via a 'DataSource' does not involve invocation of
> 'Class.forName'. The docs for 'DataSource' reveal,
> "A driver that is accessed via a DataSource object does not register
> itself with the DriverManager."
>
> Usually you set up a 'DataSource' through the application server's
> management interface and access it via a JNDI lookup.
And if he use XML files instead of a database, then the JDBC spec
does not mandate anything whatsoever.
With Roedys code he needs Class.forName per JDBC spec.
Arne
Martin Gregorie
> On Fri, 23 Oct 2009 15:40:23 +0000 (UTC), Martin Gregorie
> <mar...@address-in-sig.invalid> wrote, quoted or indirectly quoted
> someone who said :
>
>>...and don't forget that SQLExceptions can be chained so you want to see
>>the messages out of each exception in the chain. The specs don't say
>>what error conditions can generate a chain, so its only sensible to
>>assume that any error, even during connect, can do so.
>
> The error stack goes on for pages and pages.
>
> Interesting phrases included "connection refused" and words to the
> effect, "I sent a message to the server but it did not respond".
>
> As I said earlier, the problem was I configured remote TCP/IP access off
> which has the effect of turning off localhost access as well.
>
That sounds like an OS bug to me. Localhost connections are implemented
entirely in software. You should be able to use them with no networking
hardware installed provided that the TPC stack is loaded, so IMO the OS
has no business disabling localhost just because remote access has been
stopped.
> What was most frustrating is I recalled making this work for some
> project before. If there were a trick to it, surely I would have
> documented it.
>
> You might ask "Why didn't MySQL give a sensible error message, like
> "TCP/IP access to MySQL server blocked in the configuration?". However,
> it could not say that since it wasn't even listening to a TCP/IP port.
> Further, the driver had no idea why the server was not responding.
>
It looks as though your OS in fact returned ECONNREFUSED, which you'd
expect since the local net was stopped. However, the MySQL driver failed
to recognize that since it apparently went on to send a message which
never got a response. I'd say that the MySQL driver was buggy too, since
it should have given up once it saw ECONNREFUSED.
Still, its a pity they you didn't display the SQLException chain, since
that output may have been more concise and directly relevant than the
error stack contents.