Author Archives: Ralph Mönchmeyer

Hosted Strato server – backups and chained firewall tunneling via SSH and shell

Posted on by

I administer several Linux web servers for partner and customer companies. Most of these servers are V-servers hosted at the German Telekom daughter Strato.

[I have described some reasonable security measures for a Strato server with Opensuse in some articles in this blog. See e.g.:
Strato-V-Server mit Opensuse 12.3 – IV – SSH Key Authentication
Strato-V-Server mit Opensuse 12.3 – III – Stop SSH Root Login
and previous articles referenced there.]

When you work with a hosted server, of course, you want to perform periodic backups. If you – for whatever reasons – do not want to pay for the backup facilities Strato offers, a backup requires that you transfer data from the remote server into your own company network through (encrypted) SSH tunnels. Depending on your specific situation you may wish to create a complete backup or a backup of selected directories on the server. During the backup you want to preserve user-rights and mode settings.

In addition to the topic of creating tunnels across firewalls, the question arises what Linux tool might be convenient to perform the backup task if you work with a shell based connection only. Therefore, I shall briefly discuss what I normally do to perform a manually controlled backup from a hosted Strato server. You can combine the methods described of course with some scripting. The described firewall tunneling by SSH is independent of a specific provider.

Tunneling firewalls with SSH

We presume that our company network has a DMZ with two firewalls – a perimeter firewall “PFW” protecting the DMZ and its hosts against the Internet and one or several inner firewalls “IFW(s)” separating the DMZ from inner network segments. Creating firewall tunnels on the outer and/or inner side of the DMZ is always a critical issue.

Our own backup server system for customer data – let us call it “bup” – is e.g. located in an inner segment of our network. The perimeter firewall “PFW” to the Internet resides on a router and can be configured to forward external WAN ports to ports of special hosts inside the DMZ. Servers inside an inner network segment are allowed to build up SSH connections to hosts in the DMZ across their “IFW(s)”; however the servers in the DMZ are not allowed to initiate SSH connections to the servers in the inner segments. For security reasons we poll data from the servers in the DMZ; we do not forward or push from the DMZ to inner segments. For our scenaio we assume that there is only one inner segment and that we only have to deal with a single “IFW”.

I personally prefer a 2-step process to get the data from the hosted server to the backup destination server “bup”:

  • Step 1: I open a reverse SSH tunnel from a special host “intmid” in our DMZ to the Strato server on the Internet and transfer the data from the Strato server to “intmid”. There, I first of all scan the backup files for viruses and malware.
  • Step 2: Afterwards I stop the reverse tunnel and disallow any incoming connections to “intmid”. I then open a ssh reverse tunnel from my destination server “bup” to “intmid” across the IFW, transfer the files to “bup” and encapsulate them there in an encrypted container.

Some readers may think a 2 step procedure may be exaggerated. If you prefer direct connections from the hosted Strato server to “bup” inside your network you may

  • either use a chain of two reverse SSH tunnels (one tunnel per firewall)
  • or a combination of port forwarding on the “PFW” with
    a reverse tunnel across the “IFW”

Both solutions provide a complete connection from the Strato server to “bup” passing both firewalls of your DMZ. Below I describe both methods briefly.

In both of the following methods we need a reverse SSH tunnel from the backup destination server “bup” (inner segment) to the DMZ host “intmid”. I.e., we export a SSH port of server “bup” temporarily to the host “intmid” in the DMZ. This you may e.g. achieve by entering the command

ssh -R 19999:localhost:22 ssh_user_on_intmid@intmid

at your prompt on host “bup”. The “-R” option triggers the creation of a “reverse” tunnel. Port 19999 is assumed to be unused on “intmid”; port 22 is your local port on “bup” for the SSH daemon sshd. “ssh_user_on_intmid” represents a user for SSH connections on “intmid” with sufficient privileges.

Note: If you just want to build the tunnel in the background without using a terminal or executing remote commands you may use the following combination of options for SSH (instead of a plain “-R”) : ssh -nNTfR …...
Please see the man pages of ssh. These options may be especially helpful when using scripts. So, a more general form of the command could be

ssh -p<sshd port server> -nNTfR <forwarded port server>:localhost:<sshd port local> <username>@<ip server>

Note 1: If and when you really work with background SSH tunnels: Take care not to forget to stop the processes for the background tunnels at the end of your backup operation! In scripts this requires some “pid” determination operations for the running ssh processes.

Note 2: Depending on your sshd configuration you may need to have root rights to build the tunnel.

You can test your reverse tunnel through the inner firewall “IFW” by entering

ssh -p 19999 userid_on_bup@localhost

at the prompt of the “intmid” host. You will of course be asked for the password of a valid user “userid_on_bup” on server “bup” and should then get a prompt on “bup”.

Method 1: Chaining two reverse tunnels

The hosted Strato server allows for a ssh connection on a specific port as this is your only way to work on it. You can use this SSH-connection to build up a reverse tunnel from your DMZ host “intmid” to the Strato server. I.e., the SSH port of your DMZ host shall be exported e.g. as port 19999 on the hosted Strato server. By issuing the following command on “intmid” we tunnel the perimeter firewall “PFW”:

ssh -R 19999:localhost:19999 strato_user@ip_of_your_Strato-server -p 61111

Note: Here we have assumed that the Strato-Server listens on port 61111 as its configured SSH port. (Normally, I change the standard port 22 to a high port number. See:
Strato-V-Server mit Opensuse 12.3 – II – Installation / SSH-Port ändern)

You will of course have to give the password for your “strato_user” on the hosted Strato server before entering the shell there.

Why did we use port 19999 on the DMZ host “intmid”? Because we want to chain the two tunnels – the one from “bup” to “intmid” in the DMZ across firewall “IFW” and the one from “intmid” to our Strato server on the Internet across firewall “PFW” to get a complete encrypted connection from the Strato server to “bup”. Both tunnels “meet” at port 19999 on host “intmid”.

Now, at the prompt of the Strato server you can test the combined (reverse) SSH tunnels by entering

ssh -p 19999 userid_on_bup@localhost

Note: Do not get confused by “localhost” in the command above. You must provide the password for user “userid_on_bup” on the server “bup” (!) inside our inner network segment. Our connected tunnels end up there!

You then should get a prompt for user “user_id_on_bup” on our server “bup”. Ok, we now can successfully tunnel two firewalls at the same time by making use of 2 chained reverse SSH tunnels!

Method 2: Port forwarding on the perimeter firewall

Port forwarding on the perimeter firewall could be an alternative to chaining two tunnels. Having build our obligatory reverse tunnel from “bup” to “intmid” we could in addition change the behavior of our PFW – provided of course that it is configurable. In my case I can establish port forwarding for incoming connections on an some external (WAN) port from a defined IP address on the Internet to a definable port of any host at the other (DMZ) side of the perimeter firewall. E.g I could forward a connection from the Strato server trying to reach the WAN side of the perimeter firewall on port 19999 to port 19999 on “intmid” in the DMZ.

