search

USEPA / CompTox-ToxCast-tcpl

US EPA's Toxicity Forecaster (ToxCast) Pipeline. More information on the ToxCast program available here: https://www.epa.gov/comptox-tools/toxicity-forecasting-toxcast
https://cran.r-project.org/package=tcpl
Other
28 stars 12 forks source link

Updating to version 3.2 of the database #4

Closed ldecicco-USGS closed 4 years ago

ldecicco-USGS commented 4 years ago

I'm trying to update to version 3.2 of the database, using the .bat file as described in the README (from the 3.2 download). After a long time, I get:

image

I was hoping that wouldn't matter, but when I do this:

library(tcpl)

tcplConf(drvr = 'MySQL', 
         user = 'xxx',
         pass = 'xxx',
         host = 'xxx', 
         db = 'tox32')

cas <- "1912-24-9"
chem_info <- tcplLoadChem(field = 'casn', val = cas)
Warning message:
  In tcplLoadChem(field = "casn", val = cas) :
  The given casn(s) are not in the tcpl database.

"1912-24-9" is on the Comptox dashboard.

Do you happen to have any suggestions specific to the 3.2 version?

ldecicco-USGS commented 4 years ago

I wiped out the old database, re-ran the .bat file and it still had the error, but seemed move forward and now I can create dose response curves. So, I think it's all good.

ldecicco-USGS commented 4 years ago

Sorry, reopening because I still can't load the chemicals I'm interested in. I tried re-loading the database (maybe horribly stupid....), but first I ran this in the SQL terminal in MySQL Workbench:

SET GLOBAL local_infile = 1;

based on: https://dba.stackexchange.com/questions/17885/local-infile-1-using-mysql-workbench

I still got the exact same error in the exact same place. Not sure if there's someone working on this package that is familiar with the Windows database installation (Load-InvitroDB.bat).

ldecicco-USGS commented 4 years ago

Version 3.1 had a single sql files that I could run. Version 3.2 seems to have a bunch in the "prod_internal_invitrodb_v3_2" folder. I assume the batch file (Load-InvitroDB.bat) was set up to not have to worry about it, but maybe I could run each of those sql files individually (I might try tomorrow).

galenspikes commented 4 years ago

Hi Laura,

I work on InvitroDB at the EPA and also wrote the loader scripts, so I'll be able to help you with the issue you're having. I just need some environment information so I can best support you.

What version of MySQL are you using? Is the MySQL server local to your machine or is a remote server? Can you please attach any error logs to this ticket if you have them?

Thanks

Galen Spikes CSS | Senior Database Engineer | Research Laboratory Support Team for EPA-RTP National Center for Computational Toxicology Office of Research & Development Contractor to U.S. Environmental Protection Agency 109 TW ALEXANDER DR DURHAM NC 27709

ldecicco-USGS commented 4 years ago

I'm using:

$ mysql -V
C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe  Ver 8.0.16 for Win64 on x86_64 (MySQL Community Server - GPL)

MySQL Workbench (8.0.2) Running locally (so to connect, I specify localhost)

The only error I get is shown in that screenshot: image

I'm definitely not a database administrator expert...so if there's more info you need, explain it like I'm 5 😳

galenspikes commented 4 years ago

Hi Laura,

Thanks for getting back to me. That's no problem, one problem I see right away is you're using MySQL 8, but this package was developed with MySQL 5.7. There's been a lot of changes in MySQL 8 and it wasn't tested with that version.

Since you're just running it locally, can you uninstall MySQL 8 and download/install MySQL 5.7? Link: https://dev.mysql.com/downloads/windows/installer/5.7.html

galenspikes commented 4 years ago

Once you are able to install MySQL 5.7, please try running the .bat file again and we can troubleshoot from there.

ldecicco-USGS commented 4 years ago

Before I do anything rash...that means uninstalling ALL the MySQL components, right? I've been trying to get "just" the MySQL to go to version 5.7, but running the 5.7.29 installer, I see: image

I assume that means it knows that I'm already at version 8 and it doesn't want to downgrade.

galenspikes commented 4 years ago

Yeah you want to completely uninstall everything first, then install MySQL 5.7 fresh.

ldecicco-USGS commented 4 years ago

OK, completely uninstalled everything. MySQL version is now Ver 14.14 Distrib 5.7.29, for Win64 (x86_64). I run the .bat file and get:

image

And, yeah, I still can't access the chemicals I'm interested (locally).

galenspikes commented 4 years ago

Hi, can you attach or copy/paste the total console output? I would like to know what specific table(s) are failing.

ldecicco-USGS commented 4 years ago

I'm not sure how to do that....if I run:

Load-InvitroDB.bat > C:\ToxCast32\consoleOutput.txt

