Install process stuck "Waiting for table metadata lock"

[Helen]

Hello - I'm still trying to pin down my problem with AtoM not connecting to ElasticSearch ( https://groups.google.com/g/ica-atom-users/c/C_aOZo7B9OQ/m/5cK86qKRAgAJ ) - having made some changes to the server, including switching to PHP 7.0 and ElasticSearch 1.7.6.

I've been working with our webhosts to try and pin down if the problem is related to ElasticSearch memory usage, and they seem convinced the problem is somewhere else. As I was still getting the 'no enabled connection' response when trying to rebuild the index, I decided to try a fresh install of AtoM 2.4 - however this seems to be getting hung up somewhere, with the database stuck "Waiting for table metadata lock".

As there was quite a lot of back and forth to get the right dependencies enabled on the server (particularly apcu-bc), it could be that this indicates something has been misconfigured. The webhosts sent me this log to share with you (attached) in the hope that you can shed some light on what's causing it.

(I did suggest we give up on 2.4 and try installing 2.5 with the 5.6 version of ElasticSearch, but they rightly pointed out that if this error was the result of a server misconfiguration, it might also fail...)

[Dan Gillean]

Hi Helen, 

I'm really sorry to hear you're still struggling to get AtoM properly installed. It's a bit hard to get a full sense of what's going on here, but I'll try to offer a few general suggestions. 

First, if you're still at the configuration and installation stage and having problems, I still think it might be worth installing 2.6 now, to avoid having to upgrade in the near future. We've added a lot of performance enhancements in the 2.6 release, and we've also replaced the Flash-based video player with an HTML5 one - if you intend to use video in your site at all, most browsers have announced they are fully removing Flash support after December 20, 2020. You can read more about all the enhancements and new features in the 2.6 release notes, or a summary in the forum announcement about 2.6 here. 

I don't think I've seen that particular error message before, but here are a couple things you could try next:

First, if the database is locking things, perhaps you should try restarting it. In Ubuntu 16.04 or 18.04, you can typically do this with:

• sudo systemctl restart mysql

You can of course also try restarting elasticsearch if you haven't already: 

• sudo systemctl restart elasticsearch

If you don't have any data to worry about, you could also try: 

• Dropping and recreating the database
• For 2.4, this command typically looks like:  mysql -u username -p -e 'drop database new_database; create database
  new_database character set utf8 collate utf8_unicode_ci;'
• new_database character set utf8 collate utf8_unicode_ci;'
• (don't forget to replace username in the command above with the database username you used during installation)
• Running the tools:purge task - this will delete any residual data in the system: php symfony tools:purge. This command should be run from AtoM's root installation directory. 

I'm not sure if those steps will help or not, but if you don't yet have data, they also won't hurt. If you end up using a later version of AtoM, you'll find updated commands to drop/recreate the database on our Upgrading documentation page. 

You could also try returning to the web installer configuration page and making sure you've properly entered your Elasticsearch connection credentials. Take the base URL of your site, and try adding /sfInstallPlugin/configureSite to it - this should re-open the web installer, where you can check that the Elasticsearch host, port and index name are entered correctly. If you've followed our instructions to the letter, see for example:

• https://www.accesstomemory.org/docs/2.4/admin-manual/installation/linux/ubuntu-xenial/#run-the-web-installer  
  

Hope this helps!

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/1b046df3-1d51-4a21-ba81-5e2faf881594n%40googlegroups.com.

[Helen]

Hi Dan - we did try restarting the database etc (pretty much everything, at some point!), but I will try the tools:purge task as well. I'll let you know how we get on.

thanks,

Helen

[Dan Gillean]

Hi Helen, 

I'll ask our developers if they have any further thoughts on this topic - in the meantime, I have found several references to this in MySQL documentation, and in other related online articles. These might help you understand what's happening, how to get more information, and how to possibly resolve the issue. 

MySQL 5.6 docs 

(AtoM 2.4 used MySQL 5.6 - if you decide to try installing a different version, be sure to check the docs for the correct version. AtoM 2.5 moved to MySQL 5.7, while AtoM 2.6 has upgraded to MySQL 8.0)

• https://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html 
• https://dev.mysql.com/doc/refman/5.6/en/lock-tables.html   
  
• https://dev.mysql.com/doc/refman/5.6/en/locking-issues.html  
  

See also: 

• https://lxadm.com/MySQL:_avoiding_%22Waiting_for_table_metadata_lock%22_when_executing_%22ALTER_TABLE%22
• https://stackoverflow.com/questions/19801139/mysql-permanently-getting-waiting-for-table-metadata-lock 
• https://www.abhinavbit.com/2019/07/sleep-thread-causing-waiting-for-table-metadata-lock.html   
  

Cheers, 

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

@accesstomemory

he / him

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/1078b1f8-772d-407d-967b-36715869325cn%40googlegroups.com.

[Helen]

Hi Dan - I shall take a look at this. (The different MySQL version is a factor in trying to get the 2.4 version working...)

[Helen]

The purge tool seemed to get stuck on the same ElasticSearch error - I could get to the 'installed' site, after that, and also back to the site configuration, but the search index still doesn't work (and going back to the search config page, it just timed out).

(I then tried to go back to the database configuration page, and at that point the database stalled again, so I'm having to get the webhosts to restart it again before I try anything else)

The metalocking info is definitely pushing up against the limits of my understanding! If your developers are able to point to anything that might be causing this - whether it's because of the Elasticsearch error or a potential server misconfiguration - it would be very helpful.

thanks,

Helen

[José Raddaoui]

Hi Helen,

It's hard to tell from that log what may be going on, but it looks to be two different users connecting to the AtoM database. Could it be that there is more than one AtoM instance connected to that database (from previous installs or something like that)?

Nevertheless, if you got to see the Elasticsearch error in the install process or the purge task, that means the database initialization worked as expected, so I'd suggest that you give it a try to solving the Elasticsearch issue and try again with the purge task.

You can get some information about the Elasticsearch status making a request to it from the AtoM server (change the host and port accordingly):

curl -XGET 'localhost:9200/?pretty'

Best,

Radda.

[Helen]

Hello - thanks for getting back to me - I drafted a reply, but got distracted and never posted it!

I think the additional user in that log is just the server admin trying to access the database to record the problem - it was a test database set up just for this install attempt.

Throughout all this the status details from ElasticSearch have been green/'200' - it looks like ElasticSearch is fine, but AtoM is failing to reach it. I went through various attempts to run the installation process, and did manage to avoid the database lock, but this just takes us back to the 'no enabled connection' errors for the search index. Our webhosts gave me the attached log from the server indicating what they were seeing happen when this kicked in - again, I have no idea if this will shine any light on it...