November 2012 - developers - lists.mariadb.org

[Maria-developers] MDEV-520: consider parallel replication patch from taobao patches
by Kristian Nielsen 14 Jan '13

14 Jan '13

Hi xiaobin, I read through your patch found here: http://mysql.taobao.org/index.php/RLPR_for_MariaDB I am still a bit confused about the status of this patch. It would be useful if you could explain a bit about the status of the patch: - How have you tested the patch. - How are you currently using the patch? Or is it still just development/test? - What are your current plans for the patch? (You mentioned that you have some changes you are implementing at the moment). Anyway, I think I have a fair understanding of the overall idea, which seems very interesting! I will try to summarise here in a bit more detail, as well as give a few general comments on the patch. Please correct me if I misunderstood anything. ---- The basic idea is to build a set (implemented as a hash table) of every unique key in every row-event in each transaction received from the master. If two transactions have no overlapping keys, they can be applied in parallel (foreign_key_checks is set to OFF to make this work). There is a single thread that processes each event received in sequence. It checks the key set of the new transaction against the key sets of all transactions queued for each of N worker threads: - If there is no conflict with any already queued transaction, the new transaction can be queued to any worker thread (the one with fewest queued transaction is chosen). - If there is a conflict with just one thread, then the transaction can be queued for that thread - it will then execute in correct sequence with the conflicting transactions. - If there is a conflict with multiple threads, wait until the conflicting transactions have been processed. No new transactions are queued while waiting. Then there are N (default 16) threads that apply the queued transactions. Queued transactions are processsed in parallel within one thread, but transaction in different queues are processed in parallel with each other. If a transaction is received which is not using row events (statement-based replication or DDL), then we wait until all queued transactions are processed. Only after that is the new transaction queued, so that such events are processed correctly (but not in parallel, of course). Likewise, if the worker threads get too far behind, we wait with queuing any more transactions. ---- I think this is a very interesting approach, and I am happy that someone have actually tried implementing it. The biggest potential issue seems to be the performance of the thread that checks the transactions for conflicts and queues them for the appropriate thread: - Main issue will be huge transactions, if the size of hash tables of key values become too big for available memory. Maybe this could be solved by having some limit above which transactions are not tried in parallel. - There will be a lot of hash lookups of key values, all done in a single thread. This could become a limit I suppose - testing will be needed to understand this fully. But I think there will be many workloads where this will be useful. Overall, it seems to me this technique is very promising. It seems to be better at getting maximum parallelism on the slave than any of the other approaches that have been considered. As to the patch itself, it seems a bit immature in the present form, but I understood you are still working on it. Let us discuss what the plans could be for finishing it and get it ready for inclusion in MariaDB. The main criteria is that it must be reasonably complete - it must not crash or silently corrupt data in any usage. However, reasonable restrictions for what is supported is fine. (For example, we could say that LOAD DATA is not supported and give an error, but not crash or silently ignore such statement). But we can discuss this in more details. ---- Finally, a couple general comments on the code: 1. get_pk_value() does not seem to handle collations, eg. case insensitivity? It needs to understand that eg. "abab" and "ABAB" can conflict. I think this should be solved by copying in values with non-binary collations using the appropriate strxfrm() variant. 2. remote_query() retries the query in case of deadlock - but that seems wrrong, doesn't it need to retry the whole _transaction_, not just the query? 3. What about other events like User_var_event ? Will they be included in a later patch? 4. There is a my_sleep(10000) in the code when a transaction conflicts with two or more workers. It would be better to replace this with a condition variable so we can wake up as soon as the conflict is resolved. Same for some other sleeps like in wait_for_all_dml_done(). 5. Shouldn't there be some locking in dispatch_transaction()? To protect the hash lookups when checking conflicts against worker threads doing pk_hash_minus() ? 6. get_pk_value(). There seems to be a buffer overflow - st_hash_item::key is fixed size, but there seems no check against overrunning it. One solution is to just truncate if data does not fit - this can lead to false positives where potential parallelism is not exploited, but that is still safe and should be ok. Thanks! - Kristian.

5 21

Re: [Maria-developers] MDEV-318 IF (NOT) EXIST clauses for ALTER TABLE
by Sergei Golubchik 13 Jan '13

13 Jan '13

Hi, Holyfoot! On Jul 08, holyfoot(a)askmonty.org wrote: > At file:///home/hf/wmar/mdev-318/ > > ------------------------------------------------------------ > revno: 3552 > revision-id: holyfoot(a)askmonty.org-20120708152354-d7iu7yoqo3cohidz > parent: sanja(a)montyprogram.com-20120626184334-ptpg39ptq3t79dg5 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-318 > timestamp: Sun 2012-07-08 20:23:54 +0500 > message: > MDEV-318 IF (NOT) EXIST clauses for ALTER TABLE (MWL #252). > ALTER TABLE syntax modified and related implementations added to allow: > ALTER TABLE ADD/DROP COLUMN > ALTER TABLE ADD/DROP INDEX > ALTER TABLE ADD/DROP FOREIGN KEY > ALTER TABLE ADD/DROP PARTITION > ALTER TABLE CHANGE COLUMN > ALTER TABLE MODIFY COLUMN Please, find my comments below: > === modified file 'mysql-test/r/alter_table.result' > --- mysql-test/r/alter_table.result 2011-11-21 17:13:14 +0000 > +++ mysql-test/r/alter_table.result 2012-07-11 14:24:32 +0000 > @@ -1282,3 +1282,30 @@ > # Clean-up. > drop table t1; > End of 5.1 tests > +CREATE TABLE t1 ( > +id INT(11) NOT NULL, > +x_param INT(11) DEFAULT NULL, > +PRIMARY KEY (id), > +KEY x_param (x_param) > +); > +ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, > +ADD COLUMN IF NOT EXISTS lol INT AFTER id; > +Warnings: > +Note 1060 Duplicate column name 'id' > +ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; > +Warnings: > +Note 1060 Duplicate column name 'lol' > +ALTER TABLE t1 DROP COLUMN IF EXISTS lol; > +ALTER TABLE t1 DROP COLUMN IF EXISTS lol; > +Warnings: > +Note 1091 Can't DROP 'lol'; check that column/key exists > +ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); > +Warnings: > +Note 1061 Duplicate key name 'x_param' > +ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); typo? you test ADD KEY IF NOT EXISTS twice. > +Warnings: > +Note 1061 Duplicate key name 'x_param' > +ALTER TABLE t1 MODIFY IF EXISTS lol INT; > +Warnings: > +Note 1054 Unknown column 'lol' in 't1' > +DROP TABLE t1; > === modified file 'sql/field.cc' > --- sql/field.cc 2012-03-13 14:38:43 +0000 > +++ sql/field.cc 2012-07-11 14:28:40 +0000 > @@ -9182,6 +9182,7 @@ void Create_field::init_for_tmp_table(en First: please, see the last section in http://kb.askmonty.org/en/how-to-get-more-out-of-bzr-when-working-on-mariad… and do as it suggests I had to apply your patch locally and re-run bzr diff to get the function names in the diff :( > (maybe_null ? FIELDFLAG_MAYBE_NULL : 0) | > (is_unsigned ? 0 : FIELDFLAG_DECIMAL)); > vcol_info= 0; > + create_if_not_exists= FALSE; > stored_in_db= TRUE; > } > > === modified file 'sql/sql_lex.h' > --- sql/sql_lex.h 2011-12-11 16:39:33 +0000 > +++ sql/sql_lex.h 2012-07-11 14:28:40 +0000 > @@ -1816,6 +1816,7 @@ typedef struct st_lex : public Query_tab > uint16 create_view_algorithm; > uint8 create_view_check; > bool drop_if_exists, drop_temporary, local_file, one_shot_set; > + bool check_exists; /* Enabled if the IF [NOT] EXISTS specified for ALTER */ I think, you can remove drop_if_exists and use check_exists instead > bool autocommit; > bool verbose, no_write_to_binlog; > > === modified file 'sql/sql_yacc.yy' > --- sql/sql_yacc.yy 2012-06-10 09:53:06 +0000 > +++ sql/sql_yacc.yy 2012-07-11 14:28:40 +0000 > @@ -4656,8 +4659,16 @@ table_option: > ; > > opt_if_not_exists: > - /* empty */ { $$= 0; } > - | IF not EXISTS { $$=HA_LEX_CREATE_IF_NOT_EXISTS; } > + /* empty */ > + { > + Lex->check_exists= FALSE; Let's initialize check_exists either before the grammar is parsed (like in the add_create_index_prepare and in the create rule) of in the grammar (here, in the opt_if_not_exists). But don't do it twice. > + $$= 0; > + } > + | IF not EXISTS > + { > + Lex->check_exists= TRUE; > + $$=HA_LEX_CREATE_IF_NOT_EXISTS; > + } > ; > > opt_create_table_options: > @@ -5879,6 +5891,18 @@ opt_ident: > | field_ident { $$=$1.str; } > ; > > +opt_if_not_exists_ident: > + opt_if_not_exists opt_ident > + { > + LEX *lex= Lex; > + if (lex->check_exists && lex->sql_command != SQLCOM_ALTER_TABLE) > + { > + my_parse_error(ER(ER_SYNTAX_ERROR)); > + MYSQL_YYABORT; > + } > + $$= $2; > + }; > + why did you create opt_if_not_exists_ident rule, instead of adding opt_if_not_exists before ident in the alter table grammar? > opt_component: > /* empty */ { $$= null_lex_str; } > | '.' ident { $$= $2; } > @@ -6455,6 +6482,11 @@ opt_column: > | COLUMN_SYM {} > ; > > +opt_column_if_exists: > + opt_column if_exists > + {} > + ; > + same as above - why a special rule? > opt_ignore: > /* empty */ { Lex->ignore= 0;} > | IGNORE_SYM { Lex->ignore= 1;} > @@ -10096,8 +10128,16 @@ table_alias_ref: > ; > > if_exists: please, rename this old rule to opt_if_exists > - /* empty */ { $$= 0; } > - | IF EXISTS { $$= 1; } > + /* empty */ > + { > + Lex->check_exists= FALSE; > + $$= 0; > + } > + | IF EXISTS > + { > + Lex->check_exists= TRUE; > + $$= 1; > + } > ; > > opt_temporary: you didn't change CREATE INDEX and DROP INDEX, did you? and ALTER TABLE DROP PRIMARY KEY isn't covered either > === modified file 'sql/sql_table.cc' > --- sql/sql_table.cc 2012-04-05 21:07:18 +0000 > +++ sql/sql_table.cc 2012-07-11 14:28:40 +0000 > @@ -5788,6 +5788,226 @@ is_index_maintenance_unique (TABLE *tabl > > > /* > + Preparation for table creation > + > + SYNOPSIS > + handle_if_exists_option() Why did you do that in a separate function? You have basically duplicated all checks. normal alter table code checks for duplicate columns/key names and for non-existant columns/keys for DROP. this code is still in place after your patch. so, basically, you'll check for duplicates and non-existant names twice. what's the point of that? I'd expect you to take check_exists flag into account in the existing checking code and convert errors into warnings as appropriate. > + thd Thread object. > + table The altered table. > + alter_info List of columns and indexes to create > + > + DESCRIPTION > + Looks for the IF [NOT] EXISTS options, checks the states and remove items > + from the list if existing found. > + > + RETURN VALUES > + NONE > +*/ > + > +static void > +handle_if_exists_options(THD *thd, TABLE *table, Alter_info *alter_info) > +{ > + Field **f_ptr; Regards, Sergei

2 9

Re: [Maria-developers] [Commits] Rev 3435: MDEV-532: Async InnoDB commit checkpoint. in http://bazaar.launchpad.net/~maria-captains/maria/10.0
by Kristian Nielsen 14 Dec '12

14 Dec '12

