I’m a new user to Singlestore and really liking it so far. I have set up a single instance on AWS and loaded in some simple data using mysqldump.
I am trying to connect Singlestore as a target in AWS Data Migration Service (DMS) and it is unable to connect.
I receive an error of:
Test Endpoint failed: Application-Status: 1020912, Application-Message: Cannot connect to ODBC provider Network error has occurred, Application-Detailed-Message: RetCode: SQL_ERROR SqlState: 08S01 NativeError: 2012 Message: [unixODBC][MySQL][ODBC 8.0(w) Driver]Error in server handshake
I have tried both the MySQL and MariaDB connectors with no luck. I’m not having any luck on Google or searching this forum either.
I have defined additional connection settings such as “default-auth=mysql_native_password” also with no luck.
It isn’t a security group issue from the replication instance to the instance running SingleStore and I am able to connect to the database from my local client, Datagrip so it is responding on the expected IP address, port, username and password.
Currently I see from the error message that the default DMS instance is using ODBC 8.0 drivers. You will need to add the MySQL client parameter to disable the MySQL 8 handshake.
Thanks for your reply. I tried adding the default auth setting with no luck Im afraid, still the same ‘Error in server handshake’ error when I use the MariaDB or MySQL engine.
I have tried variations such as:
default-auth=mysql_native_password
--default-auth=mysql_native_password
defaultAuth=mysql_native_password
In each case, error in server handshake.
Attached is a screenshot in case I am inputting the value incorrectly or in the wrong place.
I need help to connect to Singlestore using AWS DMS and I am glad I have found your post in which I have found my solution. Now, I can easily connect it. I was so happy after finding your post.
Following the steps above I was able to get the full-load operation working with AWS DMS. However, when attempting to use ongoing replication I am running into issues that are being logged into my DMS logs. It appears that a temp table of some sort is being used to apply ongoing changes and operations that dms needs. Here are my logs when replication fails.
Failed (retcode -1) to execute statement; RetCode: SQL_ERROR SqlState: 42S02 NativeError: 1051 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-5.7.32]Temporary table awsdms_control.awsdms_changes1F622B61E0D8F267 cannot be altered; Execute truncate net changes table failed. 'TRUNCATE TABLE `awsdms_control`.`awsdms_changes1F622B61E0D8F267`'; Failed to truncate net changes table; Error executing command; Stream component failed at subtask 0, component st_0_5EOYXZ6INU6JNHKOVB2BO4532FZIQZPVLVG6TFA; Stream component 'st_0_5EOYXZ6INU6JNHKOVB2BO4532FZIQZPVLVG6TFA' terminated [1022517] (replicationtask.c:2891)
2023-04-18T22:44:05 [TASK_MANAGER ]E: Failed (retcode -1) to execute statement; RetCode: SQL_ERROR SqlState: 42S02 NativeError: 1051 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-5.7.32]Temporary table awsdms_control.awsdms_changes1F622B61E0D8F267 cannot be altered; Execute truncate net changes table failed. 'TRUNCATE TABLE `awsdms_control`.`awsdms_changes1F622B61E0D8F267`'; Failed to truncate net changes table; Error executing command; Stream component failed at subtask 0, component st_0_5EOYXZ6INU6JNHKOVB2BO4532FZIQZPVLVG6TFA; Stream component 'st_0_5EOYXZ6INU6JNHKOVB2BO4532FZIQZPVLVG6TFA' terminated [1022517] (replicationtask.c:2891)
Looking in my singlestore cloud console, I never see the table that is mentioned in this error, leading me to believe it either never gets created or it is somehow not visible in our cloud console.
I don’t how to start new topic. So, I am using this thread for my ques because my question is related to this topic.
I have on-prem singlestore setup and I want to connect singlestore with AWS DMS and after that I want to pull data from SingleStore to AWS MySQL.
So, dou you guys have any idea how can we do this using DMS? My motive is, the data which I have in Singlestore that same data I want in MySQL.
Hello @princsingla335
SingleStore is currently not supported as a source for AWS DMS and unlike the MySQL connector which can be used for destination, we cannot use MySQL as a source. We are currently working on building CDC out for SingleStore which can then be integrated with AWS DMS but this is 6 months out.
In the mean time here are the options that you can have:
We can use SingleStore replicate, to use a delta snapshot method to move data from SingleStore to MySQL. It is a query based technique.
You can also use select into S3 and incrementally load that data in MySQL. This will need automation from the database to be added to do incremental selects.
we are facing similar issues currently. We are trying to move data from aurora serverless to a singlestore running on ec2. We get the same error as gordon above (with the odbc driver issue).
We now wanted to try your provided solution in the screenshot by setting the extra connection attributes “default_auth=mysql_native_password”.
However, we cannot do this and instead get the error Unsupported value ‘default_auth’ for extra connection attributes when trying to save this configuration.
The AWS support just pointed us to the documentation and that singlestore is currently not supported.
Can anyone confirm if it is still possible to use singlestore as target for AWS DMS or why the solution from above does not work for us?
