#gsoc2014

[srirampatil]

Adding support for OR REPLACE, IF NOT EXISTS and IF EXISTS (Pencils down)

Hello All,

Yesterday was the the hard 'pencils down' date for Google Summer of Code'2014. If you have been following my blog, you would know that I had been working on adding support for OR REPLACE, IF NOT EXISTS and DROP...IF EXISTS commands into MariaDB for all objects. MDEV-5359 gives details about the project. This blog post is to summarize the work I have done in last three months.

I started with studying the MariaDB code base. I started off with bazaar and launchpad but immediately moved to github repo as I am more comfortable with git. Also, my projects changes will be applied to MariaDB version 10.1, so it allowed me to switch to github.

After getting into the coding standards and the work flow of the "query execution" in MariaDB in the first week. I picked up the most simple command of them all which required very little code changes (at least this is what I thought at that time), CREATE OR REPLACE DATABASE.

At the end of second week I was all into coding. I thought of writing blog posts every week to keep track of what I do each week and my mentor, Alexander, also gave a thumbs up to that. Believe me, writing blog was the first most important decision I took, this helped me a lot. The response that I got for my blog was also great. Many developers from MariaDB mailing lists joined in and gave their inputs about what should be there in the blog and what should not be there. So, thanks to all of them, my blog got better and better.

Again, making use of the blog that I have written and simplifying my job, following are the links to the work I have done each week starting from first week to the last. Please, visit them all and get yourself acquainted with all the changes that I have made.

CREATE OR REPLACE DATABASE, CREATE USER/ROLE/SERVER IF NOT EXISTS and DROP USER/ROLE IF EXISTS

CREATE PROCEDURE/VIEW IF NOT EXISTS

CREATE TRIGGER/FUNCTION(STORED and UDF) IF NOT EXISTS, DROP FUNCTION(UDF) IF EXISTS

All about bin logging

Fixed MDEV-6409

CREATE OR REPLACE SERVER/TRIGGER

CREATE OR REPLACE PROCEDURE/FUNCTION(STORED)/EVENT

CREATE OR REPLACE FUNCTION(UDF)/USER/ROLE, CREATE DATABASE IF NOT EXISTS and permission checks

Test cases and bug fixes

CREATE OR REPLACE INDEX, Replication test cases and bug fixes

I have also made some changes in the last week which can be checked on my repository. You can find my repository here.

Ending this, I would like to thank everyone for allowing we to work on such an amazing project, and everyone who helped me out, on IRC, on the mailing lists, and everywhere else. :)

Thanks a lot!

[srirampatil]

Week Twelve!

Update: CREATE OR REPLACE INDEX implemented. Needs review and some small changes. You can check the commit here.

The test cases phase continues from the previous week. I have written test cases for replication for almost all the objects. Only EVENTS is left for the same. There are no replication test cases for SERVER as the query is not written into the log for SERVERs.

During this I was able to find a bug in TRIGGER replication and have fixed it by adding a private method named build_trig_stmt_query. 

Following are the commits from the last week.

Replication tests for DB and USER

Replication tests for all other objects

TRIGGER replication bug fix

I am still working on CREATE OR REPLACE INDEX. It is a little complicated to handle cause CREATE, DROP and ALTER all the three commands for INDEX are handled by the same function. Will be giving a logical conclusion to it by EOD (11th August).

Suggestions and/or reviews are welcome!

[srirampatil]

Week Eleven!

I wrote basic test cases for the commands I have implemented until now in the last week. These are all very basic test cases and I will be adding into these in the coming weeks. The naming convention for the test files is create_drop_<object>.test. There is one separate file for checking CREATE OR REPLACE permissions named create_or_replace_permission.test.

Found two bugs in my code while testing. Both were in CREATE FUNCTION (stored). The return type of a stored function was not getting stored in the procedures table. So when invoked it would give an error saying that the table is corrupted.

Another bug was in permission checks, where I was checking DELETE_ACL instead of ALTER_ROUTINE. DELETE_ACL permission is checked for UDF and ALTER_ROUTINE for procedures and stored functions.

