[General] Indexing problem with sqlite3

Discussion:

Teijo

2017-03-22 11:44:09 UTC

Hello,

I have installed Mnogosearch 3.4.1 from source both to Ubuntu 16.04 and
Debian Jessie.

In Ubuntu I cannot use Mysql as database because there seem to be some
compatibility issues with Mysql 5.7. In Jessie where Mysql version is
5.5x there are no such problems.

I thought to use Sqlite3 in Ubuntu. Database setup goes without errors
with indexer --create. But when I try to make index with simply typing
indexer, I get similar to the following:

[33572]{--} indexer from mnogosearch-3.4.1-sqlite3 started with
'/usr/local/mnogosearch/etc/indexer.conf'
[33572]{01} Error: 'DB: sqlite3 driver: (19) UNIQUE constraint failed:
url.url'

There seem to be similar problems with Sqlite3 in Jessie as well.

I am not familiar with Mnogosearch and Sqlite3 so is there something I
have missed when setting up the environment? Only changes I have made in
indexer.conf are Dbaddress and server definitions. Dbaddress is just
that it's in the example of Sqlite3 definition in indexer.conf-dist.

Best regards,

Teijo

Alexander Barkov

2017-03-22 14:39:19 UTC

Permalink

Hello Teijo,

Post by Teijo
Hello,
I have installed Mnogosearch 3.4.1 from source both to Ubuntu 16.04 and
Debian Jessie.
In Ubuntu I cannot use Mysql as database because there seem to be some
compatibility issues with Mysql 5.7. In Jessie where Mysql version is
5.5x there are no such problems.
I thought to use Sqlite3 in Ubuntu. Database setup goes without errors
with indexer --create. But when I try to make index with simply typing
[33572]{--} indexer from mnogosearch-3.4.1-sqlite3 started with
'/usr/local/mnogosearch/etc/indexer.conf'
url.url'
There seem to be similar problems with Sqlite3 in Jessie as well.
I am not familiar with Mnogosearch and Sqlite3 so is there something I
have missed when setting up the environment? Only changes I have made in
indexer.conf are Dbaddress and server definitions. Dbaddress is just
that it's in the example of Sqlite3 definition in indexer.conf-dist.

Which exact version of SQLite are you using?

Can you please send your indexer.conf and the output for:

./indexer --sqlmon --exec="SELECT rec_id, url FROM url"

to ***@mnogosearch.org

Thanks.

Post by Teijo
Best regards,
Teijo
_______________________________________________
General mailing list
http://lists.mnogosearch.org/listinfo/general

Alexander Barkov

2017-03-22 14:52:50 UTC

Permalink

Hello Teijo,

SQLite changed the error message in one of the recent releases,
from "unique" in lower case to "UNIQUE" in upper case.

Please apply this patch to src/sql-sqlite.c:

- if (!strstr(db->errstr,"unique"))
+ if (!strstr(db->errstr,"unique") && !strstr(db->errstr,"UNIQUE"))

Post by Alexander Barkov
Hello Teijo,

Which exact version of SQLite are you using?
./indexer --sqlmon --exec="SELECT rec_id, url FROM url"
Thanks.

Post by Teijo
Best regards,
Teijo
_______________________________________________
General mailing list
http://lists.mnogosearch.org/listinfo/general

_______________________________________________
General mailing list
http://lists.mnogosearch.org/listinfo/general

Teijo

2017-03-22 16:51:31 UTC

Permalink

Hello,

Unfortunately patch did not solve the problem.

As to SQLite3 versions, Ubuntu 16.04 it is
SQLite version 3.11.0 2016-02-15 17:29:24
and in Jessie
SQLite version 3.8.7.1 2014-10-29 13:59:56

Best regards,

Teijo

Post by Alexander Barkov
Hello Teijo,
SQLite changed the error message in one of the recent releases,
from "unique" in lower case to "UNIQUE" in upper case.
- if (!strstr(db->errstr,"unique"))
+ if (!strstr(db->errstr,"unique") && !strstr(db->errstr,"UNIQUE"))

Post by Alexander Barkov
Hello Teijo,

Which exact version of SQLite are you using?
./indexer --sqlmon --exec="SELECT rec_id, url FROM url"
Thanks.

Post by Teijo
Best regards,
Teijo
_______________________________________________
General mailing list
http://lists.mnogosearch.org/listinfo/general

_______________________________________________
General mailing list
http://lists.mnogosearch.org/listinfo/general

Alexander Barkov

2017-03-23 15:23:42 UTC

Permalink

Hello,

Post by Teijo
Hello,
Unfortunately patch did not solve the problem.
As to SQLite3 versions, Ubuntu 16.04 it is
SQLite version 3.11.0 2016-02-15 17:29:24
and in Jessie
SQLite version 3.8.7.1 2014-10-29 13:59:56

There are two similar places in sql-sqlite.c

Please make sure to fix the SQLite3 (rather than SQLite2) code branch:

case SQLITE_ERROR:
sqlite3_finalize(pStmt);
udm_snprintf(db->errstr, sizeof(db->errstr),
"sqlite3 driver: (%d) %s",
sqlite3_errcode(UdmSQLite3Conn(db)),
sqlite3_errmsg(UdmSQLite3Conn(db)));
if (!strstr(db->errstr,"unique") && !strstr(db->errstr,"UNIQUE"))
{
UdmSetErrorCode(db, 1);
return UDM_ERROR;
}
return UDM_OK;
break;

Post by Teijo
Best regards,
Teijo

Post by Alexander Barkov
Hello Teijo,

Which exact version of SQLite are you using?
./indexer --sqlmon --exec="SELECT rec_id, url FROM url"
Thanks.

Post by Teijo
Best regards,
Teijo
_______________________________________________
General mailing list
http://lists.mnogosearch.org/listinfo/general

_______________________________________________
General mailing list
http://lists.mnogosearch.org/listinfo/general

Teijo

2017-03-23 20:58:57 UTC

Permalink

Hello,

You are correct. I made the change to the wrong branch. The patch fixed
the problem at least in Jessie, and I suppose that is the case with
Ubuntu as well. Thank you very much!

Best regards,

Teijo

Post by Teijo
Hello,

There are two similar places in sql-sqlite.c
sqlite3_finalize(pStmt);
udm_snprintf(db->errstr, sizeof(db->errstr),
"sqlite3 driver: (%d) %s",
sqlite3_errcode(UdmSQLite3Conn(db)),
sqlite3_errmsg(UdmSQLite3Conn(db)));
if (!strstr(db->errstr,"unique") && !strstr(db->errstr,"UNIQUE"))
{
UdmSetErrorCode(db, 1);
return UDM_ERROR;
}
return UDM_OK;
break;

Post by Teijo
Best regards,
Teijo

Post by Alexander Barkov
Hello Teijo,

Which exact version of SQLite are you using?
./indexer --sqlmon --exec="SELECT rec_id, url FROM url"
Thanks.

Post by Teijo
Best regards,
Teijo
_______________________________________________
General mailing list
http://lists.mnogosearch.org/listinfo/general

_______________________________________________
General mailing list
http://lists.mnogosearch.org/listinfo/general