Error in .local(conn, statement,..): operation CREATE USER failed for 'ametsecure'@'%' in AMET run

Hello @wyat.appel @cjcoats ,

I am trying to give AMET run by following this guide: AMET/AMET_QuickStart_Guide_v14.md at master · USEPA/AMET · GitHub

However in step-6 (database setup), I am getting following error (attached terminal error :

:

Failed to create new user with the error: Error in .local(conn, statement, …) : **
** could not run statement: Operation CREATE USER failed for ‘ametsecure’@’%'

**.Error: **
Execution halted

I am using mysql v8.0.23 ; perl v5.16.3, and R v3.6.0. And users of mysql is MYSQL USER

I modified amet-config.R file (attached : amet-config.R.txt (1.9 KB) AMET v1.4 as follows:
## Directory Paths
amet_base ← Sys.getenv(‘AMETBASE’) ## The AMET base directory where AMET is installed

##::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
## MySQL Configuration ###
## The AMET user info provided here must have database write permission if used in the aqProject.csh and/or metProject.csh scripts **
## Otherwise, if just used for the analysis side of AMET, database read permission only is sufficient
mysql_server ← “localhost” ## Name of MYSQL server
amet_login ← “ametsecure” ## AMET Root login for MYSQL serve
amet_pass ← "
**" ## AMET Root password for MYSQL server
maxrec ← -1 ## Set MySQL maximum records for queries (-1 for no maximum)

Also, I have created databases during mysql installation (screeshot)

Any suggestions would be greatly appreciated.

Hi,

It seems like the first likely place this error occurred is in the MySQL root name/password information. Have you confirmed those are correct? Also, if you’re familiar working directly with MySQL (which I assume you are since you already created several databases) you could create the ametsecure user directly through the MySQL command line when logged in as the MySQL root.

I’m a little stumped by the Operation CREATE USER failed for ‘ametsecure’@’%. I think that % should be the MySQL hostname. This thread seems to explain the same error:

It does seem like you specify the mysql_server to be “localhost”. You could try instead to set it to the actual name of the server and see if that fixes your issue.

Also, it looks like you do have an ametsecure user available for your local host (based on the image you posted above). Are you not able to use the ametsecure user?

Wyat

Hi @wyat.appel
Here is what I did:

  1. In the /scripts_db/dbsetup/create_amet_user.csh ; I commented (added # sign) in following lines (modified lines)
    #set mysql_login="config_file"
    #set mysql_password="config_file"
    Then when run the scripts it ask me to create a new user and password (i created new user ‘amet21’) which automatically set a database ‘mysql’ too (since **/R_db_code/setup_AMETdb_user.R scripts has a line **
    dbConnect(MySQL(),user=mysql_root_login, password=mysql_root_pass,dbname=“mysql”,host=mysql_server

I successflully ran the /dbsetup/create_amet_user.csh script and /aqExample/aqProject_post_only.csh with WRITE_SITEX, RUN_SITEX, LOAD_SITEX T option

  1. Then I tried to run /scripts_analysis/aqExample/ stats_plots_leaflet.csh ; where I intially got some errors as ‘data.table’ and ‘leaflet’ packages were not installed (Note: AMET/AMET_QuickStart_Guide_v14.md at master · USEPA/AMET · GitHub user guide didn’t mentioned these packages). I installed these packages.

  2. Then I ran the stats_plots_leaflet.csh ; which giving me following error (attached stats_plots_leaflet.txt (2.3 KB) ) [ I used COMBINE utilites from AMETV1.2 but this all other scripts/utilities is AMET V 1.4 ]

Error: $ operator is invalid for atomic vectors
Execution halted

Please help