1 / 41

Your MySQL Database: The Undiscovered Country

Learn how to access and navigate the MySQL database, what is possible without losing support, what is not possible, and discover two enhancement requests.

jhighsmith
Download Presentation

Your MySQL Database: The Undiscovered Country

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related