[SR-Users] kamcli db sqlite / posgresql error
Daniel-Constantin Mierla
miconda at gmail.com
Thu Apr 22 08:57:15 CEST 2021
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?
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
> <miconda at gmail.com <mailto:miconda at 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 at 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 at 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 at 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 at kamailio-BT:~# kamcli -c /etc/kamcli/kamcli.ini.pgsql
>> subscriber show
>> +------+------------+----------+------------+-------+--------+
>> | id | username | domain | password | ha1 | ha1b |
>> +======+============+==========+============+=======+========+
>> +------+------------+----------+------------+-------+--------+
>> root at kamailio-BT:~# kamcli -c /etc/kamcli/kamcli.ini.pgsql
>> subscriber add test password
>> root at 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 at 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 at lists.kamailio.org
> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
--
Daniel-Constantin Mierla -- www.asipto.com
www.twitter.com/miconda -- 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/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20210422/c99e53cd/attachment.htm>
More information about the sr-users
mailing list