MemSQL Schema Introspection

My company has recently started using MemSQL for database storage. They claim to be compatible with MySQL, so they advise to use MySQL drivers whenever communicating with their database. Given this, I have added it as a MySQL database in DataGrip. So far this has been working... I am able to execute queries without any issues. However, DataGrip is unable to introspect the schema. When I start the introspection, I receive the following error:

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 'order by seq_in_index separator '\n') as column_names,
non_unique
from informa' at line 4

MemSQL is a newer, less popular database, so I understand why it is not (yet?) supported. However, I wondered if there is something I might be able to do (a configuration tweak or some such thing) to get this working. MySQL Workbench works with MemSQL without issue, so I don't believe there is a compatibility issue on the MemSQL side. Rather, it doesn't like the syntax of the introspection query generated by DataGrip.

Does anybody have experience or advice with this combination?

2 comments

Hello Kevin,

Thank you for report. We have this issue logged: https://youtrack.jetbrains.com/issue/DBE-2421 . Could you please post the idea.log after you are trying to Synchronize the schema and the output from Test Connection button for the Data Source? Thanks.

As a workaround please enable Use legacy introspector option in Schemas tab of the Data Source.

1

Andrey,

Thanks for the reply. And a big thank you for the workaround. It did the job perfectly. I will attach the requested information to the support ticket in addition to supplying it here.

Here's the output of the Test Connection button: 

It doesn't appear that I can attach the log file, however I started a clean one before testing, so it isn't terribly long. I am pasting it in below. With any luck, this won't come out as a colossal wall of text...

