Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4779

Spider sort can produce null field

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Incomplete
    • Affects Version/s: 10.0.3
    • Fix Version/s: 10.0.6
    • Component/s: None
    • Labels:
    • Environment:
      10.0.2-MariaDB

      Linux version 3.2.0-4-amd64 (debian-kernel@lists.debian.org) (gcc version 4.6.3 (Debian 4.6.3-14) ) #1 SMP Debian 3.2.46-1

      Description

      SELECT hostname_max,db_max FROM `global_query_review_history` WHERE `checksum`='5922892408454701127' ORDER BY `ts_min` DESC LIMIT 1
          -> ;
      +--------------+----------+
      | hostname_max | db_max   |
      +--------------+----------+
      | 83.96.131.31 | services |
      +--------------+----------+
      1 row in set (0.25 sec)

      Adding the sample column it breack the result

      mysql> SELECT hostname_max,db_max, sample FROM `global_query_review_history` WHERE `checksum`='5922892408454701127' ORDER BY `ts_min` DESC LIMIT 1
          -> ;
      +--------------+--------+--------+
      | hostname_max | db_max | sample |
      +--------------+--------+--------+
      |              | NULL   |        |
      +--------------+--------+--------+
      1 row in set (0.11 sec)
       
      mysql> SELECT hostname_max,db_max, sample FROM `global_query_review_history` WHERE `checksum`='5922892408454701127' LIMIT 1;
      +--------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | hostname_max | db_max   | sample                                                                                                                                                                                                                       |
      +--------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | 83.96.131.31 | services | SELECT `lb`.*, `c`.* FROM `lotBids` AS `lb`
       INNER JOIN `customers` AS `c` ON c.customerId = lb.customerId
       INNER JOIN `lots` AS `l` ON l.lotId = lb.lotId WHERE (l.lotId = '6875336') ORDER BY `lb`.`muPrice` DESC LIMIT 10 |
      +--------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

       CREATE TABLE `global_query_review_history` (
        `hostname_max` varchar(64) NOT NULL,
        `db_max` varchar(64) DEFAULT NULL,
        `checksum` bigint(20) unsigned NOT NULL,
        `sample` longtext NOT NULL,
        `ts_min` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        `ts_max` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        `ts_cnt` float DEFAULT NULL,
        `Query_time_sum` float DEFAULT NULL,
        `Query_time_min` float DEFAULT NULL,
        `Query_time_max` float DEFAULT NULL,
        `Query_time_pct_95` float DEFAULT NULL,
        `Query_time_stddev` float DEFAULT NULL,
        `Query_time_median` float DEFAULT NULL,
        `Lock_time_sum` float DEFAULT NULL,
        `Lock_time_min` float DEFAULT NULL,
        `Lock_time_max` float DEFAULT NULL,
        `Lock_time_pct_95` float DEFAULT NULL,
        `Lock_time_stddev` float DEFAULT NULL,
        `Lock_time_median` float DEFAULT NULL,
        `Rows_sent_sum` float DEFAULT NULL,
        `Rows_sent_min` float DEFAULT NULL,
        `Rows_sent_max` float DEFAULT NULL,
        `Rows_sent_pct_95` float DEFAULT NULL,
        `Rows_sent_stddev` float DEFAULT NULL,
        `Rows_sent_median` float DEFAULT NULL,
        `Rows_examined_sum` float DEFAULT NULL,
        `Rows_examined_min` float DEFAULT NULL,
        `Rows_examined_max` float DEFAULT NULL,
        `Rows_examined_pct_95` float DEFAULT NULL,
        `Rows_examined_stddev` float DEFAULT NULL,
        `Rows_examined_median` float DEFAULT NULL,
        `Rows_affected_sum` float DEFAULT NULL,
        `Rows_affected_min` float DEFAULT NULL,
        `Rows_affected_max` float DEFAULT NULL,
        `Rows_affected_pct_95` float DEFAULT NULL,
        `Rows_affected_stddev` float DEFAULT NULL,
        `Rows_affected_median` float DEFAULT NULL,
        `Rows_read_sum` float DEFAULT NULL,
        `Rows_read_min` float DEFAULT NULL,
        `Rows_read_max` float DEFAULT NULL,
        `Rows_read_pct_95` float DEFAULT NULL,
        `Rows_read_stddev` float DEFAULT NULL,
        `Rows_read_median` float DEFAULT NULL,
        `Merge_passes_sum` float DEFAULT NULL,
        `Merge_passes_min` float DEFAULT NULL,
        `Merge_passes_max` float DEFAULT NULL,
        `Merge_passes_pct_95` float DEFAULT NULL,
        `Merge_passes_stddev` float DEFAULT NULL,
        `Merge_passes_median` float DEFAULT NULL,
        `InnoDB_IO_r_ops_min` float DEFAULT NULL,
        `InnoDB_IO_r_ops_max` float DEFAULT NULL,
        `InnoDB_IO_r_ops_pct_95` float DEFAULT NULL,
        `InnoDB_IO_r_bytes_pct_95` float DEFAULT NULL,
        `InnoDB_IO_r_bytes_stddev` float DEFAULT NULL,
        `InnoDB_IO_r_bytes_median` float DEFAULT NULL,
        `InnoDB_IO_r_wait_min` float DEFAULT NULL,
        `InnoDB_IO_r_wait_max` float DEFAULT NULL,
        `InnoDB_IO_r_wait_pct_95` float DEFAULT NULL,
        `InnoDB_IO_r_ops_stddev` float DEFAULT NULL,
        `InnoDB_IO_r_ops_median` float DEFAULT NULL,
        `InnoDB_IO_r_bytes_min` float DEFAULT NULL,
        `InnoDB_IO_r_bytes_max` float DEFAULT NULL,
        `InnoDB_IO_r_wait_stddev` float DEFAULT NULL,
        `InnoDB_IO_r_wait_median` float DEFAULT NULL,
        `InnoDB_rec_lock_wait_min` float DEFAULT NULL,
        `InnoDB_rec_lock_wait_max` float DEFAULT NULL,
        `InnoDB_rec_lock_wait_pct_95` float DEFAULT NULL,
        `InnoDB_rec_lock_wait_stddev` float DEFAULT NULL,
        `InnoDB_rec_lock_wait_median` float DEFAULT NULL,
        `InnoDB_queue_wait_min` float DEFAULT NULL,
        `InnoDB_queue_wait_max` float DEFAULT NULL,
        `InnoDB_queue_wait_pct_95` float DEFAULT NULL,
        `InnoDB_queue_wait_stddev` float DEFAULT NULL,
        `InnoDB_queue_wait_median` float DEFAULT NULL,
        `InnoDB_pages_distinct_min` float DEFAULT NULL,
        `InnoDB_pages_distinct_max` float DEFAULT NULL,
        `InnoDB_pages_distinct_pct_95` float DEFAULT NULL,
        `InnoDB_pages_distinct_stddev` float DEFAULT NULL,
        `InnoDB_pages_distinct_median` float DEFAULT NULL,
        `QC_Hit_cnt` float DEFAULT NULL,
        `QC_Hit_sum` float DEFAULT NULL,
        `Full_scan_cnt` float DEFAULT NULL,
        `Full_scan_sum` float DEFAULT NULL,
        `Full_join_cnt` float DEFAULT NULL,
        `Full_join_sum` float DEFAULT NULL,
        `Tmp_table_cnt` float DEFAULT NULL,
        `Tmp_table_sum` float DEFAULT NULL,
        `Filesort_cnt` float DEFAULT NULL,
        `Filesort_sum` float DEFAULT NULL,
        `Tmp_table_on_disk_cnt` float DEFAULT NULL,
        `Tmp_table_on_disk_sum` float DEFAULT NULL,
        `Filesort_on_disk_cnt` float DEFAULT NULL,
        `Filesort_on_disk_sum` float DEFAULT NULL,
        `Bytes_sum` float DEFAULT NULL,
        `Bytes_min` float DEFAULT NULL,
        `Bytes_max` float DEFAULT NULL,
        `Bytes_pct_95` float DEFAULT NULL,
        `Bytes_stddev` float DEFAULT NULL,
        `Bytes_median` float DEFAULT NULL,
        KEY `hostname_max` (`hostname_max`,`checksum`,`ts_min`,`ts_max`),
        KEY `ts_min` (`ts_min`),
        KEY `checksum` (`checksum`)
      ) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='user "skysql",password "skyvodka"'
      /*!50100 PARTITION BY LIST (mod(checksum,4))
      (PARTITION pt0 VALUES IN (0) COMMENT = ' tbl "global_query_review_history", host  "192.168.0.30 192.168.0.30", port "5055 5054"' ENGINE = SPIDER,
       PARTITION pt1 VALUES IN (1) COMMENT = ' tbl "global_query_review_history", host "192.168.0.30 192.168.0.30", port "5054 5055"' ENGINE = SPIDER,
       PARTITION pt2 VALUES IN (2) COMMENT = ' tbl "global_query_review_history", host "192.168.0.30 192.168.0.30", port "5056 5057"' ENGINE = SPIDER,
       PARTITION pt3 VALUES IN (3) COMMENT = ' tbl "global_query_review_history", host "192.168.0.30 192.168.0.30", port "5057 5056"' ENGINE = SPIDER) */ 

      Going to the shard the result is good

      alias db-78b9ef7c='/usr/local/skysql/mysql-client/bin/mysql  --user=skysql --password=skyvodka --host=192.168.0.30 --port=5056'
       
       
      db-78b9ef7c slow_query_log -e"SELECT hostname_max,db_max, sample FROM global_query_review_history WHERE checksum='5922892408454701127' ORDER BY ts_min DESC LIMIT 1"
      +--------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | hostname_max | db_max   | sample                                                                                                                                                                                                                      |
      +--------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | 83.96.131.31 | services | SELECT `lb`.*, `c`.* FROM `lotBids` AS `lb`
       INNER JOIN `customers` AS `c` ON c.customerId = lb.customerId
       INNER JOIN `lots` AS `l` ON l.lotId = lb.lotId WHERE (l.lotId = '259042') ORDER BY `lb`.`muPrice` DESC LIMIT 10 |
      +--------------+----------+-----------------------------------------------------------------------------------------
       
      show variables like 'spider%';
      +---------------------------------------+----------------------+
      | Variable_name                         | Value                |
      +---------------------------------------+----------------------+
      | spider_auto_increment_mode            | 4294967295           |
      | spider_bgs_first_read                 | 18446744073709551615 |
      | spider_bgs_mode                       | 4294967295           |
      | spider_bgs_second_read                | 18446744073709551615 |
      | spider_bka_engine                     |                      |
      | spider_bka_mode                       | 4294967295           |
      | spider_block_size                     | 16384                |
      | spider_bulk_size                      | 4294967295           |
      | spider_bulk_update_mode               | 4294967295           |
      | spider_bulk_update_size               | 4294967295           |
      | spider_conn_recycle_mode              | 1                    |
      | spider_conn_recycle_strict            | 0                    |
      | spider_connect_mutex                  | OFF                  |
      | spider_connect_retry_count            | 1000                 |
      | spider_connect_retry_interval         | 1000                 |
      | spider_connect_timeout                | 4294967295           |
      | spider_crd_bg_mode                    | 1                    |
      | spider_crd_interval                   | 4                    |
      | spider_crd_mode                       | 1                    |
      | spider_crd_sync                       | 0                    |
      | spider_crd_type                       | 4294967295           |
      | spider_crd_weight                     | 4294967295           |
      | spider_direct_dup_insert              | 1                    |
      | spider_direct_order_limit             | 18446744073709551615 |
      | spider_error_read_mode                | 4294967295           |
      | spider_error_write_mode               | 4294967295           |
      | spider_first_read                     | 18446744073709551615 |
      | spider_force_commit                   | 1                    |
      | spider_init_sql_alloc_size            | 4294967295           |
      | spider_internal_limit                 | 18446744073709551615 |
      | spider_internal_offset                | 18446744073709551615 |
      | spider_internal_optimize              | 4294967295           |
      | spider_internal_optimize_local        | 4294967295           |
      | spider_internal_sql_log_off           | ON                   |
      | spider_internal_unlock                | OFF                  |
      | spider_internal_xa                    | OFF                  |
      | spider_internal_xa_snapshot           | 0                    |
      | spider_local_lock_table               | OFF                  |
      | spider_lock_exchange                  | OFF                  |
      | spider_low_mem_read                   | 4294967295           |
      | spider_max_order                      | 4294967295           |
      | spider_multi_split_read               | 4294967295           |
      | spider_net_read_timeout               | 4294967295           |
      | spider_net_write_timeout              | 4294967295           |
      | spider_ping_interval_at_trx_start     | 3600                 |
      | spider_quick_mode                     | 4294967295           |
      | spider_quick_page_size                | 18446744073709551615 |
      | spider_read_only_mode                 | 4294967295           |
      | spider_remote_access_charset          |                      |
      | spider_remote_autocommit              | 1                    |
      | spider_remote_default_database        |                      |
      | spider_remote_sql_log_off             | 1                    |
      | spider_remote_time_zone               |                      |
      | spider_remote_trx_isolation           | 4294967295           |
      | spider_reset_sql_alloc                | 0                    |
      | spider_same_server_link               | OFF                  |
      | spider_second_read                    | 18446744073709551615 |
      | spider_select_column_mode             | 4294967295           |
      | spider_selupd_lock_mode               | 4294967295           |
      | spider_semi_split_read                | 4294967295           |
      | spider_semi_split_read_limit          | 18446744073709551615 |
      | spider_semi_table_lock                | 1                    |
      | spider_semi_table_lock_connection     | 4294967295           |
      | spider_semi_trx                       | ON                   |
      | spider_semi_trx_isolation             | 4294967295           |
      | spider_skip_default_condition         | 4294967295           |
      | spider_split_read                     | 18446744073709551615 |
      | spider_sts_bg_mode                    | 4294967295           |
      | spider_sts_interval                   | 4294967295           |
      | spider_sts_mode                       | 4294967295           |
      | spider_sts_sync                       | 0                    |
      | spider_support_xa                     | ON                   |
      | spider_sync_autocommit                | OFF                  |
      | spider_sync_time_zone                 | OFF                  |
      | spider_sync_trx_isolation             | OFF                  |
      | spider_table_init_error_interval      | 1                    |
      | spider_udf_ct_bulk_insert_interval    | 4294967295           |
      | spider_udf_ct_bulk_insert_rows        | 18446744073709551615 |
      | spider_udf_ds_bulk_insert_rows        | 18446744073709551615 |
      | spider_udf_ds_table_loop_mode         | 4294967295           |
      | spider_udf_ds_use_real_table          | 4294967295           |
      | spider_udf_table_lock_mutex_count     | 20                   |
      | spider_udf_table_mon_mutex_count      | 20                   |
      | spider_use_all_conns_snapshot         | OFF                  |
      | spider_use_consistent_snapshot        | OFF                  |
      | spider_use_default_database           | ON                   |
      | spider_use_flash_logs                 | OFF                  |
      | spider_use_handler                    | 1                    |
      | spider_use_pushdown_udf               | 4294967295           |
      | spider_use_snapshot_with_flush_tables | 0                    |
      | spider_use_table_charset              | 4294967295           |
       

        Attachments

          Activity

            People

            Assignee:
            svoj Sergey Vojtovich
            Reporter:
            stephane@skysql.com VAROQUI Stephane
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration