Med fokus på din virksomheds forretningsgange watermark with eaktion circles

Archive for the ‘system administration’ Category

Connecting Access to MySQL through a SSH tunnel using Putty and port forwarding

Sunday, December 25th, 2011

I’m publishing today this article that is some years old and was just collecting dust in my drawer. I believe it can still be useful to many. In particular the problem I’m going to solve is about the MySql error:

Connection failed: [08S1][MySQL][ODBC 5.1] Lost connection to MySQL server at ’reading initial communication packet’

Just paste the title of the post in a Google search box and you’ll get plenty of tutorials for how to set up Putty to do this.

The reason why I add something to the argument, is that after having used tunneling to connect to a MySQL RDBMS for some years, the day came when my settings didn’t work any longer. I used a fair amount of hours to find a solution to the problem and Google was only helpful to exclude other solutions. There’s no reason why you should do the same.

The error I got when testing the connection from the Connector configuration window was:

Connection failed: [08S1][MySQL][ODBC 5.1] Lost connection to MySQL server at ’reading initial communication packet’, system error: 2

If you google the Internet to find a solution to this error, you will probably come to the conclusion that something is wrong with your firewall settings or your network connectivity. Many times the solution given to solve problems that lead to the same error message, is to correct the server connect_timeout from the once default 5 (seconds) to a higher time span.

However this was definitely not the culprit in my case.

My setup

Now, before we go on to read about the mistake I made and the solution, I’d better add some details about the setup I’m working with. In a different setup you might have to use somewhat different settings.

I’m connecting to a MySQL server on a Linux box (Debian etch), from Windows XP / Vista.
The tunnel (port forwarding over SSH) is done using Putty.

What is important to notice for this text to be valid is the fact that MySQL runs on Linux and not on Windows.

The wrong tunnel configuration

The mistake is shown in this picture showing Putty tunnel configuration.

I had set the tunnel in such a way, to forward my local port to remote Linux box, port 3306 – writing the domain name, it could also have been the remote IP address. Now, this is not wrong in general and among the tutorials you’ll find on the Internet many recommend this setting. On the other hand the tunnel had been working fine for some years, and it would have gone on for more years if I hadn’t switched to a different server.

DSN configuration

Before we come to why this setting in fact is a mistake we’ll also have a look at another of the settings needed for the Access-MySQL connection to work, the DSN settings.

As you might know, here we set the data source as being the

local port (2306 in my case, different from MySQL default port 3306 to avoid conflicts with a possible MySQL for Windows installed on my PC) that we want to forward to the remote MySQL server. The setting under “Server” must be 127.0.0.1 in most of the cases. The reason is that your /etc/mysql/my.cnf file will have a line:

bind-address            = 127.0.0.1

This tells the server to only allow connection from the localhost, that is from users of the same Linux box, not connecting from outside. This line is default in a Debian distribution, and I believe it’s default in many other distros too. It avoids to expose the server to all possible connection attempts from black hat guys on the Internet. This is actually one of the reasons why we do SSH tunneling in the first place.

When you have logged into your server through Putty, your server is no longer remote, and that’s why 127.0.0.1 here doesn’t prevent the connection, and at the same time you comply with what is written in the option file (bind-address   = 127.0.0.1).  What happens is that all what you send to your local port will be transferred by Putty to the corresponding port on what “was” the remote server. MS Access or any other application using that port will never know.

This setting is shown correctly in all the tutorials, I have seen on the Internet.

Why the Tunnel configuration was wrong

However one could ask, whether or not we need to write the remote address or the localhost address in the tunnel configuration, not all the tutorials agree on this point. And in fact the answer is that we don’t need to write the remote address, or rather that we should avoid this: When Putty is establishing the port forwarding, it is already connected to your remote server, the connection happens between the two ports on the local and the remote server no matter if we write the local or the remote address in the tunnel settings, as long as it is a valid way of specifying it.

Putty doesn’t care, SSH doesn’t care, but MySQL does apparently care: as we have seen,

