Running ADempiere on MySQL
The purpose of this blog entry is to[*]Present a business case for running ADempiere on MySQL
[*]Show how to run ADempiere on MySQL and Glassfish
[*]Inform the ADempiere community that ADempiere on MySQL is soon going to be a possible deployment option
To setthe expectations right, not all the source code required to runAdempiere on MySQL is currently publicly available. I'll update therepository location as soon as the code is made available.
Why MySQL?
Implementing and deploying a new stack or component in it is a costly proposition. A new component increases the costs and complexity of the existing architecture non-linearly. Extra costs must be borne in software licenses, hardware purchases, maintenance, training and wages.Quite unsurprisingly companies want to spread fixed costs of servers and wages across various products. (For those who are wondering whether these really are fixedcosts, welcome to SMB sector!) These companies therefore look for solutions that fit in their existing stack.
MySQL, the internet db, needs no introduction. A large number of companies have MySQL deployments. When these companies start looking for an Open Source ERP application, they obviously would want to go with an ERP that has the backing of a strong community and is based on the same stack as they are currently using. Whereas Adempiere definitely meets the first criterion, the second one may not always hold true.This blog aims at introducing one more option while deploying Adempiere .
Adempiere on MySQL - a first look
Once you get Adempiere to run on MySQL, there is no way to tell any difference between Adempiere running on MySQL and Adempiere running on one of the other supported database. Here is how the installation screens look like -
Installation Screen -
http://blogs.oracle.com/praneet/resource/adempiereMysql/11.JPG
This is how theAdempiere screens look like - of course the user does not get to see anychanges at the application level. It's only on the Adempiere Connectionwindow that the MySQL connection needs to be mentioned.
http://blogs.oracle.com/praneet/resource/adempiereMysql/1.png
http://blogs.oracle.com/praneet/resource/adempiereMysql/2.png
http://blogs.oracle.com/praneet/resource/adempiereMysql/4.png
http://blogs.oracle.com/praneet/resource/adempiereMysql/5.png
http://blogs.oracle.com/praneet/resource/adempiereMysql/6.png
http://blogs.oracle.com/praneet/resource/adempiereMysql/7.png
Steps for Running Adempiere on MySQL
For running Adempiere on MySQL and Glassfish, you need to
[*]Install and prepare MySQL
[*]Check out the mysql branch source code of Adempiere. You need to build this source code to get a MySQL ready installation file.
Ifyou are using Netbeans, the good news is that ADempiere is now aNetbeans project. This means that you can check out the code and justpress the build button to get the installer file.
Note - the code is not yet checked in. As soon as the code becomes ready, I'll update this entry with the repository location.
[*]Install Glassfish. If you are running OpenSolaris, you may want to glance over this document.
As notedearlier, the source code location is not available now. So the secondstep will be outlined and details will be updated a bit later.
Preparing the DB
[*]Obtain MySQL community version from here.
[*]Install MySQL,preferably as a service or as daemon. Do remember the root password, itwill be used time and again. In case you mess up with the password, theclean up act is quite easy. Refer this link.
[*]Start MySQL in a way appropriate for your OS.
[*]Create a database called adempiere
mysql> create database adempiere;
[*]Create a user adempiere with password adempiere
mysql> create USER 'adempiere'@'localhost'; mysql>
GRANT ALL PRIVILEGES ON \*.\*to'adempiere'@'localhost';mysql> update user set password=PASSWORD("adempiere") where User='adempiere';
[*]Verify that the user is created and has the appropriate priviliges.
mysql> select User, Host, Password from mysql.user;
The output looks something like -
adempiere | localhost | \*B3829BD27D70B4AE67C3B665A2CA5DE36F37DB6F
The key point to note here is that for MySQL 'adempiere'@'localhost' is different from 'adempiere'@'\*'
[*]Import the Adempiere mysql dump. This dump location is not finalized yet, I'll update this point.
Changing ADempiere source code
Well, I am notgoing to talk of the numerous Java source code changes (mostly in queryformation) that have to go into the source code. Instead, I am going tocheck-in the modified source code in branch in the main repository andlet you know the location. I'll provide the repository location as soonas the code is committed. There are some formalities that are delayingthe code check-in.
But thebottom line is on building the source code you will get a installer zipand tar.gz file which you can use to install MySQL enabled Adempiere.
Verifying that it works
I've tested the Adempiere on MySQL and Glassfish installation on the following platforms and got satisfactory results -
Platform Name
MySQL Version
Glassfish Version
Opensolaris 2008.05, 2008.11
5.1 and 6.0
V2.1
Windows XP, 2003 Server
5.1 and 6.0
V2.1
Ubuntu 8.04 64 bit
5.1 and 6.0
V2.1
Solaris 10U6
5.1
V2ur2
Solaris 10U5 on T2000 (Sparc Machine)
5.1
V2ur2
TBD: Update the Adempiere source code link
Category: Sun
Tags: adempiere integration mysql
Permanent link to this entry
« Beginner's Guide to... | Main | SugarCRM and Sun... »
Comments:
Hi, FYI here is the discussion thread about this post on ADempiere forums:
https://sourceforge.net/forum/message.php?msg_id=7477792
Best regards,
Teo Sarca
Posted by Teo Sarca on July 03, 2009 at 04:24 PM TLT #
Hi!
I think it is a good work and a good news. I'm interresting of yourwork.I need that you explain me how you have procced. I'm also workingforthis issue, so I will very happy if you tell me your method. Werecan i have your source code? is there some modifications done in thesource code?
Fabrice.
Posted by Fabrice KAMENI on July 10, 2009 at 03:11 PM TLT #
This is incredible! Thanks for this post. I did notice that you'reusing 3.5.3a, I'm assuming this is essential since it's the first onenot requiring PL/Java
Posted by Gabe on July 31, 2009 at 10:34 PM TLT #
Hi Praneet,
I just stumbled into this. Will await your launching of the branch forverification. This can be one of the biggest news to the bazaar.
red1
Posted by Redhuan D. Oon on October 18, 2009 at 11:10 AM TLT #
good morning,
I am interested in the Adempiere with mysql database.
how to installation the mysql database in Adempiere and netbeans?
thanks
Posted by bagus sugiono on November 25, 2009 at 08:30 AM TLT #
Hi, that's great what you're doing here. I tried to follow yoursteps. And I got the following problem. I downloaded your mysql branchfrom sourceforge compiled it, that's everthing ok. But when I'm tryingto run the silentsetup on my ubuntu server, it brakes with a comment,that no mysql drivers are found...
I don't know if it's an error in the code, or what I believe more is,that my AdempiereEnv.properties-File perhaps isn't well configured...
Do you have any> Thank in advance for your help,
Rgds
Posted by maninthesun on December 03, 2009 at 03:48 AM TLT #
Hi,
I've downloaded sourcecode from adempiere trunk branch mysql.
I built it using RUN_build.sh command. It's Successful.
But on installation i found this exception.
12:43:26.644 Config.test: OK: Database Port = 3306
Exception in thread "Thread-2" java.lang.NoClassDefFoundError: org/gjt/mm/mysql/Driver
at org.compiere.db.DB_MySQL.getDriver(DB_MySQL.java:127)
at org.compiere.db.DB_MySQL.getDriverConnection(DB_MySQL.java:610)
at org.compiere.install.ConfigMySQL.testJDBC(ConfigMySQL.java:161)
at org.compiere.install.ConfigMySQL.test(ConfigMySQL.java:106)
at org.compiere.install.ConfigurationData.testDatabase(ConfigurationData.java:1388)
at org.compiere.install.ConfigurationData.test(ConfigurationData.java:387)
at org.compiere.install.ConfigurationPanel.test(ConfigurationPanel.java:619)
at org.compiere.install.ConfigurationPanel.access$400(ConfigurationPanel.java:57)
at org.compiere.install.ConfigurationPanel$1.construct(ConfigurationPanel.java:578)
at org.compiere.apps.SwingWorker$2.run(SwingWorker.java:68)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.ClassNotFoundException: org.gjt.mm.mysql.Driver
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
... 11 more
Can you tell me where to find the source code of> Posted by Adi on December 03, 2009 at 12:47 PM TLT #
Ok,
so there were quite a few comments while I was away. I'll start answering them in chronological order.
Adi,
you need to get the mysql jdbc driver and place it in the lib directory of your app server. can get the jar from - http://dev.mysql.com/downloads/connector/j/5.1.html
this is in fact a surprising revelation, I thought I checked this one too. May be you are using JBoss.
Posted by Praneet Tiwari on December 03, 2009 at 01:05 PM TLT #
Hi maninthesun,
It seems you are facing the same issue what Adi is facing. I think Imissed that jar file. Please get the mysql jdbc driver from http://dev.mysql.com/downloads/connector/j/5.1.html and place it in the lib folder. That should do the trick.
Posted by Praneet Tiwari on December 03, 2009 at 01:08 PM TLT #
Hi Praneet,
It was resolved by adding this line in the file RUN_setup.sh.
In line 21, before is like this:
CP=lib/CInstall.jar:lib/Adempiere.jar:lib/CCTools.jar:lib/oracle.jar:lib/derby.jar:lib/fyracle.jar:lib/jboss.jar:lib/postgresql.jar:
and i change it to like this:
CP=lib/CInstall.jar:lib/Adempiere.jar:lib/CCTools.jar:lib/oracle.jar:lib/derby.jar:lib/fyracle.jar:lib/jboss.jar:lib/postgresql.jar:lib/mysql-connector-java-3.0.17-ga-bin.jar:
Next problem is.....
Can you share to us how you manage to dump adempiere database?
Posted by Adi on December 03, 2009 at 02:45 PM TLT #
Hmm...
Now, I clearly remember doing that change myself. Will check repository and get back.
Managing the dump is where the devil lies. You should be seeing aschema folder in the repository. It's trunk's sibling. You would find a.sql file. This sql file creates all the DDLs and DMLs required to runAdempiere on mysql.
please note that some functions (~20) are not ported yet. expect sometroubles ahead, I plan to revisit the issue of these 20 erring functionsin another ten days.
Posted by Praneet Tiwari on December 03, 2009 at 03:19 PM TLT #
i have tried to run AdempiereDBSchema.sql. it didn't work. i think it's
because of the foreign key refers to a table that hasn't been created
yet.
maybe you can do "adempiere old ways". they made a dump file and put it
on Adempiere/data directory.
Posted by Adi on December 03, 2009 at 04:11 PM TLT #
Thank you for your help regarding the driver, that was exactly the point.
I'm> 1. When I'm running the silentsetup.sh it stops with the belowmentioned error, but, I configured the AdempiereEnv.properties file withuser=adempiere?
21:49:20.573 Config.test: OK: Database Port = 3306
===========> Config.testJDBC: java.sql.SQLException: Invalidauthorization specification message from server: "Access denied for user'root'@'localhost' (using password: YES)"
===========> Config.testJDBC: java.sql.SQLException: Invalidauthorization specification message from server: "Access denied for user'root'@'localhost' (using password: YES)"
-----------> ConfigurationData.test: Error connecting: jdbc:mysql://localhost:3306/adempiere - root/adempiere
2. This problem concerns the mysql dump. When I'm trying to importthe dump file it doesn't start, and comes with the errno 150 =>foreign key problem. Ok, I thought, i'll import the DDL Files but alsohere mySqlCreates.sql fails with error in line 8171 saying that the keyis too long...
I'm using mysql 5.1.37. In your Dump File Version 5.2 is mentioned? Didn't found anywhere... :)
Are the 20 buggy function you told errors on importing the dump file?
Thank you very much for your help.
Rgds
Posted by maninthesun on December 04, 2009 at 04:03 AM TLT #
页:
[1]