Questions tagged with MySQL
Content language: English
Sort by most recent
Hello,
I am receiving a "no handler found for uri" error when attempting to replicate data from a MySQL RDS instance using DMS 3.4.7 to OpenSearch 2.5.
```
2023-03-29T18:35:07 [TARGET_LOAD ]E: Elasticsearch:FAILED SourceTable:accounts TargetIndex:accounts Operation:INSERT_ENTRY RecordPKKey:1010 RecordPKID:7A5DF5FFA0DEC2228D90B8D0A0F1B0767B748B0A41314C123075B8289E4E053FES HttpCode:400 ESErrorResponse:
{ "error": "no handler found for uri [/accounts/doc/7A5DF5FFA0DEC2228D90B8D0A0F1B0767B748B0A41314C123075B8289E4E053F] and method [PUT]" }
[1026400] (elasticsearch_utils.c:657)
```
I ssh'd into the OpenSearch cluster and the index does exist, so it is creating the index, but no records are being written. What is strange to me is that based on this error, it looks like DMS is attempting to write the record to `/accounts/doc/id` when in the official OpenSearch documentation the operation should be using `/accounts/_doc/id` as noted here:
```
PUT sample-index/_doc/1
{
"Description": "To be or not to be, that is the question."
}
```
https://opensearch.org/docs/2.5/api-reference/document-apis/index-document/
When I attempt to insert a record with the underscore (PUT accounts/_doc/1) it works.
Am I missing something here? Here is my task config:
```
{
"Logging": {
"EnableLogging": true,
"EnableLogContext": false,
"LogComponents": [
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "TRANSFORMATION"
},
{
"Severity": "LOGGER_SEVERITY_DETAILED_DEBUG",
"Id": "SOURCE_UNLOAD"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "IO"
},
{
"Severity": "LOGGER_SEVERITY_DETAILED_DEBUG",
"Id": "TARGET_LOAD"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "PERFORMANCE"
},
{
"Severity": "LOGGER_SEVERITY_DETAILED_DEBUG",
"Id": "SOURCE_CAPTURE"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "SORTER"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "REST_SERVER"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "VALIDATOR_EXT"
},
{
"Severity": "LOGGER_SEVERITY_DETAILED_DEBUG",
"Id": "TARGET_APPLY"
},
{
"Severity": "LOGGER_SEVERITY_DETAILED_DEBUG",
"Id": "TASK_MANAGER"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "TABLES_MANAGER"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "METADATA_MANAGER"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "FILE_FACTORY"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "COMMON"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "ADDONS"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "DATA_STRUCTURE"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "COMMUNICATION"
},
{
"Severity": "LOGGER_SEVERITY_DEFAULT",
"Id": "FILE_TRANSFER"
}
],
"CloudWatchLogGroup": "hidden",
"CloudWatchLogStream": "hidden"
},
"StreamBufferSettings": {
"StreamBufferCount": 3,
"CtrlStreamBufferSizeInMB": 5,
"StreamBufferSizeInMB": 8
},
"ErrorBehavior": {
"FailOnNoTablesCaptured": false,
"ApplyErrorUpdatePolicy": "LOG_ERROR",
"FailOnTransactionConsistencyBreached": false,
"RecoverableErrorThrottlingMax": 1800,
"DataErrorEscalationPolicy": "SUSPEND_TABLE",
"ApplyErrorEscalationCount": 0,
"RecoverableErrorStopRetryAfterThrottlingMax": false,
"RecoverableErrorThrottling": true,
"ApplyErrorFailOnTruncationDdl": false,
"DataTruncationErrorPolicy": "LOG_ERROR",
"ApplyErrorInsertPolicy": "LOG_ERROR",
"EventErrorPolicy": "IGNORE",
"ApplyErrorEscalationPolicy": "LOG_ERROR",
"RecoverableErrorCount": -1,
"DataErrorEscalationCount": 0,
"TableErrorEscalationPolicy": "STOP_TASK",
"RecoverableErrorInterval": 5,
"ApplyErrorDeletePolicy": "IGNORE_RECORD",
"TableErrorEscalationCount": 0,
"FullLoadIgnoreConflicts": true,
"DataErrorPolicy": "LOG_ERROR",
"TableErrorPolicy": "SUSPEND_TABLE"
},
"TTSettings": {
"TTS3Settings": null,
"TTRecordSettings": null,
"EnableTT": false
},
"FullLoadSettings": {
"CommitRate": 10000,
"StopTaskCachedChangesApplied": false,
"StopTaskCachedChangesNotApplied": false,
"MaxFullLoadSubTasks": 8,
"TransactionConsistencyTimeout": 600,
"CreatePkAfterFullLoad": false,
"TargetTablePrepMode": "DROP_AND_CREATE"
},
"TargetMetadata": {
"ParallelApplyBufferSize": 0,
"ParallelApplyQueuesPerThread": 0,
"ParallelApplyThreads": 0,
"TargetSchema": "",
"InlineLobMaxSize": 0,
"ParallelLoadQueuesPerThread": 0,
"SupportLobs": true,
"LobChunkSize": 64,
"TaskRecoveryTableEnabled": false,
"ParallelLoadThreads": 0,
"LobMaxSize": 0,
"BatchApplyEnabled": false,
"FullLobMode": true,
"LimitedSizeLobMode": false,
"LoadMaxFileSize": 0,
"ParallelLoadBufferSize": 0
},
"BeforeImageSettings": null,
"ControlTablesSettings": {
"historyTimeslotInMinutes": 5,
"HistoryTimeslotInMinutes": 5,
"StatusTableEnabled": false,
"SuspendedTablesTableEnabled": false,
"HistoryTableEnabled": false,
"ControlSchema": "",
"FullLoadExceptionTableEnabled": false
},
"LoopbackPreventionSettings": null,
"CharacterSetSettings": null,
"FailTaskWhenCleanTaskResourceFailed": false,
"ChangeProcessingTuning": {
"StatementCacheSize": 50,
"CommitTimeout": 1,
"BatchApplyPreserveTransaction": true,
"BatchApplyTimeoutMin": 1,
"BatchSplitSize": 0,
"BatchApplyTimeoutMax": 30,
"MinTransactionSize": 1000,
"MemoryKeepTime": 60,
"BatchApplyMemoryLimit": 500,
"MemoryLimitTotal": 1024
},
"ChangeProcessingDdlHandlingPolicy": {
"HandleSourceTableDropped": true,
"HandleSourceTableTruncated": true,
"HandleSourceTableAltered": true
},
"PostProcessingRules": null
}
```
Test Endpoint failed: Application-Status: 1020912, Application-Message: Failed to retrieve secret. Unable to find Secrets Manager secret, Application-Detailed-Message: Unable to find AWS Secrets Manager secret Arn 'arn:aws:secretsmanager:<region>:<account>:secret:XXXXXXX' The secrets_manager get secret value failed: curlCode: 7, Couldn't connect to server Too many retries: curlCode: 7, Couldn't connect to server
Hi, I'm doing a DMS Endpoint connection test between Priavate VPCs.
I'm fighting the above error.
I set it up as below.
* VPC1: Replication instance
* VPC2 : Mysql RDS (admin password with secret manager)
* The VPC is on the same account.
I also created a **SecretManager VPC Endpoint on VPC1,**
and the IAM Role connected to the DMS endpoint...
This role has the following policy:
```
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "secretsmanager:GetSecretValue",
"Resource": [
"arn:aws:secretsmanager:<region>:<account>:secret:XXXXXXX",
]
},
{
"Effect": "Allow",
"Action": [
"kms:Decrypt",
"kms:DescribeKey"
],
"Resource": "arn:aws:kms:<region>:<account>:key/*"
}
]
}
```
Here is the role trust policy:
```
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": [
"dms.amazonaws.com",
"dms.<region>.amazonaws.com"
]
},
"Action": "sts:AssumeRole"
}
]
}
```
but the connection is not possible.
If I test the endpoint connection without using SecretManager with the same user password value, it will connect well.
What's the problem? Please help me.
Hi,
my current setup:
* EC2 with ARM
* Docker installed in EC2
* Spring + Java app in one container
* MySQL in another container
When I run it all in the EC2 it works like charm, but problem occurs when I am trying to connect mysql storage to an attached EBS.
my docker run command for mysql:
`docker run -d -p 3306:3306 -v /dev/xvdf/mysql:/var/lib/mysql:rw -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=erdeldb mysql:8`
When setting volume as `/dev/sdf/mysql` I get an error saying `it is not a directory`. I also can not open that directory in console with same error `cd /dev/sdf/` returns `not directory error`
When setting volume as `/dev/xvdf/mysql` I get storage issue, not enough space.
When I check storage of /dev/xvdf after I have attached the EBS, I see 4.0 MB
I am not sure what am I doing wrong. I haven't deployed before, just learning. Any inputs, thanks.
I have an on-premises MySQL database that needs to be migrated to an AWS RDS MySQL database. The on-premises database will be updated regularly, and I want to update the RDS database with the latest records from the on-premises database on a daily basis at a scheduled time. The two databases have schema differences, and I need to modify the data from the on-premises database to match the schema of the RDS database. I will not be performing any analytics on the data, and the RDS database will be used as the database for a web application.
Can you suggest an ideal approach for this scenario?
Thanks in Advance!
I've recently upgraded my database from mysql5.7 to mysql8.0.26 which also incorporated an upgrade from Aurora 2 to Aurora 3 which is where I think the issue lies as there are no references to the following issue in the mySQL documentation.
During the upgrade the myql.user table is copied to the new database (using a Blue/Green deployment). GRANT permissions are also copied across and all the details remain unchanged as I have verified with `SHOW GRANTS` on both the blue and green environments. However, my users in the mysql8 DB do not have access to the resources identified in the GRANTS where those resources contain an underscore.
To reproduce the issue:
1. Create an Aurora 3 DB instance running MySQL 8.0.26
2. CREATE SCHEMA new_schema ;
3. CREATE TABLE new_schema.test_table (id int);
4. GRANT ALL PRIVILEGES ON `new\_schema`.* TO `my-user`@`%`
Run `SHOW GRANTS for my-user;` and you will see the following response:
```
GRANT ALL PRIVILEGES ON `new\_schema`.* TO `my-user`@`%`
```
That is the expected response, and mySQL documentation advises keeping the escape character in place to avoid any issues related to partial_revokes (which I can confirm is OFF by default).
Now the problem is, in Aurora 3, if I log in with `my-user` and run `SELECT * FROM new_schema.test_table` I get a permission denied error.
I have investigated a similar sounding issue in re:post [here](https://repost.aws/questions/QUeD79WVUQT4WJ9bpMNJ7hfg/aurora-3-my-sql-the-value-of-the-partial-revokes-parameter-in-the-parameter-group-is-different-from-the-value-of-the-same-variable-in-my-sql) however, I have already confirmed that `partial_revokes` as set to 0 (disabled) both in my database parameter groups and if I query it directly in mysql.
Hi,
Im getting this strange error:
```
ERROR 1227 (42000): Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation
```
Trying to run this command on "writer instance" of "multi-az cluster":
```
grant replication slave, replication client, replication_slave_admin on *.* to 'repl'@'%';
```
Knowing that the same command works on the "multi-az" "writer instance", im confused.
I have set up a new instance in Lightsail with Debian 11 LAMP stack,
1. Connected PhpMyAdmin through a tunnelled PuTTY and uploaded my data which looks good.
2. Connected with FIleZilla and uploaded some php which works, and need to connect to the MySql with command line.
I have used mysql -u bitnami -p with password in the PuTTY command line and get ERROR 1045 (28000): Access denied for user 'bitnami'@'localhost' (using password: YES).
I have tried all the passwords I have used with this and am getting nowhere, not sure what i am missing. Can anyone help please?
Hello there,
i have currently** t3a.xlarge** instance running Ubuntu and would like to migrate to a **t4g.xlarge **to take advantage of performance of Graviton.
I am running Java and MySQL db.
How can I migrate it with minimal change?
Thanks
The other day I went to apply the update to 5.7. It failed do to some stuff I missed. The log was:
```
1) MySQL 5.7 preupgrade check to catch orphan table issues
For the following tables, either the datadir directory or frm file was removed or corrupted.
More Information:
https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
[table_schema, table_name]
DB_NAME, - Check the server logs or examine datadir to determine the issue and correct it before upgrading.
2) The DB instance must have enough space to rebuild the largest table that uses an old temporal data format.
The DB instance must have enough space for the largest table that uses an old temporal data format to rebuild.
More Information:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.MySQL.html#USER_UpgradeDBInstance.MySQL.DateTime57
You must rebuild tables that use an old temporal data format, it might take an extended period of time. - Scale storage so that there is enough space for the largest table that uses an old temporal data format, and make sure the storage type is gp2 or io1, if possible.
3) Check for heavy load or a high number of write operations on instance before upgrade
* History list length
No issues found.
* Insert buffer size
No issues found.
Errors: 1
Warnings: 1
Database Objects Affected: 2
----------------------- END OF LOG ----------------------
```
That day I did what should have been done. I dropped all the orphan tables. (There was only one) I updated all the tables and data that had the old date format. I then moved the maintenance window to the next morning so it would automatically do it in the maintenance window since, and this is the most annoying part, because I manually applied the recommendation, and it failed, It now shows the recommendation to update as applied, and I can no longer just click apply in the console.
Anyways. It didn't update the next morning. The compatibility log last written is still from the first time I ran it manually. There has been no update to it. I then changed the maintenance window again to the next morning. (this morning. 15th of March) Got to work this morning and checked it. Still hasn't been updated. By the looks of the log, it hasn't even been attempted again.
That being said. I did check a few more things and realized I had forgotten about storage space. (I know it says it in the log. Lol) I only had 18GB left so I just upped that now so there is enough space for the rebuild. I again have changed the maintenance window to tomorrow morning so hopefully, it will update. Not super hopeful since the logs haven't been touched all the other times.
Does anyone have any ideas on how I can manually update it if it doesn't work again? Or how I can get the recommendation back so I can use it?
I am using the Aurora Blue/Green deployment process to upgrade by database from mySQL5.7 to mySQL8.0.26. This also is upgrading the Aurora engine from 2 to 3.
The upgrade fails due to a pre-check failure:
```
{
"id": "engineMixupCheck",
"title": "Tables recognized by InnoDB that belong to a different engine",
"status": "OK",
"description": "Error: Following tables are recognized by InnoDB engine while the SQL layer believes they belong to a different engine. Such situation may happen when one removes InnoDB table files manually from the disk and creates e.g. a MyISAM table with the same name.\n\nA possible way to solve this situation is to e.g. in case of MyISAM table:\n\n1. Rename the MyISAM table to a temporary name (RENAME TABLE).\n2. Create some dummy InnoDB table (its definition does not need to match), then copy (copy, not move) and rename the dummy .frm and .ibd files to the orphan name using OS file commands.\n3. The orphan table can be then dropped (DROP TABLE), as well as the dummy table.\n4. Finally the MyISAM table can be renamed back to its original name.",
"detectedProblems": [
{
"level": "Error",
"dbObject": "mysql.general_log_backup",
"description": "recognized by the InnoDB engine but belongs to CSV"
}
]
}
```
As an Aurora user, it is not possible for me to delete, move, move, alter or change any tables in the `mysql` tablespace, so the recommend remediation is not possible.
So my question is, how can I force the Blue/Green process to skip this check, or even better, how can I manually DROP the `mysql.general_log_backup` table as I do not need it?
Please note I am using "FILE" based logging the DB parameters.
Steps to reproduce:
- Create an aurora instance with Engine version 5.7.mysql_aurora.2.10.3
- start a blue green deployment with
* engine version 8.0 and aurora3+
* use custom cluster parameter group
* use custom instance parameter group
- Blue Green environment created
- DB Engine Upgrade fails
Thanks!
I have a snapshot of a MySQL 5.6.35 instance that I want to restore, but it seems like this is no longer possible, I just get the following message when trying to restore the snapshot from the AWS console :
RDS does not support creating a DB instance with the following combination: DBInstanceClass=db.m6i.large, Engine=mysql, EngineVersion=5.6.35, LicenseModel=general-public-license. For supported combinations of instance class and database engine version, see the documentation.
Is there any other way to retrieve the database contents from the snapshot? Or is there a way to upgrade the snapshot itself to MySQL 5.7?
I have an RDS instance using MySQL engine version 5.7.36. RDS recommends updating to a newer minor 5.7 engine version, but no versions are available to choose when modifying the instance. describe-db-engine-versions doesn't list 5.7.36, but does list 5.7.34 and 5.7.37, and newer versions after that. Snapshots taken from the instance cannot be restored, because creating a new instance using the version the snapshots specify is not allowed.
5.7.36 is still documented on https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Concepts.VersionMgmt.html as supported until April 2023. As far as I can see there are no notifications that it's deprecated.
There was a Health notification in January of impending end of support but no indication of this behavior:
> Starting March 20, 2023 00:00:01 AM UTC, you will not be able to create new RDS instances with MySQL minor versions 8.0.27, 8.0.26, 8.0.25, 8.0.23, 5.7.36, 5.7.34 and 5.7.33 from either the AWS Console or the CLI. We recommend you to upgrade your databases before April 20, 2023. RDS will upgrade your MySQL databases running minor versions 8.0.27, 8.0.26, 8.0.25, 8.0.23, 5.7.36, 5.7.34 and 5.7.33 as well as any instances restored from the snapshots of these versions to the latest minor version during a scheduled maintenance window between April 20, 2023 00:00:01 UTC and May 20, 2023 00:00:01 UTC. On May 20, 2023 00:00:01 AM UTC, any MySQL databases running minor versions 8.0.27, 8.0.26, 8.0.25, 8.0.23, 5.7.36, 5.7.34 and 5.7.33 that remain will be automatically upgraded to the latest minor version by RDS regardless of instances’ scheduled maintenance window.
So, upgrades should be available until April 20, and even today new instances should still be able to be created with these versions. Every other version listed in the notification is still currently available, except 5.7.36.
To me this indicates an error or mistake on the part of AWS. What are my options here?