oqgraph-dev team mailing list archive

[Antony T Curtis <atcurtis@xxxxxxxxx>]

Personally, I believe that it would be simply better for the ::create
method to validate the table structure and for ::open to check and alter
behaviour accordingly. So, if the latch column is declared as an integer
column, handle it as such. If it is an enum, check that it makes sense
otherwise if it's a varchar, do the string to type manual mapping.

Just my 2¢

Antony
On Apr 28, 2013 2:54 AM, "Andrew McDonnell" <bugs@xxxxxxxxxxxxxxxxxxx>
wrote:

> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~oqgraph-dev
> More help   : https://help.launchpad.net/ListHelp
>