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

madhur · 2023-12-09T09:32:26Z

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\\\":\\\"[email protected]\\\",\\\"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\\\":\\\"[email protected]\\\",\\\"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)

The text was updated successfully, but these errors were encountered:

madhur · 2023-12-09T09:33:31Z

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

mattlord · 2023-12-09T15:22:40Z

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 · 2023-12-09T17:16:13Z

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 <#14740 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAD4L52W6VX4MSKV7SNB7DTYIR64XAVCNFSM6AAAAABANVMXFKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNBYGQZTQOBXGQ> . You are receiving this because you authored the thread.Message ID: ***@***.***>

harshit-gangal · 2023-12-11T09:03:14Z

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

madhur · 2023-12-12T07:25:21Z

Is there any workaround for upgrade?

harshit-gangal · 2023-12-12T09:33:34Z

Is there any workaround for upgrade?

There are two workaround here:

turn off query normalizer with normalize_queries vtgate flag
upgrade vtgate first

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

GuptaManan100 · 2023-12-13T12:07:37Z

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

madhur added Needs Triage This issue needs to be correctly labelled and triaged Type: Bug labels Dec 9, 2023

madhur changed the title ~~Bug Report: Insert query cannot accept now(3) as timestamp value (Major regression from v17.0.4 and above)~~ Bug Report: Insert query cannot accept now(3) as timestamp value (Major regression from v17.0.4 and above) during upgradation Dec 9, 2023

harshit-gangal added Component: Query Serving Type: Regression and removed Needs Triage This issue needs to be correctly labelled and triaged labels Dec 11, 2023

GuptaManan100 mentioned this issue Dec 13, 2023

Fix accepting bind variables in time related function calls. #14763

Merged

4 tasks

GuptaManan100 closed this as completed Dec 13, 2023

This was referenced Jan 22, 2025

Backport sqlparser patch - 14763 Fix accepting bind variables in time related function calls. slackhq/vitess#589

Closed

Backport sqlparser patch for v15->v19 upgrade: 14763 Fix accepting bind variables in time related function calls slackhq/vitess#590

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

madhur commented Dec 9, 2023

madhur commented Dec 9, 2023

mattlord commented Dec 9, 2023 •

edited

Loading

madhur commented Dec 9, 2023 via email

harshit-gangal commented Dec 11, 2023

madhur commented Dec 12, 2023

harshit-gangal commented Dec 12, 2023

GuptaManan100 commented Dec 13, 2023 •

edited

Loading

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

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

Comments

madhur commented Dec 9, 2023

Overview of the Issue

Reproduction Steps

Binary Version

Operating System and Environment details

Log Fragments

madhur commented Dec 9, 2023

mattlord commented Dec 9, 2023 • edited Loading

madhur commented Dec 9, 2023 via email

harshit-gangal commented Dec 11, 2023

madhur commented Dec 12, 2023

harshit-gangal commented Dec 12, 2023

GuptaManan100 commented Dec 13, 2023 • edited Loading

mattlord commented Dec 9, 2023 •

edited

Loading

GuptaManan100 commented Dec 13, 2023 •

edited

Loading