Replicator parsing for MySQL has a number of missing cases that result in large numbers of #UNKNOWN shards

[GoogleCodeExporter]

There are many otherwise simple SQL statements that Tungsten currently does not 
parse.  If you replicate from MySQL you will see #UNKNOWN shards for the 
following cases: 

1. CREATE / DROP VIEW

Examples:  
CREATE OR REPLACE ALGORITHM=MERGE DEFINER=`main_db_reader`@`localhost` SQL 
SECURITY DEFINER VIEW `myview`(`id`, `gmt_offset`, `time_zone`, `state`, 
`city`) AS select `t`.`id` AS `id`,`t`.`gmt_offset`, [etc.]

DROP VIEW update_table

DROP VIEW IF EXISTS db1.my_view

2. CREATE / DROP INDEX
Examples: 

CREATE INDEX my_index ON tab1(end_id)

DROP INDEX my_index

3. FLUSH TABLES
Example:  FLUSH TABLES

4. Any statement with a '--' style comment.  
Examples: 

-- segment_event
    DELETE my_event.* 
    FROM my_event as segment_event INNER JOIN  
         my_segment as segment ON segment_event.segment_fk [etc.]

-- table1
        DELETE FROM table1 WHERE create_time < '1302159600000' LIMIT 50000

(Note the specialized DELETE syntax using my_event.*.)

Original issue reported on code.google.com by berkeley...@gmail.com on 9 Jun 2011 at 4:22

[GoogleCodeExporter]

The drop index syntax is incorrect.  It should be 'drop index myindex on 
mytable', etc.

Original comment by berkeley...@gmail.com on 11 Jun 2011 at 3:23

[GoogleCodeExporter]

This issue has been solved as follows: 

1.) Add additional processing for comments to remove '--' style comments before 
parsing. 

2.) Add additional comments to regex parsing for MySQL. 

3.) Add the notion of a "global" command for sharding.  FLUSH TABLES while 
identified must in general be serialized to avoid problems with lock conflicts. 
 Global commands are assigned to #UNKNOWN.  This extension would apply to 
commands like CREATE USER when we support them. 

Original comment by berkeley...@gmail.com on 12 Jun 2011 at 1:50

[GoogleCodeExporter]

This issue was closed by revision r212.

Original comment by berkeley...@gmail.com on 12 Jun 2011 at 2:54

• Changed state: Fixed