[Maria-developers] Correct parsing of BINLOG 'XXX' with comment inside

[Sachin Setia]

Hi Bar,

Currently if we have generated the sql file using mysqlbinlog -v and
if we have big statement binlog
, then mysqlbinlog add comment in between of Binlog 'XXX' statement ,
but unfortunately base64_decode
does not understands it, and it through error since # is not a base64 char

This patches solves this. There are 2 patches one for 5.5 and 2nd for
10.0 and above
Please review it.
commit 18e21756685b75cfc89adef0b24e183196aedc02
Author: Sachin 
Date:   Mon Sep 10 13:58:57 2018 +0530

MDEV-10362 mysqlbinlog verbose output cannot be decoded

base64_encode does not correctly parse comments inside of base64 string.

This patch fixes that.

diff --git a/mysql-test/suite/rpl/r/rpl_mysqlbinlog.result b/mysql-test/suite/rpl/r/rpl_mysqlbinlog.result
new file mode 100644
index 000..f0f609220d9
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_mysqlbinlog.result
@@ -0,0 +1,13 @@
+include/master-slave.inc
+[connection master]
+create table t1(a int , b int);
+#202 entry so that binlog query is split
+select count(*) from t1;
+count(*)
+202
+DROP TABLE t1;
+select count(*) from t1;
+count(*)
+202
+DROP TABLE t1;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_mysqlbinlog.test b/mysql-test/suite/rpl/t/rpl_mysqlbinlog.test
new file mode 100644
index 000..2d458a51ab6
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_mysqlbinlog.test
@@ -0,0 +1,30 @@
+--source include/have_binlog_format_row.inc
+--source include/master-slave.inc
+
+connection master;
+create table t1(a int , b int);
+
+--echo #202 entry so that binlog query is split
+--let str=insert into t1 values(1,1),
+--let counter=200
+while ($counter)
+{
+  --let str=$str(1,1),
+  --dec $counter
+}
+--let str=$str(1,1)
+--disable_query_log
+--eval $str
+--enable_query_log
+select count(*) from t1;
+--let $MYSQLD_DATADIR= `SELECT @@datadir`
+--exec $MYSQL_BINLOG -vvv $MYSQLD_DATADIR/master-bin.01 > $MYSQTEST_VARDIR/tmp/a.binlog
+
+connection master;
+DROP TABLE t1;
+
+--exec $MYSQL < $MYSQTEST_VARDIR/tmp/a.binlog
+select count(*) from t1;
+DROP TABLE t1;
+
+--source include/rpl_end.inc
diff --git a/mysys/base64.c b/mysys/base64.c
index b48bcb85e03..3942b26b0b2 100644
--- a/mysys/base64.c
+++ b/mysys/base64.c
@@ -170,6 +170,8 @@ base64_decode(const char *src_base, size_t len,
 
 SKIP_SPACE(src, i, len);
 
+while(*src == '#')
+  while(*src++ != '\n'){}
 c += pos(*src++);
 c <<= 6;
 i++;
commit 15a8e5da3b155a7cc6063802a3cf2a9987444097
Author: Sachin 
Date:   Mon Sep 10 13:59:27 2018 +0530

MDEV-10362 mysqlbinlog verbose output cannot be decoded

base64_encode does not correctly parse comments inside of base64 string.

This patch fixes that.

diff --git a/mysql-test/suite/rpl/r/rpl_mysqlbinlog.result b/mysql-test/suite/rpl/r/rpl_mysqlbinlog.result
new file mode 100644
index 000..f0f609220d9
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_mysqlbinlog.result
@@ -0,0 +1,13 @@
+include/master-slave.inc
+[connection master]
+create table t1(a int , b int);
+#202 entry so that binlog query is split
+select count(*) from t1;
+count(*)
+202
+DROP TABLE t1;
+select count(*) from t1;
+count(*)
+202
+DROP TABLE t1;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_mysqlbinlog.test b/mysql-test/suite/rpl/t/rpl_mysqlbinlog.test
new file mode 100644
index 000..2d458a51ab6
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_mysqlbinlog.test
@@ -0,0 +1,30 @@
+--source include/have_binlog_format_row.inc
+--source include/master-slave.inc
+
+connection master;
+create table t1(a int , b int);
+
+--echo #202 entry so that binlog query is split
+--let str=insert into t1 values(1,1),
+--let counter=200
+while ($counter)
+{
+  --let str=$str(1,1),
+  --dec $counter
+}
+--let str=$str(1,1)
+--disable_query_log
+--eval $str
+--enable_query_log
+select count(*) from t1;
+--let $MYSQLD_DATADIR= `SELECT @@datadir`
+--exec $MYSQL_BINLOG -vvv $MYSQLD_DATADIR/master-bin.01 > $MYSQTEST_VARDIR/tmp/a.binlog
+
+connection master;
+DROP TABLE t1;
+
+--exec $MYSQL < $MYSQTEST_VARDIR/tmp/a.binlog
+select count(*) from t1;
+DROP TABLE t1;
+
+--source include/rpl_end.inc
diff --git a/mysys/base64.c b/mysys/base64.c
index 265b2f22aad..c99728962ef 100644
--- a/mysys/base64.c
+++ b/mysys/base64.c
@@ -201,7 +201,22 @@ my_base64_decoder_skip_spaces(MY_BASE64_DECODER *decoder)
 decoder->error= 1; /* Unexpected end-of-input found */
   return TRUE;
 }
