View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0006955SymmetricDSBugpublic2025-06-18 15:182025-06-18 15:18
Reportermdrouard Assigned Tomdrouard  
Prioritynormal 
Status assignedResolutionopen 
Product Version3.15.9 
Target Version3.16.4 
Summary0006955: When "on update CURRENT_TIMESTAMP" is not the first part of the extra column info it does not get picked up correctly
DescriptionWhen creating a timestamp column with a combination of "Default current_timestamp" and "on update CURRENT_TIMESTAMP" it is not correctly picked up by the DdlReader.
Instead is is picked up as an auto generate column. See additional information to compare the flags on "LastWritten" column
This results in the error:
 The outgoing batch Endpoint_2-12 failed: [HY000,3763] Expression of generated column 'LastWritten' contains a disallowed function: now.
Steps To Reproduce1. Create the following table in DBeaver on source database

CREATE TABLE defaultcurrenttimeonupdatewithpar (
    smID CHAR(3) NOT NULL,
    smTitle VARCHAR(20) NOT NULL,
    smLocID INT DEFAULT 0 NOT NULL,
    smDescription VARCHAR(50) NOT NULL,
    LastWritten TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0) on update current_timestamp() NOT NULL,
    PRIMARY KEY (smID)
);

2. Add table to replication in Symmetric

3. Send an initial load with create table

4. Observe the error in the log:
"The outgoing batch Endpoint_2-12 failed: [HY000,3763] Expression of generated column 'LastWritten' contains a disallowed function: now."
Additional InformationObserve flags / extra information on 'LastWritten' column for 4 different cases
A: LastWritten TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0) on update current_timestamp() NOT NULL
B: LastWritten TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0) NOT NULL
C: LastWritten TIMESTAMP on update current_timestamp() NOT NULL
D: LastWritten TIMESTAMP NOT NULL

A. Table that results in an error
1.Create Table in DBeaver
CREATE TABLE defaultcurrenttimeonupdate (
    smID CHAR(3) NOT NULL,
    smTitle VARCHAR(20) NOT NULL,
    LastWritten TIMESTAMP DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (smID)

);

2.Table "source" on explore tab
CREATE TABLE `defaultcurrenttimeonupdate`(
    `smID` CHAR(3) NOT NULL,
    `smTitle` VARCHAR(20) NOT NULL,
    `LastWritten` TIMESTAMP,
    PRIMARY KEY (`smID`)
);

3. Table XML export
<table name="defaultcurrenttimeonupdate">
    <column name="smID" primaryKey="true" primaryKeySeq="1" required="true" type="CHAR" size="3">
        <platform-column name="mysql" type="CHAR" size="3"/>
    </column>
    <column name="smTitle" required="true" type="VARCHAR" size="20">
        <platform-column name="mysql" type="VARCHAR" size="20"/>
    </column>
    <column name="LastWritten" required="true" type="TIMESTAMP" size="0" default="CURRENT_TIMESTAMP" generated="true">
        <platform-column name="mysql" type="TIMESTAMP" default="CURRENT_TIMESTAMP"/>
    </column>
</table>

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

B. LastWritten TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0) NOT NULL
1.Create Table in DBeaver
CREATE TABLE defaultcurrenttime(
    smID CHAR(3) NOT NULL,
    smTitle VARCHAR(20) NOT NULL,
    LastWritten TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0) NOT NULL,
    PRIMARY KEY (smID)
);

2.Table "source" on explore tab
CREATE TABLE `defaultcurrenttime`(
    `smID` CHAR(3) NOT NULL,
    `smTitle` VARCHAR(20) NOT NULL,
    `LastWritten` TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0) NOT NULL,
    PRIMARY KEY (`smID`)
);

3. Table XML export
<table name="defaultcurrenttime">
    <column name="smID" primaryKey="true" primaryKeySeq="1" required="true" type="CHAR" size="3">
        <platform-column name="mysql" type="CHAR" size="3"/>
    </column>
    <column name="smTitle" required="true" type="VARCHAR" size="20">
        <platform-column name="mysql" type="VARCHAR" size="20"/>
    </column>
    <column name="LastWritten" required="true" type="TIMESTAMP" size="0" default="CURRENT_TIMESTAMP" expressionAsDefault="true">
        <platform-column name="mysql" type="TIMESTAMP" default="CURRENT_TIMESTAMP"/>
    </column>
</table>

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

C. LastWritten TIMESTAMP on update current_timestamp() NOT NULL
CREATE TABLE onupdatecurrent (
    smID CHAR(3) NOT NULL,
    smTitle VARCHAR(20) NOT NULL,
    smLocID INT DEFAULT 0 NOT NULL,
    smDescription VARCHAR(50) NOT NULL,
    LastWritten TIMESTAMP on update current_timestamp() NOT NULL,
    PRIMARY KEY (smID)
);

CREATE TABLE `onupdatecurrent`(
    `smID` CHAR(3) NOT NULL,
    `smTitle` VARCHAR(20) NOT NULL,
    `smLocID` INT DEFAULT 0 NOT NULL,
    `smDescription` VARCHAR(50) NOT NULL,
    `LastWritten` TIMESTAMP on update current_timestamp() NOT NULL,
    PRIMARY KEY (`smID`)
);

<table name="onupdatecurrent">
    <column name="smID" primaryKey="true" primaryKeySeq="1" required="true" type="CHAR" size="3">
        <platform-column name="mysql" type="CHAR" size="3"/>
    </column>
    <column name="smTitle" required="true" type="VARCHAR" size="20">
        <platform-column name="mysql" type="VARCHAR" size="20"/>
    </column>
    <column name="smLocID" required="true" type="INTEGER" size="10" default="0">
        <platform-column name="mysql" type="INT" size="10" default="0"/>
    </column>
    <column name="smDescription" required="true" type="VARCHAR" size="50">
        <platform-column name="mysql" type="VARCHAR" size="50"/>
    </column>
    <column name="LastWritten" required="true" type="TIMESTAMP" size="0" autoUpdate="true">
        <platform-column name="mysql" type="TIMESTAMP"/>
    </column>
</table>

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

D. LastWritten TIMESTAMP NOT NULL

1.Create Table in DBeaver
CREATE TABLE timenotnull (
    smID CHAR(3) NOT NULL,
    smTitle VARCHAR(20) NOT NULL,
    LastWritten TIMESTAMP NOT NULL,
    PRIMARY KEY (smID)
);

2.Table "source" on explore tab
CREATE TABLE `timenotnull`(
    `smID` CHAR(3) NOT NULL,
    `smTitle` VARCHAR(20) NOT NULL,
    `LastWritten` TIMESTAMP NOT NULL,
    PRIMARY KEY (`smID`)
);

3. Table XML export
<table name="timenotnull">
    <column name="smID" primaryKey="true" primaryKeySeq="1" required="true" type="CHAR" size="3">
        <platform-column name="mysql" type="CHAR" size="3"/>
    </column>
    <column name="smTitle" required="true" type="VARCHAR" size="20">
        <platform-column name="mysql" type="VARCHAR" size="20"/>
    </column>
    <column name="LastWritten" required="true" type="TIMESTAMP" size="0">
        <platform-column name="mysql" type="TIMESTAMP"/>
    </column>
</table>
TagsNo tags attached.

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2025-06-18 15:18 mdrouard New Issue
2025-06-18 15:18 mdrouard Status new => assigned
2025-06-18 15:18 mdrouard Assigned To => mdrouard