AWS SCT Action Code Index

The following table shows the icons we use to describe the automation levels of AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS).

Automation level icon	Description
	Full automation — AWS SCT performs fully automatic conversion, no manual conversion needed.
	High automation — Minor, simple manual conversions may be needed.
	Medium automation — Low-medium complexity manual conversions may be needed.
	Low automation — Medium-high complexity manual conversions may be needed.
	Very low automation — High risk or complex manual conversions may be needed.
	No automation — Not currently supported by AWS SCT, manual conversion is required for this feature.

The following sections list the AWS Schema Conversion Tool action codes for topics that are covered in this playbook.

Note

The links in the table point to the Microsoft SQL Server topic pages, which are immediately followed by the MySQL pages for the same topics.

Creating Tables

AWS SCT automatically converts the most commonly used constructs of the CREATE TABLE statement as both SQL Server and Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) support the entry level American National Standards Institute (ANSI) compliance. These items include table names, containing security schema or database, column names, basic column data types, column and table constraints, column default values, primary, UNIQUE, and foreign keys. Some changes may be required for computed columns and global temporary tables.

For more information, see Creating Tables.

Action code	Action message
659	If you use recursion, make sure that table variables in your source database and temporary tables in your target database have the same scope.
679	AWS SCT replaced computed columns with triggers.
680	MySQL doesn’t support global temporary tables.

Constraints

AWS Schema Conversion Tool (AWS SCT automatically converts most constraints because SQL Server and Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) support the entry level ANSI compliance. These items include primary keys, foreign keys, null constraints, unique constraints, and default constraints with some exceptions. Manual conversions are required for some foreign key cascading options. AWS SCT replaces check constraints with triggers, and some default expressions for DateTime columns aren’t supported for automatic conversion. AWS SCT can’t automatically convert complex expressions for other default values.

For more information, see Constraints.

Action code	Action message
676	MySQL doesn’t support the SET DEFAULT referential constraint action.
677	MySQL doesn’t support functions or expressions as a default value for BLOB and TEXT columns.
678	MySQL doesn’t support check constraints.
825	AWS SCT removed the default value of the ё column.
826	AWS SCT can’t convert the default value of the ё variable.
827	AWS SCT can’t convert default values.

Data Types

Data type syntax and rules are very similar between SQL Server and Aurora MySQL. AWS SCT automatically converts most of data type syntax and rules. Note that date and time handling paradigms are different for SQL Server and Aurora MySQL and require manual verification or conversion. Also note that because of differences in data type behavior between SQL Server and Aurora MySQL, manual verification and strict testing are highly recommended.

For more information, see Data Types.

Action code	Action message
601	MySQL doesn’t support including BLOB and TEXT columns in foreign keys.
706	AWS SCT replaced the unsupported %s data type.
707	AWS SCT can’t convert the usage of a variable of the unsupported %s data type.
708	AWS SCT can’t convert the usage of the unsupported %s data type.
775	Converted code might lose accuracy compared to the source code.
844	AWS SCT expanded fractional seconds support for TIME, DATETIME2, and DATETIMEOFFSET values with up to 6 digits of precision.
919	MySQL doesn’t support the DECIMAL data type with scale greater than 30.

Collations

The collation paradigms of SQL Server and Aurora MySQL are significantly different. AWS SCT can successfully migrate most common use cases including data type differences such as NCHAR and NVARCHAR in SQL Server that don’t exist in Aurora MySQL. Aurora MySQL provides more options and flexibility in terms of collations. Rewrites are required for explicit collation clauses that aren’t supported by Aurora MySQL.

For more information, see Collations.

Action code	Action message
646	MySQL doesn’t support the COLLATE clause.

Window Functions

Aurora MySQL version 5.7 doesn’t support window functions. AWS SCT can’t automatically convert window functions.

For workarounds using traditional SQL syntax, see Window Functions.

Action code	Action message
647	MySQL doesn’t support the analytic form of the %s function.
648	MySQL doesn’t support the RANK function.
649	MySQL doesn’t support the DENSE_RANK function.
650	MySQL doesn’t support the NTILE function.
754	MySQL doesn’t ssupport STDEV functions with the DISTINCT clause.
755	MySQL doesn’t support STDEVP functions with the DISTINCT clause.
756	MySQL doesn’t support VAR functions with the DISTINCT clause.
757	MySQL doesn’t support VARP functions with the DISTINCT clause.

