Работа с MySQL

Удалить дубликаты из таблицы

/* 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);

СЧИТАЕТ НЕКОРРЕКТНО!!!

# 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;