- developers - lists.mariadb.org

[Maria-developers] -Wnon-virtual-dtor and scripts in
by Alexander Barkov 08 Dec '17

08 Dec '17

Hello Vlad, Are we still supposed to use these scripts in the BUILD directory: compile-pentium64-debug compile-pentium64-debug-all compile-pentium64-debug-max compile-pentium64-gcov compile-pentium64-gprof compile-pentium64-max compile-pentium64-valgrind-max I stopped using them long time ago. But these scripts set some extra warnings for gcc, so now it's easy to overlook some warnings. Monty is still using these scripts and gets those extra warnings. In particular, -Wnon-virtual-dtor is often a subject of warnings. Is there a way to enable the -Wnon-virtual-dtor for gcc in the default build, i.e. when just using "cmake" instead of BUILD scripts? Thanks!

2 2

Re: [Maria-developers] 78a1779: mysqldump fix for invisible column
by Sergei Golubchik 07 Dec '17

07 Dec '17

Hi, Sachin! Looks fine. Two comments: 1. optimization idea, see below 2. add a test case that actually restores from the dump (and checks that it was restored correctly). mysqldump.test has examples, search for $MYSQL. After that - please push! On Dec 07, sachin wrote: > revision-id: 78a1779f29ed8d2a96658583065270de8bdd14b4 (mariadb-10.3.2-61-g78a1779) > parent(s): e8c8300a1c7e7e2b99e98c5c6986df69943cb661 > author: Sachin Setiya > committer: Sachin Setiya > timestamp: 2017-12-07 16:09:16 +0530 > message: > > mysqldump fix for invisible column > > Actually there are 2 issues in the case of invisible columns > > 1st `select fields from t1` will have more fields then `select * from t1`. > So instead of `select * from t1` we are using `select a,b,invisible from t1` > these fields are supplied from `select fields from t1`. > > 2nd We are using --complete-insert when we detect that this table is using > invisible columns. > > diff --git a/client/mysqldump.c b/client/mysqldump.c > index a260065..818bc07 100644 > --- a/client/mysqldump.c > +++ b/client/mysqldump.c > @@ -2971,6 +2979,12 @@ static uint get_table_structure(char *table, char *db, char *table_type, > DBUG_RETURN(0); > } > > + while ((row= mysql_fetch_row(result))) > + { > + if (strlen(row[SHOW_EXTRA]) && strstr(row[SHOW_EXTRA],"INVISIBLE")) > + complete_insert= 1; > + } > + mysql_data_seek(result, 0); > /* > If write_data is true, then we build up insert statements for > the table's data. Note: in subsequent lines of code, this test > @@ -3006,10 +3020,16 @@ static uint get_table_structure(char *table, char *db, char *table_type, > { > dynstr_append_checked(&insert_pat, ", "); > } > - init=1; > dynstr_append_checked(&insert_pat, > quote_name(row[SHOW_FIELDNAME], name_buff, 0)); > } > + if (init) > + { > + dynstr_append_checked(&select_field_names, ", "); > + } > + dynstr_append_checked(&select_field_names, > + quote_name(row[SHOW_FIELDNAME], name_buff, 0)); > + init=1; I think, you can do it with just one loop: while ((row= mysql_fetch_row(result))) { if (strlen(row[SHOW_EXTRA]) && strstr(row[SHOW_EXTRA],"INVISIBLE")) complete_insert= 1; if (init) dynstr_append_checked(&select_field_names, ", "); dynstr_append_checked(&select_field_names, quote_name(row[SHOW_FIELDNAME], name_buff, 0)); init=1; } if (complete_insert) dynstr_append_checked(&insert_pat, select_field_names->str); > } > num_fields= mysql_num_rows(result); > mysql_free_result(result); Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] 339dd3d: Invisible Column mysqldump bug fix
by Sergei Golubchik 07 Dec '17

07 Dec '17

Hi, Sachin! Summary: 1. Add a test case, it should include a mix of tables with and without invisible columns, and columns with strange names (reserved words, spaces). 2. Always use full SELECT syntax, even if the table has no invisible columns. On Dec 05, sachin wrote: > revision-id: 339dd3d7508a1e5c7c014b5a59ed99ff0ad96542 (mariadb-10.3.2-59-g339dd3d) > parent(s): 6ed3374c8a67ae64448783ab107d1a11cb87f40c > author: Sachin Setiya > committer: Sachin Setiya > timestamp: 2017-12-05 05:35:28 +0530 > message: > > Invisible Column mysqldump bug fix > > --- > client/mysqldump.c | 46 +++++++++++++++++++++++++++++++++++++++++----- > 1 file changed, 41 insertions(+), 5 deletions(-) > > diff --git a/client/mysqldump.c b/client/mysqldump.c > index a260065..e59b203 100644 > --- a/client/mysqldump.c > +++ b/client/mysqldump.c > @@ -115,7 +115,7 @@ static my_bool verbose= 0, opt_no_create_info= 0, opt_no_data= 0, opt_no_data_m > opt_events= 0, opt_comments_used= 0, > opt_alltspcs=0, opt_notspcs= 0, opt_logging, > opt_drop_trigger= 0 ; > -static my_bool insert_pat_inited= 0, debug_info_flag= 0, debug_check_flag= 0; > +static my_bool insert_pat_inited= 0, debug_info_flag= 0, debug_check_flag= 0, invisble_table= 0; typo, *invisible* > static ulong opt_max_allowed_packet, opt_net_buffer_length; > static MYSQL mysql_connection,*mysql=0; > static DYNAMIC_STRING insert_pat; > @@ -2726,7 +2726,7 @@ static uint get_table_structure(char *table, char *db, char *table_type, > complete_insert= 0; > if ((write_data= !(*ignore_flag & IGNORE_DATA))) > { > - complete_insert= opt_complete_insert; > + invisble_table= complete_insert= opt_complete_insert; > if (!insert_pat_inited) > { > insert_pat_inited= 1; > @@ -2971,6 +2971,12 @@ static uint get_table_structure(char *table, char *db, char *table_type, > DBUG_RETURN(0); > } > > + while ((row= mysql_fetch_row(result))) > + { > + if (strlen(row[SHOW_EXTRA]) && strstr(row[SHOW_EXTRA],"INVISIBLE")) > + invisble_table= complete_insert= opt_complete_insert= 1; I think, you need to set invisible_table flag per table, just as you do now, but you should not change complete_insert or opt_complete_insert. That is, if a database has many tables, some with invisible columns and some have only normal columns - tables with invisible columns should use complete_insert, while other tables should not. Basically, INSERT should contain column names if (invisible_table || opt_complete_insert) > + } > + mysql_data_seek(result, 0); > /* > If write_data is true, then we build up insert statements for > the table's data. Note: in subsequent lines of code, this test > @@ -3617,7 +3623,7 @@ static void dump_table(char *table, char *db) > { > char ignore_flag; > char buf[200], table_buff[NAME_LEN+3]; > - DYNAMIC_STRING query_string; > + DYNAMIC_STRING query_string, select_field_names; > char table_type[NAME_LEN]; > char *result_table, table_buff2[NAME_LEN*2+3], *opt_quoted_table; > int error= 0; > @@ -3687,7 +3693,25 @@ static void dump_table(char *table, char *db) > verbose_msg("-- Sending SELECT query...\n"); > > init_dynamic_string_checked(&query_string, "", 1024, 1024); > + init_dynamic_string_checked(&select_field_names, "", 1024, 1024); > > + if (invisble_table) > + { > + /* > + Copy field name from insert_pat into select_field_names because > + select * from table will leave hidden columns. > + We will search for "(" in insert_pat and will copy the whole data > + into select_field_names , and then we will search for ")" in > + select_field_names and decrese the lenght accordingly. > + */ > + char *ptr= insert_pat.str; > + while ( *ptr++ != '(' ){} > + dynstr_append_checked(&select_field_names, ptr); > + ptr= &select_field_names.str[select_field_names.length - 1]; > + while ( *ptr-- != ')') > + select_field_names.length--; > + select_field_names.str[select_field_names.length- 1]= 0; please, check how INSERT code is doing it, you need to quote column names. > + } > if (path) > { > char filename[FN_REFLEN], tmp_path[FN_REFLEN]; > @@ -3708,7 +3732,13 @@ static void dump_table(char *table, char *db) > > /* now build the query string */ > > - dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ * INTO OUTFILE '"); > + dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ "); > + if (invisble_table) > + dynstr_append_checked(&query_string, select_field_names.str); > + else > + dynstr_append_checked(&query_string, "*"); I don't think you need to bother, you can always have a complete column list here. It should not break anything. > + dynstr_append_checked(&query_string, " INTO OUTFILE '"); > + dynstr_free(&select_field_names); > dynstr_append_checked(&query_string, filename); > dynstr_append_checked(&query_string, "'"); > > @@ -3757,7 +3787,13 @@ static void dump_table(char *table, char *db) > "\n--\n-- Dumping data for table %s\n--\n", > fix_for_comment(result_table)); > > - dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ * FROM "); > + dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ "); > + if (invisble_table) > + dynstr_append_checked(&query_string, select_field_names.str); > + else > + dynstr_append_checked(&query_string, "*"); same > + dynstr_free(&select_field_names); > + dynstr_append_checked(&query_string, " FROM "); > dynstr_append_checked(&query_string, result_table); > > if (where) > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 1