PIVOT and UNPIVOT

Aurora MySQL version 5.7 doesn’t support the PIVOT and UNPIVOT syntax. AWS SCT can’t automatically convert the PIVOT and UNPIVOT clauses.

For workarounds using traditional SQL syntax, see PIVOT and UNPIVOT.

Action code	Action message
905	MySQL doesn’t support PIVOT clauses for SELECT statements.
906	MySQL doesn’t support UNPIVOT clauses for SELECT statements.

TOP and FETCH

Aurora MySQL supports the non-ANSI compliant (although popular with other common RDBMS engines) LIMIT…​ OFFSET operator for paging of results sets. Despite the differences, AWS SCT can automatically convert most common paging queries to use the Aurora MySQL syntax. Some options such as PERCENT and WITH TIES can’t be automatically converted and require manual conversion.

For more information, see SQL Server TOP and FETCH and MySQL LIMIT.

Action code	Action message
604	MySQL doesn’t support the PERCENT argument in TOP clauses. AWS SCT skips this argument in the converted code.
605	MySQL doesn’t support the WITH TIES argument in TOP clauses. AWS SCT skips this argument in the converted code.
608	MySQL doesn’t support the PERCENT argument in TOP clauses of INSERT statements.
612	MySQL doesn’t support the PERCENT argument in TOP clauses of UPDATE statements.
621	MySQL doesn’t support the PERCENT argument in TOP clauses. AWS SCT skips this argument in the converted code.
830	MySQL doesn’t support LIMIT clauses with IN, ALL, ANY, or SOME subqueries.

Common Table Expressions

Aurora MySQL version 5.7 doesn’t support common table expressions. AWS SCT can’t automatically convert common table expressions.

For workarounds using traditional SQL syntax, see Common Table Expressions.

Action code	Action message
611	MySQL doesn’t support WITH queries in UPDATE statements.
619	MySQL doesn’t support query definitions for common table expressions.
839	MySQL doesn’t support query definitions for common table expressions.
840	AWS SCT can’t convert updated common table expressions.

Cursors

AWS SCT automatically converts the most commonly used cursor operations. These operations include forward-only, read only cursors, and the DECLARE CURSOR, CLOSE CURSOR, and FETCH NEXT operations. Modifications through cursors and non-forward-only fetches, which aren’t supported by Aurora MySQL, require manual conversions.

For more information, see Cursors.

Action code	Action message
618	MySQL doesn’t support CURRENT OF clauses for DML queries that are in the body of a cursor loop.
624	MySQL doesn’t support CURRENT OF clauses for DML queries that are in the body of a cursor loop.
625	MySQL doesn’t support the CURSOR data type as a procedure argument.
637	MySQL doesn’t support global cursors.
638	MySQL doesn’t support the SCROLL option in cursors.
639	MySQL doesn’t support dynamic cursors.
667	MySQL doesn’t support the %s option in cursors.
668	MySQL doesn’t support the FIRST option in cursors.
669	MySQL doesn’t support the PRIOR option in cursors.
670	MySQL doesn’t support the ABSOLUTE option in cursors.
671	MySQL doesn’t support the RELATIVE option in cursors.
692	MySQL doesn’t support cursor variables.
700	AWS SCT can’t convert the KEYSET option because MySQL doesn’t support changing the membership and order of rows for cursors.
701	AWS SCT doesn’t convert the FAST_FORWARD option because this is a default option for cursors in MySQL.
702	AWS SCT doesn’t convert the READ_ONLY option because this is a default option for cursors in MySQL.
703	MySQL doesn’t support the SCROLL_LOCKS option.
704	MySQL doesn’t support the OPTIMISTIC option for cursors.
705	MySQL doesn’t support the TYPE_WARNING option for cursors.
842	MySQL doesn’t support the %s option in cursors.

Flow Control

