Error when saving realm with sql resolver

Paolo · October 13, 2014, 6:33pm

I’m using privacyIDEA 1.4-dev5 on Ubuntu 14.04-amd64.
I set up the mysql *useridresolver *and when I clicked on "Test connection"
everthing went fine.
Next I set up a realm with the sql resolver but when I click on “save” the
following error message appears:

Error saving realm configuration.: invalid literal for int() with base 10:
’:3306’

Paolo · October 13, 2014, 6:34pm

obviously the sql port doesn’t have “:” in the useridresolver configuration
:)On Monday, October 13, 2014 8:33:02 PM UTC+2, Paolo Stivanin wrote:

I’m using privacyIDEA 1.4-dev5 on Ubuntu 14.04-amd64.
I set up the mysql *useridresolver *and when I clicked on “Test
connection” everthing went fine.
Next I set up a realm with the sql resolver but when I click on “save” the
following error message appears:

Error saving realm configuration.: invalid literal for int() with base
10: ‘:3306’

cornelinux · October 13, 2014, 6:43pm

Hi Paolo,

from a first glimpse I saw a bug in the creation of the connect string.
(code “cleanup” in the latest version)
I also need to check why the shiny new SQL test did not find this bug.

It looks like you are trying to setup an MySQL resolver.
As a workaround you may try to set up the resolver without a port!
You can leave the default port 3306 empty. Than this should not happen.

Kind regards
CorneliusAm Montag, 13. Oktober 2014 20:33:02 UTC+2 schrieb Paolo:

I’m using privacyIDEA 1.4-dev5 on Ubuntu 14.04-amd64.
I set up the mysql *useridresolver *and when I clicked on “Test
connection” everthing went fine.
Next I set up a realm with the sql resolver but when I click on “save” the
following error message appears:

Error saving realm configuration.: invalid literal for int() with base
10: ‘:3306’

cornelinux · October 13, 2014, 7:33pm

HI Paolo,

does the workaround work for you?

If not - I added a fix to the master branch and started a build on the
ppa-dev launchpad to build 1.5~dev1.
Do you want to use this repository?
Otherwise I will add a bugfix version 1.4.0.1 tomorrow to the ppa (stable)
repo.

Kind regards
CorneliusAm Montag, 13. Oktober 2014 20:33:02 UTC+2 schrieb Paolo:

I’m using privacyIDEA 1.4-dev5 on Ubuntu 14.04-amd64.
I set up the mysql *useridresolver *and when I clicked on “Test
connection” everthing went fine.
Next I set up a realm with the sql resolver but when I click on “save” the
following error message appears:

Error saving realm configuration.: invalid literal for int() with base
10: ‘:3306’

Paolo · October 13, 2014, 11:47pm

Hello,
with version 1.5.0-dev the problem is solved but in the User View I cannot
get a list of the users that are inside the sql db…On Monday, October 13, 2014 9:33:52 PM UTC+2, corneliu…@netknights.it wrote:

HI Paolo,

does the workaround work for you?

If not - I added a fix to the master branch and started a build on the
ppa-dev launchpad to build 1.5~dev1.
Do you want to use this repository?
Otherwise I will add a bugfix version 1.4.0.1 tomorrow to the ppa (stable)
repo.

Kind regards
Cornelius

Am Montag, 13. Oktober 2014 20:33:02 UTC+2 schrieb Paolo:

I’m using privacyIDEA 1.4-dev5 on Ubuntu 14.04-amd64.
I set up the mysql *useridresolver *and when I clicked on “Test
connection” everthing went fine.
Next I set up a realm with the sql resolver but when I click on “save”
the following error message appears:

Error saving realm configuration.: invalid literal for int() with base
10: ‘:3306’

Paolo · October 14, 2014, 6:14am

Hello,
I set up the database with the following cmds:

create database mydb;
use mydb;
create table users(userid INTEGER AUTO_INCREMENT PRIMARY KEY, username
VARCHAR(30), password VARCHAR(512));
insert into users(username, password) values(‘one’, ‘mypwd’);
insert into users(username, password) values(‘two’, ‘otherpwd’);

After that I successfully tested the connection and it reported me that it
has found 2 users.
Then I added a realm with this sql resolver but when I go to “user view”
nothing shows up.On Tuesday, October 14, 2014 6:39:05 AM UTC+2, corneliu…@netknights.it wrote:

Hello Paolo,

this might be due to a mapping issue.

What did you enter at the attribute mapping of your resolver?
You need to enter something like
{ “userid”: “Your ID column”, “username”, “The column with the name of
your user” }

If the resolver is set up correctly, you need to add this resolver to a
realm.
So you need to go to Config → Realms and create a new realm (or edit an
existing) with the resolver in it.

