Bug #2791

Backup: pre-backup-data event fails if mysqldump can't acquire lock

Added by Giacomo Sanchietti about 7 years ago. Updated almost 7 years ago.

Status:CLOSEDStart date:
Priority:NormalDue date:
Assignee:-% Done:

100%

Category:nethserver-mysql
Target version:v6.5
Security class: Resolution:
Affected version:v6.5-final NEEDINFO:No

Description

In rare cases, mysqldump can fail dumping the databases because some locks can't be acquired.
The error is something like this:

mysqldump: Got error: 1016: Can't open file: './sogo/sogopfonti0043296bda1_acl.frm' (errno: 24) when using LOCK TABLES

To avoid this kind of errors, the best practice seems to be using mysqldump with --single-transaction and --quick options (see man mysqldump).

Associated revisions

Revision 2c4e3cf2
Added by Giacomo Sanchietti about 7 years ago

Backup: avoid table locking. Refs #2791

History

#1 Updated by Giacomo Sanchietti about 7 years ago

  • Status changed from NEW to TRIAGED
  • Target version set to v6.5
  • % Done changed from 0 to 20
  • Affected version set to v6.5

#2 Updated by Giacomo Sanchietti about 7 years ago

Add documentation about this implementation can impact on MyISAM tables:
"When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state"

We should also advise to stopo/start critical services in post and pre backup.

#3 Updated by Davide Principi about 7 years ago

Could we change the default MySQL engine?

http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html

   # rpm -q mysql
mysql-5.1.73-3.el6_5.x86_64

#4 Updated by Giacomo Sanchietti about 7 years ago

I'd like to stick with our policy: configure as upstream.

In this case the default engine option (default-storage-engine) is not set, and the value is delegated to the packager.

#5 Updated by Giacomo Sanchietti about 7 years ago

  • Status changed from TRIAGED to ON_DEV
  • Assignee set to Giacomo Sanchietti
  • % Done changed from 20 to 30

#6 Updated by Giacomo Sanchietti about 7 years ago

  • Status changed from ON_DEV to MODIFIED
  • % Done changed from 30 to 60

#7 Updated by Giacomo Sanchietti about 7 years ago

  • Status changed from MODIFIED to ON_QA
  • Assignee deleted (Giacomo Sanchietti)
  • % Done changed from 60 to 70
Package in nethserver-testing:
  • nethserver-mysql-1.0.5-1.0git2c4e3cf2.ns6.noarch.rpm
Test case
  • Install mysql and backup data:
    yum install nethserver-mysql nethserver-backup-data
signal-event pre-backup-data
  • Create a database with some data:
    mysql -e "create database test" 
mysql test -e 'create table `test` ( `key`  varchar(20), `value`  varchar(20))'
mysql test -e "GRANT ALL PRIVILEGES ON test.* TO test@localhost IDENTIFIED BY 'DQAFyzsWsxfZPJuz'" 
mysql test -e "INSERT INTO test VALUES ('foo','bar')"
  • Execute pre backup and verify it returns 0
    signal-event pre-backup-data
  • Check the test database is created: /var/lib/nethserver/backup/mysql/test.dump

#8 Updated by Davide Principi about 7 years ago

  • Assignee set to Davide Principi

#9 Updated by Davide Principi about 7 years ago

  • Affected version changed from v6.5 to v6.5-final

#10 Updated by Davide Principi about 7 years ago

  • Status changed from ON_QA to VERIFIED
  • Assignee deleted (Davide Principi)
  • % Done changed from 70 to 90

VERIFIED

test.dump was created. BTW table type is MyISAM.

#11 Updated by Davide Principi almost 7 years ago

  • Status changed from VERIFIED to CLOSED
  • % Done changed from 90 to 100

In nethserver-updates:
nethserver-mysql-1.0.6-1.ns6.noarch.rpm

Also available in: Atom PDF