Database locked error

[incagarcilaso]

I have partially solved this now, with the help of @Manie who very kindly remoted in to help diagnose the issue of a "Database locked" error in ICC, but thought to post it in case anyone is experiencing the same problem or does in the future and to see if anyone can help to be able to boot directly into desktop without having to run a ssh session first.

The "Database locked" error was due to duplicate instances of ICC running simultaneously.Turns out it was caused by the way vnc works on my Raspberry. I hadn't made any changes to the Jessie/ICC image that I installed. That is set up to boot into the desktop GUI, which automatically starts ICC. That is fine, but I am running my RPi headless and so always have to vnc in to see the desktop. It turns out that in order to VNC in I have to start a vnc desktop by logging in via ssh and running "vncserver :1" from the command line. If I don't do this I cannot access via VNC.

The problem with this is that the command "vncserver :1" starts a completely separate desktop environment for a VNC connection and if the desktop GUI is already running (even though I cannot see it) it starts separate instances of all processes including launching ICC.

As a workaround for now I have changed the boot sequence so that it boots into the console rather than into the Desktop. But this means that everytime I reboot I have to ssh into the console, run "vncserver :1" to start a headless desktop environment, log out of there and then I can VNC into the desktop.

I know this should be simple but why can't I vnc into the desktop that starts at boot if I have it configured to boot into the desktop. I cannot find any way to access that booted desktop remotely. I have trawled through Google for this but cannot find a simple answer.

[___]

There is a way to make the vnc server bind to an existing X session but I cannot for the life of me remember what it is. Probably read the man pages and so on. I think I used x11vnc, and usually I would also port-forward the vnc connection over ssh.

I think there is a way to integrate x11vnc into the X startup so that it always attaches a VNC session to your X sessions, and some desktop systems (gnome, kde) even have a setting you can activate for that. Of course the default Raspbian runs a lighter desktop than that, so you may be on your own, but a lot can be done with the stub-type configuration you can do by plonking fragments into /etc/X11/XSession.d. For example, ubuntu (and others) already start an ssh-agent as part of X startup so you need only type your passphrase once, so this would be very similar to that.

I hope that wasn't completely incomprehensible... :-)

Edit: So it turns out, as usual, that someone has already done it.

All that I would change is to drop the $(whoami) test. For one, you don't have to do that, that info is already in $USER, but besides that, there is usually only one user you care about on the Pi, and if you are really concerned, then rather use a check for the password file, eg:

if test -f $HOME/.vnc/passwd; then
      x11vnc -display :0 -rfbauth ~/.vnc/passwd -listen 192.168.32.5 -ncache 10 -forever &
fi

And that way it works for any user with a .vnc/passwd :-)

[cvzyl]

Is the problem not that you are running the vncserver command rather than vncviewer? vncserver only needs to be started once, on my pi it is configured to start automatically at boot. I can then use ssh or direct vncviewer from another computer. If you are using a Windows/Mac version of vncviewer the port is 5901 for session 1.

So I think your problem is that you start multiple server instances of vnc rather than just connect as viewer.

[___]

Just now, cvzyl said:

running the vncserver command rather than vncviewer?

As I understand it (or remember it) the problem is that the vncserver default setup starts a new X session. So you end up with two sessions. You can check by looking at the $DISPLAY variable (echo $DISPLAY), on the first one it will be 0.0, on the second 1.0.

Multiple graphical sessions running on the same computer. Eat your heart out... uuuhm... just about everyone else. Unix had this in 1984. I feel like a proud daddy at times like this :-)

[SilverNodashi]

shouldn't ICC be run as a daemon instead? just an idea?

[___]

shouldn't ICC be run as a daemon instead? just an idea?

Don't confuse the windows people man!

Sent from my GT-I9195 using Tapatalk

[SilverNodashi]

19 minutes ago, plonkster said:

Don't confuse the windows people man!

Sent from my GT-I9195 using Tapatalk
 

