Petter Larsson writes:
> Hi,
>
> I recantly upgraded from 3.22.32 to 3.23.35 using an rpm from
> www.mysql.com.
> My platform: Redhat Linux 2.2.14 i686
>
> The problem is now that UPDATE do not always succed. I get a query OK
> but no
> update is done unless the same query is issued once again.
>
> This problem is not constant but rather comes and go.
>
> Case 1: Failure
>
> mysql> select * from test;
> +----+-------+
> | id | value |
> +----+-------+
> | 1 | xx |
> +----+-------+
> 1 row in set (0.00 sec)
>
> mysql> update test set value='yy' where id=1;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> select * from test;
> +----+-------+
> | id | value |
> +----+-------+
> | 1 | xx |
> +----+-------+
> 1 row in set (0.00 sec)
>
>
> Case 2: Failure first time, but second time the update is done
>
> mysql> select * from test;
> +----+-------+
> | id | value |
> +----+-------+
> | 1 | xx |
> +----+-------+
> 1 row in set (0.00 sec)
>
> mysql> update test set value='yy' where id=1;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> update test set value='yy' where id=1;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from test;
> +----+-------+
> | id | value |
> +----+-------+
> | 1 | yy |
> +----+-------+
> 1 row in set (0.00 sec)
>
> Does anyone understand this ? becouse I am lost !
>
> regards
> Petter
>
Hi!
3.23.36 will be soon out with the above problem fixed.
If you can't wait and if you can build from sources, here is a patch:
===== sql/sql_load.cc 1.22 vs edited =====
*** /tmp/sql_load.cc-1.22-24964 Sun Dec 24 15:18:59 2000
--- edited/sql/sql_load.cc Wed Mar 21 19:38:21 2001
***************
*** 91,97 ****
else
{ // Part field list
thd->dupp_field=0;
! if (setup_fields(thd,table_list,fields,1,0) < 0)
DBUG_RETURN(-1);
if (thd->dupp_field)
{
--- 91,97 ----
else
{ // Part field list
thd->dupp_field=0;
! if (setup_tables(table_list) || setup_fields(thd,table_list,fields,1,0))
DBUG_RETURN(-1);
if (thd->dupp_field)
{
===== sql/sql_insert.cc 1.34 vs edited =====
*** /tmp/sql_insert.cc-1.34-24964 Tue Mar 13 11:44:01 2001
--- edited/sql/sql_insert.cc Wed Mar 21 19:38:21 2001
***************
*** 78,84 ****
table_list.grant=table->grant;
thd->dupp_field=0;
! if (setup_fields(thd,&table_list,fields,1,0))
return -1;
if (thd->dupp_field)
{
--- 78,84 ----
table_list.grant=table->grant;
thd->dupp_field=0;
! if (setup_tables(&table_list) || setup_fields(thd,&table_list,fields,1,0))
return -1;
if (thd->dupp_field)
{
***************
*** 151,157 ****
save_time_stamp=table->time_stamp;
values= its++;
if (check_insert_fields(thd,table,fields,*values,1) ||
! setup_fields(thd,table_list,*values,0,0))
{
table->time_stamp=save_time_stamp;
goto abort;
--- 151,157 ----
save_time_stamp=table->time_stamp;
values= its++;
if (check_insert_fields(thd,table,fields,*values,1) ||
! setup_tables(table_list) || setup_fields(thd,table_list,*values,0,0))
{
table->time_stamp=save_time_stamp;
goto abort;
***************
*** 168,174 ****
table->time_stamp=save_time_stamp;
goto abort;
}
! if (setup_fields(thd,table_list,*values,0,0))
{
table->time_stamp=save_time_stamp;
goto abort;
--- 168,174 ----
table->time_stamp=save_time_stamp;
goto abort;
}
! if (setup_tables(table_list) || setup_fields(thd,table_list,*values,0,0))
{
table->time_stamp=save_time_stamp;
goto abort;
===== sql/sql_base.cc 1.67 vs edited =====
*** /tmp/sql_base.cc-1.67-24964 Sat Mar 3 03:03:12 2001
--- edited/sql/sql_base.cc Wed Mar 21 19:34:56 2001
***************
*** 39,45 ****
List_iterator<Item> *it);
static void free_cache_entry(TABLE *entry);
static void mysql_rm_tmp_tables(void);
! static key_map get_key_map_from_key_list(THD *thd, TABLE *table,
List<String> *index_list);
--- 39,45 ----
List_iterator<Item> *it);
static void free_cache_entry(TABLE *entry);
static void mysql_rm_tmp_tables(void);
! static key_map get_key_map_from_key_list(TABLE *table,
List<String> *index_list);
***************
*** 1711,1721 ****
return found;
}
-
/****************************************************************************
** Check that all given fields exists and fill struct with current data
- ** Check also that the 'used keys' and 'ignored keys' exists and set up the
- ** table structure accordingly
****************************************************************************/
int setup_fields(THD *thd, TABLE_LIST *tables, List<Item> &fields,
--- 1711,1718 ----
***************
*** 1729,1735 ****
thd->allow_sum_func= test(sum_func_list);
thd->where="field list";
! /* Remap table numbers if INSERT ... SELECT */
uint tablenr=0;
for (TABLE_LIST *table=tables ; table ; table=table->next,tablenr++)
{
--- 1726,1761 ----
thd->allow_sum_func= test(sum_func_list);
thd->where="field list";
! while ((item=it++))
! {
! if (item->type() == Item::FIELD_ITEM &&
! ((Item_field*) item)->field_name[0] == '*')
! {
! if (insert_fields(thd,tables,((Item_field*) item)->table_name,&it))
! DBUG_RETURN(-1); /* purecov: inspected */
! }
! else
! {
! if (item->fix_fields(thd,tables))
! DBUG_RETURN(-1); /* purecov: inspected */
! if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM)
! item->split_sum_func(*sum_func_list);
! thd->used_tables|=item->used_tables();
! }
! }
! DBUG_RETURN(test(thd->fatal_error));
! }
!
!
! /*
! Remap table numbers if INSERT ... SELECT
! Check also that the 'used keys' and 'ignored keys' exists and set up the
! table structure accordingly
! */
!
! bool setup_tables(TABLE_LIST *tables)
! {
! DBUG_ENTER("setup_tables");
uint tablenr=0;
for (TABLE_LIST *table=tables ; table ; table=table->next,tablenr++)
{
***************
*** 1739,1786 ****
table->table->maybe_null=1; // LEFT OUTER JOIN ...
if (table->use_index)
{
! key_map map= get_key_map_from_key_list(thd,table->table,
table->use_index);
if (map == ~(key_map) 0)
! DBUG_RETURN(-1);
table->table->keys_in_use_for_query=map;
}
if (table->ignore_index)
{
! key_map map= get_key_map_from_key_list(thd,table->table,
table->ignore_index);
if (map == ~(key_map) 0)
! DBUG_RETURN(-1);
table->table->keys_in_use_for_query &= ~map;
}
}
if (tablenr > MAX_TABLES)
{
my_error(ER_TOO_MANY_TABLES,MYF(0),MAX_TABLES);
! DBUG_RETURN(-1);
! }
! while ((item=it++))
! {
! if (item->type() == Item::FIELD_ITEM &&
! ((Item_field*) item)->field_name[0] == '*')
! {
! if (insert_fields(thd,tables,((Item_field*) item)->table_name,&it))
! DBUG_RETURN(-1); /* purecov: inspected */
! }
! else
! {
! if (item->fix_fields(thd,tables))
! DBUG_RETURN(-1); /* purecov: inspected */
! if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM)
! item->split_sum_func(*sum_func_list);
! thd->used_tables|=item->used_tables();
! }
}
! DBUG_RETURN(test(thd->fatal_error));
}
! static key_map get_key_map_from_key_list(THD *thd, TABLE *table,
List<String> *index_list)
{
key_map map=0;
--- 1765,1795 ----
table->table->maybe_null=1; // LEFT OUTER JOIN ...
if (table->use_index)
{
! key_map map= get_key_map_from_key_list(table->table,
table->use_index);
if (map == ~(key_map) 0)
! DBUG_RETURN(1);
table->table->keys_in_use_for_query=map;
}
if (table->ignore_index)
{
! key_map map= get_key_map_from_key_list(table->table,
table->ignore_index);
if (map == ~(key_map) 0)
! DBUG_RETURN(1);
table->table->keys_in_use_for_query &= ~map;
}
}
if (tablenr > MAX_TABLES)
{
my_error(ER_TOO_MANY_TABLES,MYF(0),MAX_TABLES);
! DBUG_RETURN(1);
}
! DBUG_RETURN(0);
}
! static key_map get_key_map_from_key_list(TABLE *table,
List<String> *index_list)
{
key_map map=0;
===== sql/sql_update.cc 1.29 vs edited =====
*** /tmp/sql_update.cc-1.29-24964 Wed Mar 21 00:58:38 2001
--- edited/sql/sql_update.cc Wed Mar 21 19:34:57 2001
***************
*** 70,76 ****
table->quick_keys=0;
want_privilege=table->grant.want_privilege;
table->grant.want_privilege=(SELECT_ACL & ~table->grant.privilege);
! if (setup_conds(thd,table_list,&conds))
DBUG_RETURN(-1); /* purecov: inspected */
old_used_keys=table->used_keys; // Keys used in WHERE
--- 70,76 ----
table->quick_keys=0;
want_privilege=table->grant.want_privilege;
table->grant.want_privilege=(SELECT_ACL & ~table->grant.privilege);
! if (setup_tables(table_list) || setup_conds(thd,table_list,&conds))
DBUG_RETURN(-1); /* purecov: inspected */
old_used_keys=table->used_keys; // Keys used in WHERE
===== sql/sql_select.cc 1.88 vs edited =====
*** /tmp/sql_select.cc-1.88-24964 Wed Mar 7 23:50:43 2001
--- edited/sql/sql_select.cc Wed Mar 21 19:43:53 2001
***************
*** 174,180 ****
thd->proc_info="init";
thd->used_tables=0; // Updated by setup_fields
! if (setup_fields(thd,tables,fields,1,&all_fields) ||
setup_conds(thd,tables,&conds) ||
setup_order(thd,tables,fields,all_fields,order) ||
setup_group(thd,tables,fields,all_fields,group,&hidden_group_fields) ||
--- 174,181 ----
thd->proc_info="init";
thd->used_tables=0; // Updated by setup_fields
! if (setup_tables(tables) ||
! setup_fields(thd,tables,fields,1,&all_fields) ||
setup_conds(thd,tables,&conds) ||
setup_order(thd,tables,fields,all_fields,order) ||
setup_group(thd,tables,fields,all_fields,group,&hidden_group_fields) ||
===== sql/mysql_priv.h 1.89 vs edited =====
*** /tmp/mysql_priv.h-1.89-24964 Wed Mar 21 00:58:37 2001
--- edited/sql/mysql_priv.h Wed Mar 21 19:45:20 2001
***************
*** 402,407 ****
--- 402,408 ----
SQL_SELECT *make_select(TABLE *head, table_map const_tables,
table_map read_tables, COND *conds, int *error);
Item ** find_item_in_list(Item *item,List<Item> &items);
+ bool setup_tables(TABLE_LIST *tables);
int setup_fields(THD *thd,TABLE_LIST *tables,List<Item> &item,
bool set_query_id,List<Item> *sum_func_list);
int setup_conds(THD *thd,TABLE_LIST *tables,COND **conds);
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php