----- Original Message -----
From: "Harry Rorarius" <[EMAIL PROTECTED]>
To: "Mysql list" <[EMAIL PROTECTED]>
Sent: Thursday, March 28, 2002 9:18 AM
Subject: Fw: sql
>
> ----- Original Message -----
> From: "Harry Rorarius" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, March 28, 2002 7:46 AM
> Subject: sql
>
>
> > I am a new comer to the sql world. I have some but brief knowledge of
> perl.
> > here goes!
> >
> > I am using mysql on a Winnt platform and I have perl 5 installed on it.
I
> > did setup dbi in perl and I know that mod is working. I have been
trying
> to
> > get one of the scripts that came with the copy of mysql
> > (mysql_setpermissions.pl) to work. I have it in my cgi-bin (the only
> place
> > I execute perl scripts. When I run this script it does nothing it just
> > hangs until the cgi times out.
> >
> > Most of the things I have read about in all the documentation and even
> this
> > mail help is reared for the Unix/Linux users. I have tried to get a
linux
> > server to work but that is a different issue.
> >
> > anyways I must be missing some clues to get mysql to talk to my scripts
> and
> > to my web browser. Is there anyone out there with Winnt to mysql
> experience
> >
> > HELP!!!!
> >
> I have been working with this script (mysql_setpermissions.pl) for a while
> and I chanded the path for my.cnf because in Winnt Mysql uses my.ini and
> that is used for the admin program. Mysql does not automaticly use
my.cnf,
> so I set the path. The error I get know is:
> The specified CGI application misbehaved by not returning a complete set
of
> HTTP headers. The headers it did return are:
>
>
> syntax error at F:\web\cgi-bin\mysql_setpermission.pl line 71, near
> "exit(0); # the end."
> (Might be a runaway multi-line .. string starting on line 67)
> Can't use global $_ in "my" at F:\web\cgi-bin\mysql_setpermission.pl line
> 102, near "= $_"
> syntax error at F:\web\cgi-bin\mysql_setpermission.pl line 118, near "}"
> Execution of F:\web\cgi-bin\mysql_setpermission.pl aborted due to
> compilation errors.
>
> This is the script:
>
> #!/perl/bin
> ## Emacs, this is -*- perl -*- mode? :-)
> ##
> ## Permission setter for MySQL
> ##
> ## mady by Luuk de Boer ([EMAIL PROTECTED]) 1998.
> ## it's made under GPL ...:-))
> ##
> ##
>
############################################################################
> ## History
> ##
> ## 1.0 first start of the program
> ## 1.1 some changes from monty and after that
> ## initial release in mysql 3.22.10 (nov 1998)
> ## 1.2 begin screen now in a loop + quit is using 0 instead of 9
> ## after ideas of Paul DuBois.
> ## 1.2a Add Grant, References, Index and Alter privilege handling (Monty)
>
> #### TODO
> #
> # empty ... suggestions ... mail them to me ...
>
>
> $version="1.2";
>
> use DBI;
> use Getopt::Long;
> use strict;
> use vars qw($dbh $hostname $opt_user $opt_password $opt_help $opt_host
> $opt_socket $opt_port $host $version);
>
>
> $dbh=$host=$opt_user= $opt_password= $opt_help= $opt_host= $opt_socket=
"";
> $opt_port=0;
>
> #read_my_cnf(); # Read options from ~/.my.cnf
> read_c:/mysql/my.cnf;
>
> GetOptions("user=s","password=s","help","host=s","socket=s","port=i");
>
> usage() if ($opt_help); # the help function
>
> if ($opt_host eq '')
> {
> $hostname = "localhost";
> }
> else
> {
> $hostname = $opt_host;
> }
>
> # ask for a password if no password is set already
> if ($opt_password eq '')
> {
> system "stty -echo";
> print "Password for user $opt_user to connect to MySQL: ";
> $opt_password = <STDIN>;
> chomp($opt_password);
> system "stty echo";
> print "\n";
> }
>
>
> # make the connection to MySQL
> $dbh=
>
DBI->connect("DBI:mysql:mysql:host=$hostname:port=$opt_port:mysql_socket=$op
> t_socket",$opt_user,$opt_password, {PrintError => 0}) ||
> die("Can't make a connection to the mysql server.\n The error:
> $DBI::errstr");
>
> # the start of the program
> &q1();
> exit(0); # the end...
>
> #####
> # below all subroutines of the program
> #####
>
> ###
> # the beginning of the program
> ###
> sub q1 { # first question ...
> my ($answer,$end);
> while (! $end) {
> print "#"x70;
> print "\n";
> print "## Welcome to the permission setter $version for MySQL.\n";
> print "## made by Luuk de Boer\n";
> print "#"x70;
> print "\n";
> print "What would you like to do:\n";
> print " 1. Set password for a user.\n";
> print " 2. Add a database + user privilege for that database.\n";
> print " - user can do all except all admin functions\n";
> print " 3. Add user privilege for an existing database.\n";
> print " - user can do all except all admin functions\n";
> print " 4. Add user privilege for an existing database.\n";
> print " - user can do all except all admin functions + no
> create/drop\n";
> print " 5. Add user privilege for an existing database.\n";
> print " - user can do only selects (no update/delete/insert
> etc.)\n";
> print " 0. exit this program\n";
> print "\nMake your choice [1,2,3,4,5,0]: ";
> while (<STDIN>) {
> $answer = $_;
> chomp($answer);
> if ($answer =~ /1|2|3|4|5|0/) {
> &setpwd if ($answer == 1);
> &addall($answer) if ($answer =~ /^[2345]$/);
> if ($answer == 0) {
> print "Sorry, hope we can help you next time \n\n";
> $end = 1;
> }
> } else {
> print "Your answer was $answer\n";
> print "and that's wrong .... Try again\n";
> }
> last;
> }
> }
> }
>
> ###
> # set a password for a user
> ###
> sub setpwd
> {
> my ($user,$pass,$host);
> print "\n\nSetting a (new) password for a user.\n";
>
> $user = user();
> $pass = newpass($user);
> $host = hosts($user);
>
> print "#"x70;
> print "\n\n";
> print "That was it ... here is an overview of what you gave to me:\n";
> print "The username : $user\n";
> # print "The password : $pass\n";
> print "The host : $host\n";
> print "#"x70;
> print "\n\n";
> print "Are you pretty sure you would like to implement this [yes/no]: ";
> my $no = <STDIN>;
> chomp($no);
> if ($no =~ /n/i)
> {
> print "Okay .. that was it then ... See ya\n\n";
> return(0);
> }
> else
> {
> print "Okay ... let's go then ...\n\n";
> }
> $user = $dbh->quote($user);
> $host = $dbh->quote($host);
> if ($pass eq '')
> {
> $pass = "''";
> }
> else
> {
> $pass = "PASSWORD(". $dbh->quote($pass) . ")";
> }
> my $sth = $dbh->prepare("update user set Password=$pass where User =
$user
> and Host = $host") || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> $sth->finish;
> print "The password is set for user $user.\n\n";
>
> }
>
> ###
> # all things which will be added are done here
> ###
> sub addall
> {
> my ($todo) = @_;
> my ($answer,$good,$db,$user,$pass,$host,$priv);
>
> if ($todo == 2)
> {
> $db = newdatabase();
> }
> else
> {
> $db = database();
> }
>
> $user = newuser();
> $pass = newpass();
> $host = newhosts();
>
> print "#"x70;
> print "\n\n";
> print "That was it ... here is an overview of what you gave to me:\n";
> print "The database name : $db\n";
> print "The username : $user\n";
> # print "The password : $pass\n";
> print "The host(s) : $host\n";
> print "#"x70;
> print "\n\n";
> print "Are you pretty sure you would like to implement this [yes/no]: ";
> my $no = <STDIN>;
> chomp($no);
> if ($no =~ /n/i)
> {
> print "Okay .. that was it then ... See ya\n\n";
> return(0);
> }
> else
> {
> print "Okay ... let's go then ...\n\n";
> }
>
> if ($todo == 2)
> {
> # create the database
> my $sth = $dbh->do("create database $db") || $dbh->errstr;
> }
>
> # select the privilege ....
> if (($todo == 2) || ($todo == 3))
> {
> $priv = "'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'";
> }
> elsif ($todo == 4)
> {
> $priv = "'Y','Y','Y','Y','N','N','N','Y','Y','Y'";
> }
> elsif ($todo == 5)
> {
> $priv = "'Y','N','N','N','N','N','N','N','N','N'";
> }
> else
> {
> print "Sorry, choice number $todo isn't known inside the program ..
See
> ya\n";
> quit();
> }
>
> my @hosts = split(/,/,$host);
> $user = $dbh->quote($user);
> $db = $dbh->quote($db);
> if ($pass eq '')
> {
> $pass = "''";
> }
> else
> {
> $pass = "PASSWORD(". $dbh->quote($pass) . ")";
> }
> foreach my $key (@hosts)
> {
> my $key1 = $dbh->quote($key);
> my $sth = $dbh->prepare("select Host,User from user where Host = $key1
> and User = $user") || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> my @r = $sth->fetchrow_array;
> if ($r[0])
> {
> print "WARNING WARNING SKIPPING CREATE FOR USER $user AND HOST
> $key\n";
> print "Reason: entry already exists in the user table.\n";
> }
> else
> {
> $sth = $dbh->prepare("insert into user (Host,User,Password)
> values($key1,$user,$pass)") || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> $sth->finish;
> }
> $sth = $dbh->prepare("INSERT INTO db
>
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Dr
> op_priv,Grant_priv,References_priv,Index_priv,Alter_priv) VALUES
> ($key1,$db,$user,$priv)") || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> $sth->finish;
> }
> $dbh->do("flush privileges") || print "Can't load privileges\n";
> print "Everything is inserted and mysql privileges have been
> reloaded.\n\n";
> }
>
> ###
> # ask for a new database name
> ###
> sub newdatabase
> {
> my ($answer,$good,$db);
> print "\n\nWhich database would you like to add: ";
> while (<STDIN>)
> {
> $answer = $_;
> $good = 0;
> chomp($answer);
> if ($answer)
> {
> my $sth = $dbh->prepare("show databases") || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> while (my @r = $sth->fetchrow_array)
> {
> if ($r[0] eq $answer)
> {
> print "\n\nSorry, this database name is already in use; try
> something else: ";
> $good = 1;
> }
> }
> }
> else
> {
> print "You must type something ...\nTry again: ";
> next;
> }
> last if ($good == 0);
> }
> $db = $answer;
> print "The new database $db will be created\n";
> return($db);
> }
>
> ###
> # select a database
> ###
> sub database
> {
> my ($answer,$good,$db);
> print "\n\nWhich database would you like to select: \n";
> print "You can choose from: \n";
> my $sth = $dbh->prepare("show databases") || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> while (my @r = $sth->fetchrow_array)
> {
> print " - $r[0] \n";
> }
> print "Which database will it be (case sensitive): ";
> while (<STDIN>)
> {
> $answer = $_;
> $good = 0;
> chomp($answer);
> if ($answer)
> {
> my $sth = $dbh->prepare("show databases") || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> while (my @r = $sth->fetchrow_array)
> {
> if ($r[0] eq $answer)
> {
> $good = 1;
> $db = $r[0];
> last;
> }
> }
> }
> else
> {
> print "You must type something ...\nTry again: ";
> next;
> }
> if ($good == 1)
> {
> last;
> }
> else
> {
> print "You must select one from the list.\nTry again: ";
> next;
> }
> }
> print "The database $db will be used.\n";
> return($db);
> }
>
> ###
> # ask for a new username
> ###
> sub newuser
> {
> my ($answer,$user);
>
> print "\nWhat username is to be created: ";
> while(<STDIN>)
> {
> $answer = $_;
> chomp($answer);
> if ($answer)
> {
> $user = $answer;
> }
> else
> {
> print "You must type something ...\nTry again: ";
> next;
> }
> last;
> }
> print "Username = $user\n";
> return($user);
> }
>
> ###
> # ask for a user which is already in the user table
> ###
> sub user
> {
> my ($answer,$user);
>
> print "\nFor which user do you want to specify a password: ";
> while(<STDIN>)
> {
> $answer = $_;
> chomp($answer);
> if ($answer)
> {
> my $sth = $dbh->prepare("select User from user where User =
> '$answer'") || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> my @r = $sth->fetchrow_array;
> if ($r[0])
> {
> $user = $r[0];
> }
> else
> {
> print "Sorry, user $answer isn't known in the user table.\nTry
again:
> ";
> next;
> }
> }
> else
> {
> print "You must type something ...\nTry again: ";
> next;
> }
> last;
> }
> print "Username = $user\n";
> return($user);
> }
>
> ###
> # ask for a new password
> ###
> sub newpass
> {
> my ($user) = @_;
> my ($answer,$good,$pass,$yes);
>
> print "Would you like to set a password for $user [y/n]: ";
> $yes = <STDIN>;
> chomp($yes);
> if ($yes =~ /y/)
> {
> system "stty -echo";
> print "What password do you want to specify for $user: ";
> while(<STDIN>)
> {
> $answer = $_;
> chomp($answer);
> system "stty echo";
> print "\n";
> if ($answer)
> {
> system "stty -echo";
> print "Type the password again: ";
> my $second = <STDIN>;
> chomp($second);
> system "stty echo";
> print "\n";
> if ($answer ne $second)
> {
> print "Passwords aren't the same; we begin from scratch
again.\n";
> system "stty -echo";
> print "Password please: ";
> next;
> }
> else
> {
> $pass = $answer;
> }
> }
> else
> {
> print "You must type something ...\nTry again: ";
> next;
> }
> last;
> }
> # print "The password for $user is $pass.\n";
> }
> else
> {
> print "We won't set a password so the user doesn't have to use it\n";
> $pass = "";
> }
> return($pass);
> }
>
> ###
> # ask for new hosts
> ###
> sub newhosts
> {
> my ($answer,$good,$host);
>
> print "We now need to know from what host(s) the user will connect.\n";
> print "Keep in mind that % means 'from any host' ...\n";
> print "The host please: ";
> while(<STDIN>)
> {
> $answer = $_;
> chomp($answer);
> if ($answer)
> {
> $host .= ",$answer";
> print "Would you like to add another host [yes/no]: ";
> my $yes = <STDIN>;
> chomp($yes);
> if ($yes =~ /y/i)
> {
> print "Okay, give us the host please: ";
> next;
> }
> else
> {
> print "Okay we keep it with this ...\n";
> }
> }
> else
> {
> print "You must type something ...\nTry again: ";
> next;
> }
> last;
> }
> $host =~ s/^,//;
> print "The following host(s) will be used: $host.\n";
> return($host);
> }
>
> ###
> # ask for a host which is already in the user table
> ###
> sub hosts
> {
> my ($user) = @_;
> my ($answer,$good,$host);
>
> print "We now need to know which host for $user we have to change.\n";
> print "Choose from the following hosts: \n";
> $user = $dbh->quote($user);
> my $sth = $dbh->prepare("select Host,User from user where User = $user")
> || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> while (my @r = $sth->fetchrow_array)
> {
> print " - $r[0] \n";
> }
> print "The host please (case sensitive): ";
> while(<STDIN>)
> {
> $answer = $_;
> chomp($answer);
> if ($answer)
> {
> $sth = $dbh->prepare("select Host,User from user where Host =
> '$answer' and User = $user") || die $dbh->errstr;
> $sth->execute || die $dbh->errstr;
> my @r = $sth->fetchrow_array;
> if ($r[0])
> {
> $host = $answer;
> last;
> }
> else
> {
> print "You have to select a host from the list ...\nTry again: ";
> next;
> }
> }
> else
> {
> print "You have to type something ...\nTry again: ";
> next;
> }
> last;
> }
> print "The following host will be used: $host.\n";
> return($host);
> }
>
> ###
> # a nice quit (first disconnect and then exit
> ###
> sub quit
> {
> $dbh->disconnect;
> exit(0);
> }
>
> ###
> # Read variables password, port and socket from .my.cnf under the client
> # or perl groups
> ###
>
> sub read_my_cnf
> {
> open(TMP,$ENV{'HOME'} . "f:/web/sql/.my.cnf") || return 1;
> while (<TMP>)
> {
> if (/^\[(client|perl)\]/i)
> {
> while ((defined($_=<TMP>)) && !/^\[\w+\]/)
> {
> print $_;
> if (/^host\s*=\s*(\S+)/i)
> {
> $opt_host = $1;
> }
> elsif (/^user\s*=\s*(\S+)/i)
> {
> $opt_user = $1;
> }
> elsif (/^password\s*=\s*(\S+)/i)
> {
> $opt_password = $1;
> }
> elsif (/^port\s*=\s*(\S+)/i)
> {
> $opt_port = $1;
> }
> elsif (/^socket\s*=\s*(\S+)/i)
> {
> $opt_socket = $1;
> }
> }
> }
> }
> close(TMP);
> }
>
> ###
> # the help text
> ###
> sub usage
> {
> print <<EOL;
> ----------------------------------------------------------------------
> The permission setter for MySQL.
> version: $version
>
> made by: Luuk de Boer <luuk\@wxs.nl>
> ----------------------------------------------------------------------
>
> The permission setter is a little program which can help you add users
> or databases or change passwords in MySQL. Keep in mind that we don't
> check permissions which already been set in MySQL. So if you can't
> connect to MySQL using the permission you just added, take a look at
> the permissions which have already been set in MySQL.
>
> The permission setter first reads your .my.cnf file in your Home
> directory if it exists.
>
> Options for the permission setter:
>
> --help : print this help message and exit.
>
> The options shown below are used for making the connection to the MySQL
> server. Keep in mind that the permissions for the user specified via
> these options must be sufficient to add users / create databases / set
> passwords.
>
> --user : is the username to connect with.
> --password : the password of the username.
> --host : the host to connect to.
> --socket : the socket to connect to.
> --port : the port number of the host to connect to.
>
> If you don't give a password and no password is set in your .my.cnf
> file, then the permission setter will ask for a password.
>
>
> EOL
> exit(0);
> }
>
>
OK I went through the entire script and I got the errors to stop. now when
I execute the script it hangs and hangs until the cgi times out. Is this
due to improper setup of the my.cnf file??
this is my.cnf:
# Example mysql config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# a important part and systems up to 128M very MySQL is used together with
# other programs (like a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/var) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = table_cache=64
set-variable = sort_buffer=512K
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=8M
log-bin
server-id = 1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=4M
#set-variable = bdb_max_lock=10000
# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:400M
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-nt.exe
host=localhost
user=root
password=
user=harryr
password=
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
