Hi,

I need to get paralogues for my pig genes from Sscrofa_gene_ensembl with getBM() from bioMart package, it works for my other species (macaque, mouse) but for the pig i get the following error :

Error in .processResults(postRes, mart = mart, hostURLsep = sep, fullXmlQuery = fullXmlQuery, : Query ERROR: caught BioMart::Exception::Database: Error during query execution: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND main.gene_id_1020_key=ensembl_mart_106.sscrofa_gene_ensembl__paralog_sscrofa' at line 1

i used the code below, and i tried the "useast" mirror to solve the problem it didn't work,

Any idea on where lies the problem and how to solve it ?

Thank you !

ensembl <- useMart('ensembl') 

ensembl.pig <- useEnsembl('ensembl', dataset = 'sscrofa_gene_ensembl', mirror = "useast")

parapig <- getBM(attributes = c("sscrofa_paralog_associated_gene_name","external_gene_name", "ensembl_gene_id") ,
                 filters = "external_gene_name", "ensembl_gene_id",
                 values = pig.id, 
                 mart    = ensembl.pig)
# pig.id = gene ID of my personal data set for which i want to extract paralogues


> sessionInfo()

R version 4.2.1 (2022-06-23 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252    LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C                           LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] biomaRt_2.52.0

loaded via a namespace (and not attached):
  [1] readxl_1.4.0                shadowtext_0.1.2            fastmatch_1.1-3             BiocFileCache_2.4.0        
  [5] plyr_1.8.7                  igraph_1.3.2                lazyeval_0.2.2              splines_4.2.1              
  [9] BiocParallel_1.30.3         GenomeInfoDb_1.32.2         ggplot2_3.3.6               digest_0.6.29              
 [13] htmltools_0.5.2             yulab.utils_0.0.5           GOSemSim_2.22.0             viridis_0.6.2              
 [17] GO.db_3.15.0                fansi_1.0.3                 magrittr_2.0.3              memoise_2.0.1              
 [21] openxlsx_4.2.5              remotes_2.4.2               Biostrings_2.64.0           annotate_1.74.0            
 [25] graphlayouts_0.8.0          matrixStats_0.62.0          enrichplot_1.16.1           prettyunits_1.1.1          
 [29] colorspace_2.0-3            blob_1.2.3                  rappdirs_0.3.3              ggrepel_0.9.1              
 [33] xfun_0.31                   dplyr_1.0.9                 crayon_1.5.1                RCurl_1.98-1.7             
 [37] jsonlite_1.8.0              scatterpie_0.1.7            genefilter_1.78.0           survival_3.3-1             
 [41] ape_5.6-2                   glue_1.6.2                  polyclip_1.10-0             gtable_0.3.0               
 [45] zlibbioc_1.42.0             XVector_0.36.0              DelayedArray_0.22.0         BiocGenerics_0.42.0        
 [49] scales_1.2.0                DOSE_3.22.0                 DBI_1.1.3                   Rcpp_1.0.8.3               
 [53] viridisLite_0.4.0           xtable_1.8-4                progress_1.2.2              gridGraphics_0.5-1         
 [57] tidytree_0.3.9              bit_4.0.4                   stats4_4.2.1                httr_1.4.3                 
 [61] fgsea_1.22.0                RColorBrewer_1.1-3          ellipsis_0.3.2              pkgconfig_2.0.3            
 [65] XML_3.99-0.10               farver_2.1.0                dbplyr_2.2.1                locfit_1.5-9.5             
 [69] utf8_1.2.2                  ggplotify_0.1.0             tidyselect_1.1.2            rlang_1.0.2                
 [73] reshape2_1.4.4              AnnotationDbi_1.58.0        munsell_0.5.0               cellranger_1.1.0           
 [77] tools_4.2.1                 cachem_1.0.6                downloader_0.4              cli_3.3.0                  
 [81] generics_0.1.2              RSQLite_2.2.14              evaluate_0.15               stringr_1.4.0              
 [85] fastmap_1.1.0               yaml_2.3.5                  ggtree_3.4.0                knitr_1.39                 
 [89] bit64_4.0.5                 tidygraph_1.2.1             zip_2.2.0                   purrr_0.3.4                
 [93] KEGGREST_1.36.2             ggraph_2.0.5                nlme_3.1-157                aplot_0.1.6                
 [97] DO.db_2.9                   xml2_1.3.3                  compiler_4.2.1              rstudioapi_0.13            
[101] filelock_1.0.2              curl_4.3.2                  png_0.1-7                   treeio_1.20.0              
[105] tibble_3.1.7                tweenr_1.0.2                geneplotter_1.74.0          stringi_1.7.6              
[109] lattice_0.20-45             Matrix_1.4-1                vctrs_0.4.1                 pillar_1.7.0               
[113] lifecycle_1.0.1             BiocManager_1.30.18         data.table_1.14.2           bitops_1.0-7               
[117] patchwork_1.1.1             GenomicRanges_1.48.0        qvalue_2.28.0               R6_2.5.1                   
[121] gridExtra_2.3               IRanges_2.30.0              codetools_0.2-18            MASS_7.3-57                
[125] assertthat_0.2.1            SummarizedExperiment_1.26.1 DESeq2_1.36.0               S4Vectors_0.34.0           
[129] EnhancedVolcano_1.14.0      GenomeInfoDbData_1.2.8      parallel_4.2.1              hms_1.1.1                  
[133] clusterProfiler_4.4.4       grid_4.2.1                  ggfun_0.0.6                 tidyr_1.2.0                
[137] rmarkdown_2.14              MatrixGenerics_1.8.1        ggforce_0.3.3               Biobase_2.56.0

You have many more packages loaded than needed for a minimal example.

I bet will work in a new session since your example works in my hands (though I am using R 4.2.0, not 4.2.1, the version of BioConductor is (basically) the same (though my BioMart is a little newer than yours).

If that doesn't resolve, try updating your BioMart and all dependent packages.

If that doesn't work, try it on a linux box!

> library(biomaRt)
ensembl <- useMart('ensembl') 
ensembl.pig <- useEnsembl('ensembl', dataset = 'sscrofa_gene_ensembl', mirror = "useast")
pig.id=c('ND1','ND2')
parapig <- getBM(attributes = c("sscrofa_paralog_associated_gene_name","external_gene_name", "ensembl_gene_id") ,
                 filters = "external_gene_name", "ensembl_gene_id",
                 values = pig.id, 
                 mart    = ensembl.pig)
parapig
sessionInfo()

>   sscrofa_paralog_associated_gene_name external_gene_name    ensembl_gene_id
1                                                     ND1 ENSSSCG00000018065
2                                  ND4                ND2 ENSSSCG00000018069
3                                  ND5                ND2 ENSSSCG00000018069
> R version 4.2.0 (2022-04-22)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: CentOS Linux 7 (Core)

Matrix products: default
BLAS:   /n/apps/CentOS7/install/r-4.2.0/lib64/R/lib/libRblas.so
LAPACK: /n/apps/CentOS7/install/r-4.2.0/lib64/R/lib/libRlapack.so

locale:
[1] C

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] biomaRt_2.52.0

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.8.3           pillar_1.7.0           dbplyr_2.2.0          
 [4] compiler_4.2.0         GenomeInfoDb_1.32.2    XVector_0.36.0        
 [7] prettyunits_1.1.1      bitops_1.0-7           tools_4.2.0           
[10] zlibbioc_1.42.0        progress_1.2.2         digest_0.6.29         
[13] bit_4.0.4              tibble_3.1.7           BiocFileCache_2.4.0   
[16] RSQLite_2.2.14         memoise_2.0.1          lifecycle_1.0.1       
[19] pkgconfig_2.0.3        png_0.1-7              rlang_1.0.2           
[22] DBI_1.1.2              cli_3.3.0              filelock_1.0.2        
[25] curl_4.3.2             fastmap_1.1.0          GenomeInfoDbData_1.2.8
[28] xml2_1.3.3             dplyr_1.0.9            httr_1.4.3            
[31] stringr_1.4.0          rappdirs_0.3.3         generics_0.1.2        
[34] Biostrings_2.64.0      S4Vectors_0.34.0       vctrs_0.4.1           
[37] IRanges_2.30.0         hms_1.1.1              tidyselect_1.1.2      
[40] stats4_4.2.0           bit64_4.0.5            glue_1.6.2            
[43] Biobase_2.56.0         R6_2.5.1               fansi_1.0.3           
[46] AnnotationDbi_1.58.0   XML_3.99-0.10          purrr_0.3.4           
[49] blob_1.2.3             magrittr_2.0.3         ellipsis_0.3.2        
[52] BiocGenerics_0.42.0    assertthat_0.2.1       KEGGREST_1.36.2       
[55] utf8_1.2.2             stringi_1.7.6          RCurl_1.98-1.7        
[58] cachem_1.0.6           crayon_1.5.1          
> > >