Ivan Villareal

When I use the GROUP BY clause in mysql It starts grouping by the first different row in the resultset so for example if I have this table:

 

mysql> select * from grouptest;
+----+---------+-------+------------+
| id | city    | value | date       |
+----+---------+-------+------------+
|  1 | tijuana |    10 | 2010-02-02 | 
|  2 | tijuana |    11 | 2010-02-09 | 
|  3 | tijuana |    15 | 2010-02-12 | 
|  4 | tijuana |    17 | 2010-02-17 | 
|  5 | tecate  |    12 | 2010-02-10 | 
|  6 | tecate  |     1 | 2010-02-14 | 
|  7 | tecate  |    29 | 2010-02-17 | 
+----+---------+-------+------------+
7 rows in set (0.00 sec)

 

If I group by city this is what I get:

mysql> SELECT * FROM grouptest g GROUP BY city;
+----+---------+-------+------------+
| id | city    | value | date       |
+----+---------+-------+------------+
|  5 | tecate  |    12 | 2010-02-10 | 
|  1 | tijuana |    10 | 2010-02-02 | 
+----+---------+-------+------------+
2 rows in set (0.00 sec)

 

But I want to get the last inserted rows, so could do a sub query like this:

mysql> SELECT * FROM grouptest g
    -> WHERE g.date = (SELECT MAX(date) FROM grouptest gt WHERE g.city = gt.city)
    -> GROUP BY g.city;
+----+---------+-------+------------+
| id | city    | value | date       |
+----+---------+-------+------------+
|  7 | tecate  |    29 | 2010-02-17 | 
|  4 | tijuana |    17 | 2010-02-17 | 
+----+---------+-------+------------+
2 rows in set (0.00 sec)

 

But if the db is large making subqueries could be expensive, so a cleaner solution might be using the GROUP_CONCAT function, so for example the above query could be written like this:

mysql> SELECT id, city, value, date, GROUP_CONCAT(date) AS all_dates
    -> FROM grouptest g
    -> GROUP BY city;
+----+---------+-------+------------+---------------------------------------------+
| id | city    | value | date       | all_dates                                   |
+----+---------+-------+------------+---------------------------------------------+
|  5 | tecate  |    12 | 2010-02-10 | 2010-02-10,2010-02-14,2010-02-17            | 
|  1 | tijuana |    10 | 2010-02-02 | 2010-02-02,2010-02-09,2010-02-12,2010-02-17 | 
+----+---------+-------+------------+---------------------------------------------+
2 rows in set (0.00 sec)

 
At this point I can explode the all_dates column in php or split them in perl to get the last date for each city, but a little more work on our query could do our job simpler, something like this for instance:

mysql> SELECT id, city, value, SUBSTRING_INDEX(GROUP_CONCAT(date),',',-1) AS date
    -> FROM grouptest g
    -> GROUP BY city;
+----+---------+-------+------------+
| id | city    | value | date       |
+----+---------+-------+------------+
|  5 | tecate  |    12 | 2010-02-17 | 
|  1 | tijuana |    10 | 2010-02-17 | 
+----+---------+-------+------------+
2 rows in set (0.00 sec)

 
Or if I need to get the first and last rows for each group set I just add the date column like this:

mysql> SELECT id, city, value, date as first_date, SUBSTRING_INDEX(GROUP_CONCAT(date),',',-1) AS last_date
    -> FROM grouptest g
    -> GROUP BY city;
+----+---------+-------+------------+------------+
| id | city    | value | first_date | last_date  |
+----+---------+-------+------------+------------+
|  5 | tecate  |    12 | 2010-02-10 | 2010-02-17 | 
|  1 | tijuana |    10 | 2010-02-02 | 2010-02-17 | 
+----+---------+-------+------------+------------+
2 rows in set (0.00 sec)

 

There may be several other ways around this, but for now this approach is enough, I’m not sure about the impact this 2 functions may have on the server but I’m sure it is less than executing a subquery.

