Introducing WarpTalks

This week we had our first WarpTalks session. Once a month we’ll gather in our meeting room and someone will deliver a talk, workshop or debate about topics considered interesting.

We opened this Monday with two talks. They are in Spanish but you can get the idea.

Introduction to Subversion by Victor Jimenez

Subversion is the RCS we currently use, and the developers know it well enough to do their everyday job, but the not-so technical people at the company have been expecting some training for a while.

http://vimeo.com/moogaloop.swf?clip_id=3012361&server=vimeo.com&show_title=1&show_byline=1&show_portrait=1&color=b1c800&fullscreen=1
Introduccion a Subversion from Jorge Bernal on Vimeo.

10 things you might not know about MySQL by Jorge Bernal (me)

MySQL is the obvious choice when we need a database for our projects, so many of the developers use it daily. I tried to show some aspects of MySQL which could be useful to them but not the first things you learn about a database.

http://vimeo.com/moogaloop.swf?clip_id=3009490&server=vimeo.com&show_title=1&show_byline=1&show_portrait=1&color=b1c800&fullscreen=1
10 cosas que quiza no sepas sobre MySQL from Jorge Bernal on Vimeo.

MySQL Conference 2009, I need an idea

I had a sad time this year when I missed the MySQL conference, since I had much fun last year in Santa Clara. I can’t miss it next year.

As a MySQL partner, and after almost 2 years doing MySQL training, I sure have interesting things to tell in the conference, but I’m not sure about what.

I will be thinking about this in the next weeks, but I’d appreciate some help. What topics are you interested in?

Loading a MySQL database in memory (with some Ruby help)

Let’s say when you have to run a batch process monthly, you can survive with times like 10 minutes. I can imagine a lot of seasoned DBAs right now ROFL about my insignificant 10 minutes. The point here is I was developing this process and some test cases, so my usual trial/error methodology doesn’t scale very well with 10 minute offsets.

So I borrowed an idea from a colleague: why not moving all the database to memory? It’s not so big and I have 2G of ram. But, could I change all tables (~20) to MEMORY in one line or so?

Since this was a Ruby on Rails project, I used the rails console to be able to mix SQL and Ruby. My first try:

conn.tables.each do |t|
  conn.execute "ALTER TABLE #{t} ENGINE=MEMORY"
end

First error: foreign keys couldn't be migrated from InnoDB to MEMORY
Maybe there is a more MySQL-esque way of dropping all the foreign keys on a database but this one worked quite well:

>> res = conn.execute "SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
 WHERE table_schema = 'app_development'
 AND REFERENCED_TABLE_NAME IS NOT NULL"
>> res.each_hash do |h|
  conn.execute "ALTER TABLE #{h['TABLE_NAME']}
                       DROP FOREIGN KEY #{h['CONSTRAINT_NAME']}"
end
>> conn.tables.each do |t|
  conn.execute "ALTER TABLE #{t} ENGINE=MEMORY" rescue nil
end

Still I had some problems with a few tables using BLOBs, but they were not used on the process/tests so I ignored them. That's what the rescue nil is for.

Hacking MySQL: making TRUNCATE behaviour more intuitive

This is my second article about hacking MySQL. If you are interested in this topic, you may want to read my previous Hacking MySQL: SIGNAL support (I)

The problem

If I tell you there is a function called TRUNCATE, what do you think it does? which are its arguments?

For me, the obvious behaviour would be something like:

mysql> SELECT TRUNCATE(123.45);
+--------------------+
| TRUNCATE(123.45)   |
+--------------------+
|                123 |
+--------------------+
1 row in set (0.08 sec)

But the actual behaviour is this:

mysql> SELECT TRUNCATE(123.45);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

This is because TRUNCATE requires a second argument. The syntax, according to the manual, is:

TRUNCATE(X,D)

Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. D can be negative to cause D digits left of the decimal point of the value X to become zero.

Wouldn't be cool if the default value for D were 0 if we don't specify otherwise?

The solution

This time we only have to touch the parser (sql/sql_yacc.yy). The code for this is quite simple, although you might find some problems on type conversion.

--- mysql-5.1/sql/sql_yacc.yy	2007-05-08 12:25:51.000000000 +0200
+++ mysql-5.1-truncate/sql/sql_yacc.yy	2007-05-08 15:55:42.000000000 +0200
@@ -6741,6 +6741,13 @@
 	  { $$= new (YYTHD->mem_root) Item_func_replace($3,$5,$7); }
 	| TRUNCATE_SYM '(' expr ',' expr ')'
 	  { $$= new (YYTHD->mem_root) Item_func_round($3,$5,1); }