Re: [Maria-developers] 0f47171: MDEV-14005 Remove need for Partition Key to be part of Primary Key.
by Sergei Golubchik 06 Dec '17

06 Dec '17

Hi, Alexey! On Dec 04, Alexey Botchkov wrote: > revision-id: 0f4717118e135c178fc1b12065e33ac8a5e21d0b (mariadb-10.3.2-82-g0f47171) > parent(s): ddac2d7a1ef725361c7e83b819cb0bec72db024a > committer: Alexey Botchkov > timestamp: 2017-12-04 17:45:13 +0400 > message: > > MDEV-14005 Remove need for Partition Key to be part of Primary Key. > > Now we check all partitions for the unique keys if necessary > in ha_partition::write_row and ha_partition::update_row. > > diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc > index 6bebf5d..12a6287 100644 > --- a/sql/ha_partition.cc > +++ b/sql/ha_partition.cc > @@ -372,6 +372,7 @@ void ha_partition::init_handler_variables() > part_share= NULL; > m_new_partitions_share_refs.empty(); > m_part_ids_sorted_by_num_of_records= NULL; > + m_cu_errkey= (uint) -1; > > #ifdef DONT_HAVE_TO_BE_INITALIZED > m_start_key.flag= 0; > @@ -4088,6 +4089,79 @@ void ha_partition::try_semi_consistent_read(bool yes) > } > > > +int ha_partition::check_files_for_key(uchar *key, int n_key, > + int partition_to_skip, int *res) > +{ > +// uint num_subparts= part_info->num_subparts; > + for (uint n=0; n < m_part_info->num_parts; n++) > + { > + handler *f= m_file[n]; > + int ires; > + > + if ((int) n == partition_to_skip) > + continue; > + > + f->active_index= n_key; > + if ((*res= f->extra(HA_EXTRA_KEYREAD)) || > + (*res= f->ha_index_init(n_key, FALSE))) > + return 1; > + > + *res= f->index_read_map(m_part_info->table->record[0], > + key, HA_WHOLE_KEY, HA_READ_KEY_EXACT); use index_read_idx_map here > + ires= f->ha_index_end(); > + > + if (*res == HA_ERR_KEY_NOT_FOUND) > + continue; > + > + if (*res == 0) > + *res= ires ? ires : HA_ERR_FOUND_DUPP_KEY; > + > + m_last_part= n; > + m_cu_errkey= n_key; > + return 1; > + } > + > + *res= 0; > + return 0; > +} > + > + > +int ha_partition::check_uniques_insert(uchar *buf, > + int partition_to_skip, int *res) > +{ > + uint n_key; > + > + for (n_key=0; n_key < m_part_info->n_uniques_to_check; n_key++) > + { can you use key_copy() here? > + uchar *cbuf= m_part_info->unique_key_buf[0]; > + KEY *ckey= m_part_info->uniques_to_check[n_key]; > + uint n; > + > + for (n=0; n < ckey->user_defined_key_parts; n++) > + { > + const KEY_PART_INFO *cpart= ckey->key_part + n; > + uint maybe_null= MY_TEST(cpart->null_bit); > + Field *f= cpart->field; > + my_ptrdiff_t ofs= buf-table->record[0]; > + > + f->move_field_offset(ofs); > + if (maybe_null) > + cbuf[0]= f->is_null(); > + f->get_key_image(cbuf+maybe_null, cpart->length, Field::itRAW); > + cbuf+= cpart->store_length; > + f->move_field_offset(-ofs); > + } > + > + if (check_files_for_key(m_part_info->unique_key_buf[0], n_key, > + partition_to_skip, res)) > + return 1; > + } > + > + *res= 0; > + return 0; > +} > + > + > /**************************************************************************** > MODULE change record > ****************************************************************************/ > @@ -4199,6 +4273,11 @@ int ha_partition::write_row(uchar * buf) > error= HA_ERR_NOT_IN_LOCK_PARTITIONS; > goto exit; > } > + > + if (table->part_info->n_uniques_to_check && > + check_uniques_insert(buf, part_id, &error)) > + goto exit; This would _probably_ introduce the race condition I've mentioned in a comment to MDEV-14005. Say, you have a table with two partitions and a unique key. Now, two threads try to insert rows, the first thread inserts 1 into the first partition (there are more columns in a table and a partitioning key on those columns, but I omit these details for simplicity). And a second thread inserts 1 into the second partition. So, the first thread checks for 1 in the second partition, finds nothing. Second thread checks for 1 in the first partition, finds nothing. Then the first thread inserts 1 into the first partition, second thread inserts 1 into the second partition. Monty's idea was to insert optimistically - first you insert, then check for duplicates. Not ideal, in the above example you can have both threads getting a duplicate key error. There could be other, e.g. locking, solutions too. > m_last_part= part_id; > DBUG_PRINT("info", ("Insert in partition %d", part_id)); > start_part_bulk_insert(thd, part_id); Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] MDEV-13995 MAX(timestamp) returns a wrong result near DST change
by Alexander Barkov 04 Dec '17

