[EP-tech] enlarging of text field fails?
David R Newman
drn at ecs.soton.ac.uk
Wed Aug 11 01:03:27 BST 2021
I am going to create a wiki page to explain about this issue and others
that I have encountered that directly relate to issues with EPrints
working with MySQL.
I was just looking closer at your issue. I was conflating the "row size
too large" issue which would occur when when you added one too many
VARCHAR ( type => 'text') fields and would prevent you from adding the
new field and the "data length too long for column", which is where the
fields are in place but the data inserted into one particular field, in
this case volume, is too large. This is because changing maxlength to 9
will not update the database but will let EPrints allow you to enter a
value longer value that the the VARCHAR size which has not been
updated. Therefore 7 characters would still be longer than the 6
character length VARCHAR that would have been created in the eprint
table, assuming you were using the default volume field definition
originally. Therefore, you will need to manually update the database to
change from a VARCHAR(6) to a VARCHAR(9), something like:
ALTER TABLE eprint MODIFY COLUMN volume VARCHAR(9) DEFAULT NULL;
"epadmin update" will not modify fields/columns that have already been
created. I certainly remember having to increase the length of volume
and similar fields but I did not recall at the time of your email, that
once I had done this I then went and manually updated the eprint table
like above. I am not sure if there is a robust way to facilitate
modification of existing fields using epadmin, as I can see this getting
very complicated very quickly. The more complicated it get the more
likely it is to either fail to carry out these field/column updates or
worse still, break the table as a whole, potentially leading to data loss.
On 10/08/2021 22:46, th.lauke at arcor.de wrote:
> CAUTION: This e-mail originated outside the University of Southampton.
> Hi David,
>> The problem you have is that there are too many VARCHAR columns in the eprint table.
> many thanks for your immediate response, although I would expecting this information from an "epadmin update" call ... :)
>> ... or to convert VARCHAR columns (particularly any VARCHAR(255)) to LONGTEXT fields.
> okay, no problem to manipulate the database in general, but ...
>> Both of these require manually running "ALTER TABLE" MySQL commands.
> ... I am more familiar with the MySQL commands than the database scheme.
> Please list in brief
> - which tables are typically involved
> - which commands do you recommend in which order
>> you should also change the field in the EPrints configuration to type => 'longtext'.
> for sure, this should be done simultaneously ...!
>> Feel free to ask for more details about how to modify the columns for the eprint table in MySQL.
> Maybe _we_ should add these instructions to the wiki?!
>> However, my first piece of advice is make sure you backup the database before making any changes.
> fortunately we have a daily backup and a separate server for tests :)
> Thanks in advance
This email has been checked for viruses by AVG.
More information about the Eprints-tech