# rds-database

Part of **RDS**

# ApsaraDB RDS Database Management Troubleshooting Guide

## Problem Index

| Problem | Symptom | Severity | Solution Summary |
|--------|--------|----------|------------------|
| Database Creation Permission Denied | `ERROR 1044 (42000): Access denied for user` when creating a database | High | Grant CREATE permission using a privileged account |
| Cannot Add Foreign Key Constraint | `Cannot add foreign key constraint` during ALTER TABLE | Medium | Ensure referenced column is a primary key in the parent table |
| Unknown MySQL Server Host | `Unknown MySQL server host` or `Name or service not known` on connection | High | Verify endpoint format and DNS configuration |
| Permission Denied for Language C | `ERROR: permission denied for language c` when creating PostgreSQL function | Medium | Install required extension like `uuid-ossp` |
| Definer Does Not Exist in DMS Login | `The user specified as a definer ('user'@'host') does not exist` | High | Create the missing account via RDS console |
| Temporary Table GTID Restriction | `ERROR 1784` when using temporary tables in MySQL 5.6 with GTID | Medium | Use regular tables or ensure autocommit=1 |
| ThinkSNS Installation Error | `OPERATION need to be executed set by ADMIN` during setup | Medium | Modify `install.php` to use `mysql_select_db()` |
| Extension Creation Blocked | `InvalidParameter` when creating extension on old PostgreSQL version | Medium | Upgrade minor engine version to 20230330 or later |

## Problem Details

### Problem 1: Database Creation Permission Denied

**Symptoms**
- Error message: `ERROR 1044 (42000): Access denied for user 'xxx'@'%' to database 'xxx'`
- Behavior: Standard account fails to create a new database
- Context: Occurs when using a non-privileged account on RDS for MySQL

**Root Cause**
The standard account lacks the global `CREATE` privilege required to create databases. Only privileged accounts (e.g., the instance owner) can grant this permission.

**Solution**
1. Log in to the RDS instance using a privileged account (typically the primary account created during instance setup).
2. Run the following SQL command to grant the necessary permission:
```sql
GRANT CREATE ON *.* TO '<standard_account_name>'@'%';
```
Replace `<standard_account_name>` with the actual username of the standard account.

**Verification**
- Attempt to create a test database using the standard account:
```sql
CREATE DATABASE test_db_123;
```
- Expected result: Success without error. Drop the test database afterward if no longer needed.

### Problem 2: Cannot Add Foreign Key Constraint

**Symptoms**
- Error message: `Cannot add foreign key constraint`
- Behavior: `ALTER TABLE ... ADD FOREIGN KEY` fails
- Context: Creating relationships between existing tables in RDS for MySQL

**Root Cause**
The column referenced in the foreign key is not defined as a primary key (or unique key) in the parent table. MySQL requires referential integrity constraints to point to a primary/unique key.

**Solution**
1. Check the structure of both tables:
```sql
SHOW CREATE TABLE tstudent;
SHOW CREATE TABLE tscore;
```
2. If the referenced column (e.g., `sno` in `tstudent`) is not a primary key, add one:
```sql
ALTER TABLE tstudent ADD PRIMARY KEY(sno);
```
3. Now create the foreign key constraint:
```sql
ALTER TABLE tscore ADD CONSTRAINT fk_tscore_sno FOREIGN KEY(sno) REFERENCES tstudent(sno);
```

**Verification**
- The `ALTER TABLE` command completes without error.
- Confirm the constraint exists:
```sql
SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tscore' AND REFERENCED_TABLE_NAME IS NOT NULL;
```

### Problem 3: Unknown MySQL Server Host

**Symptoms**
- Error message: `ERROR 2005 (HY000): Unknown MySQL server host 'xxx'`
- Error message: `SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known`
- Behavior: Connection attempt fails immediately with DNS resolution error
- Context: Connecting from application servers, local machines, or scripts

**Root Cause**
The RDS endpoint hostname cannot be resolved by the DNS server used by the client. Common causes include typos in the endpoint, missing public network access, or restrictive DNS settings.

**Solution**
1. Verify the endpoint format in the RDS console (should look like `xxx.mysql.rds.aliyuncs.com`).
2. Ensure the RDS instance has public network access enabled if connecting from outside Alibaba Cloud VPC.
3. If using a custom DNS server, switch to a public DNS (e.g., `8.8.8.8` or Alibaba Cloud DNS `223.5.5.5`):
   - On Linux: Edit `/etc/resolv.conf` and add `nameserver 223.5.5.5`
   - On Windows: Update DNS server in network adapter settings
4. Test resolution using `nslookup` or `dig`:
```bash
nslookup your-rds-endpoint.mysql.rds.aliyuncs.com
```

**Verification**
- The `nslookup` command returns a valid IP address.
- Application successfully establishes a database connection.

### Problem 4: Permission Denied for Language C in PostgreSQL

**Symptoms**
- Error message: `ERROR: permission denied for language c`
- Behavior: Function creation fails in ApsaraDB RDS for PostgreSQL
- Context: Attempting to create custom functions that require C language support

**Root Cause**
For security reasons, ApsaraDB RDS restricts direct use of the C language for function creation. Instead, users must rely on pre-approved extensions that encapsulate safe C-based functionality.

**Solution**
1. Install the appropriate extension that provides the needed functionality. For UUID generation:
```sql
CREATE EXTENSION "uuid-ossp";
```
2. Use the extension’s functions instead of writing raw C functions:
```sql
SELECT uuid_generate_v4();
```

