Hi Rhys > If you provide this as a bunch of CREATE TABLE & INSERT statements it'll be > easier for us to look at your problem. > > Couple of things... > > When you do.. > > MariaDB [maildb]> select mail_out_anon,mail_out_auth,timeslice from > domaincounters where domain_id=19 order by timeslice desc limit 24; > > The first row is... > > | 0 | 10 | 2015-06-22 16:00:00 | > > But when you do this... > > select mail_out_anon+mail_out_auth,timeslice from domaincounters where > domain_id=19 order by timeslice desc limit 24; > > You get ... > > | 19 | 2015-06-22 16:00:00 | > > What the discrepancy? Is this being updated in the meantime? Something is > going on here. I see nothing wrong with the SQL provided here to indicate > why the result would be wrong.
Yes, this is a live database with counters being constantly updated for the actual timeslice :-) > I think using a computed column here would simplify the semantics of your > query... > > https://mariadb.com/kb/en/mariadb/virtual-computed-columns/ Thank you, I'll look into this. > As I said provide some table create & insert statements and you'll get some > better asnwers. CREATE TABLE `domaincounters` ( `id` int(11) NOT NULL AUTO_INCREMENT, `domain_id` int(11) NOT NULL, `timeslice` datetime DEFAULT NULL, `bad_filename` int(11) DEFAULT '0', `ham` int(11) DEFAULT '0', `mail_in` int(11) DEFAULT '0', `mail_out_anon` int(11) DEFAULT '0', `mail_out_auth` int(11) DEFAULT '0', `out_bad_filename` int(11) DEFAULT '0', `skip` int(11) DEFAULT '0', `spam` int(11) DEFAULT '0', `virus` int(11) DEFAULT '0', `suspicious_chars` int(11) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `domain_id` (`domain_id`,`timeslice`) ) ENGINE=InnoDB AUTO_INCREMENT=1557693 DEFAULT CHARSET=utf8 The insert/update query looks like this: $query = "INSERT INTO domaincounters set timeslice = DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00'), $counter = $count, domain_id = " . $domainid->{'id'} . " ON DUPLICATE KEY UPDATE $counter = $counter + $count"; $counter is the column to be updated $count is the number to add to the counter Mit freundlichen Grüssen Benoit Panizzon -- I m p r o W a r e A G - ______________________________________________________ Zurlindenstrasse 29 Tel +41 61 826 93 07 CH-4133 Pratteln Fax +41 61 826 93 02 Schweiz Web http://www.imp.ch ______________________________________________________ _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
