April 2016 - developers - lists.mariadb.org

Re: [Maria-developers] [Commits] c204e9b: MDEV-9610 Trigger on normal table can't insert into CONNECT engine table - Access Denied
by Alexander Barkov 26 Apr '16

26 Apr '16

Hi Sergei, On 04/25/2016 08:42 PM, serg(a)mariadb.org wrote: > revision-id: c204e9bd63e7f6ce40e83b1f7fc0b4607a131009 (mariadb-10.0.24-43-gc204e9b) > parent(s): ce38adddfa91949b30956abd0e4cab201effcdef > author: Sergei Golubchik > committer: Sergei Golubchik > timestamp: 2016-04-25 18:41:31 +0200 > message: > > MDEV-9610 Trigger on normal table can't insert into CONNECT engine table - Access Denied > > in case of prelocking, don't check table->grant.privilege > in handler::external_lock(), do it in > handler::start_stmt(). > > --- > storage/connect/ha_connect.cc | 58 ++++++++++++++++++++-- > storage/connect/ha_connect.h | 1 + > storage/connect/mysql-test/connect/r/grant3.result | 5 ++ > storage/connect/mysql-test/connect/t/grant3.test | 11 ++++ > 4 files changed, 70 insertions(+), 5 deletions(-) > > diff --git a/storage/connect/ha_connect.cc b/storage/connect/ha_connect.cc > index 645d000..1032567 100644 > --- a/storage/connect/ha_connect.cc > +++ b/storage/connect/ha_connect.cc > @@ -4054,6 +4054,14 @@ int ha_connect::delete_all_rows() > } // end of delete_all_rows > > > +static void issue_access_denied_error(THD *thd) > +{ > + status_var_increment(thd->status_var.access_denied_errors); > + my_error(access_denied_error_code(thd->password), MYF(0), > + thd->security_ctx->priv_user, thd->security_ctx->priv_host, > + (thd->password ? ER(ER_YES) : ER(ER_NO))); > +} > + > bool ha_connect::check_privileges(THD *thd, PTOS options, char *dbn) > { > const char *db= (dbn && *dbn) ? dbn : NULL; > @@ -4124,12 +4132,9 @@ bool ha_connect::check_privileges(THD *thd, PTOS options, char *dbn) > */ > if (!table || !table->mdl_ticket || table->mdl_ticket->get_type() == MDL_EXCLUSIVE) > return check_access(thd, FILE_ACL, db, NULL, NULL, 0, 0); > - if (table->grant.privilege & FILE_ACL) > + if (thd->lex->requires_prelocking() || table->grant.privilege & FILE_ACL) > return false; > - status_var_increment(thd->status_var.access_denied_errors); > - my_error(access_denied_error_code(thd->password), MYF(0), > - thd->security_ctx->priv_user, thd->security_ctx->priv_host, > - (thd->password ? ER(ER_YES) : ER(ER_NO))); > + issue_access_denied_error(thd); > return true; > > // This is temporary until a solution is found > @@ -4146,6 +4151,46 @@ bool ha_connect::check_privileges(THD *thd, PTOS options, char *dbn) > return true; > } // end of check_privileges > > +bool ha_connect::check_cached_privileges(THD *thd) > +{ > + PTOS options= GetTableOptionStruct(); > + TABTYPE type=GetRealType(options); > + > +#ifdef NO_EMBEDDED_ACCESS_CHECKS > + return false; > +#endif > + > + switch (type) { > + case TAB_DOS: > + case TAB_FIX: > + case TAB_BIN: > + case TAB_CSV: > + case TAB_FMT: > + case TAB_DBF: > + case TAB_XML: > + case TAB_INI: > + case TAB_VEC: > + case TAB_JSON: > + if (!options->filename || !*options->filename) > + return false; > + /* Fall through to check FILE_ACL */ > + case TAB_ODBC: > + case TAB_MYSQL: > + case TAB_DIR: > + case TAB_MAC: > + case TAB_WMI: > + case TAB_OEM: > + if (table->grant.privilege & FILE_ACL) > + return false; > + issue_access_denied_error(thd); > + return true; > + default: The patch looks fine. I'd suggest two things. 1. Don't use "default" Can you please list all possible type value instead of "default"? Otherwise, when a new table type is added the next time, it will be easier to forget to update check_cached_privileges() accordingly. 2. Try to avoid duplicate code. Btw, I'd probably try to remove the duplicate switch, by adding a new method like this: enum Extra_acl { CONNECT_SECURE_FILE_PATH= 1, CONNECT_FILE_ACL= 1<<1; } int ha_connect::extra_acl_needed(THD *thd, PTOS options) { ... switch (GetRealType(options)) { ... case TAB_DOS: case TAB_FIX: case TAB_BIN: case TAB_CSV: case TAB_FMT: case TAB_DBF: case TAB_XML: case TAB_INI: case TAB_VEC: case TAB_JSON: return (options->filename && *options->filename) ? CONNECT_SECURE_FILE_PATH : 0; case TAB_ODBC: case TAB_MYSQL: case TAB_DIR: case TAB_MAC: case TAB_WMI: case TAB_OEM: #ifdef NO_EMBEDDED_ACCESS_CHECKS return 0; #endif return CONNECT_FILE_ACL; ... } ... } And then reuse it in both ha_connect::check_privilege() and ha_connect::check_cached_privileges(). The latter would look about like this: bool ha_connect::check_cached_privileges(THD *thd) { PTOS options= GetTableOptionStruct(); if (!(extra_acl_needed(options) & CONNECT_FILE_ACL) || (table->grant.privilege & FILE_ACL)) return false; issue_access_denied_error(thd); return true; } > + return false; > + } > + return false; > + > +} // end of check_cached_privileges > + > // Check that two indexes are equivalent > bool ha_connect::IsSameIndex(PIXDEF xp1, PIXDEF xp2) > { > @@ -4308,6 +4353,9 @@ int ha_connect::start_stmt(THD *thd, thr_lock_type lock_type) > PGLOBAL g= GetPlug(thd, xp); > DBUG_ENTER("ha_connect::start_stmt"); > > + if (check_cached_privileges(thd)) > + DBUG_RETURN(HA_ERR_INTERNAL_ERROR); > + > // Action will depend on lock_type > switch (lock_type) { > case TL_WRITE_ALLOW_WRITE: > diff --git a/storage/connect/ha_connect.h b/storage/connect/ha_connect.h > index 05cc872..fae804b 100644 > --- a/storage/connect/ha_connect.h > +++ b/storage/connect/ha_connect.h > @@ -537,6 +537,7 @@ int index_prev(uchar *buf); > > protected: > bool check_privileges(THD *thd, PTOS options, char *dbn); > + bool check_cached_privileges(THD *thd); It looks like the number of leading spaces is wrong here. > MODE CheckMode(PGLOBAL g, THD *thd, MODE newmode, bool *chk, bool *cras); > char *GetDBfromName(const char *name); > > diff --git a/storage/connect/mysql-test/connect/r/grant3.result b/storage/connect/mysql-test/connect/r/grant3.result > new file mode 100644 > index 0000000..2f9d37b > --- /dev/null > +++ b/storage/connect/mysql-test/connect/r/grant3.result > @@ -0,0 +1,5 @@ > +create table tcon (i int) engine=Connect table_type=DOS file_name='tcon.dos'; > +create table tin (i int); > +create trigger tr after insert on tin for each row insert into tcon values (new.i); > +insert into tin values (1); > +drop table tin,tcon; > diff --git a/storage/connect/mysql-test/connect/t/grant3.test b/storage/connect/mysql-test/connect/t/grant3.test > new file mode 100644 > index 0000000..9f05ca7 > --- /dev/null > +++ b/storage/connect/mysql-test/connect/t/grant3.test > @@ -0,0 +1,11 @@ > +# > +# MDEV-9610 Trigger on normal table can't insert into CONNECT engine table - Access Denied > +# > +create table tcon (i int) engine=Connect table_type=DOS file_name='tcon.dos'; > +create table tin (i int); > +create trigger tr after insert on tin for each row insert into tcon values (new.i); > +insert into tin values (1); > +drop table tin,tcon; > + > +let datadir=`select @@datadir`; > +remove_file $datadir/test/tcon.dos; > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits >

