Ivan Villareal

I was updating my db dev instance with a set of changes, and when I was restoring the dump for a table it contained a drop db clause it was a simple table and it took a about 15 seconds to finish the operation, when I looked at the db schema all the tables were gone!

This db has a very large table about 16Gb of binary data, so getting a new dump would require a lot of time, so as workaround I’ve dumped only sets of data that I need to my tests.

First I did a dump of the db schema with no data:

mysqldump -h host-u ${USER} --password=${PASS} --no-data --add-drop-database --databases largeDb> schema_largeDb.sql

then I created a dump for the tables that I need, for this I have to include a list of tables that I don’t want, I’ve created the list of unwanted tables manually.

mysqldump -h host-u ${USER} --password=${PASS} --no-create-info --ignore-table=largeDb.unwantedTable1 --ignore-table=largeDb.unwantedTable2 --ignore-table=largeDb.unwantedTable3 > smallData.sql

And because I need data from the big table, I’ve used the –where clause to limit the rows returned in

mysqldump -h host-u ${USER} --password=${PASS} "--where=brand_id='2496'" largeDb bigTable > binaryData.sql

With this one I was able to get only the binaryData that I need to make my tests, instead of waiting to get for the entire dbDump,

I needed to update the php version of a centos 4 box, so this is what I did:

I’ve decided to go for the easy way, using the remi repo, so first I’ve Downloaded the Extra Packages for Enterprise Linux (EPEL)

[root@box php5.3]# wget http://download.fedora.redhat.com/pub/epel/4/i386/epel-release-4-9.noarch.rpm
--13:08:07--  http://download.fedora.redhat.com/pub/epel/4/i386/epel-release-4-9.noarch.rpm
           => `epel-release-4-9.noarch.rpm'
Resolving download.fedora.redhat.com... 209.132.183.67
Connecting to download.fedora.redhat.com|209.132.183.67|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 13,686 (13K) [application/x-rpm]
 
100%[=================================================================================>] 13,686        --.--K/s            
 
13:08:08 (405.60 KB/s) - `epel-release-4-9.noarch.rpm' saved [13686/13686]

Then the remi repo:

[root@box php5.3]# wget http://rpms.famillecollet.com/enterprise/remi-release-4.rpm
--13:08:21--  http://rpms.famillecollet.com/enterprise/remi-release-4.rpm
           => `remi-release-4.rpm'
Resolving rpms.famillecollet.com... 88.191.60.189
Connecting to rpms.famillecollet.com|88.191.60.189|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5,120 (5.0K) [application/x-rpm]
 
100%[=================================================================================>] 5,120         32.45K/s            
 
13:08:22 (32.41 KB/s) - `remi-release-4.rpm' saved [5120/5120]

Then I’ve upgraded both repos:

[root@box php5.3]# rpm -Uvh remi-release-4*.rpm epel-release-4*.rpm
warning: remi-release-4.rpm: V3 DSA signature: NOKEY, key ID 00f97f56
warning: epel-release-4-9.noarch.rpm: V3 DSA signature: NOKEY, key ID 217521f6
Preparing...                ########################################### [100%]
   1:epel-release           ########################################### [ 50%]
   2:remi-release           ########################################### [100%]

and after that I downloaded the remi repo file for Yum:

[root@box php5.3]# cd /etc/yum.repos.d/
[root@box yum.repos.d]# wget http://rpms.famillecollet.com/remi-enterprise.repo
--13:09:28--  http://rpms.famillecollet.com/remi-enterprise.repo
           => `remi-enterprise.repo'
Resolving rpms.famillecollet.com... 88.191.60.189
Connecting to rpms.famillecollet.com|88.191.60.189|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 793 [text/plain]
 
100%[=================================================================================>] 793           --.--K/s            
 
13:09:28 (50.42 MB/s) - `remi-enterprise.repo' saved [793/793]

After that I just told yum to upgrade php:

