Main Site Documentation

SQLite. What is wrong


#1

DB is malformed after some time.
I just have a timer to test writing to the DB


using System;
using System.Collections;
using Microsoft.SPOT;
using GHI.SQLite;
using System.Threading;

namespace TouchLogger
{
    public class SQL_DBase
    {
        // Create a database in memory,
        // file system is possible however!
        Database myDatabase = null;

        public void Init()
        {
            try
            {
                myDatabase = new Database("\\SD\\MicLog1.db");
                // Process SQL query and save returned records in SQLiteDataTable
                ResultSet result = myDatabase.ExecuteQuery("SELECT * FROM Powerup");
            }
            catch (Exception Ex)
            {
                Debug.Print(Ex.Message);
                myDatabase.ExecuteNonQuery("CREATE Table Powerup (Room TEXT, Time INTEGER, Value INTEGER)");
                Debug.Print("DB Create");
            }

            try
            {
                // Process SQL query and save returned records in SQLiteDataTable
                ResultSet result = myDatabase.ExecuteQuery("SELECT * FROM Powerup");
                //ResultSet result = myDatabase.ExecuteQuery("SELECT * FROM Powerup where Value=777");

                String[] origin_names = result.ColumnNames; // Get a copy of columns orign names example

                ArrayList tabledata = result.Data;  // Get a copy of table data example

                String fields = "Fields: ";
                for (int i = 0; i < result.ColumnCount; i++)
                {
                    fields += result.ColumnNames[i] + " |";
                }
                Debug.Print(fields);
                object obj;
                String row = "";
                for (int j = 0; j < result.RowCount; j++)
                {
                    row = j.ToString() + ":";
                    for (int i = 0; i < result.ColumnCount; i++)
                    {
                        obj = result[j, i];
                        if (obj == null)
                            row += "N/A";
                        else
                            row += obj.ToString();
                        row += " |";
                    }
                    Debug.Print(row);
                }
            }
            catch (Exception Ex)
            {
                Debug.Print(Ex.Message);
            }

            myDatabase.Dispose();
            Timer MyTimer = new Timer(new TimerCallback(RunMe), null, 5000, 1000);
        }

        void RunMe(object o)
        {
            try
            {
                Debug.Print("Timer!");
                myDatabase = new Database("\\SD\\MicLog1.db");
                myDatabase.ExecuteNonQuery("INSERT INTO Powerup (Room, Time, Value) VALUES ('Kitchen', 010000, 4423)"); //add rows to table
                myDatabase.ExecuteNonQuery("INSERT INTO Powerup (Room, Time, Value) VALUES ('Living', 010000, 777)"); //add rows to table
                Debug.Print("DB Insert");
                myDatabase.Dispose();
            }
            catch (Exception Ex)
            {
                Debug.Print(Ex.Message);
                myDatabase.ExecuteNonQuery("CREATE Table Powerup (Room TEXT, Time INTEGER, Value INTEGER)");
                Debug.Print("DB Create");
            }
        }
    }
}


#2

@ microt - Roughly how long does it take to become malformed? What are you seeing that leads you to believe it is malformed? What other processes are running on the board?


#3

2 minutes…

