- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] fc31f6d: MDEV-7833:ANALYZE FORMAT=JSON and Range checked for each record
by Sergey Petrunia 03 Apr '15

03 Apr '15

Hi Sanja, Please find review comments below. On Thu, Apr 02, 2015 at 06:19:41PM +0200, sanja(a)mariadb.com wrote: > revision-id: fc31f6d95720b4b946b8b68c816026d65831f347 > parent(s): 01d7da6785284383b2c04f2d4474feccebb0bb6f > committer: Oleksandr Byelkin > branch nick: server > timestamp: 2015-04-02 18:19:33 +0200 > message: > > MDEV-7833:ANALYZE FORMAT=JSON and Range checked for each record > > --- > mysql-test/r/analyze_format_json.result | 62 +++++++++++++++++++++++++++++ > mysql-test/t/analyze_format_json.test | 30 ++++++++++++++ > sql/sql_explain.cc | 26 +++++++++++-- > sql/sql_explain.h | 12 +++++- > sql/sql_select.cc | 69 ++++++++++++++++++++++++++++++--- > sql/sql_select.h | 7 +++- > 6 files changed, 195 insertions(+), 11 deletions(-) > > diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result > index 9022d8c..b3ef075 100644 > --- a/mysql-test/r/analyze_format_json.result > +++ b/mysql-test/r/analyze_format_json.result > @@ -264,3 +264,65 @@ ANALYZE > } > } > drop table t1; > +# > +# MDEV-7833:ANALYZE FORMAT=JSON and Range checked for each record > +# > +create table t3(a int); > +insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); > +create table t4(a int); > +insert into t4 select A.a + B.a* 10 + C.a * 100 from t3 A, t3 B, t3 C; > +create table t1 (lb1 int, rb1 int, lb2 int, rb2 int, c1 int, c2 int); > +insert into t1 values (1,2,10,20,15,15); > +insert into t1 values (3,5,10,20,15,15); > +insert into t1 values (10,20,10,20,15,15); > +insert into t1 values (10,20,1,2,15,15); > +insert into t1 values (10,20,10,20,1,3); > +create table t2 (key1 int, key2 int, key3 int, key4 int, col1 int, > +key(key1), key(key2), key(key3), key(key4)); > +insert into t2 select a,a,a,a,a from t3; > +insert into t2 select 15,15,15,15,15 from t4; > +analyze format=json > +select * from t1, t2 where (t2.key1 between t1.lb1 and t1.rb1) and > +(t2.key2 between t1.lb2 and t1.rb2) and > +(t2.key3=t1.c1 OR t2.key4=t1.c2); > +ANALYZE > +{ > + "query_block": { > + "select_id": 1, > + "r_loops": 1, > + "r_total_time_ms": "REPLACED", > + "table": { > + "table_name": "t1", > + "access_type": "ALL", > + "r_loops": 1, > + "rows": 5, > + "r_rows": 5, > + "r_total_time_ms": "REPLACED", > + "filtered": 100, > + "r_filtered": 100 > + }, > + "range-checked-for-each-record": { > + "keys": ["key1", "key2", "key3", "key4"], > + "r_keys_none": 1, > + "r_keys_index_merge": 1, > + "r_keys": { > + "key1": 2, > + "key2": 1, > + "key3": 0, > + "key4": 0 > + }, It seems wrong to have multiple r_XXX elements. I think, having one will be better. How about: r_keys: { "none": 1, ## Maybe, change to "full_table_scan" ? (btw can it be a full index scan instead I wonder?) "index_merge": nnn, "range" : { "key1" : nnn, "key2" : nnn, "key3" : nnn } } Let's discuss it. > + "table": { > + "table_name": "t2", > + "access_type": "ALL", > + "possible_keys": ["key1", "key2", "key3", "key4"], > + "r_loops": 5, > + "rows": 1010, > + "r_rows": 203.8, > + "r_total_time_ms": "REPLACED", > + "filtered": 100, > + "r_filtered": 98.135 > + } > + } > + } > +} > +drop table t1,t2,t3,t4; > diff --git a/mysql-test/t/analyze_format_json.test b/mysql-test/t/analyze_format_json.test > index 64bafd7..0ae9d4f 100644 > --- a/mysql-test/t/analyze_format_json.test > +++ b/mysql-test/t/analyze_format_json.test > @@ -75,3 +75,33 @@ disconnect con1; > connection default; > drop table t1; > > + > +--echo # > +--echo # MDEV-7833:ANALYZE FORMAT=JSON and Range checked for each record > +--echo # > +create table t3(a int); > +insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); > + > +create table t4(a int); > +insert into t4 select A.a + B.a* 10 + C.a * 100 from t3 A, t3 B, t3 C; > + > +create table t1 (lb1 int, rb1 int, lb2 int, rb2 int, c1 int, c2 int); > + > +insert into t1 values (1,2,10,20,15,15); > +insert into t1 values (3,5,10,20,15,15); > +insert into t1 values (10,20,10,20,15,15); > +insert into t1 values (10,20,1,2,15,15); > +insert into t1 values (10,20,10,20,1,3); > + > +create table t2 (key1 int, key2 int, key3 int, key4 int, col1 int, > + key(key1), key(key2), key(key3), key(key4)); > +insert into t2 select a,a,a,a,a from t3; > +insert into t2 select 15,15,15,15,15 from t4; > + > +--replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/ > +analyze format=json > +select * from t1, t2 where (t2.key1 between t1.lb1 and t1.rb1) and > + (t2.key2 between t1.lb2 and t1.rb2) and > + (t2.key3=t1.c1 OR t2.key4=t1.c2); > + > +drop table t1,t2,t3,t4; > diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc > index 9d82f4f..edbc2bf 100644 > --- a/sql/sql_explain.cc > +++ b/sql/sql_explain.cc > @@ -1076,15 +1076,15 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai > } > > Please add a comment: @return ptr - pointer to the added string NULL - out of memory error Does it make sense to change the return type to 'const char*' ? > -bool String_list::append_str(MEM_ROOT *mem_root, const char *str) > +char *String_list::append_str(MEM_ROOT *mem_root, const char *str) > { > size_t len= strlen(str); > char *cp; > if (!(cp = (char*)alloc_root(mem_root, len+1))) > - return 1; > + return NULL; > memcpy(cp, str, len+1); > push_back(cp); > - return 0; > + return cp; > } > > > @@ -1209,6 +1209,26 @@ void Explain_table_access::print_explain_json(Explain_query *query, > { > writer->add_member("range-checked-for-each-record").start_object(); > add_json_keyset(writer, "keys", &possible_keys); > + if (is_analyze) > + { > + writer->add_member("r_keys_none").add_ll(range_checked_fer->none); > + writer->add_member("r_keys_index_merge").add_ll(range_checked_fer-> > + index_merge); > + if (range_checked_fer->keys_stat) > + { > + writer->add_member("r_keys").start_object(); > + for (uint i= 0; i < range_checked_fer->keys; i++) > + { > + if (range_checked_fer->keys_stat_names[i]) > + { > + writer->add_member(range_checked_fer-> > + keys_stat_names[i]).add_ll(range_checked_fer-> > + keys_stat[i]); > + } > + } > + writer->end_object(); > + } > + } If we move this code into Range_checked_fer::print_json(), will it be possible to make its members private? > } > > if (full_scan_on_null_key) > diff --git a/sql/sql_explain.h b/sql/sql_explain.h > index e3b41ee..f86886d 100644 > --- a/sql/sql_explain.h > +++ b/sql/sql_explain.h > @@ -54,7 +54,7 @@ it into the slow query log. > class String_list: public List<char> > { > public: > - bool append_str(MEM_ROOT *mem_root, const char *str); > + char *append_str(MEM_ROOT *mem_root, const char *str); > }; > > class Json_writer; > @@ -627,6 +627,16 @@ class Explain_range_checked_fer : public Sql_alloc > public: > String_list key_set; > key_map keys_map; > + > + ha_rows none, index_merge; 'none' looks weird. Would a 'full_scan' be better. > + ha_rows *keys_stat; > + char **keys_stat_names; > + uint keys; This needs to be documented. > + > + Explain_range_checked_fer() > + :Sql_alloc(), none(0), index_merge(0), > + keys_stat(0), keys_stat_names(0), keys(0) > + {} > }; > > /* > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index 5c3e116..4eb3d81 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -11398,6 +11398,7 @@ void JOIN_TAB::cleanup() > table->reginfo.join_tab= 0; > } > end_read_record(&read_record); > + explain_plan= 0; It's a pointer, right? Please change s/0/NULL/ > DBUG_VOID_RETURN; > } > > @@ -18762,9 +18763,30 @@ test_if_quick_select(JOIN_TAB *tab) > > delete tab->select->quick; > tab->select->quick=0; > - return tab->select->test_quick_select(tab->join->thd, tab->keys, > - (table_map) 0, HA_POS_ERROR, 0, > - FALSE, /*remove where parts*/FALSE); > + int res= tab->select->test_quick_select(tab->join->thd, tab->keys, > + (table_map) 0, HA_POS_ERROR, 0, > + FALSE, /*remove where parts*/FALSE); > + if (tab->explain_plan && tab->explain_plan->range_checked_fer) > + { > + if (tab->select->quick) > + { > + QUICK_SELECT_I *quick= tab->select->quick; > + if (quick->index == MAX_KEY) > + tab->explain_plan->range_checked_fer->index_merge++; > + else > + { > + DBUG_ASSERT(quick->index < tab->explain_plan->range_checked_fer->keys); > + DBUG_ASSERT(tab->explain_plan->range_checked_fer->keys_stat); > + DBUG_ASSERT(tab->explain_plan->range_checked_fer->keys_stat_names); > + DBUG_ASSERT(tab->explain_plan->range_checked_fer->keys_stat_names[ > + quick->index]); > + tab->explain_plan->range_checked_fer->keys_stat[quick->index]++; > + } > + } > + else > + tab->explain_plan->range_checked_fer->none++; > + } > + return res; I think it's bad that join execution code becomes littered with details about how things are stored in the Explain data structures. Can you add a set of methods to range_checked_fer: count_full_scan(); count_index_merge(); count_range_scan(); and then only call these methods from here? > } > > > @@ -23360,6 +23382,32 @@ int append_possible_keys(MEM_ROOT *alloc, String_list &list, TABLE *table, > return 0; > } > What does this function do? AFAIU it does two things: - copies indexes from possible_keys into a String_list. - allocates an empty array of ha_rows(). The set of data structures used by Range_checked_fer is quite peculiar. This function is only useful for Range_checked_fer. Do you think it would make sense to made this a method of Range_checked_fer? > +int append_possible_keys_stat(MEM_ROOT *alloc, String_list &list, > + ha_rows **stat, char ***names, > + TABLE *table, key_map possible_keys) > +{ > + uint j; > + multi_alloc_root(alloc, stat, sizeof(ha_rows) * table->s->keys, > + names, sizeof(char *) * table->s->keys, NULL); > + if ((!(*stat)) || (!(*names))) > + { > + (*stat)= NULL; (*names)= NULL; > + return 1; > + } > + bzero(*stat, sizeof(ha_rows) * table->s->keys); > + for (j= 0; j < table->s->keys; j++) > + { > + if (possible_keys.is_set(j)) > + { > + char *nm= list.append_str(alloc, table->key_info[j].name); > + (*names)[j]= nm; > + } > + else > + (*names)[j]= NULL; > + } > + return 0; > +} > + > /* > TODO: this function is only applicable for the first non-const optimization > join tab. > @@ -23400,6 +23448,8 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta, table_map prefix_tab > quick_type= -1; > QUICK_SELECT_I *quick= NULL; > > + > + explain_plan= eta; > eta->key.clear(); > eta->quick_info= NULL; > > @@ -23663,9 +23713,16 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta, table_map prefix_tab > { > eta->push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD); > eta->range_checked_fer= new (thd->mem_root) Explain_range_checked_fer; > - eta->range_checked_fer->keys_map= tab->keys; > - append_possible_keys(thd->mem_root, eta->range_checked_fer->key_set, > - table, tab->keys); > + if (eta->range_checked_fer) > + { > + eta->range_checked_fer->keys_map= tab->keys; > + eta->range_checked_fer->keys= table->s->keys; > + append_possible_keys_stat(thd->mem_root, > + eta->range_checked_fer->key_set, > + &eta->range_checked_fer->keys_stat, > + &eta->range_checked_fer->keys_stat_names, > + table, tab->keys); > + } > } > else if (tab->select->cond || > (tab->cache_select && tab->cache_select->cond)) > diff --git a/sql/sql_select.h b/sql/sql_select.h > index 0557e32..ef92690 100644 > --- a/sql/sql_select.h > +++ b/sql/sql_select.h > @@ -362,7 +362,12 @@ typedef struct st_join_table { > SJ_TMP_TABLE *check_weed_out_table; > /* for EXPLAIN only: */ > SJ_TMP_TABLE *first_weedout_table; > - > + > + /** > + reference to saved plan and execution statistics > + */ > + Explain_table_access *explain_plan; > + > /* > If set, means we should stop join enumeration after we've got the first > match and return to the specified join tab. May point to BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] [GSoC] MDEV-4674 Port InnoDB memcached interface to MariaDB
by Piotr Jurkiewicz 02 Apr '15

