August 2012 - developers - lists.mariadb.org

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

[Maria-developers] New to MariaDB
by Giacomo Picchiarelli 13 Sep '12

13 Sep '12

Hi, I'm new to MariaDB and I'm reading the knowledge base. I'm interested in studying Aria Engine (and contribute, if I can :) ), are there tasks for newbie developers? Thank you. Regards. -- Giacomo Picchiarelli <gpicchiarelli(a)gmail.com> Skype: g.picchiarelli

2 1

Re: [Maria-developers] [knielsen@knielsen-hq.org] [Commits] Rev 3459: MDEV-232: Remove one fsync() from commit phase. in http://bazaar.launchpad.net/~maria-captains/maria/5.5
by Kristian Nielsen 09 Sep '12

09 Sep '12

Sergei Golubchik <serg(a)askmonty.org> writes: > Hi, Kristian! > > On Jun 22, Kristian Nielsen wrote: >> Can you review below patch for mdev-232 (At your convenience, not urgent)? > > Here's the review. Mostly questions, I have to confess :) Thanks a lot for your review! I've tried to answer all your questions below, and for many I have tried to clarify the patch, as noted. Do you want to see the changes for when I implement async commit_checkpoint_request() in InnoDB/XtraDB, before I push it to 10.0? >> === modified file 'sql/slave.cc' >> --- a/sql/slave.cc 2012-06-22 09:40:40 +0000 >> +++ b/sql/slave.cc 2012-06-22 10:20:29 +0000 >> @@ -1751,13 +1751,12 @@ when it try to get the value of TIME_ZON >> /* Announce MariaDB slave capabilities. */ >> DBUG_EXECUTE_IF("simulate_slave_capability_none", goto after_set_capability;); >> { >> - const char *q= >> - DBUG_EVALUATE_IF("simulate_slave_capability_old_53", >> - "SET @mariadb_slave_capability=" >> - STRINGIFY_ARG(MARIA_SLAVE_CAPABILITY_ANNOTATE), >> - "SET @mariadb_slave_capability=" >> - STRINGIFY_ARG(MARIA_SLAVE_CAPABILITY_MINE)); >> - if (mysql_real_query(mysql, q, strlen(q))) >> + int rc= DBUG_EVALUATE_IF("simulate_slave_capability_old_53", >> + mysql_real_query(mysql, STRING_WITH_LEN("SET @mariadb_slave_capability=" >> + STRINGIFY_ARG(MARIA_SLAVE_CAPABILITY_ANNOTATE))), >> + mysql_real_query(mysql, STRING_WITH_LEN("SET @mariadb_slave_capability=" >> + STRINGIFY_ARG(MARIA_SLAVE_CAPABILITY_MINE)))); >> + if (rc) > > Why? Monty asked me to not use strlen() which I had introduced due to a request from you in review of an earlier worklog. And you wrote that you did not care much one way or the other (neither do I), so I reverted the change back to the original without strlen(). (Maybe I should have done it as a separate changeset, but I took this short cut since MDEV 225/181/232 are kind of tied together and will likely be pushed together.) >> === modified file 'storage/xtradb/handler/ha_innodb.cc' >> --- a/storage/xtradb/handler/ha_innodb.cc 2012-06-15 12:54:23 +0000 >> +++ b/storage/xtradb/handler/ha_innodb.cc 2012-06-22 10:20:29 +0000 >> @@ -485,8 +486,11 @@ static MYSQL_THDVAR_ULONG(lock_wait_time >> static MYSQL_THDVAR_ULONG(flush_log_at_trx_commit, PLUGIN_VAR_OPCMDARG, >> "Set to 0 (write and flush once per second)," >> " 1 (write and flush at each commit)" >> - " or 2 (write at commit, flush once per second).", >> - NULL, NULL, 1, 0, 2, 0); >> + " or 2 (write at commit, flush once per second)." >> + " Can also be set to 3 to write and flush at each commit, even when this is" >> + " unnecessary because of binlog crash recovery; should not normally be used" >> + " due to performance degration, but provided for completeness.", > > would you like to improve the description on =1 value? For example, > 1 (write and flush on prepare or on commit) > 3 (write and flush both on prepare and on commit, usually it is redundant) Yes, good idea. Here is my suggestion: "Controls the durability/speed trade-off for commits." " Set to 0 (write and flush redo log to disk only once per second)," " 1 (flush to disk at each commit)," " 2 (write to kernel at commit but flush to disk only once per second)" " or 3 (flush to disk twice per commit, usually redundant)." " 1 and 3 guarantees that after a crash, committed transactions will" " not be lost and will be consistent with the binlog and other transactional" " engines. 2 can get inconsistent and lose transactions if there is a" " power failure or kernel crash but not if mysqld crashes. 0 has no" " guarantees in case of crash. 0 and 2 can be faster than 1 or 3.", >> /*****************************************************************//** >> +Handle a commit checkpoint request from server layer. >> +We simply flush the redo log immediately and do the notify call.*/ >> +static >> +void >> +innobase_checkpoint_request( >> + handlerton *hton, >> + void *cookie) >> +{ >> + log_buffer_flush_to_disk(); >> + ha_commit_checkpoint_notify(hton, cookie); >> +} >> + >> +/*****************************************************************//** > > What about making innobase_checkpoint_request() implementation that flushed > in the background? At least to serve as an example of the correct usage of your > API, if not for performance. And to test your code for cases when the notification > isn't coming directly from the checkpoint_request method. > > it looks like it's very easy to make innodb to invoke ha_commit_checkpoint_notify > from the background flusher thread (the one that syncs once a second). > you only need to store the cookie in the log_sys, and invoke > ha_commit_checkpoint_notify after the flush when this stored cookie isn't NULL. Yes, I would like to do this, I will try. It might even help performance, I have seen several blog posts mentioning problems with stalls at binlog rotation, and this might help. As you say, this should be easy. I need to store a list of cookies and their associated LSN (not just a single cookie), but this is infrequently executed so just a simple linked list with malloc/free of elements will work fine. >> +void >> +ha_commit_checkpoint_notify(handlerton *hton, void *cookie) > > are there other ha_xxx methods that a storage engine calls? > > typically server calls methods, like, ha_commit_trans, and they invoke storage > engine methods. Not the other way around. It is exactly for this reason I used names > like "trans_register_ha" - inverse of ha_register_trans - to emphasize that this > method is invoked by the engine to call into the server, not by the server to > call into the engine. Ah, thanks, good point. I suggest "commit_checkpoint_notify_ha()", what do you think? >> === modified file 'sql/log.h' >> --- a/sql/log.h 2012-06-22 09:46:28 +0000 >> +++ b/sql/log.h 2012-06-22 10:20:29 +0000 >> @@ -359,9 +373,14 @@ class MYSQL_QUERY_LOG: public MYSQL_LOG >> Ids start from BINLOG_COOKIE_START; the value BINLOG_COOKIE_DUMMY is special >> meaning "no binlog" (we cannot use zero as that is reserved for error return >> from log_and_order). >> + The low-order bit is reserved for an error flag. >> */ >> -#define BINLOG_COOKIE_DUMMY 1 >> -#define BINLOG_COOKIE_START 2 >> +#define BINLOG_COOKIE_ERROR_FLAG 1 >> +#define BINLOG_COOKIE_ID_INCREMENT (BINLOG_COOKIE_ERROR_FLAG*2) >> +#define BINLOG_COOKIE_DUMMY (BINLOG_COOKIE_ID_INCREMENT*1) > > Is that right? BINLOG_COOKIE_DUMMY == BINLOG_COOKIE_ID_INCREMENT ? It is "right", but I agree it is incomprehensible. I will improve it. The thing is, I need to store a number of different values in the "cookie" used with log_and_order() and unlog(): - 0, which is the error return value from log_and_order() - A pair (binlog_id, error_flag) - A special dummy binlog_id meaning that nothing was logged to the binlog. I will change it to this, what do you think? : /* We assign each binlog file an internal ID, used to identify them for unlog(). The IDs start from 0 and increment for each new binlog created. In unlog() we need to know the ID of the binlog file that the corresponding transaction was written into. We also need a special value for a corner case where there is no corresponding binlog id (since nothing was logged), as well as an error flag to mark that unlog() must return failure. We use the following macros to pack all of this information into the single ulong available with log_and_order() / unlog(). Note that we cannot use the value 0, as that is reserved as error return value from log_and_order(). */ #define BINLOG_COOKIE_ERROR_RETURN 0 #define BINLOG_COOKIE_DUMMY_ID 1 #define BINLOG_COOKIE_BASE 2 #define BINLOG_COOKIE_DUMMY(error_flag) \ ( (BINLOG_COOKIE_DUMMY_ID<<1) | ((error_flag)&1) ) #define BINLOG_COOKIE_MAKE(id, error_flag) \ ( (((id)+BINLOG_COOKIE_BASE)<<1) | ((error_flag)&1) ) #define BINLOG_COOKIE_GET_ERROR_FLAG(c) ((c) & 1) #define BINLOG_COOKIE_GET_ID(c) ( ((ulong)(c)>>1) - BINLOG_COOKIE_BASE ) #define BINLOG_COOKIE_IS_DUMMY(c) \ ( ((ulong)(c)>>1) == BINLOG_COOKIE_DUMMY_ID ) Then the code in log.cc can use these macros, and not have to depend on strange details on how to pack different values into a single ulong. I think the resulting code is much clearer, now BINLOG_COOKIE_* is only used in log_and_order() and unlog() functions, thanks! >> class MYSQL_BIN_LOG: public TC_LOG, private MYSQL_LOG >> @@ -464,15 +492,6 @@ class MYSQL_BIN_LOG: public TC_LOG, priv >> uint file_id; >> uint open_count; // For replication >> int readers_count; >> - bool need_start_event; >> - /* >> - no_auto_events means we don't want any of these automatic events : >> - Start/Rotate/Stop. That is, in 4.x when we rotate a relay log, we don't >> - want a Rotate_log event to be written to the relay log. When we start a >> - relay log etc. So in 4.x this is 1 for relay logs, 0 for binlogs. >> - In 5.0 it's 0 for relay logs too! >> - */ >> - bool no_auto_events; > > Why? Because it's some old code which isn't used nowadays? Yes. This was dead code. >> === modified file 'sql/log.cc' >> --- a/sql/log.cc 2012-06-22 09:46:28 +0000 >> +++ b/sql/log.cc 2012-06-22 10:20:29 +0000 >> @@ -3597,12 +3623,40 @@ bool MYSQL_BIN_LOG::reset_logs(THD* thd) >> + /* >> We are going to nuke all binary log files. >> - So first wait until all pending binlog checkpoints have completed. >> + Without binlog, we cannot XA recover prepared-but-not-committed >> + transactions in engines. So force a commit checkpoint first. >> + >> + Note that we take and immediately release LOCK_commit_ordered. This has >> + the effect to ensure that any on-going group commit (in >> + trx_group_commit_leader()) has completed before we request the checkpoint, >> + due to the chaining of LOCK_log and LOCK_commit_ordered in that function. >> + (We are holding LOCK_log, so no new group commit can start). >> + >> + Without this, it is possible (though perhaps unlikely) that the RESET >> + MASTER could run in-between the write to the binlog and the >> + commit_ordered() in the engine of some transaction, and then a crash >> + later would leave such transaction not recoverable. >> */ >> + mysql_mutex_lock(&LOCK_commit_ordered); >> + mysql_mutex_unlock(&LOCK_commit_ordered); >> + >> + mark_xids_active(current_binlog_id, 1); >> + do_checkpoint_request(current_binlog_id); > > why do_checkpoint_request ? There may be transactions in the current binlog file that are not yet flushed to disk in the engine. If we proceed in RESET MASTER to delete the binlog file, and then crash, then we could lose such transactions. So we request a checkpoint for the current binlog file (we already did so for any previous binlog files). And then wait for this and any other pending checkpoint to complete. Only then can we safely delete binlog files. >> @@ -3731,6 +3780,31 @@ bool MYSQL_BIN_LOG::reset_logs(THD* thd) >> + /* >> + Remove all entries in the xid_count list except the last. >> + Normally we will just be deleting all the entries that we waited for to >> + drop to zero above. But if we fail during RESET MASTER for some reason >> + then we will not have created any new log file, and we may keep the last >> + of the old entries. > > I'm not sure I understand this comment. > What's the point of keeping the last of the old entries, if old binlog > files are deleted and binary logging is probably stopped (when RESET MASTER fails > it's kinda serious)? The code in sql/log.cc assumes that binlog_xid_count_list is never empty. So even in the error case we must be sure to leave at least one entry in there. So the comment is to explain why we do not simply delete everything in the list at the start, and then insert the new entry on top of that. If we get an error half-way, error handling is just easier by doing the deletion at the end, where we know what we can delete and what not. It is true that RESET MASTER failure is quite serious, and if we cannot open a new binlog after deleting the old ones we are indeed in trouble. And there are very likely legacy bugs lurking in this error case. But with this code, at least no new error handling issues are introduced, and the rest of the code is clean and can rely on having at least one entry in the list. > If some engine confirms that everything below a certain cookie of > a particular binlog_id is flushed. Does it automatically mean that > all previous binlog_id's are flushed too in this engine? Yes. This is the relevant comment in handler.h: The TC will periodically (eg. once per binlog rotation) call commit_checkpoint_request(). When this happens, the engine must arrange for all transaction that have completed commit_ordered() to be durably flushed to disk (this does not include transactions that might be in the middle of executing commit_ordered()). When such flush has completed, the engine must call commit_checkpoint_notify_ha(), passing back the opaque "cookie". commit_ordered() calls complete in the same order as transactions are written in the binlog. And we request a checkpoint for a binlog file only when all transactions in it have completed commit_ordered(). So when an engine has flushed all transactions that completed commit_ordered(), this includes everything in the given binlog, as well as anything in previous binlogs. >> @@ -5493,7 +5656,27 @@ int MYSQL_BIN_LOG::rotate(bool force_rot >> if (!write_incident_already_locked(current_thd)) >> flush_and_sync(0); >> >> - *check_purge= true; >> + /* >> + We failed to rotate - so we have to decrement the xid_count back that >> + we incremented before attempting the rotate. >> + */ > > why you don't use mark_xid_done() here? We cannot call mark_xid_done() here, as we are holding LOCK_log - and mark_xid_done() also takes (sometimes) LOCK_log. >> @@ -5920,22 +6115,29 @@ MYSQL_BIN_LOG::write_binlog_checkpoint_e >> */ >> if (!ev.write(&log_file) && !flush_and_sync(0)) >> { >> - bool check_purge= false; >> signal_update(); >> - rotate(false, &check_purge); >> - if (check_purge) >> - purge(); > > Why did you remove rotate/purge from here? Binlog rotation triggers the writing of checkpoint event in the first place. So it does not seem right that writing checkpoint event can trigger binlog rotation. This could in principle lead to infinite rotation of new binlogs, though one would need a _very_ small max binlog size to trigger that. >> +int TC_LOG_MMAP::unlog(ulong cookie, my_xid xid) >> +{ >> + pending_cookies *full_buffer= NULL; >> + DBUG_ASSERT(*(my_xid *)(data+cookie) == xid); >> + >> + /* >> + Do not delete the entry immediately, as there may be participating storage >> + engines which implement commit_checkpoint_request(), and thus have not yet >> + flushed the commit durably to disk. >> + >> + Instead put it in a queue - and periodically, we will request a checkpoint >> + from all engines and delete a whole batch at once. >> + */ >> + mysql_mutex_lock(&LOCK_pending_checkpoint); >> + if (pending_checkpoint == NULL) >> + { >> + uint32 size= sizeof(*pending_checkpoint); >> + if (!(pending_checkpoint= >> + (pending_cookies *)my_malloc(size, MYF(MY_ZEROFILL)))) >> + { >> + my_error(ER_OUTOFMEMORY, MYF(0), size); >> + mysql_mutex_unlock(&LOCK_pending_checkpoint); >> + return 1; >> + } >> + pending_checkpoint->cookies[pending_checkpoint->count++]= cookie; > > Hm, should this be under if (pending_checkpoint == NULL) ? No :-( That seems completely, utterly broken :-( So this means that I must have not tested this code at all. Which makes me sad. Of course, with PBXT dead, I am not sure how it is even possible to test it... ? Thanks for spotting. >> + /* >> + We do an extra increment and notify here - this ensures that >> + things work also if there are no engines at all that support >> + commit_checkpoint_request. >> + */ >> + ++full_buffer->pending_count; >> + ha_commit_checkpoint_request(full_buffer, mmap_do_checkpoint_callback); >> + commit_checkpoint_notify(full_buffer); > > I'm sorry, I don't understand how you handle the case when some engines don't > support commit_checkpoint_request If an engine supports commit_checkpoint_request, then ha_commit_checkpoint_request() will ask the engine to do a checkpoint; and pending_count will be incremented for each such engine to ensure that we wait for each engine to finish the checkpoint before we delete any entries from the on-disk log. If an engine does not support commit_checkpoint_request, then we will not do anything with that engine before deleting entries. But such engines are required to make their transactions durable already in their commit() method, so deletion is always safe. If _no_ engines support ha_commit_checkpoint_request then we would not get any callback to commit_checkpoint_notify() - so we add one extra such a call ourselves, at the end. Also, the increment of pending_count and decrement in the extra commit_checkpoint_notify() call protects against this race: - We call engine A's commit_checkpoint_request() - Before we have time to call engine B's commit_checkpoint_request(), engine A does commit_checkpoint_notify(), so pending_count drops to zero and we delete entries even though B has not had a chance to do a checkpoint yet - We call engine B's commit_checkpoint_request with a void *cookie pointing to freed memory. Hope this clarifies things. >> @@ -7869,6 +8196,11 @@ int TC_LOG_BINLOG::recover(LOG_INFO *lin >> if (!binlog_checkpoint_found) >> break; >> first_round= false; >> + DBUG_EXECUTE_IF("xa_recover_expect_master_bin_000004", >> + if (0 != strcmp("./master-bin.000004", binlog_checkpoint_name) && >> + 0 != strcmp(".\\master-bin.000004", binlog_checkpoint_name)) > > Are you sure the name is always lowercase even on case-insensitive > filesystems? I'd use strcasecmp to avoid the need to think about it :) > Something like > > if (0 != strcasecmp("master-bin.000004", binlog_checkpoint_name+2)) > > (right, unsafe if strlen(binlog_checkpoint_name) < 2, but it's just a test case) I don't understand - this has nothing to do with file system case sensitivity. The string is generated inside log.cc based on what mysql-test-run.pl passes to mysqld for the --log-bin option, I don't see how this could change case. And I thought it was clearer to explicitly show the two possible cases for the full file name (otherwise the offset 2 in the comparison is completely magic). But as you said, this is just a test case, I'm happy to change it if you insists (I just did not understand the reason to change it). BTW, as I remember this was for something that was quite hard to test in mysql-test-run. Since recovery happens early in the server startup, before anything mysqltest can do. So I could not think of anything better than to inject an error insert with a configuration option. - Kristian.

4 9

[Maria-developers] Please test: Cassandra batched-inserts, Batched Key Access
by Sergei Petrunia 31 Aug '12

31 Aug '12

Hi Elena, I've pushed "initial" support for batched INSERT and BKA into 5.5-cassandra, and updated http://kb.askmonty.org/en/cassandra-storage-engine to cover batching. Could you please test it? Cassandra uses "hashed" mode with BKA (see docs), which haven't been used for a while (previous user was Falcon storage engine), so it is possible that something malfunctions in BKA. Names/meaning of control variables and status counter names are preliminary and may change in the future. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

Re: [Maria-developers] [Commits] Rev 3565: MDEV-492: fixed incorrect error check. in file:///home/bell/maria/bzr/work-maria-5.3-MDEV-492/
by Sergei Golubchik 29 Aug '12

29 Aug '12

Hi, Oleksandr! On Aug 29, Oleksandr Byelkin wrote: > >> === modified file 'sql/sql_update.cc' > >> --- a/sql/sql_update.cc 2012-04-26 17:21:37 +0000 > >> +++ b/sql/sql_update.cc 2012-08-27 14:29:26 +0000 > >> @@ -872,7 +872,7 @@ int mysql_update(THD *thd, > >> id= thd->arg_of_last_insert_id_function ? > >> thd->first_successful_insert_id_in_prev_stmt : 0; > >> > >> - if (error < 0) > >> + if (error < 0 && (!thd->is_error())) > >> { > >> char buff[MYSQL_ERRMSG_SIZE]; > >> my_snprintf(buff, sizeof(buff), ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated, > > Nope, this is not good. > > The bug happens because the error from remove_eq_conds() is ignored. > > You need to catch and process this error as soon as possible. > > While in your patch you let the update work all the way till the end. > > > When "update works all the way" it catched inside update loops, but was > that if update never make any update it ignores errors. > > So actually it should be my fix or both fixes, because errors > (especially such unexpected as "out of resources" could happened almost > anywhere, so issuing OK to the client without checking error state is a > mistake. I know. Before sending a review I actually tried to get a crash by generating an error in different places of the UPDATE. E.g. in the loop: INSERT t1 VALUES (1,2); UPDATE .... or in fill_record(): UPDATE t1 SET a=COLUMN_CREATE(...); or with double my_error(): ... WHERE COLUMN_CREATE(...) OR COLUMN_CREATE(...); all these cases were correctly taken care of, no crash, no ok packet. So, I think, it's enough to fix only that early failure that you have found. Regards, Sergei

1 0

Re: [Maria-developers] [Commits] Rev 3565: MDEV-492: fixed incorrect error check. in file:///home/bell/maria/bzr/work-maria-5.3-MDEV-492/
by Sergei Golubchik 28 Aug '12

28 Aug '12

Hi, Sanja! On Aug 27, sanja(a)askmonty.org wrote: > message: > MDEV-492: fixed incorrect error check. > === modified file 'mysql-test/t/errors.test' > --- a/mysql-test/t/errors.test 2012-03-12 07:56:56 +0000 > +++ b/mysql-test/t/errors.test 2012-08-27 14:29:26 +0000 > @@ -85,3 +85,12 @@ INSERT INTO t2 VALUES (1,0) ON DUPLICATE > INSERT INTO t2(a,b) VALUES (1,0) ON DUPLICATE KEY UPDATE > b=(SELECT VALUES(a)+2 FROM t1); > DROP TABLE t1, t2; > + > +--echo # > +--echo # MDEV-492: incorrect error check before sending OK in mysql_update > +--echo # > +CREATE TABLE t1 (a CHAR(3), b BLOB); > +--error ER_DYN_COL_DATA > +UPDATE t1 SET a = 'new' > +WHERE COLUMN_CREATE( 1, 'v', 1, 'w' ) IS NULL; > +drop table t1; > > === modified file 'sql/sql_update.cc' > --- a/sql/sql_update.cc 2012-04-26 17:21:37 +0000 > +++ b/sql/sql_update.cc 2012-08-27 14:29:26 +0000 > @@ -872,7 +872,7 @@ int mysql_update(THD *thd, > id= thd->arg_of_last_insert_id_function ? > thd->first_successful_insert_id_in_prev_stmt : 0; > > - if (error < 0) > + if (error < 0 && (!thd->is_error())) > { > char buff[MYSQL_ERRMSG_SIZE]; > my_snprintf(buff, sizeof(buff), ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated, Nope, this is not good. The bug happens because the error from remove_eq_conds() is ignored. You need to catch and process this error as soon as possible. While in your patch you let the update work all the way till the end. This patch is better, it aborts much earlier: === modified file 'sql/sql_update.cc' --- sql/sql_update.cc 2012-08-09 15:22:00 +0000 +++ sql/sql_update.cc 2012-08-28 19:05:33 +0000 @@ -406,7 +406,7 @@ int mysql_update(THD *thd, table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); select= make_select(table, 0, 0, conds, 0, &error); - if (error || !limit || + if (error || !limit || thd->is_error() || (select && select->check_quick(thd, safe_update, limit))) { delete select; Regards, Sergei

1 0

Re: [Maria-developers] [Commits] Rev 3353: Performed re-factoring and re-structuring of the code for mwl#248: in file:///home/igor/maria/maria-5.5-mwl248-changes/
by Sergei Golubchik 28 Aug '12

28 Aug '12

Hi, Davi! On Jul 31, Davi Arnaut wrote: > Also, I would like to point out that my main argument a few e-mails > ago is that you cannot memset/bzero a non-POD type in C++ without > occurring in undefined/implementation-defined behavior, which was > ignored. This significantly lowers my confidence in MariaDB being a > high quality code base (save for some developers who tend to get it). :) After your first email I've checked that code. Perhaps I've missed something, but it did look like all bzero-ed structures in the igor's patch were of POD types. Regards, Sergei

2 3

[Maria-developers] MariaDB 5.3.8 Release Prep
by Daniel Bartholomew 28 Aug '12

28 Aug '12

All, The MariaDB 5.3.8 release is almost ready. The files have been uploaded and should be mirrored soon. I expect to send out the release announcement at around 10am U.S. Eastern time tomorrow (28 Aug 2012). As usual, I've created preliminary Release Notes and Changelog pages: http://kb.askmonty.org/en/mariadb-538-release-notes/ http://kb.askmonty.org/en/mariadb-538-changelog/ Please let me know ASAP if there are any particularly noteworthy items in this release which I should mention 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] table_share->field vs table->field
by Markus Pilman 22 Aug '12

22 Aug '12

Hi all, I ran into a strange problem: I am working on a storage engine for MariaDB. To access field information I usually do as written in the documentation: for (Field** field = table->field; *field; ++field) { /* some code here */ } This works fine in most cases (for writing - read handler::write_row - it seems to work always) but sometimes (I think always in the index_next method) accessing **(table->field) results in a segmentation fault (but *(table->field) is not null, but something like 0x5e6f4c6d4578244d). Strange enough, this error does not happen always. In the debugger I saw, that table_share->field points to the correct location there so I replaces table->field in the above loop with table_share->field. But this also seems to not work - it now crashes on other queries (also on updates, but after restarting MariaDB it also crashes on a full table scan). So my question: how can I access the fields in a reliable manner? I need to do that to set/get the BLOB/TEXT fields. We have a method in our handler called set_blobs that resets the pointer in the record to the blobs, which looks like the following: void ha_kvstore::set_blobs(uchar *buf, kvid key, btree::buffer &raw_data) { auto current_read_pos = raw_data.const_ptr() + table_share->reclength; for (Field** field = table->field; *field; ++field) { if (!((*field)->flags & BLOB_FLAG)) continue; (*field)->table = table; Field_blob* blob = *((Field_blob**)field); uint32 packlength = blob->pack_length_no_ptr(); bmove((*field)->ptr + packlength, &current_read_pos, sizeof(char*)); current_read_pos += blob->get_length(); } } The storage engine always crashes in this code. What are we doing wrong? Any help is appreciated! Best Markus

2 2

[Maria-developers] Introduction / Os X Build Host
by Pablito 21 Aug '12

21 Aug '12

Hello I'm Pablito Vicente, I'm from Argentina, have been using Mysql for 12 years now as a web developer ( PHP mostly ). During the weekend I was trying to download a package for Mac and I notice brew was the only choice, that I used and worked but I would like to know if I can help generating builds for the latest versions and I will try to also investigate how to create packages so people can download a DMG image for easier installation. I will follow the instructions on: https://kb.askmonty.org/en/buildbot-setup-buildbot-setup-for-macosx/ to set it up but I was wondering if this will work when the computers are turned on only for this task. I don't usually leave computers on unless that's required. I could offer builds for Mountain Lion and Snow Leopard, though I think they're probably the same. well let me know if this can be of any help. I might send some emails for some help as I've been away from sys admin tasks for years. warm regards, Pablito

1 0

Re: [Maria-developers] Fwd: [Commits] Rev 3498: Fix bug mdev-447: Wrong output from the EXPLAIN command of the test case for lp bug #714999 in file:///home/tsk/mprog/src/5.5-md447/
by Sergei Petrunia 21 Aug '12

21 Aug '12

Hi Timour Ok to push. On Mon, Aug 20, 2012 at 02:47:35PM +0300, Timour Katchaounov wrote: > Sergey, > > Could you please review this simple patch. > It is a backport of several lines from your code. > > Timour > > ------------------------------------------------------------ > revno: 3498 > revision-id: timour(a)askmonty.org-20120820114533-hznbt0z7ikdxoawq > parent: knielsen(a)knielsen-hq.org-20120817123528-eggusbbjukhxshvs > committer: timour(a)askmonty.org > branch nick: 5.5-md447 > timestamp: Mon 2012-08-20 14:45:33 +0300 > message: > Fix bug mdev-447: Wrong output from the EXPLAIN command of the test case for lp bug #714999 > > The fix backports from MWL#182: Explain running statements the logic that > saves the original JOIN_TAB array of a query plan after optimization. This > array is later used during EXPLAIN to iterate over the original JOIN plan > nodes in the cases when this plan could be changed by early subquery > execution during the optimization phase of the outer query. > > > === modified file 'mysql-test/r/subselect.result' > --- a/mysql-test/r/subselect.result 2012-07-25 16:41:48 +0000 > +++ b/mysql-test/r/subselect.result 2012-08-20 11:45:33 +0000 > @@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1); > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index > -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > DROP TABLE t1; > CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); > INSERT INTO t1 VALUES > > === modified file 'mysql-test/r/subselect_innodb.result' > --- a/mysql-test/r/subselect_innodb.result 2012-06-06 19:26:40 +0000 > +++ b/mysql-test/r/subselect_innodb.result 2012-08-20 11:45:33 +0000 > @@ -333,7 +333,7 @@ WHERE (SELECT DISTINCT b FROM t3) > 0); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using where; Using index > 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE > -3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 0 Using temporary > +3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary > SELECT * > FROM t1 > WHERE t1.a = ( > @@ -386,7 +386,7 @@ select 1 from t1 where 1 like (select 1 > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 > 2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 > -3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > +3 SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using temporary; Using filesort > select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1)); > 1 > 1 > > === modified file 'mysql-test/r/subselect_mat_cost_bugs.result' > --- a/mysql-test/r/subselect_mat_cost_bugs.result 2012-07-25 16:41:48 +0000 > +++ b/mysql-test/r/subselect_mat_cost_bugs.result 2012-08-20 11:45:33 +0000 > @@ -148,7 +148,7 @@ FROM t2 GROUP BY f1 > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables > 2 SUBQUERY t1 system NULL NULL NULL NULL 1 > -3 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > +3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > drop table t1, t2, t3; > # > # LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed > > === modified file 'mysql-test/r/subselect_no_mat.result' > --- a/mysql-test/r/subselect_no_mat.result 2012-07-25 16:41:48 +0000 > +++ b/mysql-test/r/subselect_no_mat.result 2012-08-20 11:45:33 +0000 > @@ -4200,7 +4200,7 @@ INSERT INTO t1 VALUES (1,1),(2,1); > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index > -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > DROP TABLE t1; > CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); > INSERT INTO t1 VALUES > > === modified file 'mysql-test/r/subselect_no_opts.result' > --- a/mysql-test/r/subselect_no_opts.result 2012-07-25 16:41:48 +0000 > +++ b/mysql-test/r/subselect_no_opts.result 2012-08-20 11:45:33 +0000 > @@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1); > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index > -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > DROP TABLE t1; > CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); > INSERT INTO t1 VALUES > > === modified file 'mysql-test/r/subselect_no_scache.result' > --- a/mysql-test/r/subselect_no_scache.result 2012-07-25 16:41:48 +0000 > +++ b/mysql-test/r/subselect_no_scache.result 2012-08-20 11:45:33 +0000 > @@ -4202,7 +4202,7 @@ INSERT INTO t1 VALUES (1,1),(2,1); > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index > -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > DROP TABLE t1; > CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); > INSERT INTO t1 VALUES > > === modified file 'mysql-test/r/subselect_no_semijoin.result' > --- a/mysql-test/r/subselect_no_semijoin.result 2012-07-25 16:41:48 +0000 > +++ b/mysql-test/r/subselect_no_semijoin.result 2012-08-20 11:45:33 +0000 > @@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1); > EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); > id select_type table type possible_keys key key_len ref rows Extra > 1 PRIMARY t1 ref a a 5 const 1 Using where; Using index > -2 SUBQUERY internal_tmp_table ALL group_key NULL NULL NULL 1 Using temporary; Using filesort > +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort > DROP TABLE t1; > CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); > INSERT INTO t1 VALUES > > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2012-08-15 06:34:18 +0000 > +++ b/sql/sql_select.cc 2012-08-20 11:45:33 +0000 > @@ -271,8 +271,11 @@ Item_equal *find_item_equal(COND_EQUAL * > bool *inherited_fl); > JOIN_TAB *first_depth_first_tab(JOIN* join); > JOIN_TAB *next_depth_first_tab(JOIN* join, JOIN_TAB* tab); > -JOIN_TAB *first_breadth_first_tab(JOIN *join); > -JOIN_TAB *next_breadth_first_tab(JOIN *join, JOIN_TAB *tab); > + > +enum enum_exec_or_opt {WALK_OPTIMIZATION_TABS , WALK_EXECUTION_TABS}; > +JOIN_TAB *first_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind); > +JOIN_TAB *next_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind, > + JOIN_TAB *tab); > > /** > This handles SELECT with and without UNION. > @@ -6649,12 +6652,12 @@ double JOIN::get_examined_rows() > { > ha_rows examined_rows; > double prev_fanout= 1; > - JOIN_TAB *tab= first_breadth_first_tab(this); > + JOIN_TAB *tab= first_breadth_first_tab(this, WALK_OPTIMIZATION_TABS); > JOIN_TAB *prev_tab= tab; > > examined_rows= tab->get_examined_rows(); > > - while ((tab= next_breadth_first_tab(this, tab))) > + 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); > @@ -7269,23 +7272,30 @@ prev_record_reads(POSITION *positions, u > Enumerate join tabs in breadth-first fashion, including const tables. > */ > > -JOIN_TAB *first_breadth_first_tab(JOIN *join) > +JOIN_TAB *first_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind) > { > - return join->join_tab; /* There's always one (i.e. first) table */ > + /* There's always one (i.e. first) table */ > + return (tabs_kind == WALK_EXECUTION_TABS)? join->join_tab: > + join->table_access_tabs; > } > > > -JOIN_TAB *next_breadth_first_tab(JOIN *join, JOIN_TAB *tab) > +JOIN_TAB *next_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind, > + JOIN_TAB *tab) > { > + JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, tabs_kind); > + const uint n_top_tabs_count= (tabs_kind == WALK_EXECUTION_TABS)? > + join->top_join_tab_count: > + join->top_table_access_tabs_count; > if (!tab->bush_root_tab) > { > /* We're at top level. Get the next top-level tab */ > tab++; > - if (tab < join->join_tab + join->top_join_tab_count) > + if (tab < first_top_tab + n_top_tabs_count) > return tab; > > /* No more top-level tabs. Switch to enumerating SJM nest children */ > - tab= join->join_tab; > + tab= first_top_tab; > } > else > { > @@ -7309,7 +7319,7 @@ JOIN_TAB *next_breadth_first_tab(JOIN *j > Ok, "tab" points to a top-level table, and we need to find the next SJM > nest and enter it. > */ > - for (; tab < join->join_tab + join->top_join_tab_count; tab++) > + for (; tab < first_top_tab + n_top_tabs_count; tab++) > { > if (tab->bush_children) > return tab->bush_children->start; > @@ -7333,7 +7343,7 @@ JOIN_TAB *first_top_level_tab(JOIN *join > > JOIN_TAB *next_top_level_tab(JOIN *join, JOIN_TAB *tab) > { > - tab= next_breadth_first_tab(join, tab); > + tab= next_breadth_first_tab(join, WALK_EXECUTION_TABS, tab); > if (tab && tab->bush_root_tab) > tab= NULL; > return tab; > @@ -7633,6 +7643,12 @@ get_best_combination(JOIN *join) > > join->top_join_tab_count= join->join_tab_ranges.head()->end - > join->join_tab_ranges.head()->start; > + /* > + Save pointers to select join tabs for SHOW EXPLAIN > + */ > + join->table_access_tabs= join->join_tab; > + join->top_table_access_tabs_count= join->top_join_tab_count; > + > update_depend_map(join); > DBUG_RETURN(0); > } > @@ -21389,8 +21405,8 @@ static void select_describe(JOIN *join, > bool printing_materialize_nest= FALSE; > uint select_id= join->select_lex->select_number; > > - for (JOIN_TAB *tab= first_breadth_first_tab(join); tab; > - tab= next_breadth_first_tab(join, tab)) > + for (JOIN_TAB *tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); tab; > + tab= next_breadth_first_tab(join, WALK_OPTIMIZATION_TABS, tab)) > { > if (tab->bush_root_tab) > { > @@ -21473,16 +21489,8 @@ static void select_describe(JOIN *join, > else > { > TABLE_LIST *real_table= table->pos_in_table_list; > - /* > - Internal temporary tables have no corresponding table reference > - object. Such a table may appear in EXPLAIN when a subquery that needs > - a temporary table has been executed, and JOIN::exec replaced the > - original JOIN with a plan to access the data in the temp table > - (made by JOIN::make_simple_join). > - */ > - const char *tab_name= real_table ? real_table->alias : > - "internal_tmp_table"; > - item_list.push_back(new Item_string(tab_name, strlen(tab_name), cs)); > + item_list.push_back(new Item_string(real_table->alias, > + strlen(real_table->alias), cs)); > } > /* "partitions" column */ > if (join->thd->lex->describe & DESCRIBE_PARTITIONS) > > === modified file 'sql/sql_select.h' > --- a/sql/sql_select.h 2012-06-06 19:26:40 +0000 > +++ b/sql/sql_select.h 2012-08-20 11:45:33 +0000 > @@ -897,6 +897,19 @@ class JOIN :public Sql_alloc > > public: > JOIN_TAB *join_tab, **best_ref; > + > + /* > + For "Using temporary+Using filesort" queries, JOIN::join_tab can point to > + either: > + 1. array of join tabs describing how to run the select, or > + 2. array of single join tab describing read from the temporary table. > + > + SHOW EXPLAIN code needs to read/show #1. This is why two next members are > + there for saving it. > + */ > + JOIN_TAB *table_access_tabs; > + uint top_table_access_tabs_count; > + > JOIN_TAB **map2table; ///< mapping between table indexes and JOIN_TABs > JOIN_TAB *join_tab_save; ///< saved join_tab for subquery reexecution > > > _______________________________________________ > 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] [Commits] Rev 3353: Performed re-factoring and re-structuring of the code for mwl#248: in file:///home/igor/maria/maria-5.5-mwl248-changes/
by Kristian Nielsen 13 Aug '12

13 Aug '12

Davi Arnaut <davi(a)twitter.com> writes: > On Wed, Aug 1, 2012 at 12:46 AM, Kristian Nielsen > <knielsen(a)knielsen-hq.org> wrote: >> Davi Arnaut <davi(a)twitter.com> writes: >> >>> On Thu, Jul 26, 2012 at 5:50 PM, Igor Babaev <igor(a)askmonty.org> wrote: >>>> + bzero(column_stats, sizeof(Column_statistics) * cnt); >>> >>> That's not allowed for non-POD types. >> >> Can you elaborate on exactly when bzero() (or memset() or similar) is allowed >> or not allowed? Ok, so I got time to research this a bit more. The class in question is like this, the original has more members but this is the essential structure: class Column_statistics { private: static const uint Scale_factor_nulls_ratio= 100000; public: Field *min_value; private: ulong nulls_ratio; public: void set_all_nulls() { ... } }; The question is whether it is ok to allocate this with alloc_root() or similar, and initialise it with bzero(pointer, sizeof(Column_statistics)). (The issue of bzero vs. memset is a separate one, not discussed in this mail). Davi points out that this is not a POD. This is because a POD must be a C++ aggregate, and aggregates can not have private members. Unfortunately, it seems the question of whether bzero is allowed for a non-POD, is more difficult to answer. If we look to the standards, the answer is no. It is not well-defined to bzero() any data except integers. This is what the standard says. However, this is unhelpful for the case at hand, as bzero to initialise a simple struct is a well-established practice in systems programming and commonly accepted to be valid. It seems generally accepted that bzero() of a POD is acceptable. Since the only thing that makes Column_statistics not a POD is the presence of private: members, the question becomes: Should bzero initialisation of classes with private members be disallowed? I could not find any definitive reference to support such disallowance. I am not sure why private members would be disallowed in aggretate, and hence POD. My guess is because aggregates may be initialised with an initialiser list, which makes no sense for private member. Or maybe compilers could re-arrance private members to be placed after public members. Or compilers could optimise based on knowledge that no external code can modify a private member (so it does not need to be spilled over a call to pthread_mutex_lock() or similar). But none of this would give a problem with bzero-initialisation after alloc. So I do not know of a language standard/compiler argument to disallow bzero-initialisation of non-POD. ---- But I would still strongly advice to be very careful with such code, and I think Davi's original concern is similar to mine. The problem is that Column_statistics already looks a lot like a nicely encapsulated proper C++ class. It has private members, accessors, it even defines some constants. One could easily imagine that in the future, there will be the temptation to extend it even more so, like add a virtual function - and then we suddenly have a big problem. bzero() is most certainly _NOT_ ok for such class. So I would personally stay well away from this grey area, and rather - _either_ use only a simple struct with no member functions or private or anything like that - and use bzero/memcpy and such. - _or_ do a full class, but then only initialise it with proper call of contructors (whether explicitly or implicitly defined). That makes it much clearer whether a given struct class is one or the other. But I suppose the optimiser code already heavily uses a different strategy. At the least, the declaration of class Column_statistics should have a comment saying that it is initialised with bzero(), so it can not use features not compatible with this. The nice thing about Davi's suggested rule "no bzero of non-POD" is that it is simple, easy to remember, and easy to state. Which is the primary point of coding style guidelines in the first place: make the rules clear, so there is less wasted time discussing what they are. Hope this helps, - Kristian.

1 1

Re: [Maria-developers] [Commits] Rev 3353: Performed re-factoring and re-structuring of the code for mwl#248: in file:///home/igor/maria/maria-5.5-mwl248-changes/
by Michael Widenius 13 Aug '12

13 Aug '12

Hi! >>>>> "Davi" == Davi Arnaut <davi(a)twitter.com> writes: <cut> Davi> It's not well defined, that's one of the reasons why it was Davi> deprecated. It does not even exist anymore in some systems, Davi> POSIX.1-2008 removed the specification of bzero(). It's well defined in the sence that it works everywhere (at least for MySQL code). >> Better to always use the same code to reset memory than doing it many >> different ways. In MySQL code bzero() has been the offical and >> recommended way to reset memory. Davi> Not anymore, Oracle MySQL has addressed this issue. In MariaDB code that is still not the case. Personally, I find bzero() easier to read, instead of memset() as there is no extra parameter and it's also easier grep for it. I assume you agree that for all particular purposes, there is no reason to not use bzero() in MariaDB code? Regards, Monty

2 1

[Maria-developers] Thoughts on Global Transaction ID, parallel slave, and multi-source replication
by Kristian Nielsen 10 Aug '12

10 Aug '12

This is a writeup of some thoughts I have on getting Global Transaction ID in MariaDB, as well as some related thoughts on parallel slave and multi-source replication. MySQL 5.6 has global transaction ID now, and I looked into it in some details. Unfortunately, I _really_ did not like what I saw. There are two main problems, in my opinion: 1. The basic design is bad, leading to an excessively complicated implementation. 2. The implementation looks very incomplete/immature, to the point that I am amazed that anyone would push code like this into a main tree. Point 2 means that I really do not want to maintain this piece of **** in MariaDB, though we can hope that things will improve in subsequent releases. Point 1 leads me to suggest that instead of merging the MySQL 5.6 implementation, we could do our own implementation, using a design that is both much simpler, and at the same time more flexible that what is in MySQL 5.6. I will elaborate further on these points in the rest of the mail. ---- With respect to point 2, let me quote this little gem from the MySQL 5.6 code: /* Apparently this code is not being called. We need to investigate if this is a bug or this code is not necessary. /Alfranio */ DBUG_ASSERT(0); /NOTREACHED/ Now, certainly, there are dark corners of the MySQL replication code, and adding a comment/assert like this could be justified if a developer encounters it and finds it may be dead code. But this is _new_ code, added in a new 5.6 release! So the authors of the new feature, by their own statement, have no idea what the new code they are pushing into the main tree is actually doing. That is just insane. I have no idea what the replication team at MySQL is up to these days pulling stunts like this, I have to confess I am disappointed. I found many similar examples in the 5.6 code. I wrote up a more detailed analysis in TODO-171, where I elaborate more on the problems I see with the MySQL 5.6 Global Transaction ID code: https://mariadb.atlassian.net/browse/TODO-171 When the authors do not understand the code themselves, it really does not seem attractive for us to try to support and bugfix it. I also found a lot of what I think are left-over, unsused parts of an even more complicated design that was apparently abandoned (or postponed?), but left in the code, which does not make supporting this any easier. ---- To explain point 1, I first need to mention the MySQL 5.6 feature of parallel slave apply, as I believe that a major factor in the design of MySQL Global Transaction ID has been the need to interoperate with that feature. The MySQL parallel replication slave feature works so that transactions on tables in different databases can be run in parallel with each other (incidentally, there is some indication that the MySQL replication team considers this *the* meaning of parallel slave, which is sad, as it is just one piece of the general problem of replication slave scalability - it helps in some workloads, but it is useless for others). So with parallel slave, transactions on the master can be executed in a different order on the slave. This means that the crucial notion of a slaves current position in the replication stream becomes more complicated - it is not just a single point in the master binlog, rather every slave replication thread can be at a different point in the master binlog. MySQL 5.6 solves this by introducing "Gtid sets". A Gtid is a MySQL 5.6 global transaction ID - a unique, 24-byte identifier for every transaction. The state of a replication slave at any given moment is then the set Gtids of all transactions ever executed on that slave server This notion of having a set of all transactions ever executed really complicates the design. There is a need to represent such a set as basically intervals of "holes" in the Gtid sequence, to avoid the memory representation of such sets growing without bound over time. This in turns introduces the requirement that there can _never_ be holes in the sequence of Gtids generated on a master - if there ever is introduced a hole, slaves will wait around for ever waiting for a transaction containing the missing Gtid. I think recovering from errors with such a concept will be forever a nightmare. I think this basic fundament of the design is the main source of my dislike for the MySQL global transaction ID. It sounds nice on a first look, but once you start to think deeper on the problem it just does not seem to work well. ---- So I think I have a better idea of how to make Global Transaction ID work well with parallel slave based on executing transactions in different databases out-of-order. The MySQL Gtid is defined as a pair of server-id and sequence number. Every master server has a single server-id (despite no less than 128 bits being allocated for this - anyone planning a replication hierarchy with 2**128 servers?). The master generates a single stream of events in the binlog, though streams from other servers may be interleaved with it in case of multi-master. It is then the task of a slave to try to split up the single master stream in to multiple streams that can be executed in parallel. I think this is the root of the problem. The slave is the wrong place to do this splitting! It is on the master that we know how to interpret transactions (ie. that transactions in different databases are / are not independent), so doing this on the slave is harder. And two slaves can in general split differently, which makes it harder for a slave deeper in the replication hierarchy to switch to a new master (that master being itself a slave of a higher-up master), leading to the need for a complex set-of-all-executed-transactions concept. Suppose we instead make the decision on the _master_ on how to split up the replication stream for parallel apply. Instead of a single server-id for the master, we can have multiple - let us call them "Source ID". On the _master_, we will decide that two transactions in different databases are independent, and assign them different Source ID. The slave then receives multiple replication streams, and is free to execute each stream in parallel - or not, at the discretion of the DBA. Transactions _within_ a single stream are always executed in-order, so the slave has no need to remember any sets of all transactions ever executed - all it needs to remember is the sequence number within each stream it has seen - ie. last sequence number for each Source ID. This is a simple and obvious generalisation of the old concept of current binlog position. Downstream slaves see the exact same binlog sequences, each with transactions in the same order (though sequences may be interleaved differently) - so changing masters is simpler, all servers in the hierarchy have the same view. So to implement the same functionality as MySQL 5.6 parallel replication, all we need is on the master to generate Source ID as a hash of the database name (just as MySQL 5.6 assigns transactions to slave threads based on such hash of the database name). And implement a facility on the slave such that transactions with different Source ID can be executed in parallel by multiple slave threads. And now, since we decide on the master, we can provide much more flexibility for the application. We can have _some_ databases db1,db2 be executed in parallel on the slave - while other databases db3,db4 are considered dependent, and not modified out-of-order on the slave. Or the application can declare that two transactions are independent within the same database or even within the same table; for example log record inserts into a table can often be allowed to happen in different order on slaves. Or the DBA can assign a special Source ID to a long-runnign ALTER TABLE, and such ALTER TABLE can then execute independently on slaves to not stall the normal load. All we need is to provide a simple facility (eg. session variable) that applications/users can use to specify Source ID. And then the next step is multi-source replication, which is the much-request feature that one slave can replicate from two or more different masters at the same time. The different masters will just supply different Source IDs, so apart from the need to maintain multiple TCP connections, one to each master, from the rest of the code's point of view multi-source replication is no different, it is just more independent replication streams. So my suggestion is: rather than struggle to try and merge and support the MySQL 5.6 Global Transaction ID and parallel slave, let us instead implement something like the above idea, with multiple Source IDs on the master, and parallel execution of distinct replication streams on the slave. I believe this will be no more work, and the end result will be both simpler and more flexible. Thanks, - Kristian.

4 9

[Maria-developers] Cost analysis: checking if join optimization costs match the reality.
by Sergei Petrunia 10 Aug '12

10 Aug '12

Hi! I've ran this experiment: I took the scalar subquery out of the query and then tried to compare optimizer costs with execution times. == The query == explain extended select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp where ps_partkey in (select p_partkey from part where p_name like 'forest%') ) and s_nationkey = n_nationkey and n_name = 'CANADA' order by s_name limit 10; +--+-----------+--------+------++-------------+-------+-------------------+----+---------------------------------------------------------+ |id|select_type|table |type ||key |key_len|ref |rows|Extra | +--+-----------+--------+------++-------------+-------+-------------------+----+---------------------------------------------------------+ | 1|PRIMARY |nation |ref ||n_name |26 |const | 1|Using where; Using index; Using temporary; Using filesort| | 1|PRIMARY |supplier|ref ||i_s_nationkey|5 |nation.n_nationkey | 207| | | 1|PRIMARY |partsupp|ref ||i_ps_suppkey |4 |supplier.s_suppkey | 50|Using index | | 1|PRIMARY |part |eq_ref||PRIMARY |4 |partsupp.ps_partkey| 1|Using where; FirstMatch(supplier) | +--+-----------+--------+------++-------------+-------+-------------------+----+---------------------------------------------------------+ +--+------------+-----------+------++-------------+-------+------------------+----+---------------------------------------------------------+ |id|select_type |table |type ||key |key_len|ref |rows|Extra | +--+------------+-----------+------++-------------+-------+------------------+----+---------------------------------------------------------+ | 1|PRIMARY |nation |ref ||n_name |26 |const | 1|Using where; Using index; Using temporary; Using filesort| | 1|PRIMARY |supplier |ref ||i_s_nationkey|5 |nation.n_nationkey| 207| | | 1|PRIMARY |<subquery2>|eq_ref||distinct_key |4 |func | 1| | | 2|MATERIALIZED|part |range ||p_name |58 |NULL |2387|Using where; Using index | | 2|MATERIALIZED|partsupp |ref ||i_ps_partkey |4 |part.p_partkey | 2|Using index | +--+------------+-----------+------++-------------+-------+------------------+----+---------------------------------------------------------+ === Execution times === FirstMatch: 0.20 - 0.30 sec. Materialization: 0.20 sec - 0.30 sec run times for both fluctuate between 0.20 and 0.30 sec, FirstMatch seems to be somewhat slower (dunno if the difference is statistically meaningful) == Optimizer costs === FirstMatch: 11029.4 Materialization: 3594.5 === Read record counters === (source data is plan counters provided below) FirstMatch: 43185 Materialization: 12354 + 9K temptable writes + 400 reads === Conclusions === - optimizer thinks FirstMatch is 3x more expensive. - In reality it is *somewhat* expensive. - Record counters say FirstMatch is 3.5x .. 1.9x more expensive, depending on how we treat temptable writes/reads ==> Overall, I see no big discrepancies. ==> Also, I have also discovered that JOIN::get_prefix_cost_and_fanout() forgets to add record_count/TIME_FOR_COMPARE, which causes (slightly) different cost for SJ-Materialization. (I have noted the difference in some previous email) === Firstmatch plan counters === MariaDB [dbt3sf1]> show table_statistics; +--------------+------------+-----------+--------------+-------------------------+ | Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes | +--------------+------------+-----------+--------------+-------------------------+ | dbt3sf1 | nation | 1 | 0 | 0 | | dbt3sf1 | part | 21386 | 0 | 0 | | dbt3sf1 | partsupp | 21386 | 0 | 0 | | dbt3sf1 | supplier | 412 | 0 | 0 | +--------------+------------+-----------+--------------+-------------------------+ 4 rows in set (0.00 sec) MariaDB [dbt3sf1]> show index_statistics; +--------------+------------+---------------+-----------+ | Table_schema | Table_name | Index_name | Rows_read | +--------------+------------+---------------+-----------+ | dbt3sf1 | nation | n_name | 1 | | dbt3sf1 | part | PRIMARY | 21386 | | dbt3sf1 | supplier | i_s_nationkey | 412 | | dbt3sf1 | partsupp | i_ps_suppkey | 21386 | +--------------+------------+---------------+-----------+ Total= 43185 rows read === Materialization plan counters === MariaDB [dbt3sf1]> show index_statistics; +--------------+------------+---------------+-----------+ | Table_schema | Table_name | Index_name | Rows_read | +--------------+------------+---------------+-----------+ | dbt3sf1 | nation | n_name | 1 | | dbt3sf1 | part | p_name | 2389 | | dbt3sf1 | supplier | i_s_nationkey | 412 | | dbt3sf1 | partsupp | i_ps_partkey | 9552 | +--------------+------------+---------------+-----------+ 4 rows in set (0.00 sec) MariaDB [dbt3sf1]> show table_statistics; +--------------+------------+-----------+--------------+-------------------------+ | Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes | +--------------+------------+-----------+--------------+-------------------------+ | dbt3sf1 | nation | 1 | 0 | 0 | | dbt3sf1 | part | 2389 | 0 | 0 | | dbt3sf1 | partsupp | 9552 | 0 | 0 | | dbt3sf1 | supplier | 412 | 0 | 0 | +--------------+------------+-----------+--------------+-------------------------+ 4 rows in set (0.00 sec) Total = 12354 rows read + 9K temptable writes + 400 reads from temptable. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 1

[Maria-developers] Cost analysis: Materialization plan
by Sergei Petrunia 10 Aug '12

10 Aug '12

Debugging with optimizer_switch='materialization=on', query: explain extended select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp where ps_partkey in (select p_partkey from part where p_name like 'forest%') and ps_availqty > (select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year )) and s_nationkey = n_nationkey and n_name = 'canada' order by s_name limit 10; +----+------------------+-----------+------++-------------------+-------+---------------------------------------+----+--------+--------------------------------------------+ |id |select_type |table |type ||key |key_len|ref |rows|filtered|Extra | +----+------------------+-----------+------++-------------------+-------+---------------------------------------+----+--------+--------------------------------------------+ | 1|PRIMARY |nation |ALL ||NULL |NULL |NULL | 25| 100.00|Using where; Using temporary; Using filesort| | 1|PRIMARY |supplier |ref ||i_s_nationkey |5 |nation.n_nationkey | 180| 100.00| | | 1|PRIMARY |<subquery2>|eq_ref||distinct_key |4 |func | 1| 100.00| | | 2|MATERIALIZED |part |range ||p_name |58 |NULL |2387| 100.00|Using where; Using index | | 2|MATERIALIZED |partsupp |ref ||PRIMARY |4 |part.p_partkey | 1| 100.00|Using where | | 4|DEPENDENT SUBQUERY|lineitem |ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 4| 100.00|Using where | +----+------------------+-----------+------++-------------------+-------+---------------------------------------+----+--------+--------------------------------------------+ ********* SEMI-JOIN pre-optimization ************** ===== PRE-1. part === (gdb) p position->records_read $564 = 2387 (gdb) p position->read_time $565 = 497.33246183668518 (gdb) p current_record_count / TIME_FOR_COMPARE $566 = 477.39999999999998 (gdb) p current_read_time $567 = 974.73246183668516 ===== PRE-2. partsupp === (gdb) p position->records_read $570 = 1 (gdb) p position->read_time $571 = 2390.4263969377266 (gdb) p current_record_count / TIME_FOR_COMPARE $572 = 477.39999999999998 (gdb) p current_read_time $573 = 3842.5588587744119 ## (part, partsupp) is the best plan to materialize the subquery. (gdb) p join->best_read $575 = 6229.5578587744112 (gdb) p join->record_count $576 = 2387 ## Extra expenses for semi-join (gdb) print lookup_cost $582 = 0.050000000000000003 (gdb) print write_cost $584 = 0.050000000000000003 ********* MAIN join optimization ************** ===== 1. nation === (gdb) p position->records_read $587 = 25 (gdb) p position->read_time $588 = 1 (gdb) p current_record_count / TIME_FOR_COMPARE $589 = 5 (gdb) p current_read_time $590 = 6 ===== 2. supplier === (gdb) p position->records_read $593 = 180 (gdb) p position->read_time $594 = 4525 (gdb) p current_record_count / TIME_FOR_COMPARE $595 = 900 (gdb) p current_read_time $596 = 5431 ## here it considers "part, partsupp" extension, with huge cost: ## I don't skip it: === 3.A part === (gdb) p position->records_read $599 = 2387 (gdb) p position->read_time $600 = 2237996.0782650835 (gdb) p current_record_count / TIME_FOR_COMPARE $601 = 2148300 (gdb) p current_read_time $602 = 4391727.078265084 === 4.A partsupp == (gdb) p position->records_read $605 = 1 (gdb) p position->read_time $606 = 10741500 (gdb) p current_record_count / TIME_FOR_COMPARE $607 = 2148300 (gdb) p current_read_time $608 = 17281527.078265086 > advance_sj_state > Firstmatch_picker::check_qep ### Picks first match with (gdb) print read_time $615 = 12984927.078265084 (gdb) print rec_count $616 = 4500 < Firstmatch_picker::check_qep > Sj_materialization_picker::check_qep (gdb) p prefix_cost.total_cost() $622 = 5431 # OK, same as $596 (gdb) p mat_info->materialization_cost.total_cost() $623 = 3007.1088587744121 ## This one is weird. It should be $575 + $582 + $584= 6229.6578587744098 ## I will investigate. ## The difference is not millions, but a ~3K. (gdb) print prefix_rec_count $625 = 4500 (gdb) p mat_info->lookup_cost.total_cost() $626 = 0.050000000000000003 < Sj_materialization_picker::check_qep ## Materialization is chosen as the best strategy with (gdb) print current_record_count $634 = 4500 (gdb) print current_read_time $635 = 8663.1088587744125 < advance_sj_state === 5. Finally === We hit these lines: if (join->sort_by_table && join->sort_by_table != join->positions[join->const_tables].table->table) /* We have to make a temp table */ current_read_time+= current_record_count; and get the final query plan cost of (gdb) p join->record_count $643 = 4500 (gdb) p join->best_read $644 = 13163.107858774412 #records is the same for plan in this email and in the previous. The cost is very different, however, it is 13K vs 360K. Now, when we have the breakdown of the numbers, we can evaluate what did the optimizer get wrong. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 2

[Maria-developers] Cost analysis: FirstMatch plan
by Sergei Petrunia 09 Aug '12

09 Aug '12

Hi Timour, Please find below cost analysis for the plan with FirstMatch strategy. Debugging with optimizer_switch='semijoin=off', query: explain extended select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp where ps_partkey in (select p_partkey from part where p_name like 'forest%') and ps_availqty > (select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year )) and s_nationkey = n_nationkey and n_name = 'CANADA' order by s_name limit 10; +----+------------------+--------+------++-------------------+-------+---------------------------------------+----+--------------------------------------------+ |id |select_type |table |type ||key |key_len|ef |rows|Extra | +----+------------------+--------+------++-------------------+-------+---------------------------------------+----+--------------------------------------------+ | 1|PRIMARY |nation |ALL ||NULL |NULL |NULL | 25|Using where; Using temporary; Using filesort| | 1|PRIMARY |supplier|ref ||i_s_nationkey |5 |nation.n_nationkey | 180| | | 1|PRIMARY |partsupp|ref ||i_ps_suppkey |4 |supplier.s_suppkey | 39|Using where | | 1|PRIMARY |part |eq_ref||PRIMARY |4 |partsupp.ps_partkey | 1|Using where; FirstMatch(supplier) | | 4|DEPENDENT SUBQUERY|lineitem|ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 4|Using where | +----+------------------+--------+------++-------------------+-------+---------------------------------------+----+--------------------------------------------+ ===== 1. nation === (gdb) p position->records_read $367 = 25 (gdb) p position->read_time $368 = 1 (gdb) p current_record_count / TIME_FOR_COMPARE $369 = 5 (gdb) p current_read_time $370 = 6 ===== 2. supplier === (gdb) p position->records_read $377 = 180 (gdb) p position->read_time $378 = 4525 (gdb) p current_record_count / TIME_FOR_COMPARE $379 = 900 (gdb) p current_read_time $380 = 5431 ## here it considers "part, partsupp" extension, with cost ## 12,984,927. I skip that, because that's not the optimal plan ===== 3. partsupp === (gdb) p position->records_read $411 = 39 (gdb) p position->read_time $412 = 180000 (gdb) p current_record_count / TIME_FOR_COMPARE $413 = 35100 (gdb) p current_read_time $414 = 220531 ===== 4. part === (gdb) p position->records_read $421 = 1 (gdb) p position->read_time $422 = 175500 (gdb) p current_record_count / TIME_FOR_COMPARE $423 = 35100 (gdb) p current_read_time $424 = 431131 > advance_sj_state() > Firstmatch_picker::check_qep > optimize_wo_join_buffering ### It doesn't make any best_access_path() calls here, because no table is ### using join buffering. ### It forgets to add (current_record_count / TIME_FOR_COMPARE), and ends ### up with a slightly lower cost of (gdb) print cost $425 = 360931 > optimize_wo_join_buffering > Firstmatch_picker::check_qep ## FirstMatch is chosen as the way we will use to eliminate duplicates.. ## At the end of advance_sj_state, we have: (gdb) p *current_read_time $426 = 360931 (gdb) p *current_record_count $427 = 4500 > advance_sj_state(). And this is what is chosen as the final query plan. -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 2

[Maria-developers] Cost analysis: check if optimizer is able to produce a correct E(#scalar_subquery executions)
by Sergei Petrunia 09 Aug '12

09 Aug '12

Let's see if the optimizer is theoretically able to attach the subquery to the right table for the FirstMatch plan: On Thu, Aug 09, 2012 at 12:39:00PM +0400, Sergei Petrunia wrote: > == Modified query == ... > > == Benchmarking == > > Number of times scalar-subquery was executed: > Materialization: 9552 times (same as before) > First-Match: 299 times (A LOT LESS!) > > Execution times: > Materialization: 5.30 sec (same as before) > First-Match: 1.72 sec (a lot less, used to be 15 sec! Now it beats > materialization) > Let's check whether the optimizer has sufficient input data to conclude that moving the scalar-subquery from table partsupp to table part is advantageous. The data about extra selectivity is basically tab->quick_condition_rows. Let's take the EXPLAIN and its analysis from the "Cost analysis: optimizer statistics vs real dataset properties" email. Relevant lines will start with 'NOTE>'. +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ |id|select_type |table |type ||key |key_len|ref |rows|filtered|Extra | +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ | 1|PRIMARY |nation |ref ||n_name |26 |const | 1| 100.00|Using where; Using index; Using temporary; Using filesort| | 1|PRIMARY |supplier|ref ||i_s_nationkey |5 |nation.n_nationkey | 251| 100.00| | | 1|PRIMARY |partsupp|ref ||i_ps_suppkey |4 |supplier.s_suppkey | 34| 100.00|Using where | | 1|PRIMARY |part |eq_ref||PRIMARY |4 |partsupp.ps_partkey | 1| 100.00|Using where; FirstMatch(supplier) | | 4|DEP. SUBQUERY|lineitem|ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 3| 100.00|Using where | +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ 5 rows in set, 3 warnings (0.01 sec) === nation: === like above, 1 row. OK. NOTE> - "using where" won't filter anything out. === supplier: === see above, 251 rows, ~ok 412 real... NOTE> - "using where" won't filter anything out. === partsupp: === - 800K rows and 10K distinct ps_suppkey, which gives rec_per_key=80 (EXPLAIN shows 34) - as for data that we will hit: select count(*) from nation, supplier, partsupp where s_suppkey=ps_suppkey and s_nationkey=n_nationkey and n_name='canada'; gives 32690 rows. 32690 / 412 = 80, matches rec_per_key. NOTE> "using where" won't filter anything out (NOT TAKING the scalar-subquery into account) === part: === - eq_ref, so exactly 1 match. We know DBT-3 dataset is such that it always has one. NOTE> "using where" is "p_name like forest"! it will filter stuff out! I haven't checked, but I suppose that condition "p_name like forest%'(*) is not correlated with the table access condition, 'p_partkey=partsupp_ps_partkey'. The email 'Cost analysis: Materialization plan' shows a range access on (*), with the estimate rows=2378. Total number of rows in table part is is 200K, InnoDB's estimate is 200999. This gives selectivity of 0.0118. === How many times scalar-subquery will be evaluated === Let's take estimate numbers: 1 * 251 * 34 * 1 * 0.0118 = 100.7012 evaluations. If the estimates were perfect: 1 * 400 * 80 * 1 * 0.118 = 377.6 evaluations As mentioned at the top of this email, in reality subquery is evaluated 299 times. === Conclusion#1 === quick_condition estimates provide information that will allow the optimizer to make a correct conclusion about # of times that scalar-subquery will be evaluated after table part. What remains to be checked: check that our cost formulas will make the plan of { FirstMatch, scalar-subquery attached to table part} cheaper than { FirstMatch, scalar-subquery attached to table partsupp} and cheaper than the SJ-Materialization plan. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Cost analysis: checking how fast cost-based predicate pushdown can be
by Sergei Petrunia 09 Aug '12

09 Aug '12

... > ********* Some benchmarking ************* > Execution times: > - FirstMatch is ~15 sec > - Materialization: ~5 sec. > > With scalar-subquery predicate replaced with TRUE: > - Materialization: ~0.20 sec. > - FirstMatch: 0.19 sec. > > Number of times the scalar subquery was executed: > - DEP. SUBQUERY: ... 24055 times > > - MATERIALIZATION: 9555 times > > If we take the cost of scalar-subquery predicate evaluation into account, > the difference should be around: > > (24055 - 9555) * 5 = 72K > > which is sufficient. Then, we've tried to imitate the effects of "cost-based predicate pushdown" (mdev-83), by making the scalar subquery predicate depend on both tables part and partsupp. In order to do that, we've also had to convert the subquery (select p_partkey from part where p_name like 'forest%') into join. This change doesn't affect the optimizer because this subquery would be converted into an inner join anyway. == Modified query == explain select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp, part where ps_partkey = p_partkey and p_name like 'forest%' and ps_availqty + p_retailprice*0 > (select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year )) and s_nationkey = n_nationkey and n_name = 'CANADA' order by s_name limit 10; == Benchmarking == Number of times scalar-subquery was executed: Materialization: 9552 times (same as before) First-Match: 299 times (A LOT LESS!) Execution times: Materialization: 5.30 sec (same as before) First-Match: 1.72 sec (a lot less, used to be 15 sec! Now it beats materialization) BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] what compiler should i use when building mariadb?
by Rich Prohaska 09 Aug '12

09 Aug '12

hello, we have been using gcc 4.4 to build mysql and mariadb successfully. however, when we tried to use gcc 4.7.1 to build mariadb-5.5.25, we hit this problem: /home/tokubuild/build-tokudb-46462/mariadb-5.5.25-tokudb-46462-linux-x86_64-build/mariadb-5.5.25-tokudb-46462-src/mysys/my_context.c: Assembler messages: /home/tokubuild/build-tokudb-46462/mariadb-5.5.25-tokudb-46462-linux-x86_64-build/mariadb-5.5.25-tokudb-46462-src/mysys/my_context.c:207: Error: CFI instruction used without previous .cfi_startproc what compiler do you use to build mariadb? thanks rich prohaska

4 9

[Maria-developers] Cost analysis: optimizer statistics vs real dataset properties
by Sergei Petrunia 09 Aug '12

09 Aug '12

So, we have done: alter table name add index(n_name); and now we have: explain extended select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp where ps_partkey in (select p_partkey from part where p_name like 'forest%') and ps_availqty > (select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year )) and s_nationkey = n_nationkey and n_name = 'canada' order by s_name limit 10; +--+------------------+-----------+------++-------------------+-------+---------------------------------------+----+---------------------------------------------------------+ |id|select_type |table |type ||key |key_len|ref |rows|Extra | +--+------------------+-----------+------++-------------------+-------+---------------------------------------+----+---------------------------------------------------------+ | 1|PRIMARY |nation |ref ||n_name |26 |const | 1|Using where; Using index; Using temporary; Using filesort| | 1|PRIMARY |supplier |ref ||i_s_nationkey |5 |nation.n_nationkey | 251| | | 1|PRIMARY |<subquery2>|eq_ref||distinct_key |4 |func | 1| | | 2|MATERIALIZED |part |range ||p_name |58 |NULL |2387|Using where; Using index | | 2|MATERIALIZED |partsupp |ref ||PRIMARY |4 |part.p_partkey | 1|Using where | | 4|DEPENDENT SUBQUERY|lineitem |ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 3|Using where | +--+------------------+-----------+------++-------------------+-------+---------------------------------------+----+---------------------------------------------------------+ === nation: === 1 row scanned, for real OK. - "using where" doesn't filter anything out. === supplier === - dataset has 412 rows for CANADA - rec_per_key: 10L suppliers / 25 countries=400 === <subquery2> == - eq_ref, we know that there's always match - "using where" won't filter anything out. === part === - dataset/where match 2378 rows for (p_name like 'forest%') OK. - "using where" won't filter anything out. === partsupp === - rec_per_key: partsupp has 800K rows and 200K distinct ps_partkey values, which gives rec_per_key=4 (while EXPLAIN shows 1!) - select count(*) from partsupp where ps_partkey in (select p_partkey from part where p_name like 'forest%'); gives 9552 rows. (rec_per_key=4) * 2378 = 9512 , close to 9552. - "using where" won't filter anything out (NOT TAKING scalar-subquery into account) +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ |id|select_type |table |type ||key |key_len|ref |rows|filtered|Extra | +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ | 1|PRIMARY |nation |ref ||n_name |26 |const | 1| 100.00|Using where; Using index; Using temporary; Using filesort| | 1|PRIMARY |supplier|ref ||i_s_nationkey |5 |nation.n_nationkey | 251| 100.00| | | 1|PRIMARY |partsupp|ref ||i_ps_suppkey |4 |supplier.s_suppkey | 34| 100.00|Using where | | 1|PRIMARY |part |eq_ref||PRIMARY |4 |partsupp.ps_partkey | 1| 100.00|Using where; FirstMatch(supplier) | | 4|DEP. SUBQUERY|lineitem|ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 3| 100.00|Using where | +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ 5 rows in set, 3 warnings (0.01 sec) === nation: === like above, 1 row. OK. - "using where" won't filter anything out. === supplier: === see above, 251 rows, ~ok 412 real... === partsupp: === - 800K rows and 10K distinct ps_suppkey, which gives rec_per_key=80 (EXPLAIN shows 34) - as for data that we will hit: select count(*) from nation, supplier, partsupp where s_suppkey=ps_suppkey and s_nationkey=n_nationkey and n_name='canada'; gives 32690 rows. 32690 / 412 = 80, matches rec_per_key. - "using where" won't filter anything out (NOT TAKING scalar-subquery into account) === part: === - eq_ref, so exactly 1 match. We know DBT-3 dataset is such that it always has one. - "using where" is "p_name like forest"! it will filter stuff out! ^^^^^^^^ but does it matter.. subquery is attached to ps? ********* Some benchmarking ************* Execution times: - FirstMatch is ~15 sec - Materialization: ~5 sec. With scalar-subquery predicate replaced with TRUE: - Materialization: ~0.20 sec. - FirstMatch: 0.19 sec. Number of times the scalar subquery was executed: - DEP. SUBQUERY: ... 24055 times - MATERIALIZATION: 9555 times If we take the cost of scalar-subquery predicate evaluation into account, the difference should be around: (24055 - 9555) * 5 = 72K which is sufficient. The problem is, Materialization executes the scalar-subquery fewer times than FirstMatch. And you expect that taking into account scalar-subquery execution cost will cause Materialization not to be used?? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Cost analysis: comparison of the two approaches
by Sergei Petrunia 08 Aug '12

08 Aug '12

So, we're comparing two plans that were mentioned earlier. FirstMatch plan has: (gdb) p *current_read_time $426 = 360931 (gdb) p *current_record_count $427 = 4500 which is much more greater than the SJ-Materialization plan, which has: (gdb) print current_record_count $634 = 4500 (gdb) print current_read_time $635 = 8663.1088587744125 Let's check whether the difference can be overcame by adding the cost of the scalar-subquery predicate. Cost of one execution of scalar subquery predicate is 4.5 ~= 5. SJ-Materialization plan will execute the subquery predicate 2378 times: (gdb) p join->best_read $575 = 6229.5578587744112 (gdb) p join->record_count $576 = 2387 which gives added cost of 2378 * 5 = 11890. 8663 + 11890 is still much less than 360931. Hence, just taking scalar-subquery cost into account is not sufficient for query plan to be changed. ps. psergey's opinion: too much unaccounted-for WHERE clauses. to be elaborated further... BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Review request: SHOW EXPLAIN
by Sergei Petrunia 08 Aug '12

08 Aug '12

Hi Sergei, Please find attached the combined SHOW EXPLAIN patch. It is ready for review of the concurrency part. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

3 12

[Maria-developers] Streamlining Buildbot builds
by Daniel Bartholomew 08 Aug '12

08 Aug '12

Serg brought this to my attention and asked me to look into it. Basically, over time we keep adding more and more builders to buildbot and we build an ever increasing number of packages. One way to keep turnaround times for builds reasonable is to add more hardware. Which we are doing by adding a third KVM build host. However, building every push on every KVM virtual machine is overkill. For example, if one Ubuntu build succeeds, most other Ubuntu builds will also succeed. We only really need to build "everything" when we are making a release. For other builds we can get away with building on a sub-set of builders. The main benefit of doing this is to speed up the turn-around time for builds. One potential issue is the possibility of not catching an error which, for some reason, only manifests on a certain OS version. There may be other issues. Let me know if you think of any. Assuming we want to move ahead with building most builds on a sub-set of our builders instead of the full set, the easiest thing to do is to configure buildbot to only build on every builder for pushes into the "-release" and top-level (5.3, 5.5, etc...) branches. All other branches would have an abridged set of builders assigned to them. First things first, we need to decide which builders will build every push in every tree (aka "build everything") and which builders will only build releases. Builders on dedicated machines don't need to be changed, only KVM builders. Here are the KVM builders I propose to be in the "build everything" set: "kvm-bintar-precise-amd64", "kvm-bintar-precise-x86", "kvm-deb-debian6-amd64", "kvm-deb-debian6-x86", "kvm-deb-precise-amd64", "kvm-deb-precise-x86", "kvm-rpm-fedora17-amd64", "kvm-rpm-fedora17-x86", "kvm-rpm-centos6-amd64", "kvm-rpm-centos6-x86", The reasoning for choosing the above is to have an x86 and amd64 builder for Ubuntu, CentOS, Fedora, and Debian. And also to always build binary tarballs. Thoughts? 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://gplus.to/mariadb Facebook - http://fb.com/MariaDB.dbms Knowledgebase - http://kb.askmonty.org Monty Program - http://montyprogram.com

3 2

[Maria-developers] Mysql crashes with signal 11 after a select (or even explain) on a view with a subselect.
by kirchy＠gmx.de 03 Aug '12

03 Aug '12

>Description: we see some problems with views that include other views (I think that is the problem...) and selects on views with subselects. The log says after a crash: Aug 3 17:29:22 db7 mysqld: 120803 17:29:22 [ERROR] mysqld got signal 11 ; Aug 3 17:29:22 db7 mysqld: This could be because you hit a bug. It is also possible that this binary Aug 3 17:29:22 db7 mysqld: or one of the libraries it was linked against is corrupt, improperly built, Aug 3 17:29:22 db7 mysqld: or misconfigured. This error can also be caused by malfunctioning hardware. Aug 3 17:29:22 db7 mysqld: We will try our best to scrape up some info that will hopefully help diagnose Aug 3 17:29:22 db7 mysqld: the problem, but since we have already crashed, something is definitely wrong Aug 3 17:29:22 db7 mysqld: and this may fail. Aug 3 17:29:22 db7 mysqld: Aug 3 17:29:22 db7 mysqld: key_buffer_size=0 Aug 3 17:29:22 db7 mysqld: read_buffer_size=2097152 Aug 3 17:29:22 db7 mysqld: max_used_connections=100 Aug 3 17:29:22 db7 mysqld: max_threads=302 Aug 3 17:29:22 db7 mysqld: threads_connected=35 Aug 3 17:29:22 db7 mysqld: It is possible that mysqld could use up to Aug 3 17:29:22 db7 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5570184 K Aug 3 17:29:22 db7 mysqld: bytes of memory Aug 3 17:29:22 db7 mysqld: Hope that's ok; if not, decrease some variables in the equation. Aug 3 17:29:22 db7 mysqld: Aug 3 17:29:22 db7 mysqld: Thread pointer: 0x7f3521cba000 Aug 3 17:29:22 db7 mysqld: Attempting backtrace. You can use the following information to find out Aug 3 17:29:22 db7 mysqld: where mysqld died. If you see no messages after this, something went Aug 3 17:29:22 db7 mysqld: terribly wrong... Aug 3 17:29:22 db7 mysqld: stack_bottom = 0x7f1f899b4e98 thread_stack 0x3c000 Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x2e) [0x7f341e1f39de] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(handle_segfault+0x3e5) [0x7f341de306f5] Aug 3 17:29:22 db7 mysqld: /lib/libpthread.so.0(+0xef60) [0x7f341c1def60] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(Field_dependency_recorder::visit_field(Field*)+0x1) [0x7f341dfb48c1] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(Item_field::enumerate_field_refs_processor(unsigned char*)+0x16) [0x7f341dd779d6] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(Item_subselect::enumerate_field_refs_processor(unsigned char*)+0x4c) [0x7f341ddeb6dc] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(Dep_analysis_context::setup_equality_modules_deps(List<Dep_module>*)+0x13e) [0x7f341dfb406e] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(+0x4e140d) [0x7f341dfb440d] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(+0x4e15a0) [0x7f341dfb45a0] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(+0x3d2eae) [0x7f341dea5eae] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(JOIN::optimize()+0x465) [0x7f341dea7a55] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0xda) [0x7f341deb04da] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x15d) [0x7f341deb0f5d] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(+0x368baa) [0x7f341de3bbaa] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(mysql_execute_command(THD*)+0x40e) [0x7f341de3e05e] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x2c1) [0x7f341de43711] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xceb) [0x7f341de4485b] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(do_command(THD*)+0xf4) [0x7f341de45364] Aug 3 17:29:22 db7 mysqld: /usr/sbin/mysqld(handle_one_connection+0x13e) [0x7f341de3762e] Aug 3 17:29:22 db7 mysqld: /lib/libpthread.so.0(+0x68ba) [0x7f341c1d68ba] Aug 3 17:29:22 db7 mysqld: /lib/libc.so.6(clone+0x6d) [0x7f341b7a002d] Aug 3 17:29:22 db7 mysqld: This happens on all servers over here. At least all I tested it on... even if these queries are too complex, the mysqld should never die with signal 11 but give some kind of sql error! >How-To-Repeat: I have some queries over here that crash the server. Is there something like a drop-in binary for mysqld I could use to gather more information for you? or shall I run mysqld with strace or so? the database is far to big and complex to copy it (besides legal reasons, I can't send it to anybody), but I have replication slaves I can use for testing. >Fix: just don't run the query... no idea. >Submitter-Id: <submitter ID> >Originator: root >Organization: kirchy >MySQL support: none >Synopsis: Mysql crashes with signal 11 after a select (or even explain) on a view with a subselect. >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-5.2.7-MariaDB-mariadb101~squeeze ((MariaDB - http://mariadb.com/)) >Server: /usr/bin/mysqladmin Ver 9.0 Distrib 5.2.7-MariaDB, for debian-linux-gnu on x86_64 Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc, 2009 Monty Program Ab This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.2.7-MariaDB-mariadb101~squeeze-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 57 min 5 sec Threads: 23 Questions: 491608 Slow queries: 175 Opens: 3750 Flush tables: 1 Open tables: 460 Queries per second avg: 143.535 >C compiler: gcc-4.4.real (Debian 4.4.4-8) 4.4.5 20100728 (prerelease) >C++ compiler: g++-4.4.real (Debian 4.4.4-8) 4.4.5 20100728 (prerelease) >Environment: System: Linux db7 2.6.39-bpo.2-amd64 #1 SMP Tue Jul 26 10:35:23 UTC 2011 x86_64 GNU/Linux Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: x86_64-linux-gnu Configured with: ../src/configure -v --with-pkgversion='Debian 4.4.5-8' --with-bugurl=file:///usr/share/doc/gcc-4.4/README.Bugs --enable-languages=c,c++,fortran,objc,obj-c++ --prefix=/usr --program-suffix=-4.4 --enable-shared --enable-multiarch --enable-linker-build-id --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --with-gxx-include-dir=/usr/include/c++/4.4 --libdir=/usr/lib --enable-nls --enable-clocale=gnu --enable-libstdcxx-debug --enable-objc-gc --with-arch-32=i586 --with-tune=generic --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu Thread model: posix gcc version 4.4.5 (Debian 4.4.5-8) Compilation info (call): CC='gcc -static-libgcc' CFLAGS='-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized' CXX='g++ -static-libgcc' CXXFLAGS='-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized' LDFLAGS='' ASFLAGS='' Compilation info (used): CC='gcc -static-libgcc' CFLAGS=' -O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized -DUNIV_LINUX' CXX='g++ -static-libgcc' CXXFLAGS=' -O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized -fno-implicit-templates -fno-exceptions -fno-rtti' LDFLAGS=' -rdynamic ' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 14 3. Mär 2011 /lib/libc.so.6 -> libc-2.11.2.so -rwxr-xr-x 1 root root 1432968 23. Jan 2011 /lib/libc-2.11.2.so -rw-r--r-- 1 root root 4433334 23. Jan 2011 /usr/lib/libc.a -rw-r--r-- 1 root root 247 23. Jan 2011 /usr/lib/libc.so Configure command: ../configure '--build=x86_64-linux-gnu' '--host=x86_64-linux-gnu' '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--with-server-suffix=-mariadb101~squeeze' '--with-comment=(MariaDB - http://mariadb.com/)' '--with-system-type=debian-linux-gnu' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-pstack' '--with-fast-mutexes' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-big-tables' '--without-docs' '--with-extra-charsets=all' '--with-libwrap' '--with-ssl=/usr' '--with-libevent' '--without-readline' '--with-zlib-dir=' '--with-embedded-server' '--with-embedded-privilege-control' '--with-plugins=max-no-ndb' '--without-plugin-innodb_plugin' 'build_alias=x86_64-linux-gnu' 'host_alias=x86_64-linux-gnu' 'CC =gcc -static-libgcc' 'CFLAGS=-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized' 'LDFLAGS=' 'CPPFLAGS=' 'CXX=g++ -static-libgcc' 'CXXFLAGS=-O2 -fno-omit-frame-pointer -g -pipe -Wall -Wno-uninitialized'

2 1