it will toss a: Connection failed: [08S1][MySQL][ODBC 5.1] Lost connection to MySQL server at ’reading initial communication packet’, system error: 2

To sum up

Summing up:

write the loopback address in the tunnel settings,

write the loopback address in the DSN setting

One last detail

Yes, one last thing remains to be explained and this is why on earth the “wrong” settings did work on my first server, and did no longer work when I used them on a new server?

Now, my explanation here is a fair amount more than plain guesswork, but still guesswork, however it makes sense to me and it is enough to let me sleep at night. It might satisfy you as well: I believe the reason is that on the first box (where the remote address didn’t hinder the connection) I had only one IP address on one network card. The OS was probably making its own translation of the remote address into the loopback address before MySQL received it. On the second box (where this setting stopped working) I had several IP addresses, and probably for this reason the remote address was no longer just translated by the OS into the loopback address for MySQL, and this triggered the connection error.

At least this will give you one more chance to find a solution to the problem, before blaming your firewall settings or network connectivity.

Upgrading from Debian Lenny to Squeeze (version 6)

Monday, June 6th, 2011

You want to update to Debian Squeeze from Lenny. You don’t want to scour the whole Debian documentation just to be sure not to have overlooked details that apply to your situation. Here is a complete step-by-step tutorial for upgrading to Squeeze from Debian Lenny.

In 4 steps is listed here the procedure we’ll follow. There are quicker, more risky, procedures, however the time saved doing otherwise is count in seconds, who bothers.

  1. minimal system upgrade, followed by
  2. a kernel upgrade, udev upgrade and reboot, and then
  3. (optionally) upgrade the packages associated with your critical services.
  4. full upgrade

I spell out here all the preconditions that apply for this condensed but complete step-by-step tutorial for upgrading from Debian Lenny to Squeeze. You can easily find out if this is for you or not. For web developers that use Debian as a testing/production machine this will apply in 95% of all cases.

If you don’t meet all preconditions you’ll have to go read the full documentation.

Our Goal

The goal is to be able to update from a remote terminal with as little disruption and as few risks as possible, by risks meaning the need to resort to a local console to fix problems. However no guarantees are given. None at all. The only guarantee I can give, is that I have tried the upgrade myself on 3 different Debian boxes. On one it failed because I followed a different, shorter, recipe. Two were successful following this one.

Preconditions for the Upgrade

Your Debian Lenny is a pure command line installation (no GUI, that is, x-server and more). You are running some servers and other stuff, but you don’t have external users logging in their own /home directory to care of.

You have a Debian Lenny (version 5) that is up to date to the last update point. Otherwise

aptitude update

aptitude full-upgrade

should resolve any issues. Verify with

dpkg -l | pager

no packages should have a combination of h and fh as the first 2 flags in the output.

it might be bad if you have any other combination of letters. Here is an excerpt of mine.

ii  apt-utils    0.7.20.2+lenny2    APT utility programs
ii  aptitude   0.4.11.11-1~lenny1  terminal-based package ...
rc  aspell-en     6.0-0-5.1                  English dictionary for G...

ii is OK, rc is OK

From here on we’ll use Apt instead of Aptitude (it is officially recommended for a Lenny to Squeeze upgrade)

Other preconditions to “qualify” for blindly following this step-by-step recipe:

You don’t have third party packages (from repositories other than the official Debian Lenny repositories), if you do, remove them prior the upgrade and treat their upgrade independently.

“If you are using some VPN services (such as tinc) they might not be available throughout the upgrade process”

“You should not upgrade using telnet, rlogin, rsh”, you might loose connection, use ssh instead.”

“If you have configured APT to install certain packages from a distribution other than stable (e.g. from testing), you may have to change your APT pinning configuration. In this case you want to read the whole document on Debian org.

Let’s start

1 Minimal System Upgrade

