[MDEV-4779] Spider sort can produce null field - Jira

XML

Word

Printable

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:
- spider
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:: Sergey Vojtovich

Reporter:: VAROQUI Stephane

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2013-07-11 19:09

Updated:: 2014-06-06 20:18

Resolved:: 2013-11-08 09:26

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.