-
+/**
+ * Skip all the comments inside of base64 encoding
+ *
+ * @param decode base64 decoding stream
+ *
+ * @return void
+ * */
+static inline void
+my_base64_skip_comments(MY_BASE64_DECODER *decoder)
+{
+  my_base64_decoder_skip_spaces(decoder);
+  const char* src= decoder->src;
+  while(*src == '#')
+while (*src++ != '\n'){}
+  decoder->src= src;
+}
 
 /**
  * Convert the next character in a base64 encoded stream
@@ -323,11 +338,12 @@ base64_decode(const char *src_base, size_t len,
   decoder.end= src_base + len;
   decoder.error= 0;
  

Re: [Maria-developers] 67260fd: MDEV-15130 Assertion `table->s->null_bytes == 0' failed in ...

[Sachin Setia]

Hi Serg!

Thanks for the review.

On Fri, Apr 20, 2018 at 6:36 PM, Sergei Golubchik  wrote:
> Hi, Sachin!
>
> On Apr 19, sachin wrote:
>
>> diff --git a/storage/perfschema/ha_perfschema.cc 
>> b/storage/perfschema/ha_perfschema.cc
>> index e8e9581..d5846f2 100644
>> --- a/storage/perfschema/ha_perfschema.cc
>> +++ b/storage/perfschema/ha_perfschema.cc
>> @@ -64,6 +64,16 @@ static int pfs_discover_table(handlerton *hton, THD *thd, 
>> TABLE_SHARE *share)
>>  {
>>const PFS_engine_table_share *pfs_share;
>>
>> +  DBUG_EXECUTE_IF("test_pseudo_invisible", {
>> + my_error(ER_INTERNAL_ERROR, MYF(0), "Don't query 
>> performance_schema when"
>> + "test_pseudo_invisible is on");
>> + return 1;
>> + });
>> +  DBUG_EXECUTE_IF("test_completely_invisible", {
>> + my_error(ER_INTERNAL_ERROR, MYF(0), "Don't query 
>> performance_schema when"
>> + "test_completely_invisible is on");
>> + return 1;
>> + });
>>if ((pfs_share= find_table_share(share->db.str, share->table_name.str)))
>>  return share->init_from_sql_statement_string(thd, false,
>>   pfs_share->sql.str,
>
> Better put it inside TABLE_SHARE::init_from_sql_statement_string to
> cover Connect and FederatedX too.
>
> Or - I'd say it's an option too - just close it as not a bug.
> DBUG_EXECUTE_IF can cause a crash, can make a server to misbehave, etc.
> That's normal, it's a debug hack that intentionally makes server to do
> something weird. I wouldn't spend too much time on making it user
> friendly and error-proof.
Actually I agree with second option, dbug_debug can really be
dangerous and are used for internal purpose only.
So I am closing this as not a bug.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
> ___
> Mailing list: https://launchpad.net/~maria-developers
> Post to : maria-developers@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Project Progress Report [Week 3]

[Sachin Setia]

Hi ankit!

What is your github name/account link?

Regards
Sachin

On Jun 20, 2017 12:30 AM, "ANKIT KUMAR"  wrote:

> Hi,
> In this week, we worked on in-memory structures:- Which representation
> to choose(prefix or expression tree), How to store values and how
> authentication will be done.
>
>I have prepared pseudocode to implement this subtask and have made
> implemented some part of code. Hopefully by 25th May, I will finish off
> with >50% of the project.
>
>I will declare Phase-2 deliverables next Monday(26 May).
>
> Kind regards,
> Ankit Kumar
>
> ___
> Mailing list: https://launchpad.net/~maria-developers
> Post to : maria-developers@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp
>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Race Condition in Seconds behind master.

[Sachin Setia]

Hi Kristian,

Thanks I will look into it.

Regards
sachin

On Tue, May 2, 2017 at 6:47 PM, Kristian Nielsen
 wrote:
> Sachin Setiya  writes:
>
>> Then I executed Show slave status (couple of times in a second), and
>> found Seconds_behind_master is changing arbitrarily
>
> I have always found the semantics of Seconds_behind_master complex to
> understand.
>
> I wrote an analysis some time ago:
>
>   https://lists.launchpad.net/maria-developers/msg08958.html
>   https://lists.launchpad.net/maria-developers/msg08961.html
>
> Maybe with that background, you can go back to the binlog and compare time
> stamps and see if the behaviour you see makes sense.
>
> Maybe there are other places in the code where wrong timestamp is binlogged?
>
> I do not remember if the change discussed in that email thread was ever
> implemented (for non-parallel replication).
>
>  - Kristian.
>
> ___
> Mailing list: https://launchpad.net/~maria-developers
> Post to : maria-developers@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] Why We can set binlog_format=mixed for session ?

[Sachin Setia]

Hi Monty!

When flashback is on why we can set binlog_format = Mixed/ statement for
session scope, What is use of this and why this is allowed ?

Regards
sachin

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] Unique Blob Index for lower transaction isolation levels

[Sachin Setia]

Hi Sergei,

Actually I was thinking a different way of implementing this.

in function  ha_write_row , after it calls storage engine write, we
can check for inserted values and see if it is inserted more than
once.

Of course, it can be I/O intensive (or it may not be , because  data
should be in buffer ). We have to do this only when there is multiple
clients doing inserts in same table. But I am not sure if there is any
way to find this. What do you think ?

Regards
sachin

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] MariaDB update deficiency

[Sachin Setia]

Hello Everyone!

This thread is about solving this issue
https://jira.mariadb.org/browse/MDEV-10220.
I made a google doc
https://docs.google.com/document/d/1sor3nVGz2v9Jm4-MDDFfMSn7d7xOip7Qio_T0AqFaNI/edit?usp=sharing
In
which I am explaining my idea , which i think should solve this problem.
Please have a look and tell me what you think ?

Regards
sachin
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hi Sergei!

Actually I changed code as I was suggesting you in previous email.
Duplicate scanning , update etc all works fine using this new approach.
 Currently I am working on optimizer part using this new approach
although the normal where works but now
I am working on cases involving join and delete and update optimization.
I also applied most of the changes suggested by you. but some are left.

Regards
sachin

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hi Vicențiu

Thanks Vicențiu for your comment. Although I agree with you but defining
#define HA_HASH_STR_LEN 4
or
const int HA_HASH_STR_LEN = 4;

make it independent of length of "hash".  Although we rarely gone
change "hash", so I think it
is a good idea. What do you think , Sergei?
Regards
sachin

On Fri, Aug 26, 2016 at 7:26 PM, Vicențiu Ciorbaru  wrote:
> Hi Sachin, Sergei!
>
> One quick thing I wanted to point out. I did not specifically look at how
> things get called, but,
> when defining constants, I don't agree with:
>
>> > +#define HA_HASH_STR_LEN strlen(HA_HASH_STR)
>
> Or:
>>
>> > +#define HA_HASH_STR_INDEX_LEN   strlen(HA_HASH_STR_INDEX)
>
>
> This hides an underlying strlen. Better make it a real constant value.
> Perhaps the compiler is smart enough to optimize it away, but why risk it?
>
> Another one is why not define them as const char * and const int? This also
> helps during debugging, as you can do:
>
> (gdb) $ print HA_HAST_STR_INDEX_LEN
>
> I know that a lot of the code makes use of defines with #define, but why not
> enforce a bit of type safety while we're at it?
>
> Just my 2 cents, feel free to disagree. :)
> Vicentiu
>
>

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hi Sergei!

On Tue, Aug 23, 2016 at 4:35 PM, Sergei Golubchik <s...@mariadb.org> wrote:
>
> Hi, Sachin!
>
> On Aug 22, Sachin Setia wrote:
> > Hi Sergei!
> >
> > Actually I completed the work on update and delete. Now they will use
> > index for looking up records.
> >
> > But I am thinking I have done a lot of changes in optimizer which may
> > break it, and also there are lots of queries where my code does not
> > work, fixing this might take a long amount of time.  I am thinking of
> > a change in my existing code :-
> > Suppose a table t1
> > create table t1 (a blob, b blob, c blob, unique(a,b,c));
> > In current code, for query like there will a KEY with only one
> > keypart which points to field DB_ROW_HASH_1.
> > It was okay for normal updates, insert and delete, but in the case
> > of where optimization  I have do a lot of stuff, first to match field
> > (like in add_key_part), then see whether all the fields in hash_str
> > are present in where or not, then create keys by calculating hash. I
> > do this by checking  the HA_UNIQUE_HASH flag in KEY, but this also
> > makes (I think) optimizer code bad because of too much dependence.
> > Also  I need to patch get_mm_parts and get_mm_leaf function, which I
> > think should not be patched.
>
> Later today I'll know exactly what you mean, when I'll finish
> reviewing your optimizer changes.
>
> But for now, let's say I agree on a general principle :)
> Optimizer is kinda complex and fragile, so it's good to avoid doing many
> changes in it - the effect might be difficult to predict.
>
> > I am thinking of a another approach to this problem at server level
> > instead of having just one keypart we can have 1+3 keypart. Last three
> > keyparts will be for field a, b, c and first one for
> > DB_ROW_HASH_1.These will be only at server level not at storage level.
> > key_info->key_part will point at keypart containing field a, while
> > key_part having field DB_ROW_HASH_1 will -1 index. By this way I do
> > not have to patch more of optimizer code. But there is one problem,
> > what should be the length of key_part? I am thinking of it equal to
> > field->pack_length(), this would not work because while creating keys
> > optimizer calls get_key_image() (which is real data so can exceed
> > pack_lenght() in case of blob), so to get this work I have to patch
> > optimizer  where it calls  get_key_image() and see if key is
> > HA_UNIQUE_HASH. If yes then instead of get_key_image just use
> >memcpy(key, field->ptr(), field->pack_length());
> > this wont copy the actual data, but we do not need actual data. I will
> > patch handler methods like ha_index_read, ha_index_idx_read,
> > multi_range_read_info_const basically handler methods which are
> > related to index or range search.  In these methods i  need to
> > calculate hash, which I can calculate from key_ptr but key_ptr doe
> > not have actual data(in case of blobs etc).So to get the data for
> > hash, I will make a field clone of (a,b,c etc) but there ptr will
> > point in key_ptr. Then field->val_str() method will work simply and i
> > can calculate hash. And also I can compare returned  result with
> > actual key in handler method itself.
> > What do you think of this approach ?
>
> Looks simpler, agree. The length of the keypart should not matter,
> because it should never be used. May be it would be good to set it to -1
> as it might help to catch errors (where it is erroneously used).

I think it should , because we make buffer size according to key_ptr->length.
For example  , this code at test_quick_select

(param.min_key= (uchar*)alloc_root(,max_key_len)

here max_key_length is sum of lengths of all key_part->store_length
and also in function get_mm_leaf we use

  field->get_key_image(str+maybe_null, key_part->length,
  key_part->image_type);

So I think length will matter.

>
> I didn't understand why you need to clone fields though :(

Ohh , this is actually just for reading data from key_ptr, because
key_ptr does not have direct
data  , so i thought that i have to make a clone of field and then set
its ptr. But I guess this
function would work.

inline void move_field(uchar *ptr_arg,uchar *null_ptr_arg,uchar null_bit_arg)
{
ptr=ptr_arg; null_ptr=null_ptr_arg; null_bit=null_bit_arg;
  }

>
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
Regards
sachin

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hi Sergei!

Actually I completed the work on update and delete. Now they will use index
for looking up records.

But I am thinking I have done a lot of changes in optimizer which may break
it , and also there are lots of queries where my
code does not work, fixing this might take a long amount of time.
I am thinking of a change in my existing code :-
Suppose a table t1
create table t1 (a blob, b blob, c blob, unique(a,b,c));
In current code , for query like there will a KEY with only one keypart
which points to field DB_ROW_HASH_1.
It was okay for normal updates , insert and delete , but in the case of
where optimization  I have do a lot of stuff , first to match field (like
in add_key_part), then see whether all the fields in hash_str are present
in where or not, then create keys by calculating hash. I do this by
checking  the HA_UNIQUE_HASH flag in KEY , but this also makes (I think)
optimizer code
bad because of too much dependence. Also  I need to patch get_mm_parts and
get_mm_leaf function , which I think
should not be patched.

I am thinking of a another approach to this problem at server level instead
of having just one keypart we can have 1+3
keypart. Last three keypart will be for field a, b, c and first one for
DB_ROW_HASH_1 .These will be only at server level not at
storage level. key_info->key_part will point at keypart containing field a
, while key_part having field DB_ROW_HASH_1 will
-1 index. By this way I do not have to patch more of optimizer code. But
there is one problem , what should be the length of
key_part? I am thinking of it equal to field->pack_length(), this would not
work because while creating keys optimizer
calls get_key_image() (which is real data so can exceed pack_lenght() in
case of blob), so to get this work I have to patch
optimizer  where it calls  get_key_image() and see if key is HA_UNIQUE_HASH
. If yes then instead of get_key_image just use
 memcpy(key, field->ptr(),
field->pack_length());

this wont copy the actual data, but we do not need actual data. I will
patch handler methods like ha_index_read, ha_index_idx_read ,
multi_range_read_info_const
basically handler methods which are related to index or range search.
In these methods i  need to calculate hash , which I can calculate from
key_ptr but key_ptr doe not have actual data(in case
 of blobs etc).So to get the date for hash , I will make a field clone of
 (a,b,c etc) but there ptr will point in key_ptr. Then
field->val_str() method will work simply and i can calculate hash. And also
I can compare returned  result with actual key in
handler method itself.
What do you think of this approach ?

Regards
sachin

On Sat, Aug 20, 2016 at 11:16 PM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> On Aug 19, Sachin Setia wrote:
> > On Fri, Aug 19, 2016 at 2:42 PM, Sergei Golubchik <s...@mariadb.org>
> wrote:
> >
> > > First. I believe you'll need to do your final evaluation soon, and
> > > it will need to have a link to the code. Did you check google
> > > guidelines about it? Is everything clear there? Do you need help
> > > publishing your work in a format that google requires?
> > >
> > > They don't accept delays for any reasons, so even if your code is
> > > not 100% complete and ready, you'd better still publish it and
> > > submit the evaluation, because otherwise google will fail you and
> > > that'd be too sad.
> > >
> > > If you'd like you can publish the google-way only the
> > > unique-constraint part without further optimizer work. Or at least
> > > please mention that you'd completed the original project and went
> > > working on extensions.  I mean, it's better than saying "the code is
> > > not 100% complete" :)
> > >
> > Okay I am thinking of writing a blog post with a link to my github
> > repository.
> > Blog Link <http://sachin1001gsoc.blogspot.in/2016/08/gsoc-2016.html>
> > Please check this.
>
> I think that'll do, yes.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hello Sergei!
I am stuck at one problem
consider table t1(a blob , b blob , unique(a,b));
although select * from t1 where a= 12 and b= 23 works
but consider the case like select * from t1 where ( a= 12  or a=45 ) and
(b= 23 or b=45 ) does not works
and also update and delete using long index does not work
simple query like
delete/ update table t1 where a=1 and b=3;
does not works

The reason is that because these query uses test_quick_select function
which does not recognize hash index basically in get_mm_parts it always
return false because it compare db_row_hash_1 to a and b i solved this
problem
but now the problems
1. first how to assure we have both column a, and b is in where currently i
do this as crawl through tree->key[i]->
next_key_part untill next_key_part is null this works for simple case like
a= 1 and b=2 but i am not sure how will i do this in
conditions like
((a =  1 or a =34) and (b=34 or b=33)) or (b=34)
^^^
in this condition before or past it is okay to use hash but for b=34 we
should not use hash index but do not know how to do

2. when SEL_TREE is evaluated ?

3. where should i evaluate hash i can do that but main problem is that i
need to preserve original data  like a=1 and b=3
etc  because hash does not guaranty same.

4 there is one more problem  in test_quick_select

there is one code

if ((range_trp= get_key_scans_params(, tree, FALSE, TRUE,

   best_read_time)))

in the case of hash key before reaching this function we must have to
calculate hash after this it will work
but i guess upto this point our tree is not evaluated , i do not know

 please review branch
https://github.com/SachinSetiya/server/tree/unique_index_where_up_de  and
tell me what should
i do and also review the orignal branch  unique_index_sachin  i added more
test cases and reinsert also works

On Sun, Aug 14, 2016 at 1:46 PM, Sergei Golubchik  wrote:
>
> Hi, Sachin!
>
> I'm reviewing.
> Here I only comment on your reply to the previous review.
>
> > >> +longlong  Item_func_hash::val_int()
> > >> +{
> > >> +  unsigned_flag= true;
> > >> +  ulong nr1= 1,nr2= 4;
> > >> +  CHARSET_INFO *cs;
> > >> +  for(uint i= 0;i > >> +  {
> > >> +String * str = args[i]->val_str();
> > >> +if(args[i]->null_value)
> > >> +{
> > >> +  null_value= 1;
> > >> +  return 0;
> > >> +}
> > >> +cs= str->charset();
> > >> +uchar l[4];
> > >> +int4store(l,str->length());
> > >> +cs->coll->hash_sort(cs,l,sizeof(l), , );
> > > looks good, but use my_charset_binary for the length.
> > did not get it :(
>
> You use
>
>   cs->coll->hash_sort(cs,l,sizeof(l), , );
>
> to sort the length, the byte value of str->length().
> This is binary data, you should have
>
>   cs= my_charset_binary;
i do not find any variable name my_charset_binary but i used

my_charset_utf8_bin is it ok ?

>
> but you have
>
>   cs= str->charset();
>
> for example, it can be latin1_general_ci, and then 'a' and 'A' will be
> considered equal. But 'a' is the string length 97, while 'A' is string
> length 65. String lengths are binary data, they do not consist of
> "letters" or "characters", so you need to use binary charset for
> lengths, but str->charset() for actual string data.
>
> > >> +cs->coll->hash_sort(cs, (uchar *)str->ptr(), str->length(),
, );
>
> ^^^ here cs= str->charset() is correct.
>
> > >> +  }
> > >> +  return   (longlong)nr1;
> > >> +}
> > >> +
> > >> +
> > >> diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
> > >> index e614692..4872d20 100644
> > >> --- a/sql/sql_yacc.yy
> > >> +++ b/sql/sql_yacc.yy
> > >>   | COMMENT_SYM TEXT_STRING_sys { Lex->last_field->comment=
$2; }
> > >> +| HIDDEN
> > >> +  {
> > >> +  LEX *lex =Lex;
> > > no need to do that ^^^ you can simply write
Lex->last_field->field_visibility=...
> > change but in sql_yacc.yy this type of code is use everywhere
>
> Yes :) Many years ago Lex was defined to be current_thd->lex and on some
> platforms current_thd (which is pthread_getspecific) is expensive, so we
> were trying to avoid using it when possible. That's why Lex was saved in
> a local variable.
>
> But now (also, for many years) Lex is YYTHD->Lex, where YYTHD is the
> argument of the MYSQLyyparse function, not pthread_getspecific.
> So there is no need to avoid Lex anymore. Nobody bothered to fix the old
> code, though.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hello Sergei!

Sir I am stuck at one problem.
Consider the case
create table t1 (abc blob unique, xyz int unique);
/* Insert data */
update t1 set abc = 33 where xyz =23;
This is not working because xyz = 23 will require index scan and at the time of
ha_update_row the inited != NONE and if inited != NONE then
ha_index_read_idx_map would fail so how can I update row in case of
blob unique ? In this case I think the only option left is sequential scan
but that will defeat the purpose of blob unique.
Regards
sachin

On Thu, Jul 28, 2016 at 1:10 PM, Sergei Golubchik  wrote:
> Hi, Sachin!
>
> On Jul 27, Sergei Golubchik wrote:
>> >
>> > Please review branch
>> > https://github.com/SachinSetiya/server/tree/unique_index_where
>>
>> Sure. Will do.
>
> Sorry. Correction :(
>
> I'm on vacations two weeks in August, 9th to 22nd. And I have an awful
> lot to do for 10.2. Naturally I won't work on 10.2 on vacations, but I
> will take my laptop with me and I will answer your questions and review
> your code. Which also means that I'd better use every minute before
> vacations to work on 10.2, you see...
>
> So, I'll review you patch in a couple of weeks.
> Until then I can only answer questions, sorry :(
> And when on vacations, mind it, I will be only rarely on irc, so if I'm
> not there, do not wait for me, send an email, please. I will reply to
> your emails and I will do my reviews.
>
> By the way, did you do a full mysql-test run? Like this:
>
>   ./mtr --force --parallel 5
>
> In fact, what I do is:
>
>   cmake -DCMAKE_BUILD_TYPE=Debug -DWITH_EMBEDDED_SERVER=ON
>   make -j5
>   cd mysql-test-run
>   script -c './mtr --force --parallel 5; ./mtr --force --parallel 5 --ps; 
> ./mtr --force --parallel 3 --embed'
>
> and that last command takes 4-5 hours on my laptop. I don't do that very
> often :) but it's throurough. You can run it overnight. Because of
> 'script' the complete log will be in the typescript file, so there's no
> need to monitor manually anything.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hello Sergei!
Weekly Report for 9 week of gsoc

Unique Long

1. Changed mysql_prepare_create function as suggested by you , now addition
of hash column will not be added in function start.
2. Sorted out problem of full_hidden detection now it is detected as soon
as it is found.

Where Optimization
1. In case of unique(a) if hash collides then it fetches the next record
and compares it and so on.
2. Now unique(a,b,c ..) also works and also in case of hash collusion  it
fetches the next record and compares it and so on.

Please review branch
https://github.com/SachinSetiya/server/tree/unique_index_where
The only problem i have is explain query fails , trying to solve it let you
know if something happens.
Regards
sachin

On Mon, Jul 25, 2016 at 1:34 AM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> Actually  i find that my_strnncoll  wil work :)
> Regards
> sachin
>
> On Mon, Jul 25, 2016 at 1:17 AM, Sachin Setia <sachinsetia1...@gmail.com>
> wrote:
>
>> Hello Sergei,
>> I am getting one problem related to my_strcasecmp() function currently
>> this function does not allow
>> string comparison upto length l, is there any functon which can do
>> comparison upto length l, or should i
>> write mine.
>> Regards
>> sachin
>>
>> On Fri, Jul 22, 2016 at 9:56 PM, Sachin Setia <sachinsetia1...@gmail.com>
>> wrote:
>>
>>> Hello Sergei,
>>> I have one problem my where optimization works for  query like
>>> select * from t1 where abc=1;
>>>
>>> but in query like
>>> select * from t1 where abc=(select xyz from t2 where xyz=1);
>>> does not work because in these query the charset is different from what
>>> used in
>>> t1 for inserting data and hence generation different hash for same data
>>> how i solve this problem.
>>> Regards
>>> sachin
>>>
>>> On Tue, Jul 19, 2016 at 5:52 PM, Sachin Setia <sachinsetia1...@gmail.com
>>> > wrote:
>>>
>>>> Just give me 3 days after you can review. yes I already merged with
>>>> 10.2.1 days ago.
>>>> regards
>>>> Sachin
>>>>
>>>> On Jul 19, 2016 17:28, "Sergei Golubchik" <s...@mariadb.org> wrote:
>>>>
>>>>> Hi, Sachin!
>>>>>
>>>>> On Jul 19, Sachin Setia wrote:
>>>>> > Weekly Report for 8 week of gsoc
>>>>> >
>>>>> > 1 Changed the key flags as suggested by you.
>>>>>
>>>>> okay
>>>>>
>>>>> > 2 Now update will use only one buffer as suggested by you but there
>>>>> was one
>>>>> > problem some time offset can be
>>>>> > negative so i changed the field cmp_offset parameter from uint to
>>>>> long
>>>>>
>>>>> sure
>>>>>
>>>>> > 3 Still working on coding conventions.
>>>>> >
>>>>> > 4 I have made prototype for optimizing where for keys like unique(a)
>>>>> , it
>>>>> > is on branch
>>>>> > https://github.com/SachinSetiya/server/tree/unique_index_where
>>>>> >
>>>>> > Currently I am working on muliple keys like unique(a,b,c) i think
>>>>> this
>>>>> > should take 2-3 days  and edits suggested by you.
>>>>>
>>>>> sounds good.
>>>>> did you merge with 10.2.1?
>>>>>
>>>>> tell me when you'd want me to do another review.
>>>>>
>>>>> Regards,
>>>>> Sergei
>>>>> Chief Architect MariaDB
>>>>> and secur...@mariadb.org
>>>>>
>>>>
>>>
>>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Actually  i find that my_strnncoll  wil work :)
Regards
sachin