[root@box yum.repos.d]# yum --enable remi update php
Repository remi is listed more than once in the configuration
Repository remi-test is listed more than once in the configuration
Setting up Update Process
Setting up repositories
epel                      100% |=========================| 3.4 kB    00:00    
remi                      100% |=========================| 2.6 kB    00:00    
Reading repository metadata in from local files
357f538e0bf37d10983cd3b22 100% |=========================| 732 kB    00:06    
epel      : ################################################## 2267/2267
e849a85ae6e8f1b8e5e71c08b 100% |=========================| 128 kB    00:03    
remi      : ################################################## 444/444
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for php to pack into transaction set.
php-5.3.2-1.el4.remi.x86_ 100% |=========================|  19 kB    00:00    
---> Package php.x86_64 0:5.3.2-1.el4.remi set to be updated
--> Running transaction check
--> Processing Dependency: php-common = 5.3.2-1.el4.remi for package: php
--> Processing Dependency: php-cli = 5.3.2-1.el4.remi for package: php
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for php-common to pack into transaction set.
php-common-5.3.2-1.el4.re 100% |=========================|  25 kB    00:01    
---> Package php-common.x86_64 0:5.3.2-1.el4.remi set to be updated
---> Downloading header for php-cli to pack into transaction set.
php-cli-5.3.2-1.el4.remi. 100% |=========================|  19 kB    00:01    
---> Package php-cli.x86_64 0:5.3.2-1.el4.remi set to be updated
--> Running transaction check
--> Processing Dependency: php-common = 5.1.6-3.el4s1.10 for package: php-mysql
--> Processing Dependency: php-common = 5.1.6-3.el4s1.10 for package: php-xml
--> Processing Dependency: php-common = 5.1.6-3.el4s1.10 for package: php-pdo
--> Processing Dependency: libedit.so.0()(64bit) for package: php-cli
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for php-xml to pack into transaction set.
php-xml-5.3.2-1.el4.remi. 100% |=========================|  20 kB    00:00    
---> Package php-xml.x86_64 0:5.3.2-1.el4.remi set to be updated
---> Downloading header for libedit to pack into transaction set.
libedit-20090923-3.0_1.el 100% |=========================| 5.8 kB    00:00    
---> Package libedit.x86_64 0:20090923-3.0_1.el4.rf set to be updated
---> Downloading header for php-pdo to pack into transaction set.
php-pdo-5.3.2-1.el4.remi. 100% |=========================|  19 kB    00:00    
---> Package php-pdo.x86_64 0:5.3.2-1.el4.remi set to be updated
---> Downloading header for php-mysql to pack into transaction set.
php-mysql-5.3.2-1.el4.rem 100% |=========================|  19 kB    00:00    
---> Package php-mysql.x86_64 0:5.3.2-1.el4.remi set to be updated
--> Running transaction check
--> Processing Dependency: libsqlite.so.0()(64bit) for package: php-pdo
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: php-mysql
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: php-mysql
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for sqlite2 to pack into transaction set.
sqlite2-2.8.17-2.el4.remi 100% |=========================| 5.2 kB    00:00    
---> Package sqlite2.x86_64 0:2.8.17-2.el4.remi set to be updated
---> Downloading header for mysql-libs to pack into transaction set.
mysql-libs-5.1.45-1.el4.r 100% |=========================|  24 kB    00:00    
---> Package mysql-libs.x86_64 0:5.1.45-1.el4.remi set to be updated
--> Running transaction check
--> Processing Dependency: libmysqlclient.so.15()(64bit) for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: mysql-server
--> Processing Dependency: libmysqlclient_r.so.15(libmysqlclient_15)(64bit) for package: mysql-server
--> Processing Dependency: libmysqlclient.so.15()(64bit) for package: mysql-server
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: perl-DBD-MySQL
--> Processing Dependency: mysql-libs = 5.0.82sp1-1.el4_8 for package: mysql
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: mysql
--> Processing Dependency: libmysqlclient.so.15()(64bit) for package: mysql
--> Processing Dependency: libmysqlclient_r.so.15()(64bit) for package: mysql-server
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for mysqlclient15 to pack into transaction set.
mysqlclient15-5.0.67-1.el 100% |=========================| 3.9 kB    00:00    
---> Package mysqlclient15.x86_64 0:5.0.67-1.el4.remi set to be updated
---> Package perl-DBD-mysql.x86_64 0:4.013-1.el4.rf set to be updated
---> Downloading header for mysql to pack into transaction set.
mysql-5.1.45-1.el4.remi.x 100% |=========================|  19 kB    00:00    
---> Package mysql.x86_64 0:5.1.45-1.el4.remi set to be updated
---> Downloading header for mysql-server to pack into transaction set.
mysql-server-5.1.45-1.el4 100% |=========================|  27 kB    00:00    
---> Package mysql-server.x86_64 0:5.1.45-1.el4.remi set to be updated
--> Running transaction check
--> Processing Dependency: libmysqlclient.so.14(libmysqlclient_14)(64bit) for package: perl-DBD-mysql
--> Processing Dependency: libmysqlclient.so.14()(64bit) for package: perl-DBD-mysql
--> Restarting Dependency Resolution with new changes.
--> Populating transaction set with selected packages. Please wait.
---> Downloading header for mysqlclient14 to pack into transaction set.
mysqlclient14-4.1.22-1.el 100% |=========================| 5.5 kB    00:00    
---> Package mysqlclient14.x86_64 0:4.1.22-1.el4.remi set to be updated
--> Running transaction check
 
Dependencies Resolved
 
=============================================================================
Package                 Arch       Version          Repository        Size
=============================================================================
Installing:
perl-DBD-mysql          x86_64     4.013-1.el4.rf   rpmforge          228 k
     replacing  perl-DBD-MySQL.x86_64 4.008-1.el4s1
 
Updating:
php                     x86_64     5.3.2-1.el4.remi  remi              1.4 M
Installing for dependencies:
libedit                 x86_64     20090923-3.0_1.el4.rf  rpmforge          265 k
mysqlclient14           x86_64     4.1.22-1.el4.remi  remi              1.2 M
mysqlclient15           x86_64     5.0.67-1.el4.remi  remi              1.3 M
sqlite2                 x86_64     2.8.17-2.el4.remi  remi              161 k
Updating for dependencies:
mysql                   x86_64     5.1.45-1.el4.remi  remi              1.0 M
mysql-libs              x86_64     5.1.45-1.el4.remi  remi              1.7 M
mysql-server            x86_64     5.1.45-1.el4.remi  remi               11 M
php-cli                 x86_64     5.3.2-1.el4.remi  remi              2.6 M
php-common              x86_64     5.3.2-1.el4.remi  remi              963 k
php-mysql               x86_64     5.3.2-1.el4.remi  remi              155 k
php-pdo                 x86_64     5.3.2-1.el4.remi  remi              177 k
php-xml                 x86_64     5.3.2-1.el4.remi  remi              232 k
 
Transaction Summary
=============================================================================
Install      5 Package(s)        
Update       9 Package(s)        
Remove       0 Package(s)        
Total download size: 22 M
Is this ok [y/N]: y
Downloading Packages:
(1/14): php-xml-5.3.2-1.e 100% |=========================| 232 kB    00:04    
(2/14): libedit-20090923- 100% |=========================| 265 kB    00:04    
(3/14): php-common-5.3.2- 100% |=========================| 963 kB    00:15    
(4/14): mysqlclient15-5.0 100% |=========================| 1.3 MB    00:23    
(5/14): php-cli-5.3.2-1.e 100% |=========================| 2.6 MB    00:35    
(6/14): perl-DBD-mysql-4. 100% |=========================| 228 kB    00:04    
(7/14): php-pdo-5.3.2-1.e 100% |=========================| 177 kB    00:05    
(8/14): mysql-5.1.45-1.el 100% |=========================| 1.0 MB    00:15    
(9/14): sqlite2-2.8.17-2. 100% |=========================| 161 kB    00:05    
(10/14): mysqlclient14-4. 100% |=========================| 1.2 MB    00:15    
(11/14): mysql-server-5.1 100% |=========================|  11 MB    01:59    
(12/14): php-5.3.2-1.el4. 100% |=========================| 1.4 MB    00:23    
(13/14): mysql-libs-5.1.4 100% |=========================| 1.7 MB    00:23    
(14/14): php-mysql-5.3.2- 100% |=========================| 155 kB    00:04    
warning: rpmts_HdrFromFdno: V3 DSA signature: NOKEY, key ID 00f97f56
Public key for php-xml-5.3.2-1.el4.remi.x86_64.rpm is not installed
Retrieving GPG key from http://rpms.famillecollet.com/RPM-GPG-KEY-remi
Importing GPG key 0x00F97F56 "Remi Collet "
Is this ok [y/N]:
 
