search

jorainer / ensembldb

This is the ensembldb development repository.
https://jorainer.github.io/ensembldb
33 stars 10 forks source link

local MySQL installation error #84

Closed shwong-tw closed 2 years ago

shwong-tw commented 5 years ago

Hi, I would like to report an issue while trying to install a local MySQL instance.

In short... the below code gives an error [Code] in the function createEnsDbIndices: create index entrezgene_entrezid_idx on entrezgene (entrezid(9)) gives error

[Error message] Error in .local(conn, statement, ...) : could not run statement: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

[Reason] seems to be due to the data type of entrezid (bigint) please see: https://stackoverflow.com/questions/30406066/i-keep-getting-this-mysql-error-code-1089

Thank you very much

jorainer commented 5 years ago

Thanks for reporting! In order for me to reproduce: could you provide the code you used and the output of your sessionInfo()?

shwong-tw commented 5 years ago

Hi,

Thank you for reminding. I just checked the version, and it is ensembldb_2.4.1 that comes along with Bioconductor version: Release (3.7) (https://doi.org/doi:10.18129/B9.bioc.ensembldb) seems that I just need to install the latest version from github and it will probably got resolved :)

jorainer commented 5 years ago

That should be the latest/most up-to-date version for Bioc 3.7. Could be that the one in Bioc3.8 (soon to be released) fixes it; that's what I would like to check. Which EnsDb did you want to store into a MySQL database? Was it the EnsDb.Hsapiens.v75?

shwong-tw commented 5 years ago

Hi,

Sorry I was not so specific in reporting the details... hope it gets better now...

This issue happened when I used ensembldb(Bioc 3.7 version) + either EnsDb.Hsapiens.v75 or EnsDb.Hsapiens.v86 (Bioc 3.7 version) on my ubuntu 16 machine.

So I just installed github version + EnsDb.Hsapiens.v75 and EnsDb.Hsapiens.v86 (Bioc 3.8 version), and both finished without error.

My best guess is that... the createEnsDbIndices in dbhelpers.R had different behavior: in ensembldb(Bioc 3.7 version) it used to be aff_rows <- dbExecute(con, paste0("create index ", tabname, "_", colname, "_idx ", "on ", tabname, " (", colname, idxL, ")")) and worked with error, and in github version it says tmp <- dbExecute(con, paste0("create ", idx_tp, " index ", tabname, "_", colname, "_idx on ", tabname, " (", colname, ")")) and is fine.

One thing out of topic... I just noticed that there's a EnsDb.Hsapiens.v79 version in Bioconductor, would you kindly give me some hint about what is special in this version?

Thank you very much :)

jorainer commented 5 years ago

Yep, I was expecting something along these lines. In the recent versions of ensembldb I re-wrote part of the code to store the data into a MySQL database - with the aim to improve the speed of the queries. Note that I can not fix the issue anymore for Bioc 3.7 as that branch was frozen (Bioc 3.8 will be released end of October).

Regarding the v79: that's annotations from Ensembl version 79. The vXX always refers to the Ensembl release annotations are derived from. In AnnotationHub you can find EnsDb for every Ensembl release.

shwong-tw commented 5 years ago

Hi Johannes,

Thank you very much for the reply :) and yes local MySQL did improve the speed :+1: Thanks for your effort!