#2 ✓resolved
Geordi LaForge

Problem after a moving the piggydb directory to another pc

Reported by Geordi LaForge | October 26th, 2010 @ 12:10 PM

Class org.h2.jdbc.JdbcSQLException
Message Unique index or primary key violation: CONSTRAINT_INDEX_4 ON FT.WORDS(NAME); SQL statement: INSERT INTO FT.WORDS(NAME) VALUES(?) [23001-119]

In attach the whole report of the problem.
Have you an idea how resolve this issue ?

Thank you

PS: Thank you for this great code!!! It save my life on keeping note ;)

Comments and changes to this ticket

  • marubinotto

    marubinotto November 16th, 2010 @ 03:13 AM

    • State changed from “new” to “resolved”
    • Assigned user set to “marubinotto”

    Thank you for your report and sorry for late reply. I thought a notification mail would be sent to me, but it wasn't :-<

    When you migrate the database to another environment, it is safer using the export/restore features. Could you try it again with this export/restore features?

  • O

    O May 25th, 2011 @ 02:13 PM

    I am having a similar problem. I installed the very same (4.19) version of piggydb on two computers, one with Windows XP, the other with Ubuntu, and set up synchronization between them using dropbox. While on the one in which the database was lastly edited (Ubuntu) it works fine, on the other one (XP) piggydb wouldn't start, and this is what the log says:

    ERROR [2011/05/25 10:09:57] (PiggydbServlet) org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.h2.jdbc.JdbcSQLException: Unsupported database file version or invalid file header in file D:\piggydb\piggydb.h2.db [90048-119]
    ERROR [2011/05/25 10:09:57] (/) unavailable

    So I removed the DB files and tried the export/restore trick, but even then success was only partial. The data was there, but there were error messages about problem with it within the application. On the homepage there was just an error message, but through the tags page I could reach my fragments.

    Piggydb does almost exactly what I have been looking for for a long time, and I am very impressed with what it does. But a more reliable storage mechanism, to allow the data be safely moved between machines without issues would be a very important improvement.

  • marubinotto

    marubinotto May 26th, 2011 @ 11:36 PM

    • State changed from “resolved” to “open”

    Thank you for your report.

    As you found out, using dropbox is a little bit risky.

    What kind of error messages did you get when you used the export/restore features?

  • O

    O May 27th, 2011 @ 06:57 AM

    Thanks for your answer.

    I made a few further experiments and now I am not able to reproduce the export/restore problem anymore. It works fine in both directions.

    However, just moving the the contents of the database directory between computers works only one way: When I copy a working database directory from my XP computer to my Ubuntu computer, the latter opens the database as expected. However, when I copy the same directory back to the XP computer, piggyDB fails to load and I see this in the log file:

    ERROR [2011/05/27 06:27:56] (PiggydbServlet) org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.h2.jdbc.JdbcSQLException: Unsupported database file version or invalid file header in file D:\piggydb\piggydb.h2.db [90048-119]
    ERROR [2011/05/27 06:27:57] (/) unavailable
    org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.h2.jdbc.JdbcSQLException: Unsupported database file version or invalid file header in file D:\piggydb\piggydb.h2.db [90048-119]

    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)

    Although I had no reason to suspect the problem had anything to do with dropbox, which has always worked well for me with any kind of file, I made sure this has nothing to do with my using it. I moved the files using a USB drive, as well as in an archive file through dropbox.

    One difference I am noticing is that in XP start with an empty database directory and then restore a database from an XML file, these are the files I see in the piggydb database directory after I shut down piggyDB:


    On the Ubuntu computer, the only files I find are these:


    Again, both computers run the very same 4.19.

    So, in summary:

    1. Copying the contents of the db directory works XP->Ubuntu , but not Ununtu->XP.

    2. Although both computers run the same piggydb version, the contents of that dir is different when created on each of them.

    I am not knowledgeable enough to have an idea why this wouldn't work, but I think that for users such as myself, who continuously use more than one computer and use file synchronization applications to transfer their data between them, an ability to do this reliably is very important and thus deserves looking into.


  • marubinotto

    marubinotto May 30th, 2011 @ 10:13 AM

    Thanks for the detailed information.

    Actually, the database versions appear to be different according to the contents of the XP and Ubuntu directories you wrote. That's why the error message says "Unsupported database file version".

    V4.19 release upgraded the database engine and the new database format is not compatible with the older versions of Piggydb. The file "piggydb.h2.db" is a database file of the new version.


    Do you happen to have an old version of Piggydb in your XP computer?

  • O

    O May 31st, 2011 @ 05:00 AM

    Yes, that's what I suspected, seeing the different file names and having read about the new database format, although I was sure I had upgraded my older installation of piggydb. So I removed the existing installation, re-installed the new one, and also updated Java (not a very systematic way of doing things when one tries to find the source of a problem...), and now, finally, the files I move between the two computers work in both of them, with this single problem: when I view in the XP computer the latest fragment updated on the Ubuntu installation, the text is there, but also this message:

    The application encountered an unexpected error

    The error message isn't displayed when I view other items in the database. I think this was the message I mentioned in my first message above.

  • marubinotto

    marubinotto May 31st, 2011 @ 07:51 PM

    Basically Piggydb doen't support this kind of usage. I don't know the detailed behavior of the database engine (H2 Database Engine), but the database files should be locked by the Piggydb process while running (this might be an unexpected behavior for you) and if another process tries to write to the files, that would cause trouble.

  • marubinotto

    marubinotto June 9th, 2011 @ 02:11 AM

    • State changed from “open” to “resolved”

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Piggydb is a Web notebook application that provides you with a platform to build your knowledge personally or collaboratively.

Shared Ticket Bins

People watching this ticket