Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23285

mysql.transaction_registry contains default values for the timestamp columns that conflict with NO_ZERO_DATE sql mode

    XMLWordPrintable

    Details

      Description

      show create table transaction_registry \G
             Table: transaction_registry
      Create Table: CREATE TABLE `transaction_registry` (
        `transaction_id` bigint(20) unsigned NOT NULL,
        `commit_id` bigint(20) unsigned NOT NULL,
        `begin_timestamp` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
        `commit_timestamp` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
        `isolation_level` enum('READ-UNCOMMITTED','READ-COMMITTED','REPEATABLE-READ','SERIALIZABLE') COLLATE utf8_bin NOT NULL,
        PRIMARY KEY (`transaction_id`),
        UNIQUE KEY `commit_id` (`commit_id`),
        KEY `begin_timestamp` (`begin_timestamp`),
        KEY `commit_timestamp` (`commit_timestamp`,`transaction_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0
      1 row in set (0.000 sec)
       
      \s
      --------------
      mysql  Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 8.0
       
      Connection id:		2570
      Current database:	mysql
      Current user:		root@localhost
      SSL:			Not in use
      Current pager:		/usr/bin/less
      Using outfile:		''
      Using delimiter:	;
      Server:			MariaDB
      Server version:		10.4.13-MariaDB Source distribution
      Protocol version:	10
      Connection:		Localhost via UNIX socket
      Server characterset:	utf8mb4
      Db     characterset:	utf8mb4
      Client characterset:	utf8mb4
      Conn.  characterset:	utf8mb4
      UNIX socket:		/var/run/mysqld/mysqld.sock
      Uptime:			12 hours 41 min 16 sec
      

      While the above seems normal, if you have NO_ZERO_DATE sql mode enabled, when you analyze the mysql database, it fails with a 'invalid default value for column ....' error and stops.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              lourdas_v Vasilis Lourdas
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.