UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Mysql Backend Collection Of Tips And Notes.    
 
   
javert
post Jan 31 2018, 04:47 PM
Post#1



Posts: 67
Joined: 18-January 16



Hello everyone.

Considering that information on Access use with a MySQL backend is a little scarce and disperse, I decided to collect some information here based on my experience and from others, hoping people can put their two cents on here too.

If this transgredes forum policy, let me now.
Go to the top of the page
 
javert
post Jan 31 2018, 05:00 PM
Post#2



Posts: 67
Joined: 18-January 16



First the "This record has been changed by another user since you started editing it..." problem that has been eloquently exposed before:

QUOTE
This occurs in all bound Access forms (at least in our company) with SQL tables in the back.

Open a form
Klick in a field (e.g. a textbox)
Change something (e.g. remove the last letter of a name)
Add the letter you just removed - in other words - undo the change manually
And force Access to save the "change" e.g. by exiting the form or moving to another record.

The well known write conflict warning appears.
"This record has been changed by another user since you started editing it..."
Which is simply not true. It was changed by me alone and not by another user and I didn't even CHANGE anything. I started changing but before it was written to the backend I undid the change.

This can best be observed with checkboxes. Simply clicking on a checkbox TWICE will make this warning appear.

While this sounds rather harmless it get's worse in combination with this.
If you trigger a requery of the form or if you try to set the Dirty-property of the form to false then an error shows up claiming that the property (e.g. dirty) of the form is not available. This causes the whole frontend to crash if you didn' catch the error.

So the error shows up if you undid a change like I descibed above and then set Me.dirty = false or try to requery the form.
This combination happens quite often at least with our employees. Maybe they accidantially checked a check box or started writing something in a textbox and then changed their mind and deleted it again. The datatype doesn't really matter...

