New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Your session is invalid or expired on login (MSSQL) #4078

Closed
rcubetrac opened this Issue Jan 21, 2013 · 37 comments

Comments

Projects
None yet
1 participant
@rcubetrac

Reported by vlastaw on 21 Jan 2013 08:39 UTC as Trac ticket #1488918

No users are able to log in. Login attempt causes log in page to reload and display "Your session is invalid or expired." error.

Error log:

[02:30:49 +0100](21-Jan-2013): DB Error: [[Microsoft](242])[Server Native Client 11.0](SQL)[Server](SQL)Pevod datovho typu varchar na datov typ datetime vrtil hodnotu mimo rozsah. in D:\www\_roundcube\program\lib\Roundcube\rcube_db.php on line 415 (POST /?_task=login&_action=login)
[02:30:49 +0100](21-Jan-2013): DB Error: [[Microsoft](242])[Server Native Client 11.0](SQL)[Server](SQL)Pevod datovho typu varchar na datov typ datetime vrtil hodnotu mimo rozsah. in D:\www\_roundcube\program\lib\Roundcube\rcube_db.php on line 415 (GET /?_task=mail)

Pevod datovho typu varchar na datov typ datetime vrtil hodnotu mimo rozsah = Conversion of varchar datatype to datetime datatype was out of range.

SQL log:

[09:24:16 +0100](21-Jan-2013): [SELECT vars, ip, changed FROM session WHERE sess_id = 'sgmst8a4ukdfteopnib4lgo2f0';
[21-Jan-2013 09:24:16 +0100](1]): [INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('sgmst8a4ukdfteopnib4lgo2f0', 'dGVtcHxiOjE7bGFuZ3VhZ2V8czo1OiJlbl9VUyI7dGFza3xzOjU6ImxvZ2luIjs=', 'IP', '2013-01-21 09:24:16', '2013-01-21 09:24:16');
[21-Jan-2013 09:24:28 +0100](2]): [SELECT vars, ip, changed FROM session WHERE sess_id = 'sgmst8a4ukdfteopnib4lgo2f0';
[21-Jan-2013 09:24:28 +0100](1]): [DELETE FROM session WHERE sess_id = 'sgmst8a4ukdfteopnib4lgo2f0';
[21-Jan-2013 09:24:28 +0100](2]): [SELECT * FROM users WHERE mail_host = 'host' AND username = 'vlasta@host';
[21-Jan-2013 09:24:29 +0100](3]): [UPDATE users SET last_login = getdate() WHERE user_id = '2';
[21-Jan-2013 09:24:29 +0100](4]): [INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('08iglgvg6dbvf0n72hr5q6h196', 'bGFuZ3VhZ2V8czo1OiJlbl9VUyI7c2tpbnxzOjU6ImxhcnJ5IjtpbWFwX25hbWVzcGFjZXxhOjQ6e3M6ODoicGVyc29uYWwiO2E6MTp7aTowO2E6Mjp7aTowO3M6MDoiIjtpOjE7czoxOiIuIjt9fXM6NToib3RoZXIiO047czo2OiJzaGFyZWQiO2E6MTp7aTowO2E6Mjp7aTowO3M6NzoiI1B1YmxpYyI7aToxO3M6MToiLiI7fX1zOjY6InByZWZpeCI7czowOiIiO31pbWFwX2RlbGltaXRlcnxzOjE6Ii4iO3VzZXJfaWR8czoxOiIyIjt1c2VybmFtZXxzOjE4OiJ2bGFzdGFAamFvc29ibmUuY3oiO3N0b3JhZ2VfaG9zdHxzOjExOiJqYW9zb2JuZS5jeiI7c3RvcmFnZV9wb3J0fGk6MTQzO3N0b3JhZ2Vfc3NsfE47cGFzc3dvcmR8czozMjoiOFpmUW1vTG1oanZiWitTWWhCNkh6aW4vWWdWeFRhamQiO2xvZ2luX3RpbWV8aToxMzU4NzU2NjY5O3RpbWV6b25lfHM6MTM6IkV1cm9wZS9CZXJsaW4iOw==', 'IP', '2013-01-21 09:24:29', '2013-01-21 09:24:29');
[21-Jan-2013 09:24:29 +0100](5]): [SELECT vars, ip, changed FROM session WHERE sess_id = '08iglgvg6dbvf0n72hr5q6h196';
[21-Jan-2013 09:24:29 +0100](1]): [2] INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('08iglgvg6dbvf0n72hr5q6h196', 'dGVtcHxiOjE7bGFuZ3VhZ2V8czo1OiJlbl9VUyI7dGFza3xzOjU6ImxvZ2luIjs=', 'IP', '2013-01-21 09:24:29', '2013-01-21 09:24:29'); 

Config: Windows Server 2012 / IIS 8 (PHP 5.4.11) + MS SQL 2012 (php_pdo_sqlsrv)

