2.7.1 Upgrade task - Column not found: 1054

[John Hewson]

Hi,

I'm trying a test installation of AtoM 2.7.1, which all runs very smoothly until I try the upgrade task (from a 2.5 database), when I get a red warning:

[wrapped: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'digital_object.LANGUAGE' in 'field list']

A quick grep takes me to

lib/model/om/BaseDigitalObject.php

where there's a new language field.

If I remove lines

13:       LANGUAGE = 'digital_object.LANGUAGE',
33:       $criteria->addSelectColumn(QubitDigitalObject::LANGUAGE);

the upgrade task runs successfully.

But do I need that language field? Anyone else seeing this or am I missing something obvious?

Thanks,

John

[John Hewson]

Seems to something affecting upgrades from older versions: If I upgrade from 2.6.4-184 the upgrade task runs successfully and the digital_object table acquires the new language column. If I upgrade from 2.5.2-172, the script bails unless I edit the object model, as above, and then I get no new column. Any suggestions welcome. Thanks, John

[Dan Gillean]

Hi John, 

Most common reason for these types of upgrade errors is people accidentally skipping Step 4 of this section - dropping and then recreating the database: 

• https://www.accesstomemory.org/docs/2.7/admin-manual/installation/upgrading/#copy-your-old-data
  

See also this post: 

• https://groups.google.com/g/ica-atom-users/c/9l56eymNBww/m/NC1vQ-crCwAJ
  

Please let us know if this helps! It would be good to determine if this is just another instance of a missed step or an actual bug in 2.7.x that we need to fix. If you try the drop and recreate method and it has no different effect on the outcome, let me know and I will see if our team can reproduce the issue locally. 

Cheers, 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

he / him

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/8b8453a2-1834-448e-8be8-747d1575a8d3n%40googlegroups.com.

[John Hewson]

Thanks Dan, but I've been religiously dropping and creating all the while, and every sort of cache has been cleared, and I've started over with a full fresh installation, just in case. The upgrade repeatedly works reliably for 2.6.4-184, but not for three separate instances of 2.5.3-172 that I've tried. I hope you can reproduce this. Cheers, John

[José Raddaoui]

Hi John,

Thanks for reporting this issue, we have been able to reproduce it and filed:

https://projects.artefactual.com/issues/13641

As you noticed the issue only happens when upgrading from 2.5.x (or lower) to 2.7.x and the workaround you used seems okay to me as long as you add those lines back after running the upgrade task and before restarting the AtoM services.

Best regards,

Radda.

[John Hewson]

Thanks Radda, good to have that confirmed. Cheers, John

[Rodolfo Oramas]

Thanks John for the workaround!

I had to restart the migration from zero and it worked great.

My first try had a SQLSTATE[42S22]: Column not found error about password.

[matthewb...@gmail.com]

Hi all,

I'm getting this issue now with upgrading from 2.6 to 2.7.

I did a fresh installation of 2.7 and created a database and was using it as normal. But then I decided I wanted to use this instance for another repository instead. The old repository was running 2.6. I took the database from the old one and brought it onto my 2.7 instance.

I got the issue above even though I followed all the steps and ran an upgrade task. But also before I ran any upgrade task. I dropped and recreated the database as advised.

Nothing seems to be working despite trying all variations, so I am posting to see if there are an other suggestions ?

Thanks,

Matthew

[matthewb...@gmail.com]

I tried a different approach and am getting the exact same issues.

I did a fresh install of atom 2.8. Then tried to import my database from 2.6.

Dropped database first, restored it. Same problem.

No idea what to do.

[matthewb...@gmail.com]

This here: https://groups.google.com/g/ica-atom-users/c/aDtnqo_jKs8/m/IbeKCw0gBAAJ

is exactly what I am trying to do. And following the documentation to the letter.

[Active Mishra]

Hey Matthew, I appreciate your efforts in writing this mail but the problem is I already have a team of editors. So, adding an extra editor in our team is not necessary for now but still you would be in our consideration. 

Thank You!

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/f06821cb-32fe-44f7-a74d-d3d9435d2fd6n%40googlegroups.com.

[matthewb...@gmail.com]

I think you misread the message. I was writing about an upgrade from 2.6 to 2.8

[Active Mishra]

I was just playing with you

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/680fe31c-b69b-42f8-87d4-56727791d811n%40googlegroups.com.

[Dan Gillean]

Hi Matthew, 

