PHP Linux calling MS SQL server via PDO

Many seem to have problems accessing MS SQLServer databases from Linux via PHP/PDO.

This is how I connect.

I use freeTDS to interface with PHP/PDO.

Setup freeTDS

First I I downloaded latest stable freetds release from http://www.freetds.org/software.html, which happened to be freetds-0.95.95 in my case.

Then the compile pirouette:

1. ./configure --enable-msdblib --disable-debug --with-tdsver=8.0 --enable-msdblib
2. make
3. make install (as root)

This can be checked with tsql -C:

I tried to ./configure TDS version 8.0 which is MS SQL, but I got version 5.0 which is Sybase!

The configuration directory is /usr/local/etc, the configuration file is freetds.conf, (you find an example file in the directory), I added an entry for my MS SQL Server instance:

[rdc01]        host=nn.nn.nn.nn.nn        port = 1433        tds version=8.0

The server is called rdc01, port is MSSQL default 1433 and tds version=8.0.

I tested this with the tsql command:

[tooljn@toossedwvetl3 pgm]$ tsql -S rdc01 -U userid -P 'password' -L database locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" 1> select 'hej' 2> go hej (1 row affected) 1> exit

As you see I specify rdc01 as MS SQL server (rdc01 points back to the entry in the configuration file.

I run a select ‘hej’ (terminated by go on the next line) just to make sure the server responds, and it did with ‘hej (1 row affected)’

And I ended the session with exit.

So far so good freeTDS is installed and working, now to PHP.

Compile PHP

The compile pirouette:

4. ./configure
5. make
6. make install (as root)

I have a lot of ./configure parms for the PDO interface I use:

--with-pdo-mysql=shared \ --with-pdo-dblib=shared \ --with-pdo-odbc=shared,unixODBC,/usr \ --with-unixODBC=/usr \

I use the following PHP code to display  the PDO interfaces installed:

print_r(PDO::getAvailableDrivers());

dblib is what we want.

Now we only have to do a pdo connect:

$pdoHandle = new PDO (dblib:host=rdc01;dbname=database, userid, password);

dblib:host=rdc01 points back to the configuration file.

And that’s it folks.

Improving performance with covering index.

To appreciate this post you must read the post  when fast is not enough first.

I was asked some weeks ago, ‘can you explain your PHP structure assembling program?’.

Why? I asked.

‘It seems we have a problem, it takes about 10 hours to assemble the spare parts structure for the CPD factory!’

Ten hours is definitely too much, in this spare BOM tree there are some 8000 structures or bills of meterial and some +400000 tree nodes. I expected this to take some 15 minutes at the most.

Something must be awful wrong, so I decided to take a look myself first. Sure enough it was the tree assembling PHP program that took some 10 hours. It was setup to distribute the work over 7 parallel threads like this:

This was obviously just cut and pasted from a job with a much larger BOM where the chunks assigned to threads was optimized for that special BOM. As you can see from the forevery iterator, the first chunk takes the first 11000 bills or top nodes. In this example we only have 8000 BOMs to assemble, which means all go into the first chunk effectively single-threading the assembly job, in this case it would probably be better to split the BOMs evenly over 7 chunks like this:

  

Here we do not try to optimize each chunk, it will be good enough to split the BOMs evenly (on 7 chunks) and parallel process them all. Said and done, now we were down to about 1 hour 20 minutes, which was expected since we now run seven parallel threads, still this was not good enough, since I know the PHP program iterates the same SQL query for every node I took a look into PhpMyAdmin to see what’s going on:

  

We run this query over and over again, and a close examine of the table we found an index missing. In this case you really want a covering index as this will drastically improve performance.  This is easily fixed:

We just add an alter statement in the job creating the table.

(A covering index is an index that satisfies a query without accessing the table.)

And now we are down to about three minutes for the tree assembly job.

The moral of this post is: indexes are good, but optimizing too much is not necessarily good.

Relational Big Data

Do you want sufficient fast access to data or the fastest access to data?  

In Big Data and Relational stores  I described differences between Big Data and Relational data. The starting point for my post was - ‘there is a misconception Big Data is needed for efficient manipulation of large volumes of data’. I described Big Data as a document database where a document is a number of values/attributes tied together by a key and not much more. The Big Data manager can do some clever compression and optimization of physical layout to minimize both disk space and access time.

If you remove all but one value you are left with a key value pair , if you store such pairs you have a key value store. It is perfectly ok to store all your Big Data as key value pairs, not very convenient but you can do some heavy optimization both in terms of space and performance. If your data lend itself to a key value store it is very hard to beat Big Data.

If we look at the relational data, it is very hard to compete with Big Data in terms of space utilization, this is not a big deal since disk space is dirt cheap these days. But accessing disk takes time so the larger the disk space is the longer it will take to read it. But there is a remedy for disk access time, move all data into RAM memory and replace regular hard disks with SSD, this will significantly reduce access time. It is true Big Data will benefit even more from fast disk access since the data structure is simpler. Yet again the relational data manager has a trick up his sleeve, instead of browsing through all data the clever database admin creates covering indexes . Theoretically  covering indexes  could be accessed as fast as a key value dataset,  the relational database managers are not there yet, but they will eventually be.

Map and Reduce

Big Data parallelize data access by mapping the data access onto arbitrary number of workers and then reduce the results by merging them together. This parallel programing technique is called Map and Reduce , a design pattern I’m well acquainted with.

I used Map and Reduce 1991 when I created a search engine I called ‘Fast Search for Structured Data’, I put in Structured  to emphasize the search engine was not primarily designed for free text search. The data was stored in key value bitmaps. A test we did comparing my search with DB2 and a  Cobol based serial processing precursor to my map and reduce program. DB2 we had to stop after 23 hours with no result, the cobol program took 20 minutes and my program less than 5 seconds.

Rightly tuned Map and Reduce applied on key value data can be incredibly fast on large data volumes. DB2 and other relational managers have come a long long way since 1991, rightly tuned they can be incredibly fast on large data volumes too.

Relational big data

There is nothing that stops you or the relational database manager from applying Big Data techniques on relational data. Here  I show how I have applied Map and Reduce on relational data.You can also dramatically increase relational performance with good index management.

When are my data volumes so large I need Big Data?

When the data volume becomes a challenge to your design you have ‘big data’ volumes. If your data is contained in a file cabinet and all data access is done by you manually, big data volumes may be a few thousand pieces of data. Does the relational model have conceptual or inherent problems with large data volumes? No it has not, but in some edge cases ‘Big Data’ concepts scales even better. The performance gains comes at a cost, you lose some control of your data and you lose a uniform method (SQL) for data access.

Both Big Data and Relational are borrowing/stealing from each other. There are Big Data with some SQL support and relational managers incorporating Big Data stores. What kind of Data Managers and Data Stores we have in the future remains to be seen. On the global scene the data production super-inflates and many more players want to capture more data. There is a demand for better data management.   Yesterday most data captured for analysis came from ERP systems, today it is probably Web trade and tomorrow social media .

Personally I do not believe we live in a post relational age. I bet my 5 cent on SQL and relational will prevail, but they will evolve in many ways, one of them may be Big Data. I also believe there is a place for Big Data, there will be more large data volume applications tomorrow where speed matters most and there Big Data may play a niche role. I can also see Peoples Republic of China has a great need of Big Data solutions for various reasons, this is probably not a small niche though.

MySQL 5.6 is out, so what is next?

I just read Simon Mudd’s excellent post MYSQL 5.6 is out, so what is next?  Simon put forth his wish list for MySQL 5.7. Well if Simon can wish so can I. First I think Simons wish list is a good one so I only want to add one wish. Parallel select processing of table partitions . Since my use of MySQL is Business Intelligence applications, I have some large (partitioned) tables that is read many times, so what I really want is faster select processing on large partitioned tables . Another option that could help speed up select on partitioned tables is global partitioned indexes i.e. the index is not partitioned. The MySQL 5.6 key cache support is something I will test out first thing when I have migrated to MySQL 5.6. What is holding me back from migrating is our somewhat odd MySQL setup. I have a MySQL replica  in Japan, which I Rsync with our Master database. First I have to upgrade the Japanese replica and then upgrade the master. The problem is I do not know how to upgrade the Japanese MySQL server. It is an Ubuntu 12.04 LTS standard install, MySQL is the normal Ubuntu package and how to upgrade that from MySQL 5.5 to 5.6? I found this post , but I do not try this on a machine on the other side of the globe on a Linux I only installed once. I do not know Ubuntu procedures but I hope they issue a MySQL 5.6 package for 12.04 LTS. The master MYSQL is run on a Mandriva Linux server where I install MySQL RPM packages myself.    

Big Data and Relational data stores

2013-04-01

Big Data and Relational data stores

The concept of Big Data has had a big impact in the corporate world. At least IT is talking about Big Data, even top level IT management. But few actually knows what it is about, a common misconception, ‘we need Big Data to efficiently manage our very large increasing data volumes’. I have not only come across this misconception once or twice the last six month but many times. Where does this ideas come from? Probably from evangelists who have seen the Big Data light, and more important those who think they can make a buck or two by selling Big Data solutions. All of a sudden all major soft- and hardware vendors have Big Data solutions for sale.

What is Big Data then? Before I go into that a short recap of what we have today the relational data model.

In the relational database model data is organised in tables . Data is stored in tables, can be viewed in virtual tables and the result of operations on data is returned in tables. Data in a table is strictly ordered in columns  and rows . Tables are organized into databases . The SCHEMA database  is a special database where all databases, tables, columns and rows must be predefined before they can be used. Data normalisation  is a strict procedure where (unstructured) data is deconstructed  into structured tables. All access to data are done via a common language Structured Query Language  (SQL).

In the relational model there exists a high degree of order, where data is described in strict meta data rules  in the SCHEMA database, these rules cannot be violated, the relational database manager guarantees the data integrity .

In Big Data little of these concepts exists, there is no table structure, no SCHEMA equivalent, no common data access language. In fact Big Data started it’s life as NoSQL  and SCHEMA free databases for documents  and unstructured  data and was only recently rebranded Big Data.

Now wait a minute ‘ Haven’t I heard this before? ’ Yes, in the beginning there was the Lotus Notes database . Lotus Notes database is the mother of Big Data, that is something very few Big Data guys talk about. In the beginning Big Data was basically defined as ‘not relational’, this is not a good marketing concept, I think that’s why the more positive sounding Big Data was conceived.

So what is Big Data?

Big Data a generalized definition.

This is a very superficial generalisation since there is no close relation between the different Big Data model stores. Data is stored in the form of unstructured document  or key value pairs often with JSON  notation, the data is accessed by programs  (often JavaScript). That’s basically it.

Anyone can picture a table, but what does an unstructured document look like, This an attempt

to depict a Big Data Document:

As you see it is text/data/whatever you like to call it, here with the headers Subject, Author, PostedDate,Tags & Body. You store the document by throwing the document to the Big Data Manager and it is stored for later use, simple as that. You access the document by writing a program that checks the document has the headers that defines the type of document you are interested in and then select the subject ‘I like Plankton’ and your program is given the the document(s).

Now we take the same document and deconstruct it into relational tables:

Of the document I constructed four tables (column names are removed for visibility, but they are the sames as the document headers). What I hope is obvious from the pictures - there is more order and complexibility in relational tables compared with Big Data documents . (I simplified the table structure quite a bit and left out a lot of definitions, in real life there is even more order and complexibility). The Tags are moved to a separate table and the link between the Tags and Mails are moved to a special TagMail table. Authors have got a table of it’s own, this is to show: if you add more data about the author(s), that data goes into the Authors table and not into the Mails table. At last I could not resist the temptation to change   PostedDate into a decent format.

Here I cannot  store the mail just by throwing the document at the relational database manager, I have to map the mail into the table structure and issue separate SQL update requests against the tables in the right order. This is definitely more complex than just throw the document as it is to the Big Data Manager. I can then access the mail by joining these tables together with SQL. If this is simpler than create a program of the Big Data manager’s choice is a matter of taste. I happen to think SQL is simpler and better, and it is one unifying language for all relational managers.

Relational data is ordered and documented to a very high degree, whereas Big Data is not, here you order and structure your data in the programs that access the data. It is easier to start with a ‘Big Data’ store than a relational. You don't have model your data structure and define your database schema before you start using it. But this is like pee in your pants, warm and cosy at first, but then it’s just wet and cold (and you stink).  You must be very careful with your Big Data otherwise you will lose control over your data.

This was a brief generalized overview of Big Data & Relational data stores. One question that arises when reading this is, ‘What has this to do with large data volumes’? Not much, that is another aspect of Big Data and the Relational Database I hope to address in another post.

P.S.

Lotus Notes data administration.

‘Where is my important application data, and what is all this crap’, this is what I hear over and over again from LN administrators. ’We must enforce strict rules for creating databases, only trusted developers should be able to create databases’. This is a mantra the LN admins is chanting. They (and even more top level IT management) try to fight the lack of control over data with masochistic self imposed rules, restricting the creation of LN data.

This is not something you find in the relational camp, there order prevails.

SQL foreign key access and tree structures

Relation  in relational database theory means table  and nothing else, table with rows, columns and cells. Relational databases are tables, SQL is the programing language for manipulating and accessing those tables. The relational database is based on the theory of sets, this is a mixed blessing. On the positive side the theory of sets is mathematically proven and that is a good thing in itself at least it says the foundation is sound. But databases in applications are more than sets, a database is a model of the real world the application is supposed to support. E.g. sets do not have structure or order, the real world has. The structure the relational database have is the table structure and simple ‘relational links’ between tables, that can be used to restrict creation or deletion of rows in linked tables. Unfortunately SQL do not understand these links well, you cannot implicitly access linked data or show hierarchies.

Access via foreign keys.

Something I miss in SQL is the ability to walk through children and infer through parents via foreign key links. (You can to a certain extent compensate for these shortages with database views and virtual columns.)

For this database I would like to be able to say:

1. select sono, customername , quantity sum-throu  SOlines as qty for SalesOrder;

1. customername is the customername of the SalesOrder infer through the customerid foreign key link and in case of ambiguity can be qualified as SalesOrder.customername . Customer is a ‘link owner’ of SalesOrder.
2. sum-throu is an infix function that sums the quantity of the SOlines children of the SalesOrder through sono foreign key link. Some other throu functions are max, min & mean. SalesOrder is a ‘link owner’ of the SOlines.

Compare this with the more correct SQL:

1. select sono,customername, sum(quantity) as qty for SalesOrder left join Customer on customerid, left join SOlines on sono group by sono, customername;

I do not like specifying things the computer should be able to figure out. Getting rid of explicit joins is an advantage, even trivial joins are complex for most people. Ever since the introduction of foreign key links in DB2 I wondered when SQL should include access via foreign key links. I have thought of creating a preprocessor in MySQL proxy, but we still use MyISAM storage engine which do not support foreign keys. MyISAM is very convenient for BI system, and historically it has been the fastest storage engine, with the latest MySQL versions this may not be true anymore, I will  have a look at InnoDB when MySQL 5.6 is released.

(Foreign keys are the SQL feature I miss the most in MyISAM. Other features I miss in MySQL are computed/virtual columns and materialized views.)  

Tree structures.

The support for tree structures in SQL is at the best crappy.Good tree structure support includes:

1.  Ability to go up and down the tree, starting from the top or the bottom.
2. Stop traverse the tree at certain nodes or exclude nodes from the result tree.
3. Tree level indicator so you know where in the tree you are.
4. Ability to intercept updating if loops are found in the tree structure

Tree structures are very useful but unfortunately also very complex, so they are ‘under-utilized’. Simple and powerful support for tree structures including the bullets above would take SQL to a new level. Tree structures are a big topic that deserves a post of its own. I end this post with a link to a post  describing a tree structure we use.

 

 

Automatic replication of MySQL databases with Rsync

In some posts   I have written about replicating Business Intelligence information to Local Area Network satellites.

Instead of using normal database backup procedures that guarantees the integrity of the database I use rsync and file copy the database from the source database server over to the target database server. I can do this since I know no updates are done to the database while replicating and I use MySQL MyISAM storage engine. My rsync procedure is very simple, fast and self-healing, but

Do not try this at home

The real reason why I replicate this way is - I like to experiment and try new things and I have not seen anyone replicate databases like this before.  

The Setup

This is how I have set it up. From the controlling ETL server I issue commands via ssh  to the source and target systems:

           Source system           Target system

1        Flush tables

2                                                 Stop MySQL

3        Run Rsync_repl.sh

4                                                Start MySQL.

I use ssh from the ETL server (where my Job scheduler runs) and issue the commands from a job.

First I need to set up SHH (control server):

ssh-keygen

ssh-copy-id -i ~/.ssh/id_rsa.pub userid@targetDBserver

and then sudo (Target server/BI Satelite):

visudo  (add)

MYSQLADM ALL = NOPASSWD: /usr/sbin/service

and then test it:

ssh -t userid@targetDBserver sudo service mysql status

from the control server. You should receive mysql status from the target database server with any prompts for password.

In the source database server I did almost the same thing. First SHH (control server):

ssh-copy-id -i ~/.ssh/id_rsa.pub userid@sourceDBserver

and then sudo (Source server/BI Master):

User_Alias MYSQL_REPL = userid

MYSQL_REPL ALL=(ALL) NOPASSWD:/path2/rsync_repl.sh *

replicate.sh * is a bash script (appended below) that rsync Mysql databases to the target database server. Now I have all things in place and I can system test from my control server

ssh -t userid@targetDBserver sudo service mysql stop

ssh -t userid@sourceDBserver sudo rsync_repl.sh

ssh -t userid@targetDBserver sudo service mysql start

The automation.

With everything in place and tested, I only have to create a job and schedule it.

<?xml version='1.0' encoding='UTF-8' standalone='yes'?> <job name='replicateDB' type='sql'> <!-- This job replicate databases from Source Host to Target Host --> <!-- Note of Warning! This is not according to any safe procedure. Do not try this at home! -->   <tag><name>TargetHost</name><value>TIPaddr</value></tag>   <tag><name>TargetUser</name><value>Tuserid</value></tag>   <tag><name>SourceHost</name><value>SIPaddr</value></tag>   <tag><name>SourceUser</name><value>Suserid</value></tag>     <sql>FLUSH TABLES</sql>   <exit>     <!--Action 1:  stop mysql in target server -->     <!--Action 2:  replicate from Source to Target -->     <!--Action 3:  start mysql in target server -->     <action wait='yes' cmd='ssh' parm='-t @TargetUser@@TargetHost sudo service mysql stop'/>     <action wait='yes' cmd='ssh' parm='-t @SourceUser@@SourceHost sudo /path2/rsync_repl.sh'/>     <action wait='yes' cmd='ssh' parm='-t @TargetUser@@TargetHost sudo service mysql start'/>   </exit> </job>

As a safety measure this job first flush MySQL tables to disk and then runs the exit actions. And that’s it.

If which God forbid the replicated database is trashed, I just have to run the job again. You can guarantee the integrity of the database by running the job repeatedly until no data is replicated, basically if this replication is faster than the ‘update rate’ your database will be fine.  

 I conclude this series of posts  with the rsync_repl.sh script. The comments say it all ‘hopefully the replicated database is OK’ no guarantees!

p.s.

You can make this procedure secure by take a table lock before the second replicate, but in my case it is not necessary.

 

Having PHP FUN(ctions) with SAP shop calendar

The other day I needed to calculate number of workdays in a 60 days period for our Tierp Factory .

Since I had to do this in our BI system I needed the shop calendar for Tierp from SAP. I started to look around for some procedure to extract the shop/factory calendar from SAP. To my surprise I didn’t find anything. The closest I found was  the bapi  DATE_CONVERT_TO_FACTORYDATE. This piece of code takes a date and gives the shop calendar equivalent, i.e. it tells you if the date is a work day or not and the closest  work day if it is a holiday. By exposing a range of dates to date_convert_to_factorydate you can create a shop calendar. I also found some interesting tables in SAP where I could extract the information I needed, but I decided to have some fun with my PHP job scheduler and call  date_convert_to_factorydate repeatedly for a long date span, and assemble the shop calendar with the result. I needed to feed the date_convert_to_factorydate with the id  of the Tierp factory calendar, a range of dates , and a flag  telling date_convert_to_factorydate to look forward or backward for closest work day.

The id is stored in SAP TFACD  table, the date span could easily be created by a function and I wanted a backward search. Then I just had to call SAP once  for each date in the span, for this I could use a job iterator, which would do the job, but with a nasty side effect, since the job calling SAP would repeat itself it would log on and log off from SAP once for each date. Instead I pushed down the iterator to the SAP communicator sap2.php, which logs on once and then iterates through  the dates and appends the results. After this its just a simple matter of loading the result into our BI systems mySQL database. OK here we go:

For you who have followed my posts about my PHP job scheduler  and the SAP communications examples . The interesting pieces here are the PHP functions to generate the start of the calendar in <tag name=’CALSTART’...>  and the use of the iterator <mydriver>  which creates a date span, which the script sap2.php will iterate through and call date_convert_to_factorydate once for each date. The second job in this schedule createCalendar just loads the result into MySQL.

Here you see part of the created shop calendar:

Example - The date 2012-07-08 is a holiday and the closest work day is 2012-07-06.  

If you take the time to study the first job getCalendar you will notice there is actually quite a lot FUNctionality packed in there.

My first use of this shop calendar is an advanced formatted Excel sheet, which I will create and email in subsequent jobs. That will be fun too.