[Netacct-mysql-users] Problems with oversized values

[Peter M. <Pet...@tu...>]

Heyaaa!!!

i test netacct-mysql on local network, but i have problem with oversized
values:

--[ 1 ]--

i changed sql types:
ALTER TABLE `traffic` CHANGE `input` `input` BIGINT( 20 ) UNSIGNED DEFAULT '0' NOT NULL
(same thing for output, local_input, local_output, ...)

--[ 2 ]--

i tried change some "c" code in src/mysql.c:

---------------------------------------8<--------------------------------------------------------

--- mysql.c	2003-03-17 13:17:08.000000000 +0100
+++ netacct-mysql-0.76/src/mysql.c	2004-04-14 15:58:33.000000000 +0200
@@ -43,8 +43,8 @@
         MYSQL_ROW r;
         my_ulonglong row;
         char query[8192];
-        unsigned long int old_input, old_output, old_peer_input, old_peer_output;
-        unsigned long int old_direct_input, old_direct_output, old_local_input, old_local_output;
+        unsigned long long int old_input, old_output, old_peer_input, old_peer_output;
+        unsigned long long int old_direct_input, old_direct_output, old_local_input, old_local_output;
         int rc;
         char TIME_MASK[] = "DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00')";
 
@@ -61,14 +61,14 @@
             {
                 r = mysql_fetch_row(result); /* get query result */
                      {
-                        old_input = atol(r[0]);
-                        old_output = atol(r[1]);
-                        old_peer_input = atol(r[2]);
-                        old_peer_output = atol(r[3]);
-                        old_direct_input = atol(r[4]);
-                        old_direct_output = atol(r[5]);
-                        old_local_input = atol(r[6]);
-                        old_local_output = atol(r[7]);
+                        old_input = atoll(r[0]);
+                        old_output = atoll(r[1]);
+                        old_peer_input = atoll(r[2]);
+                        old_peer_output = atoll(r[3]);
+                        old_direct_input = atoll(r[4]);
+                        old_direct_output = atoll(r[5]);
+                        old_local_input = atoll(r[6]);
+                        old_local_output = atoll(r[7]);
                      }
 
                      old_input+=(tmpData->nPeerFlag==0)?tmpData->nInTrafic:0;
@@ -81,11 +81,11 @@
                      old_local_output+=(tmpData->nPeerFlag==3)?tmpData->nOutTrafic:0;
 
 
-                /* syslog(LOG_INFO,"last record is - %li",old_trafic); */
-                /* syslog(LOG_INFO,"sum = %li",old_trafic); */
+                /* syslog(LOG_INFO,"last record is - %Lu",old_trafic); */
+                /* syslog(LOG_INFO,"sum = %Lu",old_trafic); */
 
                 DEBUG(DBG_STATE,sprintf(dbg,"upate info for ip %s, peer_flag=%u\n",spyip,tmpData->nPeerFlag));
-                sprintf(query,"UPDATE %s SET input='%li', output='%li', peer_input='%li', peer_output='%li', direct_input='%li', direct_output='%li', local_input='%li',local_output='%li' WHERE ip='%s' AND time=%s", "traffic", old_input,old_output, old_peer_input, old_peer_output,old_direct_input, old_direct_output,old_local_input,old_local_output, spyip,TIME_MASK);
+                sprintf(query,"UPDATE %s SET input='%Lu', output='%Lu', peer_input='%Lu', peer_output='%Lu', direct_input='%Lu', direct_output='%Lu', local_input='%Lu',local_output='%Lu' WHERE ip='%s' AND time=%s", "traffic", old_input,old_output, old_peer_input, old_peer_output,old_direct_input, old_direct_output,old_local_input,old_local_output, spyip,TIME_MASK);
 
 
 
@@ -104,7 +104,7 @@
 
                 DEBUG(DBG_STATE,sprintf(dbg,"upate info for ip %s\n",spyip));
 
-        sprintf(query,"INSERT INTO %s (ip,time, input,output,peer_input,peer_output,direct_input,direct_output,local_input,local_output) VALUES ( '%s',%s,'%li','%li','%li','%li','%li','%li','%li','%li')", "traffic", spyip, TIME_MASK, old_input,old_output,old_peer_input,old_peer_output,old_direct_input,old_direct_output, old_local_input,old_local_output);
+        sprintf(query,"INSERT INTO %s (ip,time, input,output,peer_input,peer_output,direct_input,direct_output,local_input,local_output) VALUES ( '%s',%s,'%Lu','%Lu','%Lu','%Lu','%Lu','%Lu','%Lu','%Lu')", "traffic", spyip, TIME_MASK, old_input,old_output,old_peer_input,old_peer_output,old_direct_input,old_direct_output, old_local_input,old_local_output);
 
 
 
@@ -143,7 +143,7 @@
 		return 0;
 	}
 
-//	syslog(LOG_DEBUG,"ip = %s, In = %li, Out = %li, peer = %i", intoa(tmpData->ipAddress), tmpData->nInTrafic, tmpData->nOutTrafic, tmpData->nPeerFlag);
+//	syslog(LOG_DEBUG,"ip = %s, In = %Lu, Out = %Lu, peer = %i", intoa(tmpData->ipAddress), tmpData->nInTrafic, tmpData->nOutTrafic, tmpData->nPeerFlag);
         /* and write it to mysql*/
 	if((tmpData->nInTrafic != 0) || (tmpData->nOutTrafic != 0))
 		write_mysql(tmpData, mysql);
@@ -152,7 +152,7 @@
         while(tmpData = (struct HOST_DATA*) GetNextHostData())
         {
 
-//		syslog(LOG_DEBUG,"ip = %s, In = %li, Out = %li, peer = %i", intoa(tmpData->ipAddress), tmpData->nInTrafic, tmpData->nOutTrafic, tmpData->nPeerFlag);
+//		syslog(LOG_DEBUG,"ip = %s, In = %Lu, Out = %Lu, peer = %i", intoa(tmpData->ipAddress), tmpData->nInTrafic, tmpData->nOutTrafic, tmpData->nPeerFlag);
 		i++;
                 /* and write data to mysql 
 		 * if IN and OUT traffic are not NULL 


---------------------------------------8<--------------------------------------------------------

but there are still some mistakes ... my changes are insufficient ;-((

help me, please! thanks

-- 

5o   Peter.Mann at tuke.sk
     KLFMANiK ICQ 12491471
         PM2185-RIPE