2

I wanted to develop an app with Python that would run on a Raspberry and be remote controlled. This app should control (switch on and off) 2 leds based on the current day, time and the user's indication. The app is to be executed when the Raspberry is booted (auto start) and should be run endlessly. For this purpose, a service is created under /usr/lib/systemd/system. The postgres (Heroku) databases were used for this purpose.

I have read several articles and used several suggestions, but unfortunately without much success. The problem with my app is that it crashes every 24 hours with the following error:

Light_system Error 1: SSL SYSCALL error: Connection timed out

Light_system Error 1: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

The raspberry is connected to the internet through a sim module (SIM7600E -H 4G HAT). But this is not supposed to be the problem, because I also connect the raspberry to the direct router, but get the same error again.

I am very grateful for any suggestions!

I have already tried the following steps:

  • keepalive_kwargs = { "keepalives": 1, "keepalives_idle": 30, "keepalives_interval": 5, "keepalives_count": 5, }

  • conn = psycopg2.connect(conn_string, **keepalive_kwargs)

  • engine = sqlalchemy.create_engine(DATABASE_URL, pool_pre_ping=True)

Here is my code:

crud.py

    keepalive_kwargs = {
    "keepalives": 1,
    "keepalives_idle": 30,
    "keepalives_interval": 5,
    "keepalives_count": 5,
}

conn_string = "host, dbname, user, password"
conn = psycopg2.connect(conn_string, **keepalive_kwargs)

class CRUDSettings:
  

    @staticmethod   
    def read_from_db():
        try:
            cur = conn.cursor()
            cur.execute("SELECT * FROM time_settings_tabel")
            data = cur.fetchall()
            return data
        except Exception as e:
            with open("read_from_db.log", "a") as f:
                f.write(f"ERROR : {str(e)}")


        @staticmethod   
        def read_from_db_LED():
            try:
                cur = conn.cursor()
                cur.execute("SELECT * FROM LED_settings_tabel")
                data = cur.fetchall()
                return data
            except Exception as e:
                with open("read_from_db_1.log", "a") as f:
                    f.write(f"ERROR : {str(e)}")

settings = CRUDSettings()

database.py

DATABASE_URL = "URL for DB"
database = databases.Database(DATABASE_URL)
engine = sqlalchemy.create_engine(DATABASE_URL, pool_pre_ping=True)

SessionLocal = sessionmaker(autocommit=False, autoflush = False, bind = engine)

Base = declarative_base()

metadata = sqlalchemy.MetaData()

Base.metadata.create_all(bind=engine)

def get_db():
    Base.metadata.create_all(bind=engine)
    database = engine.connect()
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

app.py

  class Watersystem:
    async def run(self, app):
        try:
            while(app.state.isRunning):
            # Es werden die Datenbanken aktualisiert
                for row in crud.settings.read_from_db():
                    now = datetime.now()
                    current_time  = convert(now.hour, now.minute,now.second)
                    current_day= now.strftime("%A")
                    start_split = row[2].split(":")
                    end_split = row[3].split(":")
                    start = convert(int(start_split[0]), int(start_split[1]), int(start_split[2]))
                    end = convert(int(end_split[0]), int(end_split[1]), int(end_split[2]))
                    current_id = row[0]

                    await RestartButton_einlesen()
                    GPIO.output(18,GPIO.LOW)
                    GPIO.output(20,GPIO.LOW)
                    GPIO.output(21,GPIO.LOW)                                
                    while ((current_day == row[1].replace(" ","")) and (current_time  >= start) and (current_time  <= end) and (current_id == row[0])): 
                        print("START")
                        GPIO.output(18,GPIO.HIGH)
                        await RestartButton_einlesen()
                        for vent in crud.settings.read_from_db_LED():
                            if (vent[1] is True):
                                GPIO.output(20,GPIO.HIGH)                                                                            
                            else:
                                GPIO.output(20,GPIO.LOW)
                                                                                                                    
                            if(vent[2] is True):                                      
                                GPIO.output(21,GPIO.HIGH)                                              
                            else:                                       
                                GPIO.output(21,GPIO.LOW)
                                
                        now = datetime.now()
                        current_time  = convert(now.hour, now.minute,now.second)

                        if (current_day == row[1].replace(" ","") and current_time  >= end):
                            print("END")
                            GPIO.output(18,GPIO.LOW)
                            GPIO.output(20,GPIO.LOW)
                            GPIO.output(21,GPIO.LOW)
                            break
        
        except Exception as err:
            logger.error('FunctionName: %s',  err)
            await asyncio.sleep(5)
            os.system("sudo reboot")



watersystem = Watersystem()


async def RestartButton_einlesen():
    for rest in crud.settings.read_from_db_Restart():
        if(rest[0] is True):
            GPIO.output(18,GPIO.LOW)
            GPIO.output(20,GPIO.LOW)
            GPIO.output(21,GPIO.LOW)
            await asyncio.sleep(1)
            os.system("sudo reboot")

postgresql-13-main.log file:

LOG: starting PostgreSQL 13.9 (Raspbian 13.9-0+deb11u1) on arm-unknown-linux-gnueabihf, compiled by gcc (Raspbian 10.2.1-6+rpi1) 10.2.1 20210110, 32-bit

LOG: listening on IPv4 address "0.0.0.0", port 5432

LOG: listening on IPv6 address "::", port 5432

LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2023-02-21 15:45:22.409 CET [623] LOG: database system was interrupted; last known up at 2023-02-20 21:28:14 CET

LOG: database system was not properly shut down; automatic recovery in progress

LOG: redo starts at 0/15D1718

LOG: invalid record length at 0/15D1750: wanted 24, got
 
LOG: redo done at 0/15D1718

LOG: database system is ready to accept connections
5
  • I found a couple of other articles here and here that have some good information on configuration in the OS with Postgres SQL dbs. Feb 18, 2023 at 19:38
  • @RoninEngineer The thing is that this happens when the connection is "idle". During the tag, the leds or the tables are read several times by the databases and the connection is active. If they are not read for a long time, for example for a period of about 8 hours, the connection is broken. And this happens every morning, for example, because the tables are not read during the night.
    – Jiannis4
    Feb 19, 2023 at 15:36
  • 1
    the app.py has some inconsistent indentation and some missing code
    – jsotola
    Feb 19, 2023 at 18:31
  • @jsotola I have inserted the code on my next post.
    – Jiannis4
    Feb 21, 2023 at 15:24
  • that is not the next post ... it says answer ... that is where the answer to your question goes ... use the edit button to edit your question with the new information, and then delete the non-answer
    – jsotola
    Feb 21, 2023 at 17:10

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.