Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Есть некая система (если это важно, то UserSide), которая использует в качестве БД MySQL.
Есть самописный скрипт, который запускается каждые 5 минут и переносит в эту систему информацию из другой системы.
Используется запрос такого вида:
insert into tbl_base (`BILLCODE`,`CODETI`,`LOGNAME`,`PARENTCODE`,`NOTINBILLING`,`DATEADD`) values (?,?,?,?,null,now())
on duplicate key update `LOGNAME`=?,`PARENTCODE`=?,`NOTINBILLING`=null;

Таблица, соответственно, такая:
CREATE TABLE `tbl_base` (
	`CODE` INT(11) NOT NULL AUTO_INCREMENT,
	`CODETI` VARCHAR(255) NULL DEFAULT NULL,
	`GROUPN` VARCHAR(38) NULL DEFAULT NULL,
	`ISREG` INT(1) NULL DEFAULT NULL,
	`LOGNAME` VARCHAR(255) NULL DEFAULT NULL,
	`DOGNUMBER` VARCHAR(255) NULL DEFAULT NULL,
	`DATEDOG2` VARCHAR(10) NULL DEFAULT NULL,
	`FIO` VARCHAR(255) NULL DEFAULT NULL,
	`PASS` VARCHAR(50) NULL DEFAULT NULL,
	`HOUSEC` INT(11) NULL DEFAULT NULL,
	`PODEZD` INT(3) NULL DEFAULT NULL,
	`HOUSECODE` VARCHAR(10) NULL DEFAULT NULL,
	`FLOOR` INT(3) NULL DEFAULT NULL,
	`APART` INT(11) NULL DEFAULT NULL,
	`APART_B` VARCHAR(32) NULL DEFAULT '',
	`EMAIL` VARCHAR(100) NULL DEFAULT NULL,
	`TEL` VARCHAR(255) NULL DEFAULT NULL,
	`TELMOB` VARCHAR(255) NULL DEFAULT NULL,
	`DOP` MEDIUMTEXT NULL,
	`DOP2` MEDIUMTEXT NULL,
	`BALANS` DOUBLE NULL DEFAULT NULL,
	`KREDIT` DOUBLE NULL DEFAULT NULL,
	`SKIDKA` INT(3) NULL DEFAULT NULL,
	`DATEPLUS` DATE NULL DEFAULT NULL,
	`LASTACT` DATETIME NULL DEFAULT NULL,
	`LASTPING` DATETIME NULL DEFAULT NULL,
	`WORKSTATUS` INT(2) NULL DEFAULT '0',
	`RXTRAF` VARCHAR(15) NULL DEFAULT NULL,
	`TXTRAF` VARCHAR(15) NULL DEFAULT NULL,
	`DATEINNET` DATE NULL DEFAULT NULL,
	`LASTLOGONTIME` DATETIME NULL DEFAULT NULL,
	`LASTLOGOFFTIME` DATETIME NULL DEFAULT NULL,
	`LOGONHISIP` BIGINT(20) NULL DEFAULT NULL,
	`AGENT_VER` VARCHAR(11) NULL DEFAULT NULL,
	`AGENT_LASTVISIT` DATETIME NULL DEFAULT NULL,
	`AGENT_DATA` MEDIUMTEXT NULL,
	`USERLANG` VARCHAR(11) NULL DEFAULT NULL,
	`ISVIP` INT(1) NULL DEFAULT NULL,
	`LASTVISIT` DATETIME NULL DEFAULT NULL,
	`LOGONIP` DOUBLE NULL DEFAULT NULL,
	`DATEAKCIYA` DATE NULL DEFAULT NULL,
	`METR` INT(3) UNSIGNED NULL DEFAULT NULL,
	`DATEADD` DATETIME NULL DEFAULT NULL,
	`BILLCODE` INT(3) NULL DEFAULT NULL,
	`NOTINBILLING` INT(1) NULL DEFAULT NULL,
	`DATEPAID` DATE NULL DEFAULT NULL,
	`TARIF_DATESTART` DATE NULL DEFAULT NULL,
	`ACC_F` INT(11) NULL DEFAULT NULL,
	`PARENTCODE` INT(11) NULL DEFAULT NULL,
	`NOTSMS` INT(1) NULL DEFAULT NULL,
	`DISABLE` INT(1) NULL DEFAULT '0',
	`DATECHANGESTATUS` DATE NULL DEFAULT NULL,
	`LS` VARCHAR(255) NULL DEFAULT '',
	`TYPER_UPDACT` INT(3) NULL DEFAULT '0',
	PRIMARY KEY (`CODE`),
	UNIQUE INDEX `BILLKEY` (`BILLCODE`, `CODETI`),
	INDEX `CODETI` (`CODETI`),
	INDEX `GROUPN` (`GROUPN`),
	INDEX `HOUSEC` (`HOUSEC`),
	INDEX `FIO` (`FIO`),
	INDEX `LASTACT` (`LASTACT`),
	INDEX `LASTPING` (`LASTPING`),
	INDEX `LASTVISIT` (`LASTVISIT`),
	INDEX `DATEINNET` (`DATEINNET`),
	INDEX `WORKSTATUS` (`WORKSTATUS`),
	INDEX `ISREG` (`ISREG`),
	INDEX `LOGNAME` (`LOGNAME`),
	INDEX `DATEPLUS` (`DATEPLUS`),
	INDEX `BALANS` (`BALANS`),
	INDEX `BILLCODE` (`BILLCODE`),
	INDEX `NOTINBILLING` (`NOTINBILLING`),
	INDEX `ACC_F` (`ACC_F`),
	INDEX `PARENTCODE` (`PARENTCODE`),
	INDEX `DISABLE` (`DISABLE`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=FIXED
AUTO_INCREMENT=2147483648


Как я сейчас узнал, несмотря на срабатывание on duplicate key, счетчик на таблице все равно увеличивается.
Вчера он переполнился и скрипт работать перестал.
Заменять int на bigint я бы не хотел — система большая и неизвестно, где это вылезет боком.
Я бы хотел просто "уплотнить" таблицу, перенумеровав ее заново (в этой таблице около 10к записей) и заодно обновив FK в зависимых таблицах.

Вопроса у меня два.

1. Есть ли готовый скрипт или утилита для такого "уплотнения", который бы сам умел находить зависимости?

2. Можно ли это предотвратить в дальнейшем, чтобы счетчик не увеличивался, когда строка не добавляется?
27 фев 18, 09:38    [21220782]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
1. Если и есть, то работать она сможет только в случае, если имеется FK.
2. Нет.
27 фев 18, 09:50    [21220807]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
paver
Member

Откуда: Томск
Сообщений: 221
Alibek B.,
Тут похожая проблема обсуждается
https://habrahabr.ru/post/156489/
Пишут, что ODKU увеличит Auto Incriment поле на единицу для InnoDB, для MyIsam все работает правильно.
Советуют также для автоинкремента использовать UNSIGNED
Может будет полезно
27 фев 18, 10:52    [21221046]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Ок.
Тогда не поможете составить скрипт?
Список зависимостей я составил вручную и сохранил в таблицу:
CREATE TABLE `remap_base_ref` (
	`table` VARCHAR(200) NOT NULL, --имя таблицы FK
	`column` VARCHAR(200) NOT NULL, --имя столбца FK
	`conditions` VARCHAR(2000) NULL, --дополнительные ограничения WHERE
	PRIMARY KEY (`table`, `column`)
)

Таблица соответствия идентификаторов (старый-новый) у меня подготовлена в таблице:
CREATE TABLE `remap_base_ids` (
	`oid` INT(11) NOT NULL,
	`nid` INT(11) NOT NULL
)

Как мне пройтись по remap_base_ref, сгенерировав SQL, в котором указанные столбцы будут обновлены в соответствии с таблицей соответствия?
Можно ли это сделать простым SQL-скриптом? Или нужно делать процедуру?
27 фев 18, 10:55    [21221060]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
paver
Пишут, что ODKU увеличит Auto Incriment поле на единицу для InnoDB, для MyIsam все работает правильно.

У меня с MyISAM печальный опыт повреждения данных.
Буду иметь ввиду, но хотелось бы найти другой способ.

paver
Советуют также для автоинкремента использовать UNSIGNED

Это несколько отсрочит проблему, но не решит ее.
Правильнее будет написать процедуру "упаковки" и запускать ее раз в год.
27 фев 18, 10:57    [21221064]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
paver
Member

Откуда: Томск
Сообщений: 221
Alibek B.
но хотелось бы найти другой способ.

Там в каментах варианты предлагаются, может что и подойдет
27 фев 18, 11:00    [21221080]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Подскажите, что не так в запросе?
Ошибочные блоки закомментированы.
BEGIN

declare DONE int(1) default 0;
declare CMAX int;
declare TBL varchar(80);
declare FLD varchar(80);
declare COND varchar(200);
declare Q varchar(2000);
declare CUR cursor for select `table`, `column`, `conditions` from `remap_base_ref`;

declare continue handler for sqlstate '02000' set DONE = 1;

truncate `remap_base_ids`;
alter table `remap_base_ids` AUTO_INCREMENT=1;
insert into `remap_base_ids` (`oid`) select `CODE` from `tbl_base` order by 1;

open CUR;
repeat
	fetch CUR into TBL, FLD, COND;
	if not DONE then
		set Q = concat('update `', TBL, '` B join `remap_base_ids` R on (R.`oid` = B.`', FLD, '`');
		set Q = concat(Q, ifnull(concat(' and ',COND),''), ')');
		set Q = concat(Q, ' set B.`', FLD, '` = R.`nid`;');
/*		prepare ST from Q;
		execute ST;
		deallocate prepare ST;*/
	end if;
until DONE end repeat;
close CUR;

update `tbl_base` B join `remap_base_ids` R on (R.`oid` = B.`CODE`) set B.`CODE` = R.`nid`;
select max(`nid`) into CMAX from `remap_base_ids`;
/*alter table `tbl_base` AUTO_INCREMENT=CMAX;*/

END

Как правильно выполнять динамический SQL в процедурах?
И почему я не могу использовать в alter table переменную?
27 фев 18, 11:55    [21221278]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
Alibek B.
Как правильно выполнять динамический SQL в процедурах?

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

Alibek B.
почему я не могу использовать в alter table переменную?

В первом случае - надо смотреть, что получилось в переменной Q.
Во втором случае - присваиваемое значение в предложении AUTO_INCREMENT является литералом, а не параметром.
27 фев 18, 12:22    [21221404]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Akina
В первом случае - надо смотреть, что получилось в переменной Q.

Так дело даже до компиляции не доходит.
Как только я задаю prepare с комментарием, процедура перестает сохраняться.
Akina
Во втором случае - присваиваемое значение в предложении AUTO_INCREMENT является литералом, а не параметром.

То есть нужно использовать динамический SQL? Или это можно как-то обойти?
27 фев 18, 14:25    [21222001]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Сейчас специально проверил.
Пишу:
prepare ST from '';

Процедура успешно сохраняется.
Пишу:
prepare ST from Q;

Получаю ошибку:
SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Q;
(хотя в Q валидный SQL, который сам по себе выполняется)
27 фев 18, 14:28    [21222021]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Странно, а так работает:
set @SQL = Q;
prepare ST from @SQL;

Не подскажите, в чем разница?
И почему такое все же не работает:
select max(`nid`) into @MAX from `remap_base_ids`;
alter table `tbl_base` AUTO_INCREMENT=@MAX;
27 фев 18, 14:42    [21222078]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
Ну написано же, ё моё:
https://dev.mysql.com/doc/refman/5.7/en/prepare.html
preparable_stmt is either a string literal or a user variable that contains the text of the SQL statement.
Два варианта:
- string literal
- user variable
Всё! третьего не дано! локальные переменные - не катят.
27 фев 18, 14:46    [21222091]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Финальная версия скрипта:
BEGIN

declare DONE int(1) default 0;
declare TBL varchar(80);
declare FLD varchar(80);
declare COND varchar(200);
declare Q varchar(2000);
declare CUR cursor for select `table`, `column`, `conditions` from `remap_base_ref`;

declare continue handler for sqlstate '02000' set DONE = 1;

truncate `remap_base_ids`;
alter table `remap_base_ids` AUTO_INCREMENT=1;
insert into `remap_base_ids` (`oid`) select `CODE` from `tbl_base` order by 1;

open CUR;
repeat
	fetch CUR into TBL, FLD, COND;
	if not DONE then
		set Q = 'update `$TBL` B join `remap_base_ids` R on (R.`oid` = B.`$FLD` and $COND) set B.`$FLD` = R.`nid`;';
		set Q = replace(Q, '$TBL', TBL);
		set Q = replace(Q, '$FLD', FLD);
		set Q = replace(Q, ' and $COND', ifnull(concat(' and ', COND), ''));
		set @SQL = Q;
		prepare ST from @SQL;
		execute ST;
		deallocate prepare ST;
	end if;
until DONE end repeat;
close CUR;

update `tbl_base` B join `remap_base_ids` R on (R.`oid` = B.`CODE`) set B.`CODE` = R.`nid`;
select max(`nid`) into @MAX from `remap_base_ids`;
set @SQL = concat('alter table `tbl_base` AUTO_INCREMENT=', ifnull(@MAX,0)+1);
prepare ST from @SQL;
execute ST;
deallocate prepare ST;

END

Не подскажите, есть что исправить?
27 фев 18, 14:48    [21222097]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
Alibek B.
select max(`nid`) into @MAX from `remap_base_ids`;
set @SQL = concat('alter table `tbl_base` AUTO_INCREMENT=', ifnull(@MAX,0)+1);

А чего не сразу
SELECT CONCAT('ALTER TABLE `tbl_base` AUTO_INCREMENT=', ifnull(MAX(`nid`),0)+1) INTO @SQL FROM `remap_base_ids`;
?
Alibek B.
есть что исправить?

Ну разве что вынести
set Q = 'update `$TBL` B join `remap_base_ids` R on (R.`oid` = B.`$FLD` and $COND) set B.`$FLD` = R.`nid`;';
за цикл, а REPLACE() делать в другую переменную...
27 фев 18, 16:20    [21222527]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Вообщем все прошло успешно, никаких ошибок и сбоев не выявилось.
Единственное, что я сделал еще — это добавил в первоначальный скрипт (который выполняет множество insert с ODKU) в конце alter table tbl_base AUTO_INCREMENT=<select max(CODE)+1 from tbl_base> — тогда не потребуется раз в год повторно делать "упаковку".
28 фев 18, 11:53    [21224347]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Amin
Member

Откуда:
Сообщений: 30
Я так понимаю, вы подтаскиваете абонентов из стороннего биллинга, который юзерсайдом напрямую не поддерживается, верно ?

С новых версий у них есть свой API, возможно, имеет смысл его задействовать, а не мучить базу этой прекрасной учётной системы столь злым скриптом, переполняющим счётчик.

http://wiki.userside.eu/API
http://wiki.userside.eu/ERP_"USERSIDE"

Хотя хак в автоинкрементом тоже ОК, главное, про бэкапы не забывать =)
9 мар 18, 08:33    [21245627]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
API построчный (за раз работает с одной записью) и тормозной. Да и неудобный.
9 мар 18, 11:29    [21245745]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
miksoft
Member

Откуда:
Сообщений: 38555
Alibek B.
alter table tbl_base AUTO_INCREMENT=<select max(CODE)+1 from tbl_base>
Можно упростить:
alter table tbl_base AUTO_INCREMENT=1
9 мар 18, 11:33    [21245753]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
А дублей не будет? В таблице ведь уже есть записи.
9 мар 18, 11:54    [21245789]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
miksoft
Member

Откуда:
Сообщений: 38555
Alibek B.
А дублей не будет? В таблице ведь уже есть записи.
Не будет.
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
To reset the current auto-increment value:
ALTER TABLE t1 AUTO_INCREMENT = 13;

You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.
9 мар 18, 12:01    [21245800]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
У меня в процедуре имеется цикл, внутри которого генерируется и выполняется динамический SQL:
		SET @SQL = Q;
		PREPARE ST FROM @SQL;
		EXECUTE ST;
		DEALLOCATE PREPARE ST;


Хочу перед запуском процедуры убедится, что все генерируется правильно.
Вызов SQL комментирую, а вместо него пишу:
SELECT Q AS 'SQL';


В результате получаю несколько десятков однострочных resultset.
Для читабельности хотелось бы вместо них получить один многострочный resultset.
Это возможно сделать?
27 окт 20, 22:28    [22221778]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
-- ...
SET @sql := '';
cycle:
    -- ...
    SET @sql := CONCAT(@sql, q);
    -- ...
end_cycle;
SELECT @sql;
-- ...
27 окт 20, 23:15    [22221795]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
miksoft
Member

Откуда:
Сообщений: 38555
Alibek B.
В результате получаю несколько десятков однострочных resultset.
Для читабельности хотелось бы вместо них получить один многострочный resultset.
Пишите в таблицу:
INSERT INTO debug_table SELECT Q AS 'SQL';

А потом из нее прочитаете.
27 окт 20, 23:41    [22221810]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Alibek B.
Member

Откуда:
Сообщений: 3733
Akina, это же не многострочный resultset.

miksoft
Пишите в таблицу:

Так и сделал.
Просто думал, что может быть в MySQL есть что-то вроде табличной переменной или возвращаемого курсора.
28 окт 20, 08:40    [22221895]     Ответить | Цитировать Сообщить модератору
 Re: Переполнился счетчик  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20785
Alibek B.
это же не многострочный resultset.
?? не понял фразы.
28 окт 20, 08:51    [22221900]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / MySQL Ответить