open siddur emergency, requesting help

[Aharon Varady]

Friends,

Early Monday morning 3/18, my webhost made an unannounced (presumably necessary) upgrade of the RAM on the server hosting our site. Unfortunately for us, unexpected and unexplained issues arose and the wordpress site running opensiddur.org wasn't revived. 

At this moment, opensiddur.org presents the error: "Error establishing database connection." For some reason, the mysql database supporting the opensiddur's wordpress site couldn't be re-established. I've written repeatedly to the webhost support requesting updates. 

So far, after 24 hours, the support person responsible hasn't gotten back to me and so I'm appealing for your help in determining what the problem might be with the database. (Perhaps it can be fixed without any more of their intervention!)

I found the mysql database in a folder in my root directory named "cpmove_failed_mysql_dbs.1552902684." (The name of the folder I think provides a clue as to why the database hasn't been reconnected yet.)

I've made a copy of this db and put it into my dropbox. Please let me know if you would like to take a look at it, check it for errors, and/or repair it if necessary.

The most recent post in the database (in the table "wp_siddur_posts") should date to March 16th and the most recent modified post should date to March 16th or 17th. 

Thank you.

Aharon

--

Aharon Varady
Founding Director, shamesh
the Open Siddur Project
https://opensiddur.org

Twitter | Facebook

Pronouns: He/him/his

[Marc Stober]

Is it working now? It looks like the site is up.

Marc Stober - Contact Using Spike

--
You received this message because you are subscribed to the Google Groups "opensiddur-tech" group.
To unsubscribe from this group and stop receiving emails from it, send an email to opensiddur-te...@googlegroups.com.
To post to this group, send email to opensid...@googlegroups.com.
Visit this group at https://groups.google.com/group/opensiddur-tech.
For more options, visit https://groups.google.com/d/optout.

[Amanda J. Rush]

I just checked the site, and it appears to be working. There is a piece of advice I'd like to offer, and I hope I am not out of place.

Based on what is stated below, it appears as if the site is not being backed up regularly, or at least not being properly backed up before any hardware or software upgrades are taking place. I would recommend double-checking your host's back-up policies as they will handle that to a point, but will usually not perform daily or weekly backups and leave that to the user. If you're interested I'd be willing to host the Open Siddur WordPress site at no charge, since I benefit greatly from the project and have done so for years now. I provide twice-daily restore points as well as backup/restore on demand as a matter of policy since a lot of my clients are healthcare-related and so require extra efforts at data protection and security.

I still plan to contribute code-wise to the accessibility of the Open Siddur application itself. Time, however, up to this point has not permitted this. It's getting a bit less crowded though so I plan to clone the project this week and start working on it.

Amanda

[Efraim Feinstein]

Hi Amanda,

If you're interested in the app/coding side (app.opensiddur.org), let me know. I know I've been pretty quiet lately (work, kids, etc.) but there has been some activity. 

I made an attempt to use the app (kosher dogfooding?) and - even in the state it is in, which is mostly to serve me getting data in the db- it was quite difficult to prepare texts. 

The main pain point is a requirement that texts be segmented into small, meaningful units of a few words each so they could be aligned to translations. This requirement is quite difficult to implement in practice because it requires thinking about a text and how it may be used in the future. If even I consider it too much, anyone else using it certainly would. I'm in the process of removing that requirement from the back end.

I also set up a CI/CD system on the database, which makes testing and deployment much easier and a CD system on the app (the app currently has no automated testing, and it's pretty obvious).

[Aharon Varady]

Unfortunately, the site is still down. 

If it was up briefly, that is interesting as I went to sleep uploading the db I had to the cPanel in the hope that I could do a db check and restore there. The good news is I can see the db in phpMyAdmin and that it contains all the latest revisions and posts. The bad news is whatever is keeping that db from being stably reconnected to my account through the cPanel. 

If you do see that the site is up, please note for me what is the most recent post. The webhost has their own backup but I'm afraid it dates all the way back to February 17th!

Amanda -- to answer your question: thank you for offering! I'd like to pursue this idea further. I'm also overjoyed to read that the project remains useful for you.

re: backups. I was making regular server-side complete account backups and these all disappeared without a trace. I had been receiving locally mysql database backups too, but I began to notice they were unreliable a few weeks ago. I contacted the webhost and (big red flag) recommended I make the enormous account backups (5gb zip and tarred) instead which would contain the mysql backups. 

The real solution is to use a more reliable webhost. This has long been a "devil you know vs the devil you don't" sort of decision for me -- thank you for making it easier.

--

Aharon Varady, M.C.P., M.A.J.Ed.
Community Planner, Educator

Pronouns: He/him/his

[Aharon Varady]

I've made some progress this afternoon.

Previously, the mysql database contained tables for my three sites all together. This was mainly a legacy of my account being over 20 years old, and once upon a time, it was actually a hassle to get a new mysql database. So I only had one database to work with. 

Now, each site has its own dedicated database. And the two other sites, my personal blog at aharon.varady.net/omphalos, and dimus.parrhesia press -- are back online.

I'm using a similar strategy for opensiddur.org -- I might run into a problem given the decompressed size of the SQL file is 265mb. There is I think a 50mb max upload limit for importing via phpMyAdmin so I might need to figure out another way to do this. 

In the process of reviving the other two sites, I think I stumbled upon the reason for the problem in the first place. Previously, the database had it's own password for access with the default "user" being the database name itself. This configuration didn't work at first, but when I created a dedicated user with its own password providing access to the DB everything went swimmingly.

tl;dr -- the site is still down but I have some confidence that it will be up soon-ish.


Aharon

[Aharon Varady]

The plan worked almost perfectly. 

After noticing some oddities on opensiddur.org's backend, I checked the database and discovered some tables are missing. 

B"h, I have backups from which I hope to scavenge the missing tables and import them to the database. 

For now the site is up, although it's already getting hit pretty hard by Purim traffic. 

[Amanda J. Rush]

Re: database upload limit via phpMyAdmin, you can try asking your host to temporarily increase it to allow you to upload. If that doesn't worok, (sometimes they will refuse to do this, and I do unless there is a very specific reason since on my host the limit is 500MB), they should be willing to take a zip of the database and import it using the ClI. Re: DB user and specific password for that user: this is how that user/password combo should be managed, plus granting it all necessary privileges for the database. You'll also want to redo your salts and hashes for the wp-config file if you haven't done so already, since essentially this is a new database/databases for each site. And yes, separating the sites into their own databases is a good idea. while you can have more than one WordPress site installed in a single database, it's kind of like WordPress Multisite: Rule number one is don't do it unless there is a really good reason because it's going to end up creating a lot more work.

Amanda

[Aharon Varady]

All the tables are back and things should be working properly but they are not. 

The main issue is that users with editing privileges (such as Administrators) are not able to create new posts. (There may be other issues but this one is the most obvious to me.) 

I had previously thought the issue was related to missing tables. The problem remains though.

I continued to google for information. The most relevant and similar issue appears to be reported at this question/answer on stackoverflow:

https://stackoverflow.com/questions/41486220/unable-to-edit-remove-new-users-unable-to-add-images-in-media-library-error/51534067

I hope this leads to a solution without my having to rely on a tech support which can only be described politely as disappointing.

Aharon

P.S. A thank you to Amanda for reading and responding thoughtfully to these messages.

[Amanda J. Rush]

Regarding your admin users:

Does the installation still have them listed as admin/all capabilities, or have those been changed? The easiest way to check this is to look within the "users" section of the admin panel. You, as an admin user, should be able to restore their roles if necessary. If you set up a new WordPress install, and you don't check the default role of new users before importing the database, their role will be set to the default role for new users, which is subscriber out-of-the-box. YOu'll also want to make sure that each user is resent a password reset message, as WordPress does not store passwords in plain text, so there is no way for passwords to be imported/preserved.

Amanda

[Aharon Varady]

It really doesn't appear to be an issue so much with the users system in Wordpress. Rather, it appears to be an issue with the database. sigh.

Searching online, I found others reporting the same weirdness. Alas, all efforts to solve this so far have come to nothing.

[Amanda J. Rush]

Can you check your prefix_usermeta table, specifically the wp_capabilities meta key to ensure that the capabilities are correct for the users in question? This is painful but the final resort is to delete the users and then recreate them, which raises the problem of temporarily re-assigning their posts to another author and then assigning them back for each. Note that prefix is usually wp, but will be different if there is more than one WordPress instance using the DB, and out-of-box user roles are stored as serialized arrays in wp_usermeta under wp_capabilities. Example array: a:1:{s:13:"administrator";b:1;}

Amanda

[Aharon Varady]

On inspection,The user capabilities are correct. I also created a new user with Administrator privileges -- and they couldn't add new posts or pages.

No. There's something else amiss and I just can't solve it.

Others have reported similar issues and the suggestion is it has to do with "Auto Increment" in mysql database tables.

For example, this stackoverflow question:

https://stackoverflow.com/questions/41486220/unable-to-edit-remove-new-users-unable-to-add-images-in-media-library-error/51534067

and here:

http://www.manojkshegde.com/2018/09/28/while-creating-new-post-error-you-are-currently-editing-the-page-that-shows-your-latest-posts/

also here:
https://wordpress.org/support/topic/cant-add-new-post-or-page-after-host-move/page/2/

But no luck so far actually fixing this problem.

Aharon

[Aharon Varady]

I feel like I might be narrowing in on the problem. Still no solution though.

More helpful links:

https://wordpress.stackexchange.com/questions/225641/wordpress-database-lost-auto-increment

https://www.photographerstechsupport.com/tutorials/fixing-wordpress-indexes-foreign-keys-and-auto_increment-fields/

Here's the wp_siddur_posts table. What is standing out to me is the first row with an id of 0 and dated to after (not before) the tables had been imported into the database. It seems to me that any new row should have an ID with a number that is greater than the latest post. But this row is dated 0.

(Image shows new row/record in the wp_siddur_posts table with an id of 0 and a post date of 2019-03-21 00:42:07).

The latest record from before this crisis has the following ID and post date:

ID: 24258

User: 131

Date: 2019-03-17 21:07:4

So I'm thinking that I might be on the right track that this is an issue with Auto-Increment not being set for the table(s) in the restored database, leading to this issue.

What bothers me about this diagnosis is that I'm not seeing any similar issue for my two other sites whose tables I exported and imported into new dedicated database.

Aharon

[Aharon Varady]

Confirmed that the auto-increment is not being set in the imported tables for the new opensiddur database. 

The solution to keeping the auto-increment setting retained on import appears to involve importing the structure and the data of the SQL tables separately (as user SinisterBeard reports at the following StackExchange post). 

https://wordpress.stackexchange.com/questions/263989/1067-invalid-default-value-for-post-date-when-trying-to-reset-ai-after-ba/264142#264142

So this is the plan now and I'll let everyone know whether this was successful or not before shabbes.

Aharon

[Aharon Varady]

Barukh Hashem, the site is up and fully functional again. 

Huzzah!

[Jonathan Robie]

Excellent!  Thanks for your hard work on this important resource, it's really appreciated.

Jonathan