Using pipes and redirection in the shell we can do backups or restore them, performing compression or decompression on the fly, this are the commands I frequently use:

Create a backup with mysqldump and compress the stream with bzip2

ivan@mini:~$ mysqldump –h dbHost –u dbUser –pdbPass –add-drop-database –databases <dbname> | bzip2 > dbName-02-17-2010.sql.bz2</dbname>

If I want to restore the backup I use:

ivan@mini:~$ bunzip2 < dbName-02-17-2010.sql.bz2 | mysql –h dbHost –u dbUser –pdbPass

I can switch bzip2 compression by changing only the program name for example gzip/gunzip lzma/unlzma

I needed a process to move a table from one database to another, the databases are on separate servers, so renaming the table will not work, I did a little research and found this Table Syncer it seemed fine, the problem is that it was on ruby gem, and I said the problem because the server where this will be running don’t have this installed.

So instead of doing more research I spent that time developing my own php class to sync two tables on different server, I have little time to create this so I’m re-using a class I already did for handling several databases and it already has some useful methods, right now its 3:12pm I plan to finish this in 30 minutes lets see how it goes.

In the end I spent about an hour but I have a working file that can put in a cron job to sync the table on a regular basis.

I’ve changed the way I fetch the entire result, to avoid having problems exhausting the dedicated php memory with large arrays, instead I’m fetching one row at a time, another problem I had, was that the null values came blank so, when I was inserting on a unique index, I had an error, to avoid this I remove the empty values from the insert, and use the default table value instead, this results in a bit different tables Null instead of blanks, but because this isn’t important I didn’t want to spend more with this.

The final file, contains the db class and the sync class, and here it is:

< ?php
 
/**
* The purpose of this file is to sync
* tables between 2 databases
*/
 
class tableSync {
    private $_sourceDbUser = 'ivan';
    private $_sourceDbPass = 'pass';
    private $_sourceDbName = 'bounty';
    private $_sourceDbHost = 'localhost';
 
    private $_targetDbUser = 'ivan';
    private $_targetDbPass = 'pass';
    private $_targetDbName = 'testing';
    private $_targetDbHost = 'localhost';
 
    private $_sourceDb, $_targetDb, $_primaryKey, $_table;
 
    public function __construct($table)
    {
        $this->_sourceDb = new Db($this->_sourceDbUser, $this->_sourceDbPass, $this->_sourceDbName, $this->_sourceDbHost);
        $this->_targetDb = new Db($this->_targetDbUser, $this->_targetDbPass, $this->_targetDbName, $this->_targetDbHost);
        $this->_table = $table;
        $this->begin();
    }
 
    public function begin()
    {
      //Check that the table exists
        $sql = 'SHOW TABLES';
        $tbl = $this->_targetDb->fetchAssoc($sql);
 
        if (!in_array($this->_table, current($tbl))) {
                //create the table
                $sql = "SHOW CREATE TABLE `".$this->_table."`";
                $tbl = $this->_sourceDb->fetchAssoc($sql);
                $createQry = $tbl[0]['Create Table'];
                $res = $this->_targetDb->query($createQry);
        }
 
        //Get The primary Key
        $sql     = "SHOW indexes FROM ".$this->_table." WHERE Key_name = 'PRIMARY'";
        $indexes = $this->_sourceDb->fetchAssoc($sql);
        $this->_primaryKey = $indexes[0]['Column_name'];
        //Query source  table
        $sql     = 'SELECT * FROM '.$this->_table;
        $rowset  = $this->_sourceDb->query($sql);
        while ($row = mysql_fetch_assoc($rowset)) {
            //remove empty fields
            $newRow = array();
            foreach ($row as $field => $value) {
                if ($value != '') {
                    $newRow[$field] = $value;
                }
            }
            if ($this->_primaryKey != '') {
                echo "Inserting row ".$row[$this->_primaryKey] . "\n";
                $res = $this->_targetDb->insertUpdate($this->_table, $newRow, $this->_primaryKey, $row[$this->_primaryKey]);
            } else {
                $res = $this->_targetDb->queryInsert($this->_table, $newRow);
            }
        }
    }
}
 
