#At lp:maria 2712 sanja@askmonty.org 2009-06-11 Real fix for bug Bug#41098 (http://bugs.mysql.com/bug.php?id=41098). Invalidate tables changed in insert after unlocking tables when the result of insert become really visible. modified: sql/handler.h sql/mysql_priv.h sql/sql_base.cc sql/sql_cache.cc sql/sql_cache.h sql/sql_delete.cc sql/sql_insert.cc sql/sql_load.cc sql/sql_parse.cc sql/sql_partition.cc sql/sql_rename.cc sql/sql_table.cc sql/sql_update.cc sql/sql_view.cc sql/table.h storage/maria/ha_maria.h storage/myisam/ha_myisam.h per-file messages: sql/handler.h table type for nontransactional tables with delayed to unlock insert visibility. sql/mysql_priv.h Invalidate call changed. sql/sql_base.cc Invalidation of marked tables. sql/sql_cache.cc Marking tables for on-unlock-invalidation added. sql/sql_cache.h Invalidate call changed. sql/sql_delete.cc Invalidate call changed. sql/sql_insert.cc Invalidate call changed. sql/sql_load.cc Invalidate call changed. sql/sql_parse.cc Invalidate call changed. sql/sql_partition.cc Invalidate call changed. sql/sql_rename.cc Invalidate call changed. sql/sql_table.cc Invalidate call changed. sql/sql_update.cc Invalidate call changed. sql/sql_view.cc Invalidate call changed. sql/table.h mark for tables which need query cache invalidation on unlock. storage/maria/ha_maria.h MyISAM and maria 1.5 use the new type of table for query cache. storage/myisam/ha_myisam.h MyISAM and maria 1.5 use the new type of table for query cache. === modified file 'sql/handler.h' --- a/sql/handler.h 2009-02-19 09:01:25 +0000 +++ b/sql/handler.h 2009-06-11 12:45:53 +0000 @@ -247,6 +247,11 @@ #define HA_CACHE_TBL_NOCACHE 1 #define HA_CACHE_TBL_ASKTRANSACT 2 #define HA_CACHE_TBL_TRANSACT 4 +/** + Non transactional table but insert results visible for other threads + only on unlock +*/ +#define HA_CACHE_TBL_NTRNS_INS2LOCK 8 /* Options of START TRANSACTION statement (and later of SET TRANSACTION stmt) */ #define MYSQL_START_TRANS_OPT_WITH_CONS_SNAPSHOT 1 === modified file 'sql/mysql_priv.h' --- a/sql/mysql_priv.h 2009-04-25 10:05:32 +0000 +++ b/sql/mysql_priv.h 2009-06-11 12:45:53 +0000 @@ -893,7 +893,7 @@ struct Query_cache_query_flags #define query_cache_init() query_cache.init() #define query_cache_resize(A) query_cache.resize(A) #define query_cache_set_min_res_unit(A) query_cache.set_min_res_unit(A) -#define query_cache_invalidate3(A, B, C) query_cache.invalidate(A, B, C) +#define query_cache_invalidate4(A, B, C, D) query_cache.invalidate(A, B, C, D) #define query_cache_invalidate1(A) query_cache.invalidate(A) #define query_cache_send_result_to_client(A, B, C) \ query_cache.send_result_to_client(A, B, C) @@ -912,7 +912,7 @@ struct Query_cache_query_flags #define query_cache_init() #define query_cache_resize(A) #define query_cache_set_min_res_unit(A) -#define query_cache_invalidate3(A, B, C) +#define query_cache_invalidate4(A, B, C, D) #define query_cache_invalidate1(A) #define query_cache_send_result_to_client(A, B, C) 0 #define query_cache_invalidate_by_MyISAM_filename_ref NULL === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2009-05-19 09:28:05 +0000 +++ b/sql/sql_base.cc 2009-06-11 12:45:53 +0000 @@ -1373,6 +1373,15 @@ bool close_thread_table(THD *thd, TABLE table->s->table_name.str, (long) table)); *table_ptr=table->next; + + /* Invalidate if it has mark about changing in insert + (not all tables has such marks */ + if (table->changed_in_insert) + { + table->changed_in_insert= FALSE; + query_cache_invalidate4(thd, table, FALSE, FALSE); + } + /* When closing a MERGE parent or child table, detach the children first. Clear child table references to force new assignment at next open. === modified file 'sql/sql_cache.cc' --- a/sql/sql_cache.cc 2009-04-25 10:05:32 +0000 +++ b/sql/sql_cache.cc 2009-06-11 12:45:53 +0000 @@ -1542,12 +1542,19 @@ err: } -/* +/** Remove all cached queries that uses any of the tables in the list + + @param thd Thread handler + @param tables_used List of tables used in this operation + @param using_transactions Not in autocommit mode + @param insert It is insert operation + */ void Query_cache::invalidate(THD *thd, TABLE_LIST *tables_used, - my_bool using_transactions) + my_bool using_transactions, + my_bool insert) { DBUG_ENTER("Query_cache::invalidate (table list)"); @@ -1567,6 +1574,15 @@ void Query_cache::invalidate(THD *thd, T force transaction finish. */ thd->add_changed_table(tables_used->table); + else if (insert && + (tables_used->table->file->table_cache_type() == + HA_CACHE_TBL_NTRNS_INS2LOCK)) + { + /* for other threads */ + tables_used->table->changed_in_insert= TRUE; + /* for this thread */ + invalidate_table(thd, tables_used); + } else invalidate_table(thd, tables_used); } @@ -1619,12 +1635,18 @@ void Query_cache::invalidate_locked_for_ DBUG_VOID_RETURN; } -/* +/** Remove all cached queries that uses the given table + + @param thd Thread handler + @param table TABLE descriptor + @param using_transactions Not in autocommit mode + @param insert It is insert operation */ -void Query_cache::invalidate(THD *thd, TABLE *table, - my_bool using_transactions) +void Query_cache::invalidate(THD *thd, TABLE *table, + my_bool using_transactions, + my_bool insert) { DBUG_ENTER("Query_cache::invalidate (table)"); @@ -1633,13 +1655,22 @@ void Query_cache::invalidate(THD *thd, T if (using_transactions && (table->file->table_cache_type() == HA_CACHE_TBL_TRANSACT)) thd->add_changed_table(table); + else if (insert && + (table->file->table_cache_type() == + HA_CACHE_TBL_NTRNS_INS2LOCK)) + { + /* for other threads */ + table->changed_in_insert= TRUE; + /* for this thread */ + invalidate_table(thd, table); + } else invalidate_table(thd, table); - DBUG_VOID_RETURN; } + void Query_cache::invalidate(THD *thd, const char *key, uint32 key_length, my_bool using_transactions) { === modified file 'sql/sql_cache.h' --- a/sql/sql_cache.h 2008-07-24 13:41:55 +0000 +++ b/sql/sql_cache.h 2009-06-11 12:45:53 +0000 @@ -445,10 +445,11 @@ protected: /* Remove all queries that uses any of the listed following tables */ void invalidate(THD* thd, TABLE_LIST *tables_used, - my_bool using_transactions); + my_bool using_transactions, my_bool insert); void invalidate(CHANGED_TABLE_LIST *tables_used); void invalidate_locked_for_write(TABLE_LIST *tables_used); - void invalidate(THD* thd, TABLE *table, my_bool using_transactions); + void invalidate(THD* thd, TABLE *table, + my_bool using_transactions, my_bool insert); void invalidate(THD *thd, const char *key, uint32 key_length, my_bool using_transactions); === modified file 'sql/sql_delete.cc' --- a/sql/sql_delete.cc 2009-04-25 09:04:38 +0000 +++ b/sql/sql_delete.cc 2009-06-11 12:45:53 +0000 @@ -370,7 +370,7 @@ cleanup: */ if (deleted) { - query_cache_invalidate3(thd, table_list, 1); + query_cache_invalidate4(thd, table_list, TRUE, FALSE); } delete select; @@ -783,7 +783,7 @@ void multi_delete::abort() /* Something already deleted so we have to invalidate cache */ if (deleted) - query_cache_invalidate3(thd, delete_tables, 1); + query_cache_invalidate4(thd, delete_tables, TRUE, FALSE); /* If rows from the first table only has been deleted and it is @@ -933,7 +933,7 @@ bool multi_delete::send_eof() */ if (deleted) { - query_cache_invalidate3(thd, delete_tables, 1); + query_cache_invalidate4(thd, delete_tables, TRUE, FALSE); } if ((local_error == 0) || thd->transaction.stmt.modified_non_trans_table) { @@ -1074,7 +1074,7 @@ bool mysql_truncate(THD *thd, TABLE_LIST error= ha_create_table(thd, path, table_list->db, table_list->table_name, &create_info, 1); VOID(pthread_mutex_unlock(&LOCK_open)); - query_cache_invalidate3(thd, table_list, 0); + query_cache_invalidate4(thd, table_list, FALSE, FALSE); end: if (!dont_send_ok) === modified file 'sql/sql_insert.cc' --- a/sql/sql_insert.cc 2009-04-25 10:05:32 +0000 +++ b/sql/sql_insert.cc 2009-06-11 12:45:53 +0000 @@ -880,7 +880,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t For the transactional algorithm to work the invalidation must be before binlog writing and ha_autocommit_or_rollback */ - query_cache_invalidate3(thd, table_list, 1); + query_cache_invalidate4(thd, table_list, TRUE, TRUE); } if ((changed && error <= 0) || thd->transaction.stmt.modified_non_trans_table || @@ -2743,7 +2743,7 @@ bool Delayed_insert::handle_inserts(void DBUG_PRINT("error", ("HA_EXTRA_NO_CACHE failed in loop")); goto err; } - query_cache_invalidate3(&thd, table, 1); + query_cache_invalidate4(&thd, table, TRUE, TRUE); if (thr_reschedule_write_lock(*thd.lock->locks)) { /* This is not known to happen. */ @@ -2785,7 +2785,7 @@ bool Delayed_insert::handle_inserts(void DBUG_PRINT("error", ("HA_EXTRA_NO_CACHE failed after loop")); goto err; } - query_cache_invalidate3(&thd, table, 1); + query_cache_invalidate4(&thd, table, TRUE, TRUE); pthread_mutex_lock(&mutex); DBUG_RETURN(0); @@ -3208,7 +3208,7 @@ bool select_insert::send_eof() We must invalidate the table in the query cache before binlog writing and ha_autocommit_or_rollback. */ - query_cache_invalidate3(thd, table, 1); + query_cache_invalidate4(thd, table, TRUE, TRUE); if (thd->transaction.stmt.modified_non_trans_table) thd->transaction.all.modified_non_trans_table= TRUE; } @@ -3299,7 +3299,7 @@ void select_insert::abort() { if (!thd->current_stmt_binlog_row_based && !can_rollback_data()) thd->transaction.all.modified_non_trans_table= TRUE; if (changed) - query_cache_invalidate3(thd, table, 1); + query_cache_invalidate4(thd, table, TRUE, TRUE); } DBUG_ASSERT(transactional_table || !changed || thd->transaction.stmt.modified_non_trans_table); === modified file 'sql/sql_load.cc' --- a/sql/sql_load.cc 2009-05-19 09:28:05 +0000 +++ b/sql/sql_load.cc 2009-06-11 12:45:53 +0000 @@ -446,7 +446,7 @@ int mysql_load(THD *thd,sql_exchange *ex We must invalidate the table in query cache before binlog writing and ha_autocommit_... */ - query_cache_invalidate3(thd, table_list, 0); + query_cache_invalidate4(thd, table_list, FALSE, FALSE); if (error) { if (read_file_from_client) === modified file 'sql/sql_parse.cc' --- a/sql/sql_parse.cc 2009-04-25 10:05:32 +0000 +++ b/sql/sql_parse.cc 2009-06-11 12:45:53 +0000 @@ -3211,7 +3211,7 @@ end_with_restore_list: /* INSERT ... SELECT should invalidate only the very first table */ TABLE_LIST *save_table= first_table->next_local; first_table->next_local= 0; - query_cache_invalidate3(thd, first_table, 1); + query_cache_invalidate4(thd, first_table, TRUE, FALSE); first_table->next_local= save_table; } delete sel_result; === modified file 'sql/sql_partition.cc' --- a/sql/sql_partition.cc 2009-02-15 10:58:34 +0000 +++ b/sql/sql_partition.cc 2009-06-11 12:45:53 +0000 @@ -4012,7 +4012,7 @@ static int fast_end_partition(THD *thd, thd->proc_info="end"; if (!is_empty) - query_cache_invalidate3(thd, table_list, 0); + query_cache_invalidate4(thd, table_list, FALSE, FALSE); error= ha_autocommit_or_rollback(thd, 0); if (end_active_trans(thd)) === modified file 'sql/sql_rename.cc' --- a/sql/sql_rename.cc 2008-02-19 12:45:21 +0000 +++ b/sql/sql_rename.cc 2009-06-11 12:45:53 +0000 @@ -182,7 +182,7 @@ bool mysql_rename_tables(THD *thd, TABLE } if (!error) - query_cache_invalidate3(thd, table_list, 0); + query_cache_invalidate4(thd, table_list, FALSE, FALSE); pthread_mutex_lock(&LOCK_open); unlock_table_names(thd, table_list, (TABLE_LIST*) 0); === modified file 'sql/sql_table.cc' --- a/sql/sql_table.cc 2009-06-02 09:58:27 +0000 +++ b/sql/sql_table.cc 2009-06-11 12:45:53 +0000 @@ -1775,7 +1775,7 @@ int mysql_rm_table_part2(THD *thd, TABLE if (some_tables_deleted || tmp_table_deleted || !error) { - query_cache_invalidate3(thd, tables, 0); + query_cache_invalidate4(thd, tables, FALSE, FALSE); if (!dont_log_query) { if (!thd->current_stmt_binlog_row_based || @@ -4372,7 +4372,7 @@ static bool mysql_admin_table(THD* thd, if (thd->killed) goto err; /* Flush entries in the query cache involving this table. */ - query_cache_invalidate3(thd, table->table, 0); + query_cache_invalidate4(thd, table->table, FALSE, FALSE); open_for_modify= 0; } @@ -4628,7 +4628,7 @@ send_result_message: pthread_mutex_unlock(&LOCK_open); } /* May be something modified consequently we have to invalidate cache */ - query_cache_invalidate3(thd, table->table, 0); + query_cache_invalidate4(thd, table->table, FALSE, FALSE); } } ha_autocommit_or_rollback(thd, 0); @@ -5163,7 +5163,7 @@ mysql_discard_or_import_tablespace(THD * The 0 in the call below means 'not in a transaction', which means immediate invalidation; that is probably what we wish here */ - query_cache_invalidate3(thd, table_list, 0); + query_cache_invalidate4(thd, table_list, FALSE, FALSE); /* The ALTER TABLE is always in its own transaction */ error = ha_autocommit_or_rollback(thd, 0); @@ -6433,7 +6433,7 @@ view_err: unlink_open_table(thd, name_lock, FALSE); VOID(pthread_mutex_unlock(&LOCK_open)); table_list->table= NULL; // For query cache - query_cache_invalidate3(thd, table_list, 0); + query_cache_invalidate4(thd, table_list, FALSE, FALSE); DBUG_RETURN(error); } @@ -7097,7 +7097,7 @@ view_err: ha_flush_logs(old_db_type); } table_list->table=0; // For query cache - query_cache_invalidate3(thd, table_list, 0); + query_cache_invalidate4(thd, table_list, FALSE, FALSE); if (thd->locked_tables && (new_name != table_name || new_db != db)) { === modified file 'sql/sql_update.cc' --- a/sql/sql_update.cc 2009-04-25 09:04:38 +0000 +++ b/sql/sql_update.cc 2009-06-11 12:45:53 +0000 @@ -779,7 +779,7 @@ int mysql_update(THD *thd, */ if (updated) { - query_cache_invalidate3(thd, table_list, 1); + query_cache_invalidate4(thd, table_list, TRUE, FALSE); } /* @@ -1780,7 +1780,7 @@ void multi_update::abort() /* Something already updated so we have to invalidate cache */ if (updated) - query_cache_invalidate3(thd, update_tables, 1); + query_cache_invalidate4(thd, update_tables, TRUE, FALSE); /* If all tables that has been updated are trans safe then just do rollback. If not attempt to do remaining updates. @@ -2023,7 +2023,7 @@ bool multi_update::send_eof() if (updated) { - query_cache_invalidate3(thd, update_tables, 1); + query_cache_invalidate4(thd, update_tables, TRUE, FALSE); } /* Write the SQL statement to the binlog if we updated === modified file 'sql/sql_view.cc' --- a/sql/sql_view.cc 2009-04-25 10:05:32 +0000 +++ b/sql/sql_view.cc 2009-06-11 12:45:53 +0000 @@ -667,7 +667,7 @@ bool mysql_create_view(THD *thd, TABLE_L VOID(pthread_mutex_unlock(&LOCK_open)); if (mode != VIEW_CREATE_NEW) - query_cache_invalidate3(thd, view, 0); + query_cache_invalidate4(thd, view, FALSE, FALSE); start_waiting_global_read_lock(thd); if (res) goto err; @@ -1631,7 +1631,7 @@ bool mysql_drop_view(THD *thd, TABLE_LIS pthread_mutex_unlock(&share->mutex); release_table_share(share, RELEASE_WAIT_FOR_DROP); } - query_cache_invalidate3(thd, view, 0); + query_cache_invalidate4(thd, view, FALSE, FALSE); sp_cache_invalidate(); } @@ -1983,7 +1983,7 @@ mysql_rename_view(THD *thd, DBUG_RETURN(1); /* remove cache entries */ - query_cache_invalidate3(thd, view, 0); + query_cache_invalidate4(thd, view, FALSE, FALSE); sp_cache_invalidate(); error= FALSE; === modified file 'sql/table.h' --- a/sql/table.h 2009-02-19 09:01:25 +0000 +++ b/sql/table.h 2009-06-11 12:45:53 +0000 @@ -790,6 +790,7 @@ struct st_table { my_bool get_fields_in_item_tree; /* Signal to fix_field */ /* If MERGE children attached to parent. See top comment in ha_myisammrg.cc */ my_bool children_attached; + my_bool changed_in_insert; /* Have been changed in insert since last lock */ REGINFO reginfo; /* field connections */ MEM_ROOT mem_root; === modified file 'storage/maria/ha_maria.h' --- a/storage/maria/ha_maria.h 2008-12-02 22:02:52 +0000 +++ b/storage/maria/ha_maria.h 2009-06-11 12:45:53 +0000 @@ -164,4 +164,6 @@ public: return file; } static int implicit_commit(THD *thd, bool new_trn); + /** Type of table for caching query */ + virtual uint8 table_cache_type() { return HA_CACHE_TBL_NTRNS_INS2LOCK; } }; === modified file 'storage/myisam/ha_myisam.h' --- a/storage/myisam/ha_myisam.h 2008-06-28 12:45:15 +0000 +++ b/storage/myisam/ha_myisam.h 2009-06-11 12:45:53 +0000 @@ -147,4 +147,6 @@ class ha_myisam: public handler { return file; } + /** Type of table for caching query */ + virtual uint8 table_cache_type() { return HA_CACHE_TBL_NTRNS_INS2LOCK; } };