<img alt="Liferay" src="http://cdn.www.liferay.com/osb-theme/images/custom/heading.png" width="146" height="36" />

Rank: Expert

Posts: 258

Join Date: February 8, 2011

When doing local development I have just been using the built in Hsql database with Liferay 6.0.6 CE. Here are some steps that I take:

1. Working in eclipse testing changes in Liferay 6.0.6 CE
2. Shutdown the Liferay server
3. It is totally stopped (otherwise SQuirreL cant open the files)
4. Launch SQuirreL, can view the database fine
5. Shutdown, and close SQuirreL
6. Start up Liferay, get errors, Liferay doesn't start.
7. Erase Hsql database and start over, sad day.

Any tips on how to get SQuirreL to not break my database?

14:54:22,596 WARN [ThreadPoolAsynchronousRunner:608] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@dc91e2 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
14:54:22,603 WARN [ThreadPoolAsynchronousRunner:624] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@dc91e2 -- APPARENT DEADLOCK!!! Complete Status:
Managed Threads: 3
Active Threads: 3
Active Tasks:
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1acf5f7 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@4845a2 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@d39f3c (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
Pending Tasks:
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@17b51e8
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@13e6577
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1776d65
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1af1934
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@9fe45c
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@14966cc
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1fc1b56
Pool thread stack traces:
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
      java.lang.Thread.sleep(Native Method)
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
      com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,main]
      java.lang.Thread.sleep(Native Method)
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
      com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
      java.lang.Thread.sleep(Native Method)
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
      com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

RE: SQuirreL SQL destroys my database!

February 15, 2012 5:05 PM

Answer

Mika Koivisto

LIFERAY STAFF

Rank: Liferay Legend

Posts: 1002

Join Date: August 7, 2006

HSQL is a inmemory database so you can't have two applications accessing it unless you don't start it in standalone mode. You should rather use MySQL for your development.

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 16, 2012 5:32 AM

Answer

Rank: Expert

Posts: 258

Join Date: February 8, 2011

Mika Koivisto:

HSQL is a inmemory database so you can't have two applications accessing it unless you don't start it in standalone mode. You should rather use MySQL for your development.

You can see above that I specifically state that I stop the Liferay server before starting SQuirreL. I also stop SQuirreL before starting Liferay. At no point do I try to have two applications accessing the Hsql db.

If SQuirreL is not the recommended application to view the database, then what is?

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 16, 2012 10:11 AM

Answer

Ram Manusani

Rank: Regular Member

Posts: 118

Join Date: October 27, 2011

I would suggest you to use Mysql database until and unless you have a requirement not to use or it doesn't support your development for some reason.

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 22, 2012 2:57 PM

Answer

Mika Koivisto

LIFERAY STAFF

Rank: Liferay Legend

Posts: 1002

Join Date: August 7, 2006

It could be that Squirrel uses different version of HSQL libraries than Liferay and that causes some issues but that is just a guess. In any case HSQL nor any In-memory database is not really recommended for anything else except quick throw away demos or tests. I would recommend using MySQL or Postgres for development.

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 22, 2012 6:28 PM

Answer

Rank: Liferay Legend

Posts: 4690

Join Date: March 23, 2010

Just tested it and all seems to be working without any problem. using squirrel sql on hsql and mysql.

2 things to confirm.
1. As Mika said, are you using hsql.jar from lib\ext directory in Liferay's installation
2. As you starting squirrelsql in standalone mode
check jelmer's screenshot http://www.liferay.com/community/forums/-/message_boards/message/12551881

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 23, 2012 7:58 AM

Answer

Rank: Expert

Posts: 258

Join Date: February 8, 2011

My setup did look like Jelmer's screenshot.

I have broken down and installed MySql on my development laptop. One big benefit I see here is being able to edit the database at the same time that Liferay is using it.

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 23, 2012 2:00 PM

Answer

Rank: Liferay Legend

Posts: 4690

Join Date: March 23, 2010

Editing Liferay database directly seems to be inviting disaster.

Well,I know I shouldn't do it, but I do it too on my test system. :-)

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 24, 2012 4:56 AM

Answer

Rank: Expert

Posts: 258

Join Date: February 8, 2011

Mainly I'm creating new tables with service builder and then adding in additional stuff which requires new columns to be added in the database. I was told at the Liferay developer training that the first time the portlet was deployed it would automatically create the tables (and it does) but future updates requires updating the schema manually. Instead of that I just blow away that table and let it recreate, since it is a development environment.

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 24, 2012 6:22 AM

Answer

Rank: Liferay Legend

Posts: 4690

Join Date: March 23, 2010

but future updates requires updating the schema manually. Instead of that I just blow away that table and let it recreate, since it is a development environment.

I thought you were just adding and modifying data. I do what you’re doing. It's a very bad idea to manually change the database schema because it may cause differences between schema.xml and the database schema.

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 24, 2012 1:19 PM

Answer

Rank: Expert

Posts: 258

Join Date: February 8, 2011

How about the scenario where you have an item Foo (created with service builder) and a portlet that displays the Foo items. This is in production and now you need to add a column. Obviously blowing away the table and starting over is no longer an option. You can easily modify the table's schema by looking at the sql file service builder makes. The alternative is to start to use expando for the extra columns. I would think expando is only a good solution if you are altering liferay tables and not one you created with service builder?

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 26, 2012 5:25 AM

Answer

Rank: Liferay Legend

Posts: 4690

Join Date: March 23, 2010

I usually just create an upgrade migration tool such a Liferay is doing with their upgrade. I can test the migration tool on my test server before migration data on the production server and I'll also have a trace of what I did in case something goes wrong later.
I won't modify sql scheme using a tool such as Squrrel SQL on a production server. That's just in my situation. It may be different in yours but you'll on a development server so it probably doesn't matter too much what happens to it.

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 27, 2012 7:23 AM

Answer

Rank: Expert

Posts: 258

Join Date: February 8, 2011

I'd like to do things the proper way of course in production. With other systems we would create the sql update statements, test them in dev, and our database admins would backup and apply the change to the QA and prod environments. The difference is those other systems don't have this upgrade migration tool that you mentioned.

Where can I find out information about the migration tool and creating your own? I assume it allows me to write the sql statements I need to alter tables and then it will "upgrade" it for me.

Mark as an Answer

RE: SQuirreL SQL destroys my database!

February 27, 2012 2:13 PM

Answer

Rank: Liferay Legend

Posts: 4690

Join Date: March 23, 2010

Unfornately, I don't think there's any available tool to create your own from Liferay.com