how to set up master slave on the same server, same application with litereplica

monamona

I've read the details page of Litereplica site where it explains how Litereplica works and what should we do on the master and on the slave side. But it didn't explain how to set up master slave and I did not understand how could I build master and slave on the same server, same application. For example, it is written that "On the same device, in the same app, on the same db connection, using an attached database" we should do the following on the master side:

file:/home/user/data.db?replica=master&bind=inproc://replica

and on the slave side:

ATTACH 'file:/home/user/data.db?replica=slave&master=inproc://replica' AS slave

But, I was wondering in the case when I want the slave/copy to be on the same device, same app,..with attached db, where in the application code, and how should I define/differentiate master and slave? (Sorry if this is something basic, but I am not a DB expert and I have never done replication before).

Thanks,

Mona

Bernardo

Hi Mona,

The first command is the URI Filename, and it is used when opening the database.

The second is a SQL command and it must be executed after the first database file is open.

You must choose in which folder the master/primary database will be and its name. The same for the slave/secondary/replica/backup database.

Eg.:

Primary: /home/user/myapp.db

Replica: /home/user/myapp-backup.db

Or in separate folders:

Primary: /home/user/myapp/data.db

Replica: /home/user/myapp/backup/data.db

What is the coding language you will use in your application?

And what is the OS?

monamona

Thanks for your reply. I try to explain my problem better:

I wrote this sample C code (imagine this is my application):

#include <stdio.h>

#include <sqlite3.h>

#include "litereplica.h"

int main(int argc, char* argv[])