Although the flow control syntax of SQL Server differs from Aurora MySQL, AWS SCT can convert most constructs automatically including loops, command blocks, and delays. Aurora MySQL doesn’t support the GOTO command nor the WAITFOR TIME command, which require manual conversion.

For more information, see Flow Control.

Action code	Action message
628	MySQL doesn’t support GOTO statements.
691	MySQL doesn’t support the WAITFOR TIME feature.

Transaction Isolation

Aurora MySQL supports the following four transaction isolation levels specified in the SQL:92 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. AWS SCT automatically converts all these transaction isolation levels. AWS SCT also converts BEGIN, COMMIT, and ROLLBACK commands that use slightly different syntax. Manual conversion is required for named, marked, and delayed durability transactions that aren’t supported by Aurora MySQL.

For more information, see Transactions.

Action code	Action message
629	MySQL doesn’t support named transactions.
630	MySQL doesn’t support WITH MARK options.
631	MySQL doesn’t support distributed transactions.
632	MySQL doesn’t support rolling back named transactions
633	MySQL doesn’t support the DELAYED_DURABILITY option.
916	MySQL doesn’t support the SNAPSHOT transaction isolation level.

Stored Procedures

Aurora MySQL stored procedures provide very similar functionality to SQL Server stored procedures. AWS SCT automatically converts SQL Server stored procedures. Manual conversion is required for procedures that use RETURN values and some less common EXECUTE options such as RECOMPILE and RESULTS SETS.

For more information, see Stored Procedures.

Action code	Action message
640	MySQL doesn’t support EXECUTE statements with the WITH RECOMPILE option.
641	MySQL doesn’t support EXECUTE statements with the RESULT SETS UNDEFINED option.
642	MySQL doesn’t support EXECUTE statements with the RESULT SETS NONE option.
643	MySQL doesn’t support EXECUTE statements with the RESULT SETS DEFINITION option.
689	MySQL doesn’t support RETURN statements that are used to return values from a procedure.
695	MySQL doesn’t support the call of a procedure as a variable.

Triggers

Aurora MySQL supports BEFORE and AFTER triggers for INSERT, UPDATE, and DELETE. However, Aurora MySQL triggers differ substantially from SQL Server triggers, but most common use cases can be migrated with minimal code changes. Although AWS SCT can automatically migrate trigger code, manual inspection and potential code modifications may be required because Aurora MySQL triggers are ran once for each row, not once for each statement such as triggers in SQL Server.

For more information, see Triggers.

Action code	Action message
686	MySQL doesn’t support triggers with the FOR STATEMENT clause.

GROUP BY

AWS SCT automatically converts the GROUP BY queries, except for CUBE and GROUPING SETS. You can create workarounds for these queries, but they require manual code changes.

For more information, see GROUP BY.

Action code	Action message
654	MySQL doesn’t support the GROUP BY CUBE option.
655	MySQL doesn’t support GROUP BY GROUPING SETS clauses.

Identity and Sequences

Although the syntax for SQL Server IDENTITY and Aurora MySQL AUTO_INCREMENT auto-enumeration columns differs significantly, it can be automatically converted by AWS SCT. Some limitations imposed by Aurora MySQL require manual conversion such as explicit SEED and INCREMENT auto-enumeration columns that aren’t part of the primary key and the table-independent SEQUENCE objects.

For more information, see Identity and Sequences.

Action code	Action message
696	MySQL doesn’t support identity columns with seed and increment.
697	MySQL doesn’t support identity columns outside the primary key.
732	MySQL doesn’t support identity columns in compound primary keys.
815	MySQL doesn’t support sequences.
841	MySQL doesn’t support numeric (x, 0) or decimal (x, 0) data types in columns with the AUTO_INCREMENT option. AWS SCT replaced this data type with a compatible data type.
920	MySQL doesn’t support identity columns of the DECIMAL or NUMERIC data type with precision greater than 19.

Error Handling

The error handling paradigms in Aurora MySQL and SQL Server are significantly different; the former uses condition and handler objects. AWS SCT migrates the basic error handling constructs automatically. Due to the paradigm differences, we highly recommend that you perform strict inspection and validation of the migrated code. Manual conversions are required for THROW with variables and for built-in messages in SQL Server.

