[SOLVED] Asterisk13 or 15 sqlite3 database problem

It seems to be crashing almost immediately. Is 64M enough RAM?

Hmm on my Router O2Box 6431 i have 64MB RAM to but i see it exist a Variable inside openwrt config:
CONFIG_ASTERISK13_LOW_MEMORY
these variable is
inside the Makefile of Asterisk-13

..
cd "$(PKG_BUILD_DIR)" && \
		./menuselect/menuselect \
			--disable BUILD_NATIVE \
			$(if $(CONFIG_ASTERISK13_LOW_MEMORY),--enable LOW_MEMORY) \
menuselect.makeopts
..

Can it happen that here exist are connection ?

The same story with NO extroot:

**root@OWrt-reset:~# opkg install asterisk15**
Installing asterisk15 (15.3.0-5) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/telephony/asterisk15_15.3.0-5_armeb_xscale.ipk
Installing uclibcxx (0.2.4-3) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/base/uclibcxx_0.2.4-3_armeb_xscale.ipk
Installing jansson (2.10-1) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/packages/jansson_2.10-1_armeb_xscale.ipk
Installing libcap (2.25-2) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/packages/libcap_2.25-2_armeb_xscale.ipk
Installing terminfo (6.1-1) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/base/terminfo_6.1-1_armeb_xscale.ipk
Installing libncurses (6.1-1) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/base/libncurses_6.1-1_armeb_xscale.ipk
Installing libedit (20170329-3.1-1) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/packages/libedit_20170329-3.1-1_armeb_xscale.ipk
Installing zlib (1.2.11-2) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/base/zlib_1.2.11-2_armeb_xscale.ipk
Installing libopenssl (1.0.2r-1) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/base/libopenssl_1.0.2r-1_armeb_xscale.ipk
Installing libsqlite3 (3260000-2) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/packages/libsqlite3_3260000-2_armeb_xscale.ipk
Installing librt (1.1.19-1) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/targets/ixp4xx/generic/packages/librt_1.1.19-1_armeb_xscale.ipk
Installing libuuid (2.32-2) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/base/libuuid_2.32-2_armeb_xscale.ipk
Installing libxml2 (2.9.8-4) to root...
Downloading http://downloads.openwrt.org/releases/18.06.2/packages/armeb_xscale/packages/libxml2_2.9.8-4_armeb_xscale.ipk
Configuring terminfo.
Configuring uclibcxx.
Configuring jansson.
Configuring libcap.
Configuring libncurses.
Configuring libedit.
Configuring zlib.
Configuring libopenssl.
Configuring libsqlite3.
Configuring librt.
Configuring libuuid.
Configuring libxml2.
Configuring asterisk15.
**root@OWrt-reset:~# asterisk -cvvvvv**
Asterisk 15.3.0, Copyright (C) 1999 - 2016, Digium, Inc. and others.
Created by Mark Spencer <markster@digium.com>
Asterisk comes with ABSOLUTELY NO WARRANTY; type 'core show warranty' for details.
This is free software, with components licensed under the GNU General Public
License version 2 and other licenses; you are welcome to redistribute it under
certain conditions. Type 'core show license' for details.
=========================================================================
[ Initializing Custom Configuration Options ]
Couldn't prepare statement 'CREATE TABLE IF NOT EXISTS astdb(key VARCHAR(256), value VARCHAR(256), PRIMARY KEY(key))': malformed database schema (?)
Couldn't create astdb table: malformed database schema (?)
ASTdb initialization failed.  ASTERISK EXITING!
**root@OWrt-reset:~# /etc/init.d/asterisk enable**
**root@OWrt-reset:~# /etc/init.d/asterisk start**
**root@OWrt-reset:~# asterisk -cvvvvv**
Asterisk 15.3.0, Copyright (C) 1999 - 2016, Digium, Inc. and others.
Created by Mark Spencer <markster@digium.com>
Asterisk comes with ABSOLUTELY NO WARRANTY; type 'core show warranty' for details.
This is free software, with components licensed under the GNU General Public
License version 2 and other licenses; you are welcome to redistribute it under
certain conditions. Type 'core show license' for details.
=========================================================================
[ Initializing Custom Configuration Options ]
Couldn't prepare statement 'CREATE TABLE IF NOT EXISTS astdb(key VARCHAR(256), value VARCHAR(256), PRIMARY KEY(key))': malformed database schema (?)
Couldn't create astdb table: malformed database schema (?)
ASTdb initialization failed.  ASTERISK EXITING!
root@OWrt-reset:~#

**root@OWrt-reset:~# df -h**
Filesystem                Size      Used Available Use% Mounted on
/dev/root                 2.3M      2.3M         0 100% /rom
tmpfs                    29.8M      1.0M     28.8M   3% /tmp
/dev/mtdblock3           12.0M      5.4M      6.6M  45% /overlay
overlayfs:/overlay       12.0M      5.4M      6.6M  45% /
tmpfs                   512.0K         0    512.0K   0% /dev
overlayfs:/overlay       12.0M      5.4M      6.6M  45% /tmp/cproot
**root@OWrt-reset:~# free**
             total       used       free     shared    buffers     cached
Mem:         61120      38192      22928       1052       1864      18068
-/+ buffers/cache:      18260      42860
Swap:       102396          0     102396

I have also swap partition.

Does sqlite work fine on its own?

opkg install sqlite3-cli
<snip>
root@hank2:~# sqlite3 /tmp/company_test.sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
sqlite> .q
root@hank2:~# sqlite3 /tmp/company_test.sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> SELECT * FROM COMPANY;
1|Paul|32|California|20000.0
2|Allen|25|Texas|15000.0
sqlite> .q
root@hank2:~#