class Db
{
	private	$_link;
	private $_affectedRows;
	private $_lastQueryStatus;
 
	/**
	* Instantiate the object
	**/
	public function __construct( $user, $pass, $dbName, $host = 'localhost')
	{
            $this->_link = mysql_connect($host, $user, $pass, true);
            mysql_select_db($dbName, $this->_link) or die('Could not select database');
            if (mysql_error()) {
                    printf("Connect failed: %s\n", mysql_error());
                    exit();
            } else {
                $sql = "SET NAMES `utf8`";
                mysql_query($sql, $this->_link);
                mysql_query("SET CHARACTER SET 'utf8';", $this->_link);
            }
	}
 
        public function query($sql)
	{
            $this->_lastQueryStatus = @mysql_query($sql, $this->_link) or die('Query failed: ' . mysql_error() . '<br /> SQL: '. $sql);
            if (!$this->_lastQueryStatus) {
                    $this->error("<b>MySQL Query fail:</b> $sql");
            }
            $this->_affectedRows    = @mysql_affected_rows();
            return $this->_lastQueryStatus;
	}
 
        public function fetchAssoc($sql)
	{
            $result = $this->query($sql);
            $table = array();
            while ($row = mysql_fetch_assoc($result)) {
                    $table[] = $row;
            }
            return $table;
	}
 
        public function escape($string)
	{
		if(get_magic_quotes_gpc()) $string = stripslashes($string);
		return mysql_real_escape_string($string);
	}
 
        public function fetchField($table, $field, $where = '1')
	{
	    $sql = "SELECT `$field` FROM `$table` WHERE $where";
	    $result = $this->query($sql);
	    $field = mysql_fetch_row($result);
	    return $field[0];
	}
 
        public function insertUpdate($table, $data, $field, $uniqueValue)
	{
	    $where = is_int($uniqueValue) ? "$field = $uniqueValue" : "$field = '".$this->escape($uniqueValue)."'";
	    $e = $this->fetchField($table, $field, $where);
	    if ($e) {
	        $res = $this->queryUpdate($table, $data, $where);
	    } else {
	        $res = $this->queryInsert($table, $data);
	    }
	    return $res;
	}
 
        public function queryInsert($table, $data)
	{
            $q="INSERT INTO `".$table."` ";
            $v='';
            $n='';
 
            foreach($data as $key=>$val) {
                    $n.="`$key`, ";
                    if(strtolower($val)=='null') $v.="NULL, ";
                    elseif(strtolower($val)=='now()') $v.="NOW(), ";
                    elseif(strtolower($val)=='utc_timestamp()') $v.="UTC_TIMESTAMP(), ";
                    else $v.= "'".$this->escape($val)."', ";
            }
            $q .= "(". rtrim($n, ', ') .") VALUES (". rtrim($v, ', ') .");";
            if($this->query($q)){
                    $result = mysql_insert_id();
            } else {
                    $result = false;
            }
            return $result;
	}
 
        public function queryUpdate($table, $data, $where='1')
        {
            $q="UPDATE `".$table."` SET ";
 
            foreach($data as $key=>$val) {
                if(strtolower($val)=='null') $q.= "`$key` = NULL, ";
                elseif(strtolower($val)=='now()') $q.= "`$key` = NOW(), ";
                else $q.= "`$key`='".$this->escape($val)."', ";
            }
            $q = rtrim($q, ', ') . ' WHERE '.$where.';';
 
            return $this->query($q);
        }
}
 
//Instantiate the tableSync sending the table name I want to sync
$sync = new tableSync('tod_whois_raider');

I just got a couple of vps servers, that I have to configure to run some apps, this are the tasks I did to have them ready for production use:

 

