Problems with MemSQL on Presto

3rd-Party Integrations
1

Hello. I am trying out Presto with a MySQL connector pointing to a MemSQL cluster.

Initial results were promising.

memsql.properties

connector.name=mysql
connection-url=jdbc:mysql://0.0.0.0:3306
connection-user=root

SHOW CATALOGS; correctly showed Memsql.
SHOW SCHEMAS in memsql; correctly listed databases
SELECT * FROM memsql.information_schema.columns; correctly returned a result set.

Nice!

The issue arrises when I try to do insert data into MemSQL using Presto.

CREATE TABLE memsql.presto.test AS SELECT 1 AS col1;

Query 20190808_073803_00029_525av failed: Leaf Error (127.0.0.1:3307): Feature 'Cross database ALTER TABLE ... RENAME' is not supported by MemSQL.

Is there any workaround to this, or is this functionality coming soon to MemSQL?

2

hmm… where did find this statement from:

CREATE TABLE memsql.presto.test AS SELECT 1 AS col1;

MemSql doesn’t support 3-part names like memsql.presto.test and it also doesn’t support creating tables in the memsql database (its a locked down system database).

For example I get:

MemSQL [test]> CREATE TABLE memsql.presto.test AS SELECT 1 AS col1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.test AS SELECT 1 AS col1' at line 1

If I remove the 3-part name I get:

MemSQL [test]> CREATE TABLE memsql.test AS SELECT 1 AS col1;
ERROR 1726 (HY000): CREATE TABLE is not allowed on the 'memsql' database

there must be something else going on.

I know we have had Presto hooked up to MemSQL internally a few years back as a hackathon project, so it should be possible. btw, MemSQL’s query processor is far superior to Presto (it has a very limited query optimizer), so I’m not sure why you would want to do this (beyond some federated query with data in other databases).

3

MemSql doesn’t support 3-part names like memsql.presto.test and it also doesn’t support creating tables in the memsql database (its a locked down system database).

The top level memsql refers to MemSQL in the Presto catalog, not the memsql database in the MemSQL cluster.
The second level presto refers to a database in MemSQL
And finally test is a table to be created.

Since you are not running Presto, and your test query runs directly on the memsql database, which is not what I am testing.

I know we have had Presto hooked up to MemSQL internally a few years back as a hackathon project, so it should be possible. btw,

Seems possible for querying, but not for inserting.

MemSQL’s query processor is far superior to Presto (it has a very limited query optimizer), so I’m not sure why you would want to do this (beyond some federated query with data in other databases).

No arguments from me there! But main use case is to move data between different sources, with inserts into MemSQL.

4

Ah, okay. I would turn on the general_log on whichever aggregator Presto is querying so we can see what its trying to run.

Its likely using some syntax we don’t support.

5

Its likely using some syntax we don’t support.

Yes, it seems that syntax is

Cross database ALTER TABLE ... RENAME is not supported by MemSQL

Is this support coming, or can it be enabled with a flag?

6

It depends on the particular ALTER TABLE rename presto is running (why would it be running that I wonder?)

We did fix one issue in MemSQL 7.0 (still in beta) where we improperly blocked ALTER TABLEs that used 2 part names (but the same database)

i.e.,

ALTER TABLE someDb.someTable RENAME TO someDB.someRenamedTable;

was giving that error.

7

That sounds very promising and is likely the culprit.

I will need to test with MemSQL 7. Currently doesn’t look like there’s a version 7 cluster-in-a-box Docker image. I might wait for this before proceeding.

There’s no urgency, but if you have an ETA on this, that would be great.

8

I tried with quickstart which has a minimal-7.0.3 tag. Looks brand new.

I turned on tracing: SET GLOBAL general_log = ON;

I was originally looking at memsql.log, but looks like queries.log is the interesting one.

Here’s what I see:

-- [453069049]
/* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout;
-- [453070451]
SET NAMES utf8;
-- [453071040]
SET character_set_results = NULL;
-- [453072040]
SET autocommit=1;
-- [453072946]
SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
-- [453073763]
SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS';
-- [453075145]
SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS';
-- [453076357]
SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS';
-- [453077845]
SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, CASE WHEN TABLE_TYPE='BASE TABLE' THEN CASE WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE' ELSE 'TABLE' END WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'presto' AND TABLE_NAME = 'ctas' HAVING TABLE_TYPE IN ('TABLE','VIEW',null,null,null) ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME;
-- [453098723]
/* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout;
-- [453100064]
SET NAMES utf8;
-- [453100745]
SET character_set_results = NULL;
-- [453101760]
SET autocommit=1;
-- [453102601]
SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
-- [453103375]
SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS';
-- [453104996]
SHOW DATABASES;
-- [453109403]
/* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout;
-- [453110910]
SET NAMES utf8;
-- [453111721]
SET character_set_results = NULL;
-- [453112556]
SET autocommit=1;
-- [453113324]
SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
-- [453114237]
SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS';
-- [453115690]
CREATE TABLE `presto`.`tmp_presto_867af7155fa74c0c92cc261ea37c681a` (`col1` integer);
-- [453422493]
/* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout;
-- [453432487]
SET NAMES utf8;
-- [453434193]
SET character_set_results = NULL;
-- [453435819]
SET autocommit=1;
-- [453437165]
SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
-- [453438512]
SET autocommit=0;
-- [453439547]
SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS';
-- [453447054]
INSERT INTO `presto`.`tmp_presto_867af7155fa74c0c92cc261ea37c681a` VALUES (1);
-- [453479190]
commit;
-- [453480155]
rollback;
-- [453497031]
/* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout;
-- [453498741]
SET NAMES utf8;
-- [453499760]
SET character_set_results = NULL;
-- [453500890]
SET autocommit=1;
-- [453501847]
SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
-- [453502827]
SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS';
-- [453504748]
ALTER TABLE `presto`.`tmp_presto_867af7155fa74c0c92cc261ea37c681a` RENAME TO `presto`.`ctas`;
-- [453526930]
/* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout;
-- [453528537]
SET NAMES utf8;
-- [453529565]
SET character_set_results = NULL;
-- [453530415]
SET autocommit=1;
-- [453532501]
SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
-- [453533955]
DROP TABLE `presto`.`tmp_presto_867af7155fa74c0c92cc261ea37c681a`;

I can’t tell what’s failing exactly, but probably this statement:

ALTER TABLE `presto`.`tmp_presto_867af7155fa74c0c92cc261ea37c681a` RENAME TO `presto`.`ctas`;

That looks exactly like the bug you identified previously, so perhaps not quite fixed in this Docker image?

9

Indeed, if I run these I can recreate the error independent of Presto:

CREATE TABLE `presto`.`tmp_presto_867af7155fa74c0c92cc261ea37c681a` (`col1` integer);
INSERT INTO `presto`.`tmp_presto_867af7155fa74c0c92cc261ea37c681a` VALUES (1);
ALTER TABLE `presto`.`tmp_presto_867af7155fa74c0c92cc261ea37c681a` RENAME TO `presto`.`ctas`;
10

Thanks scott,

That does look like the type of ALTER we were guesing it was running. Your sample ALTER works fine for me vs the latest 7.0 builds. That docker image is likley an earlier beta before we made the fix. Let me see if I can find out an ETA for updating it.

11

It looks like we do have cluster in a box with the latest beta:
https://www.memsql.com/7-beta-1-thank-you

I verified that 7.0 beta build has the fix.

12

Thanks for this. It’s not yet available as a Docker image, so I’ll test this out a bit more when it is. Will make my life easier. This is a side project more than anything, but I appreciate your help a lot.

1 Like