On Mon, Jul 25, 2016 at 1:17 AM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> Hello Sergei,
> I am getting one problem related to my_strcasecmp() function currently
> this function does not allow
> string comparison upto length l, is there any functon which can do
> comparison upto length l, or should i
> write mine.
> Regards
> sachin
>
> On Fri, Jul 22, 2016 at 9:56 PM, Sachin Setia <sachinsetia1...@gmail.com>
> wrote:
>
>> Hello Sergei,
>> I have one problem my where optimization works for  query like
>> select * from t1 where abc=1;
>>
>> but in query like
>> select * from t1 where abc=(select xyz from t2 where xyz=1);
>> does not work because in these query the charset is different from what
>> used in
>> t1 for inserting data and hence generation different hash for same data
>> how i solve this problem.
>> Regards
>> sachin
>>
>> On Tue, Jul 19, 2016 at 5:52 PM, Sachin Setia <sachinsetia1...@gmail.com>
>> wrote:
>>
>>> Just give me 3 days after you can review. yes I already merged with
>>> 10.2.1 days ago.
>>> regards
>>> Sachin
>>>
>>> On Jul 19, 2016 17:28, "Sergei Golubchik" <s...@mariadb.org> wrote:
>>>
>>>> Hi, Sachin!
>>>>
>>>> On Jul 19, Sachin Setia wrote:
>>>> > Weekly Report for 8 week of gsoc
>>>> >
>>>> > 1 Changed the key flags as suggested by you.
>>>>
>>>> okay
>>>>
>>>> > 2 Now update will use only one buffer as suggested by you but there
>>>> was one
>>>> > problem some time offset can be
>>>> > negative so i changed the field cmp_offset parameter from uint to long
>>>>
>>>> sure
>>>>
>>>> > 3 Still working on coding conventions.
>>>> >
>>>> > 4 I have made prototype for optimizing where for keys like unique(a)
>>>> , it
>>>> > is on branch
>>>> > https://github.com/SachinSetiya/server/tree/unique_index_where
>>>> >
>>>> > Currently I am working on muliple keys like unique(a,b,c) i think this
>>>> > should take 2-3 days  and edits suggested by you.
>>>>
>>>> sounds good.
>>>> did you merge with 10.2.1?
>>>>
>>>> tell me when you'd want me to do another review.
>>>>
>>>> Regards,
>>>> Sergei
>>>> Chief Architect MariaDB
>>>> and secur...@mariadb.org
>>>>
>>>
>>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hello Sergei,
I am getting one problem related to my_strcasecmp() function currently this
function does not allow
string comparison upto length l, is there any functon which can do
comparison upto length l, or should i
write mine.
Regards
sachin

On Fri, Jul 22, 2016 at 9:56 PM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> Hello Sergei,
> I have one problem my where optimization works for  query like
> select * from t1 where abc=1;
>
> but in query like
> select * from t1 where abc=(select xyz from t2 where xyz=1);
> does not work because in these query the charset is different from what
> used in
> t1 for inserting data and hence generation different hash for same data
> how i solve this problem.
> Regards
> sachin
>
> On Tue, Jul 19, 2016 at 5:52 PM, Sachin Setia <sachinsetia1...@gmail.com>
> wrote:
>
>> Just give me 3 days after you can review. yes I already merged with
>> 10.2.1 days ago.
>> regards
>> Sachin
>>
>> On Jul 19, 2016 17:28, "Sergei Golubchik" <s...@mariadb.org> wrote:
>>
>>> Hi, Sachin!
>>>
>>> On Jul 19, Sachin Setia wrote:
>>> > Weekly Report for 8 week of gsoc
>>> >
>>> > 1 Changed the key flags as suggested by you.
>>>
>>> okay
>>>
>>> > 2 Now update will use only one buffer as suggested by you but there
>>> was one
>>> > problem some time offset can be
>>> > negative so i changed the field cmp_offset parameter from uint to long
>>>
>>> sure
>>>
>>> > 3 Still working on coding conventions.
>>> >
>>> > 4 I have made prototype for optimizing where for keys like unique(a) ,
>>> it
>>> > is on branch
>>> > https://github.com/SachinSetiya/server/tree/unique_index_where
>>> >
>>> > Currently I am working on muliple keys like unique(a,b,c) i think this
>>> > should take 2-3 days  and edits suggested by you.
>>>
>>> sounds good.
>>> did you merge with 10.2.1?
>>>
>>> tell me when you'd want me to do another review.
>>>
>>> Regards,
>>> Sergei
>>> Chief Architect MariaDB
>>> and secur...@mariadb.org
>>>
>>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hello Sergei,
I have one problem my where optimization works for  query like
select * from t1 where abc=1;

but in query like
select * from t1 where abc=(select xyz from t2 where xyz=1);
does not work because in these query the charset is different from what
used in
t1 for inserting data and hence generation different hash for same data
how i solve this problem.
Regards
sachin

On Tue, Jul 19, 2016 at 5:52 PM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> Just give me 3 days after you can review. yes I already merged with 10.2.1
> days ago.
> regards
> Sachin
>
> On Jul 19, 2016 17:28, "Sergei Golubchik" <s...@mariadb.org> wrote:
>
>> Hi, Sachin!
>>
>> On Jul 19, Sachin Setia wrote:
>> > Weekly Report for 8 week of gsoc
>> >
>> > 1 Changed the key flags as suggested by you.
>>
>> okay
>>
>> > 2 Now update will use only one buffer as suggested by you but there was
>> one
>> > problem some time offset can be
>> > negative so i changed the field cmp_offset parameter from uint to long
>>
>> sure
>>
>> > 3 Still working on coding conventions.
>> >
>> > 4 I have made prototype for optimizing where for keys like unique(a) ,
>> it
>> > is on branch
>> > https://github.com/SachinSetiya/server/tree/unique_index_where
>> >
>> > Currently I am working on muliple keys like unique(a,b,c) i think this
>> > should take 2-3 days  and edits suggested by you.
>>
>> sounds good.
>> did you merge with 10.2.1?
>>
>> tell me when you'd want me to do another review.
>>
>> Regards,
>> Sergei
>> Chief Architect MariaDB
>> and secur...@mariadb.org
>>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Just give me 3 days after you can review. yes I already merged with 10.2.1
days ago.
regards
Sachin

On Jul 19, 2016 17:28, "Sergei Golubchik" <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> On Jul 19, Sachin Setia wrote:
> > Weekly Report for 8 week of gsoc
> >
> > 1 Changed the key flags as suggested by you.
>
> okay
>
> > 2 Now update will use only one buffer as suggested by you but there was
> one
> > problem some time offset can be
> > negative so i changed the field cmp_offset parameter from uint to long
>
> sure
>
> > 3 Still working on coding conventions.
> >
> > 4 I have made prototype for optimizing where for keys like unique(a) , it
> > is on branch
> > https://github.com/SachinSetiya/server/tree/unique_index_where
> >
> > Currently I am working on muliple keys like unique(a,b,c) i think this
> > should take 2-3 days  and edits suggested by you.
>
> sounds good.
> did you merge with 10.2.1?
>
> tell me when you'd want me to do another review.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Weekly Report for 8 week of gsoc

1 Changed the key flags as suggested by you.
2 Now update will use only one buffer as suggested by you but there was one
problem some time offset can be
negative so i changed the field cmp_offset parameter from uint to long
3 Still working on coding conventions.

4 I have made prototype for optimizing where for keys like unique(a) , it
is on branch
https://github.com/SachinSetiya/server/tree/unique_index_where

Currently I am working on muliple keys like unique(a,b,c) i think this
should take 2-3 days  and edits suggested by you.
Regards
sachin

On Fri, Jul 15, 2016 at 5:29 PM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> okay sir, i will just store the is_hash flag and on
> init_from_binary_frm_image
> add flag to key. thanks
> Regards
> sachin
>
> On Fri, Jul 15, 2016 at 3:58 PM, Sergei Golubchik <s...@mariadb.org>
> wrote:
> > Hi, Sachin!
> >
> > On Jul 14, Sachin Setia wrote:
> >> Hello Sergei
> >> Actually i have one doubt  there is two options
> >> 1 add is_hash flag to field
> >> 2 add ha_unique_hash flag to field ,but i think all 16 bits of key flag
> is used
> >> so to do this i need to add another ex_flag variable in key struct and
> >> store it and retrieve it from frm
> >> currently i have done both ,but as you pointed out in review that  it
> >> is better to have it in key only but
> >> my question is that, whether is this approach is right ?
> >
> > From the storage point of view, it's much easier to put a special flag
> > on the field. You can store it in EXTRA2 or you can store it in the
> > Field::unireg_check enum.
> >
> > And then you recognize your HA_UNIQUE_HASH keys by having is_hash
> > property on the field of the first keypart. You can even set the
> > HA_UNIQUE_HASH flag in the key, if HA_UNIQUE_HASH=65536, for example.
> > That is, it won't be stored in the frm, and you set the flag in
> > init_from_binary_frm_image - the flag can be used to simplify run-time
> > checks, but it won't be stored on disk.
> >
> > Regards,
> > Sergei
> > Chief Architect MariaDB
> > and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

okay sir, i will just store the is_hash flag and on init_from_binary_frm_image
add flag to key. thanks
Regards
sachin

On Fri, Jul 15, 2016 at 3:58 PM, Sergei Golubchik <s...@mariadb.org> wrote:
> Hi, Sachin!
>
> On Jul 14, Sachin Setia wrote:
>> Hello Sergei
>> Actually i have one doubt  there is two options
>> 1 add is_hash flag to field
>> 2 add ha_unique_hash flag to field ,but i think all 16 bits of key flag is 
>> used
>> so to do this i need to add another ex_flag variable in key struct and
>> store it and retrieve it from frm
>> currently i have done both ,but as you pointed out in review that  it
>> is better to have it in key only but
>> my question is that, whether is this approach is right ?
>
> From the storage point of view, it's much easier to put a special flag
> on the field. You can store it in EXTRA2 or you can store it in the
> Field::unireg_check enum.
>
> And then you recognize your HA_UNIQUE_HASH keys by having is_hash
> property on the field of the first keypart. You can even set the
> HA_UNIQUE_HASH flag in the key, if HA_UNIQUE_HASH=65536, for example.
> That is, it won't be stored in the frm, and you set the flag in
> init_from_binary_frm_image - the flag can be used to simplify run-time
> checks, but it won't be stored on disk.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hello Sergei
Actually i have one doubt  there is two options
1 add is_hash flag to field
2 add ha_unique_hash flag to field ,but i think all 16 bits of key flag is used
so to do this i need to add another ex_flag variable in key struct and
store it and retrieve it from frm
currently i have done both ,but as you pointed out in review that  it
is better to have it in key only but
my question is that , whether is this approach is right ?
Regards
sachin

On Tue, Jul 12, 2016 at 2:43 PM, Sachin Setia <sachinsetia1...@gmail.com> wrote:
> Hello everyone,
>
> Weekly Report for 7th week of gsoc
>
> 1Now we can alter blob columns query like
> create table t1(abc blob unique);
> alter table t1 change column abc a blob;
>   Even we can some multiple changes in one alter
> create table t1(abc blob unique, xyz blob unique);
> alter table t1 change column abc a blob , change xyz x blob;
>   Works.
>
> 2.Now we can delete blob columns if only one blob unique in
>   key then db_row_hash_ will be removed other wise hash_str
>   will be modified.If the query fails then there will be no
>   side effect
>
> 3.chaning of delete operations
>   create table t1(abc blob , xyz blob , pqr blob, unique(abc,xyz,pqr));
>   alter table t1 drop column abc, drop column xyz;
> 4. we will get right error message instead of duplicate hash
> 5. these was an glich in code when we try to add db_row_hash_ column to
> table with first key work mysql_prepare_alter_table select this
> instead of real hash
> now solved.
> 6. Added some test case.Will add more soon.
> @Sergei will reply to you soon.
> Regards
> sachin

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hello everyone,

Weekly Report for 7th week of gsoc

1Now we can alter blob columns query like
create table t1(abc blob unique);
alter table t1 change column abc a blob;
  Even we can some multiple changes in one alter
create table t1(abc blob unique, xyz blob unique);
alter table t1 change column abc a blob , change xyz x blob;
  Works.

2.Now we can delete blob columns if only one blob unique in
  key then db_row_hash_ will be removed other wise hash_str
  will be modified.If the query fails then there will be no
  side effect

3.chaning of delete operations
  create table t1(abc blob , xyz blob , pqr blob, unique(abc,xyz,pqr));
  alter table t1 drop column abc, drop column xyz;
4. we will get right error message instead of duplicate hash
5. these was an glich in code when we try to add db_row_hash_ column to
table with first key work mysql_prepare_alter_table select this
instead of real hash
now solved.
6. Added some test case.Will add more soon.
@Sergei will reply to you soon.
Regards
sachin

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hello everyone,

Weekly Report for 6th week of gsoc
1.hidden fields fully supported , they also show in extra column
2.suppose if we declare key as unique(abc,df) then instead of key name
to be abc it will be abc_df
3.prototype and prototype for how to store information about hash and
type of hash in field and KEY
finally decided to add bool flag (is_row_hash) in Field and added a
new long flag in KEY named ex_flags
it is stored and retried from extra2 region
4. studying optimizer code for  where condition on unique key.
5. Now it will give error for these statements
   create table t1(abc blob unique,index(db_row_hash_1));
   alter table t2 add column abc blob unique,add index(db_row_hash_1);
6.now we can delete unique blob columns.Db_row_hash will be
automatically removed
currently working on case like
create table t1(a blob,b blob ,unique(a,b));
alter table t1 drop column a;
this works after alter but records stored in table can be duplicate
which voids the
unique key rule.
Regards
sachin

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] HASH and unique constraint for blobs

[Sachin Setia]

Hello Sergei,
Sir actually I  added flags for unique_hash and index_hash for KEY these are

#define HA_INDEX_HASH  4

#define HA_UNIQUE_HASH 8

but ./mtr inoodb test are failing bacause KEY flag is 40 which is
HA_BINARY_PACK_KEY + HA_UNIQUE_HASH but HA_UNIQUE_HASH should not be
there . Is 4 and 8 are already used ,or should i go for another
approach. Sir please review
the code.
Regards
sachin

