Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 613B01788B for ; Sat, 7 Mar 2015 02:39:10 +0000 (UTC) Received: (qmail 80018 invoked by uid 500); 7 Mar 2015 02:39:02 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 79943 invoked by uid 500); 7 Mar 2015 02:39:02 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 79933 invoked by uid 99); 7 Mar 2015 02:39:02 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 07 Mar 2015 02:39:02 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of openkbinfo@gmail.com designates 209.85.223.170 as permitted sender) Received: from [209.85.223.170] (HELO mail-ie0-f170.google.com) (209.85.223.170) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 07 Mar 2015 02:38:58 +0000 Received: by iecrp18 with SMTP id rp18so22467621iec.10 for ; Fri, 06 Mar 2015 18:37:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=/iMF7t98jZD3m6pZHLBHW5FgAO24VMT/y+QaOPWTg3E=; b=GlRrKL54A1/zAnrALdR5egKI04HLuuHfB0gU+G+GGehN06TyMD3lGJeol+sLjtgTC9 1rN+Z7BqAZqpMWAtzECWVwirJmm33HmLCZj1SW+qzi6O0TAtYozlzcQD+twEfJp9HdFv FyhzEYDw+svmHKnxbS3sviE9DQOILTR2Z0xa6Fl0Nou8SLS6GkKXksvD3PBJ3ajMxBb5 lSSpz/Dx2tAo78yTrr3h7CupcOGkB+gIhrdEoJMvhxYelh7zQwSoMA1b4kkG2LPFKXEk QOUTdgPN/3dExZLy06f5VVqSFra70Y4BhnYEOH1McgfGmLwb/CNguhlensjSGVx7KkTe H8EA== MIME-Version: 1.0 X-Received: by 10.107.12.150 with SMTP id 22mr21541548iom.71.1425695872784; Fri, 06 Mar 2015 18:37:52 -0800 (PST) Received: by 10.36.82.205 with HTTP; Fri, 6 Mar 2015 18:37:52 -0800 (PST) In-Reply-To: References: Date: Fri, 6 Mar 2015 18:37:52 -0800 Message-ID: Subject: Re: Why hive 0.13 will initialize derby database if the metastore parameters are not set in hive-site.xml? From: Jim Green To: user@hive.apache.org Content-Type: multipart/alternative; boundary=001a113ee2f20e4c3d0510a9b052 X-Virus-Checked: Checked by ClamAV on apache.org --001a113ee2f20e4c3d0510a9b052 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Edward, No matter if I set hive.stats.autogather=3Dfalse in hive-site.xml or .hiver= c, when I run =E2=80=9Chive=E2=80=9D, the derby database is still created. # hive hive> set hive.stats.autogather ; hive.stats.autogather=3Dfalse # ls -altr|grep metastore_db drwxr-xr-x 5 root root 4096 Mar 6 18:34 metastore_db Thanks. On Fri, Mar 6, 2015 at 6:17 PM, Edward Capriolo wrote: > Make sure hive autogather stats is false . Or aetup the stats db > > > On Friday, March 6, 2015, Jim Green wrote: > >> Hi Team, >> >> Starting from hive 0.13, if the metastore parameters are not set in >> hive-site.xml, but we set in .hiverc, hive will try to initialize derby >> database in current working directory. >> This behavior did not exist in hive 0.12. >> Is it a known bug? or behavior change? >> >> I have the repro as below: >> >> *Env:* >> Hive 0.13 >> MySQL as backend metastore database. >> No hive metastore service. >> >> *Case 1:* >> .hiverc is not used and hive-site.xml has below 4 parameters: >> >> javax.jdo.option.ConnectionURL >> jdbc:mysql://localhost/metastore >> the URL of the MySQL database >> >> >> >> javax.jdo.option.ConnectionDriverName >> com.mysql.jdbc.Driver >> >> >> >> javax.jdo.option.ConnectionUserName >> hive >> >> >> >> javax.jdo.option.ConnectionPassword >> mypassword >> >> >> In this case, if we run "hive" and it works fine and connects to mysql a= s >> the backend metastore database. >> It will NOT initialize the derby database in current directory. >> >> *Case 2:* >> hive-site.xml is empty and .hiverc has below 3 parameters: >> [root@~]# cat .hiverc >> set javax.jdo.option.ConnectionURL=3Djdbc:mysql://localhost/metastore; >> set javax.jdo.option.ConnectionDriverName=3Dcom.mysql.jdbc.Driver; >> set javax.jdo.option.ConnectionUserName=3Dhive; >> set javax.jdo.option.ConnectionPassword=3Dmypassword; >> >> In this case, if we run "hive" and it also works fine and connects to >> mysql as the backend metastore database. >> However it initialized the derby database in current working directory >> where you run "hive" command: >> >> drwxr-xr-x 5 root root 4096 Mar 6 12:18 metastore_db >> -rw-r--r-- 1 root root 70754 Mar 6 12:18 derby.log >> >> If we open another putty session and run "hive" in the same directory, i= t >> will fail with below error: >> "Caused by: ERROR XSDB6: Another instance of Derby may have already >> booted the database /xxx/xxx/xxx/metastore_db." >> This is because derby database only allows one connection. >> >> We do not understand why after we moved the 4 parameters from >> hive-site.xml to .hiverc, hive will try to initialize the derby database= ? >> >> >> -- >> Thanks, >> www.openkb.info >> (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool) >> > > > -- > Sorry this was sent from mobile. Will do less grammar and spell check tha= n > usual. > --=20 Thanks, www.openkb.info (Open KnowledgeBase for Hadoop/Database/OS/Network/Tool) --001a113ee2f20e4c3d0510a9b052 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Edward,

