Emoncms "Error creating user"

Stian · 2018-02-09T08:08:53.927Z

@pb66, my info is basically the same as @belba, see bellow:

uname -a
Linux vir5ubuntu 4.4.0-87-generic #110-Ubuntu SMP Tue Jul 18 12:55:35 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

mysqld -V
mysqld Ver 5.7.21-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu))

apache2 -v
Server version: Apache/2.4.18 (Ubuntu)
Server built: 2017-09-18T15:09:02

php -v
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib/php/20151012/redis.so’ - /usr/lib/php/20151012/redis.so: cannot open shared object file: No such file or directory in Unknown on line 0
PHP 7.0.22-0ubuntu0.16.04.1 (cli) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
with Zend OPcache v7.0.22-0ubuntu0.16.04.1, Copyright (c) 1999-2017, by Zend Technologies

redis-server -v
Redis server v=3.0.6 sha=00000000:0 malloc=jemalloc-3.6.0 bits=64 build=687a2a319020fa42

I did not manage to create a user manually in the database, I tried this

mysql> INSERT INTO users ( username, password, email, salt ,apikey_read, apikey_write, admin ) VALUES (‘emoncms’, ‘testpwd’, ‘email@gmail.com’,‘1’, ‘1’,‘1’,‘1’);

ERROR 1364 (HY000): Field ‘tags’ doesn’t have a default value

Any idea why I get the error? What is the value of apikey_read, apikey_write and admin?

rostwald · 2018-02-09T08:41:01.000Z

Hello Paul,

I’m not at work today, but here’s what I know from the top of my head:

After manually creating a user and resetting the PW I was able to
access the admin panel. I didn’t test creating/modifying anythnig, but
from browsing around in the backend no errors were logged either by
apache or emoncms; so it seemed to work nominal.

All packages were installed from pkgsrc.joyent.com. These packages are
usually in sync with NetBSD package Versions.

Apache 2.4
PHP 7.2
MySQL 5.7
redis 4.0.8

Enabling or disabling redis didn’t change the behaviour; in both cases
the error was reproducible.

I will give you the system details, the rest of the packages and
full version numbers on monday when I’m back at work and have access to
the system.

Thanks,
Sebastian

markafleming · 2018-02-09T09:17:24.382Z

Unfortunately I won’t be on site again until at least next week to get the Admin settings. However I can say the installation is on a bare Ubuntu 16.04LTS + NodeRED + MySQL + PHP7. Redis is installed but I don’t know if it is enabled

pb66 · 2018-02-09T11:32:17.031Z

belba:

Should I post the detailed information further here or in the github issue thread?

Lets keep it in one place for now, thanks @belba.

Stian:

Any idea why I get the error? What is the value of apikey_read, apikey_write and admin?

Sorry @Stian no idea on the error at the moment. The admin field is 1 for an emoncms administrator and 0 for all other users. By default only the first user created is admin. Both the apikey field are unencrypted 32char 128 bit hexadecimal string apikeys (eg 5cd93050a8163115c85590a428f27fd7)

rostwald:

Enabling or disabling redis didn’t change the behaviour; in both cases

the error was reproducible.

Thanks @rostwald

markafleming:

the installation is on a bare Ubuntu 16.04LTS + NodeRED + MySQL + PHP7

Thanks @markafleming

Hopefully @TrystanLea can find the time to take a look soon, it’s a bit beyond my emoncms knowledge.

belba · 2018-02-09T15:05:30.829Z

I hacked a little bit PHP-code to get a access to the Administration Page of emoncms, here is my result:

Server Information

