Tim, much appreciated for the detailed analysis and response. I realized I mentioned the wrong example from our registry, but am glad to know how the discrepancy likely showed up. I was also just writing out a SQL statement to update that column when your email showed up, so it's good to know I was on the right track. Thanks for the response.
Andrea, I just read your response following this. :-) Thanks to you both. -Jeff ________________________________________ From: [email protected] <[email protected]> on behalf of Tim Donohue <[email protected]> Sent: Wednesday, November 18, 2015 3:49 PM To: [email protected] Subject: Re: [dspace-tech] 5.3 migration script drops columns from 4.3 database Hi Jeff, It looks like you (or your institution) have been using DSpace for a while! The "bitstreamformatregistry" table's "mimetype" column switched from 48 characters to 256 characters way back in DSpace 1.5 (circa 2008). Here's the old migration that made the switch (this script used to be called "database_schema_14-15.sql" in all prior versions of DSpace) https://github.com/DSpace/DSpace/blob/dspace-5_x/dspace-api/src/main/resources/org/dspace/storage/rdbms/sqlmigration/postgres/V1.5__Upgrade_to_DSpace_1.5_schema.sql#L39 My guess here is that a long time ago, your site upgrade to DSpace 1.5 had issues that for some reason caused that mimetype column to never be updated. (It'd be hard to say why exactly.) But, all versions since then (1.6,1.7,1.8,3.x,4.x,5.x) have had a mimetype of 256 characters. So, you would have to have started with DSpace 1.4 or earlier to encounter this issue. In anyways, to fix your existing situation, I'd recommend going back to your 4.x installation, and running: ALTER TABLE BitstreamFormatRegistry ALTER COLUMN mimetype TYPE VARCHAR(256); Then, try your migration to 5.x again. Hopefully, this time it'll work properly (assuming nothing else was overlooked in past database migrations). Unfortunately, there's really no easy way for the 5.x migration script to "compare" your existing database structure with what is expected. It only determines what version of DSpace you are running (in this case 4.x) and then assumes it only needs to run the scripts to upgrade your database from 4.x -> 5.x. This does mean there may be situations like yours where the 5.x migration doesn't go as smoothly as expected. But, we hope automating these database migrations will help us to *avoid* these database-level oddities in the future (the manual process was just too prone to human accidents or errors). Good luck, Tim On 11/18/2015 3:32 PM, Jeffrey Sheldon wrote: >> It looks like you have a too long value for something, but don't >> actually see too many varchar(48) columns in my 5.x database, so I'm not >> sure where this is falling over. Hopefully someone with more experience >> with the db migrations will help. > Thanks for the feedback, Andrea. Since the varying number is small, I > decided to grep bitstreamformatregistry in our DSpace 4.3 production SQL and > turned up this: > > bitstream_format_id | mimetype | short_description | description | > support_level | internal > ---------------------+------------+-------------------+----------------------------------------------------------------------+---------------+---------- > 11 | image/jpeg | JPEG | Joint Photographic Experts Group/JPEG File > Interchange Format (JFIF) | 1 | f > (1 row) > > I'm not sure how that inconsistency persisted with updates (1.8.2 to 3.3 to > 4.3) given the active constraint, but it did. What's more interesting is > that the 5.x migration script doesn't automatically catch the difference in > table limits. > > Of note, here's a SQL dump from our 4.3 install: > > CREATE TABLE bitstreamformatregistry ( > bitstream_format_id integer NOT NULL, > mimetype character varying(48), > short_description character varying(128), > description text, > support_level integer, > internal boolean > ); > > And here's one from 5.3: > > CREATE TABLE bitstreamformatregistry ( > bitstream_format_id integer NOT NULL, > mimetype character varying(256), > short_description character varying(128), > description text, > support_level integer, > internal boolean > ); > > That's a sizable difference. Isn't there a way for the migration script to > do a sanity check on the existing table structure before attempting changes > or manipulating populated data? I absolutely know we didn't make this change > ourselves. > > > -Jeff > -- Tim Donohue Technical Lead for DSpace & DSpaceDirect DuraSpace.org | DSpace.org | DSpaceDirect.org -- You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/dspace-tech. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/dspace-tech. For more options, visit https://groups.google.com/d/optout.
