Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MySQL > START TRANSACTION и @переменные

Автор: PriZraK 15.2.2013, 13:44

Здравствуйте.
Столкнулся с интересной проблемой при выполнении запроса. Проблема заключается в том, что «объединенный» запрос устанавливает неверные данные в переменные @ext и @id_gateway, но выполнив запрос SELECT отдельно, получаю искомые значения.
Проблемный запрос:

Код

SET AUTOCOMMIT=0;
START TRANSACTION;
    SELECT 
        @ext:=e.`ext`,
        @id_gateway:=e.`id_gateway`
    FROM 
        `gateways` g,
        `extensions` e
    WHERE 
        g.`id_region`=78 AND
        e.`id_gateway`=g.`id_gateway` AND
        e.`date_end`<=NOW()
    ORDER BY
        e.`date_end` ASC
    LIMIT 1;

    UPDATE 
        `extensions` 
    SET 
        `date_end`=NOW()
    WHERE 
        `ext`=@ext AND 
        `id_gateway`=@id_gateway;
COMMIT;
SET AUTOCOMMIT=1;

Вносит обновление в строку ext=5, id_gateway=3:

Код


ext    id_gateway    date_end
005    3    2013-02-15 14:28:05

Если выполнить SELECT отдельно:

Код

SELECT 
    e.`ext`,
    e.`id_gateway`
FROM 
    `gateways` g,
    `extensions` e
WHERE 
    g.`id_region`=78 AND
    e.`id_gateway`=g.`id_gateway` AND
    e.`date_end`<=NOW()
ORDER BY
    e.`date_end` ASC
LIMIT 1

, то получим искомые значения:

Код


ext    id_gateway
001    1

Структура таблиц и тестовые значения:

Код

CREATE TABLE IF NOT EXISTS `extensions` (
  `ext` smallint(3) unsigned zerofill NOT NULL DEFAULT '000',
  `id_gateway` int(10) unsigned NOT NULL DEFAULT '0',
  `date_end` datetime NOT NULL,
  UNIQUE KEY `EXTENSION` (`ext`,`id_gateway`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `extensions` (`ext`, `id_gateway`, `date_end`) VALUES
(001, 1, '0000-00-00 00:00:00'),
(001, 2, '0000-00-00 00:00:00'),
(001, 3, '0000-00-00 00:00:00'),
(002, 1, '0000-00-00 00:00:00'),
(002, 2, '0000-00-00 00:00:00'),
(002, 3, '0000-00-00 00:00:00'),
(003, 1, '0000-00-00 00:00:00'),
(003, 2, '0000-00-00 00:00:00'),
(003, 3, '0000-00-00 00:00:00'),
(004, 1, '0000-00-00 00:00:00'),
(004, 2, '0000-00-00 00:00:00'),
(004, 3, '0000-00-00 00:00:00'),
(005, 1, '0000-00-00 00:00:00'),
(005, 2, '0000-00-00 00:00:00'),
(005, 3, '0000-00-00 00:00:00');

CREATE TABLE IF NOT EXISTS `gateways` (
  `id_gateway` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_region` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id_gateway`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `gateways` (`id_gateway`, `id_region`) VALUES
(1, 78),
(2, 78),
(3, 78);

Версия MySQL 5.1.61-0.
Можно конечно разделить запрос на два и забыть про эту проблему, но хочу разобраться в чем же проблема.
Есть предположение, что проблема в ORDER BY.

Автор: Zloxa 15.2.2013, 14:50

1) ты сортируешься по date end и берешь первую запись. Если у тебя date_end не уникальна, ты берешь ЛЮБУЮ ПЕРВУЮ ПОПАВШУЮСЯ запись с минимальным date_end.
2) Если ты второй запрос выполняешь после коммита первой транзакции, которая устанавливает этот самый date end и ставит запись по факту в конец списка сортировки, откуда удивление что ты не получаешь этой же записи после повторного выполнения запроса?

Автор: PriZraK 15.2.2013, 16:04

1. Я пробовал использовать уникальное поле `date_end`, результат, к сожалению тот же — разные значения переменных на выходе для START TRANSACTION и для простого SELECT.

Код

CREATE TABLE IF NOT EXISTS `extensions` (
  `ext` smallint(3) unsigned zerofill NOT NULL DEFAULT '000',
  `id_gateway` int(10) unsigned NOT NULL DEFAULT '0',
  `date_end` datetime NOT NULL,
  UNIQUE KEY `EXTENSION` (`ext`,`id_gateway`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `extensions` (`ext`, `id_gateway`, `date_end`) VALUES
(001, 1, '2013-02-15 00:01:00'),
(001, 2, '2013-02-15 00:02:00'),
(001, 3, '2013-02-15 00:03:00'),
(002, 1, '2013-02-15 00:04:00'),
(002, 2, '2013-02-15 00:05:00'),
(002, 3, '2013-02-15 00:06:00'),
(003, 1, '2013-02-15 00:07:00'),
(003, 2, '2013-02-15 00:08:00'),
(003, 3, '2013-02-15 00:09:00'),
(004, 1, '2013-02-15 00:10:00'),
(004, 2, '2013-02-15 00:11:00'),
(004, 3, '2013-02-15 00:12:00'),
(005, 1, '2013-02-15 00:13:00'),
(005, 2, '2013-02-15 00:14:00'),
(005, 3, '2013-02-15 00:15:00');

CREATE TABLE IF NOT EXISTS `gateways` (
  `id_gateway` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_region` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id_gateway`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `gateways` (`id_gateway`, `id_region`) VALUES
(1, 78),
(2, 78),
(3, 78);

2. Оба запроса выполняется для одного и того же слепка базы, т.е. каждый раз данные в таблицы привожу к единому виду, поэтому условия у запросов равные.

Автор: Akina 15.2.2013, 17:11

Виновато не равенство дат, а LIMIT. Вернее, непонимание того, как выполняется запрос.
Замените

Код


    SELECT 
        @ext:=e.`ext`,
        @id_gateway:=e.`id_gateway`

на

Код


    SELECT 
        e.`ext`,
        e.`id_gateway`
    INTO
        @ext,
        @id_gateway

Присвоение выполняется для каждой записи, прошедшей отбор. Выборка в переменную - только после получения выходного набора (обрезки по LIMIT).

Автор: PriZraK 15.2.2013, 17:53

Akina, расцеловать бы тебя! Все получилось!