Following are the commits of last week, you can check the test files and their results, plus the bug fixes there.

Permission checks for create or replace

Basic tests for all objects

Return type bug in stored functions

Wrong permission check bug for stored function

With respect to implementation, only CREATE OR REPLACE INDEX is left and may be refactoring of some code. Apart from this it is going to be all about testing now on.

Suggestion and reviews are welcome!

[srirampatil]

Week Nine and Ten!

Continuing with CREATE OR REPLACE implementation from week eight. First, CREATE OR REPLACE USER/ROLE. You can check the changes in commit 02cefab. In the implementation, instead of calling complete drop function for USER/ROLE, I have just used the part that actually drops the USER/ROLE. Some code related to permission checks in this commit has been changed in the next commits, keep reading to know about it.

Moving to some more important stuff. CREATE OR REPLACE FUNCTION(UDF). My first attempt to write the code for this was not that great. It had some redundant code which could have been modulated for better understanding and code structure. Thanks to Alexander (my mentor) for a detailed review and now the code looks better. Here is how the final code looks like.I wrote a new private method in sql_udf.cc named mysql_drop_function_internal. Checkout the code below

/* Drop user defined function. @param thd Thread handler. @param udf Existing udf_func pointer which is to be deleted. @param table mysql.func table reference (opened and locked) Assumption - udf is not null. - table is already opened and locked */ static int mysql_drop_function_internal(THD *thd, udf_func *udf, TABLE *table) { DBUG_ENTER("mysql_drop_function_internal"); char *exact_name_str= udf->name.str; uint exact_name_len= udf->name.length; del_udf(udf); /* Close the handle if this was function that was found during boot or CREATE FUNCTION and it's not in use by any other udf function */ if (udf->dlhandle && !find_udf_dl(udf->dl)) dlclose(udf->dlhandle); if (!table) DBUG_RETURN(1); table->use_all_columns(); table->field[0]->store(exact_name_str, exact_name_len, &my_charset_bin); if (!table->file->ha_index_read_idx_map(table->record[0], 0, (uchar*) table->field[0]->ptr, HA_WHOLE_KEY, HA_READ_KEY_EXACT)) { int error; if ((error = table->file->ha_delete_row(table->record[0]))) table->file->print_error(error, MYF(0)); } DBUG_RETURN(0); }

As one can understand from comments, this method drops an existing UDF and nothing else. This method is invoked from both mysql_drop_function and mysql_create_function (in case of CREATE OR REPLACE) to perform actual drop. All the error checks and opening and locking of mysql.func table and searching for existing UDF is done in the mysql_drop_function and mysql_create_function. The changes made for this can be seen here and here.

Then I did some refactoring with respect to permission checks for CREATE OR REPLACE in general. Instead of checking the DROP permission before calling respective REPLACE logic, we (me and my mentor) decided to check for the permission before invoking respective create function in sql_parse.cc. Which means, if a user does not have a DROP permission on certain object, s/he will not be able to execute a CREATE OR REPLACE command ever. This behavior is consistent with the current CREATE OR REPLACE TABLE implementation (check create_table_precheck method in sql_parse.cc). General structure for checking permissions is as follows

check_access(..., (lex->is_create_or_replace()) ? CREATE | DROP : CREATE, ...)

The changes can be observed in this commit.

I have also changed some code for readability and a small bug fix in CREATE DATABASE IF NOT EXISTS. Checkout the list of commits below, bottommost being the latest one.

CREATE OR REPLACE FUNCTION (UDF) before review

CREATE OR REPLACE USER/ROLE

CREATE OR REPLACE FUNCTION (UDF) after review

More readable code

Bug fix in CREATE DATABASE IF NOT EXISTS

Changes in permission checks

Apart from this I have also fixed MDEV-6409 in the last week on MariaDB 5.5. Thanks to Sergei for helping me with the patch.

Suggestions and reviews are welcome!

Update: Writing test cases for testing permissions logic will be the first thing I do in this week.

[sawrubh]