02 Apr '15

Hello. My name is Piotr Jurkiewicz. I am a last year MSc student at AGH University of Science and Technology in Krakow, Poland. I already contributed to MySQL InnoDB Memcached plugin. I know it well, from user and developer points of view. I have been using MySQL InnoDB Memcached plugin since half year in a production system I administrate at my university. However, I must admit that quality of this plugin is rather poor. I have run several times into bugs, from the simple ones, like incorrect results of arithmetic operations, to the serious, like crashes of the whole MySQL process. I reported some of these bugs to Oracle and contributed patches fixing them [1] [2]. However, the more I was diving into the plugin code, the more I was realizing that the problem is not only buggy code and insufficient testing. The core mistakes had been made at the very beginning, when plugin architecture was set up. The most prominent result of these mistakes is lack of access security. After connecting to Memcached port, a user has access to all databases and tables exported by the plugin. Fixing this would require complete remodeling of plugin architecture and the way users configure and use it. This is not possible in MySQL, as it would break existing deployments. Therefore, I was very excited, when I saw that porting of InnoDB Memcached plugin is among MariaDB proposed GSoC ideas. This gives a possibility to build the plugin in a proper way. During the last months I have been thinking a lot how this plugin should be structured. I think I worked out a clear concept of that. With the financing from Google I will be able to dedicate sufficient amount of time for its implementation, what should result in fully featured, secure and robust Memcached plugin for MariaDB. You can review current version of my proposal in the Google Melange system: https://www.google-melange.com/gsoc/proposal/review/student/google/gsoc2015… As for now, it is only visible for MariaDB organization members, who are registered in Melange system. On March 27th, after student application deadline, I will make it visible for non-registered users as well and send its copy to this list. I am waiting for your remarks and hints. [1]: http://bugs.mysql.com/bug.php?id=74874 [2]: http://bugs.mysql.com/bug.php?id=75864 Piotr

