The specified Security Group and Parameter Group are not set in the RDS instance

If the Terraform apply execution times out during the RDS instance creation, the specified Security Group and Parameter Group is not set in the RDS instance.

The solution is to set the timeout in the aws_db_instance resource. When a multi-az RDS instance is launched from a snapshot, the process can take more than 55 minutes. The default value is 40 minutes.

resource "aws_db_instance" "default" {
...
  timeouts {
    create = "120m"
    delete = "120m"
  }
...
}

java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x96\xA4\xE2\x9C…’ for column

When your application tries to store 4-byte Unicode characters in a MySQL database you may get the error rmessage

java.sql.SQLException: Incorrect string value: ‘\x..\x..\x..\x..\x..\x…’ for column

 

To store 4-byte Unicode characters in MySQL you need

  • a modern version of the database engine, version 5.5 or later,
  • set the column collation to COLLATE utf8mb4_unicode_ci in the database,
  • configure MySQL to use utf8mb4 in the character columns.

 

To set the default character set and server settings to utf8mb4

  1. Open the C:\ProgramData\MySQL\MySQL Server 5.x\my.ini file
  2. Under [client] add
    default-character-set = utf8mb4
  3. Under [mysql] set the value to utf8mb4 in line
    default-character-set = utf8mb4
  4. Under [mysqld] set the value to utf8mb4 in line
    character-set-server = utf8mb4
  5. Under [mysqld] add the lines
    character-set-client-handshake = FALSE
    character-set-server = utf8mb4
    collation-server = utf8mb4_unicode_ci

Restart the MySQL server

  1. Restart the MySQL database server for the changes to take effect
  2. in MySQL Workbench reconnect the database.

Change the schema of an existing database

  1. In MySQL Workbench connect to the database server,
  2. Right-click the database and select Alter Schema,
  3. Set the collation to utf8mb4_unicode_ci collation
  4. Click the Appy button to save the changes,
  5. Click the Apply button again to confirm the instructions,
  6. Click the Finish button to execute the script.

To check the database settings execute the SQL query in MySQL Workbench

use MY_DATABASE_NAME;
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

It should show

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

Repair and optimize all tables

Make sure you execute the mysqlcheck command to repair and optimize all tables to avoid silent update errors

On Windows

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqlcheck" -u root -p --auto-repair --optimize --all-databases

The utility makes it easy to execute the repair and optimize commands for every table

# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;

MySQL Installation

To install the MySQL database server, download the installer from https://dev.mysql.com/downloads/

  1. Download the MySQL Community Server from https://dev.mysql.com/downloads/
  2. On the Generally Available (GA) Releases tab select the operating system and click the Go to Download Page button,
  3. Click the Download button next to the web or offline installer,
  4. If you don’t want to register at Oracle, click the No thanks, just start my download link,

On Windows

Install the Visual C++ Redistributable Packages for Visual Studio 2013

  1. In your web browser navigate to https://www.microsoft.com/en-us/download/details.aspx?id=40784
  2. Download both 32 and 64-bit packages
  3. Install both packages. The 64-bit package requires a reboot after installation.

Install MySQL

  1. Execute the downloaded MySQL installer.

How to restore a Microsoft SQL database from backup with the Microsoft SQL Server Management Studio (MSSMS) user interface

There are multiple reasons to restore a database from backup. One of them can be disaster recovery, the other is to bring the production database to the developer machine. In both cases, the computer already has the old version of the database. In the new version of Microsoft SQL Server Management Studio (MSSMS) we cannot find the “Close connections” checkbox anymore, so we have to make sure all connections are closed, and we specify a unique database file name to restore the database to.

  1. On the developer machine close all instances of Visual Studio to close the open database connections,
  2. Close all Microsoft SQL Server Management Studio tabs that are connected to the database,
  3. Move the backup file to the C:\Temp folder. Microsoft SQL Server Management Studio cannot see it in your Downloads folder.
  4. Make sure your user account does not automatically connect to the database you want to restore:
    1. In Security, Logins right click your username and select Properties,
    2. Set the Default database to master
  5.  Disconnect from the database server:
    1.  Right-click the database server and select Disconnect,

  6. Connect to the database engine, but do not open the database you want to restore:
    1. Execute,
      USE Master
  7. Import the database from production with the user interface:
    1. Right-click the database and select Tasks, Restore, Files and Filegroups
    2. Select the From device radio button
    3. Click the button to select the backup file
    4. In the Select backup devices window click the Add button to select the file
    5. Navigate to the C:\Temp folder and select the database backup file
    6. Click the OK button
    7. In the Select the backup sets section select the Restore check box in the file row, and click Options on the left side,
    8. On the Options tab select the Overwrite existing database (WITH REPLACE) check box, and click the button to set the new unique database file name,
    9. Click OK to ignore the error message on the Locate Database Files – … message box
    10. Navigate to the current database file, click it, and append the date of the backup to the end of the file name to specify a unique data file name, and click OK,
    11. On the Restore Files and FileGroups window click the OK button to restore the database

Update the database to make it work at the new location

  1. On a development machine set the databases to Simple recovery mode, so the log files do not grow out of bound.
    ALTER DATABASE my_database SET RECOVERY SIMPLE;
  2. Delete and recreate the users in the restored database, because those have different internal IDs if the database was migrated from a different server
    USE my_database;
    GO
    spDropUser 'my_user', 'dbo'
    GO
    DROP SCHEMA my_user
    GO

    For some reason If I type DECLARE below, WordPress crashes. Please add the letter E to the end of the first word below.

    DECLAR @user_name varchar(50)
    SET @user_name = 'my_user'
    USE my_database; EXEC sp_grantdbaccess @user_name; EXEC sp_addrolemember @rolename = 'db_datareader', @membername = @user_name; EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = @user_name; 
    GO

Migrate databases to and from AWS RDS instances

Amazon Web Services do not provide tools to migrate data to, from and between AWS RDS instances.

There is a free utility that can do the job.

SQL Server Management Studio (SSMS)

Install Microsoft SQL Server Management Studio (SSMS), because the utility uses libraries from it.

  1. Download the installer for free from https://msdn.microsoft.com/en-us/library/mt238290.aspx

SQL Azure Migration Wizard

Installation

  1. Download the SQL Azure Migration Wizard from http://sqlazuremw.codeplex.com/
  2. Unzip the downloaded ZIP file into a folder on your hard drive
  3. Create a shortcut in your Start Menu for SQLAzureMW.exe

Migrate a database

  1. Start the SQL Azure Migration Wizard by executing the SQLAzureMW.exe file
  2. Select Database in the Analyze / Migrate section and click Next

Connect to the source database

  1. On the Connect to Server page
    1. Enter the address of the database server
    2. Select SQL Server Authentication
    3. Enter the database owner login name and password
    4. If you keep Master DB selected, the next screen will show the list of databases to select from
    5. To save the username and password select Save Login Information
    6. Click Connect

  2. Select the database to connect to and click Next
  3. Leave Script all database objects selected and click Next
  4. On the Script Wizard Summary page click Next
  5. Click Yes to start the read process
  6. On the Results Summary page select Auto Scroll Display to see the scrolling results
  7. When the read is done click Next

Connect to the target database

  1. Enter the connection properties of the target database server
  2. Select the target database
    1. To migrate the data into a new database click the Create Database button
    2. On mirrored RDS instances we cannot drop and recreate databases. To migrate data into an existing database
      1. Use SQL Management Studio to drop the existing tables to avoid key conflicts
      2. Select the target database on this page
  3. Click Next
  4. Click Yes to start the write process
  5. When the write process is done click Exit

 

Failed to open file ‘…’ , error 2 and error 22

How to restore a MySQL database in Windows

