Solved: Ошибка в базе данных

Kristian

В лог стали валится записи

?Debug &#58; Mar 21 13&#58;34&#58;06 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; timestamp 1332329646 slink_id 17388
?Debug &#58; Mar 21 13&#58;34&#58;06 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; slink_id 17388 tclass_id 10
?Debug &#58; Mar 21 13&#58;34&#58;06 09877c00 TrafficAggregator&#58; push_charge&#58; slink_id 17388 charge 0.000000
?Debug &#58; Mar 21 13&#58;34&#58;06 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; timestamp 1332329646 slink_id 17388
?Debug &#58; Mar 21 13&#58;34&#58;06 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; slink_id 17388 tclass_id 20
?Debug &#58; Mar 21 13&#58;34&#58;06 09877c00 TrafficAggregator&#58; push_charge&#58; slink_id 17388 charge 0.000000
?Debug &#58; Mar 21 13&#58;34&#58;06 09877a00 DBCtx&#58; <140979200> MySQL query failed&#58;<Duplicate entry '119719949' for key 1> Trying to reconnect&#58; 4
?Debug &#58; Mar 21 13&#58;34&#58;08 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; timestamp 1332329648 slink_id 13090
?Debug &#58; Mar 21 13&#58;34&#58;08 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; slink_id 13090 tclass_id 20
?Debug &#58; Mar 21 13&#58;34&#58;08 09877c00 TrafficAggregator&#58; push_charge&#58; slink_id 13090 charge 0.000000
 ERROR &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; <140979200> MySQL query failed&#58;
 ERROR &#58; Mar 21 13&#58;34&#58;08 09877a00 DBASQLError&#58; MySQL query failed&#58;
 ERROR &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; Exception while doing SQL insert/update !
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; <140979200> SQL query&#58; COMMIT
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; DB transaction commit
?Debug &#58; Mar 21 13&#58;34&#58;08 09852800 BusLogic&#58; finished unknown
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBA&#58;Ctx&#58; Pushing back free context &#40;system=1&#41;
?Debug &#58; Mar 21 13&#58;34&#58;08 09852800 BusLogic&#58; try to execute 37
?Debug &#58; Mar 21 13&#58;34&#58;08 09852800 BusLogic&#58; hw_block_handler with code 37
?Debug &#58; Mar 21 13&#58;34&#58;08 0977d800 DynaShapePlugin&#58; SUM tclass_id&#58; 10 downloaded&#58; 38001771234
-Stats &#58; Mar 21 13&#58;34&#58;08 09877a00 UTM5 DBA&#58;     Stats&#58; Uptime&#58; 00&#58;00&#58;10. Events&#58; 0; Errors&#58; 0
?Debug &#58; Mar 21 13&#58;34&#58;08 0977d800 DynaShapePlugin&#58; SUM tclass_id&#58; 30 downloaded&#58; 27607184800
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 UTM5 DBA&#58; DBAccess instance created
?Debug &#58; Mar 21 13&#58;34&#58;08 0977d800 DynaShapePlugin&#58; slink_id&#58; 12300 total downloaded&#58; 65608956034 direction&#58; 1
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBA&#58;Ctx&#58; Looking for free context &#40;system=1&#41;
?Debug &#58; Mar 21 13&#58;34&#58;08 0977d800 DynaShapePlugin&#58; first shaping border not reached
?Debug &#58; Mar 21 13&#58;34&#58;08 0977d800 DynaShapePlugin&#58; calculated border -1 limit -1
?Debug &#58; Mar 21 13&#58;34&#58;08 0977d800 DynaShapePlugin&#58; slink_id=12300 BW limit unchanged. Done.
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; <141135872> SQL query&#58; BEGIN
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; DB transaction begin
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 TransactionQueueManager&#58; pushing transaction <0x9e3bd80> into queue <0> &#40;default&#41;
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 TransactionHandlerImpl&#58; push&#58; transaction ptr <0x09e3bd80> &#40;size 1, Events &#91; 95 &#93;&#41;
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; <141135872> SQL SELECT query&#58; SELECT discounted,qnt,downed_as_prepaid FROM downloaded WHERE is_deleted=0 AND downloaded_id='4027' AND tclass_id='30'
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; <141135872> SQL query&#58; UPDATE downloaded SET qnt='9692160410', discounted='0', downed_as_prepaid='0' WHERE downloaded_id='4027' AND tclass_id='30'
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DTAgg&#58; update dtagg_iptraffic for slink_id 16028
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; <141135872> SQL query&#58; UPDATE dtagg_iptraffic SET discounted='0.000000', discounted_without_tax='0.000000', bytes='9692160410' WHERE id='39315'
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 UTM5 DBA&#58; Charge&#58;0.000000 p.u. for link&#58;16028 account 102296
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; <141135872> SQL query&#58; UPDATE accounts SET balance='20.738100134409' WHERE id = '102296'
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; <141135872> SQL query&#58; INSERT INTO discount_transactions_all&#40;account_id,incoming_rest,outgoing_rest,discount,discount_with_tax,service_id,service_type,slink_id,discount_date,charge_type,discount_period_id&#41; VALUES&#40;'102296','20.738100134409','20.738100134409','0','0','236','3','16028','1332329648','3','4609'&#41;
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 UTM5 DBA&#58; Checking for rehash&#58; flags 3, balance 20.738 &#40;old 20.738&#41;, credit 50.000
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 Radius&#58; Account '102296'&#58; 20.738/50.000 send...
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 BusLogic&#58; currently blm with code 37 executing
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 BusLogic&#58; BLM&#40;37&#41; pushed &#40;comment&#58; ruh block&#41;
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; <141135872> SQL query&#58; INSERT INTO discount_transactions_iptraffic_all&#40;id,account_id,discount,discount_with_tax,service_id,slink_id,discount_date,discount_date_hour,discount_date_day,discount_date_month,t_class,base_cost,ipid,bytes,discount_period_id&#41; VALUES&#40;'119719950','102296','0','0','236','16028','1332329648','1332327600','1332280800','1330552800','30','0','167774266','671468','4609'&#41;
?Debug &#58; Mar 21 13&#58;34&#58;08 09877a00 DBCtx&#58; <141135872> MySQL query failed&#58;<Duplicate entry '119719950' for key 1> Trying to reconnect&#58; 0
?Debug &#58; Mar 21 13&#58;34&#58;09 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; timestamp 1332329649 slink_id 13090
?Debug &#58; Mar 21 13&#58;34&#58;09 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; slink_id 13090 tclass_id 10
?Debug &#58; Mar 21 13&#58;34&#58;09 09877c00 TrafficAggregator&#58; push_charge&#58; slink_id 13090 charge 0.000000
?Debug &#58; Mar 21 13&#58;34&#58;10 09877a00 DBCtx&#58; <141135872> MySQL query failed&#58;<Duplicate entry '119719950' for key 1> Trying to reconnect&#58; 1
?Debug &#58; Mar 21 13&#58;34&#58;11 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; timestamp 1332329651 slink_id 14866
?Debug &#58; Mar 21 13&#58;34&#58;11 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; slink_id 14866 tclass_id 30
?Debug &#58; Mar 21 13&#58;34&#58;11 09877c00 TrafficAggregator&#58; push_charge&#58; slink_id 14866 charge 0.000000
?Debug &#58; Mar 21 13&#58;34&#58;11 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; timestamp 1332329651 slink_id 13389
?Debug &#58; Mar 21 13&#58;34&#58;11 09877c00 TrafficAggregator&#58; charge_on_timeout&#58; slink_id 13389 tclass_id 10
?Debug &#58; Mar 21 13&#58;34&#58;11 09877c00 TrafficAggregator&#58; push_charge&#58; slink_id 13389 charge 0.000000

