Форум пользователей MySQL

immelnikoff · 08.06.2018 16:32:50

Есть функция, через которую приложение пишет в базу на сервере MySQL 8.0:

CREATE function `insert_photo` (_link char(36), _material_id mediumint unsigned, _img mediumtext) returns mediumint unsigned

BEGIN

declare _id mediumint unsigned;

declare l binary(16);

set l = UNHEX(replace(_link, '-', ''));

insert ignore into Photo(link, material_id, img) value(l, _material_id, from_base64(_img));

select id into _id from Photo where link = l;

return _id;

END

Функция принимает данные для записи в базу и возвращает автоинкрементный id сделанной записи. Приложение пишет последовательно, то есть функция для следующей записи не вызывается пока не будет получен id предыдущей записи. Проблема в следующем. Я заметил, что в таблице в автоинкрементном id есть немало пропусков – при 10 000 записей id увеличивается на 15 000. Вообще не понятно откуда в данном случае могут взяться пропуски. Стал смотреть детальнее и обнаружил, что примерно для половины передаваемых данных функция вызывалась дважды. Для условных 10 000 единиц данных 5 000 из них были записаны при первом вызове функции, остальные 5 000 – только со второй попытки. Я сначала подумал, что по каким-то причинам возвращаемый функцией id не доходит до приложения (сбои в сети) и приложение повторно передает эти данные функции и получает id со второго раза. Но тут есть сразу два возражения. Во-первых, связь между сервером MySQL 8.0 и приложением надежная, которая в принципе не может давать таких сбоев. А, во-вторых, в базе нет ни одной записи, которая была бы сделана с 3-й или большей попытки. Прям магия какая-то! Проясните, пожалуйста, причину подобного поведения MySQL 8.0.

klow · 08.06.2018 18:06:55

В этом, думаю, виновато "ignore". При вставке добавляется новый ID, но если вставка не удалась идет откат, но счетчик уже увеличен и следующая вставка будет с пропуском.

Отредактированно klow (08.06.2018 19:54:27)

immelnikoff · 08.06.2018 21:43:26

Как вставка с первой попытки не удалась, а со второй попытки удалась? Данные-то одни и те же. Все данные из приложения без исключения записались в базу. Вот только половина записались при первом же вызове функции, а вторая половина – только со второй попытки. И при этом нет ни одной записи, которая бы записалась с третьей, четвертой или любой другой большей попытки.
ps. Пропуски в id есть размером одно, два и три значения.

klow · 08.06.2018 22:00:37

Я немного о другом.
Например, Вы вставляете значение link = 'aaa'
Получили ID, например, 123. Второй раз вставляете link = 'aaa' счетчик увеличился и будет равен 124, но вернется Вам ID=123, так как значение link = 'aaa' уже есть в базе с ID = 123.
Дальше вставляете link = 'aaa' снова счетчик увеличится и будет 125, но вернется Вам снова 123.
Дальше вставляете значение, которого нет в БД и получите ID 126. Вот Вам и пропуски. Но не суть. Вы в принципе неправильно подходите к ID. Его нельзя использовать для подобных целей. Если Вам нужна нумерация используйте нумерацию, но не ID.
Но если Вам так нужно использовать ID, то перед вставкой проверяйте наличие записи в БД, например,

insert into Photo(...) SELECT ... FROM dual WHERE NOT EXISTS(SELECT 1 FROM Photo WHERE link=l);

Форум пользователей MySQL

#1 08.06.2018 16:32:50

Непонятное поведение MySQL 8.0 при последовательной записи в базу через функцию

#2 08.06.2018 18:06:55

Re: Непонятное поведение MySQL 8.0 при последовательной записи в базу через функцию

#3 08.06.2018 21:43:26

Re: Непонятное поведение MySQL 8.0 при последовательной записи в базу через функцию

#4 08.06.2018 22:00:37

Re: Непонятное поведение MySQL 8.0 при последовательной записи в базу через функцию

Board footer