| | | |
| --- | --- | --- |
|Emoncms|Version|9.8.28 : 2018.01.27
||Modules|Administration : App v1.1.0 : Dashboard v1.1.1 : EventProcesses : Feed : Input : CoreProcess : Schedule : Time : User : Visualisation
|Server|OS|Linux 4.13.0-32-generic
||Host|Solarlogger Solarlogger (127.0.1.1)
||Date|2018-02-09 16:00:46 CET
||Uptime| 16:00:46 up  2:21,  1 user,  load average: 1.65, 0.74, 0.36
|HTTP|Server|Apache/2.4.18 (Ubuntu) HTTP/1.1 CGI/1.1 80
|MySQL|Version|5.7.21-0ubuntu0.16.04.1
||Host|localhost (127.0.0.1)
||Date|2018-02-09 16:00:46 (UTC 01:00‌)
||Stats|Uptime: 8455  Threads: 1  Questions: 1610  Slow queries: 0  Opens: 184  Flush tables: 1  Open tables: 89  Queries per second avg: 0.190
|Memory|RAM|Used: 59.39% Total: 987.51 MB Used: 586.52 MB Free: 400.99 MB
||Swap|Used: 4.82% Total: 1012 MB Used: 48.75 MB Free: 963.25 MB
|Disk|Mount|Stats
||/|Used: 5.88% Total: 71.77 GB Used: 4.22 GB Free: 63.89 GB
||/boot|Used: 25.23% Total: 471.6 MB Used: 119 MB Free: 328.26 MB
|PHP|Version|7.0.25-0ubuntu0.16.04.1 (Zend Version 3.0.0)
||Modules|apache2handler : calendar v7.0.25-0ubuntu0.16.04.1 : Core v7.0.25-0ubuntu0.16.04.1 : ctype v7.0.25-0ubuntu0.16.04.1 : curl v7.0.25-0ubuntu0.16.04.1 : date v7.0.25-0ubuntu0.16.04.1 : dom v20031129 : exif v1.4 : fileinfo v1.0.5 : filter v7.0.25-0ubuntu0.16.04.1 : ftp v7.0.25-0ubuntu0.16.04.1 : gettext v7.0.25-0ubuntu0.16.04.1 : hash v1.0 : iconv v7.0.25-0ubuntu0.16.04.1 : json v1.4.0 : libxml v7.0.25-0ubuntu0.16.04.1 : mcrypt v7.0.25-0ubuntu0.16.04.1 : mysqli v7.0.25-0ubuntu0.16.04.1 : mysqlnd : openssl v7.0.25-0ubuntu0.16.04.1 : pcre v7.0.25-0ubuntu0.16.04.1 : PDO v7.0.25-0ubuntu0.16.04.1 : pdo_mysql v7.0.25-0ubuntu0.16.04.1 : Phar v2.0.2 : posix v7.0.25-0ubuntu0.16.04.1 : readline v7.0.25-0ubuntu0.16.04.1 : redis v3.1.6 : Reflection v7.0.25-0ubuntu0.16.04.1 : session v7.0.25-0ubuntu0.16.04.1 : shmop v7.0.25-0ubuntu0.16.04.1 : SimpleXML v7.0.25-0ubuntu0.16.04.1 : sockets v7.0.25-0ubuntu0.16.04.1 : SPL v7.0.25-0ubuntu0.16.04.1 : standard v7.0.25-0ubuntu0.16.04.1 : sysvmsg v7.0.25-0ubuntu0.16.04.1 : sysvsem v7.0.25-0ubuntu0.16.04.1 : sysvshm v7.0.25-0ubuntu0.16.04.1 : tokenizer v7.0.25-0ubuntu0.16.04.1 : wddx v7.0.25-0ubuntu0.16.04.1 : xml v7.0.25-0ubuntu0.16.04.1 : xmlreader v7.0.25-0ubuntu0.16.04.1 : xmlwriter v7.0.25-0ubuntu0.16.04.1 : xsl v7.0.25-0ubuntu0.16.04.1 : Zend OPcache v7.0.25-0ubuntu0.16.04.1 : zlib v7.0.25-0ubuntu0.16.04.1 : 
</pre></details>
<details><summary>Client Information</summary><pre>

| | | |
| --- | --- | --- |
|HTTP|Browser|Mozilla/5.0 (X11; Ubuntu; Linux i686; rv:58.0) Gecko/20100101 Firefox/58.0
|Screen|Resolution|1280 x 1024
|Window|Size|1138 x 826
</pre></details>