If you have 2.8 installed, then you might try running AtoM's new data integrity repair tool, which is a command-line task that can check for common forms of data corruption, and possibly even repair them. THis might help work out whatever upgrade hiccup you are encountering. Essentially I would suggest the following: 

• Make sure that you have properly followed all the installation instructions for 2.8
• Run the purge task - php symfony tools:purge
• Drop and recreate the database per the upgrade instructions
• Load A COPY of your 2.6 database - we are going to run a task on it, so if something goes wrong, we want an unaltered backup version to fall back to! 
• Run the upgrade task 

Now, from the sounds of it, this won't work, but we will try the previous steps once more just to make sure, and the following steps whether it works or not: 

• Check the schema version by running php symfony tools:get-version -  it should return something like 2.8.2 - 193. If the number is NOT 193, let us know what number it does return, so we can know how many schema versions your 2.6 instance is behind the expected version for 2.8.x. 
• Try running the data integrity repair task - be sure to set a proper path for the CSV report it will generate: php symfony tools:data-integrity-repair --mode=fix /path/to/report/filename.csv
  

Now see if you can run the upgrade task again. 

Hopefully that helps - if not, then I hope someone else here might have further suggestions for you! 

Cheers, 

Dan Gillean, MAS, MLIS
Business & User Experience Analyst
Artefactual Systems, Inc.
604-527-2056

he / him

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/680fe31c-b69b-42f8-87d4-56727791d811n%40googlegroups.com.

[matthewb...@gmail.com]

Hi Dan,

I did not know you weren't managing the forum anymore when I wrote. Thanks for checking in anyway!

So I followed all your advice, and still the same problems.

To answer your most pertinent question: The version number for symfony is 175, not 193.

I completely wiped my system and installed atom version 2.8.0 (following generic instructions)

 

Initially the upgrade task did not work returning:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'function_object' already exists

but then I ran:

php -d memory_limit=-1 symfony tools:upgrade-sql -v -n="175"

and it did. upgrading to the same version it was. 2.8.0

there was no data repair tool in the code, so I added it in. Ran it, and it worked.

But the search:populate task did not, returning as usual:

 

[wrapped: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'digital_object.LANGUAGE' in 'field list']

So then I downloaded the version 2.8.2 and upgraded to that. Data integrity too worked. But same issue with search populate task again.

I even went into the code of the data repair tool to make sure it was up to date according to the latest bug fix of july 22 this year, and it was fine.

The only thing I can try doing is just wiping again, and making sure I install 2.8.2 in the first place but that's unlikely to make any difference. But I'll try anyway. If that works I will put it up here.

[Dan Gillean]

Hi Matthew, 

So, just to clarify - the upgrade is now working, it's just the search index that is throwing an error when you try to rebuild? 

Now that you have gone through these various steps, does the tools:get-version task now return v193?

If it's just the ES index, then the first thing I would suggest you try is manually deleting the entire index. You can also try checking the ES logs directly for more information, try stopping and restarting the ES service, and more. All of these suggestions are outlined in a previous thread here, where I have tried to collect a bunch of troubleshooting suggestions for the ES index: 

• https://groups.google.com/g/ica-atom-users/c/tzvQRtkGMto/m/ezUvRyQHAQAJ
  

Hope that helps! 

Dan Gillean, MAS, MLIS
Business & User Experience Analyst
Artefactual Systems, Inc.
604-527-2056

he / him

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/05d6cc5a-501f-415d-a0c1-521127797647n%40googlegroups.com.

[José Raddaoui]

Hi Matthew,

The -n option for the upgrade task only applies a specific migration, so it's not doing the entire upgrade:

https://github.com/artefactual/atom/blob/qa/2.x/lib/task/migrate/arUpgradeSqlTask.class.php#L54

The digital_object.LANGUAGE column is added in migration 190:

https://github.com/artefactual/atom/blob/qa/2.x/lib/task/migrate/migrations/arMigration0190.class.php#L33-L38

Looking at the error when you run the upgrade task, there seems to be a mismatch between your DB version (175) and the expected schema for that version, as the function_object table is created on migration 176:

https://github.com/artefactual/atom/blob/qa/2.x/lib/task/migrate/migrations/arMigration0176.class.php#L48-L51

Considering that, I'd try to match the DB version with the current schema. But that's hard to know wihtout looking at your database state before the migration and checking what is every migration doing. Another option could be to run the migrations one by one, ignoring those that fail and updating the version after. This may lead to some duplicated data in your DB and maybe some parts of those migrations are not applied, but I think that's your best option right now:

php -d memory_limit=-1 symfony tools:upgrade-sql -n="176"     (will fail with the error you already faced)