GSoC Progress Report

It's been quite a long time since I blogged about my GSoC work.

Here's what I've been working on :

I have the patches on the adding file transfer support in js-xmpp bug in a pretty good state now (and hopefully should be ready to land). This took quite some time, with me learning more about Promises and Task, which I didn't have any experience from before. It took me quite some time getting a hang of it.

I also worked on integrating the Firefox Downloads Panel in Instantbird. I'll have a WIP soon.

I rebased my FileLink patch and am now trying to fix some issues with the signing in.

I've not had a lot to show in this time, with various things coming in like the World Cup, issues in patches which tool unexpectedly more time to debug and breaks on a few days. I'm planning to ramp up the work and get back on track with my schedule.

[srirampatil]

Week Eight!

Continuing with the CREATE OR REPLACE implementations, last week I implemented the same for procedures, stored functions and events. The approach has been the same for all. Added a parameter to drop methods PROCEDURE and STORED FUNCTION. The boolean parameter is names as drop_for_replace indicating if the drop function call is part of a replace command or not. I have changed the parameter name in the drop method for SERVER too from the one use in week seven. I thought this one is more readable.

In case of EVENTs, there is a little change, instead of calling the complete drop event routine, I just used the part from drop method which actually drops the existing event. The reason being, all the code in create event function and drop event function except for the create and drop part is the same. There is no need to execute the same code again by calling the complete drop event routine. So the CREATE OR REPLACE EVENT changes are made in event_db_repository.cc and are as follows

if (!find_named_event(parse_data->dbname, parse_data->name, table)) { if (thd->lex->is_create_or_replace()) { if ((ret= table->file->ha_delete_row(table->record[0]))) { table->file->print_error(ret, MYF(0)); goto end; } } else if (create_if_not) { *event_already_exists= true; push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, ER_EVENT_ALREADY_EXISTS, ER(ER_EVENT_ALREADY_EXISTS), parse_data->name.str); ret= 0; goto end; } else { my_error(ER_EVENT_ALREADY_EXISTS, MYF(0), parse_data->name.str); goto end; } }

As can be observed in the first if condition after find_named_event there is a new function names is_create_or_replace. Last week I have addded two inline functions in struct LEX in order to make it a little intuitive to check for CREATE OR REPLACE and CREATE...IF NOT EXISTS flags. The functions are as follows

inline bool is_create_or_replace() { return (create_info.options & HA_LEX_CREATE_REPLACE); } inline bool is_create_if_not_exists() { return (create_info.options & HA_LEX_CREATE_IF_NOT_EXISTS); }

I have replaced the use of HA_LEX_CREATE_REPLACE and HA_LEX_CREATE_IF_NOT_EXISTS constants for checking the clause flags with these function calls in sql_yacc.yy too.

I will also look into other objects to check if it is possible to take a similar approach like EVENTs. That is, executing only the drop object part of the code instead of executing the complete drop object function.

Following are the commits for last week

CREATE OR REPLACE PROCEDURE

CREATE OR REPLACE FUNCTION

CREATE OR REPLACE EVENT and struct LEX changes

Suggestions and review are welcome! 

[srirampatil]

Week Seven!

Now that CREATE...IF NOT EXISTS and DROP...IF EXISTS clauses are implemented for all the objects, I have moved on to work on CREATE OR REPLACE. Last week I implemented CREATE OR REPLACE SERVER and CREATE OR REPLACE TRIGGER. Both the implementations have some significant changes in function signatures and/or the execution flow.

Let me start with the basic workflow of CREATE OR REPLACE in general. It is going to be same as CREATE OR REPLACE DATABASE. Following is a small snippet for the same.

if object exists if CREATE OR REPLACE used drop the object but make sure deadlock does not occur and the bin log entry is not made for drop. Continue with the create algorithm in the respective function

In case of CREATE OR REPLACE SERVER, I had to deal with deadlock. There is mysql_rwlock_wrlock(&THR_LOCK_servers) statement at the beginning of both create_server and drop_server functions. Because of this statement if one does a CREATE OR REPLACE SERVER and it goes through both create_server and drop_server functions, then the next CREATE SERVER command enters deadlock.