04 Dec '17

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

1 0

[Maria-developers] Review for MDEV-8320 Allow index usage for DATE(datetime_column) = const
by Alexander Barkov 30 Nov '17

30 Nov '17

Hello Sergey, I generally like your patch for MDEV-8320: http://lists.askmonty.org/pipermail/commits/2017-November/011680.html However, there is one problem with it. We're changing the code to have data type plugins. Every data type plugin will provide its own list of SQL functions. Those data type specific functions should be able to use the same optimization approach without having to change the server code. Therefore, everything related to YEAR(field) transformation should reside inside Item_func_year, everything related to DATE(field) transformation should reside inside Item_date_typecast. I reorganized the code to implement this idea. Note, also I changed the logic behind DATE(indexed_date_field) transformation slightly. The DATE() just gets removed. DATE(indexed_date_field) = const is changed to: indexed_date_field = const instead of: indexed_date_field BETWEEN ... AND ... Please have a look: sargable_date_cond.result is slightly different in my version. Greetings.

1 1

[Maria-developers] Delete vs multi_delete performance issue
by jerome brauge 29 Nov '17

29 Nov '17

Hi All, I've a question on kind of delete on whose we have performance issues. Consider two tables: CREATE TABLE `gapfl` ( `pgegapfl` varchar(150) COLLATE utf8_bin NOT NULL, `blkgapfl` varchar(30) COLLATE utf8_bin NOT NULL, `fldgapfl` varchar(30) COLLATE utf8_bin NOT NULL, `ordgapfl` int(11) NOT NULL, ... `stlgapfl` varchar(30) COLLATE utf8_bin DEFAULT NULL, UNIQUE KEY `gapfl_fld` (`pgegapfl`,`blkgapfl`,`fldgapfl`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin and CREATE TABLE `gapge` ( `nuugapge` varchar(150) COLLATE utf8_bin NOT NULL, `nuigapge` int(11) NOT NULL, `frmgapge` varchar(30) COLLATE utf8_bin NOT NULL, `prsgapge` varchar(60) COLLATE utf8_bin NOT NULL, `typgapge` varchar(2) COLLATE utf8_bin NOT NULL, `catgapge` varchar(1) COLLATE utf8_bin NOT NULL, `numgapge` int(11) NOT NULL, `aplgapge` varchar(8) COLLATE utf8_bin DEFAULT NULL, `tragapge` varchar(8) COLLATE utf8_bin DEFAULT NULL, ... `mafgapge` varchar(1) COLLATE utf8_bin NOT NULL, UNIQUE KEY `gapge_nuu` (`nuugapge`), UNIQUE KEY `gapge_frm` (`frmgapge`,`prsgapge`,`typgapge`,`numgapge`,`tragapge`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin Table "gapfl " contains 364740 rows Table "gapge " contains 25423 rows The following delete tooks more than 38 seconds to delete 0 rows . DELETE FROM gapfl WHERE pgegapfl IN ( SELECT nuugapge FROM gapge WHERE frmgapge = 'AMCEVA' AND prsgapge = 'QUALIAC.*.*') It explain plan is : +------+--------------------+-------+-----------------+---------------------+-----------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-------+-----------------+---------------------+-----------+---------+------+--------+-------------+ | 1 | PRIMARY | gapfl | ALL | NULL | NULL | NULL | NULL | 356417 | Using where | | 2 | DEPENDENT SUBQUERY | gapge | unique_subquery | gapge_nuu,gapge_frm | gapge_nuu | 452 | func | 1 | Using where | +------+--------------------+-------+-----------------+---------------------+-----------+---------+------+--------+-------------+ It's not really good and I don't understand why the subselect is consider as a "DEPENDENT SUBQUERY" (there is no correlation columns). Now, I convert this delete into a multi_delete : DELETE gapfl FROM gapfl WHERE pgegapfl IN ( SELECT nuugapge FROM gapge WHERE frmgapge = 'AMCEVA' AND prsgapge = 'QUALIAC.*.*') It tooks 0.00 second and it explain plan is : +------+-------------+-------+------+---------------------+-----------+---------+--------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------------+-----------+---------+--------------------+------+--------------------------+ | 1 | PRIMARY | gapge | ref | gapge_nuu,gapge_frm | gapge_frm | 274 | const,const | 1 | Using where; Using index | | 1 | PRIMARY | gapfl | ref | gapfl_fld | gapfl_fld | 452 | dvt.gapge.nuugapge | 7 | | +------+-------------+-------+------+---------------------+-----------+---------+--------------------+------+--------------------------+ as expected. Is there any way to have the right plan with single delete ? Or do we must convert all our complex update to multi_update ? Sorry if it's not the right place to post this query. Regards.

