maria-discuss team mailing list archive

[Benoit Panizzon <benoit.panizzon@xxxxxx>]

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
______________________________________________________