[MDEV-4779] Spider sort can produce null field Created: 2013-07-11  Updated: 2014-06-06  Resolved: 2013-11-08

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.3
Fix Version/s: 10.0.6

Type: Bug Priority: Critical
Reporter: VAROQUI Stephane Assignee: Sergey Vojtovich
Resolution: Incomplete Votes: 0
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           |
 
 



 Comments   
Comment by Sergey Vojtovich [ 2013-11-08 ]

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

Comment by Ann Chi [ 2014-06-06 ]

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.

Comment by Sergey Vojtovich [ 2014-06-06 ]

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.

Generated at Thu Feb 08 06:59:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.