Friday, March 22, 2019

When AWS SCT Unable to Connect to MySQL in RDS or EC2

AWS Schema Conversion Tool (SCT) is one of the must tool for a successful migration of databases to AWS RDS.

If you are trying to connect to MySQL hosted on EC2 instance or RDS and unable to make the connection despite of setting the security groups correctly and making sure that port, hostname, username and password are right, then first check the log of SCT.




2019-03-22 19:40:16.866 [   1]     GENERAL INFO    global_settings:
    app_folder=AWS Schema Conversion Tool
    bucket-name=sct-cloud9-test
    console_log_folder=C:\Users\mirza\AWS Schema Conversion Tool\Log
    convert_without_statistic_reminder=false
    data_migration_settings=
    db2luw_driver_file=
    debug_mode=false
    ext_pack_version_alert=true
    facade_log_folder=C:\Users\mirza\AWS Schema Conversion Tool\Log
    file-system-thread-count=1
    forget-state=false
    greenplum_driver_file=
    install_dir=C:\Users\mirza
    log.max_file_size_mb=100
    log.max_files_count=20
    log_folder=C:\Users\mirza\AWS Schema Conversion Tool\Log
    log_folder_extractors=C:\Users\mirza\AWS Schema Conversion Tool\Extractor Log
    log_folder_extractors_ask_before_load=false
    log_folder_extractors_cassandra=C:\Users\mirza\AWS Schema Conversion Tool\Extractor Log\Cassandra
    maria_driver_file=
    message_level.CLONE_DATACENTER=INFO
    message_level.COMPARE_SCHEMA=INFO
    message_level.CONTROLLER=INFO
    message_level.GENERAL=INFO
    message_level.LOADER=INFO
    message_level.PARSER=INFO
    message_level.PRINTER=INFO
    message_level.RESOLVER=INFO
    message_level.TELEMETRY=INFO
    message_level.TRANSFORMER=INFO
    message_level.TYPEMAPPING=INFO
Caused by: com.amazon.sct.dbloader.DbLoaderHandledException: Connection wasn't established. Check connection properties.
at com.amazon.sct.dbloader.DbLoader.checkConnection(DbLoader.java:512)
at com.amazon.sct.dbloader.DbLoader.connect(DbLoader.java:349)
at com.amazon.sct.dbloader.DbLoaderContainer.checkAccessibility(DbLoaderContainer.java:25)
at com.amazon.sct.task.TestConnectionTask.call(TestConnectionTask.java:26)
at com.amazon.sct.task.TestConnectionTask.call(TestConnectionTask.java:12)
at javafx.concurrent.Task$TaskCallable.call(Task.java:1423)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
... 1 more

In this case, there are no errors about time out which commonly refers to security group configuration or any about the credential issue. Here the issue is not with SCT or any of AWS component, rather its about the user which you are using to connect to MySQL.

Create user MySQL as follows and try and it should work:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

Then first test connection using MySQL workbench and then try with SCT.

Hope it helps.