belba · 2018-02-09T15:53:06.374Z

Maybe I have found the possible problem:

The column “tags” from the table “users” is not installed as default null. That means that the DB expect a input for that column which is not given at the first registration.

The possible solution for that problem is:

ALTER TABLE `users` CHANGE `tags` `tags` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

This could be solve the problem, but…

WARNING!

I think a developer should verify my post and agree if that solves the problem.
All other who use this change of the database make it on their own risk.

PS: I wonder very much that such kind of errors are not found at any logs! Why?

pb66 · 2018-02-09T16:33:23.263Z

I had hoped that backdating to 9.8.10 would have ruled out the changes to the user schema, perhaps the emoncms tables need to be dropped when backdating so that new tables are created when creating the first user with 9.8.10.

There have been 3 changes to the “tags” field in the user schema

First introduced Aug 17

github.com/emoncms/emoncms

Tags added to user_schema

committed 01:03PM - 07 Aug 17 UTC

cagabi

+1 -0

then changed by @TrystanLea in Dec17

github.com/emoncms/emoncms

tags cannot have default value

committed 10:25AM - 10 Dec 17 UTC

+1 -1

and again in Jan 18

github.com/emoncms/emoncms

fix 'not null' db schema column creation

committed 12:04PM - 08 Jan 18 UTC

+12 -11

So you might well be on to something, sorry I can’t be of more help.

rostwald · 2018-02-12T08:49:29.962Z

I’m back at work and as promised here are the details to our installation:

[root@emoncms ~]# uname -a
SunOS emoncms 5.11 joyent_20171026T003127Z i86pc i386 i86pc
[root@emoncms ~]# mysqld -V
mysqld  Ver 5.7.20 for solaris11 on x86_64 (Source distribution)
[root@emoncms ~]# httpd -v
Server version: Apache/2.4.29 (Unix)
Server built:   Jan  3 2018 12:04:u5408 
[root@emoncms ~]# php -v
PHP 7.2.1 (cli) (built: Jan  8 2018 16:36:39) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2017 Zend Technologies
[root@emoncms ~]# redis-server -v
Redis server v=4.0.6 sha=00000000:0 malloc=libc bits=64 build=e1320b37e529b7ca

I can confirm the sql error about ‘tags’ not having a default value:

mysql> INSERT INTO users ( username, password, email, salt ,apikey_read, apikey_write, admin ) VALUES ('test','8c624673cd2319e39b521358f0eba98aa82db07380f10706d8429532d0c9a4f6','test@test.test','24f2d5d96fd5a640c7724a36021d372b','9abdeb7512ced8437e63553b21eb47e7','96991b45ca4806927be38a173b555f85',0);
ERROR 1364 (HY000): Field 'tags' doesn't have a default value

(edit) This is the actual statement generated by the emoncms register form; logged by activating @@general_log.

rostwald · 2018-02-12T10:19:36.642Z

belba:

Maybe I have found the possible problem:

The column “tags” from the table “users” is not installed as default null. That means that the DB expect a input for that column which is not given at the first registration.

This indeed solves the problem.
I’ve modified the schema for the user table to set the default for the ‘tags’ column to NULL and user registration works as intended with a fresh DB. I created a PR for this fix:

github.com/emoncms/emoncms

user registration fails if no default for 'tags' is set

emoncms:master ← rostwald:master

opened 10:17AM - 12 Feb 18 UTC

rostwald

+1 -1

If 'tags' has no default value, the sql query for user registration will silent…ly fail. Also see this thread regarding this issue: https://community.openenergymonitor.org/t/emoncms-error-creating-user/6490/19 Thanks to @belba who found the culprit of the error.

pb66 · 2018-02-12T10:53:17.591Z

Are you guys all cloning the default “master” branch from git?

Over the weekend whilst testing an emoncms installer script, I have created dozens of installs and every one of them has created a new user ok first time. Initially on Raspbian Stretch, but I have also tested numerous times on both Debian 9 and Ubuntu 16.04 too, without seeing this issue.

