View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0006955SymmetricDSBugpublic2025-06-18 15:182025-07-10 17:10
Reportermdrouard Assigned Tomdrouard  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.15.9 
Target Version3.16.4Fixed in 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>
Tagsdialect: mysql/mariadb

Relationships

related to 0006982 closedmdrouard Create unit test for MySqlDdlReader.DetermineExtraColumnInfo 

Activities

mdrouard

2025-06-30 20:51

developer   ~0003147

PR: https://github.com/JumpMind/symmetric-ds/tree/bugfix/6955_MySql_on_update_CURRENT_TIMESTAMP

pbelov

2025-07-10 17:10

manager   ~0003191

Included in the 3.16.4 release

Related Changesets

SymmetricDS: 3.16 f9d8b8ab

2025-06-30 20:58:21

mdrouard


Committer: GitHub Details Diff 		0006955: Detect on update CURRENT_TIMESTAMP in MySQL tables (#312)

0006955: Detect on update CURRENT_TIMESTAMP in MySQL tables		 Affected Issues
0006955
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/mysql/MySqlDdlReader.java Diff File

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
2025-06-30 20:51 mdrouard Note Added: 0003147
2025-06-30 20:58 pbelov Tag Attached: dialect: mysql/mariadb
2025-06-30 20:59 pbelov Status assigned => resolved
2025-06-30 20:59 pbelov Resolution open => fixed
2025-06-30 20:59 pbelov Fixed in Version => 3.16.4
2025-06-30 21:00 mdrouard Changeset attached => SymmetricDS 3.16 f9d8b8ab
2025-06-30 21:01 pbelov Relationship added related to 0006982
2025-07-10 17:10 pbelov Note Added: 0003191
2025-07-10 17:10 pbelov Status resolved => closed