test: add data types testing for mysql sink

[neverchanje]

We expect the mysql sink is ultimately able to pass this test. We'll need to add this test to https://github.com/risingwavelabs/risingwave/tree/main/integration_tests/mysql-sink

Given that mysql's type support is slightly different from postgres's, here we remove the unsupported types. For instance, INTERVALs are removed, as there is no equivalent in mysql. However, we still need to test the behavior of sinking data types that mysql doesn't support, including:

• INTERVAL, in mysql we can use STRING.
• TIMESTAMPTZ, mysql only supports TIMESTAMP
• ARRAY, mysql tends to use JSON to represent a list

CREATE TABLE data_types (
    id BIGINT PRIMARY KEY,
    varchar_column VARCHAR(255),
    text_column TEXT,
    integer_column INTEGER,
    smallint_column SMALLINT,
    bigint_column BIGINT,
    decimal_column DECIMAL(10,2),
    real_column REAL,
    double_column DOUBLE PRECISION,
    boolean_column BOOLEAN,
    date_column DATE,
    time_column TIME,
    timestamp_column TIMESTAMP,
    jsonb_column JSONB,
    bytea_column BYTEA
);

INSERT INTO data_types (id, varchar_column, text_column, integer_column, smallint_column, bigint_column, decimal_column, real_column, double_column, boolean_column, date_column, time_column, timestamp_column, jsonb_column, bytea_column)
VALUES
    (1, 'Varchar value 1', 'Text value 1', 123, 456, 789, 12.34, 56.78, 90.12, TRUE, '2023-05-22', '12:34:56', '2023-05-22 12:34:56', '{"key": "value"}', E'\\xDEADBEEF'),
    (2, 'Varchar value 2', 'Text value 2', 234, 567, 890, 23.45, 67.89, 01.23, FALSE, '2023-05-23', '23:45:01', '2023-05-23 23:45:01', '{"key": "value2"}', E'\\xFEEDBEEF'),
    (3, 'Varchar value 3', 'Text value 3', 345, 678, 901, 34.56, 78.90, 12.34, TRUE, '2023-05-24', '12:34:56', '2023-05-24 12:34:56', '{"key": "value3"}', E'\\xCAFEBABE'),
    (4, 'Varchar value 4', 'Text value 4', 456, 789, 012, 45.67, 89.01, 23.45, FALSE, '2023-05-25', '23:45:01', '2023-05-25 23:45:01', '{"key": "value4"}', E'\\xBABEC0DE'),
    (5, 'Varchar value 5', 'Text value 5', 567, 890, 123, 56.78, 90.12, 34.56, TRUE, '2023-05-26', '12:34:56', '2023-05-26 12:34:56', '{"key": "value5"}', E'\\xDEADBABE');


CREATE SINK data_types_mysql_sink
FROM
    target_count WITH (
        connector = 'jdbc',
        jdbc.url = 'jdbc:mysql://mysql:3306/mydb?user=root&password=123456',
        table.name = 'data_types',
        type = 'upsert'
    );

The schema of MySQL:

CREATE TABLE data_types (
    id BIGINT PRIMARY KEY,
    varchar_column VARCHAR(255),
    text_column TEXT,
    integer_column INT,
    smallint_column SMALLINT,
    bigint_column BIGINT,
    decimal_column DECIMAL(10,2),
    real_column FLOAT,
    double_column DOUBLE,
    boolean_column BOOLEAN,
    date_column DATE,
    time_column TIME,
    timestamp_column TIMESTAMP,
    jsonb_column JSON,
    bytea_column BLOB,
);

[StrikeW]

@WillyKidd Please take a look at this issue. Support data types for stream_chunk payload first, since JSON payload we need to do casting by ourselves which is error-prone.

[WillyKidd]

I think part of the issue will likely be resolved by #9957 🙂
oh except for the type BYTEA => BLOB

[WillyKidd]

the above e2e test has been added here
https://github.com/risingwavelabs/risingwave/tree/main/e2e_test/sink/remote