Hi Serg, As we discussed under review of MDEV-232, here is a separate patch that makes InnoDB/XtraDB commit checkpointing be more asynchroneous. See MDEV-532 for further description of this task. I hope you will review this, at your convenience. - Kristian. knielsen(a)knielsen-hq.org writes: > At http://bazaar.launchpad.net/~maria-captains/maria/10.0 > > ------------------------------------------------------------ > revno: 3435 > revision-id: knielsen(a)knielsen-hq.org-20120914124453-zsap6hjclq3vrb6n > parent: knielsen(a)knielsen-hq.org-20120913123129-kaujy4cw0jc9o08k > committer: knielsen(a)knielsen-hq.org > branch nick: work-10.0-mdev225-181-232 > timestamp: Fri 2012-09-14 14:44:53 +0200 > message: > MDEV-532: Async InnoDB commit checkpoint. > > Make the commit checkpoint inside InnoDB be asynchroneous. > Implement a background thread in binlog to do the writing and flushing of > binlog checkpoint events to disk. > === modified file 'mysql-test/suite/binlog/r/binlog_checkpoint.result' > --- a/mysql-test/suite/binlog/r/binlog_checkpoint.result 2012-09-13 12:31:29 +0000 > +++ b/mysql-test/suite/binlog/r/binlog_checkpoint.result 2012-09-14 12:44:53 +0000 > @@ -70,8 +70,14 @@ show binlog events in 'master-bin.000003 > Log_name Pos Event_type Server_id End_log_pos Info > master-bin.000003 # Format_desc # # SERVER_VERSION, BINLOG_VERSION > master-bin.000003 # Binlog_checkpoint # # master-bin.000001 > +SET DEBUG_SYNC= "RESET"; > +SET @old_dbug= @@global.DEBUG_DBUG; > +SET GLOBAL debug_dbug="+d,binlog_background_checkpoint_processed"; > SET DEBUG_SYNC= "now SIGNAL con2_continue"; > con1 is still pending, no new binlog checkpoint should have been logged. > +SET DEBUG_SYNC= "now WAIT_FOR binlog_background_checkpoint_processed"; > +SET GLOBAL debug_dbug= @old_dbug; > +SET DEBUG_SYNC= "RESET"; > show binlog events in 'master-bin.000003' from <binlog_start>; > Log_name Pos Event_type Server_id End_log_pos Info > master-bin.000003 # Format_desc # # SERVER_VERSION, BINLOG_VERSION > > === modified file 'mysql-test/suite/binlog/r/binlog_xa_recover.result' > --- a/mysql-test/suite/binlog/r/binlog_xa_recover.result 2012-09-13 12:31:29 +0000 > +++ b/mysql-test/suite/binlog/r/binlog_xa_recover.result 2012-09-14 12:44:53 +0000 > @@ -118,7 +118,11 @@ master-bin.00000<binlog_start> # Table_m > master-bin.00000<binlog_start> # Write_rows # # table_id: # flags: STMT_END_F > master-bin.00000<binlog_start> # Xid # # COMMIT /* XID */ > SET DEBUG_SYNC= "now SIGNAL con10_cont"; > +SET @old_dbug= @@global.DEBUG_DBUG; > +SET GLOBAL debug_dbug="+d,binlog_background_checkpoint_processed"; > SET DEBUG_SYNC= "now SIGNAL con12_cont"; > +SET DEBUG_SYNC= "now WAIT_FOR binlog_background_checkpoint_processed"; > +SET GLOBAL debug_dbug= @old_dbug; > SET DEBUG_SYNC= "now SIGNAL con11_cont"; > Checking that master-bin.000004 is the last binlog checkpoint > show binlog events in 'master-bin.00000<binlog_start>' from <binlog_start>; > > === modified file 'mysql-test/suite/binlog/t/binlog_checkpoint.test' > --- a/mysql-test/suite/binlog/t/binlog_checkpoint.test 2012-09-13 12:31:29 +0000 > +++ b/mysql-test/suite/binlog/t/binlog_checkpoint.test 2012-09-14 12:44:53 +0000 > @@ -71,6 +71,12 @@ SET DEBUG_SYNC= "now WAIT_FOR con2_ready > --let $binlog_file= master-bin.000003 > --source include/show_binlog_events.inc > > +# We need to sync the test case with the background processing of the > +# commit checkpoint, otherwise we get nondeterministic results. > +SET DEBUG_SYNC= "RESET"; > +SET @old_dbug= @@global.DEBUG_DBUG; > +SET GLOBAL debug_dbug="+d,binlog_background_checkpoint_processed"; > + > SET DEBUG_SYNC= "now SIGNAL con2_continue"; > > connection con2; > @@ -78,6 +84,12 @@ reap; > > connection default; > --echo con1 is still pending, no new binlog checkpoint should have been logged. > +# Make sure commit checkpoint is processed before we check that no checkpoint > +# event has been binlogged. > +SET DEBUG_SYNC= "now WAIT_FOR binlog_background_checkpoint_processed"; > +SET GLOBAL debug_dbug= @old_dbug; > +SET DEBUG_SYNC= "RESET"; > + > --let $binlog_file= master-bin.000003 > --source include/show_binlog_events.inc > > > === modified file 'mysql-test/suite/binlog/t/binlog_xa_recover.test' > --- a/mysql-test/suite/binlog/t/binlog_xa_recover.test 2012-09-13 12:31:29 +0000 > +++ b/mysql-test/suite/binlog/t/binlog_xa_recover.test 2012-09-14 12:44:53 +0000 > @@ -14,8 +14,24 @@ CREATE TABLE t1 (a INT PRIMARY KEY, b ME > # Insert some data to force a couple binlog rotations (3), so we get some > # normal binlog checkpoints before starting the test. > INSERT INTO t1 VALUES (100, REPEAT("x", 4100)); > +# Wait for the master-bin.000002 binlog checkpoint to appear. > +--let $wait_for_all= 0 > +--let $show_statement= SHOW BINLOG EVENTS IN "master-bin.000002" > +--let $field= Info > +--let $condition= = "master-bin.000002" > +--source include/wait_show_condition.inc > INSERT INTO t1 VALUES (101, REPEAT("x", 4100)); > +--let $wait_for_all= 0 > +--let $show_statement= SHOW BINLOG EVENTS IN "master-bin.000003" > +--let $field= Info > +--let $condition= = "master-bin.000003" > +--source include/wait_show_condition.inc > INSERT INTO t1 VALUES (102, REPEAT("x", 4100)); > +--let $wait_for_all= 0 > +--let $show_statement= SHOW BINLOG EVENTS IN "master-bin.000004" > +--let $field= Info > +--let $condition= = "master-bin.000004" > +--source include/wait_show_condition.inc > > # Now start a bunch of transactions that span multiple binlog > # files. Leave then in the state prepared-but-not-committed in the engine > @@ -153,10 +169,19 @@ SET DEBUG_SYNC= "now SIGNAL con10_cont"; > connection con10; > reap; > connection default; > + > +# We need to sync the test case with the background processing of the > +# commit checkpoint, otherwise we get nondeterministic results. > +SET @old_dbug= @@global.DEBUG_DBUG; > +SET GLOBAL debug_dbug="+d,binlog_background_checkpoint_processed"; > + > SET DEBUG_SYNC= "now SIGNAL con12_cont"; > connection con12; > reap; > connection default; > +SET DEBUG_SYNC= "now WAIT_FOR binlog_background_checkpoint_processed"; > +SET GLOBAL debug_dbug= @old_dbug; > + > SET DEBUG_SYNC= "now SIGNAL con11_cont"; > connection con11; > reap; > @@ -210,7 +235,20 @@ RESET MASTER; > # crash recovery fails due to the error insert used for previous test. > INSERT INTO t1 VALUES (21, REPEAT("x", 4100)); > INSERT INTO t1 VALUES (22, REPEAT("x", 4100)); > +# Wait for the master-bin.000003 binlog checkpoint to appear. > +--let $wait_for_all= 0 > +--let $show_statement= SHOW BINLOG EVENTS IN "master-bin.000003" > +--let $field= Info > +--let $condition= = "master-bin.000003" > +--source include/wait_show_condition.inc > INSERT INTO t1 VALUES (23, REPEAT("x", 4100)); > +# Wait for the last (master-bin.000004) binlog checkpoint to appear. > +--let $wait_for_all= 0 > +--let $show_statement= SHOW BINLOG EVENTS IN "master-bin.000004" > +--let $field= Info > +--let $condition= = "master-bin.000004" > +--source include/wait_show_condition.inc > + > --write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect > wait-binlog_xa_recover.test > EOF > > === modified file 'mysql-test/suite/perfschema/r/all_instances.result' > --- a/mysql-test/suite/perfschema/r/all_instances.result 2012-06-22 09:46:28 +0000 > +++ b/mysql-test/suite/perfschema/r/all_instances.result 2012-09-14 12:44:53 +0000 > @@ -76,6 +76,7 @@ wait/synch/mutex/sql/Master_info::run_lo > wait/synch/mutex/sql/Master_info::sleep_lock > wait/synch/mutex/sql/MDL_map::mutex > wait/synch/mutex/sql/MDL_wait::LOCK_wait_status > +wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_thread > wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index > wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xid_list > wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index > @@ -129,6 +130,8 @@ wait/synch/cond/sql/Master_info::sleep_c > wait/synch/cond/sql/Master_info::start_cond > wait/synch/cond/sql/Master_info::stop_cond > wait/synch/cond/sql/MDL_context::COND_wait_status > +wait/synch/cond/sql/MYSQL_BIN_LOG::COND_binlog_thread > +wait/synch/cond/sql/MYSQL_BIN_LOG::COND_binlog_thread_end > wait/synch/cond/sql/MYSQL_BIN_LOG::COND_queue_busy > wait/synch/cond/sql/MYSQL_BIN_LOG::COND_xid_list > wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond > > === modified file 'mysql-test/suite/perfschema/r/relaylog.result' > --- a/mysql-test/suite/perfschema/r/relaylog.result 2012-06-22 09:46:28 +0000 > +++ b/mysql-test/suite/perfschema/r/relaylog.result 2012-09-14 12:44:53 +0000 > @@ -56,8 +56,11 @@ where event_name like "%MYSQL_BIN_LOG%" > and event_name not like "%MYSQL_BIN_LOG::update_cond" > order by event_name; > EVENT_NAME COUNT_STAR > +wait/synch/cond/sql/MYSQL_BIN_LOG::COND_binlog_thread NONE > +wait/synch/cond/sql/MYSQL_BIN_LOG::COND_binlog_thread_end NONE > wait/synch/cond/sql/MYSQL_BIN_LOG::COND_queue_busy NONE > wait/synch/cond/sql/MYSQL_BIN_LOG::COND_xid_list NONE > +wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_thread MANY > wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index MANY > wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xid_list MANY > "Expect no slave relay log" > @@ -131,8 +134,11 @@ where event_name like "%MYSQL_BIN_LOG%" > and event_name not like "%MYSQL_BIN_LOG::update_cond" > order by event_name; > EVENT_NAME COUNT_STAR > +wait/synch/cond/sql/MYSQL_BIN_LOG::COND_binlog_thread MANY > +wait/synch/cond/sql/MYSQL_BIN_LOG::COND_binlog_thread_end NONE > wait/synch/cond/sql/MYSQL_BIN_LOG::COND_queue_busy NONE > -wait/synch/cond/sql/MYSQL_BIN_LOG::COND_xid_list NONE > +wait/synch/cond/sql/MYSQL_BIN_LOG::COND_xid_list MANY > +wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_thread MANY > wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index MANY > wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xid_list MANY > "Expect a slave relay log" > > === modified file 'sql/debug_sync.cc' > --- a/sql/debug_sync.cc 2012-03-28 17:26:00 +0000 > +++ b/sql/debug_sync.cc 2012-09-14 12:44:53 +0000 > @@ -984,6 +984,7 @@ static bool debug_sync_eval_action(THD * > DBUG_ENTER("debug_sync_eval_action"); > DBUG_ASSERT(thd); > DBUG_ASSERT(action_str); > + DBUG_PRINT("debug_sync", ("action_str='%s'", action_str)); > > /* > Get debug sync point name. Or a special command. > > === modified file 'sql/log.cc' > --- a/sql/log.cc 2012-09-13 12:31:29 +0000 > +++ b/sql/log.cc 2012-09-14 12:44:53 +0000 > @@ -53,6 +53,7 @@ > #include "rpl_handler.h" > #include "debug_sync.h" > #include "sql_show.h" > +#include "my_pthread.h" > > /* max size of the log message */ > #define MAX_LOG_BUFFER_SIZE 1024 > @@ -106,6 +107,14 @@ static SHOW_VAR binlog_status_vars_detai > {NullS, NullS, SHOW_LONG} > }; > > +/* Variables for the binlog background thread. */ > +static bool binlog_thread_started= false; > +static bool binlog_background_thread_stop= false; > +static MYSQL_BIN_LOG::xid_count_per_binlog * > + binlog_background_thread_queue= NULL; > + > +static bool start_binlog_background_thread(); > + > > /** > purge logs, master and slave sides both, related error code > @@ -2957,12 +2966,27 @@ void MYSQL_BIN_LOG::cleanup() > my_free(b); > } > > + /* Wait for the binlog thread to stop. */ > + if (!is_relay_log && binlog_thread_started) > + { > + mysql_mutex_lock(&LOCK_binlog_thread); > + binlog_background_thread_stop= true; > + mysql_cond_signal(&COND_binlog_thread); > + while (binlog_background_thread_stop) > + mysql_cond_wait(&COND_binlog_thread_end, &LOCK_binlog_thread); > + mysql_mutex_unlock(&LOCK_binlog_thread); > + binlog_thread_started= false; > + } > + > mysql_mutex_destroy(&LOCK_log); > mysql_mutex_destroy(&LOCK_index); > mysql_mutex_destroy(&LOCK_xid_list); > + mysql_mutex_destroy(&LOCK_binlog_thread); > mysql_cond_destroy(&update_cond); > mysql_cond_destroy(&COND_queue_busy); > mysql_cond_destroy(&COND_xid_list); > + mysql_cond_destroy(&COND_binlog_thread); > + mysql_cond_destroy(&COND_binlog_thread_end); > } > DBUG_VOID_RETURN; > } > @@ -2988,6 +3012,11 @@ void MYSQL_BIN_LOG::init_pthread_objects > mysql_cond_init(m_key_update_cond, &update_cond, 0); > mysql_cond_init(m_key_COND_queue_busy, &COND_queue_busy, 0); > mysql_cond_init(key_BINLOG_COND_xid_list, &COND_xid_list, 0); > + > + mysql_mutex_init(key_BINLOG_LOCK_binlog_thread, > + &LOCK_binlog_thread, MY_MUTEX_INIT_FAST); > + mysql_cond_init(key_BINLOG_COND_binlog_thread, &COND_binlog_thread, 0); > + mysql_cond_init(key_BINLOG_COND_binlog_thread_end, &COND_binlog_thread_end, 0); > } > > > @@ -3085,6 +3114,10 @@ bool MYSQL_BIN_LOG::open(const char *log > DBUG_ENTER("MYSQL_BIN_LOG::open"); > DBUG_PRINT("enter",("log_type: %d",(int) log_type_arg)); > > + if (!is_relay_log && !binlog_thread_started && > + start_binlog_background_thread()) > + DBUG_RETURN(1); > + > if (init_and_set_log_file_name(log_name, new_name, log_type_arg, > io_cache_type_arg)) > { > @@ -5540,11 +5573,7 @@ bool general_log_write(THD *thd, enum en > } > > > -/* > - I would like to make this function static, but this causes compiler warnings > - when it is declared as friend function in log.h. > -*/ > -void > +static void > binlog_checkpoint_callback(void *cookie) > { > MYSQL_BIN_LOG::xid_count_per_binlog *entry= > @@ -8116,9 +8145,128 @@ int TC_LOG_BINLOG::unlog(ulong cookie, m > void > TC_LOG_BINLOG::commit_checkpoint_notify(void *cookie) > { > - mark_xid_done(((xid_count_per_binlog *)cookie)->binlog_id, true); > + xid_count_per_binlog *entry= static_cast<xid_count_per_binlog *>(cookie); > + mysql_mutex_lock(&LOCK_binlog_thread); > + entry->next_in_queue= binlog_background_thread_queue; > + binlog_background_thread_queue= entry; > + mysql_cond_signal(&COND_binlog_thread); > + mysql_mutex_unlock(&LOCK_binlog_thread); > } > > +/* > + Binlog service thread. > + > + This thread is used to log binlog checkpoints in the background, rather than > + in the context of random storage engine threads that happen to call > + commit_checkpoint_notify_ha() and may not like the delays while syncing > + binlog to disk or may not be setup with all my_thread_init() and other > + necessary stuff. > + > + In the future, this thread could also be used to do log rotation in the > + background, which could elimiate all stalls around binlog rotations. > +*/ > +pthread_handler_t > +binlog_background_thread(void *arg __attribute__((unused))) > +{ > + bool stop; > + MYSQL_BIN_LOG::xid_count_per_binlog *queue, *next; > + THD *thd; > + > + my_thread_init(); > + thd= new THD; > + thd->system_thread= SYSTEM_THREAD_BINLOG_BACKGROUND; > + my_pthread_setspecific_ptr(THR_THD, thd); > + mysql_mutex_lock(&LOCK_thread_count); > + thd->thread_id= thread_id++; > + mysql_mutex_unlock(&LOCK_thread_count); > + > + for (;;) > + { > + /* > + Wait until there is something in the queue to process, or we are asked > + to shut down. > + */ > + thd_proc_info(thd, "Waiting for background binlog tasks"); > + mysql_mutex_lock(&mysql_bin_log.LOCK_binlog_thread); > + for (;;) > + { > + stop= binlog_background_thread_stop; > + queue= binlog_background_thread_queue; > + if (stop || queue) > + break; > + mysql_cond_wait(&mysql_bin_log.COND_binlog_thread, > + &mysql_bin_log.LOCK_binlog_thread); > + } > + /* Grab the queue, if any. */ > + binlog_background_thread_queue= NULL; > + mysql_mutex_unlock(&mysql_bin_log.LOCK_binlog_thread); > + > + /* Process any incoming commit_checkpoint_notify() calls. */ > + while (queue) > + { > + thd_proc_info(thd, "Processing binlog checkpoint notification"); > + /* Grab next pointer first, as mark_xid_done() may free the element. */ > + next= queue->next_in_queue; > + mysql_bin_log.mark_xid_done(queue->binlog_id, true); > + queue= next; > + > + DBUG_EXECUTE_IF("binlog_background_checkpoint_processed", > + DBUG_ASSERT(!debug_sync_set_action( > + thd, > + STRING_WITH_LEN("now SIGNAL binlog_background_checkpoint_processed"))); > + ); > + } > + > + if (stop) > + break; > + } > + > + thd_proc_info(thd, "Stopping binlog background thread"); > + > + mysql_mutex_lock(&LOCK_thread_count); > + delete thd; > + mysql_mutex_unlock(&LOCK_thread_count); > + > + my_thread_end(); > + > + /* Signal that we are (almost) stopped. */ > + mysql_mutex_lock(&mysql_bin_log.LOCK_binlog_thread); > + binlog_background_thread_stop= false; > + mysql_cond_signal(&mysql_bin_log.COND_binlog_thread_end); > + mysql_mutex_unlock(&mysql_bin_log.LOCK_binlog_thread); > + > + return 0; > +} > + > +#ifdef HAVE_PSI_INTERFACE > +static PSI_thread_key key_thread_binlog; > + > +static PSI_thread_info all_binlog_threads[]= > +{ > + { &key_thread_binlog, "binlog_background", PSI_FLAG_GLOBAL}, > +}; > +#endif /* HAVE_PSI_INTERFACE */ > + > +static bool > +start_binlog_background_thread() > +{ > + pthread_t th; > + > +#ifdef HAVE_PSI_INTERFACE > + if (PSI_server) > + PSI_server->register_thread("sql", all_binlog_threads, > + array_elements(all_binlog_threads)); > +#endif > + > + if (mysql_thread_create(key_thread_binlog, &th, NULL, > + binlog_background_thread, NULL)) > + return 1; > + > + binlog_thread_started= true; > + return 0; > +} > + > + > int TC_LOG_BINLOG::recover(LOG_INFO *linfo, const char *last_log_name, > IO_CACHE *first_log, > Format_description_log_event *fdle) > > === modified file 'sql/log.h' > --- a/sql/log.h 2012-09-13 12:31:29 +0000 > +++ b/sql/log.h 2012-09-14 12:44:53 +0000 > @@ -395,8 +395,6 @@ class MYSQL_QUERY_LOG: public MYSQL_LOG > #define BINLOG_COOKIE_IS_DUMMY(c) \ > ( ((ulong)(c)>>1) == BINLOG_COOKIE_DUMMY_ID ) > > -void binlog_checkpoint_callback(void *cookie); > - > class binlog_cache_mngr; > class MYSQL_BIN_LOG: public TC_LOG, private MYSQL_LOG > { > @@ -451,27 +449,6 @@ class MYSQL_BIN_LOG: public TC_LOG, priv > }; > > /* > - A list of struct xid_count_per_binlog is used to keep track of how many > - XIDs are in prepared, but not committed, state in each binlog. And how > - many commit_checkpoint_request()'s are pending. > - > - When count drops to zero in a binlog after rotation, it means that there > - are no more XIDs in prepared state, so that binlog is no longer needed > - for XA crash recovery, and we can log a new binlog checkpoint event. > - > - The list is protected against simultaneous access from multiple > - threads by LOCK_xid_list. > - */ > - struct xid_count_per_binlog : public ilink { > - char *binlog_name; > - uint binlog_name_len; > - ulong binlog_id; > - /* Total prepared XIDs and pending checkpoint requests in this binlog. */ > - long xid_count; > - xid_count_per_binlog(); /* Give link error if constructor used. */ > - }; > - I_List<xid_count_per_binlog> binlog_xid_count_list; > - /* > When this is set, a RESET MASTER is in progress. > > Then we should not write any binlog checkpoints into the binlog (that > @@ -480,7 +457,6 @@ class MYSQL_BIN_LOG: public TC_LOG, priv > checkpoint arrives - when all have arrived, RESET MASTER will complete. > */ > bool reset_master_pending; > - friend void binlog_checkpoint_callback(void *cookie); > > /* LOCK_log and LOCK_index are inited by init_pthread_objects() */ > mysql_mutex_t LOCK_index; > @@ -553,10 +529,35 @@ class MYSQL_BIN_LOG: public TC_LOG, priv > int write_transaction_or_stmt(group_commit_entry *entry); > bool write_transaction_to_binlog_events(group_commit_entry *entry); > void trx_group_commit_leader(group_commit_entry *leader); > - void mark_xid_done(ulong cookie, bool write_checkpoint); > - void mark_xids_active(ulong cookie, uint xid_count); > > public: > + /* > + A list of struct xid_count_per_binlog is used to keep track of how many > + XIDs are in prepared, but not committed, state in each binlog. And how > + many commit_checkpoint_request()'s are pending. > + > + When count drops to zero in a binlog after rotation, it means that there > + are no more XIDs in prepared state, so that binlog is no longer needed > + for XA crash recovery, and we can log a new binlog checkpoint event. > + > + The list is protected against simultaneous access from multiple > + threads by LOCK_xid_list. > + */ > + struct xid_count_per_binlog : public ilink { > + char *binlog_name; > + uint binlog_name_len; > + ulong binlog_id; > + /* Total prepared XIDs and pending checkpoint requests in this binlog. */ > + long xid_count; > + /* For linking in requests to the binlog background thread. */ > + xid_count_per_binlog *next_in_queue; > + xid_count_per_binlog(); /* Give link error if constructor used. */ > + }; > + I_List<xid_count_per_binlog> binlog_xid_count_list; > + mysql_mutex_t LOCK_binlog_thread; > + mysql_cond_t COND_binlog_thread; > + mysql_cond_t COND_binlog_thread_end; > + > using MYSQL_LOG::generate_name; > using MYSQL_LOG::is_open; > > @@ -712,6 +713,8 @@ class MYSQL_BIN_LOG: public TC_LOG, priv > bool appendv(const char* buf,uint len,...); > bool append(Log_event* ev); > > + void mark_xids_active(ulong cookie, uint xid_count); > + void mark_xid_done(ulong cookie, bool write_checkpoint); > void make_log_name(char* buf, const char* log_ident); > bool is_active(const char* log_file_name); > bool can_purge_log(const char *log_file_name); > > === modified file 'sql/mysqld.cc' > --- a/sql/mysqld.cc 2012-09-13 12:31:29 +0000 > +++ b/sql/mysqld.cc 2012-09-14 12:44:53 +0000 > @@ -724,6 +724,7 @@ PSI_mutex_key key_LOCK_des_key_file; > #endif /* HAVE_OPENSSL */ > > PSI_mutex_key key_BINLOG_LOCK_index, key_BINLOG_LOCK_xid_list, > + key_BINLOG_LOCK_binlog_thread, > key_delayed_insert_mutex, key_hash_filo_lock, key_LOCK_active_mi, > key_LOCK_connection_count, key_LOCK_crypt, key_LOCK_delayed_create, > key_LOCK_delayed_insert, key_LOCK_delayed_status, key_LOCK_error_log, > @@ -766,6 +767,7 @@ static PSI_mutex_info all_server_mutexes > > { &key_BINLOG_LOCK_index, "MYSQL_BIN_LOG::LOCK_index", 0}, > { &key_BINLOG_LOCK_xid_list, "MYSQL_BIN_LOG::LOCK_xid_list", 0}, > + { &key_BINLOG_LOCK_binlog_thread, "MYSQL_BIN_LOG::LOCK_binlog_thread", 0}, > { &key_RELAYLOG_LOCK_index, "MYSQL_RELAY_LOG::LOCK_index", 0}, > { &key_delayed_insert_mutex, "Delayed_insert::mutex", 0}, > { &key_hash_filo_lock, "hash_filo::lock", 0}, > @@ -834,6 +836,7 @@ PSI_cond_key key_PAGE_cond, key_COND_act > #endif /* HAVE_MMAP */ > > PSI_cond_key key_BINLOG_COND_xid_list, key_BINLOG_update_cond, > + key_BINLOG_COND_binlog_thread, key_BINLOG_COND_binlog_thread_end, > key_COND_cache_status_changed, key_COND_manager, > key_COND_rpl_status, key_COND_server_started, > key_delayed_insert_cond, key_delayed_insert_cond_client, > @@ -863,6 +866,8 @@ static PSI_cond_info all_server_conds[]= > #endif /* HAVE_MMAP */ > { &key_BINLOG_COND_xid_list, "MYSQL_BIN_LOG::COND_xid_list", 0}, > { &key_BINLOG_update_cond, "MYSQL_BIN_LOG::update_cond", 0}, > + { &key_BINLOG_COND_binlog_thread, "MYSQL_BIN_LOG::COND_binlog_thread", 0}, > + { &key_BINLOG_COND_binlog_thread_end, "MYSQL_BIN_LOG::COND_binlog_thread_end", 0}, > { &key_BINLOG_COND_queue_busy, "MYSQL_BIN_LOG::COND_queue_busy", 0}, > { &key_RELAYLOG_update_cond, "MYSQL_RELAY_LOG::update_cond", 0}, > { &key_RELAYLOG_COND_queue_busy, "MYSQL_RELAY_LOG::COND_queue_busy", 0}, > > === modified file 'sql/mysqld.h' > --- a/sql/mysqld.h 2012-09-13 12:31:29 +0000 > +++ b/sql/mysqld.h 2012-09-14 12:44:53 +0000 > @@ -226,6 +226,7 @@ extern PSI_mutex_key key_LOCK_des_key_fi > #endif > > extern PSI_mutex_key key_BINLOG_LOCK_index, key_BINLOG_LOCK_xid_list, > + key_BINLOG_LOCK_binlog_thread, > key_delayed_insert_mutex, key_hash_filo_lock, key_LOCK_active_mi, > key_LOCK_connection_count, key_LOCK_crypt, key_LOCK_delayed_create, > key_LOCK_delayed_insert, key_LOCK_delayed_status, key_LOCK_error_log, > @@ -257,6 +258,7 @@ extern PSI_cond_key key_PAGE_cond, key_C > #endif /* HAVE_MMAP */ > > extern PSI_cond_key key_BINLOG_COND_xid_list, key_BINLOG_update_cond, > + key_BINLOG_COND_binlog_thread, key_BINLOG_COND_binlog_thread_end, > key_COND_cache_status_changed, key_COND_manager, > key_COND_rpl_status, key_COND_server_started, > key_delayed_insert_cond, key_delayed_insert_cond_client, > > === modified file 'sql/rpl_rli.cc' > --- a/sql/rpl_rli.cc 2012-09-13 12:31:29 +0000 > +++ b/sql/rpl_rli.cc 2012-09-14 12:44:53 +0000 > @@ -58,6 +58,7 @@ Relay_log_info::Relay_log_info(bool is_s > { > DBUG_ENTER("Relay_log_info::Relay_log_info"); > > + relay_log.is_relay_log= TRUE; > #ifdef HAVE_PSI_INTERFACE > relay_log.set_psi_keys(key_RELAYLOG_LOCK_index, > key_RELAYLOG_update_cond, > @@ -206,8 +207,6 @@ a file name for --relay-log-index option > name_warning_sent= 1; > } > > - rli->relay_log.is_relay_log= TRUE; > - > /* > note, that if open() fails, we'll still have index file open > but a destructor will take care of that > > === modified file 'sql/sql_class.h' > --- a/sql/sql_class.h 2012-09-13 12:31:29 +0000 > +++ b/sql/sql_class.h 2012-09-14 12:44:53 +0000 > @@ -1244,7 +1244,8 @@ enum enum_thread_type > SYSTEM_THREAD_SLAVE_SQL= 4, > SYSTEM_THREAD_NDBCLUSTER_BINLOG= 8, > SYSTEM_THREAD_EVENT_SCHEDULER= 16, > - SYSTEM_THREAD_EVENT_WORKER= 32 > + SYSTEM_THREAD_EVENT_WORKER= 32, > + SYSTEM_THREAD_BINLOG_BACKGROUND= 64 > }; > > inline char const * > > === modified file 'storage/innobase/handler/ha_innodb.cc' > --- a/storage/innobase/handler/ha_innodb.cc 2012-09-13 12:31:29 +0000 > +++ b/storage/innobase/handler/ha_innodb.cc 2012-09-14 12:44:53 +0000 > @@ -106,6 +106,7 @@ static ulong commit_threads = 0; > static mysql_mutex_t commit_threads_m; > static mysql_cond_t commit_cond; > static mysql_mutex_t commit_cond_m; > +static mysql_mutex_t pending_checkpoint_mutex; > static bool innodb_inited = 0; > > #define INSIDE_HA_INNOBASE_CC > @@ -222,11 +223,13 @@ static mysql_pfs_key_t innobase_share_mu > static mysql_pfs_key_t commit_threads_m_key; > static mysql_pfs_key_t commit_cond_mutex_key; > static mysql_pfs_key_t commit_cond_key; > +static mysql_pfs_key_t pending_checkpoint_mutex_key; > > static PSI_mutex_info all_pthread_mutexes[] = { > {&commit_threads_m_key, "commit_threads_m", 0}, > {&commit_cond_mutex_key, "commit_cond_mutex", 0}, > - {&innobase_share_mutex_key, "innobase_share_mutex", 0} > + {&innobase_share_mutex_key, "innobase_share_mutex", 0}, > + {&pending_checkpoint_mutex_key, "pending_checkpoint_mutex", 0} > }; > > static PSI_cond_info all_innodb_conds[] = { > @@ -2592,6 +2595,9 @@ innobase_init( > mysql_mutex_init(commit_cond_mutex_key, > &commit_cond_m, MY_MUTEX_INIT_FAST); > mysql_cond_init(commit_cond_key, &commit_cond, NULL); > + mysql_mutex_init(pending_checkpoint_mutex_key, > + &pending_checkpoint_mutex, > + MY_MUTEX_INIT_FAST); > innodb_inited= 1; > #ifdef MYSQL_DYNAMIC_PLUGIN > if (innobase_hton != p) { > @@ -2639,6 +2645,7 @@ innobase_end( > mysql_mutex_destroy(&commit_threads_m); > mysql_mutex_destroy(&commit_cond_m); > mysql_cond_destroy(&commit_cond); > + mysql_mutex_destroy(&pending_checkpoint_mutex); > } > > DBUG_RETURN(err); > @@ -3008,6 +3015,16 @@ innobase_rollback_trx( > DBUG_RETURN(convert_error_code_to_mysql(error, 0, NULL)); > } > > + > +struct pending_checkpoint { > + struct pending_checkpoint *next; > + handlerton *hton; > + void *cookie; > + ib_uint64_t lsn; > +}; > +static struct pending_checkpoint *pending_checkpoint_list; > +static struct pending_checkpoint *pending_checkpoint_list_end; > + > /*****************************************************************//** > Handle a commit checkpoint request from server layer. > We simply flush the redo log immediately and do the notify call.*/ > @@ -3017,8 +3034,113 @@ innobase_checkpoint_request( > handlerton *hton, > void *cookie) > { > - log_buffer_flush_to_disk(); > - commit_checkpoint_notify_ha(hton, cookie); > + ib_uint64_t lsn; > + ib_uint64_t flush_lsn; > + struct pending_checkpoint * entry; > + > + /* Do the allocation outside of lock to reduce contention. The normal > + case is that not everything is flushed, so we will need to enqueue. */ > + entry = static_cast<struct pending_checkpoint *> > + (my_malloc(sizeof(*entry), MYF(MY_WME))); > + if (!entry) { > + sql_print_error("Failed to allocate %u bytes." > + " Commit checkpoint will be skipped.", > + static_cast<unsigned>(sizeof(*entry))); > + return; > + } > + > + entry->next = NULL; > + entry->hton = hton; > + entry->cookie = cookie; > + > + mysql_mutex_lock(&pending_checkpoint_mutex); > + lsn = log_get_lsn(); > + flush_lsn = log_get_flush_lsn(); > + if (lsn > flush_lsn) { > + /* Put the request in queue. > + When the log gets flushed past the lsn, we will remove the > + entry from the queue and notify the upper layer. */ > + entry->lsn = lsn; > + if (pending_checkpoint_list_end) { > + pending_checkpoint_list_end->next = entry; > + } else { > + pending_checkpoint_list = entry; > + } > + pending_checkpoint_list_end = entry; > + entry = NULL; > + } > + mysql_mutex_unlock(&pending_checkpoint_mutex); > + > + if (entry) { > + /* We are already flushed. Notify the checkpoint immediately. */ > + commit_checkpoint_notify_ha(entry->hton, entry->cookie); > + my_free(entry); > + } > +} > + > +/*****************************************************************//** > +Log code calls this whenever log has been written and/or flushed up > +to a new position. We use this to notify upper layer of a new commit > +checkpoint when necessary.*/ > +extern "C" UNIV_INTERN > +void > +innobase_mysql_log_notify( > +/*===============*/ > + ib_uint64_t write_lsn, /*!< in: LSN written to log file */ > + ib_uint64_t flush_lsn) /*!< in: LSN flushed to disk */ > +{ > + struct pending_checkpoint * pending; > + struct pending_checkpoint * entry; > + struct pending_checkpoint * last_ready; > + > + /* It is safe to do a quick check for NULL first without lock. > + Even if we should race, we will at most skip one checkpoint and > + take the next one, which is harmless. */ > + if (!pending_checkpoint_list) > + return; > + > + mysql_mutex_lock(&pending_checkpoint_mutex); > + pending = pending_checkpoint_list; > + if (!pending) > + { > + mysql_mutex_unlock(&pending_checkpoint_mutex); > + return; > + } > + > + last_ready = NULL; > + for (entry = pending; entry != NULL; entry = entry -> next) > + { > + if (entry->lsn > flush_lsn) > + break; > + last_ready = entry; > + } > + > + if (last_ready) > + { > + /* We found some pending checkpoints that are now flushed to > + disk. So remove them from the list. */ > + pending_checkpoint_list = entry; > + if (!entry) > + pending_checkpoint_list_end = NULL; > + } > + > + mysql_mutex_unlock(&pending_checkpoint_mutex); > + > + if (!last_ready) > + return; > + > + /* Now that we have released the lock, notify upper layer about all > + commit checkpoints that have now completed. */ > + for (;;) { > + entry = pending; > + pending = pending->next; > + > + commit_checkpoint_notify_ha(entry->hton, entry->cookie); > + > + my_free(entry); > + if (entry == last_ready) > + break; > + } > } > > /*****************************************************************//** > > === modified file 'storage/innobase/include/ha_prototypes.h' > --- a/storage/innobase/include/ha_prototypes.h 2011-04-26 17:55:52 +0000 > +++ b/storage/innobase/include/ha_prototypes.h 2012-09-14 12:44:53 +0000 > @@ -136,6 +136,17 @@ innobase_mysql_print_thd( > uint max_query_len); /*!< in: max query length to print, or 0 to > use the default max length */ > > +/*****************************************************************//** > +Log code calls this whenever log has been written and/or flushed up > +to a new position. We use this to notify upper layer of a new commit > +checkpoint when necessary.*/ > +UNIV_INTERN > +void > +innobase_mysql_log_notify( > +/*===============*/ > + ib_uint64_t write_lsn, /*!< in: LSN written to log file */ > + ib_uint64_t flush_lsn); /*!< in: LSN flushed to disk */ > + > /**************************************************************//** > Converts a MySQL type to an InnoDB type. Note that this function returns > the 'mtype' of InnoDB. InnoDB differentiates between MySQL's old <= 4.1 > > === modified file 'storage/innobase/include/log0log.h' > --- a/storage/innobase/include/log0log.h 2012-06-07 13:44:26 +0000 > +++ b/storage/innobase/include/log0log.h 2012-09-14 12:44:53 +0000 > @@ -151,6 +151,13 @@ UNIV_INLINE > ib_uint64_t > log_get_lsn(void); > /*=============*/ > +/************************************************************//** > +Gets the last lsn that is fully flushed to disk. > +@return last flushed lsn */ > +UNIV_INLINE > +ib_uint64_t > +log_get_flush_lsn(void); > +/*=============*/ > /**************************************************************** > Gets the log group capacity. It is OK to read the value without > holding log_sys->mutex because it is constant. > > === modified file 'storage/innobase/include/log0log.ic' > --- a/storage/innobase/include/log0log.ic 2011-04-05 07:18:43 +0000 > +++ b/storage/innobase/include/log0log.ic 2012-09-14 12:44:53 +0000 > @@ -411,6 +411,25 @@ log_get_lsn(void) > return(lsn); > } > > +/************************************************************//** > +Gets the last lsn that is fully flushed to disk. > +@return last flushed lsn */ > +UNIV_INLINE > +ib_uint64_t > +log_get_flush_lsn(void) > +/*=============*/ > +{ > + ib_uint64_t lsn; > + > + mutex_enter(&(log_sys->mutex)); > + > + lsn = log_sys->flushed_to_disk_lsn; > + > + mutex_exit(&(log_sys->mutex)); > + > + return(lsn); > +} > + > /**************************************************************** > Gets the log group capacity. It is OK to read the value without > holding log_sys->mutex because it is constant. > > === modified file 'storage/innobase/log/log0log.c' > --- a/storage/innobase/log/log0log.c 2012-03-21 03:48:12 +0000 > +++ b/storage/innobase/log/log0log.c 2012-09-14 12:44:53 +0000 > @@ -1353,6 +1353,8 @@ log_write_up_to( > ulint loop_count = 0; > #endif /* UNIV_DEBUG */ > ulint unlock; > + ib_uint64_t write_lsn; > + ib_uint64_t flush_lsn; > > if (recv_no_ibuf_operations) { > /* Recovery is running and no operations on the log files are > @@ -1530,8 +1532,13 @@ log_write_up_to( > > log_flush_do_unlocks(unlock); > > + write_lsn = log_sys->write_lsn; > + flush_lsn = log_sys->flushed_to_disk_lsn; > + > mutex_exit(&(log_sys->mutex)); > > + innobase_mysql_log_notify(write_lsn, flush_lsn); > + > return; > > do_waits: > > === modified file 'storage/xtradb/handler/ha_innodb.cc' > --- a/storage/xtradb/handler/ha_innodb.cc 2012-09-13 12:31:29 +0000 > +++ b/storage/xtradb/handler/ha_innodb.cc 2012-09-14 12:44:53 +0000 > @@ -120,6 +120,7 @@ static ulong commit_threads = 0; > static mysql_mutex_t commit_threads_m; > static mysql_cond_t commit_cond; > static mysql_mutex_t commit_cond_m; > +static mysql_mutex_t pending_checkpoint_mutex; > static bool innodb_inited = 0; > > > @@ -253,11 +254,13 @@ static mysql_pfs_key_t innobase_share_mu > static mysql_pfs_key_t commit_threads_m_key; > static mysql_pfs_key_t commit_cond_mutex_key; > static mysql_pfs_key_t commit_cond_key; > +static mysql_pfs_key_t pending_checkpoint_mutex_key; > > static PSI_mutex_info all_pthread_mutexes[] = { > {&commit_threads_m_key, "commit_threads_m", 0}, > {&commit_cond_mutex_key, "commit_cond_mutex", 0}, > - {&innobase_share_mutex_key, "innobase_share_mutex", 0} > + {&innobase_share_mutex_key, "innobase_share_mutex", 0}, > + {&pending_checkpoint_mutex_key, "pending_checkpoint_mutex", 0} > }; > > static PSI_cond_info all_innodb_conds[] = { > @@ -3060,6 +3063,9 @@ innobase_init( > mysql_mutex_init(commit_cond_mutex_key, > &commit_cond_m, MY_MUTEX_INIT_FAST); > mysql_cond_init(commit_cond_key, &commit_cond, NULL); > + mysql_mutex_init(pending_checkpoint_mutex_key, > + &pending_checkpoint_mutex, > + MY_MUTEX_INIT_FAST); > innodb_inited= 1; > #ifdef MYSQL_DYNAMIC_PLUGIN > if (innobase_hton != p) { > @@ -3107,6 +3113,7 @@ innobase_end( > mysql_mutex_destroy(&commit_threads_m); > mysql_mutex_destroy(&commit_cond_m); > mysql_cond_destroy(&commit_cond); > + mysql_mutex_destroy(&pending_checkpoint_mutex); > } > > DBUG_RETURN(err); > @@ -3500,6 +3507,16 @@ innobase_rollback_trx( > DBUG_RETURN(convert_error_code_to_mysql(error, 0, NULL)); > } > > + > +struct pending_checkpoint { > + struct pending_checkpoint *next; > + handlerton *hton; > + void *cookie; > + ib_uint64_t lsn; > +}; > +static struct pending_checkpoint *pending_checkpoint_list; > +static struct pending_checkpoint *pending_checkpoint_list_end; > + > /*****************************************************************//** > Handle a commit checkpoint request from server layer. > We simply flush the redo log immediately and do the notify call.*/ > @@ -3509,8 +3526,113 @@ innobase_checkpoint_request( > handlerton *hton, > void *cookie) > { > - log_buffer_flush_to_disk(); > - commit_checkpoint_notify_ha(hton, cookie); > + ib_uint64_t lsn; > + ib_uint64_t flush_lsn; > + struct pending_checkpoint * entry; > + > + /* Do the allocation outside of lock to reduce contention. The normal > + case is that not everything is flushed, so we will need to enqueue. */ > + entry = static_cast<struct pending_checkpoint *> > + (my_malloc(sizeof(*entry), MYF(MY_WME))); > + if (!entry) { > + sql_print_error("Failed to allocate %u bytes." > + " Commit checkpoint will be skipped.", > + static_cast<unsigned>(sizeof(*entry))); > + return; > + } > + > + entry->next = NULL; > + entry->hton = hton; > + entry->cookie = cookie; > + > + mysql_mutex_lock(&pending_checkpoint_mutex); > + lsn = log_get_lsn(); > + flush_lsn = log_get_flush_lsn(); > + if (lsn > flush_lsn) { > + /* Put the request in queue. > + When the log gets flushed past the lsn, we will remove the > + entry from the queue and notify the upper layer. */ > + entry->lsn = lsn; > + if (pending_checkpoint_list_end) { > + pending_checkpoint_list_end->next = entry; > + } else { > + pending_checkpoint_list = entry; > + } > + pending_checkpoint_list_end = entry; > + entry = NULL; > + } > + mysql_mutex_unlock(&pending_checkpoint_mutex); > + > + if (entry) { > + /* We are already flushed. Notify the checkpoint immediately. */ > + commit_checkpoint_notify_ha(entry->hton, entry->cookie); > + my_free(entry); > + } > +} > + > +/*****************************************************************//** > +Log code calls this whenever log has been written and/or flushed up > +to a new position. We use this to notify upper layer of a new commit > +checkpoint when necessary.*/ > +extern "C" UNIV_INTERN > +void > +innobase_mysql_log_notify( > +/*===============*/ > + ib_uint64_t write_lsn, /*!< in: LSN written to log file */ > + ib_uint64_t flush_lsn) /*!< in: LSN flushed to disk */ > +{ > + struct pending_checkpoint * pending; > + struct pending_checkpoint * entry; > + struct pending_checkpoint * last_ready; > + > + /* It is safe to do a quick check for NULL first without lock. > + Even if we should race, we will at most skip one checkpoint and > + take the next one, which is harmless. */ > + if (!pending_checkpoint_list) > + return; > + > + mysql_mutex_lock(&pending_checkpoint_mutex); > + pending = pending_checkpoint_list; > + if (!pending) > + { > + mysql_mutex_unlock(&pending_checkpoint_mutex); > + return; > + } > + > + last_ready = NULL; > + for (entry = pending; entry != NULL; entry = entry -> next) > + { > + if (entry->lsn > flush_lsn) > + break; > + last_ready = entry; > + } > + > + if (last_ready) > + { > + /* We found some pending checkpoints that are now flushed to > + disk. So remove them from the list. */ > + pending_checkpoint_list = entry; > + if (!entry) > + pending_checkpoint_list_end = NULL; > + } > + > + mysql_mutex_unlock(&pending_checkpoint_mutex); > + > + if (!last_ready) > + return; > + > + /* Now that we have released the lock, notify upper layer about all > + commit checkpoints that have now completed. */ > + for (;;) { > + entry = pending; > + pending = pending->next; > + > + commit_checkpoint_notify_ha(entry->hton, entry->cookie); > + > + my_free(entry); > + if (entry == last_ready) > + break; > + } > } > > /*****************************************************************//** > > === modified file 'storage/xtradb/include/ha_prototypes.h' > --- a/storage/xtradb/include/ha_prototypes.h 2012-02-21 19:51:56 +0000 > +++ b/storage/xtradb/include/ha_prototypes.h 2012-09-14 12:44:53 +0000 > @@ -136,6 +136,17 @@ innobase_mysql_print_thd( > uint max_query_len); /*!< in: max query length to print, or 0 to > use the default max length */ > > +/*****************************************************************//** > +Log code calls this whenever log has been written and/or flushed up > +to a new position. We use this to notify upper layer of a new commit > +checkpoint when necessary.*/ > +UNIV_INTERN > +void > +innobase_mysql_log_notify( > +/*===============*/ > + ib_uint64_t write_lsn, /*!< in: LSN written to log file */ > + ib_uint64_t flush_lsn); /*!< in: LSN flushed to disk */ > + > /**************************************************************//** > Converts a MySQL type to an InnoDB type. Note that this function returns > the 'mtype' of InnoDB. InnoDB differentiates between MySQL's old <= 4.1 > > === modified file 'storage/xtradb/include/log0log.h' > --- a/storage/xtradb/include/log0log.h 2012-08-27 16:13:17 +0000 > +++ b/storage/xtradb/include/log0log.h 2012-09-14 12:44:53 +0000 > @@ -151,6 +151,13 @@ UNIV_INLINE > ib_uint64_t > log_get_lsn(void); > /*=============*/ > +/************************************************************//** > +Gets the last lsn that is fully flushed to disk. > +@return last flushed lsn */ > +UNIV_INLINE > +ib_uint64_t > +log_get_flush_lsn(void); > +/*=============*/ > /**************************************************************** > Gets the log group capacity. It is OK to read the value without > holding log_sys->mutex because it is constant. > > === modified file 'storage/xtradb/include/log0log.ic' > --- a/storage/xtradb/include/log0log.ic 2011-07-14 19:22:41 +0000 > +++ b/storage/xtradb/include/log0log.ic 2012-09-14 12:44:53 +0000 > @@ -411,6 +411,25 @@ log_get_lsn(void) > return(lsn); > } > > +/************************************************************//** > +Gets the last lsn that is fully flushed to disk. > +@return last flushed lsn */ > +UNIV_INLINE > +ib_uint64_t > +log_get_flush_lsn(void) > +/*=============*/ > +{ > + ib_uint64_t lsn; > + > + mutex_enter(&(log_sys->mutex)); > + > + lsn = log_sys->flushed_to_disk_lsn; > + > + mutex_exit(&(log_sys->mutex)); > + > + return(lsn); > +} > + > /**************************************************************** > Gets the log group capacity. It is OK to read the value without > holding log_sys->mutex because it is constant. > > === modified file 'storage/xtradb/log/log0log.c' > --- a/storage/xtradb/log/log0log.c 2012-08-27 16:13:17 +0000 > +++ b/storage/xtradb/log/log0log.c 2012-09-14 12:44:53 +0000 > @@ -1390,6 +1390,8 @@ log_write_up_to( > ulint loop_count = 0; > #endif /* UNIV_DEBUG */ > ulint unlock; > + ib_uint64_t write_lsn; > + ib_uint64_t flush_lsn; > > if (recv_no_ibuf_operations) { > /* Recovery is running and no operations on the log files are > @@ -1568,8 +1570,13 @@ log_write_up_to( > > log_flush_do_unlocks(unlock); > > + write_lsn = log_sys->write_lsn; > + flush_lsn = log_sys->flushed_to_disk_lsn; > + > mutex_exit(&(log_sys->mutex)); > > + innobase_mysql_log_notify(write_lsn, flush_lsn); > + > return; > > do_waits: > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