2 1

Re: [Maria-developers] Deadlock with STOP SLAVE and LOCK_active_mi
by Michael Widenius 26 Apr '16

26 Apr '16

Hi! >>> Any suggestions for how this is supposed to work? Or is it just broken >>> by design, but saved because normally slave threads do not need to >>> access SHOW STATUS or system variables? <cut> > Hm. So I went through all of the code that references LOCK_active_mi to try > and understand this. It seems there are two main uses: > > 1. As you say, to serialise admin commands. I think the list is: end_slave() > (server shutdown); CHANGE MASTER; START/STOP SLAVE; START/STOP ALL SLAVES; > RESET SLAVE; FLUSH SLAVE. > > 2. To protect access to master_info_index, eg. to prevent a Master_info from > disappearing while it is accessed. When reading about this, I think it would be better to have a counter in Master_info if someone is using it and only delete if if the counter is zero. Would be trivial to add an increment of the counter in get_master_info(). The counter could either be protected by the LOCK_active_mi or one of the mutexes in Master_info like data_lock. > There is a comment in rpl_rli.h that LOCK_active_mi protects > Relay_log_info::inited, but I did not understand it - maybe it is wrong? Setting inited is indirectly protected by LOCK_active_mi, as init_all_master_info() which calls init_master_info(), is protected by LOCK_active_mi. Looking at the code, I didn't however see any need to protect inited as this is an internal flag that is always 1 after start. It's main usage is to avoid some re-initialization of relay logs on retries. > So the idea would be to make a new lock for (1), and keep LOCK_active_mi for > (2). > > Actually, using a lock for (1) seems a bad idea - STOP SLAVE can take a long > time to run, and a mutex makes it unkillable. A condition variable might be > better (to make STOP SLAVE killable). But I guess that is a separate issue, > it would not affect the possibility of deadlocks. Agree that a condition variable would be better, but not critical as one has already a problem if stop_slave doesn't work. > Both LOCK_active_mi and the new LOCK_serialize_replication_admin_commands > must by themselves prevent master_info_index from having elements added or > removed. This is needed for start_all_slaves() and stop_all_slaves() to work > correctly, I think. Why both? Isn't it enough to first take LOCK_serialize_replication_admin_commands and then take LOCK_active_mi if one needs to access master_info_index ? > Something will need to be done for remove_master_info(). Currently, it also > tries to stop slave threads (from within free_key_master_info()) while > holding LOCK_active_mi. It seems that threads should be stopped before > attempting to remove their mi from master_info_index, probably? To do that we would need to: - Add a flag in Master_info that it's not usable anymore and change this flag only under LOCK_active_mi. get_master_info() could wait (on a condition) if this flag is set. - Add a counter that Master_info is in use. - Add a function to release Master_info. - Call terminate_slave_threads() outside of free_key_master_info() > Actually, there would still be the deadlock problem after introducing the > new lock, because it is possible for a slave thread to run CHANGE MASTER! > (and I think START SLAVE / RESET SLAVE as well). But that is probably a bug, > does not seem good that this is possible. I guess these need a check to > throw an ER_LOCK_OR_ACTIVE_TRANSACTION error, like STOP SLAVE. Wouldn't the new lock LOCK_serialize_replication_admin_commands fix that? > I think I also found another lock problem while reading the code, for > MASTER_POS_WAIT() og SHOW BINLOG EVENTS. They both grab a Master_info under > LOCK_active_mi, but then proceed to access it after releasing the lock. I do > not see anything that prevents the Master_info to disappear under their feet > if RESET SLAVE ALL is run in another thread? But again, that's a separate > issue, I suppose. Probably there are some other tricky deadlock issues > lurking here. I checked the function mysql_show_binlog_events() but could not find any access to Master_info after mysql_mutex_unlock(&LOCK_active_mi) was released. > I don't know. It seems splitting into a new > LOCK_serialize_replication_admin_commands could solve the deadlock issue, > maybe, if the other problems mentioned above are addressed. It seems very > hard to convince oneself that this would not introduce new problems > somewhere, the locking in replication is really complex and feels very > fragile :-/ It doesn't really feel like something one would want to do in > 10.1 (certainly not 10.0), but maybe 10.2? > > It's kind of a very edge-case problem (and seems to have been there for a > _long_ time). Still, hanging the mysqld process hard, requiring kill -9, is > also not nice... Adding two new flags, one if master_info is in use and one if it's unusable, shouldn't be that hard to make reasonable safe in 10.1 I am now back in Finland and working. Feel free to call me to discuss this any time. Regards, Monty

2 2

[Maria-developers] Problem that some variables can be changed without stopping the slave IO thread
by Kristian Nielsen 25 Apr '16

25 Apr '16

Monty, This patch of yours introduced a change where before both slave threads must be stopped to change @@replicate_events_marked_for_skip, but after only the SQL thread is checked: ----------------------------------------------------------------------- commit 572560f38c248d5020f0e63aeb3f8905cd568208 Author: Michael Widenius <monty(a)askmonty.org> Date: Wed Oct 3 01:44:54 2012 +0300 Changed SHOW_FUNC variabels that don't return SHOW_ARRAY to SHOW_SIMPLE_FUNC. + Master_info_index::give_error_if_slave_running() + + @return + TRUE If some slave is running. An error is printed + FALSE No slave is running +*/ + +bool Master_info_index::give_error_if_slave_running() +{ + DBUG_ENTER("warn_if_slave_running"); + mysql_mutex_assert_owner(&LOCK_active_mi); + + for (uint i= 0; i< master_info_hash.records; ++i) + { + Master_info *mi; + mi= (Master_info *) my_hash_element(&master_info_hash, i); + if (mi->rli.slave_running != MYSQL_SLAVE_NOT_RUN) + { + my_error(ER_SLAVE_MUST_STOP, MYF(0), (int) mi->connection_name.length, + mi->connection_name.str); + DBUG_RETURN(TRUE); + } + } + DBUG_RETURN(FALSE); +} bool Sys_var_replicate_events_marked_for_skip::global_update(THD *thd, set_var *var) ... - /* Slave threads must be stopped to change the variable. */ + mysql_mutex_unlock(&LOCK_global_system_variables); mysql_mutex_lock(&LOCK_active_mi); - lock_slave_threads(active_mi); - init_thread_mask(&thread_mask, active_mi, 0 /*not inverse*/); - if (thread_mask) // We refuse if any slave thread is running - { - my_error(ER_SLAVE_MUST_STOP, MYF(0)); - result= true; - } - else + if (!master_info_index->give_error_if_slave_running()) ----------------------------------------------------------------------- So instead of checking both IO and SQL thread, now it checks only SQL thread (mi->rli.slave_running). Why was this change made? Was it by mistake? Unfortunately, I did not notice this before, so I subsequently used the same code (give_error_if_slave_running()) for the following variables: gtid_slave_pos slave_parallel_threads slave_domain_parallel_threads replicate_events_marked_for_skip gtid_ignore_duplicates This means that all of the above variables can be changed while the IO thread is still running. This was never the intention! There are some complex interactions between the master, the IO thread, and the SQL thread, particularly with respect to the slave's GTID position, and parts of the code are specifically written on the assumption that certain things cannot change without first stopping all replication threads. (I do not have the full overview of exactly what can break, I think that would require a careful review of large parts of the code, but I fear that it can corrupt not just the replication GTID position and possibly even internal server structures). So I am wondering what to do ... it is easy to fix give_error_if_slave_running() to check for both threads, but this is a change in behaviour (even if it should match documentation) that could easily break administration scripts, eg. after a 10.0 automatic security-fix upgrade. I also know that eg. Jean François has written about how he uses the feature of being able to not stop the IO thread in some cases. On the other hand, this can cause serious problems for users, as they have no real way of knowing not to attempt to change these variables without stopping IO thread first, when there is no error given. MDEV-9138 describes a complex scenario where slave position gets corrupted from this. Any suggestions? Thanks, - Kristian.

