sqlite3_close does not close db and does not return any error in C

Question

I write an example of sqlite C program which tries to creat a DB and its table and insert to its table continuously . but my program does not close db it opened and it has memory leak too. I cant find my mistake . this is my C code:

 #include <stdio.h>
 #include <stdlib.h>
 #include <sqlite3.h>
 #include <string.h>
 #include <time.h>
 #define SQLITE_MAIN_BASE "/var/"
 #define SQLITE_DEVLOG_BASE  "./"
 #define Create_All_Database_Error 1
 void addlog(const char * logtext,const char *logpath)
 {
      struct tm *timeinfo;
      time_t rawtime;
      if (logpath != NULL )
      {
          if (strlen(logpath) > 4)
          {
              FILE *Lfile;
              Lfile = fopen(logpath,"a");
              if (Lfile)
              {
                  char cur_dtime[50];
                  time ( &rawtime );
                  timeinfo = localtime (&rawtime);
                  strcpy(cur_dtime,asctime(timeinfo));
                  cur_dtime[strlen(cur_dtime)-1] = '\0';
                  fputs(cur_dtime,Lfile);
                  fputs(" :: ",Lfile);
                  fputs(logtext,Lfile);
                  fputs("\n",Lfile);
                  fclose(Lfile);
              }
              else
              {
                  fprintf(stderr,"can not open log file %s\n",logpath);
              }
          }
      }
 }
 MySqlite_close(sqlite3 *db)
 {
     int i=0,
         rc=0;
     rc=sqlite3_close(db);
     while(rc != SQLITE_OK)
     {
         printf("yet closing\n");
         if (rc == SQLITE_BUSY)
         {
             printf("it is busy\n");
             i++;
             if ( i > 10 )
             {
                 return rc;
             }
         }
         sleep(1);
         rc=sqlite3_close(db);
     }
     printf("2closeeeeeee\n\n");
     return 0;

 }
 int MySqlite_Exec(const char *dbname,const char *query,sqlite3_stmt **retStmt,const char *queryTail2,const char *logpath,int logfd,int mode)
 {
     sqlite3 *db;
     char logmessage[1500];
     char dbfilepath[150];
     int rc=0;
     retStmt=NULL;
     sprintf(dbfilepath,"%s%s",SQLITE_DEVLOG_BASE,dbname);
     fprintf(stdout,"%s\n",query);
 //    fprintf(stdout,"%s\n",dbfilepath);
     rc = sqlite3_open(dbfilepath, &db);
 //    if( rc )
     while(sqlite3_open(dbfilepath, &db))
     {
         sprintf(logmessage,"1Error on \"%s\" : %u  %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
         printf("%s\n",logmessage);
 //        addlog(logmessage,logpath);
 //        printf("1\n");
         sleep(10);

         MySqlite_close(db);
 //        return sqlite3_errcode(db);
         return 0;
     }
     printf("10\n");
     if( sqlite3_prepare_v2(db, query, strlen(query)+1, retStmt,NULL) != SQLITE_OK )
     {
         sprintf(logmessage,"2Error on \"%s\" : %u  %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
         printf("%s\n",logmessage);
 //        addlog(logmessage,logpath);
         while(sqlite3_prepare_v2(db, query, strlen(query)+1, retStmt,NULL) == SQLITE_BUSY)
         {
             sprintf(logmessage,"2Error on \"%s\" : %u  %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
             printf("%s\n",logmessage);
 //            addlog(logmessage,logpath);
             sleep(1);
         }
     }
     printf("12\n");
     if (mode==0)
     {
         printf("222\n");
         if (sqlite3_step(*retStmt) != SQLITE_DONE)
         {
              sprintf(logmessage,"3Error on \"%s\" : %u  %s ",query,sqlite3_errcode(db), sqlite3_errmsg(db));
              printf("%s\n",logmessage);
         }
         if ( *retStmt != NULL )
         {
             printf("retStmt is not NULL\n");
             sqlite3_step(*retStmt);
             while(sqlite3_finalize(*retStmt)!=SQLITE_OK)
             {
                 sprintf(logmessage,"20Error on  : %u  %s ",sqlite3_errcode(db), sqlite3_errmsg(db));
                 printf("%s\n",logmessage);
                 printf("finilized NOT ok\n");
                 sleep(1);
             }
             printf("finilized ok\n");

 //            sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
         }
         else
         {
             printf("retStmt is NULL\n");
         }
         MySqlite_close(db);
     }
    return 0;
 }

 int Create_SqltDB(void)
 {
     char hostip[20],
          LOG_FILE[100],
          query[1000],
          dbfilepath[100],
          logmessage[1500];
     int result=0;
     const char *queryTail;

     sqlite3_stmt *retStmt;
     sprintf(LOG_FILE,"/var/log/Emain.log");
     sprintf(query,"create table if not exists lastuptime(row integer primary key not NULL,microupdatetime double default 0 not NULL,time double default 0 not NULL);");
     if (MySqlite_Exec("lastuptime",query,&retStmt,queryTail,LOG_FILE,2,0))
     {
         return Create_All_Database_Error;
     }
     int i=0;
     for (i=0; i<1000000;i++)
     {
         sprintf(query,"insert into lastuptime(microupdatetime,time) values (%i,%i);",i,i);
         if (MySqlite_Exec("lastuptime",query,&retStmt,queryTail,LOG_FILE,2,0))
         {
             return Create_All_Database_Error;
         }
     }

     sprintf(query,"select * from lastuptime;");
     if (MySqlite_Exec("lastuptime",query,&retStmt,queryTail,LOG_FILE,2,1))
     {
         return Create_All_Database_Error;
     }

     do
     {
         result = sqlite3_step (retStmt) ;
         if (result == SQLITE_ROW) /* can read data */
         {
             printf(" %d \t|\t %f \t|\t '%f' \n",\
                     sqlite3_column_int(retStmt,0),\
                     sqlite3_column_double(retStmt,1),\
                     sqlite3_column_double(retStmt,2)) ;
         }
         else
         {
             printf("no data\n");
         }
     } while (result == SQLITE_ROW) ;

 }
 int main()
 {
     Create_SqltDB();
     return 0;
 }

and this is my log:

create table if not exists lastuptime(row integer primary key not NULL,microupdatetime double default 0 not NULL,time double default 0 not NULL);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee

insert into lastuptime(microupdatetime,time) values (0,0);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee

insert into lastuptime(microupdatetime,time) values (1,1);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee
..
..
..
..
.
insert into lastuptime(microupdatetime,time) values (1017,1017);
10
12
222
retStmt is not NULL
finilized ok
2closeeeeeee

insert into lastuptime(microupdatetime,time) values (1018,1018);
10
12
222
3Error on "insert into lastuptime(microupdatetime,time) values (1018,1018);" : 14 unable to open database file
retStmt is not NULL
20Error on : 14 unable to open database file
finilized NOT ok
20Error on : 14 unable to open database file
finilized NOT ok
20Error on : 14 unable to open database file
finilized NOT ok
20Error on : 14 unable to open database file
finilized NOT ok
20Error on : 14 unable to open database file
finilized NOT ok

I checked my running process with lsof and htop to find out its opened files and memory leak. thanks for any help

Answer 1

According to this page it is intentional. File handles are not closed if there is something going on with the database. In my experience the file handle is always closed if you don't modify the database.

http://sqlite.1065341.n5.nabble.com/sqlite3-close-doesn-t-release-always-the-file-handle-td14159.html

Answer 2

The following snippet of your code looks suspicious in that the open call is made more than once without an intervening close.

rc = sqlite3_open(dbfilepath, &db);
//    if( rc )
while(sqlite3_open(dbfilepath, &db))
...

The documentation states the following:

Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.