Actually, no; those values don't exist.

mysql:research@s1-analytics-slave.eqiad.wmnet [staging]> DESCRIBE log.MobileWebClickTracking_5929948
    -> ;
+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| id                  | int(11)      | NO   | PRI | NULL    |       |
| uuid                | char(32)     | NO   | UNI | NULL    |       |
| clientIp            | varchar(191) | YES  |     | NULL    |       |
| clientValidated     | tinyint(1)   | YES  |     | NULL    |       |
| isTruncated         | tinyint(1)   | YES  |     | NULL    |       |
| timestamp           | varchar(14)  | YES  | MUL | NULL    |       |
| webHost             | varchar(191) | YES  |     | NULL    |       |
| wiki                | varchar(191) | YES  |     | NULL    |       |
| event_destination   | varchar(191) | YES  |     | NULL    |       |
| event_mobileMode    | varchar(191) | YES  |     | NULL    |       |
| event_name          | varchar(191) | YES  |     | NULL    |       |
| event_userEditCount | int(11)      | NO   |     | NULL    |       |
| event_username      | varchar(191) | NO   |     | NULL    |       |
| userAgent           | varchar(191) | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+

MediaWiki timestamps are stored as character strings, not numeric values, because it allows for convenient alpha-sorting. You need to be using timestamp BETWEEN '2014110100000' AND..., with quotes, rather than treating them as numeric values.

On 29 December 2014 at 20:21, Jon Katz <jkatz@wikimedia.org> wrote:
Hi,
This is not urgetn, but if anyone is interested in troubleshooting a (probably basic) problem I am having with mysql, I would appreciate it!  

I am trying to query the MobileWebClickTracking table, which is ginormous and keeps timing out or boiling my RAM  So Dario suggested I use screen to dump a section of the table into a more workable table.

Here is his test query that seemed to work:  

screen mysql -h analytics-store.eqiad.wmnet -B -e "CREATE TABLE staging.jkatz_foo SELECT * FROM enwiki.user LIMIT 300;"

I tried to do this on my own (I use analytics-slave instead, as my credentials don't seem to work on analytics-store), and it doesn't seem to do anything.  Can you try on your end and let me know if you're having any luck?

Here is the query:

jkatz@bast1001:~$ screen

jkatz@bast1001:~$ mysql -h analytics-slave.eqiad.wmnet -u research -pJoFjnA90Ajyp -B -e "Insert into staging.jkatz_clicktracking1 Select * from log.MobileWebClickTracking_5929948 WHERE ('timestamp' between 20141101000000 and 20141130000000) and wiki like 'enwiki';"

When I look in processes in stat1003, I only see a sleep command--no query:

Inline image 2

Is the query within that sleep?  Anyway, I seem to be creating tables but they do not have any rows in them.  I have double checked that the data between those dates exists in that table.

Any thoughts? 

Best,

J

_______________________________________________
Analytics mailing list
Analytics@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/analytics




--
Oliver Keyes
Research Analyst
Wikimedia Foundation