incubator-oozie-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kamal Hakim" <Kamal.Ha...@aexp.com>
Subject RE: oozie database creation tool
Date Thu, 12 Apr 2012 12:54:04 GMT
Hi Alejandro, 


OK SO when I first setup oozie to run with MySQL 5.5 I updated the oozie-site.xml with all
the correct JPA settings. ( server driver info,server url, un , pw etc) I made sure db schema
name matched my MySQL database and tested the db connection worked. I then started oozie,
then checked the oozie.log. oozie starts and then he checked my MySQL db and finds that I
dont have the schema so then starts to make th db schema. At that time I get this error and
then oozie shuts down.

<openjpa-1.2.1-r752877:753278 nonfatal general error> org.apache.openjpa.persistence.PersistenceException:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'TYPE = innodb' at line 1 {stmnt 165814098 CREATE
TABLE BUNDLE_ACTIONS (bundle_action_id VARCHAR(255) NOT NULL, bundle_id VARCHAR(255), coord_id
VARCHAR(255), coord_name VARCHAR(255), critical INTEGER, last_modified_time DATETIME, pending
INTEGER, status VARCHAR(255), bean_type VARCHAR(31), PRIMARY KEY (bundle_action_id)) TYPE
= innodb} [code=1064, state=42000]

Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for the right syntax
to use near 'TYPE = innodb' at line 1 {stmnt 165814098 CREATE TABLE BUNDLE_ACTIONS (bundle_action_id
VARCHAR(255) NOT NULL, bundle_id VARCHAR(255), coord_id VARCHAR(255), coord_name VARCHAR(255),
critical INTEGER, last_modified_time DATETIME, pending INTEGER, status VARCHAR(255), bean_type
VARCHAR(31), PRIMARY KEY (bundle_action_id)) TYPE = innodb} [code=1064, state=42000]

<openjpa-1.2.1-r752877:753278 nonfatal general error> org.apache.openjpa.persistence.PersistenceException:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'TYPE = innodb' at line 1 {stmnt 1493422662 CREATE
TABLE BUNDLE_ACTIONS (bundle_action_id VARCHAR(255) NOT NULL, bundle_id VARCHAR(255), coord_id
VARCHAR(255), coord_name VARCHAR(255), critical INTEGER, last_modified_time DATETIME, pending
INTEGER, status VARCHAR(255), bean_type VARCHAR(31), PRIMARY KEY (bundle_action_id)) TYPE
= innodb} [code=1064, state=42000]


So iItried to run that CREATE TABLE command manually and the issue was that  TYPE = innodb
is now ENGINE = innodb in MySQL 5.5. After I changed that, the create table command worked!
So then I exported the whole oozie db create script to a file... checnged all  TYPE = innodb
references to ENGINE = innodb and ran the SQL on the OOZIE db manually. That created the schema
correctly!

So somehow the out of the box process was trying to make the db schema for an older versioin
of MySQl in stead of 5.5 version. Now that I think more about it... maybe the MySQL driver
I am using may have something to do with it, Somehow somewhere oozie thought I was using <
5.5 MySQL. I didnt see a db server version JPA setting...








THANKS!!


Regards,
Kamal Hakim
American Express
Hadoop Architecture
Phone: 602-537-9926

________________________________________
From: Alejandro Abdelnur [tucu@cloudera.com]
Sent: Thursday, April 12, 2012 12:42 AM
To: oozie-users@incubator.apache.org
Subject: Re: oozie database creation tool

Hi Kamal,

When implementing ooziedb I've tested with MySQL 5.0.x and 5.5.x and
it just worked.

Can you please explain how you were getting the error, the exact error
you were getting and where do you did the change for this error not to
happen anymore?

Thxs.

Alejandro

