Blynk SQL "users" db error

[Castle]

Hello Blynkers…
i trying to figure out this error which im facing from the local blynk server “users” sql database, error is popping-up while inserting data entry into table. refer to postgres log screen shot below…

users table configuration is based from the reference mentioned @ “https://raw.githubusercontent.com/blynkkk/blynk-server/master/server/core/src/main/resources/create_schema.sql”
im using windows10 operating system, so all commands for creating database, user, tables, and permissions are done manually, but i think not an issue on that way because the other table are perfectly working, entries from table fields are fine only users table having problem.
see the other screen shots for your reference…

screen shots for reporting_apps_stat_minute:

blynk log…

@Dmitriy your the boss!!!

[Dmitriy]

Hello. You need to drop and create all tables again. Data model was changed recently and at the moment we do not provide migration scripts for local servers. To be honest I didn’t expect somebody is already using DB .

[Castle]

I already did that to drop the whole db and re-create again for 4 times but still the same error occurring.

[Dmitriy]

Please try to restart postgres process. Something like :

sudo -u postgres /usr/lib/postgresql/9.5/bin/pg_ctl -D /etc/postgresql/9.5/main restart

[Castle]

I’ll try to do that tomorrow… im out of town,
ill let you know for any progress once i execute that command. tnx…

[Dmitriy]

Also - do you use latest blynk server?

[Castle]

im using v0.23.2

[marvin7]

@Dmitriy

To be honest I didn’t expect somebody is already using DB

Well, I’m using it since March, 7th And like it very much…

[Dmitriy]

@marvin7 I said it wrong. I didn’t expect someone except Marcin was using DB already ;).

[marvin7]

Ah, right! Yes, I’m bit “lonely” on this island till now. But I’m sure it’ll get crowded, once discovered Good Night!

[Castle]

Well, perhaps all questions related to sql db have to be directions @marvin7 …
Honestly i want to integrate the access control system , thats the reason why i am interested in database feature. I want to ectract the history logs, Door transactions access events into sql table. Like ex. Card holder names, card number details, access rights level, areas, door name, last accessed, and the the transaction dates. Is just simple but very usefull through blynk magic…
Hardware will be few devices and simple but powerfull.

[marvin7]

Well, well, just look at that! The same (or very similar) error is in my posgresql.log too!

But what is interesting, the database regardless this error “works” fine (I’m accesing actually only rawdata table), that is why I wasn’t aware of this error. Sure it wasn’t there when database has been setup, but that was an older server version. @Dmitriy just forgot to let us know the change in data model
Both (the server and postgresql) runs on a little headless OPI One, and tables were setup using provided script - no errors then, as noted earlier.

Last, that is how the table “users” looks like. Certainly consistent with what script had created, but not correct now.

-- Table: public.users

-- DROP TABLE public.users;

