Uncover the Mysteries of Your MySQL Database with Dr. Mathias Kratzer

Your MySQL Database: The Undiscovered Country Dr. Mathias Kratzer, Bayerische Staatsbibliothek / Bavarian Library Network

Agenda 1. How to get in? 2. How to find your way? 3. What's possible without losing support? 4. What's not possible without losing support? 2

Agenda 1. How to get in? 2. How to find your way? 3. What's possible without losing support? 4. What's not ... and yet sometimes needs to be done? 5. A closed open interface? 6. Two Enhancement requests 3

1 How to get in? 4

Interactively sfxlcl41> mysql usfxlcl41 p Enter password: Welcome to the MySQL monitor. [ ] 16:35 (none)> The MySQL user of an SFX instance usually bears the same name as the corresponding UNIX user. The respective MySQL password is usually just set to the user name by the SFX installer. Preferably, you should log in as user of a local SFX instance (e.g. as "sfxlcl41" like in the screenshot above)! 5

Interactively (continued) 16:35 (none)> use sfxlcl41; Reading table information [ ] Database changed 16:37 sfxlcl41> After successful login you need to choose a database to work with. In case of doubt: database = user = password All users have read permission on all databases. Write permission is granted to sfxglb41 on all databases, and to each of the other users on "their" own database only. 6

Wrapper script MySQL user unknown, password forgotten? Don't worry! Module Manager::Connection is all you really need: 1. Build your SQL statement, 2. insert it into a Perl wrapper script that establishes a database connection by using module Manager::Connection, and 3. run the wrapper script. (Redirect the output into a file in case you expect a longer list of results.) 7

Would Perl::DBI work as well? In principle yes, but establishing the database connection by yourself means your script contains the MySQL user as plain text your script contains the MySQL password as plain text character encoding usually needs to be set explicitely by submitting a SET NAMES 'utf8'; as first command All this is taken care of by Manager::Connection ! 8

2 How to find your way? 9

show tables lists all tables of the currently chosen database: 16:37 sfxlcl41> show tables; +------------------------------------------+ | 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) 10

show tables as sfxglb41 yields a completely different list of tables: 16:39 sfxglb41> show tables; +------------------------------------------+ | Tables_in_sfxglb41 | +------------------------------------------+ : : | KB_OBJECT_PORTFOLIOS | : : | KB_TARGET_SERVICES | : : +------------------------------------------+ 78 rows in set (0.01 sec) 11

Database architecture in SFX 3 sfxglb3 sfxlcl3 12

SFX 4: Less is more! Key change of architecture: database of a local instance no longer contains a full copy of the global KB, ... ... but only the activation status and any local fields (e.g. thresholds, parse params) Result: Less redundantly used disk space allows for more content in the global KB (e-books, author names, etc.) faster revision updates more frequent revision updates 13

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

desc(ribe) describes the columns of 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) | | | | 15

desc(ribe) as sfxlcl41 one local counterpart of 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) | | 16

3 What's possible without losing support? 17

select SELECT statements return the current contents of the specified database fields. They do not change these contents at all! The specified fields may correspond to columns FROM as many different tables as needed. Selection conditions (WHERE ...) are always conditions about the contents of the fields. By means of Boolean operators (AND, OR, NOT, resp.) conditions may be combined or negated. 18

select (continued) The result of a SELECT statement is always a set of rows: <number> rows in set (<seconds> sec) . This set of rows may be GROUPed BY the values of certain fields ... ... oder it may be LIMITed to a subset defined by size or a certain range of row numbers. 19

select example #1 How many object portfolios are active? 16:45 sfxlcl41> > select > count(*) > from > LCL_OBJECT_PORTFOLIO_INVENTORY > where > ACTIVATION_STATUS = 'ACTIVE'; 20

Can this be true??? For instance sfxlcl41 on sfx.bib-bvb.de the query stated above returned on Feb 8, 2013: 142,974 that's quite a lot for an instance which we use as sort of a prototype for newly added library instances! 21

