MySQLResolver / Add + Show User / Object Error in Post / No Object User


#1

Hello,
i tried to add a User to an existing SQLResolver. By adding the Resolver i had no problems and the connection to the Database and Table was successful established.

I found these Error Message inside the log after i tried to add a User.

[2018-10-16 10:03:00,668][8445][140227020211968][ERROR][privacyidea.app:1423] Exception on /user/ [POST]
Traceback (most recent call last):
File “/usr/lib/python2.7/dist-packages/flask/app.py”, line 1817, in wsgi_app
response = self.full_dispatch_request()
File “/usr/lib/python2.7/dist-packages/flask/app.py”, line 1477, in full_dispatch_request
rv = self.handle_user_exception(e)
File “/usr/lib/python2.7/dist-packages/flask/app.py”, line 1381, in handle_user_exception
reraise(exc_type, exc_value, tb)
File “/usr/lib/python2.7/dist-packages/flask/app.py”, line 1475, in full_dispatch_request
rv = self.dispatch_request()
File “/usr/lib/python2.7/dist-packages/flask/app.py”, line 1461, in dispatch_request
return self.view_functionsrule.endpoint
File “/usr/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py”, line 120, in policy_wrapper
return wrapped_function(*args, **kwds)
File “/usr/lib/python2.7/dist-packages/privacyidea/api/auth.py”, line 317, in decorated_function
return f(*args, **kwargs)
File “/usr/lib/python2.7/dist-packages/privacyidea/api/user.py”, line 173, in create_user_api
r = create_user(resolvername, attributes, password=password)
File “/usr/lib/python2.7/dist-packages/privacyidea/lib/log.py”, line 154, in log_wrapper
return func(*args, **kwds)
File “/usr/lib/python2.7/dist-packages/privacyidea/lib/user.py”, line 505, in create_user
uid = y.add_user(attributes)
File “/usr/lib/python2.7/dist-packages/privacyidea/lib/resolvers/SQLIdResolver.py”, line 551, in add_user
self.db.commit()
File “/usr/lib/python2.7/dist-packages/sqlsoup.py”, line 268, in commit
self.session.commit()
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py”, line 150, in do
return getattr(self.registry(), name)(*args, **kwargs)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py”, line 813, in commit
self.transaction.commit()
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py”, line 392, in commit
self._prepare_impl()
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py”, line 372, in _prepare_impl
self.session.flush()
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py”, line 2027, in flush
self._flush(objects)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py”, line 2145, in _flush
transaction.rollback(_capture_exception=True)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py”, line 60, in exit
compat.reraise(exc_type, exc_value, exc_tb)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/session.py”, line 2109, in _flush
flush_context.execute()
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py”, line 373, in execute
rec.execute(self)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py”, line 532, in execute
uow
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py”, line 174, in save_obj
mapper, table, insert)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py”, line 800, in _emit_insert_statements
execute(statement, params)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py”, line 914, in execute
return meth(self, multiparams, params)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py”, line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py”, line 1010, in _execute_clauseelement
compiled_sql, distilled_params
File “/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py”, line 1146, in _execute_context
context)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py”, line 1341, in _handle_dbapi_exception
exc_info
File “/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py”, line 200, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py”, line 1139, in _execute_context
context)
File “/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py”, line 450, in do_execute
cursor.execute(statement, parameters)
File “/usr/lib/python2.7/dist-packages/pymysql/cursors.py”, line 158, in execute
result = self._query(query)
File “/usr/lib/python2.7/dist-packages/pymysql/cursors.py”, line 308, in _query
conn.query(q)
File “/usr/lib/python2.7/dist-packages/pymysql/connections.py”, line 820, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/usr/lib/python2.7/dist-packages/pymysql/connections.py”, line 1002, in _read_query_result
result.read()
File “/usr/lib/python2.7/dist-packages/pymysql/connections.py”, line 1285, in read
first_packet = self.connection._read_packet()
File “/usr/lib/python2.7/dist-packages/pymysql/connections.py”, line 966, in _read_packet
packet.check_error()
File “/usr/lib/python2.7/dist-packages/pymysql/connections.py”, line 394, in check_error
err.raise_mysql_exception(self._data)
File “/usr/lib/python2.7/dist-packages/pymysql/err.py”, line 120, in raise_mysql_exception
_check_mysql_exception(errinfo)
File “/usr/lib/python2.7/dist-packages/pymysql/err.py”, line 112, in _check_mysql_exception
raise errorclass(errno, errorvalue)

