homepage Welcome to WebmasterWorld Guest from 54.167.185.110
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

    
perl and MYSQL
padutch




msg:3910989
 2:09 pm on May 11, 2009 (gmt 0)

From the beginning. I am trying to install a classifieds script with MYSQL. The index.cgi uses sitevariables.pm for all paths and anything else it needs. The problem is that the script cannot find MYSQL.The scripts has the following snippit to call the MYSQL

if ($dbh = DBI->connect("dbi:mysql:$svb::mysqldb","$svb::mysqluser","$svb::mysqlpass")) here is the error I get

[Mon May 4 07:53:12 2009] index.cgi: DBI connect('rlw.examplemysql.com','rlw',...) failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) at /home/users/web/b1263/hy.rlw/cgi-bin/class/index.cgi line 39

here are the settings for this line of code

our $mysqluser = 'rlw';
our $mysqlpass = 'deadly';
our $mysqldb = 'rlw.examplemysql.com'

when I am in MYSQL and have it generate a code it gives me the following

$db = DBI->connect('dbi:mysql:rlw:rlw.examplemysql.com:3306','rlw','deadly') or die('Couldnot connect');

I replaced the privious code with this changing $db to dbh and had a syntax error that I fixed and now I get

(https://protected.example.net/rlw/cgi-bin/class/template.html): No such file or directory

am really new to this and need some help
Dutch

[edited by: phranque at 6:45 pm (utc) on May 11, 2009]
[edit reason] hosting specifics [/edit]

 

rainborick




msg:3911186
 6:07 pm on May 11, 2009 (gmt 0)

I wouldn't change the variable names in a pre-packaged script. It looks like the script author used standard variable names taken from the CPAN DBI module which all have defined uses. Restore the original script first.

It looks like the value for $mysqldb is incorrect or incomplete. It should include the database server address and the database host name, as in:

our $mysqldb = "dbname@dbhost:port";

That's where I'd start until you stop getting the "fail to connect" errors. Are the database name and user name the same ("rlw")? Sorry I can't be of more help.

rocknbil




msg:3911553
 3:50 am on May 12, 2009 (gmt 0)

From the beginning.

Welcome aboard padutch, I think I see the problem, but yes, let's start from the beginning. :-) Get your original unedited code on here (anonymize the login names and mysql server!) Once you start tweaking the code you start to slip farther away from a solution. So let's try again with the original code, don't change the variable names.

I'm presuming it goes more like this, in the original (with the db names/etc. first)

our $mysqluser = 'rlw';
our $mysqlpass = 'deadly';
our $mysqldb = 'rlw.examplemysql.com';

if ($dbh = DBI->connect("dbi:mysql:$svb::mysqldb","$svb::mysqluser","$svb::mysqlpass")) {
### It does something in this IF, right?
}

Note my bolded highlighting. What you are saying here is you've created a database named 'rlw.examplemysql.com'. Which you can't, mysql won't let you. Enter your database name here:

our $mysqldb = 'my_database_name';

What's missing here is a variable for a database server, so I am presuming this connect line is only meant for a localhost server (mysql server on the same machine as the script,) which is fine. (This is why the error says "Can't connect to local MySQL server.") If it were a remote mysql server, it would look more like this:

our $mysqluser = 'rlw';
our $mysqlpass = 'deadly';
our $mysqldb = 'my_database_name';
our $mysqlhost = 'rlw.examplemysql.com';
## OR our $mysqlhost = 'localhost';

if ($dbh = DBI->connect("dbi:mysql:$svb::mysqldb:$svb::mysqlhost","$svb::mysqluser","$svb::mysqlpass")) {
### It does something in this IF, right?
}

An added note,
(https://protected.example.net/rlw/cgi-bin/class/template.html): No such file or directory

No such file or directory means the script is trying to open a file on this system.. Like

open (FILE,$template) or die("$template $!");

($! contains the system error: "no such file or directory")

But you are using an https: protocol there. It should be the system path to the file, using your previous example,

$template = '/home/users/web/b1263/rlw/cgi-bin/class/template.html';

padutch




msg:3912035
 7:31 pm on May 12, 2009 (gmt 0)

rainborick, still get the error when I use our $mysqldb = 'rlw.examplemysql.com:3306';

rocknbil,
#MySQL settings. Set your username, password, and
#database name.
our $mysqluser = 'rlw';
our $mysqlpass = 'mypassword';
our $mysqldb = 'rlw.examplemysql.com:3306';

# CONNECT TO MYSQL DATABASE ##############################################
our $dbh;
if ($dbh = DBI->connect("dbi:mysql:$svb::mysqldb","$svb::mysqluser","$svb::mysqlpass")){
}else{
print "Content-type:text/html\n\n";
cmn::dienice("$lang::connect\: $DBI::errstr");

this error
(https://protected.example.net/rlw/cgi-bin/class/template.html): No such file or directory

only happens when I change CONNECT TO MYSQL DATABASE so I changed it back to the original code that came with the script

[edited by: phranque at 12:40 am (utc) on May 13, 2009]
[edit reason] exemplified specifics [/edit]

rocknbil




msg:3912184
 11:25 pm on May 12, 2009 (gmt 0)


#MySQL settings. Set your username, password, and
#database name.

Database NAME. Not database SERVER. This

rlw.examplemysql.com:3306

Is a database server.

The DBI module parameters are like this, with database server being optional, if you plan to connect via localhost. If database server is omitted, it defaults to localhost (as I said above:)

$dbh = DBI->connect("(database driver):(database type):(database name):(database server)","(database user)","(database password)");

The connection string used by your script is devoid of a database server. This tells me it's intended for a local mysql host.

So you are using the database server value where you need to use the name of the database itself.

Now: if your database server - rlw.examplemysql.com - is NOT on the same machine as your script, this means you need to connect remotely (not via "localhost.") In this case you would make the modifications I speculated on in my first reply for a remote server.

But in any case, get your database name in the database name field.

[edited by: phranque at 12:42 am (utc) on May 13, 2009]
[edit reason] disabled graphic smileys ;) [/edit]

padutch




msg:3912648
 3:32 pm on May 13, 2009 (gmt 0)

rocknbil, ok got the right database name .
our $mysqluser = 'rlw';
our $mysqlpass = 'deadly';
our $mysqldb = 'rlw';

database name is rlw and user is rlw but still get an error in my error log.

[Wed May 13 11:22:50 2009] index.cgi: DBI connect('rlw','rlw',...) failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) at /home/users/web/b1263/hy.rlw/cgi-bin/class/index.cgi line 39

line 39 being

if ($dbh = DBI->connect("dbi:mysql:$svb::mysqldb","$svb::mysqluser","$svb::mysqlpass")){

so now what am I looking at?
Rick

chorny




msg:3912928
 8:36 pm on May 13, 2009 (gmt 0)
Check that MySQL server is running and /var/run/mysqld/mysqld.sock exists.
rocknbil




msg:3913613
 3:42 pm on May 14, 2009 (gmt 0)

so now what am I looking at?

Are you absolutely positively sure your database login info is correct?

Best option: verify the mysql connection via command line. Do you have a way to log in to the database via command line? If you do, this is a sure way to test it:

mysql -u rlw -p (press enter)

When prompted for password, enter the password.

If you get

mysql>

type

use rlw; (press enter)

You should get "database changed."

If it doesn't go exactly as above, the error messages should tell you what's wrong. "access denied for user" when you log in means there's no valid user with that name; if you log in and you get "access denied for user" when trying to use the database, it means the DB doesn't exist or this user does not have privileges to access it, or you've mistyped the name of the database.

Any of the above (and more) will kick the error you're seeing from your script.

Second thing to check: Do you know for a fact that your mysql server is "local" to your web site? This is important, as mentioned your $dbh connect is intended for a localhost. It appears you know your database server name

rlw.examplemysql.com

So you can try the following. As mentioned, mySQL was designed to allow remote connections - you can have a site on one server, mysql server on another. It's entirely possible this is your scenario, and as posted, your script will never connect remotely.

However, looking at the syntax of the code you've posted, per my previous example, you can make the following modifications safely:

our $mysqluser = 'rlw';
our $mysqlpass = 'deadly';
our $mysqldb = 'rlw';
our $mysqlhost = 'rlw.examplemysql.com';

if ($dbh = DBI->connect("dbi:mysql:$svb::mysqldb:$svb::mysqlhost","$svb::mysqluser","$svb::mysqlpass")) {
###....
}

What this will do is connect to the database server rlw.examplemysql.com, even if it's a local host. Just make sure it exists. :-)

One last comment to clarify something you mentioned earlier:

only happens when I change CONNECT TO MYSQL DATABASE so I changed it back to the original code that came with the script

The program logic is this:
if (connect to mysql server succeeds) {
## do something
}
#there is most likely an ELSE immediately following this:
else { ## error, probably }

So by changing $dbh to $db, you caused the connection attempt to FAIL. Thus it went on to "else" which probably uses a template to output the error. But since you have your template specified with https: instead of a local file path, it couldn't find it.

My point is, when you fix the database connection, you will STILL have a template error.

padutch




msg:3913747
 5:53 pm on May 14, 2009 (gmt 0)

my provider used a similar snippet to test mysql and it works. here it is.

#!/usr/bin/perl -w
use DBD::mysql;
my $host = 'rlw.#*$!#*$!xmysql.com';
my $user = 'rlw';
my $pw = 'deadly';
my $db = 'rlw';
my $dbh = DBI->connect( "dbi:mysql:dbname=$db;host=$host", "$user", "$pw")
or die ("Can't connect");
print "Content-type: text/html\n\n"; print "Success";

here is what I did

our $dbh;
if ($dbh = DBI->connect("dbi:mysql:$svb::mysqldb:$svb::mysqlhost","$svb::mysqluser","$svb::mysqlpass")){
}else{
print "Content-type:text/html\n\n";
cmn::dienice("$lang::connect\: $DBI::errstr");

I added $svb::mysqlhost to my script

but get ### (https://protected.#*$!#*$!xx.net/rlw/cgi-bin/class/template.html): No such file or directory

maybe it is the way I added the 2 files to the database. I imported them from my desktop?

padutch




msg:3913753
 5:56 pm on May 14, 2009 (gmt 0)

to reply to this question ## Are you absolutely positively sure your database login info is correct? ##
yes I am positive

padutch




msg:3914569
 5:47 pm on May 15, 2009 (gmt 0)

My web host provides a Script Checker so I ran my index.cgi with it. here are the results,

Checked home/cgi-bin/class/index.cgi

[Fri May 15 13:34:05 2009] index.cgi: Can't locate common.pm in @INC

(@INC contains: ./lib /etc/perl /usr/local/lib/perl/5.8.8

/usr/local/share/perl/5.8.8 /usr/lib/perl5 /usr/share/perl5

/usr/lib/perl/5.8 /usr/share/perl/5.8 /usr/local/lib/site_perl/5.8.8

/usr/local/lib/site_perl /usr/local/lib/perl/5.8.4

/usr/local/share/perl/5.8.4 .) at

/home/users/web/b1263/hy.rlw/cgi-bin/class/index.cgi line 30.

[Fri May 15 13:34:05 2009] index.cgi: BEGIN failed--compilation

aborted at /home/users/web/b1263/hy.rlw/cgi-bin/class/index.cgi line

30.

Content-type: text/html

<h1>Software error:</h1>

<pre>Can't locate common.pm in @INC (@INC contains: ./lib /etc/perl

/usr/local/lib/perl/5.8.8 /usr/local/share/perl/5.8.8 /usr/lib/perl5

/usr/share/perl5 /usr/lib/perl/5.8 /usr/share/perl/5.8

/usr/local/lib/site_perl/5.8.8 /usr/local/lib/site_perl

/usr/local/lib/perl/5.8.4 /usr/local/share/perl/5.8.4 .) at

/home/users/web/b1263/hy.rlw/cgi-bin/class/index.cgi line 30.

BEGIN failed--compilation aborted at

/home/users/web/b1263/hy.rlw/cgi-bin/class/index.cgi line 30.

</pre>

<p>

Maybe you know what it is telling me.

rocknbil




msg:3914811
 3:05 am on May 16, 2009 (gmt 0)

Well, I think maybe we're chasing ghosts now.

Is index.cgi the script you've been working with all along? What the above is telling you is it is looking for the perl module common.pm and can't find it.

Is common.pm the file where your variables are stored? These?

our $mysqluser = 'rlw';
our $mysqlpass = 'deadly';
our $mysqldb = 'rlw';
our $mysqlhost = 'rlw.examplemysql.com';

If this is the case - "common.pm" is part of this script - and it can't find it, that could have been your problem all along:

- script loads
- at the top is something like "use Common;" (or similar; the package name of common.pm.)
- Because perl imports only module available in the @INC array, if common.pm is in the same directory as the script, it will never find it.
- script attempts to connect to the database, but all the values - login name, pass, db name - are all null because it can't find the common.pm file.

A possible solution: at the top of your script,

push (@INC, '/full/path/to/where/common/pm/is');

will expose it to the script.

The other is to install common.pm in one of the directories in @INC, but you may not have access to those.

All this is guesswork without benefit of the entire script.

Wouldn't it be easier to pay a perl coder fifty bucks to install it for you? :-)

padutch




msg:3914944
 1:22 pm on May 16, 2009 (gmt 0)

Wouldn't it be easier to pay a perl coder fifty bucks to install it for you? :-)

to answer this question , it would be easier if I knew a coder willing to install for that.
Rick

rocknbil




msg:3914997
 5:32 pm on May 16, 2009 (gmt 0)

For anyone else reading this thread, via PM the problem has been sorted.

- This particular script indeed was intended for localhost, as there was no mysql server variable in the configuration.

- Per previous notes, adding the $mysqlhost variable to the site variables .pm file, modifying the connect line, and pointing the $mysqlhost to the correct mysql server fixed the database connection.

- There were some internal problems with this script needing alteration; it wasn't properly setting permissions on uploaded pictures and if left to default, it uploaded pics to the cgi-bin directory causing the picture views to fail in environments disallowing reads from the cgi-bin.

There was some confusion brought about by the "script checker" results. There were plain text and mysql versions of this script present, and some library files were in place for the plain text version, causing the errors shown above. It was unrelated to the genuine problem.

All fixed. :-)

phranque




msg:3915468
 9:40 pm on May 17, 2009 (gmt 0)

welcome to WebmasterWorld [webmasterworld.com], Rick!
i hope rocknbil got his 50 bucks.
=8)

that was a frustrating solution with some obscure problems and my thanks go to rocknbil for stepping up to resolve your issues.

padutch




msg:3915492
 10:13 pm on May 17, 2009 (gmt 0)

phranque
Bill did great. Wish I knew what he does.
Rick

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved