|
faeger
|
March 8, 2018 at 12:14 pm
Hi there
We got a serious problem that needs to be solved ASAP – we are currently selling tickets to over 30’000 people (approximately 1’000 tickets per day).
The following should occur:
– user logs in and goes to his front-end-dashboard where he can see his registrations
The following occurs instead:
– only users with an account that has been created after migrating old wp_users/wp_usermeta-tables see their registrations
– users that created their account last year don’t see anything
– IMPORTANT: everything else works as expected, the booking works, the registrations go through and everything, the only thing that is not working is the above described
We migrated old user-data (wp_users/wp_usermeta) from the old website like that:
– phpMyAdmin -> export -> table wp_users and table wp_usermeta from old website/db
– phpyMyAdmin -> drop both tables wp_users/wp_usermeta on new website/db
– phpMyAdmin -> import -> both tables wp_users/wp_usermeta from old website/db
From our POV it must have something to do with wrong migration or tables that aren’t joined the right way or somethings broken ID-wise or so… we really struggle here as we cannot understand why everything else should work for both types of accounts (new/old ones) …
We need help ASAP as people are calling us all the time asking why they don’t see their registrations and therefore book twice and three times just to be sure … it’s crazy! BTW: they get the messages from EE.
|
|
Josh
|
March 8, 2018 at 12:23 pm
Hi there,
The table that got overlooked when doing the migration was the wp_posts table. All of the posts that have the post type espresso_attendees should also have been migrated over.
So right now if you look at the migrated data in the wp_usermeta table, you’ll see rows with a wp_EE_Attendee_ID meta key. The meta_value in those rows each point to a post ID in the wp_posts table, and those posts are missing (or are new posts that aren’t related in any way).
|
|
faeger
|
March 8, 2018 at 12:33 pm
Hi Josh
Thanks for the fast response – how can I fix this? I would still have access to the old database… but is it possible to fix this without loosing the today made registrations (over 1’000) …?
Thanks
Philip
|
|
Josh
|
March 8, 2018 at 12:40 pm
Hi Philip,
If they’ve made a registration, then you’ll need to change the wp_EE_Attendee_ID to point to their new contact record ID. e.g. if their new contact ID is 12345, then you update the meta value in wp_usermeta to be 12345.
If they haven’t made a registration on the site yet, you can copy over the related espresso_attendees post from the original site’s wp_post table. You’ll need to be sure there isn’t already a post with that same post ID on the new site.
|
|
faeger
|
March 8, 2018 at 12:44 pm
Ok, thanks Josh. If I understand you correctly I can do the following:
1. As we already have a lot of registrations, we can’t import anything as this would overwrite newer data.
2. We therefore go ahead and overwrite every single wp_EE_Attendee_ID …?
Honestly this sounds kind of scary :O
Would there be a solution like comparing the two tables and only update those with account-registration below 2018?
Would it be possible to let you guys do it? We are not that experienced (obviously) in manipulating databases… we would of course pay the needed amount.
Thanks
Philip
|
|
Josh
|
March 8, 2018 at 1:19 pm
Hi Philip,
Database migrations and altering existing databases isn’t something we can take on at the moment.
You can check first to see if importing the existing data from wp_posts would be an issue. If the wp_posts with post_type espresso_attendees from the original site have IDs that aren’t present in the new site, that data can be imported.
For any that aren’t covered by the above (i.e. they’ve registered for an event just now) instead of importing data, you’ll be updating their wp_usermeta record (specifically the meta_value for rows where the meta_key is wp_EE_Attendee_ID) to make the user account match the Event Espresso contact record. As it stands right now there may not be that many that need to be updated.
|
|
faeger
|
March 8, 2018 at 1:22 pm
Ok, thanks. I will have a look at that.
What I am still not sure of is which record you mean by EE contact record? Where can I find that value?
Thanks
Philip
|
|
Josh
|
March 8, 2018 at 1:43 pm
Those are in the wp_posts table. Any row in that table where the post_type is espresso_attendees is an Event Espresso contact record. Then, the meta data for those posts are found in wp_esp_attendee_meta.
|
|
faeger
|
March 8, 2018 at 2:15 pm
Ok, Josh.
Can you confirm the following to be the best practice?
1. I shutdown the site.
2. I backup the db.
3. I export the new espresso_attendees table.
4. I drop the new espresso_attendees table.
5. I import the old espresso_attendees table.
6. I don’t loose any registrations whatsoever in doing so.
7. I import those 300-ish from the new espresso_attendees table and look for conflicts.
8. In doing so I leave the wp_users/wp_usermeta and wp_EE_attendees_meta table as it is right now.
Thanks
Philip
|
|
Josh
|
March 8, 2018 at 2:35 pm
Hi Phillip, items one and two are good, but there isn’t actually an espresso_attendees table. It’s the wp_posts table and you’re not going to export that entire table, or drop it or import it.
You’ll need to first compare the wp_posts tables on both sites. On the original site you can do a query where you only get the posts with post_type = espresso_attendees, then compare the post IDs you get from the original to the post IDs to the target/new site. If the new site doesn’t have post_IDs that match, then you can import those rows (Do not drop any tables!)
|
|
faeger
|
March 8, 2018 at 2:42 pm
Ok, Josh.
Can I try and import the wp_posts table with only the matching espresso_attendees in it (phpMyAdmin -> search -> export search result) and see, wether it throws an error?
Or would it import anyway without looking for duplicates? And, in this case, the only thing left to do would be to compare the tables manually?
Thanks
Philip
|
|
Josh
|
March 8, 2018 at 2:49 pm
Hi Philip,
You’ll need to compare the data before you import and make sure the data in the target site doesn’t have a post_ID that matches the data you’re going to import.
|
|
faeger
|
March 8, 2018 at 2:56 pm
Ok, Josh.
I just did that with DiffMerge (App on Mac).
1. The attached image shows the app diffmerge where I see both the old and the new wp_post table …
Is the following true?
2. When the very first number/ID in both tables (left and right in the splitted app-window) don’t match at all, I am safe importing the old table and phpMyAdmin will expand the new one with the old records, so that I then would have both sets of records in the db.
3. I would then need to update only the usermeta-key of those who made a registration today?
Screenshot
Thanks
Philip
|
|
faeger
|
March 8, 2018 at 2:58 pm
… do I have to check for the usermeta at all? From my POV the db would contain records that don’t point to anything and wouldn’t do no harm, right?
The newly created accounts would still work as their records have been made today and the old ones would work as the old set has been imported, right?
|
|
Josh
|
March 8, 2018 at 3:17 pm
One issue with the database comparison is you need to compare all of the new site’s table. Not just posts of the same post_status.
e.g. the original site has an espresso_attendee post with ID 1806, does the target site have a post with ID 1806 of any post type?
do I have to check for the usermeta at all? From my POV the db would contain records that don’t point to anything and wouldn’t do no harm, right?
The only harm would be someone would log into their WP User account and not see the registrations they made earlier.
The newly created accounts would still work as their records have been made today and the old ones would work as the old set has been imported, right?
If they have a newly created WP user account and they log into that account, yes. If they have a newly created WP user account and also an old account (from what got migrated) if they log into the old account they will not see the registration they made earlier.
|
|
faeger
|
March 8, 2018 at 3:20 pm
Ok.
I will check again for duplicates in post ID’s …
What confuses me is the wp_esp_attendee_meta table … can I leave that table on the new db as it is?
|
|
faeger
|
March 8, 2018 at 3:27 pm
Ok – unfortunately there are a lot of post ID’s that exist in both tables. What can I do now?
|
|
faeger
|
March 8, 2018 at 3:33 pm
From my POV the only thing left to try would be to manipulate all 2750-ish records where the meta-value of wp_EE_attendee_ID is wrong, right?
If you can offer any help here I’d appreciate it. I’m literally having a living nightmare over here 🙁
As I stated before I could pay for this to be fixed as I’m really not seeing how I could get this fixed within a short timeframe without having another heartattack …
|
|
faeger
|
March 8, 2018 at 3:53 pm
I managed to manually manipulate one entry – could there be a SQL-like solution that does the following…
1. in wp_users -> look for user_id where name is “XXX”
2. then: in wp_usermeta -> look for meta-value for wp_EE_attendee_ID for that user_id
3. then: in wp_posts look for post-id where post-title is “XXX” (in most cases it’s fname-lname)
4. then in wp_usermeta change the wp_EE_attendee_ID to the found post-id
Would that be possible? And if so, could you provide me with such SQL-code? The only thing I would have to do then is to manually enter every user-name each time I’m running the query …
What are your thoughts on that?
Thanks
Philip
|
|
faeger
|
March 8, 2018 at 3:59 pm
… I meant post-name, not post-title
|
|
Josh
|
March 8, 2018 at 4:01 pm
Hi Philip,
I’m afraid that writing SQL-code is a outside of my wheelhouse. I can point you to this solution that uses PHP though:
https://gist.github.com/joshfeck/2165ff295c968f861cef8775fc8b1dc9
|
|
faeger
|
March 8, 2018 at 4:10 pm
Thanks Josh
I tried and had no luck as the “run” button doesn’t seem to trigger anything … Any idea?
|
|
Josh
|
March 8, 2018 at 4:28 pm
It should update the meta value for the specified user. In any case that code can be adapted and run from a utility file (load the file once).
|