The
myDatabase.ExecuteNonQuery("INSERT

is popping up with an exception

There are no other threads running.


#4

Sorry I thought it was obvious

Exceptions is “DB is malformed”


#5

@ microt - What board are you using? I would run the test again keeping the database open the entire time and not disposing of it and recreating it in every timer callback.


#6

G120 with the SD card socket wired to it.

recreating it in every timer callback.

I do not want to do that.
I want to build up the DB and see how big it can go.

So I can test the reliability for a product I am making


#7

@ microt - I ran the code you provided on a G120 and it ran without issue for over thirty minutes. I would try to just read and write a fairly large file from the SD card and see if the issue persists so we can try and narrow it down.


#8

Thanks John

To rule our the possibility of the SD card issues I changed the code to write it to a USB Drive.

The first run it came up with DB malformed error after some time.

The 2nd run, ran for a long time.

I can send you the malformed DB file, in case you have some tool to see what happened.
(It is not letting me attach here)



            Program.DBFile = "\\USB\\MicLog1.db";


    public class SQL_DBase
    {
        // Create a database in memory,
        // file system is possible however!
        Database myDatabase = null;
        static public Int32 DBCnt = 0;
        Timer MyTimer = null;
        Thread InitThread = null;

            /// <summary>
            /// Default constructor 
            /// </summary>
        public SQL_DBase()
        {
            InitThread = new Thread(Init);
            InitThread.Start();
        }

        public void Init()
        {
            try
            {
                myDatabase = new Database(Program.DBFile);
                // Process SQL query and save returned records in SQLiteDataTable
                ResultSet result = myDatabase.ExecuteQuery("SELECT * FROM Powerup");
            }
            catch (Exception Ex)
            {
                Debug.Print(Ex.Message);
                myDatabase.ExecuteNonQuery("CREATE Table Powerup (Room TEXT, Time INTEGER, Value INTEGER)");
                Debug.Print("DB Create");
            }
            while (true)
            {
                try
                {
                    Debug.Print("DB Loop!");
                    myDatabase = new Database(Program.DBFile);
                    myDatabase.ExecuteNonQuery("INSERT INTO Powerup (Room, Time, Value) VALUES ('Kitchen', 010000, 4423)"); //add rows to table
                    myDatabase.ExecuteNonQuery("INSERT INTO Powerup (Room, Time, Value) VALUES ('Living', 010000, 777)"); //add rows to table
                    Debug.Print("DB Insert");
                    myDatabase.Dispose();
                    DBCnt++;
                }
                catch (Exception Ex)
                {
                    Debug.Print(Ex.Message);
                    //myDatabase.ExecuteNonQuery("CREATE Table Powerup (Room TEXT, Time INTEGER, Value INTEGER)");
                    //Debug.Print("DB Create");
                    myDatabase.Dispose();
                    myDatabase = new Database(Program.DBFile);
                }
                Thread.Sleep(1000);
            }
}


#9

I have not tried it. I can give that a shot.

But I am worried that the DB will not flush to disk in case of power failure.
Is that not the case?


#10

I will test and get back.

But when is the flush done?

myDatabase.ExecuteNonQuery("INSERT INTO Powerup

or
only after

myDatabase.Dispose();


#11

@ andre - what’s the result if you dont dispose the database after each write?
It still gave this error
DB Loop!
#### Exception GHI.SQLite.Database+QueryExecutionException - 0x00000000 (5) ####
#### Message: unable to open database file
#### GHI.SQLite.Database::ExecuteNonQuery [IP: 002a] ####
#### TouchLogger.SQL_DBase::Init [IP: 0050] ####
A first chance exception of type ‘GHI.SQLite.Database.QueryExecutionException’ occurred in GHI.SQLite.dll
unable to open database file


#12

@ microt - I know you said you changed the SD Card, but do you think it could be more than one bad SD Card? Or maybe the socket?

To me it sounds like a Read/Write Access issue and the first thing that comes to mind is a throughput issue.

All the Best

Chris


#13

@ microt - You’ll need to not recreate the database in each loop as well. That’s probably why you’re getting the cannot open error.


#14

@ John, I tried just dispose and that did not help either.

I think I found the issue. The SDCard as not a static object so was getting disposed by the GC.I am running a test so will confirm this soon

One thing I am still not comfortable is.
If the DB is malformed. How do I fix it.

Restarting the board does not help. Because the DB is still malformed.
Is there anything I can run on the DB so it can fix itself.
I am ok in loosing some values.
But I do not want to delete the DB file in the field and start over.


#15

@ microt - I am not sure how to recover corrupted SQLite databases. You might find more information on SQLite’s site for that. We did not write the SQLite driver itself, only a few wrapper calls to use in C#.


#16

I’ll reiterate what I said before. You can’t help but expect issues if there’s an interruption during write on any device. Now that you know you had a code issue with SD fix it. As for power, flush frequently. Every insert should have a flush. Make it part of your driver…changes = flush. Put on a battery monitor or backup to help mitigate issues. Do regular backups of the db file in case of catastrophic failure.


#17

Thanks @ Skewworks

I remember someone telling that their files are up to 2GB in Size

Now my DB size is\SD\MicLog.db:1,176,576

I can INSERT data …no issues.

But if I do a select * it, it gives me a

Failed allocation for 100 blocks, 1200 bytes

#### Exception GHI.SQLite.Database+QueryFinalizationException - 0x00000000 (4) ####
#### Message: out of memory
#### GHI.SQLite.Database::FinalizeSqlStatment [IP: 0018] ####
#### Mic.SQL_DBase::DB_Count [IP: 001c] ####
#### Mic.SQL_DBase::Init [IP: 002c] ####

GC: performing heap compaction…

What can I do to print all my data


#18

@ microt - i don’t really use SQLite but if i see the “Failed allocation for 100 blocks, 1200 bytes” the i would say it’s a memory issue. Should be easy to check if you change your select * to a select top 100 * and see if you still have the memory allocation error.


#19

What you really need to figure out is not how to load all rows into memory at once and try to print them, but to iterate through them. That’s totally a SQLite question that you might need specific guidance on - but a quick Bing search tells me that you can use a LIMIT clause with two parameters to bring in only a chunk of data at a time, so you could construct something to help not flood the memory… out of interest, how many rows are you talking about, and is printing them out actually an approach you would take??