Skip to content

Database Trickery hints and tips; combining user accounts, etc.

Jump to bottom Edit New page
Randy Fay edited this page Apr 6, 2015 · 17 revisions

Sometimes there are things that can be done through the database and no other way. These cannot be recommended because they can be a disaster. But here are some recipes.

Member deletes self in order to combine two accounts (or assign feedback to different user)

  • Find the old uid and the new uid. Get them right.
  • Assign old feedback nodes to the new user: UPDATE content_type_trust_referral SET field_member_i_trust_uid=new_uid WHERE field_member_i_trust_uid=old_uid;
  • Assign feedback they created to the new user using admin/content/VBO (find nodes posted by the old user, "Change the author of a post")
  • Consider reassigning comments (UPDATE comments SET uid=new_uid WHERE uid=old_uid)
  • Set the new user to the original join date with user/<uid>/admin

If the old user was deleted, all their created nodes will be set to anon. I was able to recover one of these by accessing db backup, getting a list of feedback nids they had created, then updating those nodes on the site with the new uid.

drush sqlq "select nid from node where uid=39675 and type='trust_referral';" >~/tmp/39675_nids.txt

update node set uid=83024 where nid in (22200, 25327, 26998, 28205, 36723, 38165, 41261, 45365, 45658, 46284, 49020, 49578, 52023, 52798, 53333, 53694, 58305, 60235, 63317, 72502, 72504, 73989, 74764, 75668, 78220, 78830, 79881);

Feedback is associated with rfay instead of the actual user

This is caused by the feedback view, which users the node_revisions table. If user 1 (rfay) updates feedback ( to edit it for some reason) it creates a new node_revisions table with rfay as the editor. From issue #208: https://github.com/rfay/Warmshowers.org/issues/208

Detect the issue with

SELECT nr.nid,nr.vid,nr.uid noderev_uid, n.uid node_uid,n.type FROM node_revisions nr, node n WHERE nr.nid=n.nid AND nr.vid=n.vid AND type="trust_referral" AND nr.uid <> n.uid AND n.uid <> 0;

Fix it with:

UPDATE node_revisions nr, node n SET nr.uid = n.uid WHERE n.type="trust_referral" AND nr.vid=n.vid AND n.nid=nr.nid AND nr.uid <> n.uid AND n.uid <> 0;

Query big days in user creation

select from_unixtime(created, '%Y-%m-%d') as reg_date, count(*) from users group by reg_date having count(*) > 20 order by count(*) desc limit 20

Recent counts:

select from_unixtime(created, '%Y-%m-%d') as reg_date, count(*) from users group by reg_date desc limit 40;

Total counts by week:

echo "select count(*), year(from_unixtime(created)) year, yearweek(from_unixtime(created)) yearweek 
from users group by yearweek order by yearweek;" | mysql warmshowers >/tmp/yearweek_report.txt

Feedback Queries

This query searches for the members in Nevada with the most feedback.

select count(*) count, uid, name from content_type_trust_referral tr right join users u on 
tr.field_member_i_trust_uid = u.uid inner join user_location l on u.uid=l.oid where l.country="us" and 
l.province="nv" group by u.uid order by count desc limit 20

Find users with most negative or neutral feedback, just change the selection to "positive" to get positive counts.

select count(*) count, w.fullname, l.country, CONCAT("https://www.warmshowers.org/user/", field_member_i_trust_uid) url from node n, content_type_trust_referral tr, users u, wsuser w, user_location l where n.nid=tr.nid and n.vid=tr.vid and field_member_i_trust_uid=w.uid and w.uid=l.oid and w.uid=u.uid and field_rating_value in ("neutral", "negative") and u.status and n.uid <> 0 and n.status group by u.uid order by count desc limit 20;

Responsiveness queries

Global Responsiveness

SElECT SUM(pi.is_new),COUNT(*),100*SUM(pi.is_new)/COUNT(*) unresponsive 
FROM pm_index pi, pm_message pm 
WHERE pm.mid=pi.mid AND pi.mid=pi.thread_id AND pi.recipient <> pm.author 
AND pm.timestamp > UNIX_TIMESTAMP('2014-01-01') 
AND pm.timestamp < UNIX_TIMESTAMP('2015-01-01');