On closer inspection of each of your setups this morning it looks like you are all running a variant of mysql around version 5.7. All my test installs have been with mariadb 10 following Debian’s lead to move to a Open-source mysql version (non-Oracle). I wonder if this is a issue borne from differences in the db’s.

Have you guys specifically chosen the mysql versions you have landed or is it just what a package-manager has supplied for a “mysql-server” and “mysql-client” as defined in the emoncms user guide’s “apt-get” list?

Working towards unifying all the Linux install methods/guides, I was considering recommending “mariadb” was specifically defined rather than allowing any ambiguity, how would this effect you guys if we were to define “mariadb-client” and “maridb-server”?

The “Tags” field was introduced for the groups module so I will ask @cagabi to also have a look at this as the revised setting default needs to work for that module too.

rostwald · 2018-02-12T11:01:15.083Z

I was trying the master branch after the stable failed, but this didn’t make any difference.

mysql was chosen because the installation guide only explicitly mentions mysql and no compatibility with mariadb. Also, on illumos/smartOS status of mariadb was quite “beta-ish” when I last tried to use it instead of mysql. SMF integration wasn’t working at all back then and placing of files was also very much poised with linuxisms, so files weren’t where they are supposed to be which was quite annoying…

I’m using the groups module here (already created 3 groups with multiple users) and it seems to work fine with the suggested fix. But yes, @cagabi as the author of the module should definitely have a look at it.

pb66 · 2018-02-12T11:19:23.975Z

rostwald:

I’m using the groups module here (already created 3 groups with multiple users) and it seems to work fine with the suggested fix.

Thanks, I’m sure the fix will be good, I just didn’t want to blindly pull it in when @TrystanLea and @cagabi are working in that area. If we don’t here from them soon’ish, I will pull in the change regardless.

The Linux guide was written quite some time ago and has just been lightly edited here and there to bring it up to date, it has become more obvious to me that we might be better to specify a specific db as the variations increase, yesterday on Ubuntu 16.04 the “mysql-server” resulted in percona-server-server-5.6 being installed that asked for an admin password no less than 4 times during the installation, not helpful for an unattended install script and I have no idea how compatible it is, specifying mariadb-server worked perfectly so at this point I’m inclined to recommend the “mariadb” route as default, but obviously you can chose you own db and then at least you are aware there maybe differences to overcome etc.

TrystanLea · 2018-02-12T11:34:48.782Z

apologies, taking a look at this now

TrystanLea · 2018-02-12T11:45:41.721Z

ok this is a bit confusing, I thought I had solved this one but dropping and recreating an emoncms database here confirms the problem and your solution @rostwald solves it. Il merge it in, it would be great to hear if this solves it for everyone, I had mixed results on different systems before I think.

pb66 · 2018-02-12T12:05:26.917Z

Just tried a fresh install on Debian 9 with mariadb with the changes and that also works fine.

Server Information


Emoncms	Version	9.8.28 : 2018.01.27
	Modules	Administration : App v1.1.0 : Dashboard v1.1.1 : EventProcesses : Feed : Graph v1.2.0 : Input : CoreProcess : Schedule : Time : User : Visualisation
Server	OS	Linux 4.9.0-5-amd64
	Host	server-2 server-2.localdomain (127.0.1.1)
	Date	2018-02-12 12:04:05 UTC
	Uptime	12:04:05 up 13 min, 1 user, load average: 0.00, 0.08, 0.07
HTTP	Server	Apache/2.4.25 (Debian) HTTP/1.1 CGI/1.1 80
MySQL	Version	5.5.5-10.1.26-MariaDB-0+deb9u1
	Host	localhost (127.0.0.1)
	Date	2018-02-12 12:04:05 (UTC 00:00‌)
	Stats	Uptime: 373 Threads: 1 Questions: 76 Slow queries: 0 Opens: 19 Flush tables: 1 Open tables: 13 Queries per second avg: 0.203
