What Id should I use inside my trigger

That's amazing - almost like a chain reaction! Suddenly, everybody at Planet MySQL is writing something on triggers.

Well, to all those that want to try it too - that is - have a trigger perform an insert or update in anohter table here's a little tip that might be of use.

Suppose our trigger table has got an auto_increment column, and you'd want to be able to get your hands on the value of that item, what should you do? The answer was a bit of a surprise to me...

Suppose we have to tables, A and B and we want to have a trigger on A that inserts into B:
So, we need to someway transfer the generated id value for A into the corresponding field in B. Normally, you'd resort to the LAST_INSERT_ID, but this does tnot work inside a trigger.

Instead, use the new. pseudocolumn. That contains the new-to-be-inserted value in both the BEFORE and AFTER triggers.

Installing MySQL 5.0 from RPM on Ubuntu 5.10 ("Breezy Badger") GNU/Linux

During the past two weeks, I've been quite busy getting to know Ubuntu 5.10 "Breezy Badger".

(In the remainder of this entry, the phrases within parentheses are probably a "duh!" to Ubuntu, Debian and other Linux users, but they may be of interest to Microsoft Windows users).

Non-linuxers and Microsoft Windows users (I am a Windows XP Professional User) particularly: Ubuntu is a Linux distribution that comes with a "Desktop" environment. The operating system and the applications can be controlled entirely using a Look and Feel that is quite similar to the Microsoft Windows Environment. Standard Ubuntu comes with the Gnome desktop environment. (I don't know how it is for other Linux Desktop environments, and I don't even know if there are differences between different Gnome deployments, but I've got a desktop with shortcuts, a taskbar, and a start menu. At any rate, enouh to get this Windows user going)

Ubuntu is free software: it's guaranteed to be free of charge forever, users are free to change and pass on the source code. Even the installation media are free! (I suspect that this could change soon. No harm done, the download will remain free for evermore).

Installing Software on Ubuntu

I browsed through some of the basic Linux stuff: mounting a drive, using the bash shell and some of the most common commands, getting to know the organization of directories and files, playing with desktops and upgrading/installing software.

Of course, all of these things can be important, depending upon the degree up to which you want to be in control of your system. However, the latter task, installing and upgrading software, is an important one for almost all kinds of users alike. Nearly everyone wants to be able to control his or hers computer up to a level that it does what you want it to do. And that nearly always means at least running the software that you want it to.

First, let me give a big thumbs up for the assorted software that comes with Ubuntu! (Yes, that's all free too!) A graphical file and directory browser (Nautilus, matches Explorer in Windows), a complete office Suite (Open Office, matches Microsoft Office and even allows file interchange with Microsoft Office), an Internet Browser (inevitably, Mozilla Firefox) and so much more. Among these many, many other pre-installed applications, Ubuntu comes with an autoupdate facility (like the Microsoft Windows update center) and the Synaptic Package Manager.

Now, this Package Manager (Synaptic is just the pre-instlalled flavour, there are lots of other ones) is a tool that allows you to review installed packages and install new ones. A package is just a bunch of files along with some information that describes installation of these files and the dependencies (if applicable). Usually, a package corresponds to an entire application, or a distinct module of an application. (Read more about package management in Ubuntu here.)

The OS has the utilities aboard to install the contents of the package. Also, a package database is maintained that records what files were installed and in what location. Package management also allows software packages to be uninstalled in a safe manner, that is without breaking the dependencies.

(As far as I can see now, there are about three different ways to install software on a linux system. In no particular order: 1: packages, 2: plain copying of files, usually after tar.gz extraction of a so called tarball, or 3: source code compilation, often accompanied by a ./configure configuration command shell script that generates the makefile for make.)

Package Management and Repositories

The Synaptic Package manager is just a front-end to the package management process. It's hooked up to one or more package repositories, offering a myriad of applications, or extensions to applications to be installed. Using the Synaptic Package Manager, installing an application is as easy as checking the packages of interest and confirming. (There's also a command line interface to access the repositories.) Depending upon the repository wherein the package resides, the necessary files are downloaded from the internet, or read from your CD.

The preconfigured Ubunutu repositories are really extensive. There's really a lot of stuff you can get there. I'm looking at 17821 distinct packages, in about a hundred categories. It's huge. Of course, some of these are different versions of the same packages, and some of these are things like language packs for a particular 'real' application, but there's really a lot of stuff available, ranging from Core System components to Games to Mathematical packages to Multimedia. There's just to much to sum it up here.

The disadvantadge of packages is that there must be some sort of authority that maintains the repositories and package products. This means that there will always be some sort of lag between the release of new software and the processes of packaging it and integrating the new packages with the repositories.

MySQL 5.0 for Ubuntu

Actually, you will encounter a lag the moment you're busy setting up a LAMP stack on your Ubuntu system. (The Ubuntu Wiki contains a topic on how to set it all up.) In doing so, I was very pleased indeed to see that I could install reasonably recent versions of both Apache (v.2.0.54, the single last 2.0 release at the time of writing) and PHP (v.5.0.5). However, the included MySQL version was only a 4.1 - not the one I'm settling for right now. The wiki topic also suggests you compile the MySQL 5.0 source yourself if your're not satisfied with 4.1.

Although challenging, I decided it was a good moment to take a look at what the MySQL reference manual had to say about all this. The whole of Chapter 2 is devoted entirely to installing MySQL on various platforms. Although I thought I'd read most of the manual, I discovered that I in fact never did read Chapter 2 (!). I simply never needed to review this documentation, as the installation process from the Windows Binary Distributions I used to download before is really self-explanatory.

Although the MySQL Reference Manual explains how to install MySQL using a source distribution, a installation using compiled binaries is recommended. Chapter 2.1.2, "Choosing a Distribution Format" does contain a discussion that points out in what cases one should choose the source distribution in favour of a binary distribution. It's probably safe to say that in most of cases, one should choose for a binary distribution.

After deciding to go for a binary installation, there are again two choices. Should you install a pre-packaged (RPM) binary distribution or a tarball (a gzip-compressed, tar-bundled distribution). Chapter 2.4, "Installing MySQL on Linux" is quite clear about this:

The recommended way to install MySQL on Linux is by using the RPM packages.

RPM's

I then reviewed what my copy of "Running Linux" had to say about this RPM format. RPM is an abbreviation of "RedHat Package Manager". (According to the book, RPM is not exclusively targeted towards the Redhat Linux distribution, despite it's name.) This is acually a tool just like the synaptic package manager, at least functionally: it is designed to install software distributions and to maintain a database of the installed files in order to manage dependencies, upgrades and deinstallations.

Having read the part on rpm's, I just went to the MySQL Downloads page, and downloaded all of the Linux x86 generic RPM Downloads. (There's a different RPM covering a particular part of funcitonality: server, testsuite, client, libraries and headers etcetera. You can decide for yourself what suits you best.) Then, I attempted to install the server package from the bash shell program that is installed by default:

roland@rpbdesktop:~$ rpm -iv Desktop/MySQL-server-5.0.18.glibc23.i386.rpm
bash: rpm: command not found
roland@rpbdesktop:~$

The roland@rpbdesktop:~$ bit is my prompt, it is generated by the command shell. The prompt is your friend: it tells you what user is currently running the shell (roland), on what host the shell is actually running (rpbdesktop) and it also tells you what the current working directory is (in this case, ~, which is a shorthand for the current user's home directory). Finally, the $ tells you the current user is an ordinary user (it would've been # if I'd been the root user).

The rpm -iv Desktop/MySQL-server-5.0.18.glibc23.i386.rpm bit is the command I typed. rpm is the executable I want to invoke, and the -iv is supposed to tell the executable to install (i) the package stored in the file (MySQL-server-5.0.18.glibc23.i386.rpm, one of the downloads, located on my Desktop directory residing directly under my home directory). (The v option stands for verbose, which is supposed to generate descriptive output concerning the installation process.)

The bash: rpm: command not found bit is returned by the bash command shell program. It informed me that the rpm tool was not found on my system. So, I searched the synaptic package manager for the string "rpm", and lo and behold, it returned me a list of packages, one of wich was titled "rpm". It was not checked, indicating is wasn't installed yet on my system. I marked it and applied the changes, and I gave it another crack:

roland@rpbdesktop:~$ rpm -iv Desktop/MySQL-server-5.0.18.glibc23.i386.rpm
rpm: To install rpm packages on Debian systems, use alien. See README.Debian.
error: cannot open Packages index using db3 - No such file or directory (2)
error: cannot open Packages database in /var/lib/rpm

Ok, this is definitely an improvement: the rpm tool was in fact installed, and in a location where it could be seen by my command shell (namely, the usual place for this type of utility, the /usr/bin directory). However, it did not install the software. Here, I was getting errors too, but this time, the errors were generated by the rpm tool itself.

RPM for Ubuntu: alien

Apart from the errors, the tool also suggests using alien on Debian systems. Now, glancing over some pages from the Ubuntu website, I had already learned that Ubuntu is in fact based on Debian. So, I tried to invoke alien instead of rpm:

roland@rpbdesktop:~$ alien -iv Desktop/MySQL-server-5.0.18.glibc23.i386.rpm
bash: alien: command not found
roland@rpbdesktop:~$

Ahah, but we know what to do next: search the Synaptic Package Manager for a tool called "alien". Of course, like rpm, alien wasn't yet installed. Like for rpm, installation of alien is just one click away. Later, I found out that while I was about to install rpm, I could've known I had to install alien too.

See, when you right click a package in the Synaptic Package Manager, a popup menu appears. From there, you can choose the "Properties" item (this is SO Windows!). A dialog is displayed, showing detailed information about the package. The info is organized in tabs, and when you click the "Dependencies" tab, you see a list of packages on which this package depens, as well as a list of packages that are marked "suggested". Now, the packages on which the current package depends are checked automatically when you decide to install the current package, just like it should. However, you should decide yourself if you want to install the suggested packages. In this case, it would've been the right choice.

Finally: Installing MySQL 5.0

Finally, I installed the MySQL packages using these lines:

cd ~/Desktop
sudo alien -iv MySQL-server-5.0.18-0.glibc23.i386.rpm
sudo alien -iv MySQL-client-5.0.18-0.glibc23.i386.rpm
sudo alien -iv MySQL-shared-5.0.18-0.glibc23.i386.rpm
sudo alien -iv MySQL-devel-5.0.18-0.glibc23.i386.rpm
sudo alien -iv MySQL-bench-5.0.18-0.glibc23.i386.rpm

(I omitted the promt and the output here. I pointed out already that one should decide for oneself what packages to install. According to the MySQL Reference manual a standard minimal installation of the server would comprise only the first two packages. Please review the pertinent manual page before you try this at home!).

There's one extra ingredient here in comparison to my previous alien command, and that's the sudo command prefixing the actual alien command. I think it is an acronym of Super User DO, and its function is to execute the following command (the alien command is this case) using the privileges of the root user. See this page from the Ubuntu wiki for more info on sudo, and the Ununtu specifics concerning it. (Tip: use gksudo for starting a gnome application with root privileges. I use this to place shortcuts, or launchers in the gnome lingo, to several configuration files on my desktop).

I put sudo there because the installation fails if you don't. You will get a bunch of errors indicating that you do not have appropriate permissions to copy files to here and there, or to create this or that directory. I don't know if using sudo is the appropriate solution, but hey,it works for me, at least for the time being.

Post-installation

After installation of the binaries, the installation is *NOT* completely finished. You still need to perform some post-installation tasks (this is very different from the windows installation procedure, which is more of a out-of-the-box experience). See also: Chapter 2.9.2 Unix Post-Installation Procedures from the MySQL Reference Manual.

First, you need to run the mysql_install_db script from a shell. It's located in /usr/bin.

sudo mysql_install_db --user=mysql

This installs the grant tables and creates the data directory.

I wanted to start the server automatically as part of the boot procedure. It took me a while to find out how to achieve that, and my impression is that there are quite some differences between the different Linux distributions. Finally, I found that this did the trick for me:

sudo cp support-files/mysql.server /etc/init.d/mysql
sudo chmod +x /etc/init.d/mysql
sudo update-rc.d mysql defaults

I didn't make it up myself, I copied it from the Unbuntu wiki, from the topic concerning the MySQL 5.0 Source installation procedure (see: MYSQL5FromSource, it's nearly at the bottom onder the title "Starting Mysql at boot time").

I thought they would never come: installation problems

After this otherwise succesful installation, I'm still left with one problem, and that's connections through a socket are not working. You can tell so by doing this:

roland@rpbdesktop:~$ mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

The problem does not occur when the command line client is forced to connect using tcp/ip:

roland@rpbdesktop:~$ mysql -uroot -hrpbdesktop
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

I tried to find information on that, but it all leads to a dead end. Especially on the MySQL installation forum there are some threads that mention this, and at least some of them are Debian users (see: http://forums.mysql.com/read.php?11,9689,56886). I found out that I could connect via tcp/ip myself, but there are some people that offer this as a solution in their posts, apparantly unaware that the underlying protocol is different.

However, I'm convinced it's just something silly, such as having a different actual location for the .sock file than is assumed by the client, or maybe missing or inappropriate (Linux File) permissions for that file. At least, that's what I'm investigating now (thanks, Felix, your post here: http://forums.mysql.com/read.php?51,52107,52111#msg-52111 gave me that idea!)