Changeset 5128


Ignore:
Timestamp:
Jun 9, 2017, 1:55:21 PM (2 years ago)
Author:
mabarracus
Message:

Fix errors counting clients
Better postinstallation script

Location:
lliurex-analytics-server/trunk/fuentes
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • lliurex-analytics-server/trunk/fuentes/debian/changelog

    r5011 r5128  
     1lliurex-analytics-server (0.1.7) xenial; urgency=medium
     2
     3  * Fix errors counting clients
     4  * Better postinstallation script
     5
     6 -- M.Angel Juan <m.angel.juan@gmail.com>  Wed, 07 Jun 2017 14:46:21 +0200
     7
    18lliurex-analytics-server (0.1.6-1) xenial; urgency=high
    29
  • lliurex-analytics-server/trunk/fuentes/debian/postinst

    r4990 r5128  
    1919# for details, see http://www.debian.org/doc/debian-policy/ or
    2020# the debian-policy package
    21 
    2221do_dialog(){
    2322    input=$(dialog      \
     
    5251            PASS=$(cat /etc/lliurex-analytics-server/config_db|perl -ne '/pass=(.*)/i && print $1')
    5352            if [ -n "${USER}" -a -n "${PASS}" ]; then
    54                 RES=$(mysql -u${USER} -p${PASS} -N -s -D analytics -e "show tables" 2>/dev/null || true)
     53                RES=$(mysql -u${USER} -p${PASS} -N -s -Danalytics -e "show tables" 2>/dev/null || true)
    5554                if [ -n "${RES}" ]; then
    5655                    user="${USER}"
     
    9897                    rootuser=$(cat /etc/mysql/debian.cnf |grep -A 4 '\[client\]'|perl -ne '/user[ \t]*=[ \t]*(.*)/i && print $1')
    9998                    rootpass=$(cat /etc/mysql/debian.cnf |grep -A 4 '\[client\]'|perl -ne '/password[ \t]*=[ \t]*(.*)/i && print $1')
    100                     if [ -n "$root_user" -a -n "$root_pass" ]; then
     99                    if [ -n "$rootuser" -a -n "$rootpass" ]; then
    101100                        root_test=$(mysql -u $rootuser -p$rootpass -s -N -e "show databases" 2>/dev/null || true)
    102                         if [ -z "$root_test" ]; then
     101                        if [ -n "$root_test" ]; then
    103102                            ask_root="no"
    104103                            rootuser="-u$rootuser"
     
    114113            if [ "x$ask_root" = "xyes" ]; then
    115114                do_dialog 'Database configuration' 'Mysql root user? (empty is root)'
    116                 rootuser=$input
     115                if [ -z "$input" ]; then
     116                    rootuser="root"
     117                else
     118                    rootuser=$input
     119                fi
    117120                do_dialog 'Database configuration' 'Password root de mysql?'
    118121                rootpass=$input
     
    123126                else
    124127                    if [ -z "$rootuser" ]; then
    125                         rootuser="root"
     128                        rootuser="-uroot"
    126129                    fi
    127130                    rootuser="-u$rootuser"
     
    145148
    146149# UPDATE DB FROM OLDER VERSIONS
    147     tables=$(mysql $rootuser $rootpass -s -N -D analytics -e "show tables" 2>/dev/null|grep historico_clients)
     150    tables=$(mysql $rootuser $rootpass -s -N -Danalytics -e "show tables" 2>/dev/null || true)
     151    if [ -n "$tables" ]; then
     152        tables=$(echo $tables|grep historico_clients)
     153        dump_old_data=yes
     154    else
     155        dump_old_data=no
     156    fi
    148157    keys=$(mysqldump $rootuser $rootpass analytics|egrep '^[[:space:]]*KEY'|wc -l)
    149158    dumpfile="/usr/lib/analytics-server/analytics-0_1_3-to-0_1_4.sql"
     
    162171    if [ "x$need_update_to_0_1_4" = "xyes" ]; then
    163172        echo Updating to database version upper than 0.1.4
    164         mysqldump $rootuser $rootpass --no-create-info analytics > /tmp/__analytics_tmp__
    165         mysql $rootuser $rootpass -s -N -e "drop database analytics"
     173        if [ "x$dump_old_data" = "xyes" ]; then
     174            mysqldump $rootuser $rootpass --no-create-info analytics > /tmp/__analytics_tmp__
     175        fi
     176        mysql $rootuser $rootpass -s -N -e "drop database if exists analytics"
    166177        mysql $rootuser $rootpass < $dumpfile
    167178        mysql $rootuser $rootpass analytics < /tmp/__analytics_tmp__
     
    175186    if [ "x$create_db" = "xyes" ]; then
    176187        if [ "x$create_user" = "xyes" ]; then
    177             mysql $rootuser $rootpass -s -N -e "create user '$user'@'%' identified by '$pass';"
    178             mysql $rootuser $rootpass -s -N -e  "grant all privileges on analytics.* to '$user'@'%';"
     188            mysql $rootuser $rootpass -s -N -e "create user '$user'@'%' identified by '$pass';" || true
     189            mysql $rootuser $rootpass -s -N -e  "grant all privileges on analytics.* to '$user'@'%';" || true
    179190            mysql $rootuser $rootpass -s -N -e  "flush privileges;"
    180191            echo "user=$user" > /etc/lliurex-analytics-server/config_db
  • lliurex-analytics-server/trunk/fuentes/debian/postrm

    r3369 r5128  
    44# see: dh_installdeb(1)
    55
    6 #set -e
     6set -e
    77
    88# summary of how this script can be called:
     
    4040    ;;
    4141    purge)
    42         mysql_cmd="mysql -s -u root"
    43         RET=$(echo "show databases;" |${mysql_cmd} 2>/dev/null)
     42        rootuser=""
     43        rootpass=""
     44        RET=$(mysql -s -N -e "show databases;" 2>/dev/null||true)
    4445        if [ -z "${RET}" ]; then
    45             ask_question 'Mysql root password?'
    46             rootpass=${input}
    47             mysql_cmd="mysql -s -uroot -p${input}"
    48             RET=$(echo "show databases;" |${mysql_cmd} 2>/dev/null)
    49             if [ -z "${RET}" ]; then
    50                 echo "Error removing database"
    51                 exit 0
     46            ask_root=yes
     47            rootuser=$(cat /etc/mysql/debian.cnf |grep -A 4 '\[client\]'|perl -ne '/user[ \t]*=[ \t]*(.*)/i && print $1')
     48            rootpass=$(cat /etc/mysql/debian.cnf |grep -A 4 '\[client\]'|perl -ne '/password[ \t]*=[ \t]*(.*)/i && print $1')
     49            if [ -n "$rootuser" -a -n "$rootpass" ]; then
     50                root_test=$(mysql -u $rootuser -p$rootpass -s -N -e "show databases" 2>/dev/null || true)
     51                if [ -n "$root_test" ]; then
     52                    ask_root="no"
     53                    rootuser="-u$rootuser"
     54                    rootpass="-p$rootpass"
     55                fi
     56            fi
     57            if  [ "x$ask_root" = "xyes" ]; then
     58                do_dialog 'Mysql root password?'
     59                rootpass=${input}
     60                RET=$(mysql -uroot -p$rootpass -s -N -e "show databases" 2>/dev/null||true)
     61                if [ -z "${RET}" ]; then
     62                    echo "Error removing database"
     63                    exit 0
     64                else
     65                    rootuser="-uroot"
     66                    rootpass="-p$rootpass"
     67                fi
    5268            fi
    5369        fi
    5470        echo "Removing database.."
    55         USER=$(echo "select user from mysql.db where db like 'analytics';"| ${mysql_cmd} 2>/dev/null)
     71        USER=$(mysql $rootuser $rootpass -s -N -e "select user from mysql.db where db like 'analytics';" 2>/dev/null||true)
    5672        if [ -z "${USER}" ]; then
    5773            echo "Error removing database"
    5874            exit 0
    5975        fi
    60         echo "drop database analytics;" | ${mysql_cmd} 2>/dev/null
    61         echo "drop user ${USER};"| ${mysql_cmd} 2>/dev/null
    62         echo "flush privileges;"| ${mysql_cmd} 2>/dev/null
     76        mysql $rootuser $rootpass -s -N -e "drop database analytics;" 2>/dev/null  || true
     77        mysql $rootuser $rootpass -s -N -e "drop user ${USER};" 2>/dev/null || true
     78        mysql $rootuser $rootpass -s -N -e "flush privileges;" 2>/dev/null || true
    6379        echo "Database analytics & user ${USER} removed from mysql"
    6480        if [ -f "/etc/lliurex-analytics-server/config_db" ]; then
  • lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/db.php

    r4990 r5128  
    5353                $query->execute();
    5454                if ( $query->affected_rows < 0 ){
    55                         throw new Exception($query->mysqli_error());
     55                        throw new Exception($this->dbconn->error);
    5656                }else{
    5757                        $id=$this->dbconn->insert_id;
     
    7070                        $noerr=$query->execute();
    7171                        if (! $noerr){
    72                                 throw new Exception($query->mysqli_error());
     72                                throw new Exception($this->dbconn->error);
    7373                        }
    7474                }
     
    101101                }
    102102                if ($thismonth > $lastmonth){
     103                        $dummy_new_date=$this->dates['first_this_month'];
     104                        $sql="update historico set fecha ='$dummy_new_date' where app = 'dummy'";
     105                        $this->dbconn->query($sql);
    103106                        $date_to_history=$this->dates['last_one_month_ago'];
    104                         $sql="insert into historico(app,count,fecha,version,sabor) (select app,count,'$date_to_history' as fecha,version,sabor from packages order by count desc limit 100);";
     107                        $sql="insert into historico(app,count,fecha,version,sabor) select app,count,'$date_to_history',version,sabor from packages";
    105108                        $this->dbconn->query($sql);
    106109                        $sql="truncate packages;";
     
    156159        }
    157160        function get_historic_data(){
    158                 $this->rotate();
     161                $this->rotate();
    159162                $this->load_alias();
    160163                //echo $today.' '.$first_this_month.' '.$first_one_month_ago.' '.$last_one_month_ago.' '.$first_two_month_ago.' '.$last_two_month_ago.' EOL';
     
    200203            $sql=array('current' =>'','old'=>'', 'very_old'=>'', 'current_clients' => '', 'old_clients' => '', 'very_old_clients' => '');
    201204
    202             $sql['current']="select app,sum(count) as count from (select app,count from packages where app not in ( select app from packages where (";
    203             $sql['old']="select app,count,fecha from historico where";
    204             $sql['very_old']="select app,count,fecha from historico where";
     205            $sql['current']="select app,sum(count) as count from (select app,count from packages where (app,version,sabor) not in ( select app,version,sabor from packages where (";
     206            $sql['old']="select app,sum(count) as count,fecha from historico where";
     207            $sql['very_old']="select app,sum(count) as count,fecha from historico where";
    205208            $where_current=array();
    206209            $where_old=array();
     
    229232            $sql['old'].=' fecha '.$date_old." and not (";
    230233            $sql['old'].=implode(' or ',$where_old);
    231             $sql['old'].=") group by app order by count DESC LIMIT 30";
     234            $sql['old'].=") group by app order by sum(count) DESC LIMIT 30";
    232235            $sql['very_old'].=' fecha '.$date_very_old." and not(";
    233236            $sql['very_old'].=implode(' or ',$where_very_old);
    234             $sql['very_old'].=") group by app order by count DESC LIMIT 30";
     237            $sql['very_old'].=") group by app order by sum(count) DESC LIMIT 30";
    235238           
    236239            $sql['current_clients'].="select coalesce(sum(suma_parcial),0) as count from (";
     
    258261                $sql_clients = "select count(user) as count from clients where (lastlogin $range_dates) and $where";
    259262                if($dumpsql == "yes")
    260                         return array('type'=>'current','date' => "$range_dates",'where' => '('.$where.')','clients'=>"$sql_clients");
     263                        return array('type'=>'current','date' => "$range_dates",'where' => '('.$where.')','clients'=>"$sql_clients",'sql'=>$sql);
    261264               
    262265                return array($this->get_result_from_sql($sql),$this->get_clients_from_sql($sql_clients));
     
    304307                }
    305308                $range_dates="BETWEEN '$date1' and '$date2'";
    306                 $sql="SELECT app,count,fecha from historico where fecha $range_dates and $where_a order by count DESC LIMIT 30";
     309                $sql="SELECT app,sum(count) as count,fecha from historico where fecha $range_dates and $where_a group by app order by sum(count) DESC LIMIT 30";
    307310                $sql_clients = "select nclients as count from historico_clients where (fecha $range_dates) and $where_a";
    308311                if($dumpsql == "yes")
    309                         return array('type'=>'old','date'=> "$range_dates",'where'=>'('.$where_a.')','clients'=>"$sql_clients");
     312                        return array('type'=>'old','date'=> "$range_dates",'where'=>'('.$where_a.')','clients'=>"$sql_clients",'sql'=>$sql);
    310313                return array($this->get_result_from_sql($sql),$this->get_clients_from_sql($sql_clients));
    311314        }
     
    319322                }
    320323                $range_dates="BETWEEN '$date1' and '$date2'";
    321                 $sql="SELECT app,count,fecha from historico where fecha $range_dates and $where_a order by count DESC LIMIT 30";
     324                $sql="SELECT app,sum(count) as count,fecha from historico where fecha $range_dates and $where_a group by app order by sum(count) DESC LIMIT 30";
    322325                $sql_clients = "select nclients as count from historico_clients where (fecha $range_dates) and $where_a";
    323326                if($dumpsql == "yes")
    324                         return array('type'=>'very_old','date' => "$range_dates",'where' => '('.$where_a.')','clients'=>"$sql_clients");
     327                        return array('type'=>'very_old','date' => "$range_dates",'where' => '('.$where_a.')','clients'=>"$sql_clients",'sql'=>$sql);
    325328               
    326329                return array($this->get_result_from_sql($sql),$this->get_clients_from_sql($sql_clients));
Note: See TracChangeset for help on using the changeset viewer.