2

I would like to vacuum all my database files under /data, to achieve somewhat better performance so I ran the following script:

for i in $(find /data -iname "*.db"); do
#echo $(basename $i);
sqlite3 $i 'VACUUM;';
resVac=$?
if [ $resVac == 0 ]; then
    resVac="SUCCESS";
else
    resVac="ERRCODE-$resVac";
fi;

sqlite3 $i 'REINDEX;';
resIndex=$?
if [ $resIndex == 0 ]; then
    resIndex="SUCCESS";
else
    resIndex="ERRCODE-$resIndex";
fi;
echo "Database $i:  VACUUM=$resVac  REINDEX=$resIndex" | tee -a /data/vacuum.log
done

As you can see this script vacuums and reindexes each database file under /data. REINDEX works fine, but VACUUM does not. It returns "Error: unable to open database file". (error code 14)

I am dealing with /data. So, it can't be a mount problem.

  • Is there an strace utility? Maybe that sheds some light. Also: is this an Android 4.2 device? – ce4 Jun 29 '13 at 7:56
  • 2
    A quick unrelated shell scripting tip: you don't need a semicolon at the end of each line of your script. You only need semicolons to put more than one command on the same line (and inside SQL queries, as you know). – Dan Hulme Jun 29 '13 at 9:44
  • You might wish to read: Android SQLiteOpenHelper cannot open database file and SQLite - Error 14 - SQLITE_CANTOPEN, the latter giving a good argument: If the table is locked by another process the sqlite3_exec will through an error. Not sure if that's your problem, though. Google Search on "sqlite error 14" might give you additional hints. – Izzy Jun 29 '13 at 11:45
  • no, the device has no strace, and it's not included in busybox. it is android 4.1.2 device. – juniecho Jul 6 '13 at 4:53
  • and this script runs at early boot stage, so i do not think all of these databases are locked by other processes. – juniecho Jul 6 '13 at 4:55
2

Unlike REINDEX, the VACUUM command needs to create a new temporary file to work in. It then replaces the database file with this temporary file. This means that it needs write access, not only to the database file, but also to the directory it's in.

  • Working on the databases below /data per se already requires root (even to read), so it's safe to assume above script has been running as root (or it would not be able to work at all). So file system permissions should be no issue -- or do I miss something? – Izzy Jun 29 '13 at 11:39
  • Yeah, it doesn't make a lot of sense to me either, but it's the only explanation I could find for why REINDEX might work but not VACUUM. – Dan Hulme Jun 29 '13 at 11:44
  • Admitted, you've made your point (as technically, this really is a difference). Though it shouldn't make a difference permission-wise. Confusing thing. – Izzy Jun 29 '13 at 11:47
  • yes it's confusing to me too :) as Izzy pointed out this script runs as root. – juniecho Jul 6 '13 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.