Problem with table locks since PR #8

[dsteinkopf]

In one of my projects, I have very large tables (several GB size). Locking while the table is being dumped would stop the whole system which the dump takes 30 minutes or more. So I decided (as a workaround instead of a "real online backup") to add MYSQLDUMP_ADD_OPTS=--skip-lock-tables. This has been working for months (or years now).

Currently (I assume since PR #8) the backup again locks the project :-(

I was not yet able to dive into that deeply but I assume the following:

• Before, --skip-lock-tables was added after --lock-tables in the command line. So table locking was disabled.
• Now, --lock-tables is repeatedly added only if not backing up "mysql", so it is the last option in the command line. So table locking is active.

As we have disabled tracing (-> removed set -x - for good reasons) I could not see in the log files if the above idea is correct. How about adding some DEBUG option which logs the effective mysql options?

[dsteinkopf]

@thib3113 what do you think about this?

[dsteinkopf]

Two ideas to solve the problem:

1. handle MYSQLDUMP_ADD_OPTS within the loop to add it after --lock-tables
2. add a special LOCK_TABLES env var. The default is 1. So in my case, I could set it to 0.

I'd prefer the second one as it seems cleaner to me.

@thib3113 Do you have the time to submit a fix?

[thib3113]

I prefer the 1/ .

Because the second one can maybe break things ( like if you need to add OPTS after the --lock-tables ), and will always show a warning if we lock the tables mysql ( If we clean the loop ) .

else, 3/ instead of adding --lock-table when the table is mysql, we maybe can add --skip-lock-tables if the table is mysql ? So, In you case, you will get two time --skip-lock-tables ( this maybe need some tests with a big db :/ )

[dsteinkopf]

Ok. I see the argument about 2. And I don't like 3.

So let's take 1?

[thib3113]

Ok for me .

Just for the moment, I can't do it for the moment ( like not today, maybe not tomorrow ) . Because I'll need to recreate repository / docker hub .... to test .

in the other way, I understand you need it asap for yours backups :/ .

[thib3113]

Oh I think .

And If I convert

CURRENT_OPTS="$CURRENT_OPTS --lock-tables";

to

CURRENT_OPTS="--lock-tables $CURRENT_OPTS";

Your option will be after the --lock-tables ? And resolve the error ? ( did you see a problem ? )

[dsteinkopf]

Seems a simple and good idea 😀