Еще

Код: Выделить всё


&#91;root@k2 /netup/utm5&#93;# tail -f /netup/utm5/log/debug.log |grep 'MySQL query failed'
?Debug &#58; Mar 21 13&#58;35&#58;48 09877a00 DBCtx&#58; <140778496> MySQL query failed&#58;<Duplicate entry '119719960' for key 1> Trying to reconnect&#58; 0
?Debug &#58; Mar 21 13&#58;35&#58;50 09877a00 DBCtx&#58; <140778496> MySQL query failed&#58;<Duplicate entry '119719960' for key 1> Trying to reconnect&#58; 1
?Debug &#58; Mar 21 13&#58;35&#58;52 09877a00 DBCtx&#58; <140778496> MySQL query failed&#58;<Duplicate entry '119719960' for key 1> Trying to reconnect&#58; 2
?Debug &#58; Mar 21 13&#58;35&#58;54 09877a00 DBCtx&#58; <140778496> MySQL query failed&#58;<Duplicate entry '119719960' for key 1> Trying to reconnect&#58; 3
?Debug &#58; Mar 21 13&#58;35&#58;56 09877a00 DBCtx&#58; <140778496> MySQL query failed&#58;<Duplicate entry '119719960' for key 1> Trying to reconnect&#58; 4
 ERROR &#58; Mar 21 13&#58;35&#58;58 09877a00 DBCtx&#58; <140778496> MySQL query failed&#58;
 ERROR &#58; Mar 21 13&#58;35&#58;58 09877a00 DBASQLError&#58; MySQL query failed&#58;
