MySQL Bugs: #68243: Microsoft Access Crashes when Bit field updates

Bug #68243	Microsoft Access Crashes when Bit field updates
Submitted:	1 Feb 2013 10:33	Modified:	11 Mar 2013 17:25
Reporter:	Sue J	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.2.3	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
Open a Microsoft Access database and link to a MySQL table which contains a Bit field.

Open the table as a datasheet (no need to use ADO or DAO) and overtype the value with a zero.

Access crashes without warning.

How to repeat:
As above

Thank you for the bug report. Which version of Access are you using?. Thanks.

I am using Access 2003 - but also have 2007, 2010 and the latest version installed on other machines nearby.
I will update this post with information on whether we get the problems on those versions too.
I had been hoping to roll out 5.2.3 to all our clients, but are stopped until this is resolved.

I can confirm that the problem is also there with Access 2010 and Access 2013.

Thank you for the feedback. With Access 2010 I got an error message: "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". I am using 64-bits version. That happens with you too?. Thanks.

Sorry - no - not seen that message so far.

My test environments all have 32-bit Access (we cannot run the 64 bit version as we have legacy code that uses Activex components).

The 3 machines I used to replicate the problem have
Win 7 (64 bit) with Access 2003
Win 7 (32 bit) with Access 2010
Win 8 (64 bit) with Access 2013

The ODBC connector is the 32 bit version (Unicode).

We use a DSN connection when making the links.

This is the DSN we use (have blanked the ids and passwords)

[ODBC]
driver={MySQL ODBC 5.2w Driver}
UID = xxx
Pwd = ************
OPTION=35
Database = xxxxxxxx
SERVER = xxxxxxxxxxxxx.com
PORT = 3307
NO_SSPS = 1

I have just experimented a little further.

If I make the link to the table 'by hand' and let Access create a File DSN
it looks different from the one we create today - and it doesn't crash Access if I edit the bit field.

[ODBC]
DRIVER=MySQL ODBC 5.2w Driver
UID=xxxxxxxx
DFLT_BIGINT_BIND_STR=1
COLUMN_SIZE_S32=1
PORT=3307
DATABASE=xxxxxxxx
SERVER=xxxxxxxxxxxx

With 'our' DSN, opening the table, the field shows up as True or False
With the 'Access generated' DSN the field shows up as 0 or -1

Note: Our DSN has worked without a problem until 5.2.2 was released.
We were then asked to put in the 
NO_SSPS = 1
statement

I have now found that if I take out the NO_SSPS = 1 then Access does not crash any more...

However - we have users out there today who are on 5.2.2 and if I remove
NO_SSPS = 1 from the DSN
it will fail for them

Conversely we get crashes with 5.2.3 if the statement stays in the DSN!!!

Unfortunately, I was not able to reproduce the crash when trying to edit values in a table with BIT(1) columns.

MS Access 2007 32-bit, MySQL ODBC Driver 5.2.2w/5.2.3w 32-bit.

The CREATE TABLE statement looks as:

CREATE TABLE `table1` (
`ID` int(11) DEFAULT NULL,
`vbit` bit(1) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

I find it strange why MS Access shows '-1' in the BIT(1) column that has the value '1' in MySQL database. Probably the problem is in the binary interpretation of BIT types in MySQL whilst MS Access treats them as NUMERIC types.

Access always shows -1 (and always has) rather than the 1 that is on the database. That is not an issue.

I have realised that there may be one more factor that is relevant for you to reproduce the problem - we make the link to a view, not the native table.

This is the Create Statement:
delimiter $$

CREATE TABLE `class` (
  `Club_Id` int(10) unsigned NOT NULL,
  `Class_Name` varchar(50) NOT NULL,
  `Instance` int(10) NOT NULL,
  `LOA` decimal(15,5) DEFAULT NULL,
  `Beam` decimal(15,5) DEFAULT NULL,
  `Description` varchar(50) DEFAULT NULL,
  `Fee_Rate_Id` int(10) DEFAULT NULL,
  `Publish` bit(1) NOT NULL DEFAULT b'1',
  PRIMARY KEY (`Club_Id`,`Class_Name`,`Instance`),
  UNIQUE KEY `Class` (`Club_Id`,`Class_Name`,`Instance`),
  CONSTRAINT `FK_class_1` FOREIGN KEY (`Club_Id`, `Class_Name`) REFERENCES `classes` (`Club_Id`, `Class_Name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

The Create for the View is:
delimiter $$

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_class` AS select `class`.`Club_Id` AS `Club Id`,`class`.`Class_Name` AS `Class Name`,`class`.`Instance` AS `Instance`,`class`.`LOA` AS `LOA`,`class`.`Beam` AS `Beam`,`class`.`Description` AS `Description`,`class`.`Fee_Rate_Id` AS `Fee Rate Id`,`class`.`Publish` AS `Publish` from `class` where (`class`.`Club_Id` = cast(user() as char charset utf8)) WITH CASCADED CHECK OPTION$$

Each user has a login that equals their Club Id (eg 25)

The Grant for the Class table, for user 25 is
GRANT SELECT, INSERT, UPDATE, DELETE ON `ourschema`.`v_class` TO '25'@'%'

The File_DSN is as follows (password, schema name and domain name have been sanitised):
[ODBC]
driver={MySQL ODBC 5.2w Driver}
UID = 25
Pwd = ourpassword
OPTION=35
Database = ourschema
SERVER = ourdomain.com
PORT = 3307
NO_SSPS = 1

I then link to v_class using the File_DSN, open the table in Access, change a -1 to a 0 and the crash happens

Thank you for the clarifications.
Crashing with the view is very essential.
I simplified the case to the minimum possible variant with only one INT column and one BIT(1) column.

Setting the status to VERIFIED.

Added to changelog for 5.2.5: 

Microsoft Access could experience a severe error when updating a BIT
column in view representing a MySQL table, for example through the
datasheet interface.