Re: [SR-Users] kamcli db sqlite / posgresql error

Hi Daniel On Thu, Apr 22, 2021 at 8:57 AM Daniel-Constantin Mierla &lt;miconda(a)gmail.com <mailto:miconda@gmail.com>> wrote: Hello, at least some of the new errors are not the same, they are related to unique key conflict, see the bottom of the traces: sqlite3.IntegrityError: UNIQUE constraint failed: subscriber.username, subscriber.domain sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: subscriber.username, subscriber.domain [SQL: insert into subscriber (username, domain, password, ha1, ha1b) values ('test', 'sip.test.dev <http://sip.test.dev>', 'passwordtest', '96a1efd95bcd6e8e573d07ebdacab623', '025f1dc363fa8c1ce1d7f2069d94ed0f')] Regarding the subscriber show command, do you have any outstyle configuration option set? What is the version of your tabulate package? sqlite> .show         echo: off          eqp: off      explain: auto      headers: off         mode: list    nullvalue: ""       output: stdout colseparator: "|" rowseparator: "\n"        stats: off        width:     filename: /etc/kamailio/kamailio.db (venv) kamailio-BT:/usr/src/kamclienv # pip3 show tabulate Name: tabulate Version: 0.8.9 Summary: Pretty-print tabular data Home-page: https://github.com/astanin/python-tabulate <https://github.com/astanin/python-tabulate> Author: Sergey Astanin Author-email: s.astanin(a)gmail.com <mailto:s.astanin@gmail.com> License: MIT Location: /usr/src/kamclienv/venv/lib/python3.7/site-packages Requires: Required-by: kamcli regards   Cheers, Daniel On 21.04.21 23:24, beer Ll wrote: > Hi Daniel > many thanks for yours commits in git  kamcli. > > I try the new version but my errors are the same > > (venv) kamailio-BT:/usr/src/kamclienv/kamcli # kamcli > -c /etc/kamcli/kamcli.ini.sqlite subscriber show > Traceback (most recent call last): >   File "/usr/src/kamclienv/venv/bin/kamcli", line 33, > in <module> >     sys.exit(load_entry_point('kamcli', > 'console_scripts', 'kamcli')()) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 829, in __call__ >     return self.main(*args, **kwargs) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 782, in main >     rv = self.invoke(ctx) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 1259, in invoke >     return > _process_result(sub_ctx.command.invoke(sub_ctx)) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 1259, in invoke >     return > _process_result(sub_ctx.command.invoke(sub_ctx)) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 1066, in invoke >     return ctx.invoke(self.callback, **ctx.params) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 610, in invoke >     return callback(*args, **kwargs) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/decorators.py", > line 73, in new_func >     return ctx.invoke(f, obj, *args, **kwargs) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 610, in invoke >     return callback(*args, **kwargs) >   File > "/usr/src/kamclienv/kamcli/kamcli/commands/cmd_subscriber.py", > line 235, in subscriber_show >     ioutils_dbres_print(ctx, oformat, ostyle, res) >   File "/usr/src/kamclienv/kamcli/kamcli/ioutils.py", > line 70, in ioutils_dbres_print >     gstring = tabulate(allrows, headers=res.keys(), > tablefmt=ostyle) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/tabulate.py", > line 1529, in tabulate >     tabular_data, headers, showindex=showindex >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/tabulate.py", > line 1162, in _normalize_tabular_data >     "headers for a list of dicts is not a dict or a > keyword" > ValueError: headers for a list of dicts is not a dict > or a keyword > (venv) kamailio-BT:/usr/src/kamclienv/kamcli # kamcli > -c /etc/kamcli/kamcli.ini.sqlite subscriber add test > passwordtest > Traceback (most recent call last): >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", > line 1706, in _execute_context >     cursor, statement, parameters, context >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", > line 716, in do_execute >     cursor.execute(statement, parameters) > sqlite3.IntegrityError: UNIQUE constraint failed: > subscriber.username, subscriber.domain > > The above exception was the direct cause of the > following exception: > > Traceback (most recent call last): >   File "/usr/src/kamclienv/venv/bin/kamcli", line 33, > in <module> >     sys.exit(load_entry_point('kamcli', > 'console_scripts', 'kamcli')()) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 829, in __call__ >     return self.main(*args, **kwargs) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 782, in main >     rv = self.invoke(ctx) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 1259, in invoke >     return > _process_result(sub_ctx.command.invoke(sub_ctx)) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 1259, in invoke >     return > _process_result(sub_ctx.command.invoke(sub_ctx)) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 1066, in invoke >     return ctx.invoke(self.callback, **ctx.params) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 610, in invoke >     return callback(*args, **kwargs) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/decorators.py", > line 73, in new_func >     return ctx.invoke(f, obj, *args, **kwargs) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/click/core.py", > line 610, in invoke >     return callback(*args, **kwargs) >   File > "/usr/src/kamclienv/kamcli/kamcli/commands/cmd_subscriber.py", > line 69, in subscriber_add >     ha1b, >   File "<string>", line 2, in execute >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/util/deprecations.py", > line 390, in warned >     return fn(*args, **kwargs) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", > line 3037, in execute >     return connection.execute(statement, *multiparams, > **params) >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", > line 1190, in execute >     future=False, >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", > line 1491, in _exec_driver_sql >     distilled_parameters, >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", > line 1749, in _execute_context >     e, statement, parameters, cursor, context >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", > line 1930, in _handle_dbapi_exception >     sqlalchemy_exception, with_traceback=exc_info[2], > from_=e >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", > line 211, in raise_ >     raise exception >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", > line 1706, in _execute_context >     cursor, statement, parameters, context >   File > "/usr/src/kamclienv/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", > line 716, in do_execute >     cursor.execute(statement, parameters) > sqlalchemy.exc.IntegrityError: > (sqlite3.IntegrityError) UNIQUE constraint failed: > subscriber.username, subscriber.domain > [SQL: insert into subscriber (username, domain, > password, ha1, ha1b) values ('test', 'sip.test.dev > <http://sip.test.dev>', 'passwordtest', > '96a1efd95bcd6e8e573d07ebdacab623', > '025f1dc363fa8c1ce1d7f2069d94ed0f')] > (Background on this error at: > http://sqlalche.me/e/14/gkpj > <http://sqlalche.me/e/14/gkpj>) > > best regards > > On Wed, Apr 21, 2021 at 3:10 PM Daniel-Constantin > Mierla &lt;miconda(a)gmail.com <mailto:miconda@gmail.com>> > wrote: > > Hello, > > On 18.04.21 22:56, beer Ll wrote: >> Hi , >> >> I have a server test with >> >> Debian Buster 10.9 >> kamailio 5.4.5  >> kamcli 2.0.0 (similar result with 3.0.0) >> >> I'm not able to use kamcli >> with sqlite  : insert and select doesn't work >> with pgsql :  insert works , select of empty >> table works , select table with entry doesn't work >> >> >> TEST WITH SQLITE >> root@kamailio-BT:~# kamcli -c >> /etc/kamcli/kamcli.ini.sqlite subscriber add test >> password >> Traceback (most recent call last): >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", >> line 1706, in _execute_context >>     cursor, statement, parameters, context >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/default.py", >> line 716, in do_execute >>     cursor.execute(statement, parameters) >> sqlite3.OperationalError: near "%": syntax error >> >> The above exception was the direct cause of the >> following exception: >> >> Traceback (most recent call last): >>   File "/usr/bin/kamcli", line 11, in <module> >>     load_entry_point('kamcli==2.0.0', >> 'console_scripts', 'kamcli')() >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 829, in __call__ >>     return self.main(*args, **kwargs) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 782, in main >>     rv = self.invoke(ctx) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 1259, in invoke >>     return >> _process_result(sub_ctx.command.invoke(sub_ctx)) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 1259, in invoke >>     return >> _process_result(sub_ctx.command.invoke(sub_ctx)) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 1066, in invoke >>     return ctx.invoke(self.callback, **ctx.params) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 610, in invoke >>     return callback(*args, **kwargs) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/decorators.py", >> line 73, in new_func >>     return ctx.invoke(f, obj, *args, **kwargs) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 610, in invoke >>     return callback(*args, **kwargs) >>   File >> "/usr/lib/python3/dist-packages/kamcli/commands/cmd_subscriber.py", >> line 43, in subscriber_add >>     e.execute('insert into subscriber (username, >> domain, password, ha1, ha1b) values (%s, %s, %s, >> %s, %s)', udata['username'], udata['domain'], >> password, ha1, ha1b) >>   File "<string>", line 2, in execute >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/deprecations.py", >> line 390, in warned >>     return fn(*args, **kwargs) >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", >> line 3037, in execute >>     return connection.execute(statement, >> *multiparams, **params) >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", >> line 1190, in execute >>     future=False, >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", >> line 1491, in _exec_driver_sql >>     distilled_parameters, >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", >> line 1749, in _execute_context >>     e, statement, parameters, cursor, context >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", >> line 1930, in _handle_dbapi_exception >>     sqlalchemy_exception, >> with_traceback=exc_info[2], from_=e >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/compat.py", >> line 211, in raise_ >>     raise exception >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", >> line 1706, in _execute_context >>     cursor, statement, parameters, context >>   File >> "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/default.py", >> line 716, in do_execute >>     cursor.execute(statement, parameters) >> sqlalchemy.exc.OperationalError: >> (sqlite3.OperationalError) near "%": syntax error >> [SQL: insert into subscriber (username, domain, >> password, ha1, ha1b) values (%s, %s, %s, %s, %s)] >> [parameters: ('test', 'sip.test.dev >> <http://sip.test.dev>', 'password', >> '4380ea9069e888b5fc92996495a0f481', >> '3a36aeb0df7f4504cc5d9b97dab540e5')] >> (Background on this error at: >> http://sqlalche.me/e/14/e3q8 >> <http://sqlalche.me/e/14/e3q8>) >> >> root@kamailio-BT:~# kamctl add  test password >> -e sqlite_query: /usr/bin/sqlite3 >>  /etc/kamailio/kamailio.db insert into subscriber >> (username, domain,ha1,ha1b,password) values >> ('test','sip.test.dev >> <http://sip.test.dev>','4380ea9069e888b5fc92996495a0f481','3a36aeb0df7f4504cc5d9b97dab540e5','password'); >> -e new user 'test' added >> >> root@kamailio-BT:~# kamcli -c >> /etc/kamcli/kamcli.ini.sqlite subscriber show >> Traceback (most recent call last): >>   File "/usr/bin/kamcli", line 11, in <module> >>     load_entry_point('kamcli==2.0.0', >> 'console_scripts', 'kamcli')() >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 829, in __call__ >>     return self.main(*args, **kwargs) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 782, in main >>     rv = self.invoke(ctx) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 1259, in invoke >>     return >> _process_result(sub_ctx.command.invoke(sub_ctx)) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 1259, in invoke >>     return >> _process_result(sub_ctx.command.invoke(sub_ctx)) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 1066, in invoke >>     return ctx.invoke(self.callback, **ctx.params) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 610, in invoke >>     return callback(*args, **kwargs) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/decorators.py", >> line 73, in new_func >>     return ctx.invoke(f, obj, *args, **kwargs) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 610, in invoke >>     return callback(*args, **kwargs) >>   File >> "/usr/lib/python3/dist-packages/kamcli/commands/cmd_subscriber.py", >> line 118, in subscriber_show >>     ioutils_dbres_print(ctx, oformat, ostyle, res) >>   File >> "/usr/lib/python3/dist-packages/kamcli/ioutils.py", >> line 45, in ioutils_dbres_print >>     gstring = tabulate(allrows, >> headers=res.keys(), tablefmt=ostyle) >>   File >> "/usr/local/lib/python3.7/dist-packages/tabulate.py", >> line 1529, in tabulate >>     tabular_data, headers, showindex=showindex >>   File >> "/usr/local/lib/python3.7/dist-packages/tabulate.py", >> line 1162, in _normalize_tabular_data >>     "headers for a list of dicts is not a dict or >> a keyword" >> ValueError: headers for a list of dicts is not a >> dict or a keyword >> >> >> TEST WITH PGSQL >> root@kamailio-BT:~# kamcli -c >> /etc/kamcli/kamcli.ini.pgsql subscriber show >> +------+------------+----------+------------+-------+--------+ >> | id   | username   | domain   | password   | ha1 >>   | ha1b   | >> +======+============+==========+============+=======+========+ >> +------+------------+----------+------------+-------+--------+ >> root@kamailio-BT:~# kamcli -c >> /etc/kamcli/kamcli.ini.pgsql subscriber add test >> password >> root@kamailio-BT:~# kamcli -c >> /etc/kamcli/kamcli.ini.pgsql subscriber show >> Traceback (most recent call last): >>   File "/usr/bin/kamcli", line 11, in <module> >>     load_entry_point('kamcli==2.0.0', >> 'console_scripts', 'kamcli')() >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 829, in __call__ >>     return self.main(*args, **kwargs) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 782, in main >>     rv = self.invoke(ctx) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 1259, in invoke >>     return >> _process_result(sub_ctx.command.invoke(sub_ctx)) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 1259, in invoke >>     return >> _process_result(sub_ctx.command.invoke(sub_ctx)) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 1066, in invoke >>     return ctx.invoke(self.callback, **ctx.params) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 610, in invoke >>     return callback(*args, **kwargs) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/decorators.py", >> line 73, in new_func >>     return ctx.invoke(f, obj, *args, **kwargs) >>   File >> "/usr/local/lib/python3.7/dist-packages/click/core.py", >> line 610, in invoke >>     return callback(*args, **kwargs) >>   File >> "/usr/lib/python3/dist-packages/kamcli/commands/cmd_subscriber.py", >> line 118, in subscriber_show >>     ioutils_dbres_print(ctx, oformat, ostyle, res) >>   File >> "/usr/lib/python3/dist-packages/kamcli/ioutils.py", >> line 45, in ioutils_dbres_print >>     gstring = tabulate(allrows, >> headers=res.keys(), tablefmt=ostyle) >>   File >> "/usr/local/lib/python3.7/dist-packages/tabulate.py", >> line 1529, in tabulate >>     tabular_data, headers, showindex=showindex >>   File >> "/usr/local/lib/python3.7/dist-packages/tabulate.py", >> line 1162, in _normalize_tabular_data >>     "headers for a list of dicts is not a dict or >> a keyword" >> ValueError: headers for a list of dicts is not a >> dict or a keyword >> >> root@kamailio-BT:~# kamctl show test >> 1|test|sip.test.dev >> <http://sip.test.dev>|password|4380ea9069e888b5fc92996495a0f481|3a36aeb0df7f4504cc5d9b97dab540e5 > > thanks for reporting! > > I could reproduce the sqlite problems, they seemed > to be related to the sql driver for sqlalchemy > that was not supporting variadic number of > parameters to the sql query execution function. I > replaced with string formatting and now seems to > work. Try with latest git version and report back > if there are still issues. > > The one for postgres I couldn't reproduce, but it > was after a commit related to output style default > value. Although it didn't seem related, it worked > what I tried for postgres. Again, test now with > latest git version and if there is still a > problem, provide updated traces. > > Cheers, > Daniel > > -- > Daniel-Constantin Mierla -- www.asipto.com <http://www.asipto.com> > www.twitter.com/miconda <http://www.twitter.com/miconda> -- www.linkedin.com/in/miconda <http://www.linkedin.com/in/miconda> > Kamailio Advanced Training - Online > May 17-20, 2021 (Europe Timezone) - June 7-10, 2021 (America Timezone) > * https://www.asipto.com/sw/kamailio-advanced-training-online/ <https://www.asipto.com/sw/kamailio-advanced-training-online/> > > > _______________________________________________ > Kamailio (SER) - Users Mailing List > sr-users(a)lists.kamailio.org <mailto:sr-users@lists.kamailio.org> > https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users <https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users> -- Daniel-Constantin Mierla -- www.asipto.com <http://www.asipto.com> www.twitter.com/miconda <http://www.twitter.com/miconda> -- www.linkedin.com/in/miconda <http://www.linkedin.com/in/miconda> Kamailio Advanced Training - Online May 17-20, 2021 (Europe Timezone) - June 7-10, 2021 (America Timezone) * https://www.asipto.com/sw/kamailio-advanced-training-online/ <https://www.asipto.com/sw/kamailio-advanced-training-online/>