mysqldump lockTables set to false does not work #73

Closed
waldemarm opened this Issue Sep 28, 2016 · 9 comments

Comments

Projects
None yet
2 participants
@waldemarm

Hello,
I did try to to set the lockTables to false in the XML configuration file, unfortunately this does not add appropriate parameter to the mysqldump command.
It also would be nice to have 'single-transaction' option available.

Thanks,
waldemarm

@sebastianfeldmann

This comment has been minimized.

Show comment
Hide comment
@sebastianfeldmann

sebastianfeldmann Sep 28, 2016

Owner

Are you using xml oder json to configure phpbu?
Currently you have to use strings if you are using json

"lockTables": "true"

Are you using xml oder json to configure phpbu?
Currently you have to use strings if you are using json

"lockTables": "true"
@waldemarm

This comment has been minimized.

Show comment
Hide comment
@waldemarm

waldemarm Sep 28, 2016

I use the xml to configure phpbu.

<source type="mysqldump">
      <option name="databases" value="db_name"/>
      <option name="user" value="db_user"/>
      <option name="password" value="db_pass"/>
      <option name="lockTables" value="false" />
</source>

phpbu version 3.2.1

waldemarm commented Sep 28, 2016

I use the xml to configure phpbu.

<source type="mysqldump">
      <option name="databases" value="db_name"/>
      <option name="user" value="db_user"/>
      <option name="password" value="db_pass"/>
      <option name="lockTables" value="false" />
</source>

phpbu version 3.2.1

@sebastianfeldmann

This comment has been minimized.

Show comment
Hide comment
@sebastianfeldmann

sebastianfeldmann Sep 28, 2016

Owner

Using this should result in a mysqldump command without the --lock-tables option.

<option name="lockTables" value="false" />

Where as the following should result in a command with the --lock-tables option.

<option name="lockTables" value="true" />

Locally I just verified that with this backup configuration.

phpbu.xml

<backups>
    <backup>
      ...
      <source type="mysqldump">
        <option name="databases" value="myDB" />
        <option name="lockTables" value="true" />
        ...

Run phpbu dry run

phpbu --simulate

outputs the command with --lock-tables

backup: [mysqldump] *******************************************************
backup data:
/usr/bin/mysqldump --user='foo' --password='******' --lock-tables 'myDB' > /dev/phpbu/backup/dump-20160928-1113.sql
ok

Setting lockTables to false results in the command without --lock-tables

backup: [mysqldump] *******************************************************
backup data:
/usr/bin/mysqldump --user='foo' --password='******' 'myDB' > /dev/phpbu/backup/dump-20160928-1116.sql
ok
Owner

sebastianfeldmann commented Sep 28, 2016

Using this should result in a mysqldump command without the --lock-tables option.

<option name="lockTables" value="false" />

Where as the following should result in a command with the --lock-tables option.

<option name="lockTables" value="true" />

Locally I just verified that with this backup configuration.

phpbu.xml

<backups>
    <backup>
      ...
      <source type="mysqldump">
        <option name="databases" value="myDB" />
        <option name="lockTables" value="true" />
        ...

Run phpbu dry run

phpbu --simulate

outputs the command with --lock-tables

backup: [mysqldump] *******************************************************
backup data:
/usr/bin/mysqldump --user='foo' --password='******' --lock-tables 'myDB' > /dev/phpbu/backup/dump-20160928-1113.sql
ok

Setting lockTables to false results in the command without --lock-tables

backup: [mysqldump] *******************************************************
backup data:
/usr/bin/mysqldump --user='foo' --password='******' 'myDB' > /dev/phpbu/backup/dump-20160928-1116.sql
ok
@waldemarm

This comment has been minimized.

Show comment
Hide comment
@waldemarm

waldemarm Sep 28, 2016

That is very true and I already verified it in a dry run with phpbu --simulate.
However mysqldump by default has the lock-tables value set to TRUE, therefore running this command without lock-tables parameter will effectively run it like with lock-tables=true.