2016-05-12 11:04:52,444 [ 0] INFO - #com.intellij.idea.Main - ------------------------------------------------------ IDE STARTED ------------------------------------------------------ 
2016-05-12 11:04:52,476 [ 32] INFO - #com.intellij.idea.Main - IDE: DataGrip (build #DB-145.863.1, 06 May 2016 00:00)
2016-05-12 11:04:52,476 [ 32] INFO - #com.intellij.idea.Main - OS: Mac OS X (10.11.4, x86_64)
2016-05-12 11:04:52,476 [ 32] INFO - #com.intellij.idea.Main - JRE: 1.8.0_76-release-b162 (JetBrains s.r.o)
2016-05-12 11:04:52,476 [ 32] INFO - #com.intellij.idea.Main - JVM: 25.76-b162 (OpenJDK 64-Bit Server VM)
2016-05-12 11:04:52,483 [ 39] INFO - #com.intellij.idea.Main - JVM Args: -Dfile.encoding=UTF-8 -XX:+UseConcMarkSweepGC -XX:SoftRefLRUPolicyMSPerMB=50 -ea -Dsun.io.useCanonCaches=false -Djava.net.preferIPv4Stack=true -XX:+HeapDumpOnOutOfMemoryError -XX:-OmitStackTraceInFastThrow -Xverify:none -Xbootclasspath/a:../lib/boot.jar -Xms128m -Xmx750m -XX:MaxPermSize=350m -XX:ReservedCodeCacheSize=240m -XX:+UseCompressedOops -Djb.vmOptionsFile=/Applications/DataGrip.app/Contents/bin/datagrip.vmoptions -Didea.java.redist=jdk-bundled -Didea.home.path=/Applications/DataGrip.app/Contents -Didea.executable=datagrip -Didea.platform.prefix=DataGrip -Didea.paths.selector=DataGrip2016.1
2016-05-12 11:04:52,484 [ 40] INFO - #com.intellij.idea.Main - ext: /Applications/DataGrip.app/Contents/jre/jdk/Contents/Home/jre/lib/ext: [cldrdata.jar, dnsns.jar, jaccess.jar, jfxrt.jar, localedata.jar, meta-index, nashorn.jar, sunec.jar, sunjce_provider.jar, sunpkcs11.jar, zipfs.jar]
2016-05-12 11:04:52,484 [ 40] INFO - #com.intellij.idea.Main - ext: /System/Library/Java/Extensions: [MRJToolkit.jar]
2016-05-12 11:04:52,484 [ 40] INFO - #com.intellij.idea.Main - JNU charset: UTF-8
2016-05-12 11:04:52,497 [ 53] INFO - #com.intellij.idea.Main - JNA library loaded (64-bit) in 13 ms
2016-05-12 11:04:52,500 [ 56] INFO - #com.intellij.idea.Main - initializing environment
2016-05-12 11:04:52,501 [ 57] INFO - .intellij.util.EnvironmentUtil - loading shell env: /usr/local/bin/zsh -l -i -c '/Applications/DataGrip.app/Contents/bin/printenv.py' '/private/var/folders/pp/smlvpj6n6n9g3sjdxcg5v2dc0000gq/T/intellij-shell-env.tmp'
2016-05-12 11:04:52,507 [ 63] INFO - #com.intellij.util.ui.JBUI - UI scale factor: 1.0
2016-05-12 11:04:53,221 [ 777] INFO - .intellij.util.EnvironmentUtil - shell environment loaded (27 vars)
2016-05-12 11:04:53,222 [ 778] INFO - .intellij.util.EnvironmentUtil - LC_CTYPE=en_US.UTF-8
2016-05-12 11:04:54,283 [ 1839] INFO - llij.ide.plugins.PluginManager - 2 plugins initialized in 193 ms
2016-05-12 11:04:54,283 [ 1839] INFO - llij.ide.plugins.PluginManager - Loaded bundled plugins: IDEA CORE (145.SNAPSHOT)
2016-05-12 11:04:54,283 [ 1839] INFO - llij.ide.plugins.PluginManager - Loaded custom plugins: IdeaVim (0.44)
2016-05-12 11:04:54,357 [ 1913] INFO - ellij.util.io.PagedFileStorage - lower=100; upper=500; buffer=10; max=705
2016-05-12 11:04:54,376 [ 1932] INFO - pl.local.NativeFileWatcherImpl - Starting file watcher: /Applications/DataGrip.app/Contents/bin/fsnotifier
2016-05-12 11:04:54,383 [ 1939] INFO - pl.local.NativeFileWatcherImpl - Native file watcher is operational.
2016-05-12 11:04:54,593 [ 2149] INFO - #com.intellij.util.ui.JBUI - UI scale factor changed: 1.0
2016-05-12 11:04:54,616 [ 2172] INFO - #com.intellij.util.ui.JBUI - UI scale factor changed: 1.0
2016-05-12 11:04:54,746 [ 2302] INFO - rains.ide.BuiltInServerManager - built-in server started, port 63343
2016-05-12 11:04:54,773 [ 2329] INFO - gs.impl.UpdateCheckerComponent - channel: release
2016-05-12 11:04:55,325 [ 2881] INFO - plication.impl.ApplicationImpl - 74 application components initialized in 1236 ms
2016-05-12 11:04:55,335 [ 2891] INFO - .intellij.idea.IdeaApplication - App initialization took 3309 ms
2016-05-12 11:04:55,454 [ 3010] WARN - j.ui.mac.MacMainFrameDecorator - no url bundle present.
To use platform protocol handler to open external links specify required protocols in the mac app layout section of the build file
Example: args.urlSchemes = ["your-protocol"] will handle following links: your-protocol://open?file=file&line=line
2016-05-12 11:04:55,490 [ 3046] INFO - .openapi.application.Preloader - Finished preloading com.intellij.openapi.actionSystem.impl.ActionPreloader@71e08869
2016-05-12 11:04:55,578 [ 3134] INFO - .openapi.application.Preloader - Finished preloading com.intellij.ide.ui.search.SearchableOptionPreloader@4631f16d
2016-05-12 11:04:55,899 [ 3455] INFO - ellij.project.impl.ProjectImpl - 90 project components initialized in 271 ms
2016-05-12 11:04:55,911 [ 3467] INFO - le.impl.ModuleManagerComponent - 1 module(s) loaded in 12 ms
2016-05-12 11:04:56,049 [ 3605] INFO - .diagnostic.PerformanceWatcher - Pushing properties took 1ms; general responsiveness: ok; EDT responsiveness: ok
2016-05-12 11:04:56,209 [ 3765] INFO - .diagnostic.PerformanceWatcher - Indexable file iteration took 159ms; general responsiveness: ok; EDT responsiveness: ok
2016-05-12 11:04:57,275 [ 4831] INFO - tor.impl.FileEditorManagerImpl - Project opening took 1673 ms
2016-05-12 11:05:03,315 [ 10871] INFO - ution.rmi.RemoteProcessSupport - /Applications/DataGrip.app/Contents/jre/jdk/Contents/Home/jre/bin/java -Djava.net.preferIPv4Stack=true -Dfile.encoding=UTF-8 -classpath /Applications/DataGrip.app/Contents/lib/util.jar:/Applications/DataGrip.app/Contents/lib/trove4j.jar:/Applications/DataGrip.app/Contents/lib/groovy-all-2.4.6.jar:/Applications/DataGrip.app/Contents/lib/jdbc-console.jar:/Applications/DataGrip.app/Contents/lib/dekaf-single-2.0.0.229.jar:/Users/kevinburdett/Library/Preferences/DataGrip2016.1/jdbc-drivers/mysql-connector-license.txt:/Users/kevinburdett/Library/Preferences/DataGrip2016.1/jdbc-drivers/mysql-connector-java-5.1.35-bin.jar com.intellij.database.remote.RemoteJdbcServer com.mysql.jdbc.Driver
2016-05-12 11:05:03,426 [ 10982] INFO - ution.rmi.RemoteProcessSupport - Port/ID: 9936/RemoteDriverImpl2ae3b88f
2016-05-12 11:05:06,024 [ 13580] INFO - ase.dataSource.LocalDataSource - Connecting to: jdbc:mysql://ATL-VTX-QMEMA01:3306/Reference
2016-05-12 11:05:06,644 [ 14200] WARN - lij.database.util.ErrorHandler - 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 'order by seq_in_index separator '\n') as column_names,
non_unique
from informa' at line 4
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 'order by seq_in_index separator '\n') as column_names,
non_unique
from informa' at line 4
The SQL statement:
select
table_name,
index_name,
group_concat(column_name order by seq_in_index separator '\n') as column_names,
non_unique
from information_schema.statistics
where table_schema = ? and
index_schema = ? and
index_name <> 'PRIMARY'
group by
table_catalog,
table_schema,
table_name,
index_name,
index_type,
non_unique

