Hi Team,
I would like to run temporal in AWS instance. Unfortunately I cannot use cassandra,elastic,postgress or elastic. Available DBs are Maria DB and Couchbase. Since MariaDB forked from mysql is it possible to use?I have tried to run in my machine and it’s works fine. Could some one please confirm
I too am curious about this. I noticed another article discussing a potential bug in performance between the two here but no one has responded. Can someone please confirm that there won’t be any issues or if there is actually a difference between using MySQL vs MariaDB
After some digging, and some troubleshooting, I have discovered the answer myself. Although, there are great similarities between MySQL and MariaDB, the roadblock occurs with the versioned updates. Some of the versioned updates include syntax that is specific MySQL and requires a workaround to execute the query. Currently, I only encountered this issue when applying versions for visibility, I had no issues applying updates for main temporal server so I was able to successfully apply all SQL updates from 1.0 - 1.14 with no issue there.
Unfortunately I was only able to get as far as 1.1 for visibility. The issue I encountered was with the JSON type. MySQL has native support for the JSON type and associated JSON functions while MariaDB does not. It requires some field changes in order to work.
Context:
Note: This is just the first thing I found, there could definitely be other blockers I am not aware. I am just sharing this for the next person.
I’ve been experimenting with getting Temporal v1.14 to run with MariaDB 11 over the past few days (as we already have a HA MariaDB cluster so would prefer not to start maintaining another database cluster), and have got to the stage where I’ve been able to start Temporal and run a few of the example workflows - though there could be incompatibilities I haven’t discovered yet.
The first issues related to differences in the MariaDB and MySQL JSON implementation. The a->b alias MySQL provides for JSON_EXTRACT(a, b), and ->> for JSON_UNQUOTE(JSON_EXTRACT(a, b)) aren’t supported, so I updated these. The CAST(column AS CHAR(255) ARRAY used for indexing JSON columns also wasn’t supported, so I updated these to column(255).
Once all these occurrences were replaced, the last issue I encountered was relating to the COALESCE function usage for indexes on the executions_visibility close_time column. The best solution I found for this (virtual columns didn’t help) was to add a new column and set its value using triggers to be either the close_date, or if null, 9999-12-31 23:59:59.
This was enough to get things working for me - I manually applied this migration and then ran the Temporal binary. I’ve provided the migration I ran below as a starting point for anyone else who’s trying to test compatibility with MariaDB. There’s a chance that queries from Temporal could begin using unsupported MariaDB syntax in future updates, so manually modifying incompatible SQL migrations may not be enough to allow MariaDB compatibility in the long term.
CREATE TABLE executions_visibility (
namespace_id CHAR(64) NOT NULL,
run_id CHAR(64) NOT NULL,
start_time DATETIME(6) NOT NULL,
execution_time DATETIME(6) NOT NULL,
workflow_id VARCHAR(255) NOT NULL,
workflow_type_name VARCHAR(255) NOT NULL,
status INT NOT NULL, -- enum WorkflowExecutionStatus {RUNNING, COMPLETED, FAILED, CANCELED, TERMINATED, CONTINUED_AS_NEW, TIMED_OUT}
close_time DATETIME(6) NULL,
history_length BIGINT NULL,
history_size_bytes BIGINT NULL,
execution_duration BIGINT NULL,
state_transition_count BIGINT NULL,
memo BLOB NULL,
encoding VARCHAR(64) NOT NULL,
task_queue VARCHAR(255) NOT NULL DEFAULT '',
search_attributes JSON NULL,
parent_workflow_id VARCHAR(255) NULL,
parent_run_id VARCHAR(255) NULL,
root_workflow_id VARCHAR(255) NOT NULL DEFAULT '',
root_run_id VARCHAR(255) NOT NULL DEFAULT '',
-- Each search attribute has its own generated column.
-- For string types (keyword and text), we need to unquote the json string,
-- ie., use `->>` instead of `->` operator.
-- For text types, the generated column need to be STORED instead of VIRTUAL,
-- so we can create a full-text search index.
-- For datetime type, MySQL can't cast datetime string with timezone to
-- datetime type directly, so we need to call CONVERT_TZ to convert to UTC.
-- Check the `custom_search_attributes` table for complete set of examples.
-- Predefined search attributes
TemporalChangeVersion JSON GENERATED ALWAYS AS (json_extract(search_attributes, '$.TemporalChangeVersion')),
BinaryChecksums JSON GENERATED ALWAYS AS (json_extract(search_attributes, '$.BinaryChecksums')),
BatcherUser VARCHAR(255) GENERATED ALWAYS AS (json_unquote(json_extract(search_attributes, '$.BatcherUser'))),
TemporalScheduledStartTime DATETIME(6) GENERATED ALWAYS AS (
CONVERT_TZ(
REGEXP_REPLACE(json_unquote(json_extract(search_attributes, '$.TemporalScheduledStartTime')), 'Z|[+-][0-9]{2}:[0-9]{2}$', ''),
SUBSTR(REPLACE(json_unquote(json_extract(search_attributes, '$.TemporalScheduledStartTime')), 'Z', '+00:00'), -6, 6),
'+00:00'
)
),
TemporalScheduledById VARCHAR(255) GENERATED ALWAYS AS (json_unquote(json_extract(search_attributes, '$.TemporalScheduledById'))),
TemporalSchedulePaused BOOLEAN GENERATED ALWAYS AS (json_unquote(json_extract(search_attributes, '$.TemporalSchedulePaused'))),
TemporalNamespaceDivision VARCHAR(255) GENERATED ALWAYS AS (json_unquote(json_extract(search_attributes, '$.TemporalNamespaceDivision'))),
BuildIds JSON GENERATED ALWAYS AS (json_unquote(json_extract(search_attributes, '$.BuildIds'))),
PRIMARY KEY (namespace_id, run_id)
);
# coalesce / ifnull can't be used in virtual columns or indexes, so need to use triggers for handling normalising this
ALTER TABLE executions_visibility ADD COLUMN close_time_normalized DATETIME;
DELIMITER //
CREATE TRIGGER before_insert_executions_visibility
BEFORE INSERT ON executions_visibility
FOR EACH ROW
BEGIN
IF NEW.close_time IS NULL THEN
SET NEW.close_time_normalized = '9999-12-31 23:59:59';
ELSE
SET NEW.close_time_normalized = NEW.close_time;
END IF;
END; //
DELIMITER ;
DELIMITER //
CREATE TRIGGER before_update_executions_visibility
BEFORE UPDATE ON executions_visibility
FOR EACH ROW
BEGIN
IF NEW.close_time IS NULL THEN
SET NEW.close_time_normalized = '9999-12-31 23:59:59';
ELSE
SET NEW.close_time_normalized = NEW.close_time;
END IF;
END;
DELIMITER ;
# end additions
CREATE INDEX default_idx ON executions_visibility (namespace_id, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_execution_time ON executions_visibility (namespace_id, execution_time, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_workflow_id ON executions_visibility (namespace_id, workflow_id, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_workflow_type ON executions_visibility (namespace_id, workflow_type_name, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_status ON executions_visibility (namespace_id, status, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_history_length ON executions_visibility (namespace_id, history_length, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_history_size_bytes ON executions_visibility (namespace_id, history_size_bytes, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_execution_duration ON executions_visibility (namespace_id, execution_duration, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_state_transition_count ON executions_visibility (namespace_id, state_transition_count, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_task_queue ON executions_visibility (namespace_id, task_queue, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_parent_workflow_id ON executions_visibility (namespace_id, parent_workflow_id, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_parent_run_id ON executions_visibility (namespace_id, parent_run_id, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_root_workflow_id ON executions_visibility (namespace_id, root_workflow_id, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_root_run_id ON executions_visibility (namespace_id, root_run_id, close_time_normalized DESC, start_time DESC, run_id);
-- Indexes for the predefined search attributes
CREATE INDEX by_temporal_change_version ON executions_visibility (namespace_id, TemporalChangeVersion(255), close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_binary_checksums ON executions_visibility (namespace_id, BinaryChecksums(255), close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_build_ids ON executions_visibility (namespace_id, BuildIds(255), close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_batcher_user ON executions_visibility (namespace_id, BatcherUser, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_temporal_scheduled_start_time ON executions_visibility (namespace_id, TemporalScheduledStartTime, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_temporal_scheduled_by_id ON executions_visibility (namespace_id, TemporalScheduledById, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_temporal_schedule_paused ON executions_visibility (namespace_id, TemporalSchedulePaused, close_time_normalized DESC, start_time DESC, run_id);
CREATE INDEX by_temporal_namespace_division ON executions_visibility (namespace_id, TemporalNamespaceDivision, close_time_normalized DESC, start_time DESC, run_id);
CREATE TABLE custom_search_attributes (
namespace_id CHAR(64) NOT NULL,
run_id CHAR(64) NOT NULL,
search_attributes JSON NULL,
Bool01 BOOLEAN GENERATED ALWAYS AS (json_extract(search_attributes, '$.Bool01')),
Bool02 BOOLEAN GENERATED ALWAYS AS (json_extract(search_attributes, '$.Bool02')),
Bool03 BOOLEAN GENERATED ALWAYS AS (json_extract(search_attributes, '$.Bool03')),
Datetime01 DATETIME(6) GENERATED ALWAYS AS (
CONVERT_TZ(
REGEXP_REPLACE(json_unquote(json_extract(search_attributes, '$.Datetime01')), 'Z|[+-][0-9]{2}:[0-9]{2}$', ''),
SUBSTR(REPLACE(json_unquote(json_extract(search_attributes, '$.Datetime01')), 'Z', '+00:00'), -6, 6),
'+00:00'
)
),
Datetime02 DATETIME(6) GENERATED ALWAYS AS (
CONVERT_TZ(
REGEXP_REPLACE(json_unquote(json_extract(search_attributes, '$.Datetime02')), 'Z|[+-][0-9]{2}:[0-9]{2}$', ''),
SUBSTR(REPLACE(json_unquote(json_extract(search_attributes, '$.Datetime02')), 'Z', '+00:00'), -6, 6),
'+00:00'
)
),
Datetime03 DATETIME(6) GENERATED ALWAYS AS (
CONVERT_TZ(
REGEXP_REPLACE(json_unquote(json_extract(search_attributes, '$.Datetime03')), 'Z|[+-][0-9]{2}:[0-9]{2}$', ''),
SUBSTR(REPLACE(json_unquote(json_extract(search_attributes, '$.Datetime03')), 'Z', '+00:00'), -6, 6),
'+00:00'
)
),
Double01 DECIMAL(20, 5) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Double01')),
Double02 DECIMAL(20, 5) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Double02')),
Double03 DECIMAL(20, 5) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Double03')),
Int01 BIGINT GENERATED ALWAYS AS (json_extract(search_attributes, '$.Int01')),
Int02 BIGINT GENERATED ALWAYS AS (json_extract(search_attributes, '$.Int02')),
Int03 BIGINT GENERATED ALWAYS AS (json_extract(search_attributes, '$.Int03')),
Keyword01 VARCHAR(255) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Keyword01')),
Keyword02 VARCHAR(255) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Keyword02')),
Keyword03 VARCHAR(255) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Keyword03')),
Keyword04 VARCHAR(255) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Keyword04')),
Keyword05 VARCHAR(255) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Keyword05')),
Keyword06 VARCHAR(255) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Keyword06')),
Keyword07 VARCHAR(255) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Keyword07')),
Keyword08 VARCHAR(255) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Keyword08')),
Keyword09 VARCHAR(255) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Keyword09')),
Keyword10 VARCHAR(255) GENERATED ALWAYS AS (json_extract(search_attributes, '$.Keyword10')),
Text01 TEXT GENERATED ALWAYS AS (json_extract(search_attributes, '$.Text01')) STORED,
Text02 TEXT GENERATED ALWAYS AS (json_extract(search_attributes, '$.Text02')) STORED,
Text03 TEXT GENERATED ALWAYS AS (json_extract(search_attributes, '$.Text03')) STORED,
KeywordList01 JSON GENERATED ALWAYS AS (json_extract(search_attributes, '$.KeywordList01')),
KeywordList02 JSON GENERATED ALWAYS AS (json_extract(search_attributes, '$.KeywordList02')),
KeywordList03 JSON GENERATED ALWAYS AS (json_extract(search_attributes, '$.KeywordList03')),
PRIMARY KEY (namespace_id, run_id)
);
CREATE INDEX by_bool_01 ON custom_search_attributes (namespace_id, Bool01);
CREATE INDEX by_bool_02 ON custom_search_attributes (namespace_id, Bool02);
CREATE INDEX by_bool_03 ON custom_search_attributes (namespace_id, Bool03);
CREATE INDEX by_datetime_01 ON custom_search_attributes (namespace_id, Datetime01);
CREATE INDEX by_datetime_02 ON custom_search_attributes (namespace_id, Datetime02);
CREATE INDEX by_datetime_03 ON custom_search_attributes (namespace_id, Datetime03);
CREATE INDEX by_double_01 ON custom_search_attributes (namespace_id, Double01);
CREATE INDEX by_double_02 ON custom_search_attributes (namespace_id, Double02);
CREATE INDEX by_double_03 ON custom_search_attributes (namespace_id, Double03);
CREATE INDEX by_int_01 ON custom_search_attributes (namespace_id, Int01);
CREATE INDEX by_int_02 ON custom_search_attributes (namespace_id, Int02);
CREATE INDEX by_int_03 ON custom_search_attributes (namespace_id, Int03);
CREATE INDEX by_keyword_01 ON custom_search_attributes (namespace_id, Keyword01);
CREATE INDEX by_keyword_02 ON custom_search_attributes (namespace_id, Keyword02);
CREATE INDEX by_keyword_03 ON custom_search_attributes (namespace_id, Keyword03);
CREATE INDEX by_keyword_04 ON custom_search_attributes (namespace_id, Keyword04);
CREATE INDEX by_keyword_05 ON custom_search_attributes (namespace_id, Keyword05);
CREATE INDEX by_keyword_06 ON custom_search_attributes (namespace_id, Keyword06);
CREATE INDEX by_keyword_07 ON custom_search_attributes (namespace_id, Keyword07);
CREATE INDEX by_keyword_08 ON custom_search_attributes (namespace_id, Keyword08);
CREATE INDEX by_keyword_09 ON custom_search_attributes (namespace_id, Keyword09);
CREATE INDEX by_keyword_10 ON custom_search_attributes (namespace_id, Keyword10);
CREATE FULLTEXT INDEX by_text_01 ON custom_search_attributes (Text01);
CREATE FULLTEXT INDEX by_text_02 ON custom_search_attributes (Text02);
CREATE FULLTEXT INDEX by_text_03 ON custom_search_attributes (Text03);
CREATE INDEX by_keyword_list_01 ON custom_search_attributes (namespace_id, KeywordList01(255));
CREATE INDEX by_keyword_list_02 ON custom_search_attributes (namespace_id, KeywordList02(255));
CREATE INDEX by_keyword_list_03 ON custom_search_attributes (namespace_id, KeywordList03(255));
I had an error relating to the schema_version table missing, and just manually created this afterwards:
CREATE TABLE `schema_version` ( `version_partition` int(11) NOT NULL, `db_name` varchar(255) NOT NULL, `creation_time` datetime(6) DEFAULT NULL,
`curr_version` varchar(64) DEFAULT NULL, `min_compatible_version` varchar(64) DEFAULT NULL, PRIMARY KEY (`version_partition`,`db_name`));
INSERT INTO schema_version VALUES (0, DATABASE(), NOW(), '1.14', '1.14');