2 2

[Maria-developers] API Documentaion for Replication Features
by Gordan Bobic 03 Dec '12

03 Dec '12

Hi, I have a need for replicating data from multiple masters (independent data going to different tables). I have looked into the following with no success: Tungsten: Slow, CPU hungry, outrageously bloated, and unacceptably unreliable (replication will stall silently at times, and the only way to resume it is to offline and online the replication service). mysql-replication-listener: Just about got it to work with local binlogs, but haven't managed to get it to connect via TCP to a MySQL server. So what I was hoping to do is write something based on the existing MySQL code since at least that should "just work". The problem is - I am completely unfamiliar with MySQL internals, and I haven't found any function level API documentation. So, would anyone here be kind enough to point me at the specific bits of code in MySQL that handle: 1) Slave TCP connections to the master and binlog fetching 2) Binlog parsing 3) Config file parsing In the longer term I'd like to write a patch that adds multiple-master functionality into MySQL itself. For configuring it, I was thinking about the following WRT configuration in my.cnf with multiple section instances, e.g.: [replicator] server-id=12345 master-host=server1 master-port=3306 master-user=user1 master-password=password1 replicate-do-table=db1.table1 [replicator] server-id=12345 master-host=server2 master-port=3306 master-user=user2 master-password=password2 replicate-do-table=db2.table2 etc. Any pointers would be greatly appreciated. If somebody here is already undertaking a similar effort, I would rather like to lend a hand. TIA. Gordan

