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

Details

Type: Bug
Status: Closed
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           |

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Sergey Vojtovich added a comment - 2013-11-08 09:26

Unfortunately we were unable to reproduce this bug. If you're still experiencing it, please provide table data.

Show

Sergey Vojtovich added a comment - 2013-11-08 09:26 Unfortunately we were unable to reproduce this bug. If you're still experiencing it, please provide table data.

Hide

Permalink

Ann Chi added a comment - 2014-06-06 09:18

Hi Sergey,
I thought my posted issue (~~MDEV-6301~~ https://mariadb.atlassian.net/browse/MDEV-6301 ) is same as Stephane.
Our solution is modifying the field to length<227. But while we are not using the same column to sort, it will produce null/0 value again.

Show

Ann Chi added a comment - 2014-06-06 09:18 Hi Sergey, I thought my posted issue ( MDEV-6301 https://mariadb.atlassian.net/browse/MDEV-6301 ) is same as Stephane. Our solution is modifying the field to length<227. But while we are not using the same column to sort, it will produce null/0 value again.

Hide

Permalink

Sergey Vojtovich added a comment - 2014-06-06 20:18

Hi Ann Chi,

I just reassigned ~~MDEV-6301~~ to Kentoku (Spider author). Let's hope he will be able to reproduce this problem.
Please feel free to discuss this issue directly with Kentoku.

Show

Sergey Vojtovich added a comment - 2014-06-06 20:18 Hi Ann Chi , I just reassigned MDEV-6301 to Kentoku (Spider author). Let's hope he will be able to reproduce this problem. Please feel free to discuss this issue directly with Kentoku.

Spider sort can produce null field

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile