308527 - Database Testing Script needed to assist users who have upgraded their back-end

Status: RESOLVED WONTFIX

(Bugzilla :: Installation & Upgrading, enhancement, P4)

Description

[Kevin Benton]

Users are complaining about errors talking with the database after upgrading
MySQL but failing to re-install their Perl DBD::mysql module.  Failing to
re-install DBD::mysql will cause Perl to fail communicating with the SQL server.

Suggested "contrib" script to follow.

Comment 1

[Kevin Benton]

As a contrib, this should not interfere with operations at all, but it will help
us make sure users have a tool to help them test their MySQL and Perl DBD::mysql
installations.  Without it, supporting users is more difficult as we don't have
a way to tell them how to test Perl's connectivity to MySQL.  This script stands
alone (doesn't need localconfig) to work.

Comment 2

[Kevin Benton]

Attachment: Perl DBD::mysql and MySQL server connectivity test script (testdb.pl)

Comment 3

[Kevin Benton]

Attachment: README for testdb.pl

Comment 4

[Kevin Benton]

Attachment: Replacement - too many apostrophes in "All's well that ends well."

Comment 5

[Kevin Benton]

Would like this to block for 2.22 since 2.20 doesn't require 4.x yet.  Still, I
think it should be available in 2.20 with the large number of users upgrading to
4.x.

Comment 6

[Max Kanat-Alexander]

Comment on attachment 196062 [details]
Replacement - too many apostrophes in "All's well that ends well."

>#!/usr/bin/perl

  You'll want a -w, really.

>my $db_host="localhost"; # replace with the host name of your MySQL server
>my $db_port="3306";      # replace with the port number of your server
>my $db_name="bugs";      # replace with your bugs database name
>my $db_user="bugs";      # replace with your MySQL bugs user name
>my $db_pass="secret";    # replace with your bugs user password
>my $db_type="mysql";     # replace with Pg if you're using Postgresql, but be
>                         #    warned - this script was not tested against PG.

  Just grab localconfig, that way the settings are guaranteed to be what
they're normally using.

>my $dsn = "DBI:${db_type}:host=${db_host};database=${db_name};port=${db_port}";
>my $dbh = DBI->connect($dsn, '', '', { RaiseError => 1,
>                                       PrintError => 1,
>                                       Username => $db_user,
>                                       Password => $db_pass,
>                                       ShowErrorStatement => 1 }) ||
>  die("Unable to connect to database.\n");

  checksetup already gives a much more detailed error message than this... is
this just small in order to simplify things?

>my $sth = $dbh->prepare("SELECT VERSION()") or die("Unable to prepare\n");
>$sth->execute() or die("Unable to execute query.\n");

>if ( my ($res)=$sth->fetchrow_array() ) {
>  print("All's well that end's well.\n");

  I like that, but I think it will confuse foreign-language users. :-)

Comment 7

[Kevin Benton]

(In reply to comment #6)
> (From update of attachment 196062 [details] [edit])
> >#!/usr/bin/perl
> 
>   You'll want a -w, really.

OK - can do.

> >my $db_host="localhost"; # replace with the host name of your MySQL server
> >my $db_port="3306";      # replace with the port number of your server
> >my $db_name="bugs";      # replace with your bugs database name
> >my $db_user="bugs";      # replace with your MySQL bugs user name
> >my $db_pass="secret";    # replace with your bugs user password
> >my $db_type="mysql";     # replace with Pg if you're using Postgresql, but be
> >                         #    warned - this script was not tested against PG.
> 
>   Just grab localconfig, that way the settings are guaranteed to be what
> they're normally using.

I didn't very intentionally.  I wanted this script to stand alone.  That way,
when users are having difficulty verifying whether or not their database works,
we can limit the entire test down to a single Perl script.  This makes it a lot
easier for people who aren't very familiar with Perl yet and may be a bit
overwhelmed with the task of installing Bugzilla for the very first time.  Joel
suggested adding functionality to check the settings in this script against the
settings in localconfig to make sure they match.  I'm in the process of doing
that but felt that a review for this part was still warranted.

> >my $dsn = "DBI:${db_type}:host=${db_host};database=${db_name};port=${db_port}";
> >my $dbh = DBI->connect($dsn, '', '', { RaiseError => 1,
> >                                       PrintError => 1,
> >                                       Username => $db_user,
> >                                       Password => $db_pass,
> >                                       ShowErrorStatement => 1 }) ||
> >  die("Unable to connect to database.\n");
> 
>   checksetup already gives a much more detailed error message than this... is
> this just small in order to simplify things?

Yes.  It's to help new admin's have a tool that will isolate the testing to just
the connectivity between Perl and MySQL along with their MySQL user setup.  Once
they've gotten that far, they can move back to checksetup.pl.

> >my $sth = $dbh->prepare("SELECT VERSION()") or die("Unable to prepare\n");
> >$sth->execute() or die("Unable to execute query.\n");
> 
> >if ( my ($res)=$sth->fetchrow_array() ) {
> >  print("All's well that end's well.\n");
> 
>   I like that, but I think it will confuse foreign-language users. :-)

Ok - I can change that.  Additional thoughts?  Thanks Max :)

Comment 8

[Vlad Dascalu]

Comment on attachment 196060 [details]
README for testdb.pl

The Initial Developer of the Original Code is Netscape Communications
Corporation. Portions created by Netscape are
Copyright (C) 1998 Netscape Communications Corporation. All
Rights Reserved.

Nit 1: You don't need to add Netscape Communications for content that you've created, and which is not based on a previous work that has the Netscape copyright.

Nit 2: The readme doesn't specify that this could be used for PG tests as well. The name of the script (testdb.pl) suggests DB testing for all BZ supported engines (which are 2 at the moment).

Looks good otherwise.

Comment 9

[Kevin Benton]

Attachment: README for testdb.pl

Carried forward review+ from vladd.  Removed extraneous Netscape license and now specifies that the test can be used for both MySQL and PostgreSQL

Comment 10

[Kevin Benton]

Attachment: Database connectivity checker (for users having difficulty with DBI/DBD and/or db server)

Again, yes, this is a tiny test designed to help users who don't understand all the stuff checksetup.pl is throwing at them and want to work one thing at a time.

Comment 11

[Kevin Benton]

Comment on attachment 212514 [details]
Database connectivity checker (for users having difficulty with DBI/DBD and/or db server)

Never mind - this patch has a bug in it.

Comment 12

[Kevin Benton]

Attachment: Perl DBI and database server connectivity test script (testdb.pl)

Fixed - optional parameters didn't belong, causing script to fail with working Perl / MySQL server.  Re-tested and script now performs as expected.  Added note that when specifying "localhost", driver attempts to use socket rather than network.  From my perspective, this is a bug in the DBD::mysql driver, but that's just me.  I changed the port to something I knew didn't have a MySQL connection on it with "localhost" as the $db_host and the check still passed.  When I changed from "localhost" to "127.0.0.1" and a known bad port, the check failed, which is what was expected.  After changing the port back, but leaving the $db_host at 127.0.0.1, the check passed.  Again, this was expected.

I'd really like to see someone with a known good PostgreSQL installation test this prior to release.

Comment 13

[Kevin Benton]

Comment on attachment 212520 [details]
Perl DBI and database server connectivity test script (testdb.pl)

Dang browser cache issue.

Comment 14

[Kevin Benton]

Attachment: Perl DBI and database server connectivity test script (testdb.pl)

Replaced following cache issue.

Comment 15

[Max Kanat-Alexander]

Comment on attachment 212522 [details]
Perl DBI and database server connectivity test script (testdb.pl)

I think requiring somebody to set variables in some place other than localconfig just introduces another place that somebody could make a mistake.

I'd want to use this script to see if somebody's installation was working or not. That is, I'd tell them "run testdb.pl and tell me what it outputs," and I'd want that to tell me what's wrong with checksetup, yes? But if this uses something that's not localconfig, then I can't do that.

Also, are you sure this script will work on PostgreSQL, or before checksetup.pl has even created the database? It doesn't look like it will--we have $dbh->bz_server_version() for VERSION(), because that's not cross-db compatible.

Also, does it properly print the error message if the connection fails, even? I know that we use $DBI::errstr manually for that in checksetup.pl.

Comment 16

[Kevin Benton]

Hi Max.

(In reply to comment #15)
> (From update of attachment 212522 [details] [edit])
> I think requiring somebody to set variables in some place other than
> localconfig just introduces another place that somebody could make a mistake.

I agree that there's a possibility that the user could make a mistake in one place and not the other.  On the other hand, by requiring the script to "stand alone", it's one less thing to have to work with while figuring out issues.  When this script works, users can copy/paste from the script directly into their localconfig.

> I'd want to use this script to see if somebody's installation was working or
> not. That is, I'd tell them "run testdb.pl and tell me what it outputs," and
> I'd want that to tell me what's wrong with checksetup, yes? But if this uses
> something that's not localconfig, then I can't do that.

Point taken - after the user has properly configured this script and it's running, they could have it run again with the values from localconfig.  It could report...

Stand-alone Test: PASS
Localconfig Test: FAIL

That would tell us that the setup is different between the two files.  I would update the script to check whether or not the $db_ variables were set internally, and if not, only run the second test (for those who use it in maintenance mode).

> Also, are you sure this script will work on PostgreSQL, or before checksetup.pl
> has even created the database? It doesn't look like it will--we have
> $dbh->bz_server_version() for VERSION(), because that's not cross-db
> compatible.

Isn't the user responsible for creating the db before checksetup.pl runs for the first time as part of the administrative setup?

It's not?  VERSION() is available in PgSQL 7.3 (http://www.postgresql.org/docs/7.3/static/functions-misc.html) and up.  It's also available in MySQL 3.x and up.  Notice that I don't care about the output of VERSION(), but I do care about the fact that it is a valid function.  Once the user has that much running, they can continue with checksetup.pl.  If the server version doesn't match expectations, checksetup.pl will gripe about it.  Adding parsing and server version requirements to testdb.pl make it more work to maintain as we change requirements.  That was something I didn't want to worry about.  I'd rather leave the complexity to checksetup.pl and use the KISS method (keep it simple, silly).

> Also, does it properly print the error message if the connection fails, even?
> I know that we use $DBI::errstr manually for that in checksetup.pl.

During testing, I ran it in three conditions:

1) Against a working MySQL server,
2) Against a working MySQL server without the required DB,
3) Against a known unused port (didn't want to take working server off-line),
4) Against SSH.

