Пробуем выполнить все действия из init:
Код:
mysql> DROP TABLE max_dates;
ERROR 1051 (42S02): Unknown table 'max_dates'
mysql> DROP TABLE problem_cids;
ERROR 1051 (42S02): Unknown table 'problem_cids'
mysql> CREATE TABLE max_dates (
-> `dt` DATETIME NOT NULL,
-> `cid` INT NOT NULL,
-> PRIMARY KEY (`cid`),
-> INDEX `dt_idx`(`dt`)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE problem_cids (
-> `cid` INT NOT NULL,
-> PRIMARY KEY (`cid`)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO problem_cids
-> SELECT cid FROM ipn_contract_status_9 group by cid having count(*) > 1; Query OK, 266 rows affected (0.01 sec)
Records: 266 Duplicates: 0 Warnings: 0
mysql> INSERT INTO max_dates(dt, cid )
-> SELECT max(dt), status.cid FROM ipn_contract_status_9 as status
-> LEFT JOIN ipn_contract_status_log_9 as log ON status.cid = log.cid
-> WHERE status.cid in ( SELECT cid FROM problem_cids )
-> GROUP BY status.cid ;
Query OK, 266 rows affected, 249 warnings (0.09 sec)
Records: 266 Duplicates: 0 Warnings: 249
Warnings: 249 - wtf?!
Смотрим, в чем проблема:
Код:
mysql> show warnings;
+---------+------+----------------------------+
| Level | Code | Message |
+---------+------+----------------------------+
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
| Warning | 1048 | Column 'dt' cannot be null |
+---------+------+----------------------------+
64 rows in set (0.00 sec)
mysql> select * from max_dates;
+---------------------+--------+
| dt | cid |
+---------------------+--------+
| 0000-00-00 00:00:00 | 116167 |
| 0000-00-00 00:00:00 | 116313 |
| 0000-00-00 00:00:00 | 116408 |
<...>
| 0000-00-00 00:00:00 | 182610 |
| 2009-12-07 11:34:03 | 165432 |
| 2010-02-24 09:24:17 | 179736 |
| 2010-06-03 11:40:07 | 182678 |
| 2010-06-03 11:40:07 | 182680 |
| 2010-06-08 09:00:03 | 176438 |
| 2010-06-08 10:59:17 | 172426 |
| 2010-06-08 10:59:23 | 178368 |
| 2010-06-08 11:28:05 | 181540 |
| 2010-06-08 11:40:05 | 182744 |
| 2010-06-08 11:40:05 | 182746 |
| 2010-06-09 02:28:07 | 181541 |
| 2010-06-09 02:28:08 | 181542 |
| 2010-06-09 02:28:09 | 181546 |
| 2010-06-09 09:10:03 | 151991 |
| 2010-06-09 09:16:54 | 143386 |
| 2010-06-09 09:51:27 | 181407 |
| 2010-06-09 09:59:17 | 181675 |
+---------------------+--------+
266 rows in set (0.00 sec)
Т.е. в ipn_contract_status_9 было много записей с dt = null, отчего в max_dates они попали с dt='0000-00-00 00:00:00'
Посмотрим, как это выглядит на примере конктретного договора:
Код:
mysql> SELECT * FROM ipn_contract_status_9 as status LEFT JOIN ipn_contract_status_log_9 as log ON status.cid = log.cid WHERE status.cid =116167;
+--------+--------+------+------+--------+------+---------+
| cid | status | dt | cid | action | uid | comment |
+--------+--------+------+------+--------+------+---------+
| 116167 | 2 | NULL | NULL | NULL | NULL | NULL |
| 116167 | 0 | NULL | NULL | NULL | NULL | NULL |
+--------+--------+------+------+--------+------+---------+
2 rows in set (0.00 sec)
Это вообще клиент, у которого нет ни поинтов, ни модуля вл на договоре.
Ну ладно, посмотрим, что будет дальше.
А дальше нам нужно по-идее дропнуть все проблемные записи и вставить новые:
Цитата:
DELETE FROM ipn_contract_status_9
WHERE
cid in
( SELECT problem_cids.cid FROM problem_cids );
INSERT INTO ipn_contract_status_$mid( cid, status )
SELECT distinct log.cid, log.action FROM ipn_contract_status_log_9 as log
LEFT JOIN bgbilling.max_dates ON max_dates.dt = log.dt
WHERE
log.cid IN
( SELECT problem_cids.cid FROM problem_cids )
AND max_dates.dt is NOT NULL
ORDER BY log.cid ;
Не будем рубить с плеча - посмотрим, что должно вставиться:
Код:
mysql> SELECT distinct log.cid, log.action FROM ipn_contract_status_log_9 as log
-> LEFT JOIN bgbilling.max_dates ON max_dates.dt = log.dt
-> WHERE
-> log.cid IN
-> ( SELECT problem_cids.cid FROM problem_cids )
-> AND max_dates.dt is NOT NULL
-> ORDER BY log.cid;
+--------+--------+
| cid | action |
+--------+--------+
| 143386 | 1 |
| 151991 | 1 |
| 165432 | 2 |
| 172426 | 2 |
| 172426 | 0 |
| 176438 | 0 |
| 178368 | 0 |
| 179736 | 0 |
| 181407 | 0 |
| 181540 | 0 |
| 181541 | 0 |
| 181542 | 0 |
| 181546 | 0 |
| 181675 | 0 |
| 182678 | 2 |
| 182680 | 2 |
| 182744 | 2 |
| 182746 | 2 |
+--------+--------+
18 rows in set (0.08 sec)
И видим, что вставляются далеко не все записи, да ещё с дубликатами. Поэтому индекс и не может добавиться в итоге.
Проблемы две (к разработчикам):
1) Почему-то большинство дубликатов соответствуют договорам, у которых нет записей в истории изменения статуса. При этом у всех таких договоров к тому же нет ни одного шлюза:
mysql> SELECT max(dt) dt, status.cid, gc.gid FROM ipn_contract_status_9 as status LEFT JOIN ipn_contract_status_log_9 as log ON status.cid = log.cid left join ipn_gate_contract_port_9 gc on status.cid=gc.cid WHERE status.cid in ( SELECT cid FROM problem_cids ) GROUP BY status.cid having dt is null and not gid is null;
Empty set (0.00 sec)
Откуда они берутся?
2) В запросе на вставку текущих статусов после удаления всех проблемных таблицы max_dates и ipn_contract_status_log_9 join-ятся только по дате, но не по cid, поэтому появляются дубликаты => не добавляется первичный ключ.
Поправленный запрос:
Цитата:
INSERT INTO ipn_contract_status_$mid( cid, status )
SELECT distinct log.cid, log.action FROM ipn_contract_status_log_9 as log
LEFT JOIN bgbilling.max_dates ON max_dates.dt = log.dt and max_dates.cid=log.cid
WHERE
log.cid IN
( SELECT problem_cids.cid FROM problem_cids )
AND max_dates.dt is NOT NULL
ORDER BY log.cid ;