Общее количество блокировок превышает размер блокировки стола

Я запускаю отчет в MySQL. Один из запросов включает вложение большого количества строк в таблицу temp. Когда я пытаюсь запустить его, я получаю эту ошибку:

Код ошибки 1206: количество блокировок превышает размер таблицы блокировок.

Задаваемые запросы:

create temporary table SkusBought(
customerNum int(11),
sku int(11),
typedesc char(25),
key `customerNum` (customerNum)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into skusBought
select t1.* from
    (select customer, sku, typedesc from transactiondatatransit
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku
union
select customer, sku, typedesc from transactiondatadelaware
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku
union
select customer, sku, typedesc from transactiondataprestige
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku) t1
join
(select customernum from topThreetransit group by customernum) t2
on t1.customer = t2.customernum;

Я читал, что изменение файла конфигурации для увеличения размера пула буферов поможет, но это ничего не делает. Какой способ исправить это, как временное обходное решение или постоянное исправление?

EDIT: изменена часть запроса. Это не должно повлиять на это, но я все-таки нашел-заменил все и не понял, что это испортило это. Не влияет на вопрос.

EDIT 2: Добавлен typedesc в t1. Я изменил его в запросе, но не здесь.

Ответы

Ответ 1

Эта проблема может быть решена путем установки более высоких значений для переменной MySQL innodb_buffer_pool_size. Значение по умолчанию для innodb_buffer_pool_size будет 8,388,608.

Чтобы изменить значение настроек для innodb_buffer_pool_size, см. ниже установленный.

  • Найдите файл my.cnf с сервера. Для серверов Linux это будет в основном на /etc/my.cnf
  • Добавьте строку innodb_buffer_pool_size=64MB в этот файл
  • Перезагрузите сервер MySQL

Чтобы перезагрузить сервер MySQL, вы можете использовать любой из следующих двух вариантов:

  • служба mysqld restart
  • /etc/init.d/mysqld restart

Ссылка Общее количество блокировок превышает размер таблицы блокировок

Ответ 2

Я нашел другой способ решить эту проблему - использовать Table Lock. Конечно, это может быть неуместно для вашего приложения - если вам нужно обновить таблицу одновременно.

См: Попробуйте использовать LOCK TABLES, чтобы заблокировать всю таблицу, вместо действия по умолчанию на уровне строк InnoDB MVCC. Если я не ошибаюсь, "таблица блокировки" относится к внутренней структуре InnoDB, в которой хранятся идентификаторы строк и версий для реализации MVCC с битом, идентифицирующим строку, которая изменяется в инструкции и с таблицей в 60 миллионов строк, вероятно, превышает выделенную ему память. Команда LOCK TABLES должна устранить эту проблему, установив блокировку на уровне таблицы вместо уровня строки:

SET @@AUTOCOMMIT=0;
LOCK TABLES avgvol WRITE, volume READ;
INSERT INTO avgvol(date,vol)
SELECT date,avg(vol) FROM volume
GROUP BY date;
UNLOCK TABLES;

Джей Пайпс, Менеджер по связям с общественностью, Северная Америка, MySQL Inc.

Ответ 3

Из документации MySQL (которую вы уже читали, как я вижу):

1206 (ER_LOCK_TABLE_FULL)

Общее количество блокировок превышает размер таблицы блокировок. Чтобы избежать этой ошибки, увеличьте значение innodb_buffer_pool_size. В рамках отдельного приложения обходной путь может заключаться в том, чтобы разбить большую операцию на более мелкие куски. Например, если ошибка возникает для большого INSERT, выполните несколько меньших операций INSERT.

Если увеличение innodb_buffer_pool_size не помогает, просто следуйте указаниям на выделенной полуживой части и разделите свой INSERT на 3. Пропустите UNION и сделайте 3 INSERT, каждый из которых имеет JOIN в таблице topThreetransit.

Ответ 4

Если вы правильно структурировали свои таблицы, чтобы каждый из них имел относительно уникальные значения, то менее интенсивным способом сделать это было бы сделать 3 отдельных оператора insert-in, 1 для каждой таблицы, с фильтром соединения для каждая вставка -

INSERT INTO SkusBought...

SELECT t1.customer, t1.SKU, t1.TypeDesc
FROM transactiondatatransit AS T1
LEFT OUTER JOIN topThreetransit AS T2
ON t1.customer = t2.customernum
WHERE T2.customernum IS NOT NULL

Повторите это для двух других таблиц - copy/paste - это прекрасный метод, просто измените имя таблицы FROM. ** Если вы пытаетесь предотвратить дублирование записей в таблице SkusBought, вы можете добавить следующий код соединения в каждом разделе перед предложением WHERE.

LEFT OUTER JOIN SkusBought AS T3
ON  t1.customer = t3.customer
AND t1.sku = t3.sku

- и затем последняя строка предложения WHERE -

AND t3.customer IS NULL

В исходном коде используется несколько подзапросов, и оператор UNION может быть дорогостоящим, так как он сначала создаст свою собственную временную таблицу, чтобы заполнить данные из трех отдельных источников, прежде чем вставлять их в таблицу, в которой вы хотите работать ALONG другой подзапрос для фильтрации результатов.

Ответ 5

в windows: если у вас есть workbench mysql. Перейти к состоянию сервера. найти местоположение исполняемого файла сервера в моем случае:

C:\ProgramData\MySQL\MySQL Server 5.7

откройте файл my.ini и найдите buffer_pool_size. Установите значение high. значение по умолчанию - 8M. Вот как я исправил эту проблему

Ответ 6

Я запускаю окна MySQL с Workbench MySql. Перейдите в C:\ProgramData\MySQL\...\my.ini Сервер> Состояние сервера. В верхней части файла указывается файл конфигурации: "путь" (C:\ProgramData\MySQL\...\my.ini)

Затем в файле "my.ini" нажмите buffer_pool_size + F и найдите buffer_pool_size. Установите значение выше, я бы рекомендовал 64 МБ (по умолчанию 8 МБ).

Перезапустите sruver, перейдя в Instance> Startup/Shutdown> Stop server (а затем снова запустите сервер)

В моем случае я не смог удалить записи из своей таблицы.

Ответ 7

Стоит сказать, что фигура, используемая для этой настройки, находится в BYTES - это было трудно!

Ответ 8

Во-первых, вы можете использовать команду sql show global variables like 'innodb_buffer%'; для проверки размера буфера.

Решение найдет ваш файл my.cnf и добавит,

[mysqld]
innodb_buffer_pool_size=1G # depends on your data and machine