Logins fail after 1.3.0 upgrade; works on 1.2.5

[paulschreiber]

I have Roundcube checked out from git. When I from upgrade from 1.2.5 to 1.3.0 (git checkout 1.3.0), logins fail with the "Invalid request! No data was saved." error. Reverting to 1.2.5 (git checkout 1.2.5) allows logins to occur successfully.

I tried the following, with no effect:
Running bin/install-jsdeps.sh, bin/installto.sh, bin/indexcontacts.sh and bin/cleandb.sh
Enabling both $config['force_https'] and $config['use_https']
Enabling only $config['force_https']
Truncating the session and cache_* tables

Logs: nothing is added to logs/imap when these failures occur; logs/sql shows an INSERT to the session table

config:
- Ubuntu 16.04.2
- dovecot 2.2.22
- apache 2.4.18
- postfix 3.1.0
$config['default_port'] = 993;
$config['default_host'] = 'ssl://myhostname.com';


Paul

[SKaero]

If you upgrading you should run bin/update.sh

What is your PHP version?
Are you using any plugins?

[paulschreiber]

• PHP 7.1 (PHP 7.1.6-1~ubuntu16.04.1+deb.sury.org+1)
• Plugins: $config['plugins'] = array('attachment_reminder', 'emoticons', 'help', 'markasjunk', 'newmail_notifier', 'password', 'twofactor_gauthenticator');

Updates:
• Updated composer to 1.4.2
• Overwrite composer.json with composer.json-dist
• Ran composer update --no-dev

bin/update.sh still fails:

Code Select Expand


What version are you upgrading from? Type '?' if you don't know.
?
Executing database schema update.
Updating database schema (2015111100)... [FAILED]
NOTE: Update dependencies by running `php composer.phar update --no-dev`


Here's the SQL it's trying to run:

Code Select Expand


# more 2015111100.sql
ALTER TABLE `users`
  ADD `failed_login` datetime DEFAULT NULL,
  ADD `failed_login_counter` int(10) UNSIGNED DEFAULT NULL;

# more SQL/mysql/2016081200.sql
ALTER TABLE `session` DROP COLUMN `created`;


Running this manually:

Code Select Expand


mysql> ALTER TABLE `users`
    ->   ADD `failed_login` datetime DEFAULT NULL,
    ->   ADD `failed_login_counter` int(10) UNSIGNED DEFAULT NULL;
ERROR 1067 (42000): Invalid default value for 'created'

ALTER TABLE `session` DROP COLUMN `created`;
ERROR 1067 (42000): Invalid default value for 'changed'


Looking in mysql.initial.sql, I see the default values for created are 1000-01-01 00:00:00.

Aside: per ]https://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at], this is due to sql_mode having NO_ZERO_IN_DATE,NO_ZERO_DATE.

Aha. Running this SQL manually:
•

Code Select Expand


alter table users modify created datetime NOT NULL default '1000-01-01 00:00:00';
alter table session modify changed datetime NOT NULL default '1000-01-01 00:00:00', modify created datetime NOT NULL default '1000-01-01 00:00:00';


Lets me run the script's SQL:

Code Select Expand


ALTER TABLE `users` ADD `failed_login` datetime DEFAULT NULL, ADD `failed_login_counter` int(10) UNSIGNED DEFAULT NULL;
ALTER TABLE `session` DROP COLUMN `created`;
ALTER TABLE `dictionary` ADD COLUMN `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; -- redundant, for compat. with Galera Cluster

DROP TABLE `cache`;
DROP TABLE `cache_shared`;

CREATE TABLE `cache` (
`user_id` int(10) UNSIGNED NOT NULL,
`cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
`expires` datetime DEFAULT NULL,
`data` longtext NOT NULL,
PRIMARY KEY (`user_id`, `cache_key`),
CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `expires_index` (`expires`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;


CREATE TABLE `cache_shared` (
`cache_key` varchar(255) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
`expires` datetime DEFAULT NULL,
`data` longtext NOT NULL,
PRIMARY KEY (`cache_key`),
INDEX `expires_index` (`expires`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;

update system set value='2016112200';


Then I was able to log in. (Aside: I had to reauth my 2FA. And log in twice. But I'm in.)

[paulschreiber]

Filed as https://github.com/roundcube/roundcubemail/issues/5833

[jpyllman]

I also get the 'Invalid Request. No data saved' message when trying to login after upgrade to 1.3.0. I upgraded from 1.2.4. And my upgrade report that everything when fine in upgrading the database. So it must be something else that is wrong here.

[jpyllman]

OK, for me it looks like it was access rights that was broken. After the upgrade the wrong user was set on the files. But after changing that it works. But now I fear that my installation is hackable. So what is the correct access rights on all parts of the installation??

[jpyllman]

OK, for me it looks like it was access rights that was broken. After the upgrade the wrong user was set on the files. But after changing that it works. But now I fear that my installation is hackable. So what is the correct access rights on all parts of the installation??

Just to clarify why I had this problem. I was running sqlite database. And sqlite has an extra access right demand. And that is that the user need to have write access to the folder where the sqllite database are. So after a upgrade I need to change the owner of that folder. Nothing else.