This servers didn’t came with a control panel (better for me), so the first thing I did was to login:

ivan@mini:~$ ssh root@23.45.12.56

After this I checked the OS, version and architecture

[root@V100205C4HB9V-1 ~]# cat /etc/*release*
CentOS release 5.4 (Final)
[root@V100205C4HB9V-1 ~]# uname -a
Linux V100205C4HB9V-1 2.6.18-028stab064.7 #1 SMP Wed Aug 26 13:11:07 MSD 2009 x86_64 x86_64 x86_64 GNU/Linux
[root@V100205C4HB9V-1 ~]#

Ok, now that I know the OS I create a normal user to avoid using the root account.

[root@V100205C4HB9V-1 ~]# useradd –Gwheel ivan

Then I change the server name to mygdon

[root@V100205C4HB9V-1 /]# sed -i 's/V100205C4HB9V-1/mygdon/g' /etc/sysconfig/network
[root@V100205C4HB9V-1 ~]# sed -i 's/V100205C4HB9V-1/mygdon/g' /etc/hosts
[root@V100205C4HB9V-1 ~]# echo HOST.DOMAIN.com > /etc/hostname
[root@V100205C4HB9V-1 ~]# hostname -F /etc/hostname

Unfortunately I was unable to persist the new hostname, because it is a VPS server, there are ways around this, but didn’t have the time to make the changes so I just moved on, and leave this for later

The next thing I did was update the OS, and add the rpmforge repo because I will need some apps from there

[root@V100205C4HB9V-1 ~]# yum update
[root@V100205C4HB9V-1 ~]# yum upgrade
[root@V100205C4HB9V-1 ~]# wget http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.1-1.el5.rf.x86_64.rpm
[root@V100205C4HB9V-1 ~]# rpm -Uhv rpmforge-release-0.5.1-1.$dist.rf.$arch.rpm
[root@V100205C4HB9V-1 ~]# yum update
[root@V100205C4HB9V-1 ~]# yum upgrade

Then I installed some packages I often use:

[root@V100205C4HB9V-1 ~]# yum install htop screen vim-enhanced

The VPS already had a web stack installed I just did some configuration:

Configuring Apache Virtual Hosts

[root@V100205C4HB9V-1 ~]# mkdir /var/www/vhosts/{site1, site2} –p
[root@V100205C4HB9V-1 ~]# vi /etc/httpd/conf/httpd.conf

Here I Uncommented the following directive

NameVirtualHost *:80

and Added a default vhost a new vhost

<virtualhost *:80>
    DocumentRoot /var/www/vhosts/default
    ServerName mygdon.site1.net
    <directory /var/www/vhosts/default>
        Options Indexes FollowSymLinks
        AllowOverride All
        Order allow,deny
        Allow from all
    </directory>
    ErrorLog logs/mygdon.site1.net-error_log
    CustomLog logs/mygdon.site1.net-access_log common
</virtualhost>
 
<virtualhost *:80>
    DocumentRoot /var/www/vhosts/site2
    ServerName appname.site2.net
    <directory /var/www/vhosts/site2>
        Options Indexes FollowSymLinks
        AllowOverride All
        Order allow,deny
        Allow from all
    </directory>
    ErrorLog logs/appname.site2.net-error_log
    CustomLog logs/appname.site2.net-access_log common
</virtualhost>

then restarted the apache server:

[root@V100205C4HB9V-1 ~]# service httpd restart
Stopping httpd:                                            [  OK  ]
Starting  httpd:                                            [  OK  ]

and  I wrote a simple php file to test this out:

[root@V100205C4HB9V-1 ~]# echo '<? phpinfo(); ?>' > /var/www/vhosts/site1/index.php

And because I haven’t configured a DNS server I just temporarily added the server to my hosts file (On my local machine):

root@mini:/etc# echo 'subdomain.site1.net 54.65.74.23' > /etc/hosts

and here is the result:

 