Redis	Version	3.2.6
	Host	localhost:6379 (127.0.0.1)
	Size	0 keys (793.98K)
	Uptime	0 days
Memory	RAM	Used: 5.11% Total: 3.77 GB Used: 197.34 MB Free: 3.58 GB
Disk	Mount	Stats
	/	Used: 8.22% Total: 19.66 GB Used: 1.62 GB Free: 17.22 GB
PHP	Version	7.0.27-0+deb9u1 (Zend Version 3.0.0)
	Modules	apache2handler : calendar v7.0.27-0+deb9u1 : Core v7.0.27-0+deb9u1 : ctype v7.0.27-0+deb9u1 : curl v7.0.27-0+deb9u1 : date v7.0.27-0+deb9u1 : dom v20031129 : exif v7.0.27-0+deb9u1 : fileinfo v1.0.5 : filter v7.0.27-0+deb9u1 : ftp v7.0.27-0+deb9u1 : gd v7.0.27-0+deb9u1 : gettext v7.0.27-0+deb9u1 : hash v1.0 : iconv v7.0.27-0+deb9u1 : json v1.4.0 : libxml v7.0.27-0+deb9u1 : mcrypt v7.0.27-0+deb9u1 : mysqli v7.0.27-0+deb9u1 : mysqlnd : openssl v7.0.27-0+deb9u1 : pcre v7.0.27-0+deb9u1 : PDO v7.0.27-0+deb9u1 : pdo_mysql v7.0.27-0+deb9u1 : Phar v2.0.2 : posix v7.0.27-0+deb9u1 : readline v7.0.27-0+deb9u1 : redis v3.1.6 : Reflection v7.0.27-0+deb9u1 : session v7.0.27-0+deb9u1 : shmop v7.0.27-0+deb9u1 : SimpleXML v7.0.27-0+deb9u1 : sockets v7.0.27-0+deb9u1 : SPL v7.0.27-0+deb9u1 : standard v7.0.27-0+deb9u1 : sysvmsg v7.0.27-0+deb9u1 : sysvsem v7.0.27-0+deb9u1 : sysvshm v7.0.27-0+deb9u1 : tokenizer v7.0.27-0+deb9u1 : wddx v7.0.27-0+deb9u1 : xml v7.0.27-0+deb9u1 : xmlreader v7.0.27-0+deb9u1 : xmlwriter v7.0.27-0+deb9u1 : xsl v7.0.27-0+deb9u1 : Zend OPcache v7.0.27-0+deb9u1 : zlib v7.0.27-0+deb9u1 :

[edit] and also tested new install on Raspian Stretch (on a Pi so it took a bit longer)

Server Information


Emoncms	Version	9.8.28 : 2018.01.27
	Modules	Administration : App v1.1.0 : Dashboard v1.1.1 : EventProcesses : Feed : Graph v1.2.0 : Input : CoreProcess : Schedule : Time : User : Visualisation
Server	OS	Linux 4.9.59-v7+
	Host	raspberrypi raspberrypi (127.0.1.1)
	Date	2018-02-12 12:26:01 UTC
	Uptime	12:26:01 up 37 min, 3 users, load average: 0.19, 0.28, 0.65
HTTP	Server	Apache/2.4.25 (Raspbian) HTTP/1.1 CGI/1.1 80
MySQL	Version	5.5.5-10.1.23-MariaDB-9+deb9u1
	Host	localhost (127.0.0.1)
	Date	2018-02-12 12:26:01 (UTC 00:00‌)
	Stats	Uptime: 912 Threads: 1 Questions: 87 Slow queries: 0 Opens: 19 Flush tables: 1 Open tables: 13 Queries per second avg: 0.095
Redis	Version	3.2.6
	Host	localhost:6379 (127.0.0.1)
	Size	0 keys (662.67K)
	Uptime	0 days
Pi	CPU Temp	42.93°C
Memory	RAM	Used: 21.44% Total: 927.32 MB Used: 198.86 MB Free: 728.47 MB
	Swap	Used: 1.00% Total: 100 MB Used: 1 MB Free: 98.99 MB
