Upgrade database from 0.4.0 to 0.6.0

Help
Newtron35
2011-05-12
2013-05-30
  • Newtron35
    Newtron35
    2011-05-12

    We're running 0.5.0 with an 0.4.0 database and looking to update this to 0.6.1.  What's the best way to update the database?  Thanks
    Nate

     
  • Backup your database first.
    Run the install script in  /openbiblio/install
    This will detect the older database layout and update it to the newer version.

     
  • Newtron35
    Newtron35
    2011-05-15

    Yeah - it failed.
    Database Query Error - You've probably found a Bug
    blah blah-

    Query update biblio_copy bc, biblio b set db.create_dt=b.create_dt where b.bibid-bc.bibid failed the DBMS said ths:
    you have an error in your SQL syntax near  'bc, biblio b set bc.create_dt=b.create_dt where b.bibid=bc.bibid ' at line 1

     
  • I'm sorry to read that it failed, Nate.

    If the upgrade was successful, then you should have seen:

    NOTICE:
        * All staff passwords have been reset to be equal to the corresponding usernames.
    

    Does OpenBiblio run normally after the failed upgrade? If it does, what database version is indicated by the footer?
    What is the version number of MySQL? It should be higher than 4.0.12 for new installs and upgrades to OpenBiblio 0.6.x

     
  • Newtron35
    Newtron35
    2011-05-17

    Thanks for the quick reply.  We're running php 5.3 with mysql 5.0.5.  I was able to login once I went into the database and changed the MD5 hash for the passwords.

    It says it's Openbiblio 0.6.1 database version 0.5.2
    Here's the error message from when I tried to access a bibliography (ps- i can access user accounts just fine):

    Query select count (*) as rowcount from biblio left join biblio_copy on biblio.bibid=biblio_copy.bibid where 'biblio_copy','barcode_nmbr' like '%10222%' failed.

    Table 'openbiblio.biblio' doesn't exist.. 

    Then appears the debug backtrace.

     
  • Newtron35
    Newtron35
    2011-05-17

    If I try to go back to ~/openbiblio/install/ to see if I can upgrade to 0.6.0, it says "Database connection good."
    It looks like we need to update database version 0.5.2 to version 0.6.0.

    Click update:  Updating Openbiblio tables,
    Query alter table biblio_copy add create_dt datetime not null after copyid failed.
    Duplicate column name 'create_dt'.
    Then the debug backtrace errors

     
  • Newtron35
    Newtron35
    2011-05-18

    Thanks again for your assistance!

    - forgive me since I never  was a SQL programmer, just a hacker so I'm not quite sure what command you want me to run off line 322?

    Wondering if the offending error is located at: update biblio_copy bc, biblio b.  I can look at the structure of biblio_copy (which my understanding is openbiblio.biblio_copy bc) then it looks at biblio b (again openbiblio.biblio b).  Like you said, there is no table  openbiblio.biblio.

    If I try to run the upgrade again (and deleted out lines 318-320 (due to the recreation of create_dt will cause another error), here's the full error message with the traceback:

    Please give all the information on this page to your support personnel.

    Query update biblio_copy bc, biblio b set bc.create_dt=b.create_dt where b.bibid=bc.bibid failed. The DBMS said this:

    Table 'openbiblio.biblio' doesn't exist
    Debug Backtrace (most recent call first):

    /Library/WebServer/openbiblio/classes/Error.php:100 FatalHandler->printBackTrace()
    /Library/WebServer/openbiblio/classes/Error.php:68 FatalHandler->dbError('update biblio_copy bc, biblio b set bc.create_dt=b.create_dt where b.bibid=bc.bibid ', 'Database query failed', 'Table \'openbiblio.biblio\' doesn\'t exist')
    /Library/WebServer/openbiblio/classes/Query.php:91 Fatal->dbError('update biblio_copy bc, biblio b set bc.create_dt=b.create_dt where b.bibid=bc.bibid ', 'Database query failed', 'Table \'openbiblio.biblio\' doesn\'t exist')
    /Library/WebServer/openbiblio/classes/Query.php:266 Query->_act('update biblio_copy bc, biblio b set bc.create_dt=b.create_dt where b.bibid=bc.bibid ')
    /Library/WebServer/openbiblio/classes/Query.php:271 Query->_exec('update biblio_copy bc, biblio b set bc.create_dt=b.create_dt where b.bibid=bc.bibid ')
    /Library/WebServer/openbiblio/classes/UpgradeQuery.php:323 Query->exec('update biblio_copy bc, biblio b set bc.create_dt=b.create_dt where b.bibid=bc.bibid ')
    /Library/WebServer/openbiblio/classes/UpgradeQuery.php:64 UpgradeQuery->_upgrade052_e('', 'obiblio_upgrade_')
    /Library/WebServer/openbiblio/install/update.php:23 UpgradeQuery->performUpgrade_e()

    Hope this helps….

     
  • You said: there is no table openbiblio.biblio.
    That's strange. The biblio table shouldn't be touched by the upgrade from a 0.4.0 database. You don't see the biblio table with SHOW TABLES ; ?

    Perhaps you can restore just the biblio table from your 0.4.0 database backup and then run these two commands:

    update biblio_copy bc, biblio b 
    set bc.create_dt=b.create_dt 
    where b.bibid=bc.bibid
    

    ;

    update settings 
    set version='0.6.0';
    
     
  • Newtron35
    Newtron35
    2011-05-19

    I spent quite a bit of time delving into the problem.  I reloaded the 0.4.0 from our archives which is slightly different from the one on sourceforge.  I reloaded the backup of the database and installed.  It worked but then came up against a slight problem between incompatibilities between the .sql backup restore command between mysql 4.2 and 5.0.5 that resulted in the error that resulted in not seeing the table biblio.  (basically 4.2 creates a line of --- and interprets it as code instead of the 2 dashed used to comment out.

    So - all is working now. 

    Thanks so much for your help

     


Anonymous


Cancel   Add attachments