UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Write Conflict w/ Mysql Backend    
 
   
prince_mallow
post Aug 5 2010, 01:34 PM
Post #1

New Member
Posts: 7



Access Version 2003, File format 2000.
i, I have a form in MSAccess that produces a write conflict under the following circumstances. There is a text box, and say it has the string "This is a sentence" in the text box. If a user were to delete the e at the end of the word sentence and insert the e back, once the user moves on to the next record (the record updates) it produces a write conflict because the data didn't change. This occurs often for me because my VBA code will do this
CODE
[Subtotal] = "" 'Empties subtotal
if blah is true then
[Subtotal] = [Subtotal] + 3.50
End if
if blah2 is true then
[Subtotal] = [Subtotal] + 3.50
End if
etc....

Thus having the same affect as if a user just altered the data without changing the data. The problem is if it has already been run before and the user clicks one of the field that runs this calculation, then when the record tries to update it will complain about Write conflict.
The suggested solution I found on the internet seem to state that simply adding a timestamp column to the table would do the trick, however this has not done the trick for me. I assumed it was because they assumed it was a mssql backend but this link specifically for MySQL states the same thing: http://webcache.googleusercontent.com/sear...=clnk&gl=us
Any suggestions
Go to the top of the page
 
+
BananaRepublic
post Aug 5 2010, 01:47 PM
Post #2

Admin under the bridge
Posts: 1,197
From: Banana Republic



I have a gut feeling that we're missing a crucial piece.
ormally, an erroneous write conflict is caused whenever we run two different modifications in different contexts. A example of this would be:
a) Editing a form, then running an action query on the same record
b) Editing same record in two different Recordset variables
Your code, as far as I can see, is fine because your code is modifying the form itself which should appear as coming from same context. If your code for instance somewhere else ran an update query, that would be a problem, and we'd want to remove the action query and replace it with a code that modify the same values in the form directly.
So, do your VBA code reference a new recordset or query that is not a part of the form itself and thus would be in a different context?
Another thought is that did you specify the ODBC connection to return matching rows instead of affected rows? MySQL documentation does suggest turning this on for Access clients.
HTH.
Go to the top of the page
 
+
prince_mallow
post Aug 5 2010, 02:27 PM
Post #3

New Member
Posts: 7



Since I have a confusing set up I was able to boil it down to the smallest element possible. Just the link table.
When I open the link table it shows me all the rows. The first row for example has the data 0,0,-1,-1, etc...
If I highlight the -1 and change it to zero and then click next record it is fine. If however I highlight the -1 and type -1 and then click the next record then it produces the write conflict.
This gives me the impression that access detects that there was new user input, but when it checks with MySql it discovers that the data is exactly the same it thinks there is something wrong. (My interpretation of what access [or mysql] is doing maybe wrong)
After finding the article I linked to I did specify the ODBC connection to return matching rows, but this did not affect anything as far as I can tell.
Go to the top of the page
 
+
BananaRepublic
post Aug 5 2010, 02:38 PM
Post #4

Admin under the bridge
Posts: 1,197
From: Banana Republic



I'm pretty sure that should not cause error by itself. But since you mention timestamp.... See if this apply to you, too.
TH.
Go to the top of the page
 
+
rabroersma
post Aug 5 2010, 03:26 PM
Post #5

UtterAccess VIP
Posts: 1,262
From: Fountain Valley, California, USA



I wonder if you table has any nulls in the boolean columns. Because if they do, you'd certainly be getting write conflict errors in such cases since MS-Access doesn't like ODBC linked nullable boolean fields.
Go to the top of the page
 
+
BananaRepublic
post Aug 5 2010, 03:36 PM
Post #6

Admin under the bridge
Posts: 1,197
From: Banana Republic



Good point. I tend to forget about non-compatibility of Access' Yes/No data types with ODBC's boolean.
Go to the top of the page
 
+
prince_mallow
post Aug 5 2010, 03:37 PM
Post #7

New Member
Posts: 7



It should not cause an error by itself you are right, unfortunately it does so whether or not there is a timestamp column present. The link you provided gave me a few ideas of where to search, but it will take me some time to look these ideas up.
Go to the top of the page
 
+
BananaRepublic
post Aug 5 2010, 03:56 PM
Post #8

Admin under the bridge
Posts: 1,197
From: Banana Republic



BTW, are you using a table or a view?
've found that view can be slightly less stable due to the fact that primary keys does not get carried over in a view.
Go to the top of the page
 
+
prince_mallow
post Aug 5 2010, 04:03 PM
Post #9

New Member
Posts: 7



I'm simply using the linked-table.
think, that MSAcess is not getting the locks on record, so I think it is a setting in either MSaccess, ODBC, or MYsql that is causing the problem, but finding what and where is the quest haha.
Go to the top of the page
 
+
BananaRepublic
post Aug 5 2010, 04:08 PM
Post #10

Admin under the bridge
Posts: 1,197
From: Banana Republic



