Debugging note

Background
HP is posting many challenges on TopCoder to advertise their new technology Haven platform.
I am currently involved in this mapping tutorial challenge, which requires the setup of a local version of Vertica DB.
The data provided by TC contains two files. The first is a schema.sql, which drops a table first and then creates it. The second is the data insertion file, which contains over 200,000 entries of INSERT
statements, following a DELETE FROM
table cleaning statement.
I created the database named NYTrafficCollision
using adminTools
, and connected to it. Then I executed the schema.sql file, and then executed the data insertion SQL file. (For the record, the data insertion was quite slow. I started the execution of the data insertion SQL file at about 23:30, and went to bed to read a book. When I finished the book in about 1:00 am, I got up and checked. The data was still being inserted and I just left my PC on all night.)
Problem description
The problem was found when I tried to fetch records from the DB using JDBC Vertica Driver. The ResultSet
returned from the Connection
was always containing 0 records.
Before this tutorial challenge, I have previously written another tutorial project that connected to Vertica performing CRUD actions, which was fine. This time when I tried to connect to the DB and perform only reading actions, however, no record was returned at all.
Nevertheless, when I executed the command below the result got me feeling very weird.
SELECT COUNT(*) FROM nypd_motor_vehicle_collisions;
The return result was
count
--------
204709
(1 row)
How come the data was stored in the table, and I could not get any records from it using JDBC?
Struggling
Feeling weird of this bug, I tried the following things to make things work.
- I checked whether the connection to the DB was successful, and as it turns out the connection was good.
- I printed the returned
ResultSet
size using awhile(rs.next())
loop, and the print result was always0
. - I used
Statement
instead ofPreparedStatement
, no luck. - I got rid of
try
-with-resource statement introduced in Java 7, no luck. - I tried to open my old project (the one teaching how to fetch Tweets from Twitter and how to get sentiment analysis results using HP IDOLOnDemand). It cannot connect to the DB called
TwitterAnalysis
.
So I turned off the databaseNYTrafficCollisions
and turned onTwitterAnalysis
instead. The query against the database was good.
It seemed the problem was in the project or in the database. - I switched back to the new project, and tried to use the project to connect to the old DB
TwitterAnalysis
and the printed size of returnedResultSet
was474
.
Finally. - I stopped the database
TwitterAnalysis
and startedNYTrafficCollisions
again. - This time, very weird things happened. When I executed the following command again, the return result was no longer
204709
.
SELECT COUNT(*) FROM nypd_motor_vehicle_collisions;
The return result was
count
--------
0
I didn’t know why this was the case. Was the schema.sql
automatically executed again? Because there is a DROP TABLE
statement in the head of it.
Then I realized maybe restarting the database was the cause. I did a search on Google and found this post. Fortunately(Unfortunately?), someone had been in the same place where I was. There is a reply in the post describing a potential cause for this problem.
Resolution
I executed the data insertion SQL file again, but this time I didn’t wait for it to finish. I interrupted using CTRL + C
and executed the SELECT COUNT(*)
statement. The count was 47
this time.
I issued a COMMIT;
command in the database terminal, and tried to fetch data using JDBC again.
Voila! The data was presented in the Webpage.
After debugging
I checked on Google again to see the commit settings of Vertica. I found this post on Stack Overflow. The OP was basically going through the same problem with me. (Why haven’t I found this post earlier!)
The accepted answer described the default setting of Vertica. Apparently, vsql is in transaction mode by default. Thus everything we insert in vsql would not be commited automatically.
We can use
set AUTOCOMMIT on
and
set AUTOCOMMIT on
to configure this setting.
Conclusion
First conclusion: It’s so stupid of me not thinking of COMMITTING A TRANSACTION. Nonetheless, there will not be a next time that I’ll go through this kind of mistake again.
This debugging process cost me about 3 hours. The process was quite frustrating, especially when I found all the data lost when I restarted the database.