For more information, see Error Handling.

Action code	Action message
729	AWS SCT can’t convert THROW operators with variables.
730	AWS SCT truncated the error code.
733	MySQL doesn’t support PRINT procedures.
814	AWS SCT can’t convert the RAISERROR operator with messages from the sys.messages view.
837	MySQL uses a different approach to handle errors compared to the source code.

Date and Time Functions

AWS SCT automatically converts the most commonly used date and time functions despite the significant difference in syntax. Be aware of differences in data types, time zone awareness, and locale handling as well the functions themselves, and inspect the expression value output carefully. Some less commonly used options such as millisecond, nanosecond, and time zone offsets require manual conversion.

For more information, see Date and Time Functions.

Action code	Action message
759	MySQL doesn’t support DATEADD functions with the nanosecond date part.
760	MySQL doesn’t support DATEDIFF functions with the week date part.
761	MySQL doesn’t support DATEDIFF functions with the millisecond date part.
762	MySQL doesn’t support DATEDIFF functions with the nanosecond date part.
763	MySQL doesn’t support DATENAME functions with the millisecond date part.
764	MySQL doesn’t support DATENAME functions with the nanosecond date part.
765	MySQL doesn’t support DATENAME functions with the TZoffset date part.
767	MySQL doesn’t support DATEPART functions with the nanosecond date part.
768	MySQL doesn’t support DATEPART functions with the TZoffset date part.
773	AWS SCT can’t convert arithmetic operations with dates.

User-Defined Functions

Aurora MySQL supports only scalar user-defined functions, which are automatically converted by AWS SCT. Table-valued user-defined functions, both in-line and multi-statement, require manual conversion. Workarounds using views or derived tables should be straightforward in most cases.

For more information, see User-Defined Functions.

Action code	Action message
777	AWS SCT can’t emulate a table-valued function because the column from the current query is used as a function parameter.
822	MySQL doesn’t support table-valued functions in views.

User-Defined Types

Aurora MySQL 5.7 doesn’t support-user defined types and user-defined table-valued parameters. AWS SCT can convert standard user defined types by replacing it with their base types, but manual conversion is required for user defined table types, which are used for table valued parameters for stored procedures.

For more information, see User-Defined Types.

Action code	Action message
690	MySQL doesn’t support table types.

Synonyms

Aurora MySQL version 5.7 doesn’t support synonyms. AWS SCT can’t automatically convert synonyms.

For more information, see Synonyms.

Action code	Action message
792	MySQL doesn’t support synonyms.

XML and JSON

Aurora MySQL provides minimal support for XML, but it does offer a native JSON data type and more than 25 dedicated JSON functions. Despite these differences, the most commonly used basic XML functions can be automatically migrated by AWS SCT. Some options such as EXPLICIT, used in functions or with subqueries, require manual conversion.

For more information, see JSON and XML.

Action code	Action message
817	AWS SCT can’t convert FOR XML clauses with EXPLICIT mode specified.
818	AWS SCT can’t convert correlated subqueries with FOR XML clauses.
843	AWS SCT can’t convert FOR XML statements in functions.

Table Joins

AWS SCT automatically converts the most commonly used join types. These types include INNER, OUTER, and CROSS joins. APPLY joins, also known as LATERAL joins, aren’t supported by Aurora MySQL and require manual conversion.

For more information, see Table JOIN.

Action code	Action message
831	MySQL doesn’t support the CROSS APPLY and OUTER APPLY operators where the subquery references to the column of attachable table.

MERGE

Aurora MySQL version 5.7 doesn’t support the MERGE statement. AWS SCT can’t automatically convert MERGE statements. Manual conversion is straightforward in most cases.

For more information and potential workarounds, see MERGE.

Action code	Action message
832	MySQL doesn’t support MERGE statements.

Query Hints

Basic query hints such as index hints can be converted automatically by AWS SCT, except for DML statements. Note that specific optimizations used for SQL Server may be completely inapplicable to a new query optimizer. We recommend that you remove all hints before the start of migration testin. Then, selectively apply hints as a last resort if other means such as schema, index, and query optimizations have failed. Plan guides aren’t supported by Aurora MySQL.

