Emoncms "Error creating user"

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

then changed by @TrystanLea in Dec17

and again in Jan 18

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

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

[[email protected] ~]# uname -a
SunOS emoncms 5.11 joyent_20171026T003127Z i86pc i386 i86pc
[[email protected] ~]# mysqld -V
mysqld  Ver 5.7.20 for solaris11 on x86_64 (Source distribution)
[[email protected] ~]# httpd -v
Server version: Apache/2.4.29 (Unix)
Server built:   Jan  3 2018 12:04:u5408 
[[email protected] ~]# 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
[[email protected] ~]# 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','[email protected]','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.

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:

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.

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.

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.

apologies, taking a look at this now

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.

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 :

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

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

@pb66 I am using mysql and the stable emoncms, all from the installation guide:

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

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.

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

From within your emoncms folder;

Firstly, ensure that you are up to date with current repo;
git pull

then to switch branch to master;
git checkout master

…and to switch back again…
git checkout stable

Paul