[Home]

Summary:ASTERISK-18174: Couldn't execute statment: SQL logic error or missing database
Reporter:Dennis DeDonatis (dennisd)Labels:
Date Opened:2011-07-26 09:19:53Date Closed:2011-10-10 18:18:29
Priority:MinorRegression?
Status:Closed/CompleteComponents:General
Versions:10.0.0-beta1 Frequency of
Occurrence		Constant
Related
Issues:
Environment:Fedora 14 - 2.6.35.13-92.fc14.x86_64 sqlite RPM - 3.6.23.1 1.fc14Attachments:
Description:From what I could see reading all the upgrade/changes docs, it looks like going to Asterisk-10 beta 1 would only break all my ConfBrdige stuff, so I figured I'd try it and see what happened. I didn't change anything from 1.8.5.0. Well, here's what happened.

Although astdb2sqlite3 was selected in make menuselect, I got this:

[2011-07-24 15:20:09.476] ERROR[12066] db.c: *** Database conversion failed!
[2011-07-24 15:20:09.476] ERROR[12066] db.c: *** Asterisk now uses SQLite3 for its internal
[2011-07-24 15:20:09.476] ERROR[12066] db.c: *** database. Conversion from the old astdb
[2011-07-24 15:20:09.476] ERROR[12066] db.c: *** failed. Most likely the astdb2sqlite3 utility
[2011-07-24 15:20:09.477] ERROR[12066] db.c: *** was not selected for build. To convert the
[2011-07-24 15:20:09.477] ERROR[12066] db.c: *** old astdb, please delete '/var/lib/asterisk/astdb.sqlite3'
[2011-07-24 15:20:09.477] ERROR[12066] db.c: *** and re-run 'make menuselect' and select astdb2sqlite3
[2011-07-24 15:20:09.477] ERROR[12066] db.c: *** in the Utilities section, then 'make && make install'.
[2011-07-24 15:20:14.477] WARNING[12066] db.c: Unable to open Asterisk database '/var/lib/asterisk/astdb.sqlite3': unable to open database file

So I manually ran astdb2sqlite3 against astdb. I make VERY little use of astdb, it contains my phone registrations, one device state and the last incoming caller number for one line. That's it. I can read the astdb.sqlite3 with sqlite3 just fine (and from the asterisk CLI).

But, I get a LOT of these messages:

[2011-07-24 15:36:01.955] WARNING[30154] db.c: Couldn't execute statment: SQL logic error or missing database
[2011-07-24 15:36:01.956] VERBOSE[30154] chan_sip.c: Saved useragent "Grandstream GXW-4008 V1.3A 1.0.5.5" for peer 2026
[2011-07-24 15:36:01.968] NOTICE[30154] chan_sip.c: Peer '2026' is now Reachable. (11ms / 700ms)
[2011-07-24 15:36:04.391] NOTICE[30154] chan_sip.c: Peer 'rapidvox' is now Reachable. (27ms / 700ms)
[2011-07-24 15:36:05.496] WARNING[30154] db.c: Couldn't execute statment: SQL logic error or missing database
[2011-07-24 15:36:05.496] VERBOSE[30154] chan_sip.c: Saved useragent "Grandstream GXW4104 (HW 1.0, Ch:8) 1.3.4.10" for peer 2201
[2011-07-24 15:36:05.499] NOTICE[30154] chan_sip.c: Peer '2201' is now Reachable. (2ms / 700ms)
[2011-07-24 15:36:06.211] WARNING[30154] db.c: Couldn't execute statment: SQL logic error or missing database
[2011-07-24 15:36:06.212] VERBOSE[30154] chan_sip.c: Saved useragent "Grandstream GXV3000 1.2.3.7" for peer 2020
[2011-07-24 15:36:06.214] NOTICE[30154] chan_sip.c: Peer '2020' is now Reachable. (2ms / 700ms)
[2011-07-24 15:36:06.685] WARNING[30154] db.c: Couldn't execute statment: SQL logic error or missing database
[2011-07-24 15:36:06.686] VERBOSE[30154] chan_sip.c: Saved useragent "Grandstream GXW4104 (HW 1.0, Ch:11) 1.3.4.10" for peer 2204
[2011-07-24 15:36:06.688] NOTICE[30154] chan_sip.c: Peer '2204' is now Reachable. (2ms / 700ms)
[2011-07-24 15:36:07.624] WARNING[30154] db.c: Couldn't execute statment: SQL logic error or missing database
[2011-07-24 15:36:07.625] VERBOSE[30154] chan_sip.c: Saved useragent "Linksys/SPA962-6.1.5(a)" for peer 2022
[2011-07-24 15:36:07.638] NOTICE[30154] chan_sip.c: Peer '2022' is now Reachable. (12ms / 700ms)
[2011-07-24 15:36:09.477] WARNING[30154] db.c: Couldn't execute statment: SQL logic error or missing database
[2011-07-24 15:36:09.478] VERBOSE[30154] chan_sip.c: Saved useragent "Linksys/SPA3102-5.1.7(GW)" for peer 2205

