Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Wednesday, June 30, 2021

Super easy script - Python3 & optimizing every table of a mysql db

 #!/usr/bin/python3.3

#The objetive of this script is to find all tables in a MYSQL DB and opmitize all of them

import dbconnect

import time

from datetime import datetime



## // VARIABLE DECLARATION ##//

startTime = datetime.now()

conn = dbconnect.dbconnect()

conn.autocommit(True)

cur = conn.cursor()


print ("Starting time: ", startTime)


SQLQUERY=("SHOW TABLES") #Find every table in the DB

cur.execute(SQLQUERY)

tables = cur.fetchall()



if len(tables)>0: #Prevent there are not tables in the list

  for table in tables:  #For every table in the DB

    try:

      SQLQUERY="OPTIMIZE TABLE "+ table[0]  #Construct the SQL QUERY

      print ("   Optimizing", table[0])

      cur.execute(SQLQUERY)

    except:

      pass


print ("Script execution time:",datetime.now()-startTime)

print ("Ending time: ", datetime.now())

print ("******** ****** ")

Saturday, January 12, 2013

Error 1017 in Cacti. MySQL. Blank Graphics

Error:
4/7/2012 5:37:49 PM - CMDPHP: Poller [0] ERROR: SQL Cell Failed!, Error: '1017 ', SQL: "SELECT count (*) FROM polle
WHERE r_time poller_id = 0 AND end_time> '0000-00-00 00:00:00 '"

4/7/2012 5:38:34 PM - CMDPHP: Poller [0] ERROR: SQL Cell Failed!, Error: '1017 ', SQL: "SELECT count (*) FROM polle
r_time WHERE end_time = '0000-00-00 00:00:00 '"

Procedure:
If the file cacti.log you see the above error tt indicates a failure with poller_output mysql table.
In this respect, I have seem three different solutions, order from the less risky to the highest.

1) Repair mysql table with a php script that brings cacti

# php $PATH-TO-CACTI/cli/repair_database.php

2) Repair the table with mysql command:

# mysql> REPAIR TABLE poller_output;

If you do not know the user information for the mysql database, it can be found here:

$PATH-TO-CACTI/include/config.php


3) Remove the table and do it again (this solution despite being aggressive works perfectly and you do not lose the historic Cacti information). Just enters the mysql CLI and copy/paste the following:

-
- Table structure for table `poller_output`
-
DROP TABLE IF EXISTS `poller_output`;
CREATE TABLE `poller_output` (
MEDIUMINT local_data_id `` (8) unsigned NOT NULL default '0 ',
Rrd_name `` varchar (19) NOT NULL default'',
`Time` datetime NOT NULL default '0000-00-00 00:00:00 ',
`Output` text NOT NULL,
PRIMARY KEY (`local_data_id`, `rrd_name`, `time`)
) TYPE = MyISAM;
-
- Dumping data for table `poller_output`
-
Poller_output `LOCK TABLES` WRITE;
/ *! 40000 ALTER TABLE `poller_output` DISABLE KEYS * /;
/ *! 40000 ALTER TABLE `poller_output` ENABLE KEYS * /;
UNLOCK TABLES;


Ready!, Then you can wait for the poller runs 15 minutes and you will have something in your graphs. If you want to force the poller command is as follows:

# /usr/bin/php-q /var /www/miServer-cacti/poller.php  -force

I hope it will be useful,