4 9

[Maria-developers] MariaDB 5.5.28a release prep
by Sergei Golubchik 28 Nov '12

28 Nov '12

Preliminary release notes and changelog pages have been created for 5.5.28a https://kb.askmonty.org/en/mariadb-5528a-release-notes/ https://kb.askmonty.org/en/mariadb-5528a-changelog/ Please let me know if there is anything in particular which should be mentioned in the release notes. Thanks, Sergei _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

1 0

[Maria-developers] Review of mdev-452
by Michael Widenius 27 Nov '12

27 Nov '12

Hi! Here is the review of https://mariadb.atlassian.net/browse/MDEV-452 (Add full support for auto-initialized/updated timestamp and datetime) === modified file 'mysql-test/r/log_tables.result' --- mysql-test/r/log_tables.result 2011-10-19 19:45:18 +0000 +++ mysql-test/r/log_tables.result 2012-10-17 12:43:56 +0000 @@ -53,7 +53,7 @@ ERROR HY000: You can't use locks with lo show create table mysql.general_log; Table Create Table general_log CREATE TABLE `general_log` ( - `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), Why does all log tables now have CURRENT_TIMESTAMP(6) instead of CURRENT_TIMESTAMP ? This would cause a problem for someone that updates to MySQL 10.0 and then want to go back... Many of the other result file changes comes from the same change for general_log and slow_log. Is this also the case for MySQL 5.6 or only for MariaDB? Answer from Timour on IRC: Timour> This comes from MySQL 5.6 and we need to stay compatbile. === modified file 'sql/event_db_repository.cc' --- sql/event_db_repository.cc 2012-09-04 16:26:30 +0000 +++ sql/event_db_repository.cc 2012-10-17 12:43:56 +0000 @@ -829,9 +829,6 @@ Event_db_repository::update_event(THD *t (int) table->field[ET_FIELD_ON_COMPLETION]->val_int())) goto end; - /* Don't update create on row update. */ - table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; - Why don't we anymore need to tell the rest of the code that the automatic fields shouldn't be updated from this function? Timour> As I remember, the main change compared to the old design is Timour> that we no longer update timestamps inside the handler Timour> So the place where timestamp updates are triggered is such Timour> that such updates will no longer be triggered in these cases. Yes, mysql_event_fill_row() doesn't call fill_record_n_invoke_before_triggers(). It instead has own code that probably not calling setting of any default fields. This does however mean that one can never get automatic timestamps with events. === modified file 'sql/field.cc' --- sql/field.cc 2012-09-08 22:22:06 +0000 +++ sql/field.cc 2012-10-17 12:43:56 +0000 @@ -4419,10 +4419,12 @@ Field_timestamp::Field_timestamp(uchar * { /* For 4.0 MYD and 4.0 InnoDB compatibility */ flags|= UNSIGNED_FLAG | BINARY_FLAG; - if (unireg_check != NONE && !share->timestamp_field) + if (unireg_check != NONE) { - /* This timestamp has auto-update */ - share->timestamp_field= this; + /* + This TIMESTAMP column is hereby quietly assumed to have an insert or + update default function. + */ We need to here add a comment what the TIMESTAMP_FLAG now means, as it has lost it's orginal meaning. Add to the above comment: /* We mark the flag with TIMESTAMP_FLAG to indicate to the client that this field will be automaticly updated on insert */ flags|= TIMESTAMP_FLAG; if (unireg_check != TIMESTAMP_DN_FIELD) flags|= ON_UPDATE_NOW_FLAG; Please also fix the function comment that refers to TABLE::timestamp_field <cut> +void Field_timestamp::set_explicit_default(Item *value) +{ + if (value && + ((value->type() == Item::DEFAULT_VALUE_ITEM && + !((Item_default_value*)value)->arg) || + (!maybe_null() && value->is_null()))) + return; + flags|= HAS_EXPLICIT_DEFAULT; +} The above function confused me greatly (mostly because of names) You should not have to check for value != 0. An item should never be 0. I noticed that some code called set_explicit_default() with NULL, but it would be better to for this add a general item function: field->set_explicit_default(NULL) -> field->mark_that_field_has_been_given_a_value() Where: Field::mark_that_field_has_been_given_a_value() { flags|= HAS_EXPLICIT_VALUE; } (Note name change for HAS_EXPLICIT_DEFAULT) This can be inline and we will win some function calls. The set_explicit_default() function also needs a comment as it's very confusing. To clarify the code, lets first add a function: /* Returns 1 if the default returns the default value for a column */ Item_default_value::default_column() { return arg != 0; } And then original function would look like: /** Mark columns that has been given a value with HAS_EXPLICIT_VALUE. For timestamp columns, the only case where a column is not marked with been given a value are: - It's explicitely assigned with DEFAULT - We assign NULL to a timestamp field that is defined as NOT NULL. This is how MySQL has worked since it's start. */ void Field_timestamp::set_explicit_default(Item *value) { if (((value->type() == Item::DEFAULT_VALUE_ITEM && !(Item_default_value*) value)->default_column()) || (!maybe_null() && value->is_null())) return; mark_that_field_has_been_given_a_value(); } <cut> + +int Field_datetime::set_time() +{ + THD *thd= current_thd; current_thd -> table->in_use; + MYSQL_TIME now_time; + thd->variables.time_zone->gmt_sec_to_TIME(&now_time, thd->query_start()); + now_time.second_part= thd->query_start_sec_part(); + set_notnull(); + store_TIME(&now_time); + thd->time_zone_used= 1; + return 0; +} @@ -8857,16 +8866,37 @@ bool Create_field::init(THD *thd, char * { uint sign_len, allowed_type_modifier= 0; ulong max_field_charlength= MAX_FIELD_CHARLENGTH; + const bool on_update_is_function= + (fld_on_update_value != NULL && + fld_on_update_value->type() == Item::FUNC_ITEM); DBUG_ENTER("Create_field::init()"); field= 0; field_name= fld_name; - def= fld_default_value; flags= fld_type_modifier; option_list= create_opt; - unireg_check= (fld_type_modifier & AUTO_INCREMENT_FLAG ? - Field::NEXT_NUMBER : Field::NONE); + + if (fld_default_value != NULL && fld_default_value->type() == Item::FUNC_ITEM) + { + /* There is a function default for insertions. */ + def= NULL; + unireg_check= on_update_is_function ? + Field::TIMESTAMP_DNUN_FIELD : // for insertions and for updates. + Field::TIMESTAMP_DN_FIELD; // only for insertions. Add braces around the on_update_is... to get better indentation. + } + else + { + /* No function default for insertions. Either NULL or a constant. */ + def= fld_default_value; + if (on_update_is_function) + unireg_check= Field::TIMESTAMP_UN_FIELD; // function default for updates + else + unireg_check= (fld_type_modifier & AUTO_INCREMENT_FLAG) != 0 ? + Field::NEXT_NUMBER : // Automatic increment. + Field::NONE; Add () around expression. + } + <cut> + + +/** + Mark the field as having an explicit default value. + + @param value if available, the value that the field is being set to + + @note + Fields that have an explicit default value should not be updated + automatically via the DEFAULT or ON UPDATE functions. The functions + that deal with data change functionality (INSERT/UPDATE/LOAD), + determine if there is an explicit value for each field before performing + the data change, and call this method to mark the field. + + If the 'value' parameter is NULL, then the field is marked unconditionally + as having an explicit value. If 'value' is not NULL, then it can be further + analyzed to check if it really should count as a value. +*/ + +void Field::set_explicit_default(Item *value) +{ /* Return if value is DEFAULT */ + if (value && value->type() == Item::DEFAULT_VALUE_ITEM && + !((Item_default_value*)value)->arg) + return; + flags|= HAS_EXPLICIT_DEFAULT; +} Remove checking if value is null. flags|= HAS_EXPLICIT_DEFAULT -> flags|= HAS_EXPLICIT_VALUE === modified file 'sql/field.h' @@ -1239,12 +1270,26 @@ class Field_timestamp :public Field_str virtual int set_time(); <cut> + virtual void set_explicit_default(Item *value); + virtual int evaluate_insert_default_function() + { + int res= 0; + if (has_insert_default_function()) + res= set_time(); + return res; + } + virtual int evaluate_update_default_function() + { + int res= 0; + if (has_update_default_function()) + res= set_time(); + return res; + } /* Get TIMESTAMP field value as seconds since begging of Unix Epoch */ virtual my_time_t get_timestamp(ulong *sec_part) const; virtual void store_TIME(my_time_t timestamp, ulong sec_part) @@ -1252,7 +1297,6 @@ class Field_timestamp :public Field_str int4store(ptr,timestamp); } bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate); - timestamp_auto_set_type get_auto_set_type() const; uchar *pack(uchar *to, const uchar *from, uint max_length __attribute__((unused))) { @@ -1503,6 +1547,28 @@ class Field_datetime :public Field_tempo void sql_type(String &str) const; bool zero_pack() const { return 1; } bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate); + virtual int set_time(); + virtual void set_default() + { + if (has_insert_default_function()) + set_time(); + else + Field::set_default(); + } + virtual int evaluate_insert_default_function() + { + int res= 0; + if (has_insert_default_function()) + res= set_time(); + return res; + } + virtual int evaluate_update_default_function() + { + int res= 0; + if (has_update_default_function()) + res= set_time(); + return res; + } It's a bit of a shame that we need to have the exact same functions twice? One way to solve it would be to add these functions to Field_str, as both Field_timestamp() and Field_datetime() depends on this class. === modified file 'sql/sql_base.cc' <cut> static bool -fill_record(THD * thd, List<Item> &fields, List<Item> &values, +fill_record(THD * thd, TABLE *table_arg, List<Item> &fields, List<Item> &values, bool ignore_errors) { Why this change (was not needed for this functionality). timour> I think I had a different implementation before, then I kept this timrou> change, because it looked simpler. bool -fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields, +fill_record_n_invoke_before_triggers(THD *thd, TABLE *table, List<Item> &fields, List<Item> &values, bool ignore_errors, - Table_triggers_list *triggers, enum trg_event_type event) { bool result; - result= (fill_record(thd, fields, values, ignore_errors) || + Table_triggers_list *triggers= table->triggers; + result= (fill_record(thd, table, fields, values, ignore_errors) || (triggers && triggers->process_triggers(thd, event, TRG_ACTION_BEFORE, TRUE))); /* @@ -8925,7 +8924,6 @@ fill_record_n_invoke_before_triggers(THD */ if (!result && triggers) { - TABLE *table= 0; List_iterator_fast<Item> f(fields); Item *fld; Item_field *item_field; @@ -8933,9 +8931,7 @@ fill_record_n_invoke_before_triggers(THD { fld= (Item_field*)f++; item_field= fld->filed_for_view_update(); - if (item_field && item_field->field && - (table= item_field->field->table) && - table->vfield) + if (item_field && item_field->field && table && table->vfield) result= update_virtual_fields(thd, table, TRUE); Add: DBUG_ASSERT(table == item_field->field->table) Note that if we can only have one table here, than we could also replace: if (!result && triggers) with: if (!result && triggers && table) As the loop will not do anything if table == 0. <cut> bool -fill_record_n_invoke_before_triggers(THD *thd, Field **ptr, +fill_record_n_invoke_before_triggers(THD *thd, TABLE *table, Field **ptr, List<Item> &values, bool ignore_errors, - Table_triggers_list *triggers, enum trg_event_type event) { bool result; - result= (fill_record(thd, ptr, values, ignore_errors, FALSE) || + Table_triggers_list *triggers= table->triggers; + result= (fill_record(thd, table, ptr, values, ignore_errors, FALSE) || (triggers && triggers->process_triggers(thd, event, TRG_ACTION_BEFORE, TRUE))); /* @@ -9071,7 +9064,6 @@ fill_record_n_invoke_before_triggers(THD */ if (!result && triggers && *ptr) { - TABLE *table= (*ptr)->table; Add DBUG_ASSERT(table == (*ptr)->table); if (table->vfield) result= update_virtual_fields(thd, table, TRUE); } <cut> === modified file 'sql/sql_insert.cc' @@ -1696,13 +1660,32 @@ int write_record(THD *thd, TABLE *table, restore_record(table,record[1]); DBUG_ASSERT(info->update_fields->elements == info->update_values->elements); - if (fill_record_n_invoke_before_triggers(thd, *info->update_fields, + if (fill_record_n_invoke_before_triggers(thd, table, *info->update_fields, *info->update_values, info->ignore, - table->triggers, TRG_EVENT_UPDATE)) goto before_trg_err; + bool different_records= (!records_are_comparable(table) || + compare_record(table)); + /* + Default fields must be updated before checking view updateability. + This branch of INSERT is executed only when a UNIQUE key was violated + with the ON DUPLICATE KEY UPDATE option. In this case the INSERT + operation is transformed to an UPDATE, and the default fields must + be updated as if this is an UPDATE. + */ + if (different_records && table->default_field) + { + bool res; + enum_sql_command cmd= thd->lex->sql_command; + thd->lex->sql_command= SQLCOM_UPDATE; + res= table->update_default_fields(); + thd->lex->sql_command= cmd; + if (res) + goto err; + } Another way to do the above nicer would be to give thd->lex->sql_command as an argument to update_default_fields(). Another option would be to have: update_defaults_fields(table) { return update_defaults_fields(table, table->in_use->lex->cmd); } And use the later in this case. However this is not that critical to fix... <cut> + Field **field,**org_field, *found_next_number_field, **dfield_ptr; You need to do dfield_ptr= 0 to avoid a compiler warning. @@ -2390,6 +2370,12 @@ TABLE *Delayed_insert::get_local_table(T bitmap= (uchar*) (field + share->fields + 1); copy->record[0]= (bitmap + share->column_bitmap_size*3); memcpy((char*) copy->record[0], (char*) table->record[0], share->reclength); + if (share->default_fields) + { + copy->default_field= (Field**) client_thd->alloc((share->default_fields+1)* + sizeof(Field**)); You should check for error condition here. + dfield_ptr= copy->default_field; + } /* Make a copy of all fields. The copied fields need to point into the copied record. This is done @@ -2407,18 +2393,19 @@ TABLE *Delayed_insert::get_local_table(T (*field)->move_field_offset(adjust_ptrs); // Point at copy->record[0] if (*org_field == found_next_number_field) (*field)->table->found_next_number_field= *field; + if (share->default_fields && + ((*org_field)->has_insert_default_function() || + (*org_field)->has_update_default_function())) + { + /* Put the newly copied field into the set of default fields. */ + *dfield_ptr= *field; + (*dfield_ptr)->unireg_check= (*org_field)->unireg_check; It's strange that new_field() resets unireg_check. Don't know why this is done and we should at some point fix that it's not reset and see what happens. + dfield_ptr++; + } } *field=0; <cut> @@ -850,7 +837,7 @@ read_fixed_length(THD *thd, COPY_INFO &i ER(ER_WARN_TOO_FEW_RECORDS), thd->warning_info->current_row_for_warning()); if (!field->maybe_null() && field->type() == FIELD_TYPE_TIMESTAMP) - ((Field_timestamp*) field)->set_time(); + field->set_time(); Shouldn't the above be done now by testing for if the field has an insert_default_function? @@ -994,10 +982,11 @@ read_sep_field(THD *thd, COPY_INFO &info DBUG_RETURN(1); } field->set_null(); + field->set_explicit_default(NULL); if (!field->maybe_null()) { if (field->type() == MYSQL_TYPE_TIMESTAMP) - ((Field_timestamp*) field)->set_time(); + field->set_time(); shouldn't we use an insert_default_function here too ? else if (field != table->next_number_field) field->set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_NULL_TO_NOTNULL, 1); @@ -1066,7 +1056,7 @@ read_sep_field(THD *thd, COPY_INFO &info DBUG_RETURN(1); } if (!field->maybe_null() && field->type() == FIELD_TYPE_TIMESTAMP) - ((Field_timestamp*) field)->set_time(); + field->set_time(); shouldn't we use an insert_default_function here too ? /* TODO: We probably should not throw warning for each field. But how about intention to always have the same number @@ -1206,7 +1196,7 @@ read_xml_field(THD *thd, COPY_INFO &info if (!field->maybe_null()) { if (field->type() == FIELD_TYPE_TIMESTAMP) - ((Field_timestamp *) field)->set_time(); + field->set_time(); shouldn't we use an insert_default_function here too ? === modified file 'sql/sql_parse.cc' --- sql/sql_parse.cc 2012-10-06 07:30:52 +0000 +++ sql/sql_parse.cc 2012-10-18 12:57:12 +0000 @@ -269,12 +269,14 @@ void init_update_queries(void) CF_CAN_GENERATE_ROW_EVENTS; sql_command_flags[SQLCOM_CREATE_INDEX]= CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS; sql_command_flags[SQLCOM_ALTER_TABLE]= CF_CHANGES_DATA | CF_WRITE_LOGS_COMMAND | - CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS ; + CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS | + CF_INSERTS_DATA; Should we really have CF_INSERTS_DATA here? We should not update any timestamps for ALTER TABLE? === modified file 'sql/table.cc' @@ -2472,7 +2471,7 @@ int open_table_from_share(THD *thd, TABL } /* - Process virtual columns, if any. + Process virtual and default columns, if any. */ if (!share->vfields) outparam->vfield= NULL; @@ -2484,10 +2483,26 @@ int open_table_from_share(THD *thd, TABL goto err; outparam->vfield= vfield_ptr; + } + + if (!share->default_fields) + outparam->default_field= NULL; You don't need to set default_fields to NULL as outparam is zeroed in beginning of the function. (Same is true for outparam->vfield) <cut> Regards, Monty