Kind regards
Cornelius

Am Dienstag, 14. Oktober 2014 01:47:42 UTC+2 schrieb Paolo:

Hello,
with version 1.5.0-dev the problem is solved but in the User View I
cannot get a list of the users that are inside the sql db…

On Monday, October 13, 2014 9:33:52 PM UTC+2, corneliu…@netknights.it wrote:

HI Paolo,

does the workaround work for you?

If not - I added a fix to the master branch and started a build on the
ppa-dev launchpad to build 1.5~dev1.
Do you want to use this repository?
Otherwise I will add a bugfix version 1.4.0.1 tomorrow to the ppa
(stable) repo.

Kind regards
Cornelius

Am Montag, 13. Oktober 2014 20:33:02 UTC+2 schrieb Paolo:

I’m using privacyIDEA 1.4-dev5 on Ubuntu 14.04-amd64.
I set up the mysql *useridresolver *and when I clicked on “Test
connection” everthing went fine.
Next I set up a realm with the sql resolver but when I click on “save”
the following error message appears:

Error saving realm configuration.: invalid literal for int() with base
10: ‘:3306’

cornelinux · October 14, 2014, 4:39am

Hello Paolo,

this might be due to a mapping issue.

What did you enter at the attribute mapping of your resolver?
You need to enter something like
{ “userid”: “Your ID column”, “username”, “The column with the name of your
user” }

If the resolver is set up correctly, you need to add this resolver to a
realm.
So you need to go to Config → Realms and create a new realm (or edit an
existing) with the resolver in it.

Kind regards
CorneliusAm Dienstag, 14. Oktober 2014 01:47:42 UTC+2 schrieb Paolo:

Hello,
with version 1.5.0-dev the problem is solved but in the User View I cannot
get a list of the users that are inside the sql db…

On Monday, October 13, 2014 9:33:52 PM UTC+2, corneliu…@netknights.it wrote:

HI Paolo,

does the workaround work for you?

If not - I added a fix to the master branch and started a build on the
ppa-dev launchpad to build 1.5~dev1.
Do you want to use this repository?
Otherwise I will add a bugfix version 1.4.0.1 tomorrow to the ppa
(stable) repo.

Kind regards
Cornelius

Am Montag, 13. Oktober 2014 20:33:02 UTC+2 schrieb Paolo:

I’m using privacyIDEA 1.4-dev5 on Ubuntu 14.04-amd64.
I set up the mysql *useridresolver *and when I clicked on “Test
connection” everthing went fine.
Next I set up a realm with the sql resolver but when I click on “save”
the following error message appears:

Error saving realm configuration.: invalid literal for int() with base
10: ‘:3306’

Paolo · October 14, 2014, 9:28am

solved, thanks a lot :DOn Tuesday, October 14, 2014 9:21:50 AM UTC+2, corneliu…@netknights.it wrote:

Hi,
the resolver testing for sql resolver just checks, if it can access the
database and how many rows are in the table.
If you are seeing no users, there might be a problem with your attribute
mapping which you defined in the resolver definition.
It should be:

{ “userid”: “userid”, “username”: “username”, “password”: “password” }

Be sure to use double quotes.
If it does not work out, you can take a look at the logfile
privacyidea.log (or send it).
Kind regards
Cornelius

Paolo · October 14, 2014, 9:34am

I have only dropped and recreated the table again and now it works
I think I messed up the table during the various tests I have made!
The doc is OK, I have followed this[1] how to

[1] http://www.howtoforge.com/secure-wordpress-login-with-two-factor-authentication-using-privacyideaOn Tuesday, October 14, 2014 11:31:01 AM UTC+2, corneliu…@netknights.it wrote:

You are very welcome.
Any further feedback is as always highly appreciated.
(Also some insights of yours, where the documentaiton might be lacking
something…, etc.)
Kind regards
Cornelius

Am Dienstag, 14. Oktober 2014 11:28:27 UTC+2 schrieb Paolo:

solved, thanks a lot

On Tuesday, October 14, 2014 9:21:50 AM UTC+2, corneliu…@netknights.it wrote:

Hi,
the resolver testing for sql resolver just checks, if it can access the
database and how many rows are in the table.
If you are seeing no users, there might be a problem with your attribute
mapping which you defined in the resolver definition.
It should be:

{ “userid”: “userid”, “username”: “username”, “password”: “password” }

Be sure to use double quotes.
If it does not work out, you can take a look at the logfile
privacyidea.log (or send it).
Kind regards
Cornelius

cornelinux · October 14, 2014, 9:31am