For more information, see Query Hints and Plan Guides.

Action code	Action message
610	MySQL doesn’t support hints in INSERT statements. AWS SCT skips WITH(Table_Hint_Limited) options in the converted code.
617	MySQL doesn’t support hints in UPDATE statements. AWS SCT skips WITH(Table_Hint_Limited) options in the converted code.
623	MySQL doesn’t support hints in DELETE statements. AWS SCT skips WITH(Table_Hint_Limited) options in the converted code.
823	MySQL doesn’t support table hints in DML statements.

Full-Text Search

Migrating full-text indexes from SQL Server to Aurora MySQL requires a full rewrite of the code that deals with both creating, managing, and querying of full-text indexes. AWS SCT can’t automatically convert full-text indexes.

For more information, see Full-Text Search.

Action code	Action message
687	MySQL doesn’t support the CONTAINS predicate.
688	MySQL doesn’t support the FREETEXT predicate.

Indexes

AWS SCT automatically converts basic non-clustered indexes, which are the most commonly used type of indexes. User-defined clustered indexes aren’t supported by Aurora MySQL because they are always created for the primary key. In addition, filtered indexes, indexes with included columns, and some SQL Server specific index options can’t be migrated automatically and require manual conversion.

For more information, see Indexes.

Action code	Action message
602	MySQL has reached the limit of the internal InnoDB maximum key length.
681	MySQL doesn’t support clustered indexes.
682	MySQL doesn’t support the INCLUDE clause in indexes.
683	MySQL doesn’t support the WHERE clause in indexes.
684	MySQL doesn’t support the WITH clause in indexes.

Partitioning

Because Aurora MySQL stores each table in its own file, and because file management is performed by AWS and can’t be modified, some of the physical aspects of partitioning in SQL Server don’t apply to Aurora MySQL. For example, the concept of file groups and assigning partitions to file groups. Aurora MySQL supports a much richer framework for table partitioning than SQL Server, with many additional options such as hash partitioning, and sub partitioning. Due to the vast differences between partition creation, query, and management between Aurora MySQL and SQL Server, AWS SCT doesn’t automatically convert table and index partitions. These items require manual conversion.

For more information, see Storage.

Action code	Action message
907	AWS SCT can’t convert tables arranged in several partitions.

Backup

Migrating from a self-managed backup policy to a Platform as a Service (PaaS) environment such as Aurora MySQL is a complete paradigm shift. You no longer need to worry about transaction logs, file groups, disks running out of space, and purging old backups. Amazon Relational Database Service (Amazon RDS) provides guaranteed continuous backup with point-in-time restore up to 35 days. Therefore, AWS SCT doesn’t automatically convert backups.

For more information, see Backup and Restore.

Action code	Action message
903	MySQL doesn’t support functionality similar to SQL Server Backup.

SQL Server Database Mail

Aurora MySQL doesn’t provide native support for sending mail from the database.

For more information and potential workarounds, see Database Mail.

Action code	Action message
900	MySQL doesn’t support functionality similar to SQL Server Database Mail.

SQL Server Agent

Aurora MySQL doesn’t provide functionality similar to SQL Server Agent as an external, cross-instance scheduler. However, Aurora MySQL provides a native, in-database scheduler. It is limited to the cluster scope and can’t be used to manage multiple clusters. Therefore, AWS SCT can’t automatically convert Agent jobs and alerts.

For more information, see SQL Server Agent and MySQL Agent.

Action code	Action message
902	MySQL doesn’t support functionality similar to SQL Server Agent.

Linked Servers

Aurora MySQL doesn’t support remote data access from the database. Connectivity between schemas is trivial, but connectivity to other instances require a custom solution. AWS SCT can’t automatically convert commands on linked servers.

For more information, see Linked Servers.

Action code	Action message
645	MySQL doesn’t support running pass-through commands on linked servers.

Views

MySQL views are similar to views in SQL Server. However, there are slight differences between the two, mostly around indexing and triggers on views, and also in the query definition.

For more information, see Views.

Action code	Action message
779	AWS SCT can’t convert SELECT statements that contain a subquery in the FROM clause.