Disk	Mount	Stats
	/	Used: 62.96% Total: 7.18 GB Used: 4.52 GB Free: 2.31 GB
	/boot	Used: 50.96% Total: 40.87 MB Used: 20.83 MB Free: 20.04 MB
PHP	Version	7.0.27-0+deb9u1 (Zend Version 3.0.0)
	Modules	apache2handler : calendar v7.0.27-0+deb9u1 : Core v7.0.27-0+deb9u1 : ctype v7.0.27-0+deb9u1 : curl v7.0.27-0+deb9u1 : date v7.0.27-0+deb9u1 : dom v20031129 : exif v7.0.27-0+deb9u1 : fileinfo v1.0.5 : filter v7.0.27-0+deb9u1 : ftp v7.0.27-0+deb9u1 : gd v7.0.27-0+deb9u1 : gettext v7.0.27-0+deb9u1 : hash v1.0 : iconv v7.0.27-0+deb9u1 : json v1.4.0 : libxml v7.0.27-0+deb9u1 : mcrypt v7.0.27-0+deb9u1 : mysqli v7.0.27-0+deb9u1 : mysqlnd : openssl v7.0.27-0+deb9u1 : pcre v7.0.27-0+deb9u1 : PDO v7.0.27-0+deb9u1 : pdo_mysql v7.0.27-0+deb9u1 : Phar v2.0.2 : posix v7.0.27-0+deb9u1 : readline v7.0.27-0+deb9u1 : redis v3.1.6 : Reflection v7.0.27-0+deb9u1 : session v7.0.27-0+deb9u1 : shmop v7.0.27-0+deb9u1 : SimpleXML v7.0.27-0+deb9u1 : sockets v7.0.27-0+deb9u1 : SPL v7.0.27-0+deb9u1 : standard v7.0.27-0+deb9u1 : sysvmsg v7.0.27-0+deb9u1 : sysvsem v7.0.27-0+deb9u1 : sysvshm v7.0.27-0+deb9u1 : tokenizer v7.0.27-0+deb9u1 : wddx v7.0.27-0+deb9u1 : xml v7.0.27-0+deb9u1 : xmlreader v7.0.27-0+deb9u1 : xmlwriter v7.0.27-0+deb9u1 : xsl v7.0.27-0+deb9u1 : Zend OPcache v7.0.27-0+deb9u1 : zlib v7.0.27-0+deb9u1 :

cagabi · 2018-02-12T12:58:44.829Z

Ups, sorry! It looks like the tags are breaking it.

I’m abroad and with no computer so can’t really check until next week. But I don’t think defaulting the tags to null would have any side effects. It would be worth trying to create users or search for users in the groups module which is the only place where tags are used so far.

Thanks rostwald for finding a solution

Cheers

pb66 · 2018-02-12T22:06:20.948Z

cagabi:

I’m abroad and with no computer so can’t really check until next week.

No problems, Trystan has reviewed and merged. Enjoy your trip

Stian · 2018-02-21T15:06:32.759Z

@pb66 I am using mysql and the stable emoncms, all from the installation guide:
https://github.com/emoncms/emoncms/blob/master/docs/LinuxInstall.md

For Ubuntu 16.04:

sudo apt-get install apache2 mysql-server mysql-client php libapache2-mod-php php-mysql php-curl php-pear php-dev php-mcrypt php-json git-core redis-server build-essential -y

and I am using the stable emoncms

git clone -b stable GitHub - emoncms/emoncms: Web-app for processing, logging and visualising energy, temperature and other environmental data

pb66 · 2018-02-21T15:51:19.641Z

This was fixed on the master branch 12th Feb, the stable branch has not been updated yet, so you’ll have to either switch branches or hang on until the next “stable” release.

Stian · 2018-02-22T07:23:05.789Z

Ok, thanks.

How do I change to the master branch without loosing my data?

git clone -b master GitHub - emoncms/emoncms: Web-app for processing, logging and visualising energy, temperature and other environmental data