Problem with record locking (1 Viewer)

[JohanS]

Hello Guy's

I want to migrate our complete MsAccess database to mariaDB.

I have already running debian with mariaDB on premises, and have already made a new table in it. (and coupled it to my MSaccess frontend no problems there.)

I'm using ODBC driver MySQL ODBC 8.0 ANSI driver.

the problem i have is when i quickly change some records in the table, i'm recieving a writing conflict (caused by a recordlock), if i wait a short time i can change it,
if i'm using heidiSQL to change the data directly in the table there is no record lock , so i'm almost confinced the problem is in the odbc connector.

does anyone has a tip or solution for this problem?

 

[Minty]

I don't run mariaDB, but I would recommend using a specific ODBC driver such as the one here https://downloads.mariadb.org/connector-odbc/

Drivers can make a massive difference to performance and reliability.

 

[theDBguy]

Hi. Welcome to AWF!

I agree, you may be using the wrong driver. Did you say you were changing the data in the table? You're were not using a form?

 

[JohanS]

@Minty ,
Thanks for the input, i have changed to this driver, but the result is the same.

@theDBguy,
i was changing the data, opened the RAW table in Msacces, and was changing some 0 to 1 from some records going down, and after this going up and changed them back to 0.

not all records gives the record lock, but some do. if i wait +/- 30 seconds i can change the record, but not directly after.

 

[The_Doc_Man]

This could also be caused by having "Pessimistic Locks" which is something you select from the File >> Options >> Current Database path starting from the ribbon.

 

[JohanS]

This could also be caused by having "Pessimistic Locks" which is something you select from the File >> Options >> Current Database path starting from the ribbon.

i think you mean the file --> options -->clien settings --> no record lock setting?

 

[arnelgp]

did you create a primary key for your tables in mariadb.

 

[JohanS]

Yes i have set a primary key INT (auto increment)

 

[arnelgp]

sorry, i just tested on mariadb (local) with same odbc driver you have and
i don't encounter any problem.

 

[bastanu]

Have you tried to tweak the refresh intervals in your Access client options (under File\Options\Client Settings)?
Cheers,

 

[The_Doc_Man]

i think you mean the file --> options -->clien settings --> no record lock setting?

Possibly. I can never remember what that silly setting is located.

 

[JohanS]

First of all, let me say, what an incredibly active forum ...
within days and already so much reactions...

i just have tried some other refresh intervall settings, but unfortunatly no difference in the fault.
The recordlock setting is turned to 'no record lock', but the checkbox below this option is enabled, "Open database with recordlock".
but since this is a front end, and is only running on my machine i don't think this has something to do with my problem.

@arnelgp do you have some options enabled in the odbc panel?
the only option i have enabled is the "enable SQL_auto_is_null" for the rest i have all option deselected

 

[arnelgp]

here is my odbc .dsn content:

[ODBC]
DRIVER=MySQL ODBC 8.0 ANSI Driver
UID=root
DFLT_BIGINT_BIND_STR=1
PORT=3307
DATABASE=test

 

[arnelgp]

do we have same client setting:

 

[JohanS]

here is my odbc .dsn content:

[ODBC]
DRIVER=MySQL ODBC 8.0 ANSI Driver
UID=root
DFLT_BIGINT_BIND_STR=1
PORT=3307
DATABASE=test

i just found out what triggers the write conflict ... when I change my 'selection' column (which contains a 0 or a 1) and change the data from 0 to 1 there is no problem, but when i go over the table and change it from a 0 to 0 (so actually no change) it gives me the error

i have made a short movy so you can see what's it doing:

 

[JohanS]

do we have same client setting: View attachment 92508

Yes i have exact the same settings, oh and i use a USER-DSN instead of a FILE-DSN don't know if this is something that could do something with the problem

 

[JohanS]

i just found out what triggers the write conflict ... when I change my 'selection' column (which contains a 0 or a 1) and change the data from 0 to 1 there is no problem, but when i go over the table and change it from a 0 to 0 (so actually no change) it gives me the error

i have made a short movy so you can see what's it doing:

its also the same for a string ... as soon as the data is the same (for example i retype exact the same characters over the current record) it throws a write conflict ...

so luckely its not a big issue ... but i do find it a strange error, why can't it update a record when the data stays the same...