- developers - lists.mariadb.org

[Maria-developers] MDEV-11829 RFC3339 Support
by Seth Shelnutt 05 Mar '17

05 Mar '17

Hello, This is in response to the comments on the initial pull request I made for adding RFC3339 support [1]. The proposal is to add support for parsing RFC3339 and then use the given timezone offset to shift the datetime. I think most users would expect the behavior to be converted to the timezone of the mariadb server / session as defined in THD. Most timezone operations are depended on the session specified timezone, whether that is set or inherited from global timezone, or system timezone. In order to convert from RFC3339 timezone to session timezone, we will need to return the timezone from the str_to_datetime function. As mentioned there is no timezone member of the MY_TIME struct. I believe a change to the MY_TIME struct would be the most beneficial and cleanest approach. This would allow for handling RFC3339 and future additions, such as a datetime with time zone field, similar to the ansi timestamp with time zone. The struct is used in a several ways, including packing into longlong, all of those cases would need to be handled. This would be a much larger and more involved patchset. An alternative approach, that is less involved, would be use the MYSQL_TIME_STATUS struct to indicate that a timezone offset exists. An offset field and indicator could be added right to the status struct, or a new warning can be used to indicate it exists in the raw string. Then the caller of str_to_datetime can handle the timezone conversion. A third approach, would be to change the function signature of str_to_datetime and provide a variable to store the offset in, if exists. I'm sure what the general stance is on altering entire function calls, especially one that is older and quite important. With any approach, the timezone could then be handled outside of my_time.c, where we have THD and the timezone class, which can easily handle the conversion. I will start working on this once I receive some feedback on the general direction that would result in an acceptable patchset. [1] https://github.com/MariaDB/server/pull/290 Thank you, Seth Shelnutt

3 4

[Maria-developers] About help on mysqltest
by Amol Holani 03 Mar '17

03 Mar '17

Hi, I'm amol holani, computer science student from College of Engineering, Pune(COEP, India). I would love to contribute to project related to mysqltest (MDEV-12130) . Can you explain more about ad hoc parser. Also, How can I get some documentation about mysqltest? -- Regards, Amol Holani

2 1

Re: [Maria-developers] [Commits] 00c30e5: MDEV-11645: archive.archive fails in buildbot with valgrind (Use of uninitialised value)
by Sergey Vojtovich 03 Mar '17

03 Mar '17

Hi Varun, On Wed, Mar 01, 2017 at 07:50:01AM +0530, Varun wrote: > revision-id: 00c30e5a234fbd01484cd069c01e97c075025323 (mariadb-10.2.3-283-g00c30e5) > parent(s): b13cee83f9cfcffa98d227c492411a9acec85f42 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-03-01 07:46:26 +0530 > message: > > MDEV-11645: archive.archive fails in buildbot with valgrind (Use of uninitialised value) > > Set null values to record_buffer->buffer for the VARCHAR fields It would be nice to elaborate where uninitialized data comes from. Am I right that it comes from record? It would be nice to explain why NULL values and CHAR type are not affected. As well as why 10.1 is unaffected, ideally point to revision that caused this. > > --- > storage/archive/ha_archive.cc | 15 +++++++++++++++ > 1 file changed, 15 insertions(+) > > diff --git a/storage/archive/ha_archive.cc b/storage/archive/ha_archive.cc > index 9d6d100..5599b43 100644 > --- a/storage/archive/ha_archive.cc > +++ b/storage/archive/ha_archive.cc > @@ -372,10 +372,25 @@ int Archive_share::write_v1_metafile() > > unsigned int ha_archive::pack_row_v1(uchar *record) > { > + uint offset; Why you declare offset here... > uint *blob, *end; > uchar *pos; > DBUG_ENTER("pack_row_v1"); > memcpy(record_buffer->buffer, record, table->s->reclength); > + for(Field** field= table->field; (*field) ; field++) > + { > + Field *fld= *field; > + if (fld->type() == MYSQL_TYPE_VARCHAR) > + { > + if (!(fld->is_real_null(record - table->record[0]))) > + { > + ptrdiff_t start= (fld->ptr - table->record[0]); > + Field_varstring *const field_var= (Field_varstring *)fld; > + offset= field_var->data_length() + field_var->length_size(); And use it here? Why not declare it inline with start and field_var? > + bzero(record_buffer->buffer + start + offset, fld->field_length - offset + 1); According to man bzero: This function is deprecated (marked as LEGACY in POSIX.1-2001): use memset(3) in new programs. POSIX.1-2008 removes the specification of bzero(). Please use memset() instead. Regards, Sergey

1 0

[Maria-developers] Is it a big? CURSOR does not see changes made by ALTER TABLE
by Alexander Barkov 02 Mar '17

02 Mar '17

Hello Elena, all. I noticed that CURSORs in combination with ALTER TABLE work strange. Is it a known bug? Thanks. -- I create a table with two columns -- And a procedure fetching two columns from this table SET sql_mode=DEFAULT; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT, b VARCHAR(10)); INSERT INTO t1 VALUES (10,20); DROP PROCEDURE IF EXISTS p1; DELIMITER $$ CREATE PROCEDURE p1() BEGIN DECLARE a,b INT; DECLARE cur CURSOR FOR SELECT * FROM t1; OPEN cur; FETCH cur INTO a,b; CLOSE cur; SELECT a,b; END; $$ DELIMITER ; CALL p1(); -- It returns -- +------+------+ -- | a | b | -- +------+------+ -- | 10 | 20 | -- +------+------+ -- So far so good -- Now I add a new column to the table and call the procedure again ALTER TABLE t1 ADD c INT; CALL p1(); -- Woops. It should have failed, because there are now 3 columns in the table -- But it returns the result, like if the table still had 2 columns.

1 1

Re: [Maria-developers] [svoj@mariadb.org: [Commits] 02d155a: MDEV-11418 - AliSQL: [Feature] Issue#1 KILL IDLE TRANSACTIONS]
by Michael Widenius 01 Mar '17

01 Mar '17

Hi! On Tue, Feb 21, 2017 at 2:37 PM, Sergey Vojtovich <svoj(a)mariadb.org> wrote: > Hi Monty, > > Please review this patch. > > Thanks, > Sergey > > ----- Forwarded message from Sergey Vojtovich <svoj(a)mariadb.org> ----- > > Date: Tue, 21 Feb 2017 16:29:36 +0400 > From: Sergey Vojtovich <svoj(a)mariadb.org> > To: commits(a)mariadb.org > Subject: [Commits] 02d155a: MDEV-11418 - AliSQL: [Feature] Issue#1 KILL IDLE TRANSACTIONS > > revision-id: 02d155aeec87aac8a827a29328d68ecb156094cc (mariadb-10.2.2-162-g02d155a) > parent(s): 1563354570c5634cb2b448b47a138b4ceaff1a72 > committer: Sergey Vojtovich > timestamp: 2017-02-21 16:28:42 +0400 > message: > > MDEV-11418 - AliSQL: [Feature] Issue#1 KILL IDLE TRANSACTIONS > > Terminate idle transactions safely in server layer by setting up socket timeout > parameter. Percona provides another patch to resolve similar problem, but it > calls server layer's callback in InnoDB plugin to close THD, which crashes in > some testcases. See https://bugs.launchpad.net/percona-server/+bug/901060 for > more detailed information. > > 1. export parameter trx_idle_timeout to handle all kinds of transactions, the priority is highest > 2. export parameter trx_readonly_idle_timeout to handle read-only transactions > 3. export parameter trx_changes_idle_timeout to handle read-write transactions <cut> > +BEGIN; > +INSERT INTO t1 VALUES (4),(5),(6); > +SELECT * FROM t1; > +ERROR HY000: MySQL server has gone away It's not optimal to get this kind of error for a timeout as it's make it's very hard for the application to know what is going on. An alternative approach would be to instead of closing the connection do a rollback and for next query send an error "transactions aborted and rolled back because of transaction timeout" What do you think about this approach? To get this done, we should remember in 'net' that we are using a transaction timeout (not default) and when we get the timeout we should do: - Do part of the rollback code + unlock tables in THD::cleanup() (Probably move to a sub function) - Remember that we did a timeout rollback. - When we got next statement and timout_rollback was done, give an error, clear flag and wait for next statement. > +++ b/sql/sql_parse.cc > @@ -1209,7 +1209,23 @@ bool do_command(THD *thd) > number of seconds has passed. > */ > if(!thd->skip_wait_timeout) > - my_net_set_read_timeout(net, thd->variables.net_wait_timeout); > + { > + if (thd->in_active_multi_stmt_transaction()) > + { > + bool is_trx_rw= thd->transaction.all.is_trx_read_write(); > + > + if (thd->variables.trx_idle_timeout > 0) > + my_net_set_read_timeout(net, thd->variables.trx_idle_timeout); > + else if (thd->variables.trx_readonly_idle_timeout > 0 && !is_trx_rw) > + my_net_set_read_timeout(net, thd->variables.trx_readonly_idle_timeout); > + else if (thd->variables.trx_changes_idle_timeout > 0 && is_trx_rw) > + my_net_set_read_timeout(net, thd->variables.trx_changes_idle_timeout); > + else > + my_net_set_read_timeout(net, thd->variables.net_wait_timeout); > + } > + else > + my_net_set_read_timeout(net, thd->variables.net_wait_timeout); > + } Please do it this way instead (less code): if (!thd->skip_wait_timeout) { ulong timeout= thd->variables.net_wait_timeout; if (thd->in_active_multi_stmt_transaction()) { bool is_trx_rw= thd->transaction.all.is_trx_read_write(); if (thd->variables.trx_idle_timeout > 0) timeout= thd->variables.trx_idle_timeout; else if (thd->variables.trx_readonly_idle_timeout > 0 && !is_trx_rw) timeout= thd->variables.trx_readonly_idle_timeout; else if (thd->variables.trx_changes_idle_timeout > 0 && is_trx_rw) timeout= thd->variables.trx_changes_idle_timeout; } my_net_set_read_timeout(net, timeout); } Regards, Monty

1 0

Re: [Maria-developers] [Commits] 00c30e5: MDEV-11645: archive.archive fails in buildbot with valgrind (Use of uninitialised value)
by Sergey Petrunia 01 Mar '17

01 Mar '17

Hi Varun, Please * fix the commit comment * add a comment * fix the coding style as indicated below. (and also please conform to the coding style in the future. If its requirements are not clear, lets sit down once and discuss them) On Wed, Mar 01, 2017 at 07:50:01AM +0530, Varun wrote: > revision-id: 00c30e5a234fbd01484cd069c01e97c075025323 (mariadb-10.2.3-283-g00c30e5) > parent(s): b13cee83f9cfcffa98d227c492411a9acec85f42 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-03-01 07:46:26 +0530 > message: > > MDEV-11645: archive.archive fails in buildbot with valgrind (Use of uninitialised value) > > Set null values to record_buffer->buffer for the VARCHAR fields What "NULL values"?? The fix is filling with zeros the space beyond the end of VARCHAR values. VARCHAR NULLs are actually ok already as NULL value has its buffer filled with zeros. Please come up with a comment that describes the ix. > > --- > storage/archive/ha_archive.cc | 15 +++++++++++++++ > 1 file changed, 15 insertions(+) > > diff --git a/storage/archive/ha_archive.cc b/storage/archive/ha_archive.cc > index 9d6d100..5599b43 100644 > --- a/storage/archive/ha_archive.cc > +++ b/storage/archive/ha_archive.cc > @@ -372,10 +372,25 @@ int Archive_share::write_v1_metafile() > > unsigned int ha_archive::pack_row_v1(uchar *record) > { > + uint offset; > uint *blob, *end; > uchar *pos; > DBUG_ENTER("pack_row_v1"); > memcpy(record_buffer->buffer, record, table->s->reclength); Please add here a comment about what this loop is doing. > + for(Field** field= table->field; (*field) ; field++) space needed in 'for (' > + { > + Field *fld= *field; > + if (fld->type() == MYSQL_TYPE_VARCHAR) > + { > + if (!(fld->is_real_null(record - table->record[0]))) > + { > + ptrdiff_t start= (fld->ptr - table->record[0]); > + Field_varstring *const field_var= (Field_varstring *)fld; > + offset= field_var->data_length() + field_var->length_size(); > + bzero(record_buffer->buffer + start + offset, fld->field_length - offset + 1); The above line is longer than 80 chars. should be broken into: bzero(record_buffer->buffer + start + offset, fld->field_length - offset + 1); > + } > + } > + } > pos= record_buffer->buffer + table->s->reclength; > for (blob= table->s->blob_field, end= blob + table->s->blob_fields; > blob != end; blob++) -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] e8ca684: MDEV-11221 - main.events_restart failed in bb
by Sergei Golubchik 28 Feb '17

28 Feb '17

Hi, Sergey! Thanks! Ok to push! On Feb 28, Sergey Vojtovich wrote: > revision-id: e8ca684c6d5ce14eb021b4b691be50a8bf9c8281 (mariadb-10.0.29-38-ge8ca684) > parent(s): b54566d73bb96ef9eb7d734742d3743fb3369a40 > committer: Sergey Vojtovich > timestamp: 2017-02-28 15:23:44 +0400 > message: > > MDEV-11221 - main.events_restart failed in bb > > Applied lost in a merge revision: next time, please specify a commit you're applying. Like Applied commit 7f38a070baa that was lost in a merge: > MDEV-10043 - main.events_restart fails sporadically in buildbot (crashes upon > shutdown) > > There was race condition between shutdown thread and event worker threads. > > Shutdown thread waits for thread_count to become 0 in close_connections(). It > may happen so that event worker thread was started but didn't increment > thread_count by this time. In this case shutdown thread may miss wait for this > working thread and continue deinitialization. Worker thread in turn may continue > execution and crash on deinitialized data. > > Fixed by incrementing thread_count before thread is actually created like it is > done for connection threads. > > Also let event scheduler not to inc/dec running threads counter for symmetry > with other "service" threads. > Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] [Commits] b13cee8: MDEV-11078: NULL NOT IN (non-empty subquery) should never return results
by Sergei Golubchik 27 Feb '17

27 Feb '17

Hi, Varun! Really, copy-pasted *8* times? Why didn't you make it a method of Item_in_subselect? On Feb 27, Varun wrote: > revision-id: b13cee83f9cfcffa98d227c492411a9acec85f42 (mariadb-10.2.3-282-gb13cee8) > parent(s): b513e3711743e3ff24b3d14794a0fba87fd18c31 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-02-27 22:10:35 +0530 > message: > > MDEV-11078: NULL NOT IN (non-empty subquery) should never return results > > Setting the value of cond guard variables during the creation of Tricond Item for null values > > --- > mysql-test/r/subselect4.result | 34 +++++++++++++++++++++++++++++ > mysql-test/t/subselect4.test | 20 +++++++++++++++++ > sql/item_subselect.cc | 49 ++++++++++++++++++++++++++++++++++++++++++ > 3 files changed, 103 insertions(+) > > diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc > index 94bc71c..acdbabd 100644 > --- a/sql/item_subselect.cc > +++ b/sql/item_subselect.cc > @@ -2113,6 +2113,13 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > We can encounter "NULL IN (SELECT ...)". Wrap the added condition > within a trig_cond. > */ > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > + > item= new (thd->mem_root) Item_func_trig_cond(thd, item, get_cond_guard(0)); > } > > @@ -2137,6 +2144,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > having= new (thd->mem_root) Item_is_not_null_test(thd, this, having); > if (left_expr->maybe_null) > { > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > if (!(having= new (thd->mem_root) Item_func_trig_cond(thd, having, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2155,6 +2168,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > */ > if (!abort_on_null && left_expr->maybe_null) > { > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > if (!(item= new (thd->mem_root) Item_func_trig_cond(thd, item, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2184,6 +2203,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > (char *)"<result>")); > if (!abort_on_null && left_expr->maybe_null) > { > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > if (!(new_having= new (thd->mem_root) Item_func_trig_cond(thd, new_having, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2383,6 +2408,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > Item_cond_or(thd, item_eq, item_isnull); > if (!abort_on_null && left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(col_item= new (thd->mem_root) > Item_func_trig_cond(thd, col_item, get_cond_guard(i)))) > DBUG_RETURN(true); > @@ -2400,6 +2431,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > (char *)"<list ref>")); > if (!abort_on_null && left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(item_nnull_test= > new (thd->mem_root) > Item_func_trig_cond(thd, item_nnull_test, get_cond_guard(i)))) > @@ -2460,6 +2497,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > item= new (thd->mem_root) Item_cond_or(thd, item, item_isnull); > if (left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(item= new (thd->mem_root) > Item_func_trig_cond(thd, item, get_cond_guard(i)))) > DBUG_RETURN(true); > @@ -2471,6 +2514,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > } > if (!abort_on_null && left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(item= new (thd->mem_root) > Item_func_trig_cond(thd, item, get_cond_guard(i)))) > DBUG_RETURN(true); Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] On constructing ref access from mis-matched charset comparisons
by Sergey Petrunia 27 Feb '17

27 Feb '17

Hi Alexander, I've got a question about mis-matched charset comparisons and ref access method. == Short form == I know that VARCHAR comparisons over mis-matching charsets cannot be used for constructing index lookups into the index over the "narrower" character set column. But is this a real limitation or just an optimizer deficiency? If it is the latter, do people hit it in the real world sufficiently often to warrant lifting it? == Long form == === Example dataset === create table t0 ( a varchar(32) character set utf8 collate utf8_general_ci ); create table t1 ( a varchar(32) character set latin1 collate latin1_swedish_ci, col1 varchar(32), key(a) ); create table t2 ( a varchar(32) character set utf8 collate utf8_general_ci, col1 varchar(32), key(a) ); insert into t0 select a from ten; insert into t1 select a,a from one_k; insert into t2 select a,a from one_k; === Queries === MariaDB [test]> explain -> select * from t0, t2 where t0.a=t2.a; +------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+ | 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | SIMPLE | t2 | ref | a | a | 99 | test.t0.a | 1 | | +------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+ Ok, good. MariaDB [test]> explain -> select * from t0, t1 where t0.a=t1.a; +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ 2 rows in set (0.00 sec) Oops, the index t1(a) is not used. FORMAT=JSON shows the reason: "attached_condition": "(t0.a = convert(t1.a using utf8))" Still it feels like the optimizer could * Try to convert the value of t0.a into latin1. * If it can't be represented in latin1, then we know that no row in t1 has t1.a=t0.a. * make a lookup on t1.a= convert(t0.a using latin1) , using t1.a's collation rules. Is this true [for some charsets] ? If yes, is it worth doing? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

3 2

[Maria-developers] bb-10.2-compatibility : delete compatibility
by jerome brauge 27 Feb '17

27 Feb '17

Hello Alexander, Can you add a task for delete statement with same table as source and target in sql_mode=oracle Example : create or replace table t1 (c1 int, c2 int); delete from t1 where c1 in (select b.c1 from t1 b where b.c2=0); Failed with : Error: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data SQLState: HY000 ErrorCode: 1093 Regards, Jérôme.

2 1