4 8

Re: [Maria-developers] [server] Mdev 7802 binlog groupcommit stats (#30)
by Kristian Nielsen 02 Apr '15

02 Apr '15

Daniel Black <notifications(a)github.com> writes: Thanks for your patch. I agree that these kinds of status information could be useful. But I have some parts that are not clear, and I want you to think about the questions below and answer with your thoughts. This is to make sure you understand yourself the issue in detail; to help me understand it; and in some cases maybe some changes should be made. > The following global status variables where added: > * binlog_group_commit_reason_count > * binlog_group_commit_reason_usec So, these names seem a bit odd. Neither "count" nor "usec" makes much sense as a reason for something to happen. "_timeout" would be a better reason that "_usec", but I am not sure of a better name for "_count". The good thing about these names of course is that they match the corresponding --binlog-commit-wait-* option. What do you think of something like binlog_commit_trigger_count binlog_commit_trigger_timeout instead ? > * binlog_group_commit_reason_transaction > * binlog_group_commit_reason_immediate > binlog_group_commit_reason_transaction is a result of ordered > transaction that need to occur in the same order on the slave and can't > be parallelised. > > binlog_group_commit_reason_immediate is caused to prevent stalls with > row locks as described in log.cc:binlog_report_wait_for. This immediate > count is also counted a second time in binlog_group_commit_reason_transaction. Please re-read that explanation, and consider: do you think an average user/DBA will fully understand what these status variables mean? Honestly, I am wondering if you can even yourself explain exactly what the difference is between the two :-). Can you give two simple examples of transactions, one example of which will increment the _transaction status variable and one of which will increment the _immediate? Do you think the distinction between the two is useful for the user? If not, maybe just have a single status variable, like binlog_commit_trigger_lock_wait for example? > Overall binlog_group_commits = binlog_group_commit_reason_count + > binlog_group_commit_reason_usec + binlog_group_commit_reason_transaction Is this also true if --binlog-commit-wait-count=0? If so, what will the values of the different status variables be in this case? Now, to the patches. > + { > + if (++count >= opt_binlog_commit_wait_count) > + { > + group_commit_reason_count++; > + return; > + } > + if (unlikely(e->thd->has_waiter)) > + { > + group_commit_reason_transaction++; > return; > + } > + } So this increments the global status variables directly, I think? How does the thread locking work? Is it possible for a reader to see a corrupt value (eg. one word of the old value and one word of the new) on 32-bit platform? How is this handled for other similar status variables? It would not be good if we had to take some LOCK_status or something inside the critical group commit code. With respect to the test cases: Can you please comment yourself on the different places where you added output of the status variables, and explain why this output will always be the same, no matter how threads are scheduled on the machine running the test? (This is the critical part of most tests related to replication; because of the inherently multi-threaded nature of such tests, a lot of care is needed to make sure the test will not produce different output on different test runs depending on the speed of the host or how threads are scheduled on a loaded machine). - Kristian.

2 2

[Maria-developers] [MDEV-7540] Information Schema SPATIAL_REF_SYS contents don't match the expected contents
by Delveri chick 02 Apr '15

02 Apr '15

diff --git a/sql/sql_show.cc b/sql/sql_show.cc index dce2f7b..c6d73bf 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -355,6 +355,324 @@ static int fill_spatial_ref_sys(THD *thd, TABLE_LIST *tables, COND *cond) if (schema_table_store_record(thd, table)) goto exit; + table->field[0]->store(1, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("SR-ORG"), cs); /*AUTH_NAME*/ + table->field[2]->store(1, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"unnamed\",\ + GEOGCS[\"GRS 1980(IUGG, 1980)\",DATUM[\"unknown\",SPHEROID[\"GRS80\",\ + 6378137,298.257222101]],PRIMEM[\"Greenwich\",0],UNIT[\"degree\",\ + 0.0174532925199433]],PROJECTION[\"Lambert_Conformal_Conic_2SP\"],\ + PARAMETER[\"standard_parallel_1\",42.6833319],PARAMETER[\"standard_parallel_2\",\ + 41.71666666666667],PARAMETER[\"latitude_of_origin\",41],PARAMETER[\"central_meridian\",\ + -71.5],PARAMETER[\"false_easting\",200000],PARAMETER[\"false_northing\",\ + 750000],UNIT[\"Meter\",1]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2000, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2000, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"Anguilla 1957 / British West Indies Grid\ + \",GEOGCS[\"Anguilla 1957\",DATUM[\"Anguilla_1957\",SPHEROID[\ + \"Clarke 1880 (RGS)\",6378249.145,293.465,AUTHORITY[\"EPSG\",\"7012\ + \"]],AUTHORITY[\"EPSG\",\"6600\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\ + \"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\ + \"]],AUTHORITY[\"EPSG\",\"4600\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\ + \"9001\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\ + \",0],PARAMETER[\"central_meridian\",-62],PARAMETER[\"scale_factor\",0.9995],PARAMETER[\ + \"false_easting\",400000],PARAMETER[\"false_northing\",0],AUTHORITY[\"EPSG\",\ + \"2000\"],AXIS[\"Easting\",EAST],AXIS[\"Northing\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2001, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2001, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"Antigua 1943 / British West Indies Grid\ + \",GEOGCS[\"Antigua 1943\",DATUM[\"Antigua_1943\",SPHEROID[\ + \"Clarke 1880 (RGS)\",6378249.145,293.465,AUTHORITY[\"EPSG\",\"7012\ + \"]],AUTHORITY[\"EPSG\",\"6601\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\ + \"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\ + \"]],AUTHORITY[\"EPSG\",\"4601\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\ + \"9001\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\ + \",0],PARAMETER[\"central_meridian\",-62],PARAMETER[\"scale_factor\ + \",0.9995],PARAMETER[\"false_easting\",400000],PARAMETER[\"false_northing\ + \",0],AUTHORITY[\"EPSG\",\"2001\"],AXIS[\"Easting\",EAST],AXIS[\"Northing\ + \",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2002, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2002, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"Dominica 1945 / British West Indies Grid\ + \",GEOGCS[\"Dominica 1945\",DATUM[\"Dominica_1945\",SPHEROID[\ + \"Clarke 1880 (RGS)\",6378249.145,293.465,AUTHORITY[\"EPSG\",\"7012\ + \"]],TOWGS84[725,685,536,0,0,0,0],AUTHORITY[\"EPSG\",\"6602\"]],PRIMEM[\ + \"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\ + \",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\ + \"4602\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\"9001\"]],PROJECTION[\ + \"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\",0],PARAMETER[\ + \"central_meridian\",-62],PARAMETER[\"scale_factor\",0.9995],PARAMETER[\ + \"false_easting\",400000],PARAMETER[\"false_northing\",0],AUTHORITY[\"EPSG\",\ + \"2002\"],AXIS[\"Easting\",EAST],AXIS[\"Northing\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2003, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2003, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"Grenada 1953 / British West Indies Grid\ + \",GEOGCS[\"Grenada 1953\",DATUM[\"Grenada_1953\",SPHEROID[\"Clarke 1880 (RGS)\ + \",6378249.145,293.465,AUTHORITY[\"EPSG\",\"7012\"]],TOWGS84[72,213.7,93,0,0,0,0],AUTHORITY[\ + \"EPSG\",\"6603\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\ + \"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\ + \",\"4603\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\"9001\"]],PROJECTION[\ + \"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\",0],PARAMETER[\ + \"central_meridian\",-62],PARAMETER[\"scale_factor\",0.9995],PARAMETER[\ + \"false_easting\",400000],PARAMETER[\"false_northing\",0],AUTHORITY[\"EPSG\",\ + \"2003\"],AXIS[\"Easting\",EAST],AXIS[\"Northing\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2004, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2004, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"Montserrat 1958 / British West Indies Grid\ + \",GEOGCS[\"Montserrat 1958\",DATUM[\"Montserrat_1958\",SPHEROID[\ + \"Clarke 1880 (RGS)\",6378249.145,293.465,AUTHORITY[\"EPSG\",\"7012\ + \"]],TOWGS84[174,359,365,0,0,0,0],AUTHORITY[\"EPSG\",\"6604\"]],PRIMEM[\ + \"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\ + \"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4604\"]],UNIT[\"metre\ + \",1,AUTHORITY[\"EPSG\",\"9001\"]],PROJECTION[\"Transverse_Mercator\ + \"],PARAMETER[\"latitude_of_origin\",0],PARAMETER[\"central_meridian\ + \",-62],PARAMETER[\"scale_factor\",0.9995],PARAMETER[\"false_easting\ + \",400000],PARAMETER[\"false_northing\",0],AUTHORITY[\"EPSG\",\"2004\ + \"],AXIS[\"Easting\",EAST],AXIS[\"Northing\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2005, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2005, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"St. Kitts 1955 / British West Indies Grid\ + \",GEOGCS[\"St. Kitts 1955\",DATUM[\"St_Kitts_1955\",SPHEROID[\"Clarke 1880 (RGS)\ + \",6378249.145,293.465,AUTHORITY[\"EPSG\",\"7012\"]],AUTHORITY[\"EPSG\",\ + \"6605\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\ + \"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\ + \"EPSG\",\"4605\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\"9001\"]],PROJECTION[\ + \"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\",0],PARAMETER[\ + \"central_meridian\",-62],PARAMETER[\"scale_factor\",0.9995],PARAMETER[\ + \"false_easting\",400000],PARAMETER[\"false_northing\",0],AUTHORITY[\"EPSG\",\ + \"2005\"],AXIS[\"Easting\",EAST],AXIS[\"Northing\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2006, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2006, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"St. Lucia 1955 / British West Indies Grid\ + \",GEOGCS[\"St. Lucia 1955\",DATUM[\"St_Lucia_1955\",SPHEROID[\"Clarke 1880 (RGS)\ + \",6378249.145,293.465,AUTHORITY[\"EPSG\",\"7012\"]],TOWGS84[-149,128,296,0,0,0,0],AUTHORITY[\ + \"EPSG\",\"6606\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\ + \"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\ + \"]],AUTHORITY[\"EPSG\",\"4606\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\ + \"9001\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\ + \",0],PARAMETER[\"central_meridian\",-62],PARAMETER[\"scale_factor\",0.9995],PARAMETER[\ + \"false_easting\",400000],PARAMETER[\"false_northing\",0],AUTHORITY[\"EPSG\",\ + \"2006\"],AXIS[\"Easting\",EAST],AXIS[\"Northing\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2007, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2007, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"St. Vincent 45 / British West Indies Grid\ + \",GEOGCS[\"St. Vincent 1945\",DATUM[\"St_Vincent_1945\",SPHEROID[\ + \"Clarke 1880 (RGS)\",6378249.145,293.465,AUTHORITY[\"EPSG\",\"7012\ + \"]],TOWGS84[195.671,332.517,274.607,0,0,0,0],AUTHORITY[\"EPSG\",\"6607\ + \"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\ + \",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\ + \"4607\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\"9001\"]],PROJECTION[\ + \"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\",0],PARAMETER[\ + \"central_meridian\",-62],PARAMETER[\"scale_factor\",0.9995],PARAMETER[\ + \"false_easting\",400000],PARAMETER[\"false_northing\",0],AUTHORITY[\"EPSG\",\ + \"2007\"],AXIS[\"Easting\",EAST],AXIS[\"Northing\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2008, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2008, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"NAD27(CGQ77) / SCoPQ zone 2\ + \",GEOGCS[\"NAD27(CGQ77)\",DATUM[\"North_American_Datum_1927_CGQ77\",SPHEROID[\ + \"Clarke 1866\",6378206.4,294.9786982138982,AUTHORITY[\"EPSG\",\"7008\"]],AUTHORITY[\ + \"EPSG\",\"6609\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\ + \",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\ + \"4609\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\"9001\"]],PROJECTION[\ + \"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\",0],PARAMETER[\ + \"central_meridian\",-55.5],PARAMETER[\"scale_factor\",0.9999],PARAMETER[\ + \"false_easting\",304800],PARAMETER[\"false_northing\",0],AUTHORITY[\"EPSG\",\ + \"2008\"],AXIS[\"X\",EAST],AXIS[\"Y\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2009, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2009, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"NAD27(CGQ77) / SCoPQ zone 3\ + \",GEOGCS[\"NAD27(CGQ77)\",DATUM[\"North_American_Datum_1927_CGQ77\",SPHEROID[\ + \"Clarke 1866\",6378206.4,294.9786982138982,AUTHORITY[\"EPSG\",\"7008\"]],AUTHORITY[\ + \"EPSG\",\"6609\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\ + \"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\ + \"4609\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\"9001\"]],PROJECTION[\"Transverse_Mercator\ + \"],PARAMETER[\"latitude_of_origin\",0],PARAMETER[\"central_meridian\",-58.5],PARAMETER[\ + \"scale_factor\",0.9999],PARAMETER[\"false_easting\",304800],PARAMETER[\"false_northing\ + \",0],AUTHORITY[\"EPSG\",\"2009\"],AXIS[\"X\",EAST],AXIS[\"Y\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2010, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2010, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"NAD27(CGQ77) / SCoPQ zone 4\ + \",GEOGCS[\"NAD27(CGQ77)\",DATUM[\"North_American_Datum_1927_CGQ77\",SPHEROID[\ + \"Clarke 1866\",6378206.4,294.9786982138982,AUTHORITY[\"EPSG\",\"7008\"]],AUTHORITY[\ + \"EPSG\",\"6609\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\ + \",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4609\"]],UNIT[\ + \"metre\",1,AUTHORITY[\"EPSG\",\"9001\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\ + \"latitude_of_origin\",0],PARAMETER[\"central_meridian\",-61.5],PARAMETER[\ + \"scale_factor\",0.9999],PARAMETER[\"false_easting\",304800],PARAMETER[\ + \"false_northing\",0],AUTHORITY[\"EPSG\",\"2010\"],AXIS[\"X\",EAST],AXIS[\ + \"Y\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2011, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2011, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"NAD27(CGQ77) / SCoPQ zone 5\ + \",GEOGCS[\"NAD27(CGQ77)\",DATUM[\"North_American_Datum_1927_CGQ77\ + \",SPHEROID[\"Clarke 1866\",6378206.4,294.9786982138982,AUTHORITY[\"EPSG\ + \",\"7008\"]],AUTHORITY[\"EPSG\",\"6609\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\ + \"EPSG\",\"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\ + \",\"9122\"]],AUTHORITY[\"EPSG\",\"4609\"]],UNIT[\"metre\",1,AUTHORITY[\ + \"EPSG\",\"9001\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\ + \"latitude_of_origin\",0],PARAMETER[\"central_meridian\",-64.5],PARAMETER[\ + \"scale_factor\",0.9999],PARAMETER[\"false_easting\",304800],PARAMETER[\ + \"false_northing\",0],AUTHORITY[\"EPSG\",\"2011\"],AXIS[\"X\",EAST],AXIS[\ + \"Y\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2012, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2012, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"NAD27(CGQ77) / SCoPQ zone 6\ + \",GEOGCS[\"NAD27(CGQ77)\",DATUM[\"North_American_Datum_1927_CGQ77\ + \",SPHEROID[\"Clarke 1866\",6378206.4,294.9786982138982,AUTHORITY[\"EPSG\ + \",\"7008\"]],AUTHORITY[\"EPSG\",\"6609\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\ + \"EPSG\",\"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\ + \",\"9122\"]],AUTHORITY[\"EPSG\",\"4609\"]],UNIT[\"metre\",1,AUTHORITY[\ + \"EPSG\",\"9001\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\ + \",0],PARAMETER[\"central_meridian\",-67.5],PARAMETER[\"scale_factor\",0.9999],PARAMETER[\ + \"false_easting\",304800],PARAMETER[\"false_northing\",0],AUTHORITY[\"EPSG\",\ + \"2012\"],AXIS[\"X\",EAST],AXIS[\"Y\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2013, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2013, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"NAD27(CGQ77) / SCoPQ zone 7\ + \",GEOGCS[\"NAD27(CGQ77)\",DATUM[\"North_American_Datum_1927_CGQ77\ + \",SPHEROID[\"Clarke 1866\",6378206.4,294.9786982138982,AUTHORITY[\"EPSG\ + \",\"7008\"]],AUTHORITY[\"EPSG\",\"6609\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\ + \"EPSG\",\"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\ + \",\"9122\"]],AUTHORITY[\"EPSG\",\"4609\"]],UNIT[\"metre\",1,AUTHORITY[\ + \"EPSG\",\"9001\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\ + \",0],PARAMETER[\"central_meridian\",-70.5],PARAMETER[\"scale_factor\",0.9999],PARAMETER[\ + \"false_easting\",304800],PARAMETER[\"false_northing\",0],AUTHORITY[\"EPSG\ + \",\"2013\"],AXIS[\"X\",EAST],AXIS[\"Y\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2014, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2014, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"NAD27(CGQ77) / SCoPQ zone 8\ + \",GEOGCS[\"NAD27(CGQ77)\",DATUM[\"North_American_Datum_1927_CGQ77\",SPHEROID[\ + \"Clarke 1866\",6378206.4,294.9786982138982,AUTHORITY[\"EPSG\",\"7008\ + \"]],AUTHORITY[\"EPSG\",\"6609\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\ + \"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\ + \"]],AUTHORITY[\"EPSG\",\"4609\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\ + \"9001\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\ + \",0],PARAMETER[\"central_meridian\",-73.5],PARAMETER[\"scale_factor\ + \",0.9999],PARAMETER[\"false_easting\",304800],PARAMETER[\"false_northing\ + \",0],AUTHORITY[\"EPSG\",\"2014\"],AXIS[\"X\",EAST],AXIS[\"Y\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2015, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2015, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"NAD27(CGQ77) / SCoPQ zone 9\ + \",GEOGCS[\"NAD27(CGQ77)\",DATUM[\"North_American_Datum_1927_CGQ77\",SPHEROID[\ + \"Clarke 1866\",6378206.4,294.9786982138982,AUTHORITY[\"EPSG\",\"7008\"]],AUTHORITY[\ + \"EPSG\",\"6609\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\ + \",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\ + \"4609\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\"9001\"]],PROJECTION[\"Transverse_Mercator\ + \"],PARAMETER[\"latitude_of_origin\",0],PARAMETER[\"central_meridian\",-76.5],PARAMETER[\ + \"scale_factor\",0.9999],PARAMETER[\"false_easting\",304800],PARAMETER[\ + \"false_northing\",0],AUTHORITY[\"EPSG\",\"2015\"],AXIS[\"X\",EAST],AXIS[\ + \"Y\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2016, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2016, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"NAD27(CGQ77) / SCoPQ zone 10\ + \",GEOGCS[\"NAD27(CGQ77)\",DATUM[\"North_American_Datum_1927_CGQ77\ + \",SPHEROID[\"Clarke 1866\",6378206.4,294.9786982138982,AUTHORITY[\"EPSG\ + \",\"7008\"]],AUTHORITY[\"EPSG\",\"6609\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\ + \"EPSG\",\"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\ + \"9122\"]],AUTHORITY[\"EPSG\",\"4609\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\ + \"9001\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\ + \",0],PARAMETER[\"central_meridian\",-79.5],PARAMETER[\"scale_factor\ + \",0.9999],PARAMETER[\"false_easting\",304800],PARAMETER[\"false_northing\ + \",0],AUTHORITY[\"EPSG\",\"2016\"],AXIS[\"X\",EAST],AXIS[\"Y\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + table->field[0]->store(2017, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(2017, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("PROJCS[\"NAD27(76) / MTM zone 8\ + \",GEOGCS[\"NAD27(76)\",DATUM[\"North_American_Datum_1927_1976\",SPHEROID[\ + \"Clarke 1866\",6378206.4,294.9786982138982,AUTHORITY[\"EPSG\",\"7008\ + \"]],AUTHORITY[\"EPSG\",\"6608\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\ + \"8901\"]],UNIT[\"degree\",0.01745329251994328,AUTHORITY[\"EPSG\",\"9122\ + \"]],AUTHORITY[\"EPSG\",\"4608\"]],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\ + \"9001\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\ + \",0],PARAMETER[\"central_meridian\",-73.5],PARAMETER[\"scale_factor\ + \",0.9999],PARAMETER[\"false_easting\",304800],PARAMETER[\"false_northing\ + \",0],AUTHORITY[\"EPSG\",\"2017\"],AXIS[\"X\",EAST],AXIS[\"Y\",NORTH]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; + + + table->field[0]->store(3819, TRUE); /*SRID*/ + table->field[1]->store(STRING_WITH_LEN("EPSG"), cs); /*AUTH_NAME*/ + table->field[2]->store(3819, TRUE); /*AUTH_SRID*/ + table->field[3]->store(STRING_WITH_LEN("GEOGCS[\"HD1909\",DATUM[\"Hungarian Datum 1909\",\ + SPHEROID[\"Bessel 1841\",6377397.155,299.1528128,AUTHORITY[\"EPSG\",\"7004\"]],\ + TOWGS84[595.48,121.69,515.35,4.115,2.9383,-0.853,-0.7029504596901044],\ + AUTHORITY[\"EPSG\",\"1024\"]],PRIMEM[\"Greenwich\",0.0,AUTHORITY[\"EPSG\",\ + \"8901\"]],UNIT[\"degree\",0.017453292519943295],AXIS[\"Geodetic latitude\",\ + NORTH],AXIS[\"Geodetic longitude\",EAST],AUTHORITY[\"EPSG\",\"3819\"]]"), cs);/*SRTEXT*/ + if (schema_table_store_record(thd, table)) + goto exit; result= 0;

1 0

Re: [Maria-developers] 622611b: MDEV-7012: SET STATEMENT character_set_client, character_set_connection, character_set_filesystem, collation_connection have no effect
by Sergei Golubchik 02 Apr '15

02 Apr '15

Hi, Sanja! On Mar 17, sanja(a)mariadb.com wrote: > revision-id: 622611bc6a9371803f349d8cf4de6538ebbcdb37 > parent(s): a21ef88d2a358904f0cdf9cadc701cb263b5e317 > committer: Oleksandr Byelkin > branch nick: server > timestamp: 2015-03-17 14:40:25 +0100 > message: > > MDEV-7012: SET STATEMENT character_set_client, character_set_connection, character_set_filesystem, collation_connection have no effect > > Variables of simple SET STATEMENT set just after assigning > > (changes in test suite result checked) While it's a neat innovative trick, I don't think we should execute statements (and setting variables is executing) from the parser. It's very fragile and breaks in various corner cases, resulting in unfixable gotchas. For example, how would the following work: CREATE PROCEDURE foo BEGIN SET STATEMENT CHARACTER_SET_CONNECTION = latin5 FOR SELECT 'фи'; END So, I'd prefer a simple rule "first - the statement is parsed, then it is executed", meaning no effects from the statement execution can affect how it was parsed. Regards, Sergei

1 0

[Maria-developers] Please review MDEV-7284 INDEX: CREATE OR REPLACE
by Alexander Barkov 02 Apr '15

02 Apr '15

Hi Sergei, Please review a patch for MDEV-7284 Thanks.

2 1

Re: [Maria-developers] [Commits] 135d926d3: MDEV-6714 mysqldump slow with tables in big databases
by Sergei Golubchik 02 Apr '15

02 Apr '15

Hi, Vicentiu! Just one minor change, see the comment in get_actual_table_name(). After that please push. Well done, thanks! Regards, Sergei On Mar 19, vicentiu(a)mariadb.org wrote: > revision-id: 135d926d347443672626f1e655709fd5bf6dec80 > parent(s): 197afb413fcc9f06b5e5e6ef41ce980d108b354f > committer: Vicențiu Ciorbaru > branch nick: server > timestamp: 2015-03-19 15:16:22 +0200 > message: > > MDEV-6714 mysqldump slow with tables in big databases > > mysqldump now attempts to make use of the INFORMATION_SCHEMA tables. > If the table name is not found with a case sensitive search, it > fallbacks to a case insensitive search. > > --- > client/mysqldump.c | 90 ++++++++++++++++++++++++++++++++++++++++++------------ > 1 file changed, 71 insertions(+), 19 deletions(-) > > diff --git a/client/mysqldump.c b/client/mysqldump.c > index 2da4ce6..c265217 100644 > --- a/client/mysqldump.c > +++ b/client/mysqldump.c > @@ -3341,8 +3341,10 @@ static int dump_triggers_for_table(char *table_name, char *db_name) > /* Get list of triggers. */ > > my_snprintf(query_buff, sizeof(query_buff), > - "SHOW TRIGGERS LIKE %s", > - quote_for_like(table_name, name_buff)); > + "SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS " > + "WHERE EVENT_OBJECT_SCHEMA = DATABASE() AND " > + "EVENT_OBJECT_TABLE = \"%s\"", > + table_name); You still need to quote table_name, it might contain a double quote inside. quote_names() almost does what you need, but you don't need a check for MASK_ANSI_QUOTES. So you might either create a quote_names_helper, again, or simply create a new function for this, it's just a few lines anyway. UPD: There are other similar places in mysqldump where it doesn't quote table names, e.g. in get_table_structure(), for show_fields_stmt query. So, please create a separate bug report for this and then it'll fix everything at once. > if (mysql_query_with_error_report(mysql, &show_triggers_rs, query_buff)) > goto done; > @@ -4695,29 +4697,37 @@ static my_bool dump_all_views_in_db(char *database) > > > /* > - get_actual_table_name -- executes a SHOW TABLES LIKE '%s' to get the actual > - table name from the server for the table name given on the command line. > - we do this because the table name given on the command line may be a > - different case (e.g. T1 vs t1) > - > - RETURN > - pointer to the table name > - 0 if error > + See get_actual_table_name. Used to retrieve the correct table name > + from the database schema. > */ > - > -static char *get_actual_table_name(const char *old_table_name, MEM_ROOT *root) > +static char *get_actual_table_name_helper(const char *old_table_name, > + my_bool case_sensitive, > + MEM_ROOT *root) > { > char *name= 0; > MYSQL_RES *table_res; > MYSQL_ROW row; > char query[50 + 2*NAME_LEN]; > char show_name_buff[FN_REFLEN]; > - DBUG_ENTER("get_actual_table_name"); > + DBUG_ENTER("get_actual_table_name_helper"); > > /* Check memory for quote_for_like() */ > DBUG_ASSERT(2*sizeof(old_table_name) < sizeof(show_name_buff)); > - my_snprintf(query, sizeof(query), "SHOW TABLES LIKE %s", > - quote_for_like(old_table_name, show_name_buff)); > + > + if (case_sensitive) > + { > + DBUG_PRINT("info", ("case sensitive search")); > + my_snprintf(query, sizeof(query), > + "SELECT table_name FROM INFORMATION_SCHEMA.TABLES " > + "WHERE table_schema = DATABASE() AND table_name = \"%s\"", > + old_table_name); > + } > + else > + { > + DBUG_PRINT("info", ("case insensitive search")); > + my_snprintf(query, sizeof(query), "SHOW TABLES LIKE %s", > + quote_for_like(old_table_name, show_name_buff)); > + } > > if (mysql_query_with_error_report(mysql, 0, query)) > return NullS; > @@ -4742,6 +4752,46 @@ static char *get_actual_table_name(const char *old_table_name, MEM_ROOT *root) > DBUG_RETURN(name); > } > > +/* > + get_actual_table_name -- executes a SELECT .. FROM I_S.tables to check > + if the table name given on the command line matches the one in the database. > + If the table is not found, it falls back to a slower SHOW TABLES LIKE '%s' to > + get the actual table name from the server. > + > + We do this because the table name given on the command line may be a > + different case (e.g. T1 vs t1), but checking this takes a long time > + when there are many tables present. > + > + RETURN > + pointer to the table name > + 0 if error > +*/ > + > +static char *get_actual_table_name(const char *old_table_name, MEM_ROOT *root) > +{ > + char *name= 0; > + MYSQL_RES *table_res; > + MYSQL_ROW row; > + int lower_case_table_names; > + const char *show_var_query = "SHOW VARIABLES LIKE 'lower_case_table_names'"; > + DBUG_ENTER("get_actual_table_name"); > + > + if (mysql_query_with_error_report(mysql, &table_res, show_var_query)) > + return NullS; I'd prefer if you do it only once, not for every get_actual_table_name() call. > + > + if ((row= mysql_fetch_row(table_res))) > + { > + lower_case_table_names= atoi(row[1]); > + mysql_free_result(table_res); > + } > + > + name= get_actual_table_name_helper(old_table_name, TRUE, root); > + if (!name && !lower_case_table_names) > + name= get_actual_table_name_helper(old_table_name, FALSE, root); > + DBUG_RETURN(name); > +} > + > + > > static int dump_selected_tables(char *db, char **table_names, int tables) > {

1 0

[Maria-developers] Problem of accessing pfs table in maria version 10.1.3
by 김경남 01 Apr '15

01 Apr '15

Hi, I'm developing monitoring tool in daemon plugin I'm tyring to access pfs table via low level interface not using sql. (see below) ------------------------------------------------------------------------ ------------- ..... tables.init_one_table(STRING_WITH_LEN("performance_schema"), STRING_WITH_LEN("events_statements_current"), "events_statements_current", TL_READ); bool result = open_and_lock_tables(ctx->thd, &tables, FALSE, MYSQL_LOCK_IGNORE_TIMEOUT); if (result) { sql_print_error("monm plugin error: Can't open the %s.%s table.",dbname,tblname); } ..... /* read routine */ ------------------------------------------------------------ In open_and_lock_table function, I hava a error. I found the reason of this error by debugging. In table_cache.cc, open_table_def() requires .frm file as a table definition. but above version 10.1.3, there is no frm file for pfs table as I know. right? Let me know a correct way to get data from pfs table not using sql. Regards, Kyungnam. _____ 상기 메시지와 첨부화일 내에는 비밀정보가 포함되어 있을 수 있으며, 지정된 수신자에 한하여 조회 및 사용될 수 있습니다. 만약 송신자의 실수로 인하여 상기 메시지를 수신하였다면, 송신자에게 메시지를 반송해 주시고, 원본 메시지와 모든 사본을 폐기해 주시기 바랍니다. 상기 메시지의 전체 또는 일부에 대해 무단 열람, 사용, 공개, 배포하는 것은 금지되어 있습니다.(주)LG CNS. This message and its attachments may contain confidential information, and they are intended to be viewed or used by only the individuals specified in the message. If you have received this message in an error from the sender, please contact the sender immediately to notify the error and delete all of the message and its copies. It is prohibited to view, use, make public and/or distribute part or whole of this message without written permission.

1 0

[Maria-developers] Using THD instance in a daemon thread.
by 김경남 31 Mar '15

31 Mar '15

Hi, I'm writing mariadb daemon type plugin. I hava a question. In a daemon thread, I want to use THD instance. for example, ------------------------------------------------------------------------ ----------- class THD; static int plugin_init() { ... pthread_create(&id,&attr,my_func,...); } void my_func(void* p) { THD* thd = new THD; thd->thread_stack= (char*) &thd; thd->store_globals(); tables.init_one_table("performance_schema", 18, "events_statements_current", 25, "events_statements_current", TL_READ); open_and_lock_tables(thd, &tables, FALSE, MYSQL_LOCK_IGNORE_TIMEOUT); ... } ------------------------------------------------------------------------ -------------------- but In my_func(), new THD cause error. I guess thread problem. So I moved that code to plugin_init() for test as below -------------------------------------------------------------- class THD; static int plugin_init() { ... pthread_create(&id,&attr,my_func,...); THD* thd = new THD; thd->thread_stack= (char*) &thd; thd->store_globals(); tables.init_one_table("performance_schema", 18, "events_statements_current", 25, "events_statements_current", TL_READ); open_and_lock_tables(thd, &tables, FALSE, MYSQL_LOCK_IGNORE_TIMEOUT); ... } ------------------------------------------------------------------------ ----- and then there is no problem. but I need a daemon thread. I think that the reason of this error is assertion of thread. So I changed my code as below. ------------------------------------------------------------------------ --------- class THD; THD* thd; static int plugin_init() { ... pthread_create(&id,&attr,my_func,...); THD* thd = new THD; // moved here from my_func() for avoiding error. } void my_func(void* p) { thd->thread_stack= (char*) &thd; thd->store_globals(); tables.init_one_table("performance_schema", 18, "events_statements_current", 25, "events_statements_current", TL_READ); open_and_lock_tables(thd, &tables, FALSE, MYSQL_LOCK_IGNORE_TIMEOUT); ... } ------------------------------------------------------------------------ ----------------- At this time, thd->store_globals() cause error. In sql_class.cc , mysys_var->id= thread_id cause error because mysys_var is null. Let me know how to use THD instance in daemon thread. Regards, Kyungnam 김경남 부책임연구원 솔루션사업본부 정보기술 연구원 신기술개발팀 02-2099-6177 / 010-2208-3006 _____ 상기 메시지와 첨부화일 내에는 비밀정보가 포함되어 있을 수 있으며, 지정된 수신자에 한하여 조회 및 사용될 수 있습니다. 만약 송신자의 실수로 인하여 상기 메시지를 수신하였다면, 송신자에게 메시지를 반송해 주시고, 원본 메시지와 모든 사본을 폐기해 주시기 바랍니다. 상기 메시지의 전체 또는 일부에 대해 무단 열람, 사용, 공개, 배포하는 것은 금지되어 있습니다.(주)LG CNS. This message and its attachments may contain confidential information, and they are intended to be viewed or used by only the individuals specified in the message. If you have received this message in an error from the sender, please contact the sender immediately to notify the error and delete all of the message and its copies. It is prohibited to view, use, make public and/or distribute part or whole of this message without written permission.

2 1

[Maria-developers] ok to change FixVersion to 10.1 for MDEV-7649 wrong result when comparing utf8 column with an invalid literal
by Alexander Barkov 31 Mar '15

31 Mar '15

Hi Sergei, Do you think it's ok to change FixVersion for MDEV-7649 to 10.1? It requires some changes in sql_lex.cc, sql_yacc.yy and needs a new Item_string-alike Item to handle such malformed strings properly. Too big change to 10.0 or 5.5.

2 1