Difference between revisions of "CCDB Schema Changes in Version 1.00"

From GlueXWiki
Jump to: navigation, search
(copy of D.'s email)
 
 
Line 1: Line 1:
 +
From Dmitry Romanov.
  
−
Hi!
+
This change was [https://mailman.jlab.org/pipermail/halld-offline/2014-January/001432.html announced] in January 2014.
−
How are you?
+
−
I was in countryside with my parents so sorry for the late responses.
+
  
−
Yes. There are changes in schema from 0.9 to 1.00 (db version 3 to 4) . It was to add isDeleted flag to users, so we can recreate users without loosing their logs. Thus, 0.9 works with database from 1.0. But 1.0 doesn't work with 0.9 database. Because of this CCDB checks version. Our database is patched (to version 4) to be compatible with 1.0. I have checked it.
+
There were changes in the CCDB database schema in going from from 0.9 to 1.00. These correspond to the internal database version numbers changing from 3 to 4. The change was to add the "isDeleted" flag to user records to support the case where a user is deleted for authentication purposes, but needs to be retained in the database for history purposes.
  
−
How to patch it?
+
* CCDB 0.9 works with databases with the 1.0 schema.
−
Verbose letters are in [halld_offline]
+
* CCDB 1.0 doesn't work with databases with the 0.9 schema.
−
CCDB database update and columnType is NULL fix
+
  
−
Concise version is:
+
Because of this CCDB now checks the version of the schema. The central MySQL database uses version 4 and is thus compatible with CCDB 1.0.
−
There are two sql patches to update sqlite and MySQL added:
+
−
$CCDB_HOME/sql/update_0.09_1.00.mysql.sql
+
−
$CCDB_HOME/sql/update_0.09_1.00.sqlite.sql
+
  
−
To patch a database run it like:
+
Old versions of databases can be upgraded. There are two SQL patches to update SQLite and MySQL:
−
sqlite3 <your_db>.sqlite < $CCDB_HOME/sql/update_0.09_1.00.sqlite.sql
+
  
−
What time for meeting is appropriate for you?
+
* $CCDB_HOME/sql/update_0.09_1.00.mysql.sql
 +
* $CCDB_HOME/sql/update_0.09_1.00.sqlite.sql
  
−
Dmitry
+
To upgrade an old SQLlite database,
  
−
 
+
  sqlite3 <your_db>.sqlite < $CCDB_HOME/sql/update_0.09_1.00.sqlite.sql
−
On Tue, Jun 10, 2014 at 7:08 PM, Mark M. Ito <marki@jlab.org> wrote:
+
−
 
+
−
    Dmitry,
+
−
 
+
−
    Sean raises a question about the CCDB. Is this related to a schema change. I confess that I do not understand the v3 vs. v4 thing.
+
−
 
+
−
      -- Mark
+
−
 
+
−
    -------- Original Message --------
+
−
    Subject: Re: mySQL CCDB down?
+
−
    Date: Mon, 9 Jun 2014 13:54:57 -0500
+
−
    From: Sean Dobbs <s-dobbs@northwestern.edu>
+
−
    To: Mark Ito <marki@jlab.org>
+
−
 
+
−
 
+
−
    Hi Mark,
+
−
 
+
−
    Just to follow up on this, it looks like there was a change to the calibration databases in the past few weeks that requires CCDB version >=1.0.  At least that's my anecdotal experience in trying to connect to the MySQL and recent sqlite DBs. Do you know if anything was changed?
+
−
 
+
−
    ---Sean
+
−
 
+
−
 
+
−
    On Fri, Jun 6, 2014 at 3:10 PM, Sean Dobbs <s-dobbs@northwestern.edu> wrote:
+
−
 
+
−
        Mark,
+
−
 
+
−
        Sorry for not following up - I updated to the latest version of CCDB and everything works fine now.
+
−
        Though I may have found another minor tracking bug for Simon to hunt down.
+
−
 
+
−
        ---Sean
+
−
 
+
−
 
+
−
        On Fri, Jun 6, 2014 at 3:07 PM, Mark Ito <marki@jlab.org> wrote:
+
−
 
+
−
            Sean,
+
−
 
+
−
            Try the following from the command line:
+
−
 
+
−
              mysql -h hallddb.jlab.org -u ccdb_user ccdb
+
−
 
+
−
            This just worked for me from home.
+
−
 
+
−
            Also I was able to connect via
+
−
 
+
−
              ccdb -i
+
−
 
+
−
            from the command line.
+
−
 
+
−
              -- Mark
+
−
 
+
−
 
+
−
            On 06/06/2014 01:52 PM, Sean Dobbs wrote:
+
−
>            Yeah, still having trouble. Maybe it's a problem with the version of CCDB I'm using - let me look into that and I'll let you know.
+
−
>
+
−
>
+
−
>            On Fri, Jun 6, 2014 at 12:29 PM, Mark M. Ito <marki@jlab.org> wrote:
+
−
>
+
−
>                Looks like it is up, both inside and outside the lab. Are you still
+
−
>                having problems?
+
−
>
+
−
>                On 06/06/2014 12:34 PM, Sean Dobbs wrote:
+
−
>                > Hi Mark,
+
−
>                >
+
−
>                > I've been trying to debug some problems running sim-recon at NU, and
+
−
>                > noticed that I'm not able to connect to the MySQL version of the CCDB.
+
−
>                >  I seem to remember you mentioning that it might be down for awhile as
+
−
>                > it was being moved between servers or something like that.  Is that
+
−
>                > what's going on right now?
+
−
>                >
+
−
>                > Cheers,
+
−
>                > Sean
+
−
>                >
+
−
>                > --
+
−
>                > Sean Dobbs
+
−
>                > Department of Physics & Astronomy
+
−
>                > Northwestern University
+
−
>                > phone: 847-467-2826
+
−
>
+
−
>
+
−
>
+
−
>
+
−
>            --
+
−
>            Sean Dobbs
+
−
>            Department of Physics & Astronomy
+
−
>            Northwestern University
+
−
>            phone: 847-467-2826
+
−
 
+
−
            --
+
−
            Mark M. Ito, Jefferson Lab, marki@jlab.org, (757)269-5295
+
−
 
+
−
 
+
−
 
+
−
 
+
−
        --
+
−
        Sean Dobbs
+
−
        Department of Physics & Astronomy
+
−
        Northwestern University
+
−
        phone: 847-467-2826
+
−
 
+
−
 
+
−
 
+
−
 
+
−
    --
+
−
    Sean Dobbs
+
−
    Department of Physics & Astronomy
+
−
    Northwestern University
+
−
    phone: 847-467-2826
+

Latest revision as of 20:41, 10 June 2014

From Dmitry Romanov.

This change was announced in January 2014.

There were changes in the CCDB database schema in going from from 0.9 to 1.00. These correspond to the internal database version numbers changing from 3 to 4. The change was to add the "isDeleted" flag to user records to support the case where a user is deleted for authentication purposes, but needs to be retained in the database for history purposes.

  • CCDB 0.9 works with databases with the 1.0 schema.
  • CCDB 1.0 doesn't work with databases with the 0.9 schema.

Because of this CCDB now checks the version of the schema. The central MySQL database uses version 4 and is thus compatible with CCDB 1.0.

Old versions of databases can be upgraded. There are two SQL patches to update SQLite and MySQL:

  • $CCDB_HOME/sql/update_0.09_1.00.mysql.sql
  • $CCDB_HOME/sql/update_0.09_1.00.sqlite.sql

To upgrade an old SQLlite database, 

 sqlite3 <your_db>.sqlite < $CCDB_HOME/sql/update_0.09_1.00.sqlite.sql
Retrieved from "https://halldweb1.jlab.org/wiki/index.php?title=CCDB_Schema_Changes_in_Version_1.00&oldid=57843"