The purpose of this blog entry is to

  1. Present a business case for running ADempiere on MySQL
  2. Show how to run ADempiere on MySQL and Glassfish
  3. Inform the ADempiere community that ADempiere on MySQL is soon going to be a possible deployment option
To set the expectations right, not all the source code required to run Adempiere on MySQL is currently publicly available. I'll update the repository 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 fixed costs, 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 -

This is how the Adempiere screens look like - of course the user does not get to see any changes at the application level. It's only on the Adempiere Connection window that the MySQL connection needs to be mentioned.

Adempiere 1st screen

 

 

Steps for Running Adempiere on MySQL

For running Adempiere on MySQL and Glassfish, you need to  

  1. Install and prepare MySQL
  2. Check out the mysql branch source code of Adempiere. You need to build this source code to get a MySQL ready installation file.
    If you are using Netbeans, the good news is that ADempiere is now a Netbeans project. This means that you can check out the code and just press 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.
  3. Install Glassfish. If you are running OpenSolaris, you may want to glance over this document.
As noted earlier, the source code location is not available now. So the second step will be outlined and details will be updated a bit later.

Preparing the DB

 

  1. Obtain MySQL community version from here.
  2. Install MySQL, preferably as a service or as daemon. Do remember the root password, it will be used time and again. In case you mess up with the password, the clean up act is quite easy. Refer this link.
  3. Start MySQL in a way appropriate for your OS.  
  4. Create a database called adempiere
    mysql> create database adempiere;
  5. Create a user adempiere with password adempiere
     
  6. 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'@'\*' 
  7. Import the Adempiere mysql dump. This dump location is not finalized yet, I'll update this point.
 
  
 
 
  
Changing ADempiere source code
 
  
Well, I am not going to talk of the numerous Java source code changes (mostly in query formation) that have to go into the source code. Instead, I am going to check-in the modified source code in branch in the main repository and let you know the location. I'll provide the repository location as soon as the code is committed. There are some formalities that are delaying the code check-in.
 
  
But the bottom line is on building the source code you will get a installer zip and 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
 
  
 
   
   
  
 
   

     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 your work.I need that you explain me how you have procced. I'm also working for this issue, so I will very happy if you tell me your method. Were can i have your source code? is there some modifications done in the source 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're using 3.5.3a, I'm assuming this is essential since it's the first one not 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 for verification. 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 your steps. And I got the following problem. I downloaded your mysql branch from sourceforge compiled it, that's everthing ok. But when I'm trying to 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 Idea?
 
    
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 [11]
 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 class: org.gjt.mm.mysql.Driver. Because last time i checked on my check out result, it was only a class file and no java file. Or is it proprietary of yours?
 
    
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 I missed 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 a schema 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 run Adempiere on mysql.
 please note that some functions (~20) are not ported yet. expect some troubles ahead, I plan to revisit the issue of these 20 erring functions in 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 faced to 2 new problems...
 
    
1. When I'm running the silentsetup.sh it stops with the below mentioned error, but, I configured the AdempiereEnv.properties file with user=adempiere?
 
    
21:49:20.573 Config.test: OK: Database Port = 3306
 ===========> Config.testJDBC: java.sql.SQLException: Invalid authorization specification message from server: "Access denied for user 'root'@'localhost' (using password: YES)"
 
    
===========> Config.testJDBC: java.sql.SQLException: Invalid authorization 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 import the 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 also here mySqlCreates.sql fails with error in line 8171 saying that the key is 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 #