Note: If you really do something like this on your firewall you have to be careful to restrict the forwarding to a defined server on the Internet and to close this firewall hole again after the backup procedure. [It makes no sense here to give commands or details as the configuration procedure varies with the type of firewall used.]

However, for this approach to work with openSSH you in addition have to set a parameter for your sshd configuration in the file “/etc/ssh/sshd_config” on “intmid”:

GatewayPorts yes

Otherwise external hosts will not be allowed to use the reverse tunnel. This setting is somewhat dangerous and you should not forget to change it back.

In this approach we couple (local) port forwarding on the PFW with a reverse tunnel from “bup” to “intmid”. The result is basically the same as with two chained reverse tunnels. However, you have to modify your commands on the Strato server somewhat to use port 19999 at the WAN side of your “PFW”. To test the forwarding plus reverse tunnel on the Strato server you would enter:

ssh userid_on_bup@ip_address_of_your_routers_WAN_interface -p 19999

You have to provide the IP address of the WAN interface of your router. Depending on your DSL provider this may change on a daily basis. So you have to find out what it presently is – e.g by the admin interface of your PFW (router) or by asking an external service (e.g. curl icanhazip.com).

All in all I regard the second method as more complicated and potentially also more dangerous than method 1 – because you must not forget to change and change back your sshd configuration on the DMZ host “intmid” and also to reset your PFW configuration. Therefore, I shall only follow the first approach below.

Use rsync to perform the backup

As we have successfully build up tunnels across our firewalls one may think of using “scp” to perform a backup of the server directories. Actually this is not a good idea if you want to make backups of all server directories including system directories. The reason is:

“scp” follows all symbolic links! At least on Opensuse systems this may lead to infinite loops for certain directories whilst copying!

Therefore it is better to use “tar” or “rsync”. See: http://stackoverflow.com/questions/11030394/is-it-possible-to-make-scp-ignore-symbolic-links-during-copy

Personally, I find “rsync” most convenient. And you can combine it
quite easily with SSH! To use the ports defined for our method 1 (with the two chained reverse SSH tunnels) the right syntax for making a backup of e.g. the directory “/var” would be

rsync -avz -e ‘ssh -p 19999’ /var userid_on_bup@localhost:/backups/bup_strato/server_name/

This command has to be performed on the Strato server. Again: Do not get confused by “localhost” – that’s how SSH tunneling works! The “-a” option of the rsync command means “archive mode”. This preserves the mode and user settings of all files and directories throughout the data copying and performs recursions. “z” leads to a transfer of compressed data which will save you time. “/backups/bup_strato/server_name/” represents an example directory on server “bup” where you want to place the backup files of your special Strato server. Of course you should replace it by your own path on your backup server.

Note 1: You have to issue this commands as root on your Strato server to get access to all of the directories there.
Note 2: Please, note that slash “/” at the end of the directory path on the destination system! And note the missing “/” after the local directory “/var”.
Note 3: Please, note the masking of ‘ssh -p 19999‘ by single quotation marks! [To specify the port by “-p 19999” at the end of the ssh command would not work as you would mix rsync and ssh parameters].
Note 4: The big advantage of rsync naturally is that you only would exchange changed files at the next backup.

Have much fun now with your Strato server and making backups through SSH tunnels!

Lohnt sich eine COBIT 5 Foundation Zertifizierung für Linuxer?

Posted on by

Linux ist heute im Serverbereich vielfach eine etablierte Größe. Dennoch mag es immer noch viele Projekte geben – im Besonderen im deutschen Mittelstand und/oder der öffentlichen Verwaltung – mit denen die Einführung von Linux-Systemen (z.B. aus Gründen von Kostensenkung) erstmalig angegangen wird. Vielleicht mag im einen oder anderen Fall sogar auch der Desktop betroffen sein (s. z.B. das (noch) laufende Limux-Projekt der Stadt München).

Im Zusammenhang mit Linux-Einführungen ist in einem ersten Schritt viel Überzeugungsarbeit zu leisten, bevor entsprechende strategische Entscheidungen getroffen werden. Hierbei sind klassische Themenfelder der IT-Governance betroffen und die Abstimmung mit einem verantwortlichen CIO kommt mit hoher Wahrscheinlichkeit auf die Tagesordnung. Und dann berührt die Welt der betrieblichen IT-Governance direkt diejenigen interner und externer Mitarbeiter wie Berater.

