DBI: connecting 'local' database

R

Ralf Müller

Hi,
I've difficulties to connect to a second myqsl-installation, listeing on port
6080.

dbh = DBI.connect('DBI:Mysql:test','ram')

works, but how can i select the on on 6080?

Any Hints?

I remember something in the "ruby developers guide".
 
M

Michael Neumann

Ralf said:
Hi,
I've difficulties to connect to a second myqsl-installation, listeing on port
6080.

dbh = DBI.connect('DBI:Mysql:test','ram')
Click to expand...

try this:

'DBI:Mysql:test;port=6080'

Regards,

Michael
 
R

Ralf Müller

Am Dienstag, 27. Juli 2004 16:12 schrieb Michael Neumann:
try this:

'DBI:Mysql:test;port=6080'
Click to expand...

irb(main):033:0* dbh = DBI.connect('DBI:Mysql:lcwa;port=6080','ram')
=> #<DBI::DatabaseHandle:0x406606a4 @trace_mode=2,
@handle=#<DBI::DBD::Mysql::Database:0x40660410 @mutex=#<Mutex:0x406602bc
@waiting=[], @locked=false>, @attr={"AutoCommit"=>true},
@have_transactions=true, @handle=#<Mysql:0x40660424>>,
@trace_output=#<IO:0x4059108c>>

seems to work, but actually this should'nt because of

ram@lilith:~$mysql -u ram lcwa
ERROR 1045: Access denied for user: 'ram@localhost' (Using password: NO)

----------------------------------------------------------
on the other hand