To restore a MySQL database on a Windows machine:

  • Place the database backup file in the root directory of your C: drive (trust me, this will make your life easier)
  • Open the Command Prompt
  • Start the MySQL command prompt
  • mysql -u root -p

    This command will prompt you for the MySQL root password

  • Switch to the new database
    use db;
  • Import the database
    source C:/BACKUP_FILE_NAME.sql

    To avoid the Failed to open file ‘…’ , error 2 and error 22 error message
    You can have spaces in the name of the backup file, but not in the directory names in the path
    Do not use quotes around the file path
    Use forward slashes
    Do not end the command with semicolon (;)

Disable database triggers in Microsoft SQL databases

If you need to temporarily disable triggers in Microsoft SQL databases during database maintenance use the following script
--Disable triggers on all tables
DECLARE @enable BIT = 0;
DECLARE @trigger SYSNAME;
DECLARE @table SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
DECLARE trigger_cursor CURSOR FOR SELECT trigger_object.name trigger_name,
table_object.name table_name
FROM sysobjects trigger_object
JOIN sysobjects table_object ON trigger_object.parent_obj = table_object.id
WHERE trigger_object.type = 'TR';
OPEN trigger_cursor;
FETCH NEXT FROM trigger_cursor INTO @trigger, @table;
WHILE @@FETCH_STATUS = 0 BEGIN
IF @enable = 1
SET @cmd = 'ENABLE ';
ELSE
SET @cmd = 'DISABLE ';
SET @cmd = @cmd + ' TRIGGER dbo.'+QUOTENAME(@trigger)+' ON dbo.'+QUOTENAME(@table)+' ';
EXEC (@cmd);
FETCH NEXT FROM trigger_cursor INTO @trigger, @table;
END
CLOSE trigger_cursor;
DEALLOCATE trigger_cursor;
GO

Disable foreign key constraints in Microsoft SQL databases

Databases do not allow the deletion of rows if those are referenced in other tables with the foreign key constraint. You can turn off the validation of foreign keys in Microsoft SQL databases for the duration of the maintenance with the following script.


--Disable foreign keys on all tables
DECLARE @table_name SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
DECLARE table_cursor CURSOR FOR SELECT name FROM sys.tables;
OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @table_name;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @cmd = 'ALTER TABLE '+QUOTENAME(@table_name)+' NOCHECK CONSTRAINT ALL';
EXEC (@cmd);
FETCH NEXT FROM table_cursor INTO @table_name;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;
GO

No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SqlClient’

When one of the projects in your solution is referencing the Entity Framework to access a database the Entity Framework NuGet package is added to that project. In the Visual Studio IDE your application may work fine, but when you deploy it to the test or production server the following error message may appear:

Exception Type: System.Data.Entity.Core.MetadataException
Exception: Schema specified is not valid.
Errors: [YOUR_DATABASE].ssdl(2,2) : error 0152: No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SqlClient’. Make sure the provider is registered in the ‘entityFramework’ section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

To resolve the problem add the Entity Framework NuGet package to the main project of your application:

  • In the Solution Explorer right click the main project,
  • Select Manage NuGet Packages… in the context menu,
  • On the left side of the NuGet manager select Online, Microsoft and .NET,
  • On the right side enter EntityFramework into the search field and hit Enter,
  • In the middle click Install in the EntityFramework box.

How to add a new table to an existing Microsoft SQL Server replication publication

To add a new table to an existing MS SQL Server publication

  • Remote desktop into the database server, or a server in the same domain of the database server
  • Start Microsoft SQL Server Management Studio (SSMS)
  • Connect to the database with ComputerName\SQLServerName
    If there is only one instance of Microsoft SQL Server on the computer the two names are usually the same, but still both are needed, otherwise you get the following error message:SQL Server is unable to connect to server…
    Additional information:
    SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name not supported. Specify the actual server name, …\…  (Replication.Utilities)
  • Right click the Local Publication
  • Select the Articles page
  • Uncheck the “Show only checked articles in the list” check box to see the not replicated tables
  • Select the tables you want to add to the replication
  • Click OK