+	| TRUNCATE_SYM '(' expr ')'
+	  {
+            THD *thd= YYTHD;
+            Item *i1= new (thd->mem_root) Item_int((char*) "0",(int32) 0,1);
+
+      	    $$= new (thd->mem_root) Item_func_round($3,i1,1);
+	  }
 	| WEEK_SYM '(' expr ')'
 	  {
             THD *thd= YYTHD;

Get the patch: [28304-truncate_default_value.diff]

Like I said, the hardest part was to go through sql/item*.h trying to guess the expected type for Item_func_round and how to cast that type correctly. I should learn how to better debug the MySQL server.

Conclusion

Unfortunately, the bug (#28304) has been marked as to be fixed later but, at least, this helped me on my way to understand MySQL internals.

Why to teach?

My decision about 1 year ago to start doing training was a strange move for me. It was totally new and scary field for me, but like I said at the moment, according to my experience all change has been for good. I think I can trust my intuition.

Like Seth Godin said (more than) once:
Safe is risky

I found a short article about How we learn which summarizes my point of view after 5 courses.

10% of what we READ

20% of what we HEAR

30% of what we SEE

50% of what we SEE and HEAR

70% of what is DISCUSSED with OTHERS

80% of what is EXPERIENCED PERSONALLY

95% of what we TEACH TO SOMEONE ELSE

William Glasser

You can never imagine the kind of questions you are going to get from so many different mindsets, so you have to find a lot of answers that you’d never find otherwise.

Hacking MySQL: SIGNAL support (I)

I’ve been looking for an open source project to collaborate for some time now, and given the time I’m spending with MySQL lately and the expertise I’m gaining thanks to MySQL training, it looked like an obvious choice.

During the last advanced bootcamp, Tobias found bug #27894, which apparently was a simple fix. Dates in binlog were formatted as 736 instead of 070306 (for 2007-03-06). During the bootcamp I used my lonely nights at the hotel and came up with a patch, and some days later my first contribution was going into the main MySQL code.

The problem

Now I had to find something bigger. One of the things that most annoys me of MySQL is the lack of some way to abort a procedure or trigger: there is no raise method. To generate a custom error you have to do hacks like:

SELECT `

Error: Invalid firmware series for this model

` INTO dummy FROM model;

The solution

There is a SIGNAL command in the SQL:2003 standard which does the job, but it's not implemented (yet) in MySQL. The syntax, according to the manual is as follows:

SIGNAL signal_value [ SET signal_information_list ]

signal_value:
    condition_name
  | sqlstate_value

signal_information_list:
    [ signal_information_list , ] signal_information_item

signal_information_item:
    condition_name = condition_value

condition_name:
    CLASS_ORIGIN
  | SUBCLASS_ORIGIN
  | CONSTRAINT_CATALOG
  | CONSTRAINT_SCHEMA
  | CONSTRAINT_NAME
  | CATALOG_NAME
  | SCHEMA_NAME
  | TABLE_NAME
  | COLUMN_NAME
  | CURSOR_NAME
  | MESSAGE_TEXT

In this first part I'll cover the basics: just the SIGNAL command with a fixed generic error, enough to get rid of the dirty hacks.

The implementation

Getting used to foreign code always takes some level of difficulty, but when you have to deal with grammars and parsers it's all crazy fun. First, we have to add a symbol for our new command

sql/lex.h

In this file, we have a symbols[] array where we have to add SIGNAL. Since it seems to be sorted in alphabetic order, we'll put our line between SHUTDOWN and SIGNED:

   { "SHUTDOWN",    SYM(SHUTDOWN)},
   { "SIGNAL",    SYM(SIGNAL_SYM)},
   { "SIGNED",    SYM(SIGNED_SYM)},

sql/share/errmsg.txt

Before we get our hands dirty with the parser file, let's get our custom error prepared. I took a look at the SQLSTATE error messages and I found the 38503 (Exception generated from user-defined function/procedure) enough related to this.

In this file we have a series of error constants with their corresponding error messages in various languages. Since our new error will be related to stored procedures, I decided to put with the rest of SP-related errors:

 ER_SP_CASE_NOT_FOUND 20000
         eng "Case not found for CASE statement"
         ger "Fall für CASE-Anweisung nicht gefunden"
 ER_SP_SIGNAL 38503
         eng "Exception generated from user-defined function/procedure"
 ER_FPARSER_TOO_BIG_FILE
         eng "Configuration file '%-.64s' is too big"
         ger "Konfigurationsdatei '%-.64s' ist zu groß"

sql/sql_yacc.yy

And finally to the point. Here we have to declare that we'll be using the SIGNAL_SYM which we defined at sql/lex.h as a token.

 %token  SHUTDOWN
 %token  SIGNAL_SYM
 %token  SIGNED_SYM

Then, in the sp_proc_stmt label (look for sp_proc_stmt: at the beginning of a line), we add sp_proc_stmt_signal as another possibility (we'll define this in a minute):

 	| sp_proc_stmt_iterate
 	| sp_proc_stmt_signal
 	| sp_proc_stmt_open

And finally, between the sp_proc_stmt_iterate and the sp_proc_stmt_open definition we add our code:

sp_proc_stmt_signal:
    SIGNAL_SYM
  	{
            LEX *lex= Lex;
	    sp_head *sp= lex->sphead;
	    sp_instr_error *i;

	    i= new sp_instr_error(sp->instructions(), lex->spcont, ER_SP_SIGNAL);
	    sp->add_instr(i);
	  }

This basically tells the parser to expect the SIGNAL_SYM token (SIGNAL) with no arguments, and generate an error with our new error code (ER_SP_SIGNAL). As you might see there's some extra code which I copied directly from similar definitions, which I'll refer to as parser magic (anyone willing to explain what sphead and lex variables are will be very welcome)

Conclusion

This one wasn't so extremely difficult if you had some previous experience with Bison, but the next part can be more interesting, since I guess we'll have to add some more functions than sp_instr_error to be able to show custom error messages. Also, we'll have to prepare some test cases to verify our newly created behaviour.

I hope this helps someone trying to contribute to MySQL. If you want to try this at home you can follow the article or apply the patch