I actually did hesitate about that statement, but know I would have been flamed for saying "shouldn't ICC be run as a Service instead?" .... Imagine that confusion!

[___]

ICC is a graphical application, it needs X and the mono assemblies and gtk libraries (I actually looked at the app some time ago). So it cannot really be turned into a daemon, but you could write a service that starts X with a window manager and ICC. I actually have a systemd file somewhere that does exactly that. It doesn't really turn it into a service, but it gets close.

It's actually amazingly simple. First, I have this service in /etc/systemd/system/icc.service (I'm doing a search and replace, my service name is different of course):

[Unit]
Description=ICC GUI
After=network.target

[Service]
ExecStart=/usr/bin/startx /home/pi/ICC/gui.sh
Type=simple
User=pi
WorkingDirectory=/home/pi/ICC
Restart=always

[Install]
WantedBy=multi-user.target

And then in gui.sh you'd have something like this:

#!/bin/sh

# Start a window manager
/usr/bin/openbox &

# Set a background logo (optional)
xsetbg /home/pi/logo.png

# Start her up
cd /home/pi/ICC
exec /home/pi/ICC/ICC

And then you'd disable the desktop on your Pi and instead enable this service (you'll have to google about how to enable it with systemctl, I have forgotten most of it, because you see I can always read a man page or three if I need it again...).

Result is that X starts up with a window manager and ICC, nothing else.

You may want to also start x11vnc in that script so you can access it remotely.

[SilverNodashi]

5 minutes ago, plonkster said:

ICC is a graphical application, it needs X and the mono assemblies and gtk libraries (I actually looked at the app some time ago). So it cannot really be turned into a daemon, but you could write a service that starts X with a window manager and ICC. I actually have a systemd file somewhere that does exactly that. It doesn't really turn it into a service, but it gets close.

It's actually amazingly simple. First, I have this service in /etc/systemd/system/icc.service (I'm doing a search and replace, my service name is different of course):

[Unit]
Description=ICC GUI
After=network.target

[Service]
ExecStart=/usr/bin/startx /home/pi/ICC/gui.sh
Type=simple
User=pi
WorkingDirectory=/home/pi/ICC
Restart=always

[Install]
WantedBy=multi-user.target

And then in gui.sh you'd have something like this:

#!/bin/sh

# Start a window manager
/usr/bin/openbox &

# Set a background logo (optional)
xsetbg /home/pi/logo.png

# Start her up
cd /home/pi/ICC
exec /home/pi/ICC/ICC

And then you'd disable the desktop on your Pi and instead enable this service (you'll have to google about how to enable it with systemctl, I have forgotten most of it, because you see I can always read a man page or three if I need it again...).

Result is that X starts up with a window manager and ICC, nothing else.

You may want to also start x11vnc in that script so you can access it remotely.

Well, the X gui stuff needs to be run as the user but the DB and other related scripts could be run as a deamon - the way Linux intended to run multi user systems. 

 

but it's just a suggestion. 

[___]

2 hours ago, SilverNodashi said:

Well, the X gui stuff needs to be run as the user but the DB and other related scripts could be run as a deamon - the way Linux intended to run multi user systems. 

I believe it uses sqlite3, so there is no database process. Given the target audience it's perfect, you really don't want to many bits and pieces.

On the CCGX they use the framebuffer directly, more specifically, they use QT-embedded which already knows how to render everything directly to the framebuffer. So Venus ships without X. Just one of the reasons it fits into just 128MB (66MB compressed download).

QT takes a bit of getting used to though. QML is not bad, but it's just one of those things, you either love it or you don't. I'm not sure yet... :-)

[Guest]

Sqlite only provide one connection per session. Wonder if it is posible to use a acess db in raspbiam?

Sent from my SM-G920F using Tapatalk

[___]

4 hours ago, Manie said:

acess db

For the love of all that is holy...

[Guest]

For the love of all that is holy...

Holy ?

Sent from my SM-G920F using Tapatalk

[SilverNodashi]

.

[Guest]

hahahaha! haha ha hahahaha!!! I almost lost it there. Manie, PLEASE don't use Access. Ever. Rather use flat files if you HAVE to, but not Access. 

Whats wrong with Acess. Flat files to me is realy stupid option. Wonder why so many use acess ?

Looking for options not criticism for easy acess for people to use. Yes msql ect are better but cost more.



Sent from my SM-G920F using Tapatalk

[SilverNodashi]

2 minutes ago, Manie said:

 

Whats wrong with Acess. Flat files to me is realy stupid option. Wonder why so many use acess ?

Looking for options not criticism for easy acess for people to use. Yes msql ect are better but cost more.



Sent from my SM-G920F using Tapatalk
 

 

MSSQL isn't better, cause it costs more. It cost more cause it's Microsoft. If you want a good DB, look at PostgreSQL. SQLLite isn't bad but it has performance issues on larger data sets - which you probably won't see in this setup. 

 

I would have gone MySQL / MariaDB / PostgreSQL - they're all about the same in terms of structure and in all cases you would use an "SQL primer" to talk to them. I presume you code in Python, there are loads of examples on the net on interfacing with these databases. AND, if you really want to / need to expand to a larger cluster type setup, it would be much easier than MSSQL as well. 

[Guest]

MSSQL isn't better, cause it costs more. It cost more cause it's Microsoft. If you want a good DB, look at PostgreSQL. SQLLite isn't bad but it has performance issues on larger data sets - which you probably won't see in this setup. 
 
I would have gone MySQL / MariaDB / PostgreSQL - they're all about the same in terms of structure and in all cases you would use an "SQL primer" to talk to them. I presume you code in Python, there are loads of examples on the net on interfacing with these databases. AND, if you really want to / need to expand to a larger cluster type setup, it would be much easier than MSSQL as well. 

I am looking easy use for users. People already struggling to install stuff and get it working on the pi.

Not for me or you thats can do things. A normal db that is acessable in the same directory.

Ms Acess have everything already in and multiple instaces. No locking ect.

I talk directly to acess dont need access to be installed.

How many people say Linux is crap. It your owne view. Please provide some facts why this is a bad choise.


Sent from my SM-G920F using Tapatalk

[___]

I must admit that my dislike for Access is for three reasons, none of which are very good when you consider them from the outside. Those reasons are:

1. It's from Microsoft.

2. It's Microsoft's toy database, even if it is good enough for some tasks, M$ isn't going to let it compete with their real database, aka MSSQL.

3. Have you ever had to get data out of access on a non-windows system? When applications outgrow their toddler-shoes and their owners want you to migrate the data to the new app you wrote for them (which is on linux, because data centers ask more money to host on windows), this problem weighs a lot.

Those are my reasons, but I am also told that it doesn't scale well with lots of data, that it also has a lock-file issue limiting it to one application (there are workarounds), and that data corruption is also a problem.

Sqlite is a better option than access. It's open source and has comparable performance.

As Silver said, I'd look at MySQL or Postgresql. In fact, I would only consider MySQL if the client really wants it. If the choice is up to me, its Postgresql every time. Open source, partial indexes, choice of index types, multiple language options for writing stored procedures or triggers (I'm not a big fan, but it has its uses), plpgsql is close to the language oracle uses so porting is easier, it's actually ANSI compliant (at least significantly more so than MySQL), performs as good as or better than MySQL (or MSSQL for that matter), it has replication, multiple configurable ways to do authentication, SSL on the database connections... all for free.

But it doesn't work so well on Windows. There is that... :-)

Also, I'm not convinced running a fully-fledged database server on a small board computer with an sdcard for a disk is a good idea either.

So I'd just stick with sqlite if it was me.

Edit: Postgresql's transaction isolation is awesome too. I don't know about the other big guns, the Oracles and MSSQLs, but certainly in the domain of cheap/free options it blows the others out of the water. Add in Point in time recovery from a backup and the WAL files, man this thing is state of the art, you can hardly imagine why it's free...

