DBI module not using new mysql.sock

[rbholder]

Greetings, I am using MySQL 5.1 and my ibdata file got too big, so I moved it from /var/lib/mysql to /data/mysql. I shut down mysql, then changed /etc/my.cnf and /etc/init.d/mysqld to reflect the dir change, then restarted mysql. I was able to connect to the database through the mysql client, but my perl cgi web pages return this error -

DBI connect('SPIT:localhost:3306','spit',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Thoughts ???

[atcroft]

In the documentation for DBD::mysql for the connect() method, there is the following:

mysql_socket

As of MySQL 3.21.15, it is possible to choose the Unix socket that is used for connecting to the server. This is done, for example, with

    mysql_socket=/dev/mysql

Usually there's no need for this option, unless you are using another location for the socket than that built into the client.

(Untested, but) I believe setting this to path of your new socket may resolve your issue. (The documentation for mysql_read_default_file, located just above it, explaining why this may be the case.)

Hope that helps.

[jmacloue]

I suppose you moved not only the ibdata structures but the entire MySQL folder under data, didn't you? In this case a safe choice is to add a symlink from /data/mysql to /var/lib/mysql, symlinks are (more or less) transparent so most of the time this works.

Unlike mysql and other client programs DBD::mysql doesn't read /etc/my.cnf and other MySQL configuration files by default (however it has a mysql_read_default_file connection option to circumvent it), so the default values hardcoded into libmysqlclient are used. So it is either to modify your code to point it to the new socket location — or keep the defaults working with a symlink.

[rbholder]

Thanks for your reply -
I like the mysql_read_default file option best so I can safely remove the files in /var/lib/mysql, but it is still not working. I get -
DBI connect('SPT:localhost:3306;mysql_read_default_file=/etc/my.cnf','spt',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

My dbi connection code looks like this -

$dsn="DBI:mysql:SPT:localhost:3306;mysql_read_default_file=/etc/my.cnf
+";

$dbh = DBI->connect($dsn, $db_user, $db_pass) or die ;

Any ideas ?

[afoken]

In your first post:

DBI connect('SPIT:localhost:3306','spit',...) failed

In your last post:

$dsn="DBI:mysql:SPT:localhost:3306;mysql_read_default_file=/etc/my.cnf";

The second one lacks an "I", or the first one has an "I" too much.

The DSN looks at least strange. I don't use MySQL, but I think that ":localhost:3306" should not be there.

Let be assume you want to connect to a MySQL database named "SPT". According to the documentation, the basic DSN should be "DBI:mysql:SPT" or "DBI:mysql:database=SPT". Now, you can specify how to connect to the database. For TCP/IP, you would add a host and optional a port parameter: "DBI:mysql:database=SPT;host=127.0.0.1;port=3306". Note that the hostname "localhost" is special for MySQL and is not equivalent to 127.0.0.1. Instead, it switches from TCP/IP sockets to unix domain sockets, making the port number meaningless. So, to connect using a unix socket, you would use "DBI:mysql:database=SPT;host=localhost". That would use the default location for the socket file. To override the location, you need to add either a mysql_socket parameter with the location, or specify the configuration file using the mysql_read_default_file parameter. So your DSN will be either "DBI:mysql:database=SPT;host=localhost;mysql_socket=/data/mysql/mysql.sock" or "DBI:mysql:database=SPT;host=localhost;mysql_read_default_file=/etc/my.cnf".

By the way: Did you try creating a symlink /var/lib/mysql pointing to /data/mysql, as proposed by jmacloue?

Alexander