If i try to list all exisiting User i got the Error that no Object “User” exists.

[2018-10-16 10:30:32,687][8445][140227011819264][ERROR][privacyidea.lib.user:651] AttributeError("'Query' object has no attribute 'user'",)

Could please anyone give me a hint or a way to fix and solve this Issue?

Best Regards


#2

Hello,

Could you post your MySQL table structure (“field” - “type”) and resolver config?

Regards,
Alexander


#3

Hi Alexander,
first of all i have to say thank you Alexander. After your answer i checked my resolver again and found two errors. One was in the Mapping and the other one was in the structure itself. Thanks!

But i still can’t list all User which are in the current table.
the other one which are not displayed are,

ID int(10) unsigned NOT NULL AUTO_INCREMENT,
USERID char(128) DEFAULT NULL,
USERNAME varchar(50) DEFAULT NULL,
PORTAL_ID int(10) unsigned NOT NULL,
ROLE_ID int(10) unsigned DEFAULT NULL,
ORGANIZATION_ID int(10) unsigned DEFAULT NULL,
EMAIL varchar(50) NOT NULL,
PASSWORD char(128) NOT NULL,
PHONE varchar(50) DEFAULT NULL,
MOBILE varchar(50) DEFAULT NULL,
GIVENNAME varchar(50) NOT NULL,
SURNAME varchar(50) NOT NULL,
ISACTIVE tinyint(1) DEFAULT NULL,
ISEMAILACTIVE tinyint(1) DEFAULT NULL,
ISEXTERNAL tinyint(1) DEFAULT NULL,
CREATEUSER_ID int(10) unsigned DEFAULT NULL,
CREATETIME datetime DEFAULT CURRENT_TIMESTAMP,
MODIFYUSER_ID int(10) unsigned DEFAULT NULL,
MODIFYTIME datetime DEFAULT NULL,

Resolverconfig
{ "userid" : "id", "username": "username", "phone" : "phone", "mobile" : "mobile", "email" : "email", "surname" : "surname", "givenname" : "givenname" ,"password" : "password"}

It would be glad if you can point me to my misstake.

Kind Regards,


#4

Is previous error appear in log?
Can you change this table without problems with other apps?
If it’s acceptable for you, change ID field from int to varchar with increment option (yes, it is not a mistake, this operation solved the same problem for me:) )

Regards,


#5

Hi,

could You turn on the debugging output by adding
PI_LOGLEVEL = logging.DEBUG
in Your pi.cfg and post the corresponding backtrace?

Regards
Paul


#6

No the second error comes also up with the Error that no User object is located.

Yes i can edit all tables and DB’s.
I’ll try it in a second with varchar.

@plettich i will change the Debuglevel in a few moments.


#7
[2018-10-16 15:34:51,571][8445][140227112531712][INFO][privacyidea.lib.usercache:99] Deleted 0 entries from the user cache (resolver=u'CMResolver', username=None, expired=None)
[2018-10-16 15:34:57,227][8445][140227011819264][ERROR][privacyidea.lib.user:651] AttributeError("'Query' object has no attribute 'username'",)
[2018-10-16 15:35:02,185][31740][139744558503680][DEBUG][privacyidea.lib.periodictask:251] Collecting periodic tasks to run at 2018-10-16T15:35:02.109437+02:00
[2018-10-16 15:35:02,186][31740][139744558503680][DEBUG][privacyidea.api.before_after:85] End handling of request u'/?'

this was the output in the Log file after i changed the log Level.

I couldn’t understand the error message, because there is a username in the mapping and also in the table and if i test the resolver it would be shonw that one user is found