Configuring Mysql

Once I had the virtual hosts configuration in place I added a mysql user and changed the default root password:

 

[root@V100205C4HB9V-1 vhosts]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.77 Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
 
mysql> UPDATE mysql.user SET Password=PASSWORD('pass-here') WHERE user='root';
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
 
mysql> CREATE USER 'ivan'@'localhost' IDENTIFIED BY 'pass-here';
Query OK, 0 rows affected (0.01 sec)
 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ivan'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE USER 'ivan'@'%' IDENTIFIED BY 'pass-here';
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ivan'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
 
mysql>

 

Now the server is  ready for adding some virtual hosts, I just need to configure a DNS server and the mail server, but because the DNS is on another server and already working I just have to add a new zone, as for mail server, this server will be only used to send emails so I don’t need to dig in postfix configuration files for now.

I’ll write another post regarding the security, but for now this will work.

I have the need to install php5 and mysql 5 on a centos 4 server, so I found that the Centos Plus repository had this, and this is what I did to get these two working.

First check what is the version of the distro:

cat /etc/*release*

Then make an update:

yum update

After 496 package updates I’ve installed php5

yum --enablerepo=centosplus install php

This were the installed packages:

php-5.1.6-3.el4s1.10
php-cli-5.1.6-3.el4s1.10
php-common-5.1.6-3.el4s1.10

So I’ve created a info.php file, and restarted the apache server:

echo "<?php phpinfo(); ?>" > /var/www/html/info.php && service httpd restart

It was working correctly, so the next thing was to have mysql 5 in it, so I did:

yum --enablerepo=centosplus install mysql-server

I got some problems here, first I got this:

Transaction Check Error: file /etc/my.cnf from install of mysql-libs-5.0.68-1.el4_6 conflicts with file from package mysql-4.1.22-2.el4
file /usr/share/mysql/charsets/Index.xml from install of mysql-libs-5.0.68-1.el4_6 conflicts with file from package mysql-4.1.22-2.el4
file /usr/share/mysql/charsets/README from install of mysql-libs-5.0.68-1.el4_6 conflicts with file from package mysql-4.1.22-2.el4
file /usr/share/mysql/charsets/armscii8.xml from install of mysql-libs-5.0.68-1.el4_6 conflicts with file from package mysql-4.1.22-2.el4
file /usr/share/mysql/charsets/ascii.xml from install of mysql-libs-5.0.68-1.el4_6 conflicts with file from package mysql-4.1.22-2.el4

So what I found was that yum was trying to install mysql.i386 but the architechture is x86_64, to fix this I had to specify the arch

yum --enablerepo=centosplus install mysql.x86_64 mysql-server

After this I was able to install it, but when I tried to start the server I received some errors about error messages not right, I’ve checked the installed packages and it was installed mysqlclient10, so I removed the error messages problem disappeared, however I still couldn’t startup the server, checking at the logs I found this:

InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
091012 11:01:18  InnoDB: Started; log sequence number 0 0                       091012 11:01:18 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist                                                     091012 11:01:18  mysqld ended                                               

\

To fix this I cleared the data dir, that had som files, this was in ‘/var/lib/mysql’ and after that I ran mysql_install_db:

[root@prestant /]# cd /var/lib/mysql 
[root@prestant mysql]# rm -rf *
[root@prestant mysql]# mysql_install_db --user=mysql -ldata=/var/lib/mysql
[root@prestant mysql]# service mysqld start

After the server was up, I changed the root password and added a new user:

[root@prestant mysql]# mysqladmin -u root password NEWPASSWORD
[root@prestant mysql]# mysql -pNEWPASSWORD
 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.82sp1 Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> CREATE USER 'ivan'@'%' IDENTIFIED BY 'passhere';
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ivan'@'%' IDENTIFIED BY 'passhere' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE USER 'ivan'@'localhost' IDENTIFIED BY 'passhere';
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ivan'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> exit
Bye