Ich habe letzte Woche die COBIT 5 Foundation Zertifizierung hinter mich gebracht. COBIT 5 ist ein Framework für die Governance und das Management von Unternehmens-IT. Da ich bereits über ein Spektrum personenbezogener Zertifikate im ITSM-, ISM-, Risk Management- und ITIL-Bereich verfüge, habe ich mich selbst gefragt, ob sich der Aufwand überhaupt lohnt. Nach dem Selbststudium des Buches “Praxiswissen COBIT” und dem Absolvieren eines zugehörigen Kurses meine ich jetzt, dass es gerade für Berater im Linux-Umfeld interessant sein kann, sich mit COBIT auseinanderzusetzen. Hierfür ein paar Gründe:

  • 1) IT-Governance und Linux
    Linux einzuführen und/oder substanziell im Unternehmen auszubauen ist immer auch ein GF- und damit Governance-Thema. Für Berater (ggf. auch technische Berater) und engagierte Mitarbeiter lohnt es sich daher durchaus, ein grundlegendes Verständnis in Bezug auf Governance-Themen – und im Besonderen bzgl. IT-Governance – zu erwerben. Und sei im Fall einer technischen (Führungs-) Kraft auch nur zu dem Zweck, die Motive und Leitlinien im Denken des Managements – im Besonderen eines CIO – besser zu verstehen. Hierfür ist ein 1,5-tägiger Foundation Kurs aus meiner Sicht gut geeignet.
    Gerade technik-begeisterte Verfechter von Linux, die sich von “ideologischen” Motiven manchmal nicht frei machen können oder wollen, mag das Credo der COBIT Zielkaskade – nämlich die Ausrichtung der IT auf die Unterstützung von Unternehmenszielen – helfen, den Entscheidungsträgern die “richtigen” Argumente zu liefern. Eine relevante Frage ist: Kann eine linux-basierte Lösung die Unternehmensziele besser, nachhaltiger und ggf. auch kostengünstiger unterstützen als andere Lösungen? Warum? In welchem Umfang? Wie sehen die zugehörigen Business Cases aus?
    “Open” allein ist auf der Management-Ebene zu wenig …. das ist zwar eine Trivialität, aber COBIT erinnert auch Linux-Anhänger zu Recht und explizit daran, das IT kein Selbstzweck ist. Wir müssen schon aufzeigen, wo, wann und warum genau Linux-basierte Lösungen das Unternehmen die Unternehmensziele effektiver und effizienter erreichen lassen als andere Lösungsansätze. Und wir müssen das auch immer wieder selbstkritisch überprüfen.
  • 2) Projekte / Programme als Teil der IT-Governance
    Wer die ISO 20000 oder die ISO 21000 kennt weiß, dass die Begriffswelten dieser Normen Projekte und deren Prozesse ausklammern. Das ist unter dem Aspekt der Allgemeingültigkeit von Normen verständlich – aber dennoch nicht wirklich praxis- und realitätsnah. Aus einem Anforderungsmanagement heraus entstehen neue Services i.d.R. durch Projekte oder bei großen, strategischen Anliege auch durch Programme. Das gilt natürlich auch und gerade im Linux-Umfeld. Daher ist ein Framework gefragt, welches neben den ITSM-Normen und ITSM, ISM- Best Practices auch auf Best Practices im Bereich des Projektmanagement verweist und entsprechende Prozesse zur Government-Unterstützung einbindet. Dies ist bei CoBIT 5 der Fall.

  • 3) Architektur unter verschiedenen Blickwinkeln
    Linux hat aus meiner Sicht natürlicherweise viel mit dem Aufbau konsistenter, aber auch flexibler und erweiterungsfähiger IT-System- und -SW-Architekturen zu tun. Auch hier gilt: Ein Governance-Framework sollte aus diesem Grund Architektur-Aspekte einbinden. Dies ist bei CoBIT 5 grundsätzlich gegeben, nachdem ein Abgleich mit Togaf stattgefunden hat.
  • 4) Richtlinien
    Ich kenne kaum ein Linux-Projekt und schon gar kein Linux-Einführungs-Projekt, dass nicht der Unterstützung durch das Management bedurft hätte. Damit sind Policies und Richtlinien gefragt. Wer sich mit dieser Thematik noch nicht im Rahmen von anderen Management-Systemen befasst hat, erhält über einen COBIT Foundation Kurs einen guten Einstieg.
  • 5) Breites, umfassendes Alignment mit anderen Frameworks
    COBIT 5 stellt aus meiner jetzigen Sicht ein sehr breit angelegtes Rahmenwerk dar, das gezielt mit anderen wichtigen Frameworks und Normen wie u.a. ITIL V3, ISO 20000, ISO 21000, ISO 31000, ISO 38500, COSO, Togaf abgeglichen wurde. Hier ist seit COBIT 4 eine große Fleißarbeit geleistet worden, die den gesamten Governance-Ansatz auf eine deutlich breitere, umfassendere Grundlage stellt als manche andere mir bekannten Frameworks oder Normen. Nach meiner Einschätzung hat das letztlich mehr Vor- als Nachteile. Zumindest verdeutlicht COBIT 5, an welchen Punkten IT-Governance auf andere Management-Systeme oder Best Practices zurückgreifen kann und sollte. Wie oben schon angedeutet, ergibt sich hierbei u.a. auch ein Anknüpfungspunkt, um die Prozess-Welten von Projekten/Programmen und des IT-Service-Managements in sinnvoller Weise miteinander zu verzahnen – auch wenn COBIT die konkrete Ausdeutung für mein Gefühl nicht hinreichend vornimmt. Aber das ist auch ein spezieller Aspekt, der mich wegen meiner Affinität zu SW-Projekten besonders interessiert.

Natürlich kann ein Foundation Kurs zu den genannten Punkten maximal Grundlagen vermitteln und Hinweise darauf geben, wie entsprechende Prozesse in ein governance-getriebenes Prozessmodell integriert werden können. Das ist aus meiner Sicht jedoch schon interessant genug – auch wenn die Untergliederung von Prozessen in sog. “Praktiken” für den ITILianer etwas gewöhnungsbedürftig ist. Zudem finde ich, dass COBIT aus Sicht des Managements eine ebenso gute Motivation für die Bschäftigung mit weiterführenden oder spezialisierten IT-bezogene Management-Frameworks und Normen bietet wie ITIL dies aus einer ganz anderen Perspektive auch tut.

Den Aufwand für die internationale, personenbezogene Foundation Zertifizierung möchte persönlich ich im Vergleich mit anderen Zertifizierungsprüfungen als wirklich überschaubar einstufen. Das von mir gewählte Buch “Praxiswissen COBIT” ist als Referenz und für ein Selbststudium sicher gut geeignet – auch wenn man nach dem Lesen nicht unbedingt einschätzen kann, was für eine Foundation Prüfung relevant ist. Der Verfasser Markus Gaulke sollte in künftigen Auflagen vielleicht zu Beginn des Buches entsprechende Lesehinweise geben und nicht erst auf S. 363. Die relevanten Kapitel kann man an einem längeren Wochenende gut durcharbeiten. Den Besuch eines ca. 1,5 tägigen Kurses zur Vorbereitung auf die Prüfung empfand ich auf dieser Grundlage als sehr hilfreich, wenn auch nicht zwingend erforderlich. Der von mir besuchte Kurs beim mITSM war erfrischend lebendig und rückte zudem die eine oder andere vorschnell auf der Basis anderer Frameworks oder Normen gefasste Überzeugung gerade. Ein Kurs hilft ferner auch, die oft etwas verquere Fragestellungen, den Stil sowie Haken und Ösen der Prüfungen besser einzuschätzen – zumal mir nach der Prüfung manche Frage zu wortwörtlich aus dem Englischen übersetzt erscheint.

Eine Warnung noch : IT-Governance ist kein Thema für Technik-Freaks – es geht vielmehr
um die Steuerung von Prozessen zur Erreichung von Unternehmens- oder Organisationszielen. Natürlich wird dabei auch die Organisation technischer Aufgabenstellungen berührt. Aber technologische Fragen sind kein primäres Thema eines COBIT-Foundation Kurses – wenngleich sich manches Steuerungselement an konkreten Beispielen gut verdeutlichen lässt.

Aber niemand hat ja festgelegt, dass nicht auch technik-affine Mitarbeiter und Führungskräfte über ihren Tellerrand hinausblicken dürfen. Und eine stringente IT-Governance ist bei den vielfältigen, heterogenen und offenen Angeboten an potentiellen linux-basierten Lösungen für Aufgabenstellungen eines modernen Unternehmens sicher ein essentieller Faktor, durch den die ungeheure Dynamik von Open Source Entwicklungen erst zum Vorteil von Unternehmen besser genutzt und auf priorisierte Ziele hin ausgerichtet werden kann. Führung, Agilität und Prinzipien einer kreativen Selbstorganisation sind für mich überhaupt keine Widersprüche sondern einander ergänzende Faktoren – gerade im Open Source Umfeld. COBIT kann der Unternehmensführung wie dem IT-Management helfen, wirksame Leitplanken für die fruchtbare Entfaltung von Agilität und Dynamik zu setzen.

Character sets and Ajax, PHP, JSON – decode/encode your strings properly!

Posted on by

