Error during setup, then unable to register/login

[moltenkaizen]

Cloned 1.7.1 on subdomain. Setup produces this error:

A Database Error Occurred
Error Number: 1067
Invalid default value for 'datearchived'
ALTER TABLE users_marks ADD datearchived timestamp NOT NULL
Filename: /var/www/unmark/migrations/002_archive_stats.php
Line Number: 14

If I ignore and refresh I'm redirected to the /register url. No matter what password I try, I'm unable to register. An empty red box appears under password field. Checked mysql db, I see tables in unmark. I could be doing something wrong during setup, thanks for the help.
| Tables_in_unmark |
+-------------------+
| groups |
| groups_invites |
| marks |
| migrations |
| users |
| users_groups |
| users_marks |
| users_smartlabels

[moltenkaizen]

noted error in console:
A Database Error Occurred
Error Number: 1054 Unknown column 'users.user_id' in 'field list'

[cdevroe]

@moltenkaizen Was this a fresh install or an upgrade? If an upgrade... did you run /upgrade ?

[moltenkaizen]

@cdevroe A fresh install here. I've been hacking around trying to get it setup for a few days off and on. I'm sure I must be missing something. I'll drop the db and reclone from git to start fresh.

My steps and the results:

• DROP DATABASE unmark;
• CREATE DATABASE unmark DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
• rm -rf unmark
• git clone https://github.com/plainmade/unmark.git
• chown www-data.www-data unmark/
• edit unmark/application/config/database.php with mysql info
• hit unmark.webserver.com/setup

A Database Error Occurred

Error Number: 1067

Invalid default value for 'datearchived'

ALTER TABLE `users_marks` ADD `datearchived` timestamp NOT NULL

Filename: /var/www/unmark/migrations/002_archive_stats.php

Line Number: 14

• Refresh page: it redirects to /register
• Attempt to register

Note error in console:

        A Database Error Occurred
        Error Number: 1054 Unknown column 'users.user_id' in 'field list'
            SELECT
            COUNT(users.user_id) AS total
            FROM `users` WHERE email = 'test@test.com' Filename: /var/www/unmark/core/Plain_Model
.php Line Number: 71
    </div>

[cdevroe]

Thanks @moltenkaizen. Odd. I do not get that issue (or any at the moment). Hopefully we can get to the bottom of it.

[cdevroe]

Btw, this is the line in question: https://github.com/plainmade/unmark/blob/master/application/migrations/002_archive_stats.php#L14

Oddly enough a default value is not being specified (which might be the problem?).

[cdevroe]

BTW, the error you're getting AFTER running /setup is because migrations are not completing for you. Your database is woefully out-of-date. You could try running /upgrade but that likely will result in a similar error.

I'm going to try a fresh install here yet again. And see if I can't see that same issue.

[cdevroe]

Can you try a fresh install with https://github.com/plainmade/unmark/releases/tag/v1.7.1.1 - Thank you.

I don't know if your issue is related but I've been unable to replicate it here so perhaps it is.

[moltenkaizen]

hey @cdevroe , I tried the new build on the Ubuntu 14 server I'd been working with, but unfortunately the same error after hitting the install link on /setup

A Database Error Occurred
Error Number: 1067
Invalid default value for 'datearchived'
ALTER TABLE `users_marks` ADD `datearchived` timestamp NOT NULL
Filename: /var/www/unmark/migrations/002_archive_stats.php
Line Number: 14

I wanted to rule out that server by trying another. I setup from scratch on Arch Linux with nginx 1.10.1-1/ mariadb 10.1.17-1 / php 7.0.11. Unmark installed and is running just fine.

My ulimate goal was to have it installed on a production server running Ubuntu 14. Maybe I'll re-image the Ubuntu 14 VM I've been testing on and report the results.

[cdevroe]

@moltenkaizen Can you give us the phpinfo() from both servers? Also, your application logs once development environment is turned on. If possible. Thanks so much.

[cdevroe]

@moltenkaizen I don't want to close this issue until you've been able to get us the info. Obviously it is an environmental issue. But I wouldn't mind knowing what it is ... especially for posterity's sake.

[moltenkaizen]

@cdevroe both arch and ubuntu 14 are working now. I can still post the configs if desired, but the latest release appears to be working. The solution was to installing a missing dependency. I don't remember exactly which one it was but I think it was a php module for mysql. Thanks for all your efforts

[neopostmodern]

I reproduced this on Ubuntu 16.04 I think. Fresh install. MySQL 5.7.15
phpinfo().txt (not allowed to upload as .html, rename yourself)

Edit: Installed from latest release, not master.

[cdevroe]

@neopostmodern I believe this bug is squashed in latest release. If you're still getting it you'll need to share your log files with us.

[neopostmodern]

@cdevroe Trying... In index.php I set

define('ENVIRONMENT', 'development');

Then hit /upgrade and get

A Database Error Occurred
Error Number: 1067
Invalid default value for 'datearchived'
ALTER TABLE `users_marks` ADD `datearchived` timestamp NOT NULL
Filename: /var/www/unmark/migrations/002_archive_stats.php
Line Number: 14

but application/logs shows no entries. /var/log/nginx/error.log empty, /var/log/php7.0-fpm.log contains nothing related. Where else to look?

[gameame]

I can reproduce this bug starting unmark in a Docker container with docker-compose using as db the standard image mysql, which is mysqld 5.7.16.
The setup finishes correctly with image mysql:5.6 (mysqld 5.6.34).

[cdevroe]

@gameame Good catch.

I wonder how we can go back in and make it backwards compatible (and forwards compat) for mySQL. I'd have to do some research there. cc @neopostmodern

[neopostmodern]