On Mon, Jun 6, 2016 at 5:00 PM, Sergei Golubchik <s...@mariadb.org> wrote:
> Hi, Sachin!
>
> On Jun 06, Sachin Setia wrote:
>> Hello Sir
>> Weekly Report for second week of gsoc
>> 1. Implemented unique in the case of null
>> 2. Implemented unique(A,b,c)
>>
>> Currently working on
>> tests , field hiding
>
> Good, thanks!
>
> I'd suggest to postpone field hiding until bb-10.2-default
> branch is merged into 10.2.
>
> Start with tests, it's very important to have them.
> See mysql-test/t/*.test and the manual is here:
> https://mariadb.com/kb/en/mariadb/mysql-test/
> Don't forget direct tests of your HASH function.
>
> Then, may be, look at UPDATE.
>
> And here's a code review up to the commit b69e141
>
>> diff --git a/sql/handler.cc b/sql/handler.cc
>> index fb2921f..e68e37b 100644
>> --- a/sql/handler.cc
>> +++ b/sql/handler.cc
>> @@ -5863,16 +5863,86 @@ int handler::ha_reset()
>>DBUG_RETURN(reset());
>>  }
>>
>> +/**  @breif
>
> typo: brief
>
>> +   Compare two records
>> +   Need a better place for this function
>> +   @returns true if equal else false*/
>> +my_bool rec_hash_cmp(TABLE *tbl ,Field * hash_field)
>
> in C++ you can use 'bool'. 'my_bool' is only for C
>
> and, please, pay attention to where you put spaces :)
>
>> +{
>> +  Item * t_item;
>> +  t_item=hash_field->vcol_info->expr_item->next;
>
> No, please don't rely on the 'next' pointer. It used to link
> all items into a list, so that they could be destroyed all at once.
> It's not how you traverse expression arguments.
>
> Use, for example,
>
>Item **arg,**arg_end;
>for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
>{
>  Item t_item= *arg;
>
> or
>  for (uint i= 0; i < arg_count; i++)
>  {
>Item *t_item= args[i];
>
> See these and other examples in item_func.cc
>
>> +  int diff = tbl->record[1]-tbl->record[0];
>
> record[1] - record[0] is always table->s->rec_buff_length
> But there's one problem with that - you cannot use record[1],
> see a comment below.
>
>> +  Field * t_field;
>> +  int first_length,second_length;
>> +  while(t_item)
>> +  {
>> +for(int i=0;is->fields;i++)
>> +{
>> +  t_field=tbl->field[i];
>> +  if(!my_strcasecmp(system_charset_info,
>> +   t_item->name,t_field->field_name))
>> +break;
>> +}
>
> your t_item is an Item_field, so your t_field
> will be t_item->field, no need to iterate anything.
>
>> +/* First check for nulls */
>> +if(t_field->is_real_null()||t_field->is_real_null(diff))
>> +{
>> +  return false;
>> +}
>
> This shouldn't be necessary, see below, you shouldn't call this function
> when a field value is NULL.
>
>>
>> +/*
>> +   length of field is not equal to pack length when it is
>> +   subclass of field_blob and field _varsting
>> + */
>> +first_length = t_field->data_length();
>> +second_length = t_field->data_length(diff);
>> +if(first_length!=second_length)
>> +  return false;
>> +if(t_field->cmp_max(t_field->ptr,t_field->ptr+diff,first_length))
>> +  return false;
>> +t_item=t_item->next;
>> +  }
>> +  return true;
>> +}
>>  int handler::ha_write_row(uchar *buf)
>>  {
>> -  int error;
>> +  int error,map;
>>Log_func *log_func= Write_rows_log_event::binlog_row_logging_function;
>> +  Field **field_iter;
>>DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE ||
>>m_lock_type == F_WRLCK);
>>DBUG_ENTER("handler::ha_write_row");
>>DEBUG_SYNC_C("ha_write_row_start");
>> +  /* First need to whether inserted record is unique or not */
>>
>> +  /* One More Thing  if i implement hidden field then detection can be easy 
>> */
>> +  field_iter=table->field;
>> +  for(uint i=0;is->fields;i++)
>
> You can iterate only virtual fields, there's a separate list for them.
>
>> +  {
>> +if(strncmp((*field_iter)->field_name,"DB_ROW_HASH_&q

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hello everyone,

Weekly Report for 5th week of gsoc

1. select worked without showing db_row_hash
2. db_row_hash column  is put in first place rather then last
3. show create table will not show db_row_hash_*
4. select will not show hidden level  3
5. user defined hidden field worked.
6  we can also create definition like create table t1(abc int blob
unique, db_row_hash_1 int unique); //this
will automatically create column db_row_hash_2 for storing hash
7. db_row_hash_1 are like invisible to alter command if we add column
db_row_hash_1 using alter.  internal hidden column db_row_hash_1 will
automatically rename to db_row_hash_2.
8 we can also add unique constraint to blob in alter command and it
can also be deleted.
9 show create table will not show db_row_hash_* , and if it find hash
column it will show like unique(column names)

Regards
sachin


On Mon, Jun 20, 2016 at 11:50 AM, Sachin Setia
<sachinsetia1...@gmail.com> wrote:
> Hi Sergei,
>
> Weekly Report for 4th week of gsoc
>
> 1. Field property is_row_hash , field_visibility successfully saved and
> retrived from frm , using extra2 space
> 2. Some tests added.
> 3. Solved the error when there is another primary key(it used to accept
> duplicate in this case ).
> 4. Added hidden in parser.
> 5. Identified the memory leak 1 is because of malloc db_row_hash str.I did
> not freed it. second memory leak i am searching for it.
> Work for this week.
> 1 First solve the memory leak problem.
> 2 Work on FULL_HIDDEN_FIELDS.
> 3 in mysql_prepare_create_table I am using an iterator it would be better if
> i can add custom field when it says an error. So will not have to use
> iterator as suggested by you sir.
> 4 rename the hash field automatically in the case clash.
> This week
> On Thu, Jun 16, 2016 at 11:46 PM, Sergei Golubchik <s...@mariadb.org> wrote:
>>
>> Hi, Sachin!
>>
>> On Jun 15, Sachin Setia wrote:
>> >
>> > But the major problem is:-
>> >  Consider this case
>> >
>> > create table tbl(abc int primary key,xyz blob unique);
>> >
>> > In this case , second key_info will have one user_defined_key_parts but
>> > two
>> > ext_key_parts
>> > second key_part refers to primary key.
>> > because of this ha_index_read_idx_map always return HA_ERR_KEY_NOT_FOUND
>> > I am trying to solve this problem.
>>
>> I've seen you solved this, but I do not understand the problem (and so I
>> cannot understand the fix either).
>
>
> Problem was
> consider this
> create table tbl(abc int primary key , xyz blob unique);
> insert into tbl value(1,12);
> insert into tbl value(2,12); # no error , details in commit comment
> https://github.com/MariaDB/server/commit/baecc73380084c61b9323a30f3e25977176e98b0
>>
>> Please, try to add a test case for
>> the problem you're fixing. In the same commit, preferrably.
>>
>> Now you can still commit a test case for this problem and your fix,
>> then, I hope, I'll be able to understand better what the problem was.
>>
>> Regards,
>> Sergei
>> Chief Architect MariaDB
>> and secur...@mariadb.org
>
>

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Sachin weekly report

[Sachin Setia]

Hi Sergei,

Weekly Report for 4th week of gsoc

1. Field property is_row_hash , field_visibility successfully saved and
retrived from frm , using extra2 space
2. Some tests added.
3. Solved the error when there is another primary key(it used to accept
duplicate in this case ).
4. Added hidden in parser.
5. Identified the memory leak 1 is because of malloc db_row_hash str.I did
not freed it. second memory leak i am searching for it.
Work for this week.
1 First solve the memory leak problem.
2 Work on FULL_HIDDEN_FIELDS.
3 in mysql_prepare_create_table I am using an iterator it would be better
if i can add custom field when it says an error. So will not have to use
iterator as suggested by you sir.
4 rename the hash field automatically in the case clash.
This week
On Thu, Jun 16, 2016 at 11:46 PM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> On Jun 15, Sachin Setia wrote:
> >
> > But the major problem is:-
> >  Consider this case
> >
> > create table tbl(abc int primary key,xyz blob unique);
> >
> > In this case , second key_info will have one user_defined_key_parts but
> two
> > ext_key_parts
> > second key_part refers to primary key.
> > because of this ha_index_read_idx_map always return HA_ERR_KEY_NOT_FOUND
> > I am trying to solve this problem.
>
> I've seen you solved this, but I do not understand the problem (and so I
> cannot understand the fix either).


Problem was
consider this
create table tbl(abc int primary key , xyz blob unique);
insert into tbl value(1,12);
insert into tbl value(2,12); # no error , details in commit comment
https://github.com/MariaDB/server/commit/baecc73380084c61b9323a30f3e25977176e98b0

> Please, try to add a test case for
> the problem you're fixing. In the same commit, preferrably.
>
Now you can still commit a test case for this problem and your fix,
> then, I hope, I'll be able to understand better what the problem was.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] MATCH returns -1

[Sachin Setia]

Hello Sergei
How can we solve this bug?
Regards
sachin

On Wed, Jun 8, 2016 at 1:28 PM, Sergei Golubchik  wrote:

> Hi, Alexander!
>
> On Jun 08, Alexander Barkov wrote:
> >
> > This script:
> >
> > DROP TABLE IF EXISTS t1;
> > CREATE TABLE t1 (a VARCHAR(30), b DOUBLE);
> > INSERT INTO t1 values('   ', MATCH (a) AGAINST('' IN
> BOOLEAN MODE));
> > SELECT * FROM t1;
> >
> > returns:
> >
> > +-+--+
> > | a   | b|
> > +-+--+
> > |     |   -1 |
> > +-+--+
> >
> > What does this -1 mean?
>
>   double Item_func_match::val_real()
>   {
> DBUG_ENTER("Item_func_match::val");
> if (ft_handler == NULL)
>   DBUG_RETURN(-1.0);
>
> > Is this a bug?
>
> I believe so!
>
> Regards,
> Sergei
>
> ___
> Mailing list: https://launchpad.net/~maria-developers
> Post to : maria-developers@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] InnoDB blob for primary key

[Sachin Setia]

yes sir
https://github.com/SachinSetiya/server
regards
sachin

On Mon, Jun 6, 2016 at 12:39 PM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> yes sir
> https://github.com/SachinSetiya/server
> regards
> sachin
>
> On Mon, Jun 6, 2016 at 12:31 PM, Oleksandr Byelkin <sa...@montyprogram.com
> > wrote:
>
>> Are there there in github?
>> Am 06.06.2016 07:57 schrieb Sachin Setia <sachinsetia1...@gmail.com>:
>>
>> Hello Sir
>> Weekly Report for second week of gsoc
>> 1. Implemented unique in the case of null
>> 2. Implemented unique(A,b,c)
>>
>> Currently working on
>> tests , field hiding
>>
>> Regards
>> sachin
>>
>> On Tue, May 31, 2016 at 10:49 PM, Sergei Golubchik <s...@mariadb.org>
>> wrote:
>>
>> Hi, Sachin!
>>
>> On May 31, Sachin Setia wrote:
>> > Hello Sir
>> > Weekly Report For Gsoc
>> > 1. Implemented hash function in parser for one column
>> > 2. Implemenetd hash function for unlimited columns in parser
>> > 3. Implemented unique checking in case of hash collusion(it retrieves
>> the
>> > row and compare data) but this only woks for unique blob column
>> >
>> > Currently Working on
>> > unique checking in case of unique(a,b,c)
>>
>> Thanks. This is ok.
>>
>> Next time, please, send it to maria-developers list, not to me only :)
>>
>> Do you have questions? Any code you want me to look at?
>>
>> Regards,
>> Sergei
>> Chief Architect MariaDB
>> and secur...@mariadb.org
>>
>>
>>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] InnoDB blob for primary key

[Sachin Setia]

Hello Sir
Weekly Report for second week of gsoc
1. Implemented unique in the case of null
2. Implemented unique(A,b,c)

Currently working on
tests , field hiding

Regards
sachin

On Tue, May 31, 2016 at 10:49 PM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> On May 31, Sachin Setia wrote:
> > Hello Sir
> > Weekly Report For Gsoc
> > 1. Implemented hash function in parser for one column
> > 2. Implemenetd hash function for unlimited columns in parser
> > 3. Implemented unique checking in case of hash collusion(it retrieves the
> > row and compare data) but this only woks for unique blob column
> >
> > Currently Working on
> > unique checking in case of unique(a,b,c)
>
> Thanks. This is ok.
>
> Next time, please, send it to maria-developers list, not to me only :)
>
> Do you have questions? Any code you want me to look at?
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] Why Max Length of virtual column string is so small

[Sachin Setia]

Hello Everyone,

Actually I was thinking why there is so small limit on virtual column
string.Does it have any design implication or some other reason

Regards
sachin
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] InnoDB blob for primary key

[Sachin Setia]

Hello Sir!
Today I made some progress related 1st project
MariaDB [a]> create table tbl(abc blob unique);
Query OK, 0 rows affected (0.03 sec)

MariaDB [a]> insert into tbl values('sachin');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [a]> insert into tbl values('setiya');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [a]> insert into tbl values('sachin');
ERROR 1062 (23000): Duplicate entry '1261' for key 'DB_ROW_HASH_1'
MariaDB [a]> desc tbl;
+---++--+-+-++
| Field | Type   | Null | Key | Default | Extra  |
+---++--+-+-++
| abc   | blob   | YES  | | NULL||
| DB_ROW_HASH_1 | int(4) | YES  | UNI | NULL| PERSISTENT |
+---++--+-+-++
2 rows in set (0.00 sec)

MariaDB [a]>
I pushed the change in github.Only one thing is remaining is getting the
value when hash collides and compare them
Regards
sachin