Key imported successfully
warning: only V3 signatures can be verified, skipping V4 signature
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
 
WARNING : This php-* RPM are not official Fedora build and
overrides the official ones. Don't file bugs on Fedora Project.
 
Use dedicated forums http://forums.famillecollet.com/
 
  Updating  : php-common                                           [ 1/24]warning: /etc/php.ini created as /etc/php.ini.rpmnew
  Updating  : php-common                   ####################### [ 1/24]
  Installing: mysqlclient15                ####################### [ 2/24]
warning: only V3 signatures can be verified, skipping V4 signature
 
WARNING : This MySQL RPM is not an official Fedora build and it
overrides the official one. Don't file bugs on Fedora Project.
Use dedicated forums http://forums.famillecollet.com/
 
  Updating  : mysql-libs                                           [ 3/24]warning: /etc/my.cnf created as /etc/my.cnf.rpmnew
  Updating  : mysql-libs                   ####################### [ 3/24]
  Updating  : mysql                        ####################### [ 4/24]
  Installing: mysqlclient14                ####################### [ 5/24]
  Installing: sqlite2                      ####################### [ 6/24]
  Updating  : php-pdo                      ####################### [ 7/24]
  Installing: libedit                      ####################### [ 8/24]
  Updating  : php-cli                      ####################### [ 9/24]
  Updating  : php                          ####################### [10/24]
  Updating  : php-mysql                    ####################### [11/24]
  Updating  : php-xml                      ####################### [12/24]
  Installing: perl-DBD-mysql               ####################### [13/24]
  Updating  : mysql-server                 ####################### [14/24]
  Cleanup   : php-xml                      ####################### [15/24]
  Cleanup   : php-common                   ####################### [16/24]
  Cleanup   : php-cli                      ####################### [17/24]
  Removing  : perl-DBD-MySQL               ####################### [18/24]
  Cleanup   : php-pdo                      ####################### [19/24]
  Cleanup   : mysql                        ####################### [20/24]
  Cleanup   : mysql-server                 ####################### [21/24]
  Cleanup   : php                          ####################### [22/24]
  Cleanup   : mysql-libs                   ####################### [23/24]
  Cleanup   : php-mysql                    ####################### [24/24]
 
Installed: perl-DBD-mysql.x86_64 0:4.013-1.el4.rf
Dependency Installed: libedit.x86_64 0:20090923-3.0_1.el4.rf mysqlclient14.x86_64 0:4.1.22-1.el4.remi mysqlclient15.x86_64 0:5.0.67-1.el4.remi sqlite2.x86_64 0:2.8.17-2.el4.remi
Updated: php.x86_64 0:5.3.2-1.el4.remi
Dependency Updated: mysql.x86_64 0:5.1.45-1.el4.remi mysql-libs.x86_64 0:5.1.45-1.el4.remi mysql-server.x86_64 0:5.1.45-1.el4.remi php-cli.x86_64 0:5.3.2-1.el4.remi php-common.x86_64 0:5.3.2-1.el4.remi php-mysql.x86_64 0:5.3.2-1.el4.remi php-pdo.x86_64 0:5.3.2-1.el4.remi php-xml.x86_64 0:5.3.2-1.el4.remi
Replaced: perl-DBD-MySQL.x86_64 0:4.008-1.el4s1
Complete!