On Thu, Apr 12, 2012 at 3:51 AM, Kamal Hakim <Kamal.Hakim@aexp.com> wrote:
> Hi Mohammad,
>
> YES. This is just what I needed. I had to change the SQL sschema file a bit from TYPE=innodb
to ENGINE=innodb to work with Mysql 5.5. Once I compiled the ooziedb.sh and the CLI Java source
with dependincies, I ran the ooziedb.sh create with -sqlfile schema.sql parameter. This exported
the database schema info to a file where I could edit it to work with MySQL 5.5 now all was
well!!
>
> Thanks !!
>
>
> - Kamal Hakim
>
>
> _______________________________________
> From: Mohammad Islam [mislam77@yahoo.com]
> Sent: Tuesday, April 10, 2012 12:24 PM
> To: oozie-users@incubator.apache.org
> Subject: Re: oozie database creation tool
>
> Hi Kamal,
> Sorry for the inconvenience.
> Did you try to create the tables automatically without using the tools? This link could
be helpful. specially the NOTE.
> http://incubator.apache.org/oozie/docs/3.1.3/docs/AG_Install.html#Database_Configuration
>
>
> Anyway, the following links has the ooziedb.sh and other related script.
>
> http://svn.apache.org/repos/asf/incubator/oozie/trunk/tools/src/main/bin/ooziedb.sh
>
> http://svn.apache.org/repos/asf/incubator/oozie/trunk/utils/dbutils/updatescripts/
>
>
> Regards,
> Mohammad
>
>
> ----- Original Message -----
> From: Kamal Hakim <Kamal.Hakim@aexp.com>
> To: "oozie-users@incubator.apache.org" <oozie-users@incubator.apache.org>
> Cc:
> Sent: Tuesday, April 10, 2012 8:52 AM
> Subject: oozie database creation tool
>
> Hi Mohammah and Alejandro,
>
> I am working on setting up oozie to run with Mysql 5.5... I have the correct JPA setting
in oozie-site.xml I have the mysql conenction jar setup correctly.
>
> My only issue if when oozie attempts to create the database schema he uses an old version
of MySQL SQL staements which includes (TYPE=innodb). So I am wondering how I can resolve this
issue please.
>
> Can you tell me where I can find the correct schema for creating the oozie database?
>
> Is there a link to the ooziedb.sh creation tool?
>
>
>
>
> THANKS!!
>
>
> Regards,
> Kamal Hakim
> American Express made the following annotations on Tue Apr 10 2012 08:53:09
>
> ******************************************************************************
>
> "This message and any attachments are solely for the intended recipient and may contain
confidential or privileged information. If you are not the intended recipient, any disclosure,
copying, use, or distribution of the information included in this message and any attachments
is prohibited. If you have received this communication in error, please notify us by reply
e-mail and immediately and permanently delete this message and any attachments. Thank you."
>
> American Express a ajouté le commentaire suivant le Tue Apr 10 2012 08:53:09
>
> Ce courrier et toute pièce jointe qu'il contient sont réservés au seul destinataire
indiqué et peuvent renfermer des renseignements confidentiels et privilégiés. Si vous n'êtes
pas le destinataire prévu, toute divulgation, duplication, utilisation ou distribution du
courrier ou de toute pièce jointe est interdite. Si vous avez reçu cette communication par
erreur, veuillez nous en aviser par courrier et détruire immédiatement le courrier et les
pièces jointes. Merci.
>
> ******************************************************************************
> -------------------------------------------------------------------------------
>
> American Express made the following annotations on Wed Apr 11 2012 18:51:56
>
> ******************************************************************************
>
> "This message and any attachments are solely for the intended recipient and may contain
confidential or privileged information. If you are not the intended recipient, any disclosure,
copying, use, or distribution of the information included in this message and any attachments
is prohibited. If you have received this communication in error, please notify us by reply
e-mail and immediately and permanently delete this message and any attachments. Thank you."
>
> American Express a ajouté le commentaire suivant le Wed Apr 11 2012 18:51:56
>
> Ce courrier et toute pièce jointe qu'il contient sont réservés au seul destinataire
indiqué et peuvent renfermer des renseignements confidentiels et privilégiés. Si vous n'êtes
pas le destinataire prévu, toute divulgation, duplication, utilisation ou distribution du
courrier ou de toute pièce jointe est interdite. Si vous avez reçu cette communication par
erreur, veuillez nous en aviser par courrier et détruire immédiatement le courrier et les
pièces jointes. Merci.
>
> ******************************************************************************
> -------------------------------------------------------------------------------
>

American Express made the following annotations on Thu Apr 12 2012 05:54:13 

****************************************************************************** 

"This message and any attachments are solely for the intended recipient and may contain confidential
or privileged information. If you are not the intended recipient, any disclosure, copying,
use, or distribution of the information included in this message and any attachments is prohibited.
If you have received this communication in error, please notify us by reply e-mail and immediately
and permanently delete this message and any attachments. Thank you." 

American Express a ajouté le commentaire suivant le Thu Apr 12 2012 05:54:13 

Ce courrier et toute pièce jointe qu'il contient sont réservés au seul destinataire indiqué
et peuvent renfermer des renseignements confidentiels et privilégiés. Si vous n'êtes pas
le destinataire prévu, toute divulgation, duplication, utilisation ou distribution du courrier
ou de toute pièce jointe est interdite. Si vous avez reçu cette communication par erreur,
veuillez nous en aviser par courrier et détruire immédiatement le courrier et les pièces
jointes. Merci. 

****************************************************************************** 
-------------------------------------------------------------------------------


Mime
View raw message