On Mon, May 16, 2016 at 8:32 PM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> Hello Sergei!
> Sir please review the prototype at
> https://github.com/SachinSetiya/server/tree/unique_index_sachin
> this is half code I guess it will be complete by tomorrow
> Regards
> sachin
>
> On Mon, May 2, 2016 at 8:42 PM, Sergei Golubchik <s...@mariadb.org> wrote:
>
>> Hi, Sachin!
>>
>> On May 02, Sachin Setia wrote:
>> > Hi Sergei!
>> >
>> > As i told you i was prototyping for hash table
>> > It is done around 90% apart from one thing when hash is same
>> > how to get record from .myd file when i have offset of record
>> > so currently i am skipping it
>> > But it is very fast i do not know why this is so fast here are
>> > results of employee database
>> > salary table definition
>> > CREATE TABLE salaries (
>> > emp_no  INT NOT NULL,
>> > salary  blob NOT NULL,
>> > from_date   DATENOT NULL,
>> > to_date DATENOT NULL,
>> > FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE
>> CASCADE,
>> > PRIMARY KEY (emp_no, from_date)
>> > )
>>
>> I presume, you had ENGINE=Aria here? Because your code patch is for Aria.
>>
>> > ;
>> > And query is
>> > MariaDB [employees]> select distinct salary from salaries;
>> >
>> > Result with out using hash table
>> >
>> > ++
>> > 85814 rows in set (2 min 24.76 sec)
>> >
>> >
>> > Result with using hash table
>> >
>> > | 39420  |
>> > ++
>> > 85809 rows in set (6.24 sec)
>> >
>> > ( number of rows are not equal but this can be solved if i get record by
>> > offset)
>> >
>> > I am sure there is something wrong.The whole hash table is in memory
>> > like wise the b tree of hash is in memory but why there is so much
>> > improvement. Please sir check the prototype and tell if i am wrong
>> > .thanks
>>
>> Sure.
>>
>> But still, please put your code on github and commit often.
>> Let's use a proper development process instead of sending patches back
>> and forth. If you need help with that, feel free to ping me on irc.
>>
>> And using // comments makes the code more difficult to review - you
>> change every line in a big block. Better use
>>
>> #if 0
>>...
>> #endif
>>
>> > diff --git a/storage/maria/ma_hash_table.h
>> b/storage/maria/ma_hash_table.h
>> > new file mode 100644
>> > index 000..c8e4578
>> > --- /dev/null
>> > +++ b/storage/maria/ma_hash_table.h
>>
>> why are you doing it in Aria? I thought we've agreed to do it on the
>> upper level, in sql/
>>
>> > @@ -0,0 +1,45 @@
>> > +#include"../../mysys/my_malloc.c"
>> > +#include"../../include/my_global.h"
>> > +typedef struct ma_hash_table_element{
>> > + unsigned int hash_code;
>> > + unsigned int  record_offset;
>> > + struct ma_hash_table * next; //we will use single link list
>> because no delete operations
>> > +} ma_hash_table_element;
>>
>> Did you look at reusing the HASH data structure? include/hash.h,
>> mysys/hash.c?
>>
>> > +
>> > +typedef struct ma_hash_table{
>> > + unsigned int size;
>> > + ma_hash_table_element * h_t_e;
>> > +}ma_hash_table;
>>
>> Because of the above (on the wrong level, ma_hash_table instead of HASH,
>> using // comments to disable code) I've only quickly looked through the
>> patch.
>> But I didn't notice anything obviously wrong.
>>
>> Regards,
>> Sergei
>> Chief Architect MariaDB
>> and secur...@mariadb.org
>>
>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] InnoDB blob for primary key

[Sachin Setia]

Hello Sergei!
Sir please review the prototype at
https://github.com/SachinSetiya/server/tree/unique_index_sachin
this is half code I guess it will be complete by tomorrow
Regards
sachin

On Mon, May 2, 2016 at 8:42 PM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> On May 02, Sachin Setia wrote:
> > Hi Sergei!
> >
> > As i told you i was prototyping for hash table
> > It is done around 90% apart from one thing when hash is same
> > how to get record from .myd file when i have offset of record
> > so currently i am skipping it
> > But it is very fast i do not know why this is so fast here are
> > results of employee database
> > salary table definition
> > CREATE TABLE salaries (
> > emp_no  INT NOT NULL,
> > salary  blob NOT NULL,
> > from_date   DATENOT NULL,
> > to_date DATENOT NULL,
> > FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
> > PRIMARY KEY (emp_no, from_date)
> > )
>
> I presume, you had ENGINE=Aria here? Because your code patch is for Aria.
>
> > ;
> > And query is
> > MariaDB [employees]> select distinct salary from salaries;
> >
> > Result with out using hash table
> >
> > ++
> > 85814 rows in set (2 min 24.76 sec)
> >
> >
> > Result with using hash table
> >
> > | 39420  |
> > ++
> > 85809 rows in set (6.24 sec)
> >
> > ( number of rows are not equal but this can be solved if i get record by
> > offset)
> >
> > I am sure there is something wrong.The whole hash table is in memory
> > like wise the b tree of hash is in memory but why there is so much
> > improvement. Please sir check the prototype and tell if i am wrong
> > .thanks
>
> Sure.
>
> But still, please put your code on github and commit often.
> Let's use a proper development process instead of sending patches back
> and forth. If you need help with that, feel free to ping me on irc.
>
> And using // comments makes the code more difficult to review - you
> change every line in a big block. Better use
>
> #if 0
>...
> #endif
>
> > diff --git a/storage/maria/ma_hash_table.h
> b/storage/maria/ma_hash_table.h
> > new file mode 100644
> > index 000..c8e4578
> > --- /dev/null
> > +++ b/storage/maria/ma_hash_table.h
>
> why are you doing it in Aria? I thought we've agreed to do it on the
> upper level, in sql/
>
> > @@ -0,0 +1,45 @@
> > +#include"../../mysys/my_malloc.c"
> > +#include"../../include/my_global.h"
> > +typedef struct ma_hash_table_element{
> > + unsigned int hash_code;
> > + unsigned int  record_offset;
> > + struct ma_hash_table * next; //we will use single link list
> because no delete operations
> > +} ma_hash_table_element;
>
> Did you look at reusing the HASH data structure? include/hash.h,
> mysys/hash.c?
>
> > +
> > +typedef struct ma_hash_table{
> > + unsigned int size;
> > + ma_hash_table_element * h_t_e;
> > +}ma_hash_table;
>
> Because of the above (on the wrong level, ma_hash_table instead of HASH,
> using // comments to disable code) I've only quickly looked through the
> patch.
> But I didn't notice anything obviously wrong.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] InnoDB blob for primary key

[Sachin Setia]

Hi Sergei!

As i told you i was prototyping for hash table
It is done around 90% apart from one thing when hash is same
how to get record from .myd file when i have offset of record
so currently i am skipping it
But it is very fast i do not know why this is so fast here are
results of employee database
salary table definition
CREATE TABLE salaries (
emp_no  INT NOT NULL,
salary  blob NOT NULL,
from_date   DATENOT NULL,
to_date DATENOT NULL,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)
)
;
And query is
MariaDB [employees]> select distinct salary from salaries;

Result with out using hash table

++
85814 rows in set (2 min 24.76 sec)


Result with using hash table

| 39420  |
++
85809 rows in set (6.24 sec)

( number of rows are not equal but this can be solved if i get record by
offset)

I am sure there is something wrong.The whole hash table is in memory like
wise the
b tree of hash is in memory but why there is so much improvement. Please
sir check the
prototype and tell if i am wrong .thanks

Regards
sachin

On Mon, May 2, 2016 at 11:43 AM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> On May 02, Sachin Setia wrote:
> > I am sorry sir Currently my exam are going on
> > But i am working on prototype of second project. Will be done by tommorow
> > Regards
> > sachin
>
> Sure thing, that's totally fine!
>
> Still, in the future, if you plan to go silent for a while (exam or you
> just want to relax for a few days or something else :) - please drop me
> a short email and then I will know that you didn't disappear from the
> project. Thanks!
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
diff --git a/storage/maria/ma_hash_table.h b/storage/maria/ma_hash_table.h
new file mode 100644
index 000..c8e4578
--- /dev/null
+++ b/storage/maria/ma_hash_table.h
@@ -0,0 +1,45 @@
+#include"../../mysys/my_malloc.c"
+#include"../../include/my_global.h"
+typedef struct ma_hash_table_element{
+   unsigned int hash_code;
+   unsigned int  record_offset;
+   struct ma_hash_table * next; //we will use single link list because no 
delete operations
+} ma_hash_table_element;
+
+typedef struct ma_hash_table{
+   unsigned int size;
+   ma_hash_table_element * h_t_e;
+}ma_hash_table;
+ma_hash_table * ma_create_hash_table(int size){
+   ma_hash_table_element * _ht = (ma_hash_table 
*)my_malloc(sizeof(ma_hash_table)*size,MYF(MY_WME));
+   ma_hash_table_element * temp=_ht;
+   for(int i=0;i<size;i++){
+   temp->next=NULL;
+   temp++;
+   }
+   ma_hash_table *temp_hash_table = (ma_hash_table 
*)my_malloc(sizeof(ma_hash_table),MYF(MY_WME));
+   temp_hash_table->size =size;
+   temp_hash_table->h_t_e = _ht;
+   return temp_hash_table;
+}
+
+int add_key_to_hash_table(ma_hash_table *_ht,unsigned int key , unsigned int 
offset){
+   int hash_position = key % _ht->size; //just a simple logic thinking of 
upgrading it may we whatever
+   ma_hash_table_element *tmp= _ht->h_t_e;
+   tmp = tmp+hash_position;
+   ma_hash_table_element *parent;
+   //transverse the whole list
+   while(tmp!=NULL){
+   if(tmp->hash_code==key)
+   return 1;
+   parent=tmp;
+   tmp=tmp->next;
+   }
+   ma_hash_table_element *temp_hash = (ma_hash_table_element 
*)my_malloc(sizeof(ma_hash_table_element),MYF(MY_WME));
+   temp_hash->hash_code=key;
+   temp_hash->record_offset=offset;
+   temp_hash->next=NULL;
+   parent->next=temp_hash;
+   return 0;
+}
+
diff --git a/storage/maria/ma_state.h b/storage/maria/ma_state.h
index 2903986..d89324f 100644
--- a/storage/maria/ma_state.h
+++ b/storage/maria/ma_state.h
@@ -14,7 +14,7 @@
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
 
 /* Struct to store tables in use by one transaction */
-
+//#include"ma_hash_table.h"
 typedef struct st_maria_status_info
 {
   ha_rows records;  /* Rows in table */
@@ -24,6 +24,7 @@ typedef struct st_maria_status_info
   my_off_t key_file_length;
   my_off_t data_file_length;
   ha_checksum checksum;
+  void * hash_table;
   uint32 changed:1, /* Set if table was changed */
  no_transid:1;  /* Set if no transid was set on rows */
 } MARIA_STATUS_INFO;
diff --git a/storage/maria/ma_write.c b/storage/maria/ma_write.c
index f57c462..b31f5a7 100644
--- a/storage/maria/ma_write.c
+++ b/storage/maria/ma_write.c
@@ -21,7 +21,7 @@
 #include "trnman.h"
 #include "ma_key_recover.h"
 #include "ma_blockrec.h"
-
+#include "ma_hash_table.h"

Re: [Maria-developers] InnoDB blob for primary key

[Sachin Setia]

Hello Sergei!
Actually I am confused about first project. Should I do it for myisam or do
i need to do
it on upper level (change in sql/ directory)
Regards
sachin

On Sat, Apr 23, 2016 at 2:25 PM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> On Apr 23, Sachin Setia wrote:
> > Hello Sergei!
> > Thanks for selecting me in gsoc.
>
> You're welcome. That was well deserved.
>
> > Actually Recently I was reading about redis database.
> > I found it very fast ,can we  do something like in maridb
> > to make it fast. Like adding a new transaction isolation level in
> > innodb which will not be acid compliant to undo logs or something
> > like that
>
> May be :) But let's restrict ourselves to the GSoC project for now.
>
> It's community bonding, kind of pre-coding period now.
> You can use the time to setup git, fork MariaDB repository on github,
> and push your prototyping work into the "prototype" branch.
> I suspect you'll need two branches for two different prototypes that
> you've had...
>
> And don't be afraid to show up on irc, #maria channel on Freenode.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] InnoDB blob for primary key

[Sachin Setia]

Hello Sergei!
Hi Actually I was thinking about how to implement
blob as a foreign key.Foreign has to be unique which
we  can already  implement. To make it foreign key
we can either store unique hash or the whole blob
column.
But I am not sure much people
want to copy so long blob data in reference table.
Second option would be use blob hash as a reference key.
But user can not directly  us hash as a reference key
because that is hidden.
What I was thinking of a clear to programmer way of
using blob hash. Suppose user can directly create
blob hash column ,use that column as a primary key or
foreign key.Like
create table t1(abc blob , blob hash(abc))//this will just create blob hash
column
create table t1(abc blob,unique(blob hash(abc))) // this will create unique
blob hash column
and similar for primary key and foreign key
user can enter hash value if they have some good algorithm
or if they do not give any value we will automatically
create and store hash. What do you think? sir.
Regards
sachin



On Sat, Apr 16, 2016 at 7:37 PM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> Hi Sergei!
> As I already told you i was building prototype.It is some what completed
> apart from one thing comparing of two field values. the difficulty is how
> to get data length of field  from table->record[1]. I will try to solve it.
> One more thing actually i got how mysql hide field. For example  condsider
> three
> fields hash,data,data. mysql field pointer point at second field not at
> hash
> field and hash field ptr is stored in  table->hash_field
> can we do something similar to store hash fields(if we make array of
> hashes in
> case of more than one unique).But will adding member variable cause
> problem?
> what do you think?
> Regards
> sachin
>
> On Fri, Apr 15, 2016 at 6:37 PM, Sachin Setia <sachinsetia1...@gmail.com>
> wrote:
> > Hi Sergei!
> >
> > Actually I was going through the mysql source code for unique long
> constraints
> > in file sql_tmp_table.cc in function create_tmp_table they make a new
> field
> > and a new key(hash_key) and pass this table obejct to storage
> > engine.They actually
> > refer this field as a hash field
> > On the time of insert they call bool check_unique_constraint(TABLE
> > *table) function
> > which first calculate the hash and store it in field then they see for
> duplicate
> > hash and retrive ha_index_next_same if records are not same then record
> >
> > We can do the same thing in mariadb by adding one more field and key in
> > mysql_prepare_create_table in this we check for blob with unlimited
> > length or varchar for length
> > greater then internal storage engine by doing this in
> > mysql_prepare_create_table there
> > will be no issues of frm file inconsistance.
> >
> > In case of insert first we will fill the hash field in fill_record
> > function of sql_base.cc
> > by first calculating the hash. Then we will retrive the index map
> > using ha_index_read_map
> > if returened value is zero then we will comapare two records and if
> > they match then we will through error
> > I am not sure where to place this code either in fill_record or later
> > Or i can simple just
> > fill hash in field in fill_record and then check for duplicates later on.
> >
> > Current I am not sure how to hide columns from user.Sir, can you
> > suggest me where to look
> >
> > But there is one problem we can make unique key by this approch but
> > not primary key because primary key
> > is clustered and hashes can collide so i think we can't use hash field
> > as primary key. To overcome this problem
> > I have one idea instead of storing just hash we can make hash field
> > length 10 bytes and in last two bytes
> > we can store short int which tells how much time hash is repeated this
> > can make hash unique
> > in case of collusion. And also we are not doing more computation
> > because we already retrive
> > all records with same hashes.
> > What do you think of this idea?.
> > And there is one more problem how to make it foreign key.
> >
> > Will send you a prototype code tomorrow.
> > Regards
> > sachin
> >
> > On Fri, Apr 15, 2016 at 12:23 AM, Sergei Golubchik <s...@mariadb.org>
> wrote:
> >> Hi, Sachin!
> >>
> >> On Apr 13, Sachin Setia wrote:
> >>> Hello Sergei
> >>> Sorry I did not see your mail. Actually i was thinking something like
> >>> this before implementing the prototype but if i am more closer to
> >>> innodb the more performance i will i get. I will definitively think
> >>> about it.
> >>
> >> Great!
> >>
> >> Could you please tell me (mailing list, that is) what you think before
> >> next Monday (before April 18h, that is)?
> >>
> >> Regards,
> >> Sergei
> >> Chief Architect MariaDB
> >> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] InnoDB blob for primary key

