MySQL Bugs: #14222: Can't modify a record by editing a single field but leaving the old value

Bug #14222	Can't modify a record by editing a single field but leaving the old value
Submitted:	21 Oct 2005 23:16	Modified:	7 Jul 2006 10:14
Reporter:	Carlos Ochoa R.	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (windows any)
Assigned to:		CPU Architecture:	Any

Description:
Español:
En MSAccess (97 ó 2003), cuando se abre una tabla vinculada y se "modifica" un campo colocándole el mismo valor que tenía anteriormente, aparece el mensaje de error:

"Conflicto de Escritura
Otro usuario modificó este registro después de que usted comenzó a modificarlo. si lo guarda, sobreescribirá los cambios que hizo el otro usuario...", pero la opción de guardar registro permanece desactivada.

Esto sucede únicamente en Access, ya que al hacer el "cambio" con cualquier otro programa no presenta ningún error.

Tengo MySQL 5.0.13rc, ODBC Connector 3.51.12, Access 97/2003.

English:
In MS-Access (97 or 2003) whenever you open a table in data view and try to "modify" a record by changing any value to the same one (say change a '0' by another '0') and then save the record either by moving to the next one or by closing the table view, you get an error message that in the english version should be something like:

"Writing conflict.
Another user changed this record after you start to modify it, if you save it will overwrite the other user's changes ..." but the button save changes remains disallowed.

It only happens within MSAccess. If you make the same "changes" in another program no error message is displayed.

I've installed MySQL 5.0.13rc, ODBC Connector 3.51.12, Access 97/2003.

How to repeat:
Spanish:
Abra MSAccess, Vincule cualquier tabla de cualquier Base de Datos, abrala para ver sus datos y modifique cualquier campo colocándole el mismo valor que tenía anteriormente.

English:
Open any database in MSAccess, link any table from your database, then open it in data view and modify a record by assigning to any field the same value it has and save the record by moving to the next record.

Suggested fix:
El sistema debería ver si realmente hay cambios o no y proceder en concordancia.

The system should see if there's really a change and actuate in concordance.

Sorry, I thing the correct sintax is "Can't modify a record by editing a single field but leaving the old value"

I thought it wasn't serious, but whenever there are more than 10 clients it becomes boring to handle.

I opened several MSAccess and tried to edit records. I was not able to reproduce the bug. Could you please write here table definition ?

Hi, Vasily.

this is a sample table:

CREATE TABLE `conexiones` (
  `Idconexion` int(10) NOT NULL default '0',
  `UsaDSN` char(1) default NULL,
  `DSNName` varchar(30) default NULL,
  `DriverName` varchar(30) default NULL,
  `SourceDBName` varchar(30) default NULL,
  `Password` varchar(30) default NULL,
  `DSNDescription` varchar(50) default NULL,
  `IdServicio` varchar(10) default NULL,
  `IdTercero` varchar(15) default NULL,
  `Servidor` varchar(30) default NULL,
  `Usuario` varchar(15) default NULL,
  PRIMARY KEY  (`Idconexion`),
  UNIQUE KEY `Idconexion` (`Idconexion`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `conexiones` */

insert into `conexiones` values (0,'','Antares','MySQL ODBC 3.51 Driver','Antares','LJMJfke','','','','servidor','root'),(1,'','Contabilidad','MySQL ODBC 3.51 Driver','Contabilidad','LJMJfke','','','','servidor','root'),(2,'','Administrativo','MySQL ODBC 3.51 Driver','Administrativo','LJMJfke','','','','servidor','root'),(3,'','DatosIPS','MySQL ODBC 3.51 Driver','DatosIPS','LJMJfke','','','','servidor','root'),(5,'','Laboratorio','','','','','','','','');

Create it, attach to an Access database then, in edit view, change the 's' in 'antares' by another 's' and go to the next row.

Whenever I do this I get the error message.

Thanks.

p.s. This is a very nice and powerful DBMS :-).

Thanks for advise. I was able to reproduce the bug. I tested on myodbc 3.51.12 and mysql 5.0.16.

So what?

This is a duplicate of http://bugs.mysql.com/bug.php?id=13540.
Carlos, as a workaround, drop duplicated UNIQUE constraint since you allready have it in PK.
  PRIMARY KEY  (`Idconexion`),
  UNIQUE KEY `Idconexion` (`Idconexion`) << drop it