- developers - lists.mariadb.org

Re: [Maria-developers] The patch for the task mdev-539
by Sergei Golubchik 13 Nov '12

13 Nov '12

Hi, Igor! Sorry for a delay :( Please, find my review attached. On Sep 27, Igor Babaev wrote: > === modified file 'mysql-test/r/alter_table.result' > --- a/mysql-test/r/alter_table.result 2012-05-21 13:30:25 +0000 > +++ b/mysql-test/r/alter_table.result 2012-09-27 21:29:26 +0000 > @@ -1340,3 +1340,203 @@ > execute stmt1; > deallocate prepare stmt1; > drop table t2; > +# > +# mdev-539: fast build of unique/primary indexes for MyISAM > +# I'd suggest to have these test cases in a separate file, say, alter_table_539.test But it's just a siggestion, do as you prefer. > +DROP DATABASE IF EXISTS dbt3_s001; > +CREATE DATABASE dbt3_s001; > +use dbt3_s001; > +drop index `primary` on customer; > +show create table customer; > +Table Create Table > +customer CREATE TABLE `customer` ( > + `c_custkey` int(11) NOT NULL, > + `c_name` varchar(25) DEFAULT NULL, > + `c_address` varchar(40) DEFAULT NULL, > + `c_nationkey` int(11) DEFAULT NULL, > + `c_phone` char(15) DEFAULT NULL, > + `c_acctbal` double DEFAULT NULL, > + `c_mktsegment` char(10) DEFAULT NULL, > + `c_comment` varchar(117) DEFAULT NULL, > + KEY `i_c_nationkey` (`c_nationkey`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +alter table customer add primary key (c_custkey); > +show create table customer; > +Table Create Table > +customer CREATE TABLE `customer` ( > + `c_custkey` int(11) NOT NULL, > + `c_name` varchar(25) DEFAULT NULL, > + `c_address` varchar(40) DEFAULT NULL, > + `c_nationkey` int(11) DEFAULT NULL, > + `c_phone` char(15) DEFAULT NULL, > + `c_acctbal` double DEFAULT NULL, > + `c_mktsegment` char(10) DEFAULT NULL, > + `c_comment` varchar(117) DEFAULT NULL, > + PRIMARY KEY (`c_custkey`), > + KEY `i_c_nationkey` (`c_nationkey`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +drop index `primary` on customer; > +insert into customer values > +(3,'Customer#00000000303','MG9kdTD2WBHm',1,'11-719-748-3364',7498.12,'AUTOMOBILE','special packages wake. slyly reg'); > +alter table customer add primary key (c_custkey); please add a test case for alter ignore table > +ERROR 23000: Duplicate entry '3' for key 'PRIMARY' > +show create table customer; > +Table Create Table > +customer CREATE TABLE `customer` ( ... > === modified file 'sql/ha_partition.cc' > --- a/sql/ha_partition.cc 2012-08-31 21:54:54 +0000 > +++ b/sql/ha_partition.cc 2012-09-27 21:29:26 +0000 > @@ -6862,6 +6862,8 @@ > > flags_to_return= ht->alter_table_flags(flags); > flags_to_return|= m_file[0]->alter_table_flags(flags); > + flags_to_return&= > + ~(HA_FAST_ADD_INDEX | HA_FAST_ADD_UNIQUE_INDEX | HA_FAST_ADD_PK_INDEX); You don't need these flags - see below > > /* > If one partition fails we must be able to revert the change for the other, > > === modified file 'sql/sql_table.cc' > --- a/sql/sql_table.cc 2012-09-02 02:41:38 +0000 > +++ b/sql/sql_table.cc 2012-09-27 21:29:26 +0000 > @@ -6743,6 +6745,11 @@ > my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0)); > goto err_new_table_cleanup; > }); > + > + if (!ignore && > + test(alter_flags & (HA_FAST_ADD_UNIQUE_INDEX | HA_FAST_ADD_PK_INDEX))) > + new_table->file->extra(HA_EXTRA_CREATE_UNIQUE_INDEX_BY_SORT); What's the point? extra() functions were always *optional* in the storage engine API, any storage engine is allowed to ignore any HA_EXTRA_* requests, or all of them. It means, you don't need to introduce HA_FAST_whatever flags, and don't need to check them. Just write if (!ignore) new_table->file->extra(HA_EXTRA_CREATE_UNIQUE_INDEX_BY_SORT); and let the engine ignore it, if it wishes to do so. as a small bonus, you won't need to move alter_flags variable around, it can stay where it was, inside the if() block. > + > error= copy_data_between_tables(thd, table, new_table, > alter_info->create_list, ignore, > order_num, order, &copied, &deleted, > > === modified file 'storage/maria/ha_maria.cc' > --- a/storage/maria/ha_maria.cc 2012-08-14 16:59:28 +0000 > +++ b/storage/maria/ha_maria.cc 2012-09-27 21:29:26 +0000 > @@ -1633,6 +1633,9 @@ please, use the recipe from https://kb.askmonty.org/en/how-to-get-more-out-of-bzr-when-working-on-maria… it's much more difficult to review patches without sufficient context (that is, without function names on every chunk) > param->testflag|= T_REP_BY_SORT; > error= maria_repair_by_sort(param, file, fixed_name, > test(param->testflag & T_QUICK)); > + if (error && share->state.dupp_key != MARIA_MAX_KEY) > + print_keydup_error(share->state.dupp_key, > + ER(ER_DUP_ENTRY_WITH_KEY_NAME)); > } > } > else > > === modified file 'storage/myisam/ha_myisam.cc' > --- a/storage/myisam/ha_myisam.cc 2012-07-18 18:40:15 +0000 > +++ b/storage/myisam/ha_myisam.cc 2012-09-27 21:29:26 +0000 > @@ -1130,6 +1130,9 @@ > thd_proc_info(thd, "Repair by sorting"); > error = mi_repair_by_sort(&param, file, fixed_name, > test(param.testflag & T_QUICK)); > + if (error && share->state.dupp_key != MAX_KEY) > + print_keydup_error(share->state.dupp_key, > + ER(ER_DUP_ENTRY_WITH_KEY_NAME)); I don't understand it. print_keydup_error() takes the value to print from table->record[[0]. But - in the case of mi_repair_by_sort - how does the value end up here? What code puts the conflicting key value into table->record[0] ? > } > } > else > > === modified file 'storage/myisam/mi_check.c' > --- a/storage/myisam/mi_check.c 2012-04-07 13:58:46 +0000 > +++ b/storage/myisam/mi_check.c 2012-09-27 21:29:26 +0000 > @@ -2408,12 +2411,22 @@ > (my_bool) (!(param->testflag & T_VERBOSE)), > param->sort_buffer_length)) > { > + > + if (sort_param.sort_info->dupp) > + { > + share->state.dupp_key= sort_param.key; > + keydup_error= 1; > + } > + if (!(keydup_error && param->suppress_keydup_error_handling)) 1. please use param->testflag for flags, instead of a new special field 2. I don't see why are you doing it here, normally the error is issued below, as "Duplicate key for record at %10s against...." I'd expect to see any new code that deals with duplicate key errors at the same place where the old code is > + { > param->retry_repair= 1; > if (! param->error_printed) > mi_check_print_error(param, "Couldn't fix table with create_index_by_sort(). Error: %d", > my_errno); > + } > goto err; > } > + > /* No need to calculate checksum again. */ > sort_param.calc_checksum= 0; > free_root(&sort_param.wordroot, MYF(0)); > @@ -2533,18 +2546,27 @@ > } > if (got_error) > { > + if (!(keydup_error && param->suppress_keydup_error_handling)) > + { > if (! param->error_printed) > mi_check_print_error(param,"%d when fixing table",my_errno); You could simply set param->error_printed when you get dplicate key error - it'll avoid all further errors, you won't need to wrap them all in if()'s > + } > if (new_file >= 0) > { > (void) mysql_file_close(new_file, MYF(0)); > (void) mysql_file_delete(mi_key_file_datatmp, > param->temp_filename, MYF(MY_WME)); > + if (!(keydup_error && param->suppress_keydup_error_handling)) > + { Why do you need this if() ? > if (info->dfile == new_file) /* Retry with key cache */ > if (unlikely(mi_open_datafile(info, share, name, -1))) > param->retry_repair= 0; /* Safety */ > + } > } > + if (!(keydup_error && param->suppress_keydup_error_handling)) > + { > mi_mark_crashed_on_repair(info); > + } Why do you need to avoid mi_mark_crashed_on_repair() ? > if (killed_ptr(param)) > param->retry_repair= 0; /* No use to retry repair */ > } > @@ -4682,8 +4708,9 @@ > (!rows || rows >= MI_MIN_ROWS_TO_DISABLE_INDEXES)); > for (i=0 ; i < share->base.keys ; i++,key++) > { > - if (!(key->flag & (HA_NOSAME | HA_SPATIAL | HA_AUTO_KEY)) && > - ! mi_too_big_key_for_sort(key,rows) && info->s->base.auto_key != i+1) > + if (!(key->flag & (HA_SPATIAL | HA_AUTO_KEY)) && > + ! mi_too_big_key_for_sort(key,rows) && info->s->base.auto_key != i+1 && > + (!(key->flag & HA_NOSAME) || info->create_unique_index_by_sort)) Now, this directly contradicts the function name and the function comment. Could you please rename the function and update the comment? And instead of checking info->create_unique_index_by_sort I'd rather pass it down as an argument: void mi_disable_indexes(MI_INFO *info, ha_rows rows, my_bool only_non_unique) { > { > mi_clear_key_active(share->state.key_map, i); > info->update|= HA_STATE_CHANGED; > > === modified file 'storage/myisam/mi_extra.c' > --- a/storage/myisam/mi_extra.c 2012-07-31 17:29:07 +0000 > +++ b/storage/myisam/mi_extra.c 2012-09-27 21:29:26 +0000 > @@ -365,6 +365,9 @@ > case HA_EXTRA_CHANGE_KEY_TO_DUP: > mi_extra_keyflag(info, function); > break; > + case HA_EXTRA_CREATE_UNIQUE_INDEX_BY_SORT: > + info->create_unique_index_by_sort= 1; > + break; Two comments here: 1. I don't like the name "create unique index by sort", because ALTER TABLE itself does not create any indexes by sort or anything it simply tells the engine to optimize a bulk insertion. and it's myisam that does repair-by-sort. I mean, a better name would be a high-level name that does not imply myisam-specific details. something like HA_EXTRA_BULK_INSERT_DUPLICATES_UNLIKELY which tells the engine to optimize the following bulk insert for the case when unique key conflicts are unlikely to happen. (in myisam case it means to build unique indexes by sort, right) 2. I've discussed this with Monty and we both didn't like using extra() here, even to preserve the API compatibility. Monty thinks there's no need to bother, and we can change start_bulk_insert() any way we want. But to make it better for third-party engines, I'd suggest something like class handler { ... virtual void start_bulk_insert(ha_rows rows) {} virtual void start_bulk_insert(ha_rows rows, ulonglong flags) { start_bulk_insert(rows); } ... } that is, you introduce the new function, and keep the old one as a fallback. This way it stays compatible, and old engines won't need any changes. But myisam/maria will implement the new start_bulk_insert variant, with two arguments, and will recognize the flag HA_BULK_INSERT_DUPLICATES_UNLIKELY And no extra() is needed. > case HA_EXTRA_MMAP: > #ifdef HAVE_MMAP > mysql_mutex_lock(&share->intern_lock); > Regards, Sergei

1 0

[Maria-developers] MariaDB 5.3.10 release prep
by Daniel Bartholomew 12 Nov '12

12 Nov '12

Preliminary release notes and changelog pages have been created for 5.3.10. https://kb.askmonty.org/en/mariadb-5310-release-notes/ https://kb.askmonty.org/en/mariadb-5310-changelog/ Please let me know if there is anything in particular which should be mentioned in the release notes. Thanks. -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com

1 0

[Maria-developers] MariaDB 10.0.0 Alpha Release Prep
by Daniel Bartholomew 12 Nov '12

12 Nov '12

All, The files for this release have been uploaded to the mirrors and we're just waiting until Monday morning, U.S. Eastern time, to announce the release. The preliminary release notes and changelog pages are here: - https://kb.askmonty.org/en/mariadb-1000-release-notes/ - https://kb.askmonty.org/en/mariadb-1000-changelog/ The release notes page is pretty sparse. Its content is from the list of "New already done Features" on the "What is MariaDB 10.0?" page. Please send me additions and corrections ASAP (or make the changes yourself). Thanks. -- Daniel Bartholomew Google+ - http://gplus.to/dbart Twitter - http://twitter.com/daniel_bart MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com

1 0

Re: [Maria-developers] [Commits] Rev 3569: MDEV-3830 - fix build on Intel compiler in file:///H:/bzr/5.5/
by Kristian Nielsen 03 Nov '12

03 Nov '12

Vladislav Vaintroub <wlad(a)montyprogram.com> writes: > === modified file 'mysys/my_context.c' > --- a/mysys/my_context.c 2012-10-31 11:47:25 +0000 > +++ b/mysys/my_context.c 2012-11-02 23:31:50 +0000 > @@ -206,7 +206,7 @@ > ( > "movq %%rsp, (%[save])\n\t" > "movq %[stack], %%rsp\n\t" > -#if __GNUC__ >= 4 && __GNUC_MINOR__ >= 4 > +#if __GNUC__ >= 4 && __GNUC_MINOR__ >= 4 && !defined(__INTEL_COMPILER) There are two places this is done (one for 32-bit and one for 64-bit). You probably need the !defined(__INTEL_COMPILER) on both. - Kristian.

1 0

Re: [Maria-developers] [Commits] Rev 3589: Fixed bug mdev-585 (LP bug #637962) in file:///home/igor/maria/maria-5.3-mdev585/
by Sergei Petrunia 02 Nov '12

02 Nov '12

Hello Igor, The fix seems to be ok to push. Which version do you want to push it into? This is not a crash, should we really push it into 5.3, or to 5.5? On Thu, Nov 01, 2012 at 02:54:34PM -0700, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-mdev585/ > > ------------------------------------------------------------ > revno: 3589 > revision-id: igor(a)askmonty.org-20121101215433-ctfm3zfhc6uhuy3i > parent: psergey(a)askmonty.org-20121031053425-kfn0l7yj6z7jtnqw > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.3-mdev585 > timestamp: Thu 2012-11-01 14:54:33 -0700 > message: > Fixed bug mdev-585 (LP bug #637962) > If, when executing a query with ORDER BY col LIMIT n, the optimizer chose > an index-merge scan to access the table containing col while there existed > an index defined over col then optimizer did not consider the possibility > of using an alternative range scan by this index to avoid filesort. This > could cause a performance degradation if the optimizer flag index_merge was > set up to 'on'. > === modified file 'mysql-test/r/range_vs_index_merge.result' > --- a/mysql-test/r/range_vs_index_merge.result 2012-03-21 00:03:28 +0000 > +++ b/mysql-test/r/range_vs_index_merge.result 2012-11-01 21:54:33 +0000 > @@ -1221,6 +1221,150 @@ > Seattle USA 563374 > Caracas VEN 1975294 > set optimizer_switch=@save_optimizer_switch; > +# > +# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n > +# (LP bug #637962) > +# > +DROP INDEX CountryPopulation ON City; > +DROP INDEX CountryName ON City; > +DROP INDEX CityName on City; > +CREATE INDEX Name ON City(Name); > +CREATE INDEX Population ON City(Population); > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City index_merge Country,Name,Population Name,Country 35,3 NULL # Using sort_union(Name,Country); Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000); > +ID Name Country Population > +384 Cabo Frio BRA 119503 > +387 Camaragibe BRA 118968 > +403 Catanduva BRA 107761 > +412 Cachoeirinha BRA 103240 > +508 Watford GBR 113080 > +509 Ipswich GBR 114000 > +510 Slough GBR 112000 > +511 Exeter GBR 111000 > +512 Cheltenham GBR 106000 > +513 Gloucester GBR 107000 > +514 Saint Helens GBR 106293 > +515 Sutton Coldfield GBR 106001 > +516 York GBR 104425 > +517 Oldham GBR 103931 > +518 Basildon GBR 100924 > +519 Worthing GBR 100000 > +635 Mallawi EGY 119283 > +636 Bilbays EGY 113608 > +637 Mit Ghamr EGY 101801 > +638 al-Arish EGY 100447 > +701 Tarragona ESP 113016 > +702 Lleida (Lérida) ESP 112207 > +703 Jaén ESP 109247 > +704 Ourense (Orense) ESP 109120 > +705 Mataró ESP 104095 > +706 Algeciras ESP 103106 > +707 Marbella ESP 101144 > +759 Gonder ETH 112249 > +869 Cabuyao PHL 106630 > +870 Calapan PHL 105910 > +873 Cauayan PHL 103952 > +903 Serekunda GMB 102600 > +909 Sohumi GEO 111700 > +913 Tema GHA 109975 > +914 Sekondi-Takoradi GHA 103653 > +924 Villa Nueva GTM 101295 > +1844 Cape Breton CAN 114733 > +1847 Cambridge CAN 109186 > +2406 Herakleion GRC 116178 > +2407 Kallithea GRC 114233 > +2408 Larisa GRC 113090 > +2908 Cajamarca PER 108009 > +3002 Besançon FRA 117733 > +3003 Caen FRA 113987 > +3004 Orléans FRA 113126 > +3005 Mulhouse FRA 110359 > +3006 Rouen FRA 106592 > +3007 Boulogne-Billancourt FRA 106367 > +3008 Perpignan FRA 105115 > +3009 Nancy FRA 103605 > +3411 Ceyhan TUR 102412 > +3567 Carúpano VEN 119639 > +3568 Catia La Mar VEN 117012 > +3571 Calabozo VEN 107146 > +3786 Cam Ranh VNM 114041 > +3792 Tartu EST 101246 > +4002 Carrollton USA 109576 > +4027 Cape Coral USA 102286 > +4032 Cambridge USA 101355 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 2 > +Handler_read_next 385 > +Handler_read_prev 0 > +Handler_read_rnd 377 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City range Country,Name,Population Population 4 NULL # Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +ID Name Country Population > +519 Worthing GBR 100000 > +638 al-Arish EGY 100447 > +518 Basildon GBR 100924 > +707 Marbella ESP 101144 > +3792 Tartu EST 101246 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 1 > +Handler_read_next 59 > +Handler_read_prev 0 > +Handler_read_rnd 0 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +set optimizer_switch='index_merge=off'; > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City range Country,Name,Population Population 4 NULL # Using index condition; Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +ID Name Country Population > +519 Worthing GBR 100000 > +638 al-Arish EGY 100447 > +518 Basildon GBR 100924 > +707 Marbella ESP 101144 > +3792 Tartu EST 101246 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 1 > +Handler_read_next 59 > +Handler_read_prev 0 > +Handler_read_rnd 0 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +set optimizer_switch=@save_optimizer_switch; > DROP DATABASE world; > use test; > CREATE TABLE t1 ( > > === modified file 'mysql-test/r/range_vs_index_merge_innodb.result' > --- a/mysql-test/r/range_vs_index_merge_innodb.result 2012-03-21 00:03:28 +0000 > +++ b/mysql-test/r/range_vs_index_merge_innodb.result 2012-11-01 21:54:33 +0000 > @@ -1222,6 +1222,150 @@ > Seattle USA 563374 > Caracas VEN 1975294 > set optimizer_switch=@save_optimizer_switch; > +# > +# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n > +# (LP bug #637962) > +# > +DROP INDEX CountryPopulation ON City; > +DROP INDEX CountryName ON City; > +DROP INDEX CityName on City; > +CREATE INDEX Name ON City(Name); > +CREATE INDEX Population ON City(Population); > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000); > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City index_merge Country,Name,Population Name,Country 35,3 NULL # Using sort_union(Name,Country); Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000); > +ID Name Country Population > +384 Cabo Frio BRA 119503 > +387 Camaragibe BRA 118968 > +403 Catanduva BRA 107761 > +412 Cachoeirinha BRA 103240 > +508 Watford GBR 113080 > +509 Ipswich GBR 114000 > +510 Slough GBR 112000 > +511 Exeter GBR 111000 > +512 Cheltenham GBR 106000 > +513 Gloucester GBR 107000 > +514 Saint Helens GBR 106293 > +515 Sutton Coldfield GBR 106001 > +516 York GBR 104425 > +517 Oldham GBR 103931 > +518 Basildon GBR 100924 > +519 Worthing GBR 100000 > +635 Mallawi EGY 119283 > +636 Bilbays EGY 113608 > +637 Mit Ghamr EGY 101801 > +638 al-Arish EGY 100447 > +701 Tarragona ESP 113016 > +702 Lleida (Lérida) ESP 112207 > +703 Jaén ESP 109247 > +704 Ourense (Orense) ESP 109120 > +705 Mataró ESP 104095 > +706 Algeciras ESP 103106 > +707 Marbella ESP 101144 > +759 Gonder ETH 112249 > +869 Cabuyao PHL 106630 > +870 Calapan PHL 105910 > +873 Cauayan PHL 103952 > +903 Serekunda GMB 102600 > +909 Sohumi GEO 111700 > +913 Tema GHA 109975 > +914 Sekondi-Takoradi GHA 103653 > +924 Villa Nueva GTM 101295 > +1844 Cape Breton CAN 114733 > +1847 Cambridge CAN 109186 > +2406 Herakleion GRC 116178 > +2407 Kallithea GRC 114233 > +2408 Larisa GRC 113090 > +2908 Cajamarca PER 108009 > +3002 Besançon FRA 117733 > +3003 Caen FRA 113987 > +3004 Orléans FRA 113126 > +3005 Mulhouse FRA 110359 > +3006 Rouen FRA 106592 > +3007 Boulogne-Billancourt FRA 106367 > +3008 Perpignan FRA 105115 > +3009 Nancy FRA 103605 > +3411 Ceyhan TUR 102412 > +3567 Carúpano VEN 119639 > +3568 Catia La Mar VEN 117012 > +3571 Calabozo VEN 107146 > +3786 Cam Ranh VNM 114041 > +3792 Tartu EST 101246 > +4002 Carrollton USA 109576 > +4027 Cape Coral USA 102286 > +4032 Cambridge USA 101355 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 2 > +Handler_read_next 385 > +Handler_read_prev 0 > +Handler_read_rnd 377 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City range Country,Name,Population Population 4 NULL # Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +ID Name Country Population > +519 Worthing GBR 100000 > +638 al-Arish EGY 100447 > +518 Basildon GBR 100924 > +707 Marbella ESP 101144 > +3792 Tartu EST 101246 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 1 > +Handler_read_next 59 > +Handler_read_prev 0 > +Handler_read_rnd 0 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +set optimizer_switch='index_merge=off'; > +EXPLAIN > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +id select_type table type possible_keys key key_len ref rows Extra > +1 SIMPLE City range Country,Name,Population Population 4 NULL # Using index condition; Using where > +FLUSH STATUS; > +SELECT * FROM City > +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > +AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +ID Name Country Population > +519 Worthing GBR 100000 > +638 al-Arish EGY 100447 > +518 Basildon GBR 100924 > +707 Marbella ESP 101144 > +3792 Tartu EST 101246 > +SHOW STATUS LIKE 'Handler_read_%'; > +Variable_name Value > +Handler_read_first 0 > +Handler_read_key 1 > +Handler_read_next 59 > +Handler_read_prev 0 > +Handler_read_rnd 0 > +Handler_read_rnd_deleted 0 > +Handler_read_rnd_next 0 > +set optimizer_switch=@save_optimizer_switch; > DROP DATABASE world; > use test; > CREATE TABLE t1 ( > > === modified file 'mysql-test/t/range_vs_index_merge.test' > --- a/mysql-test/t/range_vs_index_merge.test 2012-03-21 00:03:28 +0000 > +++ b/mysql-test/t/range_vs_index_merge.test 2012-11-01 21:54:33 +0000 > @@ -675,6 +675,64 @@ > $cond; > > set optimizer_switch=@save_optimizer_switch; > + > +--echo # > +--echo # Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n > +--echo # (LP bug #637962) > +--echo # > + > +DROP INDEX CountryPopulation ON City; > +DROP INDEX CountryName ON City; > +DROP INDEX CityName on City; > + > +CREATE INDEX Name ON City(Name); > +CREATE INDEX Population ON City(Population); > + > + > +--replace_column 9 # > +EXPLAIN > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000); > +FLUSH STATUS; > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000); > +SHOW STATUS LIKE 'Handler_read_%'; > + > + > +--replace_column 9 # > +EXPLAIN > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > + > +FLUSH STATUS; > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +SHOW STATUS LIKE 'Handler_read_%'; > + > + > +set optimizer_switch='index_merge=off'; > + > +--replace_column 9 # > +EXPLAIN > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > + > +FLUSH STATUS; > +SELECT * FROM City > + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) > + AND (Population >= 100000 AND Population < 120000) > +ORDER BY Population LIMIT 5; > +SHOW STATUS LIKE 'Handler_read_%'; > + > +set optimizer_switch=@save_optimizer_switch; > > > DROP DATABASE world; > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2012-10-10 05:21:22 +0000 > +++ b/sql/sql_select.cc 2012-11-01 21:54:33 +0000 > @@ -18079,15 +18079,18 @@ > */ > > if (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE || > - quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || > + quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || > quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || > quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) > - goto use_filesort; > - ref_key= select->quick->index; > - ref_key_parts= select->quick->used_key_parts; > + ref_key= MAX_KEY; > + else > + { > + ref_key= select->quick->index; > + ref_key_parts= select->quick->used_key_parts; > + } > } > > - if (ref_key >= 0) > + if (ref_key >= 0 && ref_key != MAX_KEY) > { > /* > We come here when there is a REF key. > @@ -18229,7 +18232,8 @@ > else > keys= usable_keys; > > - if (ref_key >= 0 && table->covering_keys.is_set(ref_key)) > + if (ref_key >= 0 && ref_key != MAX_KEY && > + table->covering_keys.is_set(ref_key)) > ref_key_quick_rows= table->quick_rows[ref_key]; > > read_time= join->best_positions[tablenr].read_time; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

Re: [Maria-developers] Rev 3176: MDEV-616 fix (MySQL fix accepted)
by Sergei Golubchik 31 Oct '12

31 Oct '12

Hi, Sanja! On Oct 09, sanja(a)montyprogram.com wrote: > revno: 3176 > committer: sanja(a)montyprogram.com > branch nick: work-maria-5.2-MDEV-616 > timestamp: Tue 2012-10-09 17:36:02 +0300 > message: > MDEV-616 fix (MySQL fix accepted) Looks ok, as far as I understand. Regards, Sergei

1 0

[Maria-developers] Fwd: possible bug in MySQL 5.6.7-rc slave replication crash safety?
by Zardosht Kasheff 24 Oct '12

24 Oct '12

Hello, Does Maria 10.0 implement crash safe replication that is found in MySQL 5.6.7? If so, does this issue exist there? Thanks -Zardosht ---------- Forwarded message ---------- From: Zardosht Kasheff <zardosht(a)gmail.com> Date: Wed, Oct 17, 2012 at 8:59 AM Subject: possible bug in MySQL 5.6.7-rc slave replication crash safety? To: internals(a)lists.mysql.com Hello all, We downloaded MySQL 5.6.7-rc to see slave replication crash safety in action. A simple test did not seem to work, as described in http://bugs.mysql.com/bug.php?id=67246. Does anyone have insight into the problem? Perhaps we screwed something up? Thanks -Zardosht

2 8

Re: [Maria-developers] Please look at and advise about MDEV-3801
by Igor Babaev 22 Oct '12

22 Oct '12

On 10/22/2012 12:53 AM, Timour Katchaounov wrote: > Igor, > > Great, the patch indeed fixes the bug both on 5.3 and 5.5, however the > reduced test case I produced still takes 21 MB, and I was not successful > in reducing it to a size that can be added to the regression test suite. > I already spent too much in reducing the test case. Would you agree that > I push your patch without a test case? I think you still have to add the test case case (in a separate file) that is run with the --big option. Regards, Igor. > > Timour > > > On 20.10.2012 08:56, Igor Babaev wrote: >> On 10/19/2012 12:43 PM, Timour Katchaounov wrote: >>> Hi Igor, >>> >>> Could you please look at this bug >>> https://mariadb.atlassian.net/browse/MDEV-3801, >>> and read my last comment. The bug may be (just a guess ATM) related to >>> index >>> creation on the temporary table created for the derived table. Since >>> this is your >>> code, I'd like you to tell me if you have seen anything similar before, >>> or if >>> you have any hints. >>> >>> If not, I will continue with the bug and will trace the cause for the >>> wrong key information. >>> >>> Thanks, >>> Timour >> >> Timour, >> >> The following patch fixes this problem >> >> Regards, >> Igor. >> >> === modified file 'sql/sql_select.cc' >> --- sql/sql_select.cc 2012-10-10 05:21:22 +0000 >> +++ sql/sql_select.cc 2012-10-20 05:49:13 +0000 >> @@ -8981,7 +8981,7 @@ void JOIN::drop_unused_derived_keys() >> JOIN_TAB *tab; >> for (tab= first_linear_tab(this, WITHOUT_CONST_TABLES); >> tab; >> - tab= next_linear_tab(this, tab, WITHOUT_BUSH_ROOTS)) >> + tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) >> { >> >> TABLE *table=tab->table; >> >> MariaDB [test]> set @@tmp_table_size=16384; >> ERROR 2006 (HY000): MySQL server has gone away >> No connection. Trying to reconnect... >> Connection id: 1 >> Current database: test >> >> Query OK, 0 rows affected (5.48 sec) >> >> MariaDB [test]> set @@tmp_table_size=16384; >> Query OK, 0 rows affected (0.00 sec) >> >> MariaDB [test]> EXPLAIN SELECT t1.deal_id FROM table_e AS t1 JOIN >> table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN >> (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS >> t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) >> JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = >> t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) >> tU) limit 10; >> +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ >> >> | id | select_type | table | type | possible_keys >> | key | key_len | ref | >> rows | Extra | >> +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ >> >> | 1 | PRIMARY | <subquery2> | ALL | distinct_key >> | NULL | NULL | NULL | >> 8488 | | >> | 1 | PRIMARY | t1 | eq_ref | PRIMARY >> | PRIMARY | 4 | tU.deal_id | >> 1 | Using index | >> | 1 | PRIMARY | t10 | ref | PRIMARY,full_id >> | PRIMARY | 4 | tU.deal_id | >> 1 | Using index | >> | 2 | MATERIALIZED | <derived3> | ALL | NULL >> | NULL | NULL | NULL | >> 8488 | | >> | 3 | DERIVED | t4 | system | PRIMARY >> | NULL | NULL | NULL | >> 1 | Using temporary | >> | 3 | DERIVED | t3 | ref | >> PRIMARY,feid,exchange,deal_feid,feid_date | exchange | 3 | const >> | 8488 | | >> | 3 | DERIVED | t1 | eq_ref | PRIMARY >> | PRIMARY | 4 | test.t3.deal_id | >> 1 | Using index | >> | 3 | DERIVED | t2 | eq_ref | PRIMARY,full_id >> | PRIMARY | 8 | test.t3.deal_id,test.t3.feid | >> 1 | Using index; Distinct | >> +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ >> >> 8 rows in set (4.22 sec) >> >> MariaDB [test]> SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 >> ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM >> (SELECT DISTINCT t1.deal_id FROM table_e AS t1 >> JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN >> table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) >> JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit >> 10; >> +---------+ >> | deal_id | >> +---------+ >> | 30 | >> | 82 | >> | 83 | >> | 108 | >> | 155 | >> | 156 | >> | 162 | >> | 184 | >> | 186 | >> | 195 | >> +---------+ >> 10 rows in set (10.62 sec) >>

1 1

Re: [Maria-developers] Please look at and advise about MDEV-3801
by Igor Babaev 20 Oct '12

20 Oct '12

On 10/19/2012 12:43 PM, Timour Katchaounov wrote: > Hi Igor, > > Could you please look at this bug > https://mariadb.atlassian.net/browse/MDEV-3801, > and read my last comment. The bug may be (just a guess ATM) related to > index > creation on the temporary table created for the derived table. Since > this is your > code, I'd like you to tell me if you have seen anything similar before, > or if > you have any hints. > > If not, I will continue with the bug and will trace the cause for the > wrong key information. > > Thanks, > Timour Timour, The following patch fixes this problem Regards, Igor. === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2012-10-10 05:21:22 +0000 +++ sql/sql_select.cc 2012-10-20 05:49:13 +0000 @@ -8981,7 +8981,7 @@ void JOIN::drop_unused_derived_keys() JOIN_TAB *tab; for (tab= first_linear_tab(this, WITHOUT_CONST_TABLES); tab; - tab= next_linear_tab(this, tab, WITHOUT_BUSH_ROOTS)) + tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) { TABLE *table=tab->table; MariaDB [test]> set @@tmp_table_size=16384; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test Query OK, 0 rows affected (5.48 sec) MariaDB [test]> set @@tmp_table_size=16384; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> EXPLAIN SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit 10; +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ | 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 8488 | | | 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | tU.deal_id | 1 | Using index | | 1 | PRIMARY | t10 | ref | PRIMARY,full_id | PRIMARY | 4 | tU.deal_id | 1 | Using index | | 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 8488 | | | 3 | DERIVED | t4 | system | PRIMARY | NULL | NULL | NULL | 1 | Using temporary | | 3 | DERIVED | t3 | ref | PRIMARY,feid,exchange,deal_feid,feid_date | exchange | 3 | const | 8488 | | | 3 | DERIVED | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t3.deal_id | 1 | Using index | | 3 | DERIVED | t2 | eq_ref | PRIMARY,full_id | PRIMARY | 8 | test.t3.deal_id,test.t3.feid | 1 | Using index; Distinct | +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+ 8 rows in set (4.22 sec) MariaDB [test]> SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit 10; +---------+ | deal_id | +---------+ | 30 | | 82 | | 83 | | 108 | | 155 | | 156 | | 162 | | 184 | | 186 | | 195 | +---------+ 10 rows in set (10.62 sec)

1 0

[Maria-developers] my_apc.cc and new innodb
by Sergei Golubchik 19 Oct '12

19 Oct '12

Hi. There's some issue we need to discuss. I've discovered it when merging your APC code into 10.0-monty, but the problem is simple enough and could've been found earlier too. In the thd_killed() you do: 3809 extern "C" int thd_killed(const MYSQL_THD thd) 3810 { 3811 if (!thd) 3812 thd= current_thd; 3813 3814 Apc_target *apc_target= (Apc_target*)&thd->apc_target; 3815 if (apc_target->have_apc_requests()) 3816 apc_target->process_apc_requests(); 3817 3818 if (!(thd->killed & KILL_HARD_BIT)) 3819 return 0; 3820 return thd->killed; 3821 } But there's no rule that thd_killed() cannot be called with thd different from current_thd. That is, with thd of *another thread*. Of course, when it happens, the caller is responsible for making sure that the owner thread of that thd isn't going to use it concurrently. And that's exactly what happens in the new InnoDB. Now, show explain wasn't designed to work in this situation, it created items (which are created using sql_alloc, that uses mem_root of the current thread, and added to the free item list of the current thd). As a short-term fix, I've added a protection to thd_killed() to not call apc functions if thd != current_thd. But I don't like it, I think a better solution would be to switch thds, like thread pool does, and process apc requests anyway. What do you think? Regards, Sergei

1 0