at org.jetbrains.dekaf.jdbc.BaseExceptionRecognizer.recognizeException(BaseExceptionRecognizer.java:48)
at org.jetbrains.dekaf.jdbc.JdbcIntermediateSession.recognizeException(JdbcIntermediateSession.java:347)
at org.jetbrains.dekaf.jdbc.JdbcIntermediateSeance.execute(JdbcIntermediateSeance.java:98)
at com.intellij.database.remote.jdba.impl.RemoteSeanceImpl.execute(RemoteSeanceImpl.java:24)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:323)
at sun.rmi.transport.Transport$1.run(Transport.java:200)
at sun.rmi.transport.Transport$1.run(Transport.java:197)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:683)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:276)
at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:253)
at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:162)
at java.rmi.server.RemoteObjectInvocationHandler.invokeRemoteMethod(RemoteObjectInvocationHandler.java:227)
at java.rmi.server.RemoteObjectInvocationHandler.invoke(RemoteObjectInvocationHandler.java:179)
at com.sun.proxy.$Proxy97.execute(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.intellij.execution.rmi.RemoteUtil.invokeRemote(RemoteUtil.java:124)
at com.intellij.execution.rmi.RemoteUtil.access$100(RemoteUtil.java:36)
at com.intellij.execution.rmi.RemoteUtil$RemoteInvocationHandler.invoke(RemoteUtil.java:229)
at com.sun.proxy.$Proxy98.execute(Unknown Source)
at org.jetbrains.dekaf.intermediate.AdaptIntermediateSeance.execute(AdaptIntermediateSeance.java:28)
at org.jetbrains.dekaf.core.BaseQueryRunner.run(BaseQueryRunner.java:66)
at com.intellij.dbm.mysql.MysqlIntrospector$SchemaRetriever.f(MysqlIntrospector.java:207)
at com.intellij.dbm.mysql.MysqlIntrospector$SchemaRetriever.access$800(MysqlIntrospector.java:105)
at com.intellij.dbm.mysql.MysqlIntrospector$SchemaRetriever$3.run(MysqlIntrospector.java:146)
at com.intellij.dbm.mysql.MysqlIntrospector$SchemaRetriever.a(MysqlIntrospector.java:115)
at com.intellij.dbm.mysql.MysqlIntrospector$SchemaRetriever.retrieve(MysqlIntrospector.java:143)
at com.intellij.dbm.mysql.MysqlIntrospector.a(MysqlIntrospector.java:88)
at com.intellij.dbm.mysql.MysqlIntrospector.access$400(MysqlIntrospector.java:22)
at com.intellij.dbm.mysql.MysqlIntrospector$2.consume(MysqlIntrospector.java:60)
at com.intellij.dbm.mysql.MysqlIntrospector$2.consume(MysqlIntrospector.java:57)
at com.intellij.dbm.mysql.MysqlIntrospector$1.run(MysqlIntrospector.java:46)
at org.jetbrains.dekaf.core.BaseSession.inTransaction(BaseSession.java:88)
at org.jetbrains.dekaf.core.BaseFacade$2.run(BaseFacade.java:93)
at org.jetbrains.dekaf.core.BaseFacade.inSession(BaseFacade.java:125)
at org.jetbrains.dekaf.core.BaseFacade.inTransaction(BaseFacade.java:89)
at com.intellij.dbm.mysql.MysqlIntrospector.a(MysqlIntrospector.java:41)
at com.intellij.dbm.mysql.MysqlIntrospector.introspect(MysqlIntrospector.java:57)
at com.intellij.database.dataSource.DatabaseModelModernLoader.performModernIntrospection(DatabaseModelModernLoader.java:81)
at com.intellij.database.dataSource.DatabaseModelLoader.performModernIntrospection(DatabaseModelLoader.java:189)
at com.intellij.database.dataSource.DatabaseModelLoader.performKnownIntrospection(DatabaseModelLoader.java:170)
at com.intellij.database.dataSource.DatabaseModelLoader.performKnownIntrospection(DatabaseModelLoader.java:144)
at com.intellij.database.dataSource.DatabaseModelLoader.performIntrospection(DatabaseModelLoader.java:98)
at com.intellij.database.dataSource.AbstractDataSource.refreshMetaData(AbstractDataSource.java:61)
at com.intellij.database.dataSource.AbstractDataSource$1.perform(AbstractDataSource.java:36)
at com.intellij.database.dataSource.AbstractDataSource$1.perform(AbstractDataSource.java:34)
at com.intellij.database.dataSource.AbstractDataSource.performJdbcOperation(AbstractDataSource.java:116)
at com.intellij.database.dataSource.AbstractDataSource.refreshMetaData(AbstractDataSource.java:34)
at com.intellij.database.dataSource.DataSourceUiUtil$3.run(DataSourceUiUtil.java:200)
at com.intellij.openapi.progress.impl.CoreProgressManager$TaskRunnable.run(CoreProgressManager.java:563)
at com.intellij.openapi.progress.impl.CoreProgressManager$2.run(CoreProgressManager.java:142)
at com.intellij.openapi.progress.impl.CoreProgressManager.a(CoreProgressManager.java:446)
at com.intellij.openapi.progress.impl.CoreProgressManager.executeProcessUnderProgress(CoreProgressManager.java:392)
at com.intellij.openapi.progress.impl.ProgressManagerImpl.executeProcessUnderProgress(ProgressManagerImpl.java:54)
at com.intellij.openapi.progress.impl.CoreProgressManager.runProcess(CoreProgressManager.java:127)
at com.intellij.openapi.progress.impl.ProgressManagerImpl$1.run(ProgressManagerImpl.java:126)
at com.intellij.openapi.application.impl.ApplicationImpl$8.run(ApplicationImpl.java:369)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.jetbrains.dekaf.exceptions.StrippedSQLException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'order by seq_in_index separator '\n') as column_names,
non_unique
from informa' at line 4
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:389)
at com.mysql.jdbc.Util.getInstance(Util.java:372)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2535)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1911)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1203)
at org.jetbrains.dekaf.jdbc.JdbcIntermediateSeance.execute(JdbcIntermediateSeance.java:84)
at com.intellij.database.remote.jdba.impl.RemoteSeanceImpl.execute(RemoteSeanceImpl.java:24)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:323)
at sun.rmi.transport.Transport$1.run(Transport.java:200)
at sun.rmi.transport.Transport$1.run(Transport.java:197)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:683)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)
... 3 more
2016-05-12 11:05:11,844 [ 19400] INFO - ution.rmi.RemoteProcessSupport - Terminating: 9936/RemoteDriverImpl2ae3b88f
2016-05-12 11:05:11,869 [ 19425] INFO - org.jetbrains.io.BuiltInServer - web server stopped
2016-05-12 11:05:11,878 [ 19434] INFO - ution.rmi.RemoteProcessSupport - Process finished with exit code 137
2016-05-12 11:05:11,886 [ 19442] INFO - Types.impl.FileTypeManagerImpl - FileTypeManager: 0 auto-detected files
Elapsed time on auto-detect: 0 ms
2016-05-12 11:05:11,886 [ 19442] INFO - il.indexing.FileBasedIndexImpl - START INDEX SHUTDOWN
2016-05-12 11:05:11,890 [ 19446] INFO - il.indexing.FileBasedIndexImpl - END INDEX SHUTDOWN
2016-05-12 11:05:11,891 [ 19447] INFO - stubs.SerializationManagerImpl - START StubSerializationManager SHUTDOWN
2016-05-12 11:05:11,891 [ 19447] INFO - stubs.SerializationManagerImpl - END StubSerializationManager SHUTDOWN
2016-05-12 11:05:11,893 [ 19449] INFO - pl.local.NativeFileWatcherImpl - Watcher terminated with exit code 0
2016-05-12 11:05:11,893 [ 19449] INFO - newvfs.persistent.PersistentFS - VFS dispose started
2016-05-12 11:05:11,913 [ 19469] INFO - newvfs.persistent.PersistentFS - VFS dispose completed
2016-05-12 11:05:11,915 [ 19471] INFO - #com.intellij.idea.Main - ------------------------------------------------------ IDE SHUTDOWN ------------------------------------------------------
2016-05-12 11:05:11,916 [ 19472] INFO - org.jetbrains.io.BuiltInServer - web server stopped
0

Please sign in to leave a comment.