JSON Listener: "unknown top level operator: $sql",
Hello everybody,
I wonder if you could give me a light on this subject:
I'm trying to execute an sql command throu mongoDB Api but get the following
error "errmsg" : "unknown top level operator: $sql".
It seems as if the api would not recognize the wiredListener.
Here are the details about the configuration:
OS: Ret HAT ENTERPRISE LINUX 6 64 bits
IDS: 12.10.FC5
MONGO SERVER: mongo 3.2.6 (Community Edition)
1. Starting mongoDB Server. Apparently succesfuly started.
[informix@aldextra3 db]$ mongod
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] MongoDB starting :
pid=34203 port=27017 dbpath=/data/db 64-bit host=aldextra3.localdomain
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] db version v3.2.6
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] git version:
05552b562c7a0b3143a729aaa0838e558dc49b25
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] OpenSSL version:
OpenSSL 1.0.1e-fips 11 Feb 2013
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] allocator: tcmalloc
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] modules: none
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] build environment:
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] distmod: rhel62
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] distarch: x86_64
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] target_arch: x86_64
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] options: {}
2016-05-12T19:36:44.575+0200 I - [initandlisten] Detected data files in
/data/db created by the 'wiredTiger' storage engine, so setting the active
storage engine to 'wiredTiger'.
2016-05-12T19:36:44.575+0200 I STORAGE [initandlisten] wiredtiger_open config:
create,cache_size=4G,session_max=20000,eviction=(threads_max=4),config_base=fals
e,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snapp
y),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),stati
stics_log=(wait=0),
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** WARNING:
/proc/sys/vm/overcommit_memory is 2
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** Journaling works
best with it set to 0 or 1
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** WARNING:
/sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** We suggest setting
it to 'never'
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** WARNING:
/sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** We suggest setting
it to 'never'
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]
2016-05-12T19:36:45.155+0200 I FTDC [initandlisten] Initializing full-time
diagnostic data capture with directory '/data/db/diagnostic.data'
2016-05-12T19:36:45.155+0200 I NETWORK [HostnameCanonicalizationWorker]
Starting hostname canonicalization worker
2016-05-12T19:36:45.156+0200 I NETWORK [initandlisten] waiting for connections
on port 27017
2. Vi sqlhosts
ol_informix1210 onsoctcp aldextra3.localdomain ol_informix1210
dr_informix1210 drsoctcp aldextra3.localdomain dr_informix1210
lo_informix1210 onsoctcp 127.0.0.1 lo_informix1210
3. vi jsonListener.properties
listener.port=27017
url=jdbc:informix-sqli://localhost:24040/sysmaster:INFORMIXSERVER=lo_informix121
0;USER=ifxjson;PASSWORD=(#DetNU8Zif
security.sql.passthrough=true
4. execute function task('start json listener')
5. vi ol_informix1210_jsonListener.log. Apparently succesfuly started.
{ date: "2016-05-12 19:13:07.546" , thread: "MongoListener-1" , level: "INFO "
, source: c.i.nosql.server.mongo.MongoListener , message: "JSON server
listening on port: 27017, localhost/0:0:0:0:0:0:0:1" }
6. Starting mongoDB Client
mongo localhost:27017
7. Using the api:
>use stores_demo
switched to db stores_demo
> db.getCollection("$sql").find({ "$sql": "create table foo (c1 int)" })
Error: error: {
"waitedMS" : NumberLong(0),
"ok" : 0,
"errmsg" : "unknown top level operator: $sql",
"code" : 2
}
I'm sorry if this mail is too long, but I think all the information is worth
the trouble.
Thanks a lot,
OK, First if you are using Informix for storing and processing your
mongo/JSON data then you do not need the moongod daemon running at all. All
of the data and command processing is handled by Informix. Second you have
the MongoDB daemon (mongod) listening on the same port (27017) as the
Informix mongo wire listener so there is a good chance that your command
was processed by mongod and not by Informix. Mongod does not understand the
$sql method, that is an Informix extension to the Mongodb API. So, shutdown
mongod, then stop and restart the Informix wire listener and see if that
fixes the problem.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Thu, May 12, 2016 at 2:14 PM, JUAN ROCA <jluis.roca@hotmail.com> wrote:
> Hello everybody,
>
> I wonder if you could give me a light on this subject:
>
> I'm trying to execute an sql command throu mongoDB Api but get the
> following
> error "errmsg" : "unknown top level operator: $sql".
> It seems as if the api would not recognize the wiredListener.
>
> Here are the details about the configuration:
>
> OS: Ret HAT ENTERPRISE LINUX 6 64 bits
> IDS: 12.10.FC5
> MONGO SERVER: mongo 3.2.6 (Community Edition)
>
> 1. Starting mongoDB Server. Apparently succesfuly started.
>
> [informix@aldextra3 db]$ mongod
>
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] MongoDB starting :
> pid=34203 port=27017 dbpath=/data/db 64-bit host=aldextra3.localdomain
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] db version v3.2.6
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] git version:
> 05552b562c7a0b3143a729aaa0838e558dc49b25
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] OpenSSL version:
> OpenSSL 1.0.1e-fips 11 Feb 2013
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] allocator: tcmalloc
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] modules: none
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] build environment:
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] distmod: rhel62
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] distarch: x86_64
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] target_arch: x86_64
> 2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] options: {}
> 2016-05-12T19:36:44.575+0200 I - [initandlisten] Detected data files in
> /data/db created by the 'wiredTiger' storage engine, so setting the active
> storage engine to 'wiredTiger'.
> 2016-05-12T19:36:44.575+0200 I STORAGE [initandlisten] wiredtiger_open
> config:
>
>
create,cache_size=4G,session_max=20000,eviction=(threads_max=4),config_base=fals
e,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snapp
y),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),stati
stics_log=(wait=0),
> 2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]
> 2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** WARNING:
> /proc/sys/vm/overcommit_memory is 2
> 2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** Journaling works
> best with it set to 0 or 1
> 2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]
> 2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** WARNING:
> /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
> 2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** We suggest
> setting
> it to 'never'
> 2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]
> 2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** WARNING:
> /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
> 2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** We suggest
> setting
> it to 'never'
> 2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]
> 2016-05-12T19:36:45.155+0200 I FTDC [initandlisten] Initializing full-time
> diagnostic data capture with directory '/data/db/diagnostic.data'
> 2016-05-12T19:36:45.155+0200 I NETWORK [HostnameCanonicalizationWorker]
> Starting hostname canonicalization worker
> 2016-05-12T19:36:45.156+0200 I NETWORK [initandlisten] waiting for
> connections
> on port 27017
>
> 2. Vi sqlhosts
>
> ol_informix1210 onsoctcp aldextra3.localdomain ol_informix1210
> dr_informix1210 drsoctcp aldextra3.localdomain dr_informix1210
> lo_informix1210 onsoctcp 127.0.0.1 lo_informix1210>
> 3. vi jsonListener.properties
>
> listener.port=27017
>
>
>
url=jdbc:informix-sqli://localhost:24040/sysmaster:INFORMIXSERVER=lo_informix121
0;USER=ifxjson;PASSWORD=(#DetNU8Zif
> security.sql.passthrough=true
>
> 4. execute function task('start json listener')
>
> 5. vi ol_informix1210_jsonListener.log. Apparently succesfuly started.
>
> { date: "2016-05-12 19:13:07.546" , thread: "MongoListener-1" , level:
> "INFO "
> , source: c.i.nosql.server.mongo.MongoListener , message: "JSON server
> listening on port: 27017, localhost/0:0:0:0:0:0:0:1" }
>
> 6. Starting mongoDB Client
>
> mongo localhost:27017
>
> 7. Using the api:
>
> >use stores_demo
> switched to db stores_demo
>
> > db.getCollection("$sql").find({ "$sql": "create table foo (c1 int)" })
> Error: error: {
>
> "waitedMS" : NumberLong(0),
>
> "ok" : 0,
>
> "errmsg" : "unknown top level operator: $sql",
>
> "code" : 2
> }
>
> I'm sorry if this mail is too long, but I think all the information is
> worth
> the trouble.
>
> Thanks a lot,
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0149c0c40547eb0532a96eb4
Hi Juan,
I think the correct syntax should be:
db.getCollection("system.sql").find({ "$sql": "create table foo (c1 int)"=20
})
so "system.sql" as the pseudo collection name rather than $sql - the=20
$sql later will be the key in the query json doc.
HTH,
Andreas
From: "JUAN ROCA" <jluis.roca@hotmail.com>
To: ids@iiug.org
Date: 12.05.2016 20:15
Subject: JSON Listener: "unknown top level operator: $sql", [37103]
Sent by: ids-bounces@iiug.org
Hello everybody,=20
I wonder if you could give me a light on this subject:=20
I'm trying to execute an sql command throu mongoDB Api but get the=20
following=20
error "errmsg" : "unknown top level operator: $sql".=20
It seems as if the api would not recognize the wiredListener.=20
Here are the details about the configuration:=20
OS: Ret HAT ENTERPRISE LINUX 6 64 bits=20
IDS: 12.10.FC5=20
MONGO SERVER: mongo 3.2.6 (Community Edition)=20
1. Starting mongoDB Server. Apparently succesfuly started.=20
[informix@aldextra3 db]$ mongod=20
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] MongoDB starting :=20
pid=3D34203 port=3D27017 dbpath=3D/data/db 64-bit host=3Daldextra3.localdom=
ain=20
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] db version v3.2.6=20
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] git version:=20
05552b562c7a0b3143a729aaa0838e558dc49b25=20
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] OpenSSL version:=20
OpenSSL 1.0.1e-fips 11 Feb 2013=20
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] allocator: tcmalloc =
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] modules: none=20
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] build environment:=20
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] distmod: rhel62=20
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] distarch: x86=5F64=20
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] target=5Farch: x86=
=5F64=20
2016-05-12T19:36:44.537+0200 I CONTROL [initandlisten] options: {}=20
2016-05-12T19:36:44.575+0200 I - [initandlisten] Detected data files in=20
/data/db created by the 'wiredTiger' storage engine, so setting the active =
storage engine to 'wiredTiger'.=20
2016-05-12T19:36:44.575+0200 I STORAGE [initandlisten] wiredtiger=5Fopen=20
config:=20
create,cache=5Fsize=3D4G,session=5Fmax=3D20000,eviction=3D(threads=5Fmax=3D=
4),config=5Fbase=3Dfalse,statistics=3D(fast),log=3D(enabled=3Dtrue,archive=
=3Dtrue,path=3Djournal,compressor=3Dsnappy),file=5Fmanager=3D(close=5Fidle=
=5Ftime=3D100000),checkpoint=3D(wait=3D60,log=5Fsize=3D2GB),statistics=5Flo=
g=3D(wait=3D0),=20
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]=20
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** WARNING:=20
/proc/sys/vm/overcommit=5Fmemory is 2=20
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** Journaling works =
best with it set to 0 or 1=20
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]=20
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** WARNING:=20
/sys/kernel/mm/transparent=5Fhugepage/enabled is 'always'.=20
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** We suggest=20
setting=20
it to 'never'=20
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]=20
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** WARNING:=20
/sys/kernel/mm/transparent=5Fhugepage/defrag is 'always'.=20
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten] ** We suggest=20
setting=20
it to 'never'=20
2016-05-12T19:36:45.153+0200 I CONTROL [initandlisten]=20
2016-05-12T19:36:45.155+0200 I FTDC [initandlisten] Initializing full-time =
diagnostic data capture with directory '/data/db/diagnostic.data'=20
2016-05-12T19:36:45.155+0200 I NETWORK [HostnameCanonicalizationWorker]=20
Starting hostname canonicalization worker=20
2016-05-12T19:36:45.156+0200 I NETWORK [initandlisten] waiting for=20
connections=20
on port 27017=20
2. Vi sqlhosts=20
ol=5Finformix1210 onsoctcp aldextra3.localdomain ol=5Finformix1210=20
dr=5Finformix1210 drsoctcp aldextra3.localdomain dr=5Finformix1210=20
lo=5Finformix1210 onsoctcp 127.0.0.1 lo=5Finformix1210=20
3. vi jsonListener.properties=20
listener.port=3D27017=20
url=3Djdbc:informix-sqli://localhost:24040/sysmaster:INFORMIXSERVER=3Dlo=5F=
informix1210;USER=3Difxjson;PASSWORD=3D(#DetNU8Zif=20
security.sql.passthrough=3Dtrue=20
4. execute function task('start json listener')=20
5. vi ol=5Finformix1210=5FjsonListener.log. Apparently succesfuly started. =
{ date: "2016-05-12 19:13:07.546" , thread: "MongoListener-1" , level:=20
"INFO "=20
, source: c.i.nosql.server.mongo.MongoListener , message: "JSON server=20
listening on port: 27017, localhost/0:0:0:0:0:0:0:1" }=20
6. Starting mongoDB Client=20
mongo localhost:27017=20
7. Using the api:=20
>use stores=5Fdemo=20
switched to db stores=5Fdemo=20
> db.getCollection("$sql").find({ "$sql": "create table foo (c1 int)" })=20
Error: error: {=20
"waitedMS" : NumberLong(0),=20
"ok" : 0,=20
"errmsg" : "unknown top level operator: $sql",=20
"code" : 2=20
}=20
I'm sorry if this mail is too long, but I think all the information is=20
worth=20
the trouble.=20
Thanks a lot,=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20