Bug Report: Insert query cannot accept now(3) as timestamp value (Major regression from v17.0.4 and above) during upgradation

[madhur]

Overview of the Issue

We are seeing complete insert failures on our table which was working fine on vitess v16.0.2.

After doing rolling upgrades on our tablets from v16.0.2-> v17.0.4. When we switched over the primary tablet to vitess v17.0.4 version, we can see complete failures.

Reproduction Steps: Create a vschema

CREATE TABLE `contest_user_team_mapping` (
  `id` bigint NOT NULL,
  `user_match_mapping_id` bigint NOT NULL,
  `contest_id` bigint NOT NULL,
  `entry_fee_type` int DEFAULT NULL,
  `channel_id` int DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ticket_source` int DEFAULT NULL,
  `ticket_discount` decimal(10,2) DEFAULT NULL,
  `entry_fee` decimal(10,2) DEFAULT NULL,
  `match_id` bigint DEFAULT NULL,
  `multiplier` decimal(5,2) DEFAULT NULL,
  `lb_id` bigint DEFAULT NULL,
  `additional_info` text CHARACTER SET utf8 COLLATE utf8_general_ci,
  `user_id` bigint DEFAULT NULL,
  `bucket_id` bigint DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `contest_user` (`contest_id`,`user_match_mapping_id`),
  KEY `idx_contest_id` (`contest_id`),
  KEY `idx_updated_at` (`updated_at`),
  KEY `idx_match_id` (`match_id`,`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Reproduction Steps

Apply the insert statement

insert into contest_user_team_mapping (user_match_mapping_id,contest_id,entry_fee_type,channel_id,ticket_source,ticket_discount,entry_fee,match_id,multiplier, lb_id, additional_info,user_id,bucket_id,created_at)
values
(1, 2, 3,4,null,null,10.0,10,null, null, null, 1, null, now(3));

Output:

ERROR 1105 (HY000): target: reverie.0.primary: vttablet: rpc error: code = InvalidArgument desc = syntax error at position 335 near ':vtg8'

Binary Version

Version: 17.0.4 (Git revision 9a3d0f4a69a840cfa2cb86654abd4afa0be6e0aa branch 'HEAD') built on Mon Nov 13 08:18:05 UTC 2023 by runner@fv-az173-607 using go1.20.11 linux/amd64

Operating System and Environment details

NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

Linux 3.10.0-1127.el7.x86_64

x86_64

Log Fragments

8: "type:INT64 value:\"138709133\""vtg9: "type:INT64 value:\"3\""}
I1201 01:15:13.068995  262044 tabletserver.go:1587] Code: INVALID_ARGUMENT
syntax error at position 336 near ':vtg9' (CallerID: gauntlet)
: Sql: "insert into contest_user_team_mapping(user_match_mapping_id, contest_id, entry_fee_type, channel_id, ticket_source, ticket_discount, entry_fee, match_id, multiplier, lb_id, additional_info, user_id, bucket_id, created_at, id) values (:vtg1, :vtg2, :v
tg3, :
vtg4, null, null, :vtg5, :vtg6, null, null, :vtg7, :_user_id_0, null, now(:vtg9), :__seq0)", BindVars: {__seq0: "type:INT64 value:\"772915622\""_user_id_0: "type:INT64 value:\"67136974\""vtg1: "type:INT64 value:\"1635582677\""vtg2: "type:INT64 value:\"141761
536\""
vtg3: "type:INT64 value:\"1\""vtg4: "type:INT64 value:\"2003\""vtg5: "type:DECIMAL value:\"49.0\""vtg6: "type:INT64 value:\"67862\""vtg7: "type:VARCHAR value:\"{\\\"clubId\\\":1,\\\"email\\\":\\\"tr803631@gmail.com\\\",\\\"mobile\\\":\\\"8373885749\\\",\\\"f
irstNa
me\\\":\\\"Tushar Roy\\\"}\""vtg8: "type:INT64 value:\"67136974\""vtg9: "type:INT64 value:\"3\""}
I1201 01:15:13.079284  262044 tabletserver.go:1587] Code: INVALID_ARGUMENT
syntax error at position 336 near ':vtg9' (CallerID: gauntlet)
: Sql: "insert into contest_user_team_mapping(user_match_mapping_id, contest_id, entry_fee_type, channel_id, ticket_source, ticket_discount, entry_fee, match_id, multiplier, lb_id, additional_info, user_id, bucket_id, created_at, id) values (:vtg1, :vtg2, :v
tg3, :
vtg4, null, null, :vtg5, :vtg6, null, null, :vtg7, :_user_id_0, null, now(:vtg9), :__seq0)", BindVars: {__seq0: "type:INT64 value:\"772915623\""_user_id_0: "type:INT64 value:\"151986868\""vtg1: "type:INT64 value:\"1635582577\""vtg2: "type:INT64 value:\"14191
7815\"
"vtg3: "type:INT64 value:\"1\""vtg4: "type:INT64 value:\"2003\""vtg5: "type:DECIMAL value:\"33.0\""vtg6: "type:INT64 value:\"68694\""vtg7: "type:VARCHAR value:\"{\\\"clubId\\\":2,\\\"mobile\\\":\\\"8058523731\\\",\\\"firstName\\\":\\\"Ashok Kumar\\\"}\""vtg8
: "typ
e:INT64 value:\"151986868\""vtg9: "type:INT64 value:\"3\""}
I1201 01:15:13.392216  262044 tabletserver.go:1587] Code: INVALID_ARGUMENT
syntax error at position 336 near ':vtg9' (CallerID: gauntlet)
: Sql: "insert into contest_user_team_mapping(user_match_mapping_id, contest_id, entry_fee_type, channel_id, ticket_source, ticket_discount, entry_fee, match_id, multiplier, lb_id, additional_info, user_id, bucket_id, created_at, id) values (:vtg1, :vtg2, :v
tg3, :
vtg4, null, null, :vtg5, :vtg6, null, null, :vtg7, :_user_id_0, null, now(:vtg9), :__seq0)", BindVars: {__seq0: "type:INT64 value:\"772915624\""_user_id_0: "type:INT64 value:\"89507699\""vtg1: "type:INT64 value:\"1635582322\""vtg2: "type:INT64 value:\"142400
530\""
vtg3: "type:INT64 value:\"1\""vtg4: "type:INT64 value:\"2003\""vtg5: "type:DECIMAL value:\"25.0\""vtg6: "type:INT64 value:\"67889\""vtg7: "type:VARCHAR value:\"{\\\"clubId\\\":1,\\\"email\\\":\\\"ysumitkumar103@gmail.com\\\",\\\"mobile\\\":\\\"7631580902\\\"
,\\\"f
irstName\\\":\\\"Kumar\\\"}\""vtg8: "type:INT64 value:\"89507699\""vtg9: "type:INT64 value:\"3\""}
I1201 01:15:13.441195  262044 tabletserver.go:1587] Code: INVALID_ARGUMENT
syntax error at position 336 near ':vtg9' (CallerID: gauntlet)

