Sqlite database error attempt to write a readonly database

Hi I have a sqlite3 database which is working from one of the programs but when i try to write to it from another program i get the following error message.

«Error: SQLITE_READONLY: attempt to write a readonly database»

How to i change the permissions for the database to allow other writes to the file?

i am trying to use node red to send i new value to the database code i use in node red is
INSERT INTO heater (timer) VALUES (?), (1.5)

is it i need to change the file location permissions?
file location is
/home/pi/heater_run.db

i have tried «sudo chmod -R ugo+rw- /home/pi/heater_run.db»
but this has not worked

Is this a permissions thing? Was the db created using sudo, and a regular user is trying to update it?

Also, you can only have one process writing to a SQLite database at any one time. You can have multiple processes with the db open, but without some extensions enabled you can only have one writer at a time.

scruss wrote: ↑

Mon Sep 14, 2020 10:14 pm

Is this a permissions thing? Was the db created using sudo, and a regular user is trying to update it?

Also, you can only have one process writing to a SQLite database at any one time. You can have multiple processes with the db open, but without some extensions enabled you can only have one writer at a time.

the database was not created with sudo and i am sure it is a permission thing but i am not sure how to correct this i have tried using sudo chmod and chmod to change the file permission but so far it has not made any difference. the only thing i have not tried is rebooting the raspberry pi yet,
i am only trying to access the file 1 at a time so its not that the file is open by the original program at the time..

It’s probably the two simultaneous writers problem.

sqlite3 doesn’t have good row locking. You can have as many readers as you need. You can have ONE writer.

All of my sqlite3 programs use

as the first SQL statement I execute in a reader program.

The fix for a read only database is

echo ‘.dump’ | sqlite3 mysqlitedata.db | sqlite3 replacementdata.db
then mv mysqlitedata.db oldsqlitedata.db; mv replacementdata.db mysqlitedata.db

DougieLawson wrote: ↑

Mon Sep 14, 2020 10:45 pm

It’s probably the two simultaneous writers problem.

sqlite3 doesn’t have good row locking. You can have as many readers as you need. You can have ONE writer.

All of my sqlite3 programs use

as the first SQL statement I execute in a reader program.

The fix for a read only database is

echo ‘.dump’ | sqlite3 mysqlitedata.db | sqlite3 replacementdata.db
then mv mysqlitedata.db oldsqlitedata.db; mv replacementdata.db mysqlitedata.db

Hi Dougie
i have stopped the original program from running to ensure there is no conflict with the other program trying to write to the database. this still came up with the same error saying trying to write to a read only Database. the database will only be writen to about every 3 hours and i only want to be able to write from the second program every few months.

i am interested in the fix you have shown but i am a little lost how to do the steps you have said. I am happy to create a new database as its not got much information in it at this stage. so would it be best to create the new database from within a terminal window instead of a python script?

cleverca22 wrote: ↑

Tue Sep 15, 2020 1:18 am

sqlite wants to create 1 or 2 more files in the same dir as the db during normal operation
so the permissions to the directory also matter, and can cause problems even if you can write to the main file

Thanks guess that makes this more of a problem for me as i use the home/pi folder. is there a way to change the permissions for this directory?

this also explains why i have seen a similar issue with csv files in the past..

johnnyh20400 wrote: ↑

Tue Sep 15, 2020 12:18 am

i am interested in the fix you have shown but i am a little lost how to do the steps you have said. I am happy to create a new database as its not got much information in it at this stage. so would it be best to create the new database from within a terminal window instead of a python script?

Do it in a lxterminal window. Type in my blue commands but with your database file name.

johnnyh20400 wrote: ↑

Tue Sep 15, 2020 12:18 am

DougieLawson wrote: ↑

Mon Sep 14, 2020 10:45 pm

It’s probably the two simultaneous writers problem.

sqlite3 doesn’t have good row locking. You can have as many readers as you need. You can have ONE writer.

All of my sqlite3 programs use

as the first SQL statement I execute in a reader program.

The fix for a read only database is

echo ‘.dump’ | sqlite3 mysqlitedata.db | sqlite3 replacementdata.db
then mv mysqlitedata.db oldsqlitedata.db; mv replacementdata.db mysqlitedata.db

Hi Dougie
i have stopped the original program from running to ensure there is no conflict with the other program trying to write to the database. this still came up with the same error saying trying to write to a read only Database. the database will only be writen to about every 3 hours and i only want to be able to write from the second program every few months.

i am interested in the fix you have shown but i am a little lost how to do the steps you have said. I am happy to create a new database as its not got much information in it at this stage. so would it be best to create the new database from within a terminal window instead of a python script?

i have now checked the permissions using ls -l for the home/pi folder and it was only read only so i have changed the permissions so that it is read and write for anyone. this still gives the message that its a read only database. i then changed the permissions for the whole home/pi directory so it also was read and write for anyone. But still it say attempting to write to a read only database.

so now not sure if its using some other locations for the database files?

DougieLawson wrote: ↑

Tue Sep 15, 2020 8:16 am

johnnyh20400 wrote: ↑

Tue Sep 15, 2020 12:18 am

i am interested in the fix you have shown but i am a little lost how to do the steps you have said. I am happy to create a new database as its not got much information in it at this stage. so would it be best to create the new database from within a terminal window instead of a python script?

Do it in a lxterminal window. Type in my blue commands but with your database file name.

Thanks Dougle
sorry to be a pain but i am a bit slow when it comes to this
my database is «heater_run.db » and its in the /home/pi directory so when i start the terminal i am in the right directory to use the commands you showed but i am not sure hoe to change the code so i dont mess up.
echo ‘.dump’ | sqlite3 mysqlitedata.db | sqlite3 replacementdata.db

i think i just need to change it to
echo ‘.dump’ | sqlite3 heater_run.db | sqlite3 replacementdata.db

mv mysqlitedata.db oldsqlitedata.db; mv replacementdata.db mysqlitedata.db
then change the 2nd command this is where i get lost

can you please explain ?

johnnyh20400 wrote: ↑

Tue Sep 15, 2020 9:14 am

… i have now checked the permissions using ls -l for the home/pi folder and it was only read only …

Something’s not right there. How your home folder got to be read-only might suggest other problems: any other unexplained warning messages, undervoltage warnings (lightning bolt on the screen), «segmentation fault» errors? Your SD card might be corrupted.

Are you running Raspberry Pi OS, or something else? SQLite is typically one of those «it just works» things (aside from lots of database management complexity) because it keeps everything in a file owned by the user.

scruss wrote: ↑

Tue Sep 15, 2020 1:31 pm

johnnyh20400 wrote: ↑

Tue Sep 15, 2020 9:14 am

… i have now checked the permissions using ls -l for the home/pi folder and it was only read only …

Something’s not right there. How your home folder got to be read-only might suggest other problems: any other unexplained warning messages, undervoltage warnings (lightning bolt on the screen), «segmentation fault» errors? Your SD card might be corrupted.

Are you running Raspberry Pi OS, or something else? SQLite is typically one of those «it just works» things (aside from lots of database management complexity) because it keeps everything in a file owned by the user.

I am using raspberry pi os using the ls -l it shows the file as owned by user pi and now all is set to
-rw-rw-rw- 1 pi pi 8192 Sep 14 18:53 heater_run.db
-rwxrwxrwx 1 pi pi 8192 Sep 15 13:28 Heater_run.db
i even tried to create a new database from the terminal to see if that made any difference. Now to be honest i am not sure whether the program that wrote the database is running from root or from pi
this is the ls -l print out for the file which created the original file

-rw-rw-rw- 1 pi pi 4461 Sep 15 13:20 temperature_control_v2b.py

i start the program with «sudo python3 /home/pi/Temperature_control_v2b.py»
the second program i am trying to use to write to the program is is run from with node red.
its got me beaten at the moment .

okay, so you are using sudo: that’s why the database doesn’t belong to you, the pi user.

You don’t need to use sudo for most sensor reading tasks. You may have followed an old (< 2019) tutorial that recommended it.

scruss wrote: ↑

Tue Sep 15, 2020 2:36 pm

okay, so you are using sudo: that’s why the database doesn’t belong to you, the pi user.

You don’t need to use sudo for most sensor reading tasks. You may have followed an old (< 2019) tutorial that recommended it.

i have deleted the original database from the raspberry pi and stopped the original program running and restarted node red. I then changed the bashrc script so it now read python3 /home/pi/Heater_run.db and the new file has been created in the directory, i then restarted node red and tried to add to the database but again its the same result.

The only reason i am using to programs to write to the file is that the thing i want to control uses a mcp23017 expander and i cant get the node to work in node red. it maybe i need to just change my original python script when ever i want to reset the database file which is in my program knowledge scope.

the other solution maybe to change to MySQL but that seemed a lot harder for me to learn.

thanks for the surgestions so far.

Poster of inconvenient truths.

Back from a short, unplanned vacation. Did you miss me?

GlowInTheDark wrote: ↑

Tue Sep 15, 2020 3:24 pm

Given the parameters of this problem, switching to another, much more complex DB (e.g., MySQL), will probably change a 3 week debugging project into a 6 month one.
[/quote/]
That’s what worries me too. as i get one thing sorted it seems i come up with another idea which starts me down another rabbit hole. A simple Aquarium controller is turning into a major project which is far beyond me. but as i don’t like admitting defeat i will keep plugging away at it.

another solution maybe to send a mqtt with the new variable to change the data i need within the temperature_control_v2b.py oh well here comes version v2c.py using mqtt from node red.

johnnyh20400 wrote: ↑

Tue Sep 15, 2020 9:31 am

Thanks Dougle
sorry to be a pain but i am a bit slow when it comes to this
my database is «heater_run.db » and its in the /home/pi directory so when i start the terminal i am in the right directory to use the commands you showed but i am not sure hoe to change the code so i dont mess up.
echo ‘.dump’ | sqlite3 mysqlitedata.db | sqlite3 replacementdata.db

i think i just need to change it to
echo ‘.dump’ | sqlite3 heater_run.db | sqlite3 replacementdata.db

mv mysqlitedata.db oldsqlitedata.db; mv replacementdata.db mysqlitedata.db
then change the 2nd command this is where i get lost

can you please explain ?

‘.dump’ causes sqlite3 to dump the contents of a database to stdout.
echo … | (vertical bar is a pipe) sends the command from the echo command line to the stdin of the next process.

So we’re sending a .dump command to sqlite3 which is reading your heater_run.db.
echo ‘.dump’ | sqlite3 heater_run.db | sqlite3 replacementdata.db

We then do more «plumbing» by piping the output (stdout) of the dump command into the input (stdin) of another sqlite3 command.
That will create a new database called replacementdata.db.

The mv command is the Linux rename command.
mv heater_run.db safe_copy_of_heater_run.db
Then move the replacement to be the current database
mv replacementdata.db heater_run.db

Job done, get a cuppa and keep calm and carry on.

DougieLawson wrote: ↑

Wed Sep 16, 2020 4:58 pm

johnnyh20400 wrote: ↑

Tue Sep 15, 2020 9:31 am

Thanks Dougle
sorry to be a pain but i am a bit slow when it comes to this
my database is «heater_run.db » and its in the /home/pi directory so when i start the terminal i am in the right directory to use the commands you showed but i am not sure hoe to change the code so i dont mess up.
echo ‘.dump’ | sqlite3 mysqlitedata.db | sqlite3 replacementdata.db

i think i just need to change it to
echo ‘.dump’ | sqlite3 heater_run.db | sqlite3 replacementdata.db

mv mysqlitedata.db oldsqlitedata.db; mv replacementdata.db mysqlitedata.db
then change the 2nd command this is where i get lost

can you please explain ?

‘.dump’ causes sqlite3 to dump the contents of a database to stdout.
echo … | (vertical bar is a pipe) sends the command from the echo command line to the stdin of the next process.

So we’re sending a .dump command to sqlite3 which is reading your heater_run.db.
echo ‘.dump’ | sqlite3 heater_run.db | sqlite3 replacementdata.db

We then do more «plumbing» by piping the output (stdout) of the dump command into the input (stdin) of another sqlite3 command.
That will create a new database called replacementdata.db.

The mv command is the Linux rename command.
mv heater_run.db safe_copy_of_heater_run.db
Then move the replacement to be the current database
mv replacementdata.db heater_run.db

Job done, get a cuppa and keep calm and carry on.

Thanks once again great explanation will try this later once i have time.