mydb.trace.db despite TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0

[EDV-Services]

Hi guys,

I've developed a software project and I've noticed that there is a file in my user folder called "mydb.trace.db".
When I open this file, I see a lot of sql statements exposing some tables of my database.
I use the following connection string: jdbc:h2:~/mydb;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0;CIPHER=AES"
I thought these two TRACE parameters in my connection string must be enough to avoid creating the mydb.trace.db file.

That's the trace output from "mydb.trace.db":

02-25 11:47:33 jdbc[2]: exception
org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "INSERT INTO CUSTOMER(xxx, yyy) VALUES('xxx', 'yyy');
...

I'm using the latest version "h2-1.3.172.dll" (dll file created from jar file with ikvm).
The trace entries occur when i use the following vb.net code:

Dim restore As New Restore()
restore.execute("backup.bck", "~", "mydb")

I don't want hackers to be able to see my database content so imho it's a serious security flaw.
Can anyone give me a hint what I'm doing wrong?

Best regards
Michael

[Thomas Mueller]

Hi,

Sorry I can't reproduce the problem. The *.trace.db file is not created in my case, with the latest version of H2.

Please note the message you posted starts with "02-25 11:47:33", this means the message is from February 25th. Are you sure you disabled the setting back then? What version of H2 did you use back then?

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[EDV-Services]

Hi Thomas,

thank you for your reply.

I've opened the trace.db-file in Notepad++ and I deleted every single line in that file.
The file was empty. Then I tried to restore a backup file and Notepad++ reloaded the file with the input I've posted above.
I also tried to delete the file, but H2 created it again.

Regarding the date I'd like to tell you that my trace.db file contains a lot of such lines with different dates:

02-25 11:47:33 jdbc[2]: exception ...
02-25 11:50:27 jdbc[2]: exception ...
02-25 11:51:02 jdbc[2]: exception ...
02-25 11:51:33 jdbc[2]: exception ...
02-25 11:52:34 jdbc[2]: exception ...
02-25 11:59:05 jdbc[2]: exception ...
02-25 20:47:35 jdbc[2]: exception ...
02-25 21:06:55 jdbc[2]: exception ...
02-25 21:07:06 jdbc[2]: exception ...
02-25 21:07:15 jdbc[2]: exception ...
02-25 21:08:24 jdbc[2]: exception ...
02-25 21:11:05 jdbc[2]: exception ...
02-25 21:11:18 jdbc[2]: exception ...
02-25 21:16:21 jdbc[2]: exception ...
02-25 21:17:06 jdbc[2]: exception ...
02-25 21:51:59 jdbc[2]: exception ...
02-25 21:52:10 jdbc[2]: exception ...
02-25 21:52:13 jdbc[2]: exception ...
02-27 13:43:27 jdbc[2]: exception ...
03-06 19:08:42 jdbc[2]: exception ...
03-07 16:39:26 jdbc[2]: exception ...
03-07 16:39:33 jdbc[2]: exception ...
03-08 15:24:57 jdbc[2]: exception ...
03-08 15:24:57 jdbc[2]: exception ...
03-08 16:46:04 jdbc[2]: exception ...
03-07 19:39:57 jdbc[2]: exception ...

Before I haven't used this code "TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0".
I just started to use it 4 weeks ago. And I used "h2-1.3.160" until last week.
Last Sunday I switched to "h2-1.3.172" because I thought it could solve my problem but it didn't.

What I've noticed is that the backup file is restored with a lot of errors.
Sometimes the column number of a table doesn't fit which is very curious because the restore file contains the db structure and also the db data.
So why are there errors when I make a backup and after 5 seconds I try to restore the same file?

Thank you again for your help.

Best regards,
Michael

[EDV-Services]

Btw: Now I did a restore by using the web interface of H2 database.
The trace.db-file was created again with the same output.

[EDV-Services]

Sorry again, now I deleted all the data in all database tables except for the table "EINSTELLUNGEN"
The full content of the trace.db-file is as follows:

