In a software used since 2013, but often refreshed, I have a strange behavior.

I used many TABLES for application service use, notably the one that display the ListBox Header Strings (so I cannot have troubles with special characters, etc. And I can modify them if needed in the .sqlite file instead of inside the project (but will do there too).

Many times a year, I checked the contents of the .sqlite file, notably these TABLEs and I saw they are not alone, but appears many times (useless).

To be sure, think that I get many times the same(s) service TABLEs in the .sqlite file: totally useless.
The TABLE that hold the precious data is still unique. No data loss was detected.

I use:


at App firing time, so I do not understand how this is possible.

Nota: the same .sqlite file is used on Windows (production) and macOS (by the manager on its old MacBook Pro).

Ideas ?

According to the sqlite specification, the ‘IF NOT EXISTS’ clause in the sql statement prevents sql from creation a double of a existing table with the same name. It does not return an error, but should not result in a new table of the same name. Is this statement the only point in your application that creates these tables?

Thank you Torsten for your answer.

Yes… I searched “CREATE TABLE” to be sure I do not forgot to place the IF NOT EXISTS in one location (but this cannot be the case since the application is fired multiple times a month and I only get some duplicate entries…

I think I have an idea… but I do not have the project handly. I have to write the idea and check this afternoon (before I forgot).

1 Like

Just an idea:
Get the list of tables through the ‘Tables’ method right after the sql statement. This way, each time a duplicate table is added, you could nail the point in time when it happens.

I will look carefully the code after CREATE TABLE…

My attention was focussed on CREATE TABLE and I may have missed something after that.

There’s something very weird here. With or without IF NOT EXISTS a table can’t be created twice with the SAME NAME. The IF NOT EXISTS clause exists to avoid TRYING such thing, that in such case, it will rise an error,

CREATE TABLE new_table1 (id Integer); -- ok, created
CREATE TABLE new_table1 (id Integer); -- error! stop. Already exists.

CREATE TABLE IF NOT EXISTS new_table2 (id Integer); -- ok, created
CREATE TABLE IF NOT EXISTS new_table2 (id Integer); -- Ignore, it already exists.

The problem lies elsewhere in Emile’s code.

as usual, I think (quite sure) you are right Rick.

I take a moment yesterday to look at the CREATEs… and, it seems so.

I tried to read later (how I add a Record *) and… was so tired… I slept until midnight and then was not in the mood to program.

After carefully looking at the contents of the .sqlite file and its TABLEs, I was wrong!

The program adds a new RECORD from time to time!

On what criteria and why from time to time?

I’ll wait for a good day and take a closer look, but it’s not clear at all.

I have 2119 Records in a TABLE (“About”) when only 1 is required.

After some searches, I found an old one with the error I recalled. It have only 3 Records, created on:
2017-01-04 10:51:44
2017-01-13 11:13:34
2017-01-13 11:13:36

Yes, the last two were created at 2 different seconds!

Well, I have work to do, I’m going to explore the “About” TABLE

Another strange thing is… in the sqlite_master TABLE; the IF NOT EXISTS part is not there!

Thank you all for forcing me to think differently. I have a lead now.

IF NOT EXISTS will not be in sqlite_master. That is normal.

@Tim_Hare : OK.

It seems, for unknow reason, my code add a Record (the same contents, each time the application is run.

In that module, I have similar code (one for each TABLE, but only that one do that.

It may be time to refactor this nearly 10 years old project.

Are you sure the title is ok? It sounds like your problem are more than one records in tables where you only expect one. This would be no wonder if you are creating a table in case it does not exist. You’d have to either drop it before or modify the single record if it already exists.

An INSERT statement being made by your startup code?

Yes, called at startup time.

I added a MessageBox there to be sure !

And I checked the other Methods that deals with the other TABLEs, none have an INSERT statement. I do not understand what I have done so long ago (4 to 6 years ago…).

PS: I printed the whole project code to pdf to compare with the one in the IDE / search globally…

Obviously I don’t know what you are intending your code to do. However, calling an INSERT during application startup will add a new record to the table each time the application is started, unless a schema constraint causes the operation to fail.

Options to avoid creating duplicate records:

  1. Add a constraint to the table to prevent duplicate records being inserted.
  2. Check for the presence of the record using a SELECT before carrying out the INSERT
  3. Change the INSERT statement to an INSERT OR UPDATE statement, also known as UPSERT.

SQLite added an UPSERT facility at V3.24.

Aha, an instance of the, “It made sense at the time” bug or the related, “I don’t know how it works but it works” bug.


It was not to count the number of time there is operations (or data base file access) on the db because this is already done (sqlite do that: “File change counter” in the file Header part).