[SilverNodashi]

9 minutes ago, Manie said:

 

 

I am looking easy use for users. People already struggling to install stuff and get it working on the pi.

Not for me or you thats can do things. A normal db that is acessable in the same directory.

Ms Acess have everything already in and multiple instaces. No locking ect.

I talk directly to acess dont need access to be installed.

How many people say Linux is crap. It your owne view. Please provide some facts why this is a bad choise.


Sent from my SM-G920F using Tapatalk
 

 

 

Manie, use what you like - it's your application. Don't take a single user's point of view on your decisions, perhaps a 100 user's point of view would be different, but don't "bend over" for a single user. 

Forget about the the fact that it's a Microsoft product. Closed source. And you can only use it the way they wanted you to use it.

 

But, when you need to migrate 75,000 records from that Access DB to something bigger, we'll talk again. Why does the biggest open source application, Wordpress run on MySQL and not Access? Access is nice. For a home user drawing up an inventory of his wine cellar. Even with 4000 bottles of wine (yes I have been there) but when you need to scale out, it doesn't play well nicely. And if you log 10 feeds every minutes, or even every few seconds, it will fill up very very quickly. 

 

 

As a Linux sys admin, why don't you automate how the installation happens? Running a simple bash script upon first boot, which runs something like "sudo apt-get install mysql-server" is very easy and doesn't need to have the user do anything. In fact, you could even create the database structure and add "Demo data" if you look. cPanel, the leading hosting control panel has been doing this for years. You download and run a single bash script and it does EVERYTHING for the end user. In fact, many hosting companies don't even know how to operate a Linux server but they operate successful hosting companies. 

 

I would be more than willing to help with this, seeing as you're not a Linux man. But first decide where you want to go with this. Automated and unattended installations on Windows isn't difficult either. Have been doing it since Windows NT days. It just takes a LOT more trail and error to get right. And then you need to worry about hardware compatibility and drivers. 

[Guest]

Thanks for the info. For me to get data out of systems is fairly easy even if that is 400 000 records and transfere it to another db.

Yes i agree with some of the points. The topic is all about locking the db. Sqlite is where the problem is as only on session is allowed. With ms its not the case.

To install a server well like plonster said it can be a issue but as with many articles from users that they run the db on sd card longer than 3 years.

I am looking for a simple solution by not repeating myself that you running 2 copies and that is why you have a lock



Sent from my SM-G920F using Tapatalk

[SilverNodashi]

7 minutes ago, Manie said:

Thanks for the info. For me to get data out of systems is fairly easy even if that is 400 000 records and transfere it to another db.

Yes i agree with some of the points. The topic is all about locking the db. Sqlite is where the problem is as only on session is allowed. With ms its not the case.

To install a server well like plonster said it can be a issue but as with many articles from users that they run the db on sd card longer than 3 years.

I am looking for a simple solution by not repeating myself that you running 2 copies and that is why you have a lock



Sent from my SM-G920F using Tapatalk
 

 

So, emoncms uses the mysql server and anyone wanting to run a local emoncms would need to install MySQL in anycase. 

[Guest]

So, emoncms uses the mysql server and anyone wanting to run a local emoncms would need to install MySQL in anycase. 

True. See now good idees is coming out. Thanks

Sent from my SM-G920F using Tapatalk

[Guest]

That will work for the Pi image !!

[SilverNodashi]

26 minutes ago, Manie said:

 

 

True. See now good idees is coming out. Thanks

Sent from my SM-G920F using Tapatalk
 

 

 

uhm, I did suggest it right from the start

[Guest]

Still not solving the issue if the user want to download the app and do the installation themself

Still in my eyes ms access is the way to go for a single app db. There are a lot to choose. Server and so on obviously not .

If you look at JDP AICC many still use it till today without the corruption and things as said above. It is up to the programmer to ensure how he aces the db and store data. I mean like simple for loops as you can just use a normal update or insert to do stuff. If you program wrong everything will be slow etc