I can't do the interactive initial log-in. If I try to just paste the output, it's way to long. Here's the initial stuff....a BUNCH of lines like "Records: 5541 Duplicates: 0 Warnings: 0" are missing, and then the line that is causing the problem is at the end:

################################################################################################
###################################### InvitroDB v3.2 ##########################################
################################################################################################
LOADING...
CREATING DATABASE AND SCHEMA...

TOO MUCH for me to paste (the cmd window only goes back so far)

Records: 5668  Duplicates: 0  Warnings: 0

Records: 5522  Duplicates: 0  Warnings: 0

Records: 5533  Duplicates: 0  Warnings: 0

Records: 5562  Duplicates: 0  Warnings: 0

Records: 5523  Duplicates: 0  Warnings: 0

Records: 5541  Duplicates: 0  Warnings: 0

Records: 5527  Duplicates: 0  Warnings: 0

Records: 5556  Duplicates: 0  Warnings: 0

Records: 5508  Duplicates: 0  Warnings: 0

Records: 5541  Duplicates: 0  Warnings: 0

Records: 5531  Duplicates: 0  Warnings: 0

Records: 5549  Duplicates: 0  Warnings: 0

Records: 5535  Duplicates: 0  Warnings: 0

Records: 5520  Duplicates: 0  Warnings: 0

Records: 5528  Duplicates: 0  Warnings: 0

Records: 5540  Duplicates: 0  Warnings: 0

Records: 5562  Duplicates: 0  Warnings: 0

Records: 5558  Duplicates: 0  Warnings: 0

Records: 5514  Duplicates: 0  Warnings: 0
ERROR: 1064 (42000) at line 12841: 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 '00:00:-00 :00:00','rjudson.r8' at line 1
ldecicco-USGS commented 4 years ago

I tried restarting again to get the info right after "CREATING DATABASE AND SCHEMA..." but it goes way too fast. I think there are some notices about missing tables that happen right away.

ldecicco-USGS commented 4 years ago

I made a copy of the .bat file, and thought I'd walk through each part one at a time. So, here's the top of the first line:

The part of the bat file I'm running (deleted everything below this):

start /B %mysql_executable% --sql %mysql_user%@%mysql_host%:%mysql_port%/%invitrodb_schema_name% --password < %invitrodb_sql_file_dir%\prod_internal_invitrodb_v3_2-mc0.sql

Output:

What do you want to name the database schema? tox32
################################################################################################
###################################### InvitroDB v3.2 ##########################################
################################################################################################
LOADING...
CREATING DATABASE AND SCHEMA...
CREATING TABLES...
LOADING DATA (this will take some time)...

############ Loading Table: mc0 ############

INVITRODB UPLOAD COMPLETED
Press any key to continue . . .
Records: 4891  Duplicates: 0  Warnings: 0
...........(and a bunch more of those until)
Records: 5558  Duplicates: 0  Warnings: 0

Records: 5514  Duplicates: 0  Warnings: 0
ERROR: 1064 (42000) at line 12841: 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 '00:00:-00 :00:00','rjudson.r8' at line 1

so, moving on to the 2nd sql line:

echo ############ Loading Table: mc0 ############
start /B %mysql_executable% --sql %mysql_user%@%mysql_host%:%mysql_port%/%invitrodb_schema_name% --password < %invitrodb_sql_file_dir%\prod_internal_invitrodb_v3_2-mc0.sql
echo.

I get:

LOADING...
CREATING DATABASE AND SCHEMA...
CREATING TABLES...
LOADING DATA (this will take some time)...

############ Loading Table: mc4 ############
The system cannot find the file specified.

INVITRODB UPLOAD COMPLETED
Press any key to continue . . .
galenspikes commented 4 years ago

I'm really sorry Laura, I'm not sure why this is happening. Let me test this on my end and figure out what the issue.

Please also double check your default collation and character set in MySQL is utf8mb4_unicode_ci. Other collations and character sets will cause issues.

brown-jason commented 4 years ago

@ldecicco-USGS have you tried downloading the updated bat file here: https://github.com/USEPA/-CompTox-ToxCast-invitrodb

Replace that .bat file with the one in your directory and see if that works any better?

ldecicco-USGS commented 4 years ago

I have re-downloaded the entire MySQL .zip file, and tried re-running that starting 2 days ago. Twice it's gone on for at least 6 hours, but then the computer hits 100% Disk and stalls out (I've left it running overnight so I know it's stalled and not going really slow)....so I'm starting to lose hope that I'll be able to get this all loaded up (I've already had our IT staff bump up the disk capacity twice, I don't think they can add more space without completely replacing it).

I'm running the new .bat file from the github link above and crossing my fingers that it'll work though.

ldecicco-USGS commented 4 years ago

image

👯‍♂️💯

I used the new .bat file from the github repo. It took 2 full days.... but if finally uploaded! Thanks for all of your help!