No matter if I set=C2=A0hive= .stats.autogather=3Dfalse in hive-site.xml or .hiverc, when I run =E2=80=9C= hive=E2=80=9D, the derby database is still created.

# hive
hive> set hive.stats.autogather ;
hive.stats.autogather=3Dfalse

# ls -altr|grep= metastore_db
drwxr-xr-x =C2=A0 5 = root root =C2=A0 =C2=A0 4096 Mar =C2=A06 18:34 metastore_db

Thanks.

On Fri, Mar 6, 2015 at 6:17 PM, Edward Capriolo <edlinuxguru@gmail.com> wrote:
Make sure hive autogather stats is false . Or aetup the stats db


On Friday, March 6, 2015, Jim G= reen <openkbin= fo@gmail.com> wrote:
Hi Team,

Starting from hive 0.13, if the metastore p= arameters are not set in hive-site.xml, but we set in .hiverc, hive will tr= y to initialize derby database in current working directory.
This= behavior did not exist in hive 0.12.
Is it a known bug? or behav= ior change?

I have the repro as below:
<= br>
Env:
Hive 0.13=C2=A0
MySQL as= backend metastore database.
No hive metastore service.

Case 1:
.hiverc is not used and hive-site.= xml has below 4 parameters:
<property&= gt;
=C2=A0 <name>javax.jdo.o= ption.ConnectionURL</name>
= =C2=A0 <value>jdbc:mysql://localhost/metastore</value>
=C2=A0 <description>the URL of the M= ySQL database</description>
= </property>

<property>
=C2=A0 <name>javax.jdo.option.ConnectionDriverName</n= ame>
=C2=A0 <value>com.my= sql.jdbc.Driver</value>
<= /property>

<property>
=C2=A0 <name>javax.jdo.option.ConnectionUserName</name>= ;
=C2=A0 <value>hive</val= ue>
</property>

<property>
=C2=A0 <nam= e>javax.jdo.option.ConnectionPassword</name>
=C2=A0 <value>mypassword</value>
</property>

In this case, if we run "hive" and it works fine and connec= ts to mysql as the backend metastore database.
It will NOT initia= lize the derby database in current directory.

C= ase 2:
hive-site.xml is empty and .hiverc has below 3 paramet= ers:
[root@~]# cat .hiverc
set javax.jdo.option.ConnectionURL=3Djdbc:mysql:= //localhost/metastore;
set javax.j= do.option.ConnectionDriverName=3Dcom.mysql.jdbc.Driver;
set javax.jdo.option.ConnectionUserName=3Dhive;
set javax.jdo.option.ConnectionPassword= =3Dmypassword;

In this case, if we run &quo= t;hive" and it also works fine and connects to mysql as the backend me= tastore database.
However it initialized the derby database in cu= rrent working directory where you run "hive" command:
<= br>
drwxr-xr-x =C2=A0 5 root root =C2=A0 = 4096 Mar =C2=A06 12:18 metastore_db
-rw-r--r-- =C2=A0 1 root root =C2=A070754 Mar =C2=A06 12:18 derby.log

If we open another putty session and run "= ;hive" in the same directory, it will fail with below error:
"Caused by: ERROR XSDB6: Another instance of = Derby may have already booted the database /xxx/xxx/xxx/metastore_db."=
This is because derby database only allows one connection= .

We do not understand why after we moved the 4 pa= rameters from hive-site.xml to .hiverc, hive will try to initialize the der= by database?


--
Thanks,
(Open KnowledgeBase for Hadoop/Database/OS/Netw= ork/Tool)


--
Sorry this was sent from mobile. Will do less grammar and spell= check than usual.



--
Thanks,(Open KnowledgeBase for Hadoop/Database/OS/Network/Tool)
--001a113ee2f20e4c3d0510a9b052--