So here some background informations.
1. The table has a TIMESTAMP field (Default: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
2. The table contains neither bit nor floating point field types

This can be reproduced with a simple table.
Two columns (VarChar(20), TIMESTAMP)
But as I already said - this "works" with any table.


As suggested in the MySQL ODBC connector documentation, it is standard routine to check the "Return matching rows instead of affected rows" checkbox when configuring the ODBC connection. PostgreSQL ODBC FAQ shows more light into the problem and some other observations apply as well.

That said, the "another user..." message can still happen. As of MySQL 5.7, using the ON_UPDATE_CURRENT_TIMESTAMP option on datetime rows will send this error if you modify a record and revisit it to modify it again some seconds later, even at table level. I guess, the change puts Access out of sync with the modified record, so I had to disable that feature (Navicat sometimes add this property automatically when using it to import Access tables to MySQL).
Go to the top of the page
 
javert
post Jan 31 2018, 05:06 PM
Post#3



Posts: 67
Joined: 18-January 16



Since there's no boolean field type in MySQL, two options are available: set the field to TINYINT(1) or BIT(1) type.

Although tinyint can be handled, it has some problems: Access sets it to -1 on True and apparently some Web Development frameworks need to handle it as 1. The other problem is that Not(Field) returns 2 when the existing value is -1.

Other than working out the code to handle the tinyint type specifically (checkboxes are a pain, though), setting the field type to bit(1) solves both the -1 / 1 problem and the NOT operator problem, with a caveat: the field MUST be set to NOT NULL since Access can't handle triple state (Null boolean) fields and a newly created record with a null bit field won't be updatable.
Go to the top of the page
 
javert
post Feb 14 2018, 01:43 PM
Post#4



Posts: 67
Joined: 18-January 16



Sometimes an ODBC error happens and a message of "Date overflow" is displayed and a record fails to update.

A quick search led to the following explanation:

"For your information the fix approach was that in C or C++ it is possible to read or write DATE type using SQL_TIMESTAMP_STRUCT. This struct can hold both date and time. The error (Date overflow) was generated when with operations that are supposed to be DATE-only this struct got non-zero values for time. That is the canonical approach as ODBC API requires, however, it causes inconveniences sometimes when for instance the app did not bother to initialize the whole structure with 0 values because it knows it will only need the DATE part but the random values for TIME fraction could cause the errors despite of being truncated. A new option was introduced to continue with the query execution rather then return error. The server will ignore the TIME part and the result is the same as if there were zeroes."

As of February 2018, the version 5.3.10 of the MySQL ODBC driver includes a "Disable date overflow error". Marking this checkbox will stop this error from appearing and so far allows records to be updated.
Go to the top of the page
 
rabroersma
post Feb 19 2018, 03:34 PM
Post#5


UtterAccess VIP
Posts: 1,299
Joined: 1-January 07
From: Whittier, California, USA


Hello Javert,

These are good points that you've enumerated. You might consider adding these to the UtterAccess wiki - before this thread gets buried in history.

http://www.UtterAccess.com/wiki/Category:Table_of_Contents

--------------------
Regards, Richard
Postgresql 10 Version Released! - Newly added MS-Access friendly features in this release -> More Parallel Query Execution, Declaritive Table Partitioning, SQL Compliant Identity Columns
Go to the top of the page
 
Akudey
post Mar 11 2018, 05:50 PM
Post#6



Posts: 29
Joined: 17-August 13



Hi Javert,

Thanks for the update, I'm currently working with MySQL and Access as Back End.

I had an issue with Tinyint when I use the GetRows method of the AdoDB Recordset to read rows into array.

All the Tinyint fields in the array were empty, I try to use the CInt function on those array elements which had the Tinyint records, but with no success, I found a work around by simply changing all the Tinyint fields in the MySQL table to SMALLINT.

The problem occurs only when the Recordset is read into an Array.

I didn't face most of the update conflict You noted, because all my updates were accomplished through MySQL Store Procedures, and AdoDB Command objects

Please, consider publishing your post as an article in the Wiki forum

Using:
MySQL 5.7
ODBC driver version 5.3.4
This post has been edited by Akudey: Mar 11 2018, 06:03 PM
Go to the top of the page
 
javert
post Mar 12 2018, 12:02 PM
Post#7



Posts: 67
Joined: 18-January 16



QUOTE
I didn't face most of the update conflict You noted, because all my updates were accomplished through MySQL Store Procedures, and AdoDB Command objects


Dude, that's awesome. Requires a little more extra work but sure it has far fewer hassles on the daily use of the data.

Ok I'm going to fill a wiki for this, I lack knowledge regarding the nuts and bolts of the ODBC specification and engine so there's something I would want to have clarified in order to post the correct info and explanation:

Apparently, when Access is working with a table row, it scans the whole row and keeps a "snapshot" (not to confuse with the Snapshot recordset) of the row for updating purposes so, when it's going to update one or several fields of the row, it checks if the whole row keeps exactly as it was when scanned and then proceeds to do the updating. If someone or something else (a MySQL event, for example) made a change on some of those fields in the interim, Access see the changes and somehow is no longer sure about the row to update, snaps and sends "the data has been modified" or "another user..." message and stops the updating, even if the affected fields are not part of the primary key.

The good new is that using a RecordSource which only takes the relevant fields to work with effectively ignores changes that may be happening on nonselected fields but, is there a reason why it should be like it? Seems reasonable that, as long as the fields of the primary key are unchanged, Access shouldn't care or at worst it should throw a warning and proceed (this is the standard behavior of MySQL itself). Is it something that happens when working with every ODBC data source or only MySQL's? Is it related to the "Return matching rows" option?
Go to the top of the page
 
Akudey
post Mar 12 2018, 07:24 PM
Post#8



Posts: 29
Joined: 17-August 13



QUOTE
Dude, that's awesome. Requires a little more extra work but sure it has far fewer hassles on the daily use of the data

Yes you are right, it requires much more efforts.

QUOTE
Apparently, when Access is working with a table row, it scans the whole row and keeps a "snapshot" (not to confuse with the Snapshot recordset) of the row for updating purposes so, when it's going to update one or several fields of the row, it checks if the whole row keeps exactly as it was when scanned and then proceeds to do the updating. If someone or something else (a MySQL event, for example) made a change on some of those fields in the interim, Access see the changes and somehow is no longer sure about the row to update, snaps and sends "the data has been modified" or "another user..." message and stops the updating, even if the affected fields are not part of the primary key.

This is obviously above my expertise, but if some of the MVPs, could jump in and enlighten us on how Access handles updates over ODBC connections with other RDBMS such us MS SQL Server, I think it will point to some directions, especially, with regards to how access create a snapshot of the whole table and it rows.

QUOTE
Is it something that happens when working with every ODBC data source or only MySQL's? Is it related to the "Return matching rows" option?


see this link MySQL Connector/ODBC Developer Guide / ... / Microsoft Access

Please, have you consider using ADO or DAO transactions to perform any update or batch updates? maybe, it might solve all the hassles with the update problems, just that, I never tried it before.
Go to the top of the page
 
javert
post Mar 13 2018, 12:15 PM
Post#9



Posts: 67
Joined: 18-January 16



QUOTE
Please, have you consider using ADO or DAO transactions to perform any update or batch updates? maybe, it might solve all the hassles with the update problems, just that, I never tried it before.


I guess you mean either using unbound forms and controls, manually assign the field values to each of them and then use code to assemble a recordset to do the updating, or binding the forms to client side ADO recordsets which are then updated... honestly I haven't used it since it seems to require a lot more custom code for each form and for me it would negate a lot of the point of using Access in the sense of quickly linking the tables and binding the forms to the data for the usual single record editing.

Not that I don't use recordsets at all. I like to use client side ADO recordsets which are batch updatable to minimize data traffic for the kind of operations that are row-specific and for which SQL would be too clunky. The ability to open and close connections quickly and use disconnected recordsets comes in handy.

Go to the top of the page
 
LPurvis
post Mar 16 2018, 05:20 AM
Post#10


UtterAccess Editor
Posts: 16,271
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

>> Apparently, when Access is working with a table row, it scans the whole row and keeps a "snapshot" (not to confuse with the Snapshot recordset) of the row for updating purposes so, when it's going to update one or several fields of the row, it checks if the whole row keeps exactly as it was when scanned and then proceeds to do the updating.

That's partly true, but it's also kinda not desired behaviour. In the absence of other functionality, it will verify each individual field value to determine change.
However, if your table includes a Rowversion (/Timestamp) column, then it is only that field which is checked to determine update before writing (and so then requesting conflict resolution if it's found).
Now that doesn't necessarily mean that Rowversions should be added universally, it's possible (albeit unlikely) to encounter conflict with yourself using them if you have two subforms bound to the exactly same source (hence unlikely). But employing a Rowversion/Timestamp will negate any issues with imprecise data types (such as floating point types and possibly Booleans). So they're often recommended (for example, if you use the SSMA to convert an ACCDB to SQL Server, you'll encounter many times when it recommends adding a Timestamp.

Cheers

--------------------
Go to the top of the page
 
Akudey
post Mar 19 2018, 08:17 PM
Post#11



Posts: 29
Joined: 17-August 13



Hi LPurvis

Thanks for your great insight on this one:
QUOTE
But employing a Rowversion/Timestamp will negate any issues with imprecise data types (such as floating point types and possibly Booleans). So they're often recommended (for example, if you use the SSMA to convert an ACCDB to SQL Server, you'll encounter many times when it recommends adding a Timestamp



Hi javert

I think the problem is not about the ODBC Data Source or "Return matching rows" option or Even MySQL, as noted in the quote above. As you can see, the same problem exists with MS SQL Server too.

So by mapping the appropriate data type of MS Access with the Server side, and a few timestamp fields, you'll be fine.
--====
I hope this will help with the article.



Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 12:37 AM