irb(main):034:0> dbh = DBI.connect('DBI:Mysql:lcwa;port=6080','ram','lcwa')
DBI::DatabaseError: Access denied for user: 'ram@localhost' (Using password:
YES)
from /usr/local/lib/ruby/site_ruby/1.8/DBD/Mysql/Mysql.rb:70:in
`connect'
from /usr/local/lib/ruby/site_ruby/1.8/dbi/dbi.rb:584:in `connect'
from /usr/local/lib/ruby/site_ruby/1.8/dbi/dbi.rb:581:in `connect'
from (irb):34
from /usr/local/lib/ruby/site_ruby/1.8/dbi/dbi.rb:439

whereas

ram@lilith:~$mysql -u ram -plcwa lcwa
mysql>


dbi does exactly the opposite of the mysql-client.
anything to do with 'irb' ??



ralf
 
L

Lennon Day-Reynolds

Ralf,

I know that the MySQL hostname-based authentication always gives me
problems. I think you need to make sure that you have access rules for
'ram@localhost', as well as just 'ram', otherwise connections coming
in over a TCP socket (which DBI uses) will be handled differently than
those coming in over the mysqld domain socket (which the command-line
client uses by default).

Lennon
 
R

Ralf Müller

Am Dienstag, 27. Juli 2004 18:56 schrieb Lennon Day-Reynolds:
Ralf,

I know that the MySQL hostname-based authentication always gives me
problems. I think you need to make sure that you have access rules for
'ram@localhost', as well as just 'ram', otherwise connections coming
in over a TCP socket (which DBI uses) will be handled differently than
those coming in over the mysqld domain socket (which the command-line
client uses by default).
Click to expand...

Moin Lennon,

I'm not sure, how to do that.

Passing

grant all on lcwa.* to ram identified by 'lcwa';

to the mysql-client did not succeed. the DBI-Statement lead to the same
results.
 
R

Ralf Müller

Am Mittwoch, 28. Juli 2004 08:43 schrieb Ralf Müller:
Am Dienstag, 27. Juli 2004 18:56 schrieb Lennon Day-Reynolds:

Moin Lennon,

I'm not sure, how to do that.

Passing

grant all on lcwa.* to ram identified by 'lcwa';

to the mysql-client did not succeed. the DBI-Statement lead to the same
results.
Click to expand...

got it:

irb(main):003:0>
DBI.connect('DBI:Mysql:database=lcwa;host=lilith;port=6080','ram','lcwa')

works, but

irb(main):005:0>
DBI.connect('DBI:Mysql:database=lcwa;host=localhost;port=6080','ram','lcwa')

does not. Seems werid to me, cause 'host=localhost' should work, should'nt it?
 
M

Michael Neumann

Ralf said:
Am Mittwoch, 28. Juli 2004 08:43 schrieb Ralf Müller:



got it:

irb(main):003:0>
DBI.connect('DBI:Mysql:database=lcwa;host=lilith;port=6080','ram','lcwa')

works, but

irb(main):005:0>
DBI.connect('DBI:Mysql:database=lcwa;host=localhost;port=6080','ram','lcwa')

does not. Seems werid to me, cause 'host=localhost' should work, should'nt it
Click to expand...

Hm, localhost=127.0.0.1 and lilith=192.168.1.xx (or something alike),
then it should not work.

Regards,

Michael
 
L

Leonid Khachaturov

Ralf said:
got it:

irb(main):003:0>
DBI.connect('DBI:Mysql:database=lcwa;host=lilith;port=6080','ram','lcwa')

works, but

irb(main):005:0>
DBI.connect('DBI:Mysql:database=lcwa;host=localhost;port=6080','ram','lcwa')

does not. Seems werid to me, cause 'host=localhost' should work, should'nt it?
Click to expand...
It depends on security settings. Check out the 'db' and 'host' tables in
'mysql' database - if localhost is specified as a host for a database,
it means only localhost can connect to it. If '*' is specified, it means
"everybody except localhost".
 
R

Ralf Müller

Am Mittwoch, 28. Juli 2004 11:00 schrieb Michael Neumann:
Hm, localhost=127.0.0.1 and lilith=192.168.1.xx (or something alike),
then it should not work.
Click to expand...
So this behavior is correct and the reason is that TCPSocket -
UnixDomainSocket-thing Lennon wrote?

root@lilith:~#netstat -lnp | grep mysql
tcp 0 0 0.0.0.0:6080 0.0.0.0:* LISTEN
2268/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
998/mysqld
unix 2 [ ACC ] STREAM LISTENING 4454 998/mysqld
/var/lib/mysql/mysql.sock
unix 2 [ ACC ] STREAM LISTENING 148042 2268/mysqld
/home/ram/amp/mysql/var/mysql.sock

Is the lo interface bound to the unixSocket and eth0 to the tcpSocket?
I don't know any internals about network interfaces and Sockets.

regards
ralf
 
M

Michael Neumann

Ralf said:
Am Mittwoch, 28. Juli 2004 11:00 schrieb Michael Neumann:
Hm, localhost=127.0.0.1 and lilith=192.168.1.xx (or something alike),
then it should not work.
Click to expand...

So this behavior is correct and the reason is that TCPSocket -
UnixDomainSocket-thing Lennon wrote?

root@lilith:~#netstat -lnp | grep mysql
tcp 0 0 0.0.0.0:6080 0.0.0.0:* LISTEN
2268/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
998/mysqld
unix 2 [ ACC ] STREAM LISTENING 4454 998/mysqld
/var/lib/mysql/mysql.sock
unix 2 [ ACC ] STREAM LISTENING 148042 2268/mysqld
/home/ram/amp/mysql/var/mysql.sock

Is the lo interface bound to the unixSocket and eth0 to the tcpSocket?
Click to expand...

no. thats okay. the problem is within mysqld, otherwise you would have
get a socket connection error and not an authentification error from
mysql. sorry, for the confusion ;-)

Regards,

Michael
 
R

Ralf Müller

Am Mittwoch, 28. Juli 2004 11:01 schrieb Leonid Khachaturov:
It depends on security settings. Check out the 'db' and 'host' tables in
'mysql' database - if localhost is specified as a host for a database,
it means only localhost can connect to it. If '*' is specified, it means
"everybody except localhost".
Click to expand...

Hi Leonid,

my 'db' table looks like

+-----------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv |
+-----------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+
| % | test | | Y | Y | Y | Y
| % | test\_% | | Y | Y | Y | Y
| localhost | lcwa | ram | Y | Y | Y | Y
| % | lcwa | ram | Y | Y | Y | Y
+-----------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+

Does these settings mean, that any host can connect to lcwa?
 
L

Leonid Khachaturov

Ralf said:
Am Mittwoch, 28. Juli 2004 11:01 schrieb Leonid Khachaturov:



Hi Leonid,

my 'db' table looks like

+-----------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv |
+-----------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+
| % | test | | Y | Y | Y | Y
| % | test\_% | | Y | Y | Y | Y
| localhost | lcwa | ram | Y | Y | Y | Y
| % | lcwa | ram | Y | Y | Y | Y
+-----------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+

Does these settings mean, that any host can connect to lcwa?
Click to expand...
I'm not sure both localhost and % are allowed at the same time... Maybe
yes, maybe no.
 
L

Lennon Day-Reynolds

I've always done my security settings in MySQL as something like the following:

grant all on somedb.* to lennon@localhost identified by 'mypasswd';
grant all on somedb.* to lennon identified by 'mypasswd';

I think that the problem is that MySQL treats truly "local"
connections (i.e., via the domain socket created on the filesystem by
mysqld) differently from all network connections, even those made over
the loopback interface.

If the Ruby MySQL bindings used the local domain socket instead of TCP
to connect to the MySQL server, you would see the same behavior you do
with the command-line client. Try adding a 'user@localhost' privilege
grant like the above to your database, and see if the connection
works.

Lennon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DBI 2
connecting to sql server with dbi ? 0
Beginner's Guide to getting CipherSweet working with PDO and MYSQL 1
Perl to SQLite bridge is not working, database connect fails .... 3
DBI Connect failure - can we retry please? 2
Ruby DBI class 2
MySQL, Ruby, DBI connetc problem...help please 6
mysql -- cannot connect (can in Perl) 6

Members online

Total: 2,429 (members: 2, guests: 2,427)
Robots: 159

Forum statistics

Threads
474,151
Messages
2,570,854
Members
47,394
Latest member
Olekdev

Latest Threads

Top