?Debug &#58; Mar 21 13&#58;35&#58;58 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;<Duplicate entry '119719961' for key 1> Trying to reconnect&#58; 0
?Debug &#58; Mar 21 13&#58;36&#58;00 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;<Duplicate entry '119719961' for key 1> Trying to reconnect&#58; 1
?Debug &#58; Mar 21 13&#58;36&#58;02 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;<Duplicate entry '119719961' for key 1> Trying to reconnect&#58; 2
?Debug &#58; Mar 21 13&#58;36&#58;04 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;<Duplicate entry '119719961' for key 1> Trying to reconnect&#58; 3
?Debug &#58; Mar 21 13&#58;36&#58;06 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;<Duplicate entry '119719961' for key 1> Trying to reconnect&#58; 4
 ERROR &#58; Mar 21 13&#58;36&#58;08 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;
 ERROR &#58; Mar 21 13&#58;36&#58;08 09877a00 DBASQLError&#58; MySQL query failed&#58;
?Debug &#58; Mar 21 13&#58;36&#58;08 09877a00 DBCtx&#58; <140979200> MySQL query failed&#58;<Duplicate entry '119719962' for key 1> Trying to reconnect&#58; 0
?Debug &#58; Mar 21 13&#58;36&#58;10 09877a00 DBCtx&#58; <140979200> MySQL query failed&#58;<Duplicate entry '119719962' for key 1> Trying to reconnect&#58; 1
?Debug &#58; Mar 21 13&#58;36&#58;12 09877a00 DBCtx&#58; <140979200> MySQL query failed&#58;<Duplicate entry '119719962' for key 1> Trying to reconnect&#58; 2
?Debug &#58; Mar 21 13&#58;36&#58;14 09877a00 DBCtx&#58; <140979200> MySQL query failed&#58;<Duplicate entry '119719962' for key 1> Trying to reconnect&#58; 3
?Debug &#58; Mar 21 13&#58;36&#58;16 09877a00 DBCtx&#58; <140979200> MySQL query failed&#58;<Duplicate entry '119719962' for key 1> Trying to reconnect&#58; 4
 ERROR &#58; Mar 21 13&#58;36&#58;18 09877a00 DBCtx&#58; <140979200> MySQL query failed&#58;
 ERROR &#58; Mar 21 13&#58;36&#58;18 09877a00 DBASQLError&#58; MySQL query failed&#58;
?Debug &#58; Mar 21 13&#58;36&#58;18 09877a00 DBCtx&#58; <140978176> MySQL query failed&#58;<Duplicate entry '119719963' for key 1> Trying to reconnect&#58; 0
?Debug &#58; Mar 21 13&#58;36&#58;20 09877a00 DBCtx&#58; <140978176> MySQL query failed&#58;<Duplicate entry '119719963' for key 1> Trying to reconnect&#58; 1
?Debug &#58; Mar 21 13&#58;36&#58;22 09877a00 DBCtx&#58; <140978176> MySQL query failed&#58;<Duplicate entry '119719963' for key 1> Trying to reconnect&#58; 2
?Debug &#58; Mar 21 13&#58;36&#58;24 09877a00 DBCtx&#58; <140978176> MySQL query failed&#58;<Duplicate entry '119719963' for key 1> Trying to reconnect&#58; 3
?Debug &#58; Mar 21 13&#58;36&#58;26 09877a00 DBCtx&#58; <140978176> MySQL query failed&#58;<Duplicate entry '119719963' for key 1> Trying to reconnect&#58; 4
 ERROR &#58; Mar 21 13&#58;36&#58;28 09877a00 DBCtx&#58; <140978176> MySQL query failed&#58;
 ERROR &#58; Mar 21 13&#58;36&#58;28 09877a00 DBASQLError&#58; MySQL query failed&#58;
