List:MySQL ODBC« Previous MessageNext Message »
From:Michael Widenius Date:October 10 1999 11:47am
Subject:Re: Access97 & #deleted#
View as plain text  
>>>>> "Marek" == Marek Krzyszton <marek.krzyszton@stripped>
> writes:

Marek> "jan branbergen" <j.branberge-@stripped> wrote: 
Marek> original article:
>> Hello to y'all,
>> Situation: in a MySQL database (3.22.25 on Linux) i have defined tables with
>> an auto-increment-integer as primary key. I link to these tables in Access
>> 97 SR2. Ik have set the appropiate ODBC-settings for MyODBC (2.50.25 on
>> Win98SE) ('return matching rows'==2). The tables contain a timestamp-field
>> and no float/double-fields.
>> Problem: If I enter data into these tables using Access and I press F9
>> (refresh),  #deleted# values appear in all fields of the just entered
>> records. If I Close/Open tableSluit/open the table, the records reappear
>> (the have never been #deleted#),


Marek> Now I'm using mysql 3.21.30, myodbc 2.50.25, access 2.0 & 97. Of course I
> need
Marek> primary key (any kind) in order to not to see a "record locked by another
Marek> user..." and if I use integer auto_increment for it, I need also another column
Marek> with a unique value for every record. The best is datetime with NOW() (only in
Marek> access form of course). If I don't do that and insert a value in any field
> which
Marek> is present in any previous record in the same field, #deleted# appears. I found
Marek> in an 'Access ODBC connectivity' explanation, that when you insert a new
> record,
Marek> Access does a 'SELECT' based on the just inserted values and if they are not
Marek> unique, you will see #deleted#. If you reopen the table, you will see proper
Marek> values of course. This is how it works with mysql 3.21.30, when I've tried
> mysql
Marek> 3.22.x and later 3.23.x the problem you have desribed occured.

Marek> I have checked all newsgroups, WWW pages and still haven't found complete
Marek> solution. Maybe someone else did?


Thanks for the logs!

Anyway, the real problem is that, even if one can with ODBC can use
sql servers with auto_increment_values, there isn't any established
protocol to find out the ID for the new row.  (Wonder who designed
this protocol :).

Access uses the following query to find out the new inserted row:

SELECT .... FROM table  WHERE auto_increment_column IS NULL

MySQL 3.23 tries to handle the above by converting the above query to:

SELECT .... FROM table  WHERE auto_increment_column= LAST_INSERT_ID().

This solves some problems, but introduces another:

The problem is that, according to the logs, Access does the above
query 7 times for each inserted row ???

I will in next MySQL 3.23 release add an option to disable this

I also added the following patch that may help solve this problem by
setting LAST_INSERT_ID() to NULL after the first SELECT

Marek, any change you can check if the following patch works for you?
(Sorry, but I don't have a copy of Access to test this :( )

*** /my/monty/master/mysql-3.23.4-alpha/sql/	Fri Sep 24 03:53:42 1999
--- ./	Sun Oct 10 14:34:53 1999
*** 2604,2610 ****
      if (args[0]->type() == Item::FIELD_ITEM)
        Field *field=((Item_field*) args[0])->field;
!       if (field->flags & AUTO_INCREMENT_FLAG &&
  	COND *new_cond;
  	if ((new_cond= new Item_func_eq(args[0],
--- 2604,2611 ----
      if (args[0]->type() == Item::FIELD_ITEM)
        Field *field=((Item_field*) args[0])->field;
!       if (field->flags & AUTO_INCREMENT_FLAG &&
!field->table->maybe_null &&
! 	  current_thd->insert_id())
  	COND *new_cond;
  	if ((new_cond= new Item_func_eq(args[0],
*** 2615,2620 ****
--- 2616,2622 ----
+ 	current_thd->insert_id(0);		// Clear for next request
        /* fix to replace 'NULL' dates with '0' (shreeve@stripped) */
        else if (((field->type() == FIELD_TYPE_DATE) ||
*** 2652,2660 ****
! **	Create a temp table according to a field list
! **	set distinct if duplicates could be removed
! **	given fields field pointers are changed to point at tmp_table
  **	for send_fields
--- 2654,2662 ----
! **	Create a temp table according to a field list.
! **	Set distinct if duplicates could be removed
! **	Given fields field pointers are changed to point at tmp_table
  **	for send_fields
*** 2712,2717 ****
--- 2714,2721 ----
        if (modify_item)
  	((Item_field*) item)->result_field= new_field;
+       else
+ 	new_field->field_name=item->name;
        if (org_field->maybe_null())
  	new_field->flags&= ~NOT_NULL_FLAG;	// Because of outer join

Re: Access97 & #deleted#Unknown Sender7 Oct
  • Re: Access97 & #deleted#Andreas Lund7 Oct
    • Re: Access97 & #deleted#Unknown Sender7 Oct
  • Re: Access97 & #deleted#Michael Widenius10 Oct
  • Access2000 & #deleted#Steven Schoch18 Oct
    • Access2000 & #deleted#Michael Widenius19 Oct
Re: Access97 & #deleted#ef7 Oct
RE: Access2000 & #deleted#Gerald R. Jensen19 Oct