MemSQL Schema Introspection

Avatar
Kevin Burdett
创建于

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?

Votes

0

分享

2 条评论
Avatar
Andrey Dernov
创建于

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
Avatar
Kevin Burdett
创建于

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

请先登录再写评论。