php -d memory_limit=-1 symfony tools:upgrade-sql -n="177"

php -d memory_limit=-1 symfony tools:upgrade-sql -n="178"

php -d memory_limit=-1 symfony tools:upgrade-sql -n="179"

... and so on until ... -n="193"

Then update the DB version in the settings table manually:

- Accessing the MySQL command prompt: https://www.accesstomemory.org/pt-br/docs/2.6/admin-manual/maintenance/common-atom-queries/#accessing-the-mysql-command-prompt

- Checking the current version: https://www.accesstomemory.org/pt-br/docs/2.6/admin-manual/maintenance/common-atom-queries/#check-the-atom-database-schema-version

- To update the version to 193, the following statement should work:

UPDATE setting s
JOIN setting_i18n i18n ON s.id = i18n.id
SET i18n.value = 193
WHERE s.name = 'version';

I hope that helps.

Best regards,

Radda.

[matthewb...@gmail.com]

Hi Radda,
I just implemented all the suggestions you made . The DB version is now 193.
But I still get the following error when I run the search:populate command:

[wrapped: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'digital_object.LANGUAGE' in 'field list']

I'm going to now have a look at the ES index as Dan suggested.

[matthewb...@gmail.com]

Hi Dan,

I am attaching the php symfony search:status output as an attachment.

Then the output of the elasticsearch log when I type in the search populate command.

The command itself on the screen is still giving:

[wrapped: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'digital_object.LANGUAGE' in 'field list']

despite me having implemented all of the suggestions you gave in the thread about elasticsearch above.

[José Raddaoui]

Hi Matthew,

Did you see any error running migration 190? That should be the one adding the 'digital_object.LANGUAGE' column to the database. You could also try to add it manually:

ALTER TABLE digital_object ADD language VARCHAR(50) AFTER usage_id;

Best regards,

Radda.

[matthewb...@gmail.com]

Hi Radda,

I did not see any errors.

Should I go looking for them in the logs?

I did the manual adding, but now this is the error I'm getting is;

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'atom.accession_event' doesn't exist

I guess I can add that manually too? But I don't know how to.

[matthewb...@gmail.com]

The only error I can see the the mysql logs that might be of relevance is this:

2024-09-18T19:53:31.700844Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode i>

[José Raddaoui]

Hi Matthew,

You should have seen the error when running the migration, same as with the upgrade task. The new error, it's a new table added in migration 185:

https://github.com/artefactual/atom/blob/qa/2.x/lib/task/migrate/migrations/arMigration0185.class.php#L36-L58

So it looks like applying the migrations one by one didn't work. Another suggestion:

- Restore your backup, it should be back to version 175.

- Drop the function_object table from the database.

- Try to run the upgrade task again.

Best regards,

Radda.

[matthewb...@gmail.com]

Hi Radda,

Sorry for the stupid question, but how would I drop the table?

Would it be

DROP TABLE function_object;    

[José Raddaoui]

Hi Matthew,

Yes, that should work. Just make sure you select the AtoM database firt: `USE atom;`.

Best,

Radda.

[matthewb...@gmail.com]

Hi Radda,

I get this error message within mysql:

ERROR 3730 (HY000): Cannot drop table 'function_object' referenced by a foreign key constraint 'function_object_i18n_FK_1' on table 'function_object_i18n'.

I had gone into mysql by typing: mysql -u root -p atom;

which I figured was the same as USE atom;

 I typed USE atom; after the error, it said database changed, and then I got the same error.

Should I have dropped and recreated the database as per earlier instructions?

One thought I had of something which may be the cause of all this is that recently I did an XML import from another instance of atom, which was using version 2.7. Possibly in caused some conflict.

But I don't know if eliminating that import at this point would make any difference?

[José Raddaoui]

Hi Matthew,

Try dropping the  `function_object_i18n` table first and then the `function_object`. Lets see if there are other errors after that.

Best regards,

Radda.

[matthewb...@gmail.com]

Hi Radda,

Yes that seemed to finally work.

There were no errors within mysql and the search:populate task finally ran and completed.

Are there any future issues I need to be aware of for having dropped these tables? Can I export as normal for example?

Am I ready to make this into a production server?

[José Raddaoui]

Hi Matthew,

After you dropped them, the migration process should have created them again on migration 176, you could check their existence in the DB.

I hope it's all good,

Radda.

[matthewb...@gmail.com]

Seems all good. Any other errors and I'll post them up.

Thanks for much for your attention to this!