Return-Path: X-Original-To: apmail-incubator-oozie-users-archive@minotaur.apache.org Delivered-To: apmail-incubator-oozie-users-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id DBA359029 for ; Thu, 12 Apr 2012 13:15:50 +0000 (UTC) Received: (qmail 34978 invoked by uid 500); 12 Apr 2012 13:15:50 -0000 Delivered-To: apmail-incubator-oozie-users-archive@incubator.apache.org Received: (qmail 34941 invoked by uid 500); 12 Apr 2012 13:15:50 -0000 Mailing-List: contact oozie-users-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: oozie-users@incubator.apache.org Delivered-To: mailing list oozie-users@incubator.apache.org Received: (qmail 34910 invoked by uid 99); 12 Apr 2012 13:15:50 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 12 Apr 2012 13:15:50 +0000 X-ASF-Spam-Status: No, hits=-0.7 required=5.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of tucu@cloudera.com designates 209.85.213.47 as permitted sender) Received: from [209.85.213.47] (HELO mail-yw0-f47.google.com) (209.85.213.47) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 12 Apr 2012 13:15:44 +0000 Received: by yhjj56 with SMTP id j56so1040701yhj.6 for ; Thu, 12 Apr 2012 06:15:23 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :content-type:content-transfer-encoding:x-gm-message-state; bh=xTneQov4mrRm2AlhSUknibaJJ2Zib0v1ppZC2gGMK+s=; b=iiYWU+L/nDwWJA1yDaUzyPKgmd1WWh+j825ijrNUOnrc7Op08HXEBtNo9bmpa5OI++ IwPHI3zakKS4xSqDjbtzMKZsVKFJs5DSQ8Nh2M1Ccnn9+RJttW68DsUmCGxUc0EPXTaf nBESKJNMYCm2g7i6pg4sBNL8zK6LpqdMsbM7HOL1kLIacoVaCcnkX6/EhhyuWpz/fr+O NbSHkRkSLBb1+4eQPYSODQpDtB/IoXLKABPUmv4FjOta4nx/MlvqKpT9XdNrNktSam2t m8Jtuzg0LNh+A3H80Oi0alEgXnkmzkayQ3sZMv5bv32RyBVL3IiELBKGiZuAhHeS+wum 61Qg== Received: by 10.60.28.33 with SMTP id y1mr2855636oeg.62.1334236523048; Thu, 12 Apr 2012 06:15:23 -0700 (PDT) MIME-Version: 1.0 Received: by 10.182.24.227 with HTTP; Thu, 12 Apr 2012 06:14:52 -0700 (PDT) In-Reply-To: <281A750F14A5904F9890751C7452E75FFDDE3B@024-SN1MPN1-092.024d.mgd.msft.net> References: <281A750F14A5904F9890751C7452E75FFCEA54@024-SN1MPN1-092.024d.mgd.msft.net> <1334085853.6300.YahooMailNeo@web161304.mail.bf1.yahoo.com> <281A750F14A5904F9890751C7452E75FFDADD0@024-SN1MPN1-092.024d.mgd.msft.net> <281A750F14A5904F9890751C7452E75FFDDE3B@024-SN1MPN1-092.024d.mgd.msft.net> From: Alejandro Abdelnur Date: Thu, 12 Apr 2012 15:14:52 +0200 Message-ID: Subject: Re: oozie database creation tool To: oozie-users@incubator.apache.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Gm-Message-State: ALoCoQlrn69h4l8LOQS+tS0OocDGqlAesXvxDTj+RlewTNgk1wOP4HLwKHgfMzCHFySXexIS+72e Hi Kamal, Thanks for the details. What version of Oozie are you using? If Oozie 2.x it works only with MySQL = 5.0.x Thxs. Alejandro On Thu, Apr 12, 2012 at 2:54 PM, Kamal Hakim wrote: > 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 tes= ted 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 hav= e the schema so then starts to make th db schema. At that time I get this e= rror and then oozie shuts down. > > org.apache.openjpa.= persistence.PersistenceException: You have an error in your SQL syntax; che= ck the manual that corresponds to your MySQL server version for the right s= yntax to use near 'TYPE =3D 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, las= t_modified_time DATETIME, pending INTEGER, status VARCHAR(255), bean_type V= ARCHAR(31), PRIMARY KEY (bundle_action_id)) TYPE =3D innodb} [code=3D1064, = state=3D42000] > > 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 =3D 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 VARCH= AR(255), critical INTEGER, last_modified_time DATETIME, pending INTEGER, st= atus VARCHAR(255), bean_type VARCHAR(31), PRIMARY KEY (bundle_action_id)) T= YPE =3D innodb} [code=3D1064, state=3D42000] > > org.apache.openjpa.= persistence.PersistenceException: You have an error in your SQL syntax; che= ck the manual that corresponds to your MySQL server version for the right s= yntax to use near 'TYPE =3D innodb' at line 1 {stmnt 1493422662 CREATE TABL= E BUNDLE_ACTIONS (bundle_action_id VARCHAR(255) NOT NULL, bundle_id VARCHAR= (255), coord_id VARCHAR(255), coord_name VARCHAR(255), critical INTEGER, la= st_modified_time DATETIME, pending INTEGER, status VARCHAR(255), bean_type = VARCHAR(31), PRIMARY KEY (bundle_action_id)) TYPE =3D innodb} [code=3D1064,= state=3D42000] > > > So iItried to run that CREATE TABLE command manually and the issue was th= at =A0TYPE =3D innodb is now ENGINE =3D innodb in MySQL 5.5. After I change= d that, the create table command worked! So then I exported the whole oozie= db create script to a file... checnged all =A0TYPE =3D innodb references t= o ENGINE =3D 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 fo= r 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 wit= h 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 wrote= : >> Hi Mohammad, >> >> YES. This is just what I needed. I had to change the SQL sschema file a = bit from TYPE=3Dinnodb to ENGINE=3Dinnodb to work with Mysql 5.5. Once I co= mpiled the ooziedb.sh and the CLI Java source with dependincies, I ran the = ooziedb.sh create with -sqlfile schema.sql parameter. This exported the dat= abase schema info to a file where I could edit it to work with MySQL 5.5 no= w 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#Databa= se_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/upda= tescripts/ >> >> >> Regards, >> Mohammad >> >> >> ----- Original Message ----- >> From: Kamal Hakim >> To: "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 cor= rect JPA setting in oozie-site.xml I have the mysql conenction jar setup co= rrectly. >> >> My only issue if when oozie attempts to create the database schema he us= es an old version of MySQL SQL staements which includes (TYPE=3Dinnodb). 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 ooz= ie 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 in= formation included in this message and any attachments is prohibited. If yo= u have received this communication in error, please notify us by reply e-ma= il and immediately and permanently delete this message and any attachments.= Thank you." >> >> American Express a ajout=E9 le commentaire suivant le Tue Apr 10 2012 08= :53:09 >> >> Ce courrier et toute pi=E8ce jointe qu'il contient sont r=E9serv=E9s au = seul destinataire indiqu=E9 et peuvent renfermer des renseignements confide= ntiels et privil=E9gi=E9s. Si vous n'=EAtes pas le destinataire pr=E9vu, to= ute divulgation, duplication, utilisation ou distribution du courrier ou de= toute pi=E8ce jointe est interdite. Si vous avez re=E7u cette communicatio= n par erreur, veuillez nous en aviser par courrier et d=E9truire imm=E9diat= ement le courrier et les pi=E8ces 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 in= formation included in this message and any attachments is prohibited. If yo= u have received this communication in error, please notify us by reply e-ma= il and immediately and permanently delete this message and any attachments.= Thank you." >> >> American Express a ajout=E9 le commentaire suivant le Wed Apr 11 2012 18= :51:56 >> >> Ce courrier et toute pi=E8ce jointe qu'il contient sont r=E9serv=E9s au = seul destinataire indiqu=E9 et peuvent renfermer des renseignements confide= ntiels et privil=E9gi=E9s. Si vous n'=EAtes pas le destinataire pr=E9vu, to= ute divulgation, duplication, utilisation ou distribution du courrier ou de= toute pi=E8ce jointe est interdite. Si vous avez re=E7u cette communicatio= n par erreur, veuillez nous en aviser par courrier et d=E9truire imm=E9diat= ement le courrier et les pi=E8ces 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 a= nd may contain confidential or privileged information. If you are not the i= ntended recipient, any disclosure, copying, use, or distribution of the inf= ormation included in this message and any attachments is prohibited. If you= have received this communication in error, please notify us by reply e-mai= l and immediately and permanently delete this message and any attachments. = Thank you." > > American Express a ajout=E9 le commentaire suivant le Thu Apr 12 2012 05:= 54:13 > > Ce courrier et toute pi=E8ce jointe qu'il contient sont r=E9serv=E9s au s= eul destinataire indiqu=E9 et peuvent renfermer des renseignements confiden= tiels et privil=E9gi=E9s. Si vous n'=EAtes pas le destinataire pr=E9vu, tou= te divulgation, duplication, utilisation ou distribution du courrier ou de = toute pi=E8ce jointe est interdite. Si vous avez re=E7u cette communication= par erreur, veuillez nous en aviser par courrier et d=E9truire imm=E9diate= ment le courrier et les pi=E8ces jointes. Merci. > > *************************************************************************= ***** > -------------------------------------------------------------------------= ------ > --=20 Alejandro