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.