1 0

[Maria-developers] Error 1422 - Explicit or implicit commit is not allowed in stored function or trigger
by jerome brauge 29 Nov '17

29 Nov '17

Hello Alexander, I'm facing to a new issue with my stored procedures. Some of them are called directly or from trigger. When these procedures are called directly, they manage the transaction and when they are called from trigger, they doesn't manage the transaction. This works fine on Oracle, SQLServer , ... but fail on Mariadb because the check is done in is_not_allowed_in_function() before the call. use test; set sql_mode=oracle; create or replace table t1 (c1 int); insert into t1 values (1); delimiter / create or replace procedure p1(manage_transactionnal int) is begin if manage_transactionnal = 1 then commit; end if; end; / create or replace trigger tg1 after delete on t1 for each row begin call p1(0); end; / delimiter ; start transaction; delete from t1; rollback; What do you think about this ? Regards, Jérôme.

2 2

[Maria-developers] MariaDB 10.2 now available on CentOS 6 &7 via SCL !!
by Michal Schorm 29 Nov '17

29 Nov '17

Hello everybody, In case you missed it, I just want to share an announce of availability of MariaDB 10.2 on CentOS 6 & 7 as a Software collections. Here are the announce emails from CentOS mailing list, which contains most basic howto: https://lists.centos.org/pipermail/centos-announce/2017-November/022640.html https://lists.centos.org/pipermail/centos-announce/2017-November/022642.html -- This made MariaDB 10.2 a big step closer to a new mass of users, because the standard MDB version in CentOS & RHEL is 5.5. It took quite an effort to fine-tune and perk-up the MDB 10.2 since its first attemps in Fedora, however it is always pleasure to cooperate with developers like you :) -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

