09:53 < glen> we have some lost emails from issues
10:58 < glen> FKKKKK
10:58 ... Irssi: #eventum: 2 nicks (@/0 +/0  -/0)
10:58 < glen> this is not cool anymore
10:58 < glen> no associated emails for issues
10:59 < glen> altho i see emails via mail queue log
11:20 < glen> shouldn't these be equal if i have just one project?
11:20 < glen> mysql> select count(*) from eventum_support_email;
11:20 < glen> +----------+
11:20 < glen> | count(*) |
11:20 < glen> +----------+
11:20 < glen> |     1199 |
11:20 < glen> +----------+
11:20 < glen> 1 row in set (0.00 sec)
11:20 < glen> mysql> select count(*) from eventum_support_email_body;
11:20 < glen> +----------+
11:20 < glen> | count(*) |
11:20 < glen> +----------+
11:20 < glen> |     3366 |
11:20 < glen> +----------+
11:20 < glen> 1 row in set (0.00 sec)
11:20 ... Irssi: #eventum: 2 nicks (@/0 +/0  -/0)
11:26 < glen> ah. lost email account_id
11:31 < glen> appears i killed some old unused imap account
11:32 < glen> but a lot of routed emails were associated to it with id in route-email.php parameter 1
12:08 < glen> restoring eventum_email_account table, is that only data i need?
12:08 < glen> btw, that delete account should had warned, that deleting account could desynchronize existing issues ;()
13:19 < glen> replayed the delete action
13:19 < glen> DELETE FROM eventum.eventum_email_account WHERE ema_id IN (2);
13:19 < glen> DELETE FROM eventum.eventum_support_email WHERE sup_ema_id IN (2);
13:19 < glen> so i've lost all emails with the deleted id!
13:22 < glen> but i believe the email bodies are still there
13:22 ... Irssi: #eventum: 2 nicks (@/0 +/0  -/0)
13:30 < glen> strange is that even i deleted account with id=1, the new mail was routed in, without any errors
13:30 < glen> mysql> select count(*) from eventum_support_email WHERE sup_ema_id IN (1);
13:30 < glen> +----------+
13:30 < glen> | count(*) |
13:30 < glen> +----------+
13:30 < glen> |        3 |
13:30 < glen> +----------+
13:39 < glen> how can i recreate eventum_support_email from eventum_support_email_body?
13:39 < glen> restored yesterdays backup and it has:
13:39 < glen> mysql> select count(*) from eventum_support_email_body;
13:40 < glen> |     3322 |
13:40 < glen> mysql> select count(*) from eventum_support_email;
13:40 < glen> |     3181 |
15:06 < glen> ok, waiting when you come online or sth
15:51 ... Irssi: #eventum: 2 nicks (@/0 +/0  -/0)
16:03 < lordrashmi> morning glen, reading backlog
16:07 < glen> thank god :)
16:07 < glen> i'm on lunch acutally
16:07 < glen> but would be nice if you say is it possible to rebuild eventum_support_email from eventum_support_email_body
16:07 < lordrashmi> yes it is, but it will require a script
16:07 < glen> and even better some hints or scripts to do it, i have just one project  active, all mails can go there
16:08 < lordrashmi> you use mail routing, right?
16:14 < glen> yes
16:15 < glen> and i really don't see why routed mails are tied with imap account
16:17 * lordrashmi points at jpm
16:18 < lordrashmi> Ok, you will need to write a script that loops through the email bodies, and parses it similar to the code found in the routing class
16:19 < lordrashmi> you will need to parse the information found in support_email out issue, parent_id, customer, message_id, date, from, to, subject, if they have attachments, etc
16:23 < glen> can u do it? :)
16:25 < lordrashmi> can yes, will no ;)
16:26 < glen> thank you very much
16:26 < glen> do do do
16:27 < lordrashmi> glen: Did you notice the "will no", or did that not come across clear?
16:35 < lordrashmi> I will be glad to give you guidance on this though
16:43 < glen> i used the typical "manager reading" :)
16:43 < glen> ie did read only first part, hehe
16:45 < glen> oeah, i'm feed up :)
16:46 < glen> but looks like have to go back to office and fix those damn mails delinkage
16:46 < lordrashmi> :( Sorry
16:49 < glen> but is that routed mails linked with imap account reasoned?
16:50 < glen> and i think that screen should warn with like "warning, deleting this account will delete XXX emails associated with issues. are you sure you want to continue"
16:50 < glen> because i really didn't think deleting account would delete some emails...
16:51 < glen> and the second fault was that that account was never used in live, it was used only for testing
16:51 < glen> but my routing script used hardcoded id=1 ;(
16:52 < glen> ok, back in 15 minutes
17:04 < lordrashmi> glen: Will respond to your comments then
17:06 < glen> in office now
17:06 < glen> what i liked in eventum that i was able to include some portion into php script and see it already working
17:07 < glen> when i did debug it earlier
17:07 < glen> like this: http://rafb.net/paste/results/LwsHer92.html
17:08 < glen> lordrashmi: to which account emails sent from eventum are tied with?
17:08 < glen> because those didn't dissapear, and the ones that were auto-created from email
17:10 < lordrashmi> I believe they just choose the first email account in the project
17:11 < glen> is the email account reference neccessary?
17:11 < lordrashmi> Currently, yes. In the grand scheme of things it shouldn't be. That is something we need to change
17:12 < lordrashmi> But currently if there is no email account reference, they don't show up since we inner join that table instead of left join
17:12 * lordrashmi blames jpm again
17:13 < glen> a, btw i did found bug in your FULLTEXT code, did jpm pass that information (you weren't in irc at that time)
17:13 < lordrashmi> A bug? NONSENSE!!!!
17:13 < lordrashmi> ;)
17:13 < lordrashmi> no he didn't
17:13 < glen> well i'm not the only one :)
17:14 < glen> somebody reported same thing into eventum-users
17:15 < lordrashmi> What problem? I have someone sayign they can't get it to work, but they never responded when we asked what indexes they had
17:15 < glen> offtopic patch: http://cvs.pld-linux.org/cgi-bin/cvsweb/SOURCES/eventum-upgrade-exit.patch
17:15 < glen> (digging irc log)
17:17 < glen> http://rafb.net/paste/results/dVZPkf43.html
17:18 < lordrashmi> bleh
17:22 ... jpm_ is (Joao Prado Maia)
17:31 < glen> is there way to iterate over db_api recordset?
17:32 < glen> i don't want to getAll for whole table
17:32 < lordrashmi> yeah there is... I forget how though.
17:32 < glen> but i cant find any code which uses more than one row (lots of getOne uses)
17:33 < lordrashmi> http://pear.php.net/manual/en/package.database.db.db-common.query.php
17:34 < lordrashmi> then http://pear.php.net/manual/en/package.database.db.db-result.fetchrow.php
17:39 < glen> do i need to call $return = Routing::route_emails($full_message, $email_account_id);
17:39 < glen> or that will duplicate things?
17:39 < jpm_> duplicate what?
17:39 < glen> jpm_: i'm trying to recover ventum_support_email from eventum_support_email_body
17:40 < glen> jpm_: some shit happened here
17:40 < jpm_> don't know what you mean
17:40 < lordrashmi> glen: That will duplicate things, you need to just use selective bits of code from there
17:40 < glen> lordrashmi: ok
17:40 < glen> jpm_: wait i'll paste backlog
17:40 < jpm_> ohh
17:40 < jpm_> recover the table?
17:40 < glen> yes
17:40 < jpm_> because it got corrupted or something?
17:40 < jpm_> yikes
17:41 < lordrashmi> jpm_: It didn't get corrupted, but he didn't realize routed emails were tied to the first email account and when he deleted the account it would delete all the messages
17:41 < lordrashmi> (we need to add a warning)
17:42 < lordrashmi> but luckily YOUR code is broken and only deletes from eventum_support, not eventum_support_body
17:42 < glen> jpm_: http://rafb.net/paste/results/3KrGb533.html
17:42 < jpm_> so the real bug is that it didn't delete from support_email_body ;)
17:42 < lordrashmi> we SHOULD add a warning though
17:42 < jpm_> yeah, the include_once thing was applied
17:42 < lordrashmi> that is a nasty surprise
17:42 < jpm_> yeah, of course
17:43 < lordrashmi> I think it should actually state the # of emails it will delete, so they know it isn't an empty account
17:43 < lordrashmi> brb
17:45 < glen> ah yes, there's other bug too, that i was able to route to account id which didn't exist
17:45 < jpm_> right
17:45 < glen> jpm_: thank you for your bug, that you didn't delete _bodies :)
17:45 < jpm_> oh, yer very welcome ;)
18:09 < glen> what's the magic cookie?
18:09 < glen>         // check for magic cookie
18:09 < glen>         if (Mail_API::hasMagicCookie($full_message)) {
18:12 < jpm_> heh
18:14 < glen> shit, this is not easy one ;(
18:14 < lordrashmi> glen: where are you getting stuck?



18:15 < glen> lordrashmi: duplicating class.routing
18:15 < lordrashmi> glen: heh, sorry :(
18:15 < glen> the route_emails method
18:16 < glen> because i don't much understand what is needing
18:16 < glen> needed
18:16 < glen> looks like i have duplicate insertEmail() too
18:16 < glen> as that's what's actually filling data, but it also fills the _body
18:18 < glen> do i need to do anything below         $res = Support::insertEmail($t, $structure);
18:18 < glen> ?
18:19 < glen> ah, looks like no, that function ends after that, huh!
18:19 < lordrashmi> No, I don't think you need to do anything after that (like extract attachments since they should already be there)
18:20 < glen> i should be additionally checking if the data is already there?
18:21 < lordrashmi> which data? Attachments? Or the email record?
18:21 < glen> email
18:22 < glen> i mean if i parse all rows from _body table, i should skip ones that have data in emails table
18:22 < lordrashmi> correct
18:22 < lordrashmi> just look at the seb_sup_id
18:25 < jpm_> isn't it easier to just create .txt files for each of the missing emails
18:25 < jpm_> and then piping them to route_emails.php ?
18:26 < lordrashmi> jpm_: That will re-notify everyone, create duplicate attachments, etc
18:27 < jpm_> ah yeah
18:27 < glen> this you will do?
18:27 < glen> select sup_id,seb_sup_id from eventum_support_email_body left join eventum_support_email on seb_sup_id=sup_id limit 10;
18:28 < glen> mysql> select count(*) from eventum_support_email_body left join eventum_support_email on seb_sup_id=sup_id where isnull(sup_id);
18:28 < glen> hmm, returns only 144, should be 2000
18:29 < lordrashmi> glen: On an unrelated topic, what version of MySQL are you running? I can't reproduce your full text bug
18:30 < glen> 4.1.x
18:30 < lordrashmi> please don't tell me 4.1 behaves different from 4.0...
18:31 < glen> and 3.23 behaves differently than 4.0
18:31 < glen> i got also hit by the latter one, since in 3.23 the varchar indexes were case insensitive
18:31 < lordrashmi> I am going to shoot some people at our next devcon...
18:31 < glen> and then supprise, case sensitive in 4.0, had to change all colums to 'binary varchar(xxx)'
18:33 < glen> ok, restored from live db, apparently i already fixed something :)
18:33 < glen> mysql> select count(*) from eventum_support_email_body left join eventum_support_email on seb_sup_id=sup_id where isnull(sup_id);
18:33 < glen> +----------+
18:33 < glen> | count(*) |
18:33 < glen> +----------+
18:33 < glen> |     2170 |
18:33 < glen> +----------+
18:33 < glen> 1 row in set (0.05 sec)
18:35 < glen> first run :)
18:35 < glen> failed...
18:35 < glen> 18:35:16 root[4]@wintersunset share/eventum# ./refill-support_email.php
18:35 < glen> Error: The routed email had no associated Eventum issue ID or had an invalid recipient address.
18:37 < glen> http://glen.alkohol.ee/pld/eventum-refill-support_email.php.txt
18:38 < glen> that's the script i run, plz loooook
18:41 < glen> a problem
18:41 < glen> if the original email was the one that autocreated the issue
18:41 < glen> there's no way to detect to which issue it did go
18:42 < glen> ?
18:43 < lordrashmi> looking...
18:43 < glen> because when i copied the code,. noticed it tries to fiture out issue id from to/cc headers
18:44 < lordrashmi> didn't you have a backup of the live server or something?
18:44 < glen> it's 1 day old
18:45 < lordrashmi> the only thing I can tell you is try to guess the issue ID based on reporter and subject
18:45 < glen> because the imap account deletion was done just minutes before fresh backup was made ;(
18:45 < glen> but if i look mail queue log in eventum interface
18:45 < glen> i see the emails
18:45 < lordrashmi> glen: Well you can restore that to a different account that and try to get most of the old emails from that
18:46 < lordrashmi> ah, mail queue might be one way to recover it, but it would just be a matter of 'guessing' right as well
18:48 < glen> why
18:48 < glen> if issue_id can see maq mails, then theres no link with maq mails and sup_emails?
18:55 ... dsias is (Dustin Sias)
18:56 < glen> lordrashmi: so it's useless?
18:56 < glen> to try recreating it, and should instead restore?
18:57 < lordrashmi> glen: Yeah, no link between maq mails and sup emails. Well there is now but I ahven't merged the change yet (and it wouldn't help with older emails)
18:58 < glen> lordrashmi: can i see how many "bad" emails i have (to give report of this issue)
18:58 < lordrashmi> I would restore what you can, and then the rest should be small enough to fix manually
18:59 < glen> manually you mean like?
18:59 < lordrashmi> glen: By 'bad' you mean issues that were auto created so they can't be regenerated?
18:59 < glen> i mean issues that are not linked to any issue
18:59 < glen> blah
18:59 < lordrashmi> manually means for the issues that were auto created, looking at the subject compared to titles of issues that were created recently
18:59 < glen> i mean emails that are not linked to any issue
19:12 < lordrashmi> glen: I don't think you can write a report on it, you just have to import them as is
19:14 < jpm_> i think it's easier just to import them without a sup_iss_id mapping, and then set those values manually
19:14 < jpm_> it's only like 20 emails missing, right?
19:20 < glen> 165
19:21 < glen> jpm_: um, so they then appear in associate emails section?
19:21 < jpm_> yes
19:21 < jpm_> but i'm talking about doing this manually on the database
19:21 < jpm_> don't use eventum for this, or you will get duplicated content
19:22 < jpm_> like attachments, notifications and etc
19:22 < glen> i should drop that i've already done?
19:22 < glen> i mean this script: eventum-refill-support_email.php.txt
19:23 < glen> somewhy all to and cc rows are missing
19:23 < glen> *headers
19:23 < jpm_> well, i think you should simply create another script that parses the full_email column from the other table, and insert the email info on support_email
19:23 < jpm_> then go into the mysql client, do:
19:23 < jpm_> select sup_id, sup_subject from eventum_support_email;
19:23 < jpm_> err
19:23 < jpm_> select sup_id, sup_subject from eventum_support_email where sup_id > some_number;
19:24 < jpm_> and then update each row to associate it with the issue that is supposed to be associated with
19:24 < jpm_> that's quicker than fixing your current script, i think
19:24 < glen> what fields i definately need to fill?
19:24 < jpm_> can't remember, look at setup/schema.sql
19:30 < glen> what's the magic cookie still
19:33 < jpm_> it's a way to work around the email blocking checks
19:34 < jpm_> which is a stupid thing we added internally, and i guess i forgot to remove it when pushing to the gpl repository
19:36 < glen> so if it get no cookie in the code, that means that mail should have been blocked?
19:38 < jpm_> no, it just goes on with the normal email blocking algorythm
19:42 < glen> i run the script, the mail is what was sent out by eventum
19:42 < glen> and i get no cookie error
19:44 < glen> because the original code will call Note::insert() if that statement fails
19:44 < glen> mm, althjo not neccessarily...
19:45 < glen> oh, yes!
19:53 < glen> seems working, all id's found
19:53 < glen> but strange, second run also runs (it should return no rows as data is associated...)
19:57 < glen> ah, sup_id is not inserted
19:57 < glen> original code:
19:57 < glen>             $new_sup_id = $GLOBALS["db_api"]->get_last_insert_id();
19:59 < glen> now is cool.
19:59 < glen> second run does nothing
20:00 < glen> http://glen.alkohol.ee/pld/eventum-refill-support_email2.php.txt
20:02 ... quit/#eventum dsias!~dsias@adsl-068-153-207-210.sip.bct.bellsouth.net ("Leaving")
20:02 < glen> k. capture time
20:02 < glen> mysql> reset master;
20:02 < glen> Query OK, 0 rows affected (0.01 sec)
20:23 < glen> k. restoring live db then
20:26 < glen> kuradi nuss putsi raisk vittu (swearing in estonian)
20:26 < glen> good it's over now :)
20:26 ... Irssi: #eventum: 3 nicks (@/0 +/0  -/0)
20:26 < glen> thx for the (late)support :)
20:27 ... Irssi: #eventum: 3 nicks (@/0 +/0  -/0)
20:56 < lordrashmi> glen: So everything is working?
21:04 < glen> lordrashmi: i hope so
21:04 < glen> at least i finished recovery activities