CREATE TABLE public.users
(
    username text COLLATE pg_catalog."default" NOT NULL,
    appname text COLLATE pg_catalog."default" NOT NULL,
    region text COLLATE pg_catalog."default",
    json text COLLATE pg_catalog."default",
    CONSTRAINT users_pkey PRIMARY KEY (username, appname)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.users
    OWNER to postgres;

GRANT ALL ON TABLE public.users TO postgres;

GRANT ALL ON TABLE public.users TO test;

---

EDIT: OK, I see… the script IS ALREADY changed…
To save a little time @Dmitriy: Only the ‘users’ table has changed?

[Dmitriy]

@marvin7

To save a little time @Dmitriy: Only the ‘users’ table has changed?

No. Almost all tables were changed (except raw table). So I would recommend for simplicity drop all (except raw if you need info there) and create again.

[marvin7]

Thanks… Yes, I need that data (not that it is live-and-death dependant, but…) Will do a copy anyway, so it might be I just recreate a whole database. Will see how it goes later.

[Dmitriy]

You can execute :

drop table reporting_raw_data;
drop table reporting_average_minute;
drop table reporting_average_hourly;
drop table reporting_average_daily;
drop table reporting_app_stat_minute;
drop table reporting_app_command_stat_minute;
drop table reporting_http_command_stat_minute;

alter table users
add column pass text,
add column last_modified timestamp,
add column last_logged timestamp,
add column last_logged_ip text,
add column is_facebook_user bool,
add column is_super_admin bool DEFAULT FALSE,
add column energy int;

And create dropped tables again (from script file).

[Castle]

hello guys, still no luck and still digging solution for this error. @marvin7 please update if you got something, ill update too once mine is resolve.

[Dmitriy]

What did you try so far?

[marvin7]

OK guys (@Dmitriy and @Castle). Basically I just updated the database, and no errors in postgresql.log:

2017-03-20 20:15:54.678 - DB url : jdbc:postgresql://localhost:5432/blynk?tcpKeepAlive=true&socketTimeout=150
2017-03-20 20:15:54.679 - DB user : test
2017-03-20 20:15:54.682 - Connecting to DB...
2017-03-20 20:15:55.308 - Connected to database successfully.
2017-03-20 20:16:00.063 - Storing MINUTE reporting...
2017-03-20 20:16:00.063 - Storing HOURLY reporting...
2017-03-20 20:16:00.074 - Removing old reporting records...
2017-03-20 20:16:00.335 - Storing HOURLY reporting finished. Time 272. Records saved 16
2017-03-20 20:16:00.335 - Removing finished. Minute records 896, hour records 16. Time 272
2017-03-20 20:16:00.335 - Storing MINUTE reporting finished. Time 273. Records saved 16
2017-03-20 20:17:00.016 - Storing MINUTE reporting...
2017-03-20 20:17:00.025 - Storing raw reporting...
2017-03-20 20:17:00.025 - Removing old reporting records...
2017-03-20 20:17:00.299 - Removing finished. Minute records 16, hour records 0. Time 275
2017-03-20 20:17:00.315 - Storing MINUTE reporting finished. Time 299. Records saved 16
2017-03-20 20:17:00.419 - Storing raw reporting finished. Time 393. Records saved 160
2017-03-20 20:17:31.155 - Storing users...
2017-03-20 20:17:31.191 - Storing users finished. Time 37. Users saved 1
2017-03-20 20:18:00.013 - Storing MINUTE reporting...
2017-03-20 20:18:00.013 - Storing raw reporting...
2017-03-20 20:18:00.014 - Removing old reporting records...
2017-03-20 20:18:00.088 - Storing MINUTE reporting finished. Time 75. Records saved 16
2017-03-20 20:18:00.093 - Removing finished. Minute records 16, hour records 0. Time 80
2017-03-20 20:18:00.218 - Storing raw reporting finished. Time 204. Records saved 192

I run a quick compare between old and new create_schema.sql scripts, and the only diffs I have found are in users table. Those are the ones depicted by @Dmitriy in previous post (where add exists):

alter table users
add column pass text,
add column last_modified timestamp,
add column last_logged timestamp,
add column last_logged_ip text,
add column is_facebook_user bool,
add column is_super_admin bool DEFAULT FALSE,
add column energy int;

So what I did, is just running the above SQL query as user postgres, and everything seems to be fine. The web dashboard refreshed without issues, no errors in log file. As a note I can add that altering table was done in pgAdmin4 for Windows. So @Castle, I have no idea why you have problems (a different server platform??) as provided script for creating database is working fine (well, the old one, but they are not much different - only few columns more) , and @Dmitriy no further differences were found in current server’s data model (???). Backup done, but not necessary

[Dmitriy]

Probably other changes were before you installed DB. So yes, could be.

[Castle]

well, well, well… finally i resolve my DB error, it is compatibility issue. i used the old version of postgresql v9.2. before and lots of bugs on that. i uninstall it and replaced by v9.6 the same procedures and commands i’ve follow,. and lastly TABLES need to give grant wizard as follow…

logs are very stable:
Postgreslog

Blynk logs

Thanks @Dmitriy, and to all of you Blynkers…