I even did a chmod 777 on /var/lib/asterisk/astdb.sqlite3 to see if that was the problem. It wasn't.

I'm running Fedora 14. My sqlite rpm is version 3.6.23.1 Release 1.fc14.

I did go back and enable MeetMe and some other modules and that didn't make any difference (although I'm sure it would help if I tried to go into a MeetMe conf room).

Please let me know what other information you would like and if there is anything you want me to try.
Comments:By: Dennis DeDonatis (dennisd) 2011-08-18 09:44:41.880-0500

I'm not running Asterisk as root.

This problem is caused by asterisk not owning /var/lib/asterisk and trying to create astdb.sqlite3-journal in /var/lib/asterisk.

Is there any way to specify where astdb.sqlite3-journal is placed (maybe /tmp)?


By: Dennis DeDonatis (dennisd) 2011-08-18 10:02:11.283-0500

http://www.sqlite.org/fileformat2.html#rollbackjournal

"The rollback journal file is always located in the same directory as the database file"

It looks like I can change astdbdir='${astvarlibdir}' in configure, but is this the best option?

By: Dennis DeDonatis (dennisd) 2011-08-18 10:47:00.399-0500

I changed my build script to include

sed -i "s/^astdbdir='\${astvarlibdir}'$/astdbdir='\${astvarlibdir}\/db'/" configure

just before it runs ./configure, so it works for me, now.  Is this the best way?
By: Terry Wilson (twilson) 2011-09-01 16:40:51.597-0500

It seems like the real problem is that asterisk should be given write access to the astdbdir/astvarlibdir. There are other functions in asterisk that would write to that directory. Is there any reason that making the asterisk user the owner of that directory is not possible? It seems like the non-root Asterisk tutorials I've read have that as a step.
By: Dennis DeDonatis (dennisd) 2011-09-02 11:37:23.822-0500

I haven't noticed any other functions in Asterisk trying to write to /var/lib/asterisk, but that definitely doesn't mean there aren't any.  

I have also seen that the non-root Asterisk tutorials usually, if not always, grant ownership/write access to the asterisk user for the astvarlibdir directory.

I just prefer Asterisk to not be able to modify anything in my agi-bin, sounds, moh or static-http directories.  I try to give anything, including Asterisk, the least amount of rights possible.  For "bsd" OSes, configure puts the db in its own directory.  Moving the db to its own directory (and giving asterisk write permissions to the astdbdir) in Linux solved this problem for me.

I fully admit that I may be overly restrictive with regard to directory permissions.  I ran into this unexpected issue going from 1.8 to 10 that wasn't obviously related (to me at least) to needing write access to the astdbdir.  If nothing else, maybe just a check for the ability to write to astdbdir with an error message (and possibly exiting Asterisk) is needed to catch anyone else that might have this problem going from 1.8 to 10.



By: Leif Madsen (lmadsen) 2011-09-21 08:39:01.247-0500

Could you provide a copy of your AstDB file (/var/lib/asterisk/astdb) so that we can take a look at why the conversion may not be happening cleanly? I believe that would be useful in making the conversion utility more robust.

Thanks!
By: Dennis DeDonatis (dennisd) 2011-09-21 08:57:00.966-0500

The problem was that I'm not running asterisk as root and sqlite wants to write the rollback journal file in the /var/lib/asterisk directory (always the same directory as the sqlite database file) and it couldn't because I only give the asterisk user read and execute to the directory itself.

When I manually ran the conversion (as root), the conversion worked fine, so I don't think it's anything in my astdb (I only have 2 values that aren't the regular registrations).  I have "/CustomDevstate/SCID" and "/lastcaller/number" and that's it.  I can still send you a copy of my old AstDB file if you want it.  I've been running 10-beta1 since August.

My recommendation is that when starting anything that would ever write to an sqlite database (Asterisk itself and the conversion stuff) you should check to see if you can create a file in the database directory first and if not, immediately die with an error message saying you can't write to the database directory and write access is needed for sqlite rollback journals.  That would catch this issue and let the person know what the real problem is right away.


By: Terry Wilson (twilson) 2011-10-10 18:18:29.826-0500

Updated the warning message to include information about verifying that the database directory has write permissions.