Работа с MySQL — различия между версиями
(→Открепить айпишники от юзеров, которые отключены давно) |
|||
(не показано 11 промежуточных версий 4 участников) | |||
Строка 24: | Строка 24: | ||
= Посчитать уменьшение дохода, если убрать правило 14-ти дней = | = Посчитать уменьшение дохода, если убрать правило 14-ти дней = | ||
− | + | = СЧИТАЕТ НЕКОРРЕКТНО!!! = | |
<pre> | <pre> | ||
# How much money we will lose if remove 14days-rule. | # How much money we will lose if remove 14days-rule. | ||
Строка 56: | Строка 56: | ||
<pre> | <pre> | ||
− | update Ip INNER JOIN users ON users.uid=Ip.user_uid SET Ip.user_uid =NULL | + | update Ip INNER JOIN users ON users.uid=Ip.user_uid SET Ip.user_uid = NULL, Ip.mac = NULL |
+ | WHERE users.disabled > 0 and users.disable_date < '2019-01-01' and Ip.subnet_id= xx ; | ||
</pre> | </pre> | ||
+ | |||
+ | id подсети берем в ПМ, вкладка подсети | ||
= Найти пропущенные Ip у коммутаторов = | = Найти пропущенные Ip у коммутаторов = | ||
Строка 69: | Строка 72: | ||
− | = Найти свободные диапазоны UID = | + | = Найти свободные диапазоны UID, выполнять в DBeaver = |
Строка 90: | Строка 93: | ||
AND gap_end >= 10000; -- if you need a specific range | AND gap_end >= 10000; -- if you need a specific range | ||
</pre> | </pre> | ||
+ | |||
+ | = Найти свободные диапазоны в номерах вланов, выполнять в DBeaver = | ||
+ | |||
+ | |||
+ | <pre> | ||
+ | -- Select gaps in Vlan number ranges | ||
+ | SELECT gaps.*, gaps.gap_end - gaps.gap_start + 1 AS gap_size | ||
+ | FROM ( | ||
+ | SELECT z.expected AS gap_start, IF(z.got - 1 > z.expected, z.got - 1, z.expected) AS gap_end | ||
+ | FROM ( | ||
+ | SELECT @rownum := CONVERT(@rownum + 1, SIGNED INTEGER) AS expected, IF(@rownum = number, 0, @rownum := number) AS got | ||
+ | FROM (SELECT @rownum := 0) AS a | ||
+ | JOIN Vlan | ||
+ | ORDER BY number | ||
+ | ) AS z | ||
+ | WHERE z.got != 0 | ||
+ | UNION | ||
+ | SELECT MAX(number) + 1 AS gap_start, 4096 AS gap_end FROM Vlan | ||
+ | ) AS gaps | ||
+ | WHERE gap_start <= gap_end | ||
+ | AND gap_end >= 1 -- if you need a specific range | ||
+ | ORDER BY gap_size DESC; | ||
+ | </pre> | ||
+ | |||
= Выбор юзеров вместе в полными адресами в файл= | = Выбор юзеров вместе в полными адресами в файл= | ||
Строка 114: | Строка 141: | ||
<pre> | <pre> | ||
select user from users u LEFT JOIN map_flat_uid mfu ON u.uid=mfu.uid WHERE u.gid NOT IN (2,15,34,49) AND mfu.uid IS NULL; | select user from users u LEFT JOIN map_flat_uid mfu ON u.uid=mfu.uid WHERE u.gid NOT IN (2,15,34,49) AND mfu.uid IS NULL; | ||
+ | |||
</pre> | </pre> | ||
+ | |||
+ | = Выбор логинов, ФИО, телефонов и адресов отключенных юзеров в файл = | ||
+ | |||
+ | <pre> | ||
+ | SELECT u.user, u.lastname, u.firstname, u.patronymic, u.disabled, u.disable_date, | ||
+ | ms.name, mh.number, mf.number, GROUP_CONCAT(ph.number) | ||
+ | FROM users u, map_streets ms, map_houses mh, map_porches mp, map_flats mf, map_flat_uid mfu, | ||
+ | users_phones up, phones ph | ||
+ | WHERE mh.id = mp.house_id | ||
+ | AND mp.id = mf.porch_id | ||
+ | AND mf.id = mfu.flat_id | ||
+ | AND mfu.uid = u.uid | ||
+ | AND mh.street_id = ms.id | ||
+ | AND up.uid=u.uid | ||
+ | AND ph.id = up.phone_id | ||
+ | AND u.disabled > 0 | ||
+ | AND disable_date > '2020-09-30' | ||
+ | AND disable_date < '2020-11-01' | ||
+ | GROUP BY u.user | ||
+ | ORDER BY ms.name | ||
+ | INTO OUTFILE '/var/db/mysql/disabled.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; | ||
+ | </pre> | ||
+ | |||
+ | |||
+ | = Изменение логина юзера= | ||
+ | |||
+ | Т.к в экшенс записи пишутся по логину, а не по юид, надо во всех экшенсах обновить логины | ||
+ | |||
+ | <pre> | ||
+ | update users set user='NEW_USERNAME' where user='OLD_USERNAME'; | ||
+ | update zayavki set login ='NEW_USERNAME' where login ='OLD_USERNAME'; | ||
+ | update actions set user ='NEW_USERNAME' where user='OLD_USERNAME'; | ||
+ | </pre> | ||
+ | |||
+ | |||
+ | = Все дома, в которых есть хотя бы 1 коммутатор, но при этом меньше 4-х активных абонентов = | ||
+ | |||
+ | <pre> | ||
+ | SELECT ms.name street, house_with_commutator.number house, house_with_low_active_users.active_users | ||
+ | FROM ( | ||
+ | SELECT mh.id, mh.number, mh.street_id | ||
+ | FROM map_houses mh | ||
+ | INNER JOIN map_porches mp ON mp.house_id = mh.id | ||
+ | INNER JOIN map_flats mf ON mf.porch_id = mp.id | ||
+ | INNER JOIN NetworkNode nn ON nn.flat_id = mf.id | ||
+ | INNER JOIN Commutator c ON c.networkNode_id = nn.id | ||
+ | GROUP BY mh.id | ||
+ | ) house_with_commutator | ||
+ | INNER JOIN ( | ||
+ | SELECT mh.id, COUNT(u.uid) active_users | ||
+ | FROM map_houses mh | ||
+ | INNER JOIN map_porches mp ON mp.house_id = mh.id | ||
+ | INNER JOIN map_flats mf ON mf.porch_id = mp.id | ||
+ | INNER JOIN map_flat_uid mfu ON mfu.flat_id = mf.id | ||
+ | INNER JOIN users u ON u.uid = mfu.uid | ||
+ | WHERE u.disabled = 0 | ||
+ | GROUP BY mh.id | ||
+ | HAVING active_users < 4 | ||
+ | ) house_with_low_active_users ON house_with_low_active_users.id = house_with_commutator.id | ||
+ | INNER JOIN map_streets ms ON ms.id = house_with_commutator.street_id | ||
+ | ORDER BY ms.name, house_with_commutator.number; | ||
+ | </pre> | ||
+ | |||
+ | = Исправление MySQL Replication not running= | ||
+ | |||
+ | SHOW SLAVE STATUS\G; | ||
+ | STOP SLAVE; | ||
+ | SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1000000000; | ||
+ | START SLAVE; | ||
+ | SELECT SLEEP(5); | ||
+ | SHOW SLAVE STATUS\G; |
Текущая версия на 09:07, 20 июня 2023
Содержание
- 1 Удалить дубликаты из таблицы
- 2 Посчитать уменьшение дохода, если убрать правило 14-ти дней
- 3 СЧИТАЕТ НЕКОРРЕКТНО!!!
- 4 Найти юзеров, у которых есть 2+ одинаковых адреса
- 5 Открепить айпишники от юзеров, которые отключены давно
- 6 Найти пропущенные Ip у коммутаторов
- 7 Найти свободные диапазоны UID, выполнять в DBeaver
- 8 Найти свободные диапазоны в номерах вланов, выполнять в DBeaver
- 9 Выбор юзеров вместе в полными адресами в файл
- 10 Выбор юзеров без адреса
- 11 Выбор логинов, ФИО, телефонов и адресов отключенных юзеров в файл
- 12 Изменение логина юзера
- 13 Все дома, в которых есть хотя бы 1 коммутатор, но при этом меньше 4-х активных абонентов
- 14 Исправление MySQL Replication not running
Удалить дубликаты из таблицы[править]
/* REMOVE DUPLICATE TABLE ROWS */ #drop table if exists tmp; #create temporary table tmp (`id` int(11) NOT NULL); #insert tmp (id) SELECT a.id FROM SubscriberLine a INNER JOIN (SELECT MIN(id) as minid, user_uid, flat_id, networkNode_id FROM SubscriberLine GROUP BY user_uid, flat_id, networkNode_id HAVING Count(1) > 1) AS b ON ((a.user_uid = b.user_uid OR a.user_uid IS NULL AND b.user_uid IS NULL) AND (a.flat_id = b.flat_id OR a.flat_id IS NULL AND b.flat_id IS NULL) AND (a.networkNode_id = b.networkNode_id OR a.networkNode_id IS NULL AND b.networkNode_id IS NULL) AND a.id != b.minid); #delete from SubscriberLine where id in (select id from tmp);
CREATE TEMPORARY TABLE tmp AS (SELECT id from SubscriberLine group by flat_id,user_uid,networkNode_id HAVING COUNT(*) > 1); DELETE from SubscriberLine where id in(select id from tmp);
Посчитать уменьшение дохода, если убрать правило 14-ти дней[править]
СЧИТАЕТ НЕКОРРЕКТНО!!![править]
# How much money we will lose if remove 14days-rule. SET @start_date = '2016-09-01'; SET @end_date = '2016-10-01'; select a1.user_uid, a1.datetime as disable_date, a2.datetime as enable_date, TIMESTAMPDIFF(SECOND, a1.datetime, a2.datetime) as seconds, TIMESTAMPDIFF(DAY, a1.datetime, a2.datetime) as days, p.fixed_cost as packet_cost, (p.fixed_cost * TIMESTAMPDIFF(SECOND, a1.datetime, a2.datetime) / 2592000) as cost_for_seconds, SUM((p.fixed_cost * TIMESTAMPDIFF(SECOND, a1.datetime, a2.datetime) / 2592000)) from admin_actions a1 inner join ( SELECT user_uid, datetime from admin_actions where type = 8 and subtype = 0 and value_after = 'подключен' and datetime between @start_date and @end_date ) a2 on a1.user_uid = a2.user_uid inner join users u on a1.user_uid = u.uid inner join packets p on u.gid = p.gid where a1.type = 8 and a1.subtype = 0 and a1.value_after = 'отключен' and a1.datetime between @start_date and @end_date and a1.datetime < a2.datetime and TIMESTAMPDIFF(DAY, a1.datetime, a2.datetime) < 14;
Найти юзеров, у которых есть 2+ одинаковых адреса[править]
SELECT COUNT(*) AS repetitions, flat_id,uid from map_flat_uid group by flat_id,uid HAVING repetitions > 1;
Открепить айпишники от юзеров, которые отключены давно[править]
update Ip INNER JOIN users ON users.uid=Ip.user_uid SET Ip.user_uid = NULL, Ip.mac = NULL WHERE users.disabled > 0 and users.disable_date < '2019-01-01' and Ip.subnet_id= xx ;
id подсети берем в ПМ, вкладка подсети
Найти пропущенные Ip у коммутаторов[править]
SELECT INET_NTOA(INET_ATON(t1.Ip) + 1) AS empty_ip FROM Commutator AS t1 LEFT JOIN Commutator AS t2 ON INET_ATON(t2.Ip) = INET_ATON(t1.Ip) +1 WHERE t2.Ip IS NULL and INET_NTOA(INET_ATON(t1.Ip) + 1) IS NOT NULL order by INET_NTOA(INET_ATON(t1.Ip) + 1);
Найти свободные диапазоны UID, выполнять в DBeaver[править]
-- Select gaps between IDs SELECT * FROM ( SELECT z.expected AS gap_start, IF(z.got - 1 > z.expected, z.got - 1, z.expected) AS gap_end FROM ( SELECT @rownum := CONVERT(@rownum + 1, SIGNED INTEGER) AS expected, IF(@rownum = uid, 0, @rownum := uid) AS got FROM (SELECT @rownum := 0) AS a JOIN users ORDER BY uid ) AS z WHERE z.got != 0 UNION SELECT MAX(uid) + 1 AS gap_start, 65535 AS gap_end FROM users ) AS gaps WHERE gap_start <= gap_end AND gap_end >= 10000; -- if you need a specific range
Найти свободные диапазоны в номерах вланов, выполнять в DBeaver[править]
-- Select gaps in Vlan number ranges SELECT gaps.*, gaps.gap_end - gaps.gap_start + 1 AS gap_size FROM ( SELECT z.expected AS gap_start, IF(z.got - 1 > z.expected, z.got - 1, z.expected) AS gap_end FROM ( SELECT @rownum := CONVERT(@rownum + 1, SIGNED INTEGER) AS expected, IF(@rownum = number, 0, @rownum := number) AS got FROM (SELECT @rownum := 0) AS a JOIN Vlan ORDER BY number ) AS z WHERE z.got != 0 UNION SELECT MAX(number) + 1 AS gap_start, 4096 AS gap_end FROM Vlan ) AS gaps WHERE gap_start <= gap_end AND gap_end >= 1 -- if you need a specific range ORDER BY gap_size DESC;
Выбор юзеров вместе в полными адресами в файл[править]
select u.user, u.disabled,i.ip, ms.name, mh.number,mf.number from users u, map_streets ms, map_houses mh, map_porches mp, map_flats mf, map_flat_uid mfu, Ip i WHERE mh.id = mp.house_id AND mp.id = mf.porch_id AND mf.id = mfu.flat_id AND mfu.uid = u.uid AND mh.street_id = ms.id AND i.user_uid=u.uid AND i.subnet_id = 116 AND u.disabled = 0 order by ms.name INTO OUTFILE '/var/db/mysql/5sradio1.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Выбор юзеров без адреса[править]
select user from users u LEFT JOIN map_flat_uid mfu ON u.uid=mfu.uid WHERE u.gid NOT IN (2,15,34,49) AND mfu.uid IS NULL;
Выбор логинов, ФИО, телефонов и адресов отключенных юзеров в файл[править]
SELECT u.user, u.lastname, u.firstname, u.patronymic, u.disabled, u.disable_date, ms.name, mh.number, mf.number, GROUP_CONCAT(ph.number) FROM users u, map_streets ms, map_houses mh, map_porches mp, map_flats mf, map_flat_uid mfu, users_phones up, phones ph WHERE mh.id = mp.house_id AND mp.id = mf.porch_id AND mf.id = mfu.flat_id AND mfu.uid = u.uid AND mh.street_id = ms.id AND up.uid=u.uid AND ph.id = up.phone_id AND u.disabled > 0 AND disable_date > '2020-09-30' AND disable_date < '2020-11-01' GROUP BY u.user ORDER BY ms.name INTO OUTFILE '/var/db/mysql/disabled.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Изменение логина юзера[править]
Т.к в экшенс записи пишутся по логину, а не по юид, надо во всех экшенсах обновить логины
update users set user='NEW_USERNAME' where user='OLD_USERNAME'; update zayavki set login ='NEW_USERNAME' where login ='OLD_USERNAME'; update actions set user ='NEW_USERNAME' where user='OLD_USERNAME';
Все дома, в которых есть хотя бы 1 коммутатор, но при этом меньше 4-х активных абонентов[править]
SELECT ms.name street, house_with_commutator.number house, house_with_low_active_users.active_users FROM ( SELECT mh.id, mh.number, mh.street_id FROM map_houses mh INNER JOIN map_porches mp ON mp.house_id = mh.id INNER JOIN map_flats mf ON mf.porch_id = mp.id INNER JOIN NetworkNode nn ON nn.flat_id = mf.id INNER JOIN Commutator c ON c.networkNode_id = nn.id GROUP BY mh.id ) house_with_commutator INNER JOIN ( SELECT mh.id, COUNT(u.uid) active_users FROM map_houses mh INNER JOIN map_porches mp ON mp.house_id = mh.id INNER JOIN map_flats mf ON mf.porch_id = mp.id INNER JOIN map_flat_uid mfu ON mfu.flat_id = mf.id INNER JOIN users u ON u.uid = mfu.uid WHERE u.disabled = 0 GROUP BY mh.id HAVING active_users < 4 ) house_with_low_active_users ON house_with_low_active_users.id = house_with_commutator.id INNER JOIN map_streets ms ON ms.id = house_with_commutator.street_id ORDER BY ms.name, house_with_commutator.number;
Исправление MySQL Replication not running[править]
SHOW SLAVE STATUS\G; STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1000000000; START SLAVE; SELECT SLEEP(5); SHOW SLAVE STATUS\G;