For this I have added a parameter in drop_server function. The signature now is as follows

int drop_server(THD *thd, LEX_SERVER_OPTIONS *server_options, bool dont_lock);

The third parameter, dont_lock, indicates whether there is a need to lock the THR_LOCK_servers in drop_server. If dont_lock is true the it is not locked and is locked otherwise. You can find the changes for SERVER in this commit.

In case of CREATE OR REPLACE TRIGGER, there are two cases

1. Different name create table t1 (a int); create or replace trigger tr1 before insert on t1 for each row begin end; create or replace trigger tr2 before insert on t1 for each row begin end; --> error, multiple triggers with the same action 2. Same name create table t1 (a int); create or replace trigger tr1 before insert on t1 for each row begin end; create or replace trigger tr1 before insert on t1 for each row begin end; --> OK, the trigger is replaced

The previous code in create_trigger function handled only the first condition as there was no CREATE OR REPLACE before. It looked something as follows

if(bodies[lex->trg_chistics.event][lex->trg_chistics.action_time] != 0) { my_error(ER_NOT_SUPPORTED_YET, MYF(0), "multiple triggers with the same action time" " and event for one table"); return 1; }

bodies, is a 2D array of sp_head pointers which represents a trigger. So if there exists a trigger with same event and action time, ER_NOT_SUPPORTED_YET error is thrown. To handle the above mentioned cases, I had to compare the names of the existing triggers and the trigger name mentioned in the CREATE OR REPLACE query. I have changed the code as follows

sp_head *trg_head= bodies[lex->trg_chistics.event] [lex->trg_chistics.action_time]; ... ... /* We don't allow creation of several triggers of the same type yet. Note: Comparing the trigger names here to avoid throwing the error without considering the possibility of CREATE OR REPLACE and IF NOT EXISTS clauses in the query. */ if(trg_head != 0 && strcmp(trg_head->m_name.str, lex->spname->m_name.str) != 0) { my_error(ER_NOT_SUPPORTED_YET, MYF(0), "multiple triggers with the same action time" " and event for one table"); return 1; }

And to handle the CREATE OR REPLACE clause, that is, drop the existing trigger and then create a new one works as follows.

if (!access(trigname_buff, F_OK)) { if (lex->create_info.options & HA_LEX_CREATE_REPLACE) { String drop_trg_query; drop_trg_query.append("DROP TRIGGER "); drop_trg_query.append(lex->spname->m_name.str); if(drop_trigger(thd, tables, &drop_trg_query)) return 1; } else if (lex->create_info.options & HA_LEX_CREATE_IF_NOT_EXISTS) { push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, ER_TRG_ALREADY_EXISTS, ER(ER_TRG_ALREADY_EXISTS), trigname_buff); return 0; } else { my_error(ER_TRG_ALREADY_EXISTS, MYF(0)); return 1; } }

drop_trg_query, holds the DROP TRIGGER command. You can find the changes for TRIGGER in this commit.

I have also done some minor changes for improving readability and removing some redundant permissions checks, you can find those here and here.

Suggestions and review are welcome!

[gauravsaini]

Half way through GSOC !

Just few days back I got my mid term evaluations cleared by my mentor.  It come as a great joy to me, as initial I was a bit tense as the project I proposed got changed by the organization.

As mentioned before I was to work on Android App (Extend Community app to run on Mobile devices with Off line Support), but later on after having discussion with the community they switched me to another project in which I am working on "Various UI improvement and Enhancements in the Community-App". 

Till the mid term evaluation, I have been working on variours parts and UI enhancement in the Community App which is based on AngularJS. You can see my work and commits at github and see my progress.

During this time I have learnt a lot from the experienced developers in the mifos community. My skills in specifically AngularJS have improved a lot. Many new things I came to know while working on various tasks and also interacting in AngularJS IRC channel.

I am looking forward to successfully complete my GSOC 2014 campaign :)