[madhur]

The error seems to be related to providing value now(3) in insert values.

[mattlord]

I cannot repeat the issue on main or v17.0.4:

git checkout v17.0.4
make build

pushd examples/local

./101_initial_cluster.sh

echo "create table test (id int not null auto_increment primary key, ts timestamp);
insert into test values (1, now(3));
CREATE TABLE contest_user_team_mapping (   id bigint NOT NULL,   user_match_mapping_id bigint NOT NULL,   contest_id bigint NOT
NULL,   entry_fee_type int DEFAULT NULL,   channel_id int DEFAULT NULL,   updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   ticket_source int DEFAULT NULL,   ticket_discount decimal(10,2) DEFAULT NULL,   entry_fee decimal(10,2) DEFAULT NULL,   match_id bigint DEFAULT NULL,   multiplier decimal(5,2) DEFAULT NULL,   lb_id bigint DEFAULT NULL,   additional_info text CHARACTER SET utf8 COLLATE utf8_general_ci,   user_id bigint DEFAULT NULL,   bucket_id bigint DEFAULT NULL,   created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (id),   UNIQUE KEY contest_user (contest_id,user_match_mapping_id),   KEY idx_contest_id (contest_id),   KEY idx_updated_at (updated_at),   KEY idx_match_id (match_id,id),   KEY idx_user_id (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into contest_user_team_mapping (id, user_match_mapping_id,contest_id,entry_fee_type,channel_id,ticket_source,ticket_discount,entry_fee,match_id,multiplier, lb_id, additional_info,user_id,bucket_id,created_at) values (1, 1, 2, 3,4,null,null,10.0,10,null, null, null, 1, null, now(3));" | mysql

I got an error trying to do the insert w/o the ID value. I'm guessing that you have that using a sequence on your side.

So clearly there's something else missing here. You didn't share other details like the MySQL version (8.0.34 in my case) or Vitess config flags (vtgate and vttablet).

[madhur]

Hi Matt

This will be reproducible if vtgate are on v16 and vttablets are on v17.

We saw this error during upgrade procedure. It works fine when enter stack is on v17.

On Sat, Dec 9, 2023, 20:52 Matt Lord @.***> wrote:

I cannot repeat the issue on main or v17.0.4:

git checkout v17.0.4 make build

pushd examples/local

./101_initial_cluster.sh

echo "create table test (id int not null auto_increment primary key, ts timestamp); insert into test values (1, now(3)); CREATE TABLE contest_user_team_mapping ( id bigint NOT NULL, user_match_mapping_id bigint NOT NULL, contest_id bigint NOT NULL, entry_fee_type int DEFAULT NULL, channel_id int DEFAULT NULL, updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ticket_source int DEFAULT NULL, ticket_discount decimal(10,2) DEFAULT NULL, entry_fee decimal(10,2) DEFAULT NULL, match_id bigint DEFAULT NULL, multiplier decimal(5,2) DEFAULT NULL, lb_id bigint DEFAULT NULL, additional_info text CHARACTER SET utf8 COLLATE utf8_general_ci, user_id bigint DEFAULT NULL, bucket_id bigint DEFAULT NULL, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY contest_user (contest_id,user_match_mapping_id), KEY idx_contest_id (contest_id), KEY idx_updated_at (updated_at), KEY idx_match_id (match_id,id), KEY idx_user_id (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into contest_user_team_mapping (id, user_match_mapping_id,contest_id,entry_fee_type,channel_id,ticket_source,ticket_discount,entry_fee,match_id,multiplier, lb_id, additional_info,user_id,bucket_id,created_at) values (1, 1, 2, 3,4,null,null,10.0,10,null, null, null, 1, null, now(3));

I got an error trying to do the insert w/o the ID value. I'm guessing that you have that using a sequence on your side.

So clearly there's something else missing here. You didn't share other details like the MySQL version (8.0.34 in my case) or Vitess config flags (vtgate and vttablet).

— Reply to this email directly, view it on GitHub https://github.com/vitessio/vitess/issues/14740#issuecomment-1848438874, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAD4L52W6VX4MSKV7SNB7DTYIR64XAVCNFSM6AAAAABANVMXFKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNBYGQZTQOBXGQ . You are receiving this because you authored the thread.Message ID: @.***>

[harshit-gangal]

I confirm select now(:v1) parses on v16 but fails on v17. Looks like a parser regression.

[madhur]

Is there any workaround for upgrade?

[harshit-gangal]

Is there any workaround for upgrade?

There are two workaround here:

1. turn off query normalizer with normalize_queries vtgate flag
2. upgrade vtgate first

We plan to fix this issue and release a patch next week.

[GuptaManan100]

This fix has been merged in release-17.0. #14763 The next time we do a patch release for this branch, that minor version would not have this problem. Thank-you for reporting this issue @madhur