Though this is an age old concept, but after seeing the ignorance factor of many regarding this, I thought of writing a bit about it. ROLLUP is a GROUP BY modifier that adds extra rows to the output which contain summary data. At each level of aggregation the aggregated column is filled with a NULL value.

Let's see a small example. I know the values are too small for the data presented, but just to keep it readable.

   1: mysql> CREATE TABLE `rollup_1` (

   2:   `url` varchar(255) DEFAULT NULL,

   3:   `year` int(11) DEFAULT NULL,

   4:   `country` char(2) DEFAULT NULL,

   5:   `visit` int(11) DEFAULT NULL

   6: ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

   7: Query OK, 0 rows affected (0.01 sec)

   8:  

   9: mysql> SELECT * FROM rollup_1;

  10: +----------------------+------+---------+-------+

  11: | url                  | year | …

Mysql AB provides print_stacktrace function inside stacktrace.c.
void print_stacktrace(gptr stack_bottom, ulong thread_stack)

mysqld.cc calls it when segfault happens:
print_stacktrace(thd ? (gptr) thd->thread_stack : (gptr) 0,
thread_stack);

Its basic idea of print_stacktrace is

1. first get current frame pointer fp

take __i386__ for example, fp is
Extended Base Pointer (ebp)

#ifdef __i386__
__asm __volatile__ ("movl %%ebp,%0"
:"=r"(fp)
:"r"(fp));
#endif


+---------+------------------+
| EBP - 4 | local variables |
| EBP | callees EBP |
| EBP + 4 | ret-addr |
| EBP + 8 | parameters |
+---------+------------------+

2. transverse the stack to print the call stack info
/* We are 1 frame above signal frame with NPTL and 2 frames above …

We can use GNU C lib backtrace functions to get call stack on Linux OS.
int backtrace (void **buffer, int size)
char ** backtrace_symbols (void *const *buffer, int size)
header file: execinfo.h
in order to get the symbols, we need pass -rdynamic to gcc.
-rdynamic will pass the flag -export-dynamic to the ELF linker, on targets that support it. This instructs the linker to add all symbols, not only used ones, to the dynamic symbol table.

Limitation: it only works when backtrace is provided in GNU C. It does not work on Mac.


Compile: gcc -o mytrace mytrace.c -rdynamic

Example Result:
Obtained 6 stack frames.
./mytrace(print_trace+0x14) [0x8048724]
./mytrace(dummy_function+0xb) [0x80487b7]
./mytrace(dummy_function_1+0xb) [0x80487a7]
./mytrace(main+0xb) [0x80487c7] …

A couple of weeks ago, a friend asked about replication on MySQL 4.1.7. I’ve worked with replication in the past, just a quick and dirty job on MySQL 5, and soon forgot about it. This time, I wanted to do it on MySQL 4, and make sure I take good notes for my own benefit. If it can help somebody else, all the better. The official documentation is here. It took a little time to wade through it.

The process below is used to replicate all databases on the master to the slave(s). I will talk about replicating only certain selected databases in a future post.

1. At the master server, check if binary logging is on. Use show variables like ‘%bin%’ to check if binary logging is on or not. If it is not on, turn it on by adding log-bin under [mysqld] section in …

I'll be heading to OSCON 2007 on Tuesday. Unfortunately, my stay will be short as I already have to head back to California on Wednesday evening but I am still looking forward to meeting as many open-source enthusiasts as possible. Our partner MySQL and us (Zend) are hosting a reception together for our friends and users which I'm very much looking forward to. With an overwhelming overlap in our communities I'm sure it'll make for many interesting discussions.

On Wednesday I'll be giving two talks back to back. The first is on Rich Internet Applications & PHP where I'll talk about the state of Ajax + PHP, show a small demo with Zend Framework and talk about some of the things we've got cooking.

After a short break, I'll be giving a talk on security. My goal for that talk is to take a completely different angle on security than what …

I'm moving my blog from http://gnu.inter.it/blogs/ilcorra/archives/cat_mysqlen.html to http://blog.pandiani.com

My new MySQL related XML feed is http://blog.pandiani.com/category/mysqlen/feed

Please update your links

Thanks

Scott Noyes wrote about a question frequently asked in for instance the freenode #mysql channel: given a table with students and answers, which students have answered all of a certain set of questions.

While his solutions are interesting, they do indulge a bit. And I'm not even referring to the fact that he uses obscure string and bit functions... he can do that if he wants to ;-) But it's a relatively simple problem that just does not need such (potentially inefficient) constructs.

For starters, it'd be good to have a WHERE clause, because even if there are many more questions in the table, you only need the As and Bs. You never want to use the HAVING as a replacement for WHERE, because having chucks away already retrieved rows from the result set, while WHERE limits what is retrieved in the first place.

Also, while it's very good to …

As we get close to the 1.0 release of our opentaps Open Source ERP + CRM system, I've been trying very hard to think about what future versions will do.

The immediate future is pretty easy to see: we have a good system for bringing together processes and data in a company. We can easily extend it to meet the needs more of more companies and industries. We'll also be adding tools for data analysis.

But what should opentaps 4.0 do? And how should it do it?

This is hard for me to see right now, but I could envision how future computers in general should work, so I'm going to jot down a few notes to help me think later:

In 2012, my computer is the size of a credit card. I'll take it with me wherever I go and turn it on either using a scan of my eye or my thumb. It comes with no monitor and no keyboard, but once turned on, it will project a …

I've been getting quite a few reports that my 5.0 and 5.1 installers don't work on Vista.  The reports all say that near the end of the install, it errors out with an error code of 2869. 

As a side note, I have yet to have an MSI error code that had a description that I  understood.  The description is almost always something like 'The transaction failed to start'.  These descriptions don't tell me anything.  But I digress.

This 2869 issue was hard to reproduce and very random.  After some work I found a 32 bit Vista machine that showed the problem.  With some trial and error and installutil style debugging I discovered that the problem was in my custom installer class.  Inside the MySql.Data assembly, we have a custom installer class that does two things.  First it adds the assembly to the system's machine.config file and then it adds a couple of performance counters.  …

While using the latest MySQL 5.1.20 yesterday I came across another situation that was not expected as with previous editions of MySQL. The background is experimenting with DRBD. When I configured MySQL to startup with a /etc/my.cnf file with data on a DRBD partition I got a failed startup error message with mysqld_safe.

$ bin/mysqld_safe &
[1] 12615
070720 10:10:42 mysqld_safe Starting mysqld daemon with databases from /drbd/data
070720 10:10:42 mysqld_safe mysqld from pid file /drbd/data/newyork.localdomain.pid ended

Ok. Well this happens so I went to the data directory to look for `hostname`.err.

$ cd /drbd/data
$ ls -l

What the! There is no error log. Then the discussion started about this. Apparently mysqld_safe now uses syslog (e.g. /var/log/messages) for logging messages. Ok, but where is the line between mysqld_safe and mysqld. …