In case 1, it worked, in cases 2 through 4, it failed (as expected).  I then repeated those conditions on a second (remote) host and got the same results.

Are you saying then that you want to see the error message reported back by DBI?  I can do that.

Comment 17

[Max Kanat-Alexander]

(In reply to comment #16)
> Point taken - after the user has properly configured this script and it's
> running, they could have it run again with the values from localconfig.  It
> could report...
> 
> Stand-alone Test: PASS
> Localconfig Test: FAIL

  Sounds good to me.

> Isn't the user responsible for creating the db before checksetup.pl runs for
> the first time as part of the administrative setup?

  No, checksetup is responsible for creating the DB.

> It's not?  VERSION() is available in PgSQL 7.3
> (http://www.postgresql.org/docs/7.3/static/functions-misc.html) and up.  It's
> also available in MySQL 3.x and up.  Notice that I don't care about the output
> of VERSION(), but I do care about the fact that it is a valid function.

  Okay. You might want to use something that's ANSI-standard, then, or something that we can be sure will be supported on all databases we may support in the future. Would just "SELECT 1" work?

> Are you saying then that you want to see the error message reported back by
> DBI?  I can do that.

  Sure. It's hard to know why it failed without the error.

Comment 18

[Vlad Dascalu]

This should remain fixed until it's backed out, and I still am within those 60 minutes.

Comment 19

[Vlad Dascalu]

Sorry, wrong bug :)

Comment 20

[Kevin Benton]

I am unable to work this bug at this time due to other priorities.  Reassigning to default assignee.

Comment 21

[Max Kanat-Alexander]

Overall I think this is actually the job of checksetup.pl.