select-Beispiel 2 Now, how many object portfolios are really active? select count(*) from LCL_OBJECT_PORTFOLIO_INVENTORY as pi, LCL_SERVICE_INVENTORY as si, LCL_TARGET_INVENTORY as ti, sfxglb41.KB_OBJECT_PORTFOLIOS as op where pi.ACTIVATION_STATUS = 'ACTIVE' and si.ACTIVATION_STATUS = 'ACTIVE' and ti.ACTIVATION_STATUS = 'ACTIVE' and si.TARGET_ID and op.TARGET_ID and op.TARGET_SERVICE_ID = si.TARGET_SERVICE_ID and op.OP_ID = ti.TARGET_ID = ti.TARGET_ID = pi.OP_ID; 22

Every answer rises new questions For instance sfxlcl41 on sfx.bib-bvb.de the query stated above returned on Feb 8, 2013: 17,100 well, that's far more plausible! But: Who activated so many OPs without activating the associated targets and services? 23

desc(ribe) as sfxlcl41 one local counterpart of 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) | | 24

What the AdminCenter doesn't tell The MySQL level often reveals information that is not accessible from the SFX AdminCenter! Example: How many e-book targets are there? A SELECT statement consisting of 29 lines (formatted as in the two examples above) determines the currently valid answer. Due to a weekly triggered Perl script that pimps the result set with some HTML markup the publicly available overview stays up-to-date at least as long as we have applied the latest revision update 25

4 What's not possible without losing support ... and yet sometimes needs to be done? 26

Read? YO! Write? NO! Warning: Writing directly into SFX MySQL tables will forfeit your entitlement to technical support! At least that's official Ex Libris policy (AFAIK). In practice: If you have manipulated local or even global tables of the SFX database you can not rely on the helping hands of Ex Libris when fighting problems clearly caused by that manipulation and be sure that they'll know what has caused your problems! 27

insert adds new rows to MySQL tables may compromise the consistency of the various links to objects stored in other tables is definitely not recommended 28

update changes already existing rows in a MySQL table should be used only if you know what you're doing ... ... or if you are invited to by SFX support. And, yes, the latter may happen once in a while! 29

Everything proxied? For how many object portfolios is proxying enabled? select t.TARGET_NAME, count( pl.OP_ID ) as count from sfxglb41.KB_TARGETS as t, sfxglb41.KB_OBJECT_PORTFOLIOS as op, LCL_OBJECT_PORTFOLIO_LINKING_INFO as pl where pl.PROXY_ENABLED = 1 and pl.OP_ID and op.TARGET_ID group by t.TARGET_NAME; = op.OP_ID = t.TARGET_ID 30

A migration bug For none of the instances on sfx.bib-bvb.de a proxy is configured at all. Nevertheless on Nov 22, 2012, the query stated above returned a total amount of 1,408 758,920 affected object portfolios affected targets SFX support recognized this as a "known issue" which occurred during the first bunch of database migrations as part of the upgrade from v3 to v4 and recommended: 31

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 update LCL_OBJECT_PORTFOLIO_LINKING_INFO set PROXY_ENABLED = 0; 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. 32

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

5 A Closed Open Interface? 34

When DataLoader cannot help ... DataLoader allows for mass changes of the most commonly customized fields of object portfolios. Doing so creates many localizations ... ... only a few of which (e.g., local thresholds) can be reverted / removed by a complementary mass change. Unless you've enabled AUTO UPDATE on service level! But you cannot do so by means of DataLoader 35

... you need to help yourself How does DataLoader operate on the MySQL database? How does the SFX AdminCenter operate on the MySQL database? (e.g., when you edit an object portfolio) Answers provided in the readable SFX source code. By copy & learn it was quite simple to develop a Perl script that enables AUTO UPDATE for a target service given by ID with absolutely no click at all! 36

DBLayer::* SFX 4 comes with a completely new interface between the MySQL database and the Perl programmes. Object-oriented modules like DBLayer::TargetService allow for database manipulations just by calling the appropriate methods with no SQL statements at all! Since SFX source code itself makes use of this interface it presumably is quite stable already but, unfortunately, as good as undocumented and officially not supported if used in customer scripts. 37

6 Two Enhancement Requests 38

ER #1 What's possible for the file system ("Unix File Manager") should be possible for the database as well! 39

ER #2 Open Platform Strategy for SFX now! 40

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