And what happens if you add another table, the one Asterisk has problems creating?

root@hank2:~# sqlite3 /tmp/company_test.sqlite3 
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .tables
COMPANY
sqlite> CREATE TABLE IF NOT EXISTS astdb(key VARCHAR(256), value VARCHAR(256), PRIMARY KEY(key));

sqlite> .tables
COMPANY  astdb  
sqlite> .q
root@hank2:~# sqlite3 /tmp/company_test.sqlite3 
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .tables
COMPANY  astdb  
sqlite> .q
root@hank2:~#

sqlite that was installed, works OK. (not sure about asterisk built-in sqltite)

root@OpenWrt-16:~# sqlite3 /var/lib/asterisk/astdb.sqlite3
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
Error: malformed database schema (?)
sqlite>

Hello,

I'm not sure I understand you correctly. Judging by your output the table creation fails with "malformed database schema (?)". The same error is printed by Asterisk. This error comes from libsqlite3. Both the command line sqlite3 tool and Asterisk link to libsqlite3.

So libsqlite3 seems to be the common denominator. It looks like the problem is not with Asterisk but with libsqlite3. Do you agree?

Just a hunch, but can you try the following? Open "feeds/packages/libs/sqlite3/Makefile" and comment out "PKG_FIXUP:=autoreconf" by putting a '#' in front of it. The recompile sqlite3:

make package/sqlite3/{clean,compile} V=s

Then copy "bin/packages/mipsel_74kc/packages/libsqlite3_3260000-2_mipsel_74kc.ipk" to your device and reinstall the package:

opkg install --force-reinstall libsqlite3_3260000-2_mipsel_74kc.ipk

Then try again.

Yes, it's the same error. I wrote about it above:

I was not sure, whether it's a DB file or sqlte3 problem. I will definitely follow your advice and re-compile sqlite to exclude other reasons of the problem. Will post results. And THANK YOU!

Compilation halts with following:

cp -fpR /home/aaa/O1/openwrt/staging_dir/toolchain-armeb_xscale_gcc-7.3.0_musl/lib/ld-musl-*.so* /home/aaa/O1/openwrt/build_dir/target-armeb_xscale_musl/toolchain/.pkgdir/libc/lib/
cp: cannot stat '/home/aaa/O1/openwrt/staging_dir/toolchain-armeb_xscale_gcc-7.3.0_musl/lib/ld-musl-*.so*': No such file or directory
Makefile:618: recipe for target '/home/aaa/O1/openwrt/build_dir/target-armeb_xscale_musl/toolchain/.pkgdir/libc.installed' failed
make[2]: *** [/home/aaa/O1/openwrt/build_dir/target-armeb_xscale_musl/toolchain/.pkgdir/libc.installed] Error 1
make[2]: Leaving directory '/home/aaa/O1/openwrt/package/libs/toolchain'
time: package/libs/toolchain/compile#0.03#0.02#0.25
package/Makefile:107: recipe for target 'package/libs/toolchain/compile' failed
make[1]: *** [package/libs/toolchain/compile] Error 2
make[1]: Leaving directory '/home/aaa/O1/openwrt'
/home/aaa/O1/openwrt/include/toplevel.mk:216: recipe for target 'package/sqlite3/compile' failed
make: *** [package/sqlite3/compile] Error 2

That's unrelated to the little change I proposed. Looks like something wrong with your build setup.

I've built the package with the change fine in 18.06 brcm47xx SDK.

Kind regards,
Seb

Hello Plonk34,

Could you please let me know the /dev/vmmc* permissions without any modifications? Like, what are they by default?

ls -lh /dev/vmmc*

Thanks!

That's it, I'm giving up... Cloning with git clone https://github.com/openwrt/openwrt.git -b v18.06.2 and it's a nightmare as my internet service provider blocks github.com.
Downloading and feeds update takes SO MUCH TIME... And I did all this 3 or 4 times during last 2 days.
PLUS I tried building environment in DEB-8, 9 AND now last Ubuntu revision - all fresh installs.

Every time I get Error2.

I will build the environment at last, but I need to know if it's really worth it, as I still don't know if that change in Makefile really works for me.

If anyone has working environment - could anyone compile sqlite3 package for me please?? (Intel ixp4xx - Generic - USR8200)

TNX in advance

I remember at one point asterisk was using actually the older sqlite 2 database format. see if you can open it's file under sqlite program rather than sqlite3

I tried this previously. sqlite can't read sqlite3 format.

yes my point is that asterisk may be creating sqlite2 format files which is why sqlite3 can't read them

blkid
mount
ls -lah /var/lib/asterisk/astdb.sqlite3

It's kind of clear that this "astdb.sqlite3" file, wherever it came from, is not a valid SQLite3 file.

Usually a SQL-based application will ship with a .sql file of text SQL commands that can be run through a CLI SQL client to prepare an empty database.

What was that for? We know that file exists. Exhaust is:

-rw-r--r--    1 root     root       12.0K May 14 10:06 /var/lib/asterisk/astdb.sqlite3

OK, guys look, I really need to resolve this. One way is to try what Micmac suggested, but I can't do it until I move to another country due to ip issues.
If you have ready build system - it's a 3 minutes to compile new sqlite3 package with

#PKG_FIXUP:=autoreconf
Will appreciate if someone could do it for me.

OK guys,

the recipe

didn't work.

But Seb wrote a patch and then compiled sqlite3. Now ALL IS UP. ))