Hiya
sorry its been a while
Recap:
- I have a branch up on LP with my changes to support the string-based latch
- it also interprets numeric values passed to the latch to support backwards
compatibility
- however if the numeric latch is specified as a number instead of a string
(e.g. select ... where latch=3 vs. select ... where latch='3' ) it fails
In recent conversation with Arjen:
>> Although there was some discussion around what to do
>> about legacy instances because of the problem with numeric autocast?
>
> Is it that the server doesn't use the correct (indexed) access method because
> of the cast?
> Show me a trace (do use the oqgraph-dev list)
> If that is the case, then perhaps returning an error if a numeric latch is
> seen might be the solution.
Following is a big dump of test and trace, you probably wont have enough
context so I am bracing for clarifying questions :-)
But basically I dont yet have enough understanding of storage engine guts to
know how to hook a query before the query optimiser bypasses us
I think the more important question, is how to handle (not) breaking existing
deployments.
After all, new databases can just be forced to use the new syntax.
But if any existing db is upgraded, our code will need to properly handle the
legacy form on upgrade....
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
To repeat the issue on my branch:
DROP TABLE IF EXISTS graph;
DROP TABLE IF EXISTS graph_base;
DROP TABLE IF EXISTS graph2;
CREATE TABLE graph_base (
from_id INT UNSIGNED NOT NULL,
to_id INT UNSIGNED NOT NULL,
PRIMARY KEY (from_id,to_id),
INDEX (to_id)
) ENGINE=MyISAM;
CREATE TABLE graph2 (
latch VARCHAR(32) NULL,
origid BIGINT UNSIGNED NULL,
destid BIGINT UNSIGNED NULL,
weight DOUBLE NULL,
seq BIGINT UNSIGNED NULL,
linkid BIGINT UNSIGNED NULL,
KEY (latch, origid, destid) USING HASH,
KEY (latch, destid, origid) USING HASH
) ENGINE=OQGRAPH DATA_TABLE='graph_base' ORIGID='from_id', DESTID='to_id';
INSERT INTO graph_base(from_id, to_id) VALUES (1,2), (2,1);
INSERT INTO graph_base(from_id, to_id) VALUES (1,3), (3,1);
INSERT INTO graph_base(from_id, to_id) VALUES (3,4), (4,3);
INSERT INTO graph_base(from_id, to_id) VALUES (5,6), (6,5);
-- expected:
-- +-------+--------+--------+--------+------+--------+
-- | latch | origid | destid | weight | seq | linkid |
-- +-------+--------+--------+--------+------+--------+
-- | 2 | 1 | NULL | 1 | 3 | 3 |
-- | 2 | 1 | NULL | 1 | 2 | 2 |
-- +-------+--------+--------+--------+------+--------+
-- reset query cache ; flush query cache;
SELECT * FROM graph2 WHERE latch = 'breadth_first' AND origid = 1 AND weight =
1; -- works
SELECT * FROM graph2 WHERE latch = '2' AND origid = 1 AND weight = 1;
-- as above
SELECT * FROM graph2 WHERE latch = 2 AND origid = 1 AND weight = 1;
-- currently, FAILs - returns empty set should be as above
I have a pile of debug output in ha_oqgraph, it can be turned on using
mysqld <...> -#d,oq-debug
I build with:
cmake -DENABLE_ASSEMBLER=1 -DWITH_EXTRA_CHARSETS=complex
-DENABLE_THREAD_SAFE_CLIENT=1 -DWITH_BIG_TABLES=1 -DWITH_PLUGIN_ARIA=1
-DWITH_ARIA_TMP_TABLES=1 -DWITH_READLINE=1 -DCMAKE_BUILD_TYPE=Debug
-DWITH_SSL=bundled -DWITH_MAX=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_LIBEVENT=1
-DENABLE_LOCAL_INFILE=1
Log with string select:
THD::decide_logging_format: info: query: SELECT * FROM graph2 WHERE latch = '2'
AND origid = 1 AND weight = 1
THD::decide_logging_format: info: variables.binlog_format: 1
THD::decide_logging_format: info: lex->get_stmt_unsafe_flags(): 0x0
THD::decide_logging_format: info: decision: no logging since
mysql_bin_log.is_open() = 0 and (options & OPTION_BIN_LOG) = 0x40000 and
binlog_format = 1 and binlog_filter->db_ok(db) = 1
Field_iterator_table_ref::set_field_iterator: info: field_it for 'graph2' is
Field_iterator_table
my_malloc: info: memory_used: 32696 size: 8152
setup_fields: info: thd->mark_used_columns: 1
setup_fields: info: thd->mark_used_columns: 1
setup_conds: info: thd->mark_used_columns: 1
WHERE:(after remove) 0x2c59ab8 ((`test`.`graph2`.`weight` = 1) and multiple
equal('2', `test`.`graph2`.`latch`) and multiple equal(1,
`test`.`graph2`.`origid`))
my_malloc: info: memory_used: 40848 size: 1600
SQL_SELECT::test_quick_select: info: records: 8
SQL_SELECT::test_quick_select: info: Time to scan table: 1e9
my_malloc: info: memory_used: 42448 size: 4056
print_sel_tree: info: SEL_TREE: 0x2c8cb00 (tree scans) scans: latch,latch_2
check_quick_select: oq-debug: records_in_range ::>> inx=0
check_quick_select: oq-debug: records_in_range ::>> key0=2.
check_quick_select: oq-debug: records_in_range ::>> N=10
check_quick_select: oq-debug: records_in_range ::>> inx=1
check_quick_select: oq-debug: records_in_range ::>> key0=2.
print_sel_tree: info: SEL_TREE: 0x2c8cb00 (ROR scans) scans: (empty)
get_key_scans_params: info: Returning range plan for key latch, cost 3.01,
records 10
SQL_SELECT::test_quick_select: info: No range reads possible, trying to
construct index_merge
my_malloc: info: memory_used: 46504 size: 128
my_malloc: info: memory_used: 46632 size: 8
my_malloc: info: memory_used: 46640 size: 4056
my_free: info: memory_used: 50696 size: -4056
quick range select, key latch, length: 44
2/1 <= X <= 2/1
other_keys: 0x0:
best_access_path: info: Considering ref access on key latch
best_access_path: info: Considering ref access on key latch_2
get_best_combination: info: type: 4
my_malloc: info: memory_used: 46640 size: 8152
make_join_select: info: select_cond changes 0x0 -> 0x2c8bc70 at line 8835 tab
0x2c8b308
Info about JOIN
graph2 type: ALL q_keys: 3 refs: 0 key: -1 len: 0
quick select used:
quick range select, key latch, length: 44
JOIN::exec: info: Sending data
init_read_record: info: using rr_quick
handler::read_range_first: oq-debug: index_read_idx ::>>
search(latch:breadth_first,1,-1)
handler::read_range_first: oq-debug: search() = 0
handler::read_range_first: oq-debug: fill_record() ::>>
breadth_first,1,-1,2.000000,4,4
handler::multi_range_read_next: oq-debug: fill_record() ::>>
breadth_first,1,-1,1.000000,3,3
handler::multi_range_read_next: oq-debug: fill_record() ::>>
breadth_first,1,-1,1.000000,2,2
handler::multi_range_read_next: oq-debug: fill_record() ::>>
breadth_first,1,-1,0.000000,1,1
my_free: info: memory_used: 54792 size: -128
my_free: info: memory_used: 54664 size: -4056
my_free: info: memory_used: 50608 size: -8
mi_update_status: info: updating status: key_file: 3072 data_file: 72 rows: 8
mi_update_status: info: invalidator... './test/graph_base' (status update)
mi_lock_database: info: changed: 0 w_locks: 0
do_select: info: 2 records output
my_free: info: memory_used: 50600 size: -1600
handle_select: info: res: 0 report_error: 0
close_thread_tables: info: thd->open_tables: 0x2c84b00
MDL_context::release_locks_stored_before: info: found lock to release
ticket=0x2b205c0
dispatch_command: info: query ready
net_send_eof: info: EOF sent, so no more error sending allowed
my_free: info: memory_used: 49000 size: -8152
my_free: info: memory_used: 40848 size: -8152
do_command: info: Command on socket (35) = 3 (Query)
dispatch_command: info: command: 3
st_select_lex::add_item_to_list: info: Item: 0x2c58d58
mysql_execute_command: info: derived: 0 view: 0
column_bitmaps_signal: info: read_set: 0x2c84c10 write_set: 0x2c84c30
get_lock_data: info: count 1
get_lock_data: info: sql_lock->table_count 1 sql_lock->lock_count 1
lock_external: info: count 1
mi_get_status: info: name: ./test/graph_base.MYI key_file: 3072 data_file: 72
rows: 8 concurrent_insert: 0
Log with number:
THD::decide_logging_format: info: query: SELECT * FROM graph2 WHERE latch = 2
AND origid = 1 AND weight = 1
THD::decide_logging_format: info: variables.binlog_format: 1
THD::decide_logging_format: info: lex->get_stmt_unsafe_flags(): 0x0
THD::decide_logging_format: info: decision: no logging since
mysql_bin_log.is_open() = 0 and (options & OPTION_BIN_LOG) = 0x40000 and
binlog_format = 1 and binlog_filter->db_ok(db) = 1
Field_iterator_table_ref::set_field_iterator: info: field_it for 'graph2' is
Field_iterator_table
my_malloc: info: memory_used: 32696 size: 8152
setup_fields: info: thd->mark_used_columns: 1
setup_fields: info: thd->mark_used_columns: 1
setup_conds: info: thd->mark_used_columns: 1
WHERE:(after remove) 0x2c59ab0 ((`test`.`graph2`.`latch` = 2) and
(`test`.`graph2`.`weight` = 1) and multiple equal(1, `test`.`graph2`.`origid`))
my_malloc: info: memory_used: 40848 size: 1600
SQL_SELECT::test_quick_select: info: records: 8
SQL_SELECT::test_quick_select: info: Time to scan table: 1e9
my_malloc: info: memory_used: 42448 size: 4056
print_sel_tree: info: SEL_TREE: 0x2c8cd08 (tree scans) scans: (empty)
print_sel_tree: info: SEL_TREE: 0x2c8cd08 (ROR scans) scans: (empty)
get_key_scans_params: info: No 'range' table read plan found
SQL_SELECT::test_quick_select: info: No range reads possible, trying to
construct index_merge
my_free: info: memory_used: 46504 size: -4056
get_best_combination: info: type: 0
make_join_select: info: select_cond changes 0x0 -> 0x2c8b898 at line 8835 tab
0x2c8b0b8
Info about JOIN
graph2 type: ALL q_keys: 3 refs: 0 key: -1 len: 0
select used
JOIN::exec: info: Sending data
init_read_record: info: using rr_sequential
init_io_cache: info: init_io_cache: cachesize = 16384
sub_select: oq-debug: fill_record() ::>> -,1,2,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,2,1,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,1,3,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,3,1,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,3,4,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,4,3,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,5,6,1.000000,-1,-1
sub_select: oq-debug: fill_record() ::>> -,6,5,1.000000,-1,-1
mi_update_status: info: updating status: key_file: 3072 data_file: 72 rows: 8
mi_update_status: info: invalidator... './test/graph_base' (status update)
mi_lock_database: info: changed: 0 w_locks: 0
do_select: info: 0 records output
my_free: info: memory_used: 42448 size: -1600
handle_select: info: res: 0 report_error: 0
close_thread_tables: info: thd->open_tables: 0x2c84b00
MDL_context::release_locks_stored_before: info: found lock to release
ticket=0x2b5ad50
dispatch_command: info: query ready
net_send_eof: info: EOF sent, so no more error sending allowed
my_free: info: memory_used: 40848 size: -8152
my_service_thread_sleep: info: sleeping 30000000000 nano seconds
--
Mailing list: https://launchpad.net/~oqgraph-dev
Post to : oqgraph-dev@lists.launchpad.net
Unsubscribe : https://launchpad.net/~oqgraph-dev
More help : https://help.launchpad.net/ListHelp
