sqlite malformed db only locally

I have a SQLite db hosted on my website and there it works fine. I need add some new query and I like to test them locally before going live.

The problem is that after I download the db file to work with it locally, when I try to run a query (the same that work开发者_如何转开发s online) I get a "database disk image is malformed".

Any idea where the problem is?

My website detail

Server: linux

PDO Driver for SQLite 3.x : enabled

SQLite Library : 3.3.7

My Local details

Server: Windows 7 with XAMPP (1.7.2)

PDO Driver for SQLite 3.x : enabled

SQLite Library : 3.6.16

---

How do you download the SQLite3 DB file to your local system?

If you use something like FTP or SCP to get it directly while the server is online, it is quite possible that you will retrieve a corrupt file. Think about it: over the Internet, transferring such a file would need at least a few seconds - a few seconds during which your site is still online and transactions are performed on the server DB file. So the first bytes of the file would reflect e.g. transaction 1003, while the last bytes would reflect transaction 1015. Essentially the DB file changes while you are still downloading it.

And we still have not got into DB journal files and partial transactions.

What you need is an atomic copy of the DB file. Even cp on the server may not be fast enough. You need a way to lock the DB while copying the file.

There are three ways to do this:

• Use the sqlite3 shell utilty on the server and .dump the DB. You can then compress the SQL dump, download it and rebuild a local DB. A bit cumbersome but almost guaranteed to work.

• Use the SQLite3 backup API. The sqlite3 shell utility has a .backup command that does pretty much the same. Then you can just download the new DB file. Unfortunately older SQLite versions may not support this.

• Lock the DB file using the sqlite3 shell utility, use cp to copy it, unlock. The old, slightly dangerous way, which is why I'll not go into more details.

• Stop your server so that no transactions are performed, copy the file, restart. I don't consider this an actual solution, hence its not being included in the final count.

BTW, this page contains a few common ways to corrupt an SQLite3 DB - you might want to have a look...