Why Your INSERT Fails After Migrating MySQL to GoldenDB – The ANSI_QUOTES Pitfall

Accident Scene

We first create a table test_1 with columns id, column1, and date_time, then insert a row, and create a duplicate table test_2 using CREATE TABLE test_2 LIKE test_1. The following INSERT‑SELECT statement copies data from test_1 to test_2:

INSERT INTO test_2(column1,date_time) SELECT "column1", now() FROM test_1;

The statement works on MySQL 8.0 but fails on GoldenDB because the double‑quoted identifier is treated as a string, causing the column to exceed its length.

Root Cause

The difference is the ANSI_QUOTES SQL mode. MySQL with ANSI_QUOTES disabled treats double quotes as string delimiters; with it enabled they act like backticks for identifiers. GoldenDB has ANSI_QUOTES disabled by default, so the double‑quoted column1 is interpreted as the literal string “column1”, leading to a “Data too long for column 'column1'” error.

How to Enable ANSI_QUOTES

You can enable it for the current session or globally:

-- Session only
SET SESSION sql_mode = 'ANSI_QUOTES';
-- Global (effective for new connections, lost after restart)
SET GLOBAL sql_mode = 'ANSI_QUOTES';

Or modify the server configuration file to make it permanent.

ANSI_QUOTES Overview

Enable

When enabled, double quotes are interpreted as identifier delimiters, equivalent to backticks. Example: SELECT "name" FROM "my_table"; Here “name” and “my_table” are treated as column and table names.

Disable

When disabled (default), double quotes behave like single quotes, representing string literals. Both of the following queries return the literal string “name”:

SELECT 'name' FROM my_table;
SELECT "name" FROM my_table;

Why Use ANSI_QUOTES?

1. Compatibility and portability – using double quotes for identifiers makes SQL portable across databases that follow the ANSI standard (PostgreSQL, Oracle, GoldenDB). 2. Enforcing SQL standards – clearly separates identifiers from string literals, reducing ambiguity.

Conclusion

ANSI_QUOTES is a MySQL SQL mode that changes how identifiers and string literals are quoted to comply with the ANSI SQL standard. Enabling it helps with SQL standardization and database migration.