Well, Access itself does not enforce any kind of locking against ODBC tables, leaving it up to the server entirely to lock as it see fit. Write Conflicts are caused whenever Access sees that the content of record has changed since it last looked at it when it try to make changes. That's why sometime triggers or timestamps can cause problems as well running code in different contexts.
The next suggestion is if you can, turn on general log and read the output (e.g. tail -f) as you work in Access. It usually tells a lot about what it is going on.
Go to the top of the page
 
+
prince_mallow
post Aug 5 2010, 04:30 PM
Post #11

New Member
Posts: 7



I'm sorry I'm not familiar with tail -f. How would I implement that, or do you have a link?
oes this mean I have to turn on General logs in the configuration file or can I just run it from the ssh.
Ofound that our server is running version 4.1 so I'm making a full backup to see if upgrading to 5.1 will change anything. But I think getting the general logs first is a better idea.
Go to the top of the page
 
+
BananaRepublic
post Aug 5 2010, 04:50 PM
Post #12

Admin under the bridge
Posts: 1,197
From: Banana Republic



Disclaimer: I've not worked on any version prior to 5.0 so what I may write may not apply here. Consult MySQL manual. I also should point out that 4.1 and earlier is not under active development anymore. The following also assumes you are using a bash shell. This will not work if MySQL is running on a Windows host since Windows does not have a bash or tail.
Link to 4.1 MySQL manual - scroll down a bit to see the EOL announcement for 4.1
If you have SUPERUSER privilege, you should log in to MySQL and issue this command in the client:
CODE
SET GLOBAL general_log = 'on';

To find where the log is being written to, issue this command:
CODE
SHOW VARIABLE LIKE 'general_log_file';

Copy the path given and save that information.
Exit MySQL client back to the bash shell, and issue this:
CODE
tail -f <full path to the log>

That will then give you the realtime output of last 10 lines in the general log being written. Note that you may or may not have the permissions, again depending on where the file is located and whether your user account has permissions to the location. If you don't have a permission, use sudo.
Also, this is very important: When you're done analyzing what's going on, turn general log off. You don't want to forget that step.
HTH.
Go to the top of the page
 
+
prince_mallow
post Aug 5 2010, 05:45 PM
Post #13

New Member
Posts: 7



I upgraded to 5.0
On either case I was unable to do SHOW VARIABLE LIKE 'general_log_file'; saying that it doesn't understand anything after the show.
and
ERROR 1193 (HY000): Unknown system variable 'general_log'
This isn't a big deal, the users have been used to receiving this error message from time to time, I guess I'll have to fiddle around with it later
Go to the top of the page
 
+
BananaRepublic
post Aug 5 2010, 05:51 PM
Post #14

Admin under the bridge
Posts: 1,197
From: Banana Republic



Looks like general_log was added to 5.1.
orry. A kludge is to use slow query log which should be available in 5.0 and set the time to say, 0.0001 to mark all queries as "slow" and thus get them in the log.
[url=http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_log-slow-queries[/url]
HTH.
Go to the top of the page
 
+
prince_mallow
post Aug 6 2010, 06:45 PM
Post #15

New Member
Posts: 7



I haven't tried the General_log but what I suspect happens is that if the user manipulates the form, table, or what have you, and upon msaccess updating the row, if the data is exactly the same then Mysql will reply"
CODE
0 row(s) affected by last query. Duration for query: 0.XXX sec

And then MSAccess sees the 0 row(s) and says, hey our request got rejected the record must have been edited and locked by someone else.
That is what I suspect happens. After searching all day for an answer to resolve it, I got super angry. And in that bit of anger an idea came to me. The "solution" (which is more of just a work around) Is that I removed all explicit data changes in the form that I was using.
So that
CODE
Process()
[subtotal].value = 0
if a = b then
  [subtotal].value = [subtotal].value + 2.95
end if
[total].value = [subtotal].value - [coupon].value

now would look something like this:
CODE
Process()
Dim dbl_subtotal AS DOUBLE
dbl_subtotal = 0
if a = b then
  dbl_subtotal = dbl_subtotal + 2.95
end if
if [subtotal].value <> dbl_subtotal then
  [subtotal].value = dbl_subtotal
End if
if [total].value <> [subtotal].value - [coupon].value then
[total].value = [subtotal].value - [coupon].value
End if
Me.Refresh

So basically no form element is altered unless their values change and the Me.Refresh prevents the user from putting on thing, removing it again, and getting the write conflict message yet again.
EDIT -------------------------------------
It should be noted that if somebody uses this as a fix, any element that calls this function would cause the form to be refreshed so if there are any fields that are indicated as "not Null" and they are not filled in first, it may or may not cause issues to crop up.
Go to the top of the page
 
+
chriswilliams
post Aug 10 2012, 02:16 PM
Post #16

New Member
Posts: 1



I had the same problem, the way I resolved it was to add a new integer field and increment it on the Open event.
Everything listed above was unsuccessful for me.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 12:04 PM