[MDEV-13377] current_timestamp variable being replaced in create table statement Created: 2017-07-24  Updated: 2021-12-08

Status: Confirmed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Packaging, Temporal Types
Affects Version/s: 10.2
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: Andrew Ramsay Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: upstream
Environment:

windows


Attachments: PNG File 10.1.26h2.png     PNG File 10.1.26heidisql.png     PNG File 10.2.7h2.png     PNG File 10.2.7heidi.png    

 Description   

table definition is being altered by server:
create a simple table:

CREATE TABLE t1 (
 
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 
);

...this is being returned by the server to read:

CREATE TABLE t1 (
 
  ts timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp()
 
);

The brackets appearing after the variable CURRENT_TIMESTAMP are being added (presumably replacing the variable with the function), which causes problems with unexpected changes in the DDL.

This does not happen in 10.0.24.
I do not know whether it happens in 10.1.x

 Comments   
Comment by Elena Stepanova [ 2017-07-24 ]

What exactly are the problems that this change causes?

Comment by Andrew Ramsay [ 2017-07-24 ]

1.change management thinks a change has occurred as the text is returned differently when interrogating the database compared to DDL used for table creation.
2. applications that understand DDL (e.g. heidisql as shipped with MariaDB) do not accept the function equivalent of an SQL variable, so if the brackets are included the table DDL is displayed up to the end of the first CURRENT_TIMESTAMP.

I made it minor as there is a workaround, but it still seems that it should remain as specified, if legal DDL, of course.

Comment by Elena Stepanova [ 2017-07-24 ]

The change was intentional, made in scope of this commit:
https://github.com/MariaDB/server/commit/a411d7f4f670c24b43b50f7d2a1129e10218f4a7

The syntax with brackets is legal (the standard seems to have it as an option), however, it only offers either CURRENT_TIMESTMP or CURRENT_TIMESTAMP(p), but not CURRENT_TIMESTAMP(). Or maybe it's just the 99th standard, and now things are different.

serg, do you want to elaborate on that? (the standard note, and the problem in general – it's not the first time that we hear the complaint about the new syntax)

Regarding the examples,

1. I'm not sure what change management is, but if it relies on SHOW CREATE output being identical to the original CREATE TABLE statement, it cannot possibly work, they are in general nothing alike:

MariaDB [test]> create table t2 (pk int primary key, f char, index(f));
 
Query OK, 0 rows affected (0.46 sec)
 
 
 
MariaDB [test]> show create table t2;
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                          |
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t2    | CREATE TABLE `t2` (
 
  `pk` int(11) NOT NULL,
 
  `f` char(1) DEFAULT NULL,
 
  PRIMARY KEY (`pk`),
 
  KEY `f` (`f`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+

Type specification, column flags, index specification, table flags – everything generally looks different.

2. The syntax with brackets is definitely legal and accepted by older versions of the server as well. Moreover, it existed in previous versions, in case a timestamp was specified with milliseconds:

MariaDB [test]> CREATE TABLE t1 ( ts TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
 
Query OK, 0 rows affected (0.36 sec)
 
 
 
MariaDB [test]> show create table t1;
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                        |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `ts` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select @@version;
 
+-----------------+
 
| @@version       |
 
+-----------------+
 
| 10.0.24-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

If HeidiSQL has a problem with brackets, it's a bug that needs to be reported to HeidiSQL and when it's fixed, the new version should be wrapped in our Windows packages.
alice, could you please verify the HeidiSQL problem with the syntax?

Comment by Alice Sherepa [ 2017-07-25 ]


Heidisql accept both values current_timestamp() and CURRENT_TIMESTAMP, but if we have in a column ON UPDATE CURRENT_TIMESTAMP in 10.2 version it is not presented in tab "create code" for a table.

Comment by Juha Pyy [ 2017-07-26 ]

FWIW, there also seems to be bugs in HeidiSQL with handling curtime() and curdate().

MariaDB [test]> CREATE TABLE `t3` (`Time` time DEFAULT CURRENT_TIME, `Date` DATE DEFAULT CURRENT_DATE) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.03 sec)
 
 
 
MariaDB [test]> SHOW CREATE TABLE t3;
 
+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table                | Create Table                                                                                                                                                                                                                                                                                                    |
 
+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t3 | CREATE TABLE `t3` (
 
  `Time` time DEFAULT curtime(),
 
  `Date` date DEFAULT curdate()
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

..the table is shown in HeidiSQL with incorrect default values:

CREATE TABLE `t3` (
 
	`Time` TIME NULL DEFAULT 'curtime',
 
	`Date` DATE NULL DEFAULT 'curdate',
 
)
 
COLLATE='latin1_swedish_ci'
 
ENGINE=InnoDB
 
;

Comment by Elena Stepanova [ 2017-09-19 ]

alice, I think you reported the issue to HeidiSQL, right? If you did, please add a link to the item so we could track it too.

I keep the report open, as we'll need to get the new version of HeidiSQL when it's fixed there.

Generated at Thu Feb 08 08:05:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.