imp_scripts.sql

-- phpMyAdmin SQL Dump
-- version 4.4.13.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 05, 2015 at 05:52 PM
-- Server version: 5.5.44-MariaDB
-- PHP Version: 5.4.16

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `imp_scripts`
--

CREATE TABLE IF NOT EXISTS `imp_scripts` (
  `id` int(15) NOT NULL,
  `title` varchar(50) NOT NULL,
  `script` longtext NOT NULL,
  `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `imp_scripts`
--

INSERT INTO `imp_scripts` (`id`, `title`, `script`, `Time`) VALUES
(1, 'Groot Migration Script', '#!/bin/bash\r\n#Create By - Amrit Sharma\r\n#Purpose Migrating Groot\r\n#Date - 15-09-2015\r\ncd /var/tmp\r\nls\r\necho "Installing begins now..........................."\r\nyum update -y\r\necho "yum update done................................."\r\nyum install epel-release -y\r\necho "Epel Installed.................................."\r\nwget http://rpms.famillecollet.com/enterprise/remi-release-7.rpm\r\nrpm -Uvh remi-release-7*.rpm\r\nyum install httpd php php-mysql php-devel gcc make git phpMyAdmin vsftpd subversion   zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gdbm-devel db4-devel libpcap-devel xz-devel -y\r\n\r\necho "All Necesarry Packages has been installed";\r\n###Install Phalcon \r\necho "Updating php and installing Phalcon..............."\r\nyum --enablerepo=remi,remi-php56 update -y\r\nyum --enablerepo=remi,remi-php56 install php-phalcon -y\r\necho "Starting Httpd Service"\r\nservice httpd start\r\nchkconfig httpd on\r\necho "Creating Virtual host ................."\r\necho "Please Enter Vhost FileName Like - sample.conf"\r\nread filename\r\necho "Please enter DocRoot Directory Like - /var/www/html"\r\nread path\r\nif [ ! -d "$path" ]; then\r\nmkdir $path\r\nfi\r\necho "Please enter ServerName Like - cvcrm.in"\r\nread servername\r\necho "Please enter log file Name Like - Groot"\r\nread log\r\nfile="<VirtualHost *:80>\\n\r\n	ServerName $servername \\n\r\n	DocumentRoot $path \\n\r\n	ErrorLog logs/$log-error_log \\n\r\n	CustomLog logs/$log-access_log common \\n\r\n	DirectoryIndex index.html index.htm index.php index.php4 index.php5 \\n\r\n	<Directory $path> \\n\r\n	Options -Indexes +IncludesNOEXEC +SymLinksIfOwnerMatch +ExecCGI \\n\r\n	allow from all \\n\r\n	AllowOverride All Options=ExecCGI,Includes,IncludesNOEXEC,Indexes,MultiViews,SymLinksIfOwnerMatch \\n\r\n	</Directory> \\n\r\n	<Directory $path> \\n\r\n	allow from all \\n\r\n	AllowOverride All Options=ExecCGI,Includes,IncludesNOEXEC,Indexes,MultiViews,SymLinksIfOwnerMatch \\n\r\n	</Directory>\\n\r\n	</VirtualHost>"\r\necho -e $file > /etc/httpd/conf.d/$filename\r\n\r\necho "Check phpinfo file using browser for Phalcon confirmation"\r\necho "<?php phpinfo(); ?>" > $path/phpinfo.php\r\n\r\necho "Taking checkout from svn"\r\ncd $path\r\nsvn checkout http://10.222.1.124/svn/Groot/branch  .\r\n\r\n\r\necho "Download Old Groot Files"\r\nif [ ! -d "/root/olddata" ]; then\r\nmkdir /root/olddata\r\nfi\r\ncd /root/olddata\r\nwget http://10.222.1.12/Old_groot.tgz \r\n\r\n	if [  $? -ne 0 ] \r\n	then\r\n	echo "Old_groot.tgz Not Found"\r\n	exit 0\r\n	else	\r\n	echo "Moving old configurations"\r\n	cd /root/olddata\r\n	tar -zxvf Old_groot.tgz\r\n	yes | cp -rf public/upload/* $path/public/upload\r\n	yes | cp -rf  require/* $path/require/\r\n	chmod +x $path/scripts/cron_scripts/log_rotate.sh\r\n	fi\r\nchown apache:apache -R /var/www/html\r\n\r\necho ""\r\necho "Now Download and Install Beanstalkd"\r\ncd /var/tmp/\r\nwget http://cbs.centos.org/kojifiles/packages/beanstalkd/1.9/3.el7/x86_64/beanstalkd-1.9-3.el7.x86_64.rpm\r\nrpm -ivh beanstalkd-1.9-3.el7.x86_64.rpm\r\ndata="[Unit]\\n\r\nDescription=Beanstalkd is a simple, fast work queue\\n\\n\r\n\r\n[Service]\\n\r\nUser=root\\n\r\nExecStart=/usr/bin/beanstalkd -b /var/lib/beanstalkd\\n\\n\r\n\r\n[Install]\\n\r\nWantedBy=multi-user.target"\r\necho -e $data > /etc/systemd/system/beanstalkd.service\r\nsystemctl enable beanstalkd\r\nsystemctl start beanstalkd\r\necho ""\r\necho "Checking Beanstalkd Process"\r\nps ax | grep beanstalkd\r\nsleep 2\r\necho "Now we are going to install Sphinx."\r\ncd /var/tmp/\r\nwget http://sphinxsearch.com/files/sphinx-2.2.10-1.rhel7.x86_64.rpm\r\nyum install sphinx-2.2.10-1.rhel7.x86_64.rpm -y\r\nmkdir /var/lib/sphinx/index_groot/\r\ncd /var/lib/sphinx/index_groot/\r\nmkdir sphinx_accounts  sphinx_approvalrules sphinx_calender  sphinx_callbacktimemanager sphinx_campaigns sphinx_contacts sphinx_freetrails sphinx_leads  sphinx_processes sphinx_receipts sphinx_receiptsapproval sphinx_salescart sphinx_serviceagreement sphinx_serviceagreement1 sphinx_services\r\nservice searchd start\r\nchkconfig searchd on\r\n\r\necho "Change Local Time to IST"\r\nsleep 3\r\nrm -rf /etc/localtime\r\nln -sf /usr/share/zoneinfo/Asia/Kolkata /etc/localtime\r\necho "Now install python3.3"\r\nwget http://www.python.org/ftp/python/3.3.6/Python-3.3.6.tgz -O /var/tmp/Python-3.3.6.tgz\r\ncd /var/tmp/\r\ntar -zxvf Python-3.3.6.tgz\r\ncd Python-3.3.6/\r\n./configure\r\nsed -i'''' ''357 s/#//'' Modules/Setup\r\n#mv Modules/Setup  Modules/zlib\r\n./configure\r\nmake\r\nmake install\r\n\r\necho "Install pip3.3"\r\nsleep 3\r\ncurl -O https://raw.githubusercontent.com/pypa/pip/master/contrib/get-pip.py\r\npython3.3 get-pip.py\r\npip3.3 install pymysql \r\npip3.3 install phonenumbers \r\npip3.3 install requests\r\n#pip install supervisor\r\n\r\n\r\necho "Opening Ports";\r\nsleep 3\r\nfirewall-cmd --zone=public --add-port=80/tcp --permanent\r\nfirewall-cmd --zone=public --add-port=21/tcp --permanent\r\nfirewall-cmd --reload\r\n\r\n\r\necho "Change Php.ini Settings"\r\nsed -i ''s/post_max_size = 8M/post_max_size = 100M/'' /etc/php.ini\r\nsed -i ''s/upload_max_filesize = 2M/upload_max_filesize = 100M/'' /etc/php.ini\r\n\r\necho "disabling SELinux"\r\nsed -i s/SELINUX=enforcing/SELINUX=disabled/g /etc/sysconfig/selinux\r\n\r\nchmod 755 -R $path\r\nchown apache:apache -R $path\r\necho "Thank You ! I am done";', '2015-11-05 08:48:22');
INSERT INTO `imp_scripts` (`id`, `title`, `script`, `Time`) VALUES
(2, 'Nagios Mysql_health Plugin', '#! /usr/bin/perl -w\r\n# nagios: -epn\r\n\r\nmy %ERRORS=( OK => 0, WARNING => 1, CRITICAL => 2, UNKNOWN => 3 );\r\nmy %ERRORCODES=( 0 => ''OK'', 1 => ''WARNING'', 2 => ''CRITICAL'', 3 => ''UNKNOWN'' );\r\npackage DBD::MySQL::Server::Instance::Innodb;\r\n\r\nuse strict;\r\n\r\nour @ISA = qw(DBD::MySQL::Server::Instance);\r\n\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  my $self = {\r\n    handle => $params{handle},\r\n    internals => undef,\r\n    warningrange => $params{warningrange},\r\n    criticalrange => $params{criticalrange},\r\n  };\r\n  bless $self, $class;\r\n  $self->init(%params);\r\n  return $self;\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  $self->init_nagios();\r\n  if ($params{mode} =~ /server::instance::innodb/) {\r\n    $self->{internals} =\r\n        DBD::MySQL::Server::Instance::Innodb::Internals->new(%params);\r\n  }\r\n}\r\n\r\nsub nagios {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  if ($params{mode} =~ /server::instance::innodb/) {\r\n    $self->{internals}->nagios(%params);\r\n    $self->merge_nagios($self->{internals});\r\n  }\r\n}\r\n\r\n\r\npackage DBD::MySQL::Server::Instance::Innodb::Internals;\r\n\r\nuse strict;\r\n\r\nour @ISA = qw(DBD::MySQL::Server::Instance::Innodb);\r\n\r\nour $internals; # singleton, nur ein einziges mal instantiierbar\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  unless ($internals) {\r\n    $internals = {\r\n      handle => $params{handle},\r\n      bufferpool_hitrate => undef,\r\n      wait_free => undef,\r\n      log_waits => undef,\r\n      have_innodb => undef,\r\n      warningrange => $params{warningrange},\r\n      criticalrange => $params{criticalrange},\r\n    };\r\n    bless($internals, $class);\r\n    $internals->init(%params);\r\n  }\r\n  return($internals);\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $dummy;\r\n  $self->debug("enter init");\r\n  $self->init_nagios();\r\n  if (DBD::MySQL::Server::return_first_server()->version_is_minimum("5.1")) {\r\n    ($dummy, $self->{have_innodb}) = $self->{handle}->fetchrow_array(q{\r\n        SELECT ENGINE, SUPPORT FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE=''InnoDB''\r\n    });\r\n  } else { \r\n    ($dummy, $self->{have_innodb}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW VARIABLES LIKE ''have_innodb''\r\n    });\r\n  }\r\n  if ($self->{have_innodb} eq "NO") {\r\n    $self->add_nagios_critical("the innodb engine has a problem (have_innodb=no)");\r\n  } elsif ($self->{have_innodb} eq "DISABLED") {\r\n    # add_nagios_ok later\r\n  } elsif ($params{mode} =~ /server::instance::innodb::bufferpool::hitrate/) {\r\n    ($dummy, $self->{bufferpool_reads}) \r\n        = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Innodb_buffer_pool_reads''\r\n    });\r\n    ($dummy, $self->{bufferpool_read_requests}) \r\n        = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Innodb_buffer_pool_read_requests''\r\n    });\r\n    if (! defined $self->{bufferpool_reads}) {\r\n      $self->add_nagios_critical("no innodb buffer pool info available");\r\n    } else {\r\n      $self->valdiff(\\%params, qw(bufferpool_reads\r\n          bufferpool_read_requests));\r\n      $self->{bufferpool_hitrate_now} =\r\n          $self->{delta_bufferpool_read_requests} > 0 ?\r\n          100 - (100 * $self->{delta_bufferpool_reads} / \r\n              $self->{delta_bufferpool_read_requests}) : 100;\r\n      $self->{bufferpool_hitrate} =\r\n          $self->{bufferpool_read_requests} > 0 ?\r\n          100 - (100 * $self->{bufferpool_reads} /\r\n              $self->{bufferpool_read_requests}) : 100;\r\n    }\r\n  } elsif ($params{mode} =~ /server::instance::innodb::bufferpool::waitfree/) {\r\n    ($dummy, $self->{bufferpool_wait_free})\r\n        = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Innodb_buffer_pool_wait_free''\r\n    });\r\n    if (! defined $self->{bufferpool_wait_free}) {\r\n      $self->add_nagios_critical("no innodb buffer pool info available");\r\n    } else {\r\n      $self->valdiff(\\%params, qw(bufferpool_wait_free));\r\n      $self->{bufferpool_wait_free_rate} =\r\n          $self->{delta_bufferpool_wait_free} / $self->{delta_timestamp};\r\n    }\r\n  } elsif ($params{mode} =~ /server::instance::innodb::logwaits/) {\r\n    ($dummy, $self->{log_waits})\r\n        = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Innodb_log_waits''\r\n    });\r\n    if (! defined $self->{log_waits}) {\r\n      $self->add_nagios_critical("no innodb log info available");\r\n    } else {\r\n      $self->valdiff(\\%params, qw(log_waits));\r\n      $self->{log_waits_rate} =\r\n          $self->{delta_log_waits} / $self->{delta_timestamp};\r\n    }\r\n  } elsif ($params{mode} =~ /server::instance::innodb::needoptimize/) {\r\n#fragmentation=$(($datafree * 100 / $datalength))\r\n\r\n#http://www.electrictoolbox.com/optimize-tables-mysql-php/\r\n    my  @result = $self->{handle}->fetchall_array(q{\r\nSHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400\r\n});\r\nprintf "%s\\n", Data::Dumper::Dumper(\\@result);\r\n\r\n  }\r\n}\r\n\r\nsub nagios {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $now = $params{lookback} ? ''_now'' : '''';\r\n  if ($self->{have_innodb} eq "DISABLED") {\r\n    $self->add_nagios_ok("the innodb engine has been disabled");\r\n  } elsif (! $self->{nagios_level}) {\r\n    if ($params{mode} =~ /server::instance::innodb::bufferpool::hitrate/) {\r\n      my $refkey = ''bufferpool_hitrate''.($params{lookback} ? ''_now'' : '''');\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{$refkey}, "99:", "95:"),\r\n              sprintf "innodb buffer pool hitrate at %.2f%%", $self->{$refkey});\r\n      $self->add_perfdata(sprintf "bufferpool_hitrate=%.2f%%;%s;%s;0;100",\r\n          $self->{bufferpool_hitrate},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n      $self->add_perfdata(sprintf "bufferpool_hitrate_now=%.2f%%",\r\n          $self->{bufferpool_hitrate_now});\r\n    } elsif ($params{mode} =~ /server::instance::innodb::bufferpool::waitfree/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{bufferpool_wait_free_rate}, "1", "10"),\r\n          sprintf "%ld innodb buffer pool waits in %ld seconds (%.4f/sec)",\r\n          $self->{delta_bufferpool_wait_free}, $self->{delta_timestamp},\r\n          $self->{bufferpool_wait_free_rate});\r\n      $self->add_perfdata(sprintf "bufferpool_free_waits_rate=%.4f;%s;%s;0;100",\r\n          $self->{bufferpool_wait_free_rate},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::innodb::logwaits/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{log_waits_rate}, "1", "10"),\r\n          sprintf "%ld innodb log waits in %ld seconds (%.4f/sec)",\r\n          $self->{delta_log_waits}, $self->{delta_timestamp},\r\n          $self->{log_waits_rate});\r\n      $self->add_perfdata(sprintf "innodb_log_waits_rate=%.4f;%s;%s;0;100",\r\n          $self->{log_waits_rate},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    }\r\n  }\r\n}\r\n\r\n\r\n\r\n\r\npackage DBD::MySQL::Server::Instance::MyISAM;\r\n\r\nuse strict;\r\n\r\nour @ISA = qw(DBD::MySQL::Server::Instance);\r\n\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  my $self = {\r\n    handle => $params{handle},\r\n    internals => undef,\r\n    warningrange => $params{warningrange},\r\n    criticalrange => $params{criticalrange},\r\n  };\r\n  bless $self, $class;\r\n  $self->init(%params);\r\n  return $self;\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  $self->init_nagios();\r\n  if ($params{mode} =~ /server::instance::myisam/) {\r\n    $self->{internals} =\r\n        DBD::MySQL::Server::Instance::MyISAM::Internals->new(%params);\r\n  }\r\n}\r\n\r\nsub nagios {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  if ($params{mode} =~ /server::instance::myisam/) {\r\n    $self->{internals}->nagios(%params);\r\n    $self->merge_nagios($self->{internals});\r\n  }\r\n}\r\n\r\n\r\npackage DBD::MySQL::Server::Instance::MyISAM::Internals;\r\n\r\nuse strict;\r\n\r\nour @ISA = qw(DBD::MySQL::Server::Instance::MyISAM);\r\n\r\nour $internals; # singleton, nur ein einziges mal instantiierbar\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  unless ($internals) {\r\n    $internals = {\r\n      handle => $params{handle},\r\n      keycache_hitrate => undef,\r\n      warningrange => $params{warningrange},\r\n      criticalrange => $params{criticalrange},\r\n    };\r\n    bless($internals, $class);\r\n    $internals->init(%params);\r\n  }\r\n  return($internals);\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $dummy;\r\n  $self->debug("enter init");\r\n  $self->init_nagios();\r\n  if ($params{mode} =~ /server::instance::myisam::keycache::hitrate/) {\r\n    ($dummy, $self->{key_reads})\r\n        = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Key_reads''\r\n    });\r\n    ($dummy, $self->{key_read_requests})\r\n        = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Key_read_requests''\r\n    });\r\n    if (! defined $self->{key_read_requests}) {\r\n      $self->add_nagios_critical("no myisam keycache info available");\r\n    } else {\r\n      $self->valdiff(\\%params, qw(key_reads key_read_requests));\r\n      $self->{keycache_hitrate} =\r\n          $self->{key_read_requests} > 0 ?\r\n          100 - (100 * $self->{key_reads} /\r\n              $self->{key_read_requests}) : 100;\r\n      $self->{keycache_hitrate_now} =\r\n          $self->{delta_key_read_requests} > 0 ?\r\n          100 - (100 * $self->{delta_key_reads} /\r\n              $self->{delta_key_read_requests}) : 100;\r\n    }\r\n  } elsif ($params{mode} =~ /server::instance::myisam::sonstnochwas/) {\r\n  }\r\n}\r\n\r\nsub nagios {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  if (! $self->{nagios_level}) {\r\n    if ($params{mode} =~ /server::instance::myisam::keycache::hitrate/) {\r\n      my $refkey = ''keycache_hitrate''.($params{lookback} ? ''_now'' : '''');\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{$refkey}, "99:", "95:"),\r\n              sprintf "myisam keycache hitrate at %.2f%%", $self->{$refkey});\r\n      $self->add_perfdata(sprintf "keycache_hitrate=%.2f%%;%s;%s",\r\n          $self->{keycache_hitrate},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n      $self->add_perfdata(sprintf "keycache_hitrate_now=%.2f%%;%s;%s",\r\n          $self->{keycache_hitrate_now},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    }\r\n  }\r\n}\r\n\r\n\r\npackage DBD::MySQL::Server::Instance::Replication;\r\n\r\nuse strict;\r\n\r\nour @ISA = qw(DBD::MySQL::Server::Instance);\r\n\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  my $self = {\r\n    handle => $params{handle},\r\n    internals => undef,\r\n    warningrange => $params{warningrange},\r\n    criticalrange => $params{criticalrange},\r\n  };\r\n  bless $self, $class;\r\n  $self->init(%params);\r\n  return $self;\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  $self->init_nagios();\r\n  if ($params{mode} =~ /server::instance::replication/) {\r\n    $self->{internals} =\r\n        DBD::MySQL::Server::Instance::Replication::Internals->new(%params);\r\n  }\r\n}\r\n\r\nsub nagios {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  if ($params{mode} =~ /server::instance::replication/) {\r\n    $self->{internals}->nagios(%params);\r\n    $self->merge_nagios($self->{internals});\r\n  }\r\n}\r\n\r\n\r\npackage DBD::MySQL::Server::Instance::Replication::Internals;\r\n\r\nuse strict;\r\n\r\nour @ISA = qw(DBD::MySQL::Server::Instance::Replication);\r\n\r\nour $internals; # singleton, nur ein einziges mal instantiierbar\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  unless ($internals) {\r\n    $internals = {\r\n      handle => $params{handle},\r\n      seconds_behind_master => undef,\r\n      slave_io_running => undef,\r\n      slave_sql_running => undef,\r\n      warningrange => $params{warningrange},\r\n      criticalrange => $params{criticalrange},\r\n    };\r\n    bless($internals, $class);\r\n    $internals->init(%params);\r\n  }\r\n  return($internals);\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  $self->debug("enter init");\r\n  $self->init_nagios();\r\n  if ($params{mode} =~ /server::instance::replication::slavelag/) {\r\n    # "show slave status", "Seconds_Behind_Master"\r\n    my $slavehash = $self->{handle}->selectrow_hashref(q{\r\n            SHOW SLAVE STATUS\r\n        });\r\n    if ((! defined $slavehash->{Seconds_Behind_Master}) && \r\n        (lc $slavehash->{Slave_IO_Running} eq ''no'')) {\r\n      $self->add_nagios_critical(\r\n          "unable to get slave lag, because io thread is not running");\r\n    } elsif (! defined $slavehash->{Seconds_Behind_Master}) {\r\n      $self->add_nagios_critical(sprintf "unable to get replication info%s",\r\n          $self->{handle}->{errstr} ? $self->{handle}->{errstr} : "");\r\n    } else {\r\n      $self->{seconds_behind_master} = $slavehash->{Seconds_Behind_Master};\r\n    }\r\n  } elsif ($params{mode} =~ /server::instance::replication::slaveiorunning/) {\r\n    # "show slave status", "Slave_IO_Running"\r\n    my $slavehash = $self->{handle}->selectrow_hashref(q{\r\n            SHOW SLAVE STATUS\r\n        });\r\n    if (! defined $slavehash->{Slave_IO_Running}) {\r\n      $self->add_nagios_critical(sprintf "unable to get replication info%s",\r\n          $self->{handle}->{errstr} ? $self->{handle}->{errstr} : "");\r\n    } else {\r\n      $self->{slave_io_running} = $slavehash->{Slave_IO_Running};\r\n    }\r\n  } elsif ($params{mode} =~ /server::instance::replication::slavesqlrunning/) {\r\n    # "show slave status", "Slave_SQL_Running"\r\n    my $slavehash = $self->{handle}->selectrow_hashref(q{\r\n            SHOW SLAVE STATUS\r\n        });\r\n    if (! defined $slavehash->{Slave_SQL_Running}) {\r\n      $self->add_nagios_critical(sprintf "unable to get replication info%s",\r\n          $self->{handle}->{errstr} ? $self->{handle}->{errstr} : "");\r\n    } else {\r\n      $self->{slave_sql_running} = $slavehash->{Slave_SQL_Running};\r\n    }\r\n  }\r\n}\r\n\r\nsub nagios {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  if (! $self->{nagios_level}) {\r\n    if ($params{mode} =~ /server::instance::replication::slavelag/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{seconds_behind_master}, "10", "20"),\r\n          sprintf "Slave is %d seconds behind master",\r\n          $self->{seconds_behind_master});\r\n      $self->add_perfdata(sprintf "slave_lag=%d;%s;%s",\r\n          $self->{seconds_behind_master},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::replication::slaveiorunning/) {\r\n      if (lc $self->{slave_io_running} eq "yes") {\r\n        $self->add_nagios_ok("Slave io is running");\r\n      } else {\r\n        $self->add_nagios_critical("Slave io is not running");\r\n      }\r\n    } elsif ($params{mode} =~ /server::instance::replication::slavesqlrunning/) {\r\n      if (lc $self->{slave_sql_running} eq "yes") {\r\n        $self->add_nagios_ok("Slave sql is running");\r\n      } else {\r\n        $self->add_nagios_critical("Slave sql is not running");\r\n      }\r\n    }\r\n  }\r\n}\r\n\r\n\r\n\r\npackage DBD::MySQL::Server::Instance;\r\n\r\nuse strict;\r\n\r\nour @ISA = qw(DBD::MySQL::Server);\r\n\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  my $self = {\r\n    handle => $params{handle},\r\n    uptime => $params{uptime},\r\n    replication_user => $params{replication_user},\r\n    warningrange => $params{warningrange},\r\n    criticalrange => $params{criticalrange},\r\n    threads_connected => undef,\r\n    threads_created => undef,\r\n    connections => undef,\r\n    threadcache_hitrate => undef,\r\n    querycache_hitrate => undef,\r\n    lowmem_prunes_per_sec => undef,\r\n    slow_queries_per_sec => undef,\r\n    longrunners => undef,\r\n    tablecache_hitrate => undef,\r\n    index_usage => undef,\r\n    engine_innodb => undef,\r\n    engine_myisam => undef,\r\n    replication => undef,\r\n  };\r\n  bless $self, $class;\r\n  $self->init(%params);\r\n  return $self;\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $dummy;\r\n  $self->init_nagios();\r\n  if ($params{mode} =~ /server::instance::connectedthreads/) {\r\n    ($dummy, $self->{threads_connected}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Threads_connected''\r\n    });\r\n  } elsif ($params{mode} =~ /server::instance::createdthreads/) {\r\n    ($dummy, $self->{threads_created}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Threads_created''\r\n    });\r\n    $self->valdiff(\\%params, qw(threads_created));\r\n    $self->{threads_created_per_sec} = $self->{delta_threads_created} /\r\n        $self->{delta_timestamp};\r\n  } elsif ($params{mode} =~ /server::instance::runningthreads/) {\r\n    ($dummy, $self->{threads_running}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Threads_running''\r\n    });\r\n  } elsif ($params{mode} =~ /server::instance::cachedthreads/) {\r\n    ($dummy, $self->{threads_cached}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Threads_cached''\r\n    });\r\n  } elsif ($params{mode} =~ /server::instance::abortedconnects/) {\r\n    ($dummy, $self->{connects_aborted}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Aborted_connects''\r\n    });\r\n    $self->valdiff(\\%params, qw(connects_aborted));\r\n    $self->{connects_aborted_per_sec} = $self->{delta_connects_aborted} /\r\n        $self->{delta_timestamp};\r\n  } elsif ($params{mode} =~ /server::instance::abortedclients/) {\r\n    ($dummy, $self->{clients_aborted}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Aborted_clients''\r\n    });\r\n    $self->valdiff(\\%params, qw(clients_aborted));\r\n    $self->{clients_aborted_per_sec} = $self->{delta_clients_aborted} /\r\n        $self->{delta_timestamp};\r\n  } elsif ($params{mode} =~ /server::instance::threadcachehitrate/) {\r\n    ($dummy, $self->{threads_created}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Threads_created''\r\n    });\r\n    ($dummy, $self->{connections}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Connections''\r\n    });\r\n    $self->valdiff(\\%params, qw(threads_created connections));\r\n    if ($self->{delta_connections} > 0) {\r\n      $self->{threadcache_hitrate_now} =\r\n          100 - ($self->{delta_threads_created} * 100.0 /\r\n          $self->{delta_connections});\r\n    } else {\r\n      $self->{threadcache_hitrate_now} = 100;\r\n    }\r\n    $self->{threadcache_hitrate} = 100 -\r\n        ($self->{threads_created} * 100.0 / $self->{connections});\r\n    $self->{connections_per_sec} = $self->{delta_connections} /\r\n        $self->{delta_timestamp};\r\n  } elsif ($params{mode} =~ /server::instance::querycachehitrate/) {\r\n    ($dummy, $self->{qcache_inserts}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Qcache_inserts''\r\n    });\r\n    ($dummy, $self->{qcache_not_cached}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Qcache_not_cached''\r\n    });\r\n    ($dummy, $self->{com_select}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Com_select''\r\n    });\r\n    ($dummy, $self->{qcache_hits}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Qcache_hits''\r\n    });\r\n    #    SHOW VARIABLES WHERE Variable_name = ''have_query_cache'' for 5.x, but LIKE is compatible\r\n    ($dummy, $self->{have_query_cache}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW VARIABLES LIKE ''have_query_cache''\r\n    });\r\n    #    SHOW VARIABLES WHERE Variable_name = ''query_cache_size''\r\n    ($dummy, $self->{query_cache_size}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW VARIABLES LIKE ''query_cache_size''\r\n    });\r\n    $self->valdiff(\\%params, qw(com_select qcache_hits));\r\n    $self->{querycache_hitrate_now} =\r\n        ($self->{delta_com_select} + $self->{delta_qcache_hits}) > 0 ?\r\n        100 * $self->{delta_qcache_hits} /\r\n            ($self->{delta_com_select} + $self->{delta_qcache_hits}) :\r\n        0;\r\n    $self->{querycache_hitrate} =\r\n        ($self->{qcache_not_cached} + $self->{qcache_inserts} + $self->{qcache_hits}) > 0 ?\r\n        100 * $self->{qcache_hits} /\r\n            ($self->{qcache_not_cached} + $self->{qcache_inserts} + $self->{qcache_hits}) :\r\n        0;\r\n    $self->{selects_per_sec} =\r\n        $self->{delta_com_select} / $self->{delta_timestamp};\r\n  } elsif ($params{mode} =~ /server::instance::querycachelowmemprunes/) {\r\n    ($dummy, $self->{lowmem_prunes}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Qcache_lowmem_prunes''\r\n    });\r\n    $self->valdiff(\\%params, qw(lowmem_prunes));\r\n    $self->{lowmem_prunes_per_sec} = $self->{delta_lowmem_prunes} /\r\n        $self->{delta_timestamp};\r\n  } elsif ($params{mode} =~ /server::instance::slowqueries/) {\r\n    ($dummy, $self->{slow_queries}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Slow_queries''\r\n    });\r\n    $self->valdiff(\\%params, qw(slow_queries));\r\n    $self->{slow_queries_per_sec} = $self->{delta_slow_queries} /\r\n        $self->{delta_timestamp};\r\n  } elsif ($params{mode} =~ /server::instance::longprocs/) {\r\n    if (DBD::MySQL::Server::return_first_server()->version_is_minimum("5.1")) {\r\n      ($self->{longrunners}) = $self->{handle}->fetchrow_array(qq(\r\n          SELECT\r\n              COUNT(*)\r\n          FROM\r\n              information_schema.processlist\r\n          WHERE user <> ?\r\n          AND id <> CONNECTION_ID() \r\n          AND time > 60 \r\n          AND command <> ''Sleep''\r\n      ), $self->{replication_user});\r\n    } else {\r\n      $self->{longrunners} = 0 if ! defined $self->{longrunners};\r\n      foreach ($self->{handle}->fetchall_array(q{\r\n          SHOW PROCESSLIST\r\n      })) {\r\n        my($id, $user, $host, $db, $command, $tme, $state, $info) = @{$_};\r\n        if (($user ne $self->{replication_user}) &&\r\n            ($tme > 60) &&\r\n            ($command ne ''Sleep'')) {\r\n          $self->{longrunners}++;\r\n        }\r\n      }\r\n    }\r\n  } elsif ($params{mode} =~ /server::instance::tablecachehitrate/) {\r\n    ($dummy, $self->{open_tables}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Open_tables''\r\n    });\r\n    ($dummy, $self->{opened_tables}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Opened_tables''\r\n    });\r\n    if (DBD::MySQL::Server::return_first_server()->version_is_minimum("5.1.3")) {\r\n      #      SHOW VARIABLES WHERE Variable_name = ''table_open_cache''\r\n      ($dummy, $self->{table_cache}) = $self->{handle}->fetchrow_array(q{\r\n          SHOW VARIABLES LIKE ''table_open_cache''\r\n      });\r\n    } else {\r\n      #    SHOW VARIABLES WHERE Variable_name = ''table_cache''\r\n      ($dummy, $self->{table_cache}) = $self->{handle}->fetchrow_array(q{\r\n          SHOW VARIABLES LIKE ''table_cache''\r\n      });\r\n    }\r\n    $self->{table_cache} ||= 0;\r\n    #$self->valdiff(\\%params, qw(open_tables opened_tables table_cache));\r\n    # _now ist hier sinnlos, da opened_tables waechst, aber open_tables wieder\r\n    # schrumpfen kann weil tabellen geschlossen werden.\r\n    if ($self->{opened_tables} != 0 && $self->{table_cache} != 0) {\r\n      $self->{tablecache_hitrate} =\r\n          100 * $self->{open_tables} / $self->{opened_tables};\r\n      $self->{tablecache_fillrate} =\r\n          100 * $self->{open_tables} / $self->{table_cache};\r\n    } elsif ($self->{opened_tables} == 0 && $self->{table_cache} != 0) {\r\n      $self->{tablecache_hitrate} = 100;\r\n      $self->{tablecache_fillrate} =\r\n          100 * $self->{open_tables} / $self->{table_cache};\r\n    } else {\r\n      $self->{tablecache_hitrate} = 0;\r\n      $self->{tablecache_fillrate} = 0;\r\n      $self->add_nagios_critical("no table cache");\r\n    }\r\n  } elsif ($params{mode} =~ /server::instance::tablelockcontention/) {\r\n    ($dummy, $self->{table_locks_waited}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Table_locks_waited''\r\n    });\r\n    ($dummy, $self->{table_locks_immediate}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Table_locks_immediate''\r\n    });\r\n    $self->valdiff(\\%params, qw(table_locks_waited table_locks_immediate));\r\n    $self->{table_lock_contention} =\r\n        ($self->{table_locks_waited} + $self->{table_locks_immediate}) > 0 ?\r\n        100 * $self->{table_locks_waited} /\r\n        ($self->{table_locks_waited} + $self->{table_locks_immediate}) :\r\n        100;\r\n    $self->{table_lock_contention_now} =\r\n        ($self->{delta_table_locks_waited} + $self->{delta_table_locks_immediate}) > 0 ?\r\n        100 * $self->{delta_table_locks_waited} /\r\n        ($self->{delta_table_locks_waited} + $self->{delta_table_locks_immediate}) :\r\n        100;\r\n  } elsif ($params{mode} =~ /server::instance::tableindexusage/) {\r\n    # http://johnjacobm.wordpress.com/2007/06/\r\n    # formula for calculating the percentage of full table scans\r\n    ($dummy, $self->{handler_read_first}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Handler_read_first''\r\n    });\r\n    ($dummy, $self->{handler_read_key}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Handler_read_key''\r\n    });\r\n    ($dummy, $self->{handler_read_next}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Handler_read_next''\r\n    });\r\n    ($dummy, $self->{handler_read_prev}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Handler_read_prev''\r\n    });\r\n    ($dummy, $self->{handler_read_rnd}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Handler_read_rnd''\r\n    });\r\n    ($dummy, $self->{handler_read_rnd_next}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Handler_read_rnd_next''\r\n    });\r\n    $self->valdiff(\\%params, qw(handler_read_first handler_read_key\r\n        handler_read_next handler_read_prev handler_read_rnd\r\n        handler_read_rnd_next));\r\n    my $delta_reads = $self->{delta_handler_read_first} +\r\n        $self->{delta_handler_read_key} +\r\n        $self->{delta_handler_read_next} +\r\n        $self->{delta_handler_read_prev} +\r\n        $self->{delta_handler_read_rnd} +\r\n        $self->{delta_handler_read_rnd_next};\r\n    my $reads = $self->{handler_read_first} +\r\n        $self->{handler_read_key} +\r\n        $self->{handler_read_next} +\r\n        $self->{handler_read_prev} +\r\n        $self->{handler_read_rnd} +\r\n        $self->{handler_read_rnd_next};\r\n    $self->{index_usage_now} = ($delta_reads == 0) ? 0 :\r\n        100 - (100.0 * ($self->{delta_handler_read_rnd} +\r\n        $self->{delta_handler_read_rnd_next}) /\r\n        $delta_reads);\r\n    $self->{index_usage} = ($reads == 0) ? 0 :\r\n        100 - (100.0 * ($self->{handler_read_rnd} +\r\n        $self->{handler_read_rnd_next}) /\r\n        $reads);\r\n  } elsif ($params{mode} =~ /server::instance::tabletmpondisk/) {\r\n    ($dummy, $self->{created_tmp_tables}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Created_tmp_tables''\r\n    });\r\n    ($dummy, $self->{created_tmp_disk_tables}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Created_tmp_disk_tables''\r\n    });\r\n    $self->valdiff(\\%params, qw(created_tmp_tables created_tmp_disk_tables));\r\n    $self->{pct_tmp_on_disk} = $self->{created_tmp_tables} > 0 ?\r\n        100 * $self->{created_tmp_disk_tables} / $self->{created_tmp_tables} :\r\n        100;\r\n    $self->{pct_tmp_on_disk_now} = $self->{delta_created_tmp_tables} > 0 ?\r\n        100 * $self->{delta_created_tmp_disk_tables} / $self->{delta_created_tmp_tables} :\r\n        100;\r\n  } elsif ($params{mode} =~ /server::instance::openfiles/) {\r\n    ($dummy, $self->{open_files_limit}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW VARIABLES LIKE ''open_files_limit''\r\n    });\r\n    ($dummy, $self->{open_files}) = $self->{handle}->fetchrow_array(q{\r\n        SHOW /*!50000 global */ STATUS LIKE ''Open_files''\r\n    });\r\n    $self->{pct_open_files} = 100 * $self->{open_files} / $self->{open_files_limit};\r\n  } elsif ($params{mode} =~ /server::instance::needoptimize/) {\r\n    $self->{fragmented} = [];\r\n    #http://www.electrictoolbox.com/optimize-tables-mysql-php/\r\n    my  @result = $self->{handle}->fetchall_array(q{\r\n        SHOW TABLE STATUS\r\n    });\r\n    foreach (@result) {\r\n      my ($name, $engine, $data_length, $data_free) =\r\n          ($_->[0], $_->[1], $_->[6 ], $_->[9]);\r\n      next if ($params{name} && $params{name} ne $name);\r\n      my $fragmentation = $data_length ? $data_free * 100 / $data_length : 0;\r\n      push(@{$self->{fragmented}},\r\n          [$name, $fragmentation, $data_length, $data_free]);\r\n    }\r\n  } elsif ($params{mode} =~ /server::instance::myisam/) {\r\n    $self->{engine_myisam} = DBD::MySQL::Server::Instance::MyISAM->new(\r\n        %params\r\n    );\r\n  } elsif ($params{mode} =~ /server::instance::innodb/) {\r\n    $self->{engine_innodb} = DBD::MySQL::Server::Instance::Innodb->new(\r\n        %params\r\n    );\r\n  } elsif ($params{mode} =~ /server::instance::replication/) {\r\n    $self->{replication} = DBD::MySQL::Server::Instance::Replication->new(\r\n        %params\r\n    );\r\n  }\r\n}\r\n\r\nsub nagios {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  if (! $self->{nagios_level}) {\r\n    if ($params{mode} =~ /server::instance::connectedthreads/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{threads_connected}, 10, 20),\r\n          sprintf "%d client connection threads", $self->{threads_connected});\r\n      $self->add_perfdata(sprintf "threads_connected=%d;%d;%d",\r\n          $self->{threads_connected},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::createdthreads/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{threads_created_per_sec}, 10, 20),\r\n          sprintf "%.2f threads created/sec", $self->{threads_created_per_sec});\r\n      $self->add_perfdata(sprintf "threads_created_per_sec=%.2f;%.2f;%.2f",\r\n          $self->{threads_created_per_sec},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::runningthreads/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{threads_running}, 10, 20),\r\n          sprintf "%d running threads", $self->{threads_running});\r\n      $self->add_perfdata(sprintf "threads_running=%d;%d;%d",\r\n          $self->{threads_running},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::cachedthreads/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{threads_cached}, 10, 20),\r\n          sprintf "%d cached threads", $self->{threads_cached});\r\n      $self->add_perfdata(sprintf "threads_cached=%d;%d;%d",\r\n          $self->{threads_cached},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::abortedconnects/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{connects_aborted_per_sec}, 1, 5),\r\n          sprintf "%.2f aborted connections/sec", $self->{connects_aborted_per_sec});\r\n      $self->add_perfdata(sprintf "connects_aborted_per_sec=%.2f;%.2f;%.2f",\r\n          $self->{connects_aborted_per_sec},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::abortedclients/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{clients_aborted_per_sec}, 1, 5),\r\n          sprintf "%.2f aborted (client died) connections/sec", $self->{clients_aborted_per_sec});\r\n      $self->add_perfdata(sprintf "clients_aborted_per_sec=%.2f;%.2f;%.2f",\r\n          $self->{clients_aborted_per_sec},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::threadcachehitrate/) {\r\n      my $refkey = ''threadcache_hitrate''.($params{lookback} ? ''_now'' : '''');\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{$refkey}, "90:", "80:"),\r\n          sprintf "thread cache hitrate %.2f%%", $self->{$refkey});\r\n      $self->add_perfdata(sprintf "thread_cache_hitrate=%.2f%%;%s;%s",\r\n          $self->{threadcache_hitrate},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n      $self->add_perfdata(sprintf "thread_cache_hitrate_now=%.2f%%",\r\n          $self->{threadcache_hitrate_now});\r\n      $self->add_perfdata(sprintf "connections_per_sec=%.2f",\r\n          $self->{connections_per_sec});\r\n    } elsif ($params{mode} =~ /server::instance::querycachehitrate/) {\r\n      my $refkey = ''querycache_hitrate''.($params{lookback} ? ''_now'' : '''');\r\n      if ((lc $self->{have_query_cache} eq ''yes'') && ($self->{query_cache_size})) {\r\n        $self->add_nagios(\r\n            $self->check_thresholds($self->{$refkey}, "90:", "80:"),\r\n            sprintf "query cache hitrate %.2f%%", $self->{$refkey});\r\n      } else {\r\n        $self->check_thresholds($self->{$refkey}, "90:", "80:");\r\n        $self->add_nagios_ok(\r\n            sprintf "query cache hitrate %.2f%% (because it''s turned off)",\r\n            $self->{querycache_hitrate});\r\n      }\r\n      $self->add_perfdata(sprintf "qcache_hitrate=%.2f%%;%s;%s",\r\n          $self->{querycache_hitrate},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n      $self->add_perfdata(sprintf "qcache_hitrate_now=%.2f%%",\r\n          $self->{querycache_hitrate_now});\r\n      $self->add_perfdata(sprintf "selects_per_sec=%.2f",\r\n          $self->{selects_per_sec});\r\n    } elsif ($params{mode} =~ /server::instance::querycachelowmemprunes/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{lowmem_prunes_per_sec}, "1", "10"),\r\n          sprintf "%d query cache lowmem prunes in %d seconds (%.2f/sec)",\r\n          $self->{delta_lowmem_prunes}, $self->{delta_timestamp},\r\n          $self->{lowmem_prunes_per_sec});\r\n      $self->add_perfdata(sprintf "qcache_lowmem_prunes_rate=%.2f;%s;%s",\r\n          $self->{lowmem_prunes_per_sec},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::slowqueries/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{slow_queries_per_sec}, "0.1", "1"),\r\n          sprintf "%d slow queries in %d seconds (%.2f/sec)",\r\n          $self->{delta_slow_queries}, $self->{delta_timestamp},\r\n          $self->{slow_queries_per_sec});\r\n      $self->add_perfdata(sprintf "slow_queries_rate=%.2f%%;%s;%s",\r\n          $self->{slow_queries_per_sec},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::longprocs/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{longrunners}, 10, 20),\r\n          sprintf "%d long running processes", $self->{longrunners});\r\n      $self->add_perfdata(sprintf "long_running_procs=%d;%d;%d",\r\n          $self->{longrunners},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /server::instance::tablecachehitrate/) {\r\n      if ($self->{tablecache_fillrate} < 95) {\r\n        $self->add_nagios_ok(\r\n            sprintf "table cache hitrate %.2f%%, %.2f%% filled",\r\n                $self->{tablecache_hitrate},\r\n                $self->{tablecache_fillrate});\r\n        $self->check_thresholds($self->{tablecache_hitrate}, "99:", "95:");\r\n      } else {\r\n        $self->add_nagios(\r\n            $self->check_thresholds($self->{tablecache_hitrate}, "99:", "95:"),\r\n            sprintf "table cache hitrate %.2f%%", $self->{tablecache_hitrate});\r\n      }\r\n      $self->add_perfdata(sprintf "tablecache_hitrate=%.2f%%;%s;%s",\r\n          $self->{tablecache_hitrate},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n      $self->add_perfdata(sprintf "tablecache_fillrate=%.2f%%",\r\n          $self->{tablecache_fillrate});\r\n    } elsif ($params{mode} =~ /server::instance::tablelockcontention/) {\r\n      my $refkey = ''table_lock_contention''.($params{lookback} ? ''_now'' : '''');\r\n      if ($self->{uptime} > 10800) { # MySQL Bug #30599\r\n        $self->add_nagios(\r\n            $self->check_thresholds($self->{$refkey}, "1", "2"),\r\n                sprintf "table lock contention %.2f%%", $self->{$refkey});\r\n      } else {\r\n        $self->check_thresholds($self->{$refkey}, "1", "2");\r\n        $self->add_nagios_ok(\r\n            sprintf "table lock contention %.2f%% (uptime < 10800)",\r\n            $self->{$refkey});\r\n      }\r\n      $self->add_perfdata(sprintf "tablelock_contention=%.2f%%;%s;%s",\r\n          $self->{table_lock_contention},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n      $self->add_perfdata(sprintf "tablelock_contention_now=%.2f%%",\r\n          $self->{table_lock_contention_now});\r\n    } elsif ($params{mode} =~ /server::instance::tableindexusage/) {\r\n      my $refkey = ''index_usage''.($params{lookback} ? ''_now'' : '''');\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{$refkey}, "90:", "80:"),\r\n              sprintf "index usage  %.2f%%", $self->{$refkey});\r\n      $self->add_perfdata(sprintf "index_usage=%.2f%%;%s;%s",\r\n          $self->{index_usage},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n      $self->add_perfdata(sprintf "index_usage_now=%.2f%%",\r\n          $self->{index_usage_now});\r\n    } elsif ($params{mode} =~ /server::instance::tabletmpondisk/) {\r\n      my $refkey = ''pct_tmp_on_disk''.($params{lookback} ? ''_now'' : '''');\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{$refkey}, "25", "50"),\r\n              sprintf "%.2f%% of %d tables were created on disk",\r\n              $self->{$refkey}, $self->{delta_created_tmp_tables});\r\n      $self->add_perfdata(sprintf "pct_tmp_table_on_disk=%.2f%%;%s;%s",\r\n          $self->{pct_tmp_on_disk},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n      $self->add_perfdata(sprintf "pct_tmp_table_on_disk_now=%.2f%%",\r\n          $self->{pct_tmp_on_disk_now});\r\n    } elsif ($params{mode} =~ /server::instance::openfiles/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{pct_open_files}, 80, 95),\r\n          sprintf "%.2f%% of the open files limit reached (%d of max. %d)",\r\n              $self->{pct_open_files},\r\n              $self->{open_files}, $self->{open_files_limit});\r\n      $self->add_perfdata(sprintf "pct_open_files=%.3f%%;%.3f;%.3f",\r\n          $self->{pct_open_files},\r\n          $self->{warningrange},\r\n          $self->{criticalrange});\r\n      $self->add_perfdata(sprintf "open_files=%d;%d;%d",\r\n          $self->{open_files},\r\n          $self->{open_files_limit} * $self->{warningrange} / 100,\r\n          $self->{open_files_limit} * $self->{criticalrange} / 100);\r\n    } elsif ($params{mode} =~ /server::instance::needoptimize/) {\r\n      foreach (@{$self->{fragmented}}) {\r\n        $self->add_nagios(\r\n            $self->check_thresholds($_->[1], 10, 25),\r\n            sprintf "table %s is %.2f%% fragmented", $_->[0], $_->[1]);\r\n        if ($params{name}) {\r\n          $self->add_perfdata(sprintf "''%s_frag''=%.2f%%;%d;%d",\r\n              $_->[0], $_->[1], $self->{warningrange}, $self->{criticalrange});\r\n        }\r\n      }\r\n    } elsif ($params{mode} =~ /server::instance::myisam/) {\r\n      $self->{engine_myisam}->nagios(%params);\r\n      $self->merge_nagios($self->{engine_myisam});\r\n    } elsif ($params{mode} =~ /server::instance::innodb/) {\r\n      $self->{engine_innodb}->nagios(%params);\r\n      $self->merge_nagios($self->{engine_innodb});\r\n    } elsif ($params{mode} =~ /server::instance::replication/) {\r\n      $self->{replication}->nagios(%params);\r\n      $self->merge_nagios($self->{replication});\r\n    }\r\n  }\r\n}\r\n\r\n\r\n\r\npackage DBD::MySQL::Server;\r\n\r\nuse strict;\r\nuse Time::HiRes;\r\nuse IO::File;\r\nuse File::Copy ''cp'';\r\nuse Data::Dumper;\r\n\r\n\r\n{\r\n  our $verbose = 0;\r\n  our $scream = 0; # scream if something is not implemented\r\n  our $access = "dbi"; # how do we access the database. \r\n  our $my_modules_dyn_dir = ""; # where we look for self-written extensions\r\n\r\n  my @servers = ();\r\n  my $initerrors = undef;\r\n\r\n  sub add_server {\r\n    push(@servers, shift);\r\n  }\r\n\r\n  sub return_servers {\r\n    return @servers;\r\n  }\r\n  \r\n  sub return_first_server() {\r\n    return $servers[0];\r\n  }\r\n\r\n}\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  my $self = {\r\n    mode => $params{mode},\r\n    access => $params{method} || ''dbi'',\r\n    hostname => $params{hostname},\r\n    database => $params{database} || ''information_schema'',\r\n    port => $params{port},\r\n    socket => $params{socket},\r\n    username => $params{username},\r\n    password => $params{password},\r\n    replication_user => $params{replication_user},\r\n    mycnf => $params{mycnf},\r\n    mycnfgroup => $params{mycnfgroup},\r\n    timeout => $params{timeout},\r\n    warningrange => $params{warningrange},\r\n    criticalrange => $params{criticalrange},\r\n    verbose => $params{verbose},\r\n    report => $params{report},\r\n    negate => $params{negate},\r\n    labelformat => $params{labelformat},\r\n    version => ''unknown'',\r\n    instance => undef,\r\n    handle => undef,\r\n  };\r\n  bless $self, $class;\r\n  $self->init_nagios();\r\n  if ($self->dbconnect(%params)) {\r\n    ($self->{dummy}, $self->{version}) = $self->{handle}->fetchrow_array(\r\n        #q{ SHOW VARIABLES WHERE Variable_name = ''version'' }\r\n        q{ SHOW VARIABLES LIKE ''version'' }\r\n    );\r\n    $self->{version} = (split "-", $self->{version})[0];\r\n    ($self->{dummy}, $self->{uptime}) = $self->{handle}->fetchrow_array(\r\n        q{ SHOW STATUS LIKE ''Uptime'' }\r\n    );\r\n    DBD::MySQL::Server::add_server($self);\r\n    $self->init(%params);\r\n  }\r\n  return $self;\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  $params{handle} = $self->{handle};\r\n  $params{uptime} = $self->{uptime};\r\n  $self->set_global_db_thresholds(\\%params);\r\n  if ($params{mode} =~ /^server::instance/) {\r\n    $self->{instance} = DBD::MySQL::Server::Instance->new(%params);\r\n  } elsif ($params{mode} =~ /^server::sql/) {\r\n    $self->set_local_db_thresholds(%params);\r\n    if ($params{regexp}) {\r\n      # sql output is treated as text\r\n      if ($params{name2} eq $params{name}) {\r\n        $self->add_nagios_unknown(sprintf "where''s the regexp????");\r\n      } else {\r\n        $self->{genericsql} =\r\n            $self->{handle}->fetchrow_array($params{selectname});\r\n        if (! defined $self->{genericsql}) {\r\n          $self->add_nagios_unknown(sprintf "got no valid response for %s",\r\n              $params{selectname});\r\n        }\r\n      }\r\n    } else {\r\n      # sql output must be a number (or array of numbers)\r\n      @{$self->{genericsql}} =\r\n          $self->{handle}->fetchrow_array($params{selectname});\r\n      if (! (defined $self->{genericsql} &&\r\n          (scalar(grep { /^[+-]?(?:\\d+(?:\\.\\d*)?|\\.\\d+)$/ } @{$self->{genericsql}})) == \r\n          scalar(@{$self->{genericsql}}))) {\r\n        $self->add_nagios_unknown(sprintf "got no valid response for %s",\r\n            $params{selectname});\r\n      } else {\r\n        # name2 in array\r\n        # units in array\r\n      }\r\n    }\r\n  } elsif ($params{mode} =~ /^server::uptime/) {\r\n    # already set with the connection. but use minutes here\r\n  } elsif ($params{mode} =~ /^server::connectiontime/) {\r\n    $self->{connection_time} = $self->{tac} - $self->{tic};\r\n  } elsif ($params{mode} =~ /^my::([^:.]+)/) {\r\n    my $class = $1;\r\n    my $loaderror = undef;\r\n    substr($class, 0, 1) = uc substr($class, 0, 1);\r\n    foreach my $libpath (split(":", $DBD::MySQL::Server::my_modules_dyn_dir)) {\r\n      foreach my $extmod (glob $libpath."/CheckMySQLHealth*.pm") {\r\n        eval {\r\n          $self->trace(sprintf "loading module %s", $extmod);\r\n          require $extmod;\r\n        };\r\n        if ($@) {\r\n          $loaderror = $extmod;\r\n          $self->trace(sprintf "failed loading module %s: %s", $extmod, $@);\r\n        }\r\n      }\r\n    }\r\n    my $obj = {\r\n        handle => $params{handle},\r\n        warningrange => $params{warningrange},\r\n        criticalrange => $params{criticalrange},\r\n    };\r\n    bless $obj, "My$class";\r\n    $self->{my} = $obj;\r\n    if ($self->{my}->isa("DBD::MySQL::Server")) {\r\n      my $dos_init = $self->can("init");\r\n      my $dos_nagios = $self->can("nagios");\r\n      my $my_init = $self->{my}->can("init");\r\n      my $my_nagios = $self->{my}->can("nagios");\r\n      if ($my_init == $dos_init) {\r\n          $self->add_nagios_unknown(\r\n              sprintf "Class %s needs an init() method", ref($self->{my}));\r\n      } elsif ($my_nagios == $dos_nagios) {\r\n          $self->add_nagios_unknown(\r\n              sprintf "Class %s needs a nagios() method", ref($self->{my}));\r\n      } else {\r\n        $self->{my}->init_nagios(%params);\r\n        $self->{my}->init(%params);\r\n      }\r\n    } else {\r\n      $self->add_nagios_unknown(\r\n          sprintf "Class %s is not a subclass of DBD::MySQL::Server%s", \r\n              ref($self->{my}),\r\n              $loaderror ? sprintf " (syntax error in %s?)", $loaderror : "" );\r\n    }\r\n  } else {\r\n    printf "broken mode %s\\n", $params{mode};\r\n  }\r\n}\r\n\r\nsub dump {\r\n  my $self = shift;\r\n  my $message = shift || "";\r\n  printf "%s %s\\n", $message, Data::Dumper::Dumper($self);\r\n}\r\n\r\nsub nagios {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  if (! $self->{nagios_level}) {\r\n    if ($params{mode} =~ /^server::instance/) {\r\n      $self->{instance}->nagios(%params);\r\n      $self->merge_nagios($self->{instance});\r\n    } elsif ($params{mode} =~ /^server::database/) {\r\n      $self->{database}->nagios(%params);\r\n      $self->merge_nagios($self->{database});\r\n    } elsif ($params{mode} =~ /^server::uptime/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{uptime} / 60, "10:", "5:"),\r\n          sprintf "database is up since %d minutes", $self->{uptime} / 60);\r\n      $self->add_perfdata(sprintf "uptime=%ds",\r\n          $self->{uptime});\r\n    } elsif ($params{mode} =~ /^server::connectiontime/) {\r\n      $self->add_nagios(\r\n          $self->check_thresholds($self->{connection_time}, 1, 5),\r\n          sprintf "%.2f seconds to connect as %s",\r\n              $self->{connection_time}, ($self->{username} || getpwuid($<)));\r\n      $self->add_perfdata(sprintf "connection_time=%.4fs;%d;%d",\r\n          $self->{connection_time},\r\n          $self->{warningrange}, $self->{criticalrange});\r\n    } elsif ($params{mode} =~ /^server::sql/) {\r\n      if ($params{regexp}) {\r\n        if (substr($params{name2}, 0, 1) eq ''!'') {\r\n          $params{name2} =~ s/^!//;\r\n          if ($self->{genericsql} !~ /$params{name2}/) {\r\n            $self->add_nagios_ok(\r\n                sprintf "output %s does not match pattern %s",\r\n                    $self->{genericsql}, $params{name2});\r\n          } else {\r\n            $self->add_nagios_critical(\r\n                sprintf "output %s matches pattern %s",\r\n                    $self->{genericsql}, $params{name2});\r\n          }\r\n        } else {\r\n          if ($self->{genericsql} =~ /$params{name2}/) {\r\n            $self->add_nagios_ok(\r\n                sprintf "output %s matches pattern %s",\r\n                    $self->{genericsql}, $params{name2});\r\n          } else {\r\n            $self->add_nagios_critical(\r\n                sprintf "output %s does not match pattern %s",\r\n                    $self->{genericsql}, $params{name2});\r\n          }\r\n        }\r\n      } else {\r\n        $self->add_nagios(\r\n            # the first item in the list will trigger the threshold values\r\n            $self->check_thresholds($self->{genericsql}[0], 1, 5),\r\n                sprintf "%s: %s%s",\r\n                $params{name2} ? lc $params{name2} : lc $params{selectname},\r\n                # float as float, integers as integers\r\n                join(" ", map {\r\n                    (sprintf("%d", $_) eq $_) ? $_ : sprintf("%f", $_)\r\n                } @{$self->{genericsql}}),\r\n                $params{units} ? $params{units} : "");\r\n        my $i = 0;\r\n        # workaround... getting the column names from the database would be nicer\r\n        my @names2_arr = split(/\\s+/, $params{name2});\r\n        foreach my $t (@{$self->{genericsql}}) {\r\n          $self->add_perfdata(sprintf "\\''%s\\''=%s%s;%s;%s",\r\n              $names2_arr[$i] ? lc $names2_arr[$i] : lc $params{selectname},\r\n              # float as float, integers as integers\r\n              (sprintf("%d", $t) eq $t) ? $t : sprintf("%f", $t),\r\n              $params{units} ? $params{units} : "",\r\n            ($i == 0) ? $self->{warningrange} : "",\r\n              ($i == 0) ? $self->{criticalrange} : ""\r\n          );\r\n          $i++;\r\n        }\r\n      }\r\n    } elsif ($params{mode} =~ /^my::([^:.]+)/) {\r\n      $self->{my}->nagios(%params);\r\n      $self->merge_nagios($self->{my});\r\n    }\r\n  }\r\n}\r\n\r\n\r\nsub init_nagios {\r\n  my $self = shift;\r\n  no strict ''refs'';\r\n  if (! ref($self)) {\r\n    my $nagiosvar = $self."::nagios";\r\n    my $nagioslevelvar = $self."::nagios_level";\r\n    $$nagiosvar = {\r\n      messages => {\r\n        0 => [],\r\n        1 => [],\r\n        2 => [],\r\n        3 => [],\r\n      },\r\n      perfdata => [],\r\n    };\r\n    $$nagioslevelvar = $ERRORS{OK},\r\n  } else {\r\n    $self->{nagios} = {\r\n      messages => {\r\n        0 => [],\r\n        1 => [],\r\n        2 => [],\r\n        3 => [],\r\n      },\r\n      perfdata => [],\r\n    };\r\n    $self->{nagios_level} = $ERRORS{OK},\r\n  }\r\n}\r\n\r\nsub check_thresholds {\r\n  my $self = shift;\r\n  my $value = shift;\r\n  my $defaultwarningrange = shift;\r\n  my $defaultcriticalrange = shift;\r\n  my $level = $ERRORS{OK};\r\n  $self->{warningrange} = defined $self->{warningrange} ?\r\n      $self->{warningrange} : $defaultwarningrange;\r\n  $self->{criticalrange} = defined $self->{criticalrange} ?\r\n      $self->{criticalrange} : $defaultcriticalrange;\r\n\r\n  if ($self->{warningrange} =~ /^([-+]?[0-9]*\\.?[0-9]+)$/) {\r\n    # warning = 10, warn if > 10 or < 0\r\n    $level = $ERRORS{WARNING}\r\n        if ($value > $1 || $value < 0);\r\n  } elsif ($self->{warningrange} =~ /^([-+]?[0-9]*\\.?[0-9]+):$/) {\r\n    # warning = 10:, warn if < 10\r\n    $level = $ERRORS{WARNING}\r\n        if ($value < $1);\r\n  } elsif ($self->{warningrange} =~ /^~:([-+]?[0-9]*\\.?[0-9]+)$/) {\r\n    # warning = ~:10, warn if > 10\r\n    $level = $ERRORS{WARNING}\r\n        if ($value > $1);\r\n  } elsif ($self->{warningrange} =~ /^([-+]?[0-9]*\\.?[0-9]+):([-+]?[0-9]*\\.?[0-9]+)$/) {\r\n    # warning = 10:20, warn if < 10 or > 20\r\n    $level = $ERRORS{WARNING}\r\n        if ($value < $1 || $value > $2);\r\n  } elsif ($self->{warningrange} =~ /^@([-+]?[0-9]*\\.?[0-9]+):([-+]?[0-9]*\\.?[0-9]+)$/) {\r\n    # warning = @10:20, warn if >= 10 and <= 20\r\n    $level = $ERRORS{WARNING}\r\n        if ($value >= $1 && $value <= $2);\r\n  }\r\n  if ($self->{criticalrange} =~ /^([-+]?[0-9]*\\.?[0-9]+)$/) {\r\n    # critical = 10, crit if > 10 or < 0\r\n    $level = $ERRORS{CRITICAL}\r\n        if ($value > $1 || $value < 0);\r\n  } elsif ($self->{criticalrange} =~ /^([-+]?[0-9]*\\.?[0-9]+):$/) {\r\n    # critical = 10:, crit if < 10\r\n    $level = $ERRORS{CRITICAL}\r\n        if ($value < $1);\r\n  } elsif ($self->{criticalrange} =~ /^~:([-+]?[0-9]*\\.?[0-9]+)$/) {\r\n    # critical = ~:10, crit if > 10\r\n    $level = $ERRORS{CRITICAL}\r\n        if ($value > $1);\r\n  } elsif ($self->{criticalrange} =~ /^([-+]?[0-9]*\\.?[0-9]+):([-+]?[0-9]*\\.?[0-9]+)$/) {\r\n    # critical = 10:20, crit if < 10 or > 20\r\n    $level = $ERRORS{CRITICAL}\r\n        if ($value < $1 || $value > $2);\r\n  } elsif ($self->{criticalrange} =~ /^@([-+]?[0-9]*\\.?[0-9]+):([-+]?[0-9]*\\.?[0-9]+)$/) {\r\n    # critical = @10:20, crit if >= 10 and <= 20\r\n    $level = $ERRORS{CRITICAL}\r\n        if ($value >= $1 && $value <= $2);\r\n  }\r\n  return $level;\r\n  #\r\n  # syntax error must be reported with returncode -1\r\n  #\r\n}\r\n\r\nsub add_nagios {\r\n  my $self = shift;\r\n  my $level = shift;\r\n  my $message = shift;\r\n  push(@{$self->{nagios}->{messages}->{$level}}, $message);\r\n  # recalc current level\r\n  foreach my $llevel (qw(CRITICAL WARNING UNKNOWN OK)) {\r\n    if (scalar(@{$self->{nagios}->{messages}->{$ERRORS{$llevel}}})) {\r\n      $self->{nagios_level} = $ERRORS{$llevel};\r\n    }\r\n  }\r\n}\r\n\r\nsub add_nagios_ok {\r\n  my $self = shift;\r\n  my $message = shift;\r\n  $self->add_nagios($ERRORS{OK}, $message);\r\n}\r\n\r\nsub add_nagios_warning {\r\n  my $self = shift;\r\n  my $message = shift;\r\n  $self->add_nagios($ERRORS{WARNING}, $message);\r\n}\r\n\r\nsub add_nagios_critical {\r\n  my $self = shift;\r\n  my $message = shift;\r\n  $self->add_nagios($ERRORS{CRITICAL}, $message);\r\n}\r\n\r\nsub add_nagios_unknown {\r\n  my $self = shift;\r\n  my $message = shift;\r\n  $self->add_nagios($ERRORS{UNKNOWN}, $message);\r\n}\r\n\r\nsub add_perfdata {\r\n  my $self = shift;\r\n  my $data = shift;\r\n  push(@{$self->{nagios}->{perfdata}}, $data);\r\n}\r\n\r\nsub merge_nagios {\r\n  my $self = shift;\r\n  my $child = shift;\r\n  foreach my $level (0..3) {\r\n    foreach (@{$child->{nagios}->{messages}->{$level}}) {\r\n      $self->add_nagios($level, $_);\r\n    }\r\n    #push(@{$self->{nagios}->{messages}->{$level}},\r\n    #    @{$child->{nagios}->{messages}->{$level}});\r\n  }\r\n  push(@{$self->{nagios}->{perfdata}}, @{$child->{nagios}->{perfdata}});\r\n}\r\n\r\nsub calculate_result {\r\n  my $self = shift;\r\n  my $labels = shift || {};\r\n  my $multiline = 0;\r\n  map {\r\n    $self->{nagios_level} = $ERRORS{$_} if\r\n        (scalar(@{$self->{nagios}->{messages}->{$ERRORS{$_}}}));\r\n  } ("OK", "UNKNOWN", "WARNING", "CRITICAL");\r\n  if ($ENV{NRPE_MULTILINESUPPORT} &&\r\n      length join(" ", @{$self->{nagios}->{perfdata}}) > 200) {\r\n    $multiline = 1;\r\n  }\r\n  my $all_messages = join(($multiline ? "\\n" : ", "), map {\r\n      join(($multiline ? "\\n" : ", "), @{$self->{nagios}->{messages}->{$ERRORS{$_}}})\r\n  } grep {\r\n      scalar(@{$self->{nagios}->{messages}->{$ERRORS{$_}}})\r\n  } ("CRITICAL", "WARNING", "UNKNOWN", "OK"));\r\n  my $bad_messages = join(($multiline ? "\\n" : ", "), map {\r\n      join(($multiline ? "\\n" : ", "), @{$self->{nagios}->{messages}->{$ERRORS{$_}}})\r\n  } grep {\r\n      scalar(@{$self->{nagios}->{messages}->{$ERRORS{$_}}})\r\n  } ("CRITICAL", "WARNING", "UNKNOWN"));\r\n  my $good_messages = join(($multiline ? "\\n" : ", "), map {\r\n      join(($multiline ? "\\n" : ", "), @{$self->{nagios}->{messages}->{$ERRORS{$_}}})\r\n  } grep {\r\n      scalar(@{$self->{nagios}->{messages}->{$ERRORS{$_}}})\r\n  } ("OK"));\r\n  my $all_messages_short = $bad_messages ? $bad_messages : ''no problems'';\r\n  # if mode = my-....\r\n  # and there are some ok-messages\r\n  # output them instead of "no problems"\r\n  if ($self->{mode} =~ /^my\\:\\:/ && $good_messages) {\r\n    $all_messages_short = $bad_messages ? $bad_messages : $good_messages;\r\n  }\r\n  my $all_messages_html = "<table style=\\"border-collapse: collapse;\\">".\r\n      join("", map {\r\n          my $level = $_;\r\n          join("", map {\r\n              sprintf "<tr valign=\\"top\\"><td class=\\"service%s\\">%s</td></tr>",\r\n              $level, $_;\r\n          } @{$self->{nagios}->{messages}->{$ERRORS{$_}}});\r\n      } grep {\r\n          scalar(@{$self->{nagios}->{messages}->{$ERRORS{$_}}})\r\n      } ("CRITICAL", "WARNING", "UNKNOWN", "OK")).\r\n  "</table>";\r\n  if (exists $self->{identstring}) {\r\n    $self->{nagios_message} .= $self->{identstring};\r\n  }\r\n  if ($self->{report} eq "long") {\r\n    $self->{nagios_message} .= $all_messages;\r\n  } elsif ($self->{report} eq "short") {\r\n    $self->{nagios_message} .= $all_messages_short;\r\n  } elsif ($self->{report} eq "html") {\r\n    $self->{nagios_message} .= $all_messages_short."\\n".$all_messages_html;\r\n  }\r\n  foreach my $from (keys %{$self->{negate}}) {\r\n    if ((uc $from) =~ /^(OK|WARNING|CRITICAL|UNKNOWN)$/ &&\r\n        (uc $self->{negate}->{$from}) =~ /^(OK|WARNING|CRITICAL|UNKNOWN)$/) {\r\n      if ($self->{nagios_level} == $ERRORS{uc $from}) {\r\n        $self->{nagios_level} = $ERRORS{uc $self->{negate}->{$from}};\r\n      }\r\n    }\r\n  }\r\n  if ($self->{labelformat} eq "pnp4nagios") {\r\n    $self->{perfdata} = join(" ", @{$self->{nagios}->{perfdata}});\r\n  } else {\r\n    $self->{perfdata} = join(" ", map {\r\n        my $perfdata = $_;\r\n        if ($perfdata =~ /^(.*?)=(.*)/) {\r\n          my $label = $1;\r\n          my $data = $2;\r\n          if (exists $labels->{$label} &&\r\n              exists $labels->{$label}->{$self->{labelformat}}) {\r\n            $labels->{$label}->{$self->{labelformat}}."=".$data;\r\n          } else {\r\n            $perfdata;\r\n          }\r\n        } else {\r\n          $perfdata;\r\n        }\r\n    } @{$self->{nagios}->{perfdata}});\r\n  }\r\n}\r\n\r\nsub set_global_db_thresholds {\r\n  my $self = shift;\r\n  my $params = shift;\r\n  my $warning = undef;\r\n  my $critical = undef;\r\n  return unless defined $params->{dbthresholds};\r\n  $params->{name0} = $params->{dbthresholds};\r\n  # :pluginmode   :name     :warning    :critical\r\n  # mode          empty     \r\n  # \r\n  eval {\r\n    if ($self->{handle}->fetchrow_array(q{\r\n        SELECT table_name FROM information_schema.tables\r\n        WHERE table_schema = ?\r\n        AND table_name = ''CHECK_MYSQL_HEALTH_THRESHOLDS'';\r\n      }, $self->{database})) { # either --database... or information_schema\r\n      my @dbthresholds = $self->{handle}->fetchall_array(q{\r\n          SELECT * FROM check_mysql_health_thresholds\r\n      });\r\n      $params->{dbthresholds} = \\@dbthresholds;\r\n      foreach (@dbthresholds) { \r\n        if (($_->[0] eq $params->{cmdlinemode}) &&\r\n            (! defined $_->[1] || ! $_->[1])) {\r\n          ($warning, $critical) = ($_->[2], $_->[3]);\r\n        }\r\n      }\r\n    }\r\n  };\r\n  if (! $@) {\r\n    if ($warning) {\r\n      $params->{warningrange} = $warning;\r\n      $self->trace("read warningthreshold %s from database", $warning);\r\n    }\r\n    if ($critical) {\r\n      $params->{criticalrange} = $critical;\r\n      $self->trace("read criticalthreshold %s from database", $critical);\r\n    }\r\n  }\r\n}\r\n\r\nsub set_local_db_thresholds {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $warning = undef;\r\n  my $critical = undef;\r\n  # :pluginmode   :name     :warning    :critical\r\n  # mode          name0\r\n  # mode          name2\r\n  # mode          name\r\n  #\r\n  # first: argument of --dbthresholds, it it exists\r\n  # second: --name2\r\n  # third: --name\r\n  if (ref($params{dbthresholds}) eq ''ARRAY'') {\r\n    my $marker;\r\n    foreach (@{$params{dbthresholds}}) {\r\n      if ($_->[0] eq $params{cmdlinemode}) {\r\n        if (defined $_->[1] && $params{name0} && $_->[1] eq $params{name0}) {\r\n          ($warning, $critical) = ($_->[2], $_->[3]);\r\n          $marker = $params{name0};\r\n          last;\r\n        } elsif (defined $_->[1] && $params{name2} && $_->[1] eq $params{name2}) {\r\n          ($warning, $critical) = ($_->[2], $_->[3]);\r\n          $marker = $params{name2};\r\n          last;\r\n        } elsif (defined $_->[1] && $params{name} && $_->[1] eq $params{name}) {\r\n          ($warning, $critical) = ($_->[2], $_->[3]);\r\n          $marker = $params{name};\r\n          last;\r\n        }\r\n      }\r\n    }\r\n    if ($warning) {\r\n      $self->{warningrange} = $warning;\r\n      $self->trace("read warningthreshold %s for %s from database",\r\n         $marker, $warning);\r\n    }\r\n    if ($critical) {\r\n      $self->{criticalrange} = $critical;\r\n      $self->trace("read criticalthreshold %s for %s from database",\r\n          $marker, $critical);\r\n    }\r\n  }\r\n}\r\n\r\nsub debug {\r\n  my $self = shift;\r\n  my $msg = shift;\r\n  if ($DBD::MySQL::Server::verbose) {\r\n    printf "%s %s\\n", $msg, ref($self);\r\n  }\r\n}\r\n\r\nsub dbconnect {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $retval = undef;\r\n  $self->{tic} = Time::HiRes::time();\r\n  $self->{handle} = DBD::MySQL::Server::Connection->new(%params);\r\n  if ($self->{handle}->{errstr}) {\r\n    if ($params{mode} =~ /^server::tnsping/ &&\r\n        $self->{handle}->{errstr} =~ /ORA-01017/) {\r\n      $self->add_nagios($ERRORS{OK},\r\n          sprintf "connection established to %s.", $self->{connect});\r\n      $retval = undef;\r\n    } elsif ($self->{handle}->{errstr} eq "alarm\\n") {\r\n      $self->add_nagios($ERRORS{CRITICAL},\r\n          sprintf "connection could not be established within %d seconds",\r\n              $self->{timeout});\r\n    } else {\r\n      $self->add_nagios($ERRORS{CRITICAL},\r\n          sprintf "cannot connect to %s. %s",\r\n          $self->{database}, $self->{handle}->{errstr});\r\n      $retval = undef;\r\n    }\r\n  } else {\r\n    $retval = $self->{handle};\r\n  }\r\n  $self->{tac} = Time::HiRes::time();\r\n  return $retval;\r\n}\r\n\r\nsub trace {\r\n  my $self = shift;\r\n  my $format = shift;\r\n  $self->{trace} = -f "/tmp/check_mysql_health.trace" ? 1 : 0;\r\n  if ($self->{verbose}) {\r\n    printf("%s: ", scalar localtime);\r\n    printf($format, @_);\r\n  }\r\n  if ($self->{trace}) {\r\n    my $logfh = new IO::File;\r\n    $logfh->autoflush(1);\r\n    if ($logfh->open("/tmp/check_mysql_health.trace", "a")) {\r\n      $logfh->printf("%s: ", scalar localtime);\r\n      $logfh->printf($format, @_);\r\n      $logfh->printf("\\n");\r\n      $logfh->close();\r\n    }\r\n  }\r\n}\r\n\r\nsub DESTROY {\r\n  my $self = shift;\r\n  my $handle1 = "null";\r\n  my $handle2 = "null";\r\n  if (defined $self->{handle}) {\r\n    $handle1 = ref($self->{handle});\r\n    if (defined $self->{handle}->{handle}) {\r\n      $handle2 = ref($self->{handle}->{handle});\r\n    }\r\n  }\r\n  $self->trace(sprintf "DESTROY %s with handle %s %s", ref($self), $handle1, $handle2);\r\n  if (ref($self) eq "DBD::MySQL::Server") {\r\n  }\r\n  $self->trace(sprintf "DESTROY %s exit with handle %s %s", ref($self), $handle1, $handle2);\r\n  if (ref($self) eq "DBD::MySQL::Server") {\r\n    #printf "humpftata\\n";\r\n  }\r\n}\r\n\r\nsub save_state {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $extension = "";\r\n  my $mode = $params{mode};\r\n  if ($params{connect} && $params{connect} =~ /(\\w+)\\/(\\w+)@(\\w+)/) {\r\n    $params{connect} = $3;\r\n  } elsif ($params{connect}) {\r\n    # just to be sure\r\n    $params{connect} =~ s/\\//_/g;\r\n  }\r\n  if ($^O =~ /MSWin/) {\r\n    $mode =~ s/::/_/g;\r\n    $params{statefilesdir} = $self->system_vartmpdir();\r\n  }\r\n  if (! -d $params{statefilesdir}) {\r\n    eval {\r\n      use File::Path;\r\n      mkpath $params{statefilesdir};\r\n    };\r\n  }\r\n  if ($@ || ! -w $params{statefilesdir}) {\r\n    $self->add_nagios($ERRORS{CRITICAL},\r\n        sprintf "statefilesdir %s does not exist or is not writable\\n",\r\n        $params{statefilesdir});\r\n    return;\r\n  }\r\n  my $statefile = sprintf "%s_%s", $params{hostname}, $mode;\r\n  $extension .= $params{differenciator} ? "_".$params{differenciator} : "";\r\n  $extension .= $params{socket} ? "_".$params{socket} : "";\r\n  $extension .= $params{port} ? "_".$params{port} : "";\r\n  $extension .= $params{database} ? "_".$params{database} : "";\r\n  $extension .= $params{tablespace} ? "_".$params{tablespace} : "";\r\n  $extension .= $params{datafile} ? "_".$params{datafile} : "";\r\n  $extension .= $params{name} ? "_".$params{name} : "";\r\n  $extension =~ s/\\//_/g;\r\n  $extension =~ s/\\(/_/g;\r\n  $extension =~ s/\\)/_/g;\r\n  $extension =~ s/\\*/_/g;\r\n  $extension =~ s/\\s/_/g;\r\n  $statefile .= $extension;\r\n  $statefile = lc $statefile;\r\n  $statefile = sprintf "%s/%s", $params{statefilesdir}, $statefile;\r\n  if (open(STATE, ">$statefile")) {\r\n    if ((ref($params{save}) eq "HASH") && exists $params{save}->{timestamp}) {\r\n      $params{save}->{localtime} = scalar localtime $params{save}->{timestamp};\r\n    }\r\n    printf STATE Data::Dumper::Dumper($params{save});\r\n    close STATE;\r\n  } else { \r\n    $self->add_nagios($ERRORS{CRITICAL},\r\n        sprintf "statefile %s is not writable", $statefile);\r\n  }\r\n  $self->debug(sprintf "saved %s to %s",\r\n      Data::Dumper::Dumper($params{save}), $statefile);\r\n}\r\n\r\nsub load_state {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $extension = "";\r\n  my $mode = $params{mode};\r\n  if ($params{connect} && $params{connect} =~ /(\\w+)\\/(\\w+)@(\\w+)/) {\r\n    $params{connect} = $3;\r\n  } elsif ($params{connect}) {\r\n    # just to be sure\r\n    $params{connect} =~ s/\\//_/g;\r\n  }\r\n  if ($^O =~ /MSWin/) {\r\n    $mode =~ s/::/_/g;\r\n    $params{statefilesdir} = $self->system_vartmpdir();\r\n  }\r\n  my $statefile = sprintf "%s_%s", $params{hostname}, $mode;\r\n  $extension .= $params{differenciator} ? "_".$params{differenciator} : "";\r\n  $extension .= $params{socket} ? "_".$params{socket} : "";\r\n  $extension .= $params{port} ? "_".$params{port} : "";\r\n  $extension .= $params{database} ? "_".$params{database} : "";\r\n  $extension .= $params{tablespace} ? "_".$params{tablespace} : "";\r\n  $extension .= $params{datafile} ? "_".$params{datafile} : "";\r\n  $extension .= $params{name} ? "_".$params{name} : "";\r\n  $extension =~ s/\\//_/g;\r\n  $extension =~ s/\\(/_/g;\r\n  $extension =~ s/\\)/_/g;\r\n  $extension =~ s/\\*/_/g;\r\n  $extension =~ s/\\s/_/g;\r\n  $statefile .= $extension;\r\n  $statefile = lc $statefile;\r\n  $statefile = sprintf "%s/%s", $params{statefilesdir}, $statefile;\r\n  if ( -f $statefile) {\r\n    our $VAR1;\r\n    eval {\r\n      require $statefile;\r\n    };\r\n    if($@) {\r\n      $self->add_nagios($ERRORS{CRITICAL},\r\n          sprintf "statefile %s is corrupt", $statefile);\r\n    }\r\n    $self->debug(sprintf "load %s", Data::Dumper::Dumper($VAR1));\r\n    return $VAR1;\r\n  } else {\r\n    return undef;\r\n  }\r\n}\r\n\r\nsub valdiff {\r\n  my $self = shift;\r\n  my $pparams = shift;\r\n  my %params = %{$pparams};\r\n  my @keys = @_;\r\n  my $now = time;\r\n  my $last_values = $self->load_state(%params) || eval {\r\n    my $empty_events = {};\r\n    foreach (@keys) {\r\n      $empty_events->{$_} = 0;\r\n    }\r\n    $empty_events->{timestamp} = 0;\r\n    if ($params{lookback}) {\r\n      $empty_events->{lookback_history} = {};\r\n    }\r\n    $empty_events;\r\n  };\r\n  foreach (@keys) {\r\n    if ($params{lookback}) {\r\n      # find a last_value in the history which fits lookback best\r\n      # and overwrite $last_values->{$_} with historic data\r\n      if (exists $last_values->{lookback_history}->{$_}) {\r\n        foreach my $date (sort {$a <=> $b} keys %{$last_values->{lookback_history}->{$_}}) {\r\n          if ($date >= ($now - $params{lookback})) {\r\n            $last_values->{$_} = $last_values->{lookback_history}->{$_}->{$date};\r\n            $last_values->{timestamp} = $date;\r\n            last;\r\n          } else {\r\n            delete $last_values->{lookback_history}->{$_}->{$date};\r\n          }\r\n        }\r\n      }\r\n    }\r\n    $last_values->{$_} = 0 if ! exists $last_values->{$_};\r\n    if ($self->{$_} >= $last_values->{$_}) {\r\n      $self->{''delta_''.$_} = $self->{$_} - $last_values->{$_};\r\n    } else {\r\n      # vermutlich db restart und zaehler alle auf null\r\n      $self->{''delta_''.$_} = $self->{$_};\r\n    }\r\n    $self->debug(sprintf "delta_%s %f", $_, $self->{''delta_''.$_});\r\n  }\r\n  $self->{''delta_timestamp''} = $now - $last_values->{timestamp};\r\n  $params{save} = eval {\r\n    my $empty_events = {};\r\n    foreach (@keys) {\r\n      $empty_events->{$_} = $self->{$_};\r\n    }\r\n    $empty_events->{timestamp} = $now;\r\n    if ($params{lookback}) {\r\n      $empty_events->{lookback_history} = $last_values->{lookback_history};\r\n      foreach (@keys) {\r\n        $empty_events->{lookback_history}->{$_}->{$now} = $self->{$_};\r\n      }\r\n    }\r\n    $empty_events;\r\n  };\r\n  $self->save_state(%params);\r\n}\r\n\r\nsub requires_version {\r\n  my $self = shift;\r\n  my $version = shift;\r\n  my @instances = DBD::MySQL::Server::return_servers();\r\n  my $instversion = $instances[0]->{version};\r\n  if (! $self->version_is_minimum($version)) {\r\n    $self->add_nagios($ERRORS{UNKNOWN}, \r\n        sprintf "not implemented/possible for MySQL release %s", $instversion);\r\n  }\r\n}\r\n\r\nsub version_is_minimum {\r\n  # the current version is newer or equal\r\n  my $self = shift;\r\n  my $version = shift;\r\n  my $newer = 1;\r\n  my @instances = DBD::MySQL::Server::return_servers();\r\n  my @v1 = map { $_ eq "x" ? 0 : $_ } split(/\\./, $version);\r\n  my @v2 = split(/\\./, $instances[0]->{version});\r\n  if (scalar(@v1) > scalar(@v2)) {\r\n    push(@v2, (0) x (scalar(@v1) - scalar(@v2)));\r\n  } elsif (scalar(@v2) > scalar(@v1)) {\r\n    push(@v1, (0) x (scalar(@v2) - scalar(@v1)));\r\n  }\r\n  foreach my $pos (0..$#v1) {\r\n    if ($v2[$pos] > $v1[$pos]) {\r\n      $newer = 1;\r\n      last;\r\n    } elsif ($v2[$pos] < $v1[$pos]) {\r\n      $newer = 0;\r\n      last;\r\n    }\r\n  }\r\n  #printf STDERR "check if %s os minimum %s\\n", join(".", @v2), join(".", @v1);\r\n  return $newer;\r\n}\r\n\r\nsub instance_thread {\r\n  my $self = shift;\r\n  my @instances = DBD::MySQL::Server::return_servers();\r\n  return $instances[0]->{thread};\r\n}\r\n\r\nsub windows_server {\r\n  my $self = shift;\r\n  my @instances = DBD::MySQL::Server::return_servers();\r\n  if ($instances[0]->{os} =~ /Win/i) {\r\n    return 1;\r\n  } else {\r\n    return 0;\r\n  }\r\n}\r\n\r\nsub system_vartmpdir {\r\n  my $self = shift;\r\n  if ($^O =~ /MSWin/) {\r\n    return $self->system_tmpdir();\r\n  } else {\r\n    return "/var/tmp/check_mysql_health";\r\n  }\r\n}\r\n\r\nsub system_oldvartmpdir {\r\n  my $self = shift;\r\n  return "/tmp";\r\n}\r\n\r\nsub system_tmpdir {\r\n  my $self = shift;\r\n  if ($^O =~ /MSWin/) {\r\n    return $ENV{TEMP} if defined $ENV{TEMP};\r\n    return $ENV{TMP} if defined $ENV{TMP};\r\n    return File::Spec->catfile($ENV{windir}, ''Temp'')\r\n        if defined $ENV{windir};\r\n    return ''C:\\Temp'';\r\n  } else {\r\n    return "/tmp";\r\n  }\r\n}\r\n\r\nsub decode_password {\r\n  my $self = shift;\r\n  my $password = shift;\r\n  if ($password && $password =~ /^rfc3986:\\/\\/(.*)/) {\r\n    $password = $1;\r\n    $password =~ s/\\%([A-Fa-f0-9]{2})/pack(''C'', hex($1))/seg;\r\n  }\r\n  return $password;\r\n}\r\n\r\n\r\npackage DBD::MySQL::Server::Connection;\r\n\r\nuse strict;\r\n\r\nour @ISA = qw(DBD::MySQL::Server);\r\n\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  my $self = {\r\n    mode => $params{mode},\r\n    timeout => $params{timeout},\r\n    access => $params{method} || "dbi",\r\n    hostname => $params{hostname},\r\n    database => $params{database} || "information_schema",\r\n    port => $params{port},\r\n    socket => $params{socket},\r\n    username => $params{username},\r\n    password => $params{password},\r\n    mycnf => $params{mycnf},\r\n    mycnfgroup => $params{mycnfgroup},\r\n    handle => undef,\r\n  };\r\n  bless $self, $class;\r\n  if ($params{method} eq "dbi") {\r\n    bless $self, "DBD::MySQL::Server::Connection::Dbi";\r\n  } elsif ($params{method} eq "mysql") {\r\n    bless $self, "DBD::MySQL::Server::Connection::Mysql";\r\n  } elsif ($params{method} eq "sqlrelay") {\r\n    bless $self, "DBD::MySQL::Server::Connection::Sqlrelay";\r\n  }\r\n  $self->init(%params);\r\n  return $self;\r\n}\r\n\r\n\r\npackage DBD::MySQL::Server::Connection::Dbi;\r\n\r\nuse strict;\r\nuse Net::Ping;\r\n\r\nour @ISA = qw(DBD::MySQL::Server::Connection);\r\n\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $retval = undef;\r\n  if ($self->{mode} =~ /^server::tnsping/) {\r\n    if (! $self->{connect}) {\r\n      $self->{errstr} = "Please specify a database";\r\n    } else {\r\n      $self->{sid} = $self->{connect};\r\n      $self->{username} ||= time;  # prefer an existing user\r\n      $self->{password} = time;\r\n    }\r\n  } else {\r\n    if (\r\n        ($self->{hostname} ne ''localhost'' && (! $self->{username} || ! $self->{password})) && \r\n        (! $self->{mycnf}) ) {\r\n      $self->{errstr} = "Please specify hostname, username and password or a .cnf file";\r\n      return undef;\r\n    }\r\n    $self->{dsn} = "DBI:mysql:";\r\n    $self->{dsn} .= sprintf "database=%s", $self->{database};\r\n    if ($self->{mycnf}) {\r\n      $self->{dsn} .= sprintf ";mysql_read_default_file=%s", $self->{mycnf};\r\n      if ($self->{mycnfgroup}) {\r\n        $self->{dsn} .= sprintf ";mysql_read_default_group=%s", $self->{mycnfgroup};\r\n      }\r\n    } else {\r\n      $self->{dsn} .= sprintf ";host=%s", $self->{hostname};\r\n      $self->{dsn} .= sprintf ";port=%s", $self->{port}\r\n          unless $self->{socket} || $self->{hostname} eq ''localhost'';\r\n      $self->{dsn} .= sprintf ";mysql_socket=%s", $self->{socket} \r\n          if $self->{socket};\r\n    }\r\n  }\r\n  if (! exists $self->{errstr}) {\r\n    eval {\r\n      require DBI;\r\n      use POSIX '':signal_h'';\r\n      if ($^O =~ /MSWin/) {\r\n        local $SIG{''ALRM''} = sub {\r\n          die "alarm\\n";\r\n        };\r\n      } else {\r\n        my $mask = POSIX::SigSet->new( SIGALRM );\r\n        my $action = POSIX::SigAction->new(\r\n            sub { die "alarm\\n" ; }, $mask);\r\n        my $oldaction = POSIX::SigAction->new();\r\n        sigaction(SIGALRM ,$action ,$oldaction );\r\n      }\r\n      alarm($self->{timeout} - 1); # 1 second before the global unknown timeout\r\n      if ($self->{handle} = DBI->connect(\r\n          $self->{dsn},\r\n          $self->{username},\r\n          $self->decode_password($self->{password}),\r\n          { RaiseError => 0, AutoCommit => 0, PrintError => 0 })) {\r\n#        $self->{handle}->do(q{\r\n#            ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".," });\r\n        $retval = $self;\r\n      } else {\r\n        $self->{errstr} = DBI::errstr();\r\n      }\r\n    };\r\n    if ($@) {\r\n      $self->{errstr} = $@;\r\n      $retval = undef;\r\n    }\r\n  }\r\n  $self->{tac} = Time::HiRes::time();\r\n  return $retval;\r\n}\r\n\r\nsub selectrow_hashref {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  my @arguments = @_;\r\n  my $sth = undef;\r\n  my $hashref = undef;\r\n  eval {\r\n    $self->trace(sprintf "SQL:\\n%s\\nARGS:\\n%s\\n",\r\n        $sql, Data::Dumper::Dumper(\\@arguments));\r\n    # helm auf! jetzt wirds dreckig.\r\n    if ($sql =~ /^\\s*SHOW/) {\r\n      $hashref = $self->{handle}->selectrow_hashref($sql);\r\n    } else {\r\n      $sth = $self->{handle}->prepare($sql);\r\n      if (scalar(@arguments)) {\r\n        $sth->execute(@arguments);\r\n      } else {\r\n        $sth->execute();\r\n      }\r\n      $hashref = $sth->selectrow_hashref();\r\n    }\r\n    $self->trace(sprintf "RESULT:\\n%s\\n",\r\n        Data::Dumper::Dumper($hashref));\r\n  };\r\n  if ($@) {\r\n    $self->debug(sprintf "bumm %s", $@);\r\n  }\r\n  if (-f "/tmp/check_mysql_health_simulation/".$self->{mode}) {\r\n    my $simulation = do { local (@ARGV, $/) =\r\n        "/tmp/check_mysql_health_simulation/".$self->{mode}; <> };\r\n    # keine lust auf den scheiss\r\n  }\r\n  return $hashref;\r\n}\r\n\r\nsub fetchrow_array {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  my @arguments = @_;\r\n  my $sth = undef;\r\n  my @row = ();\r\n  eval {\r\n    $self->trace(sprintf "SQL:\\n%s\\nARGS:\\n%s\\n",\r\n        $sql, Data::Dumper::Dumper(\\@arguments));\r\n    $sth = $self->{handle}->prepare($sql);\r\n    if (scalar(@arguments)) {\r\n      $sth->execute(@arguments);\r\n    } else {\r\n      $sth->execute();\r\n    }\r\n    @row = $sth->fetchrow_array();\r\n    $self->trace(sprintf "RESULT:\\n%s\\n",\r\n        Data::Dumper::Dumper(\\@row));\r\n  }; \r\n  if ($@) {\r\n    $self->debug(sprintf "bumm %s", $@);\r\n  }\r\n  if (-f "/tmp/check_mysql_health_simulation/".$self->{mode}) {\r\n    my $simulation = do { local (@ARGV, $/) = \r\n        "/tmp/check_mysql_health_simulation/".$self->{mode}; <> };\r\n    @row = split(/\\s+/, (split(/\\n/, $simulation))[0]);\r\n  }\r\n  return $row[0] unless wantarray;\r\n  return @row;\r\n}\r\n\r\nsub fetchall_array {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  my @arguments = @_;\r\n  my $sth = undef;\r\n  my $rows = undef;\r\n  eval {\r\n    $self->trace(sprintf "SQL:\\n%s\\nARGS:\\n%s\\n",\r\n        $sql, Data::Dumper::Dumper(\\@arguments));\r\n    $sth = $self->{handle}->prepare($sql);\r\n    if (scalar(@arguments)) {\r\n      $sth->execute(@arguments);\r\n    } else {\r\n      $sth->execute();\r\n    }\r\n    $rows = $sth->fetchall_arrayref();\r\n    $self->trace(sprintf "RESULT:\\n%s\\n",\r\n        Data::Dumper::Dumper($rows));\r\n  }; \r\n  if ($@) {\r\n    printf STDERR "bumm %s\\n", $@;\r\n  }\r\n  if (-f "/tmp/check_mysql_health_simulation/".$self->{mode}) {\r\n    my $simulation = do { local (@ARGV, $/) = \r\n        "/tmp/check_mysql_health_simulation/".$self->{mode}; <> };\r\n    @{$rows} = map { [ split(/\\s+/, $_) ] } split(/\\n/, $simulation);\r\n  }\r\n  return @{$rows};\r\n}\r\n\r\nsub func {\r\n  my $self = shift;\r\n  $self->{handle}->func(@_);\r\n}\r\n\r\n\r\nsub execute {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  eval {\r\n    my $sth = $self->{handle}->prepare($sql);\r\n    $sth->execute();\r\n  };\r\n  if ($@) {\r\n    printf STDERR "bumm %s\\n", $@;\r\n  }\r\n}\r\n\r\nsub errstr {\r\n  my $self = shift;\r\n  return $self->{errstr};\r\n}\r\n\r\nsub DESTROY {\r\n  my $self = shift;\r\n  $self->trace(sprintf "disconnecting DBD %s",\r\n      $self->{handle} ? "with handle" : "without handle");\r\n  $self->{handle}->disconnect() if $self->{handle};\r\n}\r\n\r\npackage DBD::MySQL::Server::Connection::Mysql;\r\n\r\nuse strict;\r\nuse File::Temp qw/tempfile/;\r\n\r\nour @ISA = qw(DBD::MySQL::Server::Connection);\r\n\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $retval = undef;\r\n  $self->{loginstring} = "traditional";\r\n  ($self->{sql_commandfile_handle}, $self->{sql_commandfile}) =\r\n      tempfile($self->{mode}."XXXXX", SUFFIX => ".sql", \r\n      DIR => $self->system_tmpdir() );\r\n  close $self->{sql_commandfile_handle};\r\n  ($self->{sql_resultfile_handle}, $self->{sql_resultfile}) =\r\n      tempfile($self->{mode}."XXXXX", SUFFIX => ".out", \r\n      DIR => $self->system_tmpdir() );\r\n  close $self->{sql_resultfile_handle};\r\n  if ($self->{mode} =~ /^server::tnsping/) {\r\n    if (! $self->{connect}) {\r\n      $self->{errstr} = "Please specify a database";\r\n    } else {\r\n      $self->{sid} = $self->{connect};\r\n      $self->{username} ||= time;  # prefer an existing user\r\n      $self->{password} = time;\r\n    }\r\n  } else {\r\n    if (! $self->{username} || ! $self->{password}) {\r\n      $self->{errstr} = "Please specify database, username and password";\r\n      return undef;\r\n    } elsif (! (($self->{hostname} && $self->{port}) || $self->{socket})) {\r\n      $self->{errstr} = "Please specify hostname and port or socket";\r\n      return undef;\r\n    }\r\n  }\r\n  if (! exists $self->{errstr}) {\r\n    $self->{password} = $self->decode_password($self->{password});\r\n    eval {\r\n      my $mysql = ''/''.''usr''.''/''.''bin''.''/''.''mysql'';\r\n      if (! -x $mysql) {\r\n        die "nomysql\\n";\r\n      }\r\n      if ($self->{loginstring} eq "traditional") {\r\n        $self->{sqlplus} = sprintf "%s ", $mysql;\r\n        $self->{sqlplus} .= sprintf "--batch --raw --skip-column-names ";\r\n        $self->{sqlplus} .= sprintf "--database=%s ", $self->{database};\r\n        $self->{sqlplus} .= sprintf "--host=%s ", $self->{hostname};\r\n        $self->{sqlplus} .= sprintf "--port=%s ", $self->{port}\r\n            unless $self->{socket} || $self->{hostname} eq "localhost";\r\n        $self->{sqlplus} .= sprintf "--socket=%s ", $self->{socket}\r\n            if $self->{socket};\r\n        $self->{sqlplus} .= sprintf "--user=%s --password=''%s'' < %s > %s",\r\n            $self->{username}, $self->{password},\r\n            $self->{sql_commandfile}, $self->{sql_resultfile};\r\n      }\r\n  \r\n      use POSIX '':signal_h'';\r\n      if ($^O =~ /MSWin/) {\r\n        local $SIG{''ALRM''} = sub {\r\n          die "alarm\\n";\r\n        };\r\n      } else {\r\n        my $mask = POSIX::SigSet->new( SIGALRM );\r\n        my $action = POSIX::SigAction->new(\r\n            sub { die "alarm\\n" ; }, $mask);\r\n        my $oldaction = POSIX::SigAction->new();\r\n        sigaction(SIGALRM ,$action ,$oldaction );\r\n      }\r\n      alarm($self->{timeout} - 1); # 1 second before the global unknown timeout\r\n  \r\n      my $answer = $self->fetchrow_array(\r\n          q{ SELECT 42 FROM dual});\r\n      die unless defined $answer and $answer == 42;\r\n      $retval = $self;\r\n    };\r\n    if ($@) {\r\n      $self->{errstr} = $@;\r\n      $self->{errstr} =~ s/at $0 .*//g;\r\n      chomp $self->{errstr};\r\n      $retval = undef;\r\n    }\r\n  }\r\n  $self->{tac} = Time::HiRes::time();\r\n  return $retval;\r\n}\r\n\r\nsub selectrow_hashref {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  my @arguments = @_;\r\n  my $sth = undef;\r\n  my $hashref = undef;\r\n  foreach (@arguments) {\r\n    # replace the ? by the parameters\r\n    if (/^\\d+$/) {\r\n      $sql =~ s/\\?/$_/;\r\n    } else {\r\n      $sql =~ s/\\?/''$_''/;\r\n    }\r\n  }\r\n  if ($sql =~ /^\\s*SHOW/) {\r\n    $sql .= ''\\G''; # http://dev.mysql.com/doc/refman/5.1/de/show-slave-status.html\r\n  }\r\n  $self->trace(sprintf "SQL (? resolved):\\n%s\\nARGS:\\n%s\\n",\r\n      $sql, Data::Dumper::Dumper(\\@arguments));\r\n  $self->create_commandfile($sql);\r\n  my $exit_output = `$self->{sqlplus}`;\r\n  if ($?) {\r\n    printf STDERR "fetchrow_array exit bumm \\n";\r\n    my $output = do { local (@ARGV, $/) = $self->{sql_resultfile}; <> };\r\n    my @oerrs = map {\r\n      /((ERROR \\d+).*)/ ? $1 : ();\r\n    } split(/\\n/, $output);\r\n    $self->{errstr} = join(" ", @oerrs);\r\n  } else {\r\n    my $output = do { local (@ARGV, $/) = $self->{sql_resultfile}; <> };\r\n    if ($sql =~ /^\\s*SHOW/) {\r\n      map {\r\n        if (/^\\s*([\\w_]+):\\s*(.*)/) {\r\n          $hashref->{$1} = $2;\r\n        }\r\n      } split(/\\n/, $output);\r\n    } else {\r\n      # i dont mess around here and you shouldn''t either\r\n    }\r\n    $self->trace(sprintf "RESULT:\\n%s\\n",\r\n        Data::Dumper::Dumper($hashref));\r\n  }\r\n  unlink $self->{sql_commandfile};\r\n  unlink $self->{sql_resultfile};\r\n  return $hashref;\r\n}\r\n\r\nsub fetchrow_array {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  my @arguments = @_;\r\n  my $sth = undef;\r\n  my @row = ();\r\n  foreach (@arguments) {\r\n    # replace the ? by the parameters\r\n    if (/^\\d+$/) {\r\n      $sql =~ s/\\?/$_/;\r\n    } else {\r\n      $sql =~ s/\\?/''$_''/;\r\n    }\r\n  }\r\n  $self->trace(sprintf "SQL (? resolved):\\n%s\\nARGS:\\n%s\\n",\r\n      $sql, Data::Dumper::Dumper(\\@arguments));\r\n  $self->create_commandfile($sql);\r\n  my $exit_output = `$self->{sqlplus}`;\r\n  if ($?) {\r\n    printf STDERR "fetchrow_array exit bumm \\n";\r\n    my $output = do { local (@ARGV, $/) = $self->{sql_resultfile}; <> };\r\n    my @oerrs = map {\r\n      /((ERROR \\d+).*)/ ? $1 : ();\r\n    } split(/\\n/, $output);\r\n    $self->{errstr} = join(" ", @oerrs);\r\n  } else {\r\n    my $output = do { local (@ARGV, $/) = $self->{sql_resultfile}; <> };\r\n    @row = map { convert($_) } \r\n        map { s/^\\s+([\\.\\d]+)$/$1/g; $_ }         # strip leading space from numbers\r\n        map { s/\\s+$//g; $_ }                     # strip trailing space\r\n        split(/\\t/, (split(/\\n/, $output))[0]);\r\n    $self->trace(sprintf "RESULT:\\n%s\\n",\r\n        Data::Dumper::Dumper(\\@row));\r\n  }\r\n  if ($@) {\r\n    $self->debug(sprintf "bumm %s", $@);\r\n  }\r\n  unlink $self->{sql_commandfile};\r\n  unlink $self->{sql_resultfile};\r\n  return $row[0] unless wantarray;\r\n  return @row;\r\n}\r\n\r\nsub fetchall_array {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  my @arguments = @_;\r\n  my $sth = undef;\r\n  my $rows = undef;\r\n  foreach (@arguments) {\r\n    # replace the ? by the parameters\r\n    if (/^\\d+$/) {\r\n      $sql =~ s/\\?/$_/;\r\n    } else {\r\n      $sql =~ s/\\?/''$_''/;\r\n    }\r\n  }\r\n  $self->trace(sprintf "SQL (? resolved):\\n%s\\nARGS:\\n%s\\n",\r\n      $sql, Data::Dumper::Dumper(\\@arguments));\r\n  $self->create_commandfile($sql);\r\n  my $exit_output = `$self->{sqlplus}`;\r\n  if ($?) {\r\n    printf STDERR "fetchrow_array exit bumm %s\\n", $exit_output;\r\n    my $output = do { local (@ARGV, $/) = $self->{sql_resultfile}; <> };\r\n    my @oerrs = map {\r\n      /((ERROR \\d+).*)/ ? $1 : ();\r\n    } split(/\\n/, $output);\r\n    $self->{errstr} = join(" ", @oerrs);\r\n  } else {\r\n    my $output = do { local (@ARGV, $/) = $self->{sql_resultfile}; <> };\r\n    my @rows = map { [ \r\n        map { convert($_) } \r\n        map { s/^\\s+([\\.\\d]+)$/$1/g; $_ }\r\n        map { s/\\s+$//g; $_ }\r\n        split /\\t/\r\n    ] } grep { ! /^\\d+ rows selected/ } \r\n        grep { ! /^Elapsed: / }\r\n        grep { ! /^\\s*$/ } split(/\\n/, $output);\r\n    $rows = \\@rows;\r\n    $self->trace(sprintf "RESULT:\\n%s\\n",\r\n        Data::Dumper::Dumper($rows));\r\n  }\r\n  if ($@) {\r\n    $self->debug(sprintf "bumm %s", $@);\r\n  }\r\n  unlink $self->{sql_commandfile};\r\n  unlink $self->{sql_resultfile};\r\n  return @{$rows};\r\n}\r\n\r\nsub func {\r\n  my $self = shift;\r\n  my $function = shift;\r\n  $self->{handle}->func(@_);\r\n}\r\n\r\nsub convert {\r\n  my $n = shift;\r\n  # mostly used to convert numbers in scientific notation\r\n  if ($n =~ /^\\s*\\d+\\s*$/) {\r\n    return $n;\r\n  } elsif ($n =~ /^\\s*([-+]?)(\\d*[\\.,]*\\d*)[eE]{1}([-+]?)(\\d+)\\s*$/) {\r\n    my ($vor, $num, $sign, $exp) = ($1, $2, $3, $4);\r\n    $n =~ s/E/e/g;\r\n    $n =~ s/,/\\./g;\r\n    $num =~ s/,/\\./g;\r\n    my $sig = $sign eq ''-'' ? "." . ($exp - 1 + length $num) : '''';\r\n    my $dec = sprintf "%${sig}f", $n;\r\n    $dec =~ s/\\.[0]+$//g;\r\n    return $dec;\r\n  } elsif ($n =~ /^\\s*([-+]?)(\\d+)[\\.,]*(\\d*)\\s*$/) {\r\n    return $1.$2.".".$3;\r\n  } elsif ($n =~ /^\\s*(.*?)\\s*$/) {\r\n    return $1;\r\n  } else {\r\n    return $n;\r\n  }\r\n}\r\n\r\n\r\nsub execute {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  eval {\r\n    my $sth = $self->{handle}->prepare($sql);\r\n    $sth->execute();\r\n  };\r\n  if ($@) {\r\n    printf STDERR "bumm %s\\n", $@;\r\n  }\r\n}\r\n\r\nsub errstr {\r\n  my $self = shift;\r\n  return $self->{errstr};\r\n}\r\n\r\nsub DESTROY {\r\n  my $self = shift;\r\n  $self->trace("try to clean up command and result files");\r\n  unlink $self->{sql_commandfile} if -f $self->{sql_commandfile};\r\n  unlink $self->{sql_resultfile} if -f $self->{sql_resultfile};\r\n}\r\n\r\nsub create_commandfile {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  open CMDCMD, "> $self->{sql_commandfile}"; \r\n  printf CMDCMD "%s\\n", $sql;\r\n  close CMDCMD;\r\n}\r\n\r\nsub decode_password {\r\n  my $self = shift;\r\n  my $password = shift;\r\n  $password = $self->SUPER::decode_password($password);\r\n  # we call ''...%s/%s@...'' inside backticks where the second %s is the password\r\n  # abc''xcv -> ''''abc''\\''''xcv''''\r\n  # abc''`xcv -> ''''abc''\\''''\\`xcv''''\r\n  if ($password =~ /''/) {\r\n    $password = "''".join("\\\\''", map { "''".$_."''"; } split("''", $password))."''";\r\n  }\r\n  return $password;\r\n}\r\n\r\n\r\npackage DBD::MySQL::Server::Connection::Sqlrelay;\r\n\r\nuse strict;\r\nuse Net::Ping;\r\n\r\nour @ISA = qw(DBD::MySQL::Server::Connection);\r\n\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $retval = undef;\r\n  if ($self->{mode} =~ /^server::tnsping/) {\r\n    if (! $self->{connect}) {\r\n      $self->{errstr} = "Please specify a database";\r\n    } else {\r\n      if ($self->{connect} =~ /([\\.\\w]+):(\\d+)/) {\r\n        $self->{host} = $1;\r\n        $self->{port} = $2;\r\n        $self->{socket} = "";\r\n      } elsif ($self->{connect} =~ /([\\.\\w]+):([\\w\\/]+)/) {\r\n        $self->{host} = $1;\r\n        $self->{socket} = $2;\r\n        $self->{port} = "";\r\n      }\r\n    }\r\n  } else {\r\n    if (! $self->{hostname} || ! $self->{username} || ! $self->{password}) {\r\n      if ($self->{hostname} && $self->{hostname} =~ /(\\w+?)\\/(.+)@([\\.\\w]+):(\\d+)/) {\r\n        $self->{username} = $1;\r\n        $self->{password} = $2;\r\n        $self->{hostname} = $3;\r\n        $self->{port} = $4;\r\n        $self->{socket} = "";\r\n      } elsif ($self->{hostname} && $self->{hostname} =~ /(\\w+?)\\/(.+)@([\\.\\w]+):([\\w\\/]+)/) {\r\n        $self->{username} = $1;\r\n        $self->{password} = $2;\r\n        $self->{hostname} = $3;\r\n        $self->{socket} = $4;\r\n        $self->{port} = "";\r\n      } else {\r\n        $self->{errstr} = "Please specify database, username and password";\r\n        return undef;\r\n      }\r\n    } else {\r\n      if ($self->{hostname} =~ /([\\.\\w]+):(\\d+)/) {\r\n        $self->{hostname} = $1;\r\n        $self->{port} = $2;\r\n        $self->{socket} = "";\r\n      } elsif ($self->{hostname} =~ /([\\.\\w]+):([\\w\\/]+)/) {\r\n        $self->{hostname} = $1;\r\n        $self->{socket} = $2;\r\n        $self->{port} = "";\r\n      } else {\r\n        $self->{errstr} = "Please specify hostname, username, password and port/socket";\r\n        return undef;\r\n      }\r\n    }\r\n  }\r\n  if (! exists $self->{errstr}) {\r\n    eval {\r\n      require DBI;\r\n      use POSIX '':signal_h'';\r\n      if ($^O =~ /MSWin/) {\r\n        local $SIG{''ALRM''} = sub {\r\n          die "alarm\\n";\r\n        };\r\n      } else {\r\n        my $mask = POSIX::SigSet->new( SIGALRM );\r\n        my $action = POSIX::SigAction->new(\r\n            sub { die "alarm\\n" ; }, $mask);\r\n        my $oldaction = POSIX::SigAction->new();\r\n        sigaction(SIGALRM ,$action ,$oldaction );\r\n      }\r\n      alarm($self->{timeout} - 1); # 1 second before the global unknown timeout\r\n      if ($self->{handle} = DBI->connect(\r\n          sprintf("DBI:SQLRelay:host=%s;port=%d;socket=%s", \r\n          $self->{hostname}, $self->{port}, $self->{socket}),\r\n          $self->{username},\r\n          $self->decode_password($self->{password}),\r\n          { RaiseError => 1, AutoCommit => 0, PrintError => 1 })) {\r\n        $retval = $self;\r\n        if ($self->{mode} =~ /^server::tnsping/ && $self->{handle}->ping()) {\r\n          # database connected. fake a "unknown user"\r\n          $self->{errstr} = "ORA-01017";\r\n        }\r\n      } else {\r\n        $self->{errstr} = DBI::errstr();\r\n      }\r\n    };\r\n    if ($@) {\r\n      $self->{errstr} = $@;\r\n      $self->{errstr} =~ s/at [\\w\\/\\.]+ line \\d+.*//g;\r\n      $retval = undef;\r\n    }\r\n  }\r\n  $self->{tac} = Time::HiRes::time();\r\n  return $retval;\r\n}\r\n\r\nsub fetchrow_array {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  my @arguments = @_;\r\n  my $sth = undef;\r\n  my @row = ();\r\n  $self->trace(sprintf "fetchrow_array: %s", $sql);\r\n  eval {\r\n    $sth = $self->{handle}->prepare($sql);\r\n    if (scalar(@arguments)) {\r\n      $sth->execute(@arguments);\r\n    } else {\r\n      $sth->execute();\r\n    }\r\n    @row = $sth->fetchrow_array();\r\n  };\r\n  if ($@) {\r\n    $self->debug(sprintf "bumm %s", $@);\r\n  }\r\n  if (-f "/tmp/check_mysql_health_simulation/".$self->{mode}) {\r\n    my $simulation = do { local (@ARGV, $/) =\r\n        "/tmp/check_mysql_health_simulation/".$self->{mode}; <> };\r\n    @row = split(/\\s+/, (split(/\\n/, $simulation))[0]);\r\n  }\r\n  return $row[0] unless wantarray;\r\n  return @row;\r\n}\r\n\r\nsub fetchall_array {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  my @arguments = @_;\r\n  my $sth = undef;\r\n  my $rows = undef;\r\n  $self->trace(sprintf "fetchall_array: %s", $sql);\r\n  eval {\r\n    $sth = $self->{handle}->prepare($sql);\r\n    if (scalar(@arguments)) {\r\n      $sth->execute(@arguments);\r\n    } else {\r\n      $sth->execute();\r\n    }\r\n    $rows = $sth->fetchall_arrayref();\r\n  };\r\n  if ($@) {\r\n    printf STDERR "bumm %s\\n", $@;\r\n  }\r\n  if (-f "/tmp/check_mysql_health_simulation/".$self->{mode}) {\r\n    my $simulation = do { local (@ARGV, $/) =\r\n        "/tmp/check_mysql_health_simulation/".$self->{mode}; <> };\r\n    @{$rows} = map { [ split(/\\s+/, $_) ] } split(/\\n/, $simulation);\r\n  }\r\n  return @{$rows};\r\n}\r\n\r\nsub func {\r\n  my $self = shift;\r\n  $self->{handle}->func(@_);\r\n}\r\n\r\nsub execute {\r\n  my $self = shift;\r\n  my $sql = shift;\r\n  eval {\r\n    my $sth = $self->{handle}->prepare($sql);\r\n    $sth->execute();\r\n  };\r\n  if ($@) {\r\n    printf STDERR "bumm %s\\n", $@;\r\n  }\r\n}\r\n\r\nsub DESTROY {\r\n  my $self = shift;\r\n  #$self->trace(sprintf "disconnecting DBD %s",\r\n  #    $self->{handle} ? "with handle" : "without handle");\r\n  #$self->{handle}->disconnect() if $self->{handle};\r\n}\r\n\r\n\r\n\r\n\r\npackage DBD::MySQL::Cluster;\r\n\r\nuse strict;\r\nuse Time::HiRes;\r\nuse IO::File;\r\nuse Data::Dumper;\r\n\r\n\r\n{\r\n  our $verbose = 0;\r\n  our $scream = 0; # scream if something is not implemented\r\n  our $access = "dbi"; # how do we access the database. \r\n  our $my_modules_dyn_dir = ""; # where we look for self-written extensions\r\n\r\n  my @clusters = ();\r\n  my $initerrors = undef;\r\n\r\n  sub add_cluster {\r\n    push(@clusters, shift);\r\n  }\r\n\r\n  sub return_clusters {\r\n    return @clusters;\r\n  }\r\n  \r\n  sub return_first_cluster() {\r\n    return $clusters[0];\r\n  }\r\n\r\n}\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  my $self = {\r\n    hostname => $params{hostname},\r\n    port => $params{port},\r\n    username => $params{username},\r\n    password => $params{password},\r\n    timeout => $params{timeout},\r\n    warningrange => $params{warningrange},\r\n    criticalrange => $params{criticalrange},\r\n    version => ''unknown'',\r\n    nodes => [],\r\n    ndbd_nodes => 0,\r\n    ndb_mgmd_nodes => 0,\r\n    mysqld_nodes => 0,\r\n  };\r\n  bless $self, $class;\r\n  $self->init_nagios();\r\n  if ($self->connect(%params)) {\r\n    DBD::MySQL::Cluster::add_cluster($self);\r\n    $self->init(%params);\r\n  }\r\n  return $self;\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  if ($self->{show}) {\r\n    my $type = undef;\r\n    foreach (split /\\n/, $self->{show}) {\r\n      if (/\\[(\\w+)\\((\\w+)\\)\\]\\s+(\\d+) node/) {\r\n        $type = uc $2;\r\n      } elsif (/id=(\\d+)(.*)/) {\r\n        push(@{$self->{nodes}}, DBD::MySQL::Cluster::Node->new(\r\n            type => $type,\r\n            id => $1,\r\n            status => $2,\r\n        ));\r\n      }\r\n    }\r\n  } else {\r\n  }\r\n  if ($params{mode} =~ /^cluster::ndbdrunning/) {\r\n    foreach my $node (@{$self->{nodes}}) {\r\n      $node->{type} eq "NDB" && $node->{status} eq "running" && $self->{ndbd_nodes}++;\r\n      $node->{type} eq "MGM" && $node->{status} eq "running" && $self->{ndb_mgmd_nodes}++;\r\n      $node->{type} eq "API" && $node->{status} eq "running" && $self->{mysqld_nodes}++;\r\n    }\r\n  } else {\r\n    printf "broken mode %s\\n", $params{mode};\r\n  }\r\n}\r\n\r\nsub dump {\r\n  my $self = shift;\r\n  my $message = shift || "";\r\n  printf "%s %s\\n", $message, Data::Dumper::Dumper($self);\r\n}\r\n\r\nsub nagios {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $dead_ndb = 0;\r\n  my $dead_api = 0;\r\n  if (! $self->{nagios_level}) {\r\n    if ($params{mode} =~ /^cluster::ndbdrunning/) {\r\n      foreach my $node (grep { $_->{type} eq "NDB"} @{$self->{nodes}}) {\r\n        next if $params{selectname} && $params{selectname} ne $_->{id};\r\n        if (! $node->{connected}) {\r\n          $self->add_nagios_critical(\r\n              sprintf "ndb node %d is not connected", $node->{id});\r\n          $dead_ndb++;\r\n        }\r\n      }\r\n      foreach my $node (grep { $_->{type} eq "API"} @{$self->{nodes}}) {\r\n        next if $params{selectname} && $params{selectname} ne $_->{id};\r\n        if (! $node->{connected}) {\r\n          $self->add_nagios_critical(\r\n              sprintf "api node %d is not connected", $node->{id});\r\n          $dead_api++;\r\n        }\r\n      }\r\n      if (! $dead_ndb) {\r\n        $self->add_nagios_ok("all ndb nodes are connected");\r\n      }\r\n      if (! $dead_api) {\r\n        $self->add_nagios_ok("all api nodes are connected");\r\n      }\r\n    }\r\n  }\r\n  $self->add_perfdata(sprintf "ndbd_nodes=%d ndb_mgmd_nodes=%d mysqld_nodes=%d",\r\n      $self->{ndbd_nodes}, $self->{ndb_mgmd_nodes}, $self->{mysqld_nodes});\r\n}\r\n\r\n\r\nsub init_nagios {\r\n  my $self = shift;\r\n  no strict ''refs'';\r\n  if (! ref($self)) {\r\n    my $nagiosvar = $self."::nagios";\r\n    my $nagioslevelvar = $self."::nagios_level";\r\n    $$nagiosvar = {\r\n      messages => {\r\n        0 => [],\r\n        1 => [],\r\n        2 => [],\r\n        3 => [],\r\n      },\r\n      perfdata => [],\r\n    };\r\n    $$nagioslevelvar = $ERRORS{OK},\r\n  } else {\r\n    $self->{nagios} = {\r\n      messages => {\r\n        0 => [],\r\n        1 => [],\r\n        2 => [],\r\n        3 => [],\r\n      },\r\n      perfdata => [],\r\n    };\r\n    $self->{nagios_level} = $ERRORS{OK},\r\n  }\r\n}\r\n\r\nsub check_thresholds {\r\n  my $self = shift;\r\n  my $value = shift;\r\n  my $defaultwarningrange = shift;\r\n  my $defaultcriticalrange = shift;\r\n  my $level = $ERRORS{OK};\r\n  $self->{warningrange} = $self->{warningrange} ?\r\n      $self->{warningrange} : $defaultwarningrange;\r\n  $self->{criticalrange} = $self->{criticalrange} ?\r\n      $self->{criticalrange} : $defaultcriticalrange;\r\n  if ($self->{warningrange} !~ /:/ && $self->{criticalrange} !~ /:/) {\r\n    # warning = 10, critical = 20, warn if > 10, crit if > 20\r\n    $level = $ERRORS{WARNING} if $value > $self->{warningrange};\r\n    $level = $ERRORS{CRITICAL} if $value > $self->{criticalrange};\r\n  } elsif ($self->{warningrange} =~ /([\\d\\.]+):/ && \r\n      $self->{criticalrange} =~ /([\\d\\.]+):/) {\r\n    # warning = 98:, critical = 95:, warn if < 98, crit if < 95\r\n    $self->{warningrange} =~ /([\\d\\.]+):/;\r\n    $level = $ERRORS{WARNING} if $value < $1;\r\n    $self->{criticalrange} =~ /([\\d\\.]+):/;\r\n    $level = $ERRORS{CRITICAL} if $value < $1;\r\n  }\r\n  return $level;\r\n  #\r\n  # syntax error must be reported with returncode -1\r\n  #\r\n}\r\n\r\nsub add_nagios {\r\n  my $self = shift;\r\n  my $level = shift;\r\n  my $message = shift;\r\n  push(@{$self->{nagios}->{messages}->{$level}}, $message);\r\n  # recalc current level\r\n  foreach my $llevel (qw(CRITICAL WARNING UNKNOWN OK)) {\r\n    if (scalar(@{$self->{nagios}->{messages}->{$ERRORS{$llevel}}})) {\r\n      $self->{nagios_level} = $ERRORS{$llevel};\r\n    }\r\n  }\r\n}\r\n\r\nsub add_nagios_ok {\r\n  my $self = shift;\r\n  my $message = shift;\r\n  $self->add_nagios($ERRORS{OK}, $message);\r\n}\r\n\r\nsub add_nagios_warning {\r\n  my $self = shift;\r\n  my $message = shift;\r\n  $self->add_nagios($ERRORS{WARNING}, $message);\r\n}\r\n\r\nsub add_nagios_critical {\r\n  my $self = shift;\r\n  my $message = shift;\r\n  $self->add_nagios($ERRORS{CRITICAL}, $message);\r\n}\r\n\r\nsub add_nagios_unknown {\r\n  my $self = shift;\r\n  my $message = shift;\r\n  $self->add_nagios($ERRORS{UNKNOWN}, $message);\r\n}\r\n\r\nsub add_perfdata {\r\n  my $self = shift;\r\n  my $data = shift;\r\n  push(@{$self->{nagios}->{perfdata}}, $data);\r\n}\r\n\r\nsub merge_nagios {\r\n  my $self = shift;\r\n  my $child = shift;\r\n  foreach my $level (0..3) {\r\n    foreach (@{$child->{nagios}->{messages}->{$level}}) {\r\n      $self->add_nagios($level, $_);\r\n    }\r\n    #push(@{$self->{nagios}->{messages}->{$level}},\r\n    #    @{$child->{nagios}->{messages}->{$level}});\r\n  }\r\n  push(@{$self->{nagios}->{perfdata}}, @{$child->{nagios}->{perfdata}});\r\n}\r\n\r\n\r\nsub calculate_result {\r\n  my $self = shift;\r\n  if ($ENV{NRPE_MULTILINESUPPORT} && \r\n      length join(" ", @{$self->{nagios}->{perfdata}}) > 200) {\r\n    foreach my $level ("CRITICAL", "WARNING", "UNKNOWN", "OK") {\r\n      # first the bad news\r\n      if (scalar(@{$self->{nagios}->{messages}->{$ERRORS{$level}}})) {\r\n        $self->{nagios_message} .=\r\n            "\\n".join("\\n", @{$self->{nagios}->{messages}->{$ERRORS{$level}}});\r\n      }\r\n    }\r\n    $self->{nagios_message} =~ s/^\\n//g;\r\n    $self->{perfdata} = join("\\n", @{$self->{nagios}->{perfdata}});\r\n  } else {\r\n    foreach my $level ("CRITICAL", "WARNING", "UNKNOWN", "OK") {\r\n      # first the bad news\r\n      if (scalar(@{$self->{nagios}->{messages}->{$ERRORS{$level}}})) {\r\n        $self->{nagios_message} .= \r\n            join(", ", @{$self->{nagios}->{messages}->{$ERRORS{$level}}}).", ";\r\n      }\r\n    }\r\n    $self->{nagios_message} =~ s/, $//g;\r\n    $self->{perfdata} = join(" ", @{$self->{nagios}->{perfdata}});\r\n  }\r\n  foreach my $level ("OK", "UNKNOWN", "WARNING", "CRITICAL") {\r\n    if (scalar(@{$self->{nagios}->{messages}->{$ERRORS{$level}}})) {\r\n      $self->{nagios_level} = $ERRORS{$level};\r\n    }\r\n  }\r\n}\r\n\r\nsub debug {\r\n  my $self = shift;\r\n  my $msg = shift;\r\n  if ($DBD::MySQL::Cluster::verbose) {\r\n    printf "%s %s\\n", $msg, ref($self);\r\n  }\r\n}\r\n\r\nsub connect {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $retval = undef;\r\n  $self->{tic} = Time::HiRes::time();\r\n  eval {\r\n    use POSIX '':signal_h'';\r\n    local $SIG{''ALRM''} = sub {\r\n      die "alarm\\n";\r\n    };\r\n    my $mask = POSIX::SigSet->new( SIGALRM );\r\n    my $action = POSIX::SigAction->new(\r\n        sub { die "connection timeout\\n" ; }, $mask);\r\n    my $oldaction = POSIX::SigAction->new();\r\n    sigaction(SIGALRM ,$action ,$oldaction );\r\n    alarm($self->{timeout} - 1); # 1 second before the global unknown timeout\r\n    my $ndb_mgm = "ndb_mgm";\r\n    $params{hostname} = "127.0.0.1" if ! $params{hostname};\r\n    $ndb_mgm .= sprintf " --ndb-connectstring=%s", $params{hostname}\r\n        if $params{hostname};\r\n    $ndb_mgm .= sprintf ":%d", $params{port}\r\n        if $params{port};\r\n    $self->{show} = `$ndb_mgm -e show 2>&1`;\r\n    if ($? == -1) {\r\n      $self->add_nagios_critical("ndb_mgm failed to execute $!");\r\n    } elsif ($? & 127) {\r\n      $self->add_nagios_critical("ndb_mgm failed to execute $!");\r\n    } elsif ($? >> 8 != 0) {\r\n      $self->add_nagios_critical("ndb_mgm unable to connect");\r\n    } else {\r\n      if ($self->{show} !~ /Cluster Configuration/) {\r\n        $self->add_nagios_critical("got no cluster configuration");\r\n      } else {\r\n        $retval = 1;\r\n      }\r\n    }\r\n  };\r\n  if ($@) {\r\n    $self->{errstr} = $@;\r\n    $self->{errstr} =~ s/at $0 .*//g;\r\n    chomp $self->{errstr};\r\n    $self->add_nagios_critical($self->{errstr});\r\n    $retval = undef;\r\n  }\r\n  $self->{tac} = Time::HiRes::time();\r\n  return $retval;\r\n}\r\n\r\nsub trace {\r\n  my $self = shift;\r\n  my $format = shift;\r\n  $self->{trace} = -f "/tmp/check_mysql_health.trace" ? 1 : 0;\r\n  if ($self->{verbose}) {\r\n    printf("%s: ", scalar localtime);\r\n    printf($format, @_);\r\n  }\r\n  if ($self->{trace}) {\r\n    my $logfh = new IO::File;\r\n    $logfh->autoflush(1);\r\n    if ($logfh->open("/tmp/check_mysql_health.trace", "a")) {\r\n      $logfh->printf("%s: ", scalar localtime);\r\n      $logfh->printf($format, @_);\r\n      $logfh->printf("\\n");\r\n      $logfh->close();\r\n    }\r\n  }\r\n}\r\n\r\nsub DESTROY {\r\n  my $self = shift;\r\n  my $handle1 = "null";\r\n  my $handle2 = "null";\r\n  if (defined $self->{handle}) {\r\n    $handle1 = ref($self->{handle});\r\n    if (defined $self->{handle}->{handle}) {\r\n      $handle2 = ref($self->{handle}->{handle});\r\n    }\r\n  }\r\n  $self->trace(sprintf "DESTROY %s with handle %s %s", ref($self), $handle1, $handle2);\r\n  if (ref($self) eq "DBD::MySQL::Cluster") {\r\n  }\r\n  $self->trace(sprintf "DESTROY %s exit with handle %s %s", ref($self), $handle1, $handle2);\r\n  if (ref($self) eq "DBD::MySQL::Cluster") {\r\n    #printf "humpftata\\n";\r\n  }\r\n}\r\n\r\nsub save_state {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $extension = "";\r\n  mkdir $params{statefilesdir} unless -d $params{statefilesdir};\r\n  my $statefile = sprintf "%s/%s_%s", \r\n      $params{statefilesdir}, $params{hostname}, $params{mode};\r\n  $extension .= $params{differenciator} ? "_".$params{differenciator} : "";\r\n  $extension .= $params{socket} ? "_".$params{socket} : "";\r\n  $extension .= $params{port} ? "_".$params{port} : "";\r\n  $extension .= $params{database} ? "_".$params{database} : "";\r\n  $extension .= $params{tablespace} ? "_".$params{tablespace} : "";\r\n  $extension .= $params{datafile} ? "_".$params{datafile} : "";\r\n  $extension .= $params{name} ? "_".$params{name} : "";\r\n  $extension =~ s/\\//_/g;\r\n  $extension =~ s/\\(/_/g;\r\n  $extension =~ s/\\)/_/g;\r\n  $extension =~ s/\\*/_/g;\r\n  $extension =~ s/\\s/_/g;\r\n  $statefile .= $extension;\r\n  $statefile = lc $statefile;\r\n  open(STATE, ">$statefile");\r\n  if ((ref($params{save}) eq "HASH") && exists $params{save}->{timestamp}) {\r\n    $params{save}->{localtime} = scalar localtime $params{save}->{timestamp};\r\n  }\r\n  printf STATE Data::Dumper::Dumper($params{save});\r\n  close STATE;\r\n  $self->debug(sprintf "saved %s to %s",\r\n      Data::Dumper::Dumper($params{save}), $statefile);\r\n}\r\n\r\nsub load_state {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  my $extension = "";\r\n  my $statefile = sprintf "%s/%s_%s", \r\n      $params{statefilesdir}, $params{hostname}, $params{mode};\r\n  $extension .= $params{differenciator} ? "_".$params{differenciator} : "";\r\n  $extension .= $params{socket} ? "_".$params{socket} : "";\r\n  $extension .= $params{port} ? "_".$params{port} : "";\r\n  $extension .= $params{database} ? "_".$params{database} : "";\r\n  $extension .= $params{tablespace} ? "_".$params{tablespace} : "";\r\n  $extension .= $params{datafile} ? "_".$params{datafile} : "";\r\n  $extension .= $params{name} ? "_".$params{name} : "";\r\n  $extension =~ s/\\//_/g;\r\n  $extension =~ s/\\(/_/g;\r\n  $extension =~ s/\\)/_/g;\r\n  $extension =~ s/\\*/_/g;\r\n  $extension =~ s/\\s/_/g;\r\n  $statefile .= $extension;\r\n  $statefile = lc $statefile;\r\n  if ( -f $statefile) {\r\n    our $VAR1;\r\n    eval {\r\n      require $statefile;\r\n    };\r\n    if($@) {\r\nprintf "rumms\\n";\r\n    }\r\n    $self->debug(sprintf "load %s", Data::Dumper::Dumper($VAR1));\r\n    return $VAR1;\r\n  } else {\r\n    return undef;\r\n  }\r\n}\r\n\r\nsub valdiff {\r\n  my $self = shift;\r\n  my $pparams = shift;\r\n  my %params = %{$pparams};\r\n  my @keys = @_;\r\n  my $last_values = $self->load_state(%params) || eval {\r\n    my $empty_events = {};\r\n    foreach (@keys) {\r\n      $empty_events->{$_} = 0;\r\n    }\r\n    $empty_events->{timestamp} = 0;\r\n    $empty_events;\r\n  };\r\n  foreach (@keys) {\r\n    $self->{''delta_''.$_} = $self->{$_} - $last_values->{$_};\r\n    $self->debug(sprintf "delta_%s %f", $_, $self->{''delta_''.$_});\r\n  }\r\n  $self->{''delta_timestamp''} = time - $last_values->{timestamp};\r\n  $params{save} = eval {\r\n    my $empty_events = {};\r\n    foreach (@keys) {\r\n      $empty_events->{$_} = $self->{$_};\r\n    }\r\n    $empty_events->{timestamp} = time;\r\n    $empty_events;\r\n  };\r\n  $self->save_state(%params);\r\n}\r\n\r\nsub requires_version {\r\n  my $self = shift;\r\n  my $version = shift;\r\n  my @instances = DBD::MySQL::Cluster::return_clusters();\r\n  my $instversion = $instances[0]->{version};\r\n  if (! $self->version_is_minimum($version)) {\r\n    $self->add_nagios($ERRORS{UNKNOWN}, \r\n        sprintf "not implemented/possible for MySQL release %s", $instversion);\r\n  }\r\n}\r\n\r\nsub version_is_minimum {\r\n  # the current version is newer or equal\r\n  my $self = shift;\r\n  my $version = shift;\r\n  my $newer = 1;\r\n  my @instances = DBD::MySQL::Cluster::return_clusters();\r\n  my @v1 = map { $_ eq "x" ? 0 : $_ } split(/\\./, $version);\r\n  my @v2 = split(/\\./, $instances[0]->{version});\r\n  if (scalar(@v1) > scalar(@v2)) {\r\n    push(@v2, (0) x (scalar(@v1) - scalar(@v2)));\r\n  } elsif (scalar(@v2) > scalar(@v1)) {\r\n    push(@v1, (0) x (scalar(@v2) - scalar(@v1)));\r\n  }\r\n  foreach my $pos (0..$#v1) {\r\n    if ($v2[$pos] > $v1[$pos]) {\r\n      $newer = 1;\r\n      last;\r\n    } elsif ($v2[$pos] < $v1[$pos]) {\r\n      $newer = 0;\r\n      last;\r\n    }\r\n  }\r\n  #printf STDERR "check if %s os minimum %s\\n", join(".", @v2), join(".", @v1);\r\n  return $newer;\r\n}\r\n\r\nsub instance_rac {\r\n  my $self = shift;\r\n  my @instances = DBD::MySQL::Cluster::return_clusters();\r\n  return (lc $instances[0]->{parallel} eq "yes") ? 1 : 0;\r\n}\r\n\r\nsub instance_thread {\r\n  my $self = shift;\r\n  my @instances = DBD::MySQL::Cluster::return_clusters();\r\n  return $instances[0]->{thread};\r\n}\r\n\r\nsub windows_cluster {\r\n  my $self = shift;\r\n  my @instances = DBD::MySQL::Cluster::return_clusters();\r\n  if ($instances[0]->{os} =~ /Win/i) {\r\n    return 1;\r\n  } else {\r\n    return 0;\r\n  }\r\n}\r\n\r\nsub system_vartmpdir {\r\n  my $self = shift;\r\n  if ($^O =~ /MSWin/) {\r\n    return $self->system_tmpdir();\r\n  } else {\r\n    return "/var/tmp/check_mysql_health";\r\n  }\r\n}\r\n\r\nsub system_oldvartmpdir {\r\n  my $self = shift;\r\n  return "/tmp";\r\n}\r\n\r\nsub system_tmpdir {\r\n  my $self = shift;\r\n  if ($^O =~ /MSWin/) {\r\n    return $ENV{TEMP} if defined $ENV{TEMP};\r\n    return $ENV{TMP} if defined $ENV{TMP};\r\n    return File::Spec->catfile($ENV{windir}, ''Temp'')\r\n        if defined $ENV{windir};\r\n    return ''C:\\Temp'';\r\n  } else {\r\n    return "/tmp";\r\n  }\r\n}\r\n\r\n\r\npackage DBD::MySQL::Cluster::Node;\r\n\r\nuse strict;\r\n\r\nour @ISA = qw(DBD::MySQL::Cluster);\r\n\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  my $self = {\r\n    mode => $params{mode},\r\n    timeout => $params{timeout},\r\n    type => $params{type},\r\n    id => $params{id},\r\n    status => $params{status},\r\n  };\r\n  bless $self, $class;\r\n  $self->init(%params);\r\n  if ($params{type} eq "NDB") {\r\n    bless $self, "DBD::MySQL::Cluster::Node::NDB";\r\n    $self->init(%params);\r\n  }\r\n  return $self;\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  if ($self->{status} =~ /@(\\d+\\.\\d+\\.\\d+\\.\\d+)\\s/) {\r\n    $self->{addr} = $1;\r\n    $self->{connected} = 1;\r\n  } elsif ($self->{status} =~ /accepting connect from (\\d+\\.\\d+\\.\\d+\\.\\d+)/) {\r\n    $self->{addr} = $1;\r\n    $self->{connected} = 0;\r\n  }\r\n  if ($self->{status} =~ /starting,/) {\r\n    $self->{status} = "starting";\r\n  } elsif ($self->{status} =~ /shutting,/) {\r\n    $self->{status} = "shutting";\r\n  } else {\r\n    $self->{status} = $self->{connected} ? "running" : "dead";\r\n  }\r\n}\r\n\r\n\r\npackage DBD::MySQL::Cluster::Node::NDB;\r\n\r\nuse strict;\r\n\r\nour @ISA = qw(DBD::MySQL::Cluster::Node);\r\n\r\n\r\nsub init {\r\n  my $self = shift;\r\n  my %params = @_;\r\n  if ($self->{status} =~ /Nodegroup:\\s*(\\d+)/) {\r\n    $self->{nodegroup} = $1;\r\n  }\r\n  $self->{master} = ($self->{status} =~ /Master\\)/) ? 1 : 0;\r\n}\r\n\r\n\r\npackage Extraopts;\r\n\r\nuse strict;\r\nuse File::Basename;\r\nuse Data::Dumper;\r\n\r\nsub new {\r\n  my $class = shift;\r\n  my %params = @_;\r\n  my $self = {\r\n    file => $params{file},\r\n    commandline => $params{commandline},\r\n    config => {},\r\n    section => ''default_no_section'',\r\n  };\r\n  bless $self, $class;\r\n  $self->prepare_file_and_section();\r\n  $self->init();\r\n  return $self;\r\n}\r\n\r\nsub prepare_file_and_section {\r\n  my $self = shift;\r\n  if (! defined $self->{file}) {\r\n    # ./check_stuff --extra-opts\r\n    $self->{section} = basename($0);\r\n    $self->{file} = $self->get_default_file();\r\n  } elsif ($self->{file} =~ /^[^@]+$/) {\r\n    # ./check_stuff --extra-opts=special_opts\r\n    $self->{section} = $self->{file};\r\n    $self->{file} = $self->get_default_file();\r\n  } elsif ($self->{file} =~ /^@(.*)/) {\r\n    # ./check_stuff --extra-opts=@/etc/myconfig.ini\r\n    $self->{section} = basename($0);\r\n    $self->{file} = $1;\r\n  } elsif ($self->{file} =~ /^(.*?)@(.*)/) {\r\n    # ./check_stuff --extra-opts=special_opts@/etc/myconfig.ini\r\n    $self->{section} = $1;\r\n    $self->{file} = $2;\r\n  }\r\n}\r\n\r\nsub get_default_file {\r\n  my $self = shift;\r\n  foreach my $default (qw(/etc/nagios/plugins.ini\r\n      /usr/local/nagios/etc/plugins.ini\r\n      /usr/local/etc/nagios/plugins.ini\r\n      /etc/opt/nagios/plugins.ini\r\n      /etc/nagios-plugins.ini\r\n      /usr/local/etc/nagios-plugins.ini\r\n      /etc/opt/nagios-plugins.ini)) {\r\n    if (-f $default) {\r\n      return $default;\r\n    }\r\n  }\r\n  return undef;\r\n}\r\n\r\nsub init {\r\n  my $self = shift;\r\n  if (! defined $self->{file}) {\r\n    $self->{errors} = sprintf ''no extra-opts file specified and no default file found'';\r\n  } elsif (! -f $self->{file}) {\r\n    $self->{errors} = sprintf ''could not open %s'', $self->{file};\r\n  } else {\r\n    my $data = do { local (@ARGV, $/) = $self->{file}; <> };\r\n    my $in_section = ''default_no_section'';\r\n    foreach my $line (split(/\\n/, $data)) {\r\n      if ($line =~ /\\[(.*)\\]/) {\r\n        $in_section = $1;\r\n      } elsif ($line =~ /(.*?)\\s*=\\s*(.*)/) {\r\n        $self->{config}->{$in_section}->{$1} = $2;\r\n      }\r\n    }\r\n  }\r\n}\r\n\r\nsub is_valid {\r\n  my $self = shift;\r\n  return ! exists $self->{errors};\r\n}\r\n\r\nsub overwrite {\r\n  my $self = shift;\r\n  my %commandline = ();\r\n  if (scalar(keys %{$self->{config}->{default_no_section}}) > 0) {\r\n    foreach (keys %{$self->{config}->{default_no_section}}) {\r\n      $commandline{$_} = $self->{config}->{default_no_section}->{$_};\r\n    }\r\n  }\r\n  if (exists $self->{config}->{$self->{section}}) {\r\n    foreach (keys %{$self->{config}->{$self->{section}}}) {\r\n      $commandline{$_} = $self->{config}->{$self->{section}}->{$_};\r\n    }\r\n  }\r\n  foreach (keys %commandline) {\r\n    if (! exists $self->{commandline}->{$_}) {\r\n      $self->{commandline}->{$_} = $commandline{$_};\r\n    }\r\n  }\r\n}\r\n\r\n\r\n\r\npackage main;\r\n\r\nuse strict;\r\nuse Getopt::Long qw(:config no_ignore_case);\r\nuse File::Basename;\r\nuse lib dirname($0);\r\n\r\n\r\n\r\nuse vars qw ($PROGNAME $REVISION $CONTACT $TIMEOUT $STATEFILESDIR $needs_restart %commandline);\r\n\r\n$PROGNAME = "check_mysql_health";\r\n$REVISION = ''$Revision: 2.2 $'';\r\n$CONTACT = ''gerhard.lausser@consol.de'';\r\n$TIMEOUT = 60;\r\n$STATEFILESDIR = ''/var/tmp/check_mysql_health'';\r\n$needs_restart = 0;\r\n\r\nmy @modes = (\r\n  [''server::connectiontime'',\r\n      ''connection-time'', undef,\r\n      ''Time to connect to the server'' ],\r\n  [''server::uptime'',\r\n      ''uptime'', undef,\r\n      ''Time the server is running'' ],\r\n  [''server::instance::connectedthreads'',\r\n      ''threads-connected'', undef,\r\n      ''Number of currently open connections'' ],\r\n  [''server::instance::threadcachehitrate'',\r\n      ''threadcache-hitrate'', undef,\r\n      ''Hit rate of the thread-cache'' ],\r\n  [''server::instance::createdthreads'',\r\n      ''threads-created'', undef,\r\n      ''Number of threads created per sec'' ],\r\n  [''server::instance::runningthreads'',\r\n      ''threads-running'', undef,\r\n      ''Number of currently running threads'' ],\r\n  [''server::instance::cachedthreads'',\r\n      ''threads-cached'', undef,\r\n      ''Number of currently cached threads'' ],\r\n  [''server::instance::abortedconnects'',\r\n      ''connects-aborted'', undef,\r\n      ''Number of aborted connections per sec'' ],\r\n  [''server::instance::abortedclients'',\r\n      ''clients-aborted'', undef,\r\n      ''Number of aborted connections (because the client died) per sec'' ],\r\n  [''server::instance::replication::slavelag'',\r\n      ''slave-lag'', [''replication-slave-lag''],\r\n      ''Seconds behind master'' ],\r\n  [''server::instance::replication::slaveiorunning'',\r\n      ''slave-io-running'', [''replication-slave-io-running''],\r\n      ''Slave io running: Yes'' ],\r\n  [''server::instance::replication::slavesqlrunning'',\r\n      ''slave-sql-running'', [''replication-slave-sql-running''],\r\n      ''Slave sql running: Yes'' ],\r\n  [''server::instance::querycachehitrate'',\r\n      ''qcache-hitrate'', [''querycache-hitrate''],\r\n      ''Query cache hitrate'' ],\r\n  [''server::instance::querycachelowmemprunes'',\r\n      ''qcache-lowmem-prunes'', [''querycache-lowmem-prunes''],\r\n      ''Query cache entries pruned because of low memory'' ],\r\n  [''server::instance::myisam::keycache::hitrate'',\r\n      ''keycache-hitrate'', [''myisam-keycache-hitrate''],\r\n      ''MyISAM key cache hitrate'' ],\r\n  [''server::instance::innodb::bufferpool::hitrate'',\r\n      ''bufferpool-hitrate'', [''innodb-bufferpool-hitrate''],\r\n      ''InnoDB buffer pool hitrate'' ],\r\n  [''server::instance::innodb::bufferpool::waitfree'',\r\n      ''bufferpool-wait-free'', [''innodb-bufferpool-wait-free''],\r\n      ''InnoDB buffer pool waits for clean page available'' ],\r\n  [''server::instance::innodb::logwaits'',\r\n      ''log-waits'', [''innodb-log-waits''],\r\n      ''InnoDB log waits because of a too small log buffer'' ],\r\n  [''server::instance::tablecachehitrate'',\r\n      ''tablecache-hitrate'', undef,\r\n      ''Table cache hitrate'' ],\r\n  [''server::instance::tablelockcontention'',\r\n      ''table-lock-contention'', undef,\r\n      ''Table lock contention'' ],\r\n  [''server::instance::tableindexusage'',\r\n      ''index-usage'', undef,\r\n      ''Usage of indices'' ],\r\n  [''server::instance::tabletmpondisk'',\r\n      ''tmp-disk-tables'', undef,\r\n      ''Percent of temp tables created on disk'' ],\r\n  [''server::instance::needoptimize'',\r\n      ''table-fragmentation'', undef,\r\n      ''Show tables which should be optimized'' ],\r\n  [''server::instance::openfiles'',\r\n      ''open-files'', undef,\r\n      ''Percent of opened files'' ],\r\n  [''server::instance::slowqueries'',\r\n      ''slow-queries'', undef,\r\n      ''Slow queries'' ],\r\n  [''server::instance::longprocs'',\r\n      ''long-running-procs'', undef,\r\n      ''long running processes'' ],\r\n  [''cluster::ndbdrunning'',\r\n      ''cluster-ndbd-running'', undef,\r\n      ''ndnd nodes are up and running'' ],\r\n  [''server::sql'',\r\n      ''sql'', undef,\r\n      ''any sql command returning a single number'' ],\r\n);\r\n\r\n# rrd data store names are limited to 19 characters\r\nmy %labels = (\r\n  bufferpool_hitrate => {\r\n    groundwork => ''bp_hitrate'',\r\n  },\r\n  bufferpool_hitrate_now => {\r\n    groundwork => ''bp_hitrate_now'',\r\n  },\r\n  bufferpool_free_waits_rate => {\r\n    groundwork => ''bp_freewaits'',\r\n  },\r\n  innodb_log_waits_rate => {\r\n    groundwork => ''inno_log_waits'',\r\n  },\r\n  keycache_hitrate => {\r\n    groundwork => ''kc_hitrate'',\r\n  },\r\n  keycache_hitrate_now => {\r\n    groundwork => ''kc_hitrate_now'',\r\n  },\r\n  threads_created_per_sec => {\r\n    groundwork => ''thrds_creat_per_s'',\r\n  },\r\n  connects_aborted_per_sec => {\r\n    groundwork => ''conn_abrt_per_s'',\r\n  },\r\n  clients_aborted_per_sec => {\r\n    groundwork => ''clnt_abrt_per_s'',\r\n  },\r\n  thread_cache_hitrate => {\r\n    groundwork => ''tc_hitrate'',\r\n  },\r\n  thread_cache_hitrate_now => {\r\n    groundwork => ''tc_hitrate_now'',\r\n  },\r\n  qcache_lowmem_prunes_rate => {\r\n    groundwork => ''qc_lowm_prnsrate'',\r\n  },\r\n  slow_queries_rate => {\r\n    groundwork => ''slow_q_rate'',\r\n  },\r\n  tablecache_hitrate => {\r\n    groundwork => ''tac_hitrate'',\r\n  },\r\n  tablecache_fillrate => {\r\n    groundwork => ''tac_fillrate'',\r\n  },\r\n  tablelock_contention => {\r\n    groundwork => ''tl_contention'',\r\n  },\r\n  tablelock_contention_now => {\r\n    groundwork => ''tl_contention_now'',\r\n  },\r\n  pct_tmp_table_on_disk => {\r\n    groundwork => ''tmptab_on_disk'',\r\n  },\r\n  pct_tmp_table_on_disk_now => {\r\n    groundwork => ''tmptab_on_disk_now'',\r\n  },\r\n);\r\n\r\nsub print_usage () {\r\n  print <<EOUS;\r\n  Usage:\r\n    $PROGNAME [-v] [-t <timeout>] [[--hostname <hostname>] \r\n        [--port <port> | --socket <socket>]\r\n        --username <username> --password <password>] --mode <mode>\r\n        [--method mysql]\r\n    $PROGNAME [-h | --help]\r\n    $PROGNAME [-V | --version]\r\n\r\n  Options:\r\n    --hostname\r\n       the database server''s hostname\r\n    --port\r\n       the database''s port. (default: 3306)\r\n    --socket\r\n       the database''s unix socket.\r\n    --username\r\n       the mysql db user\r\n    --password\r\n       the mysql db user''s password\r\n    --database\r\n       the database''s name. (default: information_schema)\r\n    --replication-user\r\n       the database''s replication user name (default: replication)\r\n    --warning\r\n       the warning range\r\n    --critical\r\n       the critical range\r\n    --mode\r\n       the mode of the plugin. select one of the following keywords:\r\nEOUS\r\n  my $longest = length ((reverse sort {length $a <=> length $b} map { $_->[1] } @modes)[0]);\r\n  my $format = "       %-".\r\n  (length ((reverse sort {length $a <=> length $b} map { $_->[1] } @modes)[0])).\r\n  "s\\t(%s)\\n";\r\n  foreach (@modes) {\r\n    printf $format, $_->[1], $_->[3];\r\n  }\r\n  printf "\\n";\r\n  print <<EOUS;\r\n    --name\r\n       the name of something that needs to be further specified,\r\n       currently only used for sql statements\r\n    --name2\r\n       if name is a sql statement, this statement would appear in\r\n       the output and the performance data. This can be ugly, so \r\n       name2 can be used to appear instead.\r\n    --regexp\r\n       if this parameter is used, name will be interpreted as a \r\n       regular expression.\r\n    --units\r\n       one of %, KB, MB, GB. This is used for a better output of mode=sql\r\n       and for specifying thresholds for mode=tablespace-free\r\n    --labelformat\r\n       one of pnp4nagios (which is the default) or groundwork.\r\n       It is used to shorten performance data labels to 19 characters.\r\n\r\n  In mode sql you can url-encode the statement so you will not have to mess\r\n  around with special characters in your Nagios service definitions.\r\n  Instead of \r\n  --name="select count(*) from v\\$session where status = ''ACTIVE''"\r\n  you can say \r\n  --name=select%20count%28%2A%29%20from%20v%24session%20where%20status%20%3D%20%27ACTIVE%27\r\n  For your convenience you can call check_mysql_health with the --mode encode\r\n  option and it will encode the standard input.\r\n\r\n  You can find the full documentation at \r\n  http://www.consol.de/opensource/nagios/check-mysql-health\r\n\r\nEOUS\r\n  \r\n}\r\n\r\nsub print_help () {\r\n  print "Copyright (c) 2009 Gerhard Lausser\\n\\n";\r\n  print "\\n";\r\n  print "  Check various parameters of MySQL databases \\n";\r\n  print "\\n";\r\n  print_usage();\r\n  support();\r\n}\r\n\r\n\r\nsub print_revision ($$) {\r\n  my $commandName = shift;\r\n  my $pluginRevision = shift;\r\n  $pluginRevision =~ s/^\\$Revision: //;\r\n  $pluginRevision =~ s/ \\$\\s*$//;\r\n  print "$commandName ($pluginRevision)\\n";\r\n  print "This nagios plugin comes with ABSOLUTELY NO WARRANTY. You may redistribute\\ncopies of this plugin under the terms of the GNU General Public License.\\n";\r\n}\r\n\r\nsub support () {\r\n  my $support=''Send email to gerhard.lausser@consol.de if you have questions\\nregarding use of this software. \\nPlease include version information with all correspondence (when possible,\\nuse output from the --version option of the plugin itself).\\n'';\r\n  $support =~ s/@/\\@/g;\r\n  $support =~ s/\\\\n/\\n/g;\r\n  print $support;\r\n}\r\n\r\nsub contact_author ($$) {\r\n  my $item = shift;\r\n  my $strangepattern = shift;\r\n  if ($commandline{verbose}) {\r\n    printf STDERR\r\n        "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\\n".\r\n        "You found a line which is not recognized by %s\\n".\r\n        "This means, certain components of your system cannot be checked.\\n".\r\n        "Please contact the author %s and\\nsend him the following output:\\n\\n".\r\n        "%s /%s/\\n\\nThank you!\\n".\r\n        "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\\n",\r\n            $PROGNAME, $CONTACT, $item, $strangepattern;\r\n  }\r\n}\r\n\r\n%commandline = ();\r\nmy @params = (\r\n    "timeout|t=i",\r\n    "version|V",\r\n    "help|h",\r\n    "verbose|v",\r\n    "debug|d",\r\n    "hostname|H=s",\r\n    "database=s",\r\n    "port|P=s",\r\n    "socket|S=s",\r\n    "username|u=s",\r\n    "password|p=s",\r\n    "replication-user=s",\r\n    "mycnf=s",\r\n    "mycnfgroup=s",\r\n    "mode|m=s",\r\n    "name=s",\r\n    "name2=s",\r\n    "regexp",\r\n    "perfdata",\r\n    "warning=s",\r\n    "critical=s",\r\n    "dbthresholds:s",\r\n    "absolute|a",\r\n    "environment|e=s%",\r\n    "negate=s%",\r\n    "method=s",\r\n    "runas|r=s",\r\n    "scream",\r\n    "shell",\r\n    "eyecandy",\r\n    "encode",\r\n    "units=s",\r\n    "lookback=i",\r\n    "3",\r\n    "statefilesdir=s",\r\n    "with-mymodules-dyn-dir=s",\r\n    "report=s",\r\n    "labelformat=s",\r\n    "extra-opts:s");\r\n\r\nif (! GetOptions(\\%commandline, @params)) {\r\n  print_help();\r\n  exit $ERRORS{UNKNOWN};\r\n}\r\n\r\nif (exists $commandline{''extra-opts''}) {\r\n  # read the extra file and overwrite other parameters\r\n  my $extras = Extraopts->new(file => $commandline{''extra-opts''}, commandline =>\r\n \\%commandline);\r\n  if (! $extras->is_valid()) {\r\n    printf "extra-opts are not valid: %s\\n", $extras->{errors};\r\n    exit $ERRORS{UNKNOWN};\r\n  } else {\r\n    $extras->overwrite();\r\n  }\r\n}\r\n\r\nif (exists $commandline{version}) {\r\n  print_revision($PROGNAME, $REVISION);\r\n  exit $ERRORS{OK};\r\n}\r\n\r\nif (exists $commandline{help}) {\r\n  print_help();\r\n  exit $ERRORS{OK};\r\n} elsif (! exists $commandline{mode}) {\r\n  printf "Please select a mode\\n";\r\n  print_help();\r\n  exit $ERRORS{OK};\r\n}\r\n\r\nif ($commandline{mode} eq "encode") {\r\n  my $input = <>;\r\n  chomp $input;\r\n  $input =~ s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;\r\n  printf "%s\\n", $input;\r\n  exit $ERRORS{OK};\r\n}\r\n\r\nif (exists $commandline{3}) {\r\n  $ENV{NRPE_MULTILINESUPPORT} = 1;\r\n}\r\n\r\nif (exists $commandline{timeout}) {\r\n  $TIMEOUT = $commandline{timeout};\r\n}\r\n\r\nif (exists $commandline{verbose}) {\r\n  $DBD::MySQL::Server::verbose = exists $commandline{verbose};\r\n}\r\n\r\nif (exists $commandline{scream}) {\r\n#  $DBD::MySQL::Server::hysterical = exists $commandline{scream};\r\n}\r\n\r\nif (exists $commandline{method}) {\r\n  # snmp or mysql cmdline\r\n} else {\r\n  $commandline{method} = "dbi";\r\n}\r\n\r\nif (exists $commandline{report}) {\r\n  # short, long, html\r\n} else {\r\n  $commandline{report} = "long";\r\n}\r\n\r\nif (exists $commandline{labelformat}) {\r\n  # groundwork\r\n} else {\r\n  $commandline{labelformat} = "pnp4nagios";\r\n}\r\n\r\nif (exists $commandline{''with-mymodules-dyn-dir''}) {\r\n  $DBD::MySQL::Server::my_modules_dyn_dir = $commandline{''with-mymodules-dyn-dir''};\r\n} else {\r\n  $DBD::MySQL::Server::my_modules_dyn_dir = ''/usr/local/nagios/libexec'';\r\n}\r\n\r\nif (exists $commandline{environment}) {\r\n  # if the desired environment variable values are different from\r\n  # the environment of this running script, then a restart is necessary.\r\n  # because setting $ENV does _not_ change the environment of the running script.\r\n  foreach (keys %{$commandline{environment}}) {\r\n    if ((! $ENV{$_}) || ($ENV{$_} ne $commandline{environment}->{$_})) {\r\n      $needs_restart = 1;\r\n      $ENV{$_} = $commandline{environment}->{$_};\r\n      printf STDERR "new %s=%s forces restart\\n", $_, $ENV{$_} \r\n          if $DBD::MySQL::Server::verbose;\r\n    }\r\n  }\r\n  # e.g. called with --runas dbnagio. shlib_path environment variable is stripped\r\n  # during the sudo.\r\n  # so the perl interpreter starts without a shlib_path. but --runas cares for\r\n  # a --environment shlib_path=...\r\n  # so setting the environment variable in the code above and restarting the \r\n  # perl interpreter will help it find shared libs\r\n}\r\n\r\nif (exists $commandline{runas}) {\r\n  # remove the runas parameter\r\n  # exec sudo $0 ... the remaining parameters\r\n  $needs_restart = 1;\r\n  # if the calling script has a path for shared libs and there is no --environment\r\n  # parameter then the called script surely needs the variable too.\r\n  foreach my $important_env (qw(LD_LIBRARY_PATH SHLIB_PATH \r\n      ORACLE_HOME TNS_ADMIN ORA_NLS ORA_NLS33 ORA_NLS10)) {\r\n    if ($ENV{$important_env} && ! scalar(grep { /^$important_env=/ } \r\n        keys %{$commandline{environment}})) {\r\n      $commandline{environment}->{$important_env} = $ENV{$important_env};\r\n      printf STDERR "add important --environment %s=%s\\n", \r\n          $important_env, $ENV{$important_env} if $DBD::MySQL::Server::verbose;\r\n    }\r\n  }\r\n}\r\n\r\nif ($needs_restart) {\r\n  my @newargv = ();\r\n  my $runas = undef;\r\n  if (exists $commandline{runas}) {\r\n    $runas = $commandline{runas};\r\n    delete $commandline{runas};\r\n  }\r\n  foreach my $option (keys %commandline) {\r\n    if (grep { /^$option/ && /=/ } @params) {\r\n      if (ref ($commandline{$option}) eq "HASH") {\r\n        foreach (keys %{$commandline{$option}}) {\r\n          push(@newargv, sprintf "--%s", $option);\r\n          push(@newargv, sprintf "%s=%s", $_, $commandline{$option}->{$_});\r\n        }\r\n      } else {\r\n        push(@newargv, sprintf "--%s", $option);\r\n        push(@newargv, sprintf "%s", $commandline{$option});\r\n      }\r\n    } else {\r\n      push(@newargv, sprintf "--%s", $option);\r\n    }\r\n  }\r\n  if ($runas) {\r\n    exec "sudo", "-S", "-u", $runas, $0, @newargv;\r\n  } else {\r\n    exec $0, @newargv;  \r\n    # this makes sure that even a SHLIB or LD_LIBRARY_PATH are set correctly\r\n    # when the perl interpreter starts. Setting them during runtime does not\r\n    # help loading e.g. libclntsh.so\r\n  }\r\n  exit;\r\n}\r\n\r\nif (exists $commandline{shell}) {\r\n  # forget what you see here.\r\n  system("/bin/sh");\r\n}\r\n\r\nif (! exists $commandline{statefilesdir}) {\r\n  if (exists $ENV{OMD_ROOT}) {\r\n    $commandline{statefilesdir} = $ENV{OMD_ROOT}."/var/tmp/check_mysql_health";\r\n  } else {\r\n    $commandline{statefilesdir} = $STATEFILESDIR;\r\n  }\r\n}\r\n\r\nif (exists $commandline{name}) {\r\n  if ($^O =~ /MSWin/ && $commandline{name} =~ /^''(.*)''$/) {\r\n    # putting arguments in single ticks under Windows CMD leaves the '' intact\r\n    # we remove them\r\n    $commandline{name} = $1;\r\n  }\r\n  # objects can be encoded like an url\r\n  # with s/([^A-Za-z0-9])/sprintf("%%%02X", ord($1))/seg;\r\n  if (($commandline{mode} ne "sql") || \r\n      (($commandline{mode} eq "sql") &&\r\n       ($commandline{name} =~ /select%20/i))) { # protect ... like ''%cac%'' ... from decoding\r\n    $commandline{name} =~ s/\\%([A-Fa-f0-9]{2})/pack(''C'', hex($1))/seg;\r\n  }\r\n  if ($commandline{name} =~ /^0$/) {\r\n    # without this, $params{selectname} would be treated like undef\r\n    $commandline{name} = "00";\r\n  } \r\n}\r\n\r\n$SIG{''ALRM''} = sub {\r\n  printf "UNKNOWN - %s timed out after %d seconds\\n", $PROGNAME, $TIMEOUT;\r\n  exit $ERRORS{UNKNOWN};\r\n};\r\nalarm($TIMEOUT);\r\n\r\nmy $nagios_level = $ERRORS{UNKNOWN};\r\nmy $nagios_message = "";\r\nmy $perfdata = "";\r\nif ($commandline{mode} =~ /^my-([^\\-.]+)/) {\r\n  my $param = $commandline{mode};\r\n  $param =~ s/\\-/::/g;\r\n  push(@modes, [$param, $commandline{mode}, undef, ''my extension'']);\r\n} elsif ((! grep { $commandline{mode} eq $_ } map { $_->[1] } @modes) &&\r\n    (! grep { $commandline{mode} eq $_ } map { defined $_->[2] ? @{$_->[2]} : () } @modes)) {\r\n  printf "UNKNOWN - mode %s\\n", $commandline{mode};\r\n  print_usage();\r\n  exit 3;\r\n}\r\nmy %params = (\r\n    timeout => $TIMEOUT,\r\n    mode => (\r\n        map { $_->[0] }\r\n        grep {\r\n           ($commandline{mode} eq $_->[1]) ||\r\n           ( defined $_->[2] && grep { $commandline{mode} eq $_ } @{$_->[2]})\r\n        } @modes\r\n    )[0],\r\n    cmdlinemode => $commandline{mode},\r\n    method => $commandline{method} ||\r\n        $ENV{NAGIOS__SERVICEMYSQL_METH} ||\r\n        $ENV{NAGIOS__HOSTMYSQL_METH} || ''dbi'',\r\n    hostname => $commandline{hostname} || \r\n        $ENV{NAGIOS__SERVICEMYSQL_HOST} ||\r\n        $ENV{NAGIOS__HOSTMYSQL_HOST} || ''localhost'',\r\n    database => $commandline{database} || \r\n        $ENV{NAGIOS__SERVICEMYSQL_DATABASE} ||\r\n        $ENV{NAGIOS__HOSTMYSQL_DATABASE} || ''information_schema'',\r\n    port => $commandline{port}  || (($commandline{mode} =~ /^cluster/) ?\r\n        ($ENV{NAGIOS__SERVICENDBMGM_PORT} || $ENV{NAGIOS__HOSTNDBMGM_PORT} || 1186) :\r\n        ($ENV{NAGIOS__SERVICEMYSQL_PORT} || $ENV{NAGIOS__HOSTMYSQL_PORT} || 3306)),\r\n    socket => $commandline{socket}  || \r\n        $ENV{NAGIOS__SERVICEMYSQL_SOCKET} ||\r\n        $ENV{NAGIOS__HOSTMYSQL_SOCKET},\r\n    username => $commandline{username} || \r\n        $ENV{NAGIOS__SERVICEMYSQL_USER} ||\r\n        $ENV{NAGIOS__HOSTMYSQL_USER},\r\n    password => $commandline{password} || \r\n        $ENV{NAGIOS__SERVICEMYSQL_PASS} ||\r\n        $ENV{NAGIOS__HOSTMYSQL_PASS},\r\n    replication_user => $commandline{''replication-user''} || ''replication'',\r\n    mycnf => $commandline{mycnf} || \r\n        $ENV{NAGIOS__SERVICEMYSQL_MYCNF} ||\r\n        $ENV{NAGIOS__HOSTMYSQL_MYCNF},\r\n    mycnfgroup => $commandline{mycnfgroup} || \r\n        $ENV{NAGIOS__SERVICEMYSQL_MYCNFGROUP} ||\r\n        $ENV{NAGIOS__HOSTMYSQL_MYCNFGROUP},\r\n    warningrange => $commandline{warning},\r\n    criticalrange => $commandline{critical},\r\n    dbthresholds => $commandline{dbthresholds},\r\n    absolute => $commandline{absolute},\r\n    lookback => $commandline{lookback},\r\n    selectname => $commandline{name} || $commandline{tablespace} || $commandline{datafile},\r\n    regexp => $commandline{regexp},\r\n    name => $commandline{name},\r\n    name2 => $commandline{name2} || $commandline{name},\r\n    units => $commandline{units},\r\n    lookback => $commandline{lookback} || 0,\r\n    eyecandy => $commandline{eyecandy},\r\n    statefilesdir => $commandline{statefilesdir},\r\n    verbose => $commandline{verbose},\r\n    report => $commandline{report},\r\n    labelformat => $commandline{labelformat},\r\n    negate => $commandline{negate},\r\n);\r\n\r\nmy $server = undef;\r\nmy $cluster = undef;\r\n\r\nif ($params{mode} =~ /^(server|my)/) {\r\n  $server = DBD::MySQL::Server->new(%params);\r\n  $server->nagios(%params);\r\n  $server->calculate_result(\\%labels);\r\n  $nagios_message = $server->{nagios_message};\r\n  $nagios_level = $server->{nagios_level};\r\n  $perfdata = $server->{perfdata};\r\n} elsif ($params{mode} =~ /^cluster/) {\r\n  $cluster = DBD::MySQL::Cluster->new(%params);\r\n  $cluster->nagios(%params);\r\n  $cluster->calculate_result(\\%labels);\r\n  $nagios_message = $cluster->{nagios_message};\r\n  $nagios_level = $cluster->{nagios_level};\r\n  $perfdata = $cluster->{perfdata};\r\n}\r\n\r\nprintf "%s - %s", $ERRORCODES{$nagios_level}, $nagios_message;\r\nprintf " | %s", $perfdata if $perfdata;\r\nprintf "\\n";\r\nexit $nagios_level;\r\n\r\n\r\n__END__\r\n', '2015-11-05 08:49:30');
INSERT INTO `imp_scripts` (`id`, `title`, `script`, `Time`) VALUES
(3, 'DDOS Preventing Script Perl Netstat', '#!/usr/bin/perl -w\r\n#\r\n# @File ddos.pl\r\n# @Author Amrit\r\n# @Created Sep 2, 2015 12:16:21 PM\r\n# This Script is for preventing DDOS Attack\r\n$date = `date +"%m-%d-%y-%T"`;\r\nchomp($date);\r\n#print $date;\r\n`netstat -antu|awk ''NR>2''|awk ''{print \\$5}''|grep -v ''*''|cut -d: -f1|sort|uniq -c|sort -n -r|head -20 > /root/scripts/sample.txt`;\r\n\r\nopen(han1, "cat /root/scripts/sample.txt|awk ''{print \\$1}''|") or die "can not open file";\r\nopen(han2, "cat /root/scripts/sample.txt|awk ''{print \\$2}''|") or die "can not open file";\r\nopen(han3, "cat /root/scripts/ignoreip.txt|") or die "can not open file";\r\n@num = <han1>;\r\n@ip = <han2>;\r\n@ignore = <han3>;\r\n\r\nchomp(@num);\r\nchomp(@ip);\r\nchomp(@ignore);\r\n\r\n$max_conn = 200;\r\n\r\nfor($i=0;$i<=$#num;$i++){\r\n################### Check the connections are Exceeding the Limit or Not\r\n    \r\n    if($num[$i] >= $max_conn ){\r\n    print $date." - Following IP has Crossed Limit - ".$max_conn."\\n";\r\n    print $date." - ".$ip[$i]." - ".$num[$i]."\\n";\r\n            \r\n        if(grep /$ip[$i]/, @ignore){\r\n            print $date." - ignore it - ".$ip[$i]."\\n";\r\n        }\r\n            else{\r\n##### get Ip and Number of connections into Array;\r\n                push @black_count, $num[$i];\r\n                push @black_ip, $ip[$i];\r\n                    }\r\n        \r\n    }\r\n    \r\n\r\n}\r\nif(@black_ip){\r\n$msg = "Warning........................"."\\n\\n";\r\n$msg .= "Following IP has been Blocked\\n"."\\n\\n";\r\n\r\nfor($i=0;$i<=$#black_ip;$i++) {\r\n$msg .= "IP - ".$black_ip[$i]." Has ".$black_count[$i]." Connections "."\\n\\n"; \r\n`firewall-cmd --direct --add-rule ipv4 filter INPUT_direct 0 -s $black_ip[$i]/32 -j DROP`;\r\n\r\n}\r\n\r\n$sub = "IP BLocked 10.222.1.201.GrootLIVE";\r\n\r\n\r\n`/usr/bin/sendEmail -s smtp.capitalvia.com:587 -xu "amrit.sharma\\@capitalvia.com" -xp Nector145@ -m "$msg" -u "$sub" -t "amrit.sharma\\@capitalvia.com" -f "amrit.sharma\\@capitalvia.com"`;\r\nprint $date." - Email Sent Successfully\\n\\n";\r\n\r\n\r\n\r\n}\r\nelse{\r\nprint $date." - No Ip was Found \\n\\n";\r\n}\r\n', '2015-11-05 09:13:35'),
(4, 'Mysql Dump from Remote with FTP Upload', '#!/usr/bin/perl -w\r\nuse strict;\r\nuse warnings;\r\nuse Net::FTP;\r\nmy $dumpdir = "/root/grootbackup/";\r\n\r\nmy $date = `date +"%Y%m%d"`;\r\nchomp($date);\r\nmy $fold = $dumpdir."mysql.backup.groot_master_".$date.".sql";\r\n############## taking Dump \r\n`mysqldump -u root -p''aims145'' test > $fold`;\r\n\r\nmy ($ftp, $host, $user, $pass, $dir, $fpath);\r\n################## ftp configuration\r\n$host = "10.222.1.149";\r\n$user = "backup";\r\n$pass = "backup123";\r\n$dir = "GrootBackup";\r\n\r\n$fpath = $fold;\r\n$ftp = Net::FTP->new($host, Debug => 0, Passive => 0);\r\n$ftp->login($user, $pass) || die $ftp->message;\r\n########### changing Directory\r\n$ftp->cwd($dir);\r\n################## putting dump file to other server\r\n$ftp->put($fpath) || die $ftp->message;\r\n$ftp->quit;\r\n\r\n################# sending Email for Confirmation\r\nif($ftp->message){\r\n` sendEmail -s smtp.capitalvia.com:587 -xu amrit.sharma\\@capitalvia.com -xp Nector145@ -t amrit.sharma\\@capitalvia.com -f amrit.sharma\\@capitalvia.com -l /var/log/sendEmail -u "Groot DB Dump" -m "Groot DB Dump Uploaded Successfully"`;', '2015-11-05 09:15:36'),
(5, 'Git Pull Server Side Socket script', '#!/usr/bin/perl\r\nuse IO::Socket::INET;\r\n \r\n# auto-flush on socket\r\n$| = 1;\r\n \r\n# creating a listening socket\r\nmy $socket = new IO::Socket::INET (\r\n    LocalHost => ''0.0.0.0'',\r\n    LocalPort => ''1313'',\r\n    Proto => ''tcp'',\r\n    Listen => 5,\r\n    Reuse => 1\r\n);\r\ndie "cannot create socket $!\\n" unless $socket;\r\n#print "server waiting for client connection on port 1212\\n";\r\n \r\nwhile(1)\r\n{\r\n    # waiting for a new client connection\r\n    my $client_socket = $socket->accept();\r\n \r\n    # get information about a newly connected client\r\n    my $client_address = $client_socket->peerhost();\r\n    my $client_port = $client_socket->peerport();\r\n#    print "connection from $client_address:$client_port\\n";\r\n \r\n    # read up to 1024 characters from the connected client\r\n    my $data = "";\r\n    $client_socket->recv($data, 10240);\r\n#	print $data;\r\n	if($data eq ''cvmis''){\r\n	chdir "/var/www/html/gitrepo/cvmis";\r\n	$data = `git pull`;\r\n	#$data = "Git Pull for cvmis Repo Done Successfully";	\r\n	$client_socket->send($data, 10240);\r\n	}\r\n\r\n	if($data eq ''TradeTwits''){\r\n        chdir "/var/www/html/TradeTwits";\r\n        $data = `git pull origin master`;\r\n        $client_socket->send($data, 10240);\r\n	}    \r\n\r\n\r\n	if($data eq ''rapp''){\r\n        chdir "/var/www/html/gitrepo/rapp";\r\n        $data = `git pull`;\r\n        $client_socket->send($data, 10240);\r\n        }\r\n# write response data to the connected client\r\n#    $data = "Successfully";\r\n#   $client_socket->send($data);\r\n\r\n    # notify client that response has been sent\r\n    shutdown($client_socket, 1);\r\n}\r\n \r\n$socket->close();\r\n', '2015-11-05 09:17:40'),
(6, 'Record Memory and Load into database ', '#!/usr/bin/perl \r\nuse DBI;\r\n\r\n$dbh = DBI->connect("DBI:mysql:database=server;host=10.222.1.12;port=3306",''server'', ''aims145'') or die $DBI::errstr;\r\n\r\n$server = "CRM-Dev-12";\r\n\r\n################################################## for memory\r\nsub memory {\r\n	\r\n\r\n$query = "SELECT count(*) FROM `memory` where Server_Id=''".$server."''";\r\n$sth = $dbh->prepare($query);\r\n$sth->execute();\r\n      \r\n\r\nwhile (@row = $sth->fetchrow_array()) {\r\n    push @count1, @row;\r\n}\r\n\r\n\r\n#get current memory status\r\n@memory = `free -m|grep Mem:|awk ''{print \\$2, \\$3, \\$4}''`;\r\nchomp(@memory);\r\n\r\n@memory = split / /, $memory[0];\r\n\r\n#print $memory[2];\r\n\r\n\r\n#insert current memory status into database\r\nif ($count1[0] < 5040) {\r\n$dbh->do("INSERT INTO `server`.`memory` (`id`, `Server_Id`, `Total`, `Used`, `Free`, `Time`) VALUES (NULL, ''".$server."'', ''".$memory[0]."'', ''".$memory[1]."'', ''".$memory[2]."'', CURRENT_TIMESTAMP)");\r\n}\r\nelse{\r\n$query = "SELECT id FROM `memory` where Server_Id=''".$server."'' ORDER BY `memory`.`id`  ASC";\r\n$sth = $dbh->prepare($query);\r\n$sth->execute();\r\n     \r\nwhile (@row = $sth->fetchrow_array()) {\r\n    push @del1, @row;\r\n}\r\nprint $del1[0];\r\n$dbh->do("delete from server.memory where id=''".$del1[0]."''");\r\n$dbh->do("INSERT INTO `server`.`memory` (`id`, `Server_Id`, `Total`, `Used`, `Free`, `Time`) VALUES (NULL, ''".$server."'', ''".$memory[0]."'', ''".$memory[1]."'', ''".$memory[2]."'', CURRENT_TIMESTAMP)");\r\nprint "\\n";\r\n\r\n}\r\n\r\n}\r\n\r\nsub load {\r\n	########################## for Load \r\n#get current Load Status\r\n@load = `cat /proc/loadavg |awk ''{print \\$1, \\$2, \\$3}''` ;\r\nchomp(@load);\r\n@load = split / /, $load[0];\r\n\r\n#get current table count\r\n$query = "SELECT count(*) FROM `load` where Server_Id=''".$server."'' ";\r\n$sth = $dbh->prepare($query);\r\n$sth->execute();\r\n     \r\nwhile (@row = $sth->fetchrow_array()) {\r\n    push @count2, @row;\r\n}\r\n#insert current load into databases\r\n\r\nif ($count2[0] < 5040) {\r\n$dbh->do("INSERT INTO `server`.`load` (`Server_Id`, `current`, `5min`, `15min`) VALUES (''".$server."'', ''".$load[0]."'', ''".$load[1]."'', ''".$load[2]."'')");\r\n}\r\nelse{\r\n$query = "SELECT `id` FROM `load` where Server_Id=''".$server."'' ORDER BY `load`.`id`   ASC";\r\n$sth = $dbh->prepare($query);\r\n$sth->execute();\r\n     \r\nwhile (@row = $sth->fetchrow_array()) {\r\n    push @del2, @row;\r\n}\r\nprint $del2[0];\r\nprint "\\n";\r\n$dbh->do("delete from `server`.`load` where id=''".$del2[0]."'' ");\r\n$dbh->do("INSERT INTO `server`.`load` (`Server_Id`, `current`, `5min`, `15min`) VALUES (''".$server."'', ''".$load[0]."'', ''".$load[1]."'', ''".$load[2]."'')");\r\n}\r\n\r\n}\r\n\r\n\r\nload();\r\nmemory();', '2015-11-05 09:20:16'),
(7, 'Nagios Memory Check Script', '#!/usr/bin/perl -w\r\n\r\n# Heavily based on the script from:\r\n# check_mem.pl Copyright (C) 2000 Dan Larsson <dl@tyfon.net>\r\n# heavily modified by\r\n# Justin Ellison <justin@techadvise.com>\r\n#\r\n# The MIT License (MIT)\r\n# Copyright (c) 2011 justin@techadvise.com\r\n\r\n# Permission is hereby granted, free of charge, to any person obtaining a copy of this\r\n# software and associated documentation files (the "Software"), to deal in the Software\r\n# without restriction, including without limitation the rights to use, copy, modify,\r\n# merge, publish, distribute, sublicense, and/or sell copies of the Software, and to\r\n# permit persons to whom the Software is furnished to do so, subject to the following conditions:\r\n\r\n# The above copyright notice and this permission notice shall be included in all copies\r\n# or substantial portions of the Software.\r\n\r\n# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,\r\n# INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR\r\n# PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE\r\n# FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT\r\n# OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR\r\n# OTHER DEALINGS IN THE SOFTWARE.\r\n\r\n# Tell Perl what we need to use\r\nuse strict;\r\nuse Getopt::Std;\r\n\r\n#TODO - Convert to Nagios::Plugin\r\n#TODO - Use an alarm\r\n\r\n# Predefined exit codes for Nagios\r\nuse vars qw($opt_c $opt_f $opt_u $opt_w $opt_C $opt_v %exit_codes);\r\n%exit_codes   = (''UNKNOWN'' , 3,\r\n        	 ''OK''      , 0,\r\n                 ''WARNING'' , 1,\r\n                 ''CRITICAL'', 2,\r\n                 );\r\n\r\n# Get our variables, do our checking:\r\ninit();\r\n\r\n# Get the numbers:\r\nmy ($free_memory_kb,$used_memory_kb,$caches_kb) = get_memory_info();\r\nprint "$free_memory_kb Free\\n$used_memory_kb Used\\n$caches_kb Cache\\n" if ($opt_v);\r\n\r\nif ($opt_C) { #Do we count caches as free?\r\n    $used_memory_kb -= $caches_kb;\r\n    $free_memory_kb += $caches_kb;\r\n}\r\n\r\n# Round to the nearest KB\r\n$free_memory_kb = sprintf(''%d'',$free_memory_kb);\r\n$used_memory_kb = sprintf(''%d'',$used_memory_kb);\r\n$caches_kb = sprintf(''%d'',$caches_kb);\r\n\r\n# Tell Nagios what we came up with\r\ntell_nagios($used_memory_kb,$free_memory_kb,$caches_kb);\r\n\r\n\r\nsub tell_nagios {\r\n    my ($used,$free,$caches) = @_;\r\n    \r\n    # Calculate Total Memory\r\n    my $total = $free + $used;\r\n    print "$total Total\\n" if ($opt_v);\r\n\r\n    my $perf_warn;\r\n    my $perf_crit;\r\n    if ( $opt_u ) {\r\n      $perf_warn = int(${total} * $opt_w / 100);\r\n      $perf_crit = int(${total} * $opt_c / 100);\r\n    } else {\r\n      $perf_warn = int(${total} * ( 100 - $opt_w ) / 100);\r\n      $perf_crit = int(${total} * ( 100 - $opt_c ) / 100);\r\n    }\r\n    \r\n    my $perfdata = "|TOTAL=${total}KB;;;; USED=${used}KB;${perf_warn};${perf_crit};; FREE=${free}KB;;;; CACHES=${caches}KB;;;;";\r\n\r\n    if ($opt_f) {\r\n      my $percent    = sprintf "%.1f", ($free / $total * 100);\r\n      if ($percent <= $opt_c) {\r\n          finish("CRITICAL - $percent% ($free kB) free!$perfdata",$exit_codes{''CRITICAL''});\r\n      }\r\n      elsif ($percent <= $opt_w) {\r\n          finish("WARNING - $percent% ($free kB) free!$perfdata",$exit_codes{''WARNING''});\r\n      }\r\n      else {\r\n          finish("OK - $percent% ($free kB) free.$perfdata",$exit_codes{''OK''});\r\n      }\r\n    }\r\n    elsif ($opt_u) {\r\n      my $percent    = sprintf "%.1f", ($used / $total * 100);\r\n      if ($percent >= $opt_c) {\r\n          finish("CRITICAL - $percent% ($used kB) used!$perfdata",$exit_codes{''CRITICAL''});\r\n      }\r\n      elsif ($percent >= $opt_w) {\r\n          finish("WARNING - $percent% ($used kB) used!$perfdata",$exit_codes{''WARNING''});\r\n      }\r\n      else {\r\n          finish("OK - $percent% ($used kB) used.$perfdata",$exit_codes{''OK''});\r\n      }\r\n    }\r\n}\r\n\r\n# Show usage\r\nsub usage() {\r\n  print "\\ncheck_mem.pl v1.0 - Nagios Plugin\\n\\n";\r\n  print "usage:\\n";\r\n  print " check_mem.pl -<f|u> -w <warnlevel> -c <critlevel>\\n\\n";\r\n  print "options:\\n";\r\n  print " -f           Check FREE memory\\n";\r\n  print " -u           Check USED memory\\n";\r\n  print " -C           Count OS caches as FREE memory\\n";\r\n  print " -w PERCENT   Percent free/used when to warn\\n";\r\n  print " -c PERCENT   Percent free/used when critical\\n";\r\n  print "\\nCopyright (C) 2000 Dan Larsson <dl\\@tyfon.net>\\n";\r\n  print "check_mem.pl comes with absolutely NO WARRANTY either implied or explicit\\n";\r\n  print "This program is licensed under the terms of the\\n";\r\n  print "MIT License (check source code for details)\\n";\r\n  exit $exit_codes{''UNKNOWN''}; \r\n}\r\n\r\nsub get_memory_info {\r\n    my $used_memory_kb  = 0;\r\n    my $free_memory_kb  = 0;\r\n    my $total_memory_kb = 0;\r\n    my $caches_kb       = 0;\r\n\r\n    my $uname;\r\n    if ( -e ''/usr/bin/uname'') {\r\n        $uname = `/usr/bin/uname -a`;\r\n    }\r\n    elsif ( -e ''/bin/uname'') {\r\n        $uname = `/bin/uname -a`;\r\n    }\r\n    else {\r\n        die "Unable to find uname in /usr/bin or /bin!\\n";\r\n    }\r\n    print "uname returns $uname" if ($opt_v);\r\n    if ( $uname =~ /Linux/ ) {\r\n        my @meminfo = `/bin/cat /proc/meminfo`;\r\n        foreach (@meminfo) {\r\n            chomp;\r\n            if (/^Mem(Total|Free):\\s+(\\d+) kB/) {\r\n                my $counter_name = $1;\r\n                if ($counter_name eq ''Free'') {\r\n                    $free_memory_kb = $2;\r\n                }\r\n                elsif ($counter_name eq ''Total'') {\r\n                    $total_memory_kb = $2;\r\n                }\r\n            }\r\n            elsif (/^(Buffers|Cached|SReclaimable):\\s+(\\d+) kB/) {\r\n                $caches_kb += $2;\r\n            }\r\n        }\r\n        $used_memory_kb = $total_memory_kb - $free_memory_kb;\r\n    }\r\n    elsif ( $uname =~ /HP-UX/ ) {\r\n      # HP-UX, thanks to Christoph Fürstaller\r\n      my @meminfo = `/usr/bin/sudo /usr/local/bin/kmeminfo`;\r\n      foreach (@meminfo) {\r\n        chomp;\r\n      	if (/^Physical memory\\s\\s+=\\s+(\\d+)\\s+(\\d+.\\d)g/) {\r\n      	  $total_memory_kb = ($2 * 1024 * 1024);\r\n      	}\r\n      	elsif (/^Free memory\\s\\s+=\\s+(\\d+)\\s+(\\d+.\\d)g/) {\r\n      	  $free_memory_kb = ($2 * 1024 * 1024);\r\n      	}\r\n      }\r\n     $used_memory_kb = $total_memory_kb - $free_memory_kb;\r\n    }\r\n    elsif ( $uname =~ /FreeBSD/ ) {\r\n      # The FreeBSD case. 2013-03-19 www.claudiokuenzler.com\r\n      # free mem = Inactive*Page Size + Cache*Page Size + Free*Page Size\r\n      my $pagesize = `sysctl vm.stats.vm.v_page_size`;\r\n      $pagesize =~ s/[^0-9]//g;\r\n      my $mem_inactive = 0;\r\n      my $mem_cache = 0;\r\n      my $mem_free = 0;\r\n      my $mem_total = 0;\r\n      my $free_memory = 0;\r\n        my @meminfo = `/sbin/sysctl vm.stats.vm`;\r\n        foreach (@meminfo) {\r\n            chomp;\r\n            if (/^vm.stats.vm.v_inactive_count:\\s+(\\d+)/) {\r\n            $mem_inactive = ($1 * $pagesize);\r\n            }\r\n            elsif (/^vm.stats.vm.v_cache_count:\\s+(\\d+)/) {\r\n            $mem_cache = ($1 * $pagesize);\r\n            }\r\n            elsif (/^vm.stats.vm.v_free_count:\\s+(\\d+)/) {\r\n            $mem_free = ($1 * $pagesize);\r\n            }\r\n            elsif (/^vm.stats.vm.v_page_count:\\s+(\\d+)/) {\r\n            $mem_total = ($1 * $pagesize);\r\n            }\r\n        }\r\n        $free_memory = $mem_inactive + $mem_cache + $mem_free;\r\n        $free_memory_kb = ( $free_memory / 1024);\r\n        $total_memory_kb = ( $mem_total / 1024);\r\n        $used_memory_kb = $total_memory_kb - $free_memory_kb;\r\n        $caches_kb = ($mem_cache / 1024);\r\n    }\r\n    elsif ( $uname =~ /joyent/ ) {\r\n      # The SmartOS case. 2014-01-10 www.claudiokuenzler.com\r\n      # free mem = pagesfree * pagesize\r\n      my $pagesize = `pagesize`;\r\n      my $phys_pages = `kstat -p unix:0:system_pages:pagestotal | awk ''{print \\$NF}''`;\r\n      my $free_pages = `kstat -p unix:0:system_pages:pagesfree | awk ''{print \\$NF}''`;\r\n      my $arc_size = `kstat -p zfs:0:arcstats:size | awk ''{print \\$NF}''`;\r\n      my $arc_size_kb = $arc_size / 1024;\r\n\r\n      print "Pagesize is $pagesize" if ($opt_v);\r\n      print "Total pages is $phys_pages" if ($opt_v);\r\n      print "Free pages is $free_pages" if ($opt_v);\r\n      print "Arc size is $arc_size" if ($opt_v);\r\n\r\n      $caches_kb += $arc_size_kb;\r\n\r\n      $total_memory_kb = $phys_pages * $pagesize / 1024;\r\n      $free_memory_kb = $free_pages * $pagesize / 1024;\r\n      $used_memory_kb = $total_memory_kb - $free_memory_kb;\r\n    }\r\n    elsif ( $uname =~ /SunOS/ ) {\r\n        eval "use Sun::Solaris::Kstat";\r\n        if ($@) { #Kstat not available\r\n            if ($opt_C) {\r\n                print "You can''t report on Solaris caches without Sun::Solaris::Kstat available!\\n";\r\n                exit $exit_codes{UNKNOWN};\r\n            }\r\n            my @vmstat = `/usr/bin/vmstat 1 2`;\r\n            my $line;\r\n            foreach (@vmstat) {\r\n              chomp;\r\n              $line = $_;\r\n            }\r\n            $free_memory_kb = (split(/ /,$line))[5] / 1024;\r\n            my @prtconf = `/usr/sbin/prtconf`;\r\n            foreach (@prtconf) {\r\n                if (/^Memory size: (\\d+) Megabytes/) {\r\n                    $total_memory_kb = $1 * 1024;\r\n                }\r\n            }\r\n            $used_memory_kb = $total_memory_kb - $free_memory_kb;\r\n            \r\n        }\r\n        else { # We have kstat\r\n            my $kstat = Sun::Solaris::Kstat->new();\r\n            my $phys_pages = ${kstat}->{unix}->{0}->{system_pages}->{physmem};\r\n            my $free_pages = ${kstat}->{unix}->{0}->{system_pages}->{freemem};\r\n            # We probably should account for UFS caching here, but it''s unclear\r\n            # to me how to determine UFS''s cache size.  There''s inode_cache,\r\n            # and maybe the physmem variable in the system_pages module??\r\n            # In the real world, it looks to be so small as not to really matter,\r\n            # so we don''t grab it.  If someone can give me code that does this, \r\n            # I''d be glad to put it in.\r\n            my $arc_size = (exists ${kstat}->{zfs} && ${kstat}->{zfs}->{0}->{arcstats}->{size}) ?\r\n                 ${kstat}->{zfs}->{0}->{arcstats}->{size} / 1024 \r\n                 : 0;\r\n            $caches_kb += $arc_size;\r\n            my $pagesize = `pagesize`;\r\n    \r\n            $total_memory_kb = $phys_pages * $pagesize / 1024;\r\n            $free_memory_kb = $free_pages * $pagesize / 1024;\r\n            $used_memory_kb = $total_memory_kb - $free_memory_kb;\r\n        }\r\n    }\r\n    elsif ( $uname =~ /AIX/ ) {\r\n        my @meminfo = `/usr/bin/vmstat -vh`;\r\n        foreach (@meminfo) {\r\n            chomp;\r\n            if (/^\\s*([0-9.]+)\\s+(.*)/) {\r\n                my $counter_name = $2;\r\n                if ($counter_name eq ''memory pages'') {\r\n                    $total_memory_kb = $1*4;\r\n                }\r\n                if ($counter_name eq ''free pages'') {\r\n                    $free_memory_kb = $1*4;\r\n                }\r\n                if ($counter_name eq ''file pages'') {\r\n                    $caches_kb = $1*4;\r\n                }\r\n                if ($counter_name eq ''Number of 4k page frames loaned'') {\r\n                    $free_memory_kb += $1*4;\r\n                }\r\n            }\r\n        }\r\n        $used_memory_kb = $total_memory_kb - $free_memory_kb;\r\n    }\r\n    else {\r\n        if ($opt_C) {\r\n            print "You can''t report on $uname caches!\\n";\r\n            exit $exit_codes{UNKNOWN};\r\n        }\r\n    	my $command_line = `vmstat | tail -1 | awk ''{print \\$4,\\$5}''`;\r\n    	chomp $command_line;\r\n        my @memlist      = split(/ /, $command_line);\r\n    \r\n        # Define the calculating scalars\r\n        $used_memory_kb  = $memlist[0]/1024;\r\n        $free_memory_kb = $memlist[1]/1024;\r\n        $total_memory_kb = $used_memory_kb + $free_memory_kb;\r\n    }\r\n    return ($free_memory_kb,$used_memory_kb,$caches_kb);\r\n}\r\n\r\nsub init {\r\n    # Get the options\r\n    if ($#ARGV le 0) {\r\n      &usage;\r\n    }\r\n    else {\r\n      getopts(''c:fuCvw:'');\r\n    }\r\n    \r\n    # Shortcircuit the switches\r\n    if (!$opt_w or $opt_w == 0 or !$opt_c or $opt_c == 0) {\r\n      print "*** You must define WARN and CRITICAL levels!\\n";\r\n      &usage;\r\n    }\r\n    elsif (!$opt_f and !$opt_u) {\r\n      print "*** You must select to monitor either USED or FREE memory!\\n";\r\n      &usage;\r\n    }\r\n    \r\n    # Check if levels are sane\r\n    if ($opt_w <= $opt_c and $opt_f) {\r\n      print "*** WARN level must not be less than CRITICAL when checking FREE memory!\\n";\r\n      &usage;\r\n    }\r\n    elsif ($opt_w >= $opt_c and $opt_u) {\r\n      print "*** WARN level must not be greater than CRITICAL when checking USED memory!\\n";\r\n      &usage;\r\n    }\r\n}\r\n\r\nsub finish {\r\n    my ($msg,$state) = @_;\r\n    print "$msg\\n";\r\n    exit $state;\r\n}\r\n', '2015-11-05 09:46:28'),
(8, 'Perl Create a Server Socket', '#!/usr/bin/perl -w\r\n# Filename : server.pl\r\n\r\nuse strict;\r\nuse Socket;\r\n\r\n# use port 7890 as default\r\nmy $port = shift || 7890;\r\nmy $proto = getprotobyname(''tcp'');\r\nmy $server = "localhost";  # Host IP running the server\r\n\r\n# create a socket, make it reusable\r\nsocket(SOCKET, PF_INET, SOCK_STREAM, $proto)\r\n   or die "Can''t open socket $!\\n";\r\nsetsockopt(SOCKET, SOL_SOCKET, SO_REUSEADDR, 1)\r\n   or die "Can''t set socket option to SO_REUSEADDR $!\\n";\r\n\r\n# bind to a port, then listen\r\nbind( SOCKET, pack_sockaddr_in($port, inet_aton($server)))\r\n   or die "Can''t bind to port $port! \\n";\r\n\r\nlisten(SOCKET, 5) or die "listen: $!";\r\nprint "SERVER started on port $port\\n";\r\n\r\n# accepting a connection\r\nmy $client_addr;\r\nwhile ($client_addr = accept(NEW_SOCKET, SOCKET)) {\r\n   # send them a message, close connection\r\n   my $name = gethostbyaddr($client_addr, AF_INET );\r\n   print NEW_SOCKET "Smile from the server";\r\n   print "Connection recieved from $name\\n";\r\n   close NEW_SOCKET;\r\n}', '2015-11-05 12:10:32'),
(9, 'Perl Create a Client Socket', '!/usr/bin/perl -w\r\n# Filename : client.pl\r\n\r\nuse strict;\r\nuse Socket;\r\n\r\n# initialize host and port\r\nmy $host = shift || ''localhost'';\r\nmy $port = shift || 7890;\r\nmy $server = "localhost";  # Host IP running the server\r\n\r\n# create the socket, connect to the port\r\nsocket(SOCKET,PF_INET,SOCK_STREAM,(getprotobyname(''tcp''))[2])\r\n   or die "Can''t create a socket $!\\n";\r\nconnect( SOCKET, pack_sockaddr_in($port, inet_aton($server)))\r\n   or die "Can''t connect to port $port! \\n";\r\n\r\nmy $line;\r\nwhile ($line = <SOCKET>) {\r\n        print "$line\\n";\r\n}\r\nclose SOCKET or die "close: $!";', '2015-11-05 12:11:06');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `imp_scripts`
--
ALTER TABLE `imp_scripts`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `imp_scripts`
--
ALTER TABLE `imp_scripts`
  MODIFY `id` int(15) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;