?Debug &#58; Mar 21 13&#58;36&#58;28 09877a00 DBCtx&#58; <141137920> MySQL query failed&#58;<Duplicate entry '119719964' for key 1> Trying to reconnect&#58; 0
?Debug &#58; Mar 21 13&#58;36&#58;30 09877a00 DBCtx&#58; <141137920> MySQL query failed&#58;<Duplicate entry '119719964' for key 1> Trying to reconnect&#58; 1
?Debug &#58; Mar 21 13&#58;36&#58;32 09877a00 DBCtx&#58; <141137920> MySQL query failed&#58;<Duplicate entry '119719964' for key 1> Trying to reconnect&#58; 2
?Debug &#58; Mar 21 13&#58;36&#58;34 09877a00 DBCtx&#58; <141137920> MySQL query failed&#58;<Duplicate entry '119719964' for key 1> Trying to reconnect&#58; 3
?Debug &#58; Mar 21 13&#58;36&#58;36 09877a00 DBCtx&#58; <141137920> MySQL query failed&#58;<Duplicate entry '119719964' for key 1> Trying to reconnect&#58; 4
 ERROR &#58; Mar 21 13&#58;36&#58;38 09877a00 DBCtx&#58; <141137920> MySQL query failed&#58;
 ERROR &#58; Mar 21 13&#58;36&#58;38 09877a00 DBASQLError&#58; MySQL query failed&#58;
?Debug &#58; Mar 21 13&#58;36&#58;38 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;<Duplicate entry '119719965' for key 1> Trying to reconnect&#58; 0
?Debug &#58; Mar 21 13&#58;36&#58;40 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;<Duplicate entry '119719965' for key 1> Trying to reconnect&#58; 1
?Debug &#58; Mar 21 13&#58;36&#58;42 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;<Duplicate entry '119719965' for key 1> Trying to reconnect&#58; 2
?Debug &#58; Mar 21 13&#58;36&#58;44 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;<Duplicate entry '119719965' for key 1> Trying to reconnect&#58; 3
?Debug &#58; Mar 21 13&#58;36&#58;46 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;<Duplicate entry '119719965' for key 1> Trying to reconnect&#58; 4
 ERROR &#58; Mar 21 13&#58;36&#58;48 09877a00 DBCtx&#58; <140976128> MySQL query failed&#58;
 ERROR &#58; Mar 21 13&#58;36&#58;48 09877a00 DBASQLError&#58; MySQL query failed&#58;
?Debug &#58; Mar 21 13&#58;36&#58;48 09877a00 DBCtx&#58; <141135872> MySQL query failed&#58;<Duplicate entry '119719966' for key 1> Trying to reconnect&#58; 0

Подскажите быстрый способ это полечить .
Насколько я понимаю - начинается при попытке залить данные о трафике в базу.
Админка при этом виснет наглухо.

[/code]

rudf · Сообщение **rudf** » Ср мар 21, 2012 14:52

скорее всего у вас max(id) from discount_transactions_all меньше,чем max(id) from discount_transactions_iptraffic_all. Обычно наоборот.

Kristian

А что делать в этом случае ?

Не поможет так :

Код: Выделить всё

delete from `discount_transactions_iptraffic_all` where id=ХХХХХХХХ limit 1;

?

Kristian

Как то глобально это полечить можно ?

rudf · Сообщение **rudf** » Ср мар 21, 2012 15:06

Лучше установить autoincrement для discount_transactions_all в значение больше, чем max(id) from discount_transactions_iptrafic_all. Есть вариант удаления данных из discount_transactions_iptraffic_all c id > max(id) из discount_transactions_all. Смотрите, что удобнее.

Kristian

Заметил, что не одинаковые последние значения

Код: Выделить всё

mysql> select id from `discount_transactions_iptraffic_all` order by id DESC limit 1;
+-----------+
| id        |
+-----------+
| 119720100 |
+-----------+
1 row in set &#40;0.00 sec&#41;

mysql> select id from `discount_transactions_all` order by id DESC limit 1;
+-----------+
| id        |
+-----------+
| 119720050 |
+-----------+
1 row in set &#40;0.00 sec&#41;

Как исправить, что бы были одинаковые ?
И надо ли, что бы были одинаковые ?

rudf · Сообщение **rudf** » Ср мар 21, 2012 15:32

Либо удалите записи с id>119720050 из discount_transactions_iptraffic, либо установите auto_increment=119720101 для discount_transactions_all.

Kristian

Сделал

Код: Выделить всё

delete from `discount_transactions_iptraffic_all` where id>119720063;

наблюдаю дальше

потом посмотрел ваш пост и сделал

Код: Выделить всё

delete from `discount_transactions_iptraffic_all` where id>119720050;

потому что последняя ошибка была связана с 119720063

Спасибо большое, буду смотреть, но думаю поможет

Утверждаю - помогло.

Solved

rudf · Сообщение **rudf** » Ср мар 21, 2012 15:53

Пожалуйста.