[MB-3360] mbrestore tool does not support restoring from WAL'ed database files Created: 21/Jan/11  Updated: 31/Jul/12  Resolved: 31/Jul/12

Status: Resolved
Project: Couchbase Server
Component/s: tools
Affects Version/s: 1.7 alpha 2, 1.8.0
Fix Version/s: 1.7 alpha 2
Security Level: Public

Type: Bug Priority: Major
Reporter: Perry Krug Assignee: Steve Yen
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   
This error is presented:
tcarpenter@sports-mb-test02:/opt/membase/1.6.4.1/data/ns_1]$ /tmp/python271/Python-2.7.1/python /opt/membase/1.6.4.1/bin/ep_engine/management/mbrestore -a default default-0.mb default-1.mb default-2.mb default-3.mb
Traceback (most recent call last):
  File "/opt/membase/1.6.4.1/bin/ep_engine/management/mbrestore", line 147, in <module>
    main()
  File "/opt/membase/1.6.4.1/bin/ep_engine/management/mbrestore", line 105, in main
    db.executemany("attach ? as ?", zip(db_filenames, attached_dbs))
sqlite3.OperationalError: file is encrypted or is not a database

According to Dustin, this is because the mbrestore tool does not currently support WAL db files and the workaround is to run: /opt/membase/bin/ep_engine/management/sqlite3 <filename> 'pragma journal_mode=delete' on all the db files you are trying to restore.

Please add the above workaround to the mbrestore script itself.

 Comments   
Comment by Sean Lynch (Inactive) [ 26/Jan/11 ]
I think it'd be even better to require a newer version of sqlite for Python, or ship our own sqlite module or even Python runtime or frozen scripts.
Comment by Perry Krug [ 28/Jan/11 ]
A Pivotal Tracker story has been created for this Issue: http://www.pivotaltracker.com/story/show/9309485
Comment by Perry Krug [ 03/Mar/11 ]
This is not a python version problem, it's a problem with WAL.
Comment by Perry Krug [ 05/Jun/11 ]
Perry Krug deleted the linked story in Pivotal Tracker
Comment by Sandeep Mukherjee [ 15/Jun/11 ]
Running the workaround:

run: /opt/membase/bin/ep_engine/management/sqlite3 <filename> 'pragma journal_mode=delete'

gives an error:

sqlite3.exe: Error: too many options: "journal_mode=delete'"
Use -help for a list of options.
Comment by Perry Krug [ 15/Jun/11 ]
This issue should be resolved and the workaround not needed in post 1.7 releases...can you verify that?
Comment by Sandeep Mukherjee [ 15/Jun/11 ]
Well here is my problem. I have data which ranges upto 57 Gig with 11 million entries. The data was generated using the version 1.6.5. If i try to load the data to the new version will it be backward compatible?
Comment by Perry Krug [ 15/Jun/11 ]
There are a few strategies for converting the data. They're not directly compatible, but you can either upgrade the database (http://www.couchbase.org/wiki/display/membase/Membase+Server+1.7) or use our restore tool to pull the data out and replay it into a new cluster (http://www.couchbase.org/wiki/display/membase/Backup+and+Restore+with+Membase). The latter step is where this bug came from, and if you use the latest mbrestore script (provided with the 1.7 installation) you won't need this workaround.
Comment by Perry Krug [ 13/Apr/12 ]
This has been reported on 1.8:
"When trying to restore from snapshot with cbrestore, it fails with:

"Either the metadata db file is not specified
or the backup files need to be upgraded to version 2.
Please use cbdbupgrade for upgrade or contact support."

But using cbbackup on snapshot data and restoring afterwards works."
Comment by Peter Wansch (Inactive) [ 31/Jul/12 ]
Steve, since the workaround did the trick, can this issue be closed?
Comment by Steve Yen [ 31/Jul/12 ]
Additionally, as part of the workaround, the user should also run "pragma wal_checkpoint(FULL)" to incorporate any WAL files before changing the journal_mode.

So...

  pragma wal_checkpoint(FULL);
  pragma journal_mode=delete;

See...

  http://www.sqlite.org/pragma.html#pragma_wal_checkpoint

One last workaround, possibly already mentioned, is to run cbrestore on a box with updated python and python sqlite3 binding.
Comment by Steve Yen [ 31/Jul/12 ]
Related to Perry's April 13, 2012 comment, another workaround to handle the "backup files need to be upgrade to version 2" issue...

IF the user can determine that the files _definitely_ came from the right version (e.g., backup of 1.8.1 and trying to restore to 1.8.1), then perhaps the user_version pragma information did not transfer correctly.

For example, use sqlite3 to run...

  pragma user_version;

The result should be '2'. If not then a workaround fix would be, in sqlite3 to use the following across all the sqlite files...

  pragma user_version=2;
Generated at Sat Sep 20 04:05:41 CDT 2014 using JIRA 5.2.4#845-sha1:c9f4cc41abe72fb236945343a1f485c2c844dac9.