1 0

[Maria-developers] EXISTS-to-IN can work anywhere?
by Sergei Petrunia 26 Nov '12

26 Nov '12

Hello, Currently, EXISTS-to-IN rewrite only works on WHERE's top level, or under NOT, where the rewrite doesn't change semantics wrt NULL values. It seems, it is possible to make it work in other contexts, too. We need to convert it into a "NULL-ignoring IN", a special kind of IN predicate. For normal IN: NULL IN (select that returns something) --> NULL NULL IN (select that returns nothing) --> FALSE $value IN (select that doesn't return $value) --> FALSE $value IN (select that doesn't return $value but returns NULL) --> NULL null-ignoring-IN should return FALSE in all of the above cases. In fact, the optimizer is perfectly capable of doing this. What we need is just to disable special handling for NULLs. Let's discuss this tomorrow (Tuesday). BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MariaDB 5.1.66 release prep
by Sergei Golubchik 26 Nov '12

26 Nov '12

Preliminary release notes and changelog pages have been created for 5.1.66, 5.2.13, 5.3.11. https://kb.askmonty.org/en/mariadb-5166-release-notes/ https://kb.askmonty.org/en/mariadb-5166-changelog/ https://kb.askmonty.org/en/mariadb-5213-release-notes/ https://kb.askmonty.org/en/mariadb-5213-changelog/ https://kb.askmonty.org/en/mariadb-5311-release-notes/ https://kb.askmonty.org/en/mariadb-5311-changelog/ Please let me know if there is anything in particular which should be mentioned in the release notes. Thanks, Sergei

1 0

[Maria-developers] EXISTS-to-IN is ok to push
by Sergei Petrunia 23 Nov '12

23 Nov '12

Hi Sanja, I've made some fixes to comments in lp:~maria-captains/maria/10.0-exists2in. With that, it is ok to push EXISTS-to-IN into 10.0. Sorry for the delay. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] crash in TC_LOG_MMAP::log_one_transaction in maria-10.0.0
by Rich Prohaska 19 Nov '12

19 Nov '12

Hello, I am having problems with the shinny new mariadb-10.0.0. I have a simple test case that replaces a row in innodb and tokudb and then commits. Eventually, mysqld hits this assert: mysqld: /home/prohaska/maria10-build/mariadb-10.0.0/sql/log.cc:7547: int TC_LOG_MMAP::log_one_transaction(my_xid): Assertion `p->ptr < p->end' failed. The test works great when the binlog is enabled. The test crashes when the binlog is OFF. So, maybe the problem is isolated to the TC_LOG_MMAP logic. I noticed that the TC_LOG_MMAP::unlog function is quite different than the mariadb-5.5 version. What is the best way to get this fixed? Would it help if i made the test case available? Thanks Rich Prohaska Here is the stack trace: Program received signal SIGABRT, Aborted. 0x0000003006a32885 in raise () from /lib64/libc.so.6 (gdb) bt #0 0x0000003006a32885 in raise () from /lib64/libc.so.6 #1 0x0000003006a34065 in abort () from /lib64/libc.so.6 #2 0x0000003006a2b9fe in __assert_fail_base () from /lib64/libc.so.6 #3 0x0000003006a2bac0 in __assert_fail () from /lib64/libc.so.6 #4 0x00000000008862bf in TC_LOG_MMAP::log_one_transaction (this=0x1541f00 <tc_log_mmap>, xid=142018) at /home/prohaska/maria10-build/mariadb-10.0.0/sql/log.cc:7547 #5 0x0000000000885442 in TC_LOG_MMAP::log_and_order (this=0x1541f00 <tc_log_mmap>, thd=0x27f0000, xid=142018, all=true, need_prepare_ordered=false, need_commit_ordered=true) at /home/prohaska/maria10-\ build/mariadb-10.0.0/sql/log.cc:7180 #6 0x00000000007b819f in ha_commit_trans (thd=0x27f0000, all=true) at /home/prohaska/maria10-build/mariadb-10.0.0/sql/handler.cc:1329 #7 0x0000000000710307 in trans_commit (thd=0x27f0000) at /home/prohaska/maria10-build/mariadb-10.0.0/sql/transaction.cc:213 #8 0x000000000060a2df in mysql_execute_command (thd=0x27f0000) at /home/prohaska/maria10-build/mariadb-10.0.0/sql/sql_parse.cc:4084 #9 0x000000000060f1a7 in mysql_parse (thd=0x27f0000, rawbuf=0x7fff6c0050b8 "commit", length=6, parser_state=0x7fffe4112660) at /home/prohaska/maria10-build/mariadb-10.0.0/sql/sql_parse.cc:6056 #10 0x00000000006029a3 in dispatch_command (command=COM_QUERY, thd=0x27f0000, packet=0x27fa341 "commit", packet_length=6) at /home/prohaska/maria10-build/mariadb-10.0.0/sql/sql_parse.cc:1216 #11 0x0000000000601bdb in do_command (thd=0x27f0000) at /home/prohaska/maria10-build/mariadb-10.0.0/sql/sql_parse.cc:945 #12 0x00000000006ff6d1 in do_handle_one_connection (thd_arg=0x27f0000) at /home/prohaska/maria10-build/mariadb-10.0.0/sql/sql_connect.cc:1254 #13 0x00000000006ff179 in handle_one_connection (arg=0x27f0000) at /home/prohaska/maria10-build/mariadb-10.0.0/sql/sql_connect.cc:1168 #14 0x0000003006e077f1 in start_thread () from /lib64/libpthread.so.0 #15 0x0000003006ae592d in clone () from /lib64/libc.so.6

3 9

Re: [Maria-developers] Fwd: [Commits] Rev 3479: MDEV-3858 Change JOIN_TAB::records_read from ha_rows to double in file:///home/tsk/mprog/src/10.0-md3858/
by Sergei Petrunia 15 Nov '12

15 Nov '12

Hi Timour, Ok to push. On Thu, Nov 15, 2012 at 12:56:59PM +0200, Timour Katchaounov wrote: > Sergey, > > Could you please review this trivial task, shouldn't take more than 10 min. > > Timour > > ------------------------------------------------------------ > revno: 3479 > revision-id: timour(a)askmonty.org-20121115105450-zkiva73utg8pg49j > parent: wlad(a)montyprogram.com-20121111095852-1jsqvi3zcdml80lt > fixes bug: https://mariadb.atlassian.net/browse/MDEV-3858 > committer: timour(a)askmonty.org > branch nick: 10.0-md3858 > timestamp: Thu 2012-11-15 12:54:50 +0200 > message: > MDEV-3858 Change JOIN_TAB::records_read from ha_rows to double > > Currently JOIN_TAB::records_read is of type ha_rows. > This is an integer type, which prevents proper selectivity and rows estimates. > > > === modified file 'mysql-test/r/subselect_sj_mat.result' > --- a/mysql-test/r/subselect_sj_mat.result 2012-07-25 16:53:49 +0000 > +++ b/mysql-test/r/subselect_sj_mat.result 2012-11-15 10:54:50 +0000 > @@ -94,7 +94,7 @@ a1 a2 > explain extended > select * from t1i where a1 in (select b1 from t2i where b1 > '0'); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 40.00 Using where; Using index; LooseScan > +1 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 50.00 Using where; Using index; LooseScan > 1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 > Warnings: > Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) > @@ -117,7 +117,7 @@ a1 a2 > explain extended > select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 40.00 Using where; Using index; LooseScan > +1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 50.00 Using where; Using index; LooseScan > 1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 > Warnings: > Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) > @@ -319,7 +319,7 @@ where (a1, a2) in (select b1, b2 from t2 > (a1, a2) in (select c1, c2 from t3i > where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); > id select_type table type possible_keys key key_len ref rows filtered Extra > -1 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 40.00 # > +1 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # > 1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # > 1 PRIMARY t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # > 1 PRIMARY t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # > @@ -407,7 +407,7 @@ id select_type table type possible_keys > 2 MATERIALIZED t2 ALL NULL # # # 5 100.00 # > 4 MATERIALIZED t3 ALL NULL # # # 4 100.00 # > 3 MATERIALIZED t3 ALL NULL # # # 4 100.00 # > -7 UNION t2i index it2i1,it2i2,it2i3 # # # 5 40.00 # > +7 UNION t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # > 7 UNION t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # > 7 UNION t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # > 7 UNION t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # > > === modified file 'sql/item_subselect.cc' > --- a/sql/item_subselect.cc 2012-11-03 11:28:51 +0000 > +++ b/sql/item_subselect.cc 2012-11-15 10:54:50 +0000 > @@ -4553,7 +4553,7 @@ double get_fanout_with_deps(JOIN *join, > !tab->emb_sj_nest && > tab->records_read != 0) > { > - fanout *= rows2double(tab->records_read); > + fanout *= tab->records_read; > } > } > return fanout; > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2012-11-04 15:32:32 +0000 > +++ b/sql/sql_select.cc 2012-11-15 10:54:50 +0000 > @@ -6789,7 +6789,7 @@ void JOIN::get_prefix_cost_and_fanout(ui > > double JOIN::get_examined_rows() > { > - ha_rows examined_rows; > + double examined_rows; > double prev_fanout= 1; > JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS); > JOIN_TAB *prev_tab= tab; > @@ -6799,7 +6799,7 @@ double JOIN::get_examined_rows() > while ((tab= next_breadth_first_tab(this, WALK_OPTIMIZATION_TABS, tab))) > { > prev_fanout *= prev_tab->records_read; > - examined_rows+= (ha_rows) (tab->get_examined_rows() * prev_fanout); > + examined_rows+= tab->get_examined_rows() * prev_fanout; > prev_tab= tab; > } > return examined_rows; > @@ -7704,7 +7704,8 @@ get_best_combination(JOIN *join) > sub-order > */ > SJ_MATERIALIZATION_INFO *sjm= cur_pos->table->emb_sj_nest->sj_mat_info; > - j->records= j->records_read= (ha_rows)(sjm->is_sj_scan? sjm->rows : 1); > + j->records_read= sjm->is_sj_scan? sjm->rows : 1; > + j->records= (ha_rows) j->records_read; > JOIN_TAB *jt; > JOIN_TAB_RANGE *jt_range; > if (!(jt= (JOIN_TAB*)join->thd->alloc(sizeof(JOIN_TAB)*sjm->tables)) || > @@ -7766,7 +7767,7 @@ get_best_combination(JOIN *join) > Save records_read in JOIN_TAB so that select_describe()/etc don't have > to access join->best_positions[]. > */ > - j->records_read= (ha_rows)join->best_positions[tablenr].records_read; > + j->records_read= join->best_positions[tablenr].records_read; > join->map2table[j->table->tablenr]= j; > > /* If we've reached the end of sjm nest, switch back to main sequence */ > @@ -10460,7 +10461,7 @@ double JOIN_TAB::scan_time() > > ha_rows JOIN_TAB::get_examined_rows() > { > - ha_rows examined_rows; > + double examined_rows; > > if (select && select->quick && use_quick != 2) > examined_rows= select->quick->records; > @@ -10490,7 +10491,7 @@ ha_rows JOIN_TAB::get_examined_rows() > } > } > else > - examined_rows= (ha_rows) records_read; > + examined_rows= records_read; > > return examined_rows; > } > @@ -21972,7 +21973,7 @@ int JOIN::print_explain(select_result_si > } > else > { > - ha_rows examined_rows= tab->get_examined_rows(); > + double examined_rows= tab->get_examined_rows(); > > item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows, > MY_INT64_NUM_DECIMAL_DIGITS)); > @@ -21982,7 +21983,7 @@ int JOIN::print_explain(select_result_si > { > float f= 0.0; > if (examined_rows) > - f= (float) (100.0 * tab->records_read / examined_rows); > + f= (100.0 * (float)tab->records_read) / examined_rows; > set_if_smaller(f, 100.0); > item_list.push_back(new Item_float(f, 2)); > } > > === modified file 'sql/sql_select.h' > --- a/sql/sql_select.h 2012-11-04 15:32:32 +0000 > +++ b/sql/sql_select.h 2012-11-15 10:54:50 +0000 > @@ -280,8 +280,8 @@ typedef struct st_join_table { > */ > double read_time; > > - /* psergey-todo: make the below have type double, like POSITION::records_read? */ > - ha_rows records_read; > + /* Copy of POSITION::records_read, set by get_best_combination() */ > + double records_read; > > /* Startup cost for execution */ > double startup_cost; > > _______________________________________________ > 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

1 0

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