Your MySQL Database: The Undiscovered Country - PowerPoint PPT Presentation

your mysql database the undiscovered country n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Your MySQL Database: The Undiscovered Country PowerPoint Presentation
Download Presentation
Your MySQL Database: The Undiscovered Country

play fullscreen
1 / 41
Your MySQL Database: The Undiscovered Country
118 Views
Download Presentation
mandy
Download Presentation

Your MySQL Database: The Undiscovered Country

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Your MySQL Database: TheUndiscoveredCountry

  2. Agenda • Howtoget in? • Howto find yourway? • What'spossiblewithoutlosingsupport? • What's not possiblewithoutlosingsupport?

  3. Agenda • Howtoget in? • Howto find yourway? • What'spossiblewithoutlosingsupport? • What's not ... andyetsometimesneedstobedone? • A closedopen interface? • Two Enhancementrequests

  4. 1Howtoget in?

  5. Interactively sfxlcl41> mysql –usfxlcl41 –p Enter password: Welcome to the MySQL monitor. […] 16:35 (none)> • The MySQL userof an SFX instanceusuallybearsthe same nameasthecorresponding UNIX user. • The respectiveMySQL passwordisusually just settotheusernamebythe SFX installer. • Preferably, youshould log in asuserof a local SFX instance (e.g. as "sfxlcl41" like in thescreenshotabove)!

  6. Interactively(continued) 16:35 (none)> use sfxlcl41; Reading table information […] Database changed 16:37 sfxlcl41> • After successfulloginyouneedtochoose a databasetoworkwith. • In caseofdoubt: database = user = password • All usershavereadpermission on all databases. • Write permissionisgrantedto sfxglb41 on all databases,andtoeachoftheotherusers on "their" owndatabaseonly.

  7. Wrapper script MySQL userunknown, passwordforgotten? – Don'tworry! Module Manager::Connectionis all youreallyneed: • Buildyour SQL statement, • insertitinto a Perl wrapper scriptthatestablishes a databaseconnectionbyusingmoduleManager::Connection, and • runthe wrapper script. (Redirect theoutputinto a file in caseyouexpect a longerlistofresults.)

  8. Would Perl::DBI workaswell? In principleyes, butestablishingthedatabaseconnectionbyyourselfmeans • yourscriptcontainsthe MySQL userasplaintext • yourscriptcontainsthe MySQL passwordasplaintext • characterencodingusuallyneedstobesetexplicitelybysubmitting a SET NAMES 'utf8';asfirstcommand All thisistakencareofbyManager::Connection!

  9. 2Howto find yourway?

  10. show tables lists all tablesofthecurrentlychosendatabase: 16:37 sfxlcl41> showtables; +------------------------------------------+ | Tables_in_sfxlcl41 | +------------------------------------------+ : : | LCL_OBJECT_PORTFOLIO_INVENTORY | | LCL_OBJECT_PORTFOLIO_LINKING_INFO | | LCL_SERVICE_INVENTORY | | LCL_SERVICE_LINKING_INFO | : : +------------------------------------------+ 48 rows in set (0.00 sec)

  11. showtablesas sfxglb41 yields a completely different listoftables: 16:39 sfxglb41> showtables; +------------------------------------------+ | Tables_in_sfxglb41 | +------------------------------------------+ : : | KB_OBJECT_PORTFOLIOS | : : | KB_TARGET_SERVICES | : : +------------------------------------------+ 78 rows in set (0.01 sec)

  12. Database architecture in SFX 3 sfxglb3 sfxlcl3

  13. SFX 4: Lessismore! • Key changeofarchitecture: databaseof a localinstancenolongercontains afullcopyofthe global KB, ... • ... but onlytheactivationstatusandanylocalfields (e.g. thresholds, parse params) • Result: Lessredundantlyuseddiskspaceallowsfor • morecontent in the global KB (e-books, authornames, etc.) • fasterrevisionupdates • morefrequentrevisionupdates

  14. Database architecture in SFX 4 sfxglb41 sfxlcl41 KB_*,e. g.KB_TARGETS LCL_*_LINKING_INFO LCL_*_INVENTORY

  15. desc(ribe) describesthecolumnsof a table: 16:40 sfxglb41> desc KB_OBJECT_PORTFOLIOS; +------------------------+------------------------+- | Field | Type |… +------------------------+------------------------+- | OP_ID | bigint(20) unsigned |… | TARGET_ID | bigint(20) unsigned |… | TARGET_SERVICE_ID | bigint(20) unsigned |… | OBJECT_ID | bigint(20) unsigned |… | TARGET_PARSER_PROGRAM | varchar(50) |… : : : +------------------------+------------------------+- 23 rows in set (0.01 sec)

  16. desc(ribe) as sfxlcl41 onelocalcounterpartof KB_OBJECT_PORTFOLIOS: 16:41 sfxlcl41> desc LCL_OBJECT_PORTFOLIO_INVENTORY; +---------------------+---------------------------+- | Field | Type |… +---------------------+---------------------------+- | INTERNAL_ID | bigint(20) unsigned |… | OP_ID | bigint(20) unsigned |… | ACTIVATION_STATUS | enum('ACTIVE','INACTIVE') |… | DEACTIVATION_REASON | varchar(255) |… | OWNER | varchar(100) |… : : : +---------------------+---------------------------+- 10 rows in set (0.01 sec)

  17. 3What'spossiblewithoutlosingsupport?

  18. select • SELECTstatementsreturnthecurrentcontentsofthespecifieddatabasefields. • They do not changethesecontentsat all! • The specifiedfieldsmaycorrespondtocolumnsFROMasmany different tablesasneeded. • Selectionconditions(WHERE...) arealwaysconditionsaboutthecontentsofthefields. • Bymeansof Boolean operators (AND, OR, NOT, resp.) conditionsmaybecombinedornegated.

  19. select(continued) • The resultof a SELECT statementisalways a setofrows: „<number>rows in set (<seconds>sec)“. • This setofrowsmaybeGROUPedBY thevaluesofcertainfields ... • ... oder itmaybeLIMITedto a subsetdefinedbysizeor a certainrangeofrownumbers.

  20. selectexample #1 Howmanyobjectportfoliosareactive? 16:45 sfxlcl41> > select > count(*) > from > LCL_OBJECT_PORTFOLIO_INVENTORY > where > ACTIVATION_STATUS = 'ACTIVE';

  21. Can thisbetrue??? Forinstance sfxlcl41 on sfx.bib-bvb.de thequerystatedabovereturned on Feb 8, 2013: 142,974 – that'squite a lotfor an instancewhichweuseassortof a prototype fornewlyaddedlibraryinstances!

  22. select-Beispiel 2 Now, howmanyobjectportfoliosarereallyactive? select count(*) from LCL_OBJECT_PORTFOLIO_INVENTORY aspi, LCL_SERVICE_INVENTORY as si, LCL_TARGET_INVENTORY asti, sfxglb41.KB_OBJECT_PORTFOLIOS asop where pi.ACTIVATION_STATUS = 'ACTIVE' andsi.ACTIVATION_STATUS = 'ACTIVE' andti.ACTIVATION_STATUS = 'ACTIVE' andsi.TARGET_ID = ti.TARGET_ID andop.TARGET_ID = ti.TARGET_ID andop.TARGET_SERVICE_ID = si.TARGET_SERVICE_ID andop.OP_ID = pi.OP_ID;

  23. Every answerrisesnewquestions Forinstance sfxlcl41 on sfx.bib-bvb.de thequerystatedabovereturned on Feb 8, 2013: 17,100 – well, that'sfarmore plausible! But: Who activated so many OPs withoutactivatingtheassociatedtargetsandservices?

  24. desc(ribe) as sfxlcl41 onelocalcounterpartof KB_OBJECT_PORTFOLIOS: 16:41 sfxlcl41> desc LCL_OBJECT_PORTFOLIO_INVENTORY; +---------------------+---------------------------+- | Field | Type |… +---------------------+---------------------------+- | INTERNAL_ID | bigint(20) unsigned |… | OP_ID | bigint(20) unsigned |… | ACTIVATION_STATUS | enum('ACTIVE','INACTIVE') |… | DEACTIVATION_REASON | varchar(255) |… | OWNER | varchar(100) |… : : : +---------------------+---------------------------+- 10 rows in set (0.01 sec)

  25. WhattheAdminCenterdoesn'ttell • The MySQL leveloftenrevealsinformationthatis not accessiblefromthe SFX AdminCenter! • Example: Howmanye-booktargetsarethere? • A SELECT statementconsistingof 29 lines (formattedas in thetwoexamplesabove) determinesthecurrently valid answer. • Due to a weeklytriggered Perl scriptthatpimpstheresultsetwithsome HTML markupthepubliclyavailableoverviewstaysup-to-date – at least aslongaswehaveappliedthelatestrevision update 

  26. 4What's not possiblewithoutlosingsupport ... andyetsometimesneedstobedone?

  27. Read? YO! – Write? NO! • Warning: Writing directlyinto SFX MySQL tables will forfeityourentitlementtotechnicalsupport! • At least that'sofficial Ex Libris policy (AFAIK). • In practice: Ifyouhavemanipulatedlocaloreven global tablesofthe SFX databaseyoucan not rely on thehelpinghandsof Ex Libris whenfightingproblemsclearlycausedbythatmanipulation – andbesurethatthey'llknowwhathascausedyourproblems!

  28. insert • addsnewrowsto MySQL tables • maycompromisetheconsistencyofthevarious links toobjectsstored in othertables • isdefinitely not recommended

  29. update • changesalreadyexistingrows in a MySQL table • shouldbeusedonlyifyouknowwhatyou'redoing ... • ... orifyouareinvitedtoby SFX support. And, yes, thelattermay happen once in a while!

  30. Everythingproxied? Forhowmanyobjectportfoliosisproxyingenabled? select t.TARGET_NAME, count( pl.OP_ID ) ascount from sfxglb41.KB_TARGETS as t, sfxglb41.KB_OBJECT_PORTFOLIOS asop, LCL_OBJECT_PORTFOLIO_LINKING_INFO aspl where pl.PROXY_ENABLED = 1 andpl.OP_ID = op.OP_ID andop.TARGET_ID = t.TARGET_ID groupby t.TARGET_NAME;

  31. Amigrationbug Fornoneoftheinstances on sfx.bib-bvb.de a proxyisconfiguredat all. Nevertheless on Nov 22, 2012, thequerystatedabovereturned a total amountof 1,408 affectedtargets 758,920 affectedobjectportfolios SFX supportrecognizedthisas a "knownissue" whichoccurredduringthefirstbunchofdatabasemigrationsaspartofthe upgrade from v3 to v4 andrecommended:

  32. Do it yourself! “If you would like to turn off the proxy setting for all portfolios please set the PROXY_ENABLED field to 0 for all records in the LCL_OBJECT_PORTFOLIO_LINKING_INFO table using If you want to deactivate the setting only for certain targets this is obviously a lot more complicated and will need to be done manually.” update LCL_OBJECT_PORTFOLIO_LINKING_INFO set PROXY_ENABLED = 0;

  33. Thanks a lot, Ex Libris! sfxglb41 sfxlcl41 KB_*,e. g.KB_TARGETS LCL_*_LINKING_INFO LCL_*_INVENTORY

  34. 5A Closed Open Interface?

  35. When DataLoader cannothelp ... • DataLoader allowsformasschangesofthemostcommonlycustomizedfieldsofobjectportfolios. • Doing so createsmanylocalizations ... • ... only a fewofwhich (e.g., localthresholds) canbereverted / removedby a complementarymasschange. • Unlessyou'veenabledAUTO UPDATE on servicelevel! • But youcannot do so bymeansof DataLoader 

  36. ... youneedtohelpyourself • Howdoes DataLoader operate on the MySQL database? • Howdoesthe SFX AdminCenteroperate on the MySQL database? (e.g., whenyouedit an objectportfolio) • Answersprovided in thereadable SFX sourcecode. • Bycopy & learnit was quite simple todevelop a Perl scriptthatenables AUTO UPDATE for a targetservicegivenby ID – withabsolutelynoclickat all!

  37. DBLayer::* • SFX 4 comeswith a completelynewinterfacebetweenthe MySQL databaseandthe Perl programmes. • Object-orientedmoduleslikeDBLayer::TargetServiceallowfordatabasemanipulations just bycallingtheappropriatemethods – withno SQL statementsat all! • Since SFX sourcecodeitselfmakesuseofthisinterfaceitpresumablyisquitestablealready but, unfortunately, • asgoodasundocumentedand • officiallynot supportedifusedin customerscripts.

  38. 6TwoEnhancementRequests

  39. ER #1 What'spossibleforthefilesystem ("Unix File Manager") shouldbepossibleforthedatabaseaswell!

  40. ER #2 Open PlatformStrategy for SFX now!

  41. What do youthink? Dr. Mathias Kratzer Bayerische Staatsbibliothek / Bavarian Library Network E-Mail: kratzer@bsb-muenchen.de