5

I have a Microsoft Access database that is connected to a MySQL server via ODBC and is accessed by multiple users.

Over time many records are locked and editing is not possible anymore.

Is there a way to bulk unlock all records?

The Error Message:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changed to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

7
  • it it still locked. UNLOCK TABLES; doesnt work for me. it's not the table that is locked, its the records.
    – unr3al011
    Sep 17, 2012 at 7:01
  • UNLOCK TABLES; won't work in this case, because records were locked from some other session. Sorry about that.
    – Sreenath Soman
    Sep 17, 2012 at 7:17
  • 1
    This sounds like a MySQL issue. Add a mysql tag to attract the attention of more people who know MySQL.
    – HansUp
    Sep 17, 2012 at 7:28
  • It looks like Access does the locking. Related: stackoverflow.com/questions/12026199/… Sep 17, 2012 at 8:05
  • yes but, even if this would work, this will make the data read-only. but it has to be writeable too.
    – unr3al011
    Sep 17, 2012 at 8:13

4 Answers 4

3

To help you troubleshoot this, here is a very important piece of information to keep in mind:

Locks, in MySQL, whether they are on rows, tables, advisory/named locks, and even the global FLUSH TABLES WITH READ LOCK are held by continued existence of the specific connection thread that obtained the lock. When I say "connection thread," in this context, I mean the specific instance of a client's connection to the server... the one where the lock was requested. These are the threads listed in SHOW FULL PROCESSLIST;

If the requesting connection is closed gracefully, all locks requested by that connection release immediately.

If the specific connection thread is killed by an admin (mysql> KILL #; where # is the ID from SHOW FULL PROCESSLIST that you want to force to disconnect) the locks are released as soon as that thread disappears from SHOW FULL PROCESLIST;

If that connection is not closed gracefully, the connection eventually times out (the default is 28800 seconds unless you have changed it) and then the locks are released.

Row and table locks cannot persist in the absence of the connection that originally obtained the lock... so there is no such thing in MySQL as locks piling up over time and needing to be unlocked, in the absence of client connections still holding on to the locks.

Even if it's the case that clients are improperly holding locks, then forcing a disconnect of the client will be your only workaround, as locks are there for a reason... to keep your data consistent. Forcing disconnect does not mean having everyone log out, since you've explain this isn't practical. See the KILL command, above.

My speculation is that Access is not properly interpreting responses from MySQL and only thinks records are locked when in fact it's something else.

As has already been observed, you should be able to update values in these rows using Toad, HeidiSQL, Query Browser, Workbench, or simply the mysql command line... and it's very likely you will be able to update them no problem... proving that they aren't really locked, and that Access is just confused.

Posting the precise wording of the message users are seeing from Access will probably be the best way to shed accurate light on the problem.

2
  • The Error: This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changed to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
    – pila
    Sep 18, 2012 at 6:13
  • But i found a solution how to Fix it: i added a Field named "TIMESTAMP" of type timestamp. Then i updated all records with TIMESTAMP=NOW();
    – pila
    Sep 18, 2012 at 6:14
3

I finally could fix my problem by adding a field named TIMESTAMP of type timestamp and default value CURRENT_TIMESTAMP and attribute = on update CURRENT_TIMESTAMP. Then i updated all records and set TIMESTAMP=NOW().

For now i did not get any error again.

1

You might want to make sure you don't have any bit (or boolean) values that are NULL. For some reason those mess up Access's head. The solution is either the timestamp suggested by you, or giving a default value to your boolean values. At least this is what causes this in MS SQL Server connected database. I guess it is the same funny business in MySQL.

https://support.microsoft.com/en-us/kb/280730

0

If you still have one TIMESTAMP field (called tablefield, for example), change it to DATETIME with no default value and no attributes. Then create a new TIMESTAMP field called TIMESTAMP (for example) with CURRENT_TIMESTAMP as default value, and onupdate CURRENT_TIMESTAMP as attribute.

If you use before the original changed TIMESTAMP field, you can get the current timestamp with a trigger like

CREATE TRIGGER `justnow` BEFORE INSERT ON `tablename` FOR EACH ROW set new.tablefield=now()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.