1 0

[Maria-developers] Interaction between rpl_slave_state and rpl_binlog_state
by Sachin Setiya 28 Nov '17

28 Nov '17

Hi Kristian, Andrei I have some question related to rpl_slave_state. Suppose A circular async replication between A < -- > B (gtid_ignore_duplicates on) Now, we set some temp server_id on server A , lets say `X`. Now the problem is each event group which originates from A is executed 2 times. For example we insert into table t1 and gtid is 0-X-2. The event goes to slave B B applies it, And send it back to A, Since its server_is different from A global server_id , this event is not filtered in queue_event(). Now event goes to handle_sql thread , And check_duplicate_gtid function is called. Since rpl_slave_state::hash does not have a element whose sequence_no is >= 2, So this event is applied again. Andrei suggested a solution of checking rpl_binlog_state in check_duplicate_gtid, This solution solves some problem but creates some problem. (multi_source.gtid_ignore_duplicates fails in master_sync, test for mdev_10715 also fails ). Reason for this is sync_with_master calls master_gtid_wait , which internally calls gtid_waiting::wait_for_gtid , and this checks rpl_salve_state::hash. Although this method can also be patched for to look for rpl_gtid_global_binlog_state. But I am not sure whether this will solve all problem or create new problem. Another solution might be somehow update rpl_slave_state::hash when we write gtid_event in log. But this does not make sense. rpl_slave_state should be used for slave replication usage. I think we need a more better solution for this. -- Regards sachin

3 4