1 0

[Maria-developers] newest CVEs in mysql | mariadb vulnerable?
by Christian Rebischke 23 Apr '16

23 Apr '16

Hello, I just want to inform everybody about the newest mysql CVEs: Apr 21 CVE-2016-0666 Apr 21 CVE-2016-0663 Apr 21 CVE-2016-0665 Apr 21 CVE-2016-0661 Apr 21 CVE-2016-0662 Apr 21 CVE-2016-0659 Apr 21 CVE-2016-0658 Apr 21 CVE-2016-0657 Apr 21 CVE-2016-0656 Apr 21 CVE-2016-0654 Apr 21 CVE-2016-0655 Apr 21 CVE-2016-0653 Apr 21 CVE-2016-0652 Apr 21 CVE-2016-0651 Apr 21 CVE-2016-0650 Apr 21 CVE-2016-0649 Apr 21 CVE-2016-0648 Apr 21 CVE-2016-0647 Apr 21 CVE-2016-0646 Apr 21 CVE-2016-0644 Apr 21 CVE-2016-0643 Apr 21 CVE-2016-0642 Apr 21 CVE-2016-0641 Apr 21 CVE-2016-0639 Apr 21 CVE-2016-0640 Is there a list of current CVEs for mariadb there I can compare them? Best regards Chris

2 1

Re: [Maria-developers] InnoDB blob for primary key
by Sachin Setia 23 Apr '16

23 Apr '16