When this was done, I just synced /etc/php.ini with the new one /etc/php.ini.rpmnew and /etc/my.cnf with /etc/my.cnf.rpmnew when this was done I just restarted mysql and apache and I php5.3 was now available.

About a year ago I was talking to a friend that was working for a company that offered a fleet tracking service, he described the process and I just got curious about how could I implement the entire system easily, I did some research and there wasn’t a lot of information about the entire picture, there were bits here and there but nothing concrete.

After a couple of days of researching I came up with a Plan to write a platform in one month in my spare time, the intention was to create a fully functional backend and front end pieces, with the ability to track any number of units.

It was a bit challenging because :

1. The short time span I wanted to have this ready.
2. I didn’t have any hardware to test  (GPS receivers, GPRS modems, or even a dedicated server to put the backend), and I didn’t want to spent any money buying any of this.
3. I didn’t had a lot of experience with Google maps API.

The first thing I did was a diagram of how the system should work:

I’m not good with drawings so bear with me, let me explain the diagram:

There will be a tracking device in every truck that will be sending periodic updates about its  location, speed, and other events trough a GPRS link, provided by a conventional GSM network, the device must be programmed to send this updates to a certain ip address (Application server), at specified periods of time.

The application server (background process) will have a daemon running on a specific port waiting for requests from tracking devices, if the tracking device is authorized it will accept the stream and save the info received from the device to a db.

The requirements for the backend application were:

• It should be able to run on Linux.
• The process should be able to handle a large number of requests.
• It needed to be run as a daemon, interfacing with a system to logger.
• It must be able to decode NMEA sentences and save them in a MySQL Db.

The front end application, requirements were:

• Handle user authentication.
• Present the gathered data in real time.
• Generate reports.

Technology selection:

With basic requirements on hand, the next thing was to look the technologies I should be using and this were my choices:

For the backend:

• Gentoo Linux
• Java 1.6
• Mysql 5.0

For the frontend:

• Gentoo Linux
• PHP 5.2
• Jquery 1.3
• Mysql 5.0

In my next post I will describe the approach I took on the back end process and how I started building it.

I received a javascript application, that made cross domain calls to a webservice using the flensed library, this was pretty neat, but filling the dropdown boxes was very slow. Not to mention unnecessary, because I was going to embed the dropdown values into this modal window.

What I did was to refactor the javascript app, into php code to fill the values, and left the important webservice requests alone, I didn’t want to use the flXHR library, because the widget was so simple, so what I did was a php json-rpc proxy client, winch its work would be to post the data received from the client to a remote webservice.

I’m using Zend_Json to encode/decode json, because the php version on production is 5.1, and the php json extensions are not available.

First I create an object that will hold the data to be sent.

01
$re = new stdClass();
02
$re->brand_id  = $_POST['brand_id'];
03
$names         = $_POST['domains'];
04
 
05
$domains = explode(",", $names);
06
$filteredDomains = array();
07
foreach ($domains as $domain) {
08
 $domainParts = parse_url($domain);
09
 if (isset($domainParts['host']) && $domainParts['host'] != '') {
10
 $filteredDomains[] = $domainParts['host'];
11
 }
12
}
13
$re->domains   = $filteredDomains;

That was simple, I didn’t have to worry about filtering the user input, because the webservice will take care of that, the only thing I do, is  create an array of domains, with has only the host part.

after I have my Object ready I encode it as a json string, open a socket and make the request, here is how I did it:

01
$request = Zend_Json::encode($re);
02
$opts = array ('http' => array (
03
    'method'  => 'POST',
04
    'header'  => 'Content-type: application/json',
05
    'content' => $request
06
    ));
07
$context  = stream_context_create($opts);
08
$conection = $fp = fopen($url, 'r', false, $context);
09
if ($conection) {
10
 $reply = '';
11
 while($row = fgets($fp)) {
12
 $reply.= trim($row)."\n";
13
}
14
$reply = Zend_Json::decode($reply);

Pretty simple no?

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.