GaussDB (MySQL-Compatible Mode)

Setting Compatibility Modes

1. The MySQL-compatible mode includes GaussDB Centralized (B-compatible), GaussDB Centralized (M-compatible), and GaussDB Distributed (MySQL-compatible).
2. If the target database type is GaussDB and the source database type is MySQL or GoldenDB, schemas are migrated to GaussDB (MySQL-compatible) by default. Therefore, you are advised to create a MySQL-compatible GaussDB database.
3. Create a MySQL-compatible GaussDB database.

1. Log in to your GaussDB instance as a user who has the permission to create databases.
2. Create a MySQL-compatible GaussDB database.

   GaussDB Centralized (B-compatible mode):

   create database databasename dbcompatibility = 'B';

   GaussDB Centralized (M-compatible mode):

   create database databasename dbcompatibility = 'M';

   Distributed:

   create database databasename dbcompatibility = 'MYSQL';

3. Check whether the database is created.

   select * from pg_database where datname = 'databasename';

   • GaussDB Centralized (B-compatible mode): If the value of datcompatibility is B, the database is created.
   • GaussDB Centralized (M-compatible mode): If the value of datcompatibility is M, the database is created.
   • Distributed: If the value of datcompatibility is MySQL, the database is created.

Setting GUC Parameters

For GaussDB databases compatible with MySQL syntax, you can set the GUC parameters to enhance the compatibility. For details, see Table 1.

GaussDB provides many parameters, which determine how the database system works. Before modifying these parameters, you need to understand the impact on databases. Otherwise, unexpected results may occur.

Set the b_format_dev_version parameter as follows:

• For GaussDB V2.0-8.0 Enterprise Edition, set the value to s1.
• For GaussDB V2.0-8.100 Enterprise Edition, set the value to s2.

• The S1 compatibility configuration item affects the following content:
  • NEW() function, last_day() function, date_sub(date, INTERVAL expr unit), datediff(expr1, expr2), day()/dayofmonth(), and dayname()
  • dayofweek(), dayofyear(), extract(unit FROM date), from_days(days), from_unixtime(unix_timestamp[,format]), get_format({DATE | TIME | DATETIME | TIMESTAMP}, {'EUR' | 'USA' | 'JIS' | 'ISO' | 'INTERNAL'}), hour(), makedate(year,dayofyear), microsecond(), minute(), month(), monthname(), period_add(period, month_number)
  • TIMESTAMPDIFF, yearweek(date[, mode]), year(), weekofyear(date), weekday(), week(date[, mode]), utc_timestamp(), utc_time(), period_diff(p1,p2), second().
  • quarter(), str_to_date(str, format), subdate(expr, days), time_format(time, format), ifnull(expr1, expr2), database(), current_date, current_time, current_timestamp
  • round(arg1, arg2), localtime([precision]), localtimestamp, dbtimezone, timenow(), numtodsinterval(num, interval_unit), numtoyminterval(num, interval_unit)
  • new_time(date, timezone1,timezone2), sysdate([precision]), add_months(d,n), months_between(d1, d2), convert_tz(dt, from_tz, to_tz)
  • adddate(date, INTERVAL expr unit), date_format(date, format)

• The S2 compatibility configuration item affects:
  • Compatibility behaviors controlled by s1.
  • The following functions:
    • When data is imported to the AUTO_INCREMENT column or the BATCH INSERT execution plan is inserted, if 0, NULL, and determined values are used together, the auto-increment count is updated immediately when the determined value is inserted, the subsequent 0 or NULL increases automatically based on the determined value.
    • The like operator does not report an error when an escape character is at the end of the matching string.
    • The priorities of the sorting rules for character sets and character orders are changed.
  • The following syntax:
    • The CREATE TABLE table_name LIKE source_table syntax is supported.
    • The INCLUDING and EXCLUDING options cannot be specified for the CREATE TABLE table_name LIKE source_table and CREATE TABLE table_name (LIKE source_table) syntax. By default, INCLUDING ALL is specified.
    • The LOAD DATA syntax is supported. Some syntax functions in gs_loader that are consistent with the LOAD DATA syntax will change.
    • The collate clause can be specified by set names.
    • The syntax for changing table names, such as ALTER TABLE and RENAME TABLE, is involved. For example, if the character string corresponding to the new table name starts with #MySQL50# and is followed by other characters, #MySQL50# will be ignored.

Procedure

Centralized

1. Connect to the GaussDB database and switch to the GaussDB installation user.

   su - omm

   

   In the preceding command, omm is an example. Change it to the actual installation user.

2. Run the following commands to configure the parameters:

   gs_guc reload -Z coordinator -Z datanode -N all -I all -c "b_format_behavior_compat_options='enable_set_variables'";

   gs_guc reload -Z coordinator -Z datanode -N all -I all -c "b_format_version='5.7'";

   gs_guc reload -Z coordinator -Z datanode -N all -I all -c "b_format_dev_version='s1'";

Distributed

1. Connect to the GaussDB database and switch to the GaussDB installation user.

   su - omm

   

   In the preceding command, omm is an example. Change it to the actual installation user.

2. Run the following commands to configure the parameters:

   gs_guc reload -Z coordinator -Z datanode -N all -I all -c "b_format_version='5.7'";

   gs_guc reload -Z coordinator -Z datanode -N all -I all -c "b_format_dev_version='s1'";