07-09 12:51:43 jdbc[2]: exception
org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "EINSTELLUNGEN"; erwartet "EXPLAIN, EXECUTE, {"
Syntax error in SQL statement "EINSTELLUNGEN"; expected "EXPLAIN, EXECUTE, {"; SQL statement:
EINSTELLUNGEN [42001-170]

It seems like the errors only occur when H2 tries to restore the database data.
And what does that error message mean? EXPLAIN, EXECUTE?

Am Sonntag, 14. Juli 2013 16:14:31 UTC+2 schrieb EDV-Services:

[Thomas Mueller]

Hi,

I understand that it doesn't work for your case, but I'm afraid I can't help you much because it works for me, and so far you didn't provide enough information to reproduce the problem.

Just a few things I noticed: 

* You wrote you use the latest version of H2, but in fact the latest error message you got is from an older version (1.3.170). Reason: the build number is included in the error code, which is [42001-170]. So the build is 170.

* The .trace.db file contains old dates. You wrote you reproduce the problem now, but the error message starts with  "07-09 12:51:43". So it clearly was written at 2013-07-09 and not July 15th. The same with older messages.

* You seem to backup and possibly restore files. One explanation is that you restore old files over existing files.

* My guess is that you didn't always use the database URL you provided.

That's all I can say right now. I don't think this is a bug in H2.

Regards,

Thomas

--

[EDV-Services]

Hi Thomas,

thank you again for your reply.

I definitely use the "h2-1.3.172.dll" file. I've downloaded the jar file some days ago and I used IKVM to transform the jar file into a dll file to be able to use H2 in my .NET project.
I understand that my trace-db file contains old dates but I don't understand why as I don't know anything about the internal H2 db processes.
All I can say is that I delete the trace-file before doing a restore and then that output is created.

> One explanation is that you restore old files over existing files

Thats true. In my application you have a database running and you can do backups and restores whenever you want.
When I do a restore, the database already exists. I don't delete the database before I restore an old backup file.
Is that the problem?

Also true is, that before I haven't used the same URL as now.
As I told you, I've added the two parameters "TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0;" later.
Maybe H2 saves the url parameters into the database files on first database connection so if you add these two parameters to your connection URL later, then they won't affect the trace output anymore.

I would like to send you my backup file, so you can try to restore the file on your machine.
Are you okay with that? If yes, then please tell me your email address.

Best regards
Michael

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

> I definitely use the "h2-1.3.172.dll" file.

Yes, I understand.

> I understand that my trace-db file contains old dates but I don't understand why

Well, the most simple explanation is that those files are old files. Probably your application restored them from somewhere (I think I wrote that already). You need to figure out why this happens. This is your application, and I can't really help you there. This is not a problem of H2.

> I would like to send you my backup file, so you can try to restore the file on your machine.

Sorry, I'm not quite sure how this would help. It seems you need to figure out what's wrong with your application, why it restores old .trace.db files. This is your application, and I'm not able to help you with that.

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[EDV-Services]

Hi Thomas,

I thought the same, I thought it could be a bug in my application.
Then I restored my latest backup file by using the web interface of H2 by clicking on "Tools" -> "Restore" -> "Start".
And the result was the same: Trace-output of error messages in trace.db-file even if I remove the trace file before starting to restore.
That's why I offered you to send you my backup file so you can try it with the web interface, too.

I'm 100% sure, it has nothing to do with my application.
The only mistake I could have done was when I created my database the first time and then accessed it without the trace = 0 parameters in my db url.
I guess H2 saved the trace parameters TRACE_LEVEL_SYSTEM_OUT = 1 and TRACE_LEVEL_FILE = 1 (default) inside the database files so changing the url parameters later won't affect anything.

Nevertheless, thank you for your reply again.

Best regards,
Michael

[Ryan How]

If you look inside the zip file of your backup are there trace files in there?

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[EDV-Services]

thank you for the hint, that was the problem
beside recovering the structure and data of the database, H2 also recovers the already written trace output