[EP-tech] enlarging of text field fails?

[drn at ecs.soton.ac.uk (David R Newman)]

Hi Thomas (and anyone else interested),

I have created a wiki page that I have started to populate with common 
MySQL problems with EPrints:

https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2Fw%2FCommon_MySQL_Problems_with_EPrints&data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C13fb1d465dca4092778608d95d2c3ffb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637643266994192438%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=y1RV0nQKzEGIg4Li%2B6GsjD86rCnPM3BtJP2OIuxc6oQ%3D&reserved=0

Each problem is broken down into three subsections:

1. An example error message or description of the symptom of the error 
if no message is generated.

2. The reason behind the error.

3. A resolution to the error.

I have linked this page into the Troubleshooting wiki page [1] that is 
part of the EPrints Manual [2] section of the wiki.

One thing I have learnt is that some of these problems are even more 
complicated than I already understood.? Therefore, I am not surprised 
you and others have had been unsure how best to tackle them.

Regards

David Newman

[1] https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2Fw%2FTroubleshooting&data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C13fb1d465dca4092778608d95d2c3ffb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637643266994341782%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=ceExRXPMcV1rZ%2FlgKDg6erV5nWLusttTr9y%2B4pITVEc%3D&reserved=0

[2] https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.eprints.org%2Fw%2FCategory%3AManual&data=04%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C13fb1d465dca4092778608d95d2c3ffb%7C4a5378f929f44d3ebe89669d03ada9d8%7C0%7C0%7C637643266994351737%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=hne9bfApGa0RKiEALIWAhXljrgtHfXAPl8YRG7z4uKU%3D&reserved=0

On 11/08/2021 01:03, David R Newman wrote:
> 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 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
>> Thomas
>>

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