{

sqlite3 *db;

char *zErrMsg = 0;

int rc;

// char *uri ="fuel_transaction_1.db?replica=master"; --> I commented this line because it just created an empty db named "fuel_transaction_1.db?replica=master"

rc = sqlite3_open("fuel_transaction_1.db", &db);

if( rc ){

fprintf(stderr, "CanNOT open the database: %s

", sqlite3_errmsg(db));

return(0);

}else{

fprintf(stderr, "Opened database successfully

");

}

sqlite3_close(db);

My questions are:

1. If I write char *uri ="fuel_transaction_1.db?replica=master" in my code, then it just creates an empty database named "fuel_transaction_1.db?replica=master". Also, when I used "char *uri = "file:/path/to/db&....", the output of my program was "CanNOT open the database". The only way that the code worked (I mean it opened the db) was when I wrote: rc = sqlite3_open("fuel_transaction_1.db", &db);. So, still I don't know where and how should I write “file:/path/to/main.db?replica=master" in my application code as you suggested in http://litereplica.io/details.html

2. After I compile and run my code (which open the database), I entered the db "fuel_transaction_1.db" and then I ran the following command (in sqlite3):

ATTACH '/root/litereplica/litereplica-master/sqlite3.6/fuel_transaction_1.db' AS slave

This built a slave database, so when I run .databases, I could see both main and slave databases (I attached the screenshot). However, the slave db disappears after I exit sqlite command prompt. I think there should be a way to permanently attach it to the main db and sync them. right?

Sorry for the long question, but I really feel confused.

Bernardo

Hi Mona!

The URI filenames are only available in SQLite versions starting at 3.7.7.

More info here: https://www.sqlite.org/uri.html

In these versions you can enable the use of URI with this command:

sqlite3_config(SQLITE_CONFIG_URI,1);

But I will enable them in the binaries by default using the SQLITE_USE_URI=1 compile-time option. I will update the binaries later today or maybe tomorrow.

If you need to use SQLite 3.6 then you can use the C API, like this:

/* open the primary database */
sqlite3_open("fuel_transaction_1.db", &db);

/* open the backup database attached to the main database */
sqlite3_exec(db, "ATTACH '/root/litereplica/litereplica-master/sqlite3.6/fuel_transaction_1_backup.db' AS backup", NULL, NULL, &errmsg);

/* enable the replication */
sqlite3_add_replica(db, "main", REPLICA_MASTER, REPLICA_CONNECT, "inproc://replica");
sqlite3_add_replica(db, "backup", REPLICA_SLAVE, REPLICA_BIND, "inproc://replica");

Another option: using 2 database connections:

/* open the primary database */
sqlite3_open("fuel_transaction_1.db", &db);

/* open the backup database */
sqlite3_open("/root/litereplica/litereplica-master/sqlite3.6/fuel_transaction_1_backup.db", &db2);

/* enable the replication */
sqlite3_add_replica(db, "main", REPLICA_MASTER, REPLICA_CONNECT, "inproc://replica");
sqlite3_add_replica(db2, "main", REPLICA_SLAVE, REPLICA_BIND, "inproc://replica");

The database file disappears because there were no writes on it. And this happened because the replication did not take place.

Solving the questions above may solve this one too.

Feel free and comfortable to ask your questions.

Happy coding!

Bernardo

I don't know if you understood, but the URI must start with "file:", like this:

char *uri = "file:fuel_transaction_1.db?replica=master&bind=ipc://./test.ipc";

monamona

Thanks a lot for your time. I applied the modifications you mentioned in your response, but when I compile and run my code, it creates the empty database "fuel_transaction_1_backup.db". Do you know why is it empty? (previously, when I used ATTACH inside sqlite3 command prompt, it created the copy of the db which was not empty, but now that I used it within sqlite3_exec command, it just creates an empty db)

Here is the code that I run:

#include <stdio.h>

#include <sqlite3.h>

#include "litereplica.h"

int main(int argc, char* argv[])

{

sqlite3 *db;

//char *zErrMsg = 0;

char *errmsg = 0;

int rc;

rc = sqlite3_open("fuel_transaction_1.db", &db);

sqlite3_exec(db, "ATTACH '/root/litereplica/litereplica-master/sqlite3.6/fuel_transaction_1_backup.db' AS backup", NULL, NULL, &errmsg);

sqlite3_add_replica(db, "main", REPLICA_MASTER, REPLICA_CONNECT, "inproc://replica");

sqlite3_add_replica(db, "backup", REPLICA_SLAVE, REPLICA_BIND, "inproc://replica");

if( rc ){

fprintf(stderr, "Can't open database: %s

", sqlite3_errmsg(db));

return(0);

}else{

fprintf(stderr, "Opened database successfully

");

}

sqlite3_close(db);

}

PS. as the result of running this code, it prints "Opened database successfully", and also creates the "fuel_transaction_1_backup.db" which is empty.

Thanks for your support,

monamona

Sorry, one more thing:

After I run my code, when I go into sqlite command prompt (sqlite3 fuel_transaction_1.db) and type .databases, it only shows the main db (i.e., nothing is attached here)... BUT, an empty db file fuel_transaction_1_backup.db is created in the same folder.

Bernardo

Hi!

Your app is closing the database very soon, so the engine does not have time to make the synchronization.

In a normal use the app would be kept running for sufficient time.

In a test app you can add the litereplica_join() function, so the app will not exit.

Example:

#include <stdio.h>
#include <sqlite3.h>
#include "litereplica.h"

int main(int argc, char* argv[])
{
   sqlite3 *db;
   //char *zErrMsg = 0;
   char *errmsg = 0;
   int rc;

   rc = sqlite3_open("fuel_transaction_1.db", &db);

   sqlite3_exec(db, "ATTACH '/root/litereplica/litereplica-master/sqlite3.6/fuel_transaction_1_backup.db' AS backup", NULL, NULL, &errmsg);

   sqlite3_add_replica(db, "main", REPLICA_MASTER, REPLICA_CONNECT, "inproc://replica");
   sqlite3_add_replica(db, "backup", REPLICA_SLAVE, REPLICA_BIND, "inproc://replica");

   if( rc ){
      fprintf(stderr, "Can't open database: %s
", sqlite3_errmsg(db));
      return(0);
   }else{
      fprintf(stderr, "Opened database successfully
");
   }

   litereplica_join();  // the main thread will stop here until you terminate the program

   sqlite3_close(db);
}

Remember that the code that is on GitHub, from version 1, does not work well with databases > _60MB.

For these there is the version 2, currently released only under the commercial license.

monamona

Thanks for your quick reply.

I added the litereplica_join() function before closing the db, but then when I run the code, it prints "open the db successfully" and stucks there.. (I think because as you said litereplica_join() function keeps the app open..). so, how should I exit it then??

Also, could you please let me know if there is any application (as a sample) that used litereplica?

Many thanks,

Mona

monamona

I just noticed that another file "fuel_transaction_1.db-journal" is added into the same directory "/root/litereplica/litereplica-master/sqlite3.6/ which is not empty. Do you know what is this file?

Bernardo

After I run my code, when I go into sqlite command prompt (sqlite3 fuel_transaction_1.db) and type .databases, it only shows the main db

Yes, the attachment is removed when you close the main database or when you use the DETACH DATABASE command.

The app must re-attach the database using the same commands.

The sample application depends on the usage. For which OS? How will your app be kept open? Will it use a message loop? Of what kind?

But basically instead of calling litereplica_join() you will put your normal application initialization code there. Or better: the function above will be named something like open_database() and called on the initialization.

The created file is the database journal file, used in the transactions. You can search google for "SQLite Journal" for details.

monamona

Thanks for your kind support. I try to explain my problem better:

When I run my sample C code, it does not attach the backup DB to the main DB. The only thing that happened is that an empty file (fuel_transaction_1_backup.db) was created. So, what was the reason for adding sqlite3_exec(db, "ATTACH '/root/litereplica/litereplica-master/sqlite3.6/fuel_transaction_1_backup.db' AS backup", NULL, NULL, &errmsg); to the code? As far as I understood, you said the attachment is removed because I closed the DB at the end of my program (sqlite3_close(db);), but I wrote litereplica_join(); before closing the DB, which as you mentioned in your previous comment, this function is to keep the DB open (this also caused another problem i.e., when I run my code it stucks there forever).. I think it did not even reach to sqlite3_close(db) line. So, why the ATTACH did not work?

On the other hand, when I use ATTACH directly on sqlite command prompt, it attached the DB, but it is removed as soon as I exit the sqlite environment (I think because it close the DB as well).

So, finally I didn't understand how can I produce a replicated/slave DB (on the same machine) which is synced with the main DB?

By sample application, I meant any sample code/tutorial that used Litereplica to replicate their DB.. Is there any sample I can use as starting point?

Sorry, but I really got confused.. I am quite a beginner and I feel I need some more detail info/samples.

PS. I am working on a BeagleBoneBlack (Linux Debian 7.11).

monamona

Sorry, one more question regarding Litereplica licensing. It is written in your site that it costs 195$ for commercial use supported by community forum.

Could you please let me know for how long we can use this license?

Bernardo

Hi Mona!

I cannot know without more information. So here are some questions:

1. Are you keeping the app open for sufficient time for the replication to take place? The time depends on the database size. You can check the creation of the journal file and only close the test app after the journal file is removed by the app.

2. What is the size of your main database?

3. The user used to open the database has access to the /root folder? have you tested it on another folder?

4. Do you understand how the SQLite works? Remember that it is not based on a server. It is just a library. Each time you open your app some configs must be set, as a database attachment (if it is used/required).

5. Do you really need to use an attached database? Are you understanding what it is? Maybe starting with 2 database connections would be easier to understand and start. I sent you a code sample on how to set.

6. Using SQLite version 3.7 or later is easier as you can use URI. Do you really need to use SQLite 3.6?

7. I wrote that the litereplica_join() is there in the demo code just for test purposes and it must be replaced by your way of keeping the app open. The app being keep open is not a problem, it is intentional in the test code. You did not answer what kind of message loop you are using in your code. Or will your application just execute and exit?

Additionally, consider purchasing 1 or 2 support hours. I can access your computer remotely and let the code running for you. It will be much faster because I can detect and solve problems on the device, storage, OS or usage. You will be watching all the tests.

The commercial license does not expire. If you want to read the license terms, please contact me via e-mail.

Bernardo

You can check the litereplica being used with the SQLite shell in this video:

https://www.youtube.com/watch?v=Vum6mq8cMm0