Take a backup of

  • all /etc
  • the file ./curr-pkgs.txt after having done: dpkg --get-selections "*" > ./curr-pkgs.txt
  • /var/lib/dpkg
  • /var/lib/apt/extended_states
  • /var/lib/aptitude/pkgstates
  • Any other files you care of (DB, web files, /home files etc.)

Do evaluate ”what if” in case you should be forced to reboot from the console terminal and the off/on button on your box.

If things go wrong this is the link you need.

Start doing your upgrade.

Change the sources to squeeze:

less /etc/apt/sources.list

Substitute any occurrence of lenny with squeeze. Don’t use stable, to avoid getting into troubles at the next version upgrade, write squeeze. Codenames are unique, category names are not.

Also remove sources for debian-volatile that are no longer used. Be sure to have all of main, contrib and non-free (for network cards firmware)

Here are my sources :

deb http://ftp.de.debian.org/debian/ squeeze main contrib non-free
deb-src http://ftp.de.debian.org/debian/ squeeze main contrib non-free

deb http://security.debian.org/ squeeze/updates main contrib non-free
deb-src http://security.debian.org/ squeeze/updates main contrib non-free

deb http://ftp.de.debian.org/debian squeeze-updates main contrib non-free
deb-src http://ftp.de.debian.org/debian squeeze-updates main contrib non-free
Ctrl-c
Y

Record your upgrade session with

script -t 2>~/upgrade-squeeze1.time -a ~/upgrade-squeeze1.script

When finishd you’ll stop recording typing: ”exit” Timing info is in the .time file, you can see all what happened and console messages reading the .script file.

Now enter:

apt-get update
apt-get upgrade

(we’re only doing a minimal upgrade now)

You might need to open an extra terminal during the upgrade to examine some situations as to be able to reply to questions posed to you by the upgrade process. To do so type

Alt-right arrow.

2. Upgrade Kernel and Udev

Upgrade the kernel
Start by finding a suitable linux kernel image

apt-cache search linux-image-2.6.

And choose one that suits your architecture and is equal or older to linux-image-2.6.26-2

apt-get install linux-image-2.6-<your flavor>

Although you had non-free among your sources you might end up into the following situation, due to bad handling of firmwares by the installer:

W: Possible missing firmware /lib/firmware/tigon/tg3_tso5.bin for module tg3
W: Possible missing firmware /lib/firmware/tigon/tg3_tso.bin for module tg3
W: Possible missing firmware /lib/firmware/tigon/tg3.bin for module tg3

In this case install manually the nonfree firmware package:

apt-get install firmware-linux-nonfree

And you google about it to be reassured that you’ll have the correct driver installed anyway.

In another case I got the corresponding message for another driver:

This system is currently running Linux 2.6.26-2-686 and you are installing Linux 2.6.32-5-686.
In the new version some of the drivers used on this system may require additional firmware files:
r8169: rtl_nic/rtl8168d-2.fw, rtl_nic/rtl8168d-1.fw

choose not to do anything, however I ended up anyway with a well functioning Debian, go figure it!

Install the new version of udev

apt-get install udev

stop the script recording by entering ”exit”
Reboot the system.

Restart recording in a different filename with

script -t 2>~/upgrade-squeeze2.time -a ~/upgrade-squeeze2.script

3. Optionally Upgrade Your Critical Packages

For extra control, on a production machine, upgrade some individual packages by their own.

apt-get dist-upgrade <package-name>

Go on doing the full upgrade

4. Complete the Upgrade

apt-get dist-upgrade

Do the various configuration as asked during the process, if you choose to upgrade to the configuration  (which is recommended) you can always retrieve the old configuration in the .dpkg-old version of your config files.

Enjoy your new Debian.

Upgrading Dokuwiki when installed manually – the Debian way

Saturday, January 16th, 2010

Yes, you understood right, to see what I mean by installing Dokuwiki manually -  the Debian way, read this post.

You can use the method presented on this post only if you have installed dokuwiki on Debian, with Aptitude/apt-get as described in the post linked to above, and you have followed those instructions,  in order to continue upgrading Dokuwiki manually instead.

