Running SQLite through Terminal Command Line Stability - Linux Based Systems

kebek007
2021-05-04
2021-05-05
  • kebek007

    kebek007 - 2021-05-04

    Hello community,

    I have been tinkering a little bit with a raspberry pi and Sqlite through codesys. I have noticed an instability when it comes to logging at high frequency and calling the function. I am not able to close the Database after writing but that shouldn't be an issue. What I have noticed when I monitor my processes on the pi is an unusual large number of processes that are open from the codesys runtime. After a few cycles, the Visualization hangs and the whole codesys process is killed by the Debian OS, i.e fatal crash of the application. The Sqlite was also successfully written, so the commands were performed as expected.

    The code I ran is as follows almost every 1 second:

    // Header
    METHOD mSqliteWrite : BOOL
    VAR_INPUT
        sCmd:               STRING(255);        // an sqlite write command
                                                // 'INSERT INTO fooLog(foo1,foo2)
                                                Values('10','20')' (example not real command)
    END_VAR
    VAR
            command0 : STRING := 'sqlite3 ';
            command1 : STRING := '/var/opt/codesys/PlcLogic/trend/LogBook.sql ';
            commandout : STRING(1000);
    
            stdout : STRING(1000) := '';    
            Result : RTS_IEC_RESULT;
    
            iSize : UDINT ;
    
    END_VAR
    
    
    // concat the strings to form the right command "sqlite3 path/filename sqlite3 command"
    commandout := CONCAT(command0,command1);
    commandout := concat(commandout, sCmd);
    // // Check size for testing purposes
     iSize := SIZEOF(CommandOut);
    // 
    // // send the command 
    SysProcess.SysProcessExecuteCommand2(pszCommand:=commandout, pszStdOut:=stdout, udiStdOutLen:= SIZEOF(stdout),pResult := ADR(Result));
    
    // // Error handling
    // IF Result <> 0 THEN
            // Do something
    // ELSE
    //  mSqliteWrite := TRUE;
    // END_IF
    

    Has anyone come across this problem ? Is there a way around it that doesn't need any kind of third party tools or libs? Is this something relevant to the runtime of the pi or general runtime on other linux based systems ?

    Thanks

     
  • m.prestel

    m.prestel - 2021-05-05

    hey,
    you cannot simple reuse a sqlite database which the application uses.
    If you want to do your own queries, create your own sqlite database.

    Best regards,
    Marcel

     
    • kebek007

      kebek007 - 2021-05-05

      Thanks for your answer Marcel.

      yeah exactly I am talking abt my own database. It seems that when I do a write process as described above, the processes increase with every cycle and the process is killed. I am using my own database and it already worked but the application at the end crashes.

      Best Regards,

      Kay

       
      • m.prestel

        m.prestel - 2021-05-05

        Hey

        My bad, I stopped reading the path at PlcLogic/trend.

        You are sure that you don't write outside of the bounds of e.g. the string?

        Best regards,
        Marcel

         
        • kebek007

          kebek007 - 2021-05-05

          Yes I am sure because I have a function that checks that. As I said it works, I see the log entries in the SQLite. My problem is the stability of the entire system. it seems to open so many processes and it just crashes.

          Thanks again for your help marcel, much appreciated

          Regards,
          Kay

           

          Last edit: kebek007 2021-05-05
          • m.prestel

            m.prestel - 2021-05-05

            Could you maybe provide a simplified example application to let me debug the issue?

             
        • Ingo

          Ingo - 2021-05-05

          At least my two cents from the runtime perspective:
          It should not be, that the number of processes increase, under normal
          circumstances. But, indeed, if the command produces a lot of output, I can
          imagine, that the current implementation of SysProcess might have such
          problems.

          So, I would recommend you to test it, by calling your own bash script,
          which is in fact doing the same as you do now, but discards the output.

          E.g. (untested):

          command0 : STRING := 'mysqlite3 ';

          1
          2
          #!/bin/bash
          exec "sqlite3" "$@" &> /dev/zero
          

          I'm curious about your result!

          Cheers,
          Ingo

           
          • kebek007

            kebek007 - 2021-05-05

            Well that is a very refreshing insight. I had no idea that it could have been on the output. I will definitely give that a whirl. Thanks a lot for your answer Ingo.

            Regards,

            Kay

             

Log in to post a comment.