In that case i think that phpbu configuration:

<option name="lockTables" value="false" />

should result in the following output:

/usr/bin/mysqldump --user='foo' --password='******' --lock-tables=false

instead of

/usr/bin/mysqldump --user='foo' --password='******' 

That is very true and I already verified it in a dry run with phpbu --simulate.
However mysqldump by default has the lock-tables value set to TRUE, therefore running this command without lock-tables parameter will effectively run it like with lock-tables=true.

In that case i think that phpbu configuration:

<option name="lockTables" value="false" />

should result in the following output:

/usr/bin/mysqldump --user='foo' --password='******' --lock-tables=false

instead of

/usr/bin/mysqldump --user='foo' --password='******' 
@sebastianfeldmann

This comment has been minimized.

Show comment
Hide comment
@sebastianfeldmann

sebastianfeldmann Sep 28, 2016

Owner

If I understand the mysqldump docs correctly, by default --lock-tables is deactivated.
Some options like --opt enable --lock-tables but by default it is not active.
To actively deactivate it one should use --skip-lock-tables

--lock-tables, -l

For each dumped database, lock all tables to be dumped before dumping them.
The tables are locked with READ LOCAL to permit concurrent inserts in the case
of MyISAM tables. For transactional tables such as InnoDB, --single-transaction
is a much better option than --lock-tables because it does not need to lock the
tables at all.

Because --lock-tables locks tables for each database separately,
this option does not guarantee that the tables in the dump file are
logically consistent between databases. Tables in different databases
may be dumped in completely different states.

Some options, such as --opt, automatically enable --lock-tables.
If you want to override this, use --skip-lock-tables at the end of the option list. 

If I understand the mysqldump docs correctly, by default --lock-tables is deactivated.
Some options like --opt enable --lock-tables but by default it is not active.
To actively deactivate it one should use --skip-lock-tables

--lock-tables, -l

For each dumped database, lock all tables to be dumped before dumping them.
The tables are locked with READ LOCAL to permit concurrent inserts in the case
of MyISAM tables. For transactional tables such as InnoDB, --single-transaction
is a much better option than --lock-tables because it does not need to lock the
tables at all.

Because --lock-tables locks tables for each database separately,
this option does not guarantee that the tables in the dump file are
logically consistent between databases. Tables in different databases
may be dumped in completely different states.

Some options, such as --opt, automatically enable --lock-tables.
If you want to override this, use --skip-lock-tables at the end of the option list. 
@sebastianfeldmann

This comment has been minimized.

Show comment
Hide comment
@sebastianfeldmann

sebastianfeldmann Sep 28, 2016

Owner

I just released phpbu 3.2.2 with mysqldump singleTransaction option available ;)

Just use

<option name="singleTransaction" value="true" />
Owner

sebastianfeldmann commented Sep 28, 2016

I just released phpbu 3.2.2 with mysqldump singleTransaction option available ;)

Just use

<option name="singleTransaction" value="true" />
@sebastianfeldmann

This comment has been minimized.

Show comment
Hide comment
@sebastianfeldmann

sebastianfeldmann Sep 28, 2016

Owner

I will close this issue, or do you really need the --skip-lock-tables option?

I will close this issue, or do you really need the --skip-lock-tables option?

@waldemarm

This comment has been minimized.

Show comment
Hide comment
@waldemarm

waldemarm Sep 28, 2016

Looks like the singleTransaction option sorted the problem therefore --skip-lock-tables is not necessary.
Thank you very much for your help!

Looks like the singleTransaction option sorted the problem therefore --skip-lock-tables is not necessary.
Thank you very much for your help!

@sebastianfeldmann

This comment has been minimized.

Show comment
Hide comment
@sebastianfeldmann

sebastianfeldmann Sep 28, 2016

Owner

You are welcome ;)

You are welcome ;)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment