Início / dba / Perguntas / 114256
Accepted
Ario
Asked: 2015-09-05 23:23:01 +0800 CST

Por que "select" funciona no MariaDB, mas não funciona no MySQL

  • 772
set @row_number = 0;
SELECT 
    *
FROM
    (SELECT 
        (@row_number:=@row_number + 1) AS num,
        id,
        tbl_user_id,
        title,
        description,
        length lengths,
        create_date,
        file_size,
        thumbnails,
        videos.itsOK,
        viewed
    FROM
        tbl_videos videos
    WHERE
        videos.tbl_user_id = 23
            AND videos.tbl_category_id = 265
        ORDER BY videos.create_date DESC
) AS paginateTbl
WHERE
    paginateTbl.num > 0
        && paginateTbl.num <= 9

resultados do mysql: resultados mysql

resultados mariadb: insira a descrição da imagem aqui

A consulta interna funciona em ambos, mas a consulta principal funciona apenas no mariadb! Qual é o problema no mysql não está funcionando?

As versões usadas são mysql: 5.5.44-0ubuntu0.14.04.1e mariadb 10.0.13-MariaDB-log.

As CREATE TABLEinstruções são idênticas (exceto pelo AUTO_INCREMENTnúmero de linhas):

Resultado do MySQL:

SHOW CREATE TABLE tbl_videos;

CREATE TABLE `tbl_videos` (
    `id` INT (20) NOT NULL AUTO_INCREMENT
    ,`title` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`description` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tags` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`video_quality` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link1` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link2` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link3` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed` INT (11) NOT NULL
    ,`viewed_duration` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed_traffic` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`embed_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`sharing_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`replace_times` INT (11) NOT NULL
    ,`actual_link` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tbl_user_id` INT (11) NOT NULL
    ,`tbl_category_id` INT (11) NOT NULL
    ,`tbl_player_id` INT (11) NOT NULL
    ,`itsOK` TINYINT (2) NOT NULL
    ,`length` INT (20) NOT NULL
    ,`create_date` INT (11) NOT NULL
    ,`modified_date` INT (11) NOT NULL
    ,`thumbnails` TEXT COLLATE utf8_persian_ci
    ,`serverId` VARCHAR(32) COLLATE utf8_persian_ci NOT NULL
    ,`sizes` VARCHAR(100) COLLATE utf8_persian_ci DEFAULT NULL
    ,`our_server_link` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`like` INT (11) NOT NULL DEFAULT '0'
    ,`file_size` FLOAT DEFAULT NULL
    ,`islogo` TEXT COLLATE utf8_persian_ci
    ,`uuid` VARCHAR(64) COLLATE utf8_persian_ci DEFAULT NULL
    ,`output_type` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_file` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_setting` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`soft_hard` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`soft_hard_logo` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`vastTag` TEXT COLLATE utf8_persian_ci
    ,`extra_cat_id` INT (11) NOT NULL DEFAULT '0'
    ,`all_terafic` BIGINT (20) NOT NULL DEFAULT '0'
    ,PRIMARY KEY (`id`)
    ,KEY `tbl_user_id`(`tbl_user_id`)
    ,KEY `tbl_category_id`(`tbl_category_id`)
    ,CONSTRAINT `tbl_videos_ibfk_1` FOREIGN KEY (`tbl_user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ,CONSTRAINT `tbl_videos_ibfk_2` FOREIGN KEY (`tbl_category_id`) REFERENCES `tbl_categories`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = InnoDB AUTO_INCREMENT = 4622 DEFAULT CHARSET = utf8 COLLATE = utf8_persian_ci

Resultado do MariaDB:

SHOW CREATE TABLE tbl_videos;

CREATE TABLE `tbl_videos` (
    `id` INT (20) NOT NULL AUTO_INCREMENT
    ,`title` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`description` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tags` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`video_quality` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link1` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link2` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link3` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed` INT (11) NOT NULL
    ,`viewed_duration` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed_traffic` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`embed_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`sharing_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`replace_times` INT (11) NOT NULL
    ,`actual_link` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tbl_user_id` INT (11) NOT NULL
    ,`tbl_category_id` INT (11) NOT NULL
    ,`tbl_player_id` INT (11) NOT NULL
    ,`itsOK` TINYINT (2) NOT NULL
    ,`length` INT (20) NOT NULL
    ,`create_date` INT (11) NOT NULL
    ,`modified_date` INT (11) NOT NULL
    ,`thumbnails` TEXT COLLATE utf8_persian_ci
    ,`serverId` VARCHAR(32) COLLATE utf8_persian_ci NOT NULL
    ,`sizes` VARCHAR(100) COLLATE utf8_persian_ci DEFAULT NULL
    ,`our_server_link` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`like` INT (11) NOT NULL DEFAULT '0'
    ,`file_size` FLOAT DEFAULT NULL
    ,`islogo` TEXT COLLATE utf8_persian_ci
    ,`uuid` VARCHAR(64) COLLATE utf8_persian_ci DEFAULT NULL
    ,`output_type` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_file` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_setting` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`soft_hard` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`soft_hard_logo` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`vastTag` TEXT COLLATE utf8_persian_ci
    ,`extra_cat_id` INT (11) NOT NULL DEFAULT '0'
    ,`all_terafic` BIGINT (20) NOT NULL DEFAULT '0'
    ,PRIMARY KEY (`id`)
    ,KEY `tbl_user_id`(`tbl_user_id`)
    ,KEY `tbl_category_id`(`tbl_category_id`)
    ,CONSTRAINT `tbl_videos_ibfk_1` FOREIGN KEY (`tbl_user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ,CONSTRAINT `tbl_videos_ibfk_2` FOREIGN KEY (`tbl_category_id`) REFERENCES `tbl_categories`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = InnoDB AUTO_INCREMENT = 9387 DEFAULT CHARSET = utf8 COLLATE = utf8_persian_ci

resultados do mysql:

EXPLAIN SELECT * from FROM ...
id  select_type table       type    possible_keys   key key_len ref rows    Extra
1   PRIMARY     <derived2>  ALL NULL    NULL    NULL    NULL    14  Using where
2   DERIVED     videos      index_merge tbl_user_id,tbl_category_id tbl_category_id,tbl_user_id 4,4 NULL    1   Using intersect(tbl_category_id,tbl_user_id); Using where; Using filesort

resultados mariadb:

EXPLAIN SELECT * from tbl_videos
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    2   Using where
2   DERIVED videos  index_merge tbl_user_id,tbl_category_id tbl_category_id,tbl_user_id 4,4 NULL    1   Using intersect(tbl_category_id,tbl_user_id); Using where; Using filesort
mysql mariadb

1 respostas

  1. Best Answer

    É o bug do MySQL 5.5 que foi relatado ao MySQL . então instalei o mysql 5.6 e a consulta principal funciona bem. mesma consulta com @@version nos resultados.

    set @row_number = 0;
SELECT 
    *, @@version mysql_version
FROM
    (SELECT 
        (@row_number:=@row_number + 1) AS num,
        id,
        tbl_user_id,
        title,
        description,
        length lengths,
        create_date,
        file_size,
        thumbnails,
        videos.itsOK,
        viewed
    FROM
        tbl_videos videos
    WHERE
        videos.tbl_user_id = 9
            AND videos.tbl_category_id = 113
            AND length > 0
        ORDER BY videos.create_date ASC
) AS paginateTbl
WHERE
    paginateTbl.num > 0
        && paginateTbl.num <= 9

    mysql 5.5 & mysql 5.6 principais resultados da consulta: mysql 5.5 bug - consulta principal mysql 5.6 - main qu

    Agora eu verifico um id especial, por exemplo: 1103 em tbl_videos e ambos funcionam bem.

    SELECT 
    id,
    tbl_user_id,
    title,
    description,
    length lengths,
    create_date,
    file_size,
    thumbnails,
    itsOK,
    viewed,
    @@version mysql_version
FROM
    tbl_videos
WHERE
    id = 1103

    mysql 5.5 e mysql 5.6 resultados: mysql 5.5 bug - pequena consulta mysql 5.6 - consulta minúscula

    • 4

relate perguntas