Hello Sergei! By adaptive hashing I refer to something like in innodb adaptive hashing http://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html On Tue, Apr 19, 2016 at 6:55 PM, sachin setiya <sachinsetia1001(a)gmail.com> wrote: > Hello > > > ---------- Forwarded message ---------- > From: Sachin Setia <sachinsetia1001(a)gmail.com> > To: maria-developers(a)lists.launchpad.net > Cc: > Date: Mon, 11 Apr 2016 15:03:24 +0530 > Subject: InnoDB blob for primary key > 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 including the patch file and t4.ibd and t4.frm file > Regards > sachin > > > > > ---------- Forwarded message ---------- > From: "Jan Lindström" <jan.lindstrom(a)mariadb.com> > To: Sachin Setia <sachinsetia1001(a)gmail.com> > Cc: "maria-developers(a)lists.launchpad.net" < > maria-developers(a)lists.launchpad.net> > Date: Tue, 12 Apr 2016 14:22:29 +0300 > Subject: Re: [Maria-developers] InnoDB blob for primary key > Hi, > > Sachin, naturally you may continue, this is open source, please read > https://mariadb.com/kb/en/mariadb/community-contributing-to-the-mariadb-pro… > 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 <sachinsetia1001(a)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 including the patch file and t4.ibd and t4.frm file >> Regards >> sachin >> >> >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-developers >> Post to : maria-developers(a)lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-developers >> More help : https://help.launchpad.net/ListHelp >> >> > > > ---------- Forwarded message ---------- > From: Sergei Golubchik <vuvova(a)gmail.com> > To: Sachin Setia <sachinsetia1001(a)gmail.com> > Cc: > Date: Wed, 13 Apr 2016 00:04:22 +0200 > Subject: Re: [Maria-developers] InnoDB blob for primary key > Hi, Sachin! > > Just a very quick comment now. We're having a company meeting > (and a meetup - > http://www.meetup.com/MariaDB-Developers-Berlin-Meetup/events/230026151/) > in Berlin, I'll send you a longer reply when this is all over (in a > couple of days). > > It might be an interesting option not to hack InnoDB, but to add > this feature (long UNIQUE keys) on the upper level. That is, in sql/ > directory. Automatically add a new BIGINT column, but hide it from > the user (not show it in SHOW COLUMNS and in SELECT *). Create > an index for it (also hidden). Put the blob's hash into this column. > And on collisions retrieve both rows and compare blobs. Just like MyISAM > is doing, but not on the storage engine level, but in the > engine-independent fashion, something that works for all engines. > > Want to look at this approach? > > MySQL 5.7 has something similar (long unique constraints on the sql > layer), but they've done it only for internal temporary tables (like > MyISAM's MI_UNIQUE is only used for internal temporary tables), > and it's not directly available for the user. Still, it might give you > some hinst about how to implement this feature on the sql layer. > > Regards, > Sergei > Chief Architect MariaDB > and security(a)mariadb.org > > On Apr 11, Sachin Setia 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 including the patch file and t4.ibd and t4.frm file > > Regards > > sachin > > > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > > index dfce503..efd6f22 100644 > > --- a/sql/sql_table.cc > > +++ b/sql/sql_table.cc > > @@ -3244,7 +3244,7 @@ mysql_prepare_create_table(THD *thd, > HA_CREATE_INFO *create_info, > > !(sql_field->charset= find_bin_collation(sql_field->charset))) > > DBUG_RETURN(TRUE); > > > > - /* > > + /* > > Convert the default value from client character > > set into the column character set if necessary. > > */ > > @@ -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) //change > > +// { > > +// my_error(ER_BLOB_KEY_WITHOUT_LENGTH, MYF(0), > column->field_name.str); > > +// DBUG_RETURN(TRUE); > > +// } > > } > > #ifdef HAVE_SPATIAL > > if (key->type == Key::SPATIAL) > > @@ -3992,9 +3992,9 @@ mysql_prepare_create_table(THD *thd, > HA_CREATE_INFO *create_info, > > } > > else if (key_part_length == 0 && (sql_field->flags & > NOT_NULL_FLAG)) > > { > > - my_error(ER_WRONG_KEY_COLUMN, MYF(0), file->table_type(), > > - column->field_name.str); > > - DBUG_RETURN(TRUE); > > +// my_error(ER_WRONG_KEY_COLUMN, MYF(0), file->table_type(), > > +// column->field_name.str); > > +// DBUG_RETURN(TRUE); > > } > > if (key_part_length > file->max_key_part_length() && > > key->type != Key::FULLTEXT) > > diff --git a/storage/innobase/dict/dict0dict.cc > b/storage/innobase/dict/dict0dict.cc > > index c51deb0..f6e94af 100644 > > --- a/storage/innobase/dict/dict0dict.cc > > +++ b/storage/innobase/dict/dict0dict.cc > > @@ -1315,7 +1315,7 @@ dict_table_add_to_cache( > > #define BIG_ROW_SIZE 1024 > > > > ut_ad(mutex_own(&(dict_sys->mutex))); > > - > > + > > dict_table_add_system_columns(table, heap); > > > > table->cached = TRUE; > > diff --git a/storage/xtradb/dict/dict0dict.cc > b/storage/xtradb/dict/dict0dict.cc > > index 206038d..d8223d7 100644 > > --- a/storage/xtradb/dict/dict0dict.cc > > +++ b/storage/xtradb/dict/dict0dict.cc > > @@ -1251,7 +1251,7 @@ dict_table_open_on_name( > > Adds system columns to a table object. */ > > UNIV_INTERN > > void > > -dict_table_add_system_columns( > > +dict_table_add_system_columns( //work > > /*==========================*/ > > dict_table_t* table, /*!< in/out: table */ > > mem_heap_t* heap) /*!< in: temporary heap */ > > @@ -1266,16 +1266,25 @@ dict_table_add_system_columns( > > etc.) and as the last columns of the table memory object. > > The clustered index will not always physically contain all > > system columns. */ > > - > > +// dict_mem_table_add_col(table,heap,"DB_BLOB_HASH",DATA_INT > > +// ,DATA_NOT_NULL,DATA_BLOB_HASH_LEN); > > dict_mem_table_add_col(table, heap, "DB_ROW_ID", DATA_SYS, > > DATA_ROW_ID | DATA_NOT_NULL, > > DATA_ROW_ID_LEN); > > #if DATA_ROW_ID != 0 > > #error "DATA_ROW_ID != 0" > > #endif > > +// dict_mem_table_add_col(table,heap,"DB_BLOB_HASH",DATA_SYS, > > +// > DATA_BLOB_HASH|DATA_NOT_NULL, > > +// > DATA_BLOB_HASH_LEN); > > + //just a rough trick to get it working > > + > > +// if(*table->name=='y'){ > > dict_mem_table_add_col(table, heap, "DB_TRX_ID", DATA_SYS, > > DATA_TRX_ID | DATA_NOT_NULL, > > DATA_TRX_ID_LEN); > > +// } > > + > > #if DATA_TRX_ID != 1 > > #error "DATA_TRX_ID != 1" > > #endif > > @@ -1310,7 +1319,11 @@ dict_table_add_to_cache( > > ulint row_len; > > > > ut_ad(dict_lru_validate()); > > - > > +// bool x =false;//break > > +// if(x){ > > +// dict_mem_table_add_col(table,heap,"DB_BLOB_HASH",DATA_INT > > +// > ,DATA_NOT_NULL,4); > > +// } > > /* The lower limit for what we consider a "big" row */ > > #define BIG_ROW_SIZE 1024 > > > > @@ -3075,7 +3088,7 @@ dict_index_build_internal_clust( > > /* Copy the fields of index */ > > dict_index_copy(new_index, index, table, 0, index->n_fields); > > > > - if (dict_index_is_univ(index)) { > > + if (dict_index_is_univ(index)) { //work > > /* No fixed number of fields determines an entry uniquely > */ > > > > new_index->n_uniq = REC_MAX_N_FIELDS; > > @@ -3124,7 +3137,7 @@ dict_index_build_internal_clust( > > DATA_ROLL_PTR), > > 0); > > > > - for (i = 0; i < trx_id_pos; i++) { > > + for (i = 0; i < trx_id_pos; i++) {//work i think i need to > do some stuff > > > > ulint fixed_size = dict_col_get_fixed_size( > > dict_index_get_nth_col(new_index, i), > > diff --git a/storage/xtradb/dict/dict0load.cc > b/storage/xtradb/dict/dict0load.cc > > index d6ed8ac..8cc59f8 100644 > > --- a/storage/xtradb/dict/dict0load.cc > > +++ b/storage/xtradb/dict/dict0load.cc > > @@ -2276,7 +2276,7 @@ dictionary cache. > > ibd_file_missing flag TRUE in the table object we return */ > > UNIV_INTERN > > dict_table_t* > > -dict_load_table( > > +dict_load_table( //work for corruped table > > /*============*/ > > const char* name, /*!< in: table name in the > > databasename/tablename format */ > > @@ -2337,7 +2337,7 @@ dict_load_table( > > btr_pcur_close(&pcur); > > mtr_commit(&mtr); > > mem_heap_free(heap); > > - > > + > > return(NULL); > > } > > > > diff --git a/storage/xtradb/dict/dict0mem.cc > b/storage/xtradb/dict/dict0mem.cc > > index a4f6cd6..6cbe556 100644 > > --- a/storage/xtradb/dict/dict0mem.cc > > +++ b/storage/xtradb/dict/dict0mem.cc > > @@ -101,11 +101,11 @@ dict_mem_table_create( > > memcpy(table->name, name, strlen(name) + 1); > > table->is_system_db = dict_mem_table_is_system(table->name); > > table->space = (unsigned int) space; > > - table->n_cols = (unsigned int) (n_cols + DATA_N_SYS_COLS); > > - > > + table->n_cols = (unsigned int) (n_cols + DATA_N_SYS_COLS); //work > > + > > table->cols = static_cast<dict_col_t*>( > > mem_heap_alloc(heap, > > - (n_cols + DATA_N_SYS_COLS) > > + (n_cols + DATA_N_SYS_COLS)//work > > * sizeof(dict_col_t))); > > > > ut_d(table->magic_n = DICT_TABLE_MAGIC_N); > > diff --git a/storage/xtradb/handler/ha_innodb.cc > b/storage/xtradb/handler/ha_innodb.cc > > index e5edf76..3a8dc91 100644 > > --- a/storage/xtradb/handler/ha_innodb.cc > > +++ b/storage/xtradb/handler/ha_innodb.cc > > @@ -5983,7 +5983,7 @@ ha_innobase::open( > > ibool par_case_name_set = FALSE; > > char par_case_name[FN_REFLEN]; > > dict_err_ignore_t ignore_err = DICT_ERR_IGNORE_NONE; > > - > > + ibool is_blob_primary_key=false; > > DBUG_ENTER("ha_innobase::open"); > > > > UT_NOT_USED(mode); > > @@ -6031,13 +6031,20 @@ ha_innobase::open( > > > > /* Get pointer to a table object in InnoDB dictionary cache */ > > ib_table = dict_table_open_on_name(norm_name, FALSE, TRUE, > ignore_err); > > - > > + //int number_of_columns = dict_table_get_n_user_cols(ib_table); > > + //if(ib_table->) > > + if(ib_table){ > > + int number_of_columns = > dict_table_get_n_user_cols(ib_table); > > + > if(!innobase_strcasecmp(ib_table->col_names,"DB_BLOB_HASH")){ > > + is_blob_primary_key=true; > > + number_of_columns--;//stodo i think we need to add > flag for blob primary key to make checking easier > > + } > > if (ib_table > > - && ((!DICT_TF2_FLAG_IS_SET(ib_table, DICT_TF2_FTS_HAS_DOC_ID) > > - && table->s->stored_fields != > dict_table_get_n_user_cols(ib_table)) > > + && ((!DICT_TF2_FLAG_IS_SET(ib_table, DICT_TF2_FTS_HAS_DOC_ID)) > > + && table->s->stored_fields != number_of_columns) //work > > || (DICT_TF2_FLAG_IS_SET(ib_table, DICT_TF2_FTS_HAS_DOC_ID) > > && (table->s->fields > > - != dict_table_get_n_user_cols(ib_table) - 1)))) { > > + != dict_table_get_n_user_cols(ib_table) - 1))) { > > ib_logf(IB_LOG_LEVEL_WARN, > > "table %s contains %lu user defined columns " > > "in InnoDB, but %lu columns in MySQL. Please " > > @@ -6054,7 +6061,7 @@ ha_innobase::open( > > ib_table = NULL; > > is_part = NULL; > > } > > - > > + } > > if (UNIV_UNLIKELY(ib_table && ib_table->is_corrupt && > > srv_pass_corrupt_table <= 1)) { > > free_share(share); > > @@ -6254,12 +6261,12 @@ ha_innobase::open( > > /* Looks like MySQL-3.23 sometimes has primary key number != 0 */ > > primary_key = table->s->primary_key; > > key_used_on_scan = primary_key; > > - > > + if(!is_blob_primary_key){ > > if (!innobase_build_index_translation(table, ib_table, share)) { > > sql_print_error("Build InnoDB index translation table > for" > > " Table %s failed", name); > > } > > - > > + } > > /* Allocate a buffer for a 'row reference'. A row reference is > > a string of bytes of length ref_length which uniquely specifies > > a row in our table. Note that MySQL may also compare two row > > @@ -6314,7 +6321,11 @@ ha_innobase::open( > > for (uint i = 0; i < table->s->keys; i++) { > > dict_index_t* index; > > index = innobase_get_index(i); > > - if (dict_index_is_clust(index)) { > > + if (dict_index_is_clust(index)) { //work > > + if(is_blob_primary_key){ > > + ref_length=4; //hash length > > + continue; > > + } > > ref_length = > > > table->key_info[i].key_length; > > } > > @@ -7795,7 +7806,8 @@ build_template_field( > > > > //ut_ad(field == table->field[i]); > > ut_ad(clust_index->table == index->table); > > - > > +//work here we go > > +//todo it should get the next column defs > > col = dict_table_get_nth_col(index->table, i); > > > > templ = prebuilt->mysql_template + prebuilt->n_template++; > > @@ -8138,7 +8150,8 @@ ha_innobase::build_template( > > continue; > > } > > } > > - > > + > if(!innobase_strcasecmp(clust_index->fields->name,"DB_BLOB_HASH")) > > + i++; > > build_template_field(prebuilt, clust_index, index, > > table, field, i); > > } > > @@ -11065,6 +11078,7 @@ create_table_def( > > ulint doc_id_col = 0; > > ibool has_doc_id_col = FALSE; > > mem_heap_t* heap; > > + bool is_blob_primary_key=false; > > > > DBUG_ENTER("create_table_def"); > > DBUG_PRINT("enter", ("table_name: %s", table_name)); > > @@ -11126,6 +11140,12 @@ create_table_def( > > table->fts->doc_col = doc_id_col; > > } > > } else { > > + if(form->key_info[0].key_part->length==0 && //change > > + form->key_info[0].key_part->key_type|MYSQL_TYPE_BLOB){ > > + s_cols++; > > + is_blob_primary_key=true; > > + } > > + > > table = dict_mem_table_create(table_name, 0, s_cols, > > flags, flags2); > > } > > @@ -11143,6 +11163,13 @@ create_table_def( > > table->data_dir_path = NULL; > > } > > heap = mem_heap_create(1000); > > + //work > > + //add one more column for hash > > + > > + if(is_blob_primary_key){ > > + dict_mem_table_add_col(table,heap,"DB_BLOB_HASH", > > + DATA_INT,1283,4); > > + } > > > > for (i = 0; i < n_cols; i++) { > > Field* field = form->field[i]; > > @@ -11222,7 +11249,7 @@ create_table_def( > > err = DB_ERROR; > > goto error_ret; > > } > > - > > + > > dict_mem_table_add_col(table, heap, > > field->field_name, > > col_type, > > @@ -11347,7 +11374,7 @@ create_index( > > the length of the key part versus the column. */ > > > > Field* field = NULL; > > - > > +//work > > for (ulint j = 0; j < form->s->fields; j++) { > > > > field = form->field[j]; > > @@ -11396,7 +11423,12 @@ create_index( > > } > > > > field_lengths[i] = key_part->length; > > - > > + if(form->key_info[0].key_part->length==0 && //change > > + form->key_info[0].key_part->key_type|MYSQL_TYPE_BLOB){ > > + dict_mem_index_add_field( > > + index,"DB_BLOB_HASH", 0); > > + continue; > > + } > > dict_mem_index_add_field( > > index, key_part->field->field_name, prefix_len); > > } > > diff --git a/storage/xtradb/include/data0type.h > b/storage/xtradb/include/data0type.h > > index 111664b..0f510e8 100644 > > --- a/storage/xtradb/include/data0type.h > > +++ b/storage/xtradb/include/data0type.h > > @@ -147,6 +147,9 @@ be less than 256 */ > > #define DATA_ROLL_PTR 2 /* rollback data pointer: 7 bytes > */ > > #define DATA_ROLL_PTR_LEN 7 > > > > +//#define DATA_BLOB_HASH 3 // hash coloumn for blob primay key > > +//#define DATA_BLOB_HASH_LEN 4 //used as a clustered index > > + > > #define DATA_N_SYS_COLS 3 /* number of system columns > defined above */ > > > > #define DATA_FTS_DOC_ID 3 /* Used as FTS DOC ID column */ > > diff --git a/storage/xtradb/que/que0que.cc > b/storage/xtradb/que/que0que.cc > > index e2dc023..076785d 100644 > > --- a/storage/xtradb/que/que0que.cc > > +++ b/storage/xtradb/que/que0que.cc > > @@ -1030,7 +1030,7 @@ que_thr_step( > > que_node_print_info(node); > > } > > #endif > > - if (type & QUE_NODE_CONTROL_STAT) { > > + if (type & QUE_NODE_CONTROL_STAT) { //improve > > if ((thr->prev_node != que_node_get_parent(node)) > > && que_node_get_next(thr->prev_node)) { > > > > diff --git a/storage/xtradb/row/row0ins.cc > b/storage/xtradb/row/row0ins.cc > > index d02e179..c58d7fb 100644 > > --- a/storage/xtradb/row/row0ins.cc > > +++ b/storage/xtradb/row/row0ins.cc > > @@ -137,7 +137,7 @@ row_ins_alloc_sys_fields( > > /*=====================*/ > > ins_node_t* node) /*!< in: insert node */ > > { > > - dtuple_t* row; > > + dtuple_t* row; //work > > dict_table_t* table; > > mem_heap_t* heap; > > const dict_col_t* col; > > @@ -3118,7 +3118,7 @@ row_ins_index_entry_set_vals( > > > > n_fields = dtuple_get_n_fields(entry); > > > > - for (i = 0; i < n_fields; i++) { > > + for (i = 0; i < n_fields; i++) {//see > > dict_field_t* ind_field; > > dfield_t* field; > > const dfield_t* row_field; > > @@ -3169,8 +3169,8 @@ row_ins_index_entry_step( > > > > ut_ad(dtuple_check_typed(node->row)); > > > > - row_ins_index_entry_set_vals(node->index, node->entry, node->row); > > - > > + row_ins_index_entry_set_vals(node->index, node->entry, > node->row);//explore > > +//need to see who sets the entry in row > > ut_ad(dtuple_check_typed(node->entry)); > > > > err = row_ins_index_entry(node->index, node->entry, thr); > > @@ -3208,7 +3208,7 @@ row_ins_alloc_row_id_step( > > > > /* Fill in row id value to row */ > > > > - row_id = dict_sys_get_new_row_id(); > > + row_id = dict_sys_get_new_row_id(); //work > > > > dict_sys_write_row_id(node->row_id_buf, row_id); > > } > > diff --git a/storage/xtradb/row/row0mysql.cc > b/storage/xtradb/row/row0mysql.cc > > index 9427b20..b28e778 100644 > > --- a/storage/xtradb/row/row0mysql.cc > > +++ b/storage/xtradb/row/row0mysql.cc > > @@ -510,7 +510,15 @@ row_mysql_store_col_in_innobase_format( > > > > return(buf); > > } > > - > > +//this is just dummy function > > +int dummuy_hash(byte * ptr,int length){ > > + int val=0; > > + for(int i=0;i<length;i++){ > > + val =val*8+(int )*ptr; > > + ptr++; > > + } > > + return val; > > +} > > /**************************************************************//** > > Convert a row in the MySQL format to a row in the Innobase format. Note > that > > the function to convert a MySQL format key value to an InnoDB dtuple is > > @@ -532,15 +540,22 @@ row_mysql_convert_row_to_innobase( > > const mysql_row_templ_t*templ; > > dfield_t* dfield; > > ulint i; > > - > > + ibool is_blob_primary_key=false; > > + ibool is_first_template=true; > > ut_ad(prebuilt->template_type == ROW_MYSQL_WHOLE_ROW); > > - ut_ad(prebuilt->mysql_template); > > - > > - for (i = 0; i < prebuilt->n_template; i++) { > > + ut_ad(prebuilt->mysql_template);//work > > + > if(!innobase_strcasecmp(prebuilt->table->col_names,"DB_BLOB_HASH")){ > > + is_blob_primary_key =true; > > + } > > + for (i = 0; i < prebuilt->n_template; i++) { //change add one > > > > templ = prebuilt->mysql_template + i; > > + if(is_blob_primary_key&&is_first_template){ > > + dfield = dtuple_get_nth_field(row, i+1); > > + is_first_template=false; > > + }else{ > > dfield = dtuple_get_nth_field(row, i); > > - > > + } > > if (templ->mysql_null_bit_mask != 0) { > > /* Column may be SQL NULL */ > > > > @@ -565,6 +580,17 @@ row_mysql_convert_row_to_innobase( > > next_column: > > ; > > } > > + if(is_blob_primary_key){ > > + //get the first field and set the hash > > + dfield_t * hash_field=dtuple_get_nth_field(row,0); > > + dfield_t * blob_field=dtuple_get_nth_field(row,1); > > + byte * hash = static_cast<byte > *>(mem_heap_zalloc(prebuilt->heap,4)); > > + mach_write_to_4(hash,dummuy_hash((byte > *)blob_field->data,blob_field->len)); > > + //4 is hash length > > + hash_field->len=4; > > + hash_field->data=hash; > > + //hash_field->type > > + } > > > > /* If there is a FTS doc id column and it is not user supplied ( > > generated by server) then assign it a new doc id. */ > > @@ -831,7 +857,7 @@ row_create_prebuilt( > > > > prebuilt->search_tuple = dtuple_create(heap, > search_tuple_n_fields); > > > > - ref = dtuple_create(heap, ref_len); > > + ref = dtuple_create(heap, ref_len);//work > > > > dict_index_copy_types(ref, clust_index, ref_len); > > > > @@ -1057,7 +1083,7 @@ row_get_prebuilt_insert_row( > > > > dict_table_copy_types(row, table); > > > > - ins_node_set_new_row(node, row); > > + ins_node_set_new_row(node, row);//explore > > > > prebuilt->ins_graph = static_cast<que_fork_t*>( > > que_node_get_parent( > > @@ -1353,7 +1379,7 @@ row_insert_for_mysql( > > row_get_prebuilt_insert_row(prebuilt); > > node = prebuilt->ins_node; > > > > - row_mysql_convert_row_to_innobase(node->row, prebuilt, mysql_rec); > > + row_mysql_convert_row_to_innobase(node->row, prebuilt, > mysql_rec);//debug > > > > savept = trx_savept_take(trx); > > > > diff --git a/storage/xtradb/row/row0row.cc > b/storage/xtradb/row/row0row.cc > > index be786f9..a1dd465 100644 > > --- a/storage/xtradb/row/row0row.cc > > +++ b/storage/xtradb/row/row0row.cc > > @@ -83,7 +83,7 @@ row_build_index_entry_low( > > entry, dict_index_get_n_unique_in_tree(index)); > > } > > > > - for (i = 0; i < entry_len; i++) { > > + for (i = 0; i < entry_len; i++) { //explore need to see how it > works for simple > > const dict_field_t* ind_field > > = dict_index_get_nth_field(index, i); > > const dict_col_t* col > > > > ---------- Forwarded message ---------- > From: Sachin Setia <sachinsetia1001(a)gmail.com> > To: maria-developers(a)lists.launchpad.net > Cc: > Date: Wed, 13 Apr 2016 21:17:03 +0530 > Subject: Re: [Maria-developers] InnoDB blob for primary key > 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.lindstrom(a)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-pro… > > 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 < > sachinsetia1001(a)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 including the patch file and t4.ibd and t4.frm file > >> Regards > >> sachin > >> > >> > >> > >> _______________________________________________ > >> Mailing list: https://launchpad.net/~maria-developers > >> Post to : maria-developers(a)lists.launchpad.net > >> Unsubscribe : https://launchpad.net/~maria-developers > >> More help : https://help.launchpad.net/ListHelp > >> > > > > > ---------- Forwarded message ---------- > From: Sachin Setia <sachinsetia1001(a)gmail.com> > To: maria-developers(a)lists.launchpad.net > Cc: > Date: Wed, 13 Apr 2016 22:19:44 +0530 > Subject: Re: [Maria-developers] InnoDB blob for primary key > 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 <sachinsetia1001(a)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.lindstrom(a)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-pro… > >> 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 < > sachinsetia1001(a)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 including the patch file and t4.ibd and t4.frm file > >>> Regards > >>> sachin > >>> > >>> > >>> > >>> _______________________________________________ > >>> Mailing list: https://launchpad.net/~maria-developers > >>> Post to : maria-developers(a)lists.launchpad.net > >>> Unsubscribe : https://launchpad.net/~maria-developers > >>> More help : https://help.launchpad.net/ListHelp > >>> > >> > > > ---------- Forwarded message ---------- > From: Sergei Golubchik <serg(a)mariadb.org> > To: Sachin Setia <sachinsetia1001(a)gmail.com> > Cc: > Date: Thu, 14 Apr 2016 20:53:51 +0200 > Subject: Re: [Maria-developers] InnoDB blob for primary key > 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 security(a)mariadb.org > > > ---------- Forwarded message ---------- > From: Sachin Setia <sachinsetia1001(a)gmail.com> > To: maria-developers(a)lists.launchpad.net > Cc: > Date: Fri, 15 Apr 2016 18:37:15 +0530 > Subject: Re: [Maria-developers] InnoDB blob for primary key > 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 <serg(a)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 security(a)mariadb.org > > > ---------- Forwarded message ---------- > From: Sachin Setia <sachinsetia1001(a)gmail.com> > To: maria-developers(a)lists.launchpad.net > Cc: > Date: Sat, 16 Apr 2016 19:37:45 +0530 > Subject: Re: [Maria-developers] InnoDB blob for primary key > 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 <sachinsetia1001(a)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 <serg(a)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 security(a)mariadb.org > > > ---------- Forwarded message ---------- > From: Sachin Setia <sachinsetia1001(a)gmail.com> > To: maria-developers(a)lists.launchpad.net > Cc: > Date: Mon, 18 Apr 2016 20:12:39 +0530 > Subject: Re: [Maria-developers] InnoDB blob for primary key > 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 <sachinsetia1001(a)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 <sachinsetia1001(a)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 <serg(a)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 security(a)mariadb.org >> > > > > ---------- Forwarded message ---------- > From: Sergei Golubchik <serg(a)mariadb.org> > To: Sachin Setia <sachinsetia1001(a)gmail.com> > Cc: maria-developers(a)lists.launchpad.net > Date: Mon, 18 Apr 2016 22:32:25 +0200 > Subject: Re: [Maria-developers] InnoDB blob for primary key > Hi, Sachin! > > On Apr 15, Sachin Setia 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 > > Right. Very good! > > > 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. > > MariaDB supports "virtual columns", see > https://mariadb.com/kb/en/mariadb/virtual-computed-columns/ > > So, it might be enough to mark this hash column as virtual, and will be > automatically calculated when necessary (in fill_record, etc). > > > Current I am not sure how to hide columns from user.Sir, can you suggest > > me where to look > > This would need a new flag per field, like "hidden". And in all commands > that use a list of columns (SHOW, INFORMATION_SCHEMA tables, SELECT *, > INSERT table VALUE (...), etc) - this column should be skipped. > > But don't worry, I don't think this will be a problem for you. You can, > of course, start from not hiding this column and implement this hiddden > flag later. > > > 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. > > Of course. > > > 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. > > I would say, there is no need to bother. Let's just say that UNIQUE for > blobs is only a constraint, can not be used as a primary key, can not be > used as a foreign key. This is a reasonable limitation, I think :) > > Regards, > Sergei > Chief Architect MariaDB > and security(a)mariadb.org > > > ---------- Forwarded message ---------- > From: Sergei Golubchik <serg(a)mariadb.org> > To: Sachin Setia <sachinsetia1001(a)gmail.com> > Cc: maria-developers(a)lists.launchpad.net > Date: Mon, 18 Apr 2016 22:46:42 +0200 > Subject: Re: [Maria-developers] InnoDB blob for primary key > Hi, Sachin! > > On Apr 16, Sachin Setia 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. > > Do you mean, a length of the blob value? > Try field->get_length() > > > 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? > > Well, yes, that would work. > But I see few other features where field hiding might be useful, > and in these cases this simple trick with moving table->field pointer > will not work, unfortunately. > > So we might need something more complex. But, again, this is not a most > important part of this project, so don't start from it. > > In fact, I'm rather interested to know about the adaptive hashing that > you've mentioned. Can you tell me more about it, please? > > > 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 > > @@ -8956,6 +8964,31 @@ fill_record(THD *thd, TABLE *table, Field **ptr, > List<Item> &values, > > goto err; > > field->set_explicit_default(value); > > } > > + table->file->ha_index_init(0,0); > > + res = > table->file->ha_index_read_map(table->record[1],hash_field->ptr,HA_WHOLE_KEY,HA_READ_KEY_EXACT); > > + while(!res){ > > + //compare the record if not sure how to compare it so just > assume it works > > + diff = table->record[1]-table->record[0]; > > + src_length = blob_field->data_length(); > > + //dest_length = > blob_field->data_length(table->record[1]); // i dont know how to get the > length from record 1 > > + // so i am enable to do this > > + // then we can comapare records using > > + //field->cmp_max > > + //this is mysql code > > + /* if (!(table->distinct ? > > + table_rec_cmp(table) : > > + group_rec_cmp(table->group, table->record[0], > table->record[1]))) > > + return false; // skip it > > + res= table->file->ha_index_next_same(table->record[1], > > + table->hash_field->ptr, > > + sizeof(hash)); */ > > + //fetch the next record > > + res= table->file->ha_index_next_same(table->record[1], > > + hash_field->ptr, > > + 8); > > + > > + } > > not quite, you should check unique constraints in fill_record(). This > should happen when the row is actually inserted or updated. A good place > for this check, I'd say, is handler::ha_write_row() and > handler::ha_update_row(). > > Regards, > Sergei > Chief Architect MariaDB > and security(a)mariadb.org > > > ---------- Forwarded message ---------- > From: Sergei Golubchik <serg(a)mariadb.org> > To: Sachin Setia <sachinsetia1001(a)gmail.com> > Cc: maria-developers(a)lists.launchpad.net > Date: Mon, 18 Apr 2016 22:51:57 +0200 > Subject: Re: [Maria-developers] InnoDB blob for primary key > Hi, Sachin! > > On Apr 18, Sachin Setia wrote: > > 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. > > Agree :) > > > 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. > > Mixed feelings. First, I wrote in an earlier email about using virtual > columns for that. In this line of thoughts, a user-specified hash > function is absolutely possible and logical. > > On the other hand, I don't see why anyone would need that - a hash is > not guaranteed to be unique, no matter what algorithm one uses. > > And, as I wrote in an earlier email, I don't think that primary/foreign > keys on blobs is a feature worth spending time on. > > Regards, > Sergei > Chief Architect MariaDB > and security(a)mariadb.org > >

2 6

[Maria-developers] GSoC 2016:Unique indexes for blobs
by Shubham Barai 23 Apr '16

23 Apr '16

Hello everyone, I am Shubham Barai, a 3rd-year undergraduate student in computer science at Maharashtra Institute of technology, Pune, India. 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. MyISAM and innoDB do allow users to create unique indexes on blobs with some limitations on prefix length. So end user can create an index on blobs using "create table" or "create index "statement. example:CREATE TABLE some_table (blob_column BLOB, UNIQUE (blob_column(700))); For MyISAM tables, the prefix limit is 1000 bytes and for innoDB limit is 767 bytes. After creating an index, I ran some queries like "explain select distinct blob_column from some_table" and found out that server executes this query with the help of the temporary table but for other data types like int, the server executes the query with the help of the index. I want to know what exactly is the task for this project.Any help would be greatly appreciated Thank You, Shubham.

4 18

[Maria-developers] Fwd: Do we have anywhere some code that copies from one internal tmp table into another?
by Igor Babaev 23 Apr '16

23 Apr '16

Hi, Please help me if you are aware of such code. In my case the tables are similar. I understand that it's easy to write this code. (Read rows from the source table one by one updating the destination table). Still I would prefer to use the existing code. Regards, Igor.

2 2

Re: [Maria-developers] 745b522: MDEV-8889 - Assertion `next_insert_id == 0' failed in handler::ha_external_lock
by Sergei Golubchik 22 Apr '16

22 Apr '16

Hi, Sergey! I believe this fix is ok... On Apr 21, Sergey Vojtovich wrote: > revision-id: 745b5226e68d376eed709d930f72c3fbc7f07d2a (mariadb-10.0.24-26-g745b522) > parent(s): 072ca71d26487817d025ee97955e6360c3c5c086 > committer: Sergey Vojtovich > timestamp: 2016-04-21 16:51:00 +0400 > message: > > MDEV-8889 - Assertion `next_insert_id == 0' failed in handler::ha_external_lock > > There was a race condition between delayed insert thread and connection thread > actually performing INSERT/REPLACE DELAYED. It was triggered by concurrent > INSERT/REPLACE DELAYED and statements that flush the same table either > explicitely or implicitely (like FLUSH TABLE, ALTER TABLE, ...). > > This race condition was caused by a gap in delayed thread shutdown logic, > which allowed concurrent connection running INSERT/REPLACE DELAYED to change > essential data consequently leaving table in semi-consistent state. > > Specifically query thread could decrease "tables_in_use" reference counter in > this gap, causing delayed insert thread to shutdown without releasing auto > increment and table lock. > > Fixed by extending condition so that delayed insert thread won't shutdown > until there're locked tables. s/until/if/ > Also removed volatile qualifier from tables_in_use and stacked_inserts since > they're supposed to be protected by mutexes. It looks like stacked_inserts is accessed without a mutex at the end of Delayed_insert::handle_inserts(). Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1

Re: [Maria-developers] cd216b1: MDEV-9142 :Adding Constraint with no database reference
by Sergei Golubchik 22 Apr '16

22 Apr '16

Hi, Jan! On Apr 07, Jan Lindström wrote: > revision-id: cd216b1ab577ffe00253ec135d6194051f7ecfa0 (mariadb-5.5.48-15-gcd216b1) > parent(s): 11b77e9b18a8d97063b4c4a96e40bf9c75bd0e8b > committer: Jan Lindström > timestamp: 2016-04-07 10:47:46 +0300 > message: > > MDEV-9142 :Adding Constraint with no database reference > results in ERROR 1046 (3D000) at line 13: No database selected. > > Use database from create table to foreign key database if > nothing else is given. Ok to push Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] failing tests
by Alexander Barkov 21 Apr '16

21 Apr '16

Hi Olivier, Vladislav, Please have a look into MDEV-9739 Assertion `m_status == DA_ERROR || m_status == DA_OK' failed in Diagnostics_area::message() ; connect.xml* tests fail in buildbot The crash appeared after this commit by Vladislav: commit 3a24f1cf8426409a69619982e84ca498d581f8a1 Author: Vladislav Vaintroub <vvaintroub(a)googlemail.com> Date: Mon Feb 22 12:48:03 2016 +0100 MDEV-9307 - provide info about DATA/INDEX directory in INFORMATION_SCHEMA.TA BLES (in CREATE_OPTIONS column) Vladislav thinks that the actual problem resides in the ConnectSE code, in a wrong use of my_error(). Can you please take over the problem from this point? Thanks. On 03/21/2016 12:54 PM, Alexander Barkov wrote: > Hi Olivier, > > > On 03/20/2016 02:10 AM, Olivier Bertrand wrote: >> Hello Alexander, >> >> Le 19/03/2016 14:59, Alexander Barkov a écrit : >>> Hi Olivier, >>> >>> On 03/19/2016 02:18 AM, Olivier Bertrand wrote: >>>> Hello Alexander, >>>> >>>> I went into linux ubuntu and pass the tests but all XML tests were >>>> skipped saying "need libxml2". However I could check was libxml2 was >>>> installed indeed. Don't know what to do next. >>> Please make sure that libxml2-dev is installed. >> It was not and I installed it. However it did not change anything. > > Please make sure to remove CMakeCache.txt and run cmake again from > scratch. Does this help? > > >> The problem is that libxml2 is not recognized as installed when >> compiling Connect and then the XML type is not available. >> (trying to create an XML table fails saying "invalid table type") >> >> Btw, how do the tests fail and for what reason? > > For some reasons it crashes in DEBUG build when doing this query: > > > SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE > TABLE_SCHEMA='test' AND TABLE_NAME='t1' AND ENGINE='CONNECT' > AND CREATE_OPTIONS LIKE '%`table_type`=XML%' AND CREATE_OPTIONS > LIKE '%xmlsup=libxml2%' > > > with this stack trace: > > > sql/sql_error.h:705(Diagnostics_area::message() const)[0x563787dda10a] > sql/sql_select.cc:2669(JOIN::exec_inner())[0x563787dd97b5] > sql/sql_select.cc:3453(mysql_select(THD*, Item***, TABLE_LIST*, unsigned > int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, > st_order*, unsigned long long, select_result*, st_select_lex_unit*, > st_select_lex*))[0x563787ddcd1b] > sql/sql_select.cc:384(handle_select(THD*, LEX*, select_result*, unsigned > long))[0x563787dd2811] > sql/sql_parse.cc:5936(execute_sqlcom_select)[0x563787da2ae4] > sql/sql_parse.cc:2962(mysql_execute_command(THD*))[0x563787d98625] > sql/sql_parse.cc:7336(mysql_parse(THD*, char*, unsigned int, > Parser_state*))[0x563787da615d] > sql/sql_parse.cc:1490(dispatch_command(enum_server_command, THD*, char*, > unsigned int))[0x563787d94846] > sql/sql_parse.cc:1109(do_command(THD*))[0x563787d93576] > sql/sql_connect.cc:1349(do_handle_one_connection(THD*))[0x563787ec8a6c] > sql/sql_connect.cc:1262(handle_one_connection)[0x563787ec87bb] > perfschema/pfs.cc:1862(pfs_spawn_thread)[0x56378827a1da] > /lib64/libpthread.so.0(+0x760a)[0x7f427b8aa60a] > /lib64/libc.so.6(clone+0x6d)[0x7f427959fa4d] > > > Can you please try to compile a DEBUG version on Linux and check > if you get the same problem? > > In the meanwhile I'll try to find the commit which caused this problem. > > Thanks. > > >>> >>> >>>> Olivier >>>> >>>> Le 18/03/2016 14:46, Alexander Barkov a écrit : >>>>> Hello Olivier, >>>>> >>>>> a few tests are failing in the main 10.1 tree on Linux: >>>>> >>>>> connect.xml >>>>> connect.xml_grant >>>>> connect.xml_html >>>>> connect.xml_mdev5261 >>>>> connect.xml_mult >>>>> >>>>> Can you please have a look? >>>>> >>>>> Thanks! >>

3 4