Ajax and PHP programs run in a more or less complex environment. Very often you want to transfer data from a browser client via Ajax to a PHP server and save them after some manipulation into a MariaDB or MySQL database. As you use Ajax you expect some asynchronous response sent from the PHP sever back at the client. This answer can have a complicated structure and may contain a combination of data from different sources – e.g. the database or from your PHP programs.

If and when all components and interfaces [web pages, Ajax-programs, the web server, files, PHP programs, PHP/MySQL interfaces, MySQL …) are set up for a UTF-8 character encoding you probably will not experience any problems regarding the transfer of POST data to a PHP server by Ajax and further on into a MySQL database via a suitable PHP/MySQL interface. The same would be true for the Ajax response. In this article I shall assume that the Ajax response is expected as a JSON object, which we prepare by using the function json_encode() on the PHP side.

Due to provider restrictions or customer requirements you may not always find such an ideal “utf-8 only” situation where you can control all components. Instead, you may be forced to combine your PHP classes and methods with programs others have developed. E.g., your classes may be included into programs of others. And what you have to or should do with the Ajax data may depend on settings others have already performed in classes which are beyond your control. A simple example where a lack of communication may lead to trouble is the following:

You may find situations where the data transfer from the server side PHP-programs into a MySQL database is pre-configured by a (foreign) class controlling the PHP/MySQL interface for a western character set iso-8859-1 instead of utf-8. Related settings of the MySQL system (SET NAMES) affect the PHP mysql, mysqli and pdo_mysql interfaces for the control program. In such situations the following statement would hold :

If your own classes and methods do not provide data encoded with the expected character set at your PHP/MySQL interface, you may get garbage inside the database. This may in particular lead to classical "Umlaut"-problems for German, French and other languages.

So, as a PHP developer you are prepared to decode the POST or GET data strings of an Ajax request properly before transferring such string data to the database! However, what one sometimes may forget is the following:

You have to encode all data contributing to your Ajax response – which you may deliver in a JSON format to your browser – properly, too. And this encoding may depend on the respective data source or its interface to PHP.

And even worse: For one Ajax request the response data may be fetched from multiple sources – each encoded for a different charset. In case you want to use the JSON format for the response data you probably use the json_encode() function. But this function may react allergic to an offered combination of strings encoded in different charsets! So, a proper and suitable encoding of string data from different sources should be performed before starting the json_encode()-process in your PHP-program ! This requires a complete knowledge and control over the encoding of data from all sources that contribute strings to an Ajax response !

Otherwise, you may never get any (reasonable) result data back to your javascript function handling the Ajax response data. This happened to me lately, when I deployed classes which worked perfectly in a UTF-8 environment on a French LAMP system where the PHP/MySQL interfaces were set up for a latin-1 character set (corresponding to iso-8859-1). Due to proper decoding on the server side Ajax data went correctly into a database –
however, the expected complex response data comprising database data, data from files and programs were not generated at all or incorrectly.

As I found it somewhat difficult to analyze what happened, I provide a short overview over some important steps for such Ajax situations below.

Setting a character set for the PHP/MySQL interface(s)

The character code setting for the PHP/MySQL-connections is performed from the PHP side by issuing a SQL command. For the old interface mysql-interface, e.g., this may look like

$sql_unames = “SET NAMES ‘latin1′”;
mysql_query($sql_unames, $this->db);

Note that this setting for the PHP/MySQL-interfaces has nothing to do with the MySQL character settings for the base, a specific table or a table row! The NAMES settings actually prepares the database for the character set of incoming and outgoing data streams. The transformation of string data to (or from) the character code defined in your database/tables/columns is additionally and internally done inside the MySQL RDBMS.

With such a PHP/MySQL setting you may arrive at situations like the one displayed in the following drawing:

ajax_encoding

In the case sketched above I expect the result data to come back to the server in a JSON format.

Looking at the transfer processes, one of the first questions is: How does or should the Ajax transfer to the server for POST data work with respect to character sets ?

Transfer POST data of Ajax-requests encoded with UTF-8

Normally, when you transfer data for a web form to a server you have to choose between the GET or the POST mechanism. This, of course, is also true for Ajax controlled data transfers. Before starting an Ajax request you have to set up the Ajax environment and objects in your Javascript programs accordingly. But potentially there are more things to configure. Via e.g. jQuery you may define an option regarding the so called “ContentType” for the character encoding of the transfer data, the “type” of the data to be sent to the server and the “dataType” for the structural format of the response data:

$.ajaxSetup( { …..
    ContentType : ‘application/x-www-form-urlencoded; charset=UTF-8’
    type : ‘POST’
    dataType : ‘json’
…});

With the first option you could at least in principle change the charset for the encoding to iso-8859-1. However, I normally refrain from doing so, because it is not compliant with W3C-requirements. The jQuery/Ajax documentation says:

" The W3C XMLHttpRequest specification dictates that the charset is always UTF-8; specifying another charset will not force the browser to change the encoding."
(See: http://api.jquery.com/jquery.ajax/).

Therefore, I use the standard and send POST data in Ajax-requests utf-8 encoded. In our scenario this setting would lead to dramatic consequences on the PHP/MySQL side if you did not properly decode the sent data on the server before saving them into the database.

In case you have used the “SET NAMES” SQL command to activate a latin-1 encoded database connection, you must apply the function utf8_decode() to utf-8 encoded strings in the $_POST-array before you want to save these strings in some database table-
fields!

In case you want to deploy Ajax and PHP codes in an international environment where “SET NAMES” may vary from server to server it is wise to analyze your PHP/MySQL interface settings before deciding whether and how to decode. Therefore, the PHP/MySQL interface settings should be available information for your PHP methods dealing with Ajax data.

Note, that the function utf8_decode() decodes to the iso-8859-1-charset, only. For some cases this may not be sufficient (think of the €-sign !). Then the more general function iconv() is your friend on the PHP side.
See: http://de1.php.net/manual/de/function.iconv.php.

Now, you may think we have gained what we wanted for the “Ajax to database” transfer. Not quite:

The strings you eventually want to save in the database may be composed of substrings coming from different sources – not only from the $_POST array after an Ajax request. So, you need to control where from and in which charset the strings you compose come from. A very simple source is the program itself – but the program files (and/or includes) may have another charset than the $-POST-data! So, the individual strings may require a different de- or en-coding treatment! For that purpose the general “Multibyte String Functions” of PHP may be of help for testing or creating specific encodings. See e.g.: http://php.net/manual/de/function.mb-detect-encoding.php

Do not forget to encode Ajax response data properly!

An Ajax request is answered asynchronously. I often use the JSON format for the response from the server to the browser. It is easy to handle and well suited for Javascript. On the PHP the json_encode() function helps to create the required JSON object from the components of an array. However, the strings combined into a JSON conform Ajax data response object may come from different sources. In my scenario I had to combine data defined

  • in data files,
  • in PHP class definition files,
  • in a MySQL database.

All of these sources may provide the data with a different character encoding. In the most simple case, think about a combination (inclusion) of PHP files which some other developers have encoded in UTF-8 whereas your own files are encoded in iso-8859-1. This may e.g. be due to different standard settings in the Eclipse environments the programmers use.

Or let’s take another more realistic example fitting our scenario above:
Assume you have to work with some strings which contain a German “umlaut” as “ü”, “ö”, “ä” or “ß”. E.g., in your $_POST-array you may have received (via Ajax) some string “München” in W3C compliant UTF-8 format. Now, due to database requirements discussed above you convert the “München” string in $_POST[‘muc’] with

$str_x = utf8_decode($_POST[‘muc’]);

to iso-8859-1 before saving it into the database. Then the correct characters would appear in your database table (a fact which you could check by phpMyAdmin).

However, in some other parts of your your UTF-8 encoded PHP(5) program file (or in included files) you (or some other contributing programmers) may have defined a string variable $str_x that eventually also shall contribute to a JSON formatted Ajax response:

$str_y = “München”;

Sooner or later, you prepare your Ajax response – maybe by something like :

$ay_ajax_response[‘x’] = $str_x;
$ay_ajax_response[‘y’] = $str_y;
$ajax_response = json_encode($ay_ajax_response);
echo $ajax_response;

n
(Of course I oversimplify; you would not use global data but much more sophisticated things … ). In such a situation you may never see your expected response values correctly. Depending on your concrete setup of the Ajax connection in your client Javascript/jQuery program you may not even get anything on the client side. Why? Because the PHP function json_encode() will return “false” ! Reason:

json_encode() expects all input strings to be utf-8 encoded !

But this is not the case for your decoded $str_x in our example! Now, think of string data coming from the database in our scenario:

For the same reason, weird things would also happen if you just retrieved some data from a database without thinking about the encoding of the PHP/MySQL interface. If you had used “SET NAMES” to set the PHP/MySQL interface to latin-1, then retrieved some string data from the base and injected them directly – i.e. without a transformation to utf-8 by utf8_encode() – into your Ajax response you would run into the same trouble as described in the example above. Therefore:

Before using json_encode() make sure that all strings in your input array – from whichever source they may come – are properly encoded in UTF-8 ! Watch out for specific settings for the database connection which may have been set by database handling objects. If your original strings coming from the database are encoded in iso-8859-1 you can use the PHP function ut8_encode() to get proper UTF-8 strings!

Some rules

The scenario and examples discussed above illustrate several important points when working with several sources that may use different charsets. I try to summarize these points as rules :

  • All program files should be written using the same character set encoding. (This rule seems natural but is not always guaranteed if the results of different developer groups have to be combined)
  • You should write your program statements such that you do not rely on some assumed charsets. Investigate the strings you deal with – e.g. with the PHP multibyte string functions “mb_….()” and test them for their (probable) charset.
  • When you actively use “SET NAMES” from your PHP code you should always make this information (i.e. the character code choice) available to the Ajax handling methods of your PHP objects dealing with the Ajax interface. This information is e.g. required to transform the POST input string data of Ajax requests into the right charset expected by your PHP/MySQL-interface.
  • In case of composing strings from different sources align the character enprintcoding over all sources. Relevant sources with different charsets may e.g. be: data files, data bases, POST/GET data, ..
  • In case you have used “SET NAMES” to use some specific character set for your MySQL database connection do not forget to decode properly before saving into the database and to encode data fetched from the base properly into utf-8 if these data shall be part of the Ajax response. Relevant functions for utf-8/iso-8859-1 transformations may be utf8_encode(), utf8_decode and for more general cases iconv().
  • If you use strings in your program that are encoded in some other charset than utf-8, but which shall contribute to your JSON formatted Ajax response, encode all these strings in utf-8 before you apply json_encode() ! Verify that all strings are in UTF8 format before using json_encode().
  • Always check the return value of json_encode() and react properly by something like
    if (json_encode($…) === false
    ) {
    …. error handling code …
    }
  • Last but not least: When designing your classes and methods for the Ajax handling on the PHP side always think about some internal debugging features, because due to restrictions and missing features you may not be able to fully debug variables on the server. You may need extra information in your Ajax response and you may need switches to change from a Ajax controlled situation to a standard synchronous client/server situation where you could directly see echo/print_r – outputs from the server. Take into account that in some situation you may never get the Ajax response to the client …

I hope, these rules may help some people that have to work with jQuery/Ajax, PHP, MySQL and are confronted with more than one character set.

PHP/OO/Schemata: Composite SLAVE objects and (X)HTML generator methods

Posted on by

Some time ago I wrote an article about how to treat groups of associated, related properties of a Real World Object [“RWO”] when representing it by an object [“PWO”] in PHP.

We assumed that the property description of a certain object class is done with the help of a so called “SCHEMA” (a file with a list of definitions and rules or Schema program using definitions saved in a database Schema table). A SCHEMA is specific for a PWO class. A SCHEMA defines object relations, object properties, the relations between certain object properties, the relation of object properties with database fields and of course the type of each property, the property’s own properties as well as associated constraints. Such “SCHEMATA” would play a central role in a web application as they encapsulate vital structural and detail information about objects and their properties.

Every object constructors would refer to an appropriate SCHEMA, as well as web generators for the creation e.g. of form or web template elements would do. Actually, any reasonable (web) application would of course work with multiple SCHEMATA – each for a different object class. SCHEMATA give us a flexible mode to change or adapt a classes properties and relations. They also close the gap between the OO definitions and a relational database.

To handle groups of associated properties in such an environment I suggested the following:

  • Split the SCHEMA-information for properties and related database fields into several “SLAVE-Schemata” which would be built as sub objects within the MAIN SCHEMA object. Each SCHEMA would describe a certain group of closely associated object properties.
  • Create and use “SLAVE PWO SGL objects” as encapsulated sub-objects in a MAIN SGL PWO. Each SLAVE PWO object gets it’s own properties defined in a related SLAVE-SCHEMA.
  • Each of the SLAVE PWO objects receives it’s knowledge about it’s individual SLAVE-SCHEMA by an injection process during construction.

See:
PHP/OO/Schemata: Decouple property groups by combined composite patterns

Each PWO object, representing a single RWO we shall call a SGL PWO. It comprises a series of sub-objects: SLAVE SGL PWOs that refer to corresponding SLAVE Schemata of a MAIN Schema. See the illustration from the article named above:

Slave_schemata

In the named article I had discussed that one can iterate methods for complete database interactions over the MAIN and the SLAVE objects. The same is true for interactions with POST or SESSION data in a CMS like web application. So, there is no need to rewrite core method code originally written for objects which comprise all object properties in just one property/field-SCHEMA without any SLAVE objects. At least with respect to field checks and database interactions.

This works if and when a SGL object is derived from a base class which comprises all required methods to deal with the database, POST and SESSION data. Each MAIN or SLAVE SGL PWO knows itself how to store/fetch it’s property data into/from database tables (or $_POST or $_SESSION arrays) and uses the same methods inherited from common SGL base classes (= parent classes) to do so.

I have meanwhile introduced and realized this SLAVE PWO and SLAVE SCHEMA approach in a quite general form in my web application framework. In this article I briefly want to discuss some unexpected consequences for HTML
generator methods in a web or CMS context.

(X)HTML-Generator methods – where to place them ?

When you design CMS like web applications you always have to deal with Template [TPL] structures and objects that fill the templates with reasonable formatted contents. The contents may be just text or images or links inserted into template placeholders. In more complicated cases (like e.g. the generation of maintenance masks with forms), however, you may have to generate complete HTML fragments which depend on property/field information given in your respective SCHEMA.

A basic design question is: Where do we place the generator methods? Should the SGL PWOs know how to generate their property contents or should rather a “Template Control Object” – a “TCO” – know what to do? I have always preferred the second approach. Reason:

TPL aspects may become very specific, so the methods may need to know something about the TPL structures – and this is nothing that I want to incorporate into the OO representation [PWO] of real world objects.

Over time I have developed a bunch of generator methods for all kind of funny things required in customer projects. The methods are defined in base classes for Template Control Objects and or special purpose sub classes injected into TCOs. A TCO knows about its type of TPL and works accordingly. (By the way: With Pear ITX or Smarty you can realize a complete separation of (X)HTML-code and the PHP code).

(X)HTML-Generator methods – which MAIN or SLAVE PWO and which MAIN/SLAVE SCHEMA are we dealing with ?

In addition to some property/field identifiers a (X)HTML generator method has of course to know what SGL PWO and what SCHEMA it has to work with. This information can be fetched either by the TCO due to some rules or can be directly injected into the methods.

In the past I wanted to keep interfaces lean. In many applications the SGL PWO object was a classical singleton. So, it could relatively easily be received or identified by the central TCO. I did not see any reason to clutter TCO method interfaces with object references that were already known to their TCO object. So, my generator methods referred and used the SGL PWO object and it’s SCHEMA by invoking it with the “$this”-operator:

function someTCOgenerator_method() {
…..
do something with $this->SGL and $this->Schema
….
}

However, in the light of a more general approach this appears to be a too simplistic idea.

If we regard a SLAVE SGL object as a relatively compact entity inside a PWO – as a SLAVE object with its own property and field information SCHEMA – than we see: for a generator method it behaves almost like an independent object different from the MAIN PWO. This situation is comparable to one where the generator method really would be requested to operate on instances a completely different PWO class:

A HTML generator method needs to know the qualities of certain OO properties and associated database field definitions. In our approach with MAIN SGL PWOs comprising composite SLAVE SGL PWO objects each SGL object knows exactly about its associated MAIN or SLAVE Schema object. To work correctly the generator method must get access to this specific SCHEMA. This would in a reasonable application design also be valid for PWO objects representing other, i.e. different types of RWOs.

A (X)HTML generator method can work properly as soon as it knows about

  • the SGL object,
  • the object property (identified by some name, index or other unique information) to operate on and generate HTML code for,
  • the SCHEMA describing the qualities of the object property and related database fields.

This would in
our approach also be given for our SLAVE SGL objects or any PWO as soon as we inject it into our generator method.

Therefore, (X)HTML generator methods of TCOs should be programmed according to the following rules:

  • Do not assume that there is only one defined class of PWO SGL objects that the Template Control Object TCO needs to know about and needs to apply it’s generator methods to.
  • Instead enable the (X)HTML generator methods of a TCO to be able to work with any kind of PWO and its properties – as long as the PWO provides the appropriate SCHEMA information.
  • Inject the SGL [SLAVE] PWO and thereby also its associated [SLAVE] SCHEMA into each TCO generator method:
    function someTCOgenerator_method($SGL_ext, …..).
  • Do not refer to the TCO’s knowledge about a SGL PWO by using the “$this” operator (like “$this->Sgl”) inside a generator method of a TCO; refer all generator action to a local $SGL object reference that points to the injected (!) object $SGL_ext:
    $SGL = $SGL_ext.
    Also refer to a local $Schema which points to the $SGL->Schema of the injected $SGL object:
    $Schema = $Sgl->Schema.
    (Remember: Objects are transferred by reference !)

These recipes give us the aspired flexibility to deal with properties both of SLAVE objects and objects of a different PWO class.

The injection is a small but structurally important difference in comparison to the database interaction methods directly incorporated in SGL objects or better their base classes. Here the TCO (!) method must receive the required information – and we gain flexibility when we inject the SGL object into it (plus information identifying the property for which a HTML fragment has to be generated for).

Had I respected the rules above already some time ago it would have saved me much time now. Now, I had to adapt the interfaces of many of my generator methods in my TCO base classes.

Again, I found one experience confirmed:

  • In case of doubt do not hesitate to use loose object coupling via injection on general methods which could in principle be applied to more and other objects than the ones the object containing the method knows about at the time of your class design.
  • Use injection even if it may look strange when you need to do something like
    $this->someTCOgenerator_method($this->KnownObject);
    i.e., when you inject something the present object containing the method knows about already.

It will save time afterwards when iterator patterns over other objects have to be used and when you may access the (public) method from outside, too.

Iteration over SLAVE objects

Now, if our (X)HTML-generator methods are prepared for injection of SGL PWO objects, we have no more difficulties to generate HTML fragments required in templates for selected properties of MAIN and SLAVE PWO SGL objects:

We just have to iterate the usage of the generator method for the properties/fields of the MAIN SGL PWO as well as its SLAVE SGL PWOs. By getting the SGL object injected the method also knows about the right SCHEMA to use and provide required detail information for the generation of related HTML code (e.g. for a form element).

Think about a situation in which we want to provide a form with input fields which a user can use to update data for all properties of a certain PWO. We just have to apply generator methods to create the required input field types according to the appropriate SCHEMA informations. For this

  • we loop (iterate) over the MAIN and all it’s SLAVE objects,
  • identify all properties according to the MAIN or SLAVE SCHEMA
    information
  • and apply the generator method by injecting the relevant property identifier plus the MAIN/SLAVE-SGL object in question.

Mission accomplished.

The principle of iteration over SLAVE objects is actually nothing new to us: We used it already when dealing with the database interaction methods. (It is a basic ingredient of a composite pattern).

If we only want to work on selected properties then we need to know which of the properties is located in which SLAVE PWO and described in which SLAVE SCHEMA. To be able to do so, we should create and use an array

  • that collects information about which PWO property belongs to which of the PWOs SLAVE objects
  • and which is filled in course of the construction process of a PWO.

Conclusion

When realizing a composite pattern in the form of SLAVE objects (with SLAVE Schemata) to deal with closely associated property groups of complex objects you can apply existing base class methods and iterate over the SLAVE objects to accomplish complete transactions affecting all properties of a structured PWO. This principle can be extended to (X)HTML generator methods of Template Control Objects, if these methods are prepared to receive the SLAVE SGL PWO objects by injection. If we only want to apply generator methods on a bunch of selected properties, we should use an array associating each PWO property with a SLAVE PWO SGL object.

MySQL: LOAD DATA INFILE, multiple csv-files and index creation for big tables

Posted on by

In some of the last articles in this blog I have discussed the following aspects of a data import from csv-files into MySQL ISAM tables:

  • The use of the SQL command “LOAD DATA INFILE” may accelerate the import by a factor of at least 18 in comparison to reading the data line by line with fgetcsv() and inserting single records into the database table.
  • The creation of a (small) helper table may often be useful to improve the performance of repeated consistency checks based on the evaluation of “derived result sets” from COUNT(DISTINCT …) subselects or subqueries on big tables.

The first point implies a straightforward use of “LOAD DATA INFILE” in your PHP programs. The second point may be applicable if certain data structures are given and it requires a balanced use of indices.

See:
Importing large csv files with PHP into a MySQL MyISAM table
MySQL/PHP: LOAD DATA – import of large csv files – linearity with record number?
MySQL/Aggregation: Comparing COUNT(DISTINCT) values on big tables

In these articles I have more or less neglected two things:

  • A possible splitting of the import process into several sequential processes. Instead of loading one big file we may rather distribute the data records over several files. Each individual process loads data from just one file; so the data are loaded file after file into the table.
  • The impact of index creation on the data import time.

Reasons for a sequential data loading from multiple csv-files are: Better control of the loading process due to shorter times for well defined data packages and also an adaption to memory limits. However, without tests it is not clear whether such an approach may have an impact on the overall loading performance.

The second point raises the question whether and how index creation influences the efficiency of “LOAD DATA INFILE”. Note that the MySQL documentation explicitly recommends to separate index creation from the loading process. See:
http://dev.mysql.com/doc/refman/5.1/de/load-data.html

This article provides some numbers from test runs for both measures. We shall see how the combination of both points influences the overall performance of the data import. Furthermore, an optimized loading process may influence your strategy regarding consistency checks during a sequential loading of several files.

Loading data from a sequence of multiple files instead of loading from one big file

I briefly have a look at some of the aspects of loading data from a sequence of files. In the article PHP/MySQL/Linux: File upload, database import and access right problems I have already discussed that one may split the data to be imported into bunches of records and distribute them over several files. I have pointed out that the data splitting shall not be done by fields. Instead records should be distributed over multiple files; but each record should contain all of the associated (!) fields if you want to profit from the performance of “LOAD DATA INFILE”.

A further aspect you have to take care of are logical relations between the data records. Such relations may become relevant in case you already want to work with the data in between 2 file loads:

Lets say you have
data with the structure

nr,    m,n,    i    Q1, Q2, Q3

m,n,i shall represent keys which together form an unique key of each record. An [m,n]-combination may e.g. define an object called “node” and [i] may represent time periods. Let us further assume that after having loaded one file you for some reason want to check the complete SUM (Q2) over all (!) i-values for each “node” before you (or a PHP program) import the data of the next file. Then you must organize your data such that all records related to one and the same node are placed inside exactly one file – otherwise you would get a moving target for your SUM when loading your files. In general, the distribution of the records over several files must comply with logical conditions if you want to work with the data already during the loading process.

Another aspect of distributing records over several files may be memory considerations: In one of the articles named above we saw that the loading and index generation consume more and more memory for buffering as the absolute numbers of records grow. Therefore, if you want to avoid conflicts with limited RAM, it may be reasonable to load bunches of records (in form of several csv-files) instead of processing on one gigantic file with “LOAD DATA INFILE” .

In most cases the central objective of using data packages is a better control of the file transfer and loading processes: With multiple files you can build up a “file pipeline” on the server. The files in this “pipeline” are handled by a repeatedly running import job. The required time interval to transfer each file to the server and load its data into the base can be limited to a reasonable value by choosing an appropriate number of records per file. After each file is processed on the server you may analyze the new table status, the pipeline status and send success or failure messages to the client (e.g. a browser). With a web client the communication exchange can be managed by Ajax.

A sequential loading of files also provides you with the possibility to fill the file pipeline on the server in parallel to loading data of already completely received files into the database. Despite a more complicated communication structure between a browser client and the server you can still keep control over both the transfers from the client to the server as well as over a sequence of server processes loading loading data into the base – file by file.

I have realized the sequential handling of several csv-files on a web-server with Ajax and PHP in the following way:
A web client starts an Ajax controlled PHP job that analyzes the number of files in a pipeline (a special directory) on the server and chooses the next file to import by some given rules. After having loaded the data the job returns control to the client and sends information about the processed file, the loaded number of records and the new status of the database table. Depending on parameter settings there and of course depending on the quality of the status information from the server a new server import job for the next file may automatically be started from the client with the help of Ajax from the client. This continues until the pipeline is empty. The last job may create required indices on the filled database table – depending on user settings.

Is the total import time independent of a record distribution over multiple files?

In the article MySQL/PHP: LOAD DATA – import of large csv files – linearity with record number? I discussed the seemingly linear dependency of “LOAD DATA INFILE” on the total record number of ONE file. Now, we should in addition investigate possible performance variations for import processes

  • that load a given number of records sequentially loaded from
    several csv-files;
  • with or without a parallel building of (unique) indices on defined columns.
  • that perform the creation of required (unique) indices only AFTER all records have been loaded into the database table.

Importing 5 million records from one or two files

Let us again take the data structure

nr,    m,n,    i    Q1, Q2, Q3

All Q-values are floating point numbers with double accuracy (MySQL type DOUBLE) in our scenario. We assume that at the beginning of our import runs we have already defined some indices on our database table:

  • a unique index (with auto-incrementation) on the column “nr”
  • a unique index defined for the columns “[n,m,i]”

In the course of our tests we shall study the effect of

  • dropping these indices,
  • creating them whilst filling the table with data,
  • creating them after having filled the table with data.

For each test case with 5 million records we look at two different situations :

  • Importing one csv-file with all 5 million records.
  • Sequentially importing 2 csv-files – one with 3 million records and one with 2 million records.

Our test server is a relatively small one in form of a KVM virtualized LAMP server with 2 GByte assigned RAM, CPU INTEL Q9550, Raid 10 disk array. For the tests I worked with MySQL MyISAM tables. In my concrete test table I chose:

distinct m values : 1 (as an extreme situation)
distinct n values : 13580
distinct i values : 386

I got the following numbers:

import by 1 file import by 2 files with unique ai-index on nr with non-unique index on nr without any index on nr with unique index on [n,m,i] with non-unique index on [n,m,i] without any index on [n,m,i] response time
yes no yes no no yes no no 30 secs
no yes yes no no yes no no 30 secs
yes no no yes no yes no no 30 secs
no yes no yes no yes no no 30 secs
yes no no yes no no yes no 21,3 secs
no yes no yes no no yes no 24,8 secs
yes no no no yes yes no no 19.2 secs
no yes no no yes yes no no 19.4 secs
yes no no no yes no yes no 9.8 secs
no yes no no yes no yes no 13.3 secs
yes no no no yes no no yes 8.7 secs
no yes no no yes no no yes 8.8 secs

The accuracy of the response times is in the range of a second – depending on the general situation on the virtualization host and its disk system at the measurements. The response times do not include any file transfer times between the client and the server in our LAN. However, the response time for the situations with a sequential load of several files includes some common Ajax overhead in all cases plus the time to load the PHP program files into our Apache server again. But the system buffers and caches sufficiently. So, the interaction between the client and the server should only have a marginal impact.

What do we learn from the results ? I dare to derive 4 rules:

  • Rule 1: Any unique index built during “LOAD DATA INFILE” reduces performance (due to checks which have to be performed). So, one should carefully check whether a unique index really is required later on when you work with the data.
  • Rule 2: The total import time for several or one big file are comparable if a unique index is created whilst or after data loading.
  • Rule 3: Any building of a non-unique index during the import increases the total import time for a sequential load of several csv-files in comparison to a loading process for just one big csv-file.
  • Rule 4: Without any index defined the total loading time for several files is comparable to the loading file for one big csv-file.

The most surprising effect for me was that described in rule 3. However, regarding the accuracy in the range of a second we may have some doubts whether we see a real effect. So, we should have a look at a situation with more data records and more files to confirm this rule.

Now, it will of course be interesting to see a comparison between different loading processes with a creation of the [n,m,i]-index AFTER the processing of all files with “LOAD DATA INFILE”. For such cases technically we drop any index before we load any file data and create the index only after the data of all files have been loaded into the base. Note, that index dropping may take some time, too.

Table: Comparison of server response times for index creation AFTER and WHILST data loading 5 million records with “LOAD DATA INFILE”

n

import by 1 files import by 2 files without any index on nr with unique index building on [n,m,i] whilst loading with non-unique index building on [n,m,i] whilst loading with unique index creation on [n,m,i] after loading with non-unique index creation on [n,m,i] after loading response time
yes no yes yes no no no 19.2 secs
no yes yes yes no no no 19.4 secs
yes no yes no yes no no 9.3 secs
no yes yes no yes no no 13.2 secs
yes no yes no no yes no 19.5 / 18.5 secs
yes no yes no no yes no 20.7 / 19.1 secs
yes no yes no no no yes 11.9 / 10.8 secs
no yes yes no no no yes 11.4 / 11.1 secs

The first time value given in the last four lines includes an index drop time which varies a bit according to precise server conditions at the time of measurement.

The most interesting result is that the differences measured between a sequential load of several files and a situation with just one big file almost to disappear when we create the index after the pure data loading.

Importing 10 million records from one or five files

Now, let us turn to 10 million records and an equal distribution of records over 5 files (with 2 million records each) and see whether we confirm our results. For the test we have just doubled the number of nodes in comparison to the 5 million case.

Table: Server response times for loading 10 million records with “LOAD DATA INFILE”

import by 1 files import by 5 files without any index on nr with parallel unique index building on [n,m,i] with parallel non-unique index building on [n,m,i] with unique index creation on [n,m,i] after loading with non-unique index creation on [n,m,i] after loading response time
yes no yes yes no no no 42 secs
no yes yes yes no no no 42,4 secs
yes no yes no yes no no 20 secs
no yes yes no yes no no 37 secs
yes no yes no no yes no 43.3 / 42.0 secs
no yes yes no no yes no 41.3 / 40.0 secs
yes no yes no no no yes 22.9 / 21.8 secs
no yes yes no no no yes 22.5 / 21.4 secs

Again, we see the huge impact of the creation of an unique index – but the total
loading time is almost independent of whether you create the index whilst or after the data loading. This is understandable as the required checks for a uniqueness must be done in both situations.

However, at least for me, it was almost shocking to see the difference between lines 3 and 4:

There is a big difference between a sequential loading process for several files and a loading process for just one file, when a non-unique index was created whilst loading with “LOAD DATA INFILE”.

Note in contrast the considerable reduction of the required time if you create the non-unique index after all data have been loaded into the database. The measure discrepancy in total loading time is really remarkable – and actually I have no convincing explanation for these findings. The tree structure of the index is quite simple; I do not really see what costs the overhead. And I have tested the effect several times to exclude any extraordinary conditions on the server. I have also validated parameter settings and the steps done during the sequence of loading steps to exclude any unplanned work or actions on the server.

The [n,m,i] index in my case has a size of 215 MByte; the data themselves take another 400 MByte. All in all we speak about 600 MByte. So, I suspect some reading and writing from/to the disk at the end/beginning of the individual 5 loader jobs and some inefficient memory management. I have not yet investigated this in detail. Write me a mail if you know the reason.

Independent of the reasons: For me, it was really important to see this difference because I do not need a unique index and I want to work with many more files to cover much larger numbers of records.

So, our result is:

You should avoid the creation of a non-unique index whilst using “LOAD DATA INFILE” on multiple files by sequentially started (PHP) jobs on the server instead of loading just once from one file. Whenever you load your data from several files create the index after all data from all files have been imported into the database table.

Impact on consistency checks during a sequential load of several files

In the article MySQL/Aggregation: Comparing COUNT(DISTINCT) values on big tables I discussed the creation of a helper table and respective indices to get a good performance of consistency checks. Most of these consistency checks were based on an analysis of derived result sets for “SELECT COUNT(DISTINCT … ).. ” statements. My basic idea was to perform such checks after each import process when dealing with a sequence of files.

However, in the named article I also showed that a fast creation of a helper table required already some existing index on the big data table. But our present results favor a solution where an index is created only after all data of all files have been loaded. So, no intermediate consistency checks in between the loading processes for two csv-files !

After some thinking I came to the conclusion that this is no big disadvantage. Even if you consistency checks only after all data have been loaded, you will still be able to identify the records which are problematic. And with some effort, data and file analysis – which will cost some extra time, but maybe worth it – you even may find the file from which the record was loaded. So, you can give the user who tried to load the files a full report on the suspicious records and that caused inconsistencies and the files that contained them.

Conclusion

We have tested different combinations for loading data with one or multiple files and with or without creating indices during/after the data loading. Our tests have shown that if you import data sequentially from several
files and you want to create a non-unique index over some columns, it makes a huge difference whether you create this non-unique index during or after the sequential “LOAD DATA INFILE” processes. One should avoid creating such indices whilst loading the data with “LOAD DATA INFILE”. It should be created after all data from all files have been imported into the database table.

In contrast such an order of steps does not seem to be required when creating unique indices. However, a unique index costs in general significantly more time to be created than a non-unique one. You, therefore, should evaluate the necessity of unique indices.