MySQL Sandbox tutorial translated in Portuguese

A while ago I wrote a tutorial on the MySQL Sandbox.
Wallace Reis asked me permission to translate it, and I happily agreed.
And now there it is: the tutorial Portuguese translation (the first part, but the rest will come).
Thanks, Wallace!

An ugly query should be a warning bell

A recent question in a newsgroup was

Given this table in MySQL 4.0, how can I run a single query to sort the records by the number of pipes in myfield?

id(int) -------- myfield(varchar)

1 --------- |number1|number2|
2 --------- |number1|
3 --------- |number1|number2|number3|
4 --------- |number1|number2|number3|number4|

Suppressing my urge to say something about the data structure, I suggested this clever solution from a test sample I deduced from the request:

select * from mytable;
+----+-------------------+
| id | myfield           |
+----+-------------------+
|  1 | 10|1|12|9         |
|  2 | 1|2               |
|  3 | 19|5|59|91        |
|  4 | 111|31|10|1|12|9  |
|  5 | 1|2|7             |
|  6 | 95|59|91          |
|  7 | 123456789|2000000 |
+----+-------------------+

select id, myfield,
replace(
replace(
replace(
 replace(
  replace(
   replace(
    replace(
     replace(
      replace(
       replace(myfield,
       '9',''),
      '8',''),
     '7',''),
    '6',''),
   '5',''),
  '4',''),
 '3',''),
'2',''),
'1',''),
'0','') as so
from mytable
order by so;
+----+-------------------+-------+
| id | myfield           | so    |
+----+-------------------+-------+
|  2 | 1|2               | |     |
|  7 | 123456789|2000000 | |     |
|  5 | 1|2|7             | ||    |
|  6 | 95|59|91          | ||    |
|  1 | 10|1|12|9         | |||   |
|  3 | 19|5|59|91        | |||   |
|  4 | 111|31|10|1|12|9  | ||||| |
+----+-------------------+-------+

However, the poster added hastily that his request was wrong, and that what he identified as "numbers" are actually character strings of various length, thus making my solution much harder to find. The sample given was

1 --------- |ruote in lega|macchine gialle|
2 --------- |case in affitto|
3 --------- |treni|vagoni|camini|

But then I overcame my desire of showing off with my cunning solutions, and told him that, apart for the initial misunderstanding about numbers and strings, his table was stained with the unforgivable sin of not being even in 1NF.
Therefore, the right course of action would be to split the table:

contents
1  ruote in lega
2  macchine gialle
3  case in affitto
4  treni
5  vagoni
6  camini

mytable
ID id_content
1  1
1  2
2  3
3  4
3  5
3  6

This way, the ugly query to sort by "how many pipes" would be reduced to the simple

SELECT id, count(*) as how_many
from mytable inner join contents using (id_content)
group by id
order by how_many

Beware of ugly queries. Often they tell you that something in your structure design may be wrong.

Obscure settings

I remember when MySQL was spreading rapidly, mostly due to its ease of use. That was long time ago, apparently. Now we are entering the dark ages of obscure settings, or so it seems, reading some MySQL Cluster setting definitions.
Quoting from the the manual:

TimeBetweenLocalCheckpoints

This parameter is an exception in that it does not specify a time to wait before starting a new local checkpoint; rather, it is used to ensure that local checkpoints are not performed in a cluster where relatively few updates are taking place. In most clusters with high update rates, it is likely that a new local checkpoint is started immediately after the previous one has been completed.

The size of all write operations executed since the start of the previous local checkpoints is added. This parameter is also exceptional in that it is specified as the base-2 logarithm of the number of 4-byte words, so that the default value 20 means 4MB (4 × 220) of write operations, 21 would mean 8MB, and so on up to a maximum value of 31, which equates to 8GB of write operations.

Any suggestions for taking this definition down to planet Earth?

Measuring MySQL resources consumption on Linux

If you have ever felt the need of measuring how much of your resources a MySQL process is eating up, you're welcome to share my experience on the subject, reading Measuring resources for a MySQL server on Linux, which also introduces mysqlresources, a new command line tool that gets your server's statitics from the operating system and prints a nice report.

With MySQL, it's easy to get statistics from the server itself, but sometimes you need a view from the outside. mysqlresources does just that. Read on.

A first taste of logs on demand

From time to time I install MySQL from the development tree, to see what's new, and in the latest one I found a pleasant surprise:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta

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

mysql [localhost] {datacharmer} ((none)) > show variables like "%log";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| back_log                       | 50    |
| general_log                    | OFF   |
| innodb_locks_unsafe_for_binlog | OFF   |
| log                            | OFF   |
| slow_query_log                 | OFF   |
| sync_binlog                    | 0     |
+--------------------------------+-------+
6 rows in set (0.00 sec)

mysql [localhost] {datacharmer} ((none)) > select * from mysql.general_log;
Empty set (0.01 sec)

mysql [localhost] {datacharmer} ((none)) > set global general_log=1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {datacharmer} ((none)) > select * from mysql.general_log;
+---------------------+-----------------------------------------+-----------+-----------+--------------+---------------------------------+
| event_time          | user_host                               | thread_id | server_id | command_type | argument                        |
+---------------------+-----------------------------------------+-----------+-----------+--------------+---------------------------------+
| 2006-07-05 17:26:56 | datacharmer[datacharmer] @ localhost [] |         3 |         0 | Query        | select * from mysql.general_log |
+---------------------+-----------------------------------------+-----------+-----------+--------------+---------------------------------+
1 row in set (0.04 sec)

mysql [localhost] {datacharmer} ((none)) > select * from mysql.general_log;
+---------------------+-----------------------------------------+-----------+-----------+--------------+---------------------------------+
| event_time          | user_host                               | thread_id | server_id | command_type | argument                        |
+---------------------+-----------------------------------------+-----------+-----------+--------------+---------------------------------+
| 2006-07-05 17:26:56 | datacharmer[datacharmer] @ localhost [] |         3 |         0 | Query        | select * from mysql.general_log |
| 2006-07-05 17:27:08 | datacharmer[datacharmer] @ localhost [] |         3 |         0 | Query        | select * from mysql.general_log |
+---------------------+-----------------------------------------+-----------+-----------+--------------+---------------------------------+
2 rows in set (0.00 sec)

mysql [localhost] {datacharmer} ((none)) > set global general_log=0;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {datacharmer} ((none)) > select * from mysql.general_log;
+---------------------+-----------------------------------------+-----------+-----------+--------------+---------------------------------+
| event_time          | user_host                               | thread_id | server_id | command_type | argument                        |
+---------------------+-----------------------------------------+-----------+-----------+--------------+---------------------------------+
| 2006-07-05 17:26:56 | datacharmer[datacharmer] @ localhost [] |         3 |         0 | Query        | select * from mysql.general_log |
| 2006-07-05 17:27:08 | datacharmer[datacharmer] @ localhost [] |         3 |         0 | Query        | select * from mysql.general_log |
| 2006-07-05 17:27:11 | datacharmer[datacharmer] @ localhost [] |         3 |         0 | Query        | set global general_log=0        |
+---------------------+-----------------------------------------+-----------+-----------+--------------+---------------------------------+
3 rows in set (0.00 sec)

As you can see, the promised logs on demand are there.
This feature was long overdue. Well done!