Koke’s

Hacking MySQL: SIGNAL support (I)

Posted by Jorge Bernal May 09, 2007

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

Bookmark this post: · Del.icio.us · YahooMyWeb · Spurl · Furl · Incoming links