April 2016 - developers - lists.mariadb.org

[Maria-developers] Dynamic column expression limitat - 252 characters
by Sergey Petrunia 21 Apr '16

21 Apr '16

https://mariadb.com/kb/en/mariadb/virtual-computed-columns/ says the expression cannot exceed 252 characters in length. I've got a complaint about this limitation. Do we have any plans to remove it? (Does Oracle have it in their variant of this feature?) BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

3 2

[Maria-developers] MDEV-9372 select 100 between 1 and 9223372036854775808 returns false
by Alexander Barkov 20 Apr '16

20 Apr '16

Hi Sergei, Please review a patch for MDEV-9372. Thanks.

2 1

Re: [Maria-developers] [Commits] 308cee547f6573a9825b5f08f938d4452d0095d9 Fixed bug mdev-9931.
by Oleksandr Byelkin 20 Apr '16

20 Apr '16

Hi, Igor! On 20.04.2016 00:37, Igor Babaev wrote: > commit 308cee547f6573a9825b5f08f938d4452d0095d9 > Author: Igor Babaev <igor(a)askmonty.org> > Commit: Igor Babaev <igor(a)askmonty.org> > > Fixed bug mdev-9931. > > When the specification of a WITH table referred to a view > that used a based table with the same name as the WITH table > the server went into an infinite loop because it erroneously > resolved the reference to the base table as the reference to > the WITH table. > > With tables used in a view cannot be searched for beyond the > scope the view. > --- It looks OK to push. I'd name it 'is_view_border' (because it can be more units which belon to a view), but if you think 'is_view' is better, OK to leave it as is. > mysql-test/r/cte_nonrecursive.result | 13 +++++++++++++ > mysql-test/t/cte_nonrecursive.test | 14 ++++++++++++++ > sql/sql_cte.cc | 5 ++++- > sql/sql_lex.cc | 1 + > sql/sql_lex.h | 1 + > sql/sql_view.cc | 2 ++ > 6 files changed, 35 insertions(+), 1 deletions(-) > > diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result > index df64115..a9c13f3 100644 > --- a/mysql-test/r/cte_nonrecursive.result > +++ b/mysql-test/r/cte_nonrecursive.result > @@ -746,3 +746,16 @@ with t(f1,f1) as (select * from t1 where b >= 'c') > select t1.b from t2,t1 where t1.a = t2.c; > ERROR 42S21: Duplicate column name 'f1' > drop table t1,t2; > +# > +# Bug mdev-9937: View used in the specification of with table > +# refers to the base table with the same name > +# > +create table t1 (a int); > +insert into t1 values (20), (30), (10); > +create view v1 as select * from t1 where a > 10; > +with t1 as (select * from v1) select * from t1; > +a > +20 > +30 > +drop view v1; > +drop table t1; > diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test > index 5a6e07e..e3164f5 100644 > --- a/mysql-test/t/cte_nonrecursive.test > +++ b/mysql-test/t/cte_nonrecursive.test > @@ -434,3 +434,17 @@ with t(f1,f1) as (select * from t1 where b >= 'c') > select t1.b from t2,t1 where t1.a = t2.c; > > drop table t1,t2; > + > +--echo # > +--echo # Bug mdev-9937: View used in the specification of with table > +--echo # refers to the base table with the same name > +--echo # > + > +create table t1 (a int); > +insert into t1 values (20), (30), (10); > +create view v1 as select * from t1 where a > 10; > + > +with t1 as (select * from v1) select * from t1; > + > +drop view v1; > +drop table t1; > diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc > index 1203a4c..77f0bcf 100644 > --- a/sql/sql_cte.cc > +++ b/sql/sql_cte.cc > @@ -512,7 +512,10 @@ bool With_element::prepare_unreferenced(THD *thd) > { > With_clause *with_clause=sl->get_with_clause(); > if (with_clause && (found= with_clause->find_table_def(table))) > - return found; > + return found; > + /* Do not look for the table's definition beyond the scope of the view */ > + if (sl->master_unit()->is_view) > + break; > } > return found; > } > diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc > index 6056b03..de345b4 100644 > --- a/sql/sql_lex.cc > +++ b/sql/sql_lex.cc > @@ -2078,6 +2078,7 @@ void st_select_lex_unit::init_query() > found_rows_for_union= 0; > insert_table_with_stored_vcol= 0; > derived= 0; > + is_view= false; > with_clause= 0; > with_element= 0; > columns_are_renamed= false; > diff --git a/sql/sql_lex.h b/sql/sql_lex.h > index 0b4f0da..10247bd 100644 > --- a/sql/sql_lex.h > +++ b/sql/sql_lex.h > @@ -645,6 +645,7 @@ class st_select_lex_unit: public st_select_lex_node { > derived tables/views handling. > */ > TABLE_LIST *derived; > + bool is_view; > /* With clause attached to this unit (if any) */ > With_clause *with_clause; > /* With element where this unit is used as the specification (if any) */ > diff --git a/sql/sql_view.cc b/sql/sql_view.cc > index 41fd5b7..b66f678 100644 > --- a/sql/sql_view.cc > +++ b/sql/sql_view.cc > @@ -1612,6 +1612,8 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, > sl->context.error_processor_data= (void *)table; > } > > + table->select_lex->master_unit()->is_view= true; > + > /* > check MERGE algorithm ability > - algorithm is not explicit TEMPORARY TABLE > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

[Maria-developers] MDEV-9745 Crash with CASE WHEN TRUE THEN COALESCE(CAST(NULL AS UNSIGNED)) ELSE 4 END
by Alexander Barkov 19 Apr '16

19 Apr '16

Hello Sergei, please review a patch for MDEV-9745. This is actually a backport of MDEV-9653 from 10.1 to 5.5. Thanks.

2 1

Re: [Maria-developers] 21265d5: MDEV-9943 - TokuDB fails to compile with gcc 5.2.1
by Sergei Golubchik 19 Apr '16

19 Apr '16

Hi, Sergey! On Apr 19, Sergey Vojtovich wrote: > revision-id: 21265d58c1ea65f5c21a56cf669321f39066d7b5 (mariadb-5.5.48-19-g21265d5) > parent(s): 6c0e231c0282b43d6a46a4983f5971e960d3b8ca > committer: Sergey Vojtovich > timestamp: 2016-04-19 16:16:13 +0400 > message: > > MDEV-9943 - TokuDB fails to compile with gcc 5.2.1 > > For some reason check_cxx_compiler_flag() passes result variable name down to > compiler: > https://github.com/Kitware/CMake/blob/master/Modules/CheckCXXSourceCompiles… > > But compiler doesn't permit dashes in macro name, like in > -DHAVE_CXX_-fimplicit-templates. > > Workarounded by renaming HAVE_CXX_-fimplicit-templates to > HAVE_CXX_IMPLICIT_TEMPLAES. > > diff --git a/storage/tokudb/ft-index/cmake_modules/TokuSetupCompiler.cmake b/storage/tokudb/ft-index/cmake_modules/TokuSetupCompiler.cmake > index 99629e4..4ae7b63 100644 > --- a/storage/tokudb/ft-index/cmake_modules/TokuSetupCompiler.cmake > +++ b/storage/tokudb/ft-index/cmake_modules/TokuSetupCompiler.cmake > @@ -103,8 +103,8 @@ set_cflags_if_supported( > > if (CMAKE_CXX_FLAGS MATCHES -fno-implicit-templates) > # must append this because mysql sets -fno-implicit-templates and we need to override it > - check_cxx_compiler_flag(-fimplicit-templates HAVE_CXX_-fimplicit-templates) > - if (HAVE_CXX_-fimplicit-templates) > + check_cxx_compiler_flag(-fimplicit-templates HAVE_CXX_IMPLICIT_TEMPLATES) > + if (HAVE_CXX_IMPLICIT_TEMPLATES) > set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -fimplicit-templates") > endif () > endif() I'd rather fix other similar places too. If you agree, ok to push with the patch below. --- a/storage/tokudb/ft-index/cmake_modules/TokuSetupCompiler.cmake +++ b/storage/tokudb/ft-index/cmake_modules/TokuSetupCompiler.cmake @@ -61,12 +61,13 @@ endmacro(set_cflags_if_supported_named) ## adds a compiler flag if the compiler supports it macro(set_cflags_if_supported) foreach(flag ${ARGN}) - check_c_compiler_flag(${flag} HAVE_C_${flag}) - if (HAVE_C_${flag}) + STRING(REGEX REPLACE "[-,= ]" "_" res ${flag}) + check_c_compiler_flag(${flag} HAVE_C_${res}) + if (HAVE_C_${res}) set(CMAKE_C_FLAGS "${flag} ${CMAKE_C_FLAGS}") endif () - check_cxx_compiler_flag(${flag} HAVE_CXX_${flag}) - if (HAVE_CXX_${flag}) + check_cxx_compiler_flag(${flag} HAVE_CXX_${res}) + if (HAVE_CXX_${res}) set(CMAKE_CXX_FLAGS "${flag} ${CMAKE_CXX_FLAGS}") endif () endforeach(flag) @@ -75,8 +76,9 @@ endmacro(set_cflags_if_supported) ## adds a linker flag if the compiler supports it macro(set_ldflags_if_supported) foreach(flag ${ARGN}) - check_cxx_compiler_flag(${flag} HAVE_${flag}) - if (HAVE_${flag}) + STRING(REGEX REPLACE "[-,= ]" "_" res ${flag}) + check_cxx_compiler_flag(${flag} HAVE_${res}) + if (HAVE_${res}) set(CMAKE_EXE_LINKER_FLAGS "${flag} ${CMAKE_EXE_LINKER_FLAGS}") set(CMAKE_SHARED_LINKER_FLAGS "${flag} ${CMAKE_SHARED_LINKER_FLAGS}") endif () Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1

Re: [Maria-developers] 6c0e231: MDEV-9945 - main.kill_processlist-6619 fails sporadically
by Sergei Golubchik 19 Apr '16

19 Apr '16

Hi, Sergey! On Apr 19, Sergey Vojtovich wrote: > revision-id: 6c0e231c0282b43d6a46a4983f5971e960d3b8ca (mariadb-5.5.48-18-g6c0e231) > parent(s): 18ff6f654b1ca595fc5c0eebddbc1d0c5ee0d09e > committer: Sergey Vojtovich > timestamp: 2016-04-19 14:05:52 +0400 > message: > > MDEV-9945 - main.kill_processlist-6619 fails sporadically > > SHOW PROCESSLIST output can be affected by not completed concurrent queries. > Removed this affected SHOW PROCESSLIST since it doesn't seem to affect original > problem. ok to push Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] 18ff6f6: MDEV-9944 - main.events_2 fails sporadically
by Sergei Golubchik 19 Apr '16

19 Apr '16

Hi, Sergey! On Apr 19, Sergey Vojtovich wrote: > revision-id: 18ff6f654b1ca595fc5c0eebddbc1d0c5ee0d09e (mariadb-5.5.48-17-g18ff6f6) > parent(s): 6fd54c01bb5bc480497b143d63181837148ff47f > committer: Sergey Vojtovich > timestamp: 2016-04-19 12:38:00 +0400 > message: > > MDEV-9944 - main.events_2 fails sporadically > > Fixed wait condition in test case to actually wait for get_lock() completion > (not for lock acquisition as it was before). This removes sporadic extra row in > subsequent processlist queries. ok to push Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

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

18 Apr '16

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

2 5

[Maria-developers] Deadlock with STOP SLAVE and LOCK_active_mi
by Kristian Nielsen 18 Apr '16

18 Apr '16

So while looking at MDEV-9573, I found this code in mysql_execute_command() for STOP SLAVE: mysql_mutex_lock(&LOCK_active_mi); if ((mi= (master_info_index-> get_master_info(&lex_mi->connection_name, Sql_condition::WARN_LEVEL_ERROR)))) if (!stop_slave(thd, mi, 1/* net report*/)) my_ok(thd); mysql_mutex_unlock(&LOCK_active_mi); So basically, this code is holding LOCK_active_mi for the entire duration of the STOP SLAVE operation. This seems completely broken. Anything in a slave thread that tries to take LOCK_active_mi will then deadlock with the STOP SLAVE operation. It is simple enough to trigger, testcase (with sleep-injecting patch) at the end of the email. This uses INFORMATION_SCHEMA.SESSSION_STATUS; I could imagine accessing a number of system variables could trigger it as well. >From a quick check, it looks like this has been like this forever, eg. 5.1 has similar code. 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? - Kristian. ----------------------------------------------------------------------- diff --git a/sql/mysqld.cc b/sql/mysqld.cc index ced9225..2358d6b 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -7765,6 +7765,7 @@ static int show_heartbeat_period(THD *thd, SHOW_VAR *var, char *buff, var->type= SHOW_CHAR; var->value= buff; +if (thd->slave_thread) my_sleep(5000000); mysql_mutex_lock(&LOCK_active_mi); if (master_info_index) { ----------------------------------------------------------------------- --source include/have_innodb.inc --source include/master-slave.inc --connection master CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; --save_master_pos --connection slave --sync_with_master --source include/stop_slave.inc --connection master INSERT INTO t1 SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='COM_COMMIT'; --save_master_pos --connection slave --source include/start_slave.inc # Wait a bit to hit a sleep() in code after taking LOCK_active_mi. SELECT SLEEP(2); STOP SLAVE; SELECT * FROM t1 ORDER BY a; START SLAVE; --sync_with_master SELECT * FROM t1 ORDER BY a; # Clean up. --connection master DROP TABLE t1; --source include/rpl_end.inc -----------------------------------------------------------------------

2 2

[Maria-developers] MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null
by Alexander Barkov 18 Apr '16

18 Apr '16

Hi Sergei, Please review a patch for MDEV-9521. Thanks.

2 3