**Verification**
- The `CREATE EXTENSION` command succeeds.
- The function call (e.g., `uuid_generate_v4()`) returns expected output without error.

### Problem 5: Definer Does Not Exist in DMS Login

**Symptoms**
- Error message: `The user specified as a definer ('user'@'a.b.c.d') does not exist`
- Behavior: DMS (Data Management Service) fails to log in to RDS database in proxy mode
- Context: Using DMS to manage RDS instances with high-security mode enabled

**Root Cause**
In RDS proxy mode, stored routines (procedures, functions, views) reference a definer user that does not exist in the `mysql.user` table. This often happens when the account was deleted or never created in the RDS console.

**Solution**
1. Log in to the [ApsaraDB RDS console](https://rds.console.aliyun.com).
2. Navigate to **Accounts** in the left-side menu.
3. Create a new account with the exact username and host (`%` or specific IP) that appears in the error message.
4. Assign appropriate permissions (e.g., read/write) based on the application’s needs.

**Verification**
- DMS login succeeds without the definer error.
- The account appears in the RDS console’s account list.

### Problem 6: Temporary Table GTID Restriction in MySQL 5.6

**Symptoms**
- Error message: `ERROR 1784 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in autocommitted statements or single-statement transactions`
- Behavior: `CREATE TEMPORARY TABLE` or `DROP TEMPORARY TABLE` fails
- Context: Using temporary tables in RDS for MySQL 5.6 with GTID enabled

**Root Cause**
MySQL 5.6 with GTID enforces strict transactional consistency. Temporary tables are non-transactional, so their use is restricted unless autocommit is enabled or the operation is wrapped in a single-statement transaction.

**Solution**
Choose one of the following approaches:
1. **Use regular tables instead of temporary tables** (recommended for most cases):
```sql
CREATE TABLE session_temp_data (...);
-- Clean up manually after use
DROP TABLE session_temp_data;
```
2. **Ensure autocommit is enabled** before using temporary tables:
```sql
SET autocommit = 1;
CREATE TEMPORARY TABLE temp_t (...);
-- Operations on temp_t
```

**Verification**
- The `CREATE TEMPORARY TABLE` statement executes without error.
- Subsequent operations on the temporary table succeed.

### Problem 7: ThinkSNS Installation Error

**Symptoms**
- Error message: `OPERATION need to be executed set by ADMIN`
- Behavior: ThinkSNS web installer fails during database setup
- Context: Installing ThinkSNS on RDS for MySQL

**Root Cause**
The ThinkSNS `install.php` script uses an outdated or incorrect method to select the database, which is incompatible with RDS’s security model.

**Solution**
1. Locate the `install.php` file in the ThinkSNS installation directory.
2. Find the database connection section.
3. Replace the problematic database selection line with:
```php
mysql_select_db($db_config['db_name']);
```
Ensure `$db_config['db_name']` contains the correct database name.

**Verification**
- The ThinkSNS installation proceeds past the database setup step.
- The application connects to the RDS instance successfully.

### Problem 8: Extension Creation Blocked on Older PostgreSQL Versions

**Symptoms**
- Error message: `InvalidParameter`
- Behavior: `CREATE EXTENSION` fails for certain extensions (e.g., `postgis`, `pg_cron`)
- Context: Attempting to create extensions on RDS for PostgreSQL with minor version earlier than 20230330

**Root Cause**
Starting April 17, 2023, Alibaba Cloud blocks creation of certain extensions on RDS for PostgreSQL instances running minor engine versions earlier than 20230330 due to security reviews. Existing extensions remain functional.

**Solution**
1. Upgrade the RDS instance’s minor engine version to 20230330 or later via the RDS console:
   - Go to **Instance Details** > **Upgrade Kernel Version**
   - Select the target version (≥20230330)
2. After upgrade completes, retry the extension creation:
```sql
CREATE EXTENSION postgis;
```

**Verification**
- The `CREATE EXTENSION` command succeeds.
- The extension appears in `pg_extension`:
```sql
SELECT extname FROM pg_extension WHERE extname = 'postgis';
```

## FAQ

**Q: How do I check if my RDS instance is reachable from my application server?**
A: Use `telnet` or `nc` to test connectivity to the RDS endpoint on port 3306 (MySQL) or 5432 (PostgreSQL). Also verify DNS resolution with `nslookup <endpoint>`. Ensure security group rules allow inbound traffic from your server’s IP.

**Q: What permissions are required to create databases in RDS for MySQL?**
A: The account must have the global `CREATE` privilege. Standard accounts do not have this by default. A privileged account must grant it using `GRANT CREATE ON *.* TO 'user'@'%';`.

**Q: Why can’t I create foreign keys even when columns seem compatible?**
A: The referenced column must be a primary key (or have a unique constraint) in the parent table. Use `SHOW CREATE TABLE` to verify the parent table’s structure and add a primary key if missing.

**Q: How do I enable debug logging for connection issues?**
A: For MySQL clients, use `mysql --verbose --debug`. For applications, enable driver-level logging (e.g., `PDO::ATTR_ERRMODE` in PHP). Also check RDS error logs via the console under **Logs** > **Error Logs**.

**Q: What should I do if read/write splitting causes inconsistent query results?**
A: Ensure read-after-write operations use the primary instance by adding `/*FORCE_MASTER*/` to the SQL comment. Also verify that replication lag is within acceptable limits (<1 second) in the RDS monitoring dashboard.