[Sachin Setia]

Hi Sergei!
As I already told you i was building prototype.It is some what completed
apart from one thing comparing of two field values. the difficulty is how
to get data length of field  from table->record[1]. I will try to solve it.
One more thing actually i got how mysql hide field. For example  condsider three
fields hash,data,data. mysql field pointer point at second field not at hash
field and hash field ptr is stored in  table->hash_field
can we do something similar to store hash fields(if we make array of hashes in
case of more than one unique).But will adding member variable cause problem?
what do you think?
Regards
sachin

On Fri, Apr 15, 2016 at 6:37 PM, Sachin Setia <sachinsetia1...@gmail.com> wrote:
> Hi Sergei!
>
> Actually I was going through the mysql source code for unique long constraints
> in file sql_tmp_table.cc in function create_tmp_table they make a new field
> and a new key(hash_key) and pass this table obejct to storage
> engine.They actually
> refer this field as a hash field
> On the time of insert they call bool check_unique_constraint(TABLE
> *table) function
> which first calculate the hash and store it in field then they see for 
> duplicate
> hash and retrive ha_index_next_same if records are not same then record
>
> We can do the same thing in mariadb by adding one more field and key in
> mysql_prepare_create_table in this we check for blob with unlimited
> length or varchar for length
> greater then internal storage engine by doing this in
> mysql_prepare_create_table there
> will be no issues of frm file inconsistance.
>
> In case of insert first we will fill the hash field in fill_record
> function of sql_base.cc
> by first calculating the hash. Then we will retrive the index map
> using ha_index_read_map
> if returened value is zero then we will comapare two records and if
> they match then we will through error
> I am not sure where to place this code either in fill_record or later
> Or i can simple just
> fill hash in field in fill_record and then check for duplicates later on.
>
> Current I am not sure how to hide columns from user.Sir, can you
> suggest me where to look
>
> But there is one problem we can make unique key by this approch but
> not primary key because primary key
> is clustered and hashes can collide so i think we can't use hash field
> as primary key. To overcome this problem
> I have one idea instead of storing just hash we can make hash field
> length 10 bytes and in last two bytes
> we can store short int which tells how much time hash is repeated this
> can make hash unique
> in case of collusion. And also we are not doing more computation
> because we already retrive
> all records with same hashes.
> What do you think of this idea?.
> And there is one more problem how to make it foreign key.
>
> Will send you a prototype code tomorrow.
> Regards
> sachin
>
> On Fri, Apr 15, 2016 at 12:23 AM, Sergei Golubchik <s...@mariadb.org> wrote:
>> Hi, Sachin!
>>
>> On Apr 13, Sachin Setia wrote:
>>> Hello Sergei
>>> Sorry I did not see your mail. Actually i was thinking something like
>>> this before implementing the prototype but if i am more closer to
>>> innodb the more performance i will i get. I will definitively think
>>> about it.
>>
>> Great!
>>
>> Could you please tell me (mailing list, that is) what you think before
>> next Monday (before April 18h, that is)?
>>
>> Regards,
>> Sergei
>> Chief Architect MariaDB
>> and secur...@mariadb.org
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index ac2162b..291a3e2 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -8906,7 +8906,9 @@ fill_record(THD *thd, TABLE *table, Field **ptr, List ,
   List_iterator_fast v(values);
   List tbl_list;
   Item *value;
-  Field *field;
+  Field *field,hash_field;
+  int diff,src_length,dest_length,res;
+  	Field * blob_field;
   bool abort_on_warning_saved= thd->abort_on_warning;
   uint autoinc_index= table->next_number_field
 ? table->next_number_field->field_index
@@ -8929,12 +8931,18 @@ fill_record(THD *thd, TABLE *table, Field **ptr, List ,
 Reset the table->auto_increment_field_not_null as it is valid for
 only one row.
   */
-  table->auto_increment_field_not_null= FALSE;
+  table->auto_increment_field_not_null= FALSE; 
+  hash_field=*ptr++; //change
+  hash_field->null_bit=0;
+//  field->null_offset();
+	//just random number for hash
+  field->store(random(),true);
+
   while ((field = *ptr++) && ! thd->is_error())
   {
 /* Ensure that all fields are from the same table */
 DBUG_ASSERT(field->table == table);
-
+	blob_field=field;
 value=v++;
 if (field->field_index == auto

Re: [Maria-developers] InnoDB blob for primary key

[Sachin Setia]

Hello Sergei
Sorry I did not see your mail. Actually i was thinking something like
this before implementing the prototype but if i am more closer to
innodb the more performance i will i get. I will definitively think
about it.
Regards
sachin

On Wed, Apr 13, 2016 at 9:17 PM, Sachin Setia <sachinsetia1...@gmail.com> wrote:
> Hello Jan!
>
> Actually I was going through the problems raised by you
>
> 1>Firstly, existing tables will not have that column
>
> Yes Sir, you are right that existing table woudn't have DB_BLOB_HASH
> column, but this is fine because they indexed there blob column then
> they must have provided blob length, and if so then length of key_part
> would not be zero and so need to make changes in patch to detect this.
> And also by doing I can also eliminate another problem what if existing
>  table have a coloumn  named 'DB_BLOB_HASH'
>
> 2>secondly it will effect e.g maximum row size
> For this i can  increase reclength by 4 in pack_header function(but i
> am not sure whether this will work or not)
>
> 3>and external tools like backup
> Here, I am assuming that by backup you mean mysqldump actually i tried
> this by create a table with blob coloum (length was given ) and than
> changing mariadb binary(to changed code) and .sql file of backup was
> working fine
>
> But I think there are more problems with this this prototype
> 1>Existing table with coloumn named 'DB_BLOB_HASH' .We can solve this
> as written above but some code in patch compares first coloumn name
> with 'DB_BLOB_HASH' so this will create problem Sir, can we add a flag
> in dict_table_t->flag so that we can reliable check for blob hash coloumn
>
> 2>This is very bad problem , what happen if if data is different and crc
> hash of data is same.And this will occur when we have medium amount of
> data like 100 thousand records.To avoid this we can do like
>
> 2.1> use crc 64 reduces probability
> 2.2> use two different algorithm for hash and treat these two algoriths
> hash for uniquesness.Make  collusion probability very very less
>
> But the main problem is what would happen if collusion occurs to solve
> this i have two ideas
> 2.3> like 2.2 but instead of using two algorithm use one and add column
> for number of times this hash is repeated collusion frequency will be zero
> i think this will work fine but need to test it
> 2.4> whole prototype is crap start again with some different method. Can you
> please suggest some differnt way of doing this
> Regards
> sachin
>
> On Tue, Apr 12, 2016 at 4:52 PM, Jan Lindström
> <jan.lindst...@mariadb.com> wrote:
>> Hi,
>>
>> Sachin, naturally you may continue, this is open source, please read
>> https://mariadb.com/kb/en/mariadb/community-contributing-to-the-mariadb-project/
>> From InnoDB point of view there is issue if you add a new system generated
>> column to the row. Firstly, existing tables will not have that column and
>> secondly it will effect e.g maximum row size and external tools like backup.
>>
>> R: Jan
>>
>> On Mon, Apr 11, 2016 at 12:33 PM, Sachin Setia <sachinsetia1...@gmail.com>
>> wrote:
>>>
>>> Hello Developers,
>>> Hi this is sachin.Actually i was currently experimenting with with
>>> blob uniques in innodb
>>> there is three main problems
>>> 1.Unique blob
>>> 2.Blob Forigen key
>>> 3.Blob primary key
>>>
>>> 1. For blob unique we can simply store hash in unclustered index
>>> 2. Blob Forigen key i am currently working on it
>>> 3. Blob primary key :- for this i thought we create a 4 byte column
>>> which stores the hash of blob primary key.Internally this column will work
>>> as
>>> primary key and key for clustered index. I already successufully tested
>>> this
>>> here is my output
>>>
>>> MariaDB [sachin]> create table t4 (abc blob primary key);
>>> Query OK, 0 rows affected (0.10 sec)
>>>
>>> MariaDB [sachin]> insert into t4 values('sachin');
>>> Query OK, 1 row affected (0.01 sec)
>>>
>>> MariaDB [sachin]> insert into t4 values('sachin');
>>> ERROR 1062 (23000): Duplicate entry 'sachin' for key 'PRIMARY'
>>> MariaDB [sachin]> insert into t4 values('sachin setiya');
>>> Query OK, 1 row affected (0.00 sec)
>>>
>>> MariaDB [sachin]> insert into t4 values('sachin setiya');
>>> ERROR 1062 (23000): Duplicate entry 'sachin setiya' for key 'PRIMARY'
>>>
>>> MariaDB [sachin]> desc t4;
>>> +---+--+--+-+-+---+
>>> | Field | Type | Null | Key | Defa

Re: [Maria-developers] InnoDB blob for primary key

[Sachin Setia]

Hello Jan!

Actually I was going through the problems raised by you

1>Firstly, existing tables will not have that column

Yes Sir, you are right that existing table woudn't have DB_BLOB_HASH
column, but this is fine because they indexed there blob column then
they must have provided blob length, and if so then length of key_part
would not be zero and so need to make changes in patch to detect this.
And also by doing I can also eliminate another problem what if existing
 table have a coloumn  named 'DB_BLOB_HASH'

2>secondly it will effect e.g maximum row size
For this i can  increase reclength by 4 in pack_header function(but i
am not sure whether this will work or not)

3>and external tools like backup
Here, I am assuming that by backup you mean mysqldump actually i tried
this by create a table with blob coloum (length was given ) and than
changing mariadb binary(to changed code) and .sql file of backup was
working fine

But I think there are more problems with this this prototype
1>Existing table with coloumn named 'DB_BLOB_HASH' .We can solve this
as written above but some code in patch compares first coloumn name
with 'DB_BLOB_HASH' so this will create problem Sir, can we add a flag
in dict_table_t->flag so that we can reliable check for blob hash coloumn

2>This is very bad problem , what happen if if data is different and crc
hash of data is same.And this will occur when we have medium amount of
data like 100 thousand records.To avoid this we can do like

2.1> use crc 64 reduces probability
2.2> use two different algorithm for hash and treat these two algoriths
hash for uniquesness.Make  collusion probability very very less

But the main problem is what would happen if collusion occurs to solve
this i have two ideas
2.3> like 2.2 but instead of using two algorithm use one and add column
for number of times this hash is repeated collusion frequency will be zero
i think this will work fine but need to test it
2.4> whole prototype is crap start again with some different method. Can you
please suggest some differnt way of doing this
Regards
sachin

On Tue, Apr 12, 2016 at 4:52 PM, Jan Lindström
<jan.lindst...@mariadb.com> wrote:
> Hi,
>
> Sachin, naturally you may continue, this is open source, please read
> https://mariadb.com/kb/en/mariadb/community-contributing-to-the-mariadb-project/
> From InnoDB point of view there is issue if you add a new system generated
> column to the row. Firstly, existing tables will not have that column and
> secondly it will effect e.g maximum row size and external tools like backup.
>
> R: Jan
>
> On Mon, Apr 11, 2016 at 12:33 PM, Sachin Setia <sachinsetia1...@gmail.com>
> wrote:
>>
>> Hello Developers,
>> Hi this is sachin.Actually i was currently experimenting with with
>> blob uniques in innodb
>> there is three main problems
>> 1.Unique blob
>> 2.Blob Forigen key
>> 3.Blob primary key
>>
>> 1. For blob unique we can simply store hash in unclustered index
>> 2. Blob Forigen key i am currently working on it
>> 3. Blob primary key :- for this i thought we create a 4 byte column
>> which stores the hash of blob primary key.Internally this column will work
>> as
>> primary key and key for clustered index. I already successufully tested
>> this
>> here is my output
>>
>> MariaDB [sachin]> create table t4 (abc blob primary key);
>> Query OK, 0 rows affected (0.10 sec)
>>
>> MariaDB [sachin]> insert into t4 values('sachin');
>> Query OK, 1 row affected (0.01 sec)
>>
>> MariaDB [sachin]> insert into t4 values('sachin');
>> ERROR 1062 (23000): Duplicate entry 'sachin' for key 'PRIMARY'
>> MariaDB [sachin]> insert into t4 values('sachin setiya');
>> Query OK, 1 row affected (0.00 sec)
>>
>> MariaDB [sachin]> insert into t4 values('sachin setiya');
>> ERROR 1062 (23000): Duplicate entry 'sachin setiya' for key 'PRIMARY'
>>
>> MariaDB [sachin]> desc t4;
>> +---+--+--+-+-+---+
>> | Field | Type | Null | Key | Default | Extra |
>> +---+--+--+-+-+---+
>> | abc   | blob | NO   | PRI | NULL|   |
>> +---+--+--+-+-+---+
>> 1 row in set (0.01 sec)
>>
>> MariaDB [sachin]> select * from t4;
>> +---+
>> | abc   |
>> +---+
>> | sachin|
>> | sachin setiya |
>> +---+
>> 2 rows in set (0.01 sec)
>>
>> @Sergei hi! Actually i invested arround 2 months in mariadb So for me now
>> it  does not matter either i got selected in gsoc i want to do work in
>> innodb blob unique from today.Please sir allow me to do this
>>
>> I am incl

[Maria-developers] Fwd: Gsoc 2016 Mdev 371 Unique index for blob

[Sachin Setia]

Hi Sergei
Did you see the patch.Actually the patch works for every combination of
blob and varchars table definition.
Can you please tell what extra modification I should make
Regards
sachin

On Mon, Mar 28, 2016 at 9:34 PM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

>
> -- Forwarded message ------
> From: Sachin Setia <sachinsetia1...@gmail.com>
> Date: Mon, Mar 28, 2016 at 9:13 PM
> Subject: Re: [Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob
> To: maria-developers@lists.launchpad.net
>
>
> I am Sorry I did not see this mail.So I did not send the prototype link in
> proposal
>
> On Thu, Mar 24, 2016 at 10:41 PM, Sergei Golubchik <s...@mariadb.org>
> wrote:
>
>> Hi, Sachin!
>>
>> As you might have noticed on the mailing list, there is another student
>> who wants to do this project.
>>
>> It would help us choose, if you'd put your prototype code on github and
>> put the link to the repository into your final proposal.
>>
>> Thanks!
>>
>> On Mar 15, Sachin Setia wrote:
>> > Dear Developers,
>> >
>> > I was doing prototype for this project as Mr Sergei Golubchik suggested
>> >
>>
>> Regards,
>> Sergei
>> Chief Architect MariaDB
>> and secur...@mariadb.org
>>
>
>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Unique Index for blob

[Sachin Setia]

Sorry wrong extension
Regards
sachin

On Mon, Mar 28, 2016 at 9:09 PM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> Hello Devlopers
> Hi this is sachin. I applied for gsoc 2016(applied by name sachin setiya)
> My topic was unique index for blob(MyISAM)
> But i already completed some part of it .it is working for blobs varchar
> for definitions like
> create table tbl(abc varchar(2000) , xyz blob ,unique(abc,xyz));
> I have corrected the table2myisam function .
> For documentation you can refer to my gsoc proposal
> If am doing wrong please let me know.
> I am attaching the patch file
> TODO
> Second thing which i need to do is
> to tweak check_definition function to correctly check for definition
> currently I am commenting the function code
> Regards
> sachin
>
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index dfce503..eda9dd6 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3874,11 +3874,11 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO 
*create_info,
   if (f_is_geom(sql_field->pack_flag) && sql_field->geom_type ==
   Field::GEOM_POINT)
 column->length= MAX_LEN_GEOM_POINT_FIELD;
- if (!column->length)
- {
-   my_error(ER_BLOB_KEY_WITHOUT_LENGTH, MYF(0), 
column->field_name.str);
-   DBUG_RETURN(TRUE);
- }
+//   if (!column->length) //work
+//   {
+// my_error(ER_BLOB_KEY_WITHOUT_LENGTH, MYF(0), 
column->field_name.str);
+// DBUG_RETURN(TRUE);
+//   }
}
 #ifdef HAVE_SPATIAL
if (key->type == Key::SPATIAL)
@@ -3999,7 +3999,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO 
*create_info,
   if (key_part_length > file->max_key_part_length() &&
   key->type != Key::FULLTEXT)
   {
-key_part_length= file->max_key_part_length();
+//key_part_length= file->max_key_part_length(); //work
if (key->type == Key::MULTIPLE)
{
  /* not a critical problem */
@@ -4011,8 +4011,13 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO 
*create_info,
}
else
{
- my_error(ER_TOO_LONG_KEY, MYF(0), key_part_length);
- DBUG_RETURN(TRUE);
+   if(sql_field->sql_type==MYSQL_TYPE_VARCHAR){
+   //its ok we are going to use hash index
+   }else{
+   key_part_length= file->max_key_part_length();
+   my_error(ER_TOO_LONG_KEY, MYF(0), key_part_length);
+   DBUG_RETURN(TRUE);
+   }  
}
   }
   key_part_info->length= (uint16) key_part_length;
@@ -4070,10 +4075,13 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO 
*create_info,
 if (key->type == Key::UNIQUE && !(key_info->flags & HA_NULL_PART_KEY))
   unique_key=1;
 key_info->key_length=(uint16) key_length;
-if (key_length > max_key_length && key->type != Key::FULLTEXT)
+if (key_length > max_key_length && key->type != Key::FULLTEXT 
+   &>type!=Key::UNIQUE) //one more thing key should be varchar 
+   //i frankly do not know how to check it may be FIELDFLAG_MAYBE_NULL 
+   //will work //work
 {
-  my_error(ER_TOO_LONG_KEY,MYF(0),max_key_length);
-  DBUG_RETURN(TRUE);
+//  my_error(ER_TOO_LONG_KEY,MYF(0),max_key_length);
+//  DBUG_RETURN(TRUE);
 }
 
 if (validate_comment_length(thd, >key_create_info.comment,
diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc
index 4284e22..6d6ebcb 100644
--- a/storage/myisam/ha_myisam.cc
+++ b/storage/myisam/ha_myisam.cc
@@ -402,6 +402,362 @@ int table2myisam(TABLE *table_arg, MI_KEYDEF **keydef_out,
   DBUG_RETURN(0);
 }
 
+//work
+
+ typedef struct
+{
+  uint uniques; //total number of uniques
+  /*
+   * unique_keys contain array of chars where 1 means key is unique 
+   * with zero length 
+   * */
+  char * unique_keys; 
+} uniques_keys_arr;
+/*
+ * Find unique keys in keys array
+ * 
+ * */
+ //working
+static uniques_keys_arr *
+mysql_find_unique_keys(KEY *keys,uint number_of_keys){
+  //DBUG_ENTER("mysql_find_unique_keys");
+  uniques_keys_arr * u_arr = (uniques_keys_arr 
*)my_malloc(sizeof(uniques_keys_arr),MYF(MY_WME));
+  
+  if(!u_arr)
+  {
+return NULL;
+  }
+  
+  u_arr->uniques=0;
+  u_arr->unique_keys = (char 
*)my_malloc(sizeof(char)*number_of_keys,MYF(MY_WME));
+  char * temp = u_arr->unique_keys;
+  
+  if(!u_arr->unique_keys)
+  {
+return NULL;
+  }
+  
+  for(int i=0;i<number_of_keys;i++)
+  {
+   *temp=0;
+   for(int j=0;j<keys[i].user_defined_key_parts;j++)
+   {
+   if(!keys[i].key_part[j].length 
||keys[i].key_part[j].length>1000) //we need
+   //better logic
+   {
+   *temp=1;
+

Re: [Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

[Sachin Setia]

I am Sorry I did not see this mail.So I did not send the prototype link in
proposal

On Thu, Mar 24, 2016 at 10:41 PM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> As you might have noticed on the mailing list, there is another student
> who wants to do this project.
>
> It would help us choose, if you'd put your prototype code on github and
> put the link to the repository into your final proposal.
>
> Thanks!
>
> On Mar 15, Sachin Setia wrote:
> > Dear Developers,
> >
> > I was doing prototype for this project as Mr Sergei Golubchik suggested
> >
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] Unique Index for blob

[Sachin Setia]

Hello Devlopers
Hi this is sachin. I applied for gsoc 2016(applied by name sachin setiya)
My topic was unique index for blob(MyISAM)
But i already completed some part of it .it is working for blobs varchar
for definitions like
create table tbl(abc varchar(2000) , xyz blob ,unique(abc,xyz));
I have corrected the table2myisam function .
For documentation you can refer to my gsoc proposal
If am doing wrong please let me know.
I am attaching the patch file
TODO
Second thing which i need to do is
to tweak check_definition function to correctly check for definition
currently I am commenting the function code
Regards
sachin


patch.pl
Description: Perl program
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

[Sachin Setia]

Hello Sergei
Today I made some progress related to project.
MyISAM/ARIA
Got clear understanding of how to implement unique index for query like
create table tbl(col1 int primary key , col2 blob ,col3 blob ,
unique(col2,col3))
InnoDB
Reading about it.Actually Sir, I want to do this project whether I will
select in
gsoc or not(because InnoDB is amazing).
Proposal
Still Writing

Actually sir i have one doubt in table2myisam function definition

recinfo_out, (share->fields * 2 + 2) * sizeof(MI_COLUMNDEF),
^ ^   ^
why we allocating these many number of recinfo because we only require
share->fields + 1 .

One more doubt in optimizing "select distinct coloumn_name(here it is a
blob coloumn)  from table"
query. In mi write which take one record and write it we check for unique
constraint. It takes O(n^2)
time. I was thinking if we can optimize this by first fetching the whole
table record and calculating hash for
each record.Instead of comparing one hash with all other we can sort the
hashes and ignore the duplicate
(we can make an array of 0 and 1 and if it 1 that means record is not
duplicate and for 0 it is duplicte)
.buy doing this we can reduce the time complexity to O(nlog(n)).This will
work fast if we have enough buffer_storage
in case of low buffer memory this will turn to tradeoff between cpu and i/o
requests because in order to sort keys
in low ram we need to use m way merge sort which ultimately result in more
I/O because we have to send back records to
hard disk which we can not store in ram and then once again fetch unique
record for storing in tmp table.But we can get
performance if records fit in ram .For caching the records  we can do it
over here
sql/sql_select.cc
  18313 error= info->read_record(info);
 Regards
 sachin

On Wed, Mar 23, 2016 at 12:06 AM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> On Mar 22, Sachin Setia wrote:
> > Hello Sergei
> > Actually I was prototyping for blob and varchar   for aria and myisam
> > storage engine.
> > My prototype worked for complex definations like
> > craete table(abc int primary key, blob_col blob unique, varchar_col
> > varchar(1000) unique) engine=myisam;
> > Solved the issue of frm file incosistance.
> >
> > As you suggested for doing it for innodb i am current working on
> it.Innodb
>
> *I* did not suggest that. But you're welcome to try, of course.
> If you think that just MyISAM is too simple for a three month project.
> (Aria doesn't count it's a couple of days after you done MyISAM).
>
> > does not natively support hash based index.
> > when we run select distinct column from tbl;
> > it use create_internal_tmp_table() which uses maria storage engine for
> > creating tmp table.
> > But query like this works
> > MariaDB [sachin]> create table iu2(abc blob unique);
> > Query OK, 0 rows affected (0.04 sec)
> >
> > MariaDB [sachin]> insert into iu2 values(1);
> > Query OK, 1 row affected (0.03 sec)
> >
> > MariaDB [sachin]> insert into iu2 values(1);
> > ERROR 1062 (23000): Duplicate entry '1' for key 'abc'
> > this query does not use hash but it simply compares values
>
> Interesting.
>
> > Will write a proposal shortly.
>
> Okay.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] More memory allocation for key seg

[Sachin Setia]

Thanks Sergei , this solved the mystery.
Regards
sachin

On Wed, Mar 23, 2016 at 12:03 AM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> On Mar 22, Sachin Setia wrote:
> > Hello Devlopers
> > I was debugging the source code of mariadb. My query was
> > MariaDB [sachin]> create table pro2(abc int primary key,xyz int ,
> > ass int ,unique(xyz,ass))engine=myisam;
> >
> > In table2myisam function of file ha_myisam.cc i found that
> > we are allocating more memory for keysegments
> >
> > if (!(my_multi_malloc(MYF(MY_WME),
> >   recinfo_out, (share->fields * 2 + 2) * sizeof(MI_COLUMNDEF),
> >   keydef_out, share->keys * sizeof(MI_KEYDEF),
> >   ,
> >   (share->key_parts + share->keys) * sizeof(HA_KEYSEG),
> >^ here why this
> >   NullS)))
> >
> > And we donot use these extra key segments because
> > suceeding for loop use only total number of share->key_parts
> > keysegments. And same in ha_maria file.Is this a bug or I am missing
> > something
> > Regards
> > sachin
>
> The last key segment of every key is the row id. For dynamic row format
> it's the offset in the MYD file. For static row format (all rows have
> the same length as in your example above) it's the row number.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] More memory allocation for key seg

[Sachin Setia]

Hello Devlopers
I was debugging the source code of mariadb. My query was
MariaDB [sachin]> create table pro2(abc int primary key,xyz int ,
ass int ,unique(xyz,ass))engine=myisam;

In table2myisam function of file ha_myisam.cc i found that
we are allocating more memory for keysegments

if (!(my_multi_malloc(MYF(MY_WME),
  recinfo_out, (share->fields * 2 + 2) * sizeof(MI_COLUMNDEF),
  keydef_out, share->keys * sizeof(MI_KEYDEF),
  ,
  (share->key_parts + share->keys) * sizeof(HA_KEYSEG),
   ^ here why this
  NullS)))

And we donot use these extra key segments because
suceeding for loop use only total number of share->key_parts
keysegments. And same in ha_maria file.Is this a bug or I am missing
something
Regards
sachin
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

[Sachin Setia]

Hello Sergei
Actually I was prototyping for blob and varchar   for aria and myisam
storage engine.
My prototype worked for complex definations like
craete table(abc int primary key, blob_col blob unique, varchar_col
varchar(1000) unique) engine=myisam;
Solved the issue of frm file incosistance.

As you suggested for doing it for innodb i am current working on it.Innodb
does not natively support hash based index.
when we run select distinct column from tbl;
it use create_internal_tmp_table() which uses maria storage engine for
creating tmp table.
But query like this works
MariaDB [sachin]> create table iu2(abc blob unique);
Query OK, 0 rows affected (0.04 sec)

MariaDB [sachin]> insert into iu2 values(1);
Query OK, 1 row affected (0.03 sec)

MariaDB [sachin]> insert into iu2 values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'abc'
this query does not use hash but it simply compares values
Will write a proposal shortly.

On Tue, Mar 22, 2016 at 4:20 PM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> Hello Sergi
> Actually I was prototyping for blob and varchar   for aria and myisam
> storage engine.
> My prototype worked for complex definations like
> craete table(abc int primary key, blob_col blob unique, varchar_col
> varchar(1000) unique) engine=myisam;
> Solved the issue of frm file incosistance.
>
> As you suggested for doing it for innodb i am current working on it.Innodb
> does not natively support hash based index.
> when we run select distinct column from tbl;
> it use create_internal_tmp_table() which uses maria storage engine for
> creating tmp table.
> But query like this works
> MariaDB [sachin]> create table iu2(abc blob unique);
> Query OK, 0 rows affected (0.04 sec)
>
> MariaDB [sachin]> insert into iu2 values(1);
> Query OK, 1 row affected (0.03 sec)
>
> MariaDB [sachin]> insert into iu2 values(1);
> ERROR 1062 (23000): Duplicate entry '1' for key 'abc'
> this query does not use hash but it simply compares values
> Will write a proposal shortly.
>
>
> Regards
> sachin
>
> On Sat, Mar 19, 2016 at 1:52 AM, Sergei Golubchik <s...@mariadb.org>
> wrote:
>
>> Hi, Sachin!
>>
>> On Mar 18, Sachin Setia wrote:
>> >
>> > ERROR 1030 (HY000): Got error 190 "Incompatible key or row definition
>> > between the MariaDB .frm file and the information in the storage engine.
>> > You have to dump an" from storage engine MyISAM
>> >
>> > We are getting this becuase in mi_create for each unique_def it creates
>> one
>> > keydef and writes it.And this creates two  problem
>> > 1. frm keydef algorithm is different from saved kefdef algorithm(always
>> > zero) for the time I have solved this problem .
>> >
>> > 2.Mismatch between keydef's keysegs the reason for this is when
>> mi_create
>> > creates keyseg for unique_def it did not keeps the orignal uniquedef's
>> > keyseg parameters in mind like language start length which creates
>> problem
>> > in check_definition function in ha_myisam.cc.I am currently working on
>> it
>> > Once again sorry for this foolish mistake.
>> > Regars
>> > sachin
>>
>> No problem, everyone makes mistakes :)
>>
>> It's a prototype, after all. It's much more important that you
>> understand how the code works and why it doesn't work.
>>
>> Regards,
>> Sergei
>> Chief Architect MariaDB
>> and secur...@mariadb.org
>>
>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] b1a1e5b: MDEV-6058 MySQL Bug #11766693: LOG-SLOW-ADMIN-STATEMENTS AND LOG-SLOW-SLAVE-STATEMENTS NOT DISPLAYED.

[Sachin Setia]

Dear Mr Sergei
Sorry for a careless mistake I thought prototype is working but it is
working only for InnoDB not for MyIsam because if we look at table creation
which I send you

MariaDB [sachin]> create table tbl(int_key int primary key , blob_key
blob unique);
Query OK, 0 rows affected (0.03 sec)

Here I forgot to add engine =myisam .
If we the same table creation for myisam table get created but at the time
of insert we get this message

ERROR 1030 (HY000): Got error 190 "Incompatible key or row definition
between the MariaDB .frm file and the information in the storage engine.
You have to dump an" from storage engine MyISAM

We are getting this becuase in mi_create for each unique_def it creates one
keydef and writes it.And this creates two  problem
1. frm keydef algorithm is different from saved kefdef algorithm(always
zero) for the time I have solved this problem .

2.Mismatch between keydef's keysegs the reason for this is when mi_create
creates keyseg for unique_def it did not keeps the orignal uniquedef's
keyseg parameters in mind like language start length which creates problem
in check_definition function in ha_myisam.cc.I am currently working on it
Once again sorry for this foolish mistake.
Regars
sachin

On Fri, Mar 18, 2016 at 3:16 PM, Sergei Golubchik  wrote:

> Hi, Alexey!
>
> On Mar 18, Alexey Botchkov wrote:
> > revision-id: b1a1e5b3d3d5ab95c596580de07daf38a3efa039
> (mariadb-10.1.12-19-gb1a1e5b)
> > parent(s): 4aac51db9a55a11574cbfa60484d4329d35b6f2c
> > committer: Alexey Botchkov
> > timestamp: 2016-03-18 11:47:05 +0400
> > message:
> >
> > MDEV-6058 MySQL Bug #11766693: LOG-SLOW-ADMIN-STATEMENTS AND
> LOG-SLOW-SLAVE-STATEMENTS NOT DISPLAYED.
> > These parameters were moved from the command line options to the
> > system variables section. Treatment of the
> > opt_log_slow_slave_statements changed to let the dynamic change
> > of the variable.
> >
> > ---
> >  mysql-test/r/variables.result  |  10 ++
> >  mysql-test/suite/rpl/r/rpl_slow_query_log.result   |  10 ++
> >  mysql-test/suite/rpl/t/rpl_slow_query_log.test |  43 
> >  .../r/log_slow_admin_statements_basic.result   |  76 ++
> >  .../r/log_slow_admin_statements_func.result|  46 +
> >  .../r/log_slow_slave_statements_basic.result   |  76 ++
> >  .../t/log_slow_admin_statements_basic.test | 110
> +
> >  .../sys_vars/t/log_slow_admin_statements_func.test |  61 
> >  .../t/log_slow_slave_statements_basic.test | 110
> +
> >  mysql-test/t/variables.test|  11 +++
>
> I suppose you didn't run the full test suite. Otherwise you would've
> noticed that sysvar_* results were changed.
>
> Also, because we have sysvar_* tests, we no longer create
> _basic tests for every new variable, they are no longer useful.
> Starting from 10.1 you only need to test the functionality of new
> variables, not their basic properties.
>
> >  sql/log_event.cc   |  10 ++
> >  sql/mysqld.cc  |   8 --
> >  sql/sys_vars.cc|  13 +++
> >  13 files changed, 576 insertions(+), 8 deletions(-)
> >
> > diff --git a/mysql-test/r/variables.result
> b/mysql-test/r/variables.result
> > index fef3e4a..e46e65f 100644
> > --- a/mysql-test/r/variables.result
> > +++ b/mysql-test/r/variables.result
> > @@ -1797,3 +1797,13 @@ select * from
> information_schema.session_variables where variable_name='sql_mode
> >  VARIABLE_NAMEVARIABLE_VALUE
> >  SQL_MODE ANSI_QUOTES
> >  End of 5.5 tests
> > +show variables like 'log_slow%statements';
> > +Variable_nameValue
> > +log_slow_admin_statementsOFF
> > +log_slow_slave_statementsOFF
> > +select * from information_schema.global_variables where variable_name
> like 'log_slow_admin_statements';
> > +VARIABLE_NAMEVARIABLE_VALUE
> > +LOG_SLOW_ADMIN_STATEMENTSOFF
> > +select * from information_schema.global_variables where variable_name
> like 'log_slow_slave_statements';
> > +VARIABLE_NAMEVARIABLE_VALUE
> > +LOG_SLOW_SLAVE_STATEMENTSOFF
>
> You can remove this test.
>
> > diff --git a/mysql-test/suite/rpl/t/rpl_slow_query_log.test
> b/mysql-test/suite/rpl/t/rpl_slow_query_log.test
> > index faf037a..de336fc 100644
> > --- a/mysql-test/suite/rpl/t/rpl_slow_query_log.test
> > +++ b/mysql-test/suite/rpl/t/rpl_slow_query_log.test
> > @@ -299,6 +299,49 @@ if ($master_slow_query == $slave_slow_query)
> >-- echo ### same number of queries in slow log: $master_slow_query
> >  }
> >
> > +-- echo
> 
> > +-- echo  TRUNCATE the slow log then check whether runtime changes of
> > +-- echo  log_slow_slave_statements work without slave restart.
> > +-- echo
> 

Re: [Maria-developers] GSoC 2016:Unique indexes for blobs

[Sachin Setia]

Hello everyone
@Shubham
As  I said i am just prototyping
This is not the final code
It is just poc to see whether if I send some unique key will mi_create work
fine or not as it is working fine in create_internal_tmp_table on following
query "select distinct(a2) from tbl"  where schema of tbl will be a1 int
primary key ,a2 blob

On Wed, Mar 16, 2016 at 9:18 AM, Shubham Barai 
wrote:

> Hello, Sergie!
>  I  tried to explore the source code from
> mi_create.c,ha_myisam.cc,sql/sql_table.cc,include/myisam.h,.and some
> other files.
> The main task is to create  MI_UNIQUEDEF "uniques" for long unique
> constraints.
> We have to consider all the cases where we need to create MI_UNIQUEDEF
> instead of MI_KEYDEF.
>
> It will include queries like
>create table table1 (blob_column blob,unique(blob_column) );
>create table table1 (a int,blob_column
> blob,unique(a,blob_column) );
>create table table1 (a int,blob_column1 blob,blob_column2
> blob,unique(blob_column1(300),blob_column2) );
> (key with multiple blob columns and one of the blob column specified with
> prefix length).
>
> I think we have to create  MI_UNIQUEDEF if any one of the columns in a
> key is a blob field without  prefix length.
>
> In sql/sql_table, mysql_prepare_create_table is the function which
> prepares the table and key structures for table creation in mi_create. It
> generates an error if any one of the blob fields in a key is specified
> without length.
> Currently, this task is limited to MyISAM, so if any other storage engine
> is selected, we have to generate the same error in
> mysql_prepare_create_table.
>
> In storage/myisam/ha_myisam.cc, table2myisam is a function which allocates
> and initializes myisam key and column definitions.The current function
> prototype of table2myisam is
>table2myisam(TABLE  *table_arg, MI_KEYDEF  **keydef_out,
> MI_COLUMNDEF **recinfo_out, uint records_out)
> We have to change it to
>table2myisam(TABLE  *table_arg,MI_KEYDEF  **keydef_out,MI_UNIQUEDEF **
> uniquedef_out,MI_COLUMNDEF  **recinfo_out,uint records_out)
>
> table2myisam initializes all the key definitions from table_arg->keyinfo.
> So we can  set  a new flag  (say uniquedef) in a keyinfo struct in
> mysql_prepare_create_table if any one of the key_part consists of blob
> field without prefix length.
> Later we can check the flag in table2myisam to see  if we want to create
> MI_KEYDEF or MI_UNIQUEDEF.
>
> Thanks,
> Shubham.
>
> On 5 March 2016 at 03:52, Sergei Golubchik  wrote:
>
>> Hi, Shubham!
>>
>> On Mar 03, Shubham Barai wrote:
>> >
>> > I am interested in the project "Unique indexes for blobs".I have read
>> the
>> > description of the project given on the link
>> > https://jira.mariadb.org/browse/MDEV-371.
>>
>> Great!
>>
>> > I want to know what exactly is the task for this project.Any help
>> > would be greatly appreciated
>>
>> See storage/myisam/mi_create.c. You see that a myisam table can have
>> keys (defined in MI_KEYDEF structure) and so-called "uniques" (defined
>> by MI_UNIQUEDEF).
>>
>> Keys have length limitation, keys can be unique (HA_NOSAME flag) or not
>> unique.
>>
>> "uniques" have no length limitation.
>>
>> When one creates a unique key from SQL:
>>
>>   CREATE TABLE ... (... UNIQUE KEY (...) ... )
>>
>> this will always create a key (MI_KEYDEF) in a MyISAM table. That's why
>> unique constraints in MyISAM have a limited length.
>>
>> This task is about creating MI_UNIQUEDEF "uniques" instead of MI_KEYDEF
>> "keys" for long unique constraints.
>>
>> Regards,
>> Sergei
>> Chief Architect MariaDB
>> and secur...@mariadb.org
>>
>
>
> ___
> Mailing list: https://launchpad.net/~maria-developers
> Post to : maria-developers@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp
>
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

[Sachin Setia]

Thanks sir for your reply
I have done two thing
1. First commenting some code to remove the error we get when we try to
create unique blob
file=sql/sql_table.cc
line no=3877
/*
* Gsoc 2016
* I am implementing this so comment this stuff out
 */
//  if (!column->length)
//  {
//my_error(ER_BLOB_KEY_WITHOUT_LENGTH, MYF(0), column->field_name.str);
//DBUG_RETURN(TRUE);
//  }


2. here I am assuming this thing for for just prototyping
Assumption My table will just contain two column first will be primary key
second will be unique blob
So basically what i am doing is create a custom unique key and passing it
to mi_create
Of course in real patching i will replace this code with logic like if i
have m unique blobs (i will find this using key_length ==0)
create m unique key array and pass it

file=storage/myisam/ha_myisam.cc
line no=2067
//some tweak in share for prototype
share->keys--;
share->uniques=1;
MI_UNIQUEDEF uniquedef;
MI_KEYDEF keydef_blob=*(keydef+1);
bzero((char*) ,sizeof(uniquedef));
uniquedef.keysegs=1;
uniquedef.seg=keydef_blob.seg;
uniquedef.null_are_equal=1;

  /* TODO: Check that the following fn_format is really needed */
  error= mi_create(fn_format(buff, name, "", "",
 MY_UNPACK_FILENAME|MY_APPEND_EXT),
   share->keys, keydef,
   record_count, recinfo,
   1, ,
   _info, create_flags);

If i am doing it wrongly please let me know
Regards
sachin

On Wed, Mar 16, 2016 at 12:37 AM, Sergei Golubchik <s...@mariadb.org> wrote:

> Hi, Sachin!
>
> On Mar 15, Sachin Setia wrote:
> >
> > I was doing prototype for this project as Mr Sergei Golubchik suggested
> >
> > "This task is about creating MI_UNIQUEDEF "uniques" instead of MI_KEYDEF
> > "keys" for long unique constraints."
> >
> > Here is my output
> >
> > MariaDB [sachin]> create table tbl(int_key int primary key , blob_key
> > blob unique);
> > Query OK, 0 rows affected (0.03 sec)
> >
> > MariaDB [sachin]> insert into tbl values(1,1);
> > Query OK, 1 row affected (0.01 sec)
> >
> > MariaDB [sachin]> insert into tbl values(2,1);
> > ERROR 1062 (23000): Duplicate entry '1' for key 'blob_key'
>
> Very cool!
>
> > Should i mail you the source code.Please let me know
> > Regards
> > sachin
>
> Sure! Please, do.
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and secur...@mariadb.org
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

[Sachin Setia]

Sorry one correction
@-- I am assuming that unique blob will have 0 zero key
@++ I am assuming that unique blob will have 0 zero key length
Regards
sachin

On Tue, Mar 15, 2016 at 11:02 PM, Sachin Setia <sachinsetia1...@gmail.com>
wrote:

> Dear Developers,
>
> I was doing prototype for this project as Mr Sergei Golubchik suggested
>
> "This task is about creating MI_UNIQUEDEF "uniques" instead of MI_KEYDEF
> "keys" for long unique constraints."
>
> Here is my output
>
> MariaDB [sachin]> create table tbl(int_key int primary key , blob_key blob 
> unique);
> Query OK, 0 rows affected (0.03 sec)
>
> MariaDB [sachin]> insert into tbl values(1,1);
> Query OK, 1 row affected (0.01 sec)
>
> MariaDB [sachin]> insert into tbl values(2,1);
> ERROR 1062 (23000): Duplicate entry '1' for key 'blob_key'
> MariaDB [sachin]> insert into tbl values(2,2);
> Query OK, 1 row affected (0.00 sec)
>
> MariaDB [sachin]> insert into tbl values(3,3);
> Query OK, 1 row affected (0.00 sec)
>
> MariaDB [sachin]> select * from tbl;
> +-+--+
> | int_key | blob_key |
> +-+--+
> |   1 | 1|
> |   2 | 2|
> |   3 | 3|
> +-+--+
> 3 rows in set (0.00 sec)
>
>
> I am assuming that unique blob will have 0 zero key .
> Should i mail you the source code.Please let me know
> Regards
> sachin
>
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

[Sachin Setia]

Dear Developers,

I was doing prototype for this project as Mr Sergei Golubchik suggested

"This task is about creating MI_UNIQUEDEF "uniques" instead of MI_KEYDEF
"keys" for long unique constraints."

Here is my output

MariaDB [sachin]> create table tbl(int_key int primary key , blob_key
blob unique);
Query OK, 0 rows affected (0.03 sec)

MariaDB [sachin]> insert into tbl values(1,1);
Query OK, 1 row affected (0.01 sec)

MariaDB [sachin]> insert into tbl values(2,1);
ERROR 1062 (23000): Duplicate entry '1' for key 'blob_key'
MariaDB [sachin]> insert into tbl values(2,2);
Query OK, 1 row affected (0.00 sec)

MariaDB [sachin]> insert into tbl values(3,3);
Query OK, 1 row affected (0.00 sec)

MariaDB [sachin]> select * from tbl;
+-+--+
| int_key | blob_key |
+-+--+
|   1 | 1|
|   2 | 2|
|   3 | 3|
+-+--+
3 rows in set (0.00 sec)


I am assuming that unique blob will have 0 zero key .
Should i mail you the source code.Please let me know
Regards
sachin
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob

[Sachin Setia]

Hello Developers
   My name is sachin , a third year undergrad student in India.I am
interested in gsoc 2016
I was debugging the maria db for following query

create table c4(a1 int primary key,b1 int unique,c1 blob) engine=myisam;

In mysql_prepare_create_table  there is a function
name prepare_create_field which sets the sql_field pack flag . for a1 field
it sets the pack flag =27  . do not quiet get what it means.
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp