- developers - lists.mariadb.org

[Maria-developers] Aggregate stored functions [MDEV-7773]
by Varun Gupta 19 Mar '16

19 Mar '16

Well i have checked out the syntax for the databases for aggregate functions. I thought using the syntax similar to that of HSQL would be good. In it if we are having N tuples then we are computing the values for the N rows and then after that we make another call and return the value for the function. Well this seems very reasonable to do. Using PostgreSQL <http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html> we need to have two regular functions but if we have an aggregate function like Count(column_name) , then this would be done in just a single function , I guess we would not need two functions for it . Regards, Varun

2 11

Re: [Maria-developers] b1a1e5b: MDEV-6058 MySQL Bug #11766693: LOG-SLOW-ADMIN-STATEMENTS AND LOG-SLOW-SLAVE-STATEMENTS NOT DISPLAYED.
by Sergei Golubchik 18 Mar '16

18 Mar '16

Hi, Alexey! On Mar 18, Alexey Botchkov wrote: > revision-id: b1a1e5b3d3d5ab95c596580de07daf38a3efa039 (mariadb-10.1.12-19-gb1a1e5b) > parent(s): 4aac51db9a55a11574cbfa60484d4329d35b6f2c > committer: Alexey Botchkov > timestamp: 2016-03-18 11:47:05 +0400 > message: > > MDEV-6058 MySQL Bug #11766693: LOG-SLOW-ADMIN-STATEMENTS AND LOG-SLOW-SLAVE-STATEMENTS NOT DISPLAYED. > These parameters were moved from the command line options to the > system variables section. Treatment of the > opt_log_slow_slave_statements changed to let the dynamic change > of the variable. > > --- > mysql-test/r/variables.result | 10 ++ > mysql-test/suite/rpl/r/rpl_slow_query_log.result | 10 ++ > mysql-test/suite/rpl/t/rpl_slow_query_log.test | 43 ++++++++ > .../r/log_slow_admin_statements_basic.result | 76 ++++++++++++++ > .../r/log_slow_admin_statements_func.result | 46 +++++++++ > .../r/log_slow_slave_statements_basic.result | 76 ++++++++++++++ > .../t/log_slow_admin_statements_basic.test | 110 +++++++++++++++++++++ > .../sys_vars/t/log_slow_admin_statements_func.test | 61 ++++++++++++ > .../t/log_slow_slave_statements_basic.test | 110 +++++++++++++++++++++ > mysql-test/t/variables.test | 11 +++ I suppose you didn't run the full test suite. Otherwise you would've noticed that sysvar_* results were changed. Also, because we have sysvar_* tests, we no longer create <varname>_basic tests for every new variable, they are no longer useful. Starting from 10.1 you only need to test the functionality of new variables, not their basic properties. > sql/log_event.cc | 10 ++ > sql/mysqld.cc | 8 -- > sql/sys_vars.cc | 13 +++ > 13 files changed, 576 insertions(+), 8 deletions(-) > > diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result > index fef3e4a..e46e65f 100644 > --- a/mysql-test/r/variables.result > +++ b/mysql-test/r/variables.result > @@ -1797,3 +1797,13 @@ select * from information_schema.session_variables where variable_name='sql_mode > VARIABLE_NAME VARIABLE_VALUE > SQL_MODE ANSI_QUOTES > End of 5.5 tests > +show variables like 'log_slow%statements'; > +Variable_name Value > +log_slow_admin_statements OFF > +log_slow_slave_statements OFF > +select * from information_schema.global_variables where variable_name like 'log_slow_admin_statements'; > +VARIABLE_NAME VARIABLE_VALUE > +LOG_SLOW_ADMIN_STATEMENTS OFF > +select * from information_schema.global_variables where variable_name like 'log_slow_slave_statements'; > +VARIABLE_NAME VARIABLE_VALUE > +LOG_SLOW_SLAVE_STATEMENTS OFF You can remove this test. > diff --git a/mysql-test/suite/rpl/t/rpl_slow_query_log.test b/mysql-test/suite/rpl/t/rpl_slow_query_log.test > index faf037a..de336fc 100644 > --- a/mysql-test/suite/rpl/t/rpl_slow_query_log.test > +++ b/mysql-test/suite/rpl/t/rpl_slow_query_log.test > @@ -299,6 +299,49 @@ if ($master_slow_query == $slave_slow_query) > -- echo ### same number of queries in slow log: $master_slow_query > } > > +-- echo ******************************************************************** > +-- echo **** TRUNCATE the slow log then check whether runtime changes of > +-- echo **** log_slow_slave_statements work without slave restart. > +-- echo ******************************************************************** > + > +SET @old_log_slow_slave_statements= @@global.log_slow_slave_statements; > +SET @@global.log_slow_slave_statements = off; > +TRUNCATE mysql.slow_log; > + > +-- connection master > + > +--disable_warnings > +-- eval $slow_query; > +--enable_warnings > +sync_slave_with_master; > + > +-- connection slave > + > +let $slave_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`; > +if ($slave_slow_query) > +{ > + SELECT * FROM mysql.slow_log; > + die "Assertion failed! Slow query FOUND in slow query log. Bailing out!"; > +} please, NO! If you want to test whether a certain row is present in a certain table you do SELECT columns FROM table; may be with a WHERE clause. But never if (`SELECT`) { die; } > + > +SET @@global.log_slow_slave_statements = on; > + > +-- connection master > + > +--disable_warnings > +-- eval $slow_query; > +--enable_warnings > +sync_slave_with_master; > + > +-- connection slave > + > +let $slave_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`; > +if (!$slave_slow_query) > +{ > + SELECT * FROM mysql.slow_log; > + die "Assertion failed! Slow query NOT FOUND in slow query log. Bailing out!"; > +} Same. > + > -- connection master > SET @@global.log_output= @old_log_output; > SET @@global.long_query_time= @old_long_query_time; > diff --git a/mysql-test/suite/sys_vars/r/log_slow_admin_statements_basic.result b/mysql-test/suite/sys_vars/r/log_slow_admin_statements_basic.result > new file mode 100644 > index 0000000..1cd9135 > --- /dev/null > +++ b/mysql-test/suite/sys_vars/r/log_slow_admin_statements_basic.result Remove this useless test, please > diff --git a/mysql-test/suite/sys_vars/r/log_slow_slave_statements_basic.result b/mysql-test/suite/sys_vars/r/log_slow_slave_statements_basic.result > new file mode 100644 > index 0000000..b9e88ce > --- /dev/null > +++ b/mysql-test/suite/sys_vars/r/log_slow_slave_statements_basic.result > @@ -0,0 +1,76 @@ And this one, too. > diff --git a/mysql-test/suite/sys_vars/t/log_slow_admin_statements_func.test b/mysql-test/suite/sys_vars/t/log_slow_admin_statements_func.test > new file mode 100644 > index 0000000..78e4d35 > --- /dev/null > +++ b/mysql-test/suite/sys_vars/t/log_slow_admin_statements_func.test > + > +# ALTER, OPTIMIZE, CHECK and DROP operations should be logged in slow query log. > +SELECT sql_text FROM mysql.slow_log WHERE sql_text LIKE '%TABLE log_slow_admin_statements%'; this is how one checks whether a query was logged. > diff --git a/sql/log_event.cc b/sql/log_event.cc > index e99ef16..9793f7d 100644 > --- a/sql/log_event.cc > +++ b/sql/log_event.cc > @@ -4428,6 +4428,16 @@ int Query_log_event::do_apply_event(rpl_group_info *rgi, > if (thd->m_digest != NULL) > thd->m_digest->reset(thd->m_token_array, max_digest_length); > > + if (thd->slave_thread && > + (thd->variables.sql_log_slow != opt_log_slow_slave_statements)) > + { > + /* > + The log_slow_slave_statements variable can be changed dynamically, > + so we have to set the sql_log_slow respectively. > + */ > + thd->variables.sql_log_slow= opt_log_slow_slave_statements; > + } Really? Why not if (thd->slave_thread) thd->variables.sql_log_slow= opt_log_slow_slave_statements; Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 2

[Maria-developers] failing tests
by Alexander Barkov 18 Mar '16

18 Mar '16

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!

2 1

[Maria-developers] Please review MDEV-9653 Assertion `length || !scale' failed in uint my_decimal_length_to_precision(uint, uint, bool)
by Alexander Barkov 18 Mar '16

18 Mar '16

Hello Sergei, Please review a patch for: MDEV-9653 Assertion `length || !scale' failed in uint my_decimal_length_to_precision(uint, uint, bool) Thanks!

2 6

Re: [Maria-developers] 72b709a: MDEV-9701: CREATE VIEW with GROUP BY or ORDER BY and constant produces invalid definition
by Sergei Golubchik 17 Mar '16

17 Mar '16

Hi, Sanja! On Mar 16, OleksandrByelkin wrote: > revision-id: 72b709ac7503ae6dd2b5e9049322fefb90b0ebbe (mariadb-10.1.12-16-g72b709a) > parent(s): 9b53d84d14a9b031d193f6beae382a232aa738e3 > committer: Oleksandr Byelkin > timestamp: 2016-03-16 19:49:17 +0100 > message: > > MDEV-9701: CREATE VIEW with GROUP BY or ORDER BY and constant produces invalid definition > > Fixed printing integer constant in the ORDER clause (MySQL solution) > Removed workaround for double resolving counter in the ORDER. > > --- > mysql-test/r/view.result | 19 +++++++++++++++++++ > mysql-test/t/view.test | 17 +++++++++++++++++ > sql/sql_lex.cc | 32 ++++++++++++-------------------- > sql/sql_select.cc | 6 +++++- > sql/sql_union.cc | 11 +++++++++++ > 5 files changed, 64 insertions(+), 21 deletions(-) > > diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result > index 8ee72c3..d4a2a9b 100644 > diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc > index fd84109..31fc5f9 100644 > --- a/sql/sql_lex.cc > +++ b/sql/sql_lex.cc > @@ -2689,30 +2689,22 @@ void st_select_lex::print_order(String *str, > { > if (order->counter_used) > { > - if (!(query_type & QT_VIEW_INTERNAL)) > - { > char buffer[20]; > size_t length= my_snprintf(buffer, 20, "%d", order->counter); > str->append(buffer, (uint) length); you can replace these three lines with str->append_ulonglong(order->counter); > } > else > { > - /* replace numeric reference with expression */ > + /* replace numeric reference with equivalent for ORDER constant */ > if (order->item[0]->type() == Item::INT_ITEM && > order->item[0]->basic_const_item()) > { > - char buffer[20]; > - size_t length= my_snprintf(buffer, 20, "%d", order->counter); > - str->append(buffer, (uint) length); > /* make it expression instead of integer constant */ > - str->append(STRING_WITH_LEN("+0")); > + str->append(STRING_WITH_LEN("''")); > } > else > (*order->item)->print(str, query_type); > } > - } > - else > - (*order->item)->print(str, query_type); > if (!order->asc) > str->append(STRING_WITH_LEN(" desc")); > if (order->next) > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index fff24b9..f14e8b1 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -21874,7 +21874,11 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, > */ > if (order_item->type() == Item::INT_ITEM && order_item->basic_const_item()) > { /* Order by position */ > - uint count= (uint) order_item->val_int(); > + uint count; > + if (order->counter_used) how can this happen? > + count= order->counter; // counter was once resolved > + else > + count= (uint) order_item->val_int(); > if (!count || count > fields.elements) > { > my_error(ER_BAD_FIELD_ERROR, MYF(0), > diff --git a/sql/sql_union.cc b/sql/sql_union.cc > index c835083..8145cec 100644 > --- a/sql/sql_union.cc > +++ b/sql/sql_union.cc > @@ -1161,11 +1161,22 @@ List<Item> *st_select_lex_unit::get_unit_column_types() > return &sl->item_list; > } > > + > +static void cleanup_order(ORDER *order) > +{ > + for (; order; order= order->next) > + order->counter_used= 0; > +} > + > + > bool st_select_lex::cleanup() > { > bool error= FALSE; > DBUG_ENTER("st_select_lex::cleanup()"); > > + cleanup_order(order_list.first); > + cleanup_order(group_list.first); why is it needed? > if (join) > { > DBUG_ASSERT((st_select_lex*)join->select_lex == this); Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 3

Re: [Maria-developers] [Commits] 1e542ec: MDEV-9382: After updating mariadb server apt-configure fails
by Sergei Golubchik 17 Mar '16

17 Mar '16

Hi, Nirbhay! Ok to push. And, please, check whether /etc/default/mariadb needs to be mentioned in the corresponding (mariadb on debian/ubuntu) knowledgebase pages. On Mar 16, Nirbhay Choubey wrote: > revision-id: 1e542ecd8af6f78db767a9b3da016b38d1ec3970 (mariadb-10.1.12-6-g1e542ec) > parent(s): b3bde93149e7135507fd7abbca2786d77552953d > author: Nirbhay Choubey > committer: Nirbhay Choubey > timestamp: 2016-03-16 19:39:19 -0400 > message: > > MDEV-9382: After updating mariadb server apt-configure fails > > When acting as a Galera receiver node, server startup may take > more than 30 secs (the current default) as it has to wait for > SST/IST operation to complete besides spending some time doing > wsrep recovery. > > Fixed by raising the default value of MYSQLD_STARTUP_TIMEOUT > to 60 secs. Also sourced /etc/default/mariadb into the init > script so that it can be used to set MYSQLD_STARTUP_TIMEOUT. > Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] NO PAD Collation for MariaDB
by Melissa Rose 17 Mar '16

17 Mar '16

Hello everyone, I am Melisa Rose from University of Leeds UK and I wish to contribute to MariaDB under GSoC 2016. I am interested in the project involving adding NO PAD collations to default character sets(MDEV-9711 <https://jira.mariadb.org/browse/MDEV-9711>). Apparently, this project does not require thorough understanding of the Server Internal code and the Instructions highlighted by Alexander Barkov on how to implement this are straight forward. I will be submitting a proposal for this project hoping i'm linked to the assigned mentor soon. Look forward to an awesome summer with MariaDB. Kind regards, Melisa Rose.

2 3

Re: [Maria-developers] [Maria-discuss] Collations, trailing spaces and unique indexes
by Alexander Barkov 17 Mar '16

17 Mar '16

Hi Binarus, On 03/16/2016 06:13 PM, Binarus wrote: >> I just wanted to split the task into these steps: >> >> 1. Default and _bin collations. >> This is what MDEV-9711 is about. >> >> 2. Unicode Collation Algorithm (UCA) based collations >> (e.g. utf8_unicode_nopad_ci) >> >> >> 3. The rest (not covered by the previous steps) >> This will include latin1_general_nopad_cs, latin2_czech_nopad_cs >> >> >> This is why asked in the previous letter if utf8_unicode_nopad_ci would >> work for you. >> Also, which MariaDB version do you need this for? Will 10.1 work? >> > > Thank you very much for this explanation. > > Regarding the version: Our O/S usually is Debian which generally uses software which is quite old. This means that I will have to compile MariaDB myself in every case. > The current project where this new collations are needed will probably take about three months before being finished. It would be very nice if we could have the new collations until then. > In the meantime, we'll continue development with MySQL and switch to MariaDB as soon as the new collations are available. Excellent. The fact that you compile from sources makes the things easier. I guess we can add new collations officially into 10.2. Then after 3 month if you'll feel that 10.2 is not mature enough for you, we can make a custom non-official 10.1 tree with the new collations back-ported (it should be trivial to do). > > ci collations would be sufficient as a starting point. Nevertheless, I think there must be a nopad collation for every collation which already exists. > Personally, since we are using utf8 (and no other encoding) in every part of every project, I would prefer to have *all utf8* nopad collations before >having any other nopad collation. But that's just my personal opinion. >We will be grateful for any progress regardless of the order of things being implemented. Thanks. Then we're flexible to implement #2 first, and wait for #1 to be done under terms of Google Summer Of Code 2016. > > Binarus > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp >

1 0

Re: [Maria-developers] [Maria-discuss] Collations, trailing spaces and unique indexes
by Binarus 17 Mar '16

17 Mar '16

Alexander, I'd like to continue our conversation from Maria-discuss. >> - I have found my_strnxfrm_unicode in the same file and will need more time to make my opinion of how difficult it will be >> (I don't know what a weight is, so I currently try to understand what the function does at all). > > This function is used to create sort keys for non-indexed ORDER BY, > for these cases: > > - ORDER BY on an expression > - ORDER BY on a column that does not have an index > > > The idea is exactly the same with the C function strxfrm. > See "man strxfrm". > > The code implements non-indexed ORDER BY in filesort.cc > as follows: > > 1. It calls *_strnxfrm_* functions for all records and converts > CHAR/VARCHAR/TEXT values into their fixed length binary sortable keys. > > 2 . Then executes binary sorting on these keys. > > By the way, fixing this function might be tricky. > > > Currently my_strnxfrm_unicode() pads the tail using weights of the SPACE > character. > The NO PAD version will need to pad the tail using a weight which is > less than the weight of the smallest possible character. > > This should be easy for UCA bases collations (e.g. > utf8_unicode_nopad_ci), because the smallest possible > character in UCA collations is "U+0009 HORIZONTAL TABULATION", > and its weight is 0x0201. So we can just pad the sort key > using a smaller value 0x0200. > > > But I'm not sure yet what to do with 8-bit collations, > which usually use 0x00 as weight for the smallest character. > So we don't have a smaller value. > There are two options here: > > 1. Pad with 0x00. But this will mean that 'aaa<min>' and just 'aaa' > will have unpredictable order when doing ORDER BY without an index > (where <min> is the smallest possible character in the collation). > > As the smallest character in non-UCA collations is usually > "U+0000 NULL", this will mean that 'aaa\0' and just 'aaa' > will have unpredictable order. > > 2. Reserve extra bytes at the end of the key, to store the true length, so > - 'aaa\0' will have the key '4141410004' > - 'aaa' will have the key '4141410003', and therefore will always > be sorted before 'aaa\0'. > > I'm inclined towards #2, to have consistent ORDER BY behavior > with and without indexes. Wow, thanks for explaining. I now have learned that I probably can't assist you in solving that problem (I haven't been in unicode, weights, sorting and character sets very deep until now). But if it helps, you eventually could break the problem into two steps: 1) Fix the problem with unique indexes 2) Fix ordering for any possible situation The problems we have encountered in real life so far are all related to 1), i.e. we would be happy if unique indexes would consider 'a' and 'a ' being different values. In contrast, ordering is not that important for us. I don't know what others are thinking about that, though. >> >> - My main problem: I did not find my_strnncollsp_utf8_general_ci anywhere (nor in the same neither in any other file). Where is it? > > The function name is just "my_strnncollsp_utf8". OK, thank you very much. I must have misunderstood something. >> Furthermore, studying the code has led to some questions; for example, there already seems to be a #define which controls the padding-when-comparing mode, but only for the _cs collations? > > Can you please clarify which lines do you mean? I am referring to the file ctype-utf8.c, line 5698. There is: #ifndef VARCHAR_WITH_DIFF_ENDSPACE_ARE_DIFFERENT_FOR_UNIQUE diff_if_only_endspace_difference= 0; #endif I haven't been able yet to thoroughly analyze where and how this is used, but at least somebody obviously already had some thoughts whether or not trailing spaces should make a difference in UNIQUEs :-) Regards, Binarus

2 1

Re: [Maria-developers] bf2eaa7: MDEV-9382: After updating mariadb server apt-configure fails
by Sergei Golubchik 16 Mar '16

16 Mar '16

Hi, Nirbhay! On Mar 02, Nirbhay Choubey wrote: > revision-id: bf2eaa7c1548f07ce5de13a406ff9183f082b8b3 (mariadb-10.1.12-4-gbf2eaa7) > parent(s): fd741c1a12e78035dcef9a5684b530fe09cbfdde > author: Nirbhay Choubey > committer: Nirbhay Choubey > timestamp: 2016-03-02 09:41:03 -0500 > message: > > MDEV-9382: After updating mariadb server apt-configure fails > > When acting as a Galera receiver node, server startup may take > more than 30 secs (the current default) as it has to wait for > SST/IST operation to complete besides spending some time doing > wsrep recovery. > > In specific case of this MDEV, the server startup timedout as > it took ~48 secs for the node to fully start. > > A viable workaround would be to adjust the MYSQLD_STARTUP_TIMEOUT > env to a higher value, but raising the default to 60 secs should > fix scenarios like these. > > --- > debian/mariadb-server-10.1.mysql.init | 2 +- > 1 file changed, 1 insertion(+), 1 deletion(-) > > diff --git a/debian/mariadb-server-10.1.mysql.init b/debian/mariadb-server-10.1.mysql.init > index 9e098b4..005d0e8 100644 > --- a/debian/mariadb-server-10.1.mysql.init > +++ b/debian/mariadb-server-10.1.mysql.init > @@ -109,7 +109,7 @@ case "${1:-''}" in > /usr/bin/mysqld_safe "${@:2}" > /dev/null 2>&1 & > > # 6s was reported in #352070 to be too little > - for i in $(seq 1 "${MYSQLD_STARTUP_TIMEOUT:-30}"); do > + for i in $(seq 1 "${MYSQLD_STARTUP_TIMEOUT:-60}"); do > sleep 1 This doesn't fix much. Next time somebody will need 61 second and you'll adjust to 120? SST might take many minutes and it's not a good idea to have every user wait this long. Instead you should do what MDEV-8509 says. Add a line like [ -r /etc/default/mariadb ] && . /etc/default/mariadb then one can have a very personal setting of MYSQLD_STARTUP_TIMEOUT in /etc/default/mariadb Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

3 3