#8

Now this is weird, there seem to be some logs missing. Did You restart the (web-)server? and did You check the webserver logs?
Interestingly before it couldn’t find the attribute 'user', now it is the attribute 'username'.


#9
[2018-10-16 17:32:02,987][32418][139968933373696][DEBUG][privacyidea.lib.user:634] Check for resolver class: u'CMResolver'
[2018-10-16 17:32:02,987][32418][139968933373696][DEBUG][privacyidea.lib.resolver:185] Entering get_resolver_object with arguments (u'CMResolver',) and keywords {}
[2018-10-16 17:32:02,988][32418][139968933373696][DEBUG][privacyidea.lib.resolver:185] Entering get_resolver_list with arguments () and keywords {'filter_resolver_name': u'CMResolver'}
[2018-10-16 17:32:02,988][32418][139968933373696][DEBUG][privacyidea.lib.config:72] The singleton <class 'privacyidea.lib.config.ConfigClass'> already exists.
[2018-10-16 17:32:02,989][32418][139968933373696][DEBUG][privacyidea.lib.resolver:199] Exiting get_resolver_list with result HIDDEN
[2018-10-16 17:32:02,989][32418][139968933373696][DEBUG][privacyidea.lib.config:185] Entering get_resolver_list with arguments () and keywords {}
[2018-10-16 17:32:02,989][32418][139968933373696][DEBUG][privacyidea.lib.config:549] None
[2018-10-16 17:32:02,989][32418][139968933373696][DEBUG][privacyidea.lib.config:197] Exiting get_resolver_list with result set(['privacyidea.lib.resolvers.PasswdIdResolver', 'privacyidea.lib.resolvers.SCIMIdResolver', 'privacyidea.lib.resolvers.SQLIdResolver', 'privacyidea.lib.resolvers.LDAPIdResolver'])
[2018-10-16 17:32:02,989][32418][139968933373696][DEBUG][privacyidea.lib.config:683] using the module list: set(['privacyidea.lib.resolvers.PasswdIdResolver', 'privacyidea.lib.resolvers.SCIMIdResolver', 'privacyidea.lib.resolvers.SQLIdResolver', 'privacyidea.lib.resolvers.LDAPIdResolver'])
[2018-10-16 17:32:02,989][32418][139968933373696][DEBUG][privacyidea.lib.config:691] import module: privacyidea.lib.resolvers.PasswdIdResolver
[2018-10-16 17:32:02,989][32418][139968933373696][DEBUG][privacyidea.lib.config:691] import module: privacyidea.lib.resolvers.SCIMIdResolver
[2018-10-16 17:32:02,990][32418][139968933373696][DEBUG][privacyidea.lib.config:691] import module: privacyidea.lib.resolvers.SQLIdResolver
[2018-10-16 17:32:02,990][32418][139968933373696][DEBUG][privacyidea.lib.config:691] import module: privacyidea.lib.resolvers.LDAPIdResolver
[2018-10-16 17:32:02,990][32418][139968933373696][DEBUG][privacyidea.lib.config:503] module: <module 'privacyidea.lib.resolvers.PasswdIdResolver' from '/usr/lib/python2.7/dist-packages/privacyidea/lib/resolvers/PasswdIdResolver.pyc'>
[2018-10-16 17:32:02,990][32418][139968933373696][DEBUG][privacyidea.lib.config:503] module: <module 'privacyidea.lib.resolvers.SCIMIdResolver' from '/usr/lib/python2.7/dist-packages/privacyidea/lib/resolvers/SCIMIdResolver.pyc'>
[2018-10-16 17:32:02,990][32418][139968933373696][DEBUG][privacyidea.lib.config:503] module: <module 'privacyidea.lib.resolvers.SQLIdResolver' from '/usr/lib/python2.7/dist-packages/privacyidea/lib/resolvers/SQLIdResolver.pyc'>
[2018-10-16 17:32:02,990][32418][139968933373696][DEBUG][privacyidea.lib.config:503] module: <module 'privacyidea.lib.resolvers.LDAPIdResolver' from '/usr/lib/python2.7/dist-packages/privacyidea/lib/resolvers/LDAPIdResolver.pyc'>
[2018-10-16 17:32:02,990][32418][139968933373696][DEBUG][privacyidea.lib.resolver:185] Entering get_resolver_config with arguments (u'CMResolver',) and keywords {}
[2018-10-16 17:32:02,990][32418][139968933373696][DEBUG][privacyidea.lib.resolver:185] Entering get_resolver_list with arguments () and keywords {'filter_resolver_name': u'CMResolver'}
[2018-10-16 17:32:02,991][32418][139968933373696][DEBUG][privacyidea.lib.config:72] The singleton <class 'privacyidea.lib.config.ConfigClass'> already exists.
[2018-10-16 17:32:02,992][32418][139968933373696][DEBUG][privacyidea.lib.resolver:199] Exiting get_resolver_list with result HIDDEN
[2018-10-16 17:32:02,992][32418][139968933373696][DEBUG][privacyidea.lib.resolver:199] Exiting get_resolver_config with result HIDDEN
[2018-10-16 17:32:02,994][32418][139968933373696][INFO][privacyidea.lib.pooling:84] Creating a new engine and connection pool for key sql.1abb22d9c1cb93ca69621387afbaaaecadadc5ec
[2018-10-16 17:32:02,994][32418][139968933373696][INFO][privacyidea.lib.resolvers.SQLIdResolver:409] using the connect string mysql://root:xxxx@88.79.198.27/cm
[2018-10-16 17:32:02,994][32418][139968933373696][DEBUG][privacyidea.lib.resolvers.SQLIdResolver:412] using pool_size=5, pool_timeout=10, pool_recycle=7200
[2018-10-16 17:32:03,028][32418][139968933373696][DEBUG][privacyidea.lib.resolver:197] Exiting get_resolver_object with result <privacyidea.lib.resolvers.SQLIdResolver.IdResolver object at 0x7f4cf5fb2b10>
[2018-10-16 17:32:03,028][32418][139968933373696][DEBUG][privacyidea.lib.user:636] with this search dictionary: {'username': '*'}
[2018-10-16 17:32:03,028][32418][139968933373696][ERROR][privacyidea.lib.user:651] AttributeError("'Query' object has no attribute 'username'",)
[2018-10-16 17:32:03,029][32418][139968933373696][DEBUG][privacyidea.lib.user:652] Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/privacyidea/lib/user.py", line 637, in get_user_list
	ulist = y.getUserList(searchDict)
  File "/usr/lib/python2.7/dist-packages/privacyidea/lib/resolvers/SQLIdResolver.py", line 312, in getUserList
	conditions.append(getattr(self.TABLE, column).like(value))
  File "/usr/lib/python2.7/dist-packages/sqlsoup.py", line 78, in __getattr__
	return getattr(cls._query, attr)
AttributeError: 'Query' object has no attribute 'username'

[2018-10-16 17:32:03,029][32418][139968933373696][DEBUG][privacyidea.lib.user:197] Exiting get_user_list with result []
[2018-10-16 17:32:03,052][32418][139968933373696][DEBUG][privacyidea.api.lib.utils:246] Can not get param: No JSON object could be decoded
[2018-10-16 17:32:03,055][32418][139968933373696][DEBUG][privacyidea.api.before_after:85] End handling of request u'/user/?realm=campaignmanager'

@plettich this schould be now the full log you asked to see?

yes it’s realy wired. I build up both Usertables with like the same. in the first one it was my fault in the resolverconfiguration, but now i’m a littlebit confused. username colum exists and i add them to the mapping JSON too.


#10

AttributeError: ‘Query’ object has no attribute ‘username’

Could it be a case sensitivity problem? username is the first column, which privacyIDEA will try to access, to resolve the username to a uid. Please try uppercasing your columns in the mapping.


#11

God Damn, thank you! Sorry for my language.
This Problem holds me too long up.
@cornelinux I’ll take care of the art of nameing. Realy thank you

– SOLVED –

of better

– CLOSED –