Responsiveness with location awareness SElECT SUM(pi.is_new) Unresponded,COUNT(*) TotalMessages,100*SUM(pi.is_new)/COUNT(*) unresponsive FROM users u, user_location l, pm_index pi, pm_message pm WHERE u.uid = l.oid AND u.uid=pi.recipient AND l.province="ca" AND l.city="Los Angeles" ANd pm.mid=pi.mid AND pi.mid=pi.thread_id AND pm.timestamp > UNIX_TIMESTAMP('2014-04-04');

Member counts by country

(This is now in https://www.warmshowers.org/country_count)

echo "SELECT g.continent Continent, l.country Country, g.name, count(*) 'WS Members' from users u, wsuser w, user_location l, geonames_countryinfo g WHERE u.uid = l.oid AND u.uid = w.uid and l.country=g.iso_alpha2 AND u.status  AND ! w.isstale AND ! w.isunreachable group by g.continent, g.name;" | mysql warmshowers >~/tmp/member_counts.txt

Member counts by chosen language

select language,count(*) number from users group by language order by number desc;

Restore deleted private messages for a member

UPDATE pm_index pi SET deleted=0 WHERE pi.recipient=<UID>

Excluding a message from the responsiveness count

Find the thread_id (the end of the URL when you visit the thread) Find the recipient userid

You have to find the offending message somehow to use this of course. I used select *,from_unixtime(timestamp) from pm_index pi, pm_message pm where pm.mid=pi.mid and pi.mid=pi.thread_id and pi.recipient != pm.author and pi.recipient=USER_UID and pi.deleted <> 0 to find a deleted message that hadn't been responded to.

This relies on the magic of 'deleted' being set to '1'.

UPDATE pm_index pi SET deleted=1 WHERE pi.recipient=<uid> AND pi.thread_id=<thread_id>;

Private Message Statistics

pm_index is the thread, pm_message the message within the thread

SELECT COUNT(DISTINCT(pi.thread_id)) FROM pm_index pi, pm_message pm 
WHERE pi.mid=pm.mid AND pm.timestamp > UNIX_TIMESTAMP('2013-01-01') 
AND pm.timestamp < UNIX_TIMESTAMP('2014-01-01');

Fix up situation where somebody sends bad message to multiple users

drush sqlq "select mid from pm_message where mid in (select pm.mid from pm_index pi, pm_message pm where pi.thread_id in (954860,932513) and pi.mid=pm.mid);"

Turn that into comma-separated list, then delete the pm_message items

delete from pm_message where mid in (932513, 933236, 954860, 956578, 957041, 957050, 957055, 957056, 957344, 957374, 957376, 957383, 957385);

Then delete the pm_index threads:

delete from pm_index where thread_id in (954860,932513);

Import orders to donortools

SET group_concat_max_len = 2048; SELECT "order_id", "received on", "amount", "Legacy ID", "username", "email address", "whole name", "city", "state", "country", "memo" UNION SELECT o.order_id, FROM_UNIXTIME(o.created), o.order_total, u.uid, u.name, u.mail, w.fullname, l.city, l.province, l.country, CONCAT(IFNULL(c.message, ""), " ------ https://warmshowers.org/user/", u.uid, ", ", GROUP_CONCAT(uoac.message)) FROM uc_orders o LEFT JOIN uc_order_comments c on o.order_id = c.order_id, users u, wsuser w, user_location l, uc_order_admin_comments uoac WHERE o.order_status="completed" AND o.uid=u.uid AND o.uid=w.uid AND o.created >= UNIX_TIMESTAMP('2015-02-07 00:00') AND o.order_total > 0.2 AND o.uid=l.oid AND o.order_id = uoac.order_id AND o.order_id in (914, 879, 841, 749, 724, 688, 683, 633, 620, 603, 561, 548) GROUP BY o.order_id INTO OUTFILE '/tmp/orders_donortools.csv' CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

  1. Do this query, then bring down to local
  2. Upload to Google Docs as a spreadsheet (preserves UTF-8 and does many things better than Excel)
  3. Save down to local as .xslx
  4. Upload to donortools, matching the columns

#TODO: Responsiveness by geographic area

Add a custom footer
Add a custom sidebar
Clone this wiki locally