Migrated-From: http://trac.roundcube.net/ticket/1488918

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jan 21, 2013

Comment by @alecpl on 21 Jan 2013 09:27 UTC

This is strange. These queries look correct. Also the datetime format is the same as in previous versions. So, maybe it's some setting in MSSQL. I don't know. Please, find fromunixtime() method in rcube_db.php file and change:

return date("'Y-m-d H:i:s'", $timestamp);

to

return date("'Y-m-dTH:i:s'", $timestamp);

Comment by @alecpl on 21 Jan 2013 09:27 UTC

This is strange. These queries look correct. Also the datetime format is the same as in previous versions. So, maybe it's some setting in MSSQL. I don't know. Please, find fromunixtime() method in rcube_db.php file and change:

return date("'Y-m-d H:i:s'", $timestamp);

to

return date("'Y-m-dTH:i:s'", $timestamp);
@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jan 21, 2013

Comment by vlastaw on 21 Jan 2013 23:38 UTC

Unfortunatelly that didn't help. If the queries are correct... I did not change anything in my MSSQL server, but what changed regarding the database besides roundcube version itself, was PHP SQLSRV extension. 0.8.3 used php_sqlsrv_54_nts, but 0.9 requires PDO driver php_pdo_sqlsrv. Don't these two drivers handle something differently? Why is the PDO driver mandatory in 0.9 anyway?

Comment by vlastaw on 21 Jan 2013 23:38 UTC

Unfortunatelly that didn't help. If the queries are correct... I did not change anything in my MSSQL server, but what changed regarding the database besides roundcube version itself, was PHP SQLSRV extension. 0.8.3 used php_sqlsrv_54_nts, but 0.9 requires PDO driver php_pdo_sqlsrv. Don't these two drivers handle something differently? Why is the PDO driver mandatory in 0.9 anyway?

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jan 22, 2013

Comment by @alecpl on 22 Jan 2013 07:50 UTC

PDO is required because we don't use MDB2 anymore. I also don't think this is a driver problem. The question is why MSSQL server thinks that '2013-01-21 09:24:16' is out of range. The only explanation I found is that it's wrong date format, but why it was working before? The iso8601 format (with T in the middle) should work even with non-english database localization settings. Try to run such query:

SELECT CAST('2013-01-21 09:24:16' AS datetime);

Comment by @alecpl on 22 Jan 2013 07:50 UTC

PDO is required because we don't use MDB2 anymore. I also don't think this is a driver problem. The question is why MSSQL server thinks that '2013-01-21 09:24:16' is out of range. The only explanation I found is that it's wrong date format, but why it was working before? The iso8601 format (with T in the middle) should work even with non-english database localization settings. Try to run such query:

SELECT CAST('2013-01-21 09:24:16' AS datetime);
@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jan 22, 2013

Comment by vlastaw on 22 Jan 2013 10:18 UTC

Query executed successfully.

