EPrints Technical Mailing List Archive

See the EPrints wiki for instructions on how to join this mailing list and related information.

Message: #08694

< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

Re: [EP-tech] enlarging of text field fails?

Hi Thomas,
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. 

Regards

David Newman

On 10/08/2021 22:46, th.lauke@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
Thomas



--
This email has been checked for viruses by AVG.
https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.avg.com%2F&amp;data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cf16457837e6d4996b33308d95c5b72ba%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637642370093607958%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=czw%2F%2BZCTcZImzg1bzqs3hQl9917Rjr0gaFs4bIPlZwg%3D&amp;reserved=0