Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.12, 10.2.2
-
None
-
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.
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,
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.