I checked these last 3 querries in the SQL log and the db itself if it matches the log and tried to run those queries directly over the db (just [and 2 obviously):

[10:57:50 +0100](22-Jan-2013): [INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('v7i334kve0k917gjvg32lu68q4', 'bGFuZ3VhZ2V8czo1OiJjc19DWiI7c2tpbnxzOjU6ImxhcnJ5IjtpbWFwX25hbWVzcGFjZXxhOjQ6e3M6ODoicGVyc29uYWwiO2E6MTp7aTowO2E6Mjp7aTowO3M6MDoiIjtpOjE7czoxOiIuIjt9fXM6NToib3RoZXIiO047czo2OiJzaGFyZWQiO2E6MTp7aTowO2E6Mjp7aTowO3M6NzoiI1B1YmxpYyI7aToxO3M6MToiLiI7fX1zOjY6InByZWZpeCI7czowOiIiO31pbWFwX2RlbGltaXRlcnxzOjE6Ii4iO3VzZXJfaWR8czoxOiIxIjt1c2VybmFtZXxzOjI2OiJ2bGFzdGltaWwud2FpY0BqYW9zb2JuZS5jeiI7c3RvcmFnZV9ob3N0fHM6MTE6Imphb3NvYm5lLmN6IjtzdG9yYWdlX3BvcnR8aToxNDM7c3RvcmFnZV9zc2x8TjtwYXNzd29yZHxzOjMyOiJISElOQmxYVnRTdStJTnF1M1E5MGxSbDJ5TFNxNTJ1QyI7bG9naW5fdGltZXxpOjEzNTg4NDg2NzA7dGltZXpvbmV8czoxMzoiRXVyb3BlL0JlcmxpbiI7', 'ip', '2013-01-22 10:57:50', '2013-01-22 10:57:50');
[22-Jan-2013 10:57:50 +0100](5]): [SELECT vars, ip, changed FROM session WHERE sess_id = 'v7i334kve0k917gjvg32lu68q4';
[22-Jan-2013 10:57:50 +0100](1]): [INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('v7i334kve0k917gjvg32lu68q4', 'dGVtcHxiOjE7bGFuZ3VhZ2V8czo1OiJlbl9VUyI7dGFza3xzOjU6ImxvZ2luIjs=', 'ip', '2013-01-22 10:57:50', '2013-01-22 10:57:50'); 

Query 5 executed successfully.
Query [errored with obvious reason: Violation of PRIMARY KEY constraint 'PK_session_sess_id'. Cannot insert duplicate key in object 'dbo.session'. The duplicate key value is (v7i334kve0k917gjvg32lu68q4).
The statement has been terminated.

Could this be the bug? Each login attempt produces this sequence of querries:

[1](2]) SELECT vars, ip, changed FROM session WHERE sess_id = 'sgmst8a4ukdfteopnib4lgo2f0';
[DELETE FROM session WHERE sess_id = 'sgmst8a4ukdfteopnib4lgo2f0';
[3](2]) SELECT * FROM users WHERE mail_host = 'host' AND username = 'vlasta@host';
[UPDATE users SET last_login = getdate() WHERE user_id = '2';
[5](4]) INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('08iglgvg6dbvf0n72hr5q6h196', 'bGFuZ3VhZ2V8czo1OiJlbl9VUyI7c2tpbnxzOjU6ImxhcnJ5IjtpbWFwX25hbWVzcGFjZXxhOjQ6e3M6ODoicGVyc29uYWwiO2E6MTp7aTowO2E6Mjp7aTowO3M6MDoiIjtpOjE7czoxOiIuIjt9fXM6NToib3RoZXIiO047czo2OiJzaGFyZWQiO2E6MTp7aTowO2E6Mjp7aTowO3M6NzoiI1B1YmxpYyI7aToxO3M6MToiLiI7fX1zOjY6InByZWZpeCI7czowOiIiO31pbWFwX2RlbGltaXRlcnxzOjE6Ii4iO3VzZXJfaWR8czoxOiIyIjt1c2VybmFtZXxzOjE4OiJ2bGFzdGFAamFvc29ibmUuY3oiO3N0b3JhZ2VfaG9zdHxzOjExOiJqYW9zb2JuZS5jeiI7c3RvcmFnZV9wb3J0fGk6MTQzO3N0b3JhZ2Vfc3NsfE47cGFzc3dvcmR8czozMjoiOFpmUW1vTG1oanZiWitTWWhCNkh6aW4vWWdWeFRhamQiO2xvZ2luX3RpbWV8aToxMzU4NzU2NjY5O3RpbWV6b25lfHM6MTM6IkV1cm9wZS9CZXJsaW4iOw==', 'IP', '2013-01-21 09:24:29', '2013-01-21 09:24:29');
[SELECT vars, ip, changed FROM session WHERE sess_id = '08iglgvg6dbvf0n72hr5q6h196';
[2](1]) INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('08iglgvg6dbvf0n72hr5q6h196', 'dGVtcHxiOjE7bGFuZ3VhZ2V8czo1OiJlbl9VUyI7dGFza3xzOjU6ImxvZ2luIjs=', 'IP', '2013-01-21 09:24:29', '2013-01-21 09:24:29');

So it tries to insert duplicate PK, which end up with SQL error.

Comment by vlastaw on 22 Jan 2013 10:18 UTC

Query executed successfully.

I checked these last 3 querries in the SQL log and the db itself if it matches the log and tried to run those queries directly over the db (just [and 2 obviously):

[10:57:50 +0100](22-Jan-2013): [INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('v7i334kve0k917gjvg32lu68q4', 'bGFuZ3VhZ2V8czo1OiJjc19DWiI7c2tpbnxzOjU6ImxhcnJ5IjtpbWFwX25hbWVzcGFjZXxhOjQ6e3M6ODoicGVyc29uYWwiO2E6MTp7aTowO2E6Mjp7aTowO3M6MDoiIjtpOjE7czoxOiIuIjt9fXM6NToib3RoZXIiO047czo2OiJzaGFyZWQiO2E6MTp7aTowO2E6Mjp7aTowO3M6NzoiI1B1YmxpYyI7aToxO3M6MToiLiI7fX1zOjY6InByZWZpeCI7czowOiIiO31pbWFwX2RlbGltaXRlcnxzOjE6Ii4iO3VzZXJfaWR8czoxOiIxIjt1c2VybmFtZXxzOjI2OiJ2bGFzdGltaWwud2FpY0BqYW9zb2JuZS5jeiI7c3RvcmFnZV9ob3N0fHM6MTE6Imphb3NvYm5lLmN6IjtzdG9yYWdlX3BvcnR8aToxNDM7c3RvcmFnZV9zc2x8TjtwYXNzd29yZHxzOjMyOiJISElOQmxYVnRTdStJTnF1M1E5MGxSbDJ5TFNxNTJ1QyI7bG9naW5fdGltZXxpOjEzNTg4NDg2NzA7dGltZXpvbmV8czoxMzoiRXVyb3BlL0JlcmxpbiI7', 'ip', '2013-01-22 10:57:50', '2013-01-22 10:57:50');
[22-Jan-2013 10:57:50 +0100](5]): [SELECT vars, ip, changed FROM session WHERE sess_id = 'v7i334kve0k917gjvg32lu68q4';
[22-Jan-2013 10:57:50 +0100](1]): [INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('v7i334kve0k917gjvg32lu68q4', 'dGVtcHxiOjE7bGFuZ3VhZ2V8czo1OiJlbl9VUyI7dGFza3xzOjU6ImxvZ2luIjs=', 'ip', '2013-01-22 10:57:50', '2013-01-22 10:57:50'); 

Query 5 executed successfully.
Query [errored with obvious reason: Violation of PRIMARY KEY constraint 'PK_session_sess_id'. Cannot insert duplicate key in object 'dbo.session'. The duplicate key value is (v7i334kve0k917gjvg32lu68q4).
The statement has been terminated.

Could this be the bug? Each login attempt produces this sequence of querries:

[1](2]) SELECT vars, ip, changed FROM session WHERE sess_id = 'sgmst8a4ukdfteopnib4lgo2f0';
[DELETE FROM session WHERE sess_id = 'sgmst8a4ukdfteopnib4lgo2f0';
[3](2]) SELECT * FROM users WHERE mail_host = 'host' AND username = 'vlasta@host';
[UPDATE users SET last_login = getdate() WHERE user_id = '2';
[5](4]) INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('08iglgvg6dbvf0n72hr5q6h196', 'bGFuZ3VhZ2V8czo1OiJlbl9VUyI7c2tpbnxzOjU6ImxhcnJ5IjtpbWFwX25hbWVzcGFjZXxhOjQ6e3M6ODoicGVyc29uYWwiO2E6MTp7aTowO2E6Mjp7aTowO3M6MDoiIjtpOjE7czoxOiIuIjt9fXM6NToib3RoZXIiO047czo2OiJzaGFyZWQiO2E6MTp7aTowO2E6Mjp7aTowO3M6NzoiI1B1YmxpYyI7aToxO3M6MToiLiI7fX1zOjY6InByZWZpeCI7czowOiIiO31pbWFwX2RlbGltaXRlcnxzOjE6Ii4iO3VzZXJfaWR8czoxOiIyIjt1c2VybmFtZXxzOjE4OiJ2bGFzdGFAamFvc29ibmUuY3oiO3N0b3JhZ2VfaG9zdHxzOjExOiJqYW9zb2JuZS5jeiI7c3RvcmFnZV9wb3J0fGk6MTQzO3N0b3JhZ2Vfc3NsfE47cGFzc3dvcmR8czozMjoiOFpmUW1vTG1oanZiWitTWWhCNkh6aW4vWWdWeFRhamQiO2xvZ2luX3RpbWV8aToxMzU4NzU2NjY5O3RpbWV6b25lfHM6MTM6IkV1cm9wZS9CZXJsaW4iOw==', 'IP', '2013-01-21 09:24:29', '2013-01-21 09:24:29');
[SELECT vars, ip, changed FROM session WHERE sess_id = '08iglgvg6dbvf0n72hr5q6h196';
[2](1]) INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('08iglgvg6dbvf0n72hr5q6h196', 'dGVtcHxiOjE7bGFuZ3VhZ2V8czo1OiJlbl9VUyI7dGFza3xzOjU6ImxvZ2luIjs=', 'IP', '2013-01-21 09:24:29', '2013-01-21 09:24:29');

So it tries to insert duplicate PK, which end up with SQL error.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jan 22, 2013

Comment by @alecpl on 22 Jan 2013 10:35 UTC

Replying to vlastaw:

[SELECT vars, ip, changed FROM session WHERE sess_id = '08iglgvg6dbvf0n72hr5q6h196';
[2](1]) INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('08iglgvg6dbvf0n72hr5q6h196', 'dGVtcHxiOjE7bGFuZ3VhZ2V8czo1OiJlbl9VUyI7dGFza3xzOjU6ImxvZ2luIjs=', 'IP', '2013-01-21 09:24:29', '2013-01-21 09:24:29');

So it tries to insert duplicate PK, which end up with SQL error.

Maybe the [1] query returns nothing or malformed information. Or "changed" date time in some strange format. Could you check if setting locale/language to English in MSSQL fixes the problem? Also why there's no out-of-range error when running these queries by hand?

Comment by @alecpl on 22 Jan 2013 10:35 UTC

Replying to vlastaw:

[SELECT vars, ip, changed FROM session WHERE sess_id = '08iglgvg6dbvf0n72hr5q6h196';
[2](1]) INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('08iglgvg6dbvf0n72hr5q6h196', 'dGVtcHxiOjE7bGFuZ3VhZ2V8czo1OiJlbl9VUyI7dGFza3xzOjU6ImxvZ2luIjs=', 'IP', '2013-01-21 09:24:29', '2013-01-21 09:24:29');

So it tries to insert duplicate PK, which end up with SQL error.

Maybe the [1] query returns nothing or malformed information. Or "changed" date time in some strange format. Could you check if setting locale/language to English in MSSQL fixes the problem? Also why there's no out-of-range error when running these queries by hand?

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jan 22, 2013

Comment by vlastaw on 22 Jan 2013 11:43 UTC

Unfortunatelly server locale set to US English (database with SQL_Latin_1 collation) did not do the trick.

What puzzles me is the out of range error being logged to Roundcube's log in CZECH, but my Windows Server and SQL Server are EN. I don't recall ever seeing SQL errors in czech. What determines the language of the error? Roundcube, PHP SQLSRV extension or the server itself?

Comment by vlastaw on 22 Jan 2013 11:43 UTC

Unfortunatelly server locale set to US English (database with SQL_Latin_1 collation) did not do the trick.

What puzzles me is the out of range error being logged to Roundcube's log in CZECH, but my Windows Server and SQL Server are EN. I don't recall ever seeing SQL errors in czech. What determines the language of the error? Roundcube, PHP SQLSRV extension or the server itself?

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jan 22, 2013

Comment by @alecpl on 22 Jan 2013 11:52 UTC

This definitely is not translated by Roundcube. I wouldn't give much chances to PHP too. So, this must be MS SQL server, but my knowledge of SQL Server is close to none.

Comment by @alecpl on 22 Jan 2013 11:52 UTC

This definitely is not translated by Roundcube. I wouldn't give much chances to PHP too. So, this must be MS SQL server, but my knowledge of SQL Server is close to none.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Mar 7, 2013

Comment by @alecpl on 7 Mar 2013 09:01 UTC

See this http://www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html for hints how to get/set default language of SQL Server.

Comment by @alecpl on 7 Mar 2013 09:01 UTC

See this http://www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html for hints how to get/set default language of SQL Server.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Mar 7, 2013

Comment by @alecpl on 7 Mar 2013 09:09 UTC

Check also the language setting of the SQL user you're using for Roundcube connection.

Comment by @alecpl on 7 Mar 2013 09:09 UTC

Check also the language setting of the SQL user you're using for Roundcube connection.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Mar 24, 2013

Comment by @alecpl on 24 Mar 2013 07:05 UTC

Any news on this? Without help of MSSQL user we'll not be able to do anything about this issue and we'll close it as configuration issue.

Comment by @alecpl on 24 Mar 2013 07:05 UTC

Any news on this? Without help of MSSQL user we'll not be able to do anything about this issue and we'll close it as configuration issue.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Mar 30, 2013

Comment by @alecpl on 30 Mar 2013 08:27 UTC

No feedback.

Comment by @alecpl on 30 Mar 2013 08:27 UTC

No feedback.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Mar 30, 2013

Status changed by @alecpl on 30 Mar 2013 08:27 UTC

new => closed

Status changed by @alecpl on 30 Mar 2013 08:27 UTC

new => closed

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 16, 2013

Comment by konus on 16 Apr 2013 16:24 UTC

I think I have the same problem:
New install of roundcube 0.9 (stable)
Manuell created a new mssql-Database.
During opening of roundcube, I already get the following error message:
In Browser

DB Error in C:\inetpub\wwwroot\roundcube\program\lib\Roundcube\rcube_db.php (416): [[Microsoft](242])[Server Native Client 11.0](SQL)[Server](SQL)Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert auerhalb des gltigen Bereichs.

an in errorlog

[19:15:39 +0300](16-Apr-2013): DB Error: [[Microsoft](242])[Server Native Client 11.0](SQL)[Server](SQL)Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert auerhalb des gltigen Bereichs. in C:\inetpub\wwwroot\roundcube\program\lib\Roundcube\rcube_db.php on line 416 (GET /)

I don't know where to find the sql-log

Config: Windows Server 2008 r2 / IIS 8 (PHP 5.3.13) + MS SQL 10.50.2500

Comment by konus on 16 Apr 2013 16:24 UTC

I think I have the same problem:
New install of roundcube 0.9 (stable)
Manuell created a new mssql-Database.
During opening of roundcube, I already get the following error message:
In Browser

DB Error in C:\inetpub\wwwroot\roundcube\program\lib\Roundcube\rcube_db.php (416): [[Microsoft](242])[Server Native Client 11.0](SQL)[Server](SQL)Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert auerhalb des gltigen Bereichs.

an in errorlog

[19:15:39 +0300](16-Apr-2013): DB Error: [[Microsoft](242])[Server Native Client 11.0](SQL)[Server](SQL)Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert auerhalb des gltigen Bereichs. in C:\inetpub\wwwroot\roundcube\program\lib\Roundcube\rcube_db.php on line 416 (GET /)

I don't know where to find the sql-log

Config: Windows Server 2008 r2 / IIS 8 (PHP 5.3.13) + MS SQL 10.50.2500

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 16, 2013

Status changed by konus on 16 Apr 2013 16:24 UTC

closed => reopened

Status changed by konus on 16 Apr 2013 16:24 UTC

closed => reopened

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 16, 2013

Comment by @alecpl on 16 Apr 2013 16:38 UTC

But did you read the discussion here? Could you provide more info about MS SQL server language settings?

Comment by @alecpl on 16 Apr 2013 16:38 UTC

But did you read the discussion here? Could you provide more info about MS SQL server language settings?

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 16, 2013

Milestone changed by @alecpl on 16 Apr 2013 16:38 UTC

0.9-stable => 0.9.1

Milestone changed by @alecpl on 16 Apr 2013 16:38 UTC

0.9-stable => 0.9.1

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 17, 2013

Comment by konus on 17 Apr 2013 06:21 UTC

Yes I read the discussion. Unfortunately the thread opener did not report back if and how he could solve the problem.
In my case the language of the MSSQL database is German "Deutsch (Deuschland)". I you think this could be a problem I will try to create a new database with English language settings.

Comment by konus on 17 Apr 2013 06:21 UTC

Yes I read the discussion. Unfortunately the thread opener did not report back if and how he could solve the problem.
In my case the language of the MSSQL database is German "Deutsch (Deuschland)". I you think this could be a problem I will try to create a new database with English language settings.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 17, 2013

Comment by @alecpl on 17 Apr 2013 06:34 UTC

Yes, I think language settings are the problem. This might be database or user settings.

Comment by @alecpl on 17 Apr 2013 06:34 UTC

Yes, I think language settings are the problem. This might be database or user settings.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 17, 2013

Comment by konus on 17 Apr 2013 15:42 UTC

You where right. After Changing my database to lang 1033 (us_english), the error is gone gone.

I first tried http:_www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html but it did not help. The I tried http:_coolkidsdoit.wordpress.com/2009/01/28/changing-the-default-languagelocale-sql-2005/

For me, it is now OK. Roundcube is running. However it would be nice, if the problem itself could be solved in the future. Maybe it is possible to use a date-format which is independent of the language of the database?

Comment by konus on 17 Apr 2013 15:42 UTC

You where right. After Changing my database to lang 1033 (us_english), the error is gone gone.

I first tried http:_www.sqlservercurry.com/2010/11/change-default-language-for-sql-server.html but it did not help. The I tried http:_coolkidsdoit.wordpress.com/2009/01/28/changing-the-default-languagelocale-sql-2005/

For me, it is now OK. Roundcube is running. However it would be nice, if the problem itself could be solved in the future. Maybe it is possible to use a date-format which is independent of the language of the database?

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 17, 2013

Comment by @alecpl on 17 Apr 2013 16:22 UTC

See my first comment. According to documentation Y-m-dTH:i:s format should be language independent. However, @vlastaw said it didn't work. Besides that, we have two problems with date format. One is setting date, the second is fetching date from database. I think without using database-specific queries we can't solve the second issue. Any help is appreciated. My knowledge about MSSQL is minimal.

Comment by @alecpl on 17 Apr 2013 16:22 UTC

See my first comment. According to documentation Y-m-dTH:i:s format should be language independent. However, @vlastaw said it didn't work. Besides that, we have two problems with date format. One is setting date, the second is fetching date from database. I think without using database-specific queries we can't solve the second issue. Any help is appreciated. My knowledge about MSSQL is minimal.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 24, 2013

Comment by @alecpl on 24 Apr 2013 06:30 UTC

I added a note to the INSTALL file about required language setting. That's all we can do for now.

Comment by @alecpl on 24 Apr 2013 06:30 UTC

I added a note to the INSTALL file about required language setting. That's all we can do for now.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 24, 2013

Status changed by @alecpl on 24 Apr 2013 06:30 UTC

reopened => closed

Status changed by @alecpl on 24 Apr 2013 06:30 UTC

reopened => closed

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 24, 2013

Comment by marra on 24 Apr 2013 20:12 UTC

Hello,

I also have this date conversion issue with MSSQL, but I think that changing MSSQL language settings is not necessary.
Since I'm not friendly with the PHP language i cannot submit a patch.

For what I can see from my MSSQL queries the solution might be to use the builtin CONVERT function.

for example:
CONVERT(datetime,'2013-01-21 09:24:29',120) converts this varchar value to datetime regardless of the culture.

to fetch the date from the db in varchar format you can use a query like this:

SELECT vars, ip, CONVERT(varchar, changed , 120) AS [changed] FROM session WHERE sess_id = 'blahblahblah';

the 120 number is the datetime style for the canonical ODBC representation.
more info at http://msdn.microsoft.com/en-us/library/ms187928.aspx

Comment by marra on 24 Apr 2013 20:12 UTC

Hello,

I also have this date conversion issue with MSSQL, but I think that changing MSSQL language settings is not necessary.
Since I'm not friendly with the PHP language i cannot submit a patch.

For what I can see from my MSSQL queries the solution might be to use the builtin CONVERT function.

for example:
CONVERT(datetime,'2013-01-21 09:24:29',120) converts this varchar value to datetime regardless of the culture.

to fetch the date from the db in varchar format you can use a query like this:

SELECT vars, ip, CONVERT(varchar, changed , 120) AS [changed] FROM session WHERE sess_id = 'blahblahblah';

the 120 number is the datetime style for the canonical ODBC representation.
more info at http://msdn.microsoft.com/en-us/library/ms187928.aspx

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 25, 2013

Status changed by marra on 25 Apr 2013 14:10 UTC

closed => reopened

Status changed by marra on 25 Apr 2013 14:10 UTC

closed => reopened

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 25, 2013

Comment by @alecpl on 25 Apr 2013 16:16 UTC

Replying to marra:

SELECT vars, ip, CONVERT(varchar, changed , 120) AS [changed] FROM session WHERE sess_id = 'blahblahblah';

Fetching date in correct format is one thing, but we need also use correct format on INSERT. Could anyone tell me why the solution from the first comment doesn't work?

Comment by @alecpl on 25 Apr 2013 16:16 UTC

Replying to marra:

SELECT vars, ip, CONVERT(varchar, changed , 120) AS [changed] FROM session WHERE sess_id = 'blahblahblah';

Fetching date in correct format is one thing, but we need also use correct format on INSERT. Could anyone tell me why the solution from the first comment doesn't work?

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 26, 2013

Comment by marra on 26 Apr 2013 10:43 UTC

Because CAST function uses by default the DB's culture settings.

To convert a datetime string regardless of the culture settings you have to use the CONVERT function that allows you to specify the format and the datetime style.

So a valid INSERT query for MSSQL should be like this:

INSERT INTO session (sess_id, vars, ip, created, changed) VALUES (
'v7i334kve0k917gjvg32lu68q4',
'dGVtcHxiOjE7bGFuZ3VhZ2V8czo1OiJlbl9VUyI7dGFza3xzOjU6ImxvZ2luIjs=',
'ip',
CONVERT(datetime,'2013-01-22 10:57:50', 120),
CONVERT(datetime,'2013-01-22 10:57:50', 120) );

Comment by marra on 26 Apr 2013 10:43 UTC

Because CAST function uses by default the DB's culture settings.

To convert a datetime string regardless of the culture settings you have to use the CONVERT function that allows you to specify the format and the datetime style.

So a valid INSERT query for MSSQL should be like this:

INSERT INTO session (sess_id, vars, ip, created, changed) VALUES (
'v7i334kve0k917gjvg32lu68q4',
'dGVtcHxiOjE7bGFuZ3VhZ2V8czo1OiJlbl9VUyI7dGFza3xzOjU6ImxvZ2luIjs=',
'ip',
CONVERT(datetime,'2013-01-22 10:57:50', 120),
CONVERT(datetime,'2013-01-22 10:57:50', 120) );

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 26, 2013

Comment by @alecpl on 26 Apr 2013 11:06 UTC

Are you 100% sure, we can't use 'Y-m-dTH:i:s' format? I found in MSSQL documentation that it's language/locale independent. It would simplify at least INSERT/UPDATEs.

Comment by @alecpl on 26 Apr 2013 11:06 UTC

Are you 100% sure, we can't use 'Y-m-dTH:i:s' format? I found in MSSQL documentation that it's language/locale independent. It would simplify at least INSERT/UPDATEs.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 29, 2013

Milestone changed by @alecpl on 29 Apr 2013 08:10 UTC

0.9.1 => 1.0-beta

Milestone changed by @alecpl on 29 Apr 2013 08:10 UTC

0.9.1 => 1.0-beta

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Apr 29, 2013

Comment by marra on 29 Apr 2013 11:02 UTC

As far as I know you have to use the CONVERT function to correctly parse dates when you know the source style (as specified in point G. of the article I mentioned before).

CAST works by trying to match the best datetime style, so in my opinion, it's unreliable in your case.

Comment by marra on 29 Apr 2013 11:02 UTC

As far as I know you have to use the CONVERT function to correctly parse dates when you know the source style (as specified in point G. of the article I mentioned before).

CAST works by trying to match the best datetime style, so in my opinion, it's unreliable in your case.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac May 31, 2013

Comment by @alecpl on 31 May 2013 08:02 UTC

I'm curious if executing query: "SET LANGUAGE English", just after connection to db is established, would resolve this issue? Could you try to add

$this->query("SET LANGUAGE English");

in set_charset() method of rcube_db_mssql and rcube_db_sqlsrv class?[[BR]]
If not SET LANGUAGE, then at least "SET DATEFORMAT ymd" should resolve problem with input format. We'd need only to take care on query results.

Comment by @alecpl on 31 May 2013 08:02 UTC

I'm curious if executing query: "SET LANGUAGE English", just after connection to db is established, would resolve this issue? Could you try to add

$this->query("SET LANGUAGE English");

in set_charset() method of rcube_db_mssql and rcube_db_sqlsrv class?[[BR]]
If not SET LANGUAGE, then at least "SET DATEFORMAT ymd" should resolve problem with input format. We'd need only to take care on query results.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jun 1, 2013

Comment by marra on 1 Jun 2013 21:33 UTC

With that modification I'm able to login and use RC, even if it's a bit slow on showing messages.
The set command works either way.
Still, the error log shows some failed queries.

My setup:
RC 0.9.1 (fresh install)
MSSQL 2k8 SP3 (Dedicated test db)

Comment by marra on 1 Jun 2013 21:33 UTC

With that modification I'm able to login and use RC, even if it's a bit slow on showing messages.
The set command works either way.
Still, the error log shows some failed queries.

My setup:
RC 0.9.1 (fresh install)
MSSQL 2k8 SP3 (Dedicated test db)

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jun 2, 2013

Comment by @alecpl on 2 Jun 2013 12:18 UTC

Strange, the log doesn't contain real error message. Could you run this queries by hand and see what's the problem? Maybe getdate()? Also, which modification did you already do (SET LANGUAGE or SET DATEFORMAT)?

Comment by @alecpl on 2 Jun 2013 12:18 UTC

Strange, the log doesn't contain real error message. Could you run this queries by hand and see what's the problem? Maybe getdate()? Also, which modification did you already do (SET LANGUAGE or SET DATEFORMAT)?

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jun 7, 2013

Comment by marra on 7 Jun 2013 14:40 UTC

I tried both solutions, and both let me login.
If I had to choose I'd pick SET DATEFORMAT.

I think the problem in the queries is the NUL chars inside the query text, it seems some kind of garbage in the 'data' field.
Even if I try to copy the query text I get only a part of the query.

INSERT INTO cache_index (user_id, mailbox, data, valid, changed) VALUES ('1', 'INBOX', 'O:18:"rcube_result_index":5:{s:11:"'

In another log, the same query type reports "syntax error in proximity of.." and also other queries (INSERT INTO cache_messages, cache_index) have NUL, SOH, BEL chars in the 'data field'.

Comment by marra on 7 Jun 2013 14:40 UTC

I tried both solutions, and both let me login.
If I had to choose I'd pick SET DATEFORMAT.

I think the problem in the queries is the NUL chars inside the query text, it seems some kind of garbage in the 'data' field.
Even if I try to copy the query text I get only a part of the query.

INSERT INTO cache_index (user_id, mailbox, data, valid, changed) VALUES ('1', 'INBOX', 'O:18:"rcube_result_index":5:{s:11:"'

In another log, the same query type reports "syntax error in proximity of.." and also other queries (INSERT INTO cache_messages, cache_index) have NUL, SOH, BEL chars in the 'data field'.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jun 7, 2013

Comment by @alecpl on 7 Jun 2013 16:11 UTC

Replying to marra:

I think the problem in the queries is the NUL chars inside the query text, it seems some kind of garbage in the 'data' field.

This one is a duplicate of #1489142

Comment by @alecpl on 7 Jun 2013 16:11 UTC

Replying to marra:

I think the problem in the queries is the NUL chars inside the query text, it seems some kind of garbage in the 'data' field.

This one is a duplicate of #1489142

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jun 7, 2013

Comment by marra on 7 Jun 2013 18:44 UTC

I've updated to the git-master version and I applied the

$this->query("SET DATEFORMAT ymd");

patch on rcube_db_mssql and rcube_db_sqlsrv files.

I've got no more errors on my logfile.
This bug seems finally fixed.

Comment by marra on 7 Jun 2013 18:44 UTC

I've updated to the git-master version and I applied the

$this->query("SET DATEFORMAT ymd");

patch on rcube_db_mssql and rcube_db_sqlsrv files.

I've got no more errors on my logfile.
This bug seems finally fixed.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jun 8, 2013

Comment by @alecpl on 8 Jun 2013 18:15 UTC

Fixed in 66407a7.

Comment by @alecpl on 8 Jun 2013 18:15 UTC

Fixed in 66407a7.

@rcubetrac

This comment has been minimized.

Show comment
Hide comment
@rcubetrac

rcubetrac Jun 8, 2013

Status changed by @alecpl on 8 Jun 2013 18:15 UTC

reopened => closed

Status changed by @alecpl on 8 Jun 2013 18:15 UTC

reopened => closed

@rcubetrac rcubetrac added the P2 label Mar 20, 2016

@rcubetrac rcubetrac added this to the 1.0-beta milestone Mar 20, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment