[MDEV-11553] Can't restore a PERSISTENT column that uses DATE_FORMAT() Created: 2016-12-13  Updated: 2020-08-25  Resolved: 2017-09-23

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.1.12, 10.2.2
Fix Version/s: 10.3.2

Type: Bug Priority: Major
Reporter: Jan Steinman Assignee: Sergei Golubchik
Resolution: Fixed Votes: 2
Labels: None
Environment:

MacOS 10.10.5 (export from MariaDB 10.1.12), MacOS 10.12.1 (import to MariaDB 10.2.2)


 Description   

I’m in the process of moving to 10.2, starting with slaving to my operational master (10.1.12). So I dumped the master, and started a restore on the 10.2.2 slave.

But the restore fails with:

ERROR 1901 (HY000) at line 7140: Function or expression 'date_format()' cannot be used in the GENERATED ALWAYS AS clause of `start_day`

I use PERSISTENT columns with the day of the week next to date columns quite heavily. This particular one looks like:

CREATE TABLE `a_nuke_counts` (
 
  `time_start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `start_day` enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') AS (DATE_FORMAT(`time_start`, '%a')) PERSISTENT,
 
  `time_end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `end_day` enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') AS (DATE_FORMAT(`time_end`, '%a')) PERSISTENT,
 
  `count_start` int(10) unsigned NOT NULL,
 
  `count_end` int(10) unsigned NOT NULL,
 
  `location` enum('indoor','outdoor','paper sample') NOT NULL DEFAULT 'indoor',
 
  `note` varchar(255) NOT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

but there are many more.

Should I simply be using VARCHAR(3) instead of ENUM for these? In this case, it's a bug that MDEV 10.1.12 allowed me define and use it as an ENUM.

(Please don’t tell me to use emacs on the 9.1GB dump…

----------------
UPDATE: I just tried executing the above table schema, adding a few records, exporting, DROPping, and importing on MDEV 10.1.12, using Sequel Pro (Mac MySQL GUI). No problem. So it may be a problem with 10.2.2 only. Or perhaps mysqldump.

 Comments   
Comment by Sergei Golubchik [ 2016-12-13 ]

This is a problem with 10.1. 10.2 got stricter about it, there were lots of code changes around virtual columns and some old bugs got fixed. The problem with DATE_FORMAT in particular — it depends on the session locale settings, @@lc_time_names variable. So if do, say,

set lc_time_names=de_DE;

then the stored value will no longer match the result of the expression, basically, the table will be inconsistent. The correct approach in this case would've been to make these columns VIRTUAL, not PERSISTENT.

Comment by Alexander Bienemann (Inactive) [ 2017-02-17 ]

It is important to have a possibility to use DATE_FORMAT with a PERSISTENT virtual column. The use case is as follows:

ALTER TABLE `tablename` ADD `virtualcolumn` CHAR(8) AS (DATE_FORMAT(`datecolumn`,'%Y%m%d')) PERSISTENT;

It might also be worth considering to provide a deterministic version of DATE_FORMAT.

Comment by Sergei Golubchik [ 2017-08-22 ]

Thoughts:

  1. introduce 3-argument version DATE_FORMAT(x, y, locale) and only allow that in persistent generated columns
  2. introduce 3-argument version DATE_FORMAT(x, y, locale) and automatically convert 2-argument version into it (with third argument being equal to the current value of lc_time_names) in persistent generated columns
  3. only allow 2-argument DATE_FORMAT() in persistent generated columns when the second argument is locale-independent (fully numerical — is that enough?)
Comment by Alexander Barkov [ 2017-09-23 ]

Ok to push.
One small suggestion. Instead of having a standalone function,
please move get_locale as method in Item.

Comment by Sergei Golubchik [ 2017-10-09 ]

Implemented approach 1, introduce 3-argument version DATE_FORMAT(x, y, locale)

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