You are very welcome.
Any further feedback is as always highly appreciated.
(Also some insights of yours, where the documentaiton might be lacking
something…, etc.)
Kind regards
CorneliusAm Dienstag, 14. Oktober 2014 11:28:27 UTC+2 schrieb Paolo:

solved, thanks a lot

On Tuesday, October 14, 2014 9:21:50 AM UTC+2, corneliu…@netknights.it wrote:

Hi,
the resolver testing for sql resolver just checks, if it can access the
database and how many rows are in the table.
If you are seeing no users, there might be a problem with your attribute
mapping which you defined in the resolver definition.
It should be:

{ “userid”: “userid”, “username”: “username”, “password”: “password” }

Be sure to use double quotes.
If it does not work out, you can take a look at the logfile
privacyidea.log (or send it).
Kind regards
Cornelius

cornelinux · October 14, 2014, 9:35am

Q29udGVudC1UeXBlOiBhcHBsaWNhdGlvbi9wZ3Atc2lnbmF0dXJlOyBuYW1lPSJzaWduYXR1cmUu
YXNjIg0KQ29udGVudC1EZXNjcmlwdGlvbjogT3BlblBHUCBkaWdpdGFsIHNpZ25hdHVyZQ0KQ29u
dGVudC1EaXNwb3NpdGlvbjogYXR0YWNobWVudDsgZmlsZW5hbWU9InNpZ25hdHVyZS5hc2MiDQoN
Ci0tLS0tQkVHSU4gUEdQIFNJR05BVFVSRS0tLS0tDQpWZXJzaW9uOiBHbnVQRyB2MQ0KDQppUUlj
QkFFQkFnQUdCUUpVUE81a0FBb0pFQkJoWkZVdWpZRkppQlVQL2pTcHNSTG52RmZtTFZBdVpxWFRT
b3NlDQpYdFhGZUZIbEw2YzhwRUpDWEhEK2VNZ1VMeGJDa1c3ZUJlZHBJVFpkR3lvdG45NWc5UHhP
NE9CMXc3RHMyeEZ0DQpVMSt2WFVjNXlsSk5nQkJDWnhFR0JEaEphSjBLaWEzT0MrL3dOZkM2Y0lw
K09BS1JrdWJsWVYyL08zNEtiWUdVDQplNFNmSklEZUhHQ2VKdDRhNE9uTFhHUXJzbWN2OVJQVm9U
RnZlTTdEd2w1ZmJJRGZSdG8zNlg1bDFiVUx2YituDQoxZmNPVGU4YmdhM1FvYVdXVGhrdHFBdEkv
L0Z1ajVNTXlCSGVOMUF3RjZjbXNuM2NGeFpldlE1TElNcm1zaGlWDQpNSURsS3F2ckRJZHZGSlAw
VjVTRUZrL1lISG5EODMyQTliMEhJYkNRVlYyOGljTThCQzRSb3ZCV1EzdG50OXgvDQp0bWttRFlY
M1FkNFVPZW5MYWNsMC9xZmRZWmhxRStobllkVjJpRzVjcEh4YWlTTG51K3haclV6Z2dia0E4NXhr
DQpuUCtibG4yL0ZRWVZ5UE9vWTJ0M3dFeE9LMGF3RjBPTGJORUZ0SlNIaFB4eEZaamRUVFhXUEJs
RU1Nc0lNZUhZDQpqbUpNR2RUMlVRVDRNOGI2amkvZndpWG95cTNDTHZkWmVsbVZqTmloWW81KytM
NzJjVzB2TldDL1MrYjJ4LytNDQorMDRmMTZ5TzVFZFUrQVlBWHhGVCtxaEhnT1NWc3RURlNGdGcv
Yzk2dUZjNHFUSW5tNWdLbGVCMldYQzJmU09qDQpBTHlyRGVXeW9WSWlDNktEcFlwbmNtbkwwdy9V
eWhQOGJRNFN4OC9rdW9JMmcxSklFeTJFWFpSQmFpQnNZcDJEDQpTNzdTRzl2NVRDaW0zSkZSQ29R
ZA0KPVRaM2gNCi0tLS0tRU5EIFBHUCBTSUdOQVRVUkUtLS0tLQ0K

cornelinux · October 14, 2014, 7:21am

Hi,
the resolver testing for sql resolver just checks, if it can access the
database and how many rows are in the table.
If you are seeing no users, there might be a problem with your attribute
mapping which you defined in the resolver definition.
It should be:

{ “userid”: “userid”, “username”: “username”, “password”: “password” }

Be sure to use double quotes.
If it does not work out, you can take a look at the logfile privacyidea.log
(or send it).
Kind regards
Cornelius