Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: MySQL, Perl script, DBI remote access problem

    Trying to write a simple script to access MySQL 4.0.20a using Perl 5.8.1 with additional modules like DBI and DBD-mysql on windows 2000 server. But i always get this error message even if trying on a local machine. Any suggestions??????

    ******************* ERROR displayed *****************
    DBI connect('mysql:localhost:3306','root',...) failed: Access denied for user: '
    root@localhost' (Using password: NO) at db1.pl line 46

    **********************THE Script!! *********************
    $dbuser = "root";
    $dbpasswd = "paw";
    $dbh = $DBI->connect( 'DBI:mysql:mysql:localhost:3306', $dbuser, $dbpasswd { RaiseError=>1, AutoCommit=>0 } );

    ************ THE DATABASE CONFIGURATION ***************
    mysql> select host, user, db from db;
    +-----------+------+---------+
    | host | user | db |
    +-----------+------+---------+
    | % | root | mysql |
    | % | | test |
    | % | | test\_% |
    | 10.% | root | mysql |
    | localhost | root | mysql |
    +-----------+------+---------+

    mysql> select host, user, password from user;
    +-----------+------+------------------+
    | host | user | password |
    +-----------+------+------------------+
    | localhost | root | 23e172e8219d5467 |
    | build | root | |
    | 10.% | root | 23e172e8219d5467 |
    | % | root | 23e172e8219d5467 |
    +-----------+------+------------------+

  2. #2
    Join Date
    Jun 2004
    Posts
    3

    Solution

    Quote Originally Posted by Wang Wong
    Trying to write a simple script to access MySQL 4.0.20a using Perl 5.8.1 with additional modules like DBI and DBD-mysql on windows 2000 server. But i always get this error message even if trying on a local machine. Any suggestions??????

    **********************THE Script!! *********************
    $dbuser = "root";
    $dbpasswd = "paw";
    $dbh = $DBI->connect( 'DBI:mysql:mysql:localhost:3306', $dbuser, $dbpasswd { RaiseError=>1, AutoCommit=>0 } );

    ************ THE DATABASE CONFIGURATION ***************
    mysql> select host, user, password from user;
    +-----------+------+------------------+
    | host | user | password |
    +-----------+------+------------------+
    | localhost | root | 23e172e8219d5467 |
    | build | root | |
    | % | root | |
    +-----------+------+------------------+
    I figure what is wrong with the whole setup. Actually the setup itself are very straight forward and accurate. I believe there is a compatibility problem with the password() function from MySQL and Perl DBI modules. (Perl 5.8.3 and MySQL 4.0.20a)

    To get DBI, perl and MySQL to work with any host remotely, perform the following steps. For example: You want user, john smith (username= jsmith) to have access to database name 'club' located on the server name, 'server1'. And you write up a script that allow john smith to execute from any hosts and display the 'club' database information.

    1. create a user account that has permission to 'club' database
    GRANT ALL PRIVILEGES club.* TO jsmith@'%' ;
    GRANT ALL PRIVILEGES club.* TO jsmith@'localhost';
    ****************DO NOT DO THIS *******************
    GRANT ALL PRIVILEGES club.* TO jsmith@'%' IDENTIFIED BY 'somePassword';
    GRANT ALL PRIVILEGES club.* TO jsmith@'localhost' IDENTIFIED BY 'passwd';
    WHY::because authentication always complain about the user not having the correct password.

    2. The following is what your script should look like:
    $dbuser = "jsmith";
    $dbpasswd = "anypassword";
    $dbname = "club";
    $dbhost = "server1";
    $dbport = "3308";

    $dsn = "DBI:mysql:database=$dbname;host=$host;port=$dbpor t";
    $dbh = $DBI->connect( $dsn, $dbuser, $dbpasswd { RaiseError=>1, AutoCommit=>0 } );

    3.....Wha-la......if you add a password to user name 'jsmith', then the script complain about the connection.
    ************ try it ************************
    UPDATE mysql.user SET PASSWORD=password( 'SomePasswd') where user ='jsmith';
    *****************************************

    CORRECT ME IF I AM WRONG!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •