pt-online-schema-change and --set-vars sql_mode not working as expected for modifying column type

Description

Running pt-online-schema-change with --set-vars sql_mode=\'\' to convert a DOUBLE column to DECIMAL(16,4) is not fully working.

Here's a simple test case, first demonstrating how the default SQL modes prevent this alter:

 

 

Relaxing sql_mode will allow the truncated conversion of the values:

 

 

Now, trying to set sql_mode through the --set-vars option isn't fully working:

 

I've attached the full output file (/tmp/out2), here's a couple of excerpts from it:

 

 

We do see the:

above though. I run pt-osc once again adding --no-drop-new-table:

 

 

Inspecting _t1_new it does look to result in the table we want:

 

 

Which leaves me clueless as to why the tool is aborting with error 1264 despite the relaxed sql_mode.

I found old https://jira.percona.com/browse/PT-694 but while it also touches --set-vars and sql_mode the problem here seems to be a different one so it doesn't look like a regression.

 

Environment

None

AFFECTED CS IDs

264775

Attachments

1

Smart Checklist

Activity

Show:

Lalit Choudhary September 16, 2019 at 12:45 PM

Hi Fernando,

Thank you for the report.

Details

Assignee

Reporter

Priority

Affects versions

Smart Checklist

Created September 6, 2019 at 3:34 PM
Updated February 29, 2024 at 9:02 PM