So, in the end I managed to solve this! For me at least, because the back- and forward compatibility will have to be figured out by someone who actually understands mySQL.

I basically added one default current_timestamp and dropped a bunch of default \'0000-00-00 00:00:00\'.
This is a diff of what I did:

14c14
<       $this->dbforge->add_column('users_marks', array('datearchived' => array('type'=>'timestamp default current_timestamp')));
---
>       $this->dbforge->add_column('users_marks', array('datearchived' => array('type'=>'timestamp')));
22c22
< }
---
> }
\ No newline at end of file
diff /var/www/unmark/application/migrations/003_users_update.php /home/neopostmodern/Downloads/unmark-master/application/migrations/003_users_update.php
18c18
<       $this->db->query('ALTER TABLE `users` CHANGE COLUMN `datejoined` `date_joined` datetime NOT NULL');
---
>       $this->db->query('ALTER TABLE `users` CHANGE COLUMN `datejoined` `date_joined` datetime NOT NULL DEFAULT \'0000-00-00 00:00:00\'');
40c40
< }
---
> }
\ No newline at end of file
diff /var/www/unmark/application/migrations/007_batshit_crazy.php /home/neopostmodern/Downloads/unmark-master/application/migrations/007_batshit_crazy.php
57c57
<           `created_on` datetime NOT NULL,
---
>           `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
179c179
<       $this->db->query("ALTER TABLE `marks` CHANGE COLUMN `dateadded` `created_on` datetime NOT NULL COMMENT 'The datetime this record was created.'");
---
>       $this->db->query("ALTER TABLE `marks` CHANGE COLUMN `dateadded` `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The datetime this record was created.'");
260c260
<       $this->db->query("ALTER TABLE `users` CHANGE COLUMN `date_joined` `created_on` datetime NOT NULL COMMENT 'The datetime the account was created'");
---
>       $this->db->query("ALTER TABLE `users` CHANGE COLUMN `date_joined` `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The datetime the account was created'");
353c353
<       $this->db->query("ALTER TABLE `users_to_marks` CHANGE COLUMN `dateadded` `created_on` datetime NOT NULL COMMENT 'The datetime this record was created.'");
---
>       $this->db->query("ALTER TABLE `users_to_marks` CHANGE COLUMN `dateadded` `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The datetime this record was created.'");
596c596
<       $this->db->query("ALTER TABLE `users` CHANGE COLUMN `created_on` `date_joined` datetime NOT NULL");
---
>       $this->db->query("ALTER TABLE `users` CHANGE COLUMN `created_on` `date_joined` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'");
602c602
< }
---
> }
\ No newline at end of file
diff /var/www/unmark/application/migrations/010_tokens.php /home/neopostmodern/Downloads/unmark-master/application/migrations/010_tokens.php
21c21
<           `created_on` DATETIME NOT NULL COMMENT 'Creation date',
---
>           `created_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Creation date',
41c41
< }
---
> }
\ No newline at end of file

This only fixes the setup. It's still not actually working (after login) but I'll file seperate bugs for that. Has anybody run this on mySQL 5.7 yet?

[skeith]

I got the same error

• cloned the repo
• Ubuntu 16.04
• MySQL 5.7.16, PHP 7.0.8

[gameame]

@neopostmodern the first line of your diff is missing. To which file is it referred?

If anyone is confident with docker, I set up a docker-compose configuration: https://github.com/gameame/docker-unmark
It's very handy to test database and php compatibility.

[neopostmodern]

@gameame Sorry, bad copy-paste. Just looked it up manually and it must be /var/www/unmark/application/migrations/002_archive_stats.php. I have zero knowledge of Docker though.

[ccov]

I've managed to work this out in 5.7.13 with sql-mode=""
More info here:
http://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at

[smth]

I just got this error with a fresh install on OSX.

logs contained:

ERROR - 2017-07-23 17:32:51 --> Query error: Invalid default value for 'datearchived'

[cdevroe]

This issue still exists for some. So I'm working with some that is still having this issue and going through and adjusting the old migrations files.

[cdevroe]

I will likely do a small point release to handle this.

[cdevroe]

This is going to be addressed in 1.7.1.3. During this we've also identified other issues that some may run into using mySQL straight out of the box with macOS. I'll create a new issue for those things though.

[solarchemist]

Sorry to reopen this, but I'm getting exactly this error on a fresh unmark install on Ubuntu server 16.04.

I can report that following the exact same unmark install procedure, unmark fails with the above error on Ubuntu server 16.04, but succeeds on Debian 8.9. I did extensive tests on Ubuntu, testing both the latest release and a clone of the git repo (cleaning everything out in-between).

For now I'm ok with simply moving unmark to a Debian host (my db is not very large) but I just wanted to confirm that the error reported by others above is well and alive on Ubuntu, and it is very likely related to something specific to Ubuntu.

The Ubuntu 16.04 server runs PHP 7.0.22-0ubuntu0.16.04.1 and MySQL 5.7.19.
The Debian 8.9 host runs PHP 5.6.30-0+deb8u1 and MySQL 5.5.57.

In the hopes this might help someone else.

[contactek]

I run unmark on IIS (windows server 2012 R2), PHP7 and MySQL 5.7 with Workbench 6.3
I create "unmark" database using Workbench GUI...
When you click on "create a new schema..." icon (creates a new database) you will get a windows where you input the name of your database. In this case you enter "unmark" for the name. Then click APPLY button. You will get another window "Apply SQL Script to Database". You will see that there is already a command there: CREATE SCHEMA unmark ;

now, all you have to do is put the following command above: SET GLOBAL sql_mode = '';
So, your script should look like this:

SET GLOBAL sql_mode = '';
CREATE SCHEMA unmark ;

Finish it up... you will now have a database that you can install unmark to...