When all this is said and done it is easy to upgrade (however no promises made here, backup all your files and dirs or you’ll lose data as by Murphy’s law and a couple of others), just do the following:

1. Download and save the new release into your private installations directory, so you have:

/usr/local/mypackages/dokuwiki-2008-06-05

2. Link your existing conf dir into the new location

ln -sTf /etc/dokuwiki   /usr/local/mypackages/dokuwiki-2008-06-05/conf

3. Link your exisiting data directory into the new release

ln -sTf /var/lib/dokuwiki/data /usr/local/mypackages/dokuwiki-2008-06-05/data

4.  Link the new dokuwiki package into where a normal Aptitude intallation would do

ln -sTf /usr/local/mypackages/dokuwiki-2008-06-05 /usr/share/dokuwiki

You’re done!

… er. Don’t you like the result?

Just roll back:

ln -sTf /usr/local/mypackages/dokuwiki-2008-05-05 /usr/share/dokuwiki

Do you see my point?

P.S. If you had templates they will be reput in place by doing like it is done below for the “sidebar” template:

ln -sTf
/usr/local/mypackages/sidebar /usr/local/mypackages/dokuwiki-2010-11-07/lib/tpl/sidebar

Installing Dokuwiki the Debian way – by hand

Saturday, January 16th, 2010

What? You’re joking, right? Well, no actually. Although I love the Debian way, man you just do:

aptitude update
aptitude install dokuwiki

Then you copy a chunk of Apache conf into your site’s configuration and that’s it. The various directories are placed the right place and everything’s pink.

Yes, but, one day you realize that

aptitude update
aptitude upgrade

doesn’t give you any of the latest upgrades to dokuwiki and when you log-in as an admin you see:

New release available: 2008-05-05. upgrade now! [14]
New release candidate available: 2008-04-11. upgrade now! [12]
New release candidate available: 2008-03-31. upgrade now! [11]
An XSS vulnerability was discovered, read the bug report for manual fixing or upgrading [10]
New release available: 2007-06-26. upgrade now! [9]
New release candidate available: 2007-05-24. upgrade now! [8]

This is too much even for a lazy dog like me, so I had to do something, and that something was to install the latest dokuwiki release by hand, as if it was Aptitude (or apt-get) to do it. You can do it too, follow me step by step.

The advantage of following the Debian way by hand is that if the mantainer will feel like upgrading the package and you’ll feel like upgrading again via Debian/Aptitude, nothing will  get broken (and though, read the fine print: no warranties given here, I don’t know what the mantainer will do next time. However, that this method is safe is only a tiny bit less sure then taxes and death, read ahead for the why).

Furthermore, you stick to the same file structure/logic that all other packages have in your Debian system.

How is that structure for the Debian Dokuwiki installation?

Debian will give you:

/usr/share/dokuwiki/bin
       -''-        /conf -> /etc/dokuwiki
       -''-        /data -> /var/lib/dokuwiki/data
       -''-        /doku.php
       -''-        /feed.php
       -''-        /.htaccess
       -''-        /inc
       -''-        /index.php
       -''-        /install.php
       -''-        /lib
       -''-        /prepend.php

For this to work of course your Apache configuration will have something like the chunck below for the pages to be served from /usr/share/dokuwiki

Alias /dokuwiki         /usr/share/dokuwiki
<Directory /usr/share/dokuwiki/>
Options +FollowSymLinks
AllowOverride All
order allow,deny
allow from all
</Directory>

Note that the above is different from the default Dokuwiki installation (non Debian): a normal dokuwiki foresees dropping all the files in one folder inside your server tree.
Now, we will do the same as Debian/Aptitude but with some extra symlinking to be able to upgrade dokuwiki easily when the new release is available.  And besides, the extra step we introduce will secure our installation against dokuwiki being retrograded by an accidental upgrade done via Aptitude.

Finally this is a logic we can use with many other PHP (and javascript) packages

How do we do it?

We will save the new release inside /usr/local/mypackages (mypackages being your name of choice for your “hand-made” installations) for instance you’ll have all the files inside:

/usr/local/mypackages/dokuwiki-2008-05-05

(Aptitude won’t never touch it there, as by the Debian guidelines)

Then we will move the Dokuwiki’s /data directory out of this directory because we want to be able to manage that separately from later upgrades, it is the directory that keeps all our current and future dokuwiki pages – it must grow with you, indipendently from the dokuwiki version.

So we want something like, say,

/usr/local/mypackages/dokuwiki-data

and instead of having it inside the package dir, we will link to this directory, and in this way we are ready to upgrade without needing to be afraid of overwriting the data directory: in case of an upgrade the link is wiped out and you can just relink the new release files and the original data directory is intact, no need to move,  or save files prior the upgrade, the dir stays there out of the way.

The final complete setup of our dokuwiki package is therefore like shown below:

Apache finds dokuwiki inside:

/usr/share/dokuwiki

This is a:

/usr/share/dokuwiki -> /usr/local/mypackages/dokuwiki-2008-05-05
That in turn is made up of the directories and symlinks as shown below:

/usr/local/mypackages/dokuwiki-2008-05-05/bin
                  -''-                    /conf -> /etc/dokuwiki
                  -''-                    /data -> /var/lib/dokuwiki/data
                  -''-                    /doku.php
                  -''-                    /feed.php
                  -''-                    /.htaccess
                  -''-                    /inc
                  -''-                    /index.php
                  -''-                    /install.php
                  -''-                    /lib
                  -''-                    /prepend.php

/var/lib/dokuwiki/data is also a simlink:
/var/lib/dokuwiki/data ->  /usr/local/mypackages/dokuwiki-data

How do we reach this point? Step by step it is like so:

1. Download and save the whole package into your private installations directory:

/usr/local/mypackages/dokuwiki-2008-05-05

(do this as you wish)

2. copy the data directory as a separate directory inside your personal installations directory

cp /usr/local/mypackages/dokuwiki-2008-05-05/data /usr/local/mypackages/dokuwiki-data

2.1 If this is an upgrade and not a first installation you have a previous Aptitude Dokuwiki installation, so save your existing data dir (Debian places it inside /var/lib/ and calls it “dokuwiki”

rsync -av /var/lib/dokuwiki/data/ /usr/local/mypackages/dokuwiki-data/

3. [SKIP THIS  if upgrading from previous Aptitude Dokuwiki] Create a dokuwiki dir in /var/lib/ . As root do:

mkdir /var/lib/dokuwiki/

3.1 [FOR UPGRADING from previous Debian Dokuwiki installation]
Remove the Debian-made data dir:

rm -rf /var/lib/dokuwiki/data

4. Link your private data dir into the usual location for an Aptitude dokuwiki installation

ln -sT /usr/local/mypackages/dokuwiki-data  /var/lib/dokuwiki/data

5. [SKIP THIS  if upgrading from previous Aptitude Dokuwiki]  Copy the original conf dir where possible future Aptitude installations will find it. As root:

cp /usr/local/mypackages/dokuwiki-2008-05-05/conf  /etc/dokuwiki

6. [SKIP THIS  if upgrading from previous Aptitude Dokuwiki] Remove the original conf dir

rm -rf /usr/local/mypackages/dokuwiki-2008-05-05/conf

7. Link to the conf dir in the new location

ln -sT /etc/dokuwiki   /usr/local/mypackages/dokuwiki-2008-05-05/conf

8.  Link to the whole dokuwiki package into where a normal Aptitude intallation would do

ln -sT /usr/local/mypackages/dokuwiki-2008-05-05 /usr/share/dokuwiki

9. [SKIP THIS  if upgrading from previous Aptitude Dokuwiki] Then remember to configure apache to serve dokuwiki from the

/usr/share/dokuwiki

directory and you’re done.

And click here for a quick step by step tutorial for when the time has come to upgrade your WordPress to the next release.

Happy dokuwiking!