The road to using Mysql

Hi, I've not gone into hibernation (yet)! I've been busy working on SqlServer queries in my job. One useful clause I use there is "top" to limit how many rows your query brings back. Now of course we have an equivalent in Mysql, the "LIMIT" clause. This is very useful to impose order on the rows your query brings back; you don't just have the facility to restrict the query to the first x rows. You can use OFFSET to skip rows. For example if I want to bring back 5 rows from the table EMP, but not the 5 lowest values, I could use the following statement:

Code:

Now another useful point about the LIMIT clause is you can use it on GROUP BY queries. It makes sense really, as the LIMIT clause is working on the record rows that are returned for display. Imagine that you need to group by a certain field and sum on a key value. Using the GROUP BY and ORDER BY clauses together will bring back results in ascending or (if specified by DESC) order. However, it may be that large data sets will produce more rows than you're interested in. Using the LIMIT clause will restrict the number of rows you bring back:

Code:

I think the combination of GROUP BY with other functions and clauses, is what really makes sql powerful in analyzing data-sets. The following code demonstrates this, as it relies on the use of the UNION operator (remember to use the same number of columns and column data types in each select statement) to display different summaries in the same report:

Code:

Pretty useful, hey? Before I leave, I have to admit to getting most of my code examples this time from the book SQL CookBook, published by O'Reilly. This really is a book worth purchasing, even if you're just starting in SQL or Mysql programming. You can find the book at this site booklink
Plug over - I wish you a profitable time working with your database of choice, and I hope you visit my site again soon.

A bit of a delay since my last entry. I've started a new job which uses SQLServer (boo!) so my use of Mysql has been very small lately.
Anyway, let's take a little look at the group by clause. One of the most common ways I use it is to bring back records that are duplicated more than once. For example:

Code:

;
+-------+----------+--------+----------+
| ename | job | deptno | count(*) |
+-------+----------+--------+----------+
| WARD | SALESMAN | 30 | 2 |
+-------+----------+--------+----------+
1 row in set (0.00 sec)

Now how would you bring back (to delete or update) the latest insert of this duplicated record? In this case there are 2 records, one with empno of 7521, the latest record has the empno 8000. The following code (in my humble opinion) is very elegant, and is perhaps my favourite bit of sql I've seen.. thanks to this book sqlcookbook

Code:

+-------+-------+----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+-------+----------+------+------------+------+------+--------+
| 8000 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
+-------+-------+----------+------+------------+------+------+--------+
1 row in set (0.01 sec)

Here's another way of using the group by clause (again using subqueries) to bring back the employees with the highest and lowest salary in the dataset.

Code:

+-------+-----------+--------+
| ename | job | deptno |
+-------+-----------+--------+
| SMITH | CLERK | 20 |
| KING | PRESIDENT | 10 |
+-------+-----------+--------+
2 rows in set (0.10 sec)

Now that, to me is also beautiful code.
Enjoy your coding - Until next time!

In order to enforce referential integrity and set up foreign keys, you need to convert your tables to using the Innodb storage engine (or create them using this to begin with on the storage engine). The syntax to convert a table to this storage is very straightforward:

ALTER TABLE orders ENGINE=INNODB;

However when I tried to do this, warnings were created. When I used "SHOW WARNINGS;" Mysql informed me that the tables were still using the MyISAM engine. Also, when I used the SHOW ENGINES command, I found out that the Innodb engine was disabled.

In order to enable Innodb (which is often disabled by default on Apache set-ups) the standard advice is to edit my.cnf file. The first part is to Comment out the skip-innodb line by using #skip-innodb
Also it's supposed to help by making sure the following lines are present:

Code:

However there is often more than one instance of this file my.cnf. Mine were in the directories /etc/mysql/ (often its in the /etc directory) and also in /var/lib/mysql/ Be warned - its exact location can differ on different set-ups so be sure to find the global version and the local one. Read this for further guidance:
mysql manual

Do this, restart your Apache and Mysql server, and you should be able to proceed.

However in my case, on looking at SHOW ENGINES, it seemed as though Innodb was still disabled. After much head-scratching and internet surfing I found out that if you have a zombie mysql process in the background, then Mysql never fully gets restarted. I should have realised this from the warning message "Another MySQL daemon is already running" I kept receiving. You can detect this on Unix based systems by performing this command:
ps -ef | grep mysql

Luckily, the problem was diagnosed here:
forum link

The solution turned out to be fairly straight-forward - Kill the zombie process, and then restart Apache and Mysql. You also have to put the correct path to your mysql.sock in your my.cnf files (see link again).

After doing all that I was successful - I can now convert tables to the Innodb engine.