JDBC MySQL connection using Unix Socket

Question

I am using MySQL using the --skip-networking option on Linux.

Trying to connect my J2EE based application (using servlets) to the MySQL database using JDBC.

When was using MySQL with the --skip-networking option disabled, I was connecting to the database as:

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase","myuser","mypassword");

After having enabled the --skip-networking option, I am trying to connect it as:

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase","myuser","mypassword");

But this does not seem to work, and I get java.lang.NullPointerException when I try to connect to the database in my application.

After commenting out the --skip-networking option and using the old JDBC statement, I can connect to the database.

Note - I am able to connect to the database via the command line mysql client with the --skip-networking option enabled.

Can anyone tell me how to connect to the database from JDBC? I tried searching for it but could not any satisfactory answer that worked. Thanks in advance.

Can you post full stacktrace bcoz i am on linux and tried to connect to mysql using JDBC and it works fine — sol4me, Sep 18 '14 at 17:25
Copy the stacktrace and edit your question and paste it as code — sol4me, Sep 18 '14 at 17:28
I meant how to find out the stacktrace. I am deploying my web app using Netbeans on Apache Tomcat server. — abchk1234, Sep 18 '14 at 17:28
When you run your program the on console you get some error describing the problem, just paste all that — sol4me, Sep 18 '14 at 17:30
I edited my previous comment. I am deploying my web app using Netbeans on Apache Tomcat server. — abchk1234, Sep 18 '14 at 17:31
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/61510/discussion-between-sol4me-and-aaditya-bagga). — sol4me, Sep 18 '14 at 17:36

score 14 · Answer 1 · edited May 23 '17 at 11:54

If you want to use UNIX sockets with the Mysql JDBC Connector/J you need to provide a socketFactory.

jdbc:mysql:///?user=test&password=test&socketFactory=<classname>&<socket>=/tmp/mysql.sock

So this will vary with the implementation you use. By default, Mysql does not ship with any implementation for that, just provides an example for such a factory in it's source-code.

There is an existing UNIX socket Java library named junixsocket which also has such a socketFactory class implementation. An example is outlined in Connecting to a MySQL database via Unix Domain Sockets which is part of their documentation.

You can find more Java UNIX socket library alternatives in related Q&A material:

UNIX socket implementation for Java?

score 10 · Answer 2 · edited Oct 28 '16 at 14:02

10

The JDBC Driver from the MariaDB project supports Unix domain sockets while remaining compatible with the MySQL Connector/J JDBC driver. Example jdbc url for the MariaDB driver is: jdbc:mariadb://localhost:3306/revmgt?localSocket=/var/run/mysqld/mysqld.sock

Worth noting that this requires including the JNA library as the MariaDB driver uses domain sockets via JNA internally. I saw speed improvements for CPU bound java processes when using the unix domain sockets. I believe this was largely from the offload of work from the java process to native code freeing up CPU cycles for the already CPU bottle necked java process.

edited Oct 28 '16 at 14:02

MatFiz

973
1
8
25

answered Oct 21 '16 at 15:25

Robert

131
1
3

1

In your connection string you mentioned the IP:port as localhost:3306. Does it require to mention the host properties while connecting via Unix Socket?? – iftee May 11 '20 at 00:11
1

Seems including JNA library on classpath (jna-5.6.0.jar in my case) is absolutely critical. I've spent close to a day struggling to make mariadb connector work with consistent 'Socket fail to connect to host:localhost, port:3306. Connection refused (Connection refused)'. In the same time, console-based mysql as well MySQLdb in Python worked fine. Simply including JNA on the classpath just made everything work. You do not have to specify '3306' on the url though. Sure enough, documentation for ConnectorJ does tell you to install JNA if you want to use sockets. – Maksym Dec 05 '20 at 16:36

Mark Rotteveel · Accepted Answer · 2014-09-19T13:40:09.503

8

You simply cannot do this: the MySQL JDBC driver only supports TCP/IP and - on Windows - named pipes to connect to the database. Therefor specifying --skip-networking will not allow you to use JDBC MySQL Connector/J at all.

See also http://lists.mysql.com/java/8749:

Java itself doesn't support unix domain sockets, but since you're on windows, you can use named pipes, [..]

The dead-link in the above post is now http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

edited Sep 19 '14 at 13:40

answered Sep 18 '14 at 19:29

Mark Rotteveel

100,966
191
140
197

Thanks. Do you know if named pipes can be used on Unix? The link in the post you referenced seems dead. Also could you recommend any other connector that does supoort Unix pipes.. I was trying to use the --skip-networking option for securtiy, perhaps using the --bind-address option to localhost and using a firewall to block all incoming connections serve as an alternative as I have read somewhere.. – abchk1234 Sep 19 '14 at 13:15
@AadityaBagga I have added the equivalent link in my answer. Namedpipes are - afaik - specifically for Windows, so you can't use it in Unix. Only binding on localhost or using a firewall is indeed a good option for securing the server. – Mark Rotteveel Sep 19 '14 at 13:41
Thanks, I will check it out. I also found a link https://dev.mysql.com/doc/refman/5.0/en/connecting.html, which lists some options for connecting. Marking as solved. – abchk1234 Sep 19 '14 at 13:59

score 4 · Answer 4 · edited Apr 13 '17 at 12:42

4

As another answer pointed out, it's possible (at least in Intellij IDE) to use socket connection for Mysql JDBC Connector/J (I am using version 5.1) without having to provide a socketFactory,

jdbc:mysql://localhost:3306/database_name?socket=/tmp/mysql.sock

edited Apr 13 '17 at 12:42

Community

1
1

answered May 20 '16 at 22:30

Devy

9,655
8
61
59

This really seemed useful, however I couldn't get it to work, and had to use junixsocket instead – Matteo Tassinari Jul 22 '16 at 12:52
@MatteoTassinari it's possible that Intellij IDE provides some extra libs to support the `socket` param approach. – Devy Jul 22 '16 at 18:18
The same works with Flyway. However, the [documentation still claims that they do not support it](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-unix-socket.html). So there is probably some other library which adds support for this option in the background. – JojOatXGME Nov 27 '21 at 13:49
1

After some testing, I think what might actually happen is that we are using the MariaDB connector, since it is also listening to `jdbc:mysql:` and it is also on the classpath. If I use `&disableMariaDbDriver` as documented [here](https://mariadb.com/kb/en/about-mariadb-connector-j/#having-mariadb-and-mysql-drivers-in-the-same-classpath), I run into